mysql练习题
2020的春节被新型冠状病毒搞的挺闲的，社交都停了，闲着没事在家找点事情做，找着找着就找到了一些的数据库练习题，感觉还是受益良多的。
1 表结构
/* Navicat Premium Data Transfer Source Server : local Source Server Type : MySQL Source Server Version : 50729 Source Host : localhost:3306 Source Schema : db5 Target Server Type : MySQL Target Server Version : 50729 File Encoding : 65001 Date: 29/01/2020 16:38:27 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES (1, '三年二班'); INSERT INTO `class` VALUES (2, '三年三班'); INSERT INTO `class` VALUES (3, '一年二班'); INSERT INTO `class` VALUES (4, '二年九班'); -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`) USING BTREE, INDEX `fk_course_teacher`(`teacher_id`) USING BTREE, CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (1, '生物', 1); INSERT INTO `course` VALUES (2, '物理', 2); INSERT INTO `course` VALUES (3, '体育', 3); INSERT INTO `course` VALUES (4, '美术', 2); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`) USING BTREE, INDEX `fk_score_student`(`student_id`) USING BTREE, INDEX `fk_score_course`(`course_id`) USING BTREE, CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 53 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES (1, 1, 1, 10); INSERT INTO `score` VALUES (2, 1, 3, 9); INSERT INTO `score` VALUES (5, 1, 4, 66); INSERT INTO `score` VALUES (6, 2, 1, 8); INSERT INTO `score` VALUES (8, 2, 3, 68); INSERT INTO `score` VALUES (9, 2, 4, 99); INSERT INTO `score` VALUES (10, 3, 1, 77); INSERT INTO `score` VALUES (11, 3, 2, 66); INSERT INTO `score` VALUES (12, 3, 3, 87); INSERT INTO `score` VALUES (13, 3, 4, 99); INSERT INTO `score` VALUES (14, 4, 1, 98); INSERT INTO `score` VALUES (15, 4, 2, 98); INSERT INTO `score` VALUES (16, 4, 3, 98); INSERT INTO `score` VALUES (17, 4, 4, 98); INSERT INTO `score` VALUES (18, 5, 1, 79); INSERT INTO `score` VALUES (19, 5, 2, 11); INSERT INTO `score` VALUES (20, 5, 3, 67); INSERT INTO `score` VALUES (21, 5, 4, 100); INSERT INTO `score` VALUES (22, 6, 1, 89); INSERT INTO `score` VALUES (23, 6, 2, 89); INSERT INTO `score` VALUES (24, 6, 3, 89); INSERT INTO `score` VALUES (25, 6, 4, 89); INSERT INTO `score` VALUES (26, 7, 1, 99); INSERT INTO `score` VALUES (27, 7, 2, 99); INSERT INTO `score` VALUES (28, 7, 3, 99); INSERT INTO `score` VALUES (29, 7, 4, 99); INSERT INTO `score` VALUES (30, 8, 1, 98); INSERT INTO `score` VALUES (31, 8, 2, 98); INSERT INTO `score` VALUES (32, 8, 3, 98); INSERT INTO `score` VALUES (33, 8, 4, 98); INSERT INTO `score` VALUES (34, 9, 1, 91); INSERT INTO `score` VALUES (35, 9, 2, 88); INSERT INTO `score` VALUES (36, 9, 3, 67); INSERT INTO `score` VALUES (37, 9, 4, 22); INSERT INTO `score` VALUES (38, 10, 1, 90); INSERT INTO `score` VALUES (39, 10, 2, 77); INSERT INTO `score` VALUES (40, 10, 3, 43); INSERT INTO `score` VALUES (41, 10, 4, 87); INSERT INTO `score` VALUES (42, 11, 1, 90); INSERT INTO `score` VALUES (43, 11, 2, 77); INSERT INTO `score` VALUES (44, 11, 3, 43); INSERT INTO `score` VALUES (45, 11, 4, 87); INSERT INTO `score` VALUES (46, 12, 1, 90); INSERT INTO `score` VALUES (47, 12, 2, 77); INSERT INTO `score` VALUES (48, 12, 3, 43); INSERT INTO `score` VALUES (49, 12, 4, 87); INSERT INTO `score` VALUES (52, 13, 3, 87); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`sid`) USING BTREE, INDEX `fk_class`(`class_id`) USING BTREE, CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, '男', 1, '理解'); INSERT INTO `student` VALUES (2, '女', 1, '钢蛋'); INSERT INTO `student` VALUES (3, '男', 1, '张三'); INSERT INTO `student` VALUES (4, '男', 1, '张一'); INSERT INTO `student` VALUES (5, '女', 1, '张二'); INSERT INTO `student` VALUES (6, '男', 1, '张四'); INSERT INTO `student` VALUES (7, '女', 2, '铁锤'); INSERT INTO `student` VALUES (8, '男', 2, '李三'); INSERT INTO `student` VALUES (9, '男', 2, '李一'); INSERT INTO `student` VALUES (10, '女', 2, '李二'); INSERT INTO `student` VALUES (11, '男', 2, '李四'); INSERT INTO `student` VALUES (12, '女', 3, '如花'); INSERT INTO `student` VALUES (13, '男', 3, '刘三'); INSERT INTO `student` VALUES (14, '男', 3, '刘一'); INSERT INTO `student` VALUES (15, '女', 3, '刘二'); INSERT INTO `student` VALUES (16, '男', 3, '刘四'); INSERT INTO `student` VALUES (17, '女', 2, '钢蛋'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`tid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES (1, '张磊老师'); INSERT INTO `teacher` VALUES (2, '李平老师'); INSERT INTO `teacher` VALUES (3, '刘海燕老师'); INSERT INTO `teacher` VALUES (4, '朱云海老师'); INSERT INTO `teacher` VALUES (5, '李杰老师'); -- ---------------------------- -- Table structure for userinfo -- ---------------------------- DROP TABLE IF EXISTS `userinfo`; CREATE TABLE `userinfo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `password` char(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of userinfo -- ---------------------------- INSERT INTO `userinfo` VALUES (2, 'xu', '654321'); SET FOREIGN_KEY_CHECKS = 1;
2 练习题
1、自行创建测试数据
pass
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
SELECT
A.student_id
FROM
( SELECT student_id, num FROM score JOIN course ON
score.course_id = course.cid WHERE course.cname = '生物' ) AS A
INNER JOIN ( SELECT
student_id, num FROM score JOIN course ON score.course_id = course.cid WHERE course.cname =
'物理' ) AS B ON A.student_id = B.student_id
WHERE
A.num > B.num
3、查询平均成绩大于60分的同学的学号和平均成绩；
select student_id,AVG(num) from score GROUP BY student_id HAVING AVG(num)>60
4、查询所有同学的学号、姓名、选课数、总成绩；
select student.sid,student.sname,COUNT(score.course_id),SUM(score.num) from score JOIN
course on score.course_id=course.cid JOIN student on score.student_id=student.sid GROUP BY
score.student_id
5、查询姓“李”的老师的个数；
select count(tid)from teacher where tname like('李%')
6、查询没学过“李”老师课的同学的学号、姓名；
SELECT
sid,
sname
FROM
student
WHERE
class_id NOT IN ( SELECT cid FROM course
JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = '李平老师' )
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select student.sid,student.sname from
(select course_id,student_id from score where course_id=1)as A INNER JOIN
(select course_id,student_id from score where course_id=2)as B on A.student_id=B.student_id
join student on student.sid=A.student_id
select score.student_id,student.sname from score join student on score.student_id
=student.sid where course_id=1 or course_id=2 GROUP BY student_id HAVING count(course_id)
>1;
8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT
student.sid,
student.sname
FROM
student
JOIN score ON student.sid =
score.student_id
WHERE
score.course_id IN ( SELECT cid FROM course JOIN teacher ON
course.teacher_id = teacher.tid WHERE teacher.tname = '李平老师' )
GROUP BY
score.student_id
HAVING
COUNT( course_id ) =
(SELECT
count( cid )
FROM
course
JOIN teacher ON course.teacher_id =
teacher.tid
WHERE
teacher.tname = '李平老师'
)
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select student.sid,student.sname from
(select student_id,course_id,num from score where course_id=2)AS A INNER JOIN
(select student_id,course_id,num from score where course_id=1)AS B on
A.student_id = B.student_id join student on student.sid=A.student_id where A.num<B.num
GROUP BY A.student_id
10、查询有课程成绩小于60分的同学的学号、姓名；
select student.sid,student.sname from student join score on student.sid=score.student_id
where score.num<60 GROUP BY score.student_id
11、查询没有学全所有课的同学的学号、姓名；
select student.sid,student.sname from student JOIN score on student.sid=score.student_id
GROUP BY score.student_id HAVING count(score.course_id)!=
(select count(cid) from course)
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
select student.sid,student.sname from student JOIN score on student.sid=score.student_id
where score.course_id in (select course_id from score where student_id=1) and student_id !
=1 GROUP BY score.student_id
13 查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
- select student.sid,student.sname from student join score on student.sid=score.student_id
where
-- score.course_id in (select course_id from score where student_id=1) and student_id !=1
GROUP BY
-- score.student_id HAVING count(score.course_id)=(select count(course_id) from score where
student_id=1)
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
SELECT
student_id,
student.sname
FROM
score join student on score.student_id=student.sid
WHERE
student_id IN (
SELECT
student_id
FROM
score
WHERE
student_id != 2
GROUP BY
student_id
HAVING
count( 1 )
= ( SELECT count( 1 ) FROM score WHERE student_id = 2 )
)
AND
course_id IN ( SELECT course_id FROM score WHERE student_id = 2 )
GROUP BY
student_id
HAVING
count( 1 ) = ( SELECT count( 1 ) FROM score WHERE student_id = 2 )
15、删除学习“叶平”老师课的SC表记录；
DELETE from score where course_id in (
select cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid where
teacher.tname='李平老师'
)
16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②
插入“002”号课程的平均成绩；
INSERT INTO score ( student_id, course_id, num ) SELECT
student_id,
2,
(SELECT
AVG( num )
FROM
score
WHERE
course_id = 2)
FROM
score
WHERE
course_id != 2;
17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式
显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
-- 思路：
-- 1 这里如果不加group by每个student_id出现的次数和该student_id在sc表中出现的次数相同
-- 2 这里 实际上在外层s1时已经确定了student_id所以 (select num from score as s2 where
s2.student_id=s1.student_id and course_id=1)as 语文 这里拿到的是一个定值，该列是这一个定值
，然后在去循环该student_id的其他科目，那么其他列也是定值，到这里s1.student_id完成第一次循环
，但是在sc表中还有该student_id还有其他成绩记录，所以会继续遍历，如果该student_id在sc表中有3
次记录，那么就会生成3条一样的数据
-- 3 然后分组去重后，为count 和avg 提供条件
-- 4 可以简单理解为 python中的嵌套循环 s1是外层 s2是内层
select student_id,
(select num from score as s2 where s2.student_id=s1.student_id and course_id=1)as 语文,
(select num from score as s2 where s2.student_id=s1.student_id and course_id=4)as 数学,
(select num from score as s2 where s2.student_id=s1.student_id and course_id=3)as 英语,
count(s1.course_id),
avg(s1.num)
from score as s1 GROUP BY student_id desc
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id as 课程id,MAX(num) as 最高分,min(num) as 最低分
from score GROUP BY course_id
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id,avg(num),sum(case when num<60 then 0 else 1 end),sum(1),sum(case when
num<60 then 0 else 1 end)/sum(1)as passrate from score GROUP BY course_id ORDER BY avg
(num)asc,passrate desc
20、课程平均分从高到低显示(以及现实任课老师）；
select course_id,avg(num),teacher.tname from score left join course on
score.course_id=course.cid LEFT JOIN teacher on teacher.tid=course.teacher_id GROUP BY
course_id
ORDER BY AVG( num ) DESC
select course_id,avg(if(isnull(score.num),0,score.num)),teacher.tname from score left join
course on score.course_id=course.cid LEFT JOIN teacher on teacher.tid=course.teacher_id
GROUP BY course_id
ORDER BY AVG( num ) DESC
--
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select course_id,
(select num from score as s2 where s2.course_id=s1.course_id ORDER BY num desc LIMIT 1)as
one,
(select num from score as s2 where s2.course_id=s1.course_id ORDER BY num desc LIMIT 1,1)as
two,
(select num from score as s2 where s2.course_id=s1.course_id ORDER BY num desc LIMIT 2,1)as
three
from score as s1 GROUP BY course_id
考虑成绩并列
-- select course_id,
-- (select num from score as s2 where s2.course_id=s1.course_id group by num ORDER BY num
desc LIMIT 0,1)as one,
-- (select num from score as s2 where s2.course_id=s1.course_id group by num ORDER BY num
desc LIMIT 1,1)as two,
-- (select num from score as s2 where s2.course_id=s1.course_id group by num ORDER BY num
desc LIMIT 2,1)as three
-- from score as s1 GROUP BY course_id
--
22、查询每门课程被选修的学生数；
select course_id,count(student_id) from score GROUP BY course_id
23、查询出只选修了一门课程的全部学生的学号和姓名；
select student.sid,student.sname from student join score on student.sid=score.student_id
GROUP BY score.student_id HAVING count(score.course_id)=1
24、查询男生、女生的人数；
select gender,count(sid) from student GROUP BY gender
25、查询姓“张”的学生名单；
select student.sname from student where student.sname like ('张%')
26、查询同名同姓学生名单，并统计同名人数；
select student.sname,count(student.sid) from student GROUP BY student.sname
HAVING COUNT(sid)>1
27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select score.course_id,AVG(score.num) from score GROUP BY course_id ORDER BY avg
(score.num),course_id DESC
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
select student.sid,student.sname,avg(score.num) from student join score on
student.sid=score.student_id GROUP BY score.student_id HAVING avg(score.num)>85
29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select student.sname,score.num from student join score on student.sid=score.student_id
join course on course.cid=score.course_id where course.cname='物理' and score.num<60
30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select student.sid,student.sname,score.num from student join score on
student.sid=score.student_id where score.course_id=3 and num>80
31、求选了课程的学生人数
select count(sid) from student where sid in
(select student_id from student inner join score on student.sid=score.student_id
GROUP BY student_id )
32、查询选修“李平”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
-- select score.course_id,max(num) from student join score on student.sid=score.student_id
where score.course_id in
-- (select course.cid from course join teacher on course.teacher_id=teacher.tid where
teacher.tname='李平老师')
-- GROUP BY score.course_id
33、查询各个课程及相应的选修人数；
select score.course_id,count(score.student_id) from score right join course on
score.course_id=course.cid GROUP BY course.cid
34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select * from score as s1,score as s2 where s1.sid != s2.sid and s1.course_id !
=s2.course_id and s1.num=s2.num
35、查询每门课程成绩最好的前两名；
select course_id,
-- (select student_id from score as t2 where t2.course_id=t1.course_id order by num desc
LIMIT 0,1),
-- (select student_id from score as t2 where t2.course_id=t1.course_id order by num desc
LIMIT 1,1)
-- from score as t1 GROUP BY course_id
--
select * from
(
select
student_id,course_id,num,1,
(select num from score as s2 where s2.course_id=s1.course_id GROUP BY s2.num order by
s2.num desc limit 0,1),
(select num from score as s2 where s2.course_id=s1.course_id GROUP BY s2.num ORDER BY
s2.num DESC limit 3,1)as cc
from score as s1
)as B
where B.num>B.cc
36、检索至少选修两门课程的学生学号；
select student_id from score GROUP BY student_id having count(course_id)>1
37、查询全部学生都选修的课程的课程号和课程名；
select course.cid,course.cname from score join course on score.course_id=course.cid GROUP
BY course_id
having count(student_id)=(select count(1) from student )
38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
select student.sname,student.sid from student where student.sid not in
(select student.sid
from student join score on score.student_id=student.sid where score.course_id in
(select cid from course join teacher on course.teacher_id=teacher.tid where teacher.tname='
李平老师'));
39、查询两门以上不及格课程的同学的学号及其平均成绩；
select student_id,
(select avg(num) from score as s2 where s1.student_id=s2.student_id)
from score as s1 where num <60 GROUP BY student_id having count(course_id)>1
40、检索“004”课程分数小于60，按分数降序排列的同学学号；
select student_id from score where course_id=4 and num<60 ORDER BY num desc
41、删除“002”同学的“001”课程的成绩；
DELETE from score where student_id=2 and course_id=1
