5.8 数据库多表查询

多表连接查询

 

交叉连接：

　　不适用任何匹配条件。生成笛卡尔积、

select * from employee1 ,department;

内连接：

　　找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果。（只连接匹配的行）

　　简单来说： 取交集

select * from employee1,department where employee1.dep_id=department.id;
select * from employee1 inner join department on employee1.dep_id=department.id;

左连接：

　　优先显示左表全部记录。

　　返回包括 左表 中的 所有记录 和 右表 中 联结字段 相等的记录

#左链接：在按照on的条件取到两张表共同部分的基础上，保留左表的记录
select * from employee1 left join department on department.id=employee1.dep_id; 　　# department（右表）　　employee1（左表） 

select * from department left join employee1 on department.id=employee1.dep_id;

右链接：

　　优先显示右表全部记录。

　　返回包括 右表 中的所有记录 和 左表 中联结字段相等的记录

#右链接：在按照on的条件取到两张表共同部分的基础上，保留右表的记录
select * from employee1 right join department on department.id=employee1.dep_id;　　# department（右表）　　employee1（左表）

select * from department right join employee1 on department.id=employee1.dep_id;

　　ps： 

　　　　关于傻傻分不清除左右表的额外说明：

　　　　　　不论什么关键字（left/right join）的左边就是左表，关键字右边就是右表

　　　　比如：

　　　　　　a left/right b 

　　　　　　a 为左表，b 为右表

全外连接：

　　显示左右两个表的全部记录。

　　注意：mysql不支持全外连接 full join，但是mysql可以使用union间接实现全外连接

select * from employee1 left join department on department.id=employee1.dep_id
union
select * from employee1 right join department on department.id=employee1.dep_id; 

符合条件连接查询

# 示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,
# 即找出公司所有部门中年龄大于25岁的员工
select * from employee1 inner join department on employee1.dep_id=department.id and age>25;
 

# 示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select * from employee1 inner join department on employee1.dep_id=department.id and age>25 order by age asc;

子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

#查询平均年龄在25岁以上的部门名
select name from department where id in (
select dep_id from employee group by dep_id having avg(age) > 25
);

#查看技术部员工姓名
select name from employee where dep_id = (select id from department where name='技术');

#查看小于2人的部门名
select name from department where id in (
select dep_id from employee1 group by dep_id having count(id) < 2
)
union
select name from department where id not in (select distinct dep_id from employee1);

#提取空部门                              #有人的部门
select * from department where id not in (select distinct dep_id from employee1);


或者：
select name from department where id in
(
select dep_id from employee1 group by dep_id having count(id) < 2
union
select id from department where id not in (select distinct dep_id from employee1);
);