期末复习卷《MySQL数据库基础》（上海科学普及出版社）（八）
2026-05-09
|
2份
|
16页
|
40人阅读
|
0人下载
内容正文：
《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).创建图书分类表book_category,字段如下:
category_id(分类编号) int 主键自增长,
category_name(分类名称) varchar(50) 非空,
parent_id(父分类编号) int 默认0
2).向books表中插入一条图书数据:书籍标题《三体》,作者刘慈欣,书号9787530214314,出版日期2008-01-01,借阅状态1
3).将读者张三的电话号码修改为 13912345678
4).删除所有逾期超过30天且未归还的借阅记录
5).查询当前借出(未归还)的图书信息,显示:书籍标题、作者、读者姓名、借阅时间、应还日期
6).查询每个读者的借阅次数,显示:读者姓名、借阅次数,按借阅次数降序排列
7).创建视图v_overdue_books,显示逾期未还的图书信息:读者姓名、联系电话、书籍标题、借阅时间、应还日期
8).创建触发器trg_update_book_status,当borrowrecords表中更新归还状态为1(已归还)时,自动将books表中对应书籍的借阅状态改为1(未借出)
9).创建存储过程proc_reader_borrow_history,输入参数为读者ID,返回该读者的所有借阅历史(书籍标题、借阅时间、应还日期、是否归还)
答案:
1).CREATE TABLE book_category (category_id INT PRIMARY KEY AUTO_INCREMENT,category_name VARCHAR(50) NOT NULL,parent_id INT DEFAULT 0);
2).INSERT INTO books (title, author, isbn, publishedDate, available) VALUES ('三体', '刘慈欣', '9787530214314', '2008-01-01', 1);
3).UPDATE readers SET phoneNumber = '13912345678' WHERE name = '张三';-- 4).DELETE FROM borrowRecords WHERE returned = 0 AND DATEDIFF(CURDATE(), dueDate) > 30;
5).SELECT b.title AS 书籍标题, b.author AS 作者, r.name AS 读者姓名, br.borrowDate AS 借阅时间, br.dueDate AS 应还日期 FROM borrowRecords br JOIN books b ON br.bookID = b.bookID JOIN readers r ON br.readerID = r.readerID WHERE br.returned = 0;
6).SELECT r.name AS 读者姓名, COUNT(br.borrowRecordID) AS 借阅次数FROM readers r LEFT JOIN borrowRecords br ON r.readerID = br.readerID GROUP BY r.readerID ORDER BY 借阅次数 DESC;
7).v_overdue_books CREATE VIEW v_overdue_books ASSELECT r.name AS 读者姓名,r.phoneNumber AS 联系电话,b.title AS 书籍标题, br.borrowDate AS 借阅时间, br.dueDate AS 应还日期FROM borrowRecords br JOIN readers r ON br.readerID = r.readerID JOIN books b ON br.bookID = b.bookIDWHERE br.returned = 0 AND br.dueDate < CURDATE();
8).trg_update_book_statusDELIMITER //CREATE TRIGGER trg_update_book_statusAFTER UPDATE ON borrowRecordsFOR EACH ROWBEGIN IF NEW.returned = 1 AND OLD.returned = 0 THEN UPDATE books SET available = 1 WHERE bookID = NEW.bookID;END IF;END//DELIMITER ;
9).proc_reader_borrow_historyDELIMITER //CREATE PROCEDURE proc_reader_borrow_history(IN p_readerID INT)BEGIN SELECT b.title AS 书籍标题, br.borrowDate AS 借阅时间,br.dueDate AS 应还日期, CASE WHEN br.returned = 1 THEN '已归还' ELSE '未归还' END AS 是否归还 FROM borrowRecords br JOIN books b ON br.bookID = b.bookID WHERE br.readerID = p_readerID;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).创建志愿者表volunteers,字段如下:
vol_id(志愿者编号) int 主键自增长,
name(姓名) varchar(50) 非空,
age(年龄) int,
gender(性别) varchar(10),
race_id(比赛编号) int 非空,
duty(职责) varchar(100)
2).向participants表中插入一条参赛者数据:姓名赵雷,年龄28,性别男,城市北京,邮箱zhaolei@qq.com,电话13800000000,注册日期2024-01-01
3).将比赛北京马拉松的比赛日期修改为2025-10-20
4).删除所有排名大于100的成绩记录
5).查询每个城市参赛者的平均年龄,显示:城市、平均年龄
6).查询每个比赛的参赛人数和完赛率(完赛人数/参赛人数),显示:比赛名称、参赛人数、完赛率
7).创建视图v_top10_results,显示每个比赛排名前10的选手信息:比赛名称、选手姓名、完成时间、排名
8).创建触发器trg_check_age,当向participants表插入数据时,如果年龄小于16岁或大于80岁,提示年龄必须在16-80岁之间
9).创建存储过程proc_city_best_rank,输入城市名称,返回该城市选手在所有比赛中的最好排名及对应的比赛名称
答案:
1).CREATE TABLE volunteers( vol_id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT,gender VARCHAR(10),race_id INT NOT NULL,duty VARCHAR(100));
2).INSERT INTO participants(name, age, gender, city, email, phone, registrationDate) VALUES ('赵雷', 28, '男', '北京', 'zhaolei@qq.com', '13800000000', '2024-01-01');
3).UPDATE races SET raceDate = '2025-10-20' WHERE raceName = '北京马拉松';
4).DELETE FROM marathonResults WHERE rank > 100;
5).SELECT city AS 城市, AVG(age) AS 平均年龄FROM participantsGROUP BY city;
6).SELECT r.raceName AS 比赛名称,COUNT(DISTINCT m.participantId) AS 参赛人数,ROUND(SUM(CASE WHEN m.finishTime IS NOT NULL THEN 1 ELSE 0 END) / COUNT(DISTINCT m.participantId) * 100, 2) AS 完赛率FROM races rLEFT JOIN marathonResults m ON r.raceId = m.raceIdGROUP BY r.raceId7).v_top10_resultsCREATE VIEW v_top10_results ASSELECT r.raceName AS 比赛名称, p.name AS 选手姓名,m.finishTime AS 完成时间, m.rank AS 排名FROM marathonResults m JOIN races r ON m.raceId = r.raceId JOIN participants p ON m.participantId = p.participantId WHERE m.rank <= 10ORDER BY r.raceName, m.rank;
8).trg_check_age DELIMITER //CREATE TRIGGER trg_check_age BEFORE INSERT ON participants FOR EACH ROWBEGIN IF NEW.age < 16 OR NEW.age > 80 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在16-80岁之间';END IF;END//DELIMITER;
9).proc_city_best_rankDELIMITER //CREATE PROCEDURE proc_city_best_rank(IN p_city VARCHAR(50)) BEGIN 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.raceId WHERE p.city = p_city ORDER BY m.rank ASC LIMIT 1;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_claims,字段如下:
claim_id(理赔编号) int 主键自增长,
policy_id(保单编号) int 非空,
claim_date(理赔日期) date,
claim_amount(理赔金额) decimal(10,2),
reason(理赔原因) varchar(200),
status(理赔状态) varchar(20) 默认'处理中'
2).向t_customers表中插入一条客户数据:姓名李芳,电话15612345678,邮箱lifang@163.com,地址北京市朝阳区
3).将客户王强名下的所有保单结束时间延长30天
4).删除所有已过期的保单记录(结束时间小于当前日期)
5).查询每个客户的保单总费用,显示:客户姓名、保单总费用,按总费用降序排列
6).查询即将到期(30天内到期)的保单信息,显示:客户姓名、联系电话、车牌号、结束时间
7).创建视图v_active_policies,显示当前有效的保单:客户姓名、车牌号、保单号、开始时间、结束时间、保单费用
8).创建触发器 trg_update_car_count,当向 t_cars 表插入新车时,自动更新客户表的车辆数量字段(假设客户表已有 car_count 字段)
9).创建存储过程 proc_customer_policies,输入客户姓名,返回该客户所有保单的详细信息(保单号、车牌号、开始时间、结束时间、保单费用)
答案:
1).CREATE TABLE t_claims ( claim_id INT PRIMARY KEY AUTO_INCREMENT, policy_id INT NOT NULL, claim_date DATE, claim_amount DECIMAL(10,2), reason VARCHAR(200), status VARCHAR(20) DEFAULT '处理中');
2).INSERT INTO t_customers (name, phone, email, address) VALUES ('李芳', '15612345678', 'lifang@163.com', '北京市朝阳区');
3).UPDATE t_policies p JOIN t_customers c ON p.customerID = c.customerIDSET p.endDate = DATE_ADD(p.endDate, INTERVAL 30 DAY)WHERE c.name = '王强';
4).DELETE FROM t_policies WHERE endDate < CURDATE();
5).SELECT c.name AS 客户姓名, SUM(p.premium) AS 保单总费用FROM t_customers c LEFT JOIN t_policies p ON c.customerID = p.customerIDGROUP BY c.customerIDORDER BY 保单总费用 DESC;
6).SELECT c.name AS 客户姓名, c.phone AS 联系电话,ca.licensePlate AS 车牌号, p.endDate AS 结束时间FROM t_policies p JOIN t_customers c ON p.customerID = c.customerIDJOIN t_cars ca ON p.carID = ca.carID WHERE p.endDate BETWEEN CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 30 DAY);7).CREATE VIEW v_active_policies ASSELECT c.name AS 客户姓名, ca.licensePlate AS 车牌号,p.policyNumber AS 保单号, p.startDate AS 开始时间,p.endDate AS 结束时间, p.premium 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 >= CURDATE();
8).ALTER TABLE t_customers ADD COLUMN car_count INT DEFAULT 0;-- 创建触发器trg_update_car_countDELIMITER //CREATE TRIGGER trg_update_car_countAFTER INSERT ON t_carsFOR EACH ROWBEGIN UPDATE t_customers SET car_count = car_count + 1 WHERE customerID = NEW.customerID;END//DELIMITER ;
9).DELIMITER //CREATE PROCEDURE proc_customer_policies(IN p_name VARCHAR(100))BEGIN SELECT p.policyNumber AS 保单号, ca.licensePlate AS 车牌号,p.startDate AS 开始时间, p.endDate AS 结束时间, p.premium AS 保单费用 FROM t_policies p JOIN t_customers c ON p.customerID = c.customerID JOIN t_cars ca ON p.carID = ca.carID WHERE c.name = p_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_students,字段如下:
student_id(学生编号) int 主键自增长,
name(学生姓名) varchar(50) 非空,
gender(性别) varchar(10),
age(年龄) int,
class_id(班级编号) int,
teacher_id(班主任编号) int
2).向t_departments表中插入一条记录:部门名称为外语系
3).将所有数学系教师的奖金增加500元
4).删除所有离职教师(isactive=0)的薪资记录
5).查询每位教师所教授的课程数量,显示:教师姓名、课程数量
6).查询每个部门的平均总薪资(按年份),显示:部门名称、年份、平均总薪资
7).创建视图v_active_teachers,显示在职教师信息:教师姓名、部门名称、课程名称、基本薪资、总薪资
8).创建触发器 trg_update_total_salary,当向 t_salaries 表中插入或更新基本薪资或奖金时,自动计算并更新总薪资(基本薪资+奖金)
9).创建存储过程 proc_teacher_courses,输入教师ID,返回该教师所教授的所有课程名称及选修该课程的学生人数(假设已有选课表)
答案:
1).CREATE TABLE t_students ( student_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, gender VARCHAR(10), age INT, class_id INT, teacher_id INT);
2).INSERT INTO t_departments (departmentName) VALUES ('外语系');
3).UPDATE t_salaries sJOIN t_teachers t ON s.teacherID = t.teacherIDJOIN t_departments d ON t.departmentID = d.departmentIDSET s.bonus = s.bonus + 500 WHERE d.departmentName = '数学系';
4).DELETE FROM t_salaries WHERE teacherID IN (SELECT teacherID FROM t_teachers WHERE isactive = 0);
5).SELECT t.name AS 教师姓名, COUNT(c.courseID) AS 课程数量FROM t_teachers tLEFT JOIN t_courses c ON t.teacherID = c.teacherID GROUP BY t.teacherID;
6).SELECT d.departmentName AS 部门名称, s.year AS 年份,AVG(s.totalSalary) AS 平均总薪资FROM t_salaries s JOIN t_teachers t ON s.teacherID = t.teacherID JOIN t_departments d ON t.departmentID = d.departmentIDGROUP BY d.departmentID, s.year;-- 7). 创建视图v_active_teachersCREATE VIEW v_active_teachers AS SELECT t.name AS 教师姓名, d.departmentName AS 部门名称,c.courseName AS 课程名称, s.basicSalary AS 基本薪资, s.totalSalary AS 总薪资FROM t_teachers t JOIN t_departments d ON t.departmentID = d.departmentID LEFT JOIN t_courses c ON t.teacherID = c.teacherID LEFT JOIN t_salaries s ON t.teacherID = s.teacherID WHERE t.isactive = 1;
8).trg_update_total_salaryDELIMITER //CREATE TRIGGER trg_update_total_salary BEFORE INSERT ON t_salariesFOR EACH ROW BEGIN SET NEW.totalSalary = NEW.basicSalary + NEW.bonus;END//DELIMITER ;DELIMITER //CREATE TRIGGER trg_update_total_salary_update BEFORE UPDATE ON t_salariesFOR EACH ROW BEGIN SET NEW.totalSalary = NEW.basicSalary + NEW.bonus;END//DELIMITER ;
9).CREATE TABLE t_course_selection ( selection_id INT PRIMARY KEY AUTO_INCREMENT, student_id INT, course_id INT, score DECIMAL(5,2));proc_teacher_coursesDELIMITER //CREATE PROCEDURE proc_teacher_courses(IN p_teacherID INT)BEGIN SELECT c.courseName AS 课程名称, COUNT(cs.student_id) AS 学生人数 FROM t_courses c LEFT JOIN t_course_selection cs ON c.courseID = cs.courseID WHERE c.teacherID = p_teacherID GROUP BY c.courseID;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).创建播放记录表 play_history,字段如下:
history_id(记录编号) int 主键自增长,
user_id(用户ID) int 非空,
song_id(歌曲ID) int 非空,
play_time(播放时间) datetime,
duration(播放时长) int(单位秒)
2).向users表中插入一条用户数据:用户名李雷,密码123456,邮箱lilei@qq.com,创建日期2024-01-01
3).将歌曲青花瓷的歌手姓名修改为周杰伦
4).删除所有播放次数为0的用户歌曲统计记录
5).查询每个音乐类型(genre)的歌曲数量,显示:音乐类型、歌曲数量
6).查询每个用户听歌总时长,显示:用户名,总播放时长(分钟),按总时长降序排列
7).创建视图v_hot_songs,显示播放次数排名前10的歌曲:歌曲名称、歌手姓名、播放次数
8).创建触发器trg_update_play_stats,当向play_history表中插入一条播放记录时,自动更新user_song_stats表中的播放次数和总播放时长
9).创建存储过程proc_user_favorites,输入用户ID,返回该用户播放次数超过10次的歌曲列表(歌曲名称、歌手姓名、播放次数)
答案:
1).CREATE TABLE play_history (history_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT NOT NULL,song_id INT NOT NULL,play_time DATETIME,duration INT);
2).INSERT INTO users (username, password, email, created_date) VALUES ('李雷', '123456', 'lilei@qq.com', '2024-01-01');
3).UPDATE songs SET singer_name = '周杰伦' WHERE song_name = '青花瓷';4).DELETE FROM user_song_stats WHERE play_count = 0;
5).SELECT genre AS 音乐类型, COUNT(song_id) AS 歌曲数量FROM songsGROUP BY genre;
6).SELECT u.username AS 用户名, SUM(us.total_play_time) AS 总播放时长FROM users uLEFT JOIN user_song_stats us ON u.user_id = us.user_idGROUP BY u.user_idORDER BY 总播放时长 DESC;
7).v_hot_songsCREATE VIEW v_hot_songs ASSELECT s.song_name AS 歌曲名称, s.singer_name AS 歌手姓名,SUM(us.play_count) AS 播放次数FROM user_song_stats us JOIN songs s ON us.song_id = s.song_id GROUP BY us.song_id ORDER BY 播放次数 DESC LIMIT 10;
8).DELIMITER //CREATE TRIGGER trg_update_play_statsAFTER INSERT ON play_historyFOR EACH ROWBEGIN IF EXISTS (SELECT 1 FROM user_song_stats WHERE user_id = NEW.user_id AND song_id = NEW.song_id) THEN UPDATE user_song_stats SET play_count = play_count + 1,total_play_time = total_play_time + (NEW.duration / 60) WHERE user_id = NEW.user_id AND song_id = NEW.song_id; ELSE INSERT INTO user_song_stats (user_id, song_id, play_count, total_play_time) VALUES (NEW.user_id, NEW.song_id, 1, NEW.duration / 60); END IF;END//DELIMITER ;
9).proc_user_favoritesDELIMITER //CREATE PROCEDURE proc_user_favorites(IN p_user_id INT)BEGIN SELECT s.song_name AS 歌曲名称, s.singer_name AS 歌手姓名,us.play_count AS 播放次数 FROM user_song_stats us JOIN songs s ON us.song_id = s.song_id WHERE us.user_id = p_user_id AND us.play_count > 10;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).创建图书分类表book_category,字段如下:
category_id(分类编号) int 主键自增长,
category_name(分类名称) varchar(50) 非空,
parent_id(父分类编号) int 默认0
2).向books表中插入一条图书数据:书籍标题《三体》,作者刘慈欣,书号9787530214314,出版日期2008-01-01,借阅状态1
3).将读者张三的电话号码修改为 13912345678
4).删除所有逾期超过30天且未归还的借阅记录
5).查询当前借出(未归还)的图书信息,显示:书籍标题、作者、读者姓名、借阅时间、应还日期
6).查询每个读者的借阅次数,显示:读者姓名、借阅次数,按借阅次数降序排列
7).创建视图v_overdue_books,显示逾期未还的图书信息:读者姓名、联系电话、书籍标题、借阅时间、应还日期
8).创建触发器trg_update_book_status,当borrowrecords表中更新归还状态为1(已归还)时,自动将books表中对应书籍的借阅状态改为1(未借出)
9).创建存储过程proc_reader_borrow_history,输入参数为读者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).创建志愿者表volunteers,字段如下:
vol_id(志愿者编号) int 主键自增长,
name(姓名) varchar(50) 非空,
age(年龄) int,
gender(性别) varchar(10),
race_id(比赛编号) int 非空,
duty(职责) varchar(100)
2).向participants表中插入一条参赛者数据:姓名赵雷,年龄28,性别男,城市北京,邮箱zhaolei@qq.com,电话13800000000,注册日期2024-01-01
3).将比赛北京马拉松的比赛日期修改为2025-10-20
4).删除所有排名大于100的成绩记录
5).查询每个城市参赛者的平均年龄,显示:城市、平均年龄
6).查询每个比赛的参赛人数和完赛率(完赛人数/参赛人数),显示:比赛名称、参赛人数、完赛率
7).创建视图v_top10_results,显示每个比赛排名前10的选手信息:比赛名称、选手姓名、完成时间、排名
8).创建触发器trg_check_age,当向participants表插入数据时,如果年龄小于16岁或大于80岁,提示年龄必须在16-80岁之间
9).创建存储过程proc_city_best_rank,输入城市名称,返回该城市选手在所有比赛中的最好排名及对应的比赛名称
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_claims,字段如下:
claim_id(理赔编号) int 主键自增长,
policy_id(保单编号) int 非空,
claim_date(理赔日期) date,
claim_amount(理赔金额) decimal(10,2),
reason(理赔原因) varchar(200),
status(理赔状态) varchar(20) 默认'处理中'
2).向t_customers表中插入一条客户数据:姓名李芳,电话15612345678,邮箱lifang@163.com,地址北京市朝阳区
3).将客户王强名下的所有保单结束时间延长30天
4).删除所有已过期的保单记录(结束时间小于当前日期)
5).查询每个客户的保单总费用,显示:客户姓名、保单总费用,按总费用降序排列
6).查询即将到期(30天内到期)的保单信息,显示:客户姓名、联系电话、车牌号、结束时间
7).创建视图v_active_policies,显示当前有效的保单:客户姓名、车牌号、保单号、开始时间、结束时间、保单费用
8).创建触发器 trg_update_car_count,当向 t_cars 表插入新车时,自动更新客户表的车辆数量字段(假设客户表已有 car_count 字段)
9).创建存储过程 proc_customer_policies,输入客户姓名,返回该客户所有保单的详细信息(保单号、车牌号、开始时间、结束时间、保单费用)
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_students,字段如下:
student_id(学生编号) int 主键自增长,
name(学生姓名) varchar(50) 非空,
gender(性别) varchar(10),
age(年龄) int,
class_id(班级编号) int,
teacher_id(班主任编号) int
2).向t_departments表中插入一条记录:部门名称为外语系
3).将所有数学系教师的奖金增加500元
4).删除所有离职教师(isactive=0)的薪资记录
5).查询每位教师所教授的课程数量,显示:教师姓名、课程数量
6).查询每个部门的平均总薪资(按年份),显示:部门名称、年份、平均总薪资
7).创建视图v_active_teachers,显示在职教师信息:教师姓名、部门名称、课程名称、基本薪资、总薪资
8).创建触发器 trg_update_total_salary,当向 t_salaries 表中插入或更新基本薪资或奖金时,自动计算并更新总薪资(基本薪资+奖金)
9).创建存储过程 proc_teacher_courses,输入教师ID,返回该教师所教授的所有课程名称及选修该课程的学生人数(假设已有选课表)
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).创建播放记录表 play_history,字段如下:
history_id(记录编号) int 主键自增长,
user_id(用户ID) int 非空,
song_id(歌曲ID) int 非空,
play_time(播放时间) datetime,
duration(播放时长) int(单位秒)
2).向users表中插入一条用户数据:用户名李雷,密码123456,邮箱lilei@qq.com,创建日期2024-01-01
3).将歌曲青花瓷的歌手姓名修改为周杰伦
4).删除所有播放次数为0的用户歌曲统计记录
5).查询每个音乐类型(genre)的歌曲数量,显示:音乐类型、歌曲数量
6).查询每个用户听歌总时长,显示:用户名,总播放时长(分钟),按总时长降序排列
7).创建视图v_hot_songs,显示播放次数排名前10的歌曲:歌曲名称、歌手姓名、播放次数
8).创建触发器trg_update_play_stats,当向play_history表中插入一条播放记录时,自动更新user_song_stats表中的播放次数和总播放时长
9).创建存储过程proc_user_favorites,输入用户ID,返回该用户播放次数超过10次的歌曲列表(歌曲名称、歌手姓名、播放次数)
原创精品资源学科网独家享有版权,侵权必究！
学科网(北京)股份有限公司
学科网(北京)股份有限公司
学科网(北京)股份有限公司
学科网（北京）股份有限公司
期末复习卷《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;
$
资源预览图
1
2
3
由于学科网是一个信息分享及获取的平台，不确保部分用户上传资料的 来源及知识产权归属。如您发现相关资料侵犯您的合法权益，请联系学科网，我们核实后将及时进行处理。