期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）

《MySQL数据库基础》 期末复习卷(五) 时间:90分钟 总分:100分 班级 姓名 学号 成绩 一.数据库操作题(本大题共5小题,每小题20分,共100分) 1.汽车维修系统数据库db_maintenance包含了customers客户表.vehicles车辆表.repairs维修记录表。 customers客户表包含字段customerID(客户ID),cuname(客户姓名),gender(性别),age(年龄),phone(电话号码),email(邮箱)。 vehicles车辆表包含字段vehicleID(车辆ID),plateNumber(车牌号),brand(车辆品牌),model(车辆型号),proDate(车辆生产年份),customerID(客户ID)。 repairs维修记录表包含字段repairID(维修记录ID),vehicleID(车辆ID),repairDate(维修日期),description(维修描述),cost(维修费用) 上述数据库及数据表已经创建,请按要求完成以下操作 1)创建一个维修日志表repair_log,字段如下: 字段名称 类型 长度 需求 logID(日志ID) int 主键,自增长 repairID(维修记录ID) int 能为空 operation(具体操作) text 不能为空 time(耗费工时) int tname(技术人员姓名) varchar 50 2).在客户表中插入一条客户数据(赵六,男,35,13453245040) 3).将客户王五的车牌号修改为沪A88888 4).将客户李四在2023年10月10日更换轮胎的维修费用减去1000 5).查询客户张三的车辆维修记录,要求显示:维修描述.维修日期.维修费用；按维修日期升序排序 6).查询每位客户的维修次数,列名分别为:客户姓名.维修次数 7).创建一个视图v_car_repair,要求视图中显示出客户姓名.车牌号.车辆品牌.车辆型号.维修描述.维修日期.维修费用；按维修日期升序排序； 8).创建一个名称为trigger_insert的触发器,具体描述如下:当向customers客户表新增数据之前,如果客户的年龄(age)小于18岁,提示错误信息年龄必须大于等于18周岁 9).创建一个名称为p_getTotalCost的存储过程查询指定客户在该年份的维修总费用,具体描述如下:这个存储过程接收一个客户姓名(in_name),数据类型为varchar(20),一个年份(in_year),数据类型为year,作为输入参数,查询指定年份该客户的维修总费用,通过输出参数totalCost返回,类型为DECIMAL(10, 2) 答案: 1).CREATE TABLE repair_log ( logID INT PRIMARY KEY AUTO_INCREMENT,repairID INT NULL,operation TEXT NOT NULL,time INT,tname VARCHAR(50));2).INSERT INTO customers (cuname, gender, age, phone) VALUES ('赵六', '男', 35, '13453245040'); 3).UPDATE vehicles SET plateNumber = '沪A88888' WHERE customerID = (SELECT customerID FROM customers WHERE cuname = '王五'); 4).UPDATE repairs SET cost = cost - 1000 WHERE vehicleID IN (SELECT vehicleID FROM vehicles WHERE customerID = (SELECT customerID FROM customers WHERE cuname = '李四'))AND repairDate = '2023-10-10' AND description LIKE '%更换轮胎%'; 5).SELECT r.description, r.repairDate, r.cost FROM repairs r JOIN vehicles v ON r.vehicleID = v.vehicleID JOIN customers c ON v.customerID = c.customerID WHERE c.cuname = '张三'ORDER BY r.repairDate ASC;-- 6).SELECT c.cuname AS 客户姓名, COUNT(r.repairID) AS 维修次数FROM customers c LEFT JOIN vehicles v ON c.customerID = v.customerID LEFT JOIN repairs r ON v.vehicleID = r.vehicleID GROUP BY c.customerID, c.cuname; 7).CREATE VIEW v_car_repair ASSELECT c.cuname AS 客户姓名, v.plateNumber AS 车牌号,v.brand AS 车辆品牌, v.model AS 车辆型号, r.description AS 维修描述, r.repairDate AS 维修日期, r.cost AS 维修费用FROM repairs r JOIN vehicles v ON r.vehicleID = v.vehicleID JOIN customers c ON v.customerID = c.customerID ORDER BY r.repairDate ASC; 8).DELIMITER //CREATE TRIGGER trigger_insertBEFORE INSERT ON customersFOR EACH ROWBEGIN IF NEW.age < 18 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须大于等于18周岁'; END IF;END //DELIMITER ; 9).DELIMITER //CREATE PROCEDURE p_getTotalCost( IN in_name VARCHAR(20),IN in_year YEAR,OUT totalCost DECIMAL(10,2))BEGIN SELECT IFNULL(SUM(r.cost), 0) INTO totalCost FROM repairs r JOIN vehicles v ON r.vehicleID = v.vehicleID JOIN customers c ON v.customerID = c.customerID WHERE c.cuname = in_name AND YEAR(r.repairDate) = in_year;END //DELIMITER ; 2.公交管理系统数据库db_bus包含了t_driver司机表.t_bus公交表和t_attendance发车表 t_driver包含字段id(司机编号),name(司机姓名),sex(性别),age(年龄),salary(薪资)。 t_bus包含字段 id(公交编号),platenum(车牌号),linenum(线路号码)。 t_attendance包含字段id(发车编号),b_id(公交编号),d_id(司机编号),starttime(发车时间)。 1).创建一个收益表,名称为t_income,字段如下 字段名称 类型 长度 需求 id(收益编号) int 10 主键,自动增长 att_id(用户名) int 10 外键,对应t_attendance表id字段 money(金额) double 6,2 2).添加一个公交信息,公交编号10031.车牌号汽A781681.线路号码572。 3).修改司机杜震南的薪资增加200。 4).用sql语句查询年龄37,42和48岁的司机信息。 5).用sql语句查询行驶过线路号码为709的司机信息并去除重复,查询结果按照司机编号升序显示。 6).创建视图名称为v_attendance。要求显示发车信息,分别用列名:发车编号.公交车牌号.公交线路号码.司机姓名.发车时间。 7).创建一个触发器delete_driver,该触发器在t_driver表中删除司机信息之前,先删除与该司机相关的所有发车记录； 8).创建一个存储过程UpdateDriverSalary,该存储过程接收一个司机姓名(driverName)和一个薪资增加金额(increment)作为输入参数,参数类型分别为VARCHAR(255)和INT；根据司机的姓名更新指定司机的薪资； 答案: 1).CREATE TABLE t_income ( id INT(10) PRIMARY KEY AUTO_INCREMENT, att_id INT(10),money DOUBLE(6,2), FOREIGN KEY (att_id) REFERENCES t_attendance(id)); 2).INSERT INTO t_bus (id, platenum, linenum) VALUES (10031, '汽A781681', 572); 3).UPDATE t_driver SET salary = salary + 200 WHERE name = '杜震南'; 4).SELECT * FROM t_driver WHERE age IN (37, 42, 48); 5).SELECT DISTINCT d.* FROM t_driver d JOIN t_attendance a ON d.id = a.d_id JOIN t_bus b ON a.b_id = b.id WHERE b.linenum = 709 ORDER BY d.id ASC; 6).CREATE VIEW v_attendance ASSELECT a.id AS 发车编号, b.platenum AS 公交车牌号,b.linenum AS 公交线路号码, d.name AS 司机姓名, a.starttime AS 发车时间FROM t_attendance a JOIN t_bus b ON a.b_id = b.id JOIN t_driver d ON a.d_id = d.id; 7).DELIMITER //CREATE TRIGGER delete_driver BEFORE DELETE ON t_driverFOR EACH ROWBEGIN DELETE FROM t_attendance WHERE d_id = OLD.id;END //DELIMITER ; 8).DELIMITER //CREATE PROCEDURE UpdateDriverSalary(IN driverName VARCHAR(255), IN increment INT)BEGIN UPDATE t_driver SET salary = salary + increment WHERE name = driverName;END //DELIMITER ; 3.人力资源管理系统数据库db_hrm包含了department部门表.employee员工表.和 salary薪资表 department部门表 包含字段 did(部门编号),dname(部门名称),description(部门介绍) employee员工表 包含字段eid(员工编号),ename(员工姓名),sex(性别),age(年龄),position(职位),did(部门编号) salary薪资表 包含字段sid(薪资编号, 自增长),eid(员工编号),money(薪资),stime(薪资日期) 上述数据库及数据表已经创建,请按要求完成以下操作 1).创建一个培训记录表training,字段如下: 字段名称 类型 长度 需求 tid(流水编号) int 主键,自增长 did(部门编号) varchar 10 不能为空,外键,与department表关联 eid(员工编号) varchar 10 不能为空 , 外键,与employee表关联 topics(培训内容)varchar 50 默认值空 costs (培训费用) int 默认值0 duration(培训天数) int 默认值0 2).修改程序员张三01月份的工资,为其加1000元工资。 3).删除业务员孙七的信息 4).查询技术部人员的姓名,年龄,职位和工资, 并分别用列名:姓名,年龄,职位和工资 5).查询01月份工资在10000元以上(含10000)的员工的部门,姓名,职位, 并分别用列名:部门,姓名,职位和工资 6).创建一个视图v_program 要求显示职位为程序员的姓名和工资, 并分别用列名:姓名,工资 7).创建一个触发器g_after_employee_insert,该触发器将在employee表上的插入操作之后被触发。触发器需要执行以下操作:为该员工在salary表中创建一个薪资记录。薪资记录将包含员工的编号(eid).一个默认的薪资金额5000,以及该记录被创建的时间(使用当前时间戳,即NOW()函数)； 8).创建一个存储过程GetEmployeeSalary,该存储过程接收一个员工ID(emp_id)作为输入参数,类型为VARCHAR(10),查询结果返回该员工的姓名(ename)和薪资(money),并分别用列名姓名和薪资来表示； 答案: 1).CREATE TABLE training (tid INT PRIMARY KEY AUTO_INCREMENT,did VARCHAR(10) NOT NULL,eid VARCHAR(10) NOT NULL,topics VARCHAR(50) DEFAULT '', costs INT DEFAULT 0,duration INT DEFAULT 0, FOREIGN KEY (did) REFERENCES department(did),FOREIGN KEY (eid) REFERENCES employee(eid)); 2).UPDATE salary SET money = money + 1000 WHERE eid = (SELECT eid FROM employee WHERE ename = '张三' AND position = '程序员')AND stime BETWEEN '2025-01-01' AND '2025-01-31'; 3).DELETE FROM salary WHERE eid = (SELECT eid FROM employee WHERE ename = '孙七' AND position = '业务员');DELETE FROM employee WHERE ename = '孙七' AND position = '业务员'; 4).SELECT e.ename AS 姓名, e.age AS 年龄, e.position AS 职位, s.money AS 工资FROM employee e JOIN department d ON e.did = d.did LEFT JOIN salary s ON e.eid = s.eid WHERE d.dname = '技术部'; 5).SELECT d.dname AS 部门, e.ename AS 姓名, e.position AS 职位, s.money AS 工资FROM employee e JOIN department d ON e.did = d.did JOIN salary s ON e.eid = s.eid WHERE MONTH(s.stime) = 1 AND s.money >= 10000;6).CREATE VIEW v_program ASSELECT e.ename AS 姓名, s.money AS 工资FROM employee e JOIN salary s ON e.eid = s.eid WHERE e.position = '程序员'; 7).DELIMITER //CREATE TRIGGER g_after_employee_insertAFTER INSERT ON employeeFOR EACH ROWBEGIN INSERT INTO salary (eid, money, stime) VALUES (NEW.eid, 5000, NOW());END //DELIMITER ; 8).DELIMITER //CREATE PROCEDURE GetEmployeeSalary(IN emp_id VARCHAR(10))BEGIN SELECT e.ename AS 姓名, s.money AS 薪资 FROM employee e JOIN salary s ON e.eid = s.eid WHERE e.eid = emp_id;END //DELIMITER ; 4.网上商城数据库db_online,包含商品分类表category,商品表products,用户表users, 订单表orders,订单详细表orderitems, 其中: category(cid,cname,description) 各字段的含义依次为:商品类别ID,类别名称, 类别描述。主键是cid。 products(pid,cid, pname,price, quantity,sale_qty)各字段的含义依次为: 商品ID,类别ID,商品名称,价格,库存数量,销售数量。主键商品ID, 自增长 users(username,pwd, cellphone,credit)各字段的含义依次为: 主键用户名,密码,手机,积分。 orders(oid,uid, amount,ordertime, address)各字段的含义依次为:主键订单ID,用户ID,订单金额,下单时间, 收货地址。 orderitems(item_id,oid,gid,inum)各字段的含义依次为:主键详情ID,订单ID,商品ID,购买数量。 上述数据库及数据表已经创建,请按要求完成以下操作。 1).修改用户阿美的密码,新密码为123qwe 2).创建一个购物车表cart,字段如下: 字段名称 类型 长度 需求 cart_id(购物车) int 主键,自增长 uname (用户名) char 10 不能为空, 外键,与users表关联 pid(商品ID) varchar 10 不能为空,外键,与products表关联 pnum(商品数量) int 3).要求创建一个名为 procedure_add_product的存储过程,功能是在商品表中添加商品, 要求输入参数 为以下6个,各个参数的数据类型与products表格中对应字段一致: pid:商品ID cid:类别ID pname:商品名称 price:价格 quantity:库存数量 sale_qty:销售数量 4).请使用调用语句调用procedure_add_product存储过程,在商品表中给酒类添加商品如下:法国拉菲干红葡萄酒 ,商品id是p0007 ,价格150元/瓶,库存数量300,销售数量0。张裕解百纳蛇龙珠干红葡萄酒 ,商品id是p0008, 价格120元/瓶,库存数量500,销售数量0。 5).阿美向购物车添加了如下商品,请完善数据库中cart表的信息:法国拉菲干红葡萄酒3瓶,水蜜桃5斤 6).要求在cart购物车表上创建触发器,触发器名为 trigger_product_update,当用户下订单的时候,实际上需要从cart表删除一项商品的数据,即当一项商品销售成功,购物车需要删除数据,商品表products需要减少库存数量和增加销售数量 7).创建一个视图view_sale, 查询每种商品的销售数量,并按销售数量以降序排列, 并分别用列名:商品名, 销售数量 答案: 1).UPDATE users SET pwd = '123qwe' WHERE username = '阿美'; 2).CREATE TABLE cart (cart_id INT PRIMARY KEY AUTO_INCREMENT,uname CHAR(10) NOT NULL,pid VARCHAR(10) NOT NULL,pnum INT,FOREIGN KEY (uname) REFERENCES users(username),FOREIGN KEY (pid) REFERENCES products(pid)); 3).DELIMITER //CREATE PROCEDURE procedure_add_product(IN p_pid VARCHAR(10),IN p_cid INT,IN p_pname VARCHAR(100),IN p_price DECIMAL(10,2), IN p_quantity INT,IN p_sale_qty INT)BEGIN INSERT INTO products (pid, cid, pname, price, quantity, sale_qty) VALUES (p_pid, p_cid, p_pname, p_price, p_quantity, p_sale_qty);END //DELIMITER ; 4).CALL procedure_add_product('p0007', (SELECT cid FROM category WHERE cname = '酒类'), '法国拉菲干红葡萄酒', 150, 300, 0);CALL procedure_add_product('p0008', (SELECT cid FROM category WHERE cname = '酒类'), '张裕解百纳蛇龙珠干红葡萄酒', 120, 500, 0); 5).INSERT INTO cart (uname, pid, pnum) VALUES ('阿美', 'p0007', 3),('阿美', (SELECT pid FROM products WHERE pname = '水蜜桃'), 5);-- 6). 创建触发器（下单时更新购物车和商品表）DELIMITER //CREATE TRIGGER trigger_product_update AFTER INSERT ON orderitems FOR EACH ROWBEGIN DELETE FROM cart WHERE uname = (SELECT uid FROM orders WHERE oid = NEW.oid) AND pid = NEW.gid; UPDATE products SET quantity = quantity - NEW.inum, sale_qty = sale_qty + NEW.inum WHERE pid = NEW.gid;END //DELIMITER ; 7).CREATE VIEW view_sale ASSELECT p.pname AS 商品名, p.sale_qty AS 销售数量FROM products pORDER BY p.sale_qty DESC; 5.宾馆客房管理数据库 db_hotelroom 包含了3个表,即房客表(guest).客房表(room).住宿表(accomodation)。 guest表 包含字段 gid(房客id),gcardno(身份证号),gname(姓名),gsex(性别),gcell (手机号) , 主键是 gid room表 包含字段 rno(房号),rtype(客房类型),rprice(房价), rtel(客房 电话 ), rstatus(客房状态 ), 主键是房号rno accomodation表 包含字段 gid(房客id), rno(房号),deposit( 押金), checkin(入住日期) checkout(退房日期), 复合主键是 (gid, rno) 上述数据库及数据表已经创建,客房状态分四种:清洁中.维修中.空闲.入住。请按要求完成以下操作。 1).创建一个客房物品表,名称为roomitem,记录客房内的额外物品,如麻将桌,小冰箱,咖啡机等。字段如下； 字段名称 类型 长度 需求 itemno (物品编号) varchar 10 不能为空,主键 rno(房号) varchar 10 默认为null itemname(物品名称) varchar 10 不能为空, itemprice(物品单价) int itemnum (物品数量) int 2).请将下列物品放入指定客房,完善客房物品表里的数据。 放入205套房的物品: 001号小冰箱1 台,价值500元；002号咖啡机1 台,价值200元 放入206套房的物品: 003号咖啡机1 台,价值200元；004号麻将桌1 台,价值200元 3).用一条SQL语句将102和203房间的状态改为空闲,并在客房表(room)中同步其状态 4).创建一个存储过程procedure_item,该存储过程接收一个物品名称(in_itemname)作为输入参数,数据类型为varchar(10),使用联合查询,查询那些配有额外物品的客房,要求显示字段房号.物品名称和客房类型；例如:输入参数咖啡机, 则可以查出所有配有咖啡机的客房。 5).2024年08月07日,一位名叫李明的男性房客前来住宿,李明的身份证号码是 420107196512316321, 手机号码 18908671167。 前台将他的信息录入系统时,系统使用算法给他产生的房客id是11。请写出SQL语句将李明的信息录入系统。 6).创建一个触发器trigger_update_room,该触发器将在accomodation表更新之后被触发。触发器需要执行以下操作:当住宿表中设置了退房日期,立即触发将对应客房的状态设置为空闲； 7).创建一个视图 view_availableroom_items , 查询所有空闲的套间中配置的物品,分别用列名:房号 , 客房类型 ,客房状态, 物品名称, 物品数量 答案: 1).CREATE TABLE roomitem (itemno VARCHAR(10) PRIMARY KEY,rno VARCHAR(10) DEFAULT NULL,itemname VARCHAR(10) NOT NULL,itemprice INT,itemnum INT); 2).INSERT INTO roomitem (itemno, rno, itemname, itemprice, itemnum) VALUES('001', '205', '小冰箱', 500, 1),('002', '205', '咖啡机', 200, 1),('003', '206', '咖啡机', 200, 1),('004', '206', '麻将桌', 200, 1);3).UPDATE room SET rstatus = '空闲' WHERE rno IN ('102', '203'); 4).DELIMITER //CREATE PROCEDURE procedure_item(IN in_itemname VARCHAR(10))BEGIN SELECT r.rno AS 房号, ri.itemname AS 物品名称, r.rtype AS 客房类型 FROM roomitem ri JOIN room r ON ri.rno = r.rno WHERE ri.itemname = in_itemname;END //DELIMITER ; 5).INSERT INTO guest (gid, gcardno, gname, gsex, gcell) VALUES (11, '420107196512316321', '李明', '男', '18908671167'); 6).DELIMITER //CREATE TRIGGER trigger_update_room AFTER UPDATE ON accomodation FOR EACH ROWBEGIN IF NEW.checkout IS NOT NULL AND OLD.checkout IS NULL THEN UPDATE room SET rstatus = '空闲' WHERE rno = NEW.rno; END IF;END //DELIMITER ; 7).CREATE VIEW view_availableroom_items ASSELECT r.rno AS 房号, r.rtype AS 客房类型, r.rstatus AS 客房状态,ri.itemname AS 物品名称, ri.itemnum AS 物品数量FROM room r JOIN roomitem ri ON r.rno = ri.rno WHERE r.rstatus = '空闲' AND r.rtype = '套间'; 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题一/db_maintenance.myd /* Navicat MySQL Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:6033 Source Schema : db_maintenance Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 07/11/2024 14:56:33 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for customers -- ---------------------------- DROP TABLE IF EXISTS `customers`; CREATE TABLE `customers` ( `customerID` int(11) NOT NULL AUTO_INCREMENT, `cuname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `gender` enum('男','女') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`customerID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of customers -- ---------------------------- INSERT INTO `customers` VALUES (1, '张三', '男', 30, '13842138480', 'zhangsan@qq.com'); INSERT INTO `customers` VALUES (2, '李四', '女', 25, '13457804500', 'lisi@qq.com'); INSERT INTO `customers` VALUES (3, '王五', '男', 45, '13538355400', 'wangwu@qq.com'); -- ---------------------------- -- Table structure for repairs -- ---------------------------- DROP TABLE IF EXISTS `repairs`; CREATE TABLE `repairs` ( `repairID` int(11) NOT NULL AUTO_INCREMENT, `vehicleID` int(11) NULL DEFAULT NULL, `repairDate` date NOT NULL, `description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL, `cost` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`repairID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of repairs -- ---------------------------- INSERT INTO `repairs` VALUES (1, 1, '2022-05-20', '更换空气滤清器', 150.00); INSERT INTO `repairs` VALUES (2, 1, '2023-03-10', '更换火花塞', 300.00); INSERT INTO `repairs` VALUES (3, 1, '2023-10-01', '更换刹车片', 800.00); INSERT INTO `repairs` VALUES (4, 1, '2023-11-15', '发动机保养', 1200.00); INSERT INTO `repairs` VALUES (5, 1, '2024-01-15', '四轮定位', 450.00); INSERT INTO `repairs` VALUES (6, 2, '2022-07-30', '更换冷却液', 200.00); INSERT INTO `repairs` VALUES (7, 2, '2023-08-20', '更换刹车油', 250.00); INSERT INTO `repairs` VALUES (8, 2, '2023-10-10', '更换轮胎', 2000.00); INSERT INTO `repairs` VALUES (9, 2, '2023-12-05', '更换雨刮片', 50.00); INSERT INTO `repairs` VALUES (10, 3, '2022-11-15', '更换轮胎（后轮）', 1000.00); INSERT INTO `repairs` VALUES (11, 3, '2023-06-05', '更换轮胎（前轮）', 1000.00); INSERT INTO `repairs` VALUES (12, 3, '2023-11-05', '检查并更换电池', 400.00); INSERT INTO `repairs` VALUES (13, 3, '2024-02-20', '全车检查', 800.00); -- ---------------------------- -- Table structure for vehicles -- ---------------------------- DROP TABLE IF EXISTS `vehicles`; CREATE TABLE `vehicles` ( `vehicleID` int(11) NOT NULL AUTO_INCREMENT, `plateNumber` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `brand` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `model` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `proDate` year NULL DEFAULT NULL, `customerID` int(11) NULL DEFAULT NULL, PRIMARY KEY (`vehicleID`) USING BTREE, UNIQUE INDEX `plateNumber`(`plateNumber`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of vehicles -- ---------------------------- INSERT INTO `vehicles` VALUES (1, '鄂A12345', 'Toyota', 'Camry', 2018, 1); INSERT INTO `vehicles` VALUES (2, '京C51545', 'Honda', 'Civic', 2016, 2); INSERT INTO `vehicles` VALUES (3, '沪B67890', 'BMW', 'X5', 2020, 3); INSERT INTO `vehicles` VALUES (4, '津B25487', 'xiaomi', 'su7', 2024, 4); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题三/db_hrm.myd /* Navicat MySQL Data Transfer Source Server : demo Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:3306 Source Schema : db_hrm Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 15/07/2024 13:10:10 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for department -- ---------------------------- DROP TABLE IF EXISTS `department`; CREATE TABLE `department` ( `did` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `description` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '', PRIMARY KEY (`did`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of department -- ---------------------------- INSERT INTO `department` VALUES ('01', '技术部', ''); INSERT INTO `department` VALUES ('02', '财务部', ''); INSERT INTO `department` VALUES ('03', '销售部', ''); -- ---------------------------- -- Table structure for employee -- ---------------------------- DROP TABLE IF EXISTS `employee`; CREATE TABLE `employee` ( `eid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `ename` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `position` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `did` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`eid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of employee -- ---------------------------- INSERT INTO `employee` VALUES ('001', '张一', 40, '男', '项目经理', '01'); INSERT INTO `employee` VALUES ('002', '张二', 35, '男', '架构师', '01'); INSERT INTO `employee` VALUES ('003', '张三', 30, '男', '程序员', '01'); INSERT INTO `employee` VALUES ('004', '张四', 25, '女', '程序员', '01'); INSERT INTO `employee` VALUES ('005', '李五', 25, '女', '出纳', '02'); INSERT INTO `employee` VALUES ('006', '李六', 35, '女', '会计', '02'); INSERT INTO `employee` VALUES ('007', '孙七', 25, '男', '业务员', '03'); -- ---------------------------- -- Table structure for salary -- ---------------------------- DROP TABLE IF EXISTS `salary`; CREATE TABLE `salary` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `eid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `money` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `stime` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`sid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of salary -- ---------------------------- INSERT INTO `salary` VALUES (1, '001', '10000', '2024-01-15'); INSERT INTO `salary` VALUES (2, '002', '8000', '2024-01-15'); INSERT INTO `salary` VALUES (3, '003', '15000', '2024-01-15'); INSERT INTO `salary` VALUES (4, '004', '12000', '2024-01-15'); INSERT INTO `salary` VALUES (5, '005', '9000', '2024-01-15'); INSERT INTO `salary` VALUES (6, '006', '9500', '2024-01-15'); INSERT INTO `salary` VALUES (7, '007', '20000', '2024-01-15'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题二/db_bus.myd CREATE DATABASE IF NOT EXISTS `db_bus` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `db_bus`; /* Navicat MySQL Data Transfer Source Server : 1 Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:3306 Source Schema : db_bus Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 19/06/2024 14:21:11 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_attendance -- ---------------------------- DROP TABLE IF EXISTS `t_attendance`; CREATE TABLE `t_attendance` ( `id` int(10) NOT NULL AUTO_INCREMENT, `b_id` int(10) NOT NULL, `d_id` int(10) NOT NULL, `starttime` datetime NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10101 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of t_attendance -- ---------------------------- INSERT INTO `t_attendance` VALUES (10001, 10022, 10002, '2023-08-25 09:07:31'); INSERT INTO `t_attendance` VALUES (10002, 10012, 10030, '2023-09-07 11:06:41'); INSERT INTO `t_attendance` VALUES (10003, 10014, 10010, '2023-08-31 10:41:17'); INSERT INTO `t_attendance` VALUES (10004, 10005, 10012, '2023-03-23 11:12:33'); INSERT INTO `t_attendance` VALUES (10005, 10017, 10023, '2023-08-18 14:47:37'); INSERT INTO `t_attendance` VALUES (10006, 10029, 10016, '2023-01-06 12:29:33'); INSERT INTO `t_attendance` VALUES (10007, 10008, 10004, '2023-01-24 16:52:11'); INSERT INTO `t_attendance` VALUES (10008, 10021, 10028, '2023-03-26 19:37:46'); INSERT INTO `t_attendance` VALUES (10009, 10001, 10026, '2023-09-07 13:53:22'); INSERT INTO `t_attendance` VALUES (10010, 10024, 10006, '2023-03-23 09:26:14'); INSERT INTO `t_attendance` VALUES (10011, 10013, 10018, '2023-07-05 19:57:22'); INSERT INTO `t_attendance` VALUES (10013, 10016, 10007, '2023-10-20 09:07:33'); INSERT INTO `t_attendance` VALUES (10014, 10018, 10014, '2023-02-10 15:44:22'); INSERT INTO `t_attendance` VALUES (10015, 10010, 10001, '2023-09-02 18:56:22'); INSERT INTO `t_attendance` VALUES (10016, 10003, 10029, '2023-03-07 10:18:40'); INSERT INTO `t_attendance` VALUES (10017, 10004, 10021, '2023-05-28 12:14:06'); INSERT INTO `t_attendance` VALUES (10018, 10002, 10005, '2023-03-08 12:37:45'); INSERT INTO `t_attendance` VALUES (10019, 10006, 10008, '2023-04-04 14:22:22'); INSERT INTO `t_attendance` VALUES (10020, 10030, 10024, '2023-03-05 14:50:02'); INSERT INTO `t_attendance` VALUES (10021, 10026, 10003, '2023-11-21 10:32:26'); INSERT INTO `t_attendance` VALUES (10022, 10023, 10013, '2023-11-06 13:28:33'); INSERT INTO `t_attendance` VALUES (10023, 10020, 10019, '2023-12-30 19:34:49'); INSERT INTO `t_attendance` VALUES (10024, 10027, 10015, '2023-06-25 13:35:57'); INSERT INTO `t_attendance` VALUES (10025, 10019, 10009, '2023-08-19 14:58:24'); INSERT INTO `t_attendance` VALUES (10026, 10015, 10020, '2023-01-22 15:25:28'); INSERT INTO `t_attendance` VALUES (10027, 10011, 10017, '2023-08-23 14:02:10'); INSERT INTO `t_attendance` VALUES (10028, 10007, 10025, '2023-02-25 11:05:08'); INSERT INTO `t_attendance` VALUES (10029, 10028, 10011, '2023-06-03 16:56:48'); INSERT INTO `t_attendance` VALUES (10030, 10009, 10022, '2023-08-01 11:26:41'); INSERT INTO `t_attendance` VALUES (10031, 10020, 10013, '2023-11-05 10:11:38'); INSERT INTO `t_attendance` VALUES (10032, 10018, 10015, '2023-05-21 18:35:25'); INSERT INTO `t_attendance` VALUES (10033, 10020, 10009, '2023-02-23 14:16:09'); INSERT INTO `t_attendance` VALUES (10034, 10024, 10030, '2023-08-12 13:46:36'); INSERT INTO `t_attendance` VALUES (10035, 10022, 10025, '2023-03-01 13:59:40'); INSERT INTO `t_attendance` VALUES (10036, 10027, 10022, '2023-04-23 17:15:48'); INSERT INTO `t_attendance` VALUES (10037, 10029, 10017, '2023-07-13 09:03:04'); INSERT INTO `t_attendance` VALUES (10038, 10025, 10023, '2023-02-02 19:08:01'); INSERT INTO `t_attendance` VALUES (10039, 10017, 10016, '2023-05-30 13:22:39'); INSERT INTO `t_attendance` VALUES (10040, 10029, 10021, '2023-11-21 14:04:09'); INSERT INTO `t_attendance` VALUES (10041, 10029, 10006, '2023-06-29 13:32:19'); INSERT INTO `t_attendance` VALUES (10042, 10004, 10010, '2023-01-13 19:04:23'); INSERT INTO `t_attendance` VALUES (10043, 10023, 10019, '2023-09-05 16:36:19'); INSERT INTO `t_attendance` VALUES (10044, 10022, 10023, '2023-03-08 10:34:10'); INSERT INTO `t_attendance` VALUES (10045, 10029, 10001, '2023-01-05 11:31:37'); INSERT INTO `t_attendance` VALUES (10046, 10001, 10021, '2023-12-04 09:25:37'); INSERT INTO `t_attendance` VALUES (10047, 10024, 10014, '2023-01-09 15:07:08'); INSERT INTO `t_attendance` VALUES (10048, 10001, 10003, '2023-08-14 14:52:45'); INSERT INTO `t_attendance` VALUES (10049, 10025, 10001, '2023-06-15 11:27:16'); INSERT INTO `t_attendance` VALUES (10050, 10020, 10013, '2023-08-17 16:42:19'); INSERT INTO `t_attendance` VALUES (10051, 10019, 10009, '2023-05-02 19:25:32'); INSERT INTO `t_attendance` VALUES (10052, 10024, 10004, '2023-02-16 09:40:03'); INSERT INTO `t_attendance` VALUES (10053, 10021, 10004, '2023-02-05 11:25:57'); INSERT INTO `t_attendance` VALUES (10054, 10008, 10011, '2023-08-09 16:08:41'); INSERT INTO `t_attendance` VALUES (10055, 10017, 10009, '2023-08-09 09:12:14'); INSERT INTO `t_attendance` VALUES (10056, 10023, 10027, '2023-07-01 13:49:50'); INSERT INTO `t_attendance` VALUES (10057, 10010, 10009, '2023-07-07 15:37:31'); INSERT INTO `t_attendance` VALUES (10058, 10022, 10029, '2023-01-01 17:06:08'); INSERT INTO `t_attendance` VALUES (10059, 10030, 10021, '2023-11-30 19:45:59'); INSERT INTO `t_attendance` VALUES (10060, 10025, 10025, '2023-12-10 09:49:41'); INSERT INTO `t_attendance` VALUES (10061, 10021, 10005, '2023-01-19 09:01:16'); INSERT INTO `t_attendance` VALUES (10062, 10003, 10011, '2023-11-09 18:17:00'); INSERT INTO `t_attendance` VALUES (10063, 10020, 10010, '2023-01-07 16:13:11'); INSERT INTO `t_attendance` VALUES (10064, 10018, 10013, '2023-10-26 19:16:52'); INSERT INTO `t_attendance` VALUES (10065, 10027, 10018, '2023-04-22 14:41:12'); INSERT INTO `t_attendance` VALUES (10066, 10010, 10007, '2023-06-18 19:36:42'); INSERT INTO `t_attendance` VALUES (10067, 10003, 10029, '2023-04-16 19:17:53'); INSERT INTO `t_attendance` VALUES (10068, 10007, 10018, '2023-12-01 16:02:21'); INSERT INTO `t_attendance` VALUES (10069, 10023, 10018, '2023-05-27 18:15:06'); INSERT INTO `t_attendance` VALUES (10070, 10023, 10002, '2023-02-07 09:36:44'); INSERT INTO `t_attendance` VALUES (10071, 10004, 10014, '2023-10-25 12:42:57'); INSERT INTO `t_attendance` VALUES (10072, 10030, 10029, '2023-11-02 15:48:02'); INSERT INTO `t_attendance` VALUES (10073, 10012, 10002, '2023-02-13 09:53:12'); INSERT INTO `t_attendance` VALUES (10074, 10013, 10008, '2023-06-03 13:01:57'); INSERT INTO `t_attendance` VALUES (10075, 10024, 10010, '2023-12-06 13:37:06'); INSERT INTO `t_attendance` VALUES (10076, 10001, 10030, '2023-09-25 17:11:48'); INSERT INTO `t_attendance` VALUES (10077, 10028, 10011, '2023-03-17 16:00:25'); INSERT INTO `t_attendance` VALUES (10078, 10008, 10010, '2023-03-30 16:18:53'); INSERT INTO `t_attendance` VALUES (10079, 10025, 10030, '2023-01-04 12:35:19'); INSERT INTO `t_attendance` VALUES (10080, 10017, 10021, '2023-03-06 17:14:56'); INSERT INTO `t_attendance` VALUES (10081, 10011, 10020, '2023-03-25 09:43:01'); INSERT INTO `t_attendance` VALUES (10082, 10009, 10017, '2023-04-27 18:40:03'); INSERT INTO `t_attendance` VALUES (10083, 10017, 10020, '2023-02-03 10:40:02'); INSERT INTO `t_attendance` VALUES (10084, 10019, 10018, '2023-10-18 09:47:19'); INSERT INTO `t_attendance` VALUES (10085, 10021, 10005, '2023-02-02 10:51:45'); INSERT INTO `t_attendance` VALUES (10086, 10007, 10013, '2023-12-13 09:13:00'); INSERT INTO `t_attendance` VALUES (10087, 10021, 10030, '2023-06-01 10:55:42'); INSERT INTO `t_attendance` VALUES (10088, 10022, 10022, '2023-07-25 10:45:58'); INSERT INTO `t_attendance` VALUES (10089, 10001, 10005, '2023-07-05 19:23:06'); INSERT INTO `t_attendance` VALUES (10090, 10030, 10028, '2023-07-29 14:14:38'); INSERT INTO `t_attendance` VALUES (10091, 10030, 10017, '2023-03-12 11:45:46'); INSERT INTO `t_attendance` VALUES (10092, 10002, 10015, '2023-03-05 15:44:02'); INSERT INTO `t_attendance` VALUES (10093, 10019, 10022, '2023-08-10 11:58:36'); INSERT INTO `t_attendance` VALUES (10094, 10019, 10019, '2023-07-05 09:19:00'); INSERT INTO `t_attendance` VALUES (10095, 10004, 10027, '2023-04-01 13:52:53'); INSERT INTO `t_attendance` VALUES (10096, 10011, 10005, '2023-01-14 15:09:12'); INSERT INTO `t_attendance` VALUES (10097, 10005, 10016, '2023-01-24 12:23:13'); INSERT INTO `t_attendance` VALUES (10098, 10012, 10027, '2023-10-24 16:27:54'); INSERT INTO `t_attendance` VALUES (10099, 10005, 10019, '2023-09-12 16:13:19'); INSERT INTO `t_attendance` VALUES (10100, 10007, 10028, '2023-09-02 19:04:05'); -- ---------------------------- -- Table structure for t_bus -- ---------------------------- DROP TABLE IF EXISTS `t_bus`; CREATE TABLE `t_bus` ( `id` int(10) NOT NULL AUTO_INCREMENT, `platenum` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `linenum` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10031 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of t_bus -- ---------------------------- INSERT INTO `t_bus` VALUES (10001, '汽A112955', '518'); INSERT INTO `t_bus` VALUES (10002, '汽A607691', '518'); INSERT INTO `t_bus` VALUES (10003, '汽A104488', '546'); INSERT INTO `t_bus` VALUES (10004, '汽A610201', '518'); INSERT INTO `t_bus` VALUES (10005, '汽A206621', '518'); INSERT INTO `t_bus` VALUES (10006, '汽A841495', '709'); INSERT INTO `t_bus` VALUES (10007, '汽A747618', '156'); INSERT INTO `t_bus` VALUES (10008, '汽A480086', '912'); INSERT INTO `t_bus` VALUES (10009, '汽A184551', '518'); INSERT INTO `t_bus` VALUES (10010, '汽A178252', '709'); INSERT INTO `t_bus` VALUES (10011, '汽A268525', '659'); INSERT INTO `t_bus` VALUES (10012, '汽A164580', '912'); INSERT INTO `t_bus` VALUES (10013, '汽A684949', '156'); INSERT INTO `t_bus` VALUES (10014, '汽A787640', '518'); INSERT INTO `t_bus` VALUES (10015, '汽A796090', '156'); INSERT INTO `t_bus` VALUES (10016, '汽A008904', '709'); INSERT INTO `t_bus` VALUES (10017, '汽A031829', '518'); INSERT INTO `t_bus` VALUES (10018, '汽A064919', '156'); INSERT INTO `t_bus` VALUES (10019, '汽A351322', '572'); INSERT INTO `t_bus` VALUES (10020, '汽A504115', '156'); INSERT INTO `t_bus` VALUES (10021, '汽A503898', '546'); INSERT INTO `t_bus` VALUES (10022, '汽A586531', '709'); INSERT INTO `t_bus` VALUES (10023, '汽A931569', '546'); INSERT INTO `t_bus` VALUES (10024, '汽A730504', '709'); INSERT INTO `t_bus` VALUES (10025, '汽A440456', '518'); INSERT INTO `t_bus` VALUES (10026, '汽A371647', '912'); INSERT INTO `t_bus` VALUES (10027, '汽A367102', '518'); INSERT INTO `t_bus` VALUES (10028, '汽A293829', '912'); INSERT INTO `t_bus` VALUES (10029, '汽A185285', '659'); INSERT INTO `t_bus` VALUES (10030, '汽A112046', '546'); -- ---------------------------- -- Table structure for t_driver -- ---------------------------- DROP TABLE IF EXISTS `t_driver`; CREATE TABLE `t_driver` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(3) NULL DEFAULT NULL, `salary` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10032 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of t_driver -- ---------------------------- INSERT INTO `t_driver` VALUES (10001, '崔震南', '男', 52, 7441.00); INSERT INTO `t_driver` VALUES (10002, '蔡宇宁', '男', 48, 7565.00); INSERT INTO `t_driver` VALUES (10003, '吴子异', '男', 48, 8017.00); INSERT INTO `t_driver` VALUES (10004, '雷岚', '女', 48, 8880.00); INSERT INTO `t_driver` VALUES (10005, '蒋璐', '女', 33, 7922.00); INSERT INTO `t_driver` VALUES (10006, '谢云熙', '男', 50, 7101.00); INSERT INTO `t_driver` VALUES (10007, '于安琪', '女', 49, 7130.00); INSERT INTO `t_driver` VALUES (10008, '尹睿', '男', 37, 7987.00); INSERT INTO `t_driver` VALUES (10009, '吴睿', '男', 46, 8326.00); INSERT INTO `t_driver` VALUES (10010, '曾睿', '男', 30, 7882.00); INSERT INTO `t_driver` VALUES (10011, '钟睿', '男', 33, 8263.00); INSERT INTO `t_driver` VALUES (10012, '谢嘉伦', '男', 54, 8394.00); INSERT INTO `t_driver` VALUES (10013, '杜震南', '男', 53, 7001.00); INSERT INTO `t_driver` VALUES (10014, '韩云熙', '男', 38, 7179.00); INSERT INTO `t_driver` VALUES (10015, '董子韬', '男', 49, 7271.00); INSERT INTO `t_driver` VALUES (10016, '金安琪', '女', 54, 8684.00); INSERT INTO `t_driver` VALUES (10017, '卢子异', '男', 36, 7699.00); INSERT INTO `t_driver` VALUES (10018, '叶子异', '男', 51, 7144.00); INSERT INTO `t_driver` VALUES (10019, '姚震南', '男', 36, 7136.00); INSERT INTO `t_driver` VALUES (10020, '程震南', '男', 28, 8821.00); INSERT INTO `t_driver` VALUES (10021, '于秀英', '女', 44, 8815.00); INSERT INTO `t_driver` VALUES (10022, '傅睿', '男', 50, 7667.00); INSERT INTO `t_driver` VALUES (10023, '向安琪', '女', 30, 8852.00); INSERT INTO `t_driver` VALUES (10024, '谢致远', '男', 54, 8299.00); INSERT INTO `t_driver` VALUES (10025, '姚云熙', '男', 48, 8610.00); INSERT INTO `t_driver` VALUES (10026, '姚震南', '男', 48, 8631.00); INSERT INTO `t_driver` VALUES (10027, '蒋致远', '男', 40, 7779.00); INSERT INTO `t_driver` VALUES (10028, '郑秀英', '女', 38, 7157.00); INSERT INTO `t_driver` VALUES (10029, '熊睿', '男', 42, 8965.00); INSERT INTO `t_driver` VALUES (10030, '余岚', '女', 52, 7105.00); INSERT INTO `t_driver` VALUES (10031, '李常德', '男', 34, 7200.00); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题五/db_hotelroom.myd /* Navicat MySQL Data Transfer Source Server : demo Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:3306 Source Schema : db_hotelroom Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 07/08/2024 09:31:59 */ CREATE DATABASE db_hotelroom CHARACTER SET utf8 COLLATE utf8_general_ci; USE db_hotelroom; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for accomodation -- ---------------------------- DROP TABLE IF EXISTS `accomodation`; CREATE TABLE `accomodation` ( `gid` int(11) NOT NULL COMMENT '房客id', `rno` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '房号', `deposite` float NULL DEFAULT 0 COMMENT '押金', `checkin` date NOT NULL COMMENT '入住日期', `checkout` date NULL DEFAULT NULL COMMENT '退房日期', PRIMARY KEY (`gid`, `rno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '住宿表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of accomodation -- ---------------------------- INSERT INTO accomodation VALUES (1, '100', 150.0, '2014-08-02', null), (5, '103', 200.0, '2014-08-02', null), (10, '206', 300.0, '2014-08-02', null); -- ---------------------------- -- Table structure for guest -- ---------------------------- DROP TABLE IF EXISTS `guest`; CREATE TABLE `guest` ( `gid` int(11) NOT NULL COMMENT '房客id', `gcardno` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身份证号', `gname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名', `gsex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别', `gcell` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号', PRIMARY KEY (`gid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '房客表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of guest -- ---------------------------- INSERT INTO `guest` VALUES (1, '410106196001015111', '张三', '男', '18908641111'); INSERT INTO `guest` VALUES (2, '420116196001015111', '张四', '男', '18908641112'); INSERT INTO `guest` VALUES (3, '430126196001015111', '张五', '男', '18908642113'); INSERT INTO `guest` VALUES (4, '440136196001015111', '张六', '女', '18908643114'); INSERT INTO `guest` VALUES (5, '450146196001015111', '张七', '男', '18908644115'); INSERT INTO `guest` VALUES (6, '460156196001015111', '张八', '女', '18908645116'); INSERT INTO `guest` VALUES (7, '470166196001015111', '张九', '女', '18908646117'); INSERT INTO `guest` VALUES (8, '480176196001015111', '李四', '女', '18908647118'); INSERT INTO `guest` VALUES (9, '490186196001015111', '李五', '男', '18908648119'); INSERT INTO `guest` VALUES (10, '400196196001015111', '李六', '女', '18908649110'); -- ---------------------------- -- Table structure for room -- ---------------------------- DROP TABLE IF EXISTS `room`; CREATE TABLE `room` ( `rno` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '房号', `rtype` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '客房类型', `rprice` float NOT NULL COMMENT '房价', `rtel` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客房电话', `rstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客房电话', PRIMARY KEY (`rno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '客房表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of room -- ---------------------------- INSERT INTO `room` VALUES ('100', '单人间', 150, '8100', '入住'); INSERT INTO `room` VALUES ('101', '单人间', 150, '8101', '空闲'); INSERT INTO `room` VALUES ('102', '标准间', 200, '8102', '清洁中'); INSERT INTO `room` VALUES ('103', '标准间', 200, '8103', '入住'); INSERT INTO `room` VALUES ('200', '标准间', 200, '8200', '空闲'); INSERT INTO `room` VALUES ('201', '标准间', 200, '8201', '空闲'); INSERT INTO `room` VALUES ('203', '三人间', 250, '8203', '维修中'); INSERT INTO `room` VALUES ('204', '三人间', 250, '8204', '空闲'); INSERT INTO `room` VALUES ('205', '套间', 300, '8205', '空闲'); INSERT INTO `room` VALUES ('206', '套间', 300, '8206', '入住'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题四/db_online.myd /* Navicat MySQL Data Transfer Source Server : demo Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:3306 Source Schema : db_online Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 05/08/2024 10:08:21 */ CREATE DATABASE db_online CHARACTER SET utf8 COLLATE utf8_general_ci; USE db_online; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for category -- ---------------------------- DROP TABLE IF EXISTS `category`; CREATE TABLE `category` ( `cid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `description` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of category -- ---------------------------- INSERT INTO `category` VALUES ('001', '水果', '各种应季水果'); INSERT INTO `category` VALUES ('002', '调料', '南北调料'); INSERT INTO `category` VALUES ('003', '零食', '健康零食'); INSERT INTO `category` VALUES ('004', '酒', '啤酒、葡萄酒、白酒等'); -- ---------------------------- -- Table structure for orderitems -- ---------------------------- DROP TABLE IF EXISTS `orderitems`; CREATE TABLE `orderitems` ( `items_id` int(11) NOT NULL AUTO_INCREMENT, `oid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `pid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `inum` int(11) NULL DEFAULT NULL, PRIMARY KEY (`items_id`) USING BTREE, INDEX `oid`(`oid`) USING BTREE, INDEX `pid`(`pid`) USING BTREE, CONSTRAINT `orderitems_ibfk_1` FOREIGN KEY (`oid`) REFERENCES `orders` (`oid`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `orderitems_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `products` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of orderitems -- ---------------------------- INSERT INTO `orderitems` VALUES (1, 'd0001', 'p0005', 1); INSERT INTO `orderitems` VALUES (2, 'd0001', 'p0006', 1); -- ---------------------------- -- Table structure for orders -- ---------------------------- DROP TABLE IF EXISTS `orders`; CREATE TABLE `orders` ( `oid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `username` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `amount` double NULL DEFAULT NULL, `ordertime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`oid`) USING BTREE, INDEX `username`(`username`) USING BTREE, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of orders -- ---------------------------- INSERT INTO `orders` VALUES ('d0001', '阿康', 30, '2024-07-10 10:20:30', 'A省B市前进路130号'); -- ---------------------------- -- Table structure for products -- ---------------------------- DROP TABLE IF EXISTS `products`; CREATE TABLE `products` ( `pid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `cid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `pname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `price` double NULL DEFAULT NULL, `quantity` int(11) NULL DEFAULT NULL, `sale_qty` int(11) NULL DEFAULT NULL, PRIMARY KEY (`pid`) USING BTREE, INDEX `cid`(`cid`) USING BTREE, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `category` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of products -- ---------------------------- INSERT INTO `products` VALUES ('p0001', '001', '水蜜桃', 5.5, 100, 30); INSERT INTO `products` VALUES ('p0002', '001', '西瓜', 2.5, 500, 325); INSERT INTO `products` VALUES ('p0003', '002', '李锦记生抽', 10.5, 50, 30); INSERT INTO `products` VALUES ('p0004', '002', '食盐', 2, 100, 90); INSERT INTO `products` VALUES ('p0005', '003', '牛肉粒', 20, 100, 66); INSERT INTO `products` VALUES ('p0006', '003', '果冻', 15, 100, 6); -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `username` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `pwd` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `cellphone` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `city` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `credit` int(11) NULL DEFAULT NULL, PRIMARY KEY (`username`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO `users` VALUES ('阿信', 'qwe123', '18912345678', '女','武汉',100); INSERT INTO `users` VALUES ('阿康', 'qwe456', '18956789012', '男','武汉',180); INSERT INTO `users` VALUES ('阿春', 'qwe178', '18923456789', '女','长沙',2); INSERT INTO `users` VALUES ('阿美', 'qwe345', '18934567890', '女','长沙',1000); INSERT INTO `users` VALUES ('阿龙', 'qwe567', '18945678901', '男','长沙',60); SET FOREIGN_KEY_CHECKS = 1; $ 《MySQL数据库基础》 期末复习卷(五) 时间:90分钟 总分:100分 班级 姓名 学号 成绩 一.数据库操作题(本大题共5小题,每小题20分,共100分) 1.汽车维修系统数据库db_maintenance包含了customers客户表.vehicles车辆表.repairs维修记录表。 customers客户表包含字段customerID(客户ID),cuname(客户姓名),gender(性别),age(年龄),phone(电话号码),email(邮箱)。 vehicles车辆表包含字段vehicleID(车辆ID),plateNumber(车牌号),brand(车辆品牌),model(车辆型号),proDate(车辆生产年份),customerID(客户ID)。 repairs维修记录表包含字段repairID(维修记录ID),vehicleID(车辆ID),repairDate(维修日期),description(维修描述),cost(维修费用) 上述数据库及数据表已经创建,请按要求完成以下操作 1)创建一个维修日志表repair_log,字段如下: 字段名称 类型 长度 需求 logID(日志ID) int 主键,自增长 repairID(维修记录ID) int 能为空 operation(具体操作) text 不能为空 time(耗费工时) int tname(技术人员姓名) varchar 50 2).在客户表中插入一条客户数据(赵六,男,35,13453245040) 3).将客户王五的车牌号修改为沪A88888 4).将客户李四在2023年10月10日更换轮胎的维修费用减去1000 5).查询客户张三的车辆维修记录,要求显示:维修描述.维修日期.维修费用；按维修日期升序排序 6).查询每位客户的维修次数,列名分别为:客户姓名.维修次数 7).创建一个视图v_car_repair,要求视图中显示出客户姓名.车牌号.车辆品牌.车辆型号.维修描述.维修日期.维修费用；按维修日期升序排序； 8).创建一个名称为trigger_insert的触发器,具体描述如下:当向customers客户表新增数据之前,如果客户的年龄(age)小于18岁,提示错误信息年龄必须大于等于18周岁 9).创建一个名称为p_getTotalCost的存储过程查询指定客户在该年份的维修总费用,具体描述如下:这个存储过程接收一个客户姓名(in_name),数据类型为varchar(20),一个年份(in_year),数据类型为year,作为输入参数,查询指定年份该客户的维修总费用,通过输出参数totalCost返回,类型为DECIMAL(10, 2) 2.公交管理系统数据库db_bus包含了t_driver司机表.t_bus公交表和t_attendance发车表 t_driver包含字段id(司机编号),name(司机姓名),sex(性别),age(年龄),salary(薪资)。 t_bus包含字段 id(公交编号),platenum(车牌号),linenum(线路号码)。 t_attendance包含字段id(发车编号),b_id(公交编号),d_id(司机编号),starttime(发车时间)。 1).创建一个收益表,名称为t_income,字段如下 字段名称 类型 长度 需求 id(收益编号) int 10 主键,自动增长 att_id(用户名) int 10 外键,对应t_attendance表id字段 money(金额) double 6,2 2).添加一个公交信息,公交编号10031.车牌号汽A781681.线路号码572。 3).修改司机杜震南的薪资增加200。 4).用sql语句查询年龄37,42和48岁的司机信息。 5).用sql语句查询行驶过线路号码为709的司机信息并去除重复,查询结果按照司机编号升序显示。 6).创建视图名称为v_attendance。要求显示发车信息,分别用列名:发车编号.公交车牌号.公交线路号码.司机姓名.发车时间。 7).创建一个触发器delete_driver,该触发器在t_driver表中删除司机信息之前,先删除与该司机相关的所有发车记录； 8).创建一个存储过程UpdateDriverSalary,该存储过程接收一个司机姓名(driverName)和一个薪资增加金额(increment)作为输入参数,参数类型分别为VARCHAR(255)和INT；根据司机的姓名更新指定司机的薪资； 3.人力资源管理系统数据库db_hrm包含了department部门表.employee员工表.和 salary薪资表 department部门表 包含字段 did(部门编号),dname(部门名称),description(部门介绍) employee员工表 包含字段eid(员工编号),ename(员工姓名),sex(性别),age(年龄),position(职位),did(部门编号) salary薪资表 包含字段sid(薪资编号, 自增长),eid(员工编号),money(薪资),stime(薪资日期) 上述数据库及数据表已经创建,请按要求完成以下操作 1).创建一个培训记录表training,字段如下: 字段名称 类型 长度 需求 tid(流水编号) int 主键,自增长 did(部门编号) varchar 10 不能为空,外键,与department表关联 eid(员工编号) varchar 10 不能为空 , 外键,与employee表关联 topics(培训内容)varchar 50 默认值空 costs (培训费用) int 默认值0 duration(培训天数) int 默认值0 2).修改程序员张三01月份的工资,为其加1000元工资。 3).删除业务员孙七的信息 4).查询技术部人员的姓名,年龄,职位和工资, 并分别用列名:姓名,年龄,职位和工资 5).查询01月份工资在10000元以上(含10000)的员工的部门,姓名,职位, 并分别用列名:部门,姓名,职位和工资 6).创建一个视图v_program 要求显示职位为程序员的姓名和工资, 并分别用列名:姓名,工资 7).创建一个触发器g_after_employee_insert,该触发器将在employee表上的插入操作之后被触发。触发器需要执行以下操作:为该员工在salary表中创建一个薪资记录。薪资记录将包含员工的编号(eid).一个默认的薪资金额5000,以及该记录被创建的时间(使用当前时间戳,即NOW()函数)； 8).创建一个存储过程GetEmployeeSalary,该存储过程接收一个员工ID(emp_id)作为输入参数,类型为VARCHAR(10),查询结果返回该员工的姓名(ename)和薪资(money),并分别用列名姓名和薪资来表示； 4.网上商城数据库db_online,包含商品分类表category,商品表products,用户表users, 订单表orders,订单详细表orderitems, 其中: category(cid,cname,description) 各字段的含义依次为:商品类别ID,类别名称, 类别描述。主键是cid。 products(pid,cid, pname,price, quantity,sale_qty)各字段的含义依次为: 商品ID,类别ID,商品名称,价格,库存数量,销售数量。主键商品ID, 自增长 users(username,pwd, cellphone,credit)各字段的含义依次为: 主键用户名,密码,手机,积分。 orders(oid,uid, amount,ordertime, address)各字段的含义依次为:主键订单ID,用户ID,订单金额,下单时间, 收货地址。 orderitems(item_id,oid,gid,inum)各字段的含义依次为:主键详情ID,订单ID,商品ID,购买数量。 上述数据库及数据表已经创建,请按要求完成以下操作。 1).修改用户阿美的密码,新密码为123qwe 2).创建一个购物车表cart,字段如下: 字段名称 类型 长度 需求 cart_id(购物车) int 主键,自增长 uname (用户名) char 10 不能为空, 外键,与users表关联 pid(商品ID) varchar 10 不能为空,外键,与products表关联 pnum(商品数量) int 3).要求创建一个名为 procedure_add_product的存储过程,功能是在商品表中添加商品, 要求输入参数 为以下6个,各个参数的数据类型与products表格中对应字段一致: pid:商品ID cid:类别ID pname:商品名称 price:价格 quantity:库存数量 sale_qty:销售数量 4).请使用调用语句调用procedure_add_product存储过程,在商品表中给酒类添加商品如下:法国拉菲干红葡萄酒 ,商品id是p0007 ,价格150元/瓶,库存数量300,销售数量0。张裕解百纳蛇龙珠干红葡萄酒 ,商品id是p0008, 价格120元/瓶,库存数量500,销售数量0。 5).阿美向购物车添加了如下商品,请完善数据库中cart表的信息:法国拉菲干红葡萄酒3瓶,水蜜桃5斤 6).要求在cart购物车表上创建触发器,触发器名为 trigger_product_update,当用户下订单的时候,实际上需要从cart表删除一项商品的数据,即当一项商品销售成功,购物车需要删除数据,商品表products需要减少库存数量和增加销售数量 7).创建一个视图view_sale, 查询每种商品的销售数量,并按销售数量以降序排列, 并分别用列名:商品名, 销售数量 5.宾馆客房管理数据库 db_hotelroom 包含了3个表,即房客表(guest).客房表(room).住宿表(accomodation)。 guest表 包含字段 gid(房客id),gcardno(身份证号),gname(姓名),gsex(性别),gcell (手机号) , 主键是 gid room表 包含字段 rno(房号),rtype(客房类型),rprice(房价), rtel(客房 电话 ), rstatus(客房状态 ), 主键是房号rno accomodation表 包含字段 gid(房客id), rno(房号),deposit( 押金), checkin(入住日期) checkout(退房日期), 复合主键是 (gid, rno) 上述数据库及数据表已经创建,客房状态分四种:清洁中.维修中.空闲.入住。请按要求完成以下操作。 1).创建一个客房物品表,名称为roomitem,记录客房内的额外物品,如麻将桌,小冰箱,咖啡机等。字段如下； 字段名称 类型 长度 需求 itemno (物品编号) varchar 10 不能为空,主键 rno(房号) varchar 10 默认为null itemname(物品名称) varchar 10 不能为空, itemprice(物品单价) int itemnum (物品数量) int 2).请将下列物品放入指定客房,完善客房物品表里的数据。 放入205套房的物品: 001号小冰箱1 台,价值500元；002号咖啡机1 台,价值200元 放入206套房的物品: 003号咖啡机1 台,价值200元；004号麻将桌1 台,价值200元 3).用一条SQL语句将102和203房间的状态改为空闲,并在客房表(room)中同步其状态 4).创建一个存储过程procedure_item,该存储过程接收一个物品名称(in_itemname)作为输入参数,数据类型为varchar(10),使用联合查询,查询那些配有额外物品的客房,要求显示字段房号.物品名称和客房类型；例如:输入参数咖啡机, 则可以查出所有配有咖啡机的客房。 5).2024年08月07日,一位名叫李明的男性房客前来住宿,李明的身份证号码是 420107196512316321, 手机号码 18908671167。 前台将他的信息录入系统时,系统使用算法给他产生的房客id是11。请写出SQL语句将李明的信息录入系统。 6).创建一个触发器trigger_update_room,该触发器将在accomodation表更新之后被触发。触发器需要执行以下操作:当住宿表中设置了退房日期,立即触发将对应客房的状态设置为空闲； 7).创建一个视图 view_availableroom_items , 查询所有空闲的套间中配置的物品,分别用列名:房号 , 客房类型 ,客房状态, 物品名称, 物品数量 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题一/db_maintenance.myd /* Navicat MySQL Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:6033 Source Schema : db_maintenance Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 07/11/2024 14:56:33 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for customers -- ---------------------------- DROP TABLE IF EXISTS `customers`; CREATE TABLE `customers` ( `customerID` int(11) NOT NULL AUTO_INCREMENT, `cuname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `gender` enum('男','女') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`customerID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of customers -- ---------------------------- INSERT INTO `customers` VALUES (1, '张三', '男', 30, '13842138480', 'zhangsan@qq.com'); INSERT INTO `customers` VALUES (2, '李四', '女', 25, '13457804500', 'lisi@qq.com'); INSERT INTO `customers` VALUES (3, '王五', '男', 45, '13538355400', 'wangwu@qq.com'); -- ---------------------------- -- Table structure for repairs -- ---------------------------- DROP TABLE IF EXISTS `repairs`; CREATE TABLE `repairs` ( `repairID` int(11) NOT NULL AUTO_INCREMENT, `vehicleID` int(11) NULL DEFAULT NULL, `repairDate` date NOT NULL, `description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL, `cost` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`repairID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of repairs -- ---------------------------- INSERT INTO `repairs` VALUES (1, 1, '2022-05-20', '更换空气滤清器', 150.00); INSERT INTO `repairs` VALUES (2, 1, '2023-03-10', '更换火花塞', 300.00); INSERT INTO `repairs` VALUES (3, 1, '2023-10-01', '更换刹车片', 800.00); INSERT INTO `repairs` VALUES (4, 1, '2023-11-15', '发动机保养', 1200.00); INSERT INTO `repairs` VALUES (5, 1, '2024-01-15', '四轮定位', 450.00); INSERT INTO `repairs` VALUES (6, 2, '2022-07-30', '更换冷却液', 200.00); INSERT INTO `repairs` VALUES (7, 2, '2023-08-20', '更换刹车油', 250.00); INSERT INTO `repairs` VALUES (8, 2, '2023-10-10', '更换轮胎', 2000.00); INSERT INTO `repairs` VALUES (9, 2, '2023-12-05', '更换雨刮片', 50.00); INSERT INTO `repairs` VALUES (10, 3, '2022-11-15', '更换轮胎（后轮）', 1000.00); INSERT INTO `repairs` VALUES (11, 3, '2023-06-05', '更换轮胎（前轮）', 1000.00); INSERT INTO `repairs` VALUES (12, 3, '2023-11-05', '检查并更换电池', 400.00); INSERT INTO `repairs` VALUES (13, 3, '2024-02-20', '全车检查', 800.00); -- ---------------------------- -- Table structure for vehicles -- ---------------------------- DROP TABLE IF EXISTS `vehicles`; CREATE TABLE `vehicles` ( `vehicleID` int(11) NOT NULL AUTO_INCREMENT, `plateNumber` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `brand` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `model` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `proDate` year NULL DEFAULT NULL, `customerID` int(11) NULL DEFAULT NULL, PRIMARY KEY (`vehicleID`) USING BTREE, UNIQUE INDEX `plateNumber`(`plateNumber`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of vehicles -- ---------------------------- INSERT INTO `vehicles` VALUES (1, '鄂A12345', 'Toyota', 'Camry', 2018, 1); INSERT INTO `vehicles` VALUES (2, '京C51545', 'Honda', 'Civic', 2016, 2); INSERT INTO `vehicles` VALUES (3, '沪B67890', 'BMW', 'X5', 2020, 3); INSERT INTO `vehicles` VALUES (4, '津B25487', 'xiaomi', 'su7', 2024, 4); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题三/db_hrm.myd /* Navicat MySQL Data Transfer Source Server : demo Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:3306 Source Schema : db_hrm Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 15/07/2024 13:10:10 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for department -- ---------------------------- DROP TABLE IF EXISTS `department`; CREATE TABLE `department` ( `did` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `description` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '', PRIMARY KEY (`did`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of department -- ---------------------------- INSERT INTO `department` VALUES ('01', '技术部', ''); INSERT INTO `department` VALUES ('02', '财务部', ''); INSERT INTO `department` VALUES ('03', '销售部', ''); -- ---------------------------- -- Table structure for employee -- ---------------------------- DROP TABLE IF EXISTS `employee`; CREATE TABLE `employee` ( `eid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `ename` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `position` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `did` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`eid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of employee -- ---------------------------- INSERT INTO `employee` VALUES ('001', '张一', 40, '男', '项目经理', '01'); INSERT INTO `employee` VALUES ('002', '张二', 35, '男', '架构师', '01'); INSERT INTO `employee` VALUES ('003', '张三', 30, '男', '程序员', '01'); INSERT INTO `employee` VALUES ('004', '张四', 25, '女', '程序员', '01'); INSERT INTO `employee` VALUES ('005', '李五', 25, '女', '出纳', '02'); INSERT INTO `employee` VALUES ('006', '李六', 35, '女', '会计', '02'); INSERT INTO `employee` VALUES ('007', '孙七', 25, '男', '业务员', '03'); -- ---------------------------- -- Table structure for salary -- ---------------------------- DROP TABLE IF EXISTS `salary`; CREATE TABLE `salary` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `eid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `money` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `stime` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`sid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of salary -- ---------------------------- INSERT INTO `salary` VALUES (1, '001', '10000', '2024-01-15'); INSERT INTO `salary` VALUES (2, '002', '8000', '2024-01-15'); INSERT INTO `salary` VALUES (3, '003', '15000', '2024-01-15'); INSERT INTO `salary` VALUES (4, '004', '12000', '2024-01-15'); INSERT INTO `salary` VALUES (5, '005', '9000', '2024-01-15'); INSERT INTO `salary` VALUES (6, '006', '9500', '2024-01-15'); INSERT INTO `salary` VALUES (7, '007', '20000', '2024-01-15'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题二/db_bus.myd CREATE DATABASE IF NOT EXISTS `db_bus` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `db_bus`; /* Navicat MySQL Data Transfer Source Server : 1 Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:3306 Source Schema : db_bus Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 19/06/2024 14:21:11 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_attendance -- ---------------------------- DROP TABLE IF EXISTS `t_attendance`; CREATE TABLE `t_attendance` ( `id` int(10) NOT NULL AUTO_INCREMENT, `b_id` int(10) NOT NULL, `d_id` int(10) NOT NULL, `starttime` datetime NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10101 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of t_attendance -- ---------------------------- INSERT INTO `t_attendance` VALUES (10001, 10022, 10002, '2023-08-25 09:07:31'); INSERT INTO `t_attendance` VALUES (10002, 10012, 10030, '2023-09-07 11:06:41'); INSERT INTO `t_attendance` VALUES (10003, 10014, 10010, '2023-08-31 10:41:17'); INSERT INTO `t_attendance` VALUES (10004, 10005, 10012, '2023-03-23 11:12:33'); INSERT INTO `t_attendance` VALUES (10005, 10017, 10023, '2023-08-18 14:47:37'); INSERT INTO `t_attendance` VALUES (10006, 10029, 10016, '2023-01-06 12:29:33'); INSERT INTO `t_attendance` VALUES (10007, 10008, 10004, '2023-01-24 16:52:11'); INSERT INTO `t_attendance` VALUES (10008, 10021, 10028, '2023-03-26 19:37:46'); INSERT INTO `t_attendance` VALUES (10009, 10001, 10026, '2023-09-07 13:53:22'); INSERT INTO `t_attendance` VALUES (10010, 10024, 10006, '2023-03-23 09:26:14'); INSERT INTO `t_attendance` VALUES (10011, 10013, 10018, '2023-07-05 19:57:22'); INSERT INTO `t_attendance` VALUES (10013, 10016, 10007, '2023-10-20 09:07:33'); INSERT INTO `t_attendance` VALUES (10014, 10018, 10014, '2023-02-10 15:44:22'); INSERT INTO `t_attendance` VALUES (10015, 10010, 10001, '2023-09-02 18:56:22'); INSERT INTO `t_attendance` VALUES (10016, 10003, 10029, '2023-03-07 10:18:40'); INSERT INTO `t_attendance` VALUES (10017, 10004, 10021, '2023-05-28 12:14:06'); INSERT INTO `t_attendance` VALUES (10018, 10002, 10005, '2023-03-08 12:37:45'); INSERT INTO `t_attendance` VALUES (10019, 10006, 10008, '2023-04-04 14:22:22'); INSERT INTO `t_attendance` VALUES (10020, 10030, 10024, '2023-03-05 14:50:02'); INSERT INTO `t_attendance` VALUES (10021, 10026, 10003, '2023-11-21 10:32:26'); INSERT INTO `t_attendance` VALUES (10022, 10023, 10013, '2023-11-06 13:28:33'); INSERT INTO `t_attendance` VALUES (10023, 10020, 10019, '2023-12-30 19:34:49'); INSERT INTO `t_attendance` VALUES (10024, 10027, 10015, '2023-06-25 13:35:57'); INSERT INTO `t_attendance` VALUES (10025, 10019, 10009, '2023-08-19 14:58:24'); INSERT INTO `t_attendance` VALUES (10026, 10015, 10020, '2023-01-22 15:25:28'); INSERT INTO `t_attendance` VALUES (10027, 10011, 10017, '2023-08-23 14:02:10'); INSERT INTO `t_attendance` VALUES (10028, 10007, 10025, '2023-02-25 11:05:08'); INSERT INTO `t_attendance` VALUES (10029, 10028, 10011, '2023-06-03 16:56:48'); INSERT INTO `t_attendance` VALUES (10030, 10009, 10022, '2023-08-01 11:26:41'); INSERT INTO `t_attendance` VALUES (10031, 10020, 10013, '2023-11-05 10:11:38'); INSERT INTO `t_attendance` VALUES (10032, 10018, 10015, '2023-05-21 18:35:25'); INSERT INTO `t_attendance` VALUES (10033, 10020, 10009, '2023-02-23 14:16:09'); INSERT INTO `t_attendance` VALUES (10034, 10024, 10030, '2023-08-12 13:46:36'); INSERT INTO `t_attendance` VALUES (10035, 10022, 10025, '2023-03-01 13:59:40'); INSERT INTO `t_attendance` VALUES (10036, 10027, 10022, '2023-04-23 17:15:48'); INSERT INTO `t_attendance` VALUES (10037, 10029, 10017, '2023-07-13 09:03:04'); INSERT INTO `t_attendance` VALUES (10038, 10025, 10023, '2023-02-02 19:08:01'); INSERT INTO `t_attendance` VALUES (10039, 10017, 10016, '2023-05-30 13:22:39'); INSERT INTO `t_attendance` VALUES (10040, 10029, 10021, '2023-11-21 14:04:09'); INSERT INTO `t_attendance` VALUES (10041, 10029, 10006, '2023-06-29 13:32:19'); INSERT INTO `t_attendance` VALUES (10042, 10004, 10010, '2023-01-13 19:04:23'); INSERT INTO `t_attendance` VALUES (10043, 10023, 10019, '2023-09-05 16:36:19'); INSERT INTO `t_attendance` VALUES (10044, 10022, 10023, '2023-03-08 10:34:10'); INSERT INTO `t_attendance` VALUES (10045, 10029, 10001, '2023-01-05 11:31:37'); INSERT INTO `t_attendance` VALUES (10046, 10001, 10021, '2023-12-04 09:25:37'); INSERT INTO `t_attendance` VALUES (10047, 10024, 10014, '2023-01-09 15:07:08'); INSERT INTO `t_attendance` VALUES (10048, 10001, 10003, '2023-08-14 14:52:45'); INSERT INTO `t_attendance` VALUES (10049, 10025, 10001, '2023-06-15 11:27:16'); INSERT INTO `t_attendance` VALUES (10050, 10020, 10013, '2023-08-17 16:42:19'); INSERT INTO `t_attendance` VALUES (10051, 10019, 10009, '2023-05-02 19:25:32'); INSERT INTO `t_attendance` VALUES (10052, 10024, 10004, '2023-02-16 09:40:03'); INSERT INTO `t_attendance` VALUES (10053, 10021, 10004, '2023-02-05 11:25:57'); INSERT INTO `t_attendance` VALUES (10054, 10008, 10011, '2023-08-09 16:08:41'); INSERT INTO `t_attendance` VALUES (10055, 10017, 10009, '2023-08-09 09:12:14'); INSERT INTO `t_attendance` VALUES (10056, 10023, 10027, '2023-07-01 13:49:50'); INSERT INTO `t_attendance` VALUES (10057, 10010, 10009, '2023-07-07 15:37:31'); INSERT INTO `t_attendance` VALUES (10058, 10022, 10029, '2023-01-01 17:06:08'); INSERT INTO `t_attendance` VALUES (10059, 10030, 10021, '2023-11-30 19:45:59'); INSERT INTO `t_attendance` VALUES (10060, 10025, 10025, '2023-12-10 09:49:41'); INSERT INTO `t_attendance` VALUES (10061, 10021, 10005, '2023-01-19 09:01:16'); INSERT INTO `t_attendance` VALUES (10062, 10003, 10011, '2023-11-09 18:17:00'); INSERT INTO `t_attendance` VALUES (10063, 10020, 10010, '2023-01-07 16:13:11'); INSERT INTO `t_attendance` VALUES (10064, 10018, 10013, '2023-10-26 19:16:52'); INSERT INTO `t_attendance` VALUES (10065, 10027, 10018, '2023-04-22 14:41:12'); INSERT INTO `t_attendance` VALUES (10066, 10010, 10007, '2023-06-18 19:36:42'); INSERT INTO `t_attendance` VALUES (10067, 10003, 10029, '2023-04-16 19:17:53'); INSERT INTO `t_attendance` VALUES (10068, 10007, 10018, '2023-12-01 16:02:21'); INSERT INTO `t_attendance` VALUES (10069, 10023, 10018, '2023-05-27 18:15:06'); INSERT INTO `t_attendance` VALUES (10070, 10023, 10002, '2023-02-07 09:36:44'); INSERT INTO `t_attendance` VALUES (10071, 10004, 10014, '2023-10-25 12:42:57'); INSERT INTO `t_attendance` VALUES (10072, 10030, 10029, '2023-11-02 15:48:02'); INSERT INTO `t_attendance` VALUES (10073, 10012, 10002, '2023-02-13 09:53:12'); INSERT INTO `t_attendance` VALUES (10074, 10013, 10008, '2023-06-03 13:01:57'); INSERT INTO `t_attendance` VALUES (10075, 10024, 10010, '2023-12-06 13:37:06'); INSERT INTO `t_attendance` VALUES (10076, 10001, 10030, '2023-09-25 17:11:48'); INSERT INTO `t_attendance` VALUES (10077, 10028, 10011, '2023-03-17 16:00:25'); INSERT INTO `t_attendance` VALUES (10078, 10008, 10010, '2023-03-30 16:18:53'); INSERT INTO `t_attendance` VALUES (10079, 10025, 10030, '2023-01-04 12:35:19'); INSERT INTO `t_attendance` VALUES (10080, 10017, 10021, '2023-03-06 17:14:56'); INSERT INTO `t_attendance` VALUES (10081, 10011, 10020, '2023-03-25 09:43:01'); INSERT INTO `t_attendance` VALUES (10082, 10009, 10017, '2023-04-27 18:40:03'); INSERT INTO `t_attendance` VALUES (10083, 10017, 10020, '2023-02-03 10:40:02'); INSERT INTO `t_attendance` VALUES (10084, 10019, 10018, '2023-10-18 09:47:19'); INSERT INTO `t_attendance` VALUES (10085, 10021, 10005, '2023-02-02 10:51:45'); INSERT INTO `t_attendance` VALUES (10086, 10007, 10013, '2023-12-13 09:13:00'); INSERT INTO `t_attendance` VALUES (10087, 10021, 10030, '2023-06-01 10:55:42'); INSERT INTO `t_attendance` VALUES (10088, 10022, 10022, '2023-07-25 10:45:58'); INSERT INTO `t_attendance` VALUES (10089, 10001, 10005, '2023-07-05 19:23:06'); INSERT INTO `t_attendance` VALUES (10090, 10030, 10028, '2023-07-29 14:14:38'); INSERT INTO `t_attendance` VALUES (10091, 10030, 10017, '2023-03-12 11:45:46'); INSERT INTO `t_attendance` VALUES (10092, 10002, 10015, '2023-03-05 15:44:02'); INSERT INTO `t_attendance` VALUES (10093, 10019, 10022, '2023-08-10 11:58:36'); INSERT INTO `t_attendance` VALUES (10094, 10019, 10019, '2023-07-05 09:19:00'); INSERT INTO `t_attendance` VALUES (10095, 10004, 10027, '2023-04-01 13:52:53'); INSERT INTO `t_attendance` VALUES (10096, 10011, 10005, '2023-01-14 15:09:12'); INSERT INTO `t_attendance` VALUES (10097, 10005, 10016, '2023-01-24 12:23:13'); INSERT INTO `t_attendance` VALUES (10098, 10012, 10027, '2023-10-24 16:27:54'); INSERT INTO `t_attendance` VALUES (10099, 10005, 10019, '2023-09-12 16:13:19'); INSERT INTO `t_attendance` VALUES (10100, 10007, 10028, '2023-09-02 19:04:05'); -- ---------------------------- -- Table structure for t_bus -- ---------------------------- DROP TABLE IF EXISTS `t_bus`; CREATE TABLE `t_bus` ( `id` int(10) NOT NULL AUTO_INCREMENT, `platenum` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `linenum` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10031 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of t_bus -- ---------------------------- INSERT INTO `t_bus` VALUES (10001, '汽A112955', '518'); INSERT INTO `t_bus` VALUES (10002, '汽A607691', '518'); INSERT INTO `t_bus` VALUES (10003, '汽A104488', '546'); INSERT INTO `t_bus` VALUES (10004, '汽A610201', '518'); INSERT INTO `t_bus` VALUES (10005, '汽A206621', '518'); INSERT INTO `t_bus` VALUES (10006, '汽A841495', '709'); INSERT INTO `t_bus` VALUES (10007, '汽A747618', '156'); INSERT INTO `t_bus` VALUES (10008, '汽A480086', '912'); INSERT INTO `t_bus` VALUES (10009, '汽A184551', '518'); INSERT INTO `t_bus` VALUES (10010, '汽A178252', '709'); INSERT INTO `t_bus` VALUES (10011, '汽A268525', '659'); INSERT INTO `t_bus` VALUES (10012, '汽A164580', '912'); INSERT INTO `t_bus` VALUES (10013, '汽A684949', '156'); INSERT INTO `t_bus` VALUES (10014, '汽A787640', '518'); INSERT INTO `t_bus` VALUES (10015, '汽A796090', '156'); INSERT INTO `t_bus` VALUES (10016, '汽A008904', '709'); INSERT INTO `t_bus` VALUES (10017, '汽A031829', '518'); INSERT INTO `t_bus` VALUES (10018, '汽A064919', '156'); INSERT INTO `t_bus` VALUES (10019, '汽A351322', '572'); INSERT INTO `t_bus` VALUES (10020, '汽A504115', '156'); INSERT INTO `t_bus` VALUES (10021, '汽A503898', '546'); INSERT INTO `t_bus` VALUES (10022, '汽A586531', '709'); INSERT INTO `t_bus` VALUES (10023, '汽A931569', '546'); INSERT INTO `t_bus` VALUES (10024, '汽A730504', '709'); INSERT INTO `t_bus` VALUES (10025, '汽A440456', '518'); INSERT INTO `t_bus` VALUES (10026, '汽A371647', '912'); INSERT INTO `t_bus` VALUES (10027, '汽A367102', '518'); INSERT INTO `t_bus` VALUES (10028, '汽A293829', '912'); INSERT INTO `t_bus` VALUES (10029, '汽A185285', '659'); INSERT INTO `t_bus` VALUES (10030, '汽A112046', '546'); -- ---------------------------- -- Table structure for t_driver -- ---------------------------- DROP TABLE IF EXISTS `t_driver`; CREATE TABLE `t_driver` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(3) NULL DEFAULT NULL, `salary` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10032 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of t_driver -- ---------------------------- INSERT INTO `t_driver` VALUES (10001, '崔震南', '男', 52, 7441.00); INSERT INTO `t_driver` VALUES (10002, '蔡宇宁', '男', 48, 7565.00); INSERT INTO `t_driver` VALUES (10003, '吴子异', '男', 48, 8017.00); INSERT INTO `t_driver` VALUES (10004, '雷岚', '女', 48, 8880.00); INSERT INTO `t_driver` VALUES (10005, '蒋璐', '女', 33, 7922.00); INSERT INTO `t_driver` VALUES (10006, '谢云熙', '男', 50, 7101.00); INSERT INTO `t_driver` VALUES (10007, '于安琪', '女', 49, 7130.00); INSERT INTO `t_driver` VALUES (10008, '尹睿', '男', 37, 7987.00); INSERT INTO `t_driver` VALUES (10009, '吴睿', '男', 46, 8326.00); INSERT INTO `t_driver` VALUES (10010, '曾睿', '男', 30, 7882.00); INSERT INTO `t_driver` VALUES (10011, '钟睿', '男', 33, 8263.00); INSERT INTO `t_driver` VALUES (10012, '谢嘉伦', '男', 54, 8394.00); INSERT INTO `t_driver` VALUES (10013, '杜震南', '男', 53, 7001.00); INSERT INTO `t_driver` VALUES (10014, '韩云熙', '男', 38, 7179.00); INSERT INTO `t_driver` VALUES (10015, '董子韬', '男', 49, 7271.00); INSERT INTO `t_driver` VALUES (10016, '金安琪', '女', 54, 8684.00); INSERT INTO `t_driver` VALUES (10017, '卢子异', '男', 36, 7699.00); INSERT INTO `t_driver` VALUES (10018, '叶子异', '男', 51, 7144.00); INSERT INTO `t_driver` VALUES (10019, '姚震南', '男', 36, 7136.00); INSERT INTO `t_driver` VALUES (10020, '程震南', '男', 28, 8821.00); INSERT INTO `t_driver` VALUES (10021, '于秀英', '女', 44, 8815.00); INSERT INTO `t_driver` VALUES (10022, '傅睿', '男', 50, 7667.00); INSERT INTO `t_driver` VALUES (10023, '向安琪', '女', 30, 8852.00); INSERT INTO `t_driver` VALUES (10024, '谢致远', '男', 54, 8299.00); INSERT INTO `t_driver` VALUES (10025, '姚云熙', '男', 48, 8610.00); INSERT INTO `t_driver` VALUES (10026, '姚震南', '男', 48, 8631.00); INSERT INTO `t_driver` VALUES (10027, '蒋致远', '男', 40, 7779.00); INSERT INTO `t_driver` VALUES (10028, '郑秀英', '女', 38, 7157.00); INSERT INTO `t_driver` VALUES (10029, '熊睿', '男', 42, 8965.00); INSERT INTO `t_driver` VALUES (10030, '余岚', '女', 52, 7105.00); INSERT INTO `t_driver` VALUES (10031, '李常德', '男', 34, 7200.00); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题五/db_hotelroom.myd /* Navicat MySQL Data Transfer Source Server : demo Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:3306 Source Schema : db_hotelroom Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 07/08/2024 09:31:59 */ CREATE DATABASE db_hotelroom CHARACTER SET utf8 COLLATE utf8_general_ci; USE db_hotelroom; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for accomodation -- ---------------------------- DROP TABLE IF EXISTS `accomodation`; CREATE TABLE `accomodation` ( `gid` int(11) NOT NULL COMMENT '房客id', `rno` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '房号', `deposite` float NULL DEFAULT 0 COMMENT '押金', `checkin` date NOT NULL COMMENT '入住日期', `checkout` date NULL DEFAULT NULL COMMENT '退房日期', PRIMARY KEY (`gid`, `rno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '住宿表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of accomodation -- ---------------------------- INSERT INTO accomodation VALUES (1, '100', 150.0, '2014-08-02', null), (5, '103', 200.0, '2014-08-02', null), (10, '206', 300.0, '2014-08-02', null); -- ---------------------------- -- Table structure for guest -- ---------------------------- DROP TABLE IF EXISTS `guest`; CREATE TABLE `guest` ( `gid` int(11) NOT NULL COMMENT '房客id', `gcardno` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身份证号', `gname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名', `gsex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别', `gcell` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号', PRIMARY KEY (`gid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '房客表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of guest -- ---------------------------- INSERT INTO `guest` VALUES (1, '410106196001015111', '张三', '男', '18908641111'); INSERT INTO `guest` VALUES (2, '420116196001015111', '张四', '男', '18908641112'); INSERT INTO `guest` VALUES (3, '430126196001015111', '张五', '男', '18908642113'); INSERT INTO `guest` VALUES (4, '440136196001015111', '张六', '女', '18908643114'); INSERT INTO `guest` VALUES (5, '450146196001015111', '张七', '男', '18908644115'); INSERT INTO `guest` VALUES (6, '460156196001015111', '张八', '女', '18908645116'); INSERT INTO `guest` VALUES (7, '470166196001015111', '张九', '女', '18908646117'); INSERT INTO `guest` VALUES (8, '480176196001015111', '李四', '女', '18908647118'); INSERT INTO `guest` VALUES (9, '490186196001015111', '李五', '男', '18908648119'); INSERT INTO `guest` VALUES (10, '400196196001015111', '李六', '女', '18908649110'); -- ---------------------------- -- Table structure for room -- ---------------------------- DROP TABLE IF EXISTS `room`; CREATE TABLE `room` ( `rno` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '房号', `rtype` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '客房类型', `rprice` float NOT NULL COMMENT '房价', `rtel` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客房电话', `rstatus` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '客房电话', PRIMARY KEY (`rno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '客房表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of room -- ---------------------------- INSERT INTO `room` VALUES ('100', '单人间', 150, '8100', '入住'); INSERT INTO `room` VALUES ('101', '单人间', 150, '8101', '空闲'); INSERT INTO `room` VALUES ('102', '标准间', 200, '8102', '清洁中'); INSERT INTO `room` VALUES ('103', '标准间', 200, '8103', '入住'); INSERT INTO `room` VALUES ('200', '标准间', 200, '8200', '空闲'); INSERT INTO `room` VALUES ('201', '标准间', 200, '8201', '空闲'); INSERT INTO `room` VALUES ('203', '三人间', 250, '8203', '维修中'); INSERT INTO `room` VALUES ('204', '三人间', 250, '8204', '空闲'); INSERT INTO `room` VALUES ('205', '套间', 300, '8205', '空闲'); INSERT INTO `room` VALUES ('206', '套间', 300, '8206', '入住'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（五）（素材）/题四/db_online.myd /* Navicat MySQL Data Transfer Source Server : demo Source Server Type : MySQL Source Server Version : 50743 (5.7.43-log) Source Host : localhost:3306 Source Schema : db_online Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 05/08/2024 10:08:21 */ CREATE DATABASE db_online CHARACTER SET utf8 COLLATE utf8_general_ci; USE db_online; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for category -- ---------------------------- DROP TABLE IF EXISTS `category`; CREATE TABLE `category` ( `cid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `description` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of category -- ---------------------------- INSERT INTO `category` VALUES ('001', '水果', '各种应季水果'); INSERT INTO `category` VALUES ('002', '调料', '南北调料'); INSERT INTO `category` VALUES ('003', '零食', '健康零食'); INSERT INTO `category` VALUES ('004', '酒', '啤酒、葡萄酒、白酒等'); -- ---------------------------- -- Table structure for orderitems -- ---------------------------- DROP TABLE IF EXISTS `orderitems`; CREATE TABLE `orderitems` ( `items_id` int(11) NOT NULL AUTO_INCREMENT, `oid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `pid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `inum` int(11) NULL DEFAULT NULL, PRIMARY KEY (`items_id`) USING BTREE, INDEX `oid`(`oid`) USING BTREE, INDEX `pid`(`pid`) USING BTREE, CONSTRAINT `orderitems_ibfk_1` FOREIGN KEY (`oid`) REFERENCES `orders` (`oid`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `orderitems_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `products` (`pid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of orderitems -- ---------------------------- INSERT INTO `orderitems` VALUES (1, 'd0001', 'p0005', 1); INSERT INTO `orderitems` VALUES (2, 'd0001', 'p0006', 1); -- ---------------------------- -- Table structure for orders -- ---------------------------- DROP TABLE IF EXISTS `orders`; CREATE TABLE `orders` ( `oid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `username` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `amount` double NULL DEFAULT NULL, `ordertime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`oid`) USING BTREE, INDEX `username`(`username`) USING BTREE, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of orders -- ---------------------------- INSERT INTO `orders` VALUES ('d0001', '阿康', 30, '2024-07-10 10:20:30', 'A省B市前进路130号'); -- ---------------------------- -- Table structure for products -- ---------------------------- DROP TABLE IF EXISTS `products`; CREATE TABLE `products` ( `pid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `cid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `pname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `price` double NULL DEFAULT NULL, `quantity` int(11) NULL DEFAULT NULL, `sale_qty` int(11) NULL DEFAULT NULL, PRIMARY KEY (`pid`) USING BTREE, INDEX `cid`(`cid`) USING BTREE, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `category` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of products -- ---------------------------- INSERT INTO `products` VALUES ('p0001', '001', '水蜜桃', 5.5, 100, 30); INSERT INTO `products` VALUES ('p0002', '001', '西瓜', 2.5, 500, 325); INSERT INTO `products` VALUES ('p0003', '002', '李锦记生抽', 10.5, 50, 30); INSERT INTO `products` VALUES ('p0004', '002', '食盐', 2, 100, 90); INSERT INTO `products` VALUES ('p0005', '003', '牛肉粒', 20, 100, 66); INSERT INTO `products` VALUES ('p0006', '003', '果冻', 15, 100, 6); -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `username` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `pwd` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `cellphone` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `city` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `credit` int(11) NULL DEFAULT NULL, PRIMARY KEY (`username`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC; -- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO `users` VALUES ('阿信', 'qwe123', '18912345678', '女','武汉',100); INSERT INTO `users` VALUES ('阿康', 'qwe456', '18956789012', '男','武汉',180); INSERT INTO `users` VALUES ('阿春', 'qwe178', '18923456789', '女','长沙',2); INSERT INTO `users` VALUES ('阿美', 'qwe345', '18934567890', '女','长沙',1000); INSERT INTO `users` VALUES ('阿龙', 'qwe567', '18945678901', '男','长沙',60); SET FOREIGN_KEY_CHECKS = 1; $