一. 题目
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询学生表中男女生各有多少人
3、查询物理成绩等于100的学生的姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
5、查询所有学生的学号，姓名，选课数，总成绩
6、 查询姓李老师的个数
7、 查询没有报李平老师课的学生姓名
8、 查询物理课程比生物课程高的学生的学号
9、 查询没有同时选修物理课程和体育课程的学生姓名
10、查询挂科超过两门(包括两门)的学生姓名和班级
11 、查询选修了所有课程的学生姓名
12、查询李平老师教的课程的所有成绩记录
13、查询全部学生都选修了的课程号和课程名
14、查询每门课程被选修的次数
15、查询之选修了一门课程的学生姓名和学号
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
17、查询平均成绩大于85的学生姓名和平均成绩
18、查询生物成绩不及格的学生姓名和对应生物分数
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
20、查询每门课程成绩最好的前两名学生姓名
二. 准备表和记录
-- 取消外键约束
SET FOREIGN_KEY_CHECKS=0;
-- 创建班级表
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- 为班级表插记录
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- 创建老师表
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- 为老师表插记录
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
-- 创建课程表, 课程表teacher_id关联老师表的tid
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- 为课程表插记录
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- 创建学生表, 学生表class_id关联课程表的cid
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- 为学生表插记录
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- 创建成绩表, 成绩表student_id关联学生表sid, 成绩表course_id关联课程表cid
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`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- 为成绩表插记录
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- 设置外键约束
SET FOREIGN_KEY_CHECKS=1;
三. 答案 ```python
1. 查询所有的课程的名称以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
2. 查询学生表中男女生各有多少人
SELECT
gender,
count( sid )
FROM
student
GROUP BY
gender;
3. 查询物理成绩等于100的学生的姓名
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
score.num = 100
AND course_id IN ( SELECT cid FROM course WHERE cname = '物理' )
GROUP BY
score.student_id
);
4. 查询物理成绩等于100的学生的姓名
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num )> 80 ) t1 ON t1.student_id = student.sid;
5. 查询所有学生的学号，姓名，选课数，总成绩(错题:连表条件用inner还是right或者left的问题)
SELECT
student.sid,
student.sname,
t1.count_course_id,
t1.sum_num
FROM
student
INNER JOIN ( SELECT student_id, count( course_id ) AS count_course_id, sum( num ) sum_num FROM score GROUP BY student_id ) t1 ON t1.student_id = student.sid;
# 订正: 可能学生没有选课,需要排除
SELECT
student.sid,
student.sname,
t1.count_course_id,
t1.sum_num
FROM
student # 这里应该使用right join而不是使用inner join
RIGHT JOIN ( SELECT student_id, count( course_id ) AS count_course_id, sum( num ) sum_num FROM score GROUP BY student_id ) t1 ON t1.student_id = student.sid;
6. 查询姓李老师的个数
SELECT
count( tid )
FROM
teacher
WHERE
tname LIKE '李%';
7. 查询没有报李平老师课的学生姓名(2种解题思路)
# 方式一: 去重实现
SELECT
student.sname
FROM
student
WHERE
student.sid NOT IN (
SELECT
student_id
FROM
score
WHERE
score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' )
GROUP BY
student_id
);
# 方式二: 分组实现
SELECT
student.sname
FROM
student
WHERE
student.sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' ));
8. 查询物理课程比生物课程高的学生的学号
SELECT
t1.student_id
FROM
(
SELECT
student_id,
num
FROM
score
WHERE
score.course_id IN ( SELECT cid FROM course WHERE cname = '物理' )) t1
INNER JOIN (
SELECT
student_id,
num
FROM
score
WHERE
score.course_id IN ( SELECT cid FROM course WHERE cname = '生物' )) t2 ON t1.student_id = t2.student_id
WHERE
t1.num > t2.num;
9. 查询没有同时选修物理课程和体育课程的学生姓名
SELECT
student.sname
FROM
student
WHERE
student.sid IN (
SELECT
student_id
FROM
score
WHERE
score.course_id IN (
SELECT
cid
FROM
course
WHERE
cname IN ( '物理', '体育' ))
GROUP BY
student_id
HAVING
count( course_id )= 1
);
10. 查询挂科超过两门(包括两门)的学生姓名和班级
SELECT
class.caption,
student.sname
FROM
class
INNER JOIN student ON class.cid = student.class_id
WHERE
student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id )>= 2 );
11. 查询选修了所有课程的学生姓名
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
count( course_id )=(
SELECT
count( cid ) AS count_cid
FROM
course
));
12. 查询李平老师教的课程的所有成绩记录
SELECT
*
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
course
WHERE
cid IN ( SELECT tid FROM teacher WHERE tname = '李平老师' ));
13. 查询全部学生都选修了的课程号和课程名
SELECT
course.cid,
course.cname
FROM
course
WHERE
cid IN (
SELECT
course_id
FROM
score
GROUP BY
course_id
HAVING
count( score.student_id )=(
SELECT
count( sid ) AS count_sid
FROM
student
));
14. 查询每门课程被选修的次数
SELECT
course.cid,
course.cname,
t1.count_course_id
FROM
course
INNER JOIN ( SELECT course_id, count( course_id ) AS count_course_id FROM score GROUP BY course_id ) t1 ON t1.course_id = course.cid;
15. 查询之选修了一门课程的学生姓名和学号
SELECT
student.sid,
student.sname
FROM
student
WHERE
sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING count( course_id )= 1 );
16. 查询所有学生考出的成绩并按从高到低排序（成绩去重）(错题:答非所问)
SELECT
student.sid,
student.sname,
t1.score_num,
t1.course_cname
FROM
student
INNER JOIN (
SELECT
score.student_id AS student_id,
course.cname AS course_cname,
score.num AS score_num
FROM
course
INNER JOIN score ON score.course_id = course.cid
) t1 ON student.sid = t1.student_id
ORDER BY
t1.score_num DESC;
# 正确答案
select distinct num from score num order by num desc;
17. 查询平均成绩大于85的学生姓名和平均成绩
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num )> 85 ) t1 ON t1.student_id = student.sid;
18. 查询生物成绩不及格的学生姓名和对应生物分数
SELECT
student.sname,
t1.num
FROM
student
INNER JOIN (
SELECT
score.student_id AS student_id,
score.num AS num
FROM
score
WHERE
course_id = ( SELECT cid FROM course WHERE cname = '生物' )
AND num < 60
) t1 ON t1.student_id = student.sid;
19. 查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN ( SELECT course.cid AS cid FROM course INNER JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' )
GROUP BY
student_id
HAVING
avg( num )=(
SELECT
avg( score.num ) AS avg_num
FROM
score
WHERE
course_id IN ( SELECT course.cid AS cid FROM course INNER JOIN teacher ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' )
GROUP BY
student_id
HAVING
avg( score.num )
ORDER BY
avg_num DESC
LIMIT 1
));
20. 查询每门课程成绩最好的前两名学生姓名(难题: 三种方法实现)
# 方法一:
SELECT
student.sid,
student.sname,
t2.course_id,
t2.first_num,
t2.second_num
FROM
student
INNER JOIN (
SELECT
score.student_id AS student_id,
t1.*
FROM
score
INNER JOIN (
SELECT FIRST
.*,
SECOND.second_num
FROM
( SELECT course_id, max( num ) AS first_num FROM score GROUP BY course_id )
AS FIRST INNER JOIN (
SELECT
course_id,
max( num ) AS second_num
FROM
score
WHERE
num NOT IN ( SELECT max( num ) AS first_num FROM score GROUP BY course_id ) # 获取了最大num以后,这个最大num作为等待获取第二num的where条件排除不是最大num的,接下来通过获取的最大值就是第二num
GROUP BY
course_id
) AS SECOND ON FIRST.course_id = SECOND.course_id # 通过course_id关联2张表,通过下面的where条件,就能获取对应的student_id,就可以与学生表关联,获取到是哪个学生.
) AS t1 ON t1.course_id = score.course_id
WHERE
score.num IN ( t1.first_num, t1.second_num )) AS t2 ON t2.student_id = student.sid # 通过判断score.num在first值中的所有的score.num保证能获取到 同一个课程中并列第一,第二的学生.
ORDER BY
t2.course_id,
t2.first_num;
# 方法二:
SELECT
student.sname,
t4.*
FROM
student
INNER JOIN (
SELECT
course.cname,
t3.*
FROM
course
INNER JOIN (
SELECT
score.student_id AS student_id,
t1.course_id AS course_id,
t1.FIRST AS FIRST,
t1.SECOND AS SECOND
FROM
score
INNER JOIN (
SELECT
t1.course_id,
max( t1.num ) AS FIRST,
max( t2.num ) AS SECOND
FROM
score AS t1
INNER JOIN score AS t2 ON t2.course_id = t1.course_id
WHERE
t1.num > t2.num # 连2张同样的表,和二唯一,使用t1.num>t2.num条件在筛选出t1.num的最大num,而t2.num的最大num必然小于t1.num的最大num.
GROUP BY
t1.course_id
) AS t1 ON t1.course_id = score.course_id # 通过course_id关联2张表,通过下面的where条件,就能获取对应的student_id,就可以与学生表关联,获取到是哪个学生.
WHERE
score.num IN ( t1.FIRST, t1.SECOND )) AS t3 ON t3.course_id = course.cid # 通过判断score.num在first值中的所有的score.num保证能获取到 同一个课程中并列第一,第二的学生.
) AS t4 ON t4.student_id = student.sid
ORDER BY
t4.course_id,
t4.FIRST DESC,
t4.SECOND DESC;
# 方法三:
SELECT
student.sid,
student.sname,
t4.course_id,
t4.cname,
t4.first_num,
t4.second_num
FROM
student
INNER JOIN (
SELECT
t3.student_id,
t3.course_id,
course.cname,
t3.first_num,
t3.second_num
FROM
course
INNER JOIN (
SELECT
score.student_id,
score.course_id,
t2.first_num,
t2.second_num
FROM
score
INNER JOIN (
SELECT
t1.course_id,
max( t1.num ) AS first_num,
max( t2.num ) AS second_num
FROM
score AS t1
INNER JOIN score AS t2 ON t1.num > t2.num
GROUP BY
t1.course_id
) AS t2 ON t2.course_id = score.course_id
WHERE
t2.first_num >= score.num # 通过比较大小的方式取代上面使用in. (差别并不大, 都是为了保证并列情况能正输出)
AND t2.second_num <= score.num
) AS t3 ON t3.course_id = course.cid
) AS t4 ON t4.student_id = student.sid
ORDER BY
t4.course_id,
student.sid;
