sql 经典50题--可能是你见过的最全解析

sql 经典50题--可能是你见过的最全解析

这篇文写了挺久了,有一位细心的小伙伴 @滑稽 发现了一个错误,很感谢有人认真的在看,我重新修改了一下!

很久之前我写过一篇学习sql的基本操作,但是自己好像在sql上面有点停滞了。最近有和一位朋友聊过如何学习sql,朋友给我的回复是多写代码。我深以为然于是就找到了这个经典的50题。

虽然这个经典50题网上有很多人分享过,但是,只有自己实际操作过以后才能真正的理解其中的坑,也才能够加深对知识的理解吧。我希望自己能够做一套很详细的解析。

解析包含:1解题思路,2相关知识,3 实际语句实现;

申明:我只是一个初学者,还有很多不懂的地方,所以如果您发现了错误,希望能在下边提出来,万分感谢!

如果你觉得我很走心,也欢迎点赞!

首先建表,主要有四个表,学生表(Student),课程表(Course),教师表(Teacher),以及成绩表(SC)

在分别介绍一下每个表的字段 学生表(Student )有四个字段 sid--学生id,sname--学生姓名,sage--学生年龄,ssex--学生性别

课程表(Course)有三个字段,cid--课程id,cname--课程名,tid--教师id

教师表(Teacher)有两个字段,tid--教师id,tname--教师姓名

成绩表(SC)有三个字段,sid--学生id,cid--课程id,score--成绩

我们用脑图的方式绘制出来,如下所示

学生表结构
课程表结构
教师表结构
成绩表结构

各个表之间的关系如下,Student和SC表通过学生id(sid)来连接,Course和Teacher表通过教师id(tid)连接,SC和Course表通过课程id(cid)来连接。

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
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);

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

解题思路:要查询的是两个课程的成绩,而且还要显示学生的信息。所以需要用到两张表,SC,Student这两张表。

问题拆分:(1) 怎么查找两个课程的成绩呢? (2) 如何把课程表和学员信息表连接起来呢?

那么用到哪些知识呢?

(1) 子查询 (2) join

语句实现:先找到两门课的成绩

-- 课程1的成绩
SELECT sid ,score AS class1 FROM sc WHERE sc.cid = '01';
-- 课程2的成绩
SELECT sid,score AS class2 FROM sc WHERE sc.cid = '02';
class1 查询结果
class2 查询结果

两个子查询结束了以后,下一步就是使用join把这两个查询的结果连接起来

SELECT * FROM Student RIGHT JOIN 
(SELECT t1.sid,class1,class2 
FROM 
-- 这个是要查询两个表的成绩,即课程1大于课程2
(SELECT sid ,score AS class1 FROM sc WHERE sc.cid = '01') AS t1,
(SELECT sid,score AS class2 FROM sc WHERE sc.cid = '02') AS t2
WHERE t1.sid = t2.sid AND t1.class1 > t2.class2) r -- 这里是固定的,就是子查询的一个别名的设置
-- 这一步就是把两个表按照sid联结起来
ON Student.sid = r.sid;
最终查询结果

好的,,第一题就解决了

第二题:1.1 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

思路:首先要查询所有的学生的选课情况,然后找到选择课程1和选择课程2的学生

解决方法: 使用join

选择两个分别选择课程1和课程2的学员表,然后把这两个表join起来

SELECT *
 FROM(SELECT *
 FROM sc
 WHERE sc.cid = '01' ) t1 LEFT JOIN 
 (SELECT *
 FROM sc WHERE sc.cid = '02') t2
 ON t1.sid = t2.sid ;
输出结果

1.2 查询同时存在01和02课程的情况

思路:要求同时选择了01和02课程的情况,则需要使用where链接起来,这一题和上一题相比就是多了一个去除选了01但是没有选择02课程的这一部分

知识点:子查询,where语句

 SELECT *
 FROM (SELECT *
 FROM sc WHERE sc.cid = '01') AS t1,
 (SELECT * FROM sc WHERE sc.cid = '02') AS t2
 WHERE t1.sid = t2.sid;
执行结果

1.3 查询选择了02课程但没有01课程的情况

思路,要求首先得到选择了02课程的学员,然后剔除掉选择01课程的学员

知识点:子查询,NOT IN

 SELECT *
 FROM sc
 WHERE sc.sid NOT IN (SELECT sid FROM sc WHERE sc.cid = '01')
 AND sc.cid = '02';

写好了这个语句以后我觉得自己写的不够清楚,因此我使用了别名

看一下结果:

 SELECT *
 FROM sc
 WHERE sc.sid NOT IN 
 (SELECT sid FROM sc WHERE sc.cid = '01')
 AND sc.cid = '02';
执行结果

小结:上面几个题基本可以说是同一类型,要注意子查询的使用,以及join和where的区别

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

思路:看到这个题的时候第一想法是要使用group by,和having,又因为成绩表(sc)没有学生姓名,所以还要使用join,当然avg肯定是需要的

考察知识:group by,join

SELECT s.sid,sname,AVG(sc.score)
FROM student AS s INNER JOIN sc
ON s.sid = sc.sid 
GROUP BY sc.sid
HAVING AVG(sc.score) >= 60;
执行结果

还有一种解法,使用子查询

SELECT r.*, s.sname FROM
(SELECT sid,AVG(score) FROM sc
GROUP BY sid 
HAVING AVG(score) > 60) r
LEFT JOIN student AS s ON s.sid = r.sid;

3.查询在 SC 表存在成绩的学生信息

思路:这题比较简单,就是两张表通过id连接,然后求单独值

知识点:Distinct

SELECT DISTINCT student.*
FROM sc,student
WHERE student.sid = sc.sid ;
执行结果

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

解题思路:首先用到两张表格,然后要求选课总数,和成绩总和,那么就需要用到groupby 和sum以及count

知识点:gruop by, sum,join,count

SELECT s.sid,s.sname,COUNT(sc.cid),SUM(score)
FROM student AS s INNER JOIN sc 
ON s.sid = sc.sid
GROUP BY sc.sid;
执行结果

5.查询「李」姓老师的数量

解题思路:这题考的是通配符的查询,比如使用like和%

知识点:count,like,%

SELECT COUNT(*)
FROM teacher
WHERE tname LIKE ('李%');
执行结果

6.查询学过「张三」老师授课的同学的信息

解题思路:要查找张三老师的授课科目,然后通过科目和score表连接,然后再和学生表连接

知识点:就是多重连接 inner join

SELECT s.*
FROM student AS s INNER JOIN sc ON s.sid = sc.sid
INNER JOIN  course  AS c ON sc.cid = c.cid
INNER JOIN teacher AS t ON t.tid = c.tid
WHERE t.tname = '张三';
执行结果

7.查询没有学全所有课程的同学的信息

解题思路:首先利用子查询查询course表查询得到共有几门课,按照groupby的方式求课程数加上having小于查询出来的课程的所有学员信息就好

知识点:子查询,groupby,having,join

SELECT s.*,COUNT(cid)
FROM sc RIGHT JOIN student AS s 
ON sc.sid = s.sid
GROUP BY sc.sid
HAVING COUNT(cid) < (SELECT COUNT(DISTINCT cid)
FROM course);
执行结果

网友的有一种比较好的做法,是使用not in

SELECT s.*
FROM student AS s
WHERE s.sid NOT IN (SELECT sc.sid
FROM sc 
GROUP BY sc.sid
HAVING COUNT(cid) >= 3);-- 这一句也可以使用子查询
执行结果

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

解题思路:首先要查询出01同学所学的课程,然后使用cid in 01同学的课程,并排除sid=01的同学

SELECT s.*
FROM sc INNER JOIN student AS s
ON sc.sid = s.sid
WHERE sc.cid IN (SELECT cid
FROM sc
WHERE sid = '01') AND sc.sid != '01'
GROUP BY sc.sid;
执行结果

我这里使用了groupby语句来求取单个学员id,也可以使用distinct关键字来做区分

SELECT DISTINCT s.*
FROM sc INNER JOIN student AS s
ON sc.sid = s.sid
WHERE sc.cid IN (SELECT cid
FROM sc
WHERE sid = '01') AND sc.sid != '01';
执行结果

这里就有一个知识点就是group by有去除重复值的功能,这个其实不难理解,因为group by就是按照单个组分类,可以理解为按照同一类进行切分

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

解题思路:首先感觉真题很难,想法是在student表的sid在sc的id中,而这个id又和cid相关联,然后再查询结束时,还要排除id=01的情况。反正很复杂。。。

知识点:n重子查询,in的使用

  SELECT 
    * 
  FROM
    student 
  WHERE sid IN 
    (SELECT 
      sid 
    FROM
      (SELECT 
        * 
      FROM
        sc AS a 
      WHERE cid IN 
        (SELECT 
          cid 
        FROM
          sc 
        WHERE sid = 01)) b 
    GROUP BY sid 
    HAVING COUNT(cid) = 
      (SELECT 
        COUNT(cid) 
      FROM
        sc c 
      WHERE sid = 01)) 
    AND sid != 01 ;  SELECT 
    * 
  FROM
    student 
  WHERE sid IN 
    (SELECT 
      sid 
    FROM
      (SELECT 
        * 
      FROM
        sc AS a 
      WHERE cid IN 
        (SELECT 
          cid 
        FROM
          sc 
        WHERE sid = 01)) b 
    GROUP BY sid 
    HAVING COUNT(cid) = 
      (SELECT 
        COUNT(cid) 
      FROM
        sc c 
      WHERE sid = 01)) 
    AND sid != 01 ;

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

解题思路:1先要查询出张三老师的教授课程,2然后利用not in 来找到学生的id

知识点:子查询,not in,多重join

SELECT sname FROM student 
WHERE sid NOT IN (
SELECT sid FROM sc 
LEFT JOIN course ON sc.cid=course.cid
LEFT JOIN teacher ON course.tid=teacher.tid 
WHERE tname='张三' )

11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

解题思路:题中提到了两门及以上的不及格课程,因此考虑使用Groupby 和 having,题中还提到学生姓名以及平均成绩,考虑到这里还要求出平均成绩,但是平均成绩说明不是很清楚,

知识点:Group by ,having,以及子查询在from子句中的使用

SELECT sname,s.sid,AVG(sc1.score) AS avg_score
FROM (SELECT * FROM sc WHERE score < 60)sc1,
student AS s 
WHERE sc1.sid=s.sid
GROUP BY sc1.sid
HAVING COUNT(sc1.cid)>=2;

12.检索" 01 "课程分数小于 60,按分数降序排列的学生信息

解题思路:要求01课程小于60的学员信息,然后按照降序排列学员信息

知识点:就是很简单的order by

SELECT s.*
FROM sc,student AS s
WHERE cid = '01' AND score <60
AND sc.sid = s.sid
ORDER BY score DESC;

当然还有其他解法,比如使用join,这里就不多写了

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

解题思路:要按照平均成绩降序排列,则需要使用group by,以及要显示所有的课程信息,那么就需要使用join来实现了

知识点:group by ,子查询,join

SELECT 
  s.*,
  avg_score 
FROM
  sc AS s 
  LEFT JOIN 
    (SELECT 
      sc.sid,AVG(sc.score) AS avg_score 
    FROM
      sc 
    GROUP BY sc.sid 
    ORDER BY avg_score DESC) r 
    ON s.sid = r.sid ;

可以发现这里的做法,发现并没有按照我想要的平均成绩的从高到低来排序,所以就去排查一下原因,发现这个错误是执行顺序的关系。

正确做法

SELECT 
  s.*,
  avg_score 
FROM
  sc AS s 
  LEFT JOIN 
    (SELECT 
      sc.sid ,AVG(sc.score) AS avg_score 
    FROM
      sc 
    GROUP BY sc.sid 
   ) r 
    ON s.sid = r.sid 
 ORDER BY avg_score DESC ;

这样就可以了,因此啊,sql执行顺序还是要牢记的

14.查询各科成绩最高分、最低分和平均分,以如下形式显示:

以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,

优良率,优秀率

及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90

要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序

排列

解题思路:这里要把最高分,最低分,平均分,等多个维度,那么就需要使用case when 语句了还有就是函数的使用

知识点:case when 别名的使用,以及函数的使用,和函数的嵌套使用,题目后边还考到了多列排列的使用,先按照desc,后按照asc排列

SELECT 
  cid AS 课程ID,
  COUNT(sid) AS 课程人数,
  MAX(score) AS 最高分,
  MIN(score) AS 最低分,
  AVG(score) AS 平均分,
  SUM(及格) / COUNT(sid) AS 及格率,
  SUM(中等) / COUNT(sid) AS 中等率,
  SUM(优良) / COUNT(sid) AS 优良率,
  SUM(优秀) / COUNT(sid) AS 优秀率 
FROM
  (SELECT 
    *,
    CASE
      WHEN score >= 60 
      THEN 1 
      ELSE 0 
    END AS 及格,
    CASE
      WHEN score >= 70 
      AND score < 80 
      THEN 1 
      ELSE 0 
    END AS 中等,
    CASE
      WHEN score >= 80 
      AND score < 90 
      THEN 1 
      ELSE 0 
    END AS 优良,
    CASE
      WHEN score >= 90 
      THEN 1 
      ELSE 0 
    END AS 优秀 
  FROM
    sc) a 
GROUP BY cid 
ORDER BY COUNT(sid) DESC,
  cid ;

特别长,但是知识很简单,就是case when语句的使用

15.按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺

解题思路:两张成绩表连接,然后按照成绩大小做一个排名

知识点:排序相关知识

SELECT 
  a.*,
  COUNT(a.score) AS 排名 
FROM
  sc AS a 
  LEFT JOIN sc AS b 
    ON a.cid = b.cid 
    AND a.score < b.score 
GROUP BY a.cid,
  a.sid 
ORDER BY a.cid,
  排名 ;

15.1 按各科成绩进行行排序,并显示排名, Score 重复时合并名次

解题思路:相比于上一题这里多了一个合并名次的事情,所谓名次合并,意思是两个人的成绩一致按照一个名次来,比如两个成绩一样的排名第一,下一个排名第二的就当做第三名

SELECT 
  a.*,
  COUNT(b.score)+1 AS 排名 
FROM
  sc AS a 
  LEFT JOIN sc AS b 
    ON a.cid = b.cid 
    AND a.score < b.score 
GROUP BY a.cid,
  a.sid 
ORDER BY a.cid,
  排名 ;

16.查询学生的总成绩,并进行排名,总分重复时保留名次空缺

解题思路:和15题差不多,不过我要引入一个用户变量

知识点:用户变量的使用

SELECT 
  a.*,
  @rank := @rank + 1 AS rank 
FROM
  (SELECT 
    sid,
    SUM(score) 
  FROM
    sc 
  GROUP BY sid 
  ORDER BY SUM(score) DESC) a,
  (SELECT 
    @rank := 0) b ;

16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺

解题思路:这一题,不熟练,参考了前辈们的做法

知识点:用户变量,子查询

SELECT 
  a.*,
  CASE
    WHEN @fscore = a.sumscore 
    THEN @rank 
    WHEN @fscore := a.sumscore 
    THEN @rank := @rank + 1 
  END AS 排名 
FROM
  (SELECT 
    sc.sid,
    SUM(score) AS sumscore 
  FROM
    sc 
  GROUP BY sid 
  ORDER BY SUM(score) DESC) AS a,
  (SELECT 
    @rank := 0,
    @fscore := NULL) AS t ;

17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比

解题思路:这个题和之前的那一题很像,都是使用case when 语句,然后加一个join

知识点:join case when,以及

SELECT 
  sc.cid AS 课程编号,
  cname AS 课程名称,
  SUM(
    CASE
      WHEN score >= 0 
      AND score <= 60 
      THEN 1 
      ELSE 0 
    END
  ) AS '[60-0]',
  SUM(
    CASE
      WHEN score >= 0 
      AND score <= 60 
      THEN 1 
      ELSE 0 
    END
  ) / COUNT(sid) AS '[60-0]百分比',
  SUM(
    CASE
      WHEN score >= 60 
      AND score <= 70 
      THEN 1 
      ELSE 0 
    END
  ) AS '[70-60]',
  SUM(
    CASE
      WHEN score >= 60 
      AND score <= 70 
      THEN 1 
      ELSE 0 
    END
  ) / COUNT(sid) AS '[70-60]百分比',
  SUM(
    CASE
      WHEN score >= 70 
      AND score <= 85 
      THEN 1 
      ELSE 0 
    END
  ) AS '[85-70]',
  SUM(
    CASE
      WHEN score >= 70 
      AND score <= 85 
      THEN 1 
      ELSE 0 
    END
  ) / COUNT(sid) AS '[85-70]百分比',
  SUM(
    CASE
      WHEN score >= 85 
      AND score <= 100 
      THEN 1 
      ELSE 0 
    END
  ) AS '[100-85]',
  SUM(
    CASE
      WHEN score >= 85 
      AND score <= 100 
      THEN 1 
      ELSE 0 
    END
  ) / COUNT(sid) AS '[100-85]百分比' 
FROM
  sc 
  JOIN course 
    ON sc.cid = course.cid 
GROUP BY sc.cid,
  cname ;

18.查询各科成绩前三名的记录

解题思路:按照课程id和学生id来分组,然后合并两个sc表,找到第一个表的成绩比第二个表成绩低的的值,然后排序

知识点: join groupby以及having的使用,当然还有order by的使用

SELECT a.*,COUNT(b.score) +1 AS ranking
FROM SC AS a LEFT JOIN SC AS b 
ON a.cid = b.cid AND a.score<b.score
GROUP BY a.cid,a.sid
HAVING ranking <= 3
ORDER BY a.cid,ranking;

19.查询每门课程被选修的学生数

解题思路:就是很简单的按照cid分组,然后计算每个组里面的学生人数,由于前面几题都是想了很久,遇到这题,我竟然不敢相信这么简单

知识点:groupby

SELECT cid,COUNT(sid) AS num
FROM  sc 
GROUP BY cid;

20.查询出只选修两门课程的学生学号和姓名

解题思路:查询选择了两门课的学生,然后连接student表,还算是比较基础的题

知识点:groupby having join

SELECT sc.sid,sname
FROM sc INNER JOIN student
ON sc.sid = student.sid
GROUP BY sc.sid
HAVING COUNT(sc.cid) = 2;

21. 查询男生、女生人数

解题思路:男女生人数这个毫无疑问考的是groupby 还有就是分组以后的count,或者可以使用case when

知识点:groupby,或者 case when

SELECT ssex,COUNT(ssex) as 
FROM student
GROUP BY ssex;
SELECT SUM(CASE WHEN ssex='男' THEN 1 ELSE 0 END)AS 男生人数,
       SUM(CASE WHEN ssex='女' THEN 1 ELSE 0 END) AS 女生人数
FROM student;

当然这里不完美的,可以使用count(sid)-男生人数,有很多的方式。

22. 查询名字中含有「风」字的学生信息

解题思路:很简单的一题,就是考察通配符,而考察点是是否有风

知识点:通配符%

SELECT *
FROM student
WHERE sname LIKE '%风%';

23查询同名同性学生名单,并统计同名人数

解题思路:同名同性,我们需要的是count(sname)>1,还要做一步筛选。这里有一个小坑,开始的时候我以为是同名同姓,中国文化博大精深啊

知识点:having,groupby

SELECT sname,COUNT(sname) AS 同名人数
FROM student
GROUP BY sname
HAVING 同名人数>1;

由于这个数据表中没有同名的人数,所以显示结果如上。

24.查询 1990 年出生的学生名单

解题思路:很简单的题,但是我还是做错了

知识点:通配符

SELECT sname
FROM student
WHERE sage LIKE '1990%';

本以为可以很快的执行并产生结果,但是没想到报错了,看一下报错信息,睡是datetime error,这个是什么原因呢,我们看一下,之前创表的语句

原来原本的建表的时候就是设置了datetime类型,那这里就要使用date函数了,这里对date函数做一些介绍

网上搜的图

我觉得上面的图能够很好的说明常用的时间函数处理,无非就是hour(小时),minute(分钟),second(秒),day(天),week(周),month(月),quarter(季度),year(年度)

那么这一题,显然就是要使用year了

SELECT *
FROM student
WHERE YEAR(sage) LIKE '1990%';

好的,很完美的解决了问题

25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编

号升序排列

解题思路:按照课程分组,然后求分组以后的平均值。然后外加一个排序,排序顺序按照课程id升序排列

知识点:group by ,order by

SELECT cid,AVG(score)
FROM sc
GROUP BY cid
ORDER BY AVG(score) DESC,cid;

由于这个表里面只有3门课,所以这里显示不出来,如果有很多数据,然后又恰好有相同的id

的时候应该就可以看出不同了

26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

解题思路:按照学生id分组,然后求平均分,然后用having语句判断平均分大于等于85,又因为要查询学生姓名因此可以使用join,或者直接使用笛卡尔积,但是笛卡尔积对于数据量比较大的时候不宜使用

知识点:having,groupby,join

SELECT sc.sid,sname,AVG(score) AS 平均成绩
FROM sc,student
WHERE sc.sid = student.sid
GROUP BY sc.sid
HAVING 平均成绩>= 85;


或者

SELECT sc.sid,sname,AVG(score) AS 平均成绩
FROM sc INNER JOIN student ON sc.sid = student.sid
GROUP BY sc.sid
HAVING 平均成绩>= 85;

结果都是第一样的,但是数据里大的时候我会使用第二种解法

27.查询课程名称为「数学」,且分数低于 60 的学生姓名和分数

解题思路:和上一题一样,可以使用join,也可以直接使用笛卡尔积,但是要使用三张表,所以最好还是使用join

知识点:多重join的使用

SELECT 
  sname,
  score 
FROM
  course AS c 
  INNER JOIN sc 
    ON c.cid = sc.cid 
    AND c.cname = '数学' 
    AND sc.score < 60 
  INNER JOIN student AS s 
    ON sc.sid = s.sid ;

28. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)

解题思路:要把学生的姓名展示出来,所以使用join

知识点:join的使用

SELECT 
  sname,
  sc.cid,
  score 
FROM
  sc 
  INNER JOIN student AS s 
    ON sc.sid = s.sid ;

29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

解题思路:就是在上一题的基础上,加上一个where条件判断,或者直接在join on后边判断

知识点:join加上条件判断

SELECT 
  sname,
  sc.cid,
  score 
FROM
  sc 
  INNER JOIN student AS s 
    ON sc.sid = s.sid 
    AND score > 70 ;

或者

SELECT 
  sname,
  sc.cid,
  score 
FROM
  sc 
  INNER JOIN student AS s 
    ON sc.sid = s.sid 
WHERE score > 70 ;

结果是一样的,至于两者的区别,可以参考下面这篇文章

https://blog.csdn.net/weixin_40539892/article/details/90263677blog.csdn.net

总结起来就是inner join后边时两个做法是没有区别的,但是如果是left join这种就会有多余的出来了,这是因为使用join以后会生成一个虚拟表,而使用where是在这张虚拟表的基础上进行筛选,所以结果可能不一样。这个值得注意。

30.查询不及格的课程

解题思路:这个其实和上面一题是同一个考法,不详细展开,唯一的区别就是只查询课程,所以就是要distinct,或者groupby

知识点:join,

SELECT 
sc.cid
FROM
  sc 
  INNER JOIN student AS s 
    ON sc.sid = s.sid 
WHERE score < 60 
GROUP BY sc.cid

第二种使用distinct

SELECT 
DISTINCT sc.cid
FROM
  sc 
  INNER JOIN student AS s 
    ON sc.sid = s.sid 
WHERE score < 60 ;

31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

解题思路:首先肯定需要join把两个表连起来,然后使用条件查询课程id为01且成绩大于80

知识点:join where

SELECT sc.sid,sname
FROM sc INNER JOIN student AS s
ON sc.sid = s.sid
WHERE sc.cid = 01 AND score >= 80;

32.求每门课程的学生人数

解题思路:就是简单的按照课程分组然后求sid的人数

知识点:group by ,count

SELECT cid,COUNT(sid) AS 人数
FROM sc
GROUP BY cid;

33.成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩

解题思路:这题首先要找到张三老师所授课程,然后找到选择这门课程的学生,在成绩按照从大到小的顺序排列,然后只取最高的成绩,当然还需要使用子查询

知识点:join,limit

SELECT 
  s.*,
  score 
FROM
  student AS s 
  INNER JOIN sc 
    ON s.sid = sc.sid 
WHERE sc.cid = 
  (SELECT 
    cid 
  FROM
    teacher AS t 
    INNER JOIN course AS c 
      ON t.tid = c.cid 
      AND tname = '张三') 
ORDER BY score DESC 
LIMIT 1 ;

现在慢慢有点感觉了,能够很快写出来,果然这种语言相关的还是要多敲代码,不要怕错,就是乌龟的速度,只要一直往前也有机会超过兔子

34.成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生

信息及其成绩

解题思路:有重复成绩,我们就选择一个最大成绩然后使用子查询,让成绩在各科成绩的最大值里面做筛选

知识点:多重join,以及子查询的应用

SELECT 
  student.*,
  sc.cid,
  score 
FROM
  student 
  INNER JOIN sc 
    ON student.sid = sc.sid 
  JOIN course 
    ON sc.cid = course.cid 
  JOIN teacher 
    ON course.tid = teacher.tid 
WHERE tname = '张三' 
  AND score IN 
  (SELECT 
    MAX(score) 
  FROM
    sc 
    INNER JOIN course 
      ON sc.cid = course.cid 
    JOIN teacher 
      ON course.tid = teacher.tid 
  WHERE tname = '张三') ;

由于我创建的表格里面没有重复的成绩,所以返回结果没有发生变化

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

解题思路:首先join两张表,然后做筛选1成绩相同,2课程id不相同

知识点:join,以及where的使用

SELECT 
  DISTINCT a.* 
FROM
  sc AS a 
  INNER JOIN sc AS b 
WHERE a.score = b.score 
  AND a.cid != b.cid ;

由于可能使用的是inner join,所以可能会有很多重复值,因此我用了一个distinct的做法。

36. 查询每门成绩最好的前两名

解题思路:这题和前面有一题有很相似的地方,就是找到大于某个成绩只有两人就是前两名

知识点:关联子查询

SELECT 
  * 
FROM
  sc 
WHERE 
  (SELECT 
    COUNT(*) 
  FROM
    sc AS a 
  WHERE sc.cid = a.cid 
    AND sc.score < a.score) < 2 
ORDER BY cid ASC,
  sc.score DESC ;

我觉得这个题还是相对来说好理解的,同样的查询前3名等等,只需改一个数字。

37. 统计每门课程的学生选修人数(超过 5 人的课程才统计)。

解题思路:按照课程分组,然后count人数,使用having做一个筛选

知识点:groupby having

SELECT 
  cid,
  COUNT(sid) 
FROM
  sc 
GROUP BY cid 
HAVING COUNT(sid) > 5 ;

38.检索至少选修两门课程的学生学号

解题思路:和上一题基本类似按照学生id分组然后count cid,筛选出大于等于2的学生

知识点:groupby having

SELECT 
  sid,
  COUNT(cid) 
FROM
  sc 
GROUP BY sid 
HAVING COUNT(cid) >= 2 ;

39.查询选修了全部课程的学生信息

解题思路:同样的我们要知道总共有多少门课,不光是为了回答这一道题,可能真实情况会有很多的课,那么就需要把course表中所有的课程计数,还要求学生信息,那就需要使用join了

知识点:join,where,子查询

SELECT 
  s.* 
FROM
  sc 
  INNER JOIN student AS s 
    ON sc.sid = s.sid 
WHERE cid = 
  (SELECT 
    COUNT(*) 
  FROM
    course) ;

40.查询各学生的年龄,只按年份来算

解题思路:这题开始就要使用时间函数了,不难,还算比较简单

知识点:date函数的使用

SELECT 
  sname,
  YEAR(NOW()) - YEAR(sage) AS 年纪 
FROM
  student ;


41. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一

解题思路:这个可以使用case when语句,然后使用时间戳求差值

知识点:case when ,year,date_format

SELECT 
  sname,
  CASE
    WHEN (
      DATE_FORMAT(NOW(), '%m-%d') - DATE_FORMAT(sage, '%m-%d')
    ) < 0 
    THEN YEAR(NOW()) - YEAR(sage) + 1 
    ELSE YEAR(NOW()) - YEAR(sage) 
  END AS 年龄 
FROM
  student ;

42.查询本周过生日的学生

解题思路:使用week函数

知识点:week,now

SELECT sname
FROM student
WHERE WEEK(sage) = WEEK(NOW());

可以发现没有结果,为了验证准确性,我们把student表格单独拉出来

SELECT 
  * 
FROM
  student ; 

可以看到果然没有,那么为了进一步验证,我们向student表中新增一行数据

INSERT INTO Student VALUES('09' , '关羽' , '1990-07-15' , '男');
SELECT 
  * 
FROM
  student ;

看一下执行结果

在执行以下我们之前的语句

ok看来语句是没有毛病的,我也不知道为啥非要验证以下,可能是强迫症吧。。。

43. 查询下周过生日的学生

解题思路:好的嘛,下周过生日,代表着在上题的基础上再加1

知识点:week,now

SELECT 
  sname 
FROM
  student 
WHERE WEEK(sage) = WEEK(NOW()) + 1 ;

当然还是没有结果的,不过这里就不做测试了

44.查询本月过生日的学生

解题思路:基本类似的操作,使用month函数

知识点:month,now

SELECT 
  sname,
  sage 
FROM
  student 
WHERE MONTH(sage) = MONTH(NOW());

45.查询下月过生日的学生

解题思路:同样的套路,同样的解法

知识点:month,now

SELECT 
  sname,
  sage 
FROM
  student 
WHERE MONTH(sage) = MONTH(NOW()) + 1 ;

哇哦,不知不觉间就到了最后一题了,这里给我印象最深的是那几个需要排序的题,我觉得排序的题,要好好琢磨,争取能够很快的写出来。

人果然都是有惰性的,这一篇没想到拖了这么久,不过能够在工作休息之余写完这篇文还是很开心的,接下来,要继续刷题,下一篇应该就是牛客网和leetcode上面的题了。加油!

话说写了这么长的文,不给个赞吗?

编辑于 2019-09-11

文章被以下专栏收录