MySQL练习题
一、表关系
导出现有数据库的数据：
- mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 + 文件名 # 表结构 + 数据
- mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 + 文件名 # 表结构
将文件中的数据导入数据库：
- mysqldump -uroot -p密码 数据库名称 < 文件路径 + 文件名
二、练习题
1、自行创建测试数据
1 /* 2 Navicat Premium Data Transfer 3 4 Source Server : 10.0.100.187 5 Source Server Type : MySQL 6 Source Server Version : 50625 7 Source Host : 10.0.100.187:3306 8 Source Schema : test 9 10 Target Server Type : MySQL 11 Target Server Version : 50625 12 File Encoding : 65001 13 14 Date: 11/03/2019 14:36:33 15 */ 16 17 SET NAMES utf8mb4; 18 SET FOREIGN_KEY_CHECKS = 0; 19 20 -- ---------------------------- 21 -- Table structure for class 22 -- ---------------------------- 23 DROP TABLE IF EXISTS `class`; 24 CREATE TABLE `class` ( 25 `cid` int(11) NOT NULL AUTO_INCREMENT, 26 `caption` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 27 PRIMARY KEY (`cid`) USING BTREE 28 ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; 29 30 -- ---------------------------- 31 -- Records of class 32 -- ---------------------------- 33 INSERT INTO `class` VALUES (1, '三年二班'); 34 INSERT INTO `class` VALUES (2, '三年三班'); 35 INSERT INTO `class` VALUES (3, '一年二班'); 36 INSERT INTO `class` VALUES (4, '二年九班'); 37 38 -- ---------------------------- 39 -- Table structure for course 40 -- ---------------------------- 41 DROP TABLE IF EXISTS `course`; 42 CREATE TABLE `course` ( 43 `cid` int(11) NOT NULL AUTO_INCREMENT, 44 `cname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 45 `teacher_id` int(11) NOT NULL, 46 PRIMARY KEY (`cid`) USING BTREE, 47 INDEX `fk_course_teacher`(`teacher_id`) USING BTREE, 48 CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT 49 ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; 50 51 -- ---------------------------- 52 -- Records of course 53 -- ---------------------------- 54 INSERT INTO `course` VALUES (1, '生物', 1); 55 INSERT INTO `course` VALUES (2, '物理', 2); 56 INSERT INTO `course` VALUES (3, '体育', 3); 57 INSERT INTO `course` VALUES (4, '美术', 2); 58 59 -- ---------------------------- 60 -- Table structure for score 61 -- ---------------------------- 62 DROP TABLE IF EXISTS `score`; 63 CREATE TABLE `score` ( 64 `sid` int(11) NOT NULL AUTO_INCREMENT, 65 `student_id` int(11) NOT NULL, 66 `course_id` int(11) NOT NULL, 67 `num` int(11) NOT NULL, 68 PRIMARY KEY (`sid`) USING BTREE, 69 INDEX `fk_score_student`(`student_id`) USING BTREE, 70 INDEX `fk_score_course`(`course_id`) USING BTREE, 71 CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT, 72 CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT 73 ) ENGINE = InnoDB AUTO_INCREMENT = 53 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; 74 75 -- ---------------------------- 76 -- Records of score 77 -- ---------------------------- 78 INSERT INTO `score` VALUES (1, 1, 1, 10); 79 INSERT INTO `score` VALUES (2, 1, 2, 9); 80 INSERT INTO `score` VALUES (5, 1, 4, 66); 81 INSERT INTO `score` VALUES (6, 2, 1, 8); 82 INSERT INTO `score` VALUES (8, 2, 3, 68); 83 INSERT INTO `score` VALUES (9, 2, 4, 99); 84 INSERT INTO `score` VALUES (10, 3, 1, 77); 85 INSERT INTO `score` VALUES (11, 3, 2, 66); 86 INSERT INTO `score` VALUES (12, 3, 3, 87); 87 INSERT INTO `score` VALUES (13, 3, 4, 99); 88 INSERT INTO `score` VALUES (14, 4, 1, 79); 89 INSERT INTO `score` VALUES (15, 4, 2, 11); 90 INSERT INTO `score` VALUES (16, 4, 3, 67); 91 INSERT INTO `score` VALUES (17, 4, 4, 100); 92 INSERT INTO `score` VALUES (18, 5, 1, 79); 93 INSERT INTO `score` VALUES (19, 5, 2, 11); 94 INSERT INTO `score` VALUES (20, 5, 3, 67); 95 INSERT INTO `score` VALUES (21, 5, 4, 100); 96 INSERT INTO `score` VALUES (22, 6, 1, 9); 97 INSERT INTO `score` VALUES (23, 6, 2, 100); 98 INSERT INTO `score` VALUES (24, 6, 3, 67); 99 INSERT INTO `score` VALUES (25, 6, 4, 100); 100 INSERT INTO `score` VALUES (26, 7, 1, 9); 101 INSERT INTO `score` VALUES (27, 7, 2, 100); 102 INSERT INTO `score` VALUES (28, 7, 3, 67); 103 INSERT INTO `score` VALUES (29, 7, 4, 88); 104 INSERT INTO `score` VALUES (30, 8, 1, 9); 105 INSERT INTO `score` VALUES (31, 8, 2, 100); 106 INSERT INTO `score` VALUES (32, 8, 3, 67); 107 INSERT INTO `score` VALUES (33, 8, 4, 88); 108 INSERT INTO `score` VALUES (34, 9, 1, 91); 109 INSERT INTO `score` VALUES (35, 9, 2, 88); 110 INSERT INTO `score` VALUES (36, 9, 3, 67); 111 INSERT INTO `score` VALUES (37, 9, 4, 22); 112 INSERT INTO `score` VALUES (38, 10, 1, 90); 113 INSERT INTO `score` VALUES (39, 10, 2, 77); 114 INSERT INTO `score` VALUES (40, 10, 3, 43); 115 INSERT INTO `score` VALUES (41, 10, 4, 87); 116 INSERT INTO `score` VALUES (42, 11, 1, 90); 117 INSERT INTO `score` VALUES (43, 11, 2, 77); 118 INSERT INTO `score` VALUES (44, 11, 3, 43); 119 INSERT INTO `score` VALUES (45, 11, 4, 87); 120 INSERT INTO `score` VALUES (46, 12, 1, 90); 121 INSERT INTO `score` VALUES (47, 12, 2, 77); 122 INSERT INTO `score` VALUES (48, 12, 3, 43); 123 INSERT INTO `score` VALUES (49, 12, 4, 87); 124 INSERT INTO `score` VALUES (52, 13, 3, 87); 125 126 -- ---------------------------- 127 -- Table structure for student 128 -- ---------------------------- 129 DROP TABLE IF EXISTS `student`; 130 CREATE TABLE `student` ( 131 `sid` int(11) NOT NULL AUTO_INCREMENT, 132 `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 133 `class_id` int(11) NOT NULL, 134 `sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 135 PRIMARY KEY (`sid`) USING BTREE, 136 INDEX `fk_class`(`class_id`) USING BTREE, 137 CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT 138 ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; 139 140 -- ---------------------------- 141 -- Records of student 142 -- ---------------------------- 143 INSERT INTO `student` VALUES (1, '男', 1, '理解'); 144 INSERT INTO `student` VALUES (2, '女', 1, '钢蛋'); 145 INSERT INTO `student` VALUES (3, '男', 1, '张三'); 146 INSERT INTO `student` VALUES (4, '男', 1, '张一'); 147 INSERT INTO `student` VALUES (5, '女', 1, '张二'); 148 INSERT INTO `student` VALUES (6, '男', 1, '张四'); 149 INSERT INTO `student` VALUES (7, '女', 2, '铁锤'); 150 INSERT INTO `student` VALUES (8, '男', 2, '李三'); 151 INSERT INTO `student` VALUES (9, '男', 2, '李一'); 152 INSERT INTO `student` VALUES (10, '女', 2, '李二'); 153 INSERT INTO `student` VALUES (11, '男', 2, '李四'); 154 INSERT INTO `student` VALUES (12, '女', 3, '如花'); 155 INSERT INTO `student` VALUES (13, '男', 3, '刘三'); 156 INSERT INTO `student` VALUES (14, '男', 3, '刘一'); 157 INSERT INTO `student` VALUES (15, '女', 3, '刘二'); 158 INSERT INTO `student` VALUES (16, '男', 3, '刘四'); 159 160 -- ---------------------------- 161 -- Table structure for teacher 162 -- ---------------------------- 163 DROP TABLE IF EXISTS `teacher`; 164 CREATE TABLE `teacher` ( 165 `tid` int(11) NOT NULL AUTO_INCREMENT, 166 `tname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 167 PRIMARY KEY (`tid`) USING BTREE 168 ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; 169 170 -- ---------------------------- 171 -- Records of teacher 172 -- ---------------------------- 173 INSERT INTO `teacher` VALUES (1, '张磊老师'); 174 INSERT INTO `teacher` VALUES (2, '李平老师'); 175 INSERT INTO `teacher` VALUES (3, '刘海燕老师'); 176 INSERT INTO `teacher` VALUES (4, '朱云海老师'); 177 INSERT INTO `teacher` VALUES (5, '李杰老师'); 178 179 SET FOREIGN_KEY_CHECKS = 1;
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
3、查询平均成绩大于60分的同学的学号、姓名和平均成绩；
1 -- 方法一 2 SELECT student_id,sname,AVG(num) FROM score a,student b 3 WHERE a.student_id = b.sid 4 GROUP BY student_id HAVING AVG(num) > 60; 5 6 -- 方法二 7 SELECT student_id,sname,AVG(num) FROM score a LEFT JOIN student b 8 ON a.student_id = b.sid 9 GROUP BY student_id HAVING AVG(num) > 60; 10 11 -- 方法三 12 SELECT T.student_id,student.sname,T.A FROM 13 (SELECT student_id,AVG(num) AS A FROM score GROUP BY student_id HAVING AVG(num)>60) as T 14 LEFT JOIN student ON T.student_id = student.sid;
4、查询所有同学的学号、姓名、选课数、总成绩；
1 -- 方法一 2 SELECT a.student_id,b.sname,COUNT(a.course_id),SUM(a.num) FROM score a 3 LEFT JOIN student b ON a.student_id = b.sid 4 GROUP BY a.student_id; 5 6 -- 方法二 7 SELECT a.student_id,b.sname,COUNT(course_id),SUM(num) FROM score a,student b 8 WHERE a.student_id = b.sid 9 GROUP BY a.student_id;
5、查询姓“李”的老师的个数；
1 SELECT COUNT(1) FROM teacher WHERE tname LIKE '李%';
6、查询没学过“李平”老师课的同学的学号、姓名；
1 思路： 2 先查到“李平老师”老师教的所有课ID 3 获取选过课的所有学生ID 4 学生表中筛选 5 6 SELECT sid,sname FROM student WHERE sid NOT IN( 7 SELECT DISTINCT student_id FROM score WHERE course_id IN( 8 SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = '李平老师'));
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
1 思路： 2 先查到既选择001又选择002课程的所有同学 3 根据学生进行分组，如果课程数量大于1，表示两门均已选择 4 5 SELECT student_id,sname FROM 6 (SELECT student_id FROM score WHERE course_id = 1 or course_id = 2 7 GROUP BY student_id 8 HAVING COUNT(student_id) > 1) as B 9 LEFT JOIN student ON B.student_id = student.sid;
8、查询学过“李平”老师所教的所有课的同学的学号、姓名；
1 思路： 2 先查到“李平老师”老师教的所有课ID 3 再查到学习过李平老师任意一门课程的student_id 4 根据学生进行分组，如果课程数量等于李平老师教的所有课程数量，表示两门都学习了 5 然后和student表联合查询展示student_id和sname 6 7 SELECT student_id,sname FROM 8 (SELECT student_id FROM score WHERE course_id IN 9 (SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid 10 WHERE teacher.tname = '李平老师') 11 GROUP BY student_id 12 HAVING count(student_id) = (SELECT count(cid) FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid 13 WHERE teacher.tname = '李平老师' )) as B 14 LEFT JOIN student ON B.student_id = student.sid;
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
10、查询有课程成绩小于60分的同学的学号、姓名；
1 SELECT sid,sname FROM student WHERE sid IN( 2 SELECT DISTINCT student_id FROM score WHERE num < 60);
11、查询没有学全所有课的同学的学号、姓名；
1 SELECT student_id,sname,COUNT(course_id) FROM score 2 LEFT JOIN student ON score.student_id = student.sid 3 GROUP BY student_id 4 HAVING COUNT(course_id) < (SELECT COUNT(1) FROM course);
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
1 SELECT student_id,sname FROM score LEFT JOIN student ON score.student_id = student.sid 2 WHERE student_id !=1 AND course_id IN 3 (SELECT course_id FROM score WHERE student_id = 1) 4 GROUP BY student_id;
13、查询学过学号为“001”同学所有课程的其他同学学号和姓名；
1 SELECT student_id,sname FROM score LEFT JOIN student ON score.student_id = student.sid 2 WHERE student_id !=1 AND course_id IN 3 (SELECT course_id FROM score WHERE student_id = 1) 4 GROUP BY student_id 5 HAVING COUNT(course_id) = (SELECT COUNT(1) FROM score WHERE student_id = 1);
14、查询和“003”号的同学学习的课程完全相同的其他同学学号和姓名；
1 思路： 2 课程数量和003号同学一样 3 课程交集和003号同学所学课程一样 4 5 SELECT student_id,sname,COUNT(course_id) FROM score LEFT JOIN student ON score.student_id = student.sid 6 WHERE student_id IN 7 -- 课程数量和003号同学一样 8 (SELECT student_id FROM score WHERE student_id != 3 GROUP BY student_id HAVING COUNT(course_id) = (SELECT COUNT(1) FROM score WHERE student_id = 3)) 9 -- 课程交集和003号同学所学课程一样 10 AND course_id IN (SELECT course_id FROM score WHERE student_id = 3) 11 GROUP BY student_id 12 HAVING COUNT(course_id) = (SELECT COUNT(1) FROM score WHERE student_id = 3);
15、删除学习“叶平”老师课的SC表记录；
16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
20、课程平均分从高到低显示（现实任课老师）；
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
22、查询每门课程被选修的学生数；
23、查询出只选修了一门课程的全部学生的学号和姓名；
24、查询男生、女生的人数；
25、查询姓“张”的学生名单；
26、查询同名同姓学生名单，并统计同名人数；
27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
31、求选了课程的学生人数
32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
33、查询各个课程及相应的选修人数；
34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
35、查询每门课程成绩最好的前两名；
36、检索至少选修两门课程的学生学号；
37、查询全部学生都选修的课程的课程号和课程名；
38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
39、查询两门以上不及格课程的同学的学号及其平均成绩；
40、检索“004”课程分数小于60，按分数降序排列的同学学号；
41、删除“002”同学的“001”课程的成绩；
