MySQL之多表查询（笛卡尔积查询、内连接、外连接(左外连接，右外连接)、union、union all ）
多表查询
测试数据
create table emp (id int,name char(10),sex char,dept_id int); insert emp values(1,"大黄","m",1); insert emp values(2,"老王","m",2); insert emp values(3,"老李","w",30); #一张表示部门表 #存在一些没有员工的的部门 create table dept (id int,name char(10)); insert dept values(1,"市场"); insert dept values(2,"财务"); insert dept values(3,"行政");1. 笛卡尔积查询
# 笛卡尔积查询， mysql> select *from dept,emp; +------+--------+------+--------+------+---------+ | id | name | id | name | sex | dept_id | +------+--------+------+--------+------+---------+ | 1 | 市场 | 1 | 大黄 | m | 1 | | 2 | 财务 | 1 | 大黄 | m | 1 | | 3 | 行政 | 1 | 大黄 | m | 1 | | 1 | 市场 | 2 | 老王 | m | 2 | | 2 | 财务 | 2 | 老王 | m | 2 | | 3 | 行政 | 2 | 老王 | m | 2 | | 1 | 市场 | 3 | 老李 | w | 30 | | 2 | 财务 | 3 | 老李 | w | 30 | | 3 | 行政 | 3 | 老李 | w | 30 | +------+--------+------+--------+------+---------+ 9 rows in set (0.00 sec) # 改进版 mysql> select *from dept,emp where dept.id = dept_id; +------+--------+------+--------+------+---------+ | id | name | id | name | sex | dept_id | +------+--------+------+--------+------+---------+ | 1 | 市场 | 1 | 大黄 | m | 1 | | 2 | 财务 | 2 | 老王 | m | 2 | +------+--------+------+--------+------+---------+总结：
笛卡尔积查询的结果，存在很多错误的数据。即数据关联关系错误
解决办法：
select *from dept,emp where dept.id = dept_id;同时笛卡尔积的结果，会产生重复的字段信息
解决办法：
select 指定字段... from dept,emp where dept.id = dept_id;2. 内连接
内连接查询本质上就是笛卡尔积查询
mysql> select *from dept join emp ; +------+--------+------+--------+------+---------+ | id | name | id | name | sex | dept_id | +------+--------+------+--------+------+---------+ | 1 | 市场 | 1 | 大黄 | m | 1 | | 2 | 财务 | 1 | 大黄 | m | 1 | | 3 | 行政 | 1 | 大黄 | m | 1 | | 1 | 市场 | 2 | 老王 | m | 2 | | 2 | 财务 | 2 | 老王 | m | 2 | | 3 | 行政 | 2 | 老王 | m | 2 | | 1 | 市场 | 3 | 老李 | w | 30 | | 2 | 财务 | 3 | 老李 | w | 30 | | 3 | 行政 | 3 | 老李 | w | 30 | +------+--------+------+--------+------+---------+ 9 rows in set (0.00 sec) mysql> select *from dept join emp on dept.id=emp.dept_id; +------+--------+------+--------+------+---------+ | id | name | id | name | sex | dept_id | +------+--------+------+--------+------+---------+ | 1 | 市场 | 1 | 大黄 | m | 1 | | 2 | 财务 | 2 | 老王 | m | 2 | +------+--------+------+--------+------+---------+ 2 rows in set (0.00 sec)3. 外连接：左连接
左边的表无论是否能够匹配 都要完整显示,右边即使没有也要显示出来
# 需求：要查询所有员工以及所属的部门信息 mysql> select * from emp left join dept on dept_id= dept.id; +------+--------+------+---------+------+--------+ | id | name | sex | dept_id | id | name | +------+--------+------+---------+------+--------+ | 1 | 大黄 | m | 1 | 1 | 市场 | | 2 | 老王 | m | 2 | 2 | 财务 | | 3 | 老李 | w | 30 | NULL | NULL | +------+--------+------+---------+------+--------+ 3 rows in set (0.00 sec) # 注意 在外连接查询中 不能使用where关键字，必须使用on 专门来做表的对应关系4. 外连接：右连接
右边的表无论是否能够匹配 都要完整显示,左边即使没有也要显示出来
select *from dept full join emp on dept.id = emp.dept_id;5. 外连接：全连接（不支持）
无论是否匹配成功，两边表的数据都要全显示
##mysql 不支持 select *from dept full join emp on dept.id = emp.dept_id; # 可以转化一种思路，使用左连接 + 右连接 select *from dept left join emp on dept.id=emp.dept_id union select *from dept right join emp on dept.id=emp.dept_id;6. union：联合两个表
过滤重复，即重复的数据不显示。同时必须保证 两个表的列数要相同
select * from emp union select * from emp; mysql> select * from emp -> union -> select * from emp; +------+--------+------+---------+ | id | name | sex | dept_id | +------+--------+------+---------+ | 1 | 大黄 | m | 1 | | 2 | 老王 | m | 2 | | 3 | 老李 | w | 30 | +------+--------+------+---------+ 3 rows in set (0.00 sec)7. union all ：
不过滤重复，即重复的数据可以显示。同时必须保证 两个表的列数要相同
select * from emp union all select * from emp; mysql> select * from emp -> union all -> select * from emp; +------+--------+------+---------+ | id | name | sex | dept_id | +------+--------+------+---------+ | 1 | 大黄 | m | 1 | | 2 | 老王 | m | 2 | | 3 | 老李 | w | 30 | | 1 | 大黄 | m | 1 | | 2 | 老王 | m | 2 | | 3 | 老李 | w | 30 | +------+--------+------+---------+ 6 rows in set (0.00 sec)总结：
内连接表示，只显示匹配成功的记录。一般情况下，我们通常使用内连接
外连接表示，没有匹配成功的也要显示
练习：
测试数据
create table stu(id int primary key auto_increment,name char(10)); create table tea(id int primary key auto_increment,name char(10)); create table tsr(id int primary key auto_increment,t_id int,s_id int, foreign key(s_id) references stu(id), foreign key(t_id) references tea(id)); insert into stu values(null,"张三"),(null,"李四"); insert into tea values(null,"egon"),(null,"wer"); insert into tsr values(null,1,1),(null,1,2),(null,2,2);需求：查出egon教过的学生
使用内连接：
mysql> select * from tea join tsr join stu on tea.id=tsr.t_id and tsr.s_id = stu.id where tea.name="egon"; +----+------+----+------+------+----+--------+ | id | name | id | t_id | s_id | id | name | +----+------+----+------+------+----+--------+ | 1 | egon | 1 | 1 | 1 | 1 | 张三 | | 1 | egon | 2 | 1 | 2 | 2 | 李四 | +----+------+----+------+------+----+--------+ 2 rows in set (0.00 sec) mysql> select stu.name from tea join tsr join stu on tea.id=tsr.t_id and tsr.s_id = stu.id where tea.name="egon"; +--------+ | name | +--------+ | 张三 | | 李四 | +--------+ 2 rows in set (0.00 sec)使用子查询：
# 先查出egon对应的id select id from tea where name="egon"; # 在tsr表中 查询 egon 教过学生 的id select s_id from tsr where t_id = (select id from tea where name="egon"); # 在学生表中查询出对应的id select stu.name from stu where id in (select s_id from tsr where t_id = (select id from tea where name="egon")); mysql> select * from tea where name="egon"; +----+------+ | id | name | +----+------+ | 1 | egon | +----+------+ 1 row in set (0.00 sec) mysql> select s_id from tsr where t_id = (select id from tea where name="egon"); +------+ | s_id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) mysql> select stu.name from stu where id in (select s_id from tsr where t_id = (select id from tea where name="egon")); +--------+ | name | +--------+ | 张三 | | 李四 | +--------+ 2 rows in set (0.27 sec)通常情况下，内连接能够查询出来的数据，使用子查询也能查询出来
