mysql的多表联查 left | right |inner join、exists、any、some、all、if、ifnull、case when then end

多表联查可以通过连接运算实现，即将多张表通过主外键关系关联在一起进行查询。用如下图的关系表示。

1.非等值查询和等值查询

等值查询

内联查询
等值查询：select * from 表1，表2;
后面没有查询条件查询就是笛卡尔积查询；也就是用前面表的记录和后面表的每条记录进行连接；所以笛卡尔积产生的结果就是两个表中的记录的乘积条数。

eg:
笛卡尔积：
select * from student, class;
等值查询：
select * from student,class where student.classid = class.classid;

select * from student , class ,sc where student.classid = class.classid and student.sid = sc.sid;
还有一种写法，使用内联查询：
select * from student inner join class on student.classid = class.classid

select * from student 
inner join class on student.classid = class.classid
inner join sc on student.sid = sc.sid

where和inner join on的区别：

​ where先拿到笛卡尔积的结果，然后再按照where后面的条件进行判断；而inner join on 是先拿到前面一个表，然后再按照条件从后面的表中拼接上符合条件的字段。简单理解为where是执行筛选操作，而inner join on 是执行拼接操作。

非等值查询

非等值查询：select * from 表1，表2 where 表1.字段名 = 表2.字段名

说明：
1.与单表查询类似，都是select语句
2.可以把多个表放在FROM后，用,隔开
3.可以使用你as关键字起别名，as可以省略
4.如果两个表中没有同名的字段，表名也可以省略

2.连接查询

连接查询：

left join :从左表中返回所有的记录，即便在右表中方没有匹配的行。上图中A

right join:从右表中返回所有的记录，即便在左表中方没有匹配的行。上图中C

inner join:在表中至少匹配一个时，则返回记录。上图中B

格式：SELECT * FROM 表1 LEFT|right|INNER JOIN 表2 ON 条件

内连接：上边已经介绍过了
左连接：
eg:
select * from student left join class on student.classid = class.classid

select * from student 
left join class on student.classid = class.classid
left JOIN sc on student.sid = sc.sid


右连接：
eg:
select * from class 
right join student on student.classid = class.classid
right join sc on student.sid = sc.sid

select sname,classname,avg(score) from class 
right join student on student.classid = class.classid
right join sc on student.sid = sc.sid
group by student.sid having avg(score) >= 80

3.union和union all

-- union:
select sname,ssex,classid from student
union
select tname,tsex,tmoney from teacher

select * from student left join class on student.classid = class.classid 
where class.classid is null
union 
select student.*,class.* from class left join student on student.classid = class.classid
where student.sid is null



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

union是求两个查询的并集。

union合并的是结果集，不区分来自于哪一张表，所以可以合并多张表查询出来的数据。

注意：

​ 1.列名不一致时，会以第一张表的表头为准，并对齐栏目。

​ 2.会将重复的行过滤掉，去重 distinct 一致。

​ 3.如果查询的表的列数量不相等时，会报错。

​ 4.在每个子句中的排序是没有意义的，mysql在进行合并的时候会忽略掉。

​ 5.如果子句中的排序和limit进行结合是有意义的。

​ 6.可以对合并后的整表进行排序

union all：除了不去重，其余与union一样。

子查询：就是将查询结果再次作为条件

4.where型查询：

将查询结果作为where后面的查询条件

格式：select * from 表 where 字段 判断符号 （查询结果）
问题：查询id最大的一个学生(使用排序+分页实现)
select * from student order by sid desc limit 1

用where实现：
select * from student where sid = (select max(sid) from student)

select * from student where sid in
	(select max(sid) from student group by classid)

5.from型子查询：

将查询结果作为一张表进行查询，就是一个临时表，如果要使用临时表，可以起别名来使用。

格式：select * from（查询虚拟表）
eg:
select classname,c from class 
inner join (select classid,count(*) c from student group by classid) t1 on class.classid = t1.classid
where c > = 5

6.exists子查询

exists后面的查询有结果时才会执行主句，不存在的话就不会执行主句

格式：select * from 表名 where exists （查询结果）
eg:
select * from teacher where exists( select * from student where ssex = '外星人' )

7.any, some, all 子查询

-- any/some
下面是一个原理的过程
-- 查询出一班成绩比二班最低成绩高的学生
-- 使用子查询，先查出二班最低的
select * from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '一班' and score >
(select min(score) from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '二班')

-- 下面是二班所有的成绩
select score from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '二班'

70.0
60.0
80.0
50.0
30.0
20.0
31.0
34.0
-- 使用or连接查询
select * from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '一班' and 
(score > 70.0 or score > 60.0 or score > 80 or score > 50 or score > 30 or score > 20 or score > 31 or score > 34)

-- 最终使用any或some的查询语句
select * from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '一班' and  score > some (
	select score from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '二班'
)

从上面的推到过程中可以看出：some和any是将子查询的结果逐个与前面的字段进行比较中间用or连接；也就是说满足其中一个就行。

-- all
-- 查询出一班成绩比二班最高成绩高的学生
select * from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '一班' and score >
(select max(score) from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '二班')



select * from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '一班' and 
(score > 70.0 and score > 60.0 and score > 80 and score > 50 and score > 30 and score > 20 and score > 31 and score > 34)


select * from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '一班' and  score > all (
	select score from student,class,sc 
where student.classid = class.classid and student.sid = sc.sid and classname = '二班'
)

从上面的推到过程中可以看出：all是将子查询的结果逐个与前面的字段进行比较中间用and连接；必须得全部满足

8.控制语句

if

格式：IF(expr1,expr2,expr3);类似于java中的三目运算符
-- expr1 表达式
-- expr2 成立的值
-- expr3 不成立的值

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

ifnull

格式：IFNULL(expr1,expr2)
-- expr1 字段
-- expr2 字段为null的默认值

select sid,sname,ifnull(birthday,'这个学生没有生日'),ssex,classid from student

case when then end

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

-- 2. 搜索case（case后面没有 字段）
select tid,tname,
case 
	when tsex > 1 then '外星人'
	when tsex = 1 then '男'
	when tsex < 1 then '女'
end,
tbirthday
from teacher