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 utf8mb4CREATE 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.s02FROM (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` stWHERE 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` sc2WHERE 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_scoreFROM `score` scGROUP BY sc.`s_id`HAVING avg_score >= 60;-- 两个表连接方法。SELECT st.`s_id`, st.`s_name`, AVG(sc.`s_score`)FROM `student` st, `score` scWHERE 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_scoreFROM `student` st, `score` scWHERE st.`s_id` = sc.`s_id`GROUP BY sc.`s_id`HAVING AVG(sc.`s_score`) < 60UNIONSELECT st.`s_id`, st.`s_name`, 0 AS avg_scoreFROM `student` stWHERE 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_scoreFROM `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` stWHERE 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` sc2WHERE 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` stWHERE 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`) tWHERE 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_scFROM `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` stWHERE 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 ASSELECT t1.*, sc.`c_id` cid2FROM (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`UNIONSELECT t.*, sc.`c_id` cid2FROM (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` stWHERE 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_scoreFROM `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_scoreFROM `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` < 60ORDER BY `s_score` DESC;SELECT st.*, sc.`c_id`, sc.`s_score`FROM `student` st, `score` scWHERE st.`s_id` = sc.`s_id` AND sc.`c_id` = '01' AND sc.`s_score` < 60ORDER 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` scGROUP 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 scoreFROM (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_scoreFROM `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_idWHERE 排名 BETWEEN 2 AND 3UNIONSELECT st.*, sj2.排名, sj2.s_score, sj2.c_idFROM (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_idWHERE 排名 BETWEEN 2 AND 3UNIONSELECT 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`) < 3ORDER 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 studentGROUP 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_scoreFROM `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_scoreFROM `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` tWHERE 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` stWHERE 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` sc2WHERE 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` sc1WHERE (SELECT COUNT(1) FROM `score` sc2 WHERE sc2.`c_id` = sc1.`c_id` AND sc2.`s_score` >= sc1.`s_score`) <= 2ORDER BY sc1.`c_id`;-- 43、统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。SELECT `c_id`, COUNT(*) AS totalFROM `score`GROUP BY `c_id`HAVING total > 5ORDER 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 ageFROM `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.s02FROM (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` stWHERE 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` sc2WHERE 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_scoreFROM `score` scGROUP BY sc.`s_id`HAVING avg_score >= 60;-- 两个表连接方法。SELECT st.`s_id`, st.`s_name`, AVG(sc.`s_score`)FROM `student` st, `score` scWHERE 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_scoreFROM `student` st, `score` scWHERE 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_scoreFROM `student` stWHERE 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_scoreFROM `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` stWHERE 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` sc2WHERE 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` stWHERE 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`) tWHERE 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_scFROM `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` stWHERE 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` cid2FROM (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` stWHERE 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_scoreFROM `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_scoreFROM `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` < 60ORDER BY `s_score` DESC;SELECT st.*, sc.`c_id`, sc.`s_score`FROM `student` st, `score` scWHERE st.`s_id` = sc.`s_id` AND sc.`c_id` = '01' AND sc.`s_score` < 60ORDER 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` scGROUP 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 scoreFROM (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_scoreFROM `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_idWHERE 排名 BETWEEN 2 AND 3UNIONSELECT st.*, sj2.排名, sj2.s_score, sj2.c_idFROM (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_idWHERE 排名 BETWEEN 2 AND 3UNIONSELECT 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`) < 3ORDER 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 studentGROUP 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_scoreFROM `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_scoreFROM `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` tWHERE 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` stWHERE 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` sc2WHERE 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` sc1WHERE (SELECT COUNT(1) FROM `score` sc2 WHERE sc2.`c_id` = sc1.`c_id` AND sc2.`s_score` >= sc1.`s_score`) <= 2ORDER BY sc1.`c_id`;-- 43、统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。SELECT `c_id`, COUNT(*) AS totalFROM `score`GROUP BY `c_id`HAVING total > 5ORDER 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 ageFROM `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`);来源地址：https://blog.csdn.net/lyfGeek/article/details/132656869
