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

《MySQL数据库基础》 期末复习卷(七) 时间:90分钟 总分:100分 班级 姓名 学号 成绩 一.数据库操作题(本大题共5小题,每小题20分,共100分) 1.图书管理数据库db_library包含了3个表,即读者表(readers)、书籍表(books)、借阅记录表(borrowRecords)。 读者表readers包含字段readerID(读者ID),name(读者姓名),phoneNumber(电话号码)、email(邮箱)、registrationDate(注册时间),主键是readerID 书籍表books包含字段bookID(书籍ID),title(书籍标题),author(作者),isbn(书号),publishedDate(出版日期),available(借阅状态:1:未借出,0:借出),主键是bookID 借阅阅记录表borrowRecords包含字段borrowRecordID(借阅记录ID),readerID(借阅读者ID),bookID(借阅书籍ID),borrowDate(借阅时间),dueDate(应还日期),returned(归还状态:1:归还,0:未归还),主键是borrowRecordID 上述数据库及数据表已经创建,请按要求完成以下操作。 1).修改books表的借阅状态默认为1 2).创建一个书籍预定表reservation,字段如下: 字段名称 类型 长度 需求 reservationID (预定编号) int 主键,自增长 readerID(读者ID) int 不能为空 bookID(书籍ID) int 不能为空 date(预定日期) date status(预定状态) varchar 10 默认值为待处理 3).在书籍表中新增一条记录,书籍标题:追风筝的人,作者:卡勒德·胡赛尼,书号:9787530214313,出版日期:2006-05-01,借阅状态:1。 4).请用sql语句找出读者ID为2借阅的所有书籍,要求显示书籍标题,作者,书号,出版日期,借阅状态,借阅时间。 5、请用sql语句查询出当前有书籍尚未归还的读者。要求显示读者姓名,电话号码,邮箱,借阅时间,应还日期。 6、创建一个视图view_readerHistory要求显示读者的借阅历史记录,要求显示读者姓名,书籍标题,书号,借阅时间,应还日期,是否归还。 7、创建一个触发器update_books,该触发器将在borrowrecords表插入操作之后被触发。触发器需要执行以下操作:根据新增的书籍ID,更新书籍表books中借阅状态为0(借出); 8、创建一个存储过程update_borrowrecords,该存储过程接收一个借阅记录ID(p_borrowRecordID)和一个归还状态(p_returned)作为输入参数,数据类型均为int,根据输入的借阅记录ID和归还状态更新借阅记录表borrowRecords中的记录; 答案: 1).ALTER TABLE books MODIFY available INT DEFAULT 1; 2).CREATE TABLE reservation (reservationID INT PRIMARY KEY AUTO_INCREMENT,readerID INT NOT NULL,bookID INT NOT NULL,date DATE,status VARCHAR(10) DEFAULT '待处理'); 3).INSERT INTO books (title, author, isbn, publishedDate, available) VALUES ('追风筝的人', '卡勒德·胡赛尼', '9787530214313', '2006-05-01', 1); 4).SELECT b.title AS 书籍标题, b.author AS 作者, b.isbn AS 书号,b.publishedDate AS 出版日期, b.available AS 借阅状态, br.borrowDate AS 借阅时间FROM borrowRecords br JOIN books b ON br.bookID = b.bookIDWHERE br.readerID = 2; 5).SELECT r.name AS 读者姓名, r.phoneNumber AS 电话号码, r.email AS 邮箱,br.borrowDate AS 借阅时间, br.dueDate AS 应还日期FROM borrowRecords brJOIN readers r ON br.readerID = r.readerID WHERE br.returned = 0; 6).view_readerHistoryCREATE VIEW view_readerHistory ASSELECT r.name AS 读者姓名, b.title AS 书籍标题, b.isbn AS 书号,br.borrowDate AS 借阅时间, br.dueDate AS 应还日期,CASE WHEN br.returned = 1 THEN '是' ELSE '否' END AS 是否归还FROM borrowRecords br JOIN readers r ON br.readerID = r.readerID JOIN books b ON br.bookID = b.bookID; 7).update_booksDELIMITER //CREATE TRIGGER update_booksAFTER INSERT ON borrowRecordsFOR EACH ROWBEGIN UPDATE books SET available = 0 WHERE bookID = NEW.bookID;END//DELIMITER ; 8).update_borrowrecordsDELIMITER //CREATE PROCEDURE update_borrowrecords(IN p_borrowRecordID INT,IN p_returned INT) BEGIN UPDATE borrowRecords SET returned = p_returned WHERE borrowRecordID = p_borrowRecordID;END//DELIMITER ; 2.马拉松数据库db_marathon包含了3个表,即参赛者表(participants)、比赛表(races)、成绩表(marathonresults)。 参赛者表participants包含字段participantId(参赛者ID),name(姓名),age(年龄)、gender(性别)、city(城市)、email(电子邮件)、phone(电话)、registrationDate(注册日期),主键是participantId 比赛表races包含字段raceId(比赛ID)、raceName(比赛名称)、raceDate(比赛日期)、location(比赛地点),主键是raceId 马拉松成绩表marathonResults包含字段resultId(马拉松成绩ID),participantId(参赛者ID),raceId(比赛ID),finishTime(完成时间),rank(排名),主键是resultId 上述数据库及数据表已经创建,请按要求完成以下操作。 1).修改participants表gender字段设置只能存储男或者女。 2).创建一个训练表tranning,字段如下: 字段名称 类型 长度 需求 sessionId(预定编号) int 主键,自增长,不能为空 participantId(参赛者ID) int 不能为空 sessionDate(训练日期) date duration(训练时长) int location(训练地点) varchar 100 3).为李四插入一条比赛记录,李四在2023年上海马拉松的完成时间是02:22:01,排名第10。 4).请用sql语句查询所有参赛者及其参加的比赛名称和完成时间,要求显示姓名,性别,年龄,比赛名称,完成时间,排名。排名按升序排序。 5).请用sql语句查询出张三在马拉松比赛中最好的一次排名记录。要求显示姓名,比赛名称,完成时间,排名。 6、创建一个视图view_grade显示参赛人员的马拉松记录数据,要求显示姓名,比赛名称,完成时间,排名。其中排名按升序排序,完成时间按降序排序。 7、创建一个触发器trigger_delete,该触发器将在marathonresults表新增操作之前被触发。触发器需要执行以下操作:检查参赛者信息是否存在,如果不存在,则阻止插入操作并抛出错误信息参赛者信息不存在！; 8、创建一个存储过程pro_select,该存储过程接收一个参赛者ID(p_participantId)作为输入参数,数据类型为INT,根据输入的参赛者ID,查询出参赛者的姓名,完成时间,排名; 答案: 1).ALTER TABLE participants MODIFY gender ENUM('男', '女'); 2).tranningCREATE TABLE tranning (sessionId INT PRIMARY KEY AUTO_INCREMENT NOT NULL,participantId INT NOT NULL,sessionDate DATE,duration INT,location VARCHAR(100)); 3).INSERT INTO marathonResults (participantId, raceId, finishTime, rank)SELECT p.participantId, r.raceId, '02:22:01', 10 FROM participants p, races rWHERE p.name = '李四' AND r.raceName = '上海马拉松' AND YEAR(r.raceDate) = 2023; 4).SELECT p.name AS 姓名, p.gender AS 性别, p.age AS 年龄,r.raceName AS 比赛名称, m.finishTime AS 完成时间, m.rank AS 排名FROM marathonResults mJOIN participants p ON m.participantId = p.participantIdJOIN races r ON m.raceId = r.raceIdORDER BY m.rank ASC; 5).SELECT p.name AS 姓名, r.raceName AS 比赛名称, m.finishTime AS 完成时间, m.rank AS 排名FROM marathonResults m JOIN participants p ON m.participantId = p.participantId JOIN races r ON m.raceId = r.raceIdWHERE p.name = '张三' ORDER BY m.rank ASCLIMIT 1; 6).view_gradeCREATE VIEW view_grade ASSELECT p.name AS 姓名, r.raceName AS 比赛名称, m.finishTime AS 完成时间, m.rank AS 排名FROM marathonResults mJOIN participants p ON m.participantId = p.participantIdJOIN races r ON m.raceId = r.raceIdORDER BY m.rank ASC, m.finishTime DESC; 7).trigger_deleteDELIMITER //CREATE TRIGGER trigger_deleteBEFORE INSERT ON marathonResultsFOR EACH ROWBEGIN DECLARE p_count INT; SELECT COUNT(*) INTO p_count FROM participants WHERE participantId = NEW.participantId;IF p_count = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '参赛者信息不存在！'; END IF;END//DELIMITER ; 8).pro_selectDELIMITER //CREATE PROCEDURE pro_select(IN p_participantId INT)BEGIN SELECT p.name AS 姓名, m.finishTime AS 完成时间, m.rank AS 排名 FROM marathonResults m JOIN participants p ON m.participantId = p.participantId WHERE p.participantId = p_participantId;END//DELIMITER ; 3.汽车保险系统数据库db_insurance包含了t_customers客户表、t_cars汽车表、t_policies保单表。 t_customers客户表包含字段customerID(客户ID),name(客户姓名),phone(电话号码),email(邮箱),address(地址)。 t_cars汽车表包含字段carID(汽车ID),customerID(客户ID),licensePlate(汽车牌照),brand(汽车品牌),model(汽车型号),vin(车辆识别号)。 t_policies保单表包含字段policyID(保单ID),customerID(客户ID),carID(汽车ID),policyNumber(保单号),startDate(开始时间),endDate(结束时间),premium(保单费用),coverageType(保单类型)。 上述数据库及数据表已经创建,请按要求完成以下操作。 1).创建一个保养记录表t_maintenance,字段如下: 字段名称 类型 长度 需求 recordID(记录编号) int 主键,自增长 carID(汽车ID) int 不能为空 date(保养日期) date mileage(里程数) int type(保养类型) varchar 100 details(保养记录) text cost(保养费用) decimal 10,2 2).在客户表中插入一条客户数据('王强', '15535874564', 'wangqiang@example.com', '上海市浦东新区') 3).将车牌号为鄂A12587的汽车型号修改为Model Y 4).将t_policies表中policyNumber字段修改为唯一约束 5).查询所有客户的保单信息,按保单费用升序排序,要求显示客户姓名,保单类型,开始时间,结束时间,保单费用 6).查询客户张三拥有的汽车数量和保单总金额,列名分别为汽车数量,保单总金额 7).创建一个视图view_details,要求视图中显示出所有客户的姓名、汽车品牌、汽车型号、保单类型、结束时间,查询结果按照结束时间升序,按照汽车品牌降序; 8).创建一个名称为trigger_delete的触发器用来自动删除数据,具体描述如下:当一个客户从t_customers表中删除后,与该客户相关的所有汽车记录和保单记录也会被相应地删除 9).创建一个名称为pro_select的存储过程查询指定客户的车辆信息,具体描述如下:这个存储过程接收一个客户姓名(in_name)作为输入参数,数据类型为varchar(100),查询出客户名下的汽车信息,要求显示汽车牌照、汽车品牌、汽车型号 答案: 1).CREATE TABLE t_maintenance ( recordID INT PRIMARY KEY AUTO_INCREMENT, carID INT NOT NULL, date DATE, mileage INT, type VARCHAR(100), details TEXT, cost DECIMAL(10,2)); 2).INSERT INTO t_customers (name, phone, email, address) VALUES ('王强', '15535874564', 'wangqiang@example.com', '上海市浦东新区'); 3).Model YUPDATE t_cars SET model = 'Model Y' WHERE licensePlate = '鄂A12587'; 4).ALTER TABLE t_policies ADD CONSTRAINT unique_policyNumber UNIQUE (policyNumber); 5).SELECT c.name AS 客户姓名, p.coverageType AS 保单类型,p.startDate AS 开始时间, p.endDate AS 结束时间, p.premium AS 保单费用FROM t_policies p JOIN t_customers c ON p.customerID = c.customerID ORDER BY p.premium ASC; 6).SELECT COUNT(DISTINCT ca.carID) AS 汽车数量, SUM(po.premium) AS 保单总金额FROM t_customers c LEFT JOIN t_cars ca ON c.customerID = ca.customerIDLEFT JOIN t_policies po ON c.customerID = po.customerID WHERE c.name = '张三' GROUP BY c.customerID; 7).view_detailsCREATE VIEW view_details ASSELECT c.name AS 姓名, ca.brand AS 汽车品牌, ca.model AS 汽车型号,p.coverageType AS 保单类型, p.endDate AS 结束时间FROM t_customers c JOIN t_cars ca ON c.customerID = ca.customerID JOIN t_policies p ON c.customerID = p.customerIDORDER BY p.endDate ASC, ca.brand DESC; 8).trigger_deleteDELIMITER //CREATE TRIGGER trigger_deleteBEFORE DELETE ON t_customersFOR EACH ROWBEGIN DELETE FROM t_policies WHERE customerID = OLD.customerID;DELETE FROM t_cars WHERE customerID = OLD.customerID;END//DELIMITER ; 9).pro_selectDELIMITER //CREATE PROCEDURE pro_select(IN in_name VARCHAR(100))BEGIN SELECT ca.licensePlate AS 汽车牌照, ca.brand AS 汽车品牌, ca.model AS 汽车型号 FROM t_cars ca JOIN t_customers c ON ca.customerID = c.customerID WHERE c.name = in_name;END//DELIMITER ; 4.教师系统数据库db_teacher包含了t_teachers教师表、t_courses课程表、t_salaries薪资表。 t_teachers教师表包含字段 teacherID(教师ID),name(教师名称),gender(教师性别),age(教师年龄),email(邮箱),phoneNumber(联系电话),departmentID(部门ID),isactive(是否在职,1:在职,0:离职)。 t_courses课程表包含字段courseID(课程ID),courseName(课程名称),teacherID(教师ID)。 t_salaries薪资表包含字段salaryID(薪资ID),teacherID(教师ID),basicSalary(基本薪资),bonus(奖金),totalSalary(总薪资),year(年份)。 上述数据库及数据表已经创建,请按要求完成以下操作。 1).创建一个部门表t_departments,字段如下: 字段名称 类型 长度 需求 departmentID(部门ID) int 主键,自增长 departmentName(部门名称) varchar 100 不能为空 2).在部门表中插入四条记录,部门名称分别为计算机系,数学系,文学系,物理系。 3).将计算机系部门2023年的基本薪资整体上调200。 4).郑姗璐老师已经离职,请更新她的在职状态。 5).查询每位教师的课程信息,要求显示教师名称、教师性别、教师年龄、课程名称。 6).查询2023年在职教师的薪资信息,要求显示教师名称、教师性别、教师年龄、院系名称、基本工资、奖金、总薪资。 7).创建一个视图view_info,要求视图中显示出所有在职教师的教师名称、教师性别、教师年龄、部门名称、基本薪资、年份。 8).创建一个名称为update_teacher的触发器用来删除薪资表中的记录,具体描述如下:当在t_teachers表中更新记录之后,如果教师的在职状态从在职变成离职(1→0),则删除t_salaries薪资表对应的数据。 9).创建一个名称为pro_getTotalSalary的存储过程计算指定教师在该年份的总薪资,具体描述如下:这个存储过程接收一个教师ID(p_teacherID),数据类型为int,一个年份(p_year),数据类型为int,作为输入参数,并查询指定年份该教师在t_salaries表中的总薪资,然后通过输出参数(p_totalSalary)返回总薪资,返回类型为decimal(10, 2)。 答案: 1).CREATE TABLE t_departments ( departmentID INT PRIMARY KEY AUTO_INCREMENT, departmentName VARCHAR(100) NOT NULL); 2).INSERT INTO t_departments (departmentName) VALUES ('计算机系'), ('数学系'), ('文学系'), ('物理系'); 3).UPDATE t_salaries sJOIN t_teachers t ON s.teacherID = t.teacherID JOIN t_departments d ON t.departmentID = d.departmentID SET s.basicSalary = s.basicSalary + 200 WHERE d.departmentName = '计算机系' AND s.year = 2023; 4).UPDATE t_teachers SET isactive = 0 WHERE name = '郑姗璐'; 5).SELECT t.name AS 教师名称, t.gender AS 教师性别, t.age AS 教师年龄, c.courseName AS 课程名称 FROM t_teachers t LEFT JOIN t_courses c ON t.teacherID = c.teacherID; 6).SELECT t.name AS 教师名称, t.gender AS 教师性别, t.age AS 教师年龄,d.departmentName AS 院系名称, s.basicSalary AS 基本工资,s.bonus AS 奖金, s.totalSalary AS 总薪资FROM t_teachers tJOIN t_salaries s ON t.teacherID = s.teacherID JOIN t_departments d ON t.departmentID = d.departmentID WHERE t.isactive = 1 AND s.year = 2023; 7).CREATE VIEW view_info ASSELECT t.name AS 教师名称, t.gender AS 教师性别, t.age AS 教师年龄, d.departmentName AS 部门名称, s.basicSalary AS 基本薪资, s.year AS 年份 FROM t_teachers t JOIN t_salaries s ON t.teacherID = s.teacherIDJOIN t_departments d ON t.departmentID = d.departmentID WHERE t.isactive = 1; 8).update_teacherDELIMITER //CREATE TRIGGER update_teacherAFTER UPDATE ON t_teachersFOR EACH ROWBEGIN IF OLD.isactive = 1 AND NEW.isactive = 0 THEN DELETE FROM t_salaries WHERE teacherID = NEW.teacherID; END IF;END//DELIMITER ; 9).pro_getTotalSalaryDELIMITER //CREATE PROCEDURE pro_getTotalSalary( IN p_teacherID INT, IN p_year INT, OUT p_totalSalary DECIMAL(10,2))BEGIN SELECT totalSalary INTO p_totalSalary FROM t_salaries WHERE teacherID = p_teacherID AND year = p_year;END//DELIMITER ; 5.音乐数据库db_music包含了users用户表、songs歌曲表、user_song_stats用户歌曲统计表。 users用户表包含字段user_id(用户ID),username(用户名),password(用户密码),email(邮箱),created_date(创建日期)。 songs歌曲表包含字段song_id(歌曲ID),song_name(歌曲名称),singer_name(歌手姓名),album_name(专辑名称),genre(音乐类型),release_date(发行日期)。 user_song_stats用户歌曲统计表包含字段stat_id(统计ID),user_id(用户ID),song_id(歌曲ID),play_count(播放次数),total_play_time(播放时长,单位分钟)。 上述数据库及数据表已经创建,请按要求完成以下操作。 1).创建一个歌单表favorite_songs,字段如下: 字段名称 类型 长度 需求 favorite_id(喜好ID) int 主键,自增长 user_id(用户ID) int 不能为空 song_id(歌曲ID) int 不能为空 add_date(添加日期) date 2).在歌曲表中插入一条记录('告白气球', '周杰伦', '周杰伦的床边故事', '流行', '2016-06-24') 3).用户张三播放了晴天5次,听歌时长12分钟,请更新user_song_stats用户歌曲统计表中的记录。 4).歌曲Shape of You已下架,请删除对应的歌曲和统计记录。 5).查询播放次数最多的歌曲名称及其播放次数,列名分别为:name、count; 6).查询每个用户的总播放次数和总播放时间,列名分别为:用户名、播放次数、播放时间。 7).创建一个视图view_details,要求视图中显示出所有用户的用户名、歌曲名称、播放次数、播放时长。按播放次数升序排序; 8).创建一个名称为trg_songUpdate的触发器用来添加歌单表表中的记录,具体描述如下:当在user_song_stats用户歌曲统计表中更新记录之后,如果播放次数大于等于25,则向歌单表新增记录,日期默认为2024-10-01(不考虑任何特殊情况); 9).创建一个名称为pro_update的存储过程更新指定统计ID的数据,具体描述如下:这个存储过程接收一个统计ID(in_statID),数据类型为int,一个播放次数(in_count),数据类型为int作为输入参数。根据统计ID(in_statID)在user_song_stats用户歌曲统计表里更新播放次数(in_count);现调用存储过程,in_statID为4,in_count为25; 答案: 1).CREATE TABLE favorite_songs (favorite_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT NOT NULL,song_id INT NOT NULL,add_date DATE); 2).INSERT INTO songs (song_name, singer_name, album_name, genre, release_date) VALUES ('告白气球', '周杰伦', '周杰伦的床边故事', '流行', '2016-06-24'); 3).UPDATE user_song_stats SET play_count = play_count + 5, total_play_time = total_play_time + 12 WHERE user_id = (SELECT user_id FROM users WHERE username = '张三') AND song_id = (SELECT song_id FROM songs WHERE song_name = '晴天'); 4).DELETE FROM user_song_stats WHERE song_id = (SELECT song_id FROM songs WHERE song_name = 'Shape of You');DELETE FROM songs WHERE song_name = 'Shape of You'; 5).SELECT s.song_name AS name, SUM(us.play_count) AS count FROM user_song_stats us JOIN songs s ON us.song_id = s.song_id GROUP BY us.song_id ORDER BY count DESCLIMIT 1; 6).SELECT u.username AS 用户名,SUM(us.play_count) AS 播放次数,SUM(us.total_play_time) AS 播放时间FROM user_song_stats us JOIN users u ON us.user_id = u.user_id GROUP BY u.user_id; 7).view_detailsCREATE VIEW view_details ASSELECT u.username AS 用户名, s.song_name AS 歌曲名称,us.play_count AS 播放次数, us.total_play_time AS 播放时长FROM user_song_stats us JOIN users u ON us.user_id = u.user_id JOIN songs s ON us.song_id = s.song_id ORDER BY us.play_count ASC; 8).trg_songUpdateDELIMITER //CREATE TRIGGER trg_songUpdateAFTER UPDATE ON user_song_statsFOR EACH ROWBEGIN IF NEW.play_count >= 25 THENINSERT INTO favorite_songs (user_id, song_id, add_date) VALUES (NEW.user_id, NEW.song_id, '2024-10-01'); END IF;END//DELIMITER ; 9).DELIMITER //CREATE PROCEDURE pro_update( IN in_statID INT, IN in_count INT)BEGIN UPDATE user_song_stats SET play_count = in_count WHERE stat_id = in_statID;END//DELIMITER ; 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题一/db_library.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_library Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 21/10/2024 11:41:26 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for books -- ---------------------------- DROP TABLE IF EXISTS `books`; CREATE TABLE `books` ( `bookID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `author` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `isbn` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `publishedDate` date NULL DEFAULT NULL, `available` int(11), PRIMARY KEY (`bookID`) USING BTREE, UNIQUE INDEX `isbn`(`isbn`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of books -- ---------------------------- INSERT INTO `books` VALUES (1, '数据库系统概论', '王珊', '9787302123456', '2010-01-01', 1); INSERT INTO `books` VALUES (2, '操作系统原理', '汤小丹', '9787302567890', '2015-09-01', 1); INSERT INTO `books` VALUES (3, '编程珠玑', 'Jon Bentley', '9787115234567', '1989-09-15', 0); -- ---------------------------- -- Table structure for borrowrecords -- ---------------------------- DROP TABLE IF EXISTS `borrowrecords`; CREATE TABLE `borrowrecords` ( `borrowRecordID` int(11) NOT NULL AUTO_INCREMENT, `readerID` int(11) NULL DEFAULT NULL, `bookID` int(11) NULL DEFAULT NULL, `borrowDate` date NOT NULL, `dueDate` date NOT NULL, `returned` int(11) NULL DEFAULT 0, PRIMARY KEY (`borrowRecordID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of borrowrecords -- ---------------------------- INSERT INTO `borrowrecords` VALUES (1, 1, 1, '2024-09-16', '2024-09-21', 0); INSERT INTO `borrowrecords` VALUES (2, 2, 2, '2024-09-18', '2024-09-28', 0); INSERT INTO `borrowrecords` VALUES (3, 1, 3, '2024-09-11', '2024-09-19', 1); -- ---------------------------- -- Table structure for readers -- ---------------------------- DROP TABLE IF EXISTS `readers`; CREATE TABLE `readers` ( `readerID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `phoneNumber` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `registrationDate` date NULL DEFAULT NULL, PRIMARY KEY (`readerID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of readers -- ---------------------------- INSERT INTO `readers` VALUES (1, '张三', '12345678901', 'zhangsan@example.com', '2024-05-21'); INSERT INTO `readers` VALUES (2, '李四', '09876543210', 'lisi@example.com', '2024-05-20'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题三/db_insurance.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_insurance Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 31/10/2024 15:21:23 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_cars -- ---------------------------- DROP TABLE IF EXISTS `t_cars`; CREATE TABLE `t_cars` ( `carID` int(11) NOT NULL AUTO_INCREMENT, `customerID` int(11) NOT NULL, `licensePlate` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `brand` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `model` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `vin` varchar(17) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`carID`) USING BTREE, UNIQUE INDEX `vin`(`vin`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_cars -- ---------------------------- INSERT INTO `t_cars` VALUES (1, 1, '京A12345', '宝马', 'X5', '1HGCM82633A123456'); INSERT INTO `t_cars` VALUES (2, 1, '京A11545', '小米', 'su7', 'ACEGA599357892478'); INSERT INTO `t_cars` VALUES (3, 2, '沪B67890', '奔驰', 'S级', 'WDD22412345678901'); INSERT INTO `t_cars` VALUES (4, 3, '粤C24680', '奥迪', 'A6L', 'WAUZZZ4G5DN123456'); INSERT INTO `t_cars` VALUES (5, 4, '鄂A12587', '特斯拉', 'Model S', 'TSL20222408071567'); -- ---------------------------- -- Table structure for t_customers -- ---------------------------- DROP TABLE IF EXISTS `t_customers`; CREATE TABLE `t_customers` ( `customerID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `address` 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 t_customers -- ---------------------------- INSERT INTO `t_customers` VALUES (1, '张三', '13800138000', 'zhangsan@example.com', '北京市朝阳区'); INSERT INTO `t_customers` VALUES (2, '李四', '13900139000', 'lisi@example.com', '上海市黄浦区'); INSERT INTO `t_customers` VALUES (3, '王五', '13700137000', 'wangwu@example.com', '广州市天河区'); -- ---------------------------- -- Table structure for t_policies -- ---------------------------- DROP TABLE IF EXISTS `t_policies`; CREATE TABLE `t_policies` ( `policyID` int(11) NOT NULL AUTO_INCREMENT, `customerID` int(11) NOT NULL, `carID` int(11) NOT NULL, `policyNumber` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `startDate` date NULL DEFAULT NULL, `endDate` date NULL DEFAULT NULL, `premium` decimal(10, 2) NULL DEFAULT NULL, `coverageType` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`policyID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_policies -- ---------------------------- INSERT INTO `t_policies` VALUES (1, 1, 1, 'P123456', '2023-01-01', '2024-01-01', 8000.00, '全险'); INSERT INTO `t_policies` VALUES (2, 1, 2, 'P135687', '2023-06-01', '2024-01-01', 12000.00, '全险+保养'); INSERT INTO `t_policies` VALUES (3, 2, 3, 'P654321', '2023-02-15', '2024-02-15', 10000.00, '交强险+车损险'); INSERT INTO `t_policies` VALUES (4, 3, 4, 'P789456', '2023-03-10', '2024-03-10', 9500.00, '全险+玻璃险'); INSERT INTO `t_policies` VALUES (5, 1, 1, 'P123587', '2024-01-01', '2025-01-01', 8500.00, '全险'); INSERT INTO `t_policies` VALUES (6, 4, 5, 'P202408', '2023-08-01', '2024-08-01', 7500.00, '交强险'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题二/db_marathon.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_marathon Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 21/10/2024 11:58:15 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for marathonresults -- ---------------------------- DROP TABLE IF EXISTS `marathonresults`; CREATE TABLE `marathonresults` ( `resultId` int(11) NOT NULL AUTO_INCREMENT, `participantId` int(11) NULL DEFAULT NULL, `raceId` int(11) NULL DEFAULT NULL, `finishTime` time NOT NULL, `rank` int(11) NOT NULL, PRIMARY KEY (`resultId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of marathonresults -- ---------------------------- INSERT INTO `marathonresults` VALUES (1, 1, 1, '03:05:12', 7); INSERT INTO `marathonresults` VALUES (2, 1, 2, '03:07:25', 9); INSERT INTO `marathonresults` VALUES (3, 1, 3, '02:55:32', 5); INSERT INTO `marathonresults` VALUES (4, 2, 1, '03:20:45', 25); INSERT INTO `marathonresults` VALUES (5, 3, 2, '02:58:30', 4); INSERT INTO `marathonresults` VALUES (6, 3, 3, '03:15:00', 15); INSERT INTO `marathonresults` VALUES (7, 2, 4, '03:30:00', 30); -- ---------------------------- -- Table structure for participants -- ---------------------------- DROP TABLE IF EXISTS `participants`; CREATE TABLE `participants` ( `participantId` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int(11) NOT NULL, `gender` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `city` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `registrationDate` date NULL DEFAULT NULL, PRIMARY KEY (`participantId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of participants -- ---------------------------- INSERT INTO `participants` VALUES (1, '张三', 30, '男', '北京', 'zhangsan@ex.com', '13800000001', '2023-01-01'); INSERT INTO `participants` VALUES (2, '李四', 28, '女', '上海', 'lisi@ex.com', '13900000002', '2023-02-15'); INSERT INTO `participants` VALUES (3, '王五', 35, '男', '广州', 'wangwu@ex.com', '13600000003', '2023-03-10'); -- ---------------------------- -- Table structure for races -- ---------------------------- DROP TABLE IF EXISTS `races`; CREATE TABLE `races` ( `raceId` int(11) NOT NULL AUTO_INCREMENT, `raceName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `raceDate` date NOT NULL, `location` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`raceId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of races -- ---------------------------- INSERT INTO `races` VALUES (1, '2023北京马拉松', '2023-04-16', '北京'); INSERT INTO `races` VALUES (2, '2023上海马拉松', '2023-11-05', '上海'); INSERT INTO `races` VALUES (3, '2023广州马拉松', '2023-12-10', '广州'); INSERT INTO `races` VALUES (4, '2023深圳马拉松', '2023-10-22', '深圳'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题五/db_music.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_music Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 07/11/2024 17:16:33 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for songs -- ---------------------------- DROP TABLE IF EXISTS `songs`; CREATE TABLE `songs` ( `song_id` int(11) NOT NULL AUTO_INCREMENT, `song_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `singer_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `album_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `genre` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `release_date` date NULL DEFAULT NULL, PRIMARY KEY (`song_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of songs -- ---------------------------- INSERT INTO `songs` VALUES (1, '以父之名', '周杰伦', '叶惠美', '流行', '2003-07-31'); INSERT INTO `songs` VALUES (2, '晴天', '周杰伦', '叶惠美', '流行', '2003-07-31'); INSERT INTO `songs` VALUES (3, '东风破', '周杰伦', '叶惠美', '中国风', '2003-07-31'); INSERT INTO `songs` VALUES (4, '床边故事', '周杰伦', '周杰伦床边故事', '流行', '2016-06-24'); INSERT INTO `songs` VALUES (5, '前世情人', '周杰伦', '周杰伦床边故事', '流行', '2016-06-24'); INSERT INTO `songs` VALUES (6, '说走就走', '周杰伦', '周杰伦床边故事', '嘻哈', '2016-06-24'); INSERT INTO `songs` VALUES (7, '夜第七章', '周杰伦', '十一月的萧邦', '流行', '2005-11-01'); INSERT INTO `songs` VALUES (8, '一路向北', '周杰伦', '十一月的萧邦', '流行', '2005-11-01'); INSERT INTO `songs` VALUES (9, '逆鳞', '周杰伦', '十一月的萧邦', '嘻哈', '2005-11-01'); INSERT INTO `songs` VALUES (10, '漂移', '周杰伦', '十一月的萧邦', '摇滚', '2005-11-01'); INSERT INTO `songs` VALUES (11, 'Shape of You', 'Ed Sheeran', '÷ (Divide)', 'Pop', '2017-01-06'); INSERT INTO `songs` VALUES (12, 'Blinding Lights', 'The Weeknd', 'After Hours', 'Pop/R&B', '2020-03-06'); INSERT INTO `songs` VALUES (13, 'Levitating', 'Dua Lipa', 'Future Nostalgia', 'Pop/Dance', '2020-08-28'); INSERT INTO `songs` VALUES (14, 'Radioactive', 'Imagine Dragons', 'Night Visions', 'Alternative Rock/Pop', '2012-09-04'); -- ---------------------------- -- Table structure for user_song_stats -- ---------------------------- DROP TABLE IF EXISTS `user_song_stats`; CREATE TABLE `user_song_stats` ( `stat_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NULL DEFAULT NULL, `song_id` int(11) NULL DEFAULT NULL, `play_count` int(11) NULL DEFAULT 0, `total_play_time` int(11) NULL DEFAULT 0, PRIMARY KEY (`stat_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user_song_stats -- ---------------------------- INSERT INTO `user_song_stats` VALUES (1, 1, 1, 15, 90); INSERT INTO `user_song_stats` VALUES (2, 1, 2, 8, 48); INSERT INTO `user_song_stats` VALUES (3, 2, 2, 1, 3); INSERT INTO `user_song_stats` VALUES (4, 2, 3, 22, 132); INSERT INTO `user_song_stats` VALUES (5, 2, 5, 10, 60); INSERT INTO `user_song_stats` VALUES (6, 3, 6, 7, 42); INSERT INTO `user_song_stats` VALUES (7, 3, 8, 19, 114); INSERT INTO `user_song_stats` VALUES (8, 2, 8, 4, 15); INSERT INTO `user_song_stats` VALUES (9, 1, 9, 3, 18); INSERT INTO `user_song_stats` VALUES (10, 2, 10, 5, 30); INSERT INTO `user_song_stats` VALUES (11, 3, 11, 11, 66); INSERT INTO `user_song_stats` VALUES (12, 2, 12, 2, 12); -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `created_date` date NULL DEFAULT NULL, PRIMARY KEY (`user_id`) USING BTREE, UNIQUE INDEX `username`(`username`) USING BTREE, UNIQUE INDEX `email`(`email`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO `users` VALUES (1, '张三', 'password123', 'zhangsan@qq.com', '2023-01-01'); INSERT INTO `users` VALUES (2, '李四', 'securepwd456', 'lisi@google.com', '2023-10-02'); INSERT INTO `users` VALUES (3, '王五', 'mypassword789', 'wangwu@qq.com', '2023-08-03'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题四/db_teacher.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_teacher Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 01/11/2024 08:40:33 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_courses -- ---------------------------- DROP TABLE IF EXISTS `t_courses`; CREATE TABLE `t_courses` ( `courseID` int(11) NOT NULL AUTO_INCREMENT, `courseName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `teacherID` int(11) NULL DEFAULT NULL, PRIMARY KEY (`courseID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_courses -- ---------------------------- INSERT INTO `t_courses` VALUES (1, '数据结构', 1); INSERT INTO `t_courses` VALUES (2, '高等数学', 2); INSERT INTO `t_courses` VALUES (3, '中国古代文学', 3); INSERT INTO `t_courses` VALUES (4, '操作系统', 4); INSERT INTO `t_courses` VALUES (5, 'C语言', 5); INSERT INTO `t_courses` VALUES (6, '线性代数', 2); INSERT INTO `t_courses` VALUES (7, '量子力学', 3); -- ---------------------------- -- Table structure for t_salaries -- ---------------------------- DROP TABLE IF EXISTS `t_salaries`; CREATE TABLE `t_salaries` ( `salaryID` int(11) NOT NULL AUTO_INCREMENT, `teacherID` int(11) NULL DEFAULT NULL, `basicSalary` decimal(10, 2) NOT NULL, `bonus` decimal(10, 2) NULL DEFAULT 0.00, `totalSalary` decimal(10, 2) GENERATED ALWAYS AS ((`basicSalary` + `bonus`)) STORED NULL, `year` int(11) NOT NULL, PRIMARY KEY (`salaryID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_salaries -- ---------------------------- INSERT INTO `t_salaries` VALUES (1, 1, 8000.00, 2000.00, DEFAULT, 2023); INSERT INTO `t_salaries` VALUES (2, 2, 9000.00, 1500.00, DEFAULT, 2023); INSERT INTO `t_salaries` VALUES (3, 3, 12000.00, 3000.00, DEFAULT, 2023); INSERT INTO `t_salaries` VALUES (4, 1, 8200.00, 2100.00, DEFAULT, 2022); INSERT INTO `t_salaries` VALUES (5, 2, 8800.00, 1400.00, DEFAULT, 2022); INSERT INTO `t_salaries` VALUES (6, 3, 12200.00, 2900.00, DEFAULT, 2022); INSERT INTO `t_salaries` VALUES (7, 4, 5300.00, 1000.00, DEFAULT, 2023); INSERT INTO `t_salaries` VALUES (8, 5, 7500.00, 2400.00, DEFAULT, 2023); -- ---------------------------- -- Table structure for t_teachers -- ---------------------------- DROP TABLE IF EXISTS `t_teachers`; CREATE TABLE `t_teachers` ( `teacherID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) 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, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `phoneNumber` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `departmentID` int(11) NOT NULL, `isactive` int(11) NULL DEFAULT 1, PRIMARY KEY (`teacherID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_teachers -- ---------------------------- INSERT INTO `t_teachers` VALUES (1, '马庆存', '男', 45, 'zhangsan@example.com', '13800138000', 1, 1); INSERT INTO `t_teachers` VALUES (2, '娄妮野', '女', 32, 'lisi@example.com', '13900139000', 2, 1); INSERT INTO `t_teachers` VALUES (3, '陆沁琴', '男', 50, 'wangwu@example.com', NULL, 3, 0); INSERT INTO `t_teachers` VALUES (4, '柳毓影', '女', 28, NULL, NULL, 1, 1); INSERT INTO `t_teachers` VALUES (5, '郑姗璐', '女', 35, 'zhengshanlu@example.com', NULL, 1, 1); SET FOREIGN_KEY_CHECKS = 1; $ 《MySQL数据库基础》 期末复习卷(七) 时间:90分钟 总分:100分 班级 姓名 学号 成绩 一.数据库操作题(本大题共5小题,每小题20分,共100分) 1.图书管理数据库db_library包含了3个表,即读者表(readers)、书籍表(books)、借阅记录表(borrowRecords)。 读者表readers包含字段readerID(读者ID),name(读者姓名),phoneNumber(电话号码)、email(邮箱)、registrationDate(注册时间),主键是readerID 书籍表books包含字段bookID(书籍ID),title(书籍标题),author(作者),isbn(书号),publishedDate(出版日期),available(借阅状态:1:未借出,0:借出),主键是bookID 借阅阅记录表borrowRecords包含字段borrowRecordID(借阅记录ID),readerID(借阅读者ID),bookID(借阅书籍ID),borrowDate(借阅时间),dueDate(应还日期),returned(归还状态:1:归还,0:未归还),主键是borrowRecordID 上述数据库及数据表已经创建,请按要求完成以下操作。 1).修改books表的借阅状态默认为1 2).创建一个书籍预定表reservation,字段如下: 字段名称 类型 长度 需求 reservationID (预定编号) int 主键,自增长 readerID(读者ID) int 不能为空 bookID(书籍ID) int 不能为空 date(预定日期) date status(预定状态) varchar 10 默认值为待处理 3).在书籍表中新增一条记录,书籍标题:追风筝的人,作者:卡勒德·胡赛尼,书号:9787530214313,出版日期:2006-05-01,借阅状态:1。 4).请用sql语句找出读者ID为2借阅的所有书籍,要求显示书籍标题,作者,书号,出版日期,借阅状态,借阅时间。 5、请用sql语句查询出当前有书籍尚未归还的读者。要求显示读者姓名,电话号码,邮箱,借阅时间,应还日期。 6、创建一个视图view_readerHistory要求显示读者的借阅历史记录,要求显示读者姓名,书籍标题,书号,借阅时间,应还日期,是否归还。 7、创建一个触发器update_books,该触发器将在borrowrecords表插入操作之后被触发。触发器需要执行以下操作:根据新增的书籍ID,更新书籍表books中借阅状态为0(借出); 8、创建一个存储过程update_borrowrecords,该存储过程接收一个借阅记录ID(p_borrowRecordID)和一个归还状态(p_returned)作为输入参数,数据类型均为int,根据输入的借阅记录ID和归还状态更新借阅记录表borrowRecords中的记录; 2.马拉松数据库db_marathon包含了3个表,即参赛者表(participants)、比赛表(races)、成绩表(marathonresults)。 参赛者表participants包含字段participantId(参赛者ID),name(姓名),age(年龄)、gender(性别)、city(城市)、email(电子邮件)、phone(电话)、registrationDate(注册日期),主键是participantId 比赛表races包含字段raceId(比赛ID)、raceName(比赛名称)、raceDate(比赛日期)、location(比赛地点),主键是raceId 马拉松成绩表marathonResults包含字段resultId(马拉松成绩ID),participantId(参赛者ID),raceId(比赛ID),finishTime(完成时间),rank(排名),主键是resultId 上述数据库及数据表已经创建,请按要求完成以下操作。 1).修改participants表gender字段设置只能存储男或者女。 2).创建一个训练表tranning,字段如下: 字段名称 类型 长度 需求 sessionId(预定编号) int 主键,自增长,不能为空 participantId(参赛者ID) int 不能为空 sessionDate(训练日期) date duration(训练时长) int location(训练地点) varchar 100 3).为李四插入一条比赛记录,李四在2023年上海马拉松的完成时间是02:22:01,排名第10。 4).请用sql语句查询所有参赛者及其参加的比赛名称和完成时间,要求显示姓名,性别,年龄,比赛名称,完成时间,排名。排名按升序排序。 5).请用sql语句查询出张三在马拉松比赛中最好的一次排名记录。要求显示姓名,比赛名称,完成时间,排名。 6、创建一个视图view_grade显示参赛人员的马拉松记录数据,要求显示姓名,比赛名称,完成时间,排名。其中排名按升序排序,完成时间按降序排序。 7、创建一个触发器trigger_delete,该触发器将在marathonresults表新增操作之前被触发。触发器需要执行以下操作:检查参赛者信息是否存在,如果不存在,则阻止插入操作并抛出错误信息参赛者信息不存在！; 8、创建一个存储过程pro_select,该存储过程接收一个参赛者ID(p_participantId)作为输入参数,数据类型为INT,根据输入的参赛者ID,查询出参赛者的姓名,完成时间,排名; 3.汽车保险系统数据库db_insurance包含了t_customers客户表、t_cars汽车表、t_policies保单表。 t_customers客户表包含字段customerID(客户ID),name(客户姓名),phone(电话号码),email(邮箱),address(地址)。 t_cars汽车表包含字段carID(汽车ID),customerID(客户ID),licensePlate(汽车牌照),brand(汽车品牌),model(汽车型号),vin(车辆识别号)。 t_policies保单表包含字段policyID(保单ID),customerID(客户ID),carID(汽车ID),policyNumber(保单号),startDate(开始时间),endDate(结束时间),premium(保单费用),coverageType(保单类型)。 上述数据库及数据表已经创建,请按要求完成以下操作。 1).创建一个保养记录表t_maintenance,字段如下: 字段名称 类型 长度 需求 recordID(记录编号) int 主键,自增长 carID(汽车ID) int 不能为空 date(保养日期) date mileage(里程数) int type(保养类型) varchar 100 details(保养记录) text cost(保养费用) decimal 10,2 2).在客户表中插入一条客户数据('王强', '15535874564', 'wangqiang@example.com', '上海市浦东新区') 3).将车牌号为鄂A12587的汽车型号修改为Model Y 4).将t_policies表中policyNumber字段修改为唯一约束 5).查询所有客户的保单信息,按保单费用升序排序,要求显示客户姓名,保单类型,开始时间,结束时间,保单费用 6).查询客户张三拥有的汽车数量和保单总金额,列名分别为汽车数量,保单总金额 7).创建一个视图view_details,要求视图中显示出所有客户的姓名、汽车品牌、汽车型号、保单类型、结束时间,查询结果按照结束时间升序,按照汽车品牌降序; 8).创建一个名称为trigger_delete的触发器用来自动删除数据,具体描述如下:当一个客户从t_customers表中删除后,与该客户相关的所有汽车记录和保单记录也会被相应地删除 9).创建一个名称为pro_select的存储过程查询指定客户的车辆信息,具体描述如下:这个存储过程接收一个客户姓名(in_name)作为输入参数,数据类型为varchar(100),查询出客户名下的汽车信息,要求显示汽车牌照、汽车品牌、汽车型号 4.教师系统数据库db_teacher包含了t_teachers教师表、t_courses课程表、t_salaries薪资表。 t_teachers教师表包含字段 teacherID(教师ID),name(教师名称),gender(教师性别),age(教师年龄),email(邮箱),phoneNumber(联系电话),departmentID(部门ID),isactive(是否在职,1:在职,0:离职)。 t_courses课程表包含字段courseID(课程ID),courseName(课程名称),teacherID(教师ID)。 t_salaries薪资表包含字段salaryID(薪资ID),teacherID(教师ID),basicSalary(基本薪资),bonus(奖金),totalSalary(总薪资),year(年份)。 上述数据库及数据表已经创建,请按要求完成以下操作。 1).创建一个部门表t_departments,字段如下: 字段名称 类型 长度 需求 departmentID(部门ID) int 主键,自增长 departmentName(部门名称) varchar 100 不能为空 2).在部门表中插入四条记录,部门名称分别为计算机系,数学系,文学系,物理系。 3).将计算机系部门2023年的基本薪资整体上调200。 4).郑姗璐老师已经离职,请更新她的在职状态。 5).查询每位教师的课程信息,要求显示教师名称、教师性别、教师年龄、课程名称。 6).查询2023年在职教师的薪资信息,要求显示教师名称、教师性别、教师年龄、院系名称、基本工资、奖金、总薪资。 7).创建一个视图view_info,要求视图中显示出所有在职教师的教师名称、教师性别、教师年龄、部门名称、基本薪资、年份。 8).创建一个名称为update_teacher的触发器用来删除薪资表中的记录,具体描述如下:当在t_teachers表中更新记录之后,如果教师的在职状态从在职变成离职(1→0),则删除t_salaries薪资表对应的数据。 9).创建一个名称为pro_getTotalSalary的存储过程计算指定教师在该年份的总薪资,具体描述如下:这个存储过程接收一个教师ID(p_teacherID),数据类型为int,一个年份(p_year),数据类型为int,作为输入参数,并查询指定年份该教师在t_salaries表中的总薪资,然后通过输出参数(p_totalSalary)返回总薪资,返回类型为decimal(10, 2)。 5.音乐数据库db_music包含了users用户表、songs歌曲表、user_song_stats用户歌曲统计表。 users用户表包含字段user_id(用户ID),username(用户名),password(用户密码),email(邮箱),created_date(创建日期)。 songs歌曲表包含字段song_id(歌曲ID),song_name(歌曲名称),singer_name(歌手姓名),album_name(专辑名称),genre(音乐类型),release_date(发行日期)。 user_song_stats用户歌曲统计表包含字段stat_id(统计ID),user_id(用户ID),song_id(歌曲ID),play_count(播放次数),total_play_time(播放时长,单位分钟)。 上述数据库及数据表已经创建,请按要求完成以下操作。 1).创建一个歌单表favorite_songs,字段如下: 字段名称 类型 长度 需求 favorite_id(喜好ID) int 主键,自增长 user_id(用户ID) int 不能为空 song_id(歌曲ID) int 不能为空 add_date(添加日期) date 2).在歌曲表中插入一条记录('告白气球', '周杰伦', '周杰伦的床边故事', '流行', '2016-06-24') 3).用户张三播放了晴天5次,听歌时长12分钟,请更新user_song_stats用户歌曲统计表中的记录。 4).歌曲Shape of You已下架,请删除对应的歌曲和统计记录。 5).查询播放次数最多的歌曲名称及其播放次数,列名分别为:name、count; 6).查询每个用户的总播放次数和总播放时间,列名分别为:用户名、播放次数、播放时间。 7).创建一个视图view_details,要求视图中显示出所有用户的用户名、歌曲名称、播放次数、播放时长。按播放次数升序排序; 8).创建一个名称为trg_songUpdate的触发器用来添加歌单表表中的记录,具体描述如下:当在user_song_stats用户歌曲统计表中更新记录之后,如果播放次数大于等于25,则向歌单表新增记录,日期默认为2024-10-01(不考虑任何特殊情况); 9).创建一个名称为pro_update的存储过程更新指定统计ID的数据,具体描述如下:这个存储过程接收一个统计ID(in_statID),数据类型为int,一个播放次数(in_count),数据类型为int作为输入参数。根据统计ID(in_statID)在user_song_stats用户歌曲统计表里更新播放次数(in_count);现调用存储过程,in_statID为4,in_count为25; 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题一/db_library.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_library Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 21/10/2024 11:41:26 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for books -- ---------------------------- DROP TABLE IF EXISTS `books`; CREATE TABLE `books` ( `bookID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `author` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `isbn` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `publishedDate` date NULL DEFAULT NULL, `available` int(11), PRIMARY KEY (`bookID`) USING BTREE, UNIQUE INDEX `isbn`(`isbn`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of books -- ---------------------------- INSERT INTO `books` VALUES (1, '数据库系统概论', '王珊', '9787302123456', '2010-01-01', 1); INSERT INTO `books` VALUES (2, '操作系统原理', '汤小丹', '9787302567890', '2015-09-01', 1); INSERT INTO `books` VALUES (3, '编程珠玑', 'Jon Bentley', '9787115234567', '1989-09-15', 0); -- ---------------------------- -- Table structure for borrowrecords -- ---------------------------- DROP TABLE IF EXISTS `borrowrecords`; CREATE TABLE `borrowrecords` ( `borrowRecordID` int(11) NOT NULL AUTO_INCREMENT, `readerID` int(11) NULL DEFAULT NULL, `bookID` int(11) NULL DEFAULT NULL, `borrowDate` date NOT NULL, `dueDate` date NOT NULL, `returned` int(11) NULL DEFAULT 0, PRIMARY KEY (`borrowRecordID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of borrowrecords -- ---------------------------- INSERT INTO `borrowrecords` VALUES (1, 1, 1, '2024-09-16', '2024-09-21', 0); INSERT INTO `borrowrecords` VALUES (2, 2, 2, '2024-09-18', '2024-09-28', 0); INSERT INTO `borrowrecords` VALUES (3, 1, 3, '2024-09-11', '2024-09-19', 1); -- ---------------------------- -- Table structure for readers -- ---------------------------- DROP TABLE IF EXISTS `readers`; CREATE TABLE `readers` ( `readerID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `phoneNumber` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `registrationDate` date NULL DEFAULT NULL, PRIMARY KEY (`readerID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of readers -- ---------------------------- INSERT INTO `readers` VALUES (1, '张三', '12345678901', 'zhangsan@example.com', '2024-05-21'); INSERT INTO `readers` VALUES (2, '李四', '09876543210', 'lisi@example.com', '2024-05-20'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题三/db_insurance.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_insurance Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 31/10/2024 15:21:23 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_cars -- ---------------------------- DROP TABLE IF EXISTS `t_cars`; CREATE TABLE `t_cars` ( `carID` int(11) NOT NULL AUTO_INCREMENT, `customerID` int(11) NOT NULL, `licensePlate` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `brand` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `model` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `vin` varchar(17) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`carID`) USING BTREE, UNIQUE INDEX `vin`(`vin`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_cars -- ---------------------------- INSERT INTO `t_cars` VALUES (1, 1, '京A12345', '宝马', 'X5', '1HGCM82633A123456'); INSERT INTO `t_cars` VALUES (2, 1, '京A11545', '小米', 'su7', 'ACEGA599357892478'); INSERT INTO `t_cars` VALUES (3, 2, '沪B67890', '奔驰', 'S级', 'WDD22412345678901'); INSERT INTO `t_cars` VALUES (4, 3, '粤C24680', '奥迪', 'A6L', 'WAUZZZ4G5DN123456'); INSERT INTO `t_cars` VALUES (5, 4, '鄂A12587', '特斯拉', 'Model S', 'TSL20222408071567'); -- ---------------------------- -- Table structure for t_customers -- ---------------------------- DROP TABLE IF EXISTS `t_customers`; CREATE TABLE `t_customers` ( `customerID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `address` 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 t_customers -- ---------------------------- INSERT INTO `t_customers` VALUES (1, '张三', '13800138000', 'zhangsan@example.com', '北京市朝阳区'); INSERT INTO `t_customers` VALUES (2, '李四', '13900139000', 'lisi@example.com', '上海市黄浦区'); INSERT INTO `t_customers` VALUES (3, '王五', '13700137000', 'wangwu@example.com', '广州市天河区'); -- ---------------------------- -- Table structure for t_policies -- ---------------------------- DROP TABLE IF EXISTS `t_policies`; CREATE TABLE `t_policies` ( `policyID` int(11) NOT NULL AUTO_INCREMENT, `customerID` int(11) NOT NULL, `carID` int(11) NOT NULL, `policyNumber` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `startDate` date NULL DEFAULT NULL, `endDate` date NULL DEFAULT NULL, `premium` decimal(10, 2) NULL DEFAULT NULL, `coverageType` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`policyID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_policies -- ---------------------------- INSERT INTO `t_policies` VALUES (1, 1, 1, 'P123456', '2023-01-01', '2024-01-01', 8000.00, '全险'); INSERT INTO `t_policies` VALUES (2, 1, 2, 'P135687', '2023-06-01', '2024-01-01', 12000.00, '全险+保养'); INSERT INTO `t_policies` VALUES (3, 2, 3, 'P654321', '2023-02-15', '2024-02-15', 10000.00, '交强险+车损险'); INSERT INTO `t_policies` VALUES (4, 3, 4, 'P789456', '2023-03-10', '2024-03-10', 9500.00, '全险+玻璃险'); INSERT INTO `t_policies` VALUES (5, 1, 1, 'P123587', '2024-01-01', '2025-01-01', 8500.00, '全险'); INSERT INTO `t_policies` VALUES (6, 4, 5, 'P202408', '2023-08-01', '2024-08-01', 7500.00, '交强险'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题二/db_marathon.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_marathon Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 21/10/2024 11:58:15 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for marathonresults -- ---------------------------- DROP TABLE IF EXISTS `marathonresults`; CREATE TABLE `marathonresults` ( `resultId` int(11) NOT NULL AUTO_INCREMENT, `participantId` int(11) NULL DEFAULT NULL, `raceId` int(11) NULL DEFAULT NULL, `finishTime` time NOT NULL, `rank` int(11) NOT NULL, PRIMARY KEY (`resultId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of marathonresults -- ---------------------------- INSERT INTO `marathonresults` VALUES (1, 1, 1, '03:05:12', 7); INSERT INTO `marathonresults` VALUES (2, 1, 2, '03:07:25', 9); INSERT INTO `marathonresults` VALUES (3, 1, 3, '02:55:32', 5); INSERT INTO `marathonresults` VALUES (4, 2, 1, '03:20:45', 25); INSERT INTO `marathonresults` VALUES (5, 3, 2, '02:58:30', 4); INSERT INTO `marathonresults` VALUES (6, 3, 3, '03:15:00', 15); INSERT INTO `marathonresults` VALUES (7, 2, 4, '03:30:00', 30); -- ---------------------------- -- Table structure for participants -- ---------------------------- DROP TABLE IF EXISTS `participants`; CREATE TABLE `participants` ( `participantId` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `age` int(11) NOT NULL, `gender` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `city` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `registrationDate` date NULL DEFAULT NULL, PRIMARY KEY (`participantId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of participants -- ---------------------------- INSERT INTO `participants` VALUES (1, '张三', 30, '男', '北京', 'zhangsan@ex.com', '13800000001', '2023-01-01'); INSERT INTO `participants` VALUES (2, '李四', 28, '女', '上海', 'lisi@ex.com', '13900000002', '2023-02-15'); INSERT INTO `participants` VALUES (3, '王五', 35, '男', '广州', 'wangwu@ex.com', '13600000003', '2023-03-10'); -- ---------------------------- -- Table structure for races -- ---------------------------- DROP TABLE IF EXISTS `races`; CREATE TABLE `races` ( `raceId` int(11) NOT NULL AUTO_INCREMENT, `raceName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `raceDate` date NOT NULL, `location` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`raceId`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of races -- ---------------------------- INSERT INTO `races` VALUES (1, '2023北京马拉松', '2023-04-16', '北京'); INSERT INTO `races` VALUES (2, '2023上海马拉松', '2023-11-05', '上海'); INSERT INTO `races` VALUES (3, '2023广州马拉松', '2023-12-10', '广州'); INSERT INTO `races` VALUES (4, '2023深圳马拉松', '2023-10-22', '深圳'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题五/db_music.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_music Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 07/11/2024 17:16:33 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for songs -- ---------------------------- DROP TABLE IF EXISTS `songs`; CREATE TABLE `songs` ( `song_id` int(11) NOT NULL AUTO_INCREMENT, `song_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `singer_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `album_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `genre` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `release_date` date NULL DEFAULT NULL, PRIMARY KEY (`song_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of songs -- ---------------------------- INSERT INTO `songs` VALUES (1, '以父之名', '周杰伦', '叶惠美', '流行', '2003-07-31'); INSERT INTO `songs` VALUES (2, '晴天', '周杰伦', '叶惠美', '流行', '2003-07-31'); INSERT INTO `songs` VALUES (3, '东风破', '周杰伦', '叶惠美', '中国风', '2003-07-31'); INSERT INTO `songs` VALUES (4, '床边故事', '周杰伦', '周杰伦床边故事', '流行', '2016-06-24'); INSERT INTO `songs` VALUES (5, '前世情人', '周杰伦', '周杰伦床边故事', '流行', '2016-06-24'); INSERT INTO `songs` VALUES (6, '说走就走', '周杰伦', '周杰伦床边故事', '嘻哈', '2016-06-24'); INSERT INTO `songs` VALUES (7, '夜第七章', '周杰伦', '十一月的萧邦', '流行', '2005-11-01'); INSERT INTO `songs` VALUES (8, '一路向北', '周杰伦', '十一月的萧邦', '流行', '2005-11-01'); INSERT INTO `songs` VALUES (9, '逆鳞', '周杰伦', '十一月的萧邦', '嘻哈', '2005-11-01'); INSERT INTO `songs` VALUES (10, '漂移', '周杰伦', '十一月的萧邦', '摇滚', '2005-11-01'); INSERT INTO `songs` VALUES (11, 'Shape of You', 'Ed Sheeran', '÷ (Divide)', 'Pop', '2017-01-06'); INSERT INTO `songs` VALUES (12, 'Blinding Lights', 'The Weeknd', 'After Hours', 'Pop/R&B', '2020-03-06'); INSERT INTO `songs` VALUES (13, 'Levitating', 'Dua Lipa', 'Future Nostalgia', 'Pop/Dance', '2020-08-28'); INSERT INTO `songs` VALUES (14, 'Radioactive', 'Imagine Dragons', 'Night Visions', 'Alternative Rock/Pop', '2012-09-04'); -- ---------------------------- -- Table structure for user_song_stats -- ---------------------------- DROP TABLE IF EXISTS `user_song_stats`; CREATE TABLE `user_song_stats` ( `stat_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NULL DEFAULT NULL, `song_id` int(11) NULL DEFAULT NULL, `play_count` int(11) NULL DEFAULT 0, `total_play_time` int(11) NULL DEFAULT 0, PRIMARY KEY (`stat_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user_song_stats -- ---------------------------- INSERT INTO `user_song_stats` VALUES (1, 1, 1, 15, 90); INSERT INTO `user_song_stats` VALUES (2, 1, 2, 8, 48); INSERT INTO `user_song_stats` VALUES (3, 2, 2, 1, 3); INSERT INTO `user_song_stats` VALUES (4, 2, 3, 22, 132); INSERT INTO `user_song_stats` VALUES (5, 2, 5, 10, 60); INSERT INTO `user_song_stats` VALUES (6, 3, 6, 7, 42); INSERT INTO `user_song_stats` VALUES (7, 3, 8, 19, 114); INSERT INTO `user_song_stats` VALUES (8, 2, 8, 4, 15); INSERT INTO `user_song_stats` VALUES (9, 1, 9, 3, 18); INSERT INTO `user_song_stats` VALUES (10, 2, 10, 5, 30); INSERT INTO `user_song_stats` VALUES (11, 3, 11, 11, 66); INSERT INTO `user_song_stats` VALUES (12, 2, 12, 2, 12); -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `created_date` date NULL DEFAULT NULL, PRIMARY KEY (`user_id`) USING BTREE, UNIQUE INDEX `username`(`username`) USING BTREE, UNIQUE INDEX `email`(`email`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO `users` VALUES (1, '张三', 'password123', 'zhangsan@qq.com', '2023-01-01'); INSERT INTO `users` VALUES (2, '李四', 'securepwd456', 'lisi@google.com', '2023-10-02'); INSERT INTO `users` VALUES (3, '王五', 'mypassword789', 'wangwu@qq.com', '2023-08-03'); SET FOREIGN_KEY_CHECKS = 1; 期末复习卷《MySQL数据库基础》（上海科学普及出版社）（七）（素材）/题四/db_teacher.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_teacher Target Server Type : MySQL Target Server Version : 50743 (5.7.43-log) File Encoding : 65001 Date: 01/11/2024 08:40:33 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_courses -- ---------------------------- DROP TABLE IF EXISTS `t_courses`; CREATE TABLE `t_courses` ( `courseID` int(11) NOT NULL AUTO_INCREMENT, `courseName` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `teacherID` int(11) NULL DEFAULT NULL, PRIMARY KEY (`courseID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_courses -- ---------------------------- INSERT INTO `t_courses` VALUES (1, '数据结构', 1); INSERT INTO `t_courses` VALUES (2, '高等数学', 2); INSERT INTO `t_courses` VALUES (3, '中国古代文学', 3); INSERT INTO `t_courses` VALUES (4, '操作系统', 4); INSERT INTO `t_courses` VALUES (5, 'C语言', 5); INSERT INTO `t_courses` VALUES (6, '线性代数', 2); INSERT INTO `t_courses` VALUES (7, '量子力学', 3); -- ---------------------------- -- Table structure for t_salaries -- ---------------------------- DROP TABLE IF EXISTS `t_salaries`; CREATE TABLE `t_salaries` ( `salaryID` int(11) NOT NULL AUTO_INCREMENT, `teacherID` int(11) NULL DEFAULT NULL, `basicSalary` decimal(10, 2) NOT NULL, `bonus` decimal(10, 2) NULL DEFAULT 0.00, `totalSalary` decimal(10, 2) GENERATED ALWAYS AS ((`basicSalary` + `bonus`)) STORED NULL, `year` int(11) NOT NULL, PRIMARY KEY (`salaryID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_salaries -- ---------------------------- INSERT INTO `t_salaries` VALUES (1, 1, 8000.00, 2000.00, DEFAULT, 2023); INSERT INTO `t_salaries` VALUES (2, 2, 9000.00, 1500.00, DEFAULT, 2023); INSERT INTO `t_salaries` VALUES (3, 3, 12000.00, 3000.00, DEFAULT, 2023); INSERT INTO `t_salaries` VALUES (4, 1, 8200.00, 2100.00, DEFAULT, 2022); INSERT INTO `t_salaries` VALUES (5, 2, 8800.00, 1400.00, DEFAULT, 2022); INSERT INTO `t_salaries` VALUES (6, 3, 12200.00, 2900.00, DEFAULT, 2022); INSERT INTO `t_salaries` VALUES (7, 4, 5300.00, 1000.00, DEFAULT, 2023); INSERT INTO `t_salaries` VALUES (8, 5, 7500.00, 2400.00, DEFAULT, 2023); -- ---------------------------- -- Table structure for t_teachers -- ---------------------------- DROP TABLE IF EXISTS `t_teachers`; CREATE TABLE `t_teachers` ( `teacherID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) 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, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `phoneNumber` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `departmentID` int(11) NOT NULL, `isactive` int(11) NULL DEFAULT 1, PRIMARY KEY (`teacherID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_teachers -- ---------------------------- INSERT INTO `t_teachers` VALUES (1, '马庆存', '男', 45, 'zhangsan@example.com', '13800138000', 1, 1); INSERT INTO `t_teachers` VALUES (2, '娄妮野', '女', 32, 'lisi@example.com', '13900139000', 2, 1); INSERT INTO `t_teachers` VALUES (3, '陆沁琴', '男', 50, 'wangwu@example.com', NULL, 3, 0); INSERT INTO `t_teachers` VALUES (4, '柳毓影', '女', 28, NULL, NULL, 1, 1); INSERT INTO `t_teachers` VALUES (5, '郑姗璐', '女', 35, 'zhengshanlu@example.com', NULL, 1, 1); SET FOREIGN_KEY_CHECKS = 1; $