Mysql经典查询练习题

飞熊 • 2023年3月9日下午9:30 • 技术随笔 • 阅读 161

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

方式一：自连接

SELECT 
	c.*,a.s_score,b.s_score
FROM
	score a, score b ,student c
WHERE 
	a.c_id = '01'
	AND b.c_id = '02'
	AND a.s_id = b.s_id
	AND a.s_id = c.s_id
	AND a.s_score > b.s_score

方式二：长型数据变成宽型数据

SELECT 
	s.*,t.s01,t.s02
FROM
	(SELECT 
		a.s_id,
		MAX(CASE WHEN a.c_id = '01' THEN a.s_score END) s01,
		MAX(CASE WHEN a.c_id = '02' THEN a.s_score END) s02
	FROM
		score a
	GROUP BY
		a.s_id) t, student s
WHERE 
	t.s01 > t.s02
	AND t.s_id = s.s_id

结果一样

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

SELECT 
	a.s_id,s.s_name,
	AVG(a.s_score) 平均成绩
FROM
	score a ,student s
WHERE 
	s.s_id = a.s_id
GROUP BY 
	a.s_id
HAVING 
	AVG(a.s_score) >=60;

结果

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

SELECT 
	s.s_id,s.s_name,
	IFNULL(AVG(a.s_score),0) 平均成绩
FROM
	score a
RIGHT JOIN	
	student s
ON 
	s.s_id = a.s_id
GROUP BY 
	s.s_id
HAVING 
	IFNULL(AVG(a.s_score),0) <60;

结果（通过右外连接把没有成绩的也查询出来了）

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

内连接（取两张表查询到的最少的行数作为查询结果行数）

SELECT 
	a.s_id,
	b.s_name,
	COUNT(a.c_id) 课程总数,
	SUM(a.s_score) 总分
FROM
	score a
INNER JOIN 
	student b
ON
	a.s_id = b.s_id
GROUP BY 
	a.s_id

结果

外连接（左外连接，则是以左边的表的行数为查询结果的行数。

右外连接，则是以右边边的表的行数为查询结果的行数。）

SELECT 
	b.s_id,
	b.s_name,
	COUNT(a.c_id) 课程总数,
	SUM(a.s_score) 总分
FROM
	score a
RIGHT JOIN 
	student b
ON
	a.s_id = b.s_id
GROUP BY 
	b.s_id

结果（把student中没有成绩的学生也查出来了）

处理一下null值

SELECT 
	b.s_id,
	b.s_name,
	COUNT(a.c_id) 课程总数,
	IFNULL(SUM(a.s_score),0) 总分
FROM
	score a
RIGHT JOIN 
	student b
ON
	a.s_id = b.s_id
GROUP BY 
	b.s_id

结果