写在前面：有的题目有可能有别的更好的解法，欢迎补充指正
数据表介绍
1.学生表
Student(SId,Sname,Sage,Ssex)
SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
2.课程表
Course(CId,Cname,TId)
CId 课程编号,Cname 课程名称,TId 教师编号
3.教师表
Teacher(TId,Tname)
TId 教师编号,Tname 教师姓名
4.成绩表
SC(SId,CId,score)
SId 学生编号,CId 课程编号,score 分数
=====学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
=====科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
=====教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
=====成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
# 查看四个表的内容
select * from Student
select * from SC
select * from Course
select * from Teacher
_________________________________________________________________________
练习题目
1 .查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
查找的01和02课程是在同一张表中且在同一列，所以使用一张表进行查询比较不是很好做
思路：使用子查询，将01，02课程的分数和sid分别查询出来做成两个表，然后再通过两张表中相同的sid进行分数比较
select s.*, a.score as score_01, b.score as score_02
from Student s,
(select sid, score from SC where cid=01) a,
(select sid, score from SC where cid=02) b
where a.sid = b.sid and a.score > b.score and s.sid = a.sid;
+------+--------+---------------------+------+----------+----------+
| SId | Sname | Sage | Ssex | score_01 | score_02 |
+------+--------+---------------------+------+----------+----------+
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 | 70.0 | 60.0 |
| 04 | 李云 | 1990-12-06 00:00:00 | 男 | 50.0 | 30.0 |
+------+--------+---------------------+------+----------+----------+
1.1 查询同时存在" 01 “课程和” 02 "课程的情况
可以根据上题进行类推
select * FROM
(select sid,score from SC where cid=01) A,
(select sid,score from SC where cid=02) B
where A.sid = B.sid;
+------+-------+------+-------+
| sid | score | sid | score |
+------+-------+------+-------+
| 01 | 80.0 | 01 | 90.0 |
| 02 | 70.0 | 02 | 60.0 |
| 03 | 80.0 | 03 | 80.0 |
| 04 | 50.0 | 04 | 30.0 |
| 05 | 76.0 | 05 | 87.0 |
+------+-------+------+-------+
1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
存在01可能不存在02 不存在显示null 只需要 用左连接 left join … on
左链接 是 以第一个left join前面的表为主，后面的表如果没有和前面的表匹配的数据，则显示NULL
select * from
(select sid,score from SC where cid=01) A
LEFT JOIN
(select sid,score from SC where cid=02) B
on A.sid = B.sid;
+------+-------+------+-------+
| sid | score | sid | score |
+------+-------+------+-------+
| 01 | 80.0 | 01 | 90.0 |
| 02 | 70.0 | 02 | 60.0 |
| 03 | 80.0 | 03 | 80.0 |
| 04 | 50.0 | 04 | 30.0 |
| 05 | 76.0 | 05 | 87.0 |
| 06 | 31.0 | NULL | NULL |
+------+-------+------+-------+
1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
和上题同理 只不过是用右链接
select * from
(select sid,score from SC where cid=01) A
right JOIN
(select sid,score from SC where cid=02) B
on A.sid = B.sid
where A.sid is NULL;
+------+-------+------+-------+
| sid | score | sid | score |
+------+-------+------+-------+
| NULL | NULL | 07 | 89.0 |
+------+-------+------+-------+
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
查询平均成绩需要用分组（group by），题目要求是学生信息和成绩，而成绩是需要分组的，只需要进行一次子表查询就行
#1
select s.sid,s.sname,p.avg from
Student s,(select sid,avg(score) avg from SC GROUP BY sid) p
where s.sid=p.sid and p.avg>=60;
#2
select s.sid,s.sname,p.avg from
Student s,(select sid,avg(score) avg from SC GROUP BY sid having avg>=60) p
where s.sid=p.sid ;
#3
select p.sid,s.sname,p.avg from
(select sid,avg(score) avg from SC GROUP BY sid having avg>=60) p
left join
Student s
on s.sid=p.sid ;
+------+--------+----------+
| sid | sname | avg |
+------+--------+----------+
| 01 | 赵雷 | 89.66667 |
| 02 | 钱电 | 70.00000 |
| 03 | 孙风 | 80.00000 |
| 05 | 周梅 | 81.50000 |
| 07 | 郑竹 | 93.50000 |
+------+--------+----------+
3.查询在 SC 表存在成绩的学生信息
存在成绩则在SC表中存在sid，只需要将学生表中sid和SC表中sid相匹配即可
select distinct Student.*
from
Student,SC
where Student.sid = SC.sid;
+------+--------+---------------------+------+
| SId | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-12-20 00:00:00 | 男 |
| 04 | 李云 | 1990-12-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-01-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-01-01 00:00:00 | 女 |
+------+--------+---------------------+------+
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
同2.
先创建一个子表-cs-表中将SC中数据整理 因为要求是没成绩显示为 null,所以在cs子表在前情况下应该使用右链接进行操作
select s.sid,s.sname,cs.count,cs.sum from
(select sid,count(score) count,sum(score) sum from SC GROUP BY sid) cs
right JOIN
Student s
on s.sid=cs.sid;
+------+--------+-------+-------+
| sid | sname | count | sum |
+------+--------+-------+-------+
| 01 | 赵雷 | 3 | 269.0 |
| 02 | 钱电 | 3 | 210.0 |
| 03 | 孙风 | 3 | 240.0 |
| 04 | 李云 | 3 | 100.0 |
| 05 | 周梅 | 2 | 163.0 |
| 06 | 吴兰 | 2 | 65.0 |
| 07 | 郑竹 | 2 | 187.0 |
| 09 | 张三 | NULL | NULL |
| 10 | 李四 | NULL | NULL |
| 11 | 李四 | NULL | NULL |
| 12 | 赵六 | NULL | NULL |
| 13 | 孙七 | NULL | NULL |
+------+--------+-------+-------+
4.1查有成绩的学生信息
case when实现行列转换时会出现多条记录，如果不用聚合函数直接进行group by分组，那么检索的是基表里分组字段的第一条记录
如果使用max()函数之后再进行group by分组，那么就会检索每个字段的最大值然后再分组
select sid,
max(case when cid='01' then score else "" end) as "cid_01",
max(case when cid='02' then score else "" end) as "cid_02",
max(case when cid='03' then score else "" end) as "cid_03"
from SC
group by sid;
===为什么要用max函数
= 如果不用max的话 数据很糟糕 可以看出数据是不完整的,或者说只显示的第一个数据
select sid,
(case when cid='01' then score else "" end) as "cid_01",
(case when cid='02' then score else "" end) as "cid_02",
(case when cid='03' then score else "" end) as "cid_03"
from SC
group by sid;
+------+--------+--------+--------+
| sid | cid_01 | cid_02 | cid_03 |
+------+--------+--------+--------+
| 01 | 80.0 | | |
| 02 | 70.0 | | |
= 查看未分组的数据,可以得出，分组时默认是选第一个，如果使用max()函数后，就会检索某个字段最大值，然后进行分组
select sid,
(case when cid='01' then score else "" end) as "cid_01",
(case when cid='02' then score else "" end) as "cid_02",
(case when cid='03' then score else "" end) as "cid_03"
from SC;
+------+--------+--------+--------+
| sid | cid_01 | cid_02 | cid_03 |
+------+--------+--------+--------+
| 01 | 80.0 | | |
| 01 | | 90.0 | |
| 01 | | | 99.0 |
| 02 | 70.0 | | |
| 02 | | 60.0 | |
| 02 | | | 80.0 |
5.查询「李」姓老师的数量
使用like进行模糊匹配
select count(*) 李姓老师数量 from Teacher
where tname LIKE '李%';
+--------------------+
| 李姓老师数量 |
+--------------------+
| 1 |
+--------------------+
6.查询学过「张三」老师授课的同学的信息
通过张三老师找到他教的课程的cid
只需要将学生上课的cid与老师课程cid相匹配
select s.* from Student s,SC
where SC.cid =
(select cid from Course
where tid = (select tid from Teacher
where tname='张三')) and s.sid=SC.sid;
+------+--------+---------------------+------+
| SId | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-12-20 00:00:00 | 男 |
| 04 | 李云 | 1990-12-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-01-01 00:00:00 | 女 |
+------+--------+---------------------+------+
7.查询没有学全所有课程的同学的信息
先查出所有课程数量，计算每个学生上的课程，再筛选学的课程数<课程总数的人
该表应当是以学生表为主，所以应该使用左连接或右链接，如果学生没有选课，关联查询后的表是会出现为NULL，为了方便统计数据，引入IFNULL（a,b）函数(判断字段值a是否为空，若是，则为该字段值则为b)
select s.sid,s.sname,IFNULL(cs.count,0) num from
(select sid,count(score) count from SC GROUP BY sid) cs
right JOIN
Student s
on s.sid=cs.sid
where IFNULL(cs.count,0)<(select count(distinct cid) as cid_num from Course);
+------+--------+-----+
| sid | sname | num |
+------+--------+-----+
| 05 | 周梅 | 2 |
| 06 | 吴兰 | 2 |
| 07 | 郑竹 | 2 |
| 09 | 张三 | 0 |
| 10 | 李四 | 0 |
| 11 | 李四 | 0 |
| 12 | 赵六 | 0 |
| 13 | 孙七 | 0 |
+------+--------+-----+
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
先用SC表查看01同学学的所有课程的cid
通过判断SC表中和01同学cid相同的sid即可，在通过sid去重得到最终sid与student表左链接（忽略student中没有分数的人）
select Student.* from
(select distinct(sid) from SC
where cid in
(select cid from SC where sid = '01')) A
left join
Student
on A.sid = Student.sid;
+------+--------+---------------------+------+
| SId | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-12-20 00:00:00 | 男 |
| 04 | 李云 | 1990-12-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-01-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-01-01 00:00:00 | 女 |
+------+--------+---------------------+------+
9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
把每个人的课程数都罗列出来，然后合并字符串，找到跟01同学的课程字符串相同的人(排除01同学)
select sid,
concat(max(case when cid='01' then cid else "" end),max(case when cid='02' then cid else "" end),max(case when cid='03' then cid else "" end) ) as cids
from SC
group by sid
having cids =
(select
concat(max(case when cid='01' then cid else "" end),max(case when cid='02' then cid else "" end),max(case when cid='03' then cid else "" end) ) as cids
from SC
group by sid
having sid = "01") and sid <> "01"; # <>为不等于!=
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
先在Teacher表找到张三老师对应的tid, 再通过Course表找到其讲授的课程
再通过SC表筛选出学过这些课的学生的sid，最后再通过Student表排除学过些sid,并匹配sname
select sname from Student
where sid not in
(select sid from SC
where cid in
(select cid from Course
where tid in
(select tid from Teacher` where tname = "张三")));
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
先将分数表中不及格的筛选出来，然后再进行分组，限制不及格的科目数应是两门以上
select s.sid,s.sname,avg(SC.score) from Student s,SC
where s.sid=SC.sid AND SC.score<60
GROUP BY SC.sid
having count(*)>1;
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select Student.sname,A.* from Student
right join
(select sid,score from SC
where score < 60 and cid = '01'
order by score desc) A
on Student.sid = A.sid;
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
利用 case when 将一列变成多列
利用max（）进行全部匹配，否则只匹配第一个
select sid,avg(score),
max(case when cid='01' then score else '' end) score_01,
max(case when cid='02' then score else '' end) score_02,
max(case when cid='03' then score else '' end) score_03
from SC
GROUP BY sid
ORDER BY avg(score) desc;
+-----+------------+----------+----------+----------+
| sid | avg(score) | score_01 | score_02 | score_03 |
+-----+------------+----------+----------+----------+
| 07 | 93.50000 | | 89.0 | 98.0 |
| 01 | 89.66667 | 80.0 | 90.0 | 99.0 |
| 05 | 81.50000 | 76.0 | 87.0 | |
| 03 | 80.00000 | 80.0 | 80.0 | 80.0 |
| 02 | 70.00000 | 70.0 | 60.0 | 80.0 |
| 04 | 33.33333 | 50.0 | 30.0 | 20.0 |
| 06 | 32.50000 | 31.0 | | 34.0 |
+-----+------------+----------+----------+----------+
14.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select
sc.CId ,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率
from SC sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC;
+-----+--------+--------+----------+----------+--------+--------+--------+--------+
| CId | 最高分 | 最低分 | 平均分 | 选修人数 | 及格率 | 中等率 | 优良率 | 优秀率 |
+-----+--------+--------+----------+----------+--------+--------+--------+--------+
| 01 | 80.0 | 31.0 | 64.50000 | 6 | 0.6667 | 0.3333 | 0.3333 | 0.0000 |
| 02 | 90.0 | 30.0 | 72.66667 | 6 | 0.8333 | 0.0000 | 0.5000 | 0.1667 |
| 03 | 99.0 | 20.0 | 68.50000 | 6 | 0.6667 | 0.0000 | 0.3333 | 0.3333 |
+-----+--------+--------+----------+----------+--------+--------+--------+--------+