[Mysql] 经典 50 题

数分小本本

已于 2022-07-27 11:41:48 修改

阅读量7.5k

点赞数 6

文章标签： mysql 数据库 sql

于 2022-07-22 17:21:15 首次发布

本文链接：https://blog.youkuaiyun.com/licn188/article/details/125936547

版权

50道MySql练习题（本文档只有45道）流传自远古，相当经典。这套练习在多样性和难度上平衡的比较好，换句话说，基础sql查询练习有这套就够了。
这套练习在互联网上存在时间悠久，有很多版本，本文档力图在可读性，规范性，可操作性上比这些版本做的更好
本文档用Pandas实现查询操作，这么做的原因是方便写在vscode的jupyter notebook里，更方便导出为Markdown文件。原juypter文件附在最后
导入必需库,连接数据库

import pandas as pd
import pymysql

eng = pymysql.connect(host='localhost', user='root', password='root', db='50题_1', charset='utf8')

新建练习数据库,插入样表,此部分无法在 Pandas 里操作

    CREATE DATABASE 50题;
    USE 50题;
    CREATE TABLE student (s_id VARCHAR(10),s_name VARCHAR(10),s_birthday datetime,s_sex 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('09' , '张三' , '2017-12-20' , '女');
    INSERT INTO student VALUES('10' , '李四' , '2017-12-25' , '女');
    INSERT INTO student VALUES('11' , '李四' , '2017-12-30' , '女');
    INSERT INTO student VALUES('12' , '赵六' , '2017-01-01' , '女');
    INSERT INTO student VALUES('13' , '孙七' , '2018-01-01' , '女');

    CREATE TABLE course(c_id VARCHAR(10),c_name nVARCHAR(10),t_id VARCHAR(10));
    INSERT INTO course VALUES('01' , '语文' , '02');
    INSERT INTO course VALUES('02' , '数学' , '01');
    INSERT INTO course VALUES('03' , '英语' , '03');

    CREATE TABLE teacher(t_id VARCHAR(10),t_name VARCHAR(10));
    INSERT INTO teacher VALUES('01' , '张三');
    INSERT INTO teacher VALUES('02' , '李四');
    INSERT INTO teacher VALUES('03' , '王五');

    CREATE TABLE score(s_id VARCHAR(10),c_id VARCHAR(10),score decimal(18,1));
    INSERT INTO score VALUES('01' , '01' , 80);
    INSERT INTO score VALUES('01' , '02' , 90);
    INSERT INTO score VALUES('01' , '03' , 99);
    INSERT INTO score VALUES('02' , '01' , 70);
    INSERT INTO score VALUES('02' , '02' , 60);
    INSERT INTO score VALUES('02' , '03' , 80);
    INSERT INTO score VALUES('03' , '01' , 80);
    INSERT INTO score VALUES('03' , '02' , 80);
    INSERT INTO score VALUES('03' , '03' , 80);
    INSERT INTO score VALUES('04' , '01' , 50);
    INSERT INTO score VALUES('04' , '02' , 30);
    INSERT INTO score VALUES('04' , '03' , 20);
    INSERT INTO score VALUES('05' , '01' , 76);
    INSERT INTO score VALUES('05' , '02' , 87);
    INSERT INTO score VALUES('06' , '01' , 31);
    INSERT INTO score VALUES('06' , '03' , 34);
    INSERT INTO score VALUES('07' , '02' , 89);
    INSERT INTO score VALUES('07' , '03' , 98);

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

同一个表同一个字段值的比较,把一个表变为两个表,才能比较
1.1 查询同时存在 “01” 课程和 “02” 课程的情况

        SELECT
            t1.*,
            t2.score,
            t3.score 
        FROM student t1,score t2,score t3 
        WHERE t2.c_id='01' 
            AND t3.c_id='02' 
            AND t2.score>t3.score 
            AND t1.s_id=t2.s_id 
            AND t1.s_id=t3.s_id

sql = 'SELECT t1.*,t2.score,t3.score FROM student t1,score t2,score t3 WHERE t2.c_id="01" AND t3.c_id="02" AND t2.score>t3.score AND t1.s_id=t2.s_id AND t1.s_id=t3.s_id'

df = pd.read_sql(sql, eng)
df

	s_id	s_name	s_birthday	s_sex	score	score
0	02	钱电	1990-12-21	男	70.0	60.0
1	04	李云	1990-08-06	男	50.0	30.0

1.2 查询存在 01 课程但可能不存在 02 课程的情况 (不存在时显示为 NULL)

        SELECT 
            * 
        FROM student t1 
        LEFT JOIN score t2 ON t1.s_id=t2.s_id AND t2.c_id='01'
        LEFT JOIN score t3 ON t1.s_id=t3.s_id AND t3.c_id='02'
            AND t2.score>t3.score 
            AND t2.s_id IS NOT NULL;

	s_id	s_name	s_birthday	s_sex	s_id	c_id	score	s_id	c_id	score
0	01	赵雷	1990-01-01	男	01	01	80.0	None	None	NaN
1	02	钱电	1990-12-21	男	02	01	70.0	02	02	60.0
2	03	孙风	1990-05-20	男	03	01	80.0	None	None	NaN
3	04	李云	1990-08-06	男	04	01	50.0	04	02	30.0
4	05	周梅	1991-12-01	女	05	01	76.0	None	None	NaN
5	06	吴兰	1992-03-01	女	06	01	31.0	None	None	NaN
6	07	郑竹	1989-07-01	女	None	None	NaN	None	None	NaN
7	09	张三	2017-12-20	女	None	None	NaN	None	None	NaN
8	10	李四	2017-12-25	女	None	None	NaN	None	None	NaN
9	11	李四	2017-12-30	女	None	None	NaN	None	None	NaN
10	12	赵六	2017-01-01	女	None	None	NaN	None	None	NaN
11	13	孙七	2018-01-01	女	None	None	NaN	None	None	NaN

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

        SELECT 
            t1.s_id,
            t1.s_name,
            AVG(t2.score) 
        FROM student t1,score t2 
        WHERE t1.s_id=t2.s_id 
        GROUP BY t1.s_id
        HAVING AVG(t2.score)>60 
        ORDER BY AVG(t2.score) DESC;

	s_id	s_name	AVG(t2.score)
0	07	郑竹	93.50000
1	01	赵雷	89.66667
2	05	周梅	81.50000
3	03	孙风	80.00000
4	02	钱电	70.00000

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

        SELECT 
            DISTINCT t1.* 
        FROM student t1,score t2 
        WHERE t1.s_id=t2.s_id 
        ORDER BY t1.s_id;

	s_id	s_name	s_birthday	s_sex
0	01	赵雷	1990-01-01	男
1	02	钱电	1990-12-21	男
2	03	孙风	1990-05-20	男
3	04	李云	1990-08-06	男
4	05	周梅	1991-12-01	女
5	06	吴兰	1992-03-01	女
6	07	郑竹	1989-07-01	女

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 (没成绩的显示为 `NULL`)

4.1查询有成绩的SQL

        SELECT 
            t1.*,
            COUNT(t2.score),
            SUM(t2.score) 
        FROM student t1,score t2 
        WHERE t1.s_id=t2.s_id 
        GROUP BY t1.s_id;

	s_id	s_name	s_birthday	s_sex	COUNT(t2.score)	SUM(t2.score)
0	01	赵雷	1990-01-01	男	3	269.0
1	02	钱电	1990-12-21	男	3	210.0
2	03	孙风	1990-05-20	男	3	240.0
3	04	李云	1990-08-06	男	3	100.0
4	05	周梅	1991-12-01	女	2	163.0
5	06	吴兰	1992-03-01	女	2	65.0
6	07	郑竹	1989-07-01	女	2	187.0

4.2 查询所有(包括有成绩和无成绩)的SQL

    SELECT 
        t1.*,
        COUNT(t2.score),
        SUM(t2.score) 
    FROM student t1 
    LEFT JOIN score t2 ON t1.s_id=t2.s_id 
    GROUP BY t1.s_id;

表格较长，只截取部分

	s_id	s_name	s_birthday	s_sex	COUNT(t2.score)	SUM(t2.score)
0	01	赵雷	1990-01-01	男	3	269.0
1	02	钱电	1990-12-21	男	3	210.0
2	03	孙风	1990-05-20	男	3	240.0
3	04	李云	1990-08-06	男	3	100.0
4	05	周梅	1991-12-01	女	2	163.0
5	06	吴兰	1992-03-01	女	2	65.0
6	07	郑竹	1989-07-01	女	2	187.0
7	09	张三	2017-12-20	女	0	NaN
8	10	李四	2017-12-25	女	0	NaN

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

    SELECT 
        COUNT(*) 
    FROM teacher t1.t_name 
    WHEREt1 like '李%';

	COUNT(*)
0	1

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

        SELECT 
            t1.* 
        FROM student t1, teacher t2,course t3,score t4 
        WHERE t1.s_id=t4.s_id
            AND t4.c_id=t3.c_id
            AND t3.t_id=t2.t_id
            AND t2.t_name='张三';

	s_id	s_name	s_birthday	s_sex
0	01	赵雷	1990-01-01	男
1	02	钱电	1990-12-21	男
2	03	孙风	1990-05-20	男
3	04	李云	1990-08-06	男
4	05	周梅	1991-12-01	女
5	07	郑竹	1989-07-01	女

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

        SELECT 
            * 
        FROM student
        WHERE s_id NOT IN 
            (
                SELECT 
                    s_id 
                FROM score 
                GROUP BY s_id
                HAVING COUNT(sc.c_id)=
                    (
                        SELECT 
                            COUNT(c_id) 
                        FROM course
                    )
            );

	s_id	s_name	s_birthday	s_sex
0	05	周梅	1991-12-01	女
1	06	吴兰	1992-03-01	女
2	07	郑竹	1989-07-01	女
3	09	张三	2017-12-20	女
4	10	李四	2017-12-25	女
5	11	李四	2017-12-30	女
6	12	赵六	2017-01-01	女
7	13	孙七	2018-01-01	女

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

解法一

        SELECT 
            * 
        FROM student
        WHEREs_id IN
            (
                SELECT 
                    t2.s_id 
                FROM score t2
                WHERE t2.c_id IN
                    (
                        SELECT 
                            t2.c_id 
                        FROM score t2
                        WHERE t2.s_id = '01'
                    )
            )

解法2

        SELECT 
            DISTINCT t1.* 
        FROM student t1, score t2
        WHERE t1.s_id = t2.s_id
            AND t2.c_id IN
                (
                    SELECT 
                        t2.c_id 
                    FROM score t2
                    WHERE t2.s_id = '01'
                )
            AND t1.s_id <> '01';

|   | s_id | s_name | s_birthday      | s_sex |
| - | --- | ------ | ---------- | ----- |
| 0 | 01  | 赵雷 | 1990-01-01 | 男   |
| 1 | 02  | 钱电 | 1990-12-21 | 男   |
| 2 | 03  | 孙风 | 1990-05-20 | 男   |
| 3 | 04  | 李云 | 1990-08-06 | 男   |
| 4 | 05  | 周梅 | 1991-12-01 | 女   |
| 5 | 06  | 吴兰 | 1992-03-01 | 女   |
| 6 | 07  | 郑竹 | 1989-07-01 | 女   |

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

* 解法1
```sql
        SELECT 
            DISTINCT t1.* 
        FROM student t1,score t2
        WHERE t1.s_id = t2.s_id
        AND t1.s_id <> '01'
        AND t2.c_id IN 
            (
                SELECT 
                    DISTINCT t3.c_id 
                FROM score t3
                WHERE t3.s_id = '01'
            )

解法二

        SELECT 
            student.* 
        FROM student 
        WHERE s_id IN 
            (
                SELECT 
                    DISTINCT sc.s_id 
                FROM sc
                WHERE s_id <> '01'
                    AND sc.c_id IN 
                        (
                            SELECT 
                                c_id 
                            FROM sc
                            WHEREs_id = '01'
                        )
            );

	s_id	s_name	s_birthday	s_sex
0	02	钱电	1990-12-21	男
1	03	孙风	1990-05-20	男
2	04	李云	1990-08-06	男
3	05	周梅	1991-12-01	女
4	06	吴兰	1992-03-01	女
5	07	郑竹	1989-07-01	女

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

解法一

        SELECT 
            DISTINCT t1.s_name 
        FROM student t1
        WHERE t1.s_id NOT IN
            (
                SELECT 
                    t2.s_id 
                FROM score t2, course t3
                WHERE t2.c_id = t3.c_id
                    AND t3.t_id IN
                        (
                            SELECT 
                                t4.t_id 
                            FROM teacher t4
                            WHEREt_name = '张三'
                        )
            );

解法二

        SELECT 
            DISTINCT t1.s_name 
        FROM student t1
        WHERE t1.s_id NOT IN
            (
                SELECT 
                    t2.s_id 
                FROM score t2, course t3, teacher t4
                WHEREt2.c_id = t3.c_id
                    AND t3.t_id = t4.t_id
                    AND t4.t_name = '张三'
            );

	s_name
0	吴兰
1	张三
2	李四
3	赵六
4	孙七

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

        SELECT 
            t1.s_id,
            t1.s_name,
            AVG(t2.score) 
        FROM student t1,score t2
        WHERE t1.s_id = t2.s_id
            AND t2.score < 60
        GROUP BY t2.s_id
        HAVING COUNT(*) >=2;

s_id	s_name	AVG(t2.score)
0	04	李云
1	06	吴兰

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

        SELECT 
            t1.*,
            t2.score 
        FROM student t1,score t2
        WHERE t1.s_id = t2.s_id 
            AND t2.c_id = '01'
            AND score <60
        ORDER BY t2.score DESC;

	s_id	s_name	s_birthday	s_sex	score
0	04	李云	1990-08-06	男	50.0
1	06	吴兰	1992-03-01	女	31.0

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

        SELECT 
            t1.*,
            t3.avgscore 
        FROM score t1 
        LEFT JOIN 
            (
                SELECT 
                    t2.s_id,
                    AVG(t2.score) AS avgscore 
                FROM score t2 
                GROUP BY t2.s_id
            ) 
        AS t3 ON t1.s_id = t3.s_id       
        ORDER BY t3.avgscore DESC;

表格较长，只截取部分

s_id	c_id	score	avgscore
0	07	02	89.0
1	07	03	98.0
2	01	01	80.0
3	01	02	90.0
4	01	03	99.0
5	05	01	76.0

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

#以如下形式显示：课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
#及格为 >=60,中等为：70-80,优良为：80-90,优秀为：>=90
#要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

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

	课程ID	最高分	最低分	平均分	选修人数	及格率	中等率	优良率	优秀率
0	01	80.0	31.0	64.50000	6	0.6667	0.3333	0.3333	0.0000
1	02	90.0	30.0	72.66667	6	0.8333	0.0000	0.5000	0.0000
2	03	99.0	20.0	68.50000	6	0.6667	0.0000	0.3333	0.3333

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

解法一

      SELECT 
        t1.*,
        COUNT(t2.score)+1 AS 排名
      FROM score t1
      LEFT JOIN score t2 ON t1.score<t2.score 
        AND t1.c_id = t2.c_id
      GROUP BY t1.s_id, t1.c_id, t1.score
      ORDER BY t1.c_id, 排名 ASC;

解法二

        SELECT 
            t1.c_id, 
            CASE 
                WHEN @fontscore = score THEN @currank
                WHEN @fontscore := score THEN @currank:=@currank+1
                END 
            AS 排名, 
            t1.score 
        FROM 
        (
            SELECT 
                @currank:=0, 
                @fontage:=NULL
        )
        AS t2, score t1
        ORDER BY t1.score DESC;

	c_id	排名	score
0	03	1	99.0
1	03	2	98.0
2	02	3	90.0
3	02	4	89.0
4	02	5	87.0

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

        SET @crank = 0;

        SELECT 
            t1.s_id, 
            total, 
            @crank := @crank + 1 AS 排名 
        FROM 
            (
                SELECT 
                    t2.s_id, 
                    SUM(t2.score) AS total 
                FROM score t2
                GROUP BY t2.s_id
                ORDER BY total DESC
            ) AS t1;

s_id	total	排名
01	269.0	1
03	240.0	2
02	210.0	3
07	187.0	4
05	163.0	5
04	100.0	6
06	65.0	7

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

        SELECT 
            t1.c_id,
            t1.c_name,
            SUM(CASE WHEN t2.score <=100 AND t2.score > 85 THEN 1 ELSE 0 END) AS "100-85",
            SUM(CASE WHEN t2.score <=85 AND t2.score > 70 THEN 1 ELSE 0 END) AS "85-70",
            SUM(CASE WHEN t2.score <=70 AND t2.score > 60 THEN 1 ELSE 0 END) AS "70-60",
            SUM(CASE WHEN t2.score <=60 AND t2.score > 0 THEN 1 ELSE 0 END) AS "60-0" 
        FROM course t1 LEFT JOIN  score t2  ON t1.c_id = t2.c_id
        GROUP BY t1.c_id;

	c_id	c_name	100-85	85-70	70-60	60-0
0	01	语文	0.0	3.0	1.0	2.0
1	02	数学	3.0	1.0	0.0	2.0
2	03	英语	2.0	2.0	0.0	2.0

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

        SELECT 
            * 
        FROM score t1
        WHERE
            (
                SELECT 
                    COUNT(*)
                FROM score t2
                WHERE t1.c_id = t2.c_id
                    AND t1.score < t2.score
            )<3
        ORDER BY c_id ASC, t1.score DESC;

解法二

        SELECT 
            t1.* 
        FROM score t1
        LEFT JOIN score t2 ON t1.c_id = t2.c_id
            AND t1.score < t2.score
        GROUP BY t1.c_id, t1.s_id
        HAVING COUNT(t2.c_id) < 3
        ORDER BY t1.c_id;

	s_id	c_id	score
0	01	01	80.0
1	03	01	80.0
2	05	01	76.0
3	01	02	90.0
4	07	02	90.0
5	05	02	87.0
6	01	03	99.0
7	07	03	98.0
8	02	03	80.0
9	03	03	80.0

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

        SELECT 
            c_id, 
        COUNT(s_id) 
        FROM score GROUP BY c_id 
        ORDER BY c_id ASC;

	c_id	COUNT(s_id)
0	01	6
1	02	6
2	03	6

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

解法一: 嵌套查询

        SELECT 
            t1.s_id,
            t1.s_name 
        FROM student t1
        WHERE t1.s_id IN 
            (
                SELECT 
                    t2.s_id 
                FROM score t2
                GROUP BY t2.s_id
                HAVING COUNT(t2.c_id) = 2
            );

解法二:多表关联查询

        SELECT 
            t1.s_id,
            t1.s_name 
        FROM student t1,score t2
        WHERE t1.s_id = t2.s_id
        GROUP BY t2.s_id
        HAVING COUNT(t2.c_id) = 2;

	s_id	s_name
0	05	周梅
1	06	吴兰
2	07	郑竹

21. 查询男生、女生人数

解法一

        SELECT 
            s_sex, 
            COUNT(*) 
        FROM student 
        GROUP BY s_sex;

解法二

        SELECT
            SUM(CASE WHEN s_sex = '男' THEN 1 ELSE 0 END ) AS 男,
            SUM(CASE WHEN s_sex = '女' THEN 1 ELSE 0 END ) AS 女
        FROM student;

	s_sex	COUNT(*)
0	男	4
1	女	8

	男	女
0	4.0	8.0

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

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

	s_id	s_name	s_birthday	s_sex
0	03	孙风	1990-05-20	男

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

解法一

        SELECT 
            *, 
            COUNT(*) 
        FROM student 
        GROUP BY s_name 
        HAVING COUNT(*) > 1;

解法二

        SELECT 
            *,
            COUNT(*) 
        FROM student 
        WHEREs_name IN 
            (
                SELECT 
                    s_name 
                FROM student
                GROUP BY s_name
                HAVING COUNT(*) > 1
            );

	s_id	s_name	s_birthday	s_sex	COUNT(*)
0	10	李四	2017-12-25	女	2

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

        SELECT 
            * 
        FROM student 
        WHEREYEAR(s_birthday) = 1990;

	s_id	s_name	s_birthday	s_sex
0	01	赵雷	1990-01-01	男
1	02	钱电	1990-12-21	男
2	03	孙风	1990-05-20	男
3	04	李云	1990-08-06	男

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

解法一

        SELECT 
            t1.s_id, 
            t1.s_name, 
            AVG(t2.score) AS 平均成绩 
        FROM student t1, score t2
        WHERE t1.s_id = t2.s_id
        GROUP BY t2.s_id
        HAVING AVG(t2.score) >= 85;

解法二

        SELECT 
            t1.s_id, 
            t1.s_name,
            AVG(t2.score) AS 平均成绩 
        FROM student t1
        LEFT JOIN score t2 ON t1.s_id = t2.s_id
        GROUP BY t2.s_id
        HAVING AVG(t2.score) >= 85;

	s_id	s_name	平均成绩
0	01	赵雷	89.66667
1	07	郑竹	94.00000

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

    SELECT 
        t1.sid AS 学号,
        t1.s_name AS 姓名,   
        AVG(t2.score) AS 平均成绩
    FROM student t1
    LEFT JOIN sc t2 ON t1.sid = t2.sid
    GROUP BY t1.sid
    HAVING 平均成绩 >= 85;

sql = 'SELECT t1.s_id, t1.s_name, AVG(t2.score) AS 平均成绩 FROM student t1, score t2 WHERE t1.s_id = t2.s_id GROUP BY t2.s_id HAVING AVG(t2.score) >= 85'
df = pd.read_sql(sql, eng)
df

	s_id	s_name	平均成绩
0	01	赵雷	89.66667
1	07	郑竹	93.50000

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

解法一:

        SELECT 
            t1.s_name AS 学生姓名, 
            t3.c_name AS 课程,
            t2.score AS 分数
        FROM student t1
        LEFT JOIN score t2 ON t1.s_id = t2.s_id
        LEFT JOIN course t3 ON t2.c_id = t3.c_id
        WHEREt3.c_name = '数学'
            AND t2.score < 60;

解法二:

        SELECT 
            t1.s_name AS 学生姓名,
            t3.c_name AS 课程,
            t2.score AS 分数
        FROM student t1, score t2, course t3
        WHERE t1.s_id = t2.s_id
            AND t2.c_id = t3.c_id
            AND t3.c_name = '数学'
            AND t2.score < 60;

	学生姓名	课程	分数
0	李云	数学	30.0

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

        SELECT
            t1.s_id AS 学号, 
            t1.s_name AS 姓名,
            t3.c_name AS 课程,
            t2.c_id AS 课程编号,
            t2.score AS 分数
        FROM student t1    
        LEFT JOIN score t2 ON t1.s_id = t2.s_id
        LEFT JOIN course t3 ON t2.c_id = t3.c_id
        ORDER BY t1.s_id, t2.c_id;

表格较长，只截取部分

	学号	姓名	课程	课程编号	分数
0	01	赵雷	语文	01	80.0
1	01	赵雷	数学	02	90.0
2	01	赵雷	英语	03	99.0

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

解法一

        SELECT  
            t1.s_name AS 姓名,
            t3.c_name AS 课程,
            t2.score AS 分数
        FROM student t1
        LEFT JOIN score t2 ON t1.s_id = t2.s_id
        LEFT JOIN course t3 ON t2.c_id=t3.c_id
        WHEREt2.score > 70
        ORDER BY 姓名;

解法二

        SELECT
            t1.s_name AS 姓名,
            t3.c_name AS 课程,
            t2.score AS 分数
        FROM student t1, score t2, course t3
        WHEREt2.score > 70
            AND t2.c_id = t3.c_id
            AND t1.s_id = t2.s_id
        ORDER BY 姓名;

表格较长，只截取部分

	姓名	课程	分数
0	周梅	数学	87.0
1	周梅	语文	76.0
2	孙风	英语	80.0
3	孙风	数学	80.0

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

        SELECT 
            t1.c_id AS 课程编号,
            t1.c_name AS 课程名
        FROM course t1
        LEFT JOIN score t2 ON t1.c_id = t2.c_id
        WHEREt2.score < 60
        GROUP BY 课程名
        ORDER BY 课程编号;
        #这里也可以不用 GROUP BY,而在 SELECT 后加 DITINCT 来取唯一

	课程编号	课程名
0	01	语文
1	02	数学
2	03	英语

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

        SELECT
            t1.s_id AS 学号,
            t1.s_name AS 姓名,
            t2.c_id AS 课程编号,
            t2.score AS 成绩
        FROM student t1
        LEFT JOIN score t2 ON t1.s_id = t2.s_id
        WHEREt2.c_id = '01'
        AND t2.score >= 80;

	学号	姓名	课程编号	成绩
0	01	赵雷	01	80.0
1	03	孙风	01	80.0

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

        SELECT
            t1.c_id AS 课程编号,
            t1.c_name AS 课程,
            COUNT(t2.s_id) AS 总人数
        FROM course t1
        LEFT JOIN score t2 ON t1.c_id = t2.c_id
        GROUP BY t2.c_id;

	课程编号	课程	总人数
0	01	语文	6
1	02	数学	6
2	03	英语	6

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

解法一:

        SELECT 
            t1.*,
            t4.t_name AS 教师,
            t3.c_name AS 课程,
            MAX(T2.score) AS 分数
        FROM student t1
        LEFT JOIN score t2 ON t1.s_id = t2.s_id
        LEFT JOIN course t3 ON t2.c_id = t3.c_id
        LEFT JOIN teacher t4 ON t4.t_id = t3.t_id
        WHEREt4.t_name = '张三';

解法二:

        SELECT
            t1.*,
            t2.score,
            t2.c_id
        FROM student t1, score t2, course t3, teacher t4
        WHEREt4.t_id = t3.t_id
            AND t3.c_id = t2.c_id
            AND t2.s_id = t1.s_id
            AND t4.t_name = '张三'
        HAVING MAX(t2.score);
#‘HAVING MAX(t2.score)’ 也可以写成 ‘ORDER BY t2.score DEscore LIMIT 1’

	s_id	s_name	s_birthday	s_sex	教师	课程	分数
0	01	赵雷	1990-01-01	男	张三	数学	90.0

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

为了验证，让数学有两个最高分

        UPDATE score SET score = 90 WHEREc_id = '02' AND s_id = '07';

        SELECT 
            t1.*,
            t4.t_name AS 教师,
            t3.c_name AS 课程,
            T2.score AS 分数
        FROM student t1
        LEFT JOIN score t2 ON t1.s_id = t2.s_id
        LEFT JOIN course t3 ON t2.c_id = t3.c_id
        LEFT JOIN teacher t4 ON t4.t_id = t3.t_id
        WHEREt4.t_name = '张三'
        AND t2.score = 
            (
                SELECT 
                    MAX(t5.score) 
                FROM score t5
                LEFT JOIN course t6 ON t5.c_id = t6.c_id
                LEFT JOIN teacher t7 ON t6.t_id = t7.t_id
                WHERE t7.t_name = '张三'
            );

	s_id	s_name	s_birthday	s_sex	教师	课程	分数
0	01	赵雷	1990-01-01	男	张三	数学	90.0
1	07	郑竹	1989-07-01	女	张三	数学	90.0

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

先改个数方便验证：

        UPDATE score SET score = 30 WHEREs_id = '04' AND c_id = '03';

        SELECT 
            t1.s_id AS 学生编号,
            t1.c_id AS 课程编号,
            t1.score AS 学生成绩
        FROM score t1
        INNER JOIN score t2 ON t1.s_id = t2.s_id
        WHEREt1.c_id != t2.c_id
            AND t1.score = t2.score
        GROUP BY t1.s_id;

	学生编号	课程编号	学生成绩
0	03	03	80.0

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

        SELECT
            t1.c_id AS 课程编号,
            t2.c_name AS 课程名,
            t1.s_id AS 学号,
            t3.s_name AS 姓名,
            t1.score AS 成绩
        FROM score t1
        LEFT JOIN course t2 ON t1.c_id = t2.c_id
        LEFT JOIN student t3 ON t3.s_id = t1.s_id
        WHERE
            (
                SELECT 
                    COUNT(*) 
                FROM score t4 
                WHEREt1.c_id = t4.c_id
                    AND t4.score > t1.score
            )<2
        ORDER BY t1.c_id;

	课程编号	课程名	学号	姓名	成绩
0	01	语文	01	赵雷	80.0
1	01	语文	03	孙风	80.0
2	02	数学	01	赵雷	90.0
3	02	数学	07	郑竹	89.0
4	03	英语	01	赵雷	99.0
5	03	英语	07	郑竹	98.0

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

        SELECT
            t1.c_id AS 课程号,
            t2.c_name AS 课程名,
            COUNT(t1.s_id) AS 选修人数
        FROM score t1
        LEFT JOIN course t2 ON t1.c_id = t2.c_id
        GROUP BY t1.c_id
        HAVING COUNT(t1.s_id) > 4;

	课程号	课程名	选修人数
0	01	语文	6
1	02	数学	6
2	03	英语	6

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

        SELECT
            t1.s_id AS 学号,
            COUNT(t1.c_id) AS 选修数
        FROM score t1
        GROUP BY t1.s_id
        HAVING 选修数 >= 2;

	学号	选修数
0	01	3
1	02	3
2	03	3
3	04	3
4	05	2
5	06	2
6	07	2

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

        SELECT
            t1.s_id AS 学号,
            t1.s_name AS 姓名,
            t1.s_birthday AS 出生年月,
            t1.s_sex AS 性别
        FROM student t1, score t2
        WHEREt1.s_id = t2.s_id
        GROUP BY t2.s_id
        HAVING COUNT(t2.c_id) >= 3;

	学号	姓名	出生年月	性别
0	01	赵雷	1990-01-01	男
1	02	钱电	1990-12-21	男
2	03	孙风	1990-05-20	男
3	04	李云	1990-08-06	男

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

        SELECT
        t1.s_id AS 学号,
        t1.s_name AS 姓名,
        TIMESTAMPDIFF(YEAR,t1.s_birthday,CURDATE()) AS 年龄
        FROM student t1;
        #TIMESTAMPDIFF函数规范：https://blog.youkuaiyun.com/zmxiangde_88/article/details/8011661

表格较长，只截取部分

	学号	姓名	年龄
0	01	赵雷	32
1	02	钱电	31
2	03	孙风	32

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

        SELECT 
            s_id,
            s_name,
            (DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birthday,'%Y') - 
                (
                    CASE when 
                        DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birthday,'%m%d') then 0 else 1 END)
                ) AS age
        FROM student;

表格较长，只截取部分

	s_id	s_name	age
0	01	赵雷	32.0
10	12	赵六	5.0
11	13	孙七	4.0

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

        SELECT 
            *
        FROM student
        WHEREWEEKOFYEAR(student.s_birthday) = WEEKOFYEAR(CURDATE());

s_id	s_name	s_birthday	s_sex

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

        SELECT 
                *
        FROM student
        WHEREWEEKOFYEAR(student.s_birthday) = WEEKOFYEAR(CURDATE())+1;

s_id	s_name	s_birthday	s_sex

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

        SELECT 
                *
        FROM student t1
        WHEREMONTH(t1.s_birthday) = MONTH(CURDATE());

	s_id	s_name	s_birthday	s_sex
0	07	郑竹	1989-07-01	女

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

        SELECT 
                *
        FROM student t1
        WHEREMONTH(t1.s_birthday) = MONTH(CURDATE())+1;

sql = 'SELECT * FROM student t1 WHEREMONTH(t1.s_birthday) = MONTH(CURDATE())+1; '
df = pd.read_sql(sql, eng)
df

	s_id	s_name	s_birthday	s_sex
0	04	李云	1990-08-06	男

[Mysql] 经典 50 题

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

21. 查询男生、女生人数

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 (没成绩的显示为 `NULL`)

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

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

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

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

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

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