期末复习卷《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).创建保养记录表t_maintain_log,字段如下: log_id(记录编号) int 主键自增长, vehicleID(车辆ID) int 非空, maintain_date(保养日期) datetime, maintain_type(保养类型) varchar(50), cost(费用) decimal(10,2) 2).向customers表中插入一条客户数据:孙七,女,28,15612345678,sunqi@qq.com 3).将客户李雷的邮箱修改为lilei@163.com 4).将车辆牌号沪B12345的维修记录中所有维修费用增加200元 5).查询客户王芳的所有维修记录,显示:客户姓名、车牌号、维修日期、维修费用,按维修日期降序排序 6).查询每个技术人员(tname)的维修总工时,显示:技术人员姓名、总工时 创建视图v_vehicle_repair_history,显示:车牌号、品牌、型号、维修日期、维修描述、维修费用 7).创建触发器trigger_check_age,当向customers表插入数据时,如果年龄大于80岁,提示客户年龄不能超过80岁 8).创建存储过程proc_avg_repair_cost,输入参数为品牌(brand),输出该品牌所有车辆的平均维修费用 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_bus_maintain,字段如下: mid(保养编号) int 主键自增长, bus_id(公交编号) int 非空, maintain_date(保养日期) date, maintain_content(保养内容) text, cost(费用) decimal(8,2) 2).向t_driver表中插入一条司机数据:司机编号10086,姓名张伟,男,35,6500 3).将线路号码为572的所有公交车司机薪资增加300元 4).删除所有年龄大于55岁的司机信息 5).查询薪资大于7000元的司机姓名、年龄、薪资,按薪资降序排列 6).查询每条线路的发车次数,显示:线路号码、发车次数 7).创建视图v_bus_schedule,显示:公交编号、车牌号、线路号码、司机姓名、发车时间 8).创建触发器trigger_update_salary,当t_attendance表中插入一条发车记录时,自动将该司机的薪资增加50元(作为出勤奖励) 9).创建存储过程proc_driver_attendance_count,输入司机姓名,返回该司机在2025年度的发车总次数 3.人力资源管理系统数据库db_hrm包含了department部门表.employee员工表.和 salary薪资表 department部门表 包含字段 did(部门编号),dname(部门名称),description(部门介绍) employee员工表 包含字段eid(员工编号),ename(员工姓名),sex(性别),age(年龄),position(职位),did(部门编号) salary薪资表 包含字段sid(薪资编号, 自增长),eid(员工编号),money(薪资),stime(薪资日期) 上述数据库及数据表已经创建,请按要求完成以下操作 1).创建考勤记录表t_attendance_record,字段如下: aid(考勤编号) int 主键自增长, eid(员工编号) varchar(10) 非空, att_date(考勤日期) date, status(考勤状态) varchar(20) 默认正常 2).向employee表中插入一条员工数据:员工编号E10086,姓名周杰,男,28,工程师,部门编号D002 3).将技术部所有员工的薪资增加15% 4).删除部门编号D005及其所有员工信息 5).查询每个部门的平均年龄,显示:部门名称、平均年龄 6).查询2025年02月份薪资最高的3名员工,显示:员工姓名、薪资 7).创建视图v_employee_salary,显示:员工姓名、部门名称、职位、薪资、薪资日期 8).创建触发器trigger_salary_history,当salary表中更新薪资时,自动将旧薪资记录插入到salary_history表 9).创建存储过程proc_dept_avg_salary,输入部门名称,返回该部门在指定月份的平均薪资 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).创建商品评论表t_review,字段如下: review_id  int 主键自增长, username  varchar(20) 非空, pid  varchar(10) 非空, rating  int(评分1-5), comment  text, review_time  datetime 2).向users表中插入一条用户数据:用户名张三丰,密码123456,手机13800138000,积分100 3).将商品水蜜桃的库存数量修改为200 4).删除所有积分小于50的用户账号 5).查询每个用户的订单总金额,显示:用户名、订单总金额,按总金额降序排列 6).查询购买过法国拉菲干红葡萄酒的所有用户,显示:用户名、购买数量 7).创建视图v_user_order_summary,显示:用户名、订单编号、订单金额、下单时间、收货地址 8).创建触发器trigger_update_product_sales,当orderitems表中插入订单详情时,自动更新products表中的库存数量(减少)和销售数量(增加) 9).创建存储过程proc_user_credit,输入用户名,输出该用户的当前积分,并根据积分返回等级(金牌/银牌/铜牌) 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).创建预订记录表t_reservation,字段如下: res_id(预订编号) int 主键自增长, gid(房客ID) int 非空, rno(房号) varchar(10) 非空, res_date(预订日期) date, checkin_date(入住日期) date, status(预订状态) varchar(20) 默认已预订 2).向guest表中插入一条房客数据:房客ID 12,身份证号11010119900307663X,姓名陈丽,女,13812345678 3).将所有清洁中状态的客房改为空闲 4).删除2023年之前的所有住宿记录 5).查询每个客房类型的总收入(根据房价和入住天数计算),显示:客房类型、总收入 6).查询入住次数最多的前3名房客,显示:姓名、身份证号、入住次数 7).创建视图v_current_guests,显示当前未退房的房客信息:房客姓名、身份证号、房号、入住日期 8).创建触发器trigger_check_room_status,当向accomodation表插入入住记录时,自动检查对应客房状态是否为空闲,如果不是则拒绝插入 9).创建存储过程proc_room_occupancy_rate,输入起始日期和结束日期,返回该时间段内客房的平均入住率 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 期末复习卷《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).创建保养记录表t_maintain_log,字段如下: log_id(记录编号) int 主键自增长, vehicleID(车辆ID) int 非空, maintain_date(保养日期) datetime, maintain_type(保养类型) varchar(50), cost(费用) decimal(10,2) 2).向customers表中插入一条客户数据:孙七,女,28,15612345678,sunqi@qq.com 3).将客户李雷的邮箱修改为lilei@163.com 4).将车辆牌号沪B12345的维修记录中所有维修费用增加200元 5).查询客户王芳的所有维修记录,显示:客户姓名、车牌号、维修日期、维修费用,按维修日期降序排序 6).查询每个技术人员(tname)的维修总工时,显示:技术人员姓名、总工时 创建视图v_vehicle_repair_history,显示:车牌号、品牌、型号、维修日期、维修描述、维修费用 7).创建触发器trigger_check_age,当向customers表插入数据时,如果年龄大于80岁,提示客户年龄不能超过80岁 8).创建存储过程proc_avg_repair_cost,输入参数为品牌(brand),输出该品牌所有车辆的平均维修费用 答案: 1).CREATE TABLE t_maintain_log (log_id INT PRIMARY KEY AUTO_INCREMENT, vehicleID INT NOT NULL,maintain_date DATETIME,maintain_type VARCHAR(50), cost DECIMAL(10,2)); 2).INSERT INTO customers (cuname, gender, age, phone, email) VALUES ('孙七', '女', 28, '15612345678', 'sunqi@qq.com'); 3).UPDATE customers SET email = 'lilei@163.com' WHERE cuname = '李雷'; 4).UPDATE repairs SET cost = cost + 200 WHERE vehicleID IN (SELECT vehicleID FROM vehicles WHERE plateNumber = '沪B12345'); 5).SELECT c.cuname AS 客户姓名, v.plateNumber AS 车牌号, r.repairDate AS 维修日期, r.cost AS 维修费用FROM customers c JOIN vehicles v ON c.customerID = v.customerID JOIN repairs r ON v.vehicleID = r.vehicleID WHERE c.cuname = '王芳' ORDER BY r.repairDate DESC; 6).SELECT t.tname AS 技术人员姓名, SUM(tw.hours) AS 总工时 FROM technician t JOIN technician_work tw ON t.tid = tw.tid GROUP BY t.tname;-- 创建视图CREATE VIEW v_vehicle_repair_history ASSELECT v.plateNumber AS 车牌号, v.brand AS 品牌, v.model AS 型号, r.repairDate AS 维修日期, r.description AS 维修描述, r.cost AS 维修费用FROM vehicles vJOIN repairs r ON v.vehicleID = r.vehicleID; 7).DELIMITER //CREATE TRIGGER trigger_check_ageBEFORE INSERT ON customersFOR EACH ROWBEGIN IF NEW.age > 80 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '客户年龄不能超过80岁';END IF;END//DELIMITER ; 8).DELIMITER //CREATE PROCEDURE proc_avg_repair_cost(IN p_brand VARCHAR(50))BEGIN SELECT AVG(r.cost) AS 平均维修费用 FROM vehicles v JOIN repairs r ON v.vehicleID = r.vehicleID WHERE v.brand = p_brand;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_bus_maintain,字段如下: mid(保养编号) int 主键自增长, bus_id(公交编号) int 非空, maintain_date(保养日期) date, maintain_content(保养内容) text, cost(费用) decimal(8,2) 2).向t_driver表中插入一条司机数据:司机编号10086,姓名张伟,男,35,6500 3).将线路号码为572的所有公交车司机薪资增加300元 4).删除所有年龄大于55岁的司机信息 5).查询薪资大于7000元的司机姓名、年龄、薪资,按薪资降序排列 6).查询每条线路的发车次数,显示:线路号码、发车次数 7).创建视图v_bus_schedule,显示:公交编号、车牌号、线路号码、司机姓名、发车时间 8).创建触发器trigger_update_salary,当t_attendance表中插入一条发车记录时,自动将该司机的薪资增加50元(作为出勤奖励) 9).创建存储过程proc_driver_attendance_count,输入司机姓名,返回该司机在2025年度的发车总次数 答案: 1).CREATE TABLE t_bus_maintain ( mid INT PRIMARY KEY AUTO_INCREMENT, bus_id INT NOT NULL, maintain_date DATE, maintain_content TEXT,cost DECIMAL(8,2)); 2).INSERT INTO t_driver (id, name, sex, age, salary) VALUES (10086, '张伟', '男', 35, 6500); 3).UPDATE t_driver SET salary = salary + 300 WHERE id IN (SELECT d_id FROM t_attendance a JOIN t_bus b ON a.b_id = b.id WHERE b.linenum = '572'); 4).DELETE FROM t_driver WHERE age > 55; 5).SELECT name AS 司机姓名, age AS 年龄, salary AS 薪资FROM t_driverWHERE salary > 7000ORDER BY salary DESC; 6).SELECT b.linenum AS 线路号码, COUNT(*) AS 发车次数FROM t_attendance a JOIN t_bus b ON a.b_id = b.id GROUP BY b.linenum; 7).CREATE VIEW v_bus_schedule ASSELECT b.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; 8).DELIMITER //CREATE TRIGGER trigger_update_salaryAFTER INSERT ON t_attendanceFOR EACH ROWBEGIN UPDATE t_driver SET salary = salary + 50 WHERE id = NEW.d_id;END//DELIMITER ; 9).DELIMITER //CREATE PROCEDURE proc_driver_attendance_count(IN p_name VARCHAR(50))BEGIN SELECT COUNT(*) AS 发车总次数 FROM t_attendance a JOIN t_driver d ON a.d_id = d.id WHERE d.name = p_name AND YEAR(a.starttime) = 2025;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).创建考勤记录表t_attendance_record,字段如下: aid(考勤编号) int 主键自增长, eid(员工编号) varchar(10) 非空, att_date(考勤日期) date, status(考勤状态) varchar(20) 默认正常 2).向employee表中插入一条员工数据:员工编号E10086,姓名周杰,男,28,工程师,部门编号D002 3).将技术部所有员工的薪资增加15% 4).删除部门编号D005及其所有员工信息 5).查询每个部门的平均年龄,显示:部门名称、平均年龄 6).查询2025年02月份薪资最高的3名员工,显示:员工姓名、薪资 7).创建视图v_employee_salary,显示:员工姓名、部门名称、职位、薪资、薪资日期 8).创建触发器trigger_salary_history,当salary表中更新薪资时,自动将旧薪资记录插入到salary_history表 9).创建存储过程proc_dept_avg_salary,输入部门名称,返回该部门在指定月份的平均薪资 答案: 1).CREATE TABLE t_attendance_record (aid INT PRIMARY KEY AUTO_INCREMENT, eid VARCHAR(10) NOT NULL,att_date DATE,status VARCHAR(20) DEFAULT '正常'); 2).INSERT INTO employee (eid, ename, sex, age, position, did) VALUES ('E10086', '周杰', '男', 28, '工程师', 'D002'); 3).UPDATE salary SET money = money * 1.15 WHERE eid IN (SELECT eid FROM employee WHERE did = (SELECT did FROM department WHERE dname = '技术部')); 4).DELETE FROM employee WHERE did = 'D005';-- 再删除部门DELETE FROM department WHERE did = 'D005'; 5).SELECT d.dname AS 部门名称, AVG(e.age) AS 平均年龄FROM employee eJOIN department d ON e.did = d.didGROUP BY d.dname; 6).SELECT e.ename AS 员工姓名, s.money AS 薪资FROM salary sJOIN employee e ON s.eid = e.eidWHERE DATE_FORMAT(s.stime, '%Y-%m') = '2025-02'ORDER BY s.money DESCLIMIT 3; 7).CREATE VIEW v_employee_salary ASSELECT e.ename AS 员工姓名, d.dname AS 部门名称, e.position AS 职位,s.money AS 薪资, s.stime AS 薪资日期FROM employee eJOIN department d ON e.did = d.didJOIN salary s ON e.eid = s.eid; 8).CREATE TABLE salary_history (hid INT PRIMARY KEY AUTO_INCREMENT, eid VARCHAR(10),old_money DECIMAL(10,2),new_money DECIMAL(10,2), update_time DATETIME);DELIMITER //CREATE TRIGGER trigger_salary_historyAFTER UPDATE ON salaryFOR EACH ROWBEGIN INSERT INTO salary_history (eid, old_money, new_money, update_time) VALUES (OLD.eid, OLD.money, NEW.money, NOW());END//DELIMITER ; 9).DELIMITER //CREATE PROCEDURE proc_dept_avg_salary(IN p_dname VARCHAR(50), IN p_month VARCHAR(7))BEGIN SELECT AVG(s.money) AS 平均薪资 FROM salary s JOIN employee e ON s.eid = e.eid JOIN department d ON e.did = d.did WHERE d.dname = p_dname AND DATE_FORMAT(s.stime, '%Y-%m') = p_month;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).创建商品评论表t_review,字段如下: review_id  int 主键自增长, username  varchar(20) 非空, pid  varchar(10) 非空, rating  int(评分1-5), comment  text, review_time  datetime 2).向users表中插入一条用户数据:用户名张三丰,密码123456,手机13800138000,积分100 3).将商品水蜜桃的库存数量修改为200 4).删除所有积分小于50的用户账号 5).查询每个用户的订单总金额,显示:用户名、订单总金额,按总金额降序排列 6).查询购买过法国拉菲干红葡萄酒的所有用户,显示:用户名、购买数量 7).创建视图v_user_order_summary,显示:用户名、订单编号、订单金额、下单时间、收货地址 8).创建触发器trigger_update_product_sales,当orderitems表中插入订单详情时,自动更新products表中的库存数量(减少)和销售数量(增加) 9).创建存储过程proc_user_credit,输入用户名,输出该用户的当前积分,并根据积分返回等级(金牌/银牌/铜牌) 答案: 1).CREATE TABLE t_review (review_id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL,pid VARCHAR(10) NOT NULL,rating INT CHECK (rating BETWEEN 1 AND 5),comment TEXT,review_time DATETIME); 2).INSERT INTO users (username, pwd, cellphone, credit) VALUES ('张三丰', '123456', '13800138000', 100); 3).UPDATE products SET quantity = 200 WHERE pname = '水蜜桃'; 4).DELETE FROM users WHERE credit < 50; 5).SELECT u.username AS 用户名, SUM(o.amount) AS 订单总金额FROM users u JOIN orders o ON u.username = o.uid GROUP BY u.usernameORDER BY 订单总金额 DESC; 6).SELECT DISTINCT u.username AS 用户名, oi.inum AS 购买数量FROM users uJOIN orders o ON u.username = o.uid JOIN orderitems oi ON o.oid = oi.oid JOIN products p ON oi.gid = p.pid WHERE p.pname = '法国拉菲干红葡萄酒'; 7).CREATE VIEW v_user_order_summary ASSELECT u.username AS 用户名, o.oid AS 订单编号,o.amount AS 订单金额, o.ordertime AS 下单时间, o.address AS 收货地址FROM users u JOIN orders o ON u.username = o.uid; 8).DELIMITER //CREATE TRIGGER trigger_update_product_salesAFTER INSERT ON orderitemsFOR EACH ROWBEGIN UPDATE products SET quantity = quantity - NEW.inum,sale_qty = sale_qty + NEW.inum WHERE pid = NEW.gid;END//DELIMITER ; 9).DELIMITER //CREATE PROCEDURE proc_user_credit(IN p_username VARCHAR(50))BEGIN DECLARE user_credit INT;SELECT credit INTO user_credit FROM users WHERE username = p_username;SELECT user_credit AS 当前积分, CASE WHEN user_credit >= 1000 THEN '金牌' WHEN user_credit >= 500 THEN '银牌'ELSE '铜牌' END AS 等级;END//DELIMITER ; 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).创建预订记录表t_reservation,字段如下: res_id(预订编号) int 主键自增长, gid(房客ID) int 非空, rno(房号) varchar(10) 非空, res_date(预订日期) date, checkin_date(入住日期) date, status(预订状态) varchar(20) 默认已预订 2).向guest表中插入一条房客数据:房客ID 12,身份证号11010119900307663X,姓名陈丽,女,13812345678 3).将所有清洁中状态的客房改为空闲 4).删除2023年之前的所有住宿记录 5).查询每个客房类型的总收入(根据房价和入住天数计算),显示:客房类型、总收入 6).查询入住次数最多的前3名房客,显示:姓名、身份证号、入住次数 7).创建视图v_current_guests,显示当前未退房的房客信息:房客姓名、身份证号、房号、入住日期 8).创建触发器trigger_check_room_status,当向accomodation表插入入住记录时,自动检查对应客房状态是否为空闲,如果不是则拒绝插入 9).创建存储过程proc_room_occupancy_rate,输入起始日期和结束日期,返回该时间段内客房的平均入住率 答案: 1).CREATE TABLE t_reservation (res_id INT PRIMARY KEY AUTO_INCREMENT,gid INT NOT NULL,rno VARCHAR(10) NOT NULL,res_date DATE,checkin_date DATE,status VARCHAR(20) DEFAULT '已预订'); 2).INSERT INTO guest (gid, gcardno, gname, gsex, gcell) VALUES (12, '11010119900307663X', '陈丽', '女', '13812345678'); 3).UPDATE room SET rstatus = '空闲' WHERE rstatus = '清洁中'; 4).DELETE FROM accomodation WHERE YEAR(checkin) < 2023; 5).SELECT r.rtype AS 客房类型, SUM(DATEDIFF(a.checkout, a.checkin) * r.rprice) AS 总收入FROM accomodation aJOIN room r ON a.rno = r.rnoGROUP BY r.rtype; 6).SELECT g.gname AS 姓名, g.gcardno AS 身份证号, COUNT(*) AS 入住次数FROM accomodation aJOIN guest g ON a.gid = g.gidGROUP BY a.gidORDER BY 入住次数 DESCLIMIT 3; 7).CREATE VIEW v_current_guests ASSELECT g.gname AS 房客姓名, g.gcardno AS 身份证号, a.rno AS 房号, a.checkin AS 入住日期FROM accomodation aJOIN guest g ON a.gid = g.gidWHERE a.checkout IS NULL; 8).DELIMITER //CREATE TRIGGER trigger_check_room_statusBEFORE INSERT ON accomodationFOR EACH ROWBEGIN DECLARE room_status VARCHAR(20); SELECT rstatus INTO room_status FROM room WHERE rno = NEW.rno;IF room_status != '空闲' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '客房状态不是空闲，无法办理入住';END IF;END//DELIMITER ; 9).DELIMITER //CREATE PROCEDURE proc_room_occupancy_rate(IN start_date DATE, IN end_date DATE)BEGIN DECLARE total_days INT; DECLARE total_rooms INT;DECLARE occupied_room_days INT;SET total_days = DATEDIFF(end_date, start_date) + 1;SELECT COUNT(*) INTO total_rooms FROM room;SELECT SUM(DATEDIFF( LEAST(checkout, end_date),GREATEST(checkin, start_date)) + 1) INTO occupied_room_days FROM accomodation WHERE checkout > start_date AND checkin < end_date; SELECT (occupied_room_days / (total_rooms * total_days)) * 100 AS 平均入住率;END//DELIMITER ; 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 期末复习卷《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; $