sql语句50题的个人解法+理解（MySQL）

前言

  写这篇文章主要是自觉sql方面的不足，在网上找练习题的时候找到了CSDN博主启明星的指引的sql语句练习50题(Mysql版)这篇文章，感觉很有用。于是将自己做题的思路和思考记录下来，以备平常复习。本文也有参考其他文献，参考的文献网址也已记录下来。如果有新的解法或者自己思考的内容有错误，烦请大家能够补缺和指正。再次感谢启明星的指引和其他文献作者的贡献。
  本人用的是mysql的5.7版本。建表语句和数据放在最后的附录中。

练习题

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

解题关键思路：01课程和02课程的比较，说明score这个表需要使用2次，分别查询为01、02课程的分数；需要展示课程分数，说明返回的结果中需要分别将2个score表的分数返回

select s.*,a.score 01score,b.score 02score from student s 
join 
(select * from score sc where sc.cid="01") a
on s.sid=a.sid
join
(select * from score sc where sc.cid="02") b 
on a.sid=b.sid where a.score>b.score;

上述可以继续优化：

select s.*,a.score 01score,b.score 02score from student s 
join score a on s.sid=a.sid and a.cid="01"
join score b on s.sid=b.sid and b.cid="02" 
where a.score>b.score;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

同上，只是比较成绩的时候用小于号

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

解题关键：使用groupby根据学生编号和学生姓名进行分组计算，再用having来比较大小

select s.sid,s.sname,AVG(score) avgscore from student s
join score sc on s.sid=sc.sid 
group by s.sid,s.sname having avgscore>=60;

小优化：可以用round()函数对小数点位数进行控制

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

同上，只是比较的时候用小于号

注意：上述的sql语句仅限于对有成绩的学生进行查询，如果将无成绩（学生没有选修该课程）视为小于60分，那么还需要将没有成绩的学生union进去

select s.sid,s.sname,AVG(score) avgscore from student s
join score sc on s.sid=sc.sid 
group by s.sid,s.sname having avgscore>=60
union
select s2.sid,s2.sname,0 avgscore from student s2
where s2.sid not in 
(select distinct(sid) from score);

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select s.sid,s.sname,count(sc.cid) "选课门数",sum(sc.score) "各门总分" from student s join score sc on s.sid=sc.sid group by s.sid,s.sname;

6、查询"李"姓老师的数量

select count(1) from teacher where tname like '李%';

7、查询学过"张三"老师授课的同学的信息

select * from student where sid in (
  select distinct sid from score where cid=(
    select distinct cid from course where tid=(
      select tid from teacher where tname="张三")));

也可以将student和score两张表join起来

8、查询没学过"张三"老师授课的同学的信息

select * from student where sid not in (
  select distinct sid from score where cid=(
    select distinct cid from course where tid=(
      select tid from teacher where tname="张三")));

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

下面是错误的写法：

select * from student where sid in(
select distinct(s.sid) from student s join score sc on s.sid=sc.sid 
where sc.cid in ('01','02'));

第二个in，即in (‘01’,‘02’)，只要满足其中一个就会被选上，导致只学过01或02的学生也被选上，正确的做法是：

select s.* from student s 
join score b on s.sid = b.sid 
join score c on s.sid = c.sid where b.cid='01' and c.cid='02';

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

解题思路：不能单纯套用第九题，然后将c.cid='02’改为c.cid!=‘02’，这样无法排除b.cid='02’的情况。所以思路应该改为，先用student和score关联，筛选出cid='01’的数据，然后获取学过’02’课程的学生编号，只要再从筛选出来的数据中排除这些学生编号，剩下的就是满足题意的学生信息

select s.* from student s 
join score b on s.sid=b.sid where b.cid='01' and s.sid not in(
select c.sid from score c where c.cid='02');

还有另外的写法相对简单，不用进行表关联

select s.* from student s 
where s.sid in (select sid from score where cid='01' ) and s.sid not in(select sid from score where cid='02');

11、查询没有学全所有课程的同学的信息

解题思路：没有学全所有课程，意味着学生学习的课程数!=课程总数，难点在于怎么获取课程数、比较后还能获取到该学生的编号。提示：聚合函数不必与select语句一同使用！每个学生都要比较，所以要使用group by分组，并用having关键字进行分组后的条件比较！

select * from student where sid not in (
  select sc.sid from score sc group by sc.sid having 
  count(sc.cid) = (select count(c.cid) from course c));

一开始我是写成：select * from student where sid in ( select sc.sid from score sc group by sc.sid having count(sc.cid) < (select count(c.cid) from course c))，但对比其他人的写法之后发现一个问题，如果有学生一门都不选，那么这语句的结果集是没有包含这个学生的（因为没有数据的情况下，count函数是不会返回任何值，导致一门都不选的学生没有返回值而没有包含在in的选项中），所以需要改变思路，将学全的学生排除。

优化思路：用explain语句解析后发现上述的语句性能不是很好，对student表进行了全表扫描，对score表虽然使用了索引，但是也查询了所有的行，毕竟not in语句应该尽量少用。之所以用not in是为了解决有学生一门不选的问题，那么用左连接去保证student没有被遗漏就好了

select s.* from student s 
left join score sc on sc.sid=s.sid group by s.sid 
having count(sc.cid)<(select count(*) from course);

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select s.* from student s 
left join score sc1 on sc1.sid=s.sid
where sc1.cid in(
    select distinct(sc2.cid) from score sc2 where sc2.sid='01') 
    group by s.sid ;

另外一种写法：

select * from student where sid in(
	select distinct a.sid from score a where a.cid in(
        select a.cid from score a where a.sid='01'));

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

解题思路：再次强调，in语句只要满足其中一个条件就能被选上。我原本的思路是排除01没选择的课程的学生，排除一门都没选的学生，排除选择课程数小于01的学生。参考别人的写法后，有一种思路与我的类似，但是条件是：筛选出课程数=01的学生，再排除01没选择课程的学生，再排除01本身。sql如下：

select s.* from student s 
where s.sid in (
  select distinct(sid) from score group by sid 
    having count(cid)=(
    select count(cid) from score where sid='01'))
and s.sid not in (
  select distinct(sid) from score where cid not in (
    select cid from score where sid='01')
    group by sid)
and s.sid!='01';

在第9行的group by sid即使去掉似乎不影响结果，因为最外面的语句是单表查询，不会有笛卡尔积。

还有另外一种思路：用01选的课程编号拼成字符串与每个非01的学生的课程编号拼成的字符串进行比较，如果相同，说明是选择了同样的课程，sql如下：

SELECT t3.* FROM (
  SELECT sid, group_concat(cid ORDER BY cid) group1 FROM score WHERE sid <> '01' GROUP BY sid) t1
INNER JOIN (
 SELECT group_concat(cid ORDER BY cid) group2 FROM score WHERE sid = '01' GROUP BY sid) t2 
ON t1.group1 = t2.group2
INNER JOIN student t3 ON t1.sid = t3.sid;

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select sname from student where sid not in (
  select distinct(sid) from score where cid = (
    select cid from course where tid = (
      select tid from teacher where tname='张三')));

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.sid,s.sname,round(avg(sc.score),2) avgscore from student s 
left join score sc on s.sid=sc.sid where s.sid in (
  select sid from sco