期末复习卷《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).创建出版社表publisher,字段如下: pub_id(出版社编号) int 主键自增长, pub_name(出版社名称) varchar(100) 非空, address(地址) varchar(200), tel(联系电话) varchar(20) 2).向books表中插入一条图书数据:书籍标题《活着》,作者余华,书号9787506365437,出版日期1993-11-01,借阅状态1 3).将读者“李华”的邮箱修改为 lihua@163.com 4).删除所有从未借阅过书籍的读者记录 5).查询当前借阅次数最多的前5本书,显示:书籍标题、作者、借阅次数 6).查询每位读者的逾期未还书籍数量,显示:读者姓名、逾期数量(逾期指应还日期小于当前日期且未归还) 7).创建视图v_reader_borrow_status,显示当前未归还借阅信息:读者姓名、书籍标题、借阅时间、应还日期、逾期天数 8).创建触发器trg_prevent_borrow,当向borrowrecords表插入借阅记录时,检查该读者是否有逾期未还书籍,如有则阻止借阅 9).创建存储过程proc_return_book,输入参数为借阅记录ID,实现还书操作(更新归还状态、更新书籍借阅状态、计算逾期罚款) 答案: 1).CREATE TABLE publisher ( pub_id INT PRIMARY KEY AUTO_INCREMENT, pub_name VARCHAR(100) NOT NULL, address VARCHAR(200), tel VARCHAR(20)); 2).INSERT INTO books (title, author, isbn, publishedDate, available) VALUES ('活着', '余华', '9787506365437', '1993-11-01', 1);-- 3). 将读者"李华"的邮箱修改为lihua@163.comUPDATE readers SET email = 'lihua@163.com' WHERE name = '李华'; 4).DELETE FROM readers WHERE readerID NOT IN (SELECT DISTINCT readerID FROM borrowRecords); 5).SELECT b.title AS 书籍标题, b.author AS 作者, COUNT(br.borrowRecordID) AS 借阅次数FROM books b JOIN borrowRecords br ON b.bookID = br.bookID GROUP BY b.bookID ORDER BY 借阅次数 DESC LIMIT 5; 6).SELECT r.name AS 读者姓名, COUNT(br.borrowRecordID) AS 逾期数量FROM readers rJOIN borrowRecords br ON r.readerID = br.readerID WHERE br.returned = 0 AND br.dueDate < CURDATE() GROUP BY r.readerID; 7).CREATE VIEW v_reader_borrow_status ASSELECT r.name AS 读者姓名, b.title AS 书籍标题,br.borrowDate AS 借阅时间, br.dueDate AS 应还日期, DATEDIFF(CURDATE(), br.dueDate) AS 逾期天数FROM borrowRecords br JOIN readers r ON br.readerID = r.readerID JOIN books b ON br.bookID = b.bookID WHERE br.returned = 0; 8).DELIMITER //CREATE TRIGGER trg_prevent_borrow BEFORE INSERT ON borrowRecordsFOR EACH ROWBEGIN DECLARE overdue_count INT;SELECT COUNT(*) INTO overdue_count FROM borrowRecords WHERE readerID = NEW.readerID AND returned = 0 AND dueDate < CURDATE();IF overdue_count > 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该读者有逾期未还书籍，无法借阅'; END IF;END//DELIMITER ; 9).DELIMITER //CREATE PROCEDURE proc_return_book(IN p_borrowRecordID INT) BEGIN DECLARE v_bookID INT;DECLARE v_dueDate DATE;DECLARE v_fine DECIMAL(10,2);SELECT bookID, dueDate INTO v_bookID, v_dueDate FROM borrowRecords WHERE borrowRecordID = p_borrowRecordID;IF v_dueDate < CURDATE() THEN SET v_fine = DATEDIFF(CURDATE(), v_dueDate) * 0.5; ELSE SET v_fine = 0; END IF; UPDATE borrowRecords SET returned = 1 WHERE borrowRecordID = p_borrowRecordID; UPDATE books SET available = 1 WHERE bookID = v_bookID; SELECT v_fine AS 逾期罚款;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).创建报名表registrations,字段如下: reg_id(报名编号) int 主键自增长, participant_id(参赛者ID) int 非空, race_id(比赛ID) int 非空, reg_date(报名日期) date, status(报名状态) varchar(20) 默认'已报名' 2).向races表中插入一条比赛数据:比赛名称“广州马拉松”,比赛日期2025-12-10,比赛地点“广州市” 3).将参赛者王芳的年龄修改为30岁 4).删除所有未参赛(无成绩记录)的参赛者报名信息 5).查询每个年龄段(18-25岁、26-35岁、36-45岁、46岁以上)的参赛者人数,显示:年龄段、人数 6).查询每个比赛的平均完成时间,显示:比赛名称、平均完成时间(格式:HH:MM:SS) 7).创建视图v_city_rank,显示每个城市参赛者的最好成绩排名:城市名称、选手姓名、完成时间、城市内排名 8).创建触发器trg_check_registration_limit,当向registrations 表插入报名记录时,检查该比赛报名人数是否超过1000人,如超过则拒绝 9).创建存储过程proc_gender_avg_time,输入比赛ID,返回该比赛男女选手的平均完成时间 答案: 1).CREATE TABLE registrations ( reg_id INT PRIMARY KEY AUTO_INCREMENT, participant_id INT NOT NULL, race_id INT NOT NULL, reg_date DATE, status VARCHAR(20) DEFAULT '已报名'); 2).INSERT INTO races (raceName, raceDate, location) VALUES ('广州马拉松', '2025-12-10', '广州市'); 3).UPDATE participants SET age = 30 WHERE name = '王芳'; 4).DELETE FROM registrations WHERE participant_id NOT IN (SELECT DISTINCT participantId FROM marathonResults); 5).SELECT CASE WHEN age BETWEEN 18 AND 25 THEN '18-25岁'WHEN age BETWEEN 26 AND 35 THEN '26-35岁' WHEN age BETWEEN 36 AND 45 THEN '36-45岁' ELSE '46岁以上' END AS 年龄段,COUNT(*) AS 人数 FROM participants GROUP BY 年龄段; 6).SELECT r.raceName AS 比赛名称, SEC_TO_TIME(AVG(TIME_TO_SEC(m.finishTime))) AS 平均完成时间FROM marathonResults m JOIN races r ON m.raceId = r.raceId WHERE m.finishTime IS NOT NULL GROUP BY r.raceId; 7).CREATE VIEW v_city_rank ASSELECT city AS 城市名称, name AS 选手姓名, finishTime AS 完成时间,RANK() OVER (PARTITION BY city ORDER BY finishTime ASC) AS 城市内排名FROM participants p JOIN marathonResults m ON p.participantId = m.participantId; 8).DELIMITER //CREATE TRIGGER trg_check_registration_limitBEFORE INSERT ON registrationsFOR EACH ROWBEGIN DECLARE reg_count INT; SELECT COUNT(*) INTO reg_count FROM registrations WHERE race_id = NEW.race_id; IF reg_count >= 1000 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该比赛报名人数已超过1000人，无法报名'; END IF;END//DELIMITER ; 9).eDELIMITER //CREATE PROCEDURE proc_gender_avg_time(IN p_raceId INT)BEGIN SELECT p.gender AS 性别, SEC_TO_TIME(AVG(TIME_TO_SEC(m.finishTime))) AS 平均完成时间 FROM marathonResults m JOIN participants p ON m.participantId = p.participantId WHERE m.raceId = p_raceId AND m.finishTime IS NOT NULL GROUP BY p.gender;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_renewal,字段如下: renewal_id(续保编号) int 主键自增长, policy_id(保单编号) int 非空, renewal_date(续保日期) date, new_premium(新保费) decimal(10,2), new_end_date(新结束时间) date 2).向t_cars表中插入一条汽车数据:客户ID为C001,车牌号鄂B12345,品牌丰田,型号凯美瑞,车辆识别号LFV3A23K9P3123456 3).将所有即将到期(7天内到期)的保单费用打9折 4).删除所有没有保单的汽车信息 5).查询每个保险类型的保单数量和总保费,显示:保险类型、保单数量、总保费 6).查询每个客户名下车辆数量,显示:客户姓名、车辆数量,按车辆数量降序排列 7).创建视图v_expiring_policies,显示未来30天内到期的保单:客户姓名、联系电话、车牌号、结束时间、剩余天数 8).创建触发器trg_auto_renewal,当保单结束时(end_date小于当前日期),自动在t_renewal表中创建一条续保记录 9).创建存储过程proc_customer_claims,输入客户ID,返回该客户的所有理赔记录(理赔日期、理赔金额、理赔原因、状态) 答案: 1).CREATE TABLE t_renewal (renewal_id INT PRIMARY KEY AUTO_INCREMENT, policy_id INT NOT NULL, renewal_date DATE,new_premium DECIMAL(10,2), new_end_date DATE); 2).INSERT INTO t_cars (customerID, licensePlate, brand, model, vin) VALUES ('C001', '鄂B12345', '丰田', '凯美瑞', 'LFV3A23K9P3123456'); 3).UPDATE t_policies SET premium = premium * 0.9 WHERE endDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY); 4).DELETE FROM t_cars WHERE carID NOT IN (SELECT DISTINCT carID FROM t_policies WHERE carID IS NOT NULL); 5).SELECT coverageType AS 保险类型,COUNT(*) AS 保单数量,SUM(premium) AS 总保费FROM t_policies GROUP BY coverageType; 6).SELECT c.name AS 客户姓名, COUNT(ca.carID) AS 车辆数量FROM t_customers c LEFT JOIN t_cars ca ON c.customerID = ca.customerID GROUP BY c.customerID ORDER BY 车辆数量 DESC; 7).CREATE VIEW v_expiring_policies ASSELECT c.name AS 客户姓名, c.phone AS 联系电话,ca.licensePlate AS 车牌号, p.endDate AS 结束时间, DATEDIFF(p.endDate, CURDATE()) AS 剩余天数FROM t_policies p JOIN t_customers c ON p.customerID = c.customerID JOIN t_cars ca ON p.carID = ca.carID WHERE p.endDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY); 8).DELIMITER //CREATE TRIGGER trg_auto_renewalAFTER UPDATE ON t_policiesFOR EACH ROWBEGIN IF OLD.endDate < CURDATE() AND NEW.endDate > OLD.endDate THEN INSERT INTO t_renewal (policy_id, renewal_date, new_premium, new_end_date) VALUES (NEW.policyID, CURDATE(), NEW.premium, NEW.endDate); END IF;END//DELIMITER ; 9).DELIMITER //CREATE PROCEDURE proc_customer_claims(IN p_customerID INT)BEGIN SELECT c.claim_date AS 理赔日期, c.claim_amount AS 理赔金额,c.reason AS 理赔原因, c.status AS 状态 FROM t_claims c JOIN t_policies p ON c.policy_id = p.policyID WHERE p.customerID = p_customerID;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_enrollment,字段如下: enroll_id(选课编号) int 主键自增长, student_id(学生编号) int 非空, course_id(课程编号) int 非空, enroll_date(选课日期) date, score(成绩) decimal(5,2) 2).向t_departments表中插入一条记录:部门名称为“经济管理学院” 3).将“王教授”的职称(通过薪资表体现)基本薪资上调1000元 4).删除所有没有分配课程的在职教师记录 5).查询每位教师的班级数量(假设教师是班主任),显示:教师姓名、班级数量 6).查询每个部门教师的总薪资(按年份),显示:部门名称、年份、总薪资 7).创建视图v_student_teacher,显示学生与班主任信息:学生姓名、班级、班主任姓名、班主任电话 8).创建触发器trg_check_max_courses,当向 t_courses 表插入课程时,检查该教师已授课程数量是否超过5门,如超过则拒绝 9).创建存储过程proc_student_grade,输入学生ID,返回该学生所有课程的成绩及学分(假设课程表已有credit字段) 答案: 1).CREATE TABLE t_enrollment (enroll_id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL,course_id INT NOT NULL,enroll_date DATE, score DECIMAL(5,2)); 2).INSERT INTO t_departments (departmentName) VALUES ('经济管理学院'); 3).UPDATE t_salaries sJOIN t_teachers t ON s.teacherID = t.teacherIDSET s.basicSalary = s.basicSalary + 1000WHERE t.name = '王教授'; 4).DELETE FROM t_teachers WHERE isactive = 1 AND teacherID NOT IN (SELECT DISTINCT teacherID FROM t_courses); 5).SELECT t.name AS 教师姓名, COUNT(DISTINCT s.class_id) AS 班级数量FROM t_teachers t LEFT JOIN t_students s ON t.teacherID = s.teacher_id GROUP BY t.teacherID; 6).SELECT d.departmentName AS 部门名称, s.year AS 年份,SUM(s.totalSalary) AS 总薪资FROM t_salaries s JOIN t_teachers t ON s.teacherID = t.teacherID JOIN t_departments d ON t.departmentID = d.departmentID GROUP BY d.departmentID, s.year; 7).CREATE VIEW v_student_teacher ASSELECT stu.name AS 学生姓名,stu.class_id AS 班级,t.name AS 班主任姓名, t.phoneNumber AS 班主任电话FROM t_students stu LEFT JOIN t_teachers t ON stu.teacher_id = t.teacherID; 8).DELIMITER //CREATE TRIGGER trg_check_max_courses BEFORE INSERT ON t_coursesFOR EACH ROWBEGIN DECLARE course_count INT; SELECT COUNT(*) INTO course_count FROM t_courses WHERE teacherID = NEW.teacherID;IF course_count >= 5 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该教师已授课程数量超过5门，无法添加'; END IF;END//DELIMITER ; 9).ALTER TABLE t_courses ADD COLUMN credit INT DEFAULT 2;DELIMITER //CREATE PROCEDURE proc_student_grade(IN p_student_id INT) BEGIN SELECT c.courseName AS 课程名称, e.score AS 成绩, c.credit AS 学分 FROM t_enrollment e JOIN t_courses c ON e.course_id = c.courseID WHERE e.student_id = p_student_id;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).创建歌单表playlist,字段如下: playlist_id(歌单编号) int 主键自增长, user_id(用户ID) int 非空, playlist_name(歌单名称) varchar(100) 非空, create_time(创建时间) datetime 2).向songs表中插入一条歌曲数据:歌曲名称《夜曲》,歌手姓名周杰伦,专辑名称《十一月的萧邦》,音乐类型流行,发行日期2005-11-01 3).将用户“小红”的密码修改为 newpassword123 4).删除所有从未被播放过的歌曲 5).查询每个歌手的歌曲数量和总播放次数,显示:歌手姓名、歌曲数量、总播放次数 6).查询每个用户的平均听歌时长(总时长/不同歌曲数),显示:用户名、平均听歌时长 7).创建视图v_user_play_rank,显示每个用户播放次数排名前3的歌曲:用户名、歌曲名称、播放次数、排名 8).创建触发器trg_update_total_play,当向play_history表插入播放记录时,自动更新songs表中对应歌曲的总播放次数字段 9).创建存储过程proc_recommend_songs,输入用户ID,返回该用户未播放过的、同类型中播放次数最高的前5首歌曲 答案: 1).CREATE TABLE playlist (playlist_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL,playlist_name VARCHAR(100) NOT NULL,create_time DATETIME); 2).INSERT INTO songs (song_name, singer_name, album_name, genre, release_date) VALUES ('夜曲', '周杰伦', '十一月的萧邦', '流行', '2005-11-01'); 3).UPDATE users SET password = 'newpassword123' WHERE username = '小红'; 4).DELETE FROM songs WHERE song_id NOT IN (SELECT DISTINCT song_id FROM user_song_stats); 5).SELECT s.singer_name AS 歌手姓名,COUNT(DISTINCT s.song_id) AS 歌曲数量,SUM(us.play_count) AS 总播放次数FROM songs s LEFT JOIN user_song_stats us ON s.song_id = us.song_id GROUP BY s.singer_name; 6).SELECT u.username AS 用户名,SUM(us.total_play_time) / COUNT(DISTINCT us.song_id) AS 平均听歌时长FROM users u JOIN user_song_stats us ON u.user_id = us.user_id GROUP BY u.user_id; 7).CREATE VIEW v_user_play_rank ASSELECT u.username AS 用户名, s.song_name AS 歌曲名称,us.play_count AS 播放次数,RANK() OVER (PARTITION BY u.user_id ORDER BY us.play_count DESC) 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 HAVING 排名 <= 3; 8).ALTER TABLE songs ADD COLUMN total_play_count INT DEFAULT 0;DELIMITER //CREATE TRIGGER trg_update_total_playAFTER INSERT ON play_historyFOR EACH ROWBEGIN UPDATE songs SET total_play_count = total_play_count + 1 WHERE song_id = NEW.song_id;END//DELIMITER ; 9).DELIMITER //CREATE PROCEDURE proc_recommend_songs(IN p_user_id INT) BEGIN SELECT s.song_name AS 歌曲名称, s.singer_name AS 歌手姓名, s.genre AS 音乐类型, s.total_play_count AS 总播放次数 FROM songs s WHERE s.genre IN (SELECT DISTINCT s2.genre FROM user_song_stats us JOIN songs s2 ON us.song_id = s2.song_id WHERE us.user_id = p_user_id) AND s.song_id NOT IN (SELECT song_id FROM user_song_stats WHERE user_id = p_user_id)ORDER BY s.total_play_count DESC LIMIT 5;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).创建出版社表publisher,字段如下: pub_id(出版社编号) int 主键自增长, pub_name(出版社名称) varchar(100) 非空, address(地址) varchar(200), tel(联系电话) varchar(20) 2).向books表中插入一条图书数据:书籍标题《活着》,作者余华,书号9787506365437,出版日期1993-11-01,借阅状态1 3).将读者“李华”的邮箱修改为 lihua@163.com 4).删除所有从未借阅过书籍的读者记录 5).查询当前借阅次数最多的前5本书,显示:书籍标题、作者、借阅次数 6).查询每位读者的逾期未还书籍数量,显示:读者姓名、逾期数量(逾期指应还日期小于当前日期且未归还) 7).创建视图v_reader_borrow_status,显示当前未归还借阅信息:读者姓名、书籍标题、借阅时间、应还日期、逾期天数 8).创建触发器trg_prevent_borrow,当向borrowrecords表插入借阅记录时,检查该读者是否有逾期未还书籍,如有则阻止借阅 9).创建存储过程proc_return_book,输入参数为借阅记录ID,实现还书操作(更新归还状态、更新书籍借阅状态、计算逾期罚款) 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).创建报名表registrations,字段如下: reg_id(报名编号) int 主键自增长, participant_id(参赛者ID) int 非空, race_id(比赛ID) int 非空, reg_date(报名日期) date, status(报名状态) varchar(20) 默认'已报名' 2).向races表中插入一条比赛数据:比赛名称“广州马拉松”,比赛日期2025-12-10,比赛地点“广州市” 3).将参赛者王芳的年龄修改为30岁 4).删除所有未参赛(无成绩记录)的参赛者报名信息 5).查询每个年龄段(18-25岁、26-35岁、36-45岁、46岁以上)的参赛者人数,显示:年龄段、人数 6).查询每个比赛的平均完成时间,显示:比赛名称、平均完成时间(格式:HH:MM:SS) 7).创建视图v_city_rank,显示每个城市参赛者的最好成绩排名:城市名称、选手姓名、完成时间、城市内排名 8).创建触发器trg_check_registration_limit,当向registrations 表插入报名记录时,检查该比赛报名人数是否超过1000人,如超过则拒绝 9).创建存储过程proc_gender_avg_time,输入比赛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_renewal,字段如下: renewal_id(续保编号) int 主键自增长, policy_id(保单编号) int 非空, renewal_date(续保日期) date, new_premium(新保费) decimal(10,2), new_end_date(新结束时间) date 2).向t_cars表中插入一条汽车数据:客户ID为C001,车牌号鄂B12345,品牌丰田,型号凯美瑞,车辆识别号LFV3A23K9P3123456 3).将所有即将到期(7天内到期)的保单费用打9折 4).删除所有没有保单的汽车信息 5).查询每个保险类型的保单数量和总保费,显示:保险类型、保单数量、总保费 6).查询每个客户名下车辆数量,显示:客户姓名、车辆数量,按车辆数量降序排列 7).创建视图v_expiring_policies,显示未来30天内到期的保单:客户姓名、联系电话、车牌号、结束时间、剩余天数 8).创建触发器trg_auto_renewal,当保单结束时(end_date小于当前日期),自动在t_renewal表中创建一条续保记录 9).创建存储过程proc_customer_claims,输入客户ID,返回该客户的所有理赔记录(理赔日期、理赔金额、理赔原因、状态) 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_enrollment,字段如下: enroll_id(选课编号) int 主键自增长, student_id(学生编号) int 非空, course_id(课程编号) int 非空, enroll_date(选课日期) date, score(成绩) decimal(5,2) 2).向t_departments表中插入一条记录:部门名称为“经济管理学院” 3).将“王教授”的职称(通过薪资表体现)基本薪资上调1000元 4).删除所有没有分配课程的在职教师记录 5).查询每位教师的班级数量(假设教师是班主任),显示:教师姓名、班级数量 6).查询每个部门教师的总薪资(按年份),显示:部门名称、年份、总薪资 7).创建视图v_student_teacher,显示学生与班主任信息:学生姓名、班级、班主任姓名、班主任电话 8).创建触发器trg_check_max_courses,当向 t_courses 表插入课程时,检查该教师已授课程数量是否超过5门,如超过则拒绝 9).创建存储过程proc_student_grade,输入学生ID,返回该学生所有课程的成绩及学分(假设课程表已有credit字段) 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).创建歌单表playlist,字段如下: playlist_id(歌单编号) int 主键自增长, user_id(用户ID) int 非空, playlist_name(歌单名称) varchar(100) 非空, create_time(创建时间) datetime 2).向songs表中插入一条歌曲数据:歌曲名称《夜曲》,歌手姓名周杰伦,专辑名称《十一月的萧邦》,音乐类型流行,发行日期2005-11-01 3).将用户“小红”的密码修改为 newpassword123 4).删除所有从未被播放过的歌曲 5).查询每个歌手的歌曲数量和总播放次数,显示:歌手姓名、歌曲数量、总播放次数 6).查询每个用户的平均听歌时长(总时长/不同歌曲数),显示:用户名、平均听歌时长 7).创建视图v_user_play_rank,显示每个用户播放次数排名前3的歌曲:用户名、歌曲名称、播放次数、排名 8).创建触发器trg_update_total_play,当向play_history表插入播放记录时,自动更新songs表中对应歌曲的总播放次数字段 9).创建存储过程proc_recommend_songs,输入用户ID,返回该用户未播放过的、同类型中播放次数最高的前5首歌曲 原创精品资源学科网独家享有版权,侵权必究！ 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网(北京)股份有限公司 学科网（北京）股份有限公司 期末复习卷《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; $