MySQL 50 题。

已于 2023-09-12 16:46:09 修改
阅读量4.7k 收藏 52
点赞数 3
文章标签： mysql android 数据库
于 2023-09-05 00:00:00 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lyfGeek/article/details/132656869
版权

MySQL 50 题。

文章目录

数据库。

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`);
lyfGeek
关注
  • 3
    点赞
  • 踩
  • 52
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
MySQL——基础50题
ly000666的博客
08-15 2519
mysql基础练习题50道
MySQL50题-练习
一个好学生_叶纪杉
08-23 2574
SQL面试50题 看了一些SQL面试的题目，这里做个记录。本文代码为MySql。 这个练习题目里面共有四张表： Student 学生表 字段名 字段含义 字段类型 sid 学生编号 varchar(10) sname 学生姓名 varchar(10) sage 学生年龄 datetime ssex 学生性别 varchar(10) Student表数据 insert into St...
MySQL练习题（经典50题）
weixin_43939159的博客
11-05 3万+
MySQL练习题（经典50题） – 建表 –学生表 CREATE TABLE Student( s_id VARCHAR(20), s_name VARCHAR(20) NOT NULL DEFAULT ‘’, s_birth VARCHAR(20) NOT NULL DEFAULT ‘’, s_sex VARCHAR(10) NOT NULL DEFAULT ‘’, PRIMARY KEY(s_id) ); –课程表 CREATE TABLE Course( c_id VARCHAR(20), c_nam
[Mysql] 经典 50 题
licn188的博客
07-22 6986
Mysql 经典 50题
MySQL之经典50道题
qq_40216188的博客
08-09 1万+
MySQL之经典50道题目一、创建数据表并插入数据 一、创建数据表并插入数据 1、学生表 Student(s_id,s_name,s_birth,s_sex) ：学生编号、姓名、年月、性别 -- 1、学生表 -- Student(s_id,s_name,s_birth,s_sex) ：学生编号、姓名、年月、性别 CREATE TABLE IF NOT EXISTS `Student` ( `s_id` VARCHAR (20), `s_name` VARCHAR (20) NOT NULL DEFA
mysql 50道经典面试题_2020年MySQL数据库面试题总结（50道题含答案解析）
weixin_42406647的博客
02-01 4518
1、MySQL 中有哪几种锁？(1)表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最 高，并发度最低。(2)行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最 低，并发度也最高。(3)页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表 锁和行锁之间，并发度一般。 2、MySQL 中有哪些不同的表格？共有 5 种类型的表格：(1)MyISAM(...
MySQL 50 道查询题汇总，足以巩固大部分查询（附带数据准备SQL、题型分析、演示、50道题的完整SQL）
Java 领域技术分享
04-06 2035
MySQL 50 道查询题汇总，足以巩固大部分查询（附带数据准备SQL、题型分析、演示、50道题的完整SQL）
MySQL数据库面试题（2020最新版）
热门推荐
ThinkWon的博客
03-10 121万+
文章目录数据库基础知识为什么要使用数据库什么是SQL？什么是MySQL?数据库三大范式是什么mysql有关权限的表都有哪几个MySQL的binlog有有几种录入格式？分别有什么区别？数据类型mysql有哪些数据类型引擎MySQL存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别？InnoDB引擎的4大特性存储引擎选择索引什么是索引？索引有哪些优缺点？索引使用场景（重点）............
mysql sql语句面试经典50题_常见的SQL面试题：经典50题（简单）
weixin_35559171的博客
02-01 9854
目录一、简单查询1. 查询姓“猴”的学生名单2.查询姓“孟”老师的个数二、汇总分析1.查询课程编号为“0002”的总成绩2.查询选了课程的学生人数3.查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分4.查询每门课程被选修的学生数5.查询男生、女生人数6.查询平均成绩大于60分学生的学号和平均成绩7.查询至少选修两门课程的学生学号8.查询同名同姓学生名单并统计同名人数9.查询不...
MySQL经典50题
weixin_44839146的博客
03-23 4892
虽然网上已经有很多相关资源，但是正确解答的优质资源并不太多，因此本篇文章将这50道题重新整理了一下，希望能给刚学习sql的童鞋一点帮助。 Student(S,Sname,Sage,Ssex) 学生表 Course(C,Cname,T) 课程表 SC(S,C,score) 成绩表 Teacher(T,Tname) 教师 1.1 创建学生表 CREATE TABLE `student` ( `S` varchar(10) NOT NULL, `Sname` varchar(10) DEFA
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做...
mysql 数据库练习50题
09-20
sql语句练习，支持mysql. 表包含：老师、学生、课程、成绩、年级、班级、老师教的班级表 多表数据查询的sql练习。
mysql面试题50题
04-02
MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由 Oracle 公司拥有和...总的来说，MySQL 面试题涵盖了从基础概念到高级特性的广泛知识领域，理解这些知识点对于成为合格的 MySQL 开发者或管理员至关重要。
mysql经典50题
04-01
mysql经典50题。以及注释，经过实战运行及改善，可以直接用。挺有用的，对于sql入门及网申笔试来说。
mysql查询必练50题
10-10
摘要： -- 1、查询“01”课程比“02”课程成绩高的所有学生的学号； SELECT a.sid FROM (SELECT sid, score FROM sc WHERE cid=01) a, (SELECT sid ,score FROM sc WHERE cid=02) b ...
在JPA项目启动时新增MySQL字段
VioletKiss
06-18 992
我用的方案1，如果你也是这种项目，那么我也推荐你用方案1，简单粗暴又方便；方案2是问了chatgpt得出来的答案，感觉不错，但是报错了又不想改；不建议使用 schema.sql 和 data.sql 对数据库结构及数据进行初始化，因为在每一次项目启动，这两个文件都必须执行一次，大大减慢了项目的启动速度，替代方案可以是使用 flyway 进行数据库版本控制。
mysqladmin——MySQL Server管理程序（二）
最新发布
u011565038的博客
06-22 994
mysqladmin是一个命令行工具，用于执行简单的 MySQL 服务器管理任务，如检查服务器的状态、创建和删除数据库、重载权限等。
如何在 MySQL 中创建和使用事务?
lgbingyu00的博客
06-21 928
事务对于MySQL在业务生产系统中的作用非常重要，以上示例主要显示的数据的隔离性。当多条数据同时执行的时候，事务会保证业务执行同时成功或同时失败，这样使用有关联性的业务可以保持业务的一致性。
mysql中生僻汉字的解决办法
codemami的博客
06-20 554
除了字符集之外，还需要选择适当的排序规则（collation）。如果你的数据库或表当前使用的字符集不支持生僻汉字（如latin1或utf8），你可以通过ALTER DATABASE或ALTER TABLE命令来更改它们。在MySQL中处理生僻汉字时，主要关注的是字符集和排序规则（collation）的设置。在进行任何可能更改数据的操作之前（如更改字符集或排序规则），务必备份你的数据库。总之，处理MySQL中生僻汉字问题的关键是选择正确的字符集和排序规则，并确保在存储、检索和显示过程中正确处理字符编码。
mysql经典50题pdf
09-25
MySQL经典50题PDF是一份内容丰富、涵盖各个方面的MySQL面试题集合。这份PDF中包含了MySQL数据库的基础知识、数据查询、索引优化、性能调优等方面的问题。 MySQL是一种流行的关系型数据库管理系统，广泛应用于各种...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

lyfGeek

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值