Version: Next

45 道 MySQL 练习题

建库建表

create database mysqlPractice charset=utf8;
use mysqlPractice;

student 学生表

create table Student(
      Sno 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-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' , '女');

course 课程表

create table Course(
      Cno varchar(10),
      Cname nvarchar(10),
      Tno varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

techear 教师表

create table Teacher(
      Tno varchar(10),
      Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

sco 成绩表

create table SCo(
      Sno varchar(10),
      Cno varchar(10),
      score decimal(18,1));
insert into SCo values('01' , '01' , 80);
insert into SCo values('01' , '02' , 90);
insert into SCo values('01' , '03' , 99);
insert into SCo values('02' , '01' , 70);
insert into SCo values('02' , '02' , 60);
insert into SCo values('02' , '03' , 80);
insert into SCO values('03' , '01' , 80);
insert into SCo values('03' , '02' , 80);
insert into SCo values('03' , '03' , 80);
insert into SCo values('04' , '01' , 50);
insert into SCo values('04' , '02' , 30);
insert into SCo values('04' , '03' , 20);
insert into SCo values('05' , '01' , 76);
insert into SCo values('05' , '02' , 87);
insert into SCo values('06' , '01' , 31);
insert into SCo values('06' , '03' , 34);
insert into SCo values('07' , '02' , 89);
insert into SCo values('07' , '03' , 98);

题目

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

分析题目关键词 学生信息、课程分数、课程编号
锁定使用表，学生表、成绩表，两张表能够提供题目所有所需信息
通过 Sno 主键来连接学生表和成绩表
进行同一学生不同成绩比较，那么就还需要再连接成绩表，利用Sid相同，Cid不同进行关联
在一条记录中同时显示 01 、 02 课程
最终回归题目，只需要筛选一个课程，利用 where 比较分数即可

SELECT 
    Student.*, Sco1.score '01课程', Sco2.score '02课程'
FROM
    Student 
INNER JOIN Sco sco1 on Student.Sno = sco1.sno
INNER JOIN Sco sco2 on Student.Sno = sco2.sno 
AND sco1.cno = '01' AND sco2.cno = '02' 
WHERE sco1.score > sco2.score

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

分析：只用成绩表就可以完成

SELECT
 *
FROM Sco s1
INNER JOIN Sco s2
ON s1.sno = s2.sno
WHERE s1.cno = '01' AND s2.cno = '02'

1.2 查询存在01课程但可能不存在02课程的情况（不存在显示为null）

与 1.1 类似，但限制条件01课程必须有，02课程不必须
仅需要成绩表
需要使用 LEFT JOIN ，实现1名学生的2门课程在同一行，且左边cid等于01课程，右边cid等于02课程

SELECT
 s1.score '01', s2.score '02'
FROM Sco s1
LEFT JOIN Sco s2
ON s1.sno = s2.sno AND s2.cno = '02' 
WHERE s1.cno = '01'

1.3 查询不存在01课程但存在02课程的学生及02课程的成绩

反着做
筛选出存在 01 课程的学生
使用 not in 筛选出不能存在 01 课程但是存在 02 课程的学生

SELECT
*
FROM Sco
WHERE sno NOT IN (SELECT sno from Sco where cno = '01')
AND cno = '02'

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

关键词：学生编号、学生姓名、平均成绩大于等于60
使用学生表、成绩表
成绩表按照学生id Sno 聚合，获取平均成绩，并且筛选出平举成绩大于等于60的 Sno 和平均成绩
利用 INNER JOIN 连接学生表、获取学生姓名
第三步
SELECT
sno, avg(score) as avg_score
FROM SCo
GROUP BY sno
HAVING avg_score >= 60
第三步结果
01 89.66667
02 70.00000
03 80.00000
05 81.50000
07 93.50000
第四步
SELECT
a.sno, a.Sname, b.avg_score
FROM Student a
INNER JOIN (
SELECT
sno, avg(score) as avg_score
FROM SCo
GROUP BY sno
HAVING avg_score >= 60
)b
ON a.sno = b.sno
非子查询写法
SELECT
a.sno, a.Sname, avg(b.score) as avg_score
FROM Student a
INNER JOIN SCo b
ON a.sno = b.sno
GROUP BY a.sno, a.Sname
HAVING avg_score >= 60

提示

GROUP BY 语句后面，必须写上 SELECT 中，除了聚合函数以外，所有会出现重复的字段

3. 查询在SCo中存在成绩记录的学生的信息

关键词：SCo 表、成绩、学生信息
学生表、成绩表
以成绩为主表，左连接学生表，连接主键 Sno，使用 LEFT JOIN
使用 GROUP BY ，从成居中取出唯一 Sno ，左连接学生表
第一步
SELECT
b.*
FROM SCo a
LEFT JOIN Student b
ON a.sno = b.sno
此时，同一个人会查出多次，因为每个人都多条成绩记录
GROUP BY
SELECT
b.*
FROM (SELECT sno FROM SCo GROUP BY sno) a
LEFT JOIN Student b
ON a.sno = b.sno
不使用子查询
SELECT
DISTINCT b.*
FROM SCo a
LEFT JOIN Student b
ON a.sno = b.sno

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为 null）

关键词：学生成绩、选课总数、所有课程总成绩
学生表、成绩表
在成绩表中对 Sno 进行聚合，获取每个学生的选课总数、所有课程总成绩
学生表为主表，与第二步获得的成绩汇总表进行左连接
子查询
SELECT
a.sno, a.sname, b.class_num, b.total_score
FROM Student a
LEFT JOIN (
SELECT
sno, count(cno) as class_num, sum(score) as total_score
FROM Sco
GROUP BY sno
) b
ON a.sno = b.sno
SELECT
a.sno, a.sname, count(b.cno) as class_num, sum(b.score) as total_score
FROM Student a
LEFT JOIN SCo b
ON a.sno = b.sno
GROUP BY a.sno, a.sname

4.1 查询有成绩的学生的信息

学生表、成绩表
筛选出有成绩的 Sno
利用有成绩的 Sno 对学生表进行筛选
SELECT
a.*
FROM Student a
WHERE a.sno IN (SELECT sno FROM sco GROUP BY sno )
不使用子查询
SELECT
DISTINCT b.sno, b.sname, b.Sage, b.Ssex
FROM Sco a
INNER JOIN Student b
ON a.sno = b.sno

5. 查询 [李] 姓老师的数量

关键词：老师、李、数量
教师表
筛选出 [李] 姓老师使用 like 和 % 占位符
统计李姓老师的数量

SELECT
   count(Tno)
FROM
    Teacher
WHERE
    Tname like '李%'

6.查询学过 [张三] 老师的课的学生的信息

关键词：教师姓名、学生信息
教师表、课程表、成绩表、学生表
关联课程表和教师表，得到课程 Cno 和教师 Tname 的关系
与成绩表关联，得到学生 Sno 与教师 Tname 的关系
与学生表关联，获取学生信息
增加筛选条件 Tname 为 [张三]

# 查询老师和自己的课 
SELECT
 t.tname, c.*
FROM Course c
INNER JOIN Teacher t ON c.tno = t.tno

# 查询课的成绩
SELECT
 sc.sno, c.cname, c.tno, sc.score
FROM Course c
INNER JOIN Sco sc ON c.cno = sc.cno

# 查询获得成绩的学生
SELECT
    s.sname
FROM Student s
INNER JOIN SCo sc ON s.sno = sc.sno

# 总
SELECT
        s.*, t.tname
FROM Student s
INNER JOIN SCo sc ON s.sno = sc.sno
INNER JOIN Course c ON c.cno = sc.cno
INNER JOIN Teacher t ON c.tno = t.tno
WHERE t.tname = '张三'

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

关键词：学生信息、所有课程
学生表、成绩表、课程表
根据课程表、获取全部课程总数
根据成绩表，查询每个学生选课总数，并筛选出所学课程总数小于全部课程总数的学生 Sno
关联学生表、获取学生信息

# 查询所有选课数目小于课程总数的学生的学生ID
SELECT
    sc.sno, count(score) as scc
FROM SCo sc
GROUP BY sc.sno
HAVING scc < (SELECT count(cno) FROM Course)

关联学生表，查出学生信息

子查询

SELECT
    a.*, b.scc
FROM Student a 
INNER JOIN (
    SELECT
            sc.sno, count(score) as scc
    FROM SCo sc
    GROUP BY sc.sno
    HAVING scc < (SELECT count(cno) FROM Course)
) b
ON a.sno = b.sno

非子查询

SELECT
 s.*, count(score) as scc
FROM SCo 
INNER JOIN Student s ON s.sno = Sco.sno
GROUP BY SCo.sno, s.sname, s.sage, s.ssex
HAVING scc < (SELECT count(cno) FROM Course)

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

关键词：学生信息、课程
成绩表、学生表
根据成绩表，获取 01 同学所学课程的课程编号
使用 in，获取课程编号在 01 同学所学课程课程编号范围内的记录
关联学生表，获取学生信息
使用 DISTINCT 去重

SELECT
DISTINCT    a.*
FROM Student a
INNER JOIN (
    SELECT
            *
    FROM Sco 
    WHERE cno IN (
        SELECT
                cno
        FROM Sco
        WHERE sno = '07'
    )
) b
ON a.sno = b.sno

关联学生表时不使用子查询

SELECT
DISTINCT Student.*
FROM Sco
INNER JOIN Student ON Sco.sno = Student.sno
WHERE cno IN (
    SELECT
        cno
    FROM Sco sc
    WHERE sc.sno = '07'
)

9. 查询和 01 同学的课程完全相同的同学的信息

关键词：学生信息、课程、完全相同
完全相同：没有学习 01 同学没学的课程
与 01 同学学习课程数目相同
学生表、成绩表
找出学了 01 同学所选课程以外课程的学生
排除上一步上一步中的学生和 01 同学本人
筛选出与 01 同学所学课程数目一样的学生
关联课程表获取学生信息

找出学了 01 同学所选课程以外课程的学生

SELECT
   sno
FROM SCo 
WHERE cno NOT IN (
    SELECT 
        cno
    FROM SCo
    WHERE sno = '01'
)

排除上一步上一步中的学生和 01 同学本人

SELECT
    sno
FROM Sco
WHERE sno NOT IN (
   SELECT
        sno
    FROM SCo 
    WHERE cno NOT IN (
        SELECT 
            cno
        FROM SCo
        WHERE sno = '01'
    )
)
AND sno != '01'
GROUP BY sno

筛选出与 01 同学所学课程数目一样的学生

SELECT
    sno
FROM Sco
WHERE sno NOT IN (
   SELECT
        sno
    FROM SCo 
    WHERE cno NOT IN (
        SELECT 
            cno
        FROM SCo
        WHERE sno = '01'
    )
)
AND sno != '01'
GROUP BY sno
HAVING count(cno) = (SELECT count(cno) FROM Sco WHERE sno = '01')

关联学生表

SELECT
    a.*
FROM Student a
INNER JOIN (
    SELECT
            sno
    FROM Sco
    WHERE sno NOT IN (
        SELECT
            sno
        FROM SCo 
        WHERE cno NOT IN (
            SELECT 
                cno
            FROM SCo
            WHERE sno = '01'
        )
    )
    AND sno != '01'
    GROUP BY sno
    HAVING count(cno) = (SELECT count(cno) FROM Sco WHERE sno = '01')   
) b 
ON a.sno = b.sno

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

关键词：学生姓名、张三老师
四张表
关联课程表和教师表，筛选出张三老师所授课程的 cno
关联成绩表，获取学过张三老师课程的学生的 sno
关联学生表，使用 NOT IN 筛选出没学过张三老师任意课程的学生 sno

张三老师课程 cno

SELECT
  cno
FROM Course c
INNER JOIN Teacher t ON c.tno = t.tno
WHERE t.Tname = '张三'

学过张三老师课程的学生的 sno

SELECT
DISTINCT sno
FROM Sco sc
WHERE cno IN (
    SELECT
        cno
    FROM Course c
    INNER JOIN Teacher t ON c.tno = t.tno
    WHERE t.Tname = '张三'
)

# 或者

SELECT
DISTINCT sno
FROM Sco sc
LEFT JOIN Course c ON c.cno = sc.cno
INNER JOIN Teacher t ON c.tno = t.tno
WHERE t.Tname = '张三'

使用 NOT IN 筛选

SELECT
    sname
FROM Student
WHERE sno NOT IN (
    SELECT
    DISTINCT sno
    FROM Sco sc
    WHERE cno IN (
        SELECT
            cno
        FROM Course c
        INNER JOIN Teacher t ON c.tno = t.tno
        WHERE t.Tname = '张三'
    )   
)

# 或者

SELECT
   sname
FROM Student
WHERE sno NOT IN (
    SELECT
        DISTINCT sno
    FROM Sco sc
    LEFT JOIN Course c ON c.cno = sc.cno
    INNER JOIN Teacher t ON c.tno = t.tno
    WHERE t.Tname = '张三'
)

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

成绩表、学生表
计算学生平均成绩
找出两门课程及以上不及格的同学
计算两门课程及以上不及格同学的平均成绩
关联学生表

# 学生的平均成绩 
SELECT
    sno, AVG(score)
FROM SCo
GROUP BY sno

# 两门及以上课程不及格的学生
SELECT
    sno
FROM Sco
WHERE score < 60
GROUP BY Sno
HAVING count(cno >= 2)

# 两门及以上课程不及格的学生及平均成绩
SELECT
    sno, avg(score)
FROM Sco
WHERE score < 60
GROUP BY Sno
HAVING count(cno >= 2)

# 两门及以上课程不及格的学生及平均成绩以及学号，姓名
SELECT
    a.sno, a.sname, b.avg_score
FROM Student a
INNER JOIN (
    SELECT
        sno, avg(score) as avg_score
    FROM Sco
    WHERE score < 60
    GROUP BY Sno
    HAVING count(cno >= 2)
) b
ON a.sno = b.sno

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

成绩表、学生表
查询 01 课程分数小于 60 分的学生ID
ORDER BY score DESC 按成绩降序排列
关联学生表

# 查询 01 课程分数小于 60 的学生ID
SELECT
    sno
FROM Sco
WHERE cno = '01'
AND score < 60

# 查询 01 课程分数小于 60 的学生ID 按成绩降序排列 
SELECT
    s.*
FROM Sco sc
INNER JOIN Student s ON sc.sno = s.sno
WHERE sc.cno = '01'
AND sc.score < 60
ORDER BY sc.score DESC

# 子查询写法
SELECT
 a.*
FROM Student a
INNER JOIN (
    SELECT
        sno, score
    FROM Sco
    WHERE cno = '01'
    AND score < 60
) b ON a.sno = b.sno
ORDER BY b.score DESC

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

学生表、成绩表
获取学生所有课程的成绩，学生表左连接成绩表，没有成绩显示null
求学生的平均成绩
给每条记录加上平均成绩，按照平均成绩降序排列

# 查询所有学生的成绩
SELECT
  s.*, sc.score
FROM Student s
LEFT JOIN Sco sc ON s.sno = sc.sno

# 查询学生平均成绩
SELECT
    sno, avg(score)
FROM sco
GROUP BY sno 
 
# 拼接
SELECT
  s.*, sc.score, b.average
FROM Student s
LEFT JOIN Sco sc ON s.sno = sc.sno
LEFT JOIN (
        SELECT
            sno, avg(score) as average
        FROM sco
        GROUP BY sno 
) b ON s.sno = b.sno
ORDER BY b.average DESC

14. 查询各科成绩最高分、最低分、平均分

形式：

课程ID	课程name	最高分	最低分	平均分	及格率	中等率	优良率		优秀率

及格： >= 60
中等：[70, 80)
优良：[80, 90)
优秀：>= 90

分析题目：课程号、课程名称、聚合计算（选修人数、最高分、最低分、平均分、及格率、中等率、优良率、优秀率）、排序
成绩表、课程表
聚合计算（使用条件判断语句 case when）
排序（按照选修人数降序、课程号升序排列）
关联课程表、获取课程名称 cname

SELECT
    Cno,
    count(*) as 选修人数,
    max(score) as 最高分,
    min(Score) as 最低分,
    avg(score) as 平均分,
    sum(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / count(*) as 及格率, # 及格人数 / 选课人数
    sum(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) / count(*) as 中等率,
    sum(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) / count(*) as 优良率,
    sum(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / count(*) as 优秀率
FROM Sco
GROUP BY Cno
ORDER BY count(*) DESC, cno

关联课程表，显示课程名称

SELECT
    b.cname, a.*
FROM (
    SELECT
        Cno,
        count(*) as 选修人数,
        max(score) as 最高分,
        min(Score) as 最低分,
        avg(score) as 平均分,
        sum(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / count(*) as 及格率, # 及格人数 / 选课人数
        sum(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) / count(*) as 中等率,
        sum(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) / count(*) as 优良率,
        sum(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / count(*) as 优秀率
    FROM Sco
    GROUP BY Cno
    ORDER BY count(*) DESC, cno
) a
LEFT JOIN Course b ON a.cno = b.cno

15. 按各科成绩进行排序，并显示排名，Score 重复时也要继续排名

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

就是在语文这个科目里排12345名，再到数学这个科目里排12345名...

知识点：row_number()、rank()、dense_rank() 的区别
row_number()：依次排序，不会出现并列排名
rank()：允许并列排名，出现并列排名时，跳跃排序
dense_rank()：允许并列排名，出现并列排名时，连续排序

MySQL 8.0 开始支持窗口函数

窗口 可以理解为记录集合，窗口函数就是在满足某种条件的记录上执行的特殊函数，按照功能划分，可以把 MySQL 支持的窗口函数分为如下几类：
- 序号函数：row_nubmer()、rank()、dense_rank()
- 分布函数：percent_rank()、cume_dist()
- 前后函数：lag()、lead()
- 头尾函数：first_val()、last_val()
- 其他函数：nth_value()、nfile()
窗口函数的基本用法如下：
- 函数姓名 ([expr]) over 字句
- over 是关键字，用来指定函数执行的窗口范围

SELECT
 *，
 row_number() over (partition by cno ORDER BY score DESC) as 排名
FROM Sco

15.1

# 排名为 1 1 3
SELECT
 *，
 rank() over (partition by cno ORDER BY score DESC) as 排名
FROM Sco

# 排名为 1 1 2
SELECT
 *，
 DENSE_RANK() over (partition by cno ORDER BY score DESC) as 排名
FROM Sco

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

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

# 计算每个学生的总成绩
SELECT
    sno,
    sum(score) as sum_score
FROM score
GROUP BY sno

# 16
SELECT
    *,
    rank() over (ORDER BY sum_score DESC) as 排名
FROM (
    SELECT
        sno,
        sum(score) as sum_score
    FROM score
    GROUP BY sno
)

17. 同级各科成绩各分数段人数：课程编号，课程名称，[100,85],[85,70],[70,60],[60,0] 及所占百分比

关键词：课程编号、课程名称、分数段、人数
成绩表、课程表
使用 case when 按照课程编号统计各分数段人数
转化成所占百分比
使用 concat 拼接，输出百分比符号
关联课程表，输出课程名称

# 人数
SELECT
    cno,
    SUM(case when score >=0 and score < 60 THEN 1 ELSE 0 END) as '[0-60]',
    SUM(case when score >=60 and score < 70 THEN 1 ELSE 0 END) as '[60-70]',
    SUM(case when score >=70 and score < 85 THEN 1 ELSE 0 END) as '[70-85]',
    SUM(case when score >=85 THEN 1 ELSE 0 END) as '[85-100]'
FROM Sco
GROUP BY cno

# 转化为比率，乘以 100，用 concat 拼接 %
SELECT
    cno,
    CONCAT(SUM(case when score >=0 and score < 60 THEN 1 ELSE 0 END) / count(*) * 100, '%') as '[0-60]',
    CONCAT(SUM(case when score >=60 and score < 70 THEN 1 ELSE 0 END) / count(*) * 100, '%') as '[60-70]',
    CONCAT(SUM(case when score >=70 and score < 85 THEN 1 ELSE 0 END) / count(*) * 100, '%') as '[70-85]',
    CONCAT(SUM(case when score >=85 THEN 1 ELSE 0 END)  / count(*) * 100, '%') as '[85-100]'
FROM Sco
GROUP BY cno

# 关联课程名称
SELECT
    b.cname,
    a.*
FROM (
    SELECT
        cno,
        CONCAT(SUM(case when score >=0 and score < 60 THEN 1 ELSE 0 END) / count(*) * 100, '%') as '[0-60]',
        CONCAT(SUM(case when score >=60 and score < 70 THEN 1 ELSE 0 END) / count(*) * 100, '%') as '[60-70]',
        CONCAT(SUM(case when score >=70 and score < 85 THEN 1 ELSE 0 END) / count(*) * 100, '%') as '[70-85]',
        CONCAT(SUM(case when score >=85 THEN 1 ELSE 0 END)  / count(*) * 100, '%') as '[85-100]'
    FROM Sco
    GROUP BY cno
) a
LEFT JOIN course b on b.cno = a.cno

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

查询各科成绩排名
截取前三名

# 按各科成绩排名
SELECT
 *,
 DENSE_RANK() over (PARTITION BY cno ORDER BY score DESC) as 'SCORE_RANK'
FROM Sco
GROUP BY Cno
ORDER BY Sco DESC

# 截取前三名
SELECT
    *
FROM (
    SELECT
     *,
     DENSE_RANK() over (PARTITION BY cno ORDER BY score DESC) as 'SCORE_RANK'
    FROM Sco
    GROUP BY Cno
    ORDER BY Sco DESC
) a
WHERE SCORE_RANK <= 3

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

成绩表
按照课程编号进行分组统计

SELECT
    cno,
    count(sno) as '选修人数'
FROM Sco
GROUP BY Cno

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

两门课程、学生学号、学生姓名
成绩表、学生表
统计每个学生选修课程数，并筛选出只选修2门课程的学生Sno
关联学生表、输出学生姓名

SELECT
    sno,
    count(cno) as c_num
FROM Sco
GROUP BY sno

# 子查询
SELECT
    a.sno,
    b.sname,
    a.c_num
FROM (
    SELECT
        sno,
        count(cno) as c_num
    FROM Sco
    GROUP BY sno
    having c_num = 2
) a
INNER JOIN Student b ON a.sno = b.sno

21. 查询男生、女生人数

不同性别的学生数
学生表
按照性别分组排名

SELECT
    ssex,
    count(sno) as '人数'
FROM Student
GROUP BY ssex

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

学生表
使用 like 操作符，% 通配符

SELECT
    *
FROM Student
WHERE sname like '%风%'

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

同名同性、人数
学生表
自连接查询，筛选出名字、性别相同但学生编号不同的信息
统计同名同性的人数

SELECT
    sno
FROM Student a
INNER JOIN Student b ON a.sname = b.sname
AND a.ssex = b.ssex
AND a.sno != b.sno

# 答案
SELECT
    sname,
    ssex,
    count(sno) as '人数'
FROM (
    SELECT
        a.*
    FROM Student a
    INNER JOIN Student b ON a.sname = b.sname
    AND a.ssex = b.ssex
    AND a.sno != b.sno
) c 
GROUP BY sname, ssex;

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

出生日期 sage
学生表
使用 year(sage) 筛选出 1990 年出生的学生
也可以用 left(sage, 4) 截取前四个字符

SELECT
    *
FROM Student
WHERE year(sage) = '1990'

SELECT
    *
FROM Student
WHERE left(sage, 4) = '1990'

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

平均成绩、排序
成绩表
计算每门课程的平均成绩
排序（按平均成绩降序，课程编号升序）

SELECT
    cno,
    avg(score) as '平均分'
FROM SCo
GROUP BY cno
ORDER BY avg(score) DESC, cno

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

平均成绩、学号、姓名
学生表、成绩表
分组聚合求出每个学生的平均成绩，并筛选出平均成绩大于等于85分的学生
关联学生表、获取学生信息

# 首先计算平均分，并筛选平均分大于等于85的
SELECT
    sno,
    avg(score) avg_score
FROM SCo
GROUP BY sno
HAVING avg_score >= 85

# 关联后的结果
SELECT
    b.*, a.avg_score
FROM (
    SELECT
        sno,
        avg(score) avg_score
    FROM SCo
    GROUP BY sno
    HAVING avg_score >= 85
) a
LEFT JOIN Student b ON a.sno = b.sno

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

课程名称、分数、学生姓名
课程表、成绩表、学生表
课程表中找到数学的cno
关联成绩表筛选低于60，关联学生表

# 查询数学的cno
SELECT 
 cno
FROM Course
WHERE Cname = '数学'

# 去成绩表里筛选数学成绩低于60的，关联学生表显示学生姓名
SELECT
    c.Sname,
    b.score
FROM (
    SELECT 
     cno
    FROM Course
    WHERE Cname = '数学'
) a
LEFT JOIN Sco b ON a.cno = b.cno
LEFT JOIN Student c ON b.sno = c.sno
WHERE b.score < 60

# 课程视频中的写法，更合理
SELECT
    b.Sname,
    a.score
FROM SCo a
LEFT JOIN Student b ON b.sno = a.sno
WHERE a.cno = ( SELECT 
     cno
    FROM Course
    WHERE Cname = '数学')
AND a.score < 60

28. 查询所有学生的课程及分数情况（存在学生没成绩、没选课的情况）

所有学生、课程、分数
学生表、成绩表
学生表 左连接 成绩表

SELECT
    *
FROM Student a
LEFT JOIN sco b
ON a.sno = b.sno

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

学生姓名、课程名称、分数
学生表、课程表、成绩表
关联成绩表和课程表，并筛选出成绩在70分以上的记录
关联学生表，获取学生姓名

SELECT 
    s.sname,
    c.Cname,
    a.score
FROM SCo a
LEFT JOIN Course c ON c.cno = a.cno
LEFT JOIN Student s ON s.sno = a.sno
WHERE score > 70

30. 查询存在不及格的课程

不及格、课程
成绩表、课程表
成绩表筛选分数 < 60的 cno (去重)
关联课程表（子查询）

SELECT 
    cno,
    cname
FROM Course
WHERE cno IN (SELECT
    DISTINCT cno
FROM sco s
WHERE score < 60)

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

课程编号、成绩、学生学号、学生姓名
成绩表，筛选 score > 70
关联学生表

SELECT 
    b.sno,
    b.sname,
    a.score
FROM Sco a
LEFT JOIN Student b ON a.sno = b.sno
WHERE a.cno = 01 AND a.score > 70

32. 查询每门课程的学生人数

课程、学生人数
成绩表
分组聚合

SELECT
    cno,
    count(sno)
FROM SCo
GROUP BY cno

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

张三老师，成绩不重复，课程，成绩最高，学生信息
教师表、学生表、成绩表、课程表
关联四张表
筛选 张三 老师
按分数降序排序，使用 limit 只显示第一条

SELECT
    st.sname,
    s.score
FROM Sco s
LEFT JOIN Student st ON st.sno = s.sno
LEFT JOIN Course c ON c.Cno = s.Cno
LEFT JOIN Teacher t ON c.Tno = t.Tno
WHERE t.Tname = '张三'
ORDER BY s.score DESC
Limit 0, 1

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

关联四张表，删选出 张三 老师所授课程的记录
使用 dense_rank() 进行排名
筛选出第一名

# 基本四表关联
SELECT
    st.sname,
    s.score,
    DENSE_RANK() over (ORDER BY s.score DESC) Score_Rank
FROM Sco s
LEFT JOIN Student st ON st.sno = s.sno
LEFT JOIN Course c ON c.Cno = s.Cno
LEFT JOIN Teacher t ON c.Tno = t.Tno
WHERE t.Tname = '张三'

# 子查询，窗口函数需要 MySQL 8以上版本
SELECT
    *
FROM（
    SELECT
        st.sname,
        s.score,
        DENSE_RANK() over (ORDER BY s.score DESC) Score_Rank
    FROM Sco s
    LEFT JOIN Student st ON st.sno = s.sno
    LEFT JOIN Course c ON c.Cno = s.Cno
    LEFT JOIN Teacher t ON c.Tno = t.Tno
    WHERE t.Tname = '张三'
）a
WHERE Score_Rank = 1;

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

不同课程、成绩相同、学生编号、学生成绩
成绩表
自连接成绩表，连接条件是学生编号相同、课程编号不同、成绩相同
去重

# 可以查出有一个学生，自己三门不同的课成绩正好都是80分
SELECT
    DISTINCT a.*
FROM Sco a
INNER JOIN Sco b ON a.sno = b.sno
AND a.cno != b.cno
AND a.score = b.score

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

成绩表
dense_rank() (需要 MySQL 8.0)
筛选出前两名

SELECT 
    DENSE_RANK(score) over (PARTITION BY cno ORDER BY score DESC) score_rank
FROM Sco

# 筛选前两个
SELECT 
*
FROM (
    SELECT 
        DENSE_RANK(score) over (PARTITION BY cno ORDER BY score DESC) score_rank
    FROM Sco
) a
WHERE score_rank <= 2;

8 版本一下的写法

自关联，a.cno = b.cno AND a.score a.score < b.score，意思是列出同一门课程内所有分数比较的情况
通过 a.sno、b.sno 可以联合确定这个同学这门课的当前分数比多少个其他记录高/低
如果这个特定的 a.sno 和 b.sno 组合出现在这张表里的次数少于 2个，意味着这个学号+课程号+分数的组合是这么课里排前2的

# MySQL 5.7
SELECT
    a.sno,
    a.cno,
    a.score
FROM SCo a
WHERE (
    SELECT count(b.score)
    FROM SCo b 
    WHERE a.cno = b.cno AND a.score < b.score
) < 2
ORDER BY a.cno, a.score DESC

37. 统计每门课程的选修人数（超过5人的才统计）

每门课程、选修人数、超过5人统计
成绩表

SELECT
    cno,
    COUNT(sno)
FROM Sco
GROUP BY cno
HAVING count(sno) > 5

38. 查询至少选修两门课程的学生学号

成绩表
按学号，统计 cno 数目 >= 2

SELECT
 sno,
 count(cno) ct
FROM Sco
GROUP BY sno
HAVING ct >= 2

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

成绩表、学生表、课程表
课程表：计算课程总数
成绩表，按 sno 计算 count(cno)
筛选出二者相等的学生的 sno
获取学生信息

# 查询课程总数
SELECT
    COUNT(cno) as class_count
FROM Course

# 统计每个学生选修的课程数目
SELECT
    count(cno) student_class_count
FROM SCo
GROUP BY sno

# 筛选学习全部课程的学生的 sno 
SELECT
    sno
FROM SCo
GROUP BY sno
HAVING count(cno) = (
    SELECT
        COUNT(cno)
    FROM Course
)

# 关联学生表
SELECT
    a.*
FROM Student a
WHERE a.sno IN (
    SELECT
        sno
    FROM SCo
    GROUP BY sno
    HAVING count(cno) = (
        SELECT
            COUNT(cno)
        FROM Course
    )
)

40. 查询各学生的年龄，只按年份计算

学生表
使用 now()、year() 函数计算

SELECT
    *,
    year(now()) - year(sage) as age
FROM Student

41. 查询学生年龄，按照出生日期计算，如果当前月日 < 出生月日，则年龄-1

学生表
使用 now()、timestampdiff() 函数计算年龄
- timestampdiff(时间单位，起始完整时间，结束完胜之间) ，这个方法会自动根据月日值进行 -1

SELECT
    *,
    TIMESTAMPDIFF(year, Sage, now())
FROM Student

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

周数
学生表
使用 week() 函数

SELECT 
    *
FROM Student
WHERE week('2020-05-20 00:00:00') = week(sage) # 由于真的查当前时间会查不到东西，随意手动输入

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

SELECT 
    *
FROM Student
WHERE week(now()) + 1 = week(sage)

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

月份
学生表
使用 month() 函数

SELECT
    *
FROM Student
WHERE MONTH(now()) = MONTH(sage)

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

SELECT
    *
FROM Student
WHERE MONTH(now()) + 1 = MONTH(sage)

45 道 MySQL 练习题#

建库建表#

student 学生表#

course 课程表#

techear 教师表#

sco 成绩表#

题目#

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

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

1.2 查询存在01课程但可能不存在02课程的情况（不存在显示为null）#

1.3 查询不存在01课程但存在02课程的学生及02课程的成绩#

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

提示

3. 查询在SCo中存在成绩记录的学生的信息#

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为 null）#

4.1 查询有成绩的学生的信息#

5. 查询 [李] 姓老师的数量#

6.查询学过 [张三] 老师的课的学生的信息#

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

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

9. 查询和 01 同学的课程完全相同的同学的信息#

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

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

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

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

14. 查询各科成绩最高分、最低分、平均分#

15. 按各科成绩进行排序，并显示排名，Score 重复时也要继续排名#

15.1 按各科成绩进行排序，并显示排名，Score 重复时合并名次（并列）#

MySQL 8.0 开始支持窗口函数#

16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺（排名跳跃）#

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺（排名连续）#

17. 同级各科成绩各分数段人数：课程编号，课程名称，[100,85],[85,70],[70,60],[60,0] 及所占百分比#

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

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

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

21. 查询男生、女生人数#

22. 查询名字中含有"风"字的学生信息#

23. 查询同名同性学生名单，并统计同名同性人数#

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

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

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

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

28. 查询所有学生的课程及分数情况（存在学生没成绩、没选课的情况）#

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

30. 查询存在不及格的课程#

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

32. 查询每门课程的学生人数#

33. 假设成绩不重复，查询选修 「张三」老师所授课程的学生中，成绩最高的学生的信息和成绩#

34. 假设成绩有重复的情况下，查询选修 「张三」老师所授课程的学生中，成绩最高的学生的信息和成绩#

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

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

37. 统计每门课程的选修人数（超过5人的才统计）#

38. 查询至少选修两门课程的学生学号#

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

40. 查询各学生的年龄，只按年份计算#

41. 查询学生年龄，按照出生日期计算，如果当前月日 < 出生月日，则年龄-1#

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

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

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

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

45 道 MySQL 练习题

建库建表

student 学生表

course 课程表

techear 教师表

sco 成绩表

题目

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

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

1.2 查询存在01课程但可能不存在02课程的情况（不存在显示为null）

1.3 查询不存在01课程但存在02课程的学生及02课程的成绩

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

3. 查询在SCo中存在成绩记录的学生的信息

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为 null）

4.1 查询有成绩的学生的信息

5. 查询 [李] 姓老师的数量

6.查询学过 [张三] 老师的课的学生的信息

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

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

9. 查询和 01 同学的课程完全相同的同学的信息

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

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

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

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

14. 查询各科成绩最高分、最低分、平均分

15. 按各科成绩进行排序，并显示排名，Score 重复时也要继续排名

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

MySQL 8.0 开始支持窗口函数

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

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

17. 同级各科成绩各分数段人数：课程编号，课程名称，[100,85],[85,70],[70,60],[60,0] 及所占百分比

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

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

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

21. 查询男生、女生人数

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

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

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

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

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

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

28. 查询所有学生的课程及分数情况（存在学生没成绩、没选课的情况）

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

30. 查询存在不及格的课程

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

32. 查询每门课程的学生人数

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

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

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

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

37. 统计每门课程的选修人数（超过5人的才统计）

38. 查询至少选修两门课程的学生学号

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

40. 查询各学生的年龄，只按年份计算

41. 查询学生年龄，按照出生日期计算，如果当前月日 < 出生月日，则年龄-1

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

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

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

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