MySQL -- 06_最流行的查询需求分析（解释在case when 前面加max()函数的原因、一些函数使用、分组排序查询等）

最流行的查询需求分析06

演示数据准备的SQL

演示数据准备的SQL

需求演示

26、查询每门课程被选修的学生数

case when 写法和普通表连接写法

-- 26、查询每门课程被选修的学生数 

-- 写法1：========================

SELECT
	sum( CASE WHEN c_id = '01' THEN 1 ELSE 0 END ) '语文',
	sum( CASE WHEN c_id = '02' THEN 1 ELSE 0 END ) '数学',
	sum( CASE WHEN c_id = '03' THEN 1 ELSE 0 END ) '英语' 
FROM
	score
	

-- 写法2：========================
SELECT
	sc.c_id,
	co.c_name,
	count( sc.c_id ) '选修人数' 
FROM
	score sc
	LEFT JOIN course co ON sc.c_id = co.c_id 
GROUP BY
	sc.c_id

27、查询出只有两门课程的全部学生的学号和姓名

group by + having + count 的普通写法

-- 27、查询出只有两门课程的全部学生的学号和姓名  

SELECT
	st.s_id,
	st.s_name 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
	st.s_id 
HAVING
	count( sc.c_id ) = 2

28、查询男生、女生人数

简单的三种写法：

-- 28、查询男生、女生人数

-- 写法1：

SELECT
	sum(case when s_sex = '男' then 1 else 0 end) '男生人数',
	sum(case when s_sex = '女' then 1 else 0 end) '女生人数'
FROM
	student
	

-- 写法2：
SELECT
	t1.boy,
	t2.girl 
FROM
	( SELECT count( s_sex ) boy FROM student WHERE s_sex = '男' ) t1
	JOIN ( SELECT count( s_sex ) girl FROM student WHERE s_sex = '女' ) t2



-- 写法3：

SELECT
	s_sex,
	count( s_id ) 
FROM
	student 
GROUP BY
	s_sex

29、查询名字中含有"风"字的学生信息

like 模糊查询

-- 29、查询名字中含有"风"字的学生信息

SELECT
	* 
FROM
	student 
WHERE
	s_name LIKE '%风%'

30、查询同名同性别学生名单，并统计同名人数

创建临时表并插入演示数据

简单的group by + having

-- 30、查询同名同性别学生名单，并统计同名人数
-- 创建临时表
CREATE TEMPORARY TABLE temp_students (
    s_id INT PRIMARY KEY,
    s_name VARCHAR(255),
    s_birth VARCHAR(255),
    s_sex VARCHAR(255)
);

-- 插入数据
-- INSERT INTO ... SELECT 是一种从一个表复制数据到另一个表的方法
INSERT INTO temp_students ( s_id, s_name, s_birth, s_sex )
SELECT
s_id,
s_name,
s_birth,
s_sex 
FROM
	student

-- 插入同名同性别的数据
INSERT INTO temp_students ( s_id, s_name, s_birth, s_sex )
VALUES
	( '9', '赵雷' ,'1990-01-01', '男' ),
	( '10', '钱电', '1990-01-01' ,'女' )


-- 查询数据
SELECT * FROM temp_students 

-- 查询同名同性别学生名单，并统计同名人数

SELECT
	s_name,
	s_sex,
	count( s_name ) 
FROM
	temp_students 
GROUP BY
	s_name,
	s_sex 
HAVING
	count( s_name ) > 1

31、查询1990年出生的学生名单

使用 year() 函数提取年份部分

-- 31、查询1990年出生的学生名单 

-- like 写法

select * from student where s_birth like '1990%'

-- YEAR() 函数用于从日期中提取年份部分

select * from student where year(s_birth) = 1990

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

普通分组、排序查询

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
	co.c_name,
	round( avg( sc.s_score ), 2 ) 
FROM
	score sc
	LEFT JOIN course co ON co.c_id = sc.c_id 
GROUP BY
	sc.c_id 
ORDER BY
	round( avg( sc.s_score ), 2 ) DESC, sc.c_id ASC

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

group by … having

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
SELECT
	st.s_id,
	st.s_name,
	IFNULL(round(avg( sc.s_score )),0) '平均成绩'
FROM
	score sc
	RIGHT JOIN student st ON st.s_id = sc.s_id 
GROUP BY 
  sc.s_id
HAVING
	round(avg( sc.s_score )) >= 85

34、查询课程名称为"数学”，且分数低于60的学生姓名和分数

普通表连接

-- 34、查询课程名称为"数学”，且分数低于60的学生姓名和分数

SELECT
	st.s_name,
	sc.s_score 
FROM
	course co
	LEFT JOIN score sc ON co.c_id = sc.c_id
	LEFT JOIN student st ON st.s_id = sc.s_id 
WHERE
	co.c_name = "数学" 
	AND sc.s_score < 60

35、查询所有学生的课程及分数情况

使用 max()+ case when + 子查询 实现需求

解释为何要在case when 前面加上max()函数。

-- 35、查询所有学生的课程及分数情况
select 
	t.s_name,
	max(case when t.c_id = '01' then t.s_score else 0 end) '语文',
	max(case when t.c_id = '02' then t.s_score else 0 end) '数学',
	max(case when t.c_id = '03' then t.s_score else 0 end) '英语'
from
(select st.s_name,sc.* from student st left join score sc on st.s_id = sc.s_id) t
GROUP BY t.s_id