sql语句练习（一）

以下为数据库sql50中的四张表，其结构如下：

学生表t_student——学生编号（stu_id），学生姓名（stu_name），出生年月（stu_birth），学生性别（stu_sex）

教师表t_teacher——教师编号（tea_id），教师姓名（tea_name）

课程表t_course——课程编号（cou_id），课程名称（cou_name），教师编号（tea_id）

成绩表t_score——学生编号（stu_id），课程编号（cou_id），学生成绩（stu_score）

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

知识点：自联结、显式内部联结（INNER JOIN）

方法一：分两步查询（自联结>内部联结）
mysql> SELECT c.*, score_01 AS '01', score_02 AS '02'
    -> FROM t_student c
    -> INNER JOIN (
    ->     SELECT a.stu_id, a.cou_id cou_01, a.stu_score score_01, b.cou_id cou_02, b.stu_score score_02
    ->     FROM t_score a
    ->     INNER JOIN t_score b ON a.stu_id = b.stu_id AND b.cou_id = '02' AND a.stu_score > b.stu_score
    ->     WHERE a.cou_id = '01')t
    -> ON c.stu_id = t.stu_id;
+--------+----------+------------+---------+------+------+
| stu_id | stu_name | stu_birth  | stu_sex | 01   | 02   |
+--------+----------+------------+---------+------+------+
| 02     | 钱电     | 1990-12-21 | 男      |   70 |   60 |
| 04     | 李云     | 1990-08-06 | 男      |   50 |   30 |
+--------+----------+------------+---------+------+------+

方法二：内部联结、自联结
mysql> SELECT a.*, b.stu_score AS '01', c.stu_score AS '02'
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id AND b.cou_id = '01'
    -> INNER JOIN t_score c ON a.stu_id = c.stu_id AND c.cou_id = '02'
    -> WHERE b.stu_score > c.stu_score;

2. 查询“01”课程比“02”课程成绩低的学生的信息及其课程分数。

知识点：隐式内部联结

mysql> SELECT a.*, b.stu_score AS '01', c.stu_score AS '02'
    -> FROM t_student a, t_score b, t_score c
    -> WHERE a.stu_id = b.stu_id AND a.stu_id = c.stu_id
    -> AND b.cou_id = '01' AND c.cou_id = '02'
    -> AND b.stu_score < c.stu_score;
+--------+----------+------------+---------+------+------+
| stu_id | stu_name | stu_birth  | stu_sex | 01   | 02   |
+--------+----------+------------+---------+------+------+
| 01     | 赵雷     | 1990-01-01 | 男      |   80 |   90 |
| 05     | 周梅     | 1991-12-01 | 女      |   76 |   87 |
+--------+----------+------------+---------+------+------+

3. 查询平均成绩及格（大于、等于60分）的学生的编号、姓名及平均成绩。

知识点：AVG()函数、ROUND(x,d)函数、GROUP BY分组、HAVING过滤（分组后）、内部联结

方法一：分两步查询（过滤>内部联结）
mysql> SELECT a.stu_id, stu_name, score_avg
    -> FROM t_student a
    -> INNER JOIN (
    ->     SELECT stu_id, ROUND(AVG(stu_score), 2) score_avg
    ->     FROM t_score
    ->     GROUP BY stu_id
    ->     HAVING score_avg >= 60)t
    -> ON a.stu_id = t.stu_id;
+--------+----------+-----------+
| stu_id | stu_name | score_avg |
+--------+----------+-----------+
| 01     | 赵雷     |     89.67 |
| 02     | 钱电     |     70.00 |
| 03     | 孙风     |     80.00 |
| 05     | 周梅     |     81.50 |
| 07     | 郑竹     |     93.50 |
+--------+----------+-----------+

方法二：内部联结
mysql> SELECT a.stu_id, stu_name, ROUND(AVG(stu_score), 2) score_avg
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> GROUP BY b.stu_id
    -> HAVING score_avg >= 60;

4. 查询平均成绩不及格（小于60分）的学生的编号、姓名、平均成绩。（包括没有成绩的同学）

知识点：NOT EXISTS关键字、UNION操作符、内部联结

步骤1：检索平均成绩不合格的学生信息
mysql> SELECT a.stu_id, stu_name, ROUND(AVG(stu_score), 2) score_avg
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> GROUP BY b.stu_id
    -> HAVING score_avg < 60;
+--------+----------+-----------+
| stu_id | stu_name | score_avg |
+--------+----------+-----------+
| 04     | 李云     |     33.33 |
| 06     | 吴兰     |     32.50 |
+--------+----------+-----------+

步骤2：检索没有成绩的学生（NOT EXISTS）
mysql> SELECT a.stu_id, stu_name, 0 AS score_avg
    -> FROM t_student a
    -> WHERE NOT EXISTS (
    ->     SELECT * FROM t_score b
    ->     WHERE a.stu_id = b.stu_id);
+--------+----------+-----------+
| stu_id | stu_name | score_avg |
+--------+----------+-----------+
| 08     | 王菊     |         0 |
+--------+----------+-----------+
步骤2：检索没有成绩的学生（NOT IN）
mysql> SELECT a.stu_id, stu_name, 0 AS score_avg
    -> FROM t_student a
    -> WHERE a.stu_id NOT IN (
    ->     SELECT stu_id FROM t_score);
+--------+----------+-----------+
| stu_id | stu_name | score_avg |
+--------+----------+-----------+
| 08     | 王菊     |         0 |
+--------+----------+-----------+

步骤3：UNION操作符连接两个表（步骤1+步骤2）
mysql> SELECT a.stu_id, stu_name, ROUND(AVG(stu_score), 2) score_avg
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> GROUP BY b.stu_id
    -> HAVING score_avg < 60
    -> UNION
    -> SELECT a.stu_id, stu_name, 0 AS score_avg
    -> FROM t_student a
    -> WHERE NOT EXISTS (
    ->     SELECT * FROM t_score b
    ->     WHERE a.stu_id = b.stu_id);
+--------+----------+-----------+
| stu_id | stu_name | score_avg |
+--------+----------+-----------+
| 04     | 李云     |     33.33 |
| 06     | 吴兰     |     32.50 |
| 08     | 王菊     |      0.00 |
+--------+----------+-----------+

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

知识点：外部联结（LEFT OUTER JOIN）
**注意：**因为是左外部联结，所以在GROUP BY子句中，应以表a的stu_id为准，否则会报错。

mysql> SELECT a.stu_id, stu_name, COUNT(cou_id) score_count, SUM(stu_score) score_sum
    -> FROM t_student a
    -> LEFT OUTER JOIN t_score b ON a.stu_id = b.stu_id
    -> GROUP BY a.stu_id;
+--------+----------+-------------+-----------+
| stu_id | stu_name | score_count | score_sum |
+--------+----------+-------------+-----------+
| 01     | 赵雷     |           3 |       269 |
| 02     | 钱电     |           3 |       210 |
| 03     | 孙风     |           3 |       240 |
| 04     | 李云     |           3 |       100 |
| 05     | 周梅     |           2 |       163 |
| 06     | 吴兰     |           2 |        65 |
| 07     | 郑竹     |           2 |       187 |
| 08     | 王菊     |           0 |      NULL |
+--------+----------+-------------+-----------+

6. 查询“李”姓老师的数量。

知识点：LIKE操作符（用通配符%进行过滤）

mysql> SELECT COUNT(tea_name)
    -> FROM t_teacher
    -> WHERE tea_name LIKE '李%';
+-----------------+
| COUNT(tea_name) |
+-----------------+
|               1 |
+-----------------+

7. 查询考了“张三”老师课程的学生的信息。

知识点：子查询、内部联结（INNER JOIN）

方法一：子查询、内部联结
mysql> SELECT c.*
    -> FROM t_student c
    -> INNER JOIN t_score d ON c.stu_id = d.stu_id
    -> AND d.cou_id IN (
    ->     SELECT cou_id
    ->     FROM t_course a
    ->     INNER JOIN t_teacher b ON a.tea_id = b.tea_id AND tea_name = '张三');
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 01     | 赵雷     | 1990-01-01 | 男      |
| 02     | 钱电     | 1990-12-21 | 男      |
| 03     | 孙风     | 1990-05-20 | 男      |
| 04     | 李云     | 1990-08-06 | 男      |
| 05     | 周梅     | 1991-12-01 | 女      |
| 07     | 郑竹     | 1989-07-01 | 女      |
+--------+----------+------------+---------+

方法二：内部联结
mysql> SELECT a.*
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> INNER JOIN t_course c ON b.cou_id = c.cou_id
    -> INNER JOIN t_teacher d ON c.tea_id = d.tea_id AND tea_name = '张三';

8. 查询没有考“张三”老师课程的学生的信息。

知识点：子查询、内部联结（INNER JOIN）、NOT IN 操作符
**注意：**不能使用第7题的方法二（tea_name != '张三'），因为一个学生可以选择多门课程，具体见测试数据。所以应该先设定条件（stu_id），再进行内部联结。

#测试数据
mysql> SELECT a.*, b.cou_id, c.tea_id, d.tea_name
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> INNER JOIN t_course c ON b.cou_id = c.cou_id
    -> INNER JOIN t_teacher d ON c.tea_id = d.tea_id AND tea_name != '张三';
+--------+----------+------------+---------+--------+--------+----------+
| stu_id | stu_name | stu_birth  | stu_sex | cou_id | tea_id | tea_name |
+--------+----------+------------+---------+--------+--------+----------+
| 01     | 赵雷     | 1990-01-01 | 男      | 01     | 02     | 李四     |
| 01     | 赵雷     | 1990-01-01 | 男      | 03     | 03     | 王五     |
| 02     | 钱电     | 1990-12-21 | 男      | 01     | 02     | 李四     |
| 02     | 钱电     | 1990-12-21 | 男      | 03     | 03     | 王五     |
| 03     | 孙风     | 1990-05-20 | 男      | 01     | 02     | 李四     |
| 03     | 孙风     | 1990-05-20 | 男      | 03     | 03     | 王五     |
| 04     | 李云     | 1990-08-06 | 男      | 01     | 02     | 李四     |
| 04     | 李云     | 1990-08-06 | 男      | 03     | 03     | 王五     |
| 05     | 周梅     | 1991-12-01 | 女      | 01     | 02     | 李四     |
| 06     | 吴兰     | 1992-03-01 | 女      | 01     | 02     | 李四     |
| 06     | 吴兰     | 1992-03-01 | 女      | 03     | 03     | 王五     |
| 07     | 郑竹     | 1989-07-01 | 女      | 03     | 03     | 王五     |
+--------+----------+------------+---------+--------+--------+----------+

mysql> SELECT d.*
    -> FROM t_student d
    -> WHERE d.stu_id NOT IN (
    ->     SELECT stu_id
    ->     FROM t_score a
    ->     INNER JOIN t_course b ON a.cou_id = b.cou_id
    ->     INNER JOIN t_teacher c ON b.tea_id = c.tea_id AND tea_name = '张三');
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 06     | 吴兰     | 1992-03-01 | 女      |
| 08     | 王菊     | 1990-01-20 | 女      |
+--------+----------+------------+---------+

9. 查询考了“01”课程和“02”课程的学生的信息。

知识点：自联结、内部联结

mysql> SELECT a.*
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id AND b.cou_id = '01'
    -> INNER JOIN t_score c ON a.stu_id = c.stu_id AND c.cou_id = '02';
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 01     | 赵雷     | 1990-01-01 | 男      |
| 02     | 钱电     | 1990-12-21 | 男      |
| 03     | 孙风     | 1990-05-20 | 男      |
| 04     | 李云     | 1990-08-06 | 男      |
| 05     | 周梅     | 1991-12-01 | 女      |
+--------+----------+------------+---------+

10. 查询考了“01”课程但没考“02”课程的学生的信息。

知识点：子查询

mysql> SELECT a.*
    -> FROM t_student a
    -> WHERE a.stu_id IN (
    ->     SELECT stu_id FROM t_score WHERE cou_id = '01')
    -> AND a.stu_id NOT IN (
    ->     SELECT stu_id FROM t_score WHERE cou_id = '02');
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 06     | 吴兰     | 1992-03-01 | 女      |
+--------+----------+------------+---------+

11. 查询没有考全所有课程的学生的信息。（不包括没参加考试的学生）

知识点：内部联结、IN 操作符、子查询

步骤1：检索考试科目数小于课程总数的学生
mysql> SELECT *
    -> FROM (
    ->     SELECT stu_id, COUNT(cou_id) stu_cou_number
    ->     FROM t_score
    ->     GROUP BY stu_id)t
    -> WHERE stu_cou_number < (
    ->     SELECT COUNT(*) cou_number FROM t_course);
+--------+----------------+
| stu_id | stu_cou_number |
+--------+----------------+
| 05     |              2 |
| 06     |              2 |
| 07     |              2 |
+--------+----------------+

步骤2：:检索学生具体信息（依据步骤1）
mysql> SELECT *
    -> FROM t_student a
    -> WHERE a.stu_id IN (
    ->     SELECT stu_id FROM (
    ->         SELECT stu_id, COUNT(cou_id) stu_cou_number FROM t_score GROUP BY stu_id)t
    ->     WHERE stu_cou_number < (SELECT COUNT(*) cou_number FROM t_course));
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 05     | 周梅     | 1991-12-01 | 女      |
| 06     | 吴兰     | 1992-03-01 | 女      |
| 07     | 郑竹     | 1989-07-01 | 女      |
+--------+----------+------------+---------+

12. 查询没有考全所有课程的学生的信息。（包括没参加考试的学生）

知识点：自联结、内部联结、NOT IN 操作符

步骤1：检索学全了三门课程的学生stu_id
mysql> SELECT a.stu_id
    -> FROM t_score a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id AND b.cou_id = '02'
    -> INNER JOIN t_score c ON a.stu_id = c.stu_id AND c.cou_id = '03'
    -> WHERE a.cou_id = '01';
+--------+
| stu_id |
+--------+
| 01     |
| 02     |
| 03     |
| 04     |
+--------+

步骤2：依据:步骤1的stu_id排除学生
mysql> SELECT d.*
    -> FROM t_student d
    -> WHERE d.stu_id NOT IN (
    ->     SELECT a.stu_id
    ->     FROM t_score a
    ->     INNER JOIN t_score b ON a.stu_id = b.stu_id AND b.cou_id = '02'
    ->     INNER JOIN t_score c ON a.stu_id = c.stu_id AND c.cou_id = '03'
    ->     WHERE a.cou_id = '01') ;
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 05     | 周梅     | 1991-12-01 | 女      |
| 06     | 吴兰     | 1992-03-01 | 女      |
| 07     | 郑竹     | 1989-07-01 | 女      |
| 08     | 王菊     | 1990-01-20 | 女      |
+--------+----------+------------+---------+

13. 查询至少有一门课与“01”号学生所考课程相同的学生的信息。

知识点：内部联结、子查询、IN 操作符、DISTINCT关键字
**注意：**方法一中需要使用DISTINCT关键字，因为是内联结，行数是成绩表的行数，导致数据冗余。

方法一：内部联结
mysql> SELECT DISTINCT a.*
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id AND b.stu_id != '01'
    -> AND cou_id IN (
    ->     SELECT cou_id FROM t_score WHERE stu_id = '01');
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 02     | 钱电     | 1990-12-21 | 男      |
| 03     | 孙风     | 1990-05-20 | 男      |
| 04     | 李云     | 1990-08-06 | 男      |
| 05     | 周梅     | 1991-12-01 | 女      |
| 06     | 吴兰     | 1992-03-01 | 女      |
| 07     | 郑竹     | 1989-07-01 | 女      |
+--------+----------+------------+---------+

方法二：子查询、内部联结
mysql> SELECT b.*
    -> FROM t_student b
    -> INNER JOIN (
    ->     SELECT DISTINCT a.stu_id
    ->     FROM t_score a
    ->     WHERE a.stu_id != '01' AND a.cou_id IN (
    ->         SELECT cou_id FROM t_score WHERE stu_id = '01'))t
    -> ON b.stu_id = t.stu_id;

14. 查询没考“张三”老师讲授的任一门课程的学生姓名。

知识点：子查询、NOT IN 操作符
**注意：**不能将学生表和成绩表内联结，以cou_id作为条件（NOT IN），因为一个学生可以选择多门课程（同第8题）。还是用排除法吧，先检索选了“张三”老师课程的stu_id，再排除。

mysql> SELECT stu_name
    -> FROM t_student c
    -> WHERE c.stu_id NOT IN (
    ->     SELECT stu_id FROM t_score
    ->     WHERE cou_id = (
    ->         SELECT cou_id FROM t_course a, t_teacher b
    ->         WHERE a.tea_id = b.tea_id AND b.tea_name = '张三'));
+----------+
| stu_name |
+----------+
| 吴兰     |
| 王菊     |
+----------+

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

知识点：COUNT()函数、ROUND(x,d)函数、AVG()函数、子查询、GROUP BY分组、HAVING分组后过滤、子查询、内部联结

mysql> SELECT a.stu_id, stu_name, score_avg
    -> FROM t_student a, (
    ->     SELECT stu_id, COUNT(cou_id) stu_cou, ROUND(AVG(stu_score),2) score_avg
    ->     FROM t_score
    ->     WHERE stu_score < 60
    ->     GROUP BY stu_id
    ->     HAVING stu_cou >= 2)t
    -> WHERE a.stu_id = t.stu_id;
+--------+----------+-----------+
| stu_id | stu_name | score_avg |
+--------+----------+-----------+
| 04     | 李云     |     33.33 |
| 06     | 吴兰     |     32.50 |
+--------+----------+-----------+

16. 检索“01”课程分数小于60分，按分数降序排列的学生信息。

知识点：内部联结、ORDER BY子句、DESC关键字

mysql> SELECT a.*, stu_score
    -> FROM t_student a
    -> INNER JOIN (
    ->     SELECT *
    ->     FROM t_score
    ->     WHERE cou_id = '01' AND stu_score < 60)b
    -> ON a.stu_id = b.stu_id
    -> ORDER BY stu_score DESC;
+--------+----------+------------+---------+-----------+
| stu_id | stu_name | stu_birth  | stu_sex | stu_score |
+--------+----------+------------+---------+-----------+
| 04     | 李云     | 1990-08-06 | 男      |        50 |
| 06     | 吴兰     | 1992-03-01 | 女      |        31 |
+--------+----------+------------+---------+-----------+

17. 按各科成绩进行排序，并显示排名。

知识点：窗口函数中的序号函数（RANK()）、DESC关键字、内部联结

mysql> SELECT a.stu_id, stu_name, cou_name, stu_score,
    -> RANK() OVER (PARTITION BY b.cou_id ORDER BY stu_score DESC) 'cou_range'
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> INNER JOIN t_course c ON b.cou_id = c.cou_id;
+--------+----------+----------+-----------+-----------+
| stu_id | stu_name | cou_name | stu_score | cou_range |
+--------+----------+----------+-----------+-----------+
| 01     | 赵雷     | 语文     |        80 |         1 |
| 03     | 孙风     | 语文     |        80 |         1 |
| 05     | 周梅     | 语文     |        76 |         3 |
| 02     | 钱电     | 语文     |        70 |         4 |
| 04     | 李云     | 语文     |        50 |         5 |
| 06     | 吴兰     | 语文     |        31 |         6 |
| 01     | 赵雷     | 数学     |        90 |         1 |
| 07     | 郑竹     | 数学     |        89 |         2 |
| 05     | 周梅     | 数学     |        87 |         3 |
| 03     | 孙风     | 数学     |        80 |         4 |
| 02     | 钱电     | 数学     |        60 |         5 |
| 04     | 李云     | 数学     |        30 |         6 |
| 01     | 赵雷     | 英语     |        99 |         1 |
| 07     | 郑竹     | 英语     |        98 |         2 |
| 02     | 钱电     | 英语     |        80 |         3 |
| 03     | 孙风     | 英语     |        80 |         3 |
| 06     | 吴兰     | 英语     |        34 |         5 |
| 04     | 李云     | 英语     |        20 |         6 |
+--------+----------+----------+-----------+-----------+

18. 查询学生的总成绩并进行排名。

知识点：窗口函数中的序号函数（RANK()）、SUM()函数、内部联结

mysql> SELECT a.stu_id '编号', stu_name '姓名', SUM(stu_score) '总成绩',
    -> RANK() OVER (ORDER BY SUM(stu_score) DESC) '排名'
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> GROUP BY b.stu_id;
+--------+--------+-----------+--------+
| 编号   | 姓名   | 总成绩    | 排名   |
+--------+--------+-----------+--------+
| 01     | 赵雷   |       269 |      1 |
| 03     | 孙风   |       240 |      2 |
| 02     | 钱电   |       210 |      3 |
| 07     | 郑竹   |       187 |      4 |
| 05     | 周梅   |       163 |      5 |
| 04     | 李云   |       100 |      6 |
| 06     | 吴兰   |        65 |      7 |
+--------+--------+-----------+--------+

19. 查询不同老师所教的不同课程的平均分，并从高到低显示 。

知识点：ROUND()函数、AVG()函数、窗口函数中的序号函数（ROW_NUMBER()）、内部联结（隐式）
注意：ORDER BY子句中，不要用'课程平均分'。

mysql> SELECT tea_name '老师', cou_name '课程', ROUND(AVG(stu_score),2) '课程平均分'
    -> FROM t_teacher a
    -> INNER JOIN t_course b ON a.tea_id = b.tea_id
    -> INNER JOIN t_score c ON b.cou_id = c.cou_id
    -> GROUP BY c.cou_id
    -> ORDER BY ROUND(AVG(stu_score),2) DESC;
+--------+--------+-----------------+
| 老师   | 课程   | 课程平均分      |
+--------+--------+-----------------+
| 张三   | 数学   |           72.67 |
| 王五   | 英语   |           68.50 |
| 李四   | 语文   |           64.50 |
+--------+--------+-----------------+

20. 查询所有课程的成绩第2、3名的学生信息及该课程成绩。

知识点：窗口函数中的序号函数（ROW_NUMBER()）、内部联结、子查询、IN操作符

mysql> SELECT *
    -> FROM (
    ->     SELECT a.stu_id, stu_name, cou_name, stu_score,
    ->     ROW_NUMBER() OVER (PARTITION BY c.cou_id ORDER BY stu_score DESC) score_order
    ->     FROM t_student a
    ->     INNER JOIN t_score b ON a.stu_id = b.stu_id
    ->     INNER JOIN t_course c ON b.cou_id = c.cou_id)t
    -> WHERE t.score_order IN (2,3);
+--------+----------+----------+-----------+-------------+
| stu_id | stu_name | cou_name | stu_score | score_order |
+--------+----------+----------+-----------+-------------+
| 03     | 孙风     | 语文     |        80 |           2 |
| 05     | 周梅     | 语文     |        76 |           3 |
| 07     | 郑竹     | 数学     |        89 |           2 |
| 05     | 周梅     | 数学     |        87 |           3 |
| 07     | 郑竹     | 英语     |        98 |           2 |
| 02     | 钱电     | 英语     |        80 |           3 |
+--------+----------+----------+-----------+-------------+

21. 统计各科成绩：课程编号、课程名称、及格人数、及格率。

知识点：SUM()函数、ROUND(x,d)函数、CASE WHEN 条件 THEN... ELSE... END、内部联结

mysql> SELECT a.cou_id '课程编号', cou_name '课程名称',
    -> SUM(CASE WHEN stu_score >= 60 THEN 1 ELSE 0 END) '及格人数',
    -> ROUND(100 * SUM(CASE WHEN stu_score >= 60 THEN 1 ELSE 0 END) / COUNT(*) , 2) '及格率'
    -> FROM t_course a
    -> INNER JOIN t_score b ON a.cou_id = b.cou_id
    -> GROUP BY b.cou_id;
+--------------+--------------+--------------+-----------+
| 课程编号     | 课程名称     | 及格人数     | 及格率    |
+--------------+--------------+--------------+-----------+
| 01           | 语文         |            4 |     66.67 |
| 02           | 数学         |            5 |     83.33 |
| 03           | 英语         |            4 |     66.67 |
+--------------+--------------+--------------+-----------+

22. 查询学生的平均成绩及名次。

知识点：AVG()函数、ROUND(x,d)函数、窗口函数中的序号函数（ROW_NUMBER()）、内部联结

mysql> SELECT a.stu_id '学生编号', stu_name '学生姓名',
    -> ROUND(AVG(stu_score), 2) '平均成绩',
    -> ROW_NUMBER() OVER (ORDER BY ROUND(AVG(stu_score), 2) DESC) '排名'
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> GROUP BY b.stu_id;
+--------------+--------------+--------------+--------+
| 学生编号     | 学生姓名     | 平均成绩     | 排名   |
+--------------+--------------+--------------+--------+
| 07           | 郑竹         |        93.50 |      1 |
| 01           | 赵雷         |        89.67 |      2 |
| 05           | 周梅         |        81.50 |      3 |
| 03           | 孙风         |        80.00 |      4 |
| 02           | 钱电         |        70.00 |      5 |
| 04           | 李云         |        33.33 |      6 |
| 06           | 吴兰         |        32.50 |      7 |
+--------------+--------------+--------------+--------+

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

知识点：窗口函数中的序号函数（RANK()）、内部联结、子查询

mysql> SELECT *
    -> FROM (
    ->     SELECT a.stu_id, stu_name, cou_name, stu_score,
    ->     RANK() OVER(PARTITION BY b.cou_id ORDER BY stu_score DESC) cou_order
    ->     FROM t_student a
    ->     INNER JOIN t_score b ON a.stu_id = b.stu_id
    ->     INNER JOIN t_course c ON b.cou_id = c.cou_id)t
    -> WHERE cou_order <= 3;
+--------+----------+----------+-----------+-----------+
| stu_id | stu_name | cou_name | stu_score | cou_order |
+--------+----------+----------+-----------+-----------+
| 01     | 赵雷     | 语文     |        80 |         1 |
| 03     | 孙风     | 语文     |        80 |         1 |
| 05     | 周梅     | 语文     |        76 |         3 |
| 01     | 赵雷     | 数学     |        90 |         1 |
| 07     | 郑竹     | 数学     |        89 |         2 |
| 05     | 周梅     | 数学     |        87 |         3 |
| 01     | 赵雷     | 英语     |        99 |         1 |
| 07     | 郑竹     | 英语     |        98 |         2 |
| 02     | 钱电     | 英语     |        80 |         3 |
| 03     | 孙风     | 英语     |        80 |         3 |
+--------+----------+----------+-----------+-----------+

24. 查询每门课程的选课数量（按考试人数计算）。

知识点：COUNT()函数、GROUP BY分组

mysql> SELECT cou_id, COUNT(*) cou_num
    -> FROM t_score
    -> GROUP BY cou_id;
+--------+---------+
| cou_id | cou_num |
+--------+---------+
| 01     |       6 |
| 02     |       6 |
| 03     |       6 |
+--------+---------+

25. 查询只考了两门课程的学生的学号和姓名。

知识点：COUNT()函数、GROUP BY分组、HAVING分组后过滤、内部联结
**注意：**内部联结时，不能用COUNT(b.*)，否则会报错

mysql> SELECT a.stu_id, stu_name, COUNT(b.stu_id)
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> GROUP BY b.stu_id
    -> HAVING COUNT(b.stu_id) = 2;
+--------+----------+-----------------+
| stu_id | stu_name | COUNT(b.stu_id) |
+--------+----------+-----------------+
| 05     | 周梅     |               2 |
| 06     | 吴兰     |               2 |
| 07     | 郑竹     |               2 |
+--------+----------+-----------------+

26. 查询男生、女生人数。

知识点：COUNT()函数、GROUP BY分组

mysql> SELECT stu_sex, COUNT(*) '人数'
    -> FROM t_student
    -> GROUP BY stu_sex;
+---------+--------+
| stu_sex | 人数   |
+---------+--------+
| 男      |      4 |
| 女      |      4 |
+---------+--------+

27. 查询名字中含有“风”字的学生信息。

知识点：LIKE操作符（用通配符%进行过滤）

mysql> SELECT *
    -> FROM t_student a
    -> WHERE a.stu_name LIKE '%风%';
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 03     | 孙风     | 1990-05-20 | 男      |
+--------+----------+------------+---------+

28. 查询同名同性的学生名单，并统计同名人数。

知识点：COUNT()函数、GROUP BY分组、HAVING分组后过滤、内部联结

#新插入3条记录
mysql> INSERT t_student VALUES
    -> ('09', '钱电', '1991-12-12', '女'),
    -> ('10', '钱电', '1991-02-12', '男'),
    -> ('11', '郑竹', '1989-08-20', '男');

mysql> SELECT a.*, b.name_num
    -> FROM t_student a
    -> INNER JOIN (
    ->     SELECT stu_name, COUNT(*) 'name_num'
    ->     FROM t_student
    ->     GROUP BY stu_name
    ->     HAVING name_num > 1)b
    -> ON a.stu_name = b.stu_name;
+--------+----------+------------+---------+----------+
| stu_id | stu_name | stu_birth  | stu_sex | name_num |
+--------+----------+------------+---------+----------+
| 02     | 钱电     | 1990-12-21 | 男      |        3 |
| 07     | 郑竹     | 1989-07-01 | 女      |        2 |
| 09     | 钱电     | 1991-12-12 | 女      |        3 |
| 10     | 钱电     | 1991-02-12 | 男      |        3 |
| 11     | 郑竹     | 1989-08-20 | 男      |        2 |
+--------+----------+------------+---------+----------+

29. 查询1990年出生的学生名单。

知识点：时间处理函数：Year()

mysql> SELECT *
    -> FROM t_student
    -> WHERE Year(stu_birth) = 1990;
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 01     | 赵雷     | 1990-01-01 | 男      |
| 02     | 钱电     | 1990-12-21 | 男      |
| 03     | 孙风     | 1990-05-20 | 男      |
| 04     | 李云     | 1990-08-06 | 男      |
| 08     | 王菊     | 1990-01-20 | 女      |
+--------+----------+------------+---------+

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

知识点：GROUP BY分组、ORDER BY排序（DESC）、内部联结

mysql> SELECT a.cou_id, cou_name,
    -> ROUND(AVG(stu_score) ,2) avg_score
    -> FROM t_course a
    -> INNER JOIN t_score b ON a.cou_id = b.cou_id
    -> GROUP BY b.cou_id
    -> ORDER BY avg_score DESC, cou_id;
+--------+----------+-----------+
| cou_id | cou_name | avg_score |
+--------+----------+-----------+
| 02     | 数学     |     72.67 |
| 03     | 英语     |     68.50 |
| 01     | 语文     |     64.50 |
+--------+----------+-----------+

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

知识点：ROUND()函数、AVG()函数、GROUP BY分组、HAVING分组后过滤、内部联结

mysql> SELECT a.stu_id, stu_name,
    -> ROUND(AVG(stu_score),2) avg_score
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> GROUP BY b.stu_id
    -> HAVING avg_score >= 85;
+--------+----------+-----------+
| stu_id | stu_name | avg_score |
+--------+----------+-----------+
| 01     | 赵雷     |     89.67 |
| 07     | 郑竹     |     93.50 |
+--------+----------+-----------+

32. 查询“数学"成绩低于60分的学生姓名和分数。

知识点：内部联结

mysql> SELECT stu_name, stu_score
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id AND stu_score < 60
    -> INNER JOIN t_course c ON b.cou_id = c.cou_id AND cou_name = '数学';
+----------+-----------+
| stu_name | stu_score |
+----------+-----------+
| 李云     |        30 |
+----------+-----------+

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

知识点：内部联结

mysql> SELECT stu_name, cou_name, stu_score
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> INNER JOIN t_course c ON b.cou_id = c.cou_id
    -> WHERE stu_score > 70;
+----------+----------+-----------+
| stu_name | cou_name | stu_score |
+----------+----------+-----------+
| 赵雷     | 语文     |        80 |
| 赵雷     | 数学     |        90 |
| 赵雷     | 英语     |        99 |
| 钱电     | 英语     |        80 |
| 孙风     | 语文     |        80 |
| 孙风     | 数学     |        80 |
| 孙风     | 英语     |        80 |
| 周梅     | 语文     |        76 |
| 周梅     | 数学     |        87 |
| 郑竹     | 数学     |        89 |
| 郑竹     | 英语     |        98 |
+----------+----------+-----------+

34. 查询不及格的课程。

知识点：内部联结

mysql> SELECT stu_name, cou_name, stu_score
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> INNER JOIN t_course c ON b.cou_id = c.cou_id
    -> WHERE stu_score < 60;
+----------+----------+-----------+
| stu_name | cou_name | stu_score |
+----------+----------+-----------+
| 李云     | 语文     |        50 |
| 李云     | 数学     |        30 |
| 李云     | 英语     |        20 |
| 吴兰     | 语文     |        31 |
| 吴兰     | 英语     |        34 |
+----------+----------+-----------+

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

知识点：内部联结

mysql> SELECT a.stu_id, stu_name
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> WHERE cou_id = 01 AND stu_score > 70;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 01     | 赵雷     |
| 03     | 孙风     |
| 05     | 周梅     |
+--------+----------+

36. 查询“张三”老师所教的课程的成绩最高的学生信息及其成绩。

知识点：内部联结、ORDER BY 排序、LIMIT

mysql> SELECT a.*, stu_score
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> INNER JOIN t_course c ON b.cou_id = c.cou_id
    -> INNER JOIN t_teacher d ON c.tea_id = d.tea_id
    -> WHERE tea_name = '张三'
    -> ORDER BY stu_score DESC
    -> LIMIT 1;
+--------+----------+------------+---------+-----------+
| stu_id | stu_name | stu_birth  | stu_sex | stu_score |
+--------+----------+------------+---------+-----------+
| 01     | 赵雷     | 1990-01-01 | 男      |        90 |
+--------+----------+------------+---------+-----------+

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

知识点：自联结、DISTINCT关键字

mysql> SELECT DISTINCT a.*
    -> FROM t_score a, t_score b
    -> WHERE a.stu_score = b.stu_score AND a.cou_id != b.cou_id;
+--------+--------+-----------+
| stu_id | cou_id | stu_score |
+--------+--------+-----------+
| 02     | 03     |        80 |
| 03     | 02     |        80 |
| 03     | 03     |        80 |
| 01     | 01     |        80 |
| 03     | 01     |        80 |
+--------+--------+-----------+

38 查询每门课程成绩最好的前两名同学。

知识点：窗口函数中的序号函数（ROW_NUMBER()）、内部联结、子查询

mysql> SELECT *
    -> FROM (
    ->     SELECT a.stu_id, stu_name, cou_name, stu_score,
    ->     ROW_NUMBER() OVER (PARTITION BY b.cou_id ORDER BY stu_score DESC) score_order
    ->     FROM t_student a
    ->     INNER JOIN t_score b ON a.stu_id = b.stu_id
    ->     INNER JOIN t_course c ON b.cou_id = c.cou_id)t
    -> WHERE score_order <= 2;
+--------+----------+----------+-----------+-------------+
| stu_id | stu_name | cou_name | stu_score | score_order |
+--------+----------+----------+-----------+-------------+
| 01     | 赵雷     | 语文     |        80 |           1 |
| 03     | 孙风     | 语文     |        80 |           2 |
| 01     | 赵雷     | 数学     |        90 |           1 |
| 07     | 郑竹     | 数学     |        89 |           2 |
| 01     | 赵雷     | 英语     |        99 |           1 |
| 07     | 郑竹     | 英语     |        98 |           2 |
+--------+----------+----------+-----------+-------------+

39. 统计每门课程的选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

知识点：COUNT()函数、GROUP BY分组、HAVING分组后过滤、ORDER BY 排序

mysql> SELECT cou_id, COUNT(*) stu_num
    -> FROM t_score
    -> GROUP BY cou_id
    -> HAVING stu_num > 5
    -> ORDER BY stu_num DESC, cou_id;
+--------+---------+
| cou_id | stu_num |
+--------+---------+
| 01     |       6 |
| 02     |       6 |
| 03     |       6 |
+--------+---------+

40. 检索至少选修（参加考试）了两门课程的学生。

知识点：COUNT()函数、GROUP BY分组、HAVING分组后过滤、内部联结

mysql> SELECT a.stu_id, stu_name, COUNT(cou_id) stu_cou
    -> FROM t_student a
    -> INNER JOIN t_score b ON a.stu_id = b.stu_id
    -> GROUP BY b.stu_id
    -> HAVING stu_cou >= 2;
+--------+----------+---------+
| stu_id | stu_name | stu_cou |
+--------+----------+---------+
| 01     | 赵雷     |       3 |
| 02     | 钱电     |       3 |
| 03     | 孙风     |       3 |
| 04     | 李云     |       3 |
| 05     | 周梅     |       2 |
| 06     | 吴兰     |       2 |
| 07     | 郑竹     |       2 |
+--------+----------+---------+

41. 查询选修（参加考试）了全部课程的学生信息。

知识点：COUNT()函数、GROUP BY分组、HAVING分组后过滤、内部联结

mysql> SELECT a.*
    -> FROM t_student a
    -> INNER JOIN (
    ->     SELECT stu_id, COUNT(*) stu_cou
    ->     FROM t_score
    ->     GROUP BY stu_id
    ->     HAVING stu_cou = (SELECT COUNT(*) FROM t_course))t
    -> ON a.stu_id = t.stu_id;
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 01     | 赵雷     | 1990-01-01 | 男      |
| 02     | 钱电     | 1990-12-21 | 男      |
| 03     | 孙风     | 1990-05-20 | 男      |
| 04     | 李云     | 1990-08-06 | 男      |
+--------+----------+------------+---------+

42. 查询每位学生的年龄。

知识点：
日期和时间处理函数：①Date_Format(data,format)，其中%Y（年）、%m（月份）、%d（日）；②CurDate()函数：返回当前日期；③Year()：返回日期的年份
思路：当前年份-出生年份（当前月份小于出生月份再减1）

mysql> SELECT stu_id, stu_name, stu_birth,
    -> CurDate() cur_date,
    -> (
    ->     Year(CurDate()) - Year(stu_birth) -
    ->     (CASE WHEN Date_Format(CurDate(),'%m%d') > Date_Format(stu_birth,'%m%d') THEN 0 ELSE 1 END)
    -> ) stu_age
    ->  FROM t_student;
+--------+----------+------------+------------+---------+
| stu_id | stu_name | stu_birth  | cur_date   | stu_age |
+--------+----------+------------+------------+---------+
| 01     | 赵雷     | 1990-01-01 | 2019-03-10 |      29 |
| 02     | 钱电     | 1990-12-21 | 2019-03-10 |      28 |
| 03     | 孙风     | 1990-05-20 | 2019-03-10 |      28 |
| 04     | 李云     | 1990-08-06 | 2019-03-10 |      28 |
| 05     | 周梅     | 1991-12-01 | 2019-03-10 |      27 |
| 06     | 吴兰     | 1992-03-01 | 2019-03-10 |      27 |
| 07     | 郑竹     | 1989-07-01 | 2019-03-10 |      29 |
| 08     | 王菊     | 1990-01-20 | 2019-03-10 |      29 |
+--------+----------+------------+------------+---------+

43. 查询本周过生日的学生。

知识点：日期和时间处理函数Week()，返回日期的星期数

#测试函数
mysql> SELECT CurDate() cur_date, Week(CurDate()) cur_week;
+------------+----------+
| cur_date   | cur_week |
+------------+----------+
| 2019-03-10 |       10 |
+------------+----------+

#完整代码
mysql> SELECT stu_id, stu_name, stu_birth
    -> FROM t_student
    -> WHERE Week(stu_birth) = Week(CurDate());
+--------+----------+-----------+
| stu_id | stu_name | stu_birth |
+--------+----------+-----------+
| 09     | 测试     | 1998-3-8  |
+--------+----------+-----------+

44. 查询下周过生日的学生。

知识点：日期和时间处理函数Week()，返回日期的星期数

mysql> SELECT stu_id, stu_name, stu_birth
    -> FROM t_student
    -> WHERE Week(stu_birth) = Week(CurDate()) + 1;

45. 查询本月过生日的学生。

知识点：日期和时间处理函数Month()，返回日期的月份

mysql> SELECT stu_id, stu_name, stu_birth
    -> FROM t_student
    -> WHERE Month(stu_birth) = Month(CurDate());
+--------+----------+------------+
| stu_id | stu_name | stu_birth  |
+--------+----------+------------+
| 06     | 吴兰     | 1992-03-01 |
+--------+----------+------------+

46. 查询下个月过生日的学生。

知识点：日期和时间处理函数Month()，返回日期的月份

mysql> SELECT stu_id, stu_name, stu_birth
    -> FROM t_student
    -> WHERE Month(stu_birth) = Month(CurDate()) + 1;

47. 查询和“01”号学生所考课程完全相同的学生的信息。

知识点：GROUP_CONCAT()函数、自联结、内部联结、子查询、COUNT()函数、GROUP BY分组、HAVING分组后过滤
**注意：**方法一实现效果较为全面；而方法二相较于13题添加了对课程数量的限制，主要依靠数据进行检索，实现得不全面。

方法一：
步骤1：使用GROUP_CONCAT()函数对stu_cou进行聚合
mysql> SELECT stu_id, GROUP_CONCAT(cou_id ORDER BY cou_id) AS stu_cou
    -> FROM t_score
    -> GROUP BY stu_id;
+--------+----------+
| stu_id | stu_cou  |
+--------+----------+
| 01     | 01,02,03 |
| 02     | 01,02,03 |
| 03     | 01,02,03 |
| 04     | 01,02,03 |
| 05     | 01,02    |
| 06     | 01,03    |
| 07     | 02,03    |
+--------+----------+
步骤2：自联结、内部联结
mysql> SELECT c.*
    -> FROM (
    ->     SELECT stu_id, GROUP_CONCAT(cou_id ORDER BY cou_id) AS stu_cou FROM t_score GROUP BY stu_id)a
    -> INNER JOIN (
    ->     SELECT stu_id, GROUP_CONCAT(cou_id ORDER BY cou_id) AS stu_cou FROM t_score GROUP BY stu_id)b
    -> ON b.stu_id = '01' AND a.stu_cou = b.stu_cou
    -> INNER JOIN t_student c ON a.stu_id = c.stu_id
    -> WHERE a.stu_id != '01';
+--------+----------+------------+---------+
| stu_id | stu_name | stu_birth  | stu_sex |
+--------+----------+------------+---------+
| 02     | 钱电     | 1990-12-21 | 男      |
| 03     | 孙风     | 1990-05-20 | 男      |
| 04     | 李云     | 1990-08-06 | 男      |
+--------+----------+------------+---------+

方法二：添加对课程数量的限制
mysql> SELECT a.*
    -> FROM t_student a
    -> WHERE a.stu_id IN (
    ->     SELECT DISTINCT stu_id
    ->     FROM t_score
    ->     WHERE stu_id != '01' AND cou_id IN (
    ->         SELECT cou_id FROM t_score WHERE stu_id = '01')
    ->     GROUP BY stu_id
    ->     HAVING COUNT(cou_id) = (
    ->         SELECT COUNT(cou_id) FROM t_score WHERE stu_id = '01'));

48. 查询各科成绩最高分、最低分和平均分。以如下形式显示：课程编号、课程名称、最高分、最低分、平均分、及格率、优秀率。（及格：>=60，优秀：>=90）

知识点：MAX()函数、MIN()函数、ROUND(x,d)函数、AVG()函数、SUM()函数、CASE WHEN 条件 THEN... ELSE... END、内部联结

mysql> SELECT a.cou_id '编号', cou_name '名称',
    -> MAX(stu_score) '最高分', MIN(stu_score) '最低分',
    -> ROUND(AVG(stu_score), 2) '平均分',
    -> ROUND(100 * (SUM(CASE WHEN stu_score >= 60 THEN 1 ELSE 0 END) / SUM(CASE WHEN stu_score THEN 1 ELSE 0 END)) ,2) '及格率',
    -> ROUND(100 * (SUM(CASE WHEN stu_score >= 90 THEN 1 ELSE 0 END) / SUM(CASE WHEN stu_score THEN 1 ELSE 0 END)) ,2) '优秀率'
    -> FROM t_course a
    -> LEFT OUTER JOIN t_score b ON a.cou_id = b.cou_id
    -> GROUP BY a.cou_id;
+--------+--------+-----------+-----------+-----------+-----------+-----------+
| 编号   | 名称   | 最高分    | 最低分    | 平均分    | 及格率    | 优秀率    |
+--------+--------+-----------+-----------+-----------+-----------+-----------+
| 01     | 语文   |        80 |        31 |     64.50 |     66.67 |      0.00 |
| 02     | 数学   |        90 |        30 |     72.67 |     83.33 |     16.67 |
| 03     | 英语   |        99 |        20 |     68.50 |     66.67 |     33.33 |
+--------+--------+-----------+-----------+-----------+-----------+-----------+

49. 查询所有学生的所有课程的成绩以及平均成绩。

知识点：ROUND(x,d)函数、AVG()函数、子查询

mysql> SELECT a.stu_id '编号', stu_name '姓名',
    -> (SELECT stu_score FROM t_score WHERE cou_id = '01' AND stu_id = a.stu_id) '语文',
    -> (SELECT stu_score FROM t_score WHERE cou_id = '02' AND stu_id = a.stu_id) '数学',
    -> (SELECT stu_score FROM t_score WHERE cou_id = '03' AND stu_id = a.stu_id) '英语',
    -> (SELECT ROUND(AVG(stu_score),2) FROM t_score WHERE stu_id = a.stu_id GROUP BY stu_id) '平均分'
    -> FROM t_student a;
+--------+--------+--------+--------+--------+-----------+
| 编号   | 姓名   | 语文   | 数学   | 英语   | 平均分    |
+--------+--------+--------+--------+--------+-----------+
| 01     | 赵雷   |     80 |     90 |     99 |     89.67 |
| 02     | 钱电   |     70 |     60 |     80 |     70.00 |
| 03     | 孙风   |     80 |     80 |     80 |     80.00 |
| 04     | 李云   |     50 |     30 |     20 |     33.33 |
| 05     | 周梅   |     76 |     87 |   NULL |     81.50 |
| 06     | 吴兰   |     31 |   NULL |     34 |     32.50 |
| 07     | 郑竹   |   NULL |     89 |     98 |     93.50 |
| 08     | 王菊   |   NULL |   NULL |   NULL |      NULL |
+--------+--------+--------+--------+--------+-----------+

50. 查询所有学生的课程及分数情况。

知识点：SUM()函数、CASE WHEN 条件 THEN... ELSE... END、GROUP BY分组、外部联结（LEFT OUTER JOIN）

# 测试数据
mysql> SELECT stu_id,
    -> (CASE WHEN cou_name = '语文' THEN stu_score ELSE 0 END) 'Chinese',
    -> (CASE WHEN cou_name = '数学' THEN stu_score ELSE 0 END) 'Math',
    -> (CASE WHEN cou_name = '英语' THEN stu_score ELSE 0 END) 'English'
    -> FROM t_score a
    -> INNER JOIN t_course b ON a.cou_id = b.cou_id;
+--------+---------+------+---------+
| stu_id | Chinese | Math | English |
+--------+---------+------+---------+
| 01     |      80 |    0 |       0 |
| 01     |       0 |   90 |       0 |
| 01     |       0 |    0 |      99 |
| 02     |      70 |    0 |       0 |
| 02     |       0 |   60 |       0 |
| 02     |       0 |    0 |      80 |
| 03     |      80 |    0 |       0 |
| 03     |       0 |   80 |       0 |
| 03     |       0 |    0 |      80 |
| 04     |      50 |    0 |       0 |
| 04     |       0 |   30 |       0 |
| 04     |       0 |    0 |      20 |
| 05     |      76 |    0 |       0 |
| 05     |       0 |   87 |       0 |
| 06     |      31 |    0 |       0 |
| 06     |       0 |    0 |      34 |
| 07     |       0 |   89 |       0 |
| 07     |       0 |    0 |      98 |
+--------+---------+------+---------+

mysql> SELECT a.stu_id, stu_name,
    -> SUM(CASE WHEN cou_name = '语文' THEN stu_score ELSE 0 END) 'Chinese',
    -> SUM(CASE WHEN cou_name = '数学' THEN stu_score ELSE 0 END) 'Math',
    -> SUM(CASE WHEN cou_name = '英语' THEN stu_score ELSE 0 END) 'English',
    -> SUM(stu_score) 'score_sum'
    -> FROM t_student a
    -> LEFT OUTER JOIN t_score b ON a.stu_id = b.stu_id
    -> LEFT OUTER JOIN t_course c ON b.cou_id = c.cou_id
    -> GROUP BY a.stu_id;
+--------+----------+---------+------+---------+-----------+
| stu_id | stu_name | Chinese | Math | English | score_sum |
+--------+----------+---------+------+---------+-----------+
| 01     | 赵雷     |      80 |   90 |      99 |       269 |
| 02     | 钱电     |      70 |   60 |      80 |       210 |
| 03     | 孙风     |      80 |   80 |      80 |       240 |
| 04     | 李云     |      50 |   30 |      20 |       100 |
| 05     | 周梅     |      76 |   87 |       0 |       163 |
| 06     | 吴兰     |      31 |    0 |      34 |        65 |
| 07     | 郑竹     |       0 |   89 |      98 |       187 |
| 08     | 王菊     |       0 |    0 |       0 |      NULL |
+--------+----------+---------+------+---------+-----------+

建表：

mysql> CREATE TABLE IF NOT EXISTS t_student(
    -> stu_id VARCHAR(20),
    -> stu_name VARCHAR(20) NOT NULL DEFAULT '',
    -> stu_birth VARCHAR(20) NOT NULL DEFAULT '',
    -> stu_sex VARCHAR(10) NOT NULL DEFAULT '',
    -> PRIMARY KEY(stu_id));
mysql> INSERT t_student VALUES
    -> ('01', '赵雷', '1990-01-01', '男'),
    -> ('02', '钱电', '1990-12-21', '男'),
    -> ('03', '孙风', '1990-05-20', '男'),
    -> ('04', '李云', '1990-08-06', '男'),
    -> ('05', '周梅', '1991-12-01', '女'),
    -> ('06', '吴兰', '1992-03-01', '女'),
    -> ('07', '郑竹', '1989-07-01', '女'),
    -> ('08', '王菊', '1990-01-20', '女');

mysql> CREATE TABLE IF NOT EXISTS t_teacher(
    -> tea_id VARCHAR(20),
    -> tea_name VARCHAR(20) NOT NULL DEFAULT '',
    -> PRIMARY KEY(tea_id));
mysql> INSERT t_teacher VALUES
    -> ('01', '张三'),
    -> ('02', '李四'),
    -> ('03', '王五');

 mysql> CREATE TABLE IF NOT EXISTS t_course(
        -> cou_id VARCHAR(20),
        -> cou_name VARCHAR(20) NOT NULL DEFAULT '',
        -> tea_id VARCHAR(20) NOT NULL,
        -> PRIMARY KEY(cou_id));
mysql> INSERT t_course VALUES
    -> ('01', '语文', '02'),
    -> ('02', '数学', '01'),
    -> ('03', '英语', '03');

mysql> CREATE TABLE IF NOT EXISTS t_score(
    -> stu_id VARCHAR(20),
    -> cou_id VARCHAR(20),
    -> stu_score INT(3),
    -> PRIMARY KEY(stu_id, cou_id))
    -> ;
mysql> INSERT t_score VALUES
    -> ('01', '01', 80),
    -> ('01', '02', 90),
    -> ('01', '03', 99),
    -> ('02', '01', 70),
    -> ('02', '02', 60),
    -> ('02', '03', 80),
    -> ('03', '01', 80),
    -> ('03', '02', 80),
    -> ('03', '03', 80),
    -> ('04', '01', 50),
    -> ('04', '02', 30),
    -> ('04', '03', 20),
    -> ('05', '01', 76),
    -> ('05', '02', 87),
    -> ('06', '01', 31),
    -> ('06', '03', 34),
    -> ('07', '02', 89),
    -> ('07', '03', 98);

参考链接：https://blog.youkuaiyun.com/fashion2014/article/details/78826299/