多表查询

多表连接查询

语法

1SELECT 字段列表
2    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
3    ON 表1.字段 = 表2.字段;

示例表
mysql> select * from department;

id	name
200	技术
201	人力资源
202	销售
203	运营

mysql> select * from employee;

id	name	sex	age	dep_id
1	egon	male	18	200
2	alex	female	48	201
3	wupeiqi	male	38	201
4	yuanhao	female	28	202
5	liwenzhou	male	18	200
6	jingliyang	female	18	204

笛卡尔积

交叉连接：不适用任何匹配条件。生成笛卡尔积
mysql> select * from employee,department;

id	name	sex	age	dep_id	id	name
1	egon	male	18	200	200	技术
1	egon	male	18	200	201	人力资源
1	egon	male	18	200	202	销售
1	egon	male	18	200	203	运营
2	alex	female	48	201	200	技术
2	alex	female	48	201	201	人力资源
2	alex	female	48	201	202	销售
2	alex	female	48	201	203	运营
3	wupeiqi	male	38	201	200	技术
3	wupeiqi	male	38	201	201	人力资源
3	wupeiqi	male	38	201	202	销售
3	wupeiqi	male	38	201	203	运营
4	yuanhao	female	28	202	200	技术
4	yuanhao	female	28	202	201	人力资源
4	yuanhao	female	28	202	202	销售
4	yuanhao	female	28	202	203	运营
5	liwenzhou	male	18	200	200	技术
5	liwenzhou	male	18	200	201	人力资源
5	liwenzhou	male	18	200	202	销售
5	liwenzhou	male	18	200	203	运营
6	jingliyang	female	18	204	200	技术
6	jingliyang	female	18	204	201	人力资源
6	jingliyang	female	18	204	202	销售
6	jingliyang	female	18	204	203	运营

内连接:只连接匹配的行

找两张表共有的部分
相当于利用条件从笛卡尔积结果中筛选出了正确的结果 department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来

语法示例

1mysql> select
2employee.id,employee.name,employee.age,employee.sex,
3department.name from employee inner join department 
4on employee.dep_id=department.id; 

内连接后的虚拟表

id	name	age	sex	name
1	egon	18	male	技术
2	alex	48	female	人力资源
3	wupeiqi	38	male	人力资源
4	yuanhao	28	female	销售
5	liwenzhou	18	male	技术

1#上述sql等同于
2mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

外链接之左连接：优先显示左表全部记录

以左表为准，即找出所有员工信息，当然包括没有部门的员工
本质就是：在内连接的基础上增加左边有右边没有的结果

语法示例

1mysql> select 
2employee.id,employee.name,department.name as depart_name from 
3employee left join department 
4on employee.dep_id=department.id;

左连接之后的虚拟表

id	name	depart_name
1	egon	技术
5	liwenzhou	技术
2	alex	人力资源
3	wupeiqi	人力资源
4	yuanhao	销售
6	jingliyang	NULL

外连接之右连接优:先显示右表全部记录

以右表为准，即找出所有部门信息，包括没有员工的部门
本质就是：在内连接的基础上增加右边有左边没有的结果

语法示例

1mysql> select 
2employee.id,employee.name,department.name as depart_name 
3from employee right join department 
4on employee.dep_id=department.id;

右连接之后的虚拟表

id	name	depart_name
1	egon	技术
2	alex	人力资源
3	wupeiqi	人力资源
4	yuanhao	销售
5	liwenzhou	技术
NULL	NULL	运营

全连接：显示左右两个表全部记录

全连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

语法示例

1#注意：mysql不支持全外连接 full JOIN
2#强调：mysql可以使用此种方式间接实现全外连接
3select * from employee left join department 
4on employee.dep_id = department.id
5union
6select * from employee right join department 
7on employee.dep_id = department.id
8;
9#注意 union与union all的区别：union会去掉相同的纪录

全连接后的虚拟表

id	name	sex	age	dep_id	id	name
1	egon	male	18	200	200	技术
5	liwenzhou	male	18	200	200	技术
2	alex	female	48	201	201	人力资源
3	wupeiqi	male	38	201	201	人力资源
4	yuanhao	female	28	202	202	销售
6	jingliyang	female	18	204	NULL	NULL
NULL	NULL	NULL	NULL	NULL	203	运营

多表连接可以不断地与虚拟表连接

1select t1.* from emp as t1
2inner join
3(select post,max(salary) as ms from emp group by post) as t2
4on t1.post = t2.post
5where t1.salary = t2.ms
6;