MySQL经典50题

Zhang Wenhao

于 2023-10-06 15:41:31 发布

阅读量194

点赞数

本文链接：https://blog.csdn.net/qq_42856609/article/details/131623055

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

23 MySQL中case when的用法

SELECT c_id, c_name, COUNT(1) AS '总人数', COUNT(CASE WHEN s_score BETWEEN 85 AND 100+1 THEN 1 ELSE NULL END) AS '85-100人数', ROUND(COUNT(CASE WHEN s_score BETWEEN 85 AND 100+1 THEN 1 ELSE NULL END)/COUNT(1), 2) AS '85-100百分比',
COUNT(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE NULL END) AS '70-84人数', ROUND(COUNT(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE NULL END)/COUNT(1), 2) AS '70-84百分比',
COUNT(CASE WHEN s_score BETWEEN 60 AND 69 THEN 1 ELSE NULL END) AS '60-69人数', ROUND(COUNT(CASE WHEN s_score BETWEEN 60 AND 69 THEN 1 ELSE NULL END)/COUNT(1), 2) AS '60-69百分比',
COUNT(CASE WHEN s_score BETWEEN 0 AND 59 THEN 1 ELSE NULL END) AS '0-59人数', ROUND(COUNT(CASE WHEN s_score BETWEEN 0 AND 59 THEN 1 ELSE NULL END)/COUNT(1), 2) AS '0-59百分比'
FROM course 
JOIN score USING(c_id)
GROUP BY c_id, c_name;

在这里插入图片描述
33 MySQL中having

-- 1 having 可以跟 别名
-- 2 where理解为行筛选，having理解为分组筛选
SELECT s_id, s_name, ROUND(AVG(s_score), 1) AS avg_score
FROM student
JOIN score USING(s_id)
GROUP BY s_id, s_name
HAVING avg_score>=85;

34 MySQL中多表联结

# 表和表联结前后顺序无关紧要，表之间有对应的外键就可以
SELECT c_name, s_name, s_score
FROM student s
JOIN score sc USING(s_id)
JOIN course c USING(c_id)
WHERE c_name='数学' AND s_score<60;

35 MySQL中列转行

SELECT s_id, s_name, 
SUM(CASE WHEN c_name='语文' THEN s_score ELSE 0 END) AS '语文',
SUM(CASE WHEN c_name='数学' THEN s_score ELSE 0 END) AS '数学',
SUM(CASE WHEN c_name='英语' THEN s_score ELSE 0 END) AS '英语'
FROM student 
JOIN score USING (s_id)
JOIN course USING (c_id)
GROUP BY s_id, s_name;

在这里插入图片描述

SELECT s_id, s_name, 
SUM(CASE WHEN c_name='语文' THEN s_score ELSE NULL END) AS '语文',
SUM(CASE WHEN c_name='数学' THEN s_score ELSE NULL END) AS '数学',
SUM(CASE WHEN c_name='英语' THEN s_score ELSE NULL END) AS '英语'
FROM student 
JOIN score USING (s_id)
JOIN course USING (c_id)
GROUP BY s_id, s_name;

在这里插入图片描述

38 MySQL中where和and用法

SELECT s.s_id, s_name
FROM student s
JOIN score sc
ON s.s_id=sc.s_id
WHERE c_id='01' AND s_score>=80;

39 MySQL中group by 分组用法

SELECT c_name, c.c_id, COUNT(1) AS 人数
FROM course c
LEFT JOIN score s
USING(c_id)
GROUP BY c_name, c.c_id;

40 MySQL中多个子查询

SELECT s.s_id, s_name, s_brith, s_sex, s_score
FROM student s 
JOIN score sc USING(s_id)
WHERE (c_id, s_score) IN(
	SELECT c_id, MAX(s_score)
	FROM score 
	JOIN course USING(c_id)
	JOIN teacher USING(t_id)
	WHERE t_name='张三'
	GROUP BY c_id
);