MySQL -- 01_最流行的查询需求分析（内连接、inner join 三表联结，group by、case when ，子查询、聚合函数、子句）附带演示数据的sql

_L_J_H_

已于 2024-03-29 00:31:14 修改

阅读量334

点赞数 4

分类专栏： MySQL 文章标签： mysql sql 数据库

于 2024-03-28 02:00:31 首次发布

本文链接：https://blog.csdn.net/weixin_44411039/article/details/137090870

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

MySQL – 最流行的查询需求分析（内连接、inner join 三表联结，group by、case when ，子查询）持续更新中~

数据准备：

在这里插入图片描述

创建表sql

只是简单的演示，就没有搞什么主键和限制之类的。
需要的后续可以添加。
可视化工具使用的是Navicat。

-- 数据准备
-- 创建表命令==========================================================
-- 创建学生表
CREATE TABLE student ( 
	s_id   VARCHAR (50) COMMENT '学生编号',
	s_name VARCHAR (50) COMMENT '学生姓名', 
	s_birth DATE COMMENT '出生年月', 
	s_sex  VARCHAR (50)  COMMENT '性别'
);

-- 创建课程表
CREATE TABLE course ( 
	c_id   VARCHAR (50) COMMENT '课程编号',
	c_name VARCHAR (50) COMMENT '课程名称', 
	t_id   VARCHAR (50) COMMENT '教师编号'
);

-- 创建教师表
CREATE TABLE teacher ( 
	t_id   VARCHAR (50) COMMENT '教师编号',
	t_name VARCHAR (50) COMMENT '教师姓名'
);

-- 创建成绩表
CREATE TABLE score ( 
	s_id    VARCHAR (50) COMMENT '学生编号',
	c_id    VARCHAR (50) COMMENT '课程编号',
	s_score VARCHAR (20) COMMENT '分数'
);

添加表数据sql

-- 添加表数据 ==========================================================
-- 添加学生表数据
INSERT INTO student ( s_id, s_name, s_birth, s_sex )
VALUES
	( '01', '赵雷', '1990-01-01', '男' ),
	( '02', '钱电', '1990-12-21', '男' ),
	( '03', '孙风', '1990-05-20', '男' ),
	( '04', '李云', '1990-08-06', '男' ),
	( '05', '周梅', '1991-12-01', '女' ),
	( '06', '吴兰', '1992-03-01', '女' ),
	( '07', '郑竹', '1989-07-01', '女' ),
	( '08', '王菊', '1990-01-20', '女' );

-- 添加课程表数据
INSERT INTO course (c_id, c_name, t_id )
VALUES
	( '01', '语文', '02' ),
	( '02', '数学', '01' ),
	( '03', '英语', '03' );
	
-- 添加教师表数据
INSERT INTO teacher ( t_id, t_name )
VALUES
	( '01', '张三'),
	( '02', '李四'),
	( '03', '王五');

-- 添加成绩表数据
INSERT INTO score ( s_id, c_id, s_score )
VALUES
	( '01', '01', 80),
	( '01', '02', 90),
	( '01', '03', 99),
	( '02', '01', 70),
	( '02', '02', 60),
	( '02', '03', 80),
	( '03', '01', 80),
	( '03', '02', 80),
	( '03', '03', 80),
	( '04', '01', 50),
	( '04', '02', 30),
	( '04', '03', 20),
	( '05', '01', 76),
	( '05', '02', 87),
	( '06', '01', 31),
	( '06', '03', 34),
	( '07', '02', 89),
	( '07', '03', 98);

需求：

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

1-1：自连接（隐式内连接）

内连接可以通过使用 INNER JOIN 关键字或者简写形式 JOIN 来实现。

内连接（Inner Join） 是 SQL 中最常见的连接类型之一，用于根据两个表之间的共同字段将它们连接起来，并返回满足连接条件的行。

在内连接中，只有在连接条件成立时（即连接字段在两个表中具有匹配值时），才会返回符合条件的行。如果没有匹配的行，则不会包括在结果集中

在这里插入图片描述


-- 1、查询 “01” 语文成绩比 “02” 数学成绩高的学生的信息及课程分数

-- 涉及到 student、score 这两张表

SELECT
	s.*,
	s1.s_score AS '语文成绩',
	s2.s_score '数学成绩' 
FROM
	score s1,-- 隐式内连接的写法来连接这三个表
	score s2,
	student s 
WHERE
	s1.c_id = '01' -- 查询score表的语文成绩
	AND s2.c_id = '02' -- 查询score表的数学成绩
	AND s1.s_id = s2.s_id -- 进行表连接
	AND s1.s_score > s2.s_score -- 进行成绩判断
	AND s.s_id = s1.s_id -- 关联student表的条件

1-2：GROUP BY、CASE WHEN 使长型数据变宽型数据

长型数据变宽型数据，
就是原本每个学生会查出两列数据（语文成绩一行，数学成绩一行），这就属于长型数据。
通过分组给需要的数据给弄成一行显示，就是宽型数据。

如图：

在这里插入图片描述

完整查询：

在这里插入图片描述

-- 长型数据变宽型数据写法

SELECT
	s.*,
	t.s01 '语文成绩',
	t.s02 '数学成绩' 
FROM
	(
	SELECT
		s.s_id,
		-- 	如果 s.c_id = '01' ，则返回 s.s_score ，否则返回null（else null可省略）
		max( CASE WHEN s.c_id = '01' THEN s.s_score ELSE NULL END ) AS s01,
		max( CASE WHEN s.c_id = '02' THEN s.s_score ELSE NULL END ) AS s02 
	FROM
		score s 
	GROUP BY
		s.s_id 
	) t
	LEFT JOIN student s ON s.s_id = t.s_id 
WHERE
	t.s01 > t.s02

1-3：三表联结（inner join）

在这里插入图片描述

SELECT
	st.*,
	s1.s_score AS '01语文成绩',
	s2.s_score AS '02数学成绩' 
FROM
	student AS st
	INNER JOIN ( SELECT * FROM score WHERE c_id = '01' ) AS s1 ON st.s_id = s1.s_id
	INNER JOIN ( SELECT * FROM score WHERE c_id = '02' ) AS s2 ON s1.s_id = s2.s_id 
	AND s1.s_score > s2.s_score;

1-4：子查询写法

在这里插入图片描述


select t1.s_name ,t1.c_name, t1.s_score, t2.c_name,  t2.s_score from 
(
-- 查询所有学生的数学成绩 01
select st.s_name , st.s_id, c.c_name, sc.s_score  from student st 
left join score sc on  st.s_id = sc.s_id
left join course c on c.c_id = sc.c_id
where sc.c_id = '01' 

) t1 
join 
(
-- 查询所有学生的语文成绩 02
select st.s_name, st.s_id, c.c_name, sc.s_score  from student st 
left join score sc on  st.s_id = sc.s_id
left join course c on c.c_id = sc.c_id
where sc.c_id = '02'
) t2

on t1.s_id = t2.s_id  

where t1.s_score > t2.s_score

2、查询 "01语文课程"比"02数学课程"成绩低的学生的信息及课程分数

和第一道题一样

内连接写法

在这里插入图片描述

-- 2、查询 "01语文课程"比"02数学课程"成绩低的学生的信息及课程分数
SELECT
	sc.*,
	s1.c_id '语文课程编号',
	s1.s_score '语文成绩',
	s2.c_id '数学课程编号',
	s2.s_score '数学成绩' 
FROM
	score s1,
	score s2,
	student sc 
WHERE
	s1.s_id = s2.s_id -- 表的内连接
	AND s1.c_id = '01' -- 表示s1这张表查询出来的是“01”语文成绩
	AND s2.c_id = '02' -- 表示s2这张表查询出来的是“02”数学成绩
	AND s1.s_score < s2.s_score -- 查询语文成绩比数学成绩低的条件
	AND s1.s_id = sc.s_id -- 连接学生表

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

聚合函数和子句的解释

MySQL 的一些聚合函数：

COUNT(): 用于计算行数或非空值的数量。
SUM(): 用于计算数值列的总和。
AVG(): 用于计算数值列的平均值。
MAX(): 用于获取数值列的最大值。
MIN(): 用于获取数值列的最小值。

子句解释：

聚合函数大部分情况下需要和子句一起搭配使用：

GROUP BY 和 HAVING 在 SQL 中被称为子句（clause）；

子句是 SQL 查询语句中的组成部分，用于指定如何筛选、分组或排序数据。这些子句帮助我们更精确地从数据库中检索所需的数据，并对数据进行聚合、过滤和排序

具体来说：

GROUP BY 子句用于将行分组为汇总行，通常与聚合函数一起使用，用于对分组后的数据进行聚合计算。

HAVING 子句通常与 GROUP BY 子句一起使用，在分组后对数据进行筛选，类似于 WHERE 子句对行数据进行筛选，但 HAVING 对分组后的结果进行筛选。

三种取平均数的演示

– AVG() 函数用于取平均数
– ROUND() 函数用于将数值四舍五入到指定的小数位数
– FLOOR() 函数会返回不大于给定参数的最大整数值

在这里插入图片描述

SELECT
	avg(s_score),
	ROUND(avg(s_score),1), -- 取小数点后1位数
	ROUND(avg(s_score),2), -- 取小数点后2位数
	FLOOR(avg(s_score))
FROM
	score
group by s_id

3-1：普通的两个表连接

在这里插入图片描述

SELECT
	st.*,
	ROUND( avg( sc.s_score ), 1 ) '分数' -- 取小数点后1位数
FROM
	score sc
	LEFT JOIN student st ON sc.s_id = st.s_id 
GROUP BY
	sc.s_id 
HAVING
	ROUND( avg( sc.s_score ), 1 ) >= 60

注意：

不能在 WHERE 子句中直接使用聚合函数的结果。在这种情况下，应该使用 HAVING 子句来筛选分组后的数据。

在这里插入图片描述

3-2：子查询写法

在查询字段的位置中，添加这个子查询的作用是动态地根据主查询中的每个学生 ID，在 student 表中查找对应的学生姓名，并将这个学生姓名作为一个查询字段返回给用户

在这个查询中，首先是从 score 表开始获取数据，然后才会执行子查询来获取对应的学生姓名

select * from score sc 这个就是主查询

在这里插入图片描述

-- 子查询
SELECT
	sc.s_id,
	-- 这个子查询的作用是动态地根据主查询中的每个学生 ID，在 student 表中查找对应的学生姓名，并将这个学生姓名作为一个查询字段返回给用户
	-- 在这个查询中，首先是从 score 表开始获取数据，然后才会执行子查询来获取对应的学生姓名
	(select st.s_name from	student st where sc.s_id = st.s_id ) '学生姓名',
	ROUND( avg( sc.s_score ), 1 ) '分数' -- ROUND() 函数用于取平均值的小数点后1位数
FROM
	score sc
GROUP BY
	sc.s_id 
HAVING
	ROUND( avg( sc.s_score ), 1 ) >= 60

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

用到 IFNULL()、ROUND()、avg()这些聚合函数，用 right join 右连接

IFNULL( ROUND( AVG( sc.s_score ), 1 ), 0 ) 解释：

ifnull(xxx，0) 函数：如果返回值的xxx为null ，则返回0
round(xxx，1) 函数：取xxx值小数点后一位数
avg(xxx)：取xxx的平均数

如图，如果是left join 左连接，就是以 score 这张成绩表为主，
如果是 right join 右连接，则以 student 这张学生表为主。

如图：右连接是为了把【08王菊】也显示出来，因为score 成绩表里面没有这个人的成绩。

在这里插入图片描述

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT
	st.*,
	IFNULL( ROUND( AVG( sc.s_score ), 1 ), 0 ) '分数' -- ROUND() 函数用于取小数点后1位数
	
FROM
	score sc
	RIGHT JOIN student st ON sc.s_id = st.s_id  -- 右连接，就是连接student这张表
GROUP BY
	sc.s_id 
HAVING
	IFNULL( ROUND( avg( sc.s_score ), 1 ), 0 ) < 60  -- having 后面写条件判断

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

用到 count() 、IFNULL()、sum() 函数

count() 函数：计算行数
sum() 函数：计算总数
ifnull(xxx,0) 函数：如果返回的xxx字段的值为null，则返回0

在这里插入图片描述

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT
	st.s_id,
	st.s_name,
	count( sc.c_id ) '选课总数',
	IFNULL(sum( sc.s_score ),0) '总成绩'  -- 如果成绩为null，则返回0
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	sc.s_id

_L_J_H_

关注

4
点赞
踩
10

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL -- 01_最流行的查询需求分析（内连接、inner join 三表联结，group by、case when ，子查询、聚合函数、子句）附带演示数据的sql

MySQL -- 01_常见查询需求分析（内连接、inner join 三表联结，group by、case when ，子查询）附带数据准备sql--持续更新中~
复制链接

扫一扫