MySql 50题

饶雨涛

已于 2024-02-19 09:49:28 修改

阅读量901

点赞数 18

文章标签： mysql 数据库

于 2023-12-18 22:17:37 首次发布

本文链接：https://blog.csdn.net/Elleat/article/details/135072975

版权

原题目、文章地址：http://t.csdnimg.cn/JmOwl

sql题50道数据：

[注意]：此文章仅作为个人记录，请谨慎选择是否观看！如有错误感谢提出。

**[提示]：**DQL语句关键词执行顺序：from->where->group by->having->select->order by->limit ;

1701910007378

[注意]：实际数据与演示数据由于中途修改导致可能不同，仅供参考

/*
Navicat MySQL Data Transfer

Source Server         : 10.130.23.134-MYSQL
Source Server Version : 80029
Source Host           : localhost:3306
Source Database       : examination01

Target Server Type    : MYSQL
Target Server Version : 80029
File Encoding         : 65001

Date: 2023-12-07 08:46:29
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8_general_ci DEFAULT NULL,
  `t_id` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for `sc`
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `s_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `c_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `score` decimal(18,1) DEFAULT NULL,
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', '80.0');
INSERT INTO `sc` VALUES ('01', '02', '90.0');
INSERT INTO `sc` VALUES ('01', '03', '99.0');
INSERT INTO `sc` VALUES ('02', '01', '80.0');
INSERT INTO `sc` VALUES ('02', '02', '70.0');
INSERT INTO `sc` VALUES ('02', '03', '90.0');
INSERT INTO `sc` VALUES ('03', '01', '80.0');
INSERT INTO `sc` VALUES ('03', '02', '80.0');
INSERT INTO `sc` VALUES ('03', '03', '80.0');
INSERT INTO `sc` VALUES ('04', '01', '50.0');
INSERT INTO `sc` VALUES ('04', '02', '30.0');
INSERT INTO `sc` VALUES ('04', '03', '20.0');
INSERT INTO `sc` VALUES ('05', '01', '76.0');
INSERT INTO `sc` VALUES ('05', '02', '87.0');
INSERT INTO `sc` VALUES ('06', '01', '31.0');
INSERT INTO `sc` VALUES ('06', '03', '34.0');
INSERT INTO `sc` VALUES ('07', '02', '89.0');
INSERT INTO `sc` VALUES ('07', '03', '98.0');

-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` datetime DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-12-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-12-01 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `student` VALUES ('10', '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `student` VALUES ('11', '李四', '2012-06-06 00:00:00', '女');
INSERT INTO `student` VALUES ('12', '赵六', '2013-06-13 00:00:00', '女');
INSERT INTO `student` VALUES ('13', '孙七', '2014-06-01 00:00:00', '女');

-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

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

代码：

select 
	s.*, sc1.score c1_score, sc2.score c2_score
from ( select * from sc where c_id = "01") sc1
,( select * from sc where c_id = "02") sc2
, student s
where sc1.s_id = sc2.s_id and sc1.score > sc2.score
and sc1.s_id = s.id;

思路：

先子查询两个课程
( select * from sc where c_id = “01”) s1
( select * from sc where c_id = “02”) s2

1699588561572 1699588569583

再条件 (distinct不需要，可忽略)

where s1.s_id = s2.s_id and s1.score > s2.score

1699588802942

再补充学生信息

, student s

and sc1.s_id = s.id

1699588894411

结果调整：

1699589147964

2.查询学生同时存在" 01 “课程和” 02 "课程的情况

代码：

select 
sc1.s_id,sc1.c_id,sc2.c_id
from (select * from sc where sc.c_id = "01") sc1
join (select * from sc where sc.c_id = "02") sc2
on sc1.s_id = sc2.s_id;

思路

子查询

(select * from sc where sc.c_id = "01") sc1
(select * from sc where sc.c_id = "02") sc2

1699605369698 1699605375976

结果

1699605429447

结果调整：

1699605692850

3.查询学生存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

代码

select 
	sc1.s_id,sc1.c_id,sc2.c_id
from (select * from sc where sc.c_id = "01") sc1
left join (select * from sc where sc.c_id = "02") sc2
on sc1.s_id = sc2.s_id;

思路

01子查询左连接 02子查询即可

1699606369024

结果调整：

1699606433159

4.查询学生不存在" 01 “课程但存在” 02 "课程的情况

代码

select 	
	sc1.s_id, sc1.c_id, sc2.c_id
from sc sc1
join (select * from sc where sc.c_id = "02") sc2
on sc1.s_id = sc2.s_id 
and sc1.c_id != "01" 
and sc1.c_id != "02";

思路

子查询sc中的02

(select * from sc where sc.c_id = "02") as sc2

内联查

1699607819811

过滤 01 和左右重复的 02

1699607866195

结果调整：

1699607934265

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

代码

select 
	sc1.s_id, round(avg(sc1.score),2) as avgScore
from sc sc1
GROUP BY sc1.s_id
having avg(sc1.score) > 60;

思路

分组， round保留两位小数(四舍五入)

1699608641454

过滤 > 60

1699608698385

6.查询在 SC 表存在成绩的学生信息

代码

select * from student 
where id in (select DISTINCT s_id from sc);

思路

列子查询

1699616186717

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为 null

代码

select 
 s.*,
 count(sc.s_id) count, 
 SUM(sc.score) sumScore
from student s
left join sc on sc.s_id = s.id
GROUP BY s.id

思路

左连接（left join），对student.id分组

注意：join内连接会在匹配时忽略无法连接的行（左右表参与匹配的行都会被抛弃），这样我们将无法在sc中不存在成绩时保留student表中的记录并展示

1699617304395

错误示例1：如果对sc.id分组会报错，因为左连接时sc.id可能为空无法分组

1699617494737

错误示例2：最后select时，如果不是count(sc.s_id)，而是count( * )或者count(s. * ) 结果如下

1699617703812

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

代码

select COUNT(*) from teacher where name like "李%";

思路

略

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

代码

select
	s.*
from student s
where s.id in (select sc.s_id from sc where sc.c_id in (SELECT id from course where t_id = (select id from teacher where name = "张三")));

思路（疯狂的“子查询”）

1.查教师id(标量子查询)

(select id from teacher where name = "张三")

2.根据教师id 查课程id列表（列子查询）

(select id from course where t_id = [教师id])

3.根据课程id列表查 sc中的学生id列表（列子查询）

(select sc.s_id from sc where sc.c_id in [课程id列表])

4.根据学生id列表查学生信息

select
	s.*
from student s
where s.id in [学生id列表]

结果

1699619468017

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

代码

select
 s.*,
 count(sc.s_id) count
from student s
left join sc on sc.s_id = s.id
GROUP BY s.id
having count < (select count(*) from course);

思路

这里可以利用第7题中的count字段判断学生是否学全

select 
 s.*,
 count(sc.s_id) count, 
 SUM(sc.score) sumScore
from student s
left join sc on sc.s_id = s.id
GROUP BY s.id

👇

select
 s.*,
 count(sc.s_id) count
from student s
left join sc on sc.s_id = s.id
GROUP BY s.id

总课程数(标量子查询)

(select count(*) from course)

过滤条件 count < [总课程数]

having count < [总课程数]

注意：不能在where中过滤，因为where的执行时机在group by之前（具体自查DQL执行顺序）

结果

select
 s.*,
 count(sc.s_id) count
from student s
left join sc on sc.s_id = s.id
GROUP BY s.id
having count < (select count(*) from course);

1699621925778

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

代码

select * from student 
where id in 
(select distinct s_id from sc where c_id in (select c_id from sc where s_id = "01") and s_id != "01");

思路

根据该学生id 查 sc中对应的课程列表（列子查询）

(select c_id from sc where s_id = "01")

1699623706549

根据[课程列表] 查 sc中的学生id列表，并distinct一下（列子查询）

select s_id from sc where c_id in [课程列表]

1699623834663

👇distinct 并排除自身

(select distinct s_id from sc where c_id in [课程列表] and s_id != "01")

1699624112439

根据上面的 [学生id列表] 查学生信息

select * from student where id in [学生id列表]

结果

1699624198849

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

代码

--- 用户信息表就不连接了
select 
	t1.s_id
from (select
	sc.s_id
from (select * from sc where s_id != "01") as sc
join 
(select c_id from sc where s_id = "01") as c_01
on c_01.c_id = sc.c_id) t1
GROUP BY t1.s_id
HAVING COUNT(t1.s_id) = (select count(*) from sc where s_id = "01")

思路

根据该学生id 查 sc中对应的 01学生课程列表

select c_id from sc where s_id = "01"

1699623706549

sc中学生id不为 01 的其他学生课程列表

select
	*
from sc
where s_id != "01";

1699673739406

连接两表 [01学生课程列表] 和 [其他学生课程列表], 条件 c_01.c_id = sc.c_id, 得到表我们称它为 t1

(select
	*
from [01学生课程列表] as sc
join [其他学生课程列表] as c_01
on c_01.c_id = sc.c_id )

1699673943528

最后根据 [s_id分组的每组记录数] 与 [01学生课程列表总记录数] 是否匹配来过滤出符合结果的s_id列表

[01学生课程列表总记录数] :

(select count(*) from sc where s_id = "01")

过滤

select 
	t1.s_id
from [t1] t1
GROUP BY t1.s_id
HAVING COUNT(t1.s_id) = [01学生课程列表总记录数]

👇 结果（用户信息表这里就不连接了，以免代码太长）

1699675382821

思考： [01学生课程列表总记录数] 为0或null时，这个是时候我们还能找到课程数为0或null的其他同学吗？

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

代码

select 
	s.*
from student s 
where s.id not in (
	select 
		sc.s_id
	from sc
	join (
		select id from course where t_id = (
			select id from teacher where name = "张三"
		)
	) c on c.id = sc.c_id
)

思路

查老师的课程列表（列子查询）

select id from course where t_id = (
	select id from teacher where name = "张三"
)

1699685927368

sc表 join 内连接 [老师的课程列表] 得到所有存在该课的学生id列表（列子查询）

select 
	distinct sc.s_id
from sc
join [老师的课程列表]  c on c.id = sc.c_id

1699685909392

not in

select 
	s.*
from student s 
where s.id not in [存在该课的学生id列表]

1699686053215

14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩(保留2位小数)

代码

思路

sc表查得到 [平均成绩表]

(
	select s_id, round(avg(score),2) as avgScore
	from sc group by s_id
)

1699772702689

sc表查得到 [不及格学生id和不及格数表]

select sc.s_id, count(*) as count
from sc 
where sc.score < 60
group by sc.s_id

1699772833730

筛选 count >= 2

select sc.s_id, count(*) as count
from sc 
where sc.score < 60
group by sc.s_id
having count >= 2

1699772922317

[不及格学生id和不及格数表] 关联 [平均成绩表]、student表

select sc.s_id, s.name, sc2.avgScore
from sc
join student s on s.id = sc.s_id
join 
(select s_id, round(avg(score),2) as avgScore from sc group by s_id) sc2 on sc.s_id = sc2.s_id
where sc.score < 60
group by sc.s_id
having count(sc.s_id) >= 2

1699774272805

注意事项

是 having count(sc.s_id)，而不是 having count(sc.*) 或 having count(*)
执行顺序：where > 聚合函数 > having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义，但这里student中的记录和分组后的sc是一对一关系，所以能拿到 s.name 的内容

15.检索" 01 "课程分数小于 60，按分数降序排列的学生信息（姓名、分数）

代码

select s.id,s.name,sc.score
from sc
join student s on s.id = sc.s_id
where c_id = "01" and score < 60
order by score desc

思路

按分数降序查询所有 [" 01 "课程分数小于 60] 的学生id列表

select *
from sc
where c_id = "01" and score < 60
order by score desc

1699777055102

连接student表，得到结果

select s.id,s.name,sc.score
from sc
join student s on s.id = sc.s_id
where c_id = "01" and score < 60
order by score desc

1699777070656

16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(保留两位小数)

代码

select 
	t1.s_id, 
	t2.score as "语文",
	t3.score as "数学",
	t4.score as "英语",
	t1.avgScore as "平均分"
from  
    (
        select s_id, round(avg(score), 2) as avgScore
        from sc 
        group by s_id
    ) t1
left join (select * from sc where c_id = "01") t2
on t1.s_id = t2.s_id
left join (select * from sc where c_id = "02") t3
on t1.s_id = t3.s_id
left join (select * from sc where c_id = "03") t4
on t1.s_id = t4.s_id
order by t1.avgScore desc;

思路

sc表根据平均分查平均成绩 , 称 [t1]

(
    select s_id, round(avg(score), 2) as avgScore
    from sc 
    group by s_id
) t1

1699777871770

如果 [t1] 直接连接 sc 表, 根据 t1.avgScore 降序，如下结果

select sc.*, t1.avgScore
from sc
join 
    (
        select s_id, round(avg(score), 2) as avgScore
        from sc 
        group by s_id
    ) t1
on sc.s_id = t1.s_id
order by t1.avgScore desc;

1699778103245

我们希望不懂课程的分数是横向展示的，可以利用 join , 这样

select 
	t1.s_id, 
	t2.score as "语文",
	t3.score as "数学",
	t4.score as "英语",
	t1.avgScore as "平均分"
from  
    (
        select s_id, round(avg(score), 2) as avgScore
        from sc 
        group by s_id
    ) t1
left join (select * from sc where c_id = "01") t2
on t1.s_id = t2.s_id
left join (select * from sc where c_id = "02") t3
on t1.s_id = t3.s_id
left join (select * from sc where c_id = "03") t4
on t1.s_id = t4.s_id
order by t1.avgScore desc;

1699779594110

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

代码

SELECT t1.c_id
,t2.name
,MAX(score) AS '最高分'
,MIN(score) AS '最低分'
,AVG(score) AS '平均分'
,CONCAT(ROUND(SUM(IF(score >= 60,1,0))*100/COUNT(score),2),'%') AS '及格率'
,CONCAT(ROUND(SUM(IF(score >= 70 and score < 80,1,0))*100/COUNT(score),2),'%') AS '中等率'
,CONCAT(ROUND(SUM(IF(score >= 80 and score < 90,1,0))*100/COUNT(score),2),'%') AS '优良率'
,CONCAT(ROUND(SUM(IF(score >= 90,1,0))*100/COUNT(score),2),'%') AS '优秀率'
,COUNT(score) AS '人数'
FROM SC t1 JOIN Course t2
ON t1.c_id = t2.id
GROUP BY t1.c_id,t2.name
ORDER BY '人数' DESC,c_id ASC;

2 逆天版：你就说对没对吧

select 
	sc.c_id,
	count(sc.c_id) "选修人数",
	c.name "课程名",
	max(sc.score) "最高分",
	MIN(sc.score) "最低分",
	AVG(sc.score) "平均分",
	count1.countRate "及格率(%)",
	count2.countRate "中等率(%)",
	count3.countRate "优良率(%)",
	count4.countRate "优秀率(%)"
from sc 

left join course c on c.id = sc.c_id

left join (
	select 
		sc.c_id,
		ROUND(
			(100*count(sc.c_id)/sc2.count), 2
		) as countRate
	from sc
	join (
		select sc.c_id,count(*) count from sc group by sc.c_id
	) sc2 on sc.c_id = sc2.c_id
	where sc.score >= 60
	group by sc.c_id
) count1 on count1.c_id = sc.c_id

left join (
	select 
		sc.c_id,
		ROUND(
			(100*count(sc.c_id)/sc2.count), 2
		) as countRate
	from sc
	join (
		select sc.c_id,count(*) count from sc group by sc.c_id
	) sc2 on sc.c_id = sc2.c_id
	where sc.score >= 70 and sc.score < 80
	group by sc.c_id
) count2 on count2.c_id = sc.c_id

left join (
	select 
		sc.c_id,
		ROUND(
			(100*count(sc.c_id)/sc2.count), 2
		) as countRate
	from sc
	join (
		select sc.c_id,count(*) count from sc group by sc.c_id
	) sc2 on sc.c_id = sc2.c_id
	where sc.score >= 80 and sc.score < 90
	group by sc.c_id
) count3 on count3.c_id = sc.c_id

left join (
	select 
		sc.c_id,
		ROUND(
			(100*count(sc.c_id)/sc2.count), 2
		) as countRate
	from sc
	join (
		select sc.c_id,count(*) count from sc group by sc.c_id
	) sc2 on sc.c_id = sc2.c_id
	where sc.score >= 90
	group by sc.c_id
) count4 on count4.c_id = sc.c_id

GROUP BY sc.c_id

order by count(sc.c_id) desc, sc.c_id asc

思路

sc 表根据 c_id 分组，得到 [主表]

select 
	sc.c_id,
	max(sc.score) 最高分,
	MIN(sc.score) 最低分,
	AVG(sc.score) 平均分
from sc 

GROUP BY sc.c_id

1700188644538

一个单独课程的学生数

select count(*) from sc where c_id = "01"

一个单独课程的及格人数

select count(*) from sc where c_id = "01" and score >= 60

将课程id利用分组通用化，利用join合并 , 过滤、计算出及格率（score >= 60）。 ‘中等率’…等同理

select 
	sc.c_id,
	ROUND(
		(100*count(sc.c_id)/sc2.count), 2
	) as countRate
from sc
join (
	select sc.c_id,count(*) count from sc group by sc.c_id
) sc2 on sc.c_id = sc2.c_id
where sc.score >= 60
group by sc.c_id

1700188936392

将上面的结果视为子查询，利用left join连接到 [主表]（其他…率同理）

注意：需要left join左连接，内连接不行，会导致其他课程信息丢失

select 
	sc.c_id,
	max(sc.score) 最高分,
	MIN(sc.score) 最低分,
	AVG(sc.score) 平均分,
	count1.countRate 及格率(%)
from sc 
left join (
	select 
		sc.c_id,
		ROUND(
			(100*count(sc.c_id)/sc2.count), 2
		) as countRate
	from sc
	join (
		select sc.c_id,count(*) count from sc group by sc.c_id
	) sc2 on sc.c_id = sc2.c_id
	where sc.score >= 60
	group by sc.c_id
) count1 on count1.c_id = sc.c_id

    ...left join'其他率'
    
GROUP BY sc.c_id

1700192409892

补充选修人数、课程名、排序

# 
select 
	sc.c_id,
	count(sc.c_id) "选修人数",
	c.name "课程名",
	max(sc.score) "最高分",
	MIN(sc.score) "最低分",
	AVG(sc.score) "平均分",
	count1.countRate "及格率(%)",
	count2.countRate "中等率(%)",
	count3.countRate "优良率(%)",
	count4.countRate "优秀率(%)"
from sc 

left join course c on c.id = sc.c_id

left join (
	select 
		sc.c_id,
		ROUND(
			(100*count(sc.c_id)/sc2.count), 2
		) as countRate
	from sc
	join (
		select sc.c_id,count(*) count from sc group by sc.c_id
	) sc2 on sc.c_id = sc2.c_id
	where sc.score >= 60
	group by sc.c_id
) count1 on count1.c_id = sc.c_id

left join (
	select 
		sc.c_id,
		ROUND(
			(100*count(sc.c_id)/sc2.count), 2
		) as countRate
	from sc
	join (
		select sc.c_id,count(*) count from sc group by sc.c_id
	) sc2 on sc.c_id = sc2.c_id
	where sc.score >= 70 and sc.score < 80
	group by sc.c_id
) count2 on count2.c_id = sc.c_id

...left join'其他率'

GROUP BY sc.c_id

order by count(sc.c_id) desc, sc.c_id asc

1700192877459

白学版：

SUM(IF(exper, trueValue, falseValue))

SELECT t1.c_id
,t2.name
,MAX(score) AS '最高分'
,MIN(score) AS '最低分'
,AVG(score) AS '平均分'
,CONCAT(ROUND(SUM(IF(score >= 60,1,0))*100/COUNT(score),2),'%') AS '及格率'
,CONCAT(ROUND(SUM(IF(score >= 70 and score < 80,1,0))*100/COUNT(score),2),'%') AS '中等率'
,CONCAT(ROUND(SUM(IF(score >= 80 and score < 90,1,0))*100/COUNT(score),2),'%') AS '优良率'
,CONCAT(ROUND(SUM(IF(score >= 90,1,0))*100/COUNT(score),2),'%') AS '优秀率'
,COUNT(score) AS '人数'
FROM SC t1 JOIN Course t2
ON t1.c_id = t2.id
GROUP BY t1.c_id,t2.name
ORDER BY '人数' DESC,c_id ASC;

18.按各科平均成绩进行排序，并显示排名

代码

SET @i := 0;-- 定义一个变量

select 
	t1.c_id,
	t1.avgScore,
	@i := @i + 1 AS "排名"
from (
	select 
	 sc.c_id, 
	 avg(score) avgScore
	from sc
	GROUP BY sc.c_id
	order by avgScore desc
) t1

思路

SET @i :=0; 定义变量

mysql中 = 一般用于比较， := 用于赋值

1700203279168

三种常见的排名

第19题（https://blog.csdn.net/m0_67392182/article/details/126037354?spm=1001.2014.3001.5506）

row_number、dense_rank、rank在MySQL 5.7中的实现
对SC中的学生score进行整体排名

ROW_NUMBER

1 2 3 4 5 6 7 没有重复排名，依次递增

DENSE_RANK

1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的

RANK

1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续

进行分组排名

ROW_NUMBER

查询每课程成绩最好的前三名 1 2 3 4 5 6 7 没有重复排名，依次递增

DENSE_RANK

查询每课程成绩最好的前三名 1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的

RANK

查询每课程成绩最好的前三名 1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续

19.按各科平均成绩进行排序，并显示排名，重复时不保留名次空缺

122456

参考 21 题

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

122345

代码

从这里开始，修改了表内容：sc表中的s_id=02的学生的没门课增加了10分

set @a := 0;
set @b := 0;
set @sum := 0;

select 
	t.s_id,
	t.sum_score as '总分',
	IF(@sum = t.sum_score,@a,@a := @a + 1) as '排名',
	@sum := t.sum_score
from
(
	select 
	s_id, sum(score) as sum_score
	from sc group by s_id 
	order by sum_score desc
) t;

这里调整了下 s_id = 2 学生的总成绩

1701485145909

思路

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

122456

代码

set @a := 0;
set @b := 0;
set @c := 0;
set @sum := 0;

select 
	t.s_id,
	t.sum_score as '总分',
	@c := @c + 1,
	IF(@sum = t.sum_score,@a, @a := @c) as '排名',
	@sum := t.sum_score
from
(
	select 
	s_id, sum(score) as sum_score
	from sc group by s_id 
	order by sum_score desc
) t;

1701485844572

-----------------------------------------------------------------------分割线------------------------------------------------------------------------------

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

代码

select 
	c_id,
	name as '学科',
  CONCAT(ROUND((sum(if(score < 60 and score >= 0, 1, 0)) * 100) / sum(c_id), 2), '%') as '[0,60)人数占比',
	CONCAT(ROUND((sum(if(score < 70 and score >= 60, 1, 0)) * 100) / sum(c_id), 2), '%') as '[60,70)人数占比',
	CONCAT(ROUND((sum(if(score < 85 and score >= 70, 1, 0)) * 100) / sum(c_id), 2), '%') as '[70,85)人数占比',
	CONCAT(ROUND((sum(if(score <= 100 and score >= 85, 1, 0)) * 100) / sum(c_id), 2), '%') as '[85,100]人数占比'
from sc, course where c_id = id
group by c_id

1701487388651

23.查询各科成绩前三名的记录

代码

set @cId = 0;
set @sort = 0;

select 
	t.sort as '排名',
	t.s_id,
	s.name as '姓名',
	t.c_id,
	c.name as '课程',
	t.score
from
	(
		select 
		 t.*,
		 if(@cId = t.c_id, @sort := @sort + 1, @sort := 1) as sort,
		 @cId := t.c_id
		from
		(select * from sc order by c_id, score desc) t
	) t
join student s on s.id = t.s_id
join course c on c.id = t.c_id
where t.sort < 4

思路

先排个序

select * from sc order by c_id, score desc;

1701487872416

再根据课程id排个名

set @cId = 0;
set @sort = 0;

select 
 t.*,
 if(@cId = t.c_id, @sort := @sort + 1, @sort := 1) as '排名',
 @cId := t.c_id
from
(select * from sc order by c_id, score desc) t

1701488750678

我们只需要上面红色框内的内容，但我们不能直接在后面加where，因为where在select之前执行，我们必须在select后做筛选操作，要么使用分组的having、要么再嵌套一层子查询，显然我们应该选择嵌套子查询

(t.sort < 4)：

set @cId = 0;
set @sort = 0;

select 
	t.*
from
	(
		select 
		 t.*,
		 if(@cId = t.c_id, @sort := @sort + 1, @sort := 1) as sort,
		 @cId := t.c_id
		from
		(select * from sc order by c_id, score desc) t
	) t

where t.sort < 4

1701489301494

剩下的就是一些收尾操作（补充其他信息）

set @cId = 0;
set @sort = 0;

select 
	t.sort as '排名',
	t.s_id,
	s.name as '姓名',
	t.c_id,
	c.name as '课程',
	t.score
from
	(
		select 
		 t.*,
		 if(@cId = t.c_id, @sort := @sort + 1, @sort := 1) as sort,
		 @cId := t.c_id
		from
		(select * from sc order by c_id, score desc) t
	) t
join student s on s.id = t.s_id
join course c on c.id = t.c_id
where t.sort < 4

1701489604627

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

代码

select 
	c.name,
	count(sc.c_id)
from sc
join course c on c.id = sc.c_id
GROUP BY sc.c_id;

1701509959527

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

代码

select 
 s_id, count(s_id) as '选课数' 
from sc
group by s_id
having count(s_id) = 2;

1701510110825

26.查询男生、女生人数

代码

select 
 s.sex, count(s.sex) as '人数'
from student s
group by s.sex

1701510225072

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

代码

select 
	s.*
from student s
where s.name like '%风%';

1701510446694

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

代码

select
	s.name, s.sex, count(*)
from student s
group by s.name,s.sex
having count(*) > 1;

1701510589956

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

代码

select * from student where age like '1990%';

1701510733924

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

代码

select
	c_id, name, avg(score) as avg_score
from sc
join course on id = c_id
group by c_id
order by avg_score desc, c_id;

1701514197073

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

代码

select
	s_id, name, round(avg(score),2)
from sc
join student on id = s_id
group by s_id
having avg(score) >= 85;

1701514495594

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

代码

方式1：where

select
	s.name, sc.score
from sc
join course c on c.id = sc.c_id
join student s on s.id = sc.s_id
where c.name = '数学' and sc.score < 60;

方式2：on

select
	s.name, sc.score
from sc
join course c on c.id = sc.c_id and c.name = '数学' and sc.score < 60
join student s on s.id = sc.s_id

1701514843362

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

代码

SELECT
	s.id,
	s.name,
	t.01 as "数学",
	t.02 as "语文",
	t.03 as "英语"
from student s
left join 
(
	select
		s_id,
		sum(if(c_id = '01', score, 0)) as '01',
		sum(if(c_id = '02', score, 0)) as '02',
		sum(if(c_id = '03', score, 0)) as '03'
	from sc
	group by s_id
) t on t.s_id = s.id

思路

1.根据s_id分组，筛选不同的课程，因为c_id和socre都不是分组字段，所以得配合分组函数sum处理

select
	s_id,
	sum(if(c_id = '01', score, 0)) as '01',
	sum(if(c_id = '02', score, 0)) as '02',
	sum(if(c_id = '03', score, 0)) as '03'
from sc
group by s_id

1701516322729

2.由于不能忽略没选课的学生，所以使用左连接（student左，上面的表右）

SELECT
	s.id,
	s.name,
	t.01 as "数学",
	t.02 as "语文",
	t.03 as "英语"
from student s
left join 
(
	select
		s_id,
		sum(if(c_id = '01', score, 0)) as '01',
		sum(if(c_id = '02', score, 0)) as '02',
		sum(if(c_id = '03', score, 0)) as '03'
	from sc
	group by s_id
) t on t.s_id = s.id

结果

1701516648972

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

代码

select 
	s.name, c.name, sc.score
from sc
join student s on s.id = sc.s_id
join course c on c.id = sc.c_id
where sc.score > 70;

1701567681116

35.查询不及格的课程

代码

select 
	s.id
	s.name,
	c.id,
	c.name,
	sc.score
from sc
join student s on s.id = sc.s_id
join course c on c.id = sc.c_id
where sc.score < 60;

1701567874403

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

代码

select
	s_id,name, score
from sc
join student on id = s_id
where c_id = '01' and score >= 80;

1701568211477

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

代码

select
 c_id, count(c_id)
from sc
group by c_id;

1701568280160

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

代码

SELECT
	id, name, c_id, score
FROM sc
join student on id = s_id
where c_id = 
	(select id from course where t_id = ((select id from teacher where name = '张三')))
order by score desc
limit 1;

思路

SELECT
	id, name, c_id, score
FROM sc
join student on id = s_id
where c_id = 
	(select id from course where t_id = ((select id from teacher where name = '张三')))
order by score desc;

1701568647554

then

SELECT
	id, name, c_id, score
FROM sc
join student on id = s_id
where c_id = 
	(select id from course where t_id = ((select id from teacher where name = '张三')))
order by score desc
limit 1;

1701568680874

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

代码

select 
	t.name, sc.*
from sc,
(
	SELECT
		id, name, c_id, score
	FROM sc
	join student on id = s_id
	where c_id = 
		(select id from course where t_id = ((select id from teacher where name = '张三')))
	order by score desc
	limit 1
) t
where sc.score = t.score and sc.c_id = t.c_id;

1701571201799

思路

把38题当作39题的标量子查询

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

41.查询每门课程成绩最好的前两名

代码

set @sort := 0;
set @cId := 0;

SELECT
	s.id,
	s.name,
	t.*
from student s
join
	(
		select 
			sc.*,
			if(@cId = c_id, @sort := @sort + 1 , @sort := 1) as sort,
			@cId := c_id
		from sc
		-- 这里sort在select上，无法用where和having过滤sort < 3，只能嵌套一层或联表
		ORDER BY c_id, score desc
	) t on t.s_id = s.id and t.sort < 3

思路

排序、并设置变量处理排名

set @sort := 0;
set @cId := 0;

select 
	sc.*,
	if(@cId = c_id, @sort := @sort + 1 , @sort := 1),
	@cId := c_id
from sc
ORDER BY c_id, score

1701610588588

条件过滤符合题目的记录

– 这里sort在select上，无法用where和having过滤sort < 3，只能嵌套一层或联表

set @sort := 0;
set @cId := 0;

SELECT
	s.id,
	s.name,
	t.*
from student s
join
	(
		select 
			sc.*,
			if(@cId = c_id, @sort := @sort + 1 , @sort := 1) as sort,
			@cId := c_id
		from sc
		-- 这里sort在select上，无法用where和having过滤sort < 3，只能嵌套一层或联表
		ORDER BY c_id, score desc
	) t on t.s_id = s.id and t.sort < 3

1701611101682

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

代码

SELECT
	c_id, count(*) as count
FROM sc
group by c_id
having count > 5;

1701611862165

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

代码

select
	id, name, count(c_id) as count
from sc
join student on id = s_id
group by s_id
having count > 1;

1701614261210

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

代码

-- 这里用变量代替子查询
set @count := (select count(*) from course);

select 
	id,name, count
from student 
JOIN
	(
		  select
				s_id,count(c_id) as count
			from sc
			group by s_id
			having count = @count
	) t on id = t.s_id;

1701614780272

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

代码

-- 方式1 substr()
select 
	id, name, (year(now()) - substr(age,1,4)) as age
from student;

--  方式2 date_format()
select 
	id, name, (year(now()) - date_format(age,'%Y')) as age
from student;

1701615565956

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

代码

select 
	id,
  name,
  (year(now()) - substr(age,1,4)) - 1 + if(substr('2003-03-11', 6,5) > substr('2001-06-05', 6,5), 1, 0) as age
from student;

思路

1 剪切比较

select (substr('2003-03-11', 6,5) > substr('2001-02-05', 6,5))
union
select (substr('2003-03-11', 6,5) > substr('2001-06-05', 6,5))

1701617000240

2 使用 if()拼接

select 
	id,
  name,
  (year(now()) - substr(age,1,4)) - 1 + if(substr('2003-03-11', 6,5) > substr('2001-06-05', 6,5), 1, 0) as age
from student;

↓

select 
	id,
  name,
  (year(now()) - substr(age,1,4)) - if(substr(now(), 6,5) < substr(age, 6,5), 1, 0) as age
from student;

1701617354107

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

代码

SELECT
	*
FROM student
where 
	week(age) = week(now());

1701618814286

【注意】week()函数的结果不同的年份可能出现不一致，甚至同一天，所以感觉上面的解法不严谨

下面的尝试也不能选出所有的答案，这个需要大家解决

SELECT
	*
FROM student
where 
	week(age) = week(concat(substr(age, 1, 5), substr(now(), 6)));

1701619030771

预期： 1701619107298 1701619075703

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

date_add()方法

-- 方式1
SELECT
	*
FROM student
where 
	week(age) = week(now()) + 1;
-- 方式2
SELECT
	*
FROM student
where 
	week(age) = week(date_add(now(),INTERVAL 1 WEEK));

1701619189758

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

代码

SELECT
	*
FROM student
where 
	 MONTH(age) = MONTH(now());

1701619372344

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

代码

date_add()方法

SELECT
	*
FROM student
Where 
	 MONTH(age) = MONTH(date_add(now(),INTERVAL 1 MONTH));

1701619435890

结束~~~~

– 方式2 date_format()
select
id, name, (year(now()) - date_format(age,‘%Y’)) as age
from student;


[外链图片转存中...(img-UXHYNBW9-1702908948844)]



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

### 代码

```sql
select 
	id,
  name,
  (year(now()) - substr(age,1,4)) - 1 + if(substr('2003-03-11', 6,5) > substr('2001-06-05', 6,5), 1, 0) as age
from student;

思路

1 剪切比较

select (substr('2003-03-11', 6,5) > substr('2001-02-05', 6,5))
union
select (substr('2003-03-11', 6,5) > substr('2001-06-05', 6,5))

[外链图片转存中…(img-NFsQbues-1702908948844)]

2 使用 if()拼接

select 
	id,
  name,
  (year(now()) - substr(age,1,4)) - 1 + if(substr('2003-03-11', 6,5) > substr('2001-06-05', 6,5), 1, 0) as age
from student;

↓

select 
	id,
  name,
  (year(now()) - substr(age,1,4)) - if(substr(now(), 6,5) < substr(age, 6,5), 1, 0) as age
from student;

[外链图片转存中…(img-FpiXHUtP-1702908948845)]

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

代码

SELECT
	*
FROM student
where 
	week(age) = week(now());

[外链图片转存中…(img-B29aRTwE-1702908948845)]

【注意】week()函数的结果不同的年份可能出现不一致，甚至同一天，所以感觉上面的解法不严谨

下面的尝试也不能选出所有的答案，这个需要大家解决

SELECT
	*
FROM student
where 
	week(age) = week(concat(substr(age, 1, 5), substr(now(), 6)));

[外链图片转存中…(img-2Ci2ao0B-1702908948846)]

预期：[外链图片转存中…(img-zO81nAD6-1702908948846)][外链图片转存中…(img-8yj4Rv6F-1702908948847)]

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

date_add()方法

-- 方式1
SELECT
	*
FROM student
where 
	week(age) = week(now()) + 1;
-- 方式2
SELECT
	*
FROM student
where 
	week(age) = week(date_add(now(),INTERVAL 1 WEEK));

[外链图片转存中…(img-DwmU1Dq3-1702908948847)]

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

代码

SELECT
	*
FROM student
where 
	 MONTH(age) = MONTH(now());

[外链图片转存中…(img-T1Eet7dC-1702908948848)]

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

代码

date_add()方法

SELECT
	*
FROM student
Where 
	 MONTH(age) = MONTH(date_add(now(),INTERVAL 1 MONTH));

[外链图片转存中…(img-i72axsmH-1702908948848)]

结束~~~~

饶雨涛

关注

18
点赞
踩
18

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

MySql 50题

sql题50道数据：

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

代码：

思路：

2.查询学生同时存在" 01 “课程和” 02 "课程的情况

代码：

思路

3.查询学生存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

代码

思路

4.查询学生不存在" 01 “课程但存在” 02 "课程的情况

代码

思路

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

代码

思路

6.查询在 SC 表存在成绩的学生信息

代码

思路

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为 null

代码

思路

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

代码

思路

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

代码

思路（疯狂的“子查询”）

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

代码

思路

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

代码

思路

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

代码

思路

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

代码

思路

14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩(保留2位小数)

代码

思路

注意事项

15.检索" 01 "课程分数小于 60，按分数降序排列的学生信息（姓名、分数）

代码

思路

16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(保留两位小数)

代码

思路

代码

思路

18.按各科平均成绩进行排序，并显示排名

代码

思路

三种常见的排名

ROW_NUMBER

DENSE_RANK

RANK

ROW_NUMBER

DENSE_RANK

RANK

19.按各科平均成绩进行排序，并显示排名，重复时不保留名次空缺

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

代码

思路

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

代码

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

代码

23.查询各科成绩前三名的记录

代码

思路

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

代码

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

代码

26.查询男生、女生人数

代码

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