MySQL练习(2)

最新推荐文章于 2022-08-09 22:59:07 发布

CodeBoy975

最新推荐文章于 2022-08-09 22:59:07 发布

阅读量374

点赞数

分类专栏： MySQL 文章标签： MySQL 面试

本文链接：https://blog.csdn.net/weixin_38426554/article/details/98441236

版权

MySQL 专栏收录该内容

5 篇文章 4 订阅

订阅专栏

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

思路：

学全也就是一个同学都要把课程表的所有课程都学了。
学生要到课程表的流程是学生表—成绩表 ---- 课程表，但成绩表就有课程表的c_id，也就是其实我们能够在成绩表这里就可以进行判断了；
将学生表跟成绩表关联能够得出这个学生的成绩即课程的id，然后再以学生id分组，此时出来的数据就是学了对应课程对应的数据(比如2门，则出来学生s1则是两条，那么我们可以统计下学生1的总和多少，即用count来算出)，这个时候count就是学生1学的课程数；
既然count得出的数据是学生课程总数，那么我们获取课程表的总数,只要小于课程表总数，结果就出来了

自己解法：
SELECT
	st.*
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
	st.s_id 
HAVING
	count( sc.c_id )  < (select count(1) from course )

网上版本：

select *
from student
where s_id not in(
select s_id from score t1  
group by s_id having count(*) =(select count(distinct c_id)  from course))

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

思路：至少有一门，也就是所有课程要在一个范围内；而这个范围就是01同学所有的课程，也就是要查出这个课程id的范围

SELECT
	st.* 
FROM
	student st
	INNER JOIN score sc ON sc.s_id = st.s_id 
WHERE
	sc.c_id IN ( SELECT sc.c_id FROM score sc WHERE sc.s_id = "01" ) 
GROUP BY
st.s_id

网上版本：

SELECT
	* 
FROM
	student 
WHERE
	s_id IN (
SELECT DISTINCT
	a.s_id 
FROM
	score a 
WHERE
	a.c_id IN ( SELECT a.c_id FROM score a WHERE a.s_id = '01' ) 
	);

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

思路：
法一:

首先说一下这个函数：group_concat()，手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果。

可以测试一下这个函数的作用，比如语句：

SELECT GROUP_CONCAT( sc.c_id ) AS c_id, sc.s_id FROM score sc GROUP BY sc.s_id

也就是会按分组后的结果拼接成字符串变成一列；那么我们可以再弄一个为01时候拼接起来这个字符串，如下sql：

select * from 
	(SELECT GROUP_CONCAT( sc.c_id ) AS c_id, sc.s_id FROM score sc GROUP BY sc.s_id ) t1
	  INNER JOIN ( SELECT GROUP_CONCAT( sc.c_id ) AS c_id1 FROM score sc WHERE s_id = "01" GROUP BY sc.s_id ) t2

那么我们只要加上条件为c_id = c_id1即可出来我们要的帅选条件，那么我们要的是学生信息，则整合起来就如下
结果：

SELECT
	st.* 
FROM
	student st 
WHERE
	s_id IN (
SELECT
	t1.s_id 
FROM
	( SELECT GROUP_CONCAT( sc.c_id ) AS c_id, sc.s_id FROM score sc GROUP BY sc.s_id ) t1
	  INNER JOIN ( SELECT GROUP_CONCAT( sc.c_id ) AS c_id1 FROM score sc WHERE s_id = "01" GROUP BY sc.s_id ) t2 
    WHERE
	        c_id = c_id1 
	)
and s_id != "01"

法二：
逐步过滤出来
步骤：

既然是相同，那么课程总数也应该相同，则我们先过滤掉课程总数不同的
课程总数相同的前提下，再过滤掉选了学生A没有选择的课程的同学
剩下就是和学生A相同的，则在结果中去掉A同学

SELECT
	st.* 
FROM
	student st 
WHERE
-- 过滤出课程总数相同
	st.s_id IN (
		SELECT
			sc.s_id 
		FROM
			score sc 
		GROUP BY
			sc.s_id 
		HAVING
			COUNT( sc.c_id ) = ( SELECT count( * ) FROM score WHERE s_id = "01" ) 
	) -- 过滤掉学生01没有选的课程
	
	AND st.s_id NOT IN (
		SELECT DISTINCT
			sc.s_id 
		FROM
			score sc 
		WHERE
			sc.c_id NOT IN ( SELECT sc.c_id FROM score sc WHERE sc.s_id = "01" ) 
	) -- 过滤自己
	
	AND st.s_id != "01"

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

思路：首先，我们思考的肯定是那么张三老师讲过哪些课程呢？如果我们能找到张三老师讲的课程，即课程表关联教师表可以满足这个要求找到课程c_id，因为学生表关联的只有成绩表，所以我们找一下在成绩表中有多少数据是等于我们找到的c_id,得出的数据也就是张三老师所教的学生，那么所教的学生都选出来了。没学过他教的就自然懂了；
步骤：

找出哪些课程是张三老师教的，这个作为临时结果
课程表去关联上面得出临时表并筛选出成绩表中的c_id = 我们在步骤1得出的结果，从而得出老师教的学生id
学生表关联上面的结果，使用not in即可得出结果

SELECT
	st.s_name 
FROM
	student st 
WHERE
	st.s_id NOT IN (
	SELECT
		sc.s_id 
	FROM
		score sc 
	WHERE
		sc.c_id IN (
		SELECT
			sc.c_id AS c_id1 
		FROM
			course sc
		LEFT JOIN teacher t ON t.t_id = sc.t_id 
		WHERE
			t.t_name = "张三" 
		) 
	)

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

思路：不及格，也就是成绩要小于60，那么要去成绩表中进行过滤，并且平均成绩则要用到avg函数，分组则必须是在是要过滤出小于60后再进行分组，得出的结果就是小于60的平均成绩和学号id，那么姓名就在学生表，加个表关联即可
步骤：

在成绩表中过滤出成绩小于60的，然后在剩余的数据进行分组计算平均值，再帅选出大于2门即2门以上的数据
接着是学生的名字，在学生表，而其他数据在第一步骤中有，把它作为临时表进行表关联，用inner join 减少不必要的连接，即可得出结果

SELECT
	st.s_id,
	st.s_name,
	t1.avg_score 
FROM
	student st
	INNER JOIN (
		SELECT
			sc.s_id,
			ROUND( AVG( sc.s_score ) ) AS avg_score 
		FROM
			score sc 
		WHERE
			sc.s_score < 60  GROUP BY sc.s_id HAVING count( sc.c_id ) >= 2 
	) t1 ON t1.s_id = st.s_id

网上解法：其实思路差不多；

SELECT
	a.s_id,
	a.s_name,
	ROUND( AVG( b.s_score ) ) 
FROM
	student a
	LEFT JOIN score b ON a.s_id = b.s_id 
WHERE
	a.s_id IN ( SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING count( 1 ) >= 2 ) 
GROUP BY
	a.s_id,
	a.s_name

16、检索"01"课程分数小于60，按分数降序排列的学生信息

思路：
课程id以及分数都在课程表中有，那么我们可以在课程表中判断得出结果，而学生信息在学生表有，那么进行两个表关联，加上条件，最后进行一个降序排序即可；

SELECT
	st.*,
	sc.s_score 
FROM
	student st
	INNER JOIN score sc ON sc.s_id = st.s_id 
WHERE
	sc.s_score < 60 
	AND sc.c_id = "01" 
ORDER BY
	sc.s_score DESC

网上解法:

SELECT
	a.*,
	b.c_id,
	b.s_score 
FROM
	student a,
	score b 
WHERE
	a.s_id = b.s_id 
	AND b.c_id = '01' 
	AND b.s_score < 60 
ORDER BY
b.s_score DESC;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

最初思路：课程id和成绩都在课程表中有，则我们可以从课程表中获取，平均值则也是课程表的，但要单独弄一张临时表进行关联，也就是得出结果就是每个课程名后面都有平均值，有重复的数据。例如：

然后做出来后看着越发感觉不对，看了下答案。。

发现我将课程名和课程分数全部变成一列了。离答案好像很近又好像很远，但其实那么我们其实只要把列转化为行，结果就对了；
错误解法：

SELECT
	sc.s_id as "学生学号",
	CASE WHEN sc.c_id  = "01" THEN "语文"
	WHEN sc.c_id ="02" THEN "数学"
	WHEN sc.c_id = "03" THEN "英语"
	END AS "课程名",
	sc.s_score as "课程分数",
	t1.avg_score as "平均成绩"
FROM
	  score sc 
	LEFT JOIN ( SELECT sc.s_id, ROUND( avg( sc.s_score ) ) AS avg_score FROM score sc GROUP BY sc.s_id ) t1 ON t1.s_id = sc.s_id 
ORDER BY
	t1.avg_score DESC

行转列：
接下来首先介绍一个函数：Max(CASE … WHEN … THEN)的用法，该用法可以将行转为列，有疑问等下再解答为什么要加MAX这个函数；(CASE … WHEN … THEN)类似与if…else用法；
解法：

自己解法(复杂化了，应该学习网上解法，这里之所有多了表关联，应该是当初没有加上MAX，所以聚合函数无法用，只能用表关联)：
SELECT
	sc.s_id as "学生学号",
	MAX(CASE WHEN sc.c_id  = "01" THEN sc.s_score END ) "语文",
	MAX(CASE WHEN sc.c_id  = "02" THEN sc.s_score END ) "数学",
	MAX(CASE WHEN sc.c_id  = "03" THEN sc.s_score END ) "英语",
	t1.avg_score as "平均成绩"
FROM
	  score sc 
	LEFT JOIN ( SELECT sc.s_id, ROUND( avg( sc.s_score ) ) AS avg_score FROM score sc GROUP BY sc.s_id ) t1 ON t1.s_id = sc.s_id 
group BY
	sc.s_id
order by 
t1.avg_score desc
网上解法：
SELECT
	a.s_id,
	b.s_name ,
	MAX( CASE a.c_id WHEN '01' THEN a.s_score END ) 语文,
	MAX( CASE a.c_id WHEN '02' THEN a.s_score END ) 数学,
	MAX( CASE a.c_id WHEN '03' THEN a.s_score END ) 英语,
	avg( a.s_score ) as avg
FROM
	Score a
	JOIN Student b ON a.s_id = b.s_id 
GROUP BY
	a.s_id 
ORDER BY
	avg DESC

那么对于为什么要加上MAX，我们要MAX去掉看看会发生什么情况

自己解法：
SELECT
	sc.s_id as "学生学号",
	(CASE WHEN sc.c_id  = "01" THEN sc.s_score END ) "语文",
	(CASE WHEN sc.c_id  = "02" THEN sc.s_score END ) "数学",
	(CASE WHEN sc.c_id  = "03" THEN sc.s_score END ) "英语",
	t1.avg_score as "平均成绩"
FROM
	  score sc 
	LEFT JOIN ( SELECT sc.s_id, ROUND( avg( sc.s_score ) ) AS avg_score FROM score sc GROUP BY sc.s_id ) t1 ON t1.s_id = sc.s_id 
order by

t1.avg_score desc

结果：

可以看出红色我标记的，也就是max的作用，其实就是去选其中的最大值；

该题的另外一种解法思路：

SELECT
	a.s_id,
	( SELECT s_score FROM score WHERE s_id = a.s_id AND c_id = '01' ) AS 语文,
	( SELECT s_score FROM score WHERE s_id = a.s_id AND c_id = '02' ) AS 数学,
	( SELECT s_score FROM score WHERE s_id = a.s_id AND c_id = '03' ) AS 英语,
	round( avg( s_score ), 2 ) AS 平均分 
FROM
	score a 
GROUP BY
	a.s_id 
ORDER BY
	平均分 DESC;

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

思路：
首先肯定是先看要的字段来源与哪张表，发现基本都是从课程表以及成绩表中就可以获取。而最高分、最低分、平均分我们都可以用函数来实现，即MAX、MIN、AVG，这里是及格率以及中等率、优良率、优秀率等是怎么求是本题的重点；笔者想了半天。。放弃看答案，结果是用了SUM(case…when)也可以实现，我记得这个是用来进行列转行的时候用到的；没想到这里也可以用上。。。即用SUM函数肯定是要分组的，我们肯定是以课程id分组的，这个时候聚合函数SUM要来统计什么呢，SUM(统计不及格的人数)/SUM(及格人数),即在SUM里面我们要进行一个判断即可，最后得出的数据再乘100；

SELECT
	a.c_id,
	b.c_name,
	MAX( s_score ),
	MIN( s_score ),
	ROUND( AVG( s_score ), 2 ),
	ROUND(
		100 * (
		SUM( CASE WHEN a.s_score >= 60 THEN 1 ELSE 0 END ) / SUM( CASE WHEN a.s_score THEN 1 ELSE 0 END ) 
		),
		2 
	) AS 及格率,
	ROUND(
		100 * (
		SUM( CASE WHEN a.s_score >= 70 AND a.s_score <= 80 THEN 1 ELSE 0 END ) / SUM( CASE WHEN a.s_score THEN 1 ELSE 0 END ) 
		),
		2 
	) AS 中等率,
	ROUND(
		100 * (
		SUM( CASE WHEN a.s_score >= 80 AND a.s_score <= 90 THEN 1 ELSE 0 END ) / SUM( CASE WHEN a.s_score THEN 1 ELSE 0 END ) 
		),
		2 
	) AS 优良率,
	ROUND(
		100 * (
		SUM( CASE WHEN a.s_score >= 90 THEN 1 ELSE 0 END ) / SUM( CASE WHEN a.s_score THEN 1 ELSE 0 END ) 
		),
		2 
	) AS 优秀率 
FROM
	score a
	LEFT JOIN course b ON a.c_id = b.c_id 
GROUP BY
	a.c_id,
	b.c_name

19、按各科成绩进行排序，并显示排名

思路：首先是按各科成绩排序，也就是先不管什么科，我把成绩从高到低进行排序就对了，然后这题主要是怎么进行排名显示呢，这里就又用到了一个用户自定义遍变量的知识点，比如
在开始是定义一个变量i，让它每增一条结果是➕1， @i:=1;
这里顺带复习下mysql定义用户变量的方式：select @变量名
对用户变量赋值有两种方式，一种是直接用"=“号，另一种是用”:="号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用":=“方式，因为在select语句中，”="号被看作是比较操作符
(@i:=@i+1) 也可以写成 @i:=@i+1 ，加括号是为了视觉上看这结构更清楚些。在定义好一个变量后每次查询都会给这个变量自增，而我们每次执行查询语句获取结果后就不需要这个变量自增了，所以要把它重置为0，在表名后用逗号分格下使用 (SELECT @i:=0) as i 就可以了，说下这个as i为什么要这样用，是因为派生表必须需要一个别名，这个就是做它的别名，可以任意字符。
步骤：
1 先将各科成绩按从高到低的顺序进行排序作为一个临时表，因为加上用户自定义的序列号会出现乱号
2 将用户自定义的序列号和这个临时表进行表关联即可

SELECT
	t1.*,
	( @rank := @rank + 1 ) AS rank 
FROM
	(
		SELECT
			st.s_id,
			st.s_name,
			c.c_id,
			c.c_name,
			sc.s_score 
		FROM
			student st
			LEFT JOIN score sc ON sc.s_id = st.s_id
			LEFT JOIN course c ON c.c_id = sc.c_id 
		ORDER BY
			sc.s_score DESC 
	) t1
	INNER JOIN ( SELECT @rank := 0 ) AS i

20、查询学生的总成绩并进行排名

思路：这里也是用到了用户自定义变量，因为要显示排名，所以我们先统计出学生的成绩总数,并按成绩进行排序(order by是最后的执行顺序)，所以我们把这个直接作为临时表，然后再跟用户自定义函数进行表关联即可

自己解法：

SELECT
	t1.*,
	( @i := @i + 1 ) AS rank 
FROM
	(
		SELECT
			st.s_id,
			st.s_name,
			SUM( sc.s_score ) AS sum_score 
		FROM
			student st
			LEFT JOIN score sc ON sc.s_id = st.s_id 
		GROUP BY
			st.s_id 
		ORDER BY
			sum_score DESC 
	) t1
	INNER JOIN ( SELECT @i := 0 ) AS i
网上解法
select a.s_id,
	@i:=@i+1 as i,
	@k:=(case when @score=a.sum_score then @k else @i end) as rank,
	@score:=a.sum_score as score
from (select s_id,SUM(s_score) as sum_score from score GROUP BY s_id ORDER BY sum_score DESC)a,
(select @k:=0,@i:=0,@score:=0)s

CodeBoy975

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL练习(2)

11. 查询没有学全所有课程的同学的信息思路：学全也就是一个同学都要把课程表的所有课程都学了。学生要到课程表的流程是学生表—成绩表 ---- 课程表，但成绩表就有课程表的c_id，也就是其实我们能够在成绩表这里就可以进行判断了；将学生表跟成绩表关联能够得出这个学生的成绩即课程的id，然后再以学生id分组，此时出来的数据就是学了对应课程对应的数据(比如2门，则出来学生s1则是两条，那么我...
复制链接

扫一扫

专栏目录