MySQL 50 题。

文章目录

- MySQL 50 题。
- - 数据库。
  - sql。

数据库。

CREATE SCHEMA `new_schema` DEFAULT CHARACTER SET utf8mb4 ;


Operation failed: There was an error while applying the SQL script to the database.
Executing:
CREATE SCHEMA `new_schema` DEFAULT CHARACTER SET utf8mb4 ;

ERROR 1044: Access denied for user 'lyfgeek'@'%' to database 'new_schema'
SQL Statement:
CREATE SCHEMA `new_schema` DEFAULT CHARACTER SET utf8mb4



1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE SCHEMA `mysql_fifty` DEFAULT CHARACTER SET utf8 ;

1
2

CREATE TABLE `lyfgeek`.`student`
(
    `id`             BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键 id。',
    `s_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '学生 id。',
    `s_name`         VARCHAR(45) NOT NULL DEFAULT '' COMMENT '姓名。',
    `s_birth`        VARCHAR(45) NOT NULL DEFAULT '' COMMENT '出生年月。',
    `s_sex`          VARCHAR(45) NOT NULL DEFAULT '' COMMENT '性别。',
    `db_insert_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。',
    `db_update_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。',
    `db_deleted`     INT         NOT NULL DEFAULT 0 COMMENT 'db_deleted。',
    PRIMARY KEY (`id`, `s_id`),
    UNIQUE INDEX `s_id_UNIQUE` (`s_id` ASC) VISIBLE
)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COMMENT = '学生。';


-- 课程表。
CREATE TABLE `lyfgeek`.`course`
(
    `id`             BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键 id。',
    `c_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '课程 id。',
    `c_name`         VARCHAR(45) NOT NULL DEFAULT '' COMMENT '课程名称。',
    `t_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '教师 id。',
    `db_insert_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。',
    `db_update_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。',
    `db_deleted`     INT         NOT NULL DEFAULT 0 COMMENT 'db_deleted。',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `c_id_UNIQUE` (`c_id` ASC) VISIBLE
)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COMMENT = '成绩。';


-- 教师表。
CREATE TABLE `lyfgeek`.`teacher`
(
    `id`             BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键 id。',
    `t_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '教师 id。',
    `t_name`         VARCHAR(45) NOT NULL DEFAULT '' COMMENT '姓名。',
    `db_insert_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。',
    `db_update_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。',
    `db_deleted`     INT         NOT NULL DEFAULT 0 COMMENT 'db_deleted。',
    PRIMARY KEY (`id`, `t_id`),
    UNIQUE INDEX `t_id_UNIQUE` (`t_id` ASC) VISIBLE
)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COMMENT = '教师。';


-- 成绩表。
CREATE TABLE `lyfgeek`.`score`
(
    `id`             BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键 id。',
    `s_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '学生 id。',
    `c_id`           VARCHAR(45) NOT NULL DEFAULT '' COMMENT '课程 id。',
    `s_score`        INT         NULL     DEFAULT NULL COMMENT '成绩。',
    `db_insert_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。',
    `db_update_time` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。',
    `db_deleted`     INT         NOT NULL DEFAULT 0 COMMENT 'db_deleted。',
    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COMMENT = '成绩。';


# 学生表测试数据。
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('01', '学生 1', '2021-01-01', '男');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('02', '学生 2', '2022-02-02', '男');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('03', '学生 3', '2023-03-03', '男');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('04', '学生 4', '2024-04-04', '男');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('05', '学生 5', '2025-05-05', '女');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('06', '学生 6', '2026-06-06', '女');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('07', '学生 7', '2027-07-07', '女');
INSERT INTO `lyfgeek`.`student` (`s_id`, `s_name`, `s_birth`, `s_sex`)
VALUES ('08', '学生 8', '2028-08-08', '女');


# 课程表测试数据。
INSERT INTO `lyfgeek`.`course`(`c_id`, `c_name`, `t_id`)
VALUES ('01', '语文', '02');
INSERT INTO `lyfgeek`.`course`(`c_id`, `c_name`, `t_id`)
VALUES ('02', '数学', '01');
INSERT INTO `lyfgeek`.`course`(`c_id`, `c_name`, `t_id`)
VALUES ('03', '英语', '03');


# 教师表测试数据。
INSERT INTO `lyfgeek`.`teacher` (`t_id`, `t_name`)
VALUES ('01', '老师 1');
INSERT INTO `lyfgeek`.`teacher` (`t_id`, `t_name`)
VALUES ('02', '老师 2');
INSERT INTO `lyfgeek`.`teacher` (`t_id`, `t_name`)
VALUES ('03', '老师 3');


# 成绩表测试数据。
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('01', '01', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('01', '02', '90');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('01', '03', '99');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('02', '01', '70');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('02', '02', '60');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('02', '03', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('03', '01', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('03', '02', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('03', '03', '80');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('04', '01', '50');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('04', '02', '30');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('04', '03', '20');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('05', '01', '76');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('05', '02', '87');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('06', '01', '31');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('06', '03', '34');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('07', '02', '89');
INSERT INTO `lyfgeek`.`score` (`s_id`, `c_id`, `s_score`)
VALUES ('07', '03', '98');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

sql。

-- 查询课程"01"比课程"02"成绩高的学生的信息及课程分数。
-- - in ~ 长型数据变成宽型数据。
SELECT st.*,
       t.s01,
       t.s02
FROM (SELECT sc.`s_id`,
             MAX(CASE
                     WHEN sc.`c_id` = '01' THEN sc.`s_score`
                 END) s01,
             MAX(CASE
                     WHEN sc.`c_id` = '02' THEN sc.`s_score`
                 END) s02
      FROM `score` sc
      GROUP BY sc.`s_id`) t,
     `student` st
WHERE t.s01 > t.s02
  AND t.`s_id` = st.`s_id`;

-- 自连接。
SELECT st.*,
       sc1.`s_score`,
       sc2.`s_score`
FROM `student` st,
     `score` sc1,
     `score` sc2
WHERE st.`s_id` = sc1.`s_id`
  AND sc1.`s_id` = sc2.`s_id`
  AND sc1.`c_id` = '01'
  AND sc2.`c_id` = '02'
  AND sc1.`s_score` > sc2.`s_score`;


-- 2、查询课程"01"比课程"02"成绩低的学生的信息及课程分数。
SELECT `student`.*,
       sc1.`s_score`,
       sc2.`s_score`
FROM `student`
         INNER JOIN
     `score` sc1 ON `student`.`s_id` = sc1.`s_id`
         AND sc1.`c_id` = '01'
         INNER JOIN
     `score` sc2 ON sc1.`s_id` = sc2.`s_id`
         AND sc2.`c_id` = '02'
WHERE sc1.`s_score` < sc2.`s_score`
   OR sc1.`s_score` IS NULL;


-- 3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩。
-- 子查询方法。
SELECT sc.`s_id`,
       (SELECT `s_name`
        FROM `student` st
        WHERE st.`s_id` = sc.`s_id`) AS s_name,
       AVG(sc.`s_score`)                avg_score
FROM `score` sc
GROUP BY sc.`s_id`
HAVING avg_score >= 60;

-- 两个表连接方法。
SELECT st.`s_id`,
       st.`s_name`,
       AVG(sc.`s_score`)
FROM `student` st,
     `score` sc
WHERE st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING AVG(sc.`s_score` >= 60);


-- 4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩。
-- （包括有成绩的和无成绩的）。
SELECT st.`s_id`,
       st.`s_name`,
       AVG(sc.`s_score`) AS avg_score
FROM `student` st,
     `score` sc
WHERE st.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
HAVING AVG(sc.`s_score`) < 60
UNION
SELECT st.`s_id`,
       st.`s_name`,
       0 AS avg_score
FROM `student` st
WHERE st.`s_id` NOT IN (SELECT DISTINCT `s_id`
                        FROM `score`);


-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。
SELECT st.`s_id`,
       st.`s_name`,
       COUNT(sc.s_id)               AS sum_course,
       IFNULL(SUM(sc.`s_score`), 0) AS sum_score
FROM `student` st
         LEFT OUTER JOIN
     `score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`;


-- 6、查询"李"姓老师的数量。
SELECT COUNT(`t_id`)
FROM `teacher`
WHERE `t_name` LIKE '李%';


-- 7、查询学过"张三"老师授课的同学的信息。
SELECT st.*
FROM `student` st
         JOIN
     `score` sc ON st.`s_id` = sc.`s_id`
WHERE sc.`c_id` IN (SELECT `c_id`
                    FROM `course`
                    WHERE `t_id` = (SELECT `t_id`
                                    FROM `teacher`
                                    WHERE `t_name` = '老师 1'));

SELECT st.*
FROM `student` st
         INNER JOIN
     `score` sc ON st.`s_id` = sc.`s_id`
WHERE sc.`c_id` IN (SELECT `c_id`
                    FROM `course` c
                             INNER JOIN
                         `teacher` t ON c.`t_id` = t.`t_id`
                    WHERE `t`.`t_name` = '老师 1');


-- 8、查询没学过"张三"老师授课的同学的信息。
SELECT *
FROM `student` st
WHERE st.s_id NOT IN (SELECT st.`s_id`
                      FROM `student` st
                               JOIN
                           `score` sc ON st.`s_id` = sc.`s_id`
                      WHERE sc.`c_id` IN (SELECT `c_id`
                                          FROM `course`
                                          WHERE `t_id` = (SELECT `t_id`
                                                          FROM `teacher`
                                                          WHERE `t_name` = '老师 1')));

SELECT *
FROM `student`
WHERE `s_id` NOT IN (SELECT sc.`s_id`
                     FROM `teacher` t,
                          `score` sc,
                          `course` c
                     WHERE t.`t_id` = c.`t_id`
                       AND c.`c_id` = sc.`c_id`
                       AND t.`t_name` = '老师 1');

SELECT *
FROM `student`
WHERE NOT EXISTS(SELECT 1
                 FROM (SELECT sc.`s_id`
                       FROM `teacher` t,
                            `score` sc,
                            `course` c
                       WHERE t.`t_id` = c.`t_id`
                         AND c.`c_id` = sc.`c_id`
                         AND t.`t_name` = '老师 1') t
                 WHERE t.s_id = student.s_id);


-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。
SELECT st.*
FROM `student` st,
     `score` sc1,
     `score` sc2
WHERE st.`s_id` = sc1.`s_id`
  AND st.`s_id` = sc2.`s_id`
  AND sc1.`c_id` = '01'
  AND sc2.`c_id` = '02';


-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。
SELECT st.*
FROM `student` st
WHERE st.`s_id` IN (SELECT `s_id`
                    FROM `score`
                    WHERE `c_id` = '01')
  AND st.`s_id` NOT IN (SELECT `s_id`
                        FROM `score`
                        WHERE `c_id` = '02');

SELECT st.*
FROM `student` st,
     (SELECT `s_id`,
             MAX(IF(`c_id` = '01', `s_score`, NULL)) s01,
             MAX(IF(`c_id` = '02', `s_score`, NULL)) s02
      FROM `score`
      GROUP BY `s_id`) t
WHERE t.`s_id` = st.`s_id`
  AND t.s01 IS NOT NULL
  AND t.s02 IS NULL;


-- 11、查询没有学全所有课程的同学的信息。
SELECT st.*,
       COUNT(sc.`c_id`) count_sc
FROM `student` st
         LEFT JOIN
     `score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING COUNT(sc.`c_id`) < (SELECT COUNT(`c_id`)
                           FROM `course`);

SELECT st.*
FROM `student` st
WHERE st.`s_id` IN (SELECT `s_id`
                    FROM `score`
                    WHERE `s_id` NOT IN (SELECT sc1.`s_id`
                                         FROM `score` sc1
                                                  JOIN
                                              `score` sc2 ON sc1.`s_id` = sc2.`s_id`
                                                  AND sc2.`c_id` = '02'
                                                  JOIN
                                              `score` sc3 ON sc1.`s_id` = sc3.`s_id`
                                                  AND sc3.`c_id` = '03'
                                         WHERE sc1.`c_id` = '01'));


-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息。
SELECT *
FROM `student`
WHERE `s_id` IN (SELECT DISTINCT `s_id`
                 FROM `score`
                 WHERE `c_id` IN (SELECT `c_id`
                                  FROM `score`
                                  WHERE `s_id` = '01'))
GROUP BY 1, 2, 3, 4;
-- 第 1 2 3 4 字段。


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

-- 创建 01 所学临时表。
CREATE TABLE s01_sc_temp AS
SELECT t1.*, sc.`c_id` cid2
FROM (SELECT st.*,
             t2.`c_id`
      FROM `student` st,
           (SELECT `c_id`
            FROM `score`
            WHERE `s_id` = '01') t2) t1
         LEFT JOIN
     `score` sc ON t1.`s_id` = sc.`s_id`
         AND t1.`c_id` = sc.`c_id`
UNION
SELECT t.*,
       sc.`c_id` cid2
FROM (SELECT st.*,
             b.`c_id`
      FROM `student` st,
           (SELECT `c_id`
            FROM `score`
            WHERE `s_id` = '01') b) t
         RIGHT JOIN
     `score` sc ON t.`s_id` = sc.`s_id`
         AND t.`c_id` = sc.`c_id`;

SELECT *
FROM `student`
WHERE `s_id` NOT IN (SELECT `s_id`
                     FROM s01_s_temp
                     WHERE cid2 IS NULL
                        OR `c_id` IS NULL)
  AND `s_id` != '01';

-- ~ ~ ~

SELECT *
FROM `student`
WHERE `s_id` IN (SELECT DISTINCT `s_id`
                 FROM `score`
                 WHERE `s_id` != '01'
                   AND `c_id` IN (SELECT `c_id`
                                  FROM `score`
                                  WHERE `s_id` = '01')
                 GROUP BY `s_id`
                 HAVING COUNT(1) = (SELECT COUNT(1)
                                    FROM `score`
                                    WHERE `s_id` = '01'));


-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名。
SELECT st.`s_name`
FROM `student` st
WHERE st.`s_id` NOT IN (SELECT `s_id`
                        FROM `score`
                        WHERE `c_id` = (SELECT `c_id`
                                        FROM `course`
                                        WHERE `t_id` = (SELECT `t_id`
                                                        FROM `teacher`
                                                        WHERE `t_name` = '老师 1'))
                        GROUP BY s_id);


-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
SELECT st.`s_id`,
       st.`s_name`,
       AVG(sc.`s_score`) avg_score
FROM `student` st
         LEFT JOIN
     `score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`s_score` >= 60, 0, 1)) >= 2;

SELECT st.`s_id`,
       st.`s_name`,
       AVG(sc.`s_score`) avg_score
FROM `student` st
         LEFT JOIN
     `score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`s_score` >= 60, 0, 1)) >= 2;

SELECT st.`s_id`,
       st.`s_name`,
       ROUND(AVG(sc.`s_score`))
FROM student st
         LEFT JOIN
     score sc ON st.`s_id` = sc.`s_id`
WHERE st.`s_id` IN (SELECT `s_id`
                    FROM `score`
                    WHERE `s_score` < 60
                    GROUP BY `s_id`
                    HAVING COUNT(1) >= 2)
GROUP BY st.`s_id`, st.`s_name`;


-- 16、检索课程"01"分数小于 60，按分数降序排列的学生信息。
SELECT st.*,
       sc.`s_score`
FROM `score` sc
         RIGHT JOIN
     `student` st ON sc.`s_id` = st.`s_id`
WHERE sc.`c_id` = '01'
  AND sc.`s_score` < 60
ORDER BY `s_score` DESC;

SELECT st.*,
       sc.`c_id`,
       sc.`s_score`
FROM `student` st,
     `score` sc
WHERE st.`s_id` = sc.`s_id`
  AND sc.`c_id` = '01'
  AND sc.`s_score` < 60
ORDER BY sc.`s_score` DESC;


-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
SELECT sc.`s_id`,
       (SELECT `s_score`
        FROM `score`
        WHERE `s_id` = sc.`s_id`
          AND `c_id` = '01')    AS 语文,
       (SELECT `s_score`
        FROM `score`
        WHERE `s_id` = sc.`s_id`
          AND `c_id` = '02')    AS 数学,
       (SELECT `s_score`
        FROM `score`
        WHERE `s_id` = sc.`s_id`
          AND `c_id` = '03')    AS 英语,
       ROUND(AVG(`s_score`), 2) AS 平均分
FROM `score` sc
GROUP BY sc.`s_id`
ORDER BY 平均分 DESC;


-- 18.查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。
-- 及格为 >= 60，中等为：70 ~ 80，优良为：80 ~ 90，优秀为：>= 90。
SELECT sc.`c_id`,
       c.`c_name`,
       MAX(`s_score`),
       MIN(`s_score`),
       ROUND(AVG(`s_score`), 2),
       ROUND(100 * (SUM(IF(sc.`s_score` >= 60, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),
             2) AS 及格率,
       ROUND(100 * (SUM(IF(sc.`s_score` >= 70 AND sc.`s_score` <= 80, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),
             2) AS 中等率,
       ROUND(100 * (SUM(IF(sc.`s_score` >= 80 AND sc.`s_score` <= 90, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),
             2) AS 优良率,
       ROUND(100 * (SUM(IF(sc.`s_score` >= 90, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),
             2) AS 优秀率
FROM `score` sc
         LEFT JOIN
     `course` c ON sc.`c_id` = c.`c_id`
GROUP BY sc.`c_id`, c.`c_name`;


-- 19、按各科成绩进行排序，并显示排名（实现不完全）。
-- mysql 没有 rank(); 函数。
SELECT sc1.`c_id`,
       sc1.`s_id`,
       sc1.`s_score`,
       COUNT(sc2.`s_score`) + 1 AS `rank`
FROM `score` sc1
         LEFT JOIN
     `score` sc2 ON sc1.`s_score` < sc2.`s_score`
         AND sc1.`c_id` = sc2.`c_id`
GROUP BY sc1.`c_id`, sc1.`s_id`, sc1.`s_score`
ORDER BY sc1.`c_id`, `rank`;


-- 20、查询学生的总成绩并进行排名。
SELECT t1.`s_id`,
       @i := @i + 1                              AS i,
       @k := (IF(@score = t1.sum_score, @k, @i)) AS `rank`,
       @score := t1.sum_score                    AS score
FROM (SELECT `s_id`,
             SUM(`s_score`) AS sum_score
      FROM `score`
      GROUP BY `s_id`
      ORDER BY sum_score DESC) t1,
     (SELECT @k := 0, @i := 0, @score := 0) AS kis;


-- 21、查询不同老师所教不同课程平均分从高到低显示。
SELECT c.`t_id`,
       t.`t_name`,
       c.`c_id`,
       ROUND(AVG(`s_score`), 2) AS avg_score
FROM `course` c
         LEFT JOIN
     `score` sc ON c.`c_id` = sc.`c_id`
         LEFT JOIN
     `teacher` t ON c.`t_id` = t.`t_id`
GROUP BY c.`c_id`, c.`t_id`, t.`t_name`
ORDER BY avg_score DESC;


-- 22、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩。
SELECT d.*,
       si2.排名,
       si2.`s_score`,
       si2.`c_id`
FROM (SELECT sc.`s_id`,
             sc.`s_score`,
             sc.`c_id`,
             @i := @i + 1 AS 排名
      FROM `score` sc,
           (SELECT @i := 0) as i2
      WHERE sc.c_id = '01') as si2
         LEFT JOIN
     student d ON si2.s_id = d.s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT st.*,
       sj2.排名,
       sj2.s_score,
       sj2.c_id
FROM (SELECT sc.s_id,
             sc.s_score,
             sc.c_id,
             @j := @j + 1 AS 排名
      FROM `score` sc,
           (SELECT @j := 0) as j2
      WHERE sc.c_id = '02') as sj2
         LEFT JOIN
     `student` st ON sj2.s_id = st.s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT d.*,
       s.排名,
       s.`s_score`,
       s.`c_id`
FROM (SELECT sc.`s_id`,
             sc.`s_score`,
             sc.`c_id`,
             @k := @k + 1 AS 排名
      FROM `score` sc,
           (SELECT @k := 0) as k2
      WHERE sc.`c_id` = '03') as s
         LEFT JOIN
     `student` d ON s.`s_id` = d.`s_id`
WHERE 排名 BETWEEN 2 AND 3;


-- 23、统计各科成绩各分数段人数：课程编号, 课程名称, [100-85], [85-70], [70-60], [0-60] 及所占百分比。
SELECT DISTINCT c.`c_name`,
                sc.`c_id`,
                t1.`85-100`,
                t1.百分比,
                t2.`70-85`,
                t2.百分比,
                t3.`60-70`,
                t3.百分比,
                t4.`0-60`,
                t4.百分比
FROM `score` sc
         LEFT JOIN
     (SELECT `c_id`,
             SUM(IF(`s_score` > 85 AND `s_score` <= 100, 1, 0))                              AS `85-100`,
             ROUND(100 * (SUM(IF(`s_score` > 85 AND `s_score` <= 100, 1, 0)) / COUNT(*)), 2) AS 百分比
      FROM `score`
      GROUP BY `c_id`) t1 ON sc.`c_id` = t1.`c_id`
         LEFT JOIN
     (SELECT `c_id`,
             SUM(IF(`s_score` > 70 AND `s_score` <= 85, 1, 0))                              AS `70-85`,
             ROUND(100 * (SUM(IF(`s_score` > 70 AND `s_score` <= 85, 1, 0)) / COUNT(*)), 2) AS 百分比
      FROM `score`
      GROUP BY `c_id`) t2 ON sc.`c_id` = t2.`c_id`
         LEFT JOIN
     (SELECT `c_id`,
             SUM(IF(`s_score` > 60 AND `s_score` <= 70, 1, 0))                              AS `60-70`,
             ROUND(100 * (SUM(IF(`s_score` > 60 AND `s_score` <= 70, 1, 0)) / COUNT(*)), 2) AS 百分比
      FROM `score`
      GROUP BY `c_id`) t3 ON sc.`c_id` = t3.`c_id`
         LEFT JOIN
     (SELECT `c_id`,
             SUM(IF(`s_score` >= 0 AND `s_score` <= 60, 1, 0))                              AS `0-60`,
             ROUND(100 * (SUM(IF(`s_score` >= 0 AND `s_score` <= 60, 1, 0)) / COUNT(*)), 2) AS 百分比
      FROM `score`
      GROUP BY `c_id`) t4 ON sc.`c_id` = t4.`c_id`
         LEFT JOIN
     `course` c ON sc.`c_id` = c.`c_id`;


-- 24、查询学生平均成绩及其名次。
SELECT t.`s_id`,
       @i := @i + 1                                 AS '不保留空缺排名',
       @k := (IF(@avg_score = t.avg_score, @k, @i)) AS '保留空缺排名',
       @avg_score := avg_score                      AS '平均分'
FROM (SELECT `s_id`,
             ROUND(AVG(`s_score`), 2) AS avg_score
      FROM score
      GROUP BY `s_id`) t,
     (SELECT @avg_score := 0, @i := 0, @k := 0) b;


-- 25、查询各科成绩前三名的记录。
-- - 选出 sc1 表比 sc2 表成绩大的所有组。
-- - 选出比当前 id 成绩大的小于三个的。
SELECT sc1.`s_id`,
       sc1.`c_id`,
       sc1.`s_score`
FROM `score` sc1
         LEFT JOIN
     `score` sc2 ON sc1.`c_id` = sc2.`c_id`
         AND sc1.`s_score` < sc2.`s_score`
GROUP BY sc1.`s_id`, sc1.`c_id`, sc1.`s_score`
HAVING COUNT(sc2.`s_id`) < 3
ORDER BY sc1.`c_id`, sc1.`s_score` DESC;


-- 26、查询每门课程被选修的学生数。
SELECT `c_id`,
       COUNT(`s_id`)
FROM `score`
GROUP BY `c_id`;


-- 27、查询出只有两门课程的全部学生的学号和姓名。
SELECT `s_id`,
       `s_name`
FROM `student`
WHERE `s_id` IN (SELECT `s_id`
                 FROM `score`
                 GROUP BY `s_id`
                 HAVING COUNT(`c_id`) = 2);


-- 28、查询男生、女生人数。
SELECT `s_sex`,
       COUNT(`s_sex`) AS 人数
FROM student
GROUP BY s_sex;


-- 29、查询名字中含有"风"字的学生信息。
SELECT *
FROM `student`
WHERE `s_name` LIKE '%风%';


-- 30、查询同名同性学生名单，并统计同名人数。
SELECT st1.`s_name`,
       st1.`s_sex`,
       COUNT(*)
FROM `student` st1
         JOIN
     `student` st2 ON st1.`s_id` != st2.`s_id`
         AND st1.`s_name` = st2.`s_name`
         AND st1.`s_sex` = st2.`s_sex`
GROUP BY st1.`s_name`, st1.`s_sex`;


-- 31、查询 1990 年出生的学生名单。
SELECT `s_name`
FROM `student`
WHERE `s_birth` LIKE '1990%';


-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。
SELECT `c_id`,
       ROUND(AVG(`s_score`), 2) AS avg_score
FROM `score`
GROUP BY `c_id`
ORDER BY avg_score DESC, `c_id` ASC;


-- 33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩。
SELECT sc.`s_id`,
       st.`s_name`,
       ROUND(AVG(sc.`s_score`), 2) AS avg_score
FROM `score` sc
         LEFT JOIN
     `student` st ON sc.`s_id` = st.`s_id`
GROUP BY `s_id`
HAVING avg_score >= 85;


-- 34、查询课程名称为"数学"，且分数低于 60 的学生姓名和分数。
SELECT st.`s_name`,
       sc.`s_score`
FROM `score` sc
         LEFT JOIN
     `student` st ON st.`s_id` = sc.`s_id`
WHERE sc.`c_id` = (SELECT `c_id`
                   FROM `course`
                   WHERE `c_name` = '数学')
  AND sc.s_score < 60;


-- 35、查询所有学生的课程及分数情况。
SELECT st.`s_id`,
       st.`s_name`,
       SUM(IF(c.`c_name` = '语文', sc.`s_score`, 0)) AS '语文',
       SUM(IF(c.`c_name` = '数学', sc.`s_score`, 0)) AS '数学',
       SUM(IF(c.`c_name` = '英语', sc.`s_score`, 0)) AS '英语',
       SUM(sc.`s_score`)                             AS '总分'
FROM `student` st
         LEFT JOIN
     `score` sc ON st.`s_id` = sc.`s_id`
         LEFT JOIN
     `course` c ON sc.`c_id` = c.`c_id`
GROUP BY st.`s_id`, st.`s_name`;


-- 36、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数。
SELECT st.`s_name`,
       c.`c_name`,
       sc.`s_score`
FROM `course` c
         LEFT JOIN
     `score` sc ON c.`c_id` = sc.`c_id`
         LEFT JOIN
     `student` st ON st.`s_id` = sc.`s_id`
WHERE sc.`s_score` >= 70;


-- 37、查询不及格的课程。
SELECT sc.`s_id`,
       sc.`c_id`,
       c.`c_name`,
       sc.`s_score`
FROM `score` sc
         LEFT JOIN
     `course` c ON sc.`c_id` = c.`c_id`
WHERE sc.`s_score` < 60;


-- 38、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名。
SELECT sc.`s_id`,
       st.`s_name`
FROM `score` sc
         LEFT JOIN
     `student` st ON sc.`s_id` = st.`s_id`
WHERE sc.`c_id` = '01'
  AND sc.`s_score` > 80;


-- 39、求每门课程的学生人数。
SELECT COUNT(*)
FROM `score`
GROUP BY `c_id`;


-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。
-- 查询老师 id。
SELECT `c_id`
FROM `course` c,
     `teacher` t
WHERE c.`t_id` = t.`t_id`
  AND t.`t_name` = '老师 1';
-- 查询最高分（可能有相同分数）。
SELECT MAX(`s_score`)
FROM `score`
WHERE `c_id` = '02';
-- 查询信息。
SELECT st.*,
       sc.`s_score`,
       sc.`c_id`,
       c.`c_name`
FROM `student` st
         LEFT JOIN
     `score` sc ON st.`s_id` = sc.`s_id`
         LEFT JOIN
     `course` c ON sc.`c_id` = c.`c_id`
WHERE sc.`c_id` = (SELECT `c_id`
                   FROM `course` c,
                        `teacher` t
                   WHERE c.`t_id` = t.`t_id`
                     AND t.`t_name` = '老师 1')
  AND sc.s_score IN (SELECT MAX(s_score)
                     FROM score
                     WHERE c_id = '02');

SELECT c.`c_name`, sc.`s_score`, st.*
FROM `course` c,
     `score` sc,
     `teacher` t,
     `student` st
WHERE t.`t_id` = c.`t_id`
  AND c.`c_id` = sc.`c_id`
  AND st.`s_id` = sc.`s_id`
  AND t.`t_name` = '老师 1'
  AND sc.`s_score` IN (SELECT MAX(`s_score`)
                       FROM `course`,
                            `score`,
                            `teacher`,
                            `student`
                       WHERE `teacher`.`t_id` = `course`.`t_id`
                         AND `course`.`c_id` = `score`.`c_id`
                         AND `student`.`s_id` = `score`.`s_id`
                         AND `teacher`.`t_name` = '老师 1');


-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。
SELECT DISTINCT sc2.`s_id`,
                sc2.`c_id`,
                sc2.`s_score`
FROM `score` sc1,
     `score` sc2
WHERE sc1.`c_id` != sc2.`c_id`
  AND sc1.`s_score` = sc2.`s_score`;


-- 42、查询每门功成绩最好的前两名。
-- 牛逼的写法。
SELECT sc1.`s_id`,
       sc1.`c_id`,
       sc1.`s_score`
FROM `score` sc1
WHERE (SELECT COUNT(1)
       FROM `score` sc2
       WHERE sc2.`c_id` = sc1.`c_id`
         AND sc2.`s_score` >= sc1.`s_score`) <= 2
ORDER BY sc1.`c_id`;


-- 43、统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
SELECT `c_id`,
       COUNT(*) AS total
FROM `score`
GROUP BY `c_id`
HAVING total > 5
ORDER BY total DESC, `c_id`;


-- 44、检索至少选修两门课程的学生学号。
SELECT `s_id`,
       COUNT(*)
FROM `score`
GROUP BY `s_id`
HAVING COUNT(*) >= 2;


-- 45、查询选修了全部课程的学生信息。
SELECT *
FROM `student`
WHERE `s_id` IN (SELECT `s_id`
                 FROM `score`
                 GROUP BY `s_id`
                 HAVING COUNT(*) = (SELECT COUNT(*)
                                    FROM `course`));


-- 46、查询各学生的年龄。
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一。
SELECT `s_birth`,
       (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`s_birth`, '%Y') -
        (IF(DATE_FORMAT(NOW(), '%m%d') > DATE_FORMAT(`s_birth`, '%m%d'),
            0,
            1))) AS age
FROM `student`;


-- 47、查询本周过生日的学生。
SELECT *
FROM `student`
WHERE WEEK(DATE_FORMAT(NOW(), '%Y%m%d')) = WEEK(`s_birth`);

SELECT *
FROM `student`
WHERE YEARWEEK(`s_birth`) = YEARWEEK(DATE_FORMAT(NOW(), '%Y%m%d'));

SELECT WEEK(DATE_FORMAT(NOW(), '%Y%m%d'));


-- 48、查询下周过生日的学生。
SELECT *
FROM `student`
WHERE WEEK(DATE_FORMAT(NOW(), '%Y%m%d')) + 1 = WEEK(`s_birth`);


-- 49、查询本月过生日的学生。
SELECT *
FROM `student`
WHERE MONTH(DATE_FORMAT(NOW(), '%Y%m%d')) = MONTH(`s_birth`);


-- 50、查询下月过生日的学生。
SELECT *
FROM `student`
WHERE MONTH(DATE_FORMAT(NOW(), '%Y-%m-%d')) + 1 = MONTH(`s_birth`);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819

-- 查询课程"01"比课程"02"成绩高的学生的信息及课程分数。
-- - in ~ 长型数据变成宽型数据。
SELECT 
    st.*, t.s01, t.s02
FROM
    (SELECT 
        sc.`s_id`,
            MAX(CASE
                WHEN sc.`c_id` = '01' THEN sc.`s_score`
            END) s01,
            MAX(CASE
                WHEN sc.`c_id` = '02' THEN sc.`s_score`
            END) s02
    FROM
        `score` sc
    GROUP BY sc.`s_id`) t,
    `student` st
WHERE
    t.s01 > t.s02 AND t.`s_id` = st.`s_id`;

-- 自连接。
SELECT 
    st.*, sc1.`s_score`, sc2.`s_score`
FROM
    `student` st,
    `score` sc1,
    `score` sc2
WHERE
    st.`s_id` = sc1.`s_id`
        AND sc1.`s_id` = sc2.`s_id`
        AND sc1.`c_id` = '01'
        AND sc2.`c_id` = '02'
        AND sc1.`s_score` > sc2.`s_score`;


-- 2、查询课程"01"比课程"02"成绩低的学生的信息及课程分数。
SELECT 
    `student`.*, sc1.`s_score`, sc2.`s_score`
FROM
    `student`
        INNER JOIN
    `score` sc1 ON `student`.`s_id` = sc1.`s_id`
        AND sc1.`c_id` = '01'
        INNER JOIN
    `score` sc2 ON sc1.`s_id` = sc2.`s_id`
        AND sc2.`c_id` = '02'
WHERE
    sc1.`s_score` < sc2.`s_score`
        OR sc1.`s_score` IS NULL;


-- 3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩。
-- 子查询方法。
SELECT 
    sc.`s_id`,
    (SELECT 
            `s_name`
        FROM
            `student` st
        WHERE
            st.`s_id` = sc.`s_id`) AS s_name,
    AVG(sc.`s_score`) avg_score
FROM
    `score` sc
GROUP BY sc.`s_id`
HAVING avg_score >= 60;

-- 两个表连接方法。
SELECT 
    st.`s_id`, st.`s_name`, AVG(sc.`s_score`)
FROM
    `student` st,
    `score` sc
WHERE
    st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING AVG(sc.`s_score` >= 60);


-- 4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩。
-- （包括有成绩的和无成绩的）。
SELECT 
    st.`s_id`, st.`s_name`, AVG(sc.`s_score`) AS avg_score
FROM
    `student` st,
    `score` sc
WHERE
    st.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
HAVING AVG(sc.`s_score`) < 60 
UNION SELECT 
    st.`s_id`, st.`s_name`, 0 AS avg_score
FROM
    `student` st
WHERE
    st.`s_id` NOT IN (SELECT DISTINCT
            `s_id`
        FROM
            `score`);


-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。
SELECT 
    st.`s_id`,
    st.`s_name`,
    COUNT(sc.s_id) AS sum_course,
    IFNULL(SUM(sc.`s_score`), 0) AS sum_score
FROM
    `student` st
        LEFT OUTER JOIN
    `score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`;


-- 6、查询"李"姓老师的数量。
SELECT 
    COUNT(`t_id`)
FROM
    `teacher`
WHERE
    `t_name` LIKE '李%';


-- 7、查询学过"张三"老师授课的同学的信息。
SELECT 
    st.*
FROM
    `student` st
        JOIN
    `score` sc ON st.`s_id` = sc.`s_id`
WHERE
    sc.`c_id` IN (SELECT 
            `c_id`
        FROM
            `course`
        WHERE
            `t_id` = (SELECT 
                    `t_id`
                FROM
                    `teacher`
                WHERE
                    `t_name` = '老师 1'));

SELECT 
    st.*
FROM
    `student` st
        INNER JOIN
    `score` sc ON st.`s_id` = sc.`s_id`
WHERE
    sc.`c_id` IN (SELECT 
            `c_id`
        FROM
            `course` c
                INNER JOIN
            `teacher` t ON c.`t_id` = t.`t_id`
        WHERE
            `t`.`t_name` = '老师 1');


-- 8、查询没学过"张三"老师授课的同学的信息。
SELECT 
    *
FROM
    `student` st
WHERE
    st.s_id NOT IN (SELECT 
            st.`s_id`
        FROM
            `student` st
                JOIN
            `score` sc ON st.`s_id` = sc.`s_id`
        WHERE
            sc.`c_id` IN (SELECT 
                    `c_id`
                FROM
                    `course`
                WHERE
                    `t_id` = (SELECT 
                            `t_id`
                        FROM
                            `teacher`
                        WHERE
                            `t_name` = '老师 1')));

SELECT 
    *
FROM
    `student`
WHERE
    `s_id` NOT IN (SELECT 
            sc.`s_id`
        FROM
            `teacher` t,
            `score` sc,
            `course` c
        WHERE
            t.`t_id` = c.`t_id`
                AND c.`c_id` = sc.`c_id`
                AND t.`t_name` = '老师 1');

SELECT 
    *
FROM
    `student`
WHERE
    NOT EXISTS( SELECT 
            1
        FROM
            (SELECT 
                sc.`s_id`
            FROM
                `teacher` t, `score` sc, `course` c
            WHERE
                t.`t_id` = c.`t_id`
                    AND c.`c_id` = sc.`c_id`
                    AND t.`t_name` = '老师 1') t
        WHERE
            t.s_id = student.s_id);


-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。
SELECT 
    st.*
FROM
    `student` st,
    `score` sc1,
    `score` sc2
WHERE
    st.`s_id` = sc1.`s_id`
        AND st.`s_id` = sc2.`s_id`
        AND sc1.`c_id` = '01'
        AND sc2.`c_id` = '02';


-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。
SELECT 
    st.*
FROM
    `student` st
WHERE
    st.`s_id` IN (SELECT 
            `s_id`
        FROM
            `score`
        WHERE
            `c_id` = '01')
        AND st.`s_id` NOT IN (SELECT 
            `s_id`
        FROM
            `score`
        WHERE
            `c_id` = '02');

SELECT 
    st.*
FROM
    `student` st,
    (SELECT 
        `s_id`,
            MAX(IF(`c_id` = '01', `s_score`, NULL)) s01,
            MAX(IF(`c_id` = '02', `s_score`, NULL)) s02
    FROM
        `score`
    GROUP BY `s_id`) t
WHERE
    t.`s_id` = st.`s_id`
        AND t.s01 IS NOT NULL
        AND t.s02 IS NULL;


-- 11、查询没有学全所有课程的同学的信息。
SELECT 
    st.*, COUNT(sc.`c_id`) count_sc
FROM
    `student` st
        LEFT JOIN
    `score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING COUNT(sc.`c_id`) < (SELECT 
        COUNT(`c_id`)
    FROM
        `course`);

SELECT 
    st.*
FROM
    `student` st
WHERE
    st.`s_id` IN (SELECT 
            `s_id`
        FROM
            `score`
        WHERE
            `s_id` NOT IN (SELECT 
                    sc1.`s_id`
                FROM
                    `score` sc1
                        JOIN
                    `score` sc2 ON sc1.`s_id` = sc2.`s_id`
                        AND sc2.`c_id` = '02'
                        JOIN
                    `score` sc3 ON sc1.`s_id` = sc3.`s_id`
                        AND sc3.`c_id` = '03'
                WHERE
                    sc1.`c_id` = '01'));


-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息。
SELECT 
    *
FROM
    `student`
WHERE
    `s_id` IN (SELECT DISTINCT
            `s_id`
        FROM
            `score`
        WHERE
            `c_id` IN (SELECT 
                    `c_id`
                FROM
                    `score`
                WHERE
                    `s_id` = '01'))
GROUP BY 1 , 2 , 3 , 4;
-- 第 1 2 3 4 字段。


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

-- 创建 01 所学临时表。
CREATE TABLE s01_sc_temp AS SELECT t1.*, sc.`c_id` cid2 FROM
    (SELECT 
        st.*, t2.`c_id`
    FROM
        `student` st, (SELECT 
        `c_id`
    FROM
        `score`
    WHERE
        `s_id` = '01') t2) t1
        LEFT JOIN
    `score` sc ON t1.`s_id` = sc.`s_id`
        AND t1.`c_id` = sc.`c_id` 
UNION SELECT 
    t.*, sc.`c_id` cid2
FROM
    (SELECT 
        st.*, b.`c_id`
    FROM
        `student` st, (SELECT 
        `c_id`
    FROM
        `score`
    WHERE
        `s_id` = '01') b) t
        RIGHT JOIN
    `score` sc ON t.`s_id` = sc.`s_id`
        AND t.`c_id` = sc.`c_id`;

SELECT 
    *
FROM
    `student`
WHERE
    `s_id` NOT IN (SELECT 
            `s_id`
        FROM
            s01_s_temp
        WHERE
            cid2 IS NULL OR `c_id` IS NULL)
        AND `s_id` != '01';

-- ~ ~ ~

SELECT 
    *
FROM
    `student`
WHERE
    `s_id` IN (SELECT DISTINCT
            `s_id`
        FROM
            `score`
        WHERE
            `s_id` != '01'
                AND `c_id` IN (SELECT 
                    `c_id`
                FROM
                    `score`
                WHERE
                    `s_id` = '01')
        GROUP BY `s_id`
        HAVING COUNT(1) = (SELECT 
                COUNT(1)
            FROM
                `score`
            WHERE
                `s_id` = '01'));


-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名。
SELECT 
    st.`s_name`
FROM
    `student` st
WHERE
    st.`s_id` NOT IN (SELECT 
            `s_id`
        FROM
            `score`
        WHERE
            `c_id` = (SELECT 
                    `c_id`
                FROM
                    `course`
                WHERE
                    `t_id` = (SELECT 
                            `t_id`
                        FROM
                            `teacher`
                        WHERE
                            `t_name` = '老师 1'))
        GROUP BY s_id);


-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
SELECT 
    st.`s_id`, st.`s_name`, AVG(sc.`s_score`) avg_score
FROM
    `student` st
        LEFT JOIN
    `score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`s_score` >= 60, 0, 1)) >= 2;

SELECT 
    st.`s_id`, st.`s_name`, AVG(sc.`s_score`) avg_score
FROM
    `student` st
        LEFT JOIN
    `score` sc ON st.`s_id` = sc.`s_id`
GROUP BY st.`s_id`
HAVING SUM(IF(sc.`s_score` >= 60, 0, 1)) >= 2;

SELECT 
    st.`s_id`, st.`s_name`, ROUND(AVG(sc.`s_score`))
FROM
    student st
        LEFT JOIN
    score sc ON st.`s_id` = sc.`s_id`
WHERE
    st.`s_id` IN (SELECT 
            `s_id`
        FROM
            `score`
        WHERE
            `s_score` < 60
        GROUP BY `s_id`
        HAVING COUNT(1) >= 2)
GROUP BY st.`s_id` , st.`s_name`;


-- 16、检索课程"01"分数小于 60，按分数降序排列的学生信息。
SELECT 
    st.*, sc.`s_score`
FROM
    `score` sc
        RIGHT JOIN
    `student` st ON sc.`s_id` = st.`s_id`
WHERE
    sc.`c_id` = '01' AND sc.`s_score` < 60
ORDER BY `s_score` DESC;

SELECT 
    st.*, sc.`c_id`, sc.`s_score`
FROM
    `student` st,
    `score` sc
WHERE
    st.`s_id` = sc.`s_id`
        AND sc.`c_id` = '01'
        AND sc.`s_score` < 60
ORDER BY sc.`s_score` DESC;


-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
SELECT sc.`s_id`,
       (SELECT `s_score`
        FROM `score`
        WHERE `s_id` = sc.`s_id`
          AND `c_id` = '01')    AS 语文,
       (SELECT `s_score`
        FROM `score`
        WHERE `s_id` = sc.`s_id`
          AND `c_id` = '02')    AS 数学,
       (SELECT `s_score`
        FROM `score`
        WHERE `s_id` = sc.`s_id`
          AND `c_id` = '03')    AS 英语,
       ROUND(AVG(`s_score`), 2) AS 平均分
FROM `score` sc
GROUP BY sc.`s_id`
ORDER BY 平均分 DESC;


-- 18.查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。
-- 及格为 >= 60，中等为：70 ~ 80，优良为：80 ~ 90，优秀为：>= 90。
SELECT sc.`c_id`,
       c.`c_name`,
       MAX(`s_score`),
       MIN(`s_score`),
       ROUND(AVG(`s_score`), 2),
       ROUND(100 * (SUM(IF(sc.`s_score` >= 60, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),
             2) AS 及格率,
       ROUND(100 * (SUM(IF(sc.`s_score` >= 70 AND sc.`s_score` <= 80, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),
             2) AS 中等率,
       ROUND(100 * (SUM(IF(sc.`s_score` >= 80 AND sc.`s_score` <= 90, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),
             2) AS 优良率,
       ROUND(100 * (SUM(IF(sc.`s_score` >= 90, 1, 0)) / SUM(IF(sc.`s_score`, 1, 0))),
             2) AS 优秀率
FROM `score` sc
         LEFT JOIN
     `course` c ON sc.`c_id` = c.`c_id`
GROUP BY sc.`c_id`, c.`c_name`;


-- 19、按各科成绩进行排序，并显示排名（实现不完全）。
-- mysql 没有 rank(); 函数。
SELECT 
    sc1.`c_id`,
    sc1.`s_id`,
    sc1.`s_score`,
    COUNT(sc2.`s_score`) + 1 AS `rank`
FROM
    `score` sc1
        LEFT JOIN
    `score` sc2 ON sc1.`s_score` < sc2.`s_score`
        AND sc1.`c_id` = sc2.`c_id`
GROUP BY sc1.`c_id` , sc1.`s_id` , sc1.`s_score`
ORDER BY sc1.`c_id` , `rank`;


-- 20、查询学生的总成绩并进行排名。
SELECT 
    t1.`s_id`,
    @i:=@i + 1 AS i,
    @k:=(IF(@score = t1.sum_score, @k, @i)) AS `rank`,
    @score:=t1.sum_score AS score
FROM
    (SELECT 
        `s_id`, SUM(`s_score`) AS sum_score
    FROM
        `score`
    GROUP BY `s_id`
    ORDER BY sum_score DESC) t1,
    (SELECT @k:=0, @i:=0, @score:=0) AS kis;


-- 21、查询不同老师所教不同课程平均分从高到低显示。
SELECT 
    c.`t_id`,
    t.`t_name`,
    c.`c_id`,
    ROUND(AVG(`s_score`), 2) AS avg_score
FROM
    `course` c
        LEFT JOIN
    `score` sc ON c.`c_id` = sc.`c_id`
        LEFT JOIN
    `teacher` t ON c.`t_id` = t.`t_id`
GROUP BY c.`c_id` , c.`t_id` , t.`t_name`
ORDER BY avg_score DESC;


-- 22、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩。
SELECT d.*,
       si2.排名,
       si2.`s_score`,
       si2.`c_id`
FROM (SELECT sc.`s_id`,
             sc.`s_score`,
             sc.`c_id`,
             @i := @i + 1 AS 排名
      FROM `score` sc,
           (SELECT @i := 0) as i2
      WHERE sc.c_id = '01') as si2
         LEFT JOIN
     student d ON si2.s_id = d.s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT st.*,
       sj2.排名,
       sj2.s_score,
       sj2.c_id
FROM (SELECT sc.s_id,
             sc.s_score,
             sc.c_id,
             @j := @j + 1 AS 排名
      FROM `score` sc,
           (SELECT @j := 0) as j2
      WHERE sc.c_id = '02') as sj2
         LEFT JOIN
     `student` st ON sj2.s_id = st.s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT d.*,
       s.排名,
       s.`s_score`,
       s.`c_id`
FROM (SELECT sc.`s_id`,
             sc.`s_score`,
             sc.`c_id`,
             @k := @k + 1 AS 排名
      FROM `score` sc,
           (SELECT @k := 0) as k2
      WHERE sc.`c_id` = '03') as s
         LEFT JOIN
     `student` d ON s.`s_id` = d.`s_id`
WHERE 排名 BETWEEN 2 AND 3;


-- 23、统计各科成绩各分数段人数：课程编号, 课程名称, [100-85], [85-70], [70-60], [0-60] 及所占百分比。
SELECT DISTINCT c.`c_name`,
                sc.`c_id`,
                t1.`85-100`,
                t1.百分比,
                t2.`70-85`,
                t2.百分比,
                t3.`60-70`,
                t3.百分比,
                t4.`0-60`,
                t4.百分比
FROM `score` sc
         LEFT JOIN
     (SELECT `c_id`,
             SUM(IF(`s_score` > 85 AND `s_score` <= 100, 1, 0))                              AS `85-100`,
             ROUND(100 * (SUM(IF(`s_score` > 85 AND `s_score` <= 100, 1, 0)) / COUNT(*)), 2) AS 百分比
      FROM `score`
      GROUP BY `c_id`) t1 ON sc.`c_id` = t1.`c_id`
         LEFT JOIN
     (SELECT `c_id`,
             SUM(IF(`s_score` > 70 AND `s_score` <= 85, 1, 0))                              AS `70-85`,
             ROUND(100 * (SUM(IF(`s_score` > 70 AND `s_score` <= 85, 1, 0)) / COUNT(*)), 2) AS 百分比
      FROM `score`
      GROUP BY `c_id`) t2 ON sc.`c_id` = t2.`c_id`
         LEFT JOIN
     (SELECT `c_id`,
             SUM(IF(`s_score` > 60 AND `s_score` <= 70, 1, 0))                              AS `60-70`,
             ROUND(100 * (SUM(IF(`s_score` > 60 AND `s_score` <= 70, 1, 0)) / COUNT(*)), 2) AS 百分比
      FROM `score`
      GROUP BY `c_id`) t3 ON sc.`c_id` = t3.`c_id`
         LEFT JOIN
     (SELECT `c_id`,
             SUM(IF(`s_score` >= 0 AND `s_score` <= 60, 1, 0))                              AS `0-60`,
             ROUND(100 * (SUM(IF(`s_score` >= 0 AND `s_score` <= 60, 1, 0)) / COUNT(*)), 2) AS 百分比
      FROM `score`
      GROUP BY `c_id`) t4 ON sc.`c_id` = t4.`c_id`
         LEFT JOIN
     `course` c ON sc.`c_id` = c.`c_id`;


-- 24、查询学生平均成绩及其名次。
SELECT t.`s_id`,
       @i := @i + 1                                 AS '不保留空缺排名',
       @k := (IF(@avg_score = t.avg_score, @k, @i)) AS '保留空缺排名',
       @avg_score := avg_score                      AS '平均分'
FROM (SELECT `s_id`,
             ROUND(AVG(`s_score`), 2) AS avg_score
      FROM score
      GROUP BY `s_id`) t,
     (SELECT @avg_score := 0, @i := 0, @k := 0) b;


-- 25、查询各科成绩前三名的记录。
-- - 选出 sc1 表比 sc2 表成绩大的所有组。
-- - 选出比当前 id 成绩大的小于三个的。
SELECT 
    sc1.`s_id`, sc1.`c_id`, sc1.`s_score`
FROM
    `score` sc1
        LEFT JOIN
    `score` sc2 ON sc1.`c_id` = sc2.`c_id`
        AND sc1.`s_score` < sc2.`s_score`
GROUP BY sc1.`s_id` , sc1.`c_id` , sc1.`s_score`
HAVING COUNT(sc2.`s_id`) < 3
ORDER BY sc1.`c_id` , sc1.`s_score` DESC;


-- 26、查询每门课程被选修的学生数。
SELECT 
    `c_id`, COUNT(`s_id`)
FROM
    `score`
GROUP BY `c_id`;


-- 27、查询出只有两门课程的全部学生的学号和姓名。
SELECT 
    `s_id`, `s_name`
FROM
    `student`
WHERE
    `s_id` IN (SELECT 
            `s_id`
        FROM
            `score`
        GROUP BY `s_id`
        HAVING COUNT(`c_id`) = 2);


-- 28、查询男生、女生人数。
SELECT 
    `s_sex`, COUNT(`s_sex`) AS 人数
FROM
    student
GROUP BY s_sex;


-- 29、查询名字中含有"风"字的学生信息。
SELECT 
    *
FROM
    `student`
WHERE
    `s_name` LIKE '%风%';


-- 30、查询同名同性学生名单，并统计同名人数。
SELECT 
    st1.`s_name`, st1.`s_sex`, COUNT(*)
FROM
    `student` st1
        JOIN
    `student` st2 ON st1.`s_id` != st2.`s_id`
        AND st1.`s_name` = st2.`s_name`
        AND st1.`s_sex` = st2.`s_sex`
GROUP BY st1.`s_name` , st1.`s_sex`;


-- 31、查询 1990 年出生的学生名单。
SELECT 
    `s_name`
FROM
    `student`
WHERE
    `s_birth` LIKE '1990%';


-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。
SELECT 
    `c_id`, ROUND(AVG(`s_score`), 2) AS avg_score
FROM
    `score`
GROUP BY `c_id`
ORDER BY avg_score DESC , `c_id` ASC;


-- 33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩。
SELECT 
    sc.`s_id`,
    st.`s_name`,
    ROUND(AVG(sc.`s_score`), 2) AS avg_score
FROM
    `score` sc
        LEFT JOIN
    `student` st ON sc.`s_id` = st.`s_id`
GROUP BY `s_id`
HAVING avg_score >= 85;


-- 34、查询课程名称为"数学"，且分数低于 60 的学生姓名和分数。
SELECT 
    st.`s_name`, sc.`s_score`
FROM
    `score` sc
        LEFT JOIN
    `student` st ON st.`s_id` = sc.`s_id`
WHERE
    sc.`c_id` = (SELECT 
            `c_id`
        FROM
            `course`
        WHERE
            `c_name` = '数学')
        AND sc.s_score < 60;


-- 35、查询所有学生的课程及分数情况。
SELECT st.`s_id`,
       st.`s_name`,
       SUM(IF(c.`c_name` = '语文', sc.`s_score`, 0)) AS '语文',
       SUM(IF(c.`c_name` = '数学', sc.`s_score`, 0)) AS '数学',
       SUM(IF(c.`c_name` = '英语', sc.`s_score`, 0)) AS '英语',
       SUM(sc.`s_score`)                             AS '总分'
FROM `student` st
         LEFT JOIN
     `score` sc ON st.`s_id` = sc.`s_id`
         LEFT JOIN
     `course` c ON sc.`c_id` = c.`c_id`
GROUP BY st.`s_id`, st.`s_name`;


-- 36、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数。
SELECT 
    st.`s_name`, c.`c_name`, sc.`s_score`
FROM
    `course` c
        LEFT JOIN
    `score` sc ON c.`c_id` = sc.`c_id`
        LEFT JOIN
    `student` st ON st.`s_id` = sc.`s_id`
WHERE
    sc.`s_score` >= 70;


-- 37、查询不及格的课程。
SELECT 
    sc.`s_id`, sc.`c_id`, c.`c_name`, sc.`s_score`
FROM
    `score` sc
        LEFT JOIN
    `course` c ON sc.`c_id` = c.`c_id`
WHERE
    sc.`s_score` < 60;


-- 38、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名。
SELECT 
    sc.`s_id`, st.`s_name`
FROM
    `score` sc
        LEFT JOIN
    `student` st ON sc.`s_id` = st.`s_id`
WHERE
    sc.`c_id` = '01' AND sc.`s_score` > 80;


-- 39、求每门课程的学生人数。
SELECT 
    COUNT(*)
FROM
    `score`
GROUP BY `c_id`;


-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。
-- 查询老师 id。
SELECT 
    `c_id`
FROM
    `course` c,
    `teacher` t
WHERE
    c.`t_id` = t.`t_id`
        AND t.`t_name` = '老师 1';
-- 查询最高分（可能有相同分数）。
SELECT 
    MAX(`s_score`)
FROM
    `score`
WHERE
    `c_id` = '02';
-- 查询信息。
SELECT 
    st.*, sc.`s_score`, sc.`c_id`, c.`c_name`
FROM
    `student` st
        LEFT JOIN
    `score` sc ON st.`s_id` = sc.`s_id`
        LEFT JOIN
    `course` c ON sc.`c_id` = c.`c_id`
WHERE
    sc.`c_id` = (SELECT 
            `c_id`
        FROM
            `course` c,
            `teacher` t
        WHERE
            c.`t_id` = t.`t_id`
                AND t.`t_name` = '老师 1')
        AND sc.s_score IN (SELECT 
            MAX(s_score)
        FROM
            score
        WHERE
            c_id = '02');

SELECT 
    c.`c_name`, sc.`s_score`, st.*
FROM
    `course` c,
    `score` sc,
    `teacher` t,
    `student` st
WHERE
    t.`t_id` = c.`t_id`
        AND c.`c_id` = sc.`c_id`
        AND st.`s_id` = sc.`s_id`
        AND t.`t_name` = '老师 1'
        AND sc.s_score IN (SELECT 
            MAX(s_score)
        FROM
            course,
            score,
            teacher,
            student
        WHERE
            teacher.t_id = course.t_id
                AND course.c_id = score.c_id
                AND student.s_id = score.s_id
                AND teacher.t_name = '老师 1');


-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。
SELECT DISTINCT
    sc2.`s_id`, sc2.`c_id`, sc2.`s_score`
FROM
    `score` sc1,
    `score` sc2
WHERE
    sc1.`c_id` != sc2.`c_id`
        AND sc1.`s_score` = sc2.`s_score`;


-- 42、查询每门功成绩最好的前两名。
-- 牛逼的写法。
SELECT 
    sc1.`s_id`, sc1.`c_id`, sc1.`s_score`
FROM
    `score` sc1
WHERE
    (SELECT 
            COUNT(1)
        FROM
            `score` sc2
        WHERE
            sc2.`c_id` = sc1.`c_id`
                AND sc2.`s_score` >= sc1.`s_score`) <= 2
ORDER BY sc1.`c_id`;


-- 43、统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
SELECT 
    `c_id`, COUNT(*) AS total
FROM
    `score`
GROUP BY `c_id`
HAVING total > 5
ORDER BY total DESC , `c_id`;


-- 44、检索至少选修两门课程的学生学号。
SELECT 
    `s_id`, COUNT(*)
FROM
    `score`
GROUP BY `s_id`
HAVING COUNT(*) >= 2;


-- 45、查询选修了全部课程的学生信息。
SELECT 
    *
FROM
    `student`
WHERE
    `s_id` IN (SELECT 
            `s_id`
        FROM
            `score`
        GROUP BY `s_id`
        HAVING COUNT(*) = (SELECT 
                COUNT(*)
            FROM
                `course`));


-- 46、查询各学生的年龄。
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一。
SELECT 
    `s_birth`,
    (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(`s_birth`, '%Y') - (IF(DATE_FORMAT(NOW(), '%m%d') > DATE_FORMAT(`s_birth`, '%m%d'),
        0,
        1))) AS age
FROM
    `student`;


-- 47、查询本周过生日的学生。
SELECT 
    *
FROM
    `student`
WHERE
    WEEK(DATE_FORMAT(NOW(), '%Y%m%d')) = WEEK(`s_birth`);

SELECT 
    *
FROM
    `student`
WHERE
    YEARWEEK(`s_birth`) = YEARWEEK(DATE_FORMAT(NOW(), '%Y%m%d'));

SELECT WEEK(DATE_FORMAT(NOW(), '%Y%m%d'));


-- 48、查询下周过生日的学生。
SELECT 
    *
FROM
    `student`
WHERE
    WEEK(DATE_FORMAT(NOW(), '%Y%m%d')) + 1 = WEEK(`s_birth`);


-- 49、查询本月过生日的学生。
SELECT 
    *
FROM
    `student`
WHERE
    MONTH(DATE_FORMAT(NOW(), '%Y%m%d')) = MONTH(`s_birth`);


-- 50、查询下月过生日的学生。
SELECT 
    *
FROM
    `student`
WHERE
    MONTH(DATE_FORMAT(NOW(), '%Y-%m-%d')) + 1 = MONTH(`s_birth`);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034

相关阅读:
Electron和vue3集成（可用于生产打包）
在 Kubesphere 中开启新一代云原生数仓 Databend
【云原生之Docker实战】使用Docker部署Lychee照片管理平台
 Note——torch.size() & umr_maximum() array.max() & itertools.product()
2023年10月中国数据库排行榜：墨天轮榜单前五开新局，金仓、亚信热度攀升
 Genesis公链：夯实Web 3.0发展底座
 表内容的操作（增删查改）【MySQL】
Scala面向对象部分演示（IDEA开发）
Dubbo Admin修改注册中心为Nacos 以及Nacos整合Dubbo
独立思考之-----家庭容器的合理构造
原文地址：https://blog.csdn.net/lyfGeek/article/details/132656869