第74卷 存储过程与存储函数《MySQL数据库基础》湖北省技能高考《计算机类考纲百套卷》

编写说明：湖北省技能高考《计算机类考纲百套卷》,依据《湖北省普通高等学校招收中等职业学校毕业生技能高考计算机类技能考试大纲》编写。每门课程均分三层训练：基础层(具象化支架).以拆解考点为微目标,紧扣考纲 掌握理解 要求编写考点训练卷；巩固层(关联性支架).强化知识交叉与场景关联,按考纲专题编专题训练卷；应用层(引导性支架).聚焦真题突破,结合知识模块与教材编写课程综合卷。 本试卷是第74卷,按《MySQL数据库基础》范围和要求编写。具体内容为:掌握存储过程与存储函数的特性、应用场景及核心区别;熟练执行存储过程的创建、修改与删除操作 湖北省《计算机类考纲百套卷》 第74卷 《MySQL数据库基础》 存储过程与存储函数 考点训练卷 考试时间80分钟 满分100分 班级 姓名 学号 成绩 素材 一、数据库操作题(本大题共10小题,每题10分,共100分). 1.线上教育数据库db_education包含了tb_student学生表、tb_course课程表和tb_video视频表 tb_student包含字段sid(学生编号),sname(学生姓名),username(用户名),password(密码),classes(班级)。 tb_course包含字段 cid(课程编号),cname(课程名称),description(描述), classhour(课时)。 tb_video包括字段vid(视频编号),cid(课程编号),title(标题),link(链接地址) (1).创建一个存储函数GetCourseVideoCount,接收课程编号c_id作为参数,返回该课程拥有的视频数量。 (2).创建一个存储过程AddVideo,接收课程编号、视频标题和链接地址作为参数,向tb_video表中插入一条新视频记录,并返回新插入视频的编号 2.酒店预订数据库db_hotel包含了t_hotel酒店表、t_room房间表、t_client客户表和t_Booking预定表 t_hotel包含字段hid(酒店编号),hname(酒店名称),location(地址),tel(联系方式) t_room包含字段 rid(房间编号),hid(酒店编号),rname(房间名称),price(价格) t_client包含字段cid(客户编号),cname(客户姓名),idcard(身份证号),tel(联系方式),integral(会员积分) t_Booking包含字段bid(预定编号),rid(房间编号),cid(会员编号),btime(预定时间) (1).创建一个存储过程SearchRooms,接收价格下限min_price和价格上限max_price作为参数,查询该价格区间内的所有房间信息(包含酒店名称).。 (2).创建一个存储函数GetClientBookingCount,接收客户编号c_id作为参数,返回该客户的预订总次数 3.公交管理系统数据库db_bus包含了t_driver司机表、t_bus公交表和t_attendance发车表 t_driver包含字段did(司机编号),dname(司机姓名),sex(性别),age(年龄),salary(薪资)。 t_bus包含字段 bid(公交编号),platenum(车牌号),linenum(线路号码)。 t_attendance包含字段aid(发车编号),bid(公交编号),did(司机编号),starttime(发车时间),endtime(收班时间)。 (1).创建一个存储过程GetDriverAttendance,接收司机编号d_id作为参数,查询该司机的所有发车记录(显示车牌号、线路号码、发车时间和收班时间).。 (2).创建一个存储函数GetDriverTotalHours,接收司机编号d_id作为参数,计算该司机所有发车记录的累计工作时长 4.健身房管理系统数据库db_gym包含了t_member会员表、t_trainer私教表和t_course 课程表 t_member包含字段mid(会员编号),mname(会员姓名),sex(性别),age(年龄),money(余额),integral(会员积分)。 t_trainer包含字段 tid(私教编号),tname(私教姓名),sex(性别),age(年龄),salary(薪资)。 t_course包含字段cid(课程编号),mid(会员编号),tid(私教编号),period (课时),price(收费价格)。 (1).创建一个存储过程AddCourse,接收会员编号、私教编号、课时数和价格作为参数,向t_course表中插入一条新课程记录。 (2).创建一个存储函数GetMemberTotalCost,接收会员编号m_id作为参数,返回该会员购买所有课程的总费用(SUM(period * price)) 5.人力资源管理系统数据库db_hrm包含了t_employee员工表、t_department部门表和t_salary薪资表 t_employee包含字段eid(员工编号),ename(员工姓名),sex(性别),age(年龄),position(职位),did(部门编号)。 t_department包含字段 did(部门编号),dname(部门名称),description(部门描述)。 t_salary包含字段sid(薪资编号),eid(员工编号),money(薪资),stime(薪资日期) (1).创建一个存储过程GetEmployeeSalary,接收员工编号e_id作为参数,查询该员工的所有薪资记录(显示薪资额和薪资日期).。 (2).创建一个存储函数GetDeptAvgSalary,接收部门编号d_id作为参数,返回该部门员工的平均薪资 6.数据库db_shop中有商品表tb_goods和客户表tb_customer tb_goods表包含字段有gno(商品编号)、gname(商品名称)、price(价格)、stock(库存)、type(商品类型) tb_customer表包含字段有cno(客户编号)、cname(客户姓名)、sex(性别)、tel(手机号)、 birthday(生日)、register_date(注册日期) (1).创建一个存储过程GetGoodsByType,接收商品类型g_type作为参数,查询该类型下所有库存大于0的商品信息。 (2).创建一个存储函数CountCustomersBySex,接收性别g_sex作为参数,返回该性别的客户人数 7.校园信息管理数据库db_school,包含了班级表tb_class和学生信息表tb_student tb_class包含字段cid(班级编号),cname(班级名称),address(班级楼层) tb_student包含字段sid(学生编号),sname(学生姓名),sex(学生性别),age(学生年龄),cid(班级编号) (1).创建一个存储过程GetClassStudents,接收班级编号c_id作为参数,查询该班级所有学生的信息。 (2).创建一个存储函数GetClassStudentCount,接收班级编号c_id作为参数,返回该班级的学生人数 8.社交网络数据库db_social包含了tb_user用户表和tb_friendship好友关系表 tb_user包含字段uid(用户编号),username(用户名),password(密码),nickname(昵称),age(年龄),sex(性别)。 tb_friendship包含字段 fsid(好友关系编号),uid(用户id),fid(好友id),createtime(添加日期)。 (1).创建一个存储过程GetUserFriends，接收用户编号u_id作为参数，查询该用户的所有好友信息（显示好友昵称和添加日期）。 (2).创建一个存储函数GetUserFriendCount，接收用户编号u_id作为参数，返回该用户的好友数量 9.运动场地租借系统数据库db_sport包含了t_site场地表、t_client客户表和t_lease 租借记录表 t_client包含字段cid(客户编号),name(客户姓名),tel(联系电话),integral(积分)。 t_site包含字段 sid(场地编号),type(场地类别),name(场地名称),price(场地价格)。 t_lease包含字段lid(租借编号),cid(客户编号),sid(场地编号),time(租借时间),duration(时长)。 (1).创建一个存储过程GetClientLease,接收客户编号c_id作为参数,查询该客户的所有租借记录(显示场地名称、租借时间和时长).。 (2).创建一个存储函数GetSiteLeaseCount,接收场地编号s_id作为参数,返回该场地的租借总次数 10.房屋租赁系统数据库db_lease包含了t_house房屋信息表、t_client客户表和t_order 订单表 t_house包含字段hid(房屋编号),address(地址),tel(户主联系电话),type(房屋类型),decoration(装修情况),area(面积),price(每月价格)。 t_client包含字段 cid(客户编号),name(客户姓名),tel(联系电话),need (需求备注)。 t_order包含字段oid(订单编号),cid(客户编号),hid(房屋编号),time(租赁时间),duration(租赁时长)。 (1).创建一个存储过程GetClientOrders,接收客户编号c_id作为参数,查询该客户的所有订单记录(显示房屋地址、租赁时间和租赁时长).。 (2).创建一个存储函数GetHouseOrderCount,接收房屋编号h_id作为参数,返回该房屋的订单总次数 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京).股份有限公司 学科网(北京).股份有限公司 学科网（北京）股份有限公司 第10题/db_lease.myd /* 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_lease Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 23/11/2023 10:28:11 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_client -- ---------------------------- DROP TABLE IF EXISTS `t_client`; CREATE TABLE `t_client` ( `cid` int(11) NULL DEFAULT NULL, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `need` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_client -- ---------------------------- INSERT INTO `t_client` VALUES (10001, '蔡致远', '19503210699', '精装'); INSERT INTO `t_client` VALUES (10002, '谢子异', '7696954760', '两室'); INSERT INTO `t_client` VALUES (10003, '田宇宁', '108144296', '毛胚'); INSERT INTO `t_client` VALUES (10004, '沈杰宏', '104971363', '两室'); INSERT INTO `t_client` VALUES (10005, '汤睿', '2068695528', '毛胚'); INSERT INTO `t_client` VALUES (10006, '薛岚', '14879352296', '毛胚'); INSERT INTO `t_client` VALUES (10007, '秦岚', '13545030053', '单间'); INSERT INTO `t_client` VALUES (10008, '郑杰宏', '2183213547', '毛胚'); INSERT INTO `t_client` VALUES (10009, '谢秀英', '14067372900', '单间'); INSERT INTO `t_client` VALUES (10010, '范致远', '18789991507', '单间'); INSERT INTO `t_client` VALUES (10011, '杨云熙', '19481632713', '两室'); INSERT INTO `t_client` VALUES (10012, '石子异', '2165515768', '单间'); INSERT INTO `t_client` VALUES (10013, '韦秀英', '2872609276', '两室'); INSERT INTO `t_client` VALUES (10014, '张致远', '76035737758', '精装'); INSERT INTO `t_client` VALUES (10015, '孙詩涵', '17793665808', '单间'); INSERT INTO `t_client` VALUES (10016, '陶嘉伦', '18568570306', '精装'); INSERT INTO `t_client` VALUES (10017, '徐晓明', '15655432389', '简装'); INSERT INTO `t_client` VALUES (10018, '何云熙', '19437875223', '简装'); INSERT INTO `t_client` VALUES (10019, '孙杰宏', '289342263', '精装'); INSERT INTO `t_client` VALUES (10020, '范震南', '13121528802', '毛胚'); -- ---------------------------- -- Table structure for t_house -- ---------------------------- DROP TABLE IF EXISTS `t_house`; CREATE TABLE `t_house` ( `hid` int(11) NOT NULL AUTO_INCREMENT, `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` int(20) NULL DEFAULT NULL, `type` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `decoration` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `area` int(5) NULL DEFAULT NULL, `price` int(5) NULL DEFAULT NULL, PRIMARY KEY (`hid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10021 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_house -- ---------------------------- INSERT INTO `t_house` VALUES (10001, '中国北京市西城区西長安街354号43室', 10146743, '三室一厅', '精装', 73, 1705); INSERT INTO `t_house` VALUES (10002, '中国东莞环区南街二巷284号28室', 18084482, '三室两厅', '精装', 102, 889); INSERT INTO `t_house` VALUES (10003, '中国北京市海淀区清河中街68号478号31栋', 15712749, '三室两厅', '精装', 96, 1000); INSERT INTO `t_house` VALUES (10004, '中国深圳龙岗区学园一巷533号41栋', 19563209, '三室一厅', '毛胚', 125, 1921); INSERT INTO `t_house` VALUES (10005, '中国中山乐丰六路892号华润大厦41室', 19823516, '三室两厅', '毛胚', 74, 1271); INSERT INTO `t_house` VALUES (10006, '中国上海市浦东新区健祥路633号22楼', 16335231, '两室一厅', '精装', 112, 1179); INSERT INTO `t_house` VALUES (10007, '中国东莞环区南街二巷658号45楼', 19297583, '三室一厅', '简装', 92, 1843); INSERT INTO `t_house` VALUES (10008, '中国中山京华商圈华夏街595号华润大厦31室', 12692049, '三室两厅', '毛胚', 93, 1212); INSERT INTO `t_house` VALUES (10009, '中国北京市房山区岳琉路334号38号楼', 18408913, '一室一厅', '精装', 115, 1298); INSERT INTO `t_house` VALUES (10010, '中国深圳罗湖区清水河一路25号34室', 14485481, '三室两厅', '精装', 112, 985); INSERT INTO `t_house` VALUES (10011, '中国东莞东泰五街26号29号楼', 13695465, '三室一厅', '精装', 85, 1084); INSERT INTO `t_house` VALUES (10012, '中国广州市白云区机场路棠苑街五巷771号华润大厦43室', 17241769, '两室一厅', '毛胚', 84, 1072); INSERT INTO `t_house` VALUES (10013, '中国中山紫马岭商圈中山五路68号8楼', 19474063, '三室一厅', '毛胚', 114, 1434); INSERT INTO `t_house` VALUES (10014, '中国北京市東城区東直門內大街509号26栋', 18408702, '三室两厅', '精装', 83, 1152); INSERT INTO `t_house` VALUES (10015, '中国广州市海珠区江南西路13号10室', 13507746, '三室一厅', '精装', 79, 1408); INSERT INTO `t_house` VALUES (10016, '中国广州市天河区天河路660号18栋', 17834698, '两室一厅', '精装', 110, 1676); INSERT INTO `t_house` VALUES (10017, '中国广州市越秀区中山二路829号11号楼', 15707608, '一室一厅', '毛胚', 113, 1301); INSERT INTO `t_house` VALUES (10018, '中国成都市成华区双庆路404号华润大厦22室', 16839914, '两室一厅', '精装', 90, 1423); INSERT INTO `t_house` VALUES (10019, '中国深圳龙岗区学园一巷217号8楼', 14488797, '一室一厅', '精装', 116, 1331); INSERT INTO `t_house` VALUES (10020, '中国成都市锦江区红星路三段947号25号楼', 10422150, '三室一厅', '精装', 74, 1594); -- ---------------------------- -- Table structure for t_order -- ---------------------------- DROP TABLE IF EXISTS `t_order`; CREATE TABLE `t_order` ( `oid` int(11) NOT NULL AUTO_INCREMENT, `cid` int(11) NOT NULL, `hid` int(11) NOT NULL, `time` date NULL DEFAULT NULL, `duration` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`oid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10021 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_order -- ---------------------------- INSERT INTO `t_order` VALUES (10001, 10010, 10008, '2020-12-19', '三个月'); INSERT INTO `t_order` VALUES (10002, 10001, 10020, '2022-01-03', '半年'); INSERT INTO `t_order` VALUES (10003, 10017, 10019, '2020-05-14', '半年'); INSERT INTO `t_order` VALUES (10004, 10005, 10004, '2000-11-07', '半年'); INSERT INTO `t_order` VALUES (10005, 10008, 10006, '2001-10-18', '一年'); INSERT INTO `t_order` VALUES (10006, 10020, 10010, '2010-07-29', '一年'); INSERT INTO `t_order` VALUES (10007, 10018, 10017, '2003-12-12', '一年'); INSERT INTO `t_order` VALUES (10008, 10004, 10013, '2001-01-28', '三个月'); INSERT INTO `t_order` VALUES (10009, 10016, 10018, '2023-03-04', '三个月'); INSERT INTO `t_order` VALUES (10010, 10013, 10005, '2012-06-30', '三个月'); INSERT INTO `t_order` VALUES (10011, 10014, 10003, '2010-07-23', '长期'); INSERT INTO `t_order` VALUES (10012, 10007, 10015, '2009-06-03', '三个月'); INSERT INTO `t_order` VALUES (10013, 10009, 10011, '2003-09-29', '长期'); INSERT INTO `t_order` VALUES (10014, 10006, 10009, '2010-05-01', '一年'); INSERT INTO `t_order` VALUES (10015, 10011, 10002, '2021-07-18', '长期'); INSERT INTO `t_order` VALUES (10016, 10015, 10012, '2019-06-19', '半年'); INSERT INTO `t_order` VALUES (10017, 10019, 10016, '2015-05-26', '半年'); INSERT INTO `t_order` VALUES (10018, 10012, 10001, '2018-11-14', '三个月'); INSERT INTO `t_order` VALUES (10019, 10002, 10014, '2016-09-03', '三个月'); INSERT INTO `t_order` VALUES (10020, 10003, 10007, '2013-07-09', '一年'); SET FOREIGN_KEY_CHECKS = 1; 第1题/db_education.myd /* 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_education Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 15/11/2023 15:59:03 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for tb_course -- ---------------------------- DROP TABLE IF EXISTS `tb_course`; CREATE TABLE `tb_course` ( `cid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `classhour` int(10) NULL DEFAULT NULL, PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of tb_course -- ---------------------------- INSERT INTO `tb_course` VALUES ('c0001', '语文', '高中语文课程', 100); INSERT INTO `tb_course` VALUES ('c0002', '数学', '高中数学课程', 100); INSERT INTO `tb_course` VALUES ('c0003', '英语', '高中英语课程', 100); INSERT INTO `tb_course` VALUES ('c0004', '计算机', '计算机课程', 100); -- ---------------------------- -- Table structure for tb_student -- ---------------------------- DROP TABLE IF EXISTS `tb_student`; CREATE TABLE `tb_student` ( `sid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `sname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `classes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`sid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of tb_student -- ---------------------------- INSERT INTO `tb_student` VALUES ('s0001', '吴大', 'abc123', '6666666', '高一1班'); INSERT INTO `tb_student` VALUES ('s0002', '刘二', 'ldq123', '8888888', '高一1班'); INSERT INTO `tb_student` VALUES ('s0003', '张三', 'zzs123', '7777777', '高一1班'); INSERT INTO `tb_student` VALUES ('s0004', '李四', 'lis123', '12345678', '高一2班'); INSERT INTO `tb_student` VALUES ('s0005', '王五', 'wan123', '21259239', '高一2班'); INSERT INTO `tb_student` VALUES ('s0006', '赵六', 'zll123', '11111111', '高二1班'); INSERT INTO `tb_student` VALUES ('s0007', '田七', 'tqq123', '22222222', '高二2班'); INSERT INTO `tb_student` VALUES ('s0008', '方八', 'fan123', '33333333', '高三1班'); INSERT INTO `tb_student` VALUES ('s0009', '陈九', 'che123', '91991919', '高三1班'); INSERT INTO `tb_student` VALUES ('s0010', '罗十', 'luo123', 'luoluo', '高三2班'); -- ---------------------------- -- Table structure for tb_video -- ---------------------------- DROP TABLE IF EXISTS `tb_video`; CREATE TABLE `tb_video` ( `vid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `cid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `title` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `link` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`vid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of tb_video -- ---------------------------- INSERT INTO `tb_video` VALUES ('v0001', 'c0001', '第一课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0002', 'c0001', '第二课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0003', 'c0001', '第三课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0004', 'c0001', '第四课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0005', 'c0002', '第一课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0006', 'c0002', '第二课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0007', 'c0002', '第三课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0008', 'c0003', '第一课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0009', 'c0003', '第二课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0010', 'c0003', '第三课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0011', 'c0004', '第一课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0012', 'c0004', '第二课', 'www.kecheng11.com'); INSERT INTO `tb_video` VALUES ('v0013', 'c0004', '第三课', 'www.kecheng11.com'); SET FOREIGN_KEY_CHECKS = 1; 第2题/db_hotel.myd /* 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_hotel Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 06/12/2023 10:26:23 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_booking -- ---------------------------- DROP TABLE IF EXISTS `t_booking`; CREATE TABLE `t_booking` ( `bid` int(10) NOT NULL AUTO_INCREMENT, `rid` int(10) NOT NULL, `cid` int(50) NULL DEFAULT NULL, `btime` datetime NULL DEFAULT NULL, PRIMARY KEY (`bid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10101 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_booking -- ---------------------------- INSERT INTO `t_booking` VALUES (10001, 10012, 10004, '2022-04-20 06:40:49'); INSERT INTO `t_booking` VALUES (10002, 10017, 10009, '2020-12-25 19:58:20'); INSERT INTO `t_booking` VALUES (10003, 10023, 10002, '2020-04-01 15:44:43'); INSERT INTO `t_booking` VALUES (10004, 10001, 10016, '2021-08-12 20:20:19'); INSERT INTO `t_booking` VALUES (10005, 10008, 10013, '2022-04-05 00:50:59'); INSERT INTO `t_booking` VALUES (10006, 10027, 10013, '2023-03-11 02:28:46'); INSERT INTO `t_booking` VALUES (10007, 10009, 10004, '2022-01-22 18:43:31'); INSERT INTO `t_booking` VALUES (10008, 10016, 10014, '2023-09-12 15:23:35'); INSERT INTO `t_booking` VALUES (10009, 10014, 10003, '2022-06-16 00:56:26'); INSERT INTO `t_booking` VALUES (10010, 10026, 10003, '2023-05-30 06:53:02'); INSERT INTO `t_booking` VALUES (10011, 10028, 10005, '2021-10-23 14:49:36'); INSERT INTO `t_booking` VALUES (10012, 10010, 10010, '2021-02-03 09:47:05'); INSERT INTO `t_booking` VALUES (10013, 10005, 10006, '2023-01-05 06:40:03'); INSERT INTO `t_booking` VALUES (10014, 10004, 10010, '2021-01-25 04:22:33'); INSERT INTO `t_booking` VALUES (10015, 10029, 10013, '2020-03-17 06:57:33'); INSERT INTO `t_booking` VALUES (10016, 10002, 10006, '2020-04-24 18:36:45'); INSERT INTO `t_booking` VALUES (10017, 10012, 10018, '2022-11-14 23:28:54'); INSERT INTO `t_booking` VALUES (10018, 10007, 10010, '2021-12-10 23:49:05'); INSERT INTO `t_booking` VALUES (10019, 10028, 10015, '2022-06-04 06:34:08'); INSERT INTO `t_booking` VALUES (10020, 10009, 10005, '2020-05-26 00:53:41'); INSERT INTO `t_booking` VALUES (10021, 10017, 10012, '2021-01-10 10:02:25'); INSERT INTO `t_booking` VALUES (10022, 10006, 10019, '2021-03-27 17:49:27'); INSERT INTO `t_booking` VALUES (10023, 10027, 10012, '2022-01-09 15:19:22'); INSERT INTO `t_booking` VALUES (10024, 10011, 10008, '2022-02-19 15:44:41'); INSERT INTO `t_booking` VALUES (10025, 10012, 10004, '2023-07-22 06:13:55'); INSERT INTO `t_booking` VALUES (10026, 10020, 10003, '2022-04-20 05:17:44'); INSERT INTO `t_booking` VALUES (10027, 10009, 10002, '2023-03-23 19:46:14'); INSERT INTO `t_booking` VALUES (10028, 10014, 10013, '2022-02-05 00:13:28'); INSERT INTO `t_booking` VALUES (10029, 10029, 10020, '2020-04-24 15:29:56'); INSERT INTO `t_booking` VALUES (10030, 10005, 10015, '2022-04-01 05:47:38'); INSERT INTO `t_booking` VALUES (10031, 10012, 10001, '2021-09-30 11:16:31'); INSERT INTO `t_booking` VALUES (10032, 10022, 10003, '2023-10-27 12:50:18'); INSERT INTO `t_booking` VALUES (10033, 10023, 10003, '2020-09-11 17:15:35'); INSERT INTO `t_booking` VALUES (10034, 10010, 10020, '2022-12-13 12:35:23'); INSERT INTO `t_booking` VALUES (10035, 10012, 10015, '2022-08-08 15:51:23'); INSERT INTO `t_booking` VALUES (10036, 10001, 10016, '2021-03-10 23:39:39'); INSERT INTO `t_booking` VALUES (10037, 10022, 10018, '2023-01-11 17:38:42'); INSERT INTO `t_booking` VALUES (10038, 10030, 10018, '2020-01-27 15:38:44'); INSERT INTO `t_booking` VALUES (10039, 10008, 10011, '2021-12-27 23:00:19'); INSERT INTO `t_booking` VALUES (10040, 10018, 10011, '2022-09-09 02:05:08'); INSERT INTO `t_booking` VALUES (10041, 10014, 10004, '2021-09-10 21:53:05'); INSERT INTO `t_booking` VALUES (10042, 10010, 10007, '2023-08-11 04:46:32'); INSERT INTO `t_booking` VALUES (10043, 10004, 10015, '2020-10-22 20:19:11'); INSERT INTO `t_booking` VALUES (10044, 10013, 10008, '2022-09-06 23:13:12'); INSERT INTO `t_booking` VALUES (10045, 10023, 10012, '2021-04-02 08:58:00'); INSERT INTO `t_booking` VALUES (10046, 10027, 10004, '2022-03-10 03:27:28'); INSERT INTO `t_booking` VALUES (10047, 10030, 10006, '2023-07-25 02:07:54'); INSERT INTO `t_booking` VALUES (10048, 10013, 10017, '2021-01-13 07:57:39'); INSERT INTO `t_booking` VALUES (10049, 10018, 10018, '2020-05-03 05:57:14'); INSERT INTO `t_booking` VALUES (10050, 10003, 10019, '2021-12-10 04:23:41'); INSERT INTO `t_booking` VALUES (10051, 10021, 10007, '2022-07-21 22:39:18'); INSERT INTO `t_booking` VALUES (10052, 10020, 10016, '2022-03-07 02:16:20'); INSERT INTO `t_booking` VALUES (10053, 10011, 10002, '2021-04-13 11:08:49'); INSERT INTO `t_booking` VALUES (10054, 10020, 10017, '2020-10-30 00:06:40'); INSERT INTO `t_booking` VALUES (10055, 10002, 10001, '2022-02-06 11:58:59'); INSERT INTO `t_booking` VALUES (10056, 10008, 10004, '2023-04-24 08:32:12'); INSERT INTO `t_booking` VALUES (10057, 10026, 10004, '2023-07-12 15:55:46'); INSERT INTO `t_booking` VALUES (10058, 10007, 10008, '2022-01-14 08:38:41'); INSERT INTO `t_booking` VALUES (10059, 10001, 10004, '2021-10-14 11:10:41'); INSERT INTO `t_booking` VALUES (10060, 10030, 10001, '2020-03-21 22:03:53'); INSERT INTO `t_booking` VALUES (10061, 10025, 10010, '2022-03-15 10:39:54'); INSERT INTO `t_booking` VALUES (10062, 10020, 10005, '2022-12-05 16:06:30'); INSERT INTO `t_booking` VALUES (10063, 10014, 10016, '2023-04-28 23:03:04'); INSERT INTO `t_booking` VALUES (10064, 10004, 10012, '2020-05-29 16:44:39'); INSERT INTO `t_booking` VALUES (10065, 10030, 10014, '2022-12-29 15:23:34'); INSERT INTO `t_booking` VALUES (10066, 10020, 10012, '2021-07-26 03:07:31'); INSERT INTO `t_booking` VALUES (10067, 10029, 10001, '2023-04-29 13:36:08'); INSERT INTO `t_booking` VALUES (10068, 10029, 10006, '2021-07-10 01:21:00'); INSERT INTO `t_booking` VALUES (10069, 10010, 10018, '2021-08-17 05:40:06'); INSERT INTO `t_booking` VALUES (10070, 10006, 10008, '2023-09-10 21:12:06'); INSERT INTO `t_booking` VALUES (10071, 10019, 10017, '2020-12-30 22:48:21'); INSERT INTO `t_booking` VALUES (10072, 10003, 10005, '2021-10-17 23:19:03'); INSERT INTO `t_booking` VALUES (10073, 10017, 10015, '2023-08-13 12:54:31'); INSERT INTO `t_booking` VALUES (10074, 10010, 10008, '2021-07-15 12:48:19'); INSERT INTO `t_booking` VALUES (10075, 10009, 10002, '2022-09-27 10:34:08'); INSERT INTO `t_booking` VALUES (10076, 10020, 10001, '2023-10-02 22:47:30'); INSERT INTO `t_booking` VALUES (10077, 10025, 10020, '2022-09-30 08:21:32'); INSERT INTO `t_booking` VALUES (10078, 10021, 10002, '2020-11-06 10:29:57'); INSERT INTO `t_booking` VALUES (10079, 10016, 10002, '2021-01-15 22:55:00'); INSERT INTO `t_booking` VALUES (10080, 10019, 10009, '2021-01-08 16:14:36'); INSERT INTO `t_booking` VALUES (10081, 10017, 10018, '2023-06-08 16:46:53'); INSERT INTO `t_booking` VALUES (10082, 10025, 10011, '2023-02-27 10:55:55'); INSERT INTO `t_booking` VALUES (10083, 10011, 10015, '2020-04-18 23:42:55'); INSERT INTO `t_booking` VALUES (10084, 10004, 10003, '2022-01-09 00:40:28'); INSERT INTO `t_booking` VALUES (10085, 10003, 10011, '2020-11-17 18:26:22'); INSERT INTO `t_booking` VALUES (10086, 10001, 10013, '2022-06-11 22:06:21'); INSERT INTO `t_booking` VALUES (10087, 10003, 10007, '2022-05-11 08:16:50'); INSERT INTO `t_booking` VALUES (10088, 10009, 10001, '2021-07-26 16:12:23'); INSERT INTO `t_booking` VALUES (10089, 10022, 10009, '2020-01-10 17:53:16'); INSERT INTO `t_booking` VALUES (10090, 10019, 10011, '2021-01-13 15:12:37'); INSERT INTO `t_booking` VALUES (10091, 10016, 10012, '2020-01-05 15:01:51'); INSERT INTO `t_booking` VALUES (10092, 10019, 10012, '2021-07-17 07:34:48'); INSERT INTO `t_booking` VALUES (10093, 10003, 10019, '2022-11-09 17:27:16'); INSERT INTO `t_booking` VALUES (10094, 10012, 10015, '2020-12-30 15:27:07'); INSERT INTO `t_booking` VALUES (10095, 10006, 10011, '2021-08-19 03:06:18'); INSERT INTO `t_booking` VALUES (10096, 10016, 10001, '2020-01-03 17:46:09'); INSERT INTO `t_booking` VALUES (10097, 10013, 10013, '2022-05-30 13:58:51'); INSERT INTO `t_booking` VALUES (10098, 10023, 10016, '2022-08-08 13:49:57'); INSERT INTO `t_booking` VALUES (10099, 10027, 10009, '2022-10-11 08:52:49'); INSERT INTO `t_booking` VALUES (10100, 10025, 10002, '2022-01-11 12:59:14'); -- ---------------------------- -- Table structure for t_client -- ---------------------------- DROP TABLE IF EXISTS `t_client`; CREATE TABLE `t_client` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `idcard` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `integral` int(10) NULL DEFAULT NULL, PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10021 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_client -- ---------------------------- INSERT INTO `t_client` VALUES (10001, '高晓明', '220416018020156237', '10-3487-0240', 6849); INSERT INTO `t_client` VALUES (10002, '丁秀英', '120139219383861722', '755-326-9317', 868); INSERT INTO `t_client` VALUES (10003, '姚致远', '813608313703608154', '760-894-7559', 269); INSERT INTO `t_client` VALUES (10004, '邵致远', '077073219007392324', '21-3135-3876', 3547); INSERT INTO `t_client` VALUES (10005, '朱安琪', '956859116269772335', '139-8217-1057', 5618); INSERT INTO `t_client` VALUES (10006, '吕子韬', '410367571693790681', '21-559-2317', 3454); INSERT INTO `t_client` VALUES (10007, '金璐', '365995992969536218', '28-4117-9355', 3125); INSERT INTO `t_client` VALUES (10008, '史詩涵', '745617853395864631', '769-9412-7899', 2342); INSERT INTO `t_client` VALUES (10009, '傅震南', '048808250969656123', '133-4735-8147', 6322); INSERT INTO `t_client` VALUES (10010, '杨安琪', '421306350748447077', '178-5318-1276', 5502); INSERT INTO `t_client` VALUES (10011, '姚宇宁', '922107807524199050', '10-1954-4512', 3461); INSERT INTO `t_client` VALUES (10012, '钟詩涵', '361100327772438632', '162-0437-3373', 2910); INSERT INTO `t_client` VALUES (10013, '刘子韬', '359483343792579656', '20-695-0556', 7401); INSERT INTO `t_client` VALUES (10014, '熊晓明', '287355904929330230', '10-508-2430', 3726); INSERT INTO `t_client` VALUES (10015, '林杰宏', '903663840114584468', '10-544-9685', 4549); INSERT INTO `t_client` VALUES (10016, '严杰宏', '465686241399543593', '162-1246-0410', 771); INSERT INTO `t_client` VALUES (10017, '何晓明', '620317163610218418', '169-5363-6951', 6794); INSERT INTO `t_client` VALUES (10018, '黄云熙', '540763562777864268', '755-653-6484', 5836); INSERT INTO `t_client` VALUES (10019, '郝宇宁', '315450867940104124', '148-5602-3286', 1304); INSERT INTO `t_client` VALUES (10020, '金嘉伦', '377713803532578908', '187-9991-7315', 4259); -- ---------------------------- -- Table structure for t_hotel -- ---------------------------- DROP TABLE IF EXISTS `t_hotel`; CREATE TABLE `t_hotel` ( `hid` int(10) NOT NULL AUTO_INCREMENT, `hname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `location` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`hid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10011 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_hotel -- ---------------------------- INSERT INTO `t_hotel` VALUES (10001, '香蕉酒店', '锦江区人民南路四段44号', '28-367-1903'); INSERT INTO `t_hotel` VALUES (10002, '菠萝酒店', '越秀区中山二路397号', '20-2652-1333'); INSERT INTO `t_hotel` VALUES (10003, '荔枝酒店', '白云区小坪东路671号', '20-1899-0416'); INSERT INTO `t_hotel` VALUES (10004, '西瓜酒店', '锦江区红星路三段109号', '194-4179-8952'); INSERT INTO `t_hotel` VALUES (10005, '草莓酒店', '西城区复兴门内大街149号', '10-7508-0015'); INSERT INTO `t_hotel` VALUES (10006, '芒果酒店', '龙岗区学园一巷805号', '190-6027-4900'); INSERT INTO `t_hotel` VALUES (10007, '榴莲酒店', '徐汇区虹桥路819号', '144-6666-6586'); INSERT INTO `t_hotel` VALUES (10008, '柠檬酒店', '黄浦区淮海中路115号', '21-332-3251'); INSERT INTO `t_hotel` VALUES (10009, '香橙酒店', '延庆区028县道931号', '168-4439-4517'); INSERT INTO `t_hotel` VALUES (10010, '甘蔗酒店', '延庆区028县道750号', '158-4297-7695'); -- ---------------------------- -- Table structure for t_room -- ---------------------------- DROP TABLE IF EXISTS `t_room`; CREATE TABLE `t_room` ( `rid` int(10) NOT NULL AUTO_INCREMENT, `hid` int(10) NOT NULL, `rname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `price` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`rid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10031 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_room -- ---------------------------- INSERT INTO `t_room` VALUES (10001, 10005, '大床房', 836.00); INSERT INTO `t_room` VALUES (10002, 10007, '大床房', 888.00); INSERT INTO `t_room` VALUES (10003, 10007, '家庭间', 736.00); INSERT INTO `t_room` VALUES (10004, 10006, '大床房', 934.00); INSERT INTO `t_room` VALUES (10005, 10004, '家庭间', 712.00); INSERT INTO `t_room` VALUES (10006, 10009, '家庭间', 518.00); INSERT INTO `t_room` VALUES (10007, 10003, '家庭间', 959.00); INSERT INTO `t_room` VALUES (10008, 10005, '家庭间', 884.00); INSERT INTO `t_room` VALUES (10009, 10004, '家庭间', 670.00); INSERT INTO `t_room` VALUES (10010, 10004, '家庭间', 872.00); INSERT INTO `t_room` VALUES (10011, 10007, '标准间', 552.00); INSERT INTO `t_room` VALUES (10012, 10002, '家庭间', 819.00); INSERT INTO `t_room` VALUES (10013, 10005, '标准间', 844.00); INSERT INTO `t_room` VALUES (10014, 10010, '家庭间', 573.00); INSERT INTO `t_room` VALUES (10015, 10008, '标准间', 807.00); INSERT INTO `t_room` VALUES (10016, 10010, '家庭间', 968.00); INSERT INTO `t_room` VALUES (10017, 10001, '家庭间', 543.00); INSERT INTO `t_room` VALUES (10018, 10006, '大床房', 972.00); INSERT INTO `t_room` VALUES (10019, 10006, '标准间', 883.00); INSERT INTO `t_room` VALUES (10020, 10010, '标准间', 727.00); INSERT INTO `t_room` VALUES (10021, 10003, '大床房', 772.00); INSERT INTO `t_room` VALUES (10022, 10009, '家庭间', 605.00); INSERT INTO `t_room` VALUES (10023, 10003, '家庭间', 999.00); INSERT INTO `t_room` VALUES (10024, 10009, '家庭间', 653.00); INSERT INTO `t_room` VALUES (10025, 10008, '家庭间', 790.00); INSERT INTO `t_room` VALUES (10026, 10008, '标准间', 711.00); INSERT INTO `t_room` VALUES (10027, 10002, '大床房', 999.00); INSERT INTO `t_room` VALUES (10028, 10001, '大床房', 601.00); INSERT INTO `t_room` VALUES (10029, 10002, '标准间', 600.00); INSERT INTO `t_room` VALUES (10030, 10001, '大床房', 850.00); SET FOREIGN_KEY_CHECKS = 1; 第3题/db_bus.myd /* 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: 11/12/2023 11:25:12 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_attendance -- ---------------------------- DROP TABLE IF EXISTS `t_attendance`; CREATE TABLE `t_attendance` ( `aid` int(10) NOT NULL AUTO_INCREMENT, `bid` int(10) NOT NULL, `did` int(10) NOT NULL, `starttime` datetime NULL DEFAULT NULL, `endtime` datetime NULL DEFAULT NULL, PRIMARY KEY (`aid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10011 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_attendance -- ---------------------------- INSERT INTO `t_attendance` VALUES (10001, 10001, 10002, '2023-11-08 08:00:33', '2023-11-08 10:23:48'); INSERT INTO `t_attendance` VALUES (10002, 10003, 10005, '2023-11-08 13:00:33', '2023-11-08 15:23:48'); INSERT INTO `t_attendance` VALUES (10003, 10004, 10001, '2023-11-08 09:00:33', '2023-11-08 11:23:48'); INSERT INTO `t_attendance` VALUES (10004, 10006, 10004, '2023-11-08 07:00:33', '2023-11-08 09:23:48'); INSERT INTO `t_attendance` VALUES (10005, 10001, 10009, '2023-11-08 15:00:33', '2023-11-08 17:23:48'); INSERT INTO `t_attendance` VALUES (10006, 10003, 10009, '2023-11-09 08:00:33', '2023-11-09 10:23:48'); INSERT INTO `t_attendance` VALUES (10007, 10008, 10009, '2023-11-10 21:00:33', '2023-11-10 22:23:48'); INSERT INTO `t_attendance` VALUES (10008, 10002, 10005, '2023-11-08 10:00:33', '2023-11-08 12:23:48'); INSERT INTO `t_attendance` VALUES (10009, 10007, 10007, '2023-11-08 17:00:33', '2023-11-08 19:23:48'); INSERT INTO `t_attendance` VALUES (10010, 10009, 10002, '2023-11-08 18:00:33', '2023-11-08 20:00:33'); -- ---------------------------- -- Table structure for t_bus -- ---------------------------- DROP TABLE IF EXISTS `t_bus`; CREATE TABLE `t_bus` ( `bid` 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 (`bid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10011 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_bus -- ---------------------------- INSERT INTO `t_bus` VALUES (10001, '汽A00001', '518'); INSERT INTO `t_bus` VALUES (10002, '汽A00002', '659'); INSERT INTO `t_bus` VALUES (10003, '汽A00003', '485'); INSERT INTO `t_bus` VALUES (10004, '汽A00004', '518'); INSERT INTO `t_bus` VALUES (10005, '汽A00005', '485'); INSERT INTO `t_bus` VALUES (10006, '汽A00006', '945'); INSERT INTO `t_bus` VALUES (10007, '汽A00007', '568'); INSERT INTO `t_bus` VALUES (10008, '汽A00008', '912'); INSERT INTO `t_bus` VALUES (10009, '汽A00009', '912'); INSERT INTO `t_bus` VALUES (10010, '汽A00010', '776'); -- ---------------------------- -- Table structure for t_driver -- ---------------------------- DROP TABLE IF EXISTS `t_driver`; CREATE TABLE `t_driver` ( `did` int(10) NOT NULL, `dname` 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 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_driver -- ---------------------------- INSERT INTO `t_driver` VALUES (10001, '赵芳', '女', 37, 8000.00); INSERT INTO `t_driver` VALUES (10002, '刘龙', '男', 45, 8300.00); INSERT INTO `t_driver` VALUES (10003, '刘凤', '女', 45, 8300.00); INSERT INTO `t_driver` VALUES (10004, '王树', '男', 52, 7900.00); INSERT INTO `t_driver` VALUES (10005, '陆山', '男', 39, 7500.00); INSERT INTO `t_driver` VALUES (10006, '吴莉', '女', 35, 7000.00); INSERT INTO `t_driver` VALUES (10007, '张云', '女', 54, 8000.00); INSERT INTO `t_driver` VALUES (10008, '方星', '男', 49, 8600.00); INSERT INTO `t_driver` VALUES (10009, '何国', '男', 32, 6700.00); INSERT INTO `t_driver` VALUES (10010, '吴天', '男', 33, 6500.00); SET FOREIGN_KEY_CHECKS = 1; 第4题/db_gym.myd /* 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_gym Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 13/12/2023 09:35:50 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_course -- ---------------------------- DROP TABLE IF EXISTS `t_course`; CREATE TABLE `t_course` ( `cid` int(10) NOT NULL AUTO_INCREMENT, `mid` int(10) NULL DEFAULT NULL, `tid` int(10) NULL DEFAULT NULL, `period` int(3) NULL DEFAULT NULL, `price` decimal(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10051 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_course -- ---------------------------- INSERT INTO `t_course` VALUES (10001, 10010, 10006, 36, 9001.00); INSERT INTO `t_course` VALUES (10002, 10001, 10008, 36, 9385.00); INSERT INTO `t_course` VALUES (10003, 10008, 10005, 12, 9389.00); INSERT INTO `t_course` VALUES (10004, 10009, 10002, 24, 6293.00); INSERT INTO `t_course` VALUES (10005, 10015, 10010, 36, 3934.00); INSERT INTO `t_course` VALUES (10006, 10007, 10007, 36, 5963.00); INSERT INTO `t_course` VALUES (10007, 10012, 10001, 36, 6047.00); INSERT INTO `t_course` VALUES (10008, 10002, 10003, 12, 6498.00); INSERT INTO `t_course` VALUES (10009, 10005, 10009, 24, 3793.00); INSERT INTO `t_course` VALUES (10010, 10004, 10004, 12, 4773.00); INSERT INTO `t_course` VALUES (10011, 10006, 10003, 12, 4099.00); INSERT INTO `t_course` VALUES (10012, 10014, 10009, 24, 8450.00); INSERT INTO `t_course` VALUES (10013, 10011, 10005, 12, 3540.00); INSERT INTO `t_course` VALUES (10014, 10016, 10004, 36, 7985.00); INSERT INTO `t_course` VALUES (10015, 10019, 10007, 12, 9509.00); INSERT INTO `t_course` VALUES (10016, 10003, 10006, 36, 3042.00); INSERT INTO `t_course` VALUES (10017, 10017, 10010, 36, 7962.00); INSERT INTO `t_course` VALUES (10018, 10020, 10008, 24, 7994.00); INSERT INTO `t_course` VALUES (10019, 10018, 10003, 36, 3809.00); INSERT INTO `t_course` VALUES (10020, 10013, 10001, 24, 4276.00); INSERT INTO `t_course` VALUES (10021, 10017, 10005, 24, 7914.00); INSERT INTO `t_course` VALUES (10022, 10004, 10006, 24, 8233.00); INSERT INTO `t_course` VALUES (10023, 10015, 10009, 36, 5175.00); INSERT INTO `t_course` VALUES (10024, 10018, 10003, 36, 8613.00); INSERT INTO `t_course` VALUES (10025, 10012, 10004, 36, 5179.00); INSERT INTO `t_course` VALUES (10026, 10009, 10001, 36, 7533.00); INSERT INTO `t_course` VALUES (10027, 10006, 10003, 24, 6311.00); INSERT INTO `t_course` VALUES (10028, 10009, 10001, 24, 8665.00); INSERT INTO `t_course` VALUES (10029, 10010, 10002, 12, 5972.00); INSERT INTO `t_course` VALUES (10030, 10002, 10001, 36, 9264.00); INSERT INTO `t_course` VALUES (10031, 10013, 10005, 36, 6974.00); INSERT INTO `t_course` VALUES (10032, 10006, 10004, 24, 6840.00); INSERT INTO `t_course` VALUES (10033, 10016, 10008, 24, 4333.00); INSERT INTO `t_course` VALUES (10034, 10002, 10004, 12, 9357.00); INSERT INTO `t_course` VALUES (10035, 10005, 10005, 24, 9106.00); INSERT INTO `t_course` VALUES (10036, 10010, 10007, 36, 3728.00); INSERT INTO `t_course` VALUES (10037, 10015, 10009, 24, 9403.00); INSERT INTO `t_course` VALUES (10038, 10003, 10007, 36, 4411.00); INSERT INTO `t_course` VALUES (10039, 10014, 10010, 36, 5053.00); INSERT INTO `t_course` VALUES (10040, 10005, 10008, 12, 8198.00); INSERT INTO `t_course` VALUES (10041, 10001, 10006, 24, 8156.00); INSERT INTO `t_course` VALUES (10042, 10003, 10010, 36, 6448.00); INSERT INTO `t_course` VALUES (10043, 10008, 10008, 24, 7485.00); INSERT INTO `t_course` VALUES (10044, 10016, 10006, 12, 3540.00); INSERT INTO `t_course` VALUES (10045, 10012, 10010, 24, 5789.00); INSERT INTO `t_course` VALUES (10046, 10017, 10009, 36, 4889.00); INSERT INTO `t_course` VALUES (10047, 10014, 10007, 36, 9069.00); INSERT INTO `t_course` VALUES (10048, 10020, 10002, 24, 3779.00); INSERT INTO `t_course` VALUES (10049, 10004, 10002, 24, 4498.00); INSERT INTO `t_course` VALUES (10050, 10008, 10002, 24, 5511.00); -- ---------------------------- -- Table structure for t_member -- ---------------------------- DROP TABLE IF EXISTS `t_member`; CREATE TABLE `t_member` ( `mid` int(10) NOT NULL AUTO_INCREMENT, `mname` 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, `money` decimal(10, 2) NULL DEFAULT 0.00, `integral` int(10) NULL DEFAULT 0, PRIMARY KEY (`mid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10021 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_member -- ---------------------------- INSERT INTO `t_member` VALUES (10001, '袁詩涵', '女', 26, 8675.00, 4375); INSERT INTO `t_member` VALUES (10002, '薛安琪', '女', 20, 6622.00, 6020); INSERT INTO `t_member` VALUES (10003, '孔嘉伦', '男', 42, 8055.00, 9039); INSERT INTO `t_member` VALUES (10004, '方秀英', '女', 48, 6599.00, 15762); INSERT INTO `t_member` VALUES (10005, '顾詩涵', '女', 40, 2151.00, 5379); INSERT INTO `t_member` VALUES (10006, '曹云熙', '男', 25, 5666.00, 6715); INSERT INTO `t_member` VALUES (10007, '石致远', '男', 48, 2464.00, 18448); INSERT INTO `t_member` VALUES (10008, '常安琪', '女', 41, 9923.00, 4427); INSERT INTO `t_member` VALUES (10009, '顾杰宏', '男', 36, 1991.00, 3734); INSERT INTO `t_member` VALUES (10010, '卢安琪', '女', 23, 9962.00, 13997); INSERT INTO `t_member` VALUES (10011, '尹震南', '男', 23, 328.00, 1221); INSERT INTO `t_member` VALUES (10012, '石子韬', '男', 24, 1450.00, 17348); INSERT INTO `t_member` VALUES (10013, '蒋璐', '女', 19, 3468.00, 9734); INSERT INTO `t_member` VALUES (10014, '蒋秀英', '女', 43, 2061.00, 10609); INSERT INTO `t_member` VALUES (10015, '孙宇宁', '男', 46, 7092.00, 7715); INSERT INTO `t_member` VALUES (10016, '韩岚', '女', 48, 2745.00, 960); INSERT INTO `t_member` VALUES (10017, '贺致远', '男', 45, 8080.00, 10547); INSERT INTO `t_member` VALUES (10018, '吕璐', '女', 41, 9519.00, 12192); INSERT INTO `t_member` VALUES (10019, '邱致远', '男', 35, 7532.00, 6735); INSERT INTO `t_member` VALUES (10020, '石嘉伦', '男', 20, 6793.00, 8850); -- ---------------------------- -- Table structure for t_trainer -- ---------------------------- DROP TABLE IF EXISTS `t_trainer`; CREATE TABLE `t_trainer` ( `tid` int(10) NOT NULL AUTO_INCREMENT, `tname` 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 (`tid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10011 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_trainer -- ---------------------------- INSERT INTO `t_trainer` VALUES (10001, '郭嘉伦', '男', 29, 11306.00); INSERT INTO `t_trainer` VALUES (10002, '常睿', '男', 39, 10837.00); INSERT INTO `t_trainer` VALUES (10003, '许秀英', '女', 28, 9394.00); INSERT INTO `t_trainer` VALUES (10004, '杜詩涵', '女', 33, 8998.00); INSERT INTO `t_trainer` VALUES (10005, '邹詩涵', '女', 26, 11477.00); INSERT INTO `t_trainer` VALUES (10006, '熊晓明', '男', 32, 8276.00); INSERT INTO `t_trainer` VALUES (10007, '卢詩涵', '女', 29, 8430.00); INSERT INTO `t_trainer` VALUES (10008, '钟致远', '男', 38, 9871.00); INSERT INTO `t_trainer` VALUES (10009, '武晓明', '男', 26, 10653.00); INSERT INTO `t_trainer` VALUES (10010, '武云熙', '男', 35, 10069.00); SET FOREIGN_KEY_CHECKS = 1; 第5题/db_hrm.myd /* 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_hrm Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 07/12/2023 14:54:15 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_department -- ---------------------------- DROP TABLE IF EXISTS `t_department`; CREATE TABLE `t_department` ( `did` int(10) NOT NULL AUTO_INCREMENT, `dname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`did`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10005 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_department -- ---------------------------- INSERT INTO `t_department` VALUES (10001, '财务部', '财务部是指在本机构一定的整体目标下，关于资产的购置（投资），资本的融通（筹资）和经营中现金流量（营运资金），以及利润分配的管理的部门。'); INSERT INTO `t_department` VALUES (10002, '行政部', '行政部门是组织或企业中负责管理和支持日常行政事务的部门。'); INSERT INTO `t_department` VALUES (10003, '销售部', '销售部门是组织内负责销售和市场推广活动的部门。其主要职责是促进产品或服务的销售，实现组织的销售目标和利润增长。销售部门通常与市场部门密切协作，共同制定销售策略和营销计划。'); INSERT INTO `t_department` VALUES (10004, '技术部', '技术部门是一个组织内负责技术开发、技术支持和技术管理的部门。其主要职责是提供技术解决方案，支持组织的技术需求，并确保技术的有效运作和发展。'); -- ---------------------------- -- Table structure for t_employee -- ---------------------------- DROP TABLE IF EXISTS `t_employee`; CREATE TABLE `t_employee` ( `eid` int(10) NOT NULL AUTO_INCREMENT, `ename` varchar(30) 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(5) NULL DEFAULT NULL, `position` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `did` int(10) NULL DEFAULT NULL, PRIMARY KEY (`eid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10014 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_employee -- ---------------------------- INSERT INTO `t_employee` VALUES (10001, '王经理', '男', 42, '经理', 10004); INSERT INTO `t_employee` VALUES (10002, '赵经理', '女', 37, '经理', 10002); INSERT INTO `t_employee` VALUES (10003, '吴经理', '男', 33, '经理', 10003); INSERT INTO `t_employee` VALUES (10004, '方经理', '女', 39, '经理', 10001); INSERT INTO `t_employee` VALUES (10005, '张开发', '男', 27, '开发工程师', 10004); INSERT INTO `t_employee` VALUES (10006, '田前端', '女', 25, 'web前端工程师', 10004); INSERT INTO `t_employee` VALUES (10007, '毛销售', '女', 22, '销售实习生', 10003); INSERT INTO `t_employee` VALUES (10008, '李销冠', '男', 29, '销售', 10003); INSERT INTO `t_employee` VALUES (10009, '钱台', '女', 23, '前台', 10002); INSERT INTO `t_employee` VALUES (10010, '崔人事', '女', 31, '人事', 10003); INSERT INTO `t_employee` VALUES (10011, '曾出纳', '女', 28, '出纳', 10001); INSERT INTO `t_employee` VALUES (10012, '朱财务', '女', 32, '财务', 10001); INSERT INTO `t_employee` VALUES (10013, '刘红', '女', 22, '实习生', 10002); -- ---------------------------- -- Table structure for t_salary -- ---------------------------- DROP TABLE IF EXISTS `t_salary`; CREATE TABLE `t_salary` ( `sid` int(10) NOT NULL AUTO_INCREMENT, `eid` int(10) NULL DEFAULT NULL, `money` int(11) NULL DEFAULT NULL, `stime` date NULL DEFAULT NULL, PRIMARY KEY (`sid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10014 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_salary -- ---------------------------- INSERT INTO `t_salary` VALUES (10001, 10003, 9266, '2023-12-10'); INSERT INTO `t_salary` VALUES (10002, 10011, 6534, '2023-12-10'); INSERT INTO `t_salary` VALUES (10003, 10009, 9728, '2023-12-10'); INSERT INTO `t_salary` VALUES (10004, 10001, 5620, '2023-12-10'); INSERT INTO `t_salary` VALUES (10005, 10006, 8507, '2023-12-10'); INSERT INTO `t_salary` VALUES (10006, 10007, 5118, '2023-12-10'); INSERT INTO `t_salary` VALUES (10007, 10005, 7754, '2023-12-10'); INSERT INTO `t_salary` VALUES (10008, 10002, 7322, '2023-12-10'); INSERT INTO `t_salary` VALUES (10009, 10013, 6346, '2023-12-10'); INSERT INTO `t_salary` VALUES (10010, 10008, 6170, '2023-12-10'); INSERT INTO `t_salary` VALUES (10011, 10012, 9900, '2023-12-10'); INSERT INTO `t_salary` VALUES (10012, 10004, 6369, '2023-12-10'); INSERT INTO `t_salary` VALUES (10013, 10010, 7645, '2023-12-10'); SET FOREIGN_KEY_CHECKS = 1; 第6题/db_shop.myd /* 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_shop Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 14/11/2023 10:41:56 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for tb_customer -- ---------------------------- DROP TABLE IF EXISTS `tb_customer`; CREATE TABLE `tb_customer` ( `cno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `birthday` date NULL DEFAULT NULL, `register_date` date NULL DEFAULT NULL, PRIMARY KEY (`cno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of tb_customer -- ---------------------------- INSERT INTO `tb_customer` VALUES ('c0001', '小明', '男', '12345678901', '2003-06-13', '2023-11-14'); INSERT INTO `tb_customer` VALUES ('c0002', '小丽', '女', '12345678901', '2002-12-01', '2023-11-15'); INSERT INTO `tb_customer` VALUES ('c0003', '小华', '男', '12449195010', '2003-03-12', '2023-11-30'); INSERT INTO `tb_customer` VALUES ('c0004', '小涛', '男', '01020120301', '2003-07-14', '2023-12-01'); INSERT INTO `tb_customer` VALUES ('c0005', '小芳', '女', '12351051020', '2003-04-05', '2023-12-01'); -- ---------------------------- -- Table structure for tb_goods -- ---------------------------- DROP TABLE IF EXISTS `tb_goods`; CREATE TABLE `tb_goods` ( `gno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `gname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `price` decimal(10, 2) NOT NULL, `stock` int(10) UNSIGNED ZEROFILL NULL DEFAULT NULL, `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`gno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of tb_goods -- ---------------------------- INSERT INTO `tb_goods` VALUES ('g0001', '绿茶', 3.00, 0000000020, '饮品'); INSERT INTO `tb_goods` VALUES ('g0002', '薯片', 5.00, 0000000010, '零食'); INSERT INTO `tb_goods` VALUES ('g0003', '可乐', 3.00, 0000000020, '饮品'); INSERT INTO `tb_goods` VALUES ('g0004', '果汁', 7.00, 0000000000, '饮品'); INSERT INTO `tb_goods` VALUES ('g0005', '果脯', 8.00, 0000000015, '零食'); INSERT INTO `tb_goods` VALUES ('g0006', '酸奶', 6.00, 0000000005, '饮品'); SET FOREIGN_KEY_CHECKS = 1; 第7题/db_school.myd /* 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_school Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 14/11/2023 15:21:51 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for tb_class -- ---------------------------- DROP TABLE IF EXISTS `tb_class`; CREATE TABLE `tb_class` ( `cid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `address` varchar(20) 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 tb_class -- ---------------------------- INSERT INTO `tb_class` VALUES ('c0001', '高一1班', '一楼'); INSERT INTO `tb_class` VALUES ('c0002', '高一2班', '一楼'); INSERT INTO `tb_class` VALUES ('c0003', '高二1班', '二楼'); INSERT INTO `tb_class` VALUES ('c0004', '高二2班', '二楼'); INSERT INTO `tb_class` VALUES ('c0005', '高三1班', '三楼'); INSERT INTO `tb_class` VALUES ('c0006', '高三2班', '三楼'); -- ---------------------------- -- Table structure for tb_student -- ---------------------------- DROP TABLE IF EXISTS `tb_student`; CREATE TABLE `tb_student` ( `sid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(2) NULL DEFAULT NULL, `cid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`sid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of tb_student -- ---------------------------- INSERT INTO `tb_student` VALUES ('s0001', '王一', '女', 18, 'c0005'); INSERT INTO `tb_student` VALUES ('s0002', '刘二', '女', 15, 'c0001'); INSERT INTO `tb_student` VALUES ('s0003', '张三', '男', 17, 'c0006'); INSERT INTO `tb_student` VALUES ('s0004', '高四', '男', 16, 'c0001'); INSERT INTO `tb_student` VALUES ('s0005', '郑五', '男', 17, 'c0003'); INSERT INTO `tb_student` VALUES ('s0006', '田六', '女', 16, 'c0004'); INSERT INTO `tb_student` VALUES ('s0007', '方七', '男', 16, 'c0002'); INSERT INTO `tb_student` VALUES ('s0008', '陈八', '男', 15, 'c0001'); INSERT INTO `tb_student` VALUES ('s0009', '吴九', '女', 16, 'c0004'); SET FOREIGN_KEY_CHECKS = 1; 第8题/db_social.myd /* 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_social Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 15/11/2023 11:11:24 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for tb_friendship -- ---------------------------- DROP TABLE IF EXISTS `tb_friendship`; CREATE TABLE `tb_friendship` ( `fsid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `uid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `fid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `createtime` date NULL DEFAULT NULL, PRIMARY KEY (`fsid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of tb_friendship -- ---------------------------- INSERT INTO `tb_friendship` VALUES ('fs0001', 'u0001', 'u0003', '2023-11-14'); INSERT INTO `tb_friendship` VALUES ('fs0002', 'u0001', 'u0004', '2023-11-14'); INSERT INTO `tb_friendship` VALUES ('fs0003', 'u0001', 'u0006', '2023-11-14'); INSERT INTO `tb_friendship` VALUES ('fs0004', 'u0003', 'u0004', '2023-11-14'); INSERT INTO `tb_friendship` VALUES ('fs0005', 'u0003', 'u0001', '2023-11-14'); INSERT INTO `tb_friendship` VALUES ('fs0006', 'u0004', 'u0001', '2023-11-14'); INSERT INTO `tb_friendship` VALUES ('fs0007', 'u0005', 'u0007', '2023-11-14'); INSERT INTO `tb_friendship` VALUES ('fs0008', 'u0007', 'u0005', '2023-11-14'); -- ---------------------------- -- Table structure for tb_user -- ---------------------------- DROP TABLE IF EXISTS `tb_user`; CREATE TABLE `tb_user` ( `uid` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `username` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `password` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `nickname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(3) NULL DEFAULT NULL, `sex` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`uid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of tb_user -- ---------------------------- INSERT INTO `tb_user` VALUES ('u0001', 'abc123', '12345678', '张飞', 18, '男'); INSERT INTO `tb_user` VALUES ('u0002', 'dc4912', '8888888', '貂蝉', 17, '女'); INSERT INTO `tb_user` VALUES ('u0003', 'gy666', '666888', '关羽', 21, '男'); INSERT INTO `tb_user` VALUES ('u0004', 'lb777', '789456123', '刘备', 22, '男'); INSERT INTO `tb_user` VALUES ('u0005', 'qiao123', 'abc1111', '小乔', 18, '女'); INSERT INTO `tb_user` VALUES ('u0006', 'liang192', 'jfu241', '诸葛亮', 16, '男'); INSERT INTO `tb_user` VALUES ('u0007', 'zy124', 'qq520', '周瑜', 19, '男'); SET FOREIGN_KEY_CHECKS = 1; 第9题/db_sport.myd /* 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_sport Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 22/11/2023 14:54:12 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_client -- ---------------------------- DROP TABLE IF EXISTS `t_client`; CREATE TABLE `t_client` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `integral` int(10) NULL DEFAULT NULL, PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10021 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_client -- ---------------------------- INSERT INTO `t_client` VALUES (10001, '陆子异', '130-7551-5666', 3123); INSERT INTO `t_client` VALUES (10002, '王嘉伦', '760-184-9612', 3443); INSERT INTO `t_client` VALUES (10003, '朱安琪', '168-8534-9596', 2991); INSERT INTO `t_client` VALUES (10004, '邵詩涵', '755-066-6924', 1455); INSERT INTO `t_client` VALUES (10005, '姚秀英', '10-711-1933', 7601); INSERT INTO `t_client` VALUES (10006, '韩岚', '197-7025-0977', 6612); INSERT INTO `t_client` VALUES (10007, '郝安琪', '141-1101-0564', 8509); INSERT INTO `t_client` VALUES (10008, '朱嘉伦', '156-6204-0434', 8873); INSERT INTO `t_client` VALUES (10009, '毛安琪', '189-2044-0406', 6877); INSERT INTO `t_client` VALUES (10010, '李晓明', '162-2659-5828', 4276); INSERT INTO `t_client` VALUES (10011, '孟岚', '171-1275-1205', 3217); INSERT INTO `t_client` VALUES (10012, '史嘉伦', '138-4843-3825', 2792); INSERT INTO `t_client` VALUES (10013, '龙子韬', '171-9505-8508', 8413); INSERT INTO `t_client` VALUES (10014, '朱子韬', '21-487-3662', 1847); INSERT INTO `t_client` VALUES (10015, '韩子韬', '196-8333-7410', 7784); INSERT INTO `t_client` VALUES (10016, '胡嘉伦', '10-993-2956', 8069); INSERT INTO `t_client` VALUES (10017, '陶宇宁', '21-3737-9044', 6508); INSERT INTO `t_client` VALUES (10018, '毛致远', '760-1740-3540', 4678); INSERT INTO `t_client` VALUES (10019, '方杰宏', '769-090-1443', 6551); INSERT INTO `t_client` VALUES (10020, '邓璐', '179-2081-5425', 1990); -- ---------------------------- -- Table structure for t_lease -- ---------------------------- DROP TABLE IF EXISTS `t_lease`; CREATE TABLE `t_lease` ( `lid` int(11) NOT NULL AUTO_INCREMENT, `cid` int(11) NOT NULL, `sid` int(11) NOT NULL, `time` datetime NULL DEFAULT NULL, `duration` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`lid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_lease -- ---------------------------- INSERT INTO `t_lease` VALUES (10001, 10007, 10007, '2002-08-26 16:39:50', '1天'); INSERT INTO `t_lease` VALUES (10002, 10009, 10001, '2000-11-17 14:03:56', '2小时'); INSERT INTO `t_lease` VALUES (10003, 10015, 10005, '2010-06-07 10:23:16', '1小时'); INSERT INTO `t_lease` VALUES (10004, 10016, 10009, '2022-06-02 11:41:43', '3小时'); INSERT INTO `t_lease` VALUES (10005, 10018, 10010, '2001-07-02 19:53:43', '1天'); INSERT INTO `t_lease` VALUES (10006, 10006, 10006, '2006-07-09 11:58:26', '2小时'); INSERT INTO `t_lease` VALUES (10007, 10014, 10004, '2010-10-24 22:30:40', '1天'); INSERT INTO `t_lease` VALUES (10008, 10010, 10003, '2013-02-01 09:07:54', '1小时'); INSERT INTO `t_lease` VALUES (10009, 10020, 10008, '2011-03-17 15:18:43', '1天'); INSERT INTO `t_lease` VALUES (10010, 10011, 10002, '2008-08-12 12:46:58', '1天'); INSERT INTO `t_lease` VALUES (10011, 10019, 10006, '2021-11-25 23:43:55', '3小时'); INSERT INTO `t_lease` VALUES (10012, 10001, 10010, '2004-06-09 04:46:04', '1小时'); INSERT INTO `t_lease` VALUES (10013, 10017, 10004, '2010-04-24 00:30:18', '1小时'); INSERT INTO `t_lease` VALUES (10014, 10003, 10005, '2010-06-22 21:47:28', '2小时'); INSERT INTO `t_lease` VALUES (10015, 10004, 10004, '2014-10-09 11:49:04', '3小时'); INSERT INTO `t_lease` VALUES (10016, 10013, 10002, '2007-12-03 11:49:49', '1天'); INSERT INTO `t_lease` VALUES (10017, 10005, 10010, '2000-09-26 09:04:44', '1小时'); INSERT INTO `t_lease` VALUES (10018, 10008, 10010, '2020-10-09 12:44:14', '3小时'); INSERT INTO `t_lease` VALUES (10019, 10002, 10003, '2012-07-29 21:08:13', '2小时'); INSERT INTO `t_lease` VALUES (10020, 10012, 10008, '2011-01-13 21:21:55', '3小时'); INSERT INTO `t_lease` VALUES (10021, 10004, 10001, '2007-05-19 20:47:59', '3小时'); INSERT INTO `t_lease` VALUES (10022, 10004, 10009, '2018-12-19 00:35:50', '3小时'); INSERT INTO `t_lease` VALUES (10023, 10005, 10001, '2012-12-31 19:29:23', '1天'); INSERT INTO `t_lease` VALUES (10024, 10009, 10008, '2002-08-14 19:33:10', '3小时'); INSERT INTO `t_lease` VALUES (10025, 10016, 10005, '2011-02-14 01:39:03', '2小时'); INSERT INTO `t_lease` VALUES (10026, 10009, 10006, '2003-02-02 19:06:40', '2小时'); INSERT INTO `t_lease` VALUES (10027, 10020, 10001, '2013-07-05 07:27:32', '1天'); INSERT INTO `t_lease` VALUES (10028, 10014, 10010, '2023-07-31 11:33:19', '1天'); INSERT INTO `t_lease` VALUES (10029, 10012, 10001, '2003-07-07 20:03:21', '2小时'); INSERT INTO `t_lease` VALUES (10030, 10002, 10006, '2017-06-26 07:54:53', '2小时'); INSERT INTO `t_lease` VALUES (10031, 10016, 10007, '2021-03-06 01:38:31', '2小时'); INSERT INTO `t_lease` VALUES (10032, 10014, 10009, '2002-01-24 15:56:52', '1小时'); INSERT INTO `t_lease` VALUES (10033, 10003, 10003, '2005-02-16 23:18:24', '2小时'); INSERT INTO `t_lease` VALUES (10034, 10007, 10004, '2001-04-02 15:29:34', '3小时'); INSERT INTO `t_lease` VALUES (10035, 10010, 10005, '2006-03-08 11:03:16', '1小时'); INSERT INTO `t_lease` VALUES (10036, 10011, 10004, '2018-02-20 20:30:16', '2小时'); INSERT INTO `t_lease` VALUES (10037, 10002, 10003, '2005-12-29 22:28:25', '1天'); INSERT INTO `t_lease` VALUES (10038, 10015, 10002, '2005-09-14 21:07:10', '2小时'); INSERT INTO `t_lease` VALUES (10039, 10012, 10009, '2018-10-12 18:23:40', '2小时'); INSERT INTO `t_lease` VALUES (10040, 10005, 10006, '2018-12-18 13:35:23', '3小时'); INSERT INTO `t_lease` VALUES (10041, 10015, 10005, '2015-04-16 18:04:22', '3小时'); INSERT INTO `t_lease` VALUES (10042, 10019, 10007, '2006-08-30 23:09:49', '1小时'); INSERT INTO `t_lease` VALUES (10043, 10006, 10008, '2005-02-28 07:28:31', '1天'); INSERT INTO `t_lease` VALUES (10044, 10008, 10009, '2023-02-26 18:04:02', '3小时'); INSERT INTO `t_lease` VALUES (10045, 10011, 10003, '2001-01-20 00:05:45', '3小时'); INSERT INTO `t_lease` VALUES (10046, 10019, 10002, '2011-02-24 10:39:53', '3小时'); INSERT INTO `t_lease` VALUES (10047, 10010, 10007, '2014-11-10 08:20:44', '1小时'); INSERT INTO `t_lease` VALUES (10048, 10001, 10008, '2017-06-01 18:27:48', '1天'); INSERT INTO `t_lease` VALUES (10049, 10006, 10002, '2012-06-20 14:55:13', '1小时'); INSERT INTO `t_lease` VALUES (10050, 10007, 10007, '2012-04-14 11:31:33', '2小时'); -- ---------------------------- -- Table structure for t_site -- ---------------------------- DROP TABLE IF EXISTS `t_site`; CREATE TABLE `t_site` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `price` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`sid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10011 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_site -- ---------------------------- INSERT INTO `t_site` VALUES (10001, '篮球', '篮球场地2号', '70/小时'); INSERT INTO `t_site` VALUES (10002, '拳击', '拳击场地1号', '50/小时'); INSERT INTO `t_site` VALUES (10003, '篮球', '篮球场地1号', '70/小时'); INSERT INTO `t_site` VALUES (10004, '羽毛球', '高级羽毛球场地2号', '70/小时'); INSERT INTO `t_site` VALUES (10005, '羽毛球', '入门羽毛球场地2号', '50/小时'); INSERT INTO `t_site` VALUES (10006, '足球', '足球场地', '100/小时'); INSERT INTO `t_site` VALUES (10007, '拳击', '拳击场地2号', '50/小时'); INSERT INTO `t_site` VALUES (10008, '拳击', '拳击场地3号', '50/小时'); INSERT INTO `t_site` VALUES (10009, '羽毛球', '高级羽毛球场地1号', '70/小时'); INSERT INTO `t_site` VALUES (10010, '羽毛球', '入门羽毛球场地1号', '50/小时'); SET FOREIGN_KEY_CHECKS = 1; $ 编写说明：湖北省技能高考《计算机类考纲百套卷》,依据《湖北省普通高等学校招收中等职业学校毕业生技能高考计算机类技能考试大纲》编写。每门课程均分三层训练：基础层(具象化支架).以拆解考点为微目标,紧扣考纲 掌握理解 要求编写考点训练卷；巩固层(关联性支架).强化知识交叉与场景关联,按考纲专题编专题训练卷；应用层(引导性支架).聚焦真题突破,结合知识模块与教材编写课程综合卷。 本试卷是第74卷,按《MySQL数据库基础》范围和要求编写。具体内容为:掌握存储过程与存储函数的特性、应用场景及核心区别;熟练执行存储过程的创建、修改与删除操作 湖北省《计算机类考纲百套卷》 第74卷 《MySQL数据库基础》 存储过程与存储函数 考点训练卷 考试时间80分钟 满分100分 班级 姓名 学号 成绩 一、数据库操作题(本大题共10小题,每题10分,共100分). 1.线上教育数据库db_education包含了tb_student学生表、tb_course课程表和tb_video视频表 tb_student包含字段sid(学生编号),sname(学生姓名),username(用户名),password(密码),classes(班级)。 tb_course包含字段 cid(课程编号),cname(课程名称),description(描述), classhour(课时)。 tb_video包括字段vid(视频编号),cid(课程编号),title(标题),link(链接地址) (1).创建一个存储函数GetCourseVideoCount,接收课程编号c_id作为参数,返回该课程拥有的视频数量。 (2).创建一个存储过程AddVideo,接收课程编号、视频标题和链接地址作为参数,向tb_video表中插入一条新视频记录,并返回新插入视频的编号 答案: (1).DELIMITER // CREATE FUNCTION GetCourseVideoCount(c_id VARCHAR(20)) RETURNS INT DETERMINISTIC BEGIN DECLARE v_count INT; SELECT COUNT(*) INTO v_count FROM tb_video WHERE cid = c_id; RETURN v_count; END // DELIMITER ; (2).DELIMITER // CREATE PROCEDURE AddVideo( IN c_id VARCHAR(20), IN v_title VARCHAR(100), IN v_link VARCHAR(200), OUT new_vid VARCHAR(20) ) BEGIN INSERT INTO tb_video(cid, title, link) VALUES(c_id, v_title, v_link); SET new_vid = LAST_INSERT_ID(); END // DELIMITER ; 2.酒店预订数据库db_hotel包含了t_hotel酒店表、t_room房间表、t_client客户表和t_Booking预定表 t_hotel包含字段hid(酒店编号),hname(酒店名称),location(地址),tel(联系方式) t_room包含字段 rid(房间编号),hid(酒店编号),rname(房间名称),price(价格) t_client包含字段cid(客户编号),cname(客户姓名),idcard(身份证号),tel(联系方式),integral(会员积分) t_Booking包含字段bid(预定编号),rid(房间编号),cid(会员编号),btime(预定时间) (1).创建一个存储过程SearchRooms,接收价格下限min_price和价格上限max_price作为参数,查询该价格区间内的所有房间信息(包含酒店名称).。 (2).创建一个存储函数GetClientBookingCount,接收客户编号c_id作为参数,返回该客户的预订总次数 答案: (1).DELIMITER // CREATE PROCEDURE SearchRooms( IN min_price DECIMAL(10,2), IN max_price DECIMAL(10,2) ) BEGIN SELECT r.rid, r.rname, r.price, h.hname, h.location FROM t_room r INNER JOIN t_hotel h ON r.hid = h.hid WHERE r.price BETWEEN min_price AND max_price; END // DELIMITER ; (2).DELIMITER // CREATE FUNCTION GetClientBookingCount(c_id VARCHAR(20)) RETURNS INT DETERMINISTIC BEGIN DECLARE v_count INT; SELECT COUNT(*) INTO v_count FROM t_Booking WHERE cid = c_id; RETURN v_count; END // DELIMITER ; 3.公交管理系统数据库db_bus包含了t_driver司机表、t_bus公交表和t_attendance发车表 t_driver包含字段did(司机编号),dname(司机姓名),sex(性别),age(年龄),salary(薪资)。 t_bus包含字段 bid(公交编号),platenum(车牌号),linenum(线路号码)。 t_attendance包含字段aid(发车编号),bid(公交编号),did(司机编号),starttime(发车时间),endtime(收班时间)。 (1).创建一个存储过程GetDriverAttendance,接收司机编号d_id作为参数,查询该司机的所有发车记录(显示车牌号、线路号码、发车时间和收班时间).。 (2).创建一个存储函数GetDriverTotalHours,接收司机编号d_id作为参数,计算该司机所有发车记录的累计工作时长 答案: (1).DELIMITER // CREATE PROCEDURE GetDriverAttendance(IN d_id VARCHAR(20)) BEGIN SELECT b.platenum, b.linenum, a.starttime, a.endtime FROM t_attendance a INNER JOIN t_bus b ON a.bid = b.bid WHERE a.did = d_id; END // DELIMITER ; (2).DELIMITER // CREATE FUNCTION GetDriverTotalHours(d_id VARCHAR(20)) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN DECLARE total DECIMAL(10,2); SELECT SUM(TIMESTAMPDIFF(HOUR, starttime, endtime)) INTO total FROM t_attendance WHERE did = d_id; RETURN IFNULL(total, 0); END // DELIMITER ; 4.健身房管理系统数据库db_gym包含了t_member会员表、t_trainer私教表和t_course 课程表 t_member包含字段mid(会员编号),mname(会员姓名),sex(性别),age(年龄),money(余额),integral(会员积分)。 t_trainer包含字段 tid(私教编号),tname(私教姓名),sex(性别),age(年龄),salary(薪资)。 t_course包含字段cid(课程编号),mid(会员编号),tid(私教编号),period (课时),price(收费价格)。 (1).创建一个存储过程AddCourse,接收会员编号、私教编号、课时数和价格作为参数,向t_course表中插入一条新课程记录。 (2).创建一个存储函数GetMemberTotalCost,接收会员编号m_id作为参数,返回该会员购买所有课程的总费用(SUM(period * price)) 答案: (1).DELIMITER // CREATE PROCEDURE AddCourse( IN m_id VARCHAR(20), IN t_id VARCHAR(20), IN p_period INT, IN p_price DECIMAL(10,2) ) BEGIN INSERT INTO t_course(mid, tid, period, price) VALUES(m_id, t_id, p_period, p_price); END // DELIMITER ; (2).DELIMITER // CREATE FUNCTION GetMemberTotalCost(m_id VARCHAR(20)) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN DECLARE total DECIMAL(10,2); SELECT SUM(period * price) INTO total FROM t_course WHERE mid = m_id; RETURN IFNULL(total, 0); END // DELIMITER ; 5.人力资源管理系统数据库db_hrm包含了t_employee员工表、t_department部门表和t_salary薪资表 t_employee包含字段eid(员工编号),ename(员工姓名),sex(性别),age(年龄),position(职位),did(部门编号)。 t_department包含字段 did(部门编号),dname(部门名称),description(部门描述)。 t_salary包含字段sid(薪资编号),eid(员工编号),money(薪资),stime(薪资日期) (1).创建一个存储过程GetEmployeeSalary,接收员工编号e_id作为参数,查询该员工的所有薪资记录(显示薪资额和薪资日期).。 (2).创建一个存储函数GetDeptAvgSalary,接收部门编号d_id作为参数,返回该部门员工的平均薪资 答案: (1).DELIMITER // CREATE PROCEDURE GetEmployeeSalary(IN e_id VARCHAR(20)) BEGIN SELECT money, stime FROM t_salary WHERE eid = e_id; END // DELIMITER ; (2).DELIMITER // CREATE FUNCTION GetDeptAvgSalary(d_id VARCHAR(20)) RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN DECLARE avg_sal DECIMAL(10,2); SELECT AVG(s.money) INTO avg_sal FROM t_salary s INNER JOIN t_employee e ON s.eid = e.eid WHERE e.did = d_id; RETURN IFNULL(avg_sal, 0); END // DELIMITER ; 6.数据库db_shop中有商品表tb_goods和客户表tb_customer tb_goods表包含字段有gno(商品编号)、gname(商品名称)、price(价格)、stock(库存)、type(商品类型) tb_customer表包含字段有cno(客户编号)、cname(客户姓名)、sex(性别)、tel(手机号)、 birthday(生日)、register_date(注册日期) (1).创建一个存储过程GetGoodsByType,接收商品类型g_type作为参数,查询该类型下所有库存大于0的商品信息。 (2).创建一个存储函数CountCustomersBySex,接收性别g_sex作为参数,返回该性别的客户人数 答案: (1).DELIMITER // CREATE PROCEDURE GetGoodsByType(IN g_type VARCHAR(50)) BEGIN SELECT gno, gname, price, stock FROM tb_goods WHERE type = g_type AND stock > 0; END // DELIMITER ; (2).DELIMITER // CREATE FUNCTION CountCustomersBySex(g_sex VARCHAR(2)) RETURNS INT DETERMINISTIC BEGIN DECLARE v_count INT; SELECT COUNT(*) INTO v_count FROM tb_customer WHERE sex = g_sex; RETURN v_count; END // DELIMITER ; 7.校园信息管理数据库db_school,包含了班级表tb_class和学生信息表tb_student tb_class包含字段cid(班级编号),cname(班级名称),address(班级楼层) tb_student包含字段sid(学生编号),sname(学生姓名),sex(学生性别),age(学生年龄),cid(班级编号) (1).创建一个存储过程GetClassStudents,接收班级编号c_id作为参数,查询该班级所有学生的信息。 (2).创建一个存储函数GetClassStudentCount,接收班级编号c_id作为参数,返回该班级的学生人数 答案: (1).DELIMITER // CREATE PROCEDURE GetClassStudents(IN c_id VARCHAR(20)) BEGIN SELECT sid, sname, sex, age FROM tb_student WHERE cid = c_id; END // DELIMITER ; (2).DELIMITER // CREATE FUNCTION GetClassStudentCount(c_id VARCHAR(20)) RETURNS INT DETERMINISTIC BEGIN DECLARE v_count INT; SELECT COUNT(*) INTO v_count FROM tb_student WHERE cid = c_id; RETURN v_count; END // DELIMITER ; 8.社交网络数据库db_social包含了tb_user用户表和tb_friendship好友关系表 tb_user包含字段uid(用户编号),username(用户名),password(密码),nickname(昵称),age(年龄),sex(性别)。 tb_friendship包含字段 fsid(好友关系编号),uid(用户id),fid(好友id),createtime(添加日期)。 (1).创建一个存储过程GetUserFriends，接收用户编号u_id作为参数，查询该用户的所有好友信息（显示好友昵称和添加日期）。 (2).创建一个存储函数GetUserFriendCount，接收用户编号u_id作为参数，返回该用户的好友数量 答案: (1).DELIMITER // CREATE PROCEDURE GetUserFriends(IN u_id VARCHAR(20)) BEGIN SELECT u2.nickname, f.createtime FROM tb_friendship f INNER JOIN tb_user u2 ON f.fid = u2.uid WHERE f.uid = u_id; END // DELIMITER ; (2).DELIMITER // CREATE FUNCTION GetUserFriendCount(u_id VARCHAR(20)) RETURNS INT DETERMINISTIC BEGIN DECLARE v_count INT; SELECT COUNT(*) INTO v_count FROM tb_friendship WHERE uid = u_id; RETURN v_count; END // DELIMITER ; 9.运动场地租借系统数据库db_sport包含了t_site场地表、t_client客户表和t_lease 租借记录表 t_client包含字段cid(客户编号),name(客户姓名),tel(联系电话),integral(积分)。 t_site包含字段 sid(场地编号),type(场地类别),name(场地名称),price(场地价格)。 t_lease包含字段lid(租借编号),cid(客户编号),sid(场地编号),time(租借时间),duration(时长)。 (1).创建一个存储过程GetClientLease,接收客户编号c_id作为参数,查询该客户的所有租借记录(显示场地名称、租借时间和时长).。 (2).创建一个存储函数GetSiteLeaseCount,接收场地编号s_id作为参数,返回该场地的租借总次数 答案: (1).DELIMITER // CREATE PROCEDURE GetClientLease(IN c_id VARCHAR(20)) BEGIN SELECT s.name, l.time, l.duration FROM t_lease l INNER JOIN t_site s ON l.sid = s.sid WHERE l.cid = c_id; END // DELIMITER ; (2).DELIMITER // CREATE FUNCTION GetSiteLeaseCount(s_id VARCHAR(20)) RETURNS INT DETERMINISTIC BEGIN DECLARE v_count INT; SELECT COUNT(*) INTO v_count FROM t_lease WHERE sid = s_id; RETURN v_count; END // DELIMITER ; 10.房屋租赁系统数据库db_lease包含了t_house房屋信息表、t_client客户表和t_order 订单表 t_house包含字段hid(房屋编号),address(地址),tel(户主联系电话),type(房屋类型),decoration(装修情况),area(面积),price(每月价格)。 t_client包含字段 cid(客户编号),name(客户姓名),tel(联系电话),need (需求备注)。 t_order包含字段oid(订单编号),cid(客户编号),hid(房屋编号),time(租赁时间),duration(租赁时长)。 (1).创建一个存储过程GetClientOrders,接收客户编号c_id作为参数,查询该客户的所有订单记录(显示房屋地址、租赁时间和租赁时长).。 (2).创建一个存储函数GetHouseOrderCount,接收房屋编号h_id作为参数,返回该房屋的订单总次数 答案: (1).DELIMITER // CREATE PROCEDURE GetClientOrders(IN c_id VARCHAR(20)) BEGIN SELECT h.address, o.time, o.duration FROM t_order o INNER JOIN t_house h ON o.hid = h.hid WHERE o.cid = c_id; END // DELIMITER ; (2).DELIMITER // CREATE FUNCTION GetHouseOrderCount(h_id VARCHAR(20)) RETURNS INT DETERMINISTIC BEGIN DECLARE v_count INT; SELECT COUNT(*) INTO v_count FROM t_order WHERE hid = h_id; RETURN v_count; END // DELIMITER ; 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京).股份有限公司 学科网(北京).股份有限公司 学科网（北京）股份有限公司 $