MySQL 50 题。

Posted on 2023年9月20日 by hackdl

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

CREATE SCHEMA `mysql_fifty` DEFAULT CHARACTER SET utf8 ;

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');

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`);

-- 查询课程"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`);

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇: 喜马拉雅 Redis 与 Pika 缓存使用军规
下一篇: 写在开源前 1 天：OceanBase ODC 开源背后的故事