MySQL学习笔记（6）

这里开始引入mysql的练习题，并且根据题目类型梳理知识点

练习题的来源如下

MySQL练习题参考答案 - 武沛齐 - 博客园导出现有数据库数据： mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据 mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 #https://www.cnblogs.com/wupeiqi/articles/5748496.html首先介绍下练习数据库的基本情况

一共有如图所示的5个数据表，分别存放班级ID，课程信息和任课教师ID，学生成绩，学生信息，教师ID和名称

1. 第四题--连表操作 

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

由于学生姓名和成绩存放在不同表中，所以需要连接学生信息和成绩两张数据表，

指令如下所示

select
id,
name,
choose,
full_score
from(
select count(1) as choose,student_id as id,sum(num) as full_score from score group by student_id
) AS s1
left join(
select sid,sname as name from student
) as s2
on id = s2.sid;

注意需要给要链接的两张表进行临时命名

如果不进行命名的话，就会提示以下报错

Every derived table must have its own alias

 就是说没有给其中的某个子表命名，必须要给所有子表起一个别名才可以

2. 第二题&第二十题--三元语句判断

2. 查询“生物”课程比“物理”课程成绩高的所有学生的学号

首先选出所有学生的生物和物理成绩，然后再根据成绩进行比较筛选

指令如下所示

SELECT
	student_id 
FROM
	(
	SELECT
		s1.student_id,
		s1.num AS sw,
		s2.num AS wl 
	FROM
		(
		SELECT
			score.student_id,
			score.num 
		FROM
			score 
		WHERE
		score.course_id = ( SELECT course.cid FROM course WHERE cname = '生物' )) AS s1
		LEFT JOIN (
		SELECT
			score.student_id,
			score.num 
		FROM
			score 
		WHERE
			score.course_id = ( SELECT course.cid FROM course WHERE cname = '物理' ) 
		) AS s2 ON s1.student_id = s2.student_id 
	) AS s3 
WHERE
	s3.wl > if(ISNULL(sw),0,sw);

这里用到了一个三元语句操作，具体写为

if(condition, a, b)

如果condition为真输出a,反之输出b

所以，我们可以依此将学生的生物成绩进行筛选，选择生物课置为生物成绩，没选则置0

20. 课程平均分从高到低显示（现实任课老师）

同样的，因为要从course表中查找老师成绩，所以如果老师没教记为0，教过即记录成绩

指令如下

    select avg(if(isnull(score.num),0,score.num)),teacher.tname from course
    left join score on course.cid = score.course_id
    left join teacher on course.teacher_id = teacher.tid
		group by teacher.tname;

3. 第六题--not in逻辑问题

查询没学过“叶平”老师课的同学的学号、姓名

因为题目问的是没学过叶平老师课的学生，所以可能会想当然的使用not in 操作进行筛选，例如

SELECT
	student_id 
FROM
	score 
WHERE
	course_id not IN (
	SELECT
		cid 
	FROM
		course 
WHERE
	teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ));

但是这样并不能选择出没有选李平老师课程的学生，只是把学生所选的课程里李平老师的课排除掉了

正确的做法是先选出选过李平老师课程的所有学生，之后再构建一层查询排除掉他们

结果如下

select student_id,sname from(
SELECT
	student_id 
FROM
	score 
WHERE
	student_id NOT IN ( SELECT student_id FROM score WHERE course_id IN ( SELECT cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) )
	)
	as B
	left join
	(select sid,sname from student) as C
	on
	B.student_id = C.sid;
	

4. 第七题--筛选计数

查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

分别选择学过001和002的学生并进行连表，如果查询中有学生的计数刚好为2则筛选

指令如下

select student_id from(
select student_id,course_id from score where course_id = (1) or course_id = (2) 
)as a 
group by
student_id
having count(course_id) = 2;

需要看一下这里group by和 having的顺序，分组操作要在having操作之前

*第8题“查询学过“叶平”老师所教的所有课的同学的学号、姓名”和此题内容相似，只需改变筛选条件即可*

5. 第十三题&第十四题--筛选相同情况

13.查询至少学过学号为“001”同学所有课的其他同学学号和姓名

首先查找1号学生学的所有课，之后再筛选出相关课程的学生选择情况，之后再在其中选择出条数和1号学生相同的学生即可找全

select student_id from(
select student_id,count(1) from score where course_id in(
SELECT course_id FROM score  WHERE student_id = 1 
)
group by
student_id
)as C
group by student_id
having
count(1) = 
(select COUNT(1) from score where student_id = 4
)
;

14.查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名

前面步骤和13相同，但是要在13得出的结果中再筛选出score表中选课数量刚好和1号学生相同的同学

指令如下

SELECT
	student_id 
FROM
	(
	SELECT
		student_id 
	FROM
		( SELECT student_id, count( 1 ) FROM score WHERE course_id IN ( SELECT course_id FROM score WHERE student_id = 2 ) GROUP BY student_id ) AS C 
	GROUP BY
		student_id 
	HAVING
		count( 1 ) = ( SELECT COUNT( 1 ) FROM score WHERE student_id = 2) 
	) AS D 
WHERE
	student_id IN ( SELECT student_id FROM score GROUP BY student_id HAVING count( 1 ) = ( SELECT count( 1 ) FROM score WHERE student_id = 2 ) );

12题情况类似

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

指令如下

        select student_id,sname, count(course_id)
        from score left join student on score.student_id = student.sid
        where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id

6. 第十九题--case语句的使用

按各科平均成绩从低到高和及格率的百分数从高到低顺序

对于筛选及格率需要使用到case语句。

case语句的条件如下

case...when...then...else...end

比如，对于成绩表来说，如果要筛选及格的学生，可以用如下指令

SELECT student_id,course_id,(CASE WHEN num >= 60 THEN '及格' ELSE '不及格' end) AS 成绩
FROM
	score;

结果如下 

对于这道题目来说，将及格的同学记为1，不及格的记为0即可数出及格的同学，除掉所有同学即可算出及格率，指令如下

SELECT
	avg( num ) AS avg_num,
	sum( CASE WHEN score.num > 60 THEN 1 ELSE 0 END )/ count( 1 )* 100 AS percent 
FROM
	score 
GROUP BY
	course_id 
ORDER BY
	avg_num ASC,
	percent DESC;

 7. 其他题目简单梳理

3. 查询平均成绩大于60分的同学的学号和平均成绩

使用avg（）获取平均成绩，之后再使用having()进行筛选即可

  select student_id,avg(num) from score group by student_id having avg(num) > 60

5、查询姓“李”的老师的个数

使用通配符进行筛选

 select count(tid) from teacher where tname like '李%'

10、查询有课程成绩小于60分的同学的学号、姓名

select student_id from score where num < 60 group by student_id;

11、查询没有学全所有课的同学的学号、姓名

SELECT
	student_id 
FROM
	( SELECT student_id, count( 1 ) AS cs FROM score GROUP BY student_id ) AS a 
WHERE
	cs < ( SELECT count( 1 ) FROM course );

17.按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分

    select sc.student_id,
        (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
        (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
        (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
        count(sc.course_id),
        avg(sc.num)
    from score as sc
    group by student_id order by avg(sc.num) asc;

注意每个子表都要具有别名

18.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

考察最大、最小值的使用

 select course_id, max(num) as max_num, min(num) as min_num from score group by course_id;

最后一点补充

对于一个3*3的数据表

如果对其进行如下操作

select * from course as a1, course as a2;

 就会得到如下九条结果

 就是上面的表数据两两组合得到的结果，也就是笛卡尔积的模式