一、综合练习
1.1 init.sql文件内容
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MysqL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MysqL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
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) NOT NULL,PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1','三年二班'),('2','三年三班'),('3','一年二班'),('4','二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
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;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1','生物','1'),'物理','2'),'体育','3'),'美术','2');
COMMIT;
-- ----------------------------
-- 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`),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;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1','1','10'),'2','9'),('5','4','66'),('6','8'),('8','3','68'),('9','99'),('10','77'),('11',('12','87'),('13',('14','79'),('15','11'),('16','67'),('17','100'),('18','5',('19',('20',('21',('22','6',('23',('24',('25',('26','7',('27',('28',('29','88'),('30','8',('31',('32',('33',('34','9','91'),('35',('36',('37','22'),('38','10','90'),('39',('40','43'),('41',('42','11',('43',('44',('45',('46','12',('47',('48',('49',('52','13','87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
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,KEY `fk_class` (`class_id`),CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1','男','理解'),'女','钢蛋'),'张三'),'张一'),'张二'),'张四'),('7','铁锤'),'李三'),'李一'),'李二'),'李四'),'如花'),'刘三'),'刘一'),'刘二'),'刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
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;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1','张磊老师'),'李平老师'),'刘海燕老师'),'朱云海老师'),'李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
1.2 从init.sql文件中导入数据
# 准备表、记录
MysqL> create database db1;
MysqL> use db1;
MysqL> source /root/init.sql
1.3 基础练习
-
查询男生、女生的人数；
-
查询姓“张”的学生名单；
-
课程平均分从高到低显示
-
查询有课程成绩小于60分的同学的学号、姓名；
-
查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
-
查询出只选修了一门课程的全部学生的学号和姓名；
-
查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
-
查询“生物”课程比“物理”课程成绩高的所有学生的学号；
-
查询平均成绩大于60分的同学的学号和平均成绩;
-
查询所有同学的学号、姓名、选课数、总成绩；
-
查询姓“李”的老师的个数；
-
查询没学过“张磊老师”课的同学的学号、姓名；
-
查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
-
查询学过“李平老师”所教的所有课的同学的学号、姓名；
1.4 进阶练习
- 查询没有学全所有课的同学的学号、姓名；
- 查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
- 删除学习“叶平”老师课的SC表记录；
- 向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
- 按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
- 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
- 按各科平均成绩从低到高和及格率的百分数从高到低顺序；
- 查询各科成绩前三名的记录:(不考虑成绩并列情况)
- 查询每门课程被选修的学生数；
- 查询同名同姓学生名单，并统计同名人数；
- 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
- 查询平均成绩大于85的所有学生的学号. 姓名和平均成绩；
- 查询课程名称为“数学”，且分数低于60的学生姓名和分数；
- 查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
- 求选了课程的学生人数
- 查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
- 查询各个课程及相应的选修人数；
- 查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
- 查询每门课程成绩最好的前两名；
- 检索至少选修两门课程的学生学号；
- 查询全部学生都选修的课程的课程号和课程名；
- 查询没学过“叶平”老师讲授的任一门课程的学生姓名；
- 查询两门以上不及格课程的同学的学号及其平均成绩；
- 检索“004”课程分数小于60，按分数降序排列的同学学号；
- 删除“002”同学的“001”课程的成绩；
二、基础练习答案
1、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
select * from(
(select * from score where course_id in (select cid from course where cname = '生物')) t1
left join
(select * from score where course_id in (select cid from course where cname = '物理')) t2
on t1.student_id = t2.student_id)
where t1.num > t2.num;
2、查询平均成绩大于60分的同学的学号和平均成绩;
# 先查看每个同学的平均分数
select student_id,avg(num) from score group by student_id;
# 在筛选成绩大于60分的同学的学号和平均成绩；
# select student_id,avg(num) from score group by student_id having avg(num) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩；
# 先查看每个同学的总成绩
select student_id,sum(num) from score group by student_id;
# 学生和课程的关系只有成绩表中存在，因此要获取每个学生选择的课程，需要通过score表
select count(sid),student_id from score group by student_id;
# 将上面两步合并
select sum(num),count(sid),student_id from score group by student_id;
# 将学生的信息和成绩选课情况拼在一起
select sid,sname,sum_num,count_stu
from student
left join
(select sum(num) sum_num,count(sid) count_stu,student_id from score group by student_id) t2
on sid = student_id;
# 还可以更严谨，那些没有选课的同学选课数和总成绩应该是0
select sid,(
CASE
WHEN sum_num is null THEN 0
ELSE sum_num
END
) as sum_num,(
CASE
WHEN count_stu is null THEN 0
ELSE count_stu
END
) as count_stu
from student
left join
(select sum(num) sum_num,student_id from score group by student_id) t2
on sid = student_id;
4、查询姓“李”的老师的个数；
# 找到所有姓李的
# 方法一
# select * from teacher where tname like '李%';
# 方法二
# select * from teacher where tname regexp '^李';
# 统计个数
select count(tid) from teacher where tname regexp '^李';
或者
select count(id) from teacher where tname like '李%';
5、查询没学过“张磊老师”课的同学的学号、姓名；
# 找到张磊老师的id
select tid from teacher where tname == '张磊老师';
# 找到张磊老师所教课程
select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师');
# 找到所有学习这门课的学生id
select student_id from score where course_id = (select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师'));
# 找到没有学过这门课的学生对应的学生学号、姓名
select sid,sname from student where sid not in
(select student_id from score where course_id = (select cid from course where teacher_id = (select tid from teacher where tname = '张磊老师'))
);
6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
# 先查询学习课程id为1的所有学生
select * from score where course_id = 1;
# 先查询学习课程id为2的所有学生
select * from score where course_id = 2;
# 把这两张表按照学生的id 内连接起来 去掉只学习某一门课程的学生
select t1.student_id from
(select student_id from score where course_id = 1) t1
inner join
(select student_id from score where course_id = 2) t2
on t1.student_id = t2.student_id
# 根据学号在学生表中找到对应的姓名
select sid,sname from student where sid in (select t1.student_id from (select student_id from score where course_id = 1) t1 inner join (select student_id from score where course_id = 2) t2 on t1.student_id = t2.student_id);
7、查询学过“李平老师”所教的所有课的同学的学号、姓名；
#找到李平老师的tid
select tid from teacher where tname ='李平老师';
# 找到李平老师教的所有课程cid
select cid from course where teacher_id in (select tid from teacher where tname ='李平老师');
# 找到李平老师教的所有课程数
select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师');
# 找到所有学习李平老师课程的学生
select * from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师'));
# 查看所有学习李平老师课程的学生选课数
select student_id,count(course_id) from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id;
# 找到所有选择了李平老师所有课程的学生id
select student_id from (
select student_id,count(course_id) course_count from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id) t1
where t1.course_count =
(select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师'));
# 找到学生的其他信息
select sid,sname from student where sid in (
select student_id from (
select student_id,count(course_id) course_count from score where course_id in ( select cid from course where teacher_id in (select tid from teacher where tname ='李平老师')) group by student_id) t1
where t1.course_count =
(select count(cid) from course where teacher_id in (select tid from teacher where tname ='李平老师'))
);
8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
# 先找到每个学生的课程编号“1”的和课程编号“2”的成绩组成一张表
select t1.student_id from (select num num2,student_id from score where course_id = 2) t2 inner join (select student_id,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id
# 再找到课程编号“2”的成绩比课程编号“1”课程低的所有学生的学号
select t1.student_id from (select num num2,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1
# 再找到所有学生的学号、姓名
select sid,sname from student where sid in(select t1.student_id from (select num num2,num num1 from score where course_id = 1) t1 on t1.student_id = t2.student_id where num2 < num1);
9、查询有课程成绩小于60分的同学的学号、姓名；
# 先查询成绩小于60分的同学的学号
select distinct student_id from score where num < 60;
# 再查询有课程成绩小于60分的同学的学号、姓名
select sid,sname from student where sid in (select distinct student_id from score where num < 60);
10、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；
# 先看看学号为1的同学都学了哪些课程
select course_id from score where student_id = 1
# 找到学习 学号为1的同学所学课程 的学号
select distinct student_id from score where course_id in (select course_id from score where student_id = 1);
# 找到学习 学号为1的同学所学课程 的学号\姓名
select sid,sname from student where sid in (select distinct student_id from score where course_id in (select course_id from score where student_id = 1));
11、课程平均分从高到低显示
select course_id,avg(num) avg_num from score group by course_id order by avg_num desc;
12、查询出只选修了一门课程的全部学生的学号和姓名；
# 查询出只选修了一门课程的全部学生的学号
select student_id,count(student_id) from score group by student_id having count(student_id) =1;
# 查询出只选修了一门课程的全部学生的学号和姓名；
select sid,sname from student where sid in (select student_id from score group by student_id having count(student_id) =1);
13、查询男生、女生的人数；
select gender,count(sid) from student group by gender;
14、查询姓“张”的学生名单；
select * from student where sname like '张%';
15、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
# 查询成绩的最高分
select course_id c1,max(num) from score group by course_id
# 查询成绩的最低分
select course_id c1,min(num) from score group by course_id
# 查询成绩的最高分和最低分拼接
select * from ( (select course_id c1,max(num) from score group by course_id) t1 inner join (select course_id c2,min(num) from score group by course_id) t2 on t1.c1 = t2.c2 );
# 格式整理
select t1.c1,t1.max_num,t2.min_num from ( (select course_id c1,max(num) max_num from score group by course_id) t1 inner join (select course_id c2,min(num) min_num from score group by course_id) t2 on t1.c1 = t2.c2 );
三、进阶练习答案
1、查询没有学全所有课的同学的学号、姓名；
# 先统计一共有多少门课程
select count(cid) from course;
# 查看每个学生选择的课程书
select count(course_id) from score group by student_id;
# 查询所学课程数小于总课程数的学生学号
select student_id
from (select count(course_id) c_course_id,student_id from score group by student_id) t1
where t1.c_course_id < (select count(cid) from course) ;
# 查询没有学全所有课的同学的学号、姓名；
select sid,sname from student where sid in (
select student_id from (select count(course_id) c_course_id,student_id from score group by student_id
) t1 where t1.c_course_id < (select count(cid) from course)
) ;
2、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
# 先查询2号同学学了哪些课程
select * from score where student_id =2;
# 找到学习了2号同学没学习课程的所有同学（找到所有和2号同学学习的课程不一样的同学）
select student_id from score where course_id not in (select course_id from score where student_id=2)
# 找到score表中所有的学生并且把 2号同学 以及（和2号同学学习的课程不一样的同学）排除出去
select student_id from score where student_id not in (select student_id from score where course_id not in (select course_id from score where student_id=2)) and student_id !=2
# 对剩余的和2号同学所选课程没有不同的同学所选课程数进行统计，如果和2号同学的课程数相同，就是选择了相同的课程
select student_id from score where student_id not in (
select student_id from score where course_id not in (select course_id from score where student_id=2)
) and student_id !=2
group by student_id
having count(course_id)= (select count(course_id) from score where student_id=2);
3、删除学习“叶平”老师课的SC(score)表记录；
# 先查出李平老师的id
select tid from teacher where tname = '李平老师';
# 查看李平老师所教授的课程
select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 查看李平老师所教课程的成绩数据
select * from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'));
# 执行删除命令
delete from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'));
4、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
# 先找寻上过2号课程的同学
select student_id from score where course_id = 2;
# 再找到没上过2号课程的所有同学
select * from student where sid not in (select student_id from score where course_id = 2);
# 计算出学习2号课程的同学的平均成绩
select avg(num) from score where course_id = 2 group by course_id;
# 用笛卡尔积将上述两个表拼起来
select * from (select sid from student where sid not in (select student_id from score where course_id = 2)) t1,(select avg(num) from score where course_id = 2 group by course_id) t2;
# 向SC表中插入记录
insert into score (course_id,student_id,num) select 2,t1.sid,t2.avg_num from (select sid from student where sid not in (select student_id from score where course_id = 2)) t1,(select avg(num) avg_num from score where course_id = 2 group by course_id) t2;
5、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,有效平均分；
# 查看每个学生的数学成绩
select student_id,num from score where course_id = (select cid from course where cname = '数学');
# 查看每个学生的语文成绩
select student_id,num from score where course_id = (select cid from course where cname = '语文');
# 查看每个学生的英语成绩
select student_id,num from score where course_id = (select cid from course where cname = '英语');
# 查看每个学生的平均成绩
select student_id,avg(num),count(num) from score group by student_id;
# 将上面的几张表拼接起来,为了生成所有学生的信息，用student表作为左连接的第一张表
select sid 学生ID,t2.num 语文,t1.num 数学,t3.num 英语,t4.count_course 有效课程数,t4.avg_num 有效平均分 from student
left join (select student_id,num from score where course_id = (select cid from course where cname = '数学')) t1
on student.sid = t1.student_id
left join (select student_id,num from score where course_id = (select cid from course where cname = '语文')) t2
on student.sid = t2.student_id
left join (select student_id,num from score where course_id = (select cid from course where cname = '英语')) t3
on student.sid = t3.student_id
left join (select student_id,avg(num) avg_num,count(num) count_course from score group by student_id) t4
on student.sid = t4.student_id
6、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id 课程ID,max(num) 最高分,min(num) 最低分 from score group by course_id;
7、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
# 方法1:
# 先求平均成绩
select course_id,avg(num) from score group by course_id;
# 解决计算各科及格率的问题
所有及格的人/所有人数
select t1.course_id,t1.count1/t2.count2 from
(select course_id,count(course_id) count1 from score where num>60 group by course_id) t1
left join
(select course_id,count(course_id) count2 from score group by course_id) t2
on t1.course_id = t2.course_id;
# 根据上述内容进行表的拼接
select t_out1.course_id,t_out1.avgnum,t_out2.pass_per from
(select course_id,avg(num) avgnum from score group by course_id ) t_out1
left join
(select t1.course_id,t1.count1/t2.count2 pass_per from
(select course_id,count(course_id) count2 from score group by course_id) t2
on t1.course_id = t2.course_id) t_out2
on t_out1.course_id = t_out2.course_id
# 加上排序
select t_out1.course_id,t_out2.pass_per from (select course_id,avg(num) avgnum from score group by course_id ) t_out1 left join (select t1.course_id,t1.count1/t2.count2 pass_per from (select course_id,count(course_id) count1 from score where num>60 group by course_id) t1 left join (select course_id,count(course_id) count2 from score group by course_id) t2 on t1.course_id = t2.course_id) t_out2 on t_out1.course_id = t_out2.course_id order by avgnum,pass_per desc;
# 方法2
# 使用case when直接计算合格率
select
sum(case when num>60 then 1 else 0 end)/count(course_id)
from score group by course_id
# 加上课程id和平均值
select course_id,sum(case when num>60 then 1 else 0 end)/count(course_id)
from score group by course_id
# 加上排序
select course_id,avg(num) avgnum,sum(case when num>60 then 1 else 0 end)/count(course_id) pass_per
from score group by course_id
order by avgnum,pass_per desc;
8、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select
t1.sid,t1.student_id,t1.course_id,t1.num from score t1
left join
(
select sid,course_id,(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_num,(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 2,1) as third_num
from score as s1
) t2
on t1.sid = t2.sid
where t1.num = t2.first_num or t1.num = t2.second_num or t1.num = t2.third_num;
9、查询每门课程被选修的学生数；
select course_id,count(course_id) from score group by course_id;
10、查询同名同姓学生名单，并统计同名人数；
select sname,count(1) as count from student group by sname;
11、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select course_id,avg(if(isnull(num),num)) as avg from score group by course_id order by avg asc,course_id desc;
12、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
select student_id,num)) from score left join student on score.student_id = student.sid group by student_id;
13、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select student.sname,score.num from score
left join course on score.course_id = course.cid
left join student on score.student_id = student.sid
where score.num < 60 and course.cname = '数学'
14、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select * from score where score.student_id = 3 and score.num > 80
15、求选了课程的学生人数
select sid,sname from student where sid not in (select student_id from score group by student_id);
16、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
# 先找到“杨艳”老师的教师id
select tid from teacher where tname = '杨艳';
# 再找到杨艳老师教的所有课程
select cid from course where teacher_id in (select tid from teacher where tname = '杨艳');
# 再找到杨艳老师教的所有课程的最高分
select max(num) from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));
# 再找到杨艳老师教的所有课程的最高分对应的学生
select distinct student_id,num from score
where num = (select max(num) from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')))
and course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));
# 找到学生的姓名
select student.sname,t1.num from(
select distinct student_id,num from score
where num = (select max(num) from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')))
and course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'))
) t1
left join
student
on
t1.student_id = student.sid;
17、查询各个课程及相应的选修人数；
select course.cname,count(1) from score
left join course on score.course_id = course.cid
group by course_id;
18、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1,score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;
19、查询每门课程成绩最好的前两名；
先查询每条数据对应学科成绩的第一名和第二名，这里必须要保留所有的s1，以便后续进行连表查询
select sid,1) as second_num
from score as s1
按照sid连表，把学生的成绩和对应的第一名、第二名成绩连起来
select
* from score t1
left join
(
select sid,1) as second_num
from score as s1
) t2
on t1.sid = t2.sid
判断如果学生的成绩是第一名、第二名的成绩，那么就符合条件，显示学生的id、学科和成绩
select
t1.sid,1) as second_num
from score as s1
) t2
on t1.sid = t2.sid
where t1.num = t2.first_num or t1.num = t2.second_num;
20、检索至少选修两门课程的学生学号；
select student_id from score group by student_id having count(student_id) > 1;
21、查询全部学生都选修的课程的课程号和课程名；
# 先查看一共有多少学生
select count(sid) from student;
# 查看哪一门课选秀的学生个数和学生的总个数相等
select course_id from score group by course_id having count(student_id) = (select count(sid) from student);
22、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
# 先查看要查找老师的id
select tid from teacher where tname = '李平老师';
# 查看该老师交了哪些课程
select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')
# 看看有多少学生学习了该老师的课程
select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师'));
# 把不在上表中的学生姓名查出来
select sname from student where sid not in (select distinct student_id from score where course_id in (select cid from course where teacher_id in (select tid from teacher where tname = '李平老师')));
23、查询两门以上不及格课程的同学的学号及其平均成绩；
select student_id,avg(num) from score where num<60 group by student_id having count(num)>=2;
24、检索“004”课程分数小于60，按分数降序排列的同学学号；
select student_id from score where num< 60 and course_id = 4 order by num desc;
25、删除“002”同学的“001”课程的成绩；
delete from score where course_id = 1 and student_id = 2；