MySQL 数据库练习题记录02

前言

本文主要记录B站视频视频链接的内容，做到知识梳理和总结的作用，项目git地址。

一、数据库基础

1.1 sql练习题

user表数据:

id	username
1	张三
2	李四
3	王五
4	小刘

user_role表数据:

id	name	user_id
1	售后	1
2	运维	2
3	研发	3
4	实施	5

1.数据库有两张表为 user 表和 user_role 表，如上所示。执行：select u.*，r.name from user u left join user_role r on u.id = r.user_id 之后获取到的结果为: A
A.
1 张三 售后
2 李四 运维
3 王五 研发
4 小刘 null
B.
1 张三 售后
2 李四 运维
3 王五 研发
C.
1 张三 售后
2 李四 运维
3 王五 研发
null null 实施

联查:
left join/right join 特点:主表的所有数据均显示,另外一张表中有匹配的数据,则显示,若无匹配的数据,该字段显示为null。
inner join 特点:查询的数据是满足所有条件的数据,若没有满足所有条件的数据,则结果集为空。
所以,user表在left join的左边故为主表,4条记录均显示且连接条件为user的id等于user_role的user_id。
所以user表前三条数据匹配到user_role的前三条记录(一一匹配),user表第四条记录小刘未匹配到user_role的user_id所以显示未null。而B选项为内连接,C选项为右外连。

1.2 sql语句执行顺序

select distinct...from t1 (left/right) join t2 on t1.xx=t2.xx where t1.xx=? and
t2.xx=? group by t1.xx having ...order by ... limit ..

where: 筛选条件,分组之前
having: 筛选条件 前提:必然先分组,对分组后的结果再次筛选
where,group by,having 三者同时出现,则顺序一定为:where....group by ... having ....

limit用法:
limit m,n 从索引m处开始,筛选n条件数据 -- 实现分页
limit m 从第一条数据开始,筛选前m条

1. from join 产生虚拟表v1 产生的是笛卡尔积(两表完全连接的结果叫做笛卡尔积,会产生m*n条数据)
2. on 根据连接条件进行筛选,基于V1,产生虚拟表v2
3. left/right join 若是外连接,将主表中的所有数据进行补充到v2表中
4. where 根据查询条件,从v2中进行数据筛选,产生虚拟表v3
5. group by 对v3分组,产生虚拟表v4,经常会和聚合函数配合使用,聚合函数的结果在group by之后就会产生
使用场景: 统计每个班级的学生人数
select count(xx) ....group by bj_id
统计每个部门的人数
select count(xx) ... group by d.id
6. having 对分组后的结果再次筛选,产生虚拟表v5
7. select 选择最终要显示的字段,产生虚拟表v6
8. distinct 对结果集进行去重,产生虚拟表v7
9. order by 对结果根据某字段进行升序/降序排列
10. limit 选择需要的数据
where 和 having 区别：
where 是在分组之前进行数据筛选,having是对分组后的结果再次筛选
where,group by,having 若同时出现,则三者的顺序一定是where在group by之前,having在group by之后

1.3 sql语句编写

stu_score表数据:

id	name	score	bj
1	赵一	89	1班
2	钱二	88	2班
3	孙三	84	1班
4	李四	86	2班
5	王五	87	1班
6	吴六	91	2班

题目1：获取每个班级的最后一名

考点: group by能查到什么?
group by column 可以查询到分组后的column,相关的聚合函数,与column具有一对一关系的列,除此之外,其他字段都查询不到。
正确的思路:
1.查询出每个班级的最低分
2.根据上述的查询结果,再次到stu_Score表中查询,查询某个班级最低分对应的学生是谁

错误SQL语句：

select name,bj,MIN(score) from stu_score group by bj;

执行结果：

虽然能查询出来班级最低分，但是名字对应错误，一班最低分是孙三，二班最低分是李四。错误原因：name字段不是与bj一一对应，将赵一与钱二进行顺序匹配到查询记录中。

正确SQL语句：

select ss.* from stu_score ss join
(select bj,min(score) min from stu_score group by bj)tmp
on ss.score=tmp.min and ss.bj=tmp.bj

执行结果：

题目2：表user，字段有：id（非自增，不连续），age，name，sex，addTime。求创建时间（addTime）在2020-05-22至2020-06-17之间所有数据。

查询某段时间区间内的数据，使用between…and…也可以使用> <，数据库中between…and两个边界值都包含。

SQL语句：

select * from user where addTime between '2020-05-22' and '2020-06-17'

题目3：基于表user，查出表中第10条至第20条连续分页数据的sql语句

select * from user limit 9,11

三张表分别为：student，course，score 表数据如下：

student 表:

sno	sname	sage
1	周杰伦	18
2	周润发	18
3	吴孟达	25
4	刘德华	25
5	李连杰	29

course 表：

cno	cname
1	语文
2	数学
3	英语

score 表:

sno	cno	score
1	1	60
1	2	61
2	1	80

题目1：查询所有学生的学号，姓名，选课数，总成绩

分析题意,得出需要使用student表和score两表外连接查询,其中student表为主表
从student表中查询sno,sname,选课数和总成绩查询时需要根据学号分组,必须根据是student表的sno分组
不能根据score表的sno分组

若结果为null,想替换为其他值,则可以使用数据库提供的函数ifnull(exp1,exp2)

SQL语句：

SELECT stu.sno,sname,COUNT(cno) 选课数,IFNULL(SUM(score),0) 总成绩
FROM student stu
LEFT JOIN score sco
ON stu.sno = sco.sno
GROUP BY stu.sno;

查询结果：

题目2：查询没有学完所有课程的学生学号，姓名

思路:查询选课数<总课程数的学生
1.查询总课程数
2.统计每个学生的选课数,然后筛选选课数<总课程数的

count()该函数中可以用*,也可以用列名,count(*): 统计有多少行;count(sno): sno字段对应的若为空则不统计。

SQL语句：

SELECT s.sno,sname 
FROM student s 
LEFT JOIN score sc 
ON s.sno=sc.sno 
GROUP BY s.sno 
HAVING COUNT(cno)<(SELECT COUNT(*) FROM course1)

查询结果：