MySQL （四）多表查询(内连接、外连接、联合、子查询)

 多表查询存在笛卡尔积问题

举例：

A表：                                                                                                                                        B表：

select * from test.total,test.section #多表查询存在笛卡尔积问题

运行结果：

1. 内连接查询

#数据准备
create database test;
use test;
create table total  ( #总表：学生信息
`id` int primary key, 
`dept` int,
`age` int,
`height` float ,
`university` varchar(20)
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

insert into `total` values(1,3500,18,162,"CUMT");
insert into `total` values(2,3522,19,165,"JNU");
insert into `total` values(3,3545,20,178,"ABC");
insert into `total` values(4,3576,20,173,"ICBC");
insert into `total` values(5,3587,25,173,"CCB");
insert into `total` values(6,3523,22,178,"CUMT");

create table section  ( #分表：各科成绩
`id` int primary key, 
`dept` int,
`chinese` float,
`math` float ,
`english` float
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

insert into `section` values(1,3500,76,84,99);
insert into `section` values(2,3522,45,40,82);
insert into `section` values(3,3545,89,88,82);
insert into `section` values(4,3576,26,88,92);

#内链接查询
select #最终表里保存的列名
	tt.dept,  #dept存在模糊问题，总表和分表都有（个人认为选择数量多的那个）
    math, 
    university
from test.total as tt #将总表命名为tt
inner join test.section as ts on tt.dept = ts.dept; #筛选条件是总表dept和分表dept相等
#查询总表和分表中具有相同学号dept部分的数学成绩math和大学名称university的数据

总表：行号id，学号dept，年龄age，身高height，学校university

分表：行号id，学号dept，语文成绩chinese，数学成绩math，英语成绩english

运行结果：

2. 外连接查询

select
	tt.dept, 
    math,
    university
from test.total as tt #左表
left join test.section as ts on tt.dept = ts.dept; #右表

运行结果：以左表为准，右表缺失的数据为null

select
	ts.dept, 
    math,
    university
from test.section as ts
left join test.total as tt on ts.dept = tt.dept;

3.联合查询

要合并的多张表的列数、类型要保持一致

union all 将所有数据合并，union将对合并后的数据去重

use test;
select * from test.section where chinese > 80 
union all #将两部分的结果拼接起来（union均可）
select * from test.section where math < 60;
#筛选语文成绩大于80 或 数学成绩小于60的人

4.子查询

将代码看成A和B两部分，B的结果作为A的条件

B的结果可以是标量、列、行、表，并搭配一定的操作符

a.标量子查询

select #A：筛选总表中学号dept和B筛选结果一致的学生的学校university
	tt.dept,
	university
from test.total as tt
where dept = #此时子查询中B的结果只能有一条，否则会报错。
( #B：筛选分表中语文成绩chinese大于80的学生的学号dept
select
	ts.dept
from test.section as ts
where chinese > 80)
#输出语文成绩chinese大于80的学生的学校

运行结果：

b.列子查询

操作符：

select * from test.section as ts
where math in #操作符in
(select math from test.section as ts where ts.dept in ('3522',3545)) #B:学号dept为3522或3545的数学成绩

B的运行结果：

A的运行结果：返回数学成绩math等于40或等于88的学生的全部信息

select * from test.section as ts
where math > #操作符 > any
any(select math from test.section as ts where ts.dept in ('3522',3545))

A运行结果：返回数学成绩math大于40或大于88的学生的全部信息

select * from test.section as ts
where math > #操作符 > all
all(select math from test.section as ts where ts.dept in ('3522',3545))

A运行结果：返回数学成绩math大于40且大于88的学生的全部信息

c.行子查询

操作符：=、<>、in、not in

select * from test.section as ts
where (math,chinese) > #将列名用（）,然后再加操作符
(select math,chinese from test.section as ts where ts.dept = '3522') #B：学号为3522的数学和语文成绩

B的运行结果：

A的运行结果：返回数学成绩大于40且，语文成绩大于45的学生的全部信息

d.表子查询

操作符：in

select * from test.section as ts
where (math,chinese) in #操作符in
(select math,chinese from test.section as ts where ts.dept in ('3522',3576))

B的运行结果：

A的运行结果：返回（数学成绩math=40且语文成绩chinese=45）或（数学成绩math=88且语文成绩chinese=26）的学生的全部信息

主要再举例操作符如何用，所以在同一张表中进行演示。