MySQL高级数据查询语言（多表联查/子查询/流程控制语句）

猿究院-张睿泽

已于 2024-07-24 09:47:28 修改

阅读量1k

点赞数 9

文章标签： mysql

于 2024-07-24 09:19:14 首次发布

本文链接：https://blog.csdn.net/weixin_71072718/article/details/140644027

版权

（3） case when then and

1、多表联查

（1）概念

多表联查可以通过连接运算实现，即将多张表通过主外键关系关联在一起进行查询。下图提供了多表联查时用到的数据库表之间的关系。

（2）内联查询

* 概念：只有完全满足条件(主外键关系的条件)的数据才能出现的结果

<1> 非等值查询

概念：直接用两个表来查询，两个结果之间的相乘，逻辑上有错误（笛卡尔积）。
代码格式：SELECT * FROM 表1，表2

* 示例代码（查询student表和class表信息）：

select * from student, class

* 代码结果（笛卡尔积相乘会让数据重复）

<2> 等值查询

概念：通过where语句来等值表中的主键外键信息
代码格式：SELECT * FROM 表1，表2 WHERE 表1.字段1 = 表2.字段2...

* 示例代码（查询每个学生的平均成绩学生姓名,班级名称,平均成绩）

select student.Sname,class.classname,avg(score) 
from student,class,sc 
where student.sid = sc.sid 
and student.classid = class.classid
group by student.sid;

* 代码结果（对学生的学号进行分组，查询姓名，班级号和平均成绩）：

* 等值查询面试题（查询出学过张三老师课程的学生信息）

select student.* from student,course,sc,teacher 
where student.Sid = sc.Sid 
and sc.Cid = course.Cid 
and course.Tid = teacher.Tid 
and teacher.Tname = '张三';

* 代码结果

<3> 连接查询（内联）

概念：用inner join连接两个表之间的关系，完成查询
代码格式：SELECT * FROM 表1 INNER JOIN 表2 ON 条件

* inner 和 where的区别：

where：表的个数多，每个表的数据量不大,吃内存(笛卡尔积相乘) IO小(读的操作少)
inner：表少，每张表的数据量大，内存占用小，IO高

* 示例代码（每门课程的平均成绩课程的名称代课老师的姓名平均成绩）

select course.Cname,teacher.Tname,avg(score) from sc
inner join course on course.Cid = sc.Cid
inner join teacher on teacher.Tid = course.Tid
group by course.cid;

* 代码结果（通过课程号来分组）

（3）外联查询

* 概念：

左外联（left join）：从左表（表1）中返回所有的记录，即便在右（表2）中没有匹配的行。
右外联（right join）：从右表（table_2）中返回所有的记录，即便在左（table_1）中没有匹配的行。

<1> 左外联（连接查询外联）

概念：已前面的表为主，只要自己主键有值，数据都返回
代码格式：SELECT * FROM 表1 LEFT JOIN 表2 ON 条件

* 示例演示（左表独有的数据）

select * from t1 
left join t2 on t1.id = t2.id where t2.id is null;

* 结果示例

* 示例代码

select class.classname from class
left join student on student.classid = class.classid
where student.sid is null;

* 代码结果

<2> 右外联（连接查询外联）

概念：已右边的表为主，只要自己主键有值，数据都返回
代码格式：SELECT * FROM 表1 RIGHT JOIN 表2 ON 条件

* 示例演示（右表独有的数据）

select * from t1 
right join t2 on t1.id = t2.id where t1.id is null;

* 结果示例

* 示例代码（没有学生的班级）

select class.classname from student
right join class on student.classid = class.classid
where student.sid is null;

* 代码结果

（4）union

* 概念：

两个结果集的并集
去除重复和distinct一样,张表的表头为准，并对其栏目。
不同类型的字段是可以合并的
不同列数量的结果集不允许合并
起别名给第一个结果集才有用
在每个子句中的排序是没有意义的，mysql在进行合并的时候会忽略掉。
如果子句中的排序和limit进行结合是有意义的。

* 代码格式

select ....
union
select ....

* 示例代码（统计各个分数段的人数格式：分数段人数）

select '100-90' 分数段,count(*) 人数 from sc where sc.score >= 90
union
select '90-70',count(*) from sc where sc.score >= 70 and sc.score < 90
union
select '70-60',count(*) from sc where sc.score >= 60 and sc.score < 70
union
select '不及格',count(*) from sc where sc.score < 60;

* 代码结果

（5）union all

概念：求两个查询的并集，但是不会把重复的过滤掉，而是全部显示出来，相当于不去重的并集。
代码格式：select .... union all select ....

* 示例代码

select * from student 
left join class on student.classid = class.classid
union all
select * from class 
left join student on student.classid = class.classid

* 代码结果

2、子查询

* 概念：又叫内部查询，SELECT嵌入在另一个SQL查询的WHERE或HAVING子句中的查询

（1）where型子类查询：

代码格式：select 字段名 from 表名 where(子类查询)

* 示例代码（查询没学过张三老师课程的学生）

select * from student where sid not in(
	select sid from sc where cid = (
		select cid from course where tid =
			(select tid from teacher where tname = '张三')
		)
);

* 代码结果（依次查询学过张三老师课程学生的信息）

（2）from型子类查询：

概念：把内层的查询结果当成临时表，供外层sql再次查询。查询结果集可以当成表看待，临时表要使用一个别名，查询结果将作为一张表使用。
代码格式：select 字段名 from 表名 left join (from型子类查询) on 语句

* 示例代码（查找班级人数大于5人的班级）：

select classname,人数 from class left join
(select classid, count(*) 人数
	from student group by classid
)t1
on class.classid = t1.classid
where 人数 > 5;

* 代码结果：

（3）exists型子类查询

概念：把外层sql的结果，拿到内层sql去测试，如果内层的sql成立，则该行取出。内层查询是exists后的查询，子句有结果,父句执行,子句没结果,父句不执行
代码格式：select 字段名 from 表名 where exists(子查询)

* 示例代码（有无二班学生，有则打印全部由老师，无则不打印）

select * from teacher
where exists (select * from student where classid = 2);

* 代码结果

（3）any / some / all

<1> any

概念：和括号中的依次比较，只要满足条件就返回相当于||
代码格式：select 字段名 from 表名 where 字段名运算符 any(子查询)

* 示例代码（查询出一班成绩比二班最低成绩高的学生）

select distinct student.* from sc 
left join student on sc.sid = student.sid
where student.classid = 1 
and score > any (select score from sc 
left join student on sc.sid = student.sid
where student.classid = 2);

* 代码结果（用any来判断至少比二班最低成绩要高的学生）

<2> all

概念：和括号中的依次比较，全部满足条件就返回相当于&&
代码格式：select 字段名 from 表名 where 字段名运算符 all(子查询)

* 示例代码（查询出一班成绩比二班最高成绩高的学生）

select distinct student.* from sc 
left join student on sc.sid = student.sid
where student.classid = 1 
and score > all (select score from sc 
left join student on sc.sid = student.sid
where student.classid = 2);

* 代码结果（用all来比较一班学生和二班全体学生）

3、流程控制函数/语句

* 概念：流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

（1）IF

代码格式：IF(expr1,expr2,expr3) （expr1 条件，expr2 条件成立显示的数据，expr3 条件不成立显示的数据）

* 示例代码（查找老师性别，如果是0则为男，如果是1则为女）

select tid,tname,if(tsex=1,'女','男') tsex,tbirthday,taddress from teacher;

* 代码结果（通过比较是否为1来判断老师的性别）

（2）IFNULL

用法格式：IFNULL(expr1,expr2) （expr1 字段，expr2 当字段为null 默认值）

* 示例代码（查看学生生日是否存在，不存在则插入相关默认值）

select sid,sname,ifnull(birthday,'这个学生没有生日，很可怜，惨'),ssex from student;

* 代码结果（查看学生是否有生日信息，有则显示，无则显示默认值）

（3） case when then and

* 注意事项：

要么都出现，要么就都不出现
when后必须为常量
如果为null则执行else后的语句

* 代码格式：

格式一

case
when 条件1 then 结果一
when 条件2 then 结果二
when 条件3 then 结果三
else 结果四
end

格式二

case 字段名
when 常量值1 then 结果一
when 常量值2 then 结果二
when 常量值3 then 结果三
else 结果四
end

* 示例代码（格式一判断老师性别）

select tid,tname,
case 
	when tsex > 1 then '男'
	when tsex = 1 then '女'
	when tsex < 1 then '未知'
	else '保密'
end,tbirthday
from teacher;

* 代码结果

* 示例代码（格式二判断老师性别）

select tid,tname,
case tsex
	when 0 then '男'
	when 1 then '女'
	else '保密'
end tsex,tbirthday
from teacher;

* 代码结果

4、练习

-- 统计各个分数段的人数
-- 格式一
分数段人数
100-90 5
90-70 10
70-60 2
不及格 3

* 示例代码

select '100-90' 分数段,count(*) 人数 from sc where sc.score >= 90
union
select '90-70',count(*) from sc where sc.score >= 70 and sc.score < 90
union
select '70-60',count(*) from sc where sc.score >= 60 and sc.score < 70
union
select '不及格',count(*) from sc where sc.score < 60;

* 代码结果

-- 格式二
分数段 100-90 90-70 70-60 不及格
人数 5 10 2 3

* 示例代码

select '人数' 分段式,
count(case when score >= 90 then score end) '100-90',
count(case when score >= 70 and score < 90 then score end) '90-70',
count(case when score >= 60 and score < 70 then score end) '70-60',
count(case when score < 60 then score end) '不及格'
from sc;

* 代码结果