mysql多表查询的两种方法

多表查询的两种方法

为什么要用多表查询？
因为我们在涉及表的时候肯定不止一张表。

数据准备：
# 建表
create table dep(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

# 插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保');


insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('tony','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

1、多表查询方法之连表操作

补充：

1、from 后可跟多个表用逗号隔开，使两张表拼接
eg ：from emp,dep

2、在涉及到多表操作的时候 为了避免表字段重复
	需要在字段名的前面加上表名限制
	
实例：
1、查找员工名字和对应的部门名称
分析：
select * from emp,dep;   # 先使员工表和部门表进行拼接 这样查找的结果是员工对应了每一个部门的id
select * from emp,dep where dep_id=id;  # 想要两个表id对应 这样的写法是不对的
select * from emp,dep where emp.dep_id=dep.id;  # 这样的写法才对 指定表名 表名.字段名的方式
select emp.name,dep.name from emp,dep where emp.dep_id=dep.id;  # 只取人名和对应的部门

# 上述的方法非常的麻烦也不符合合理的操作方式 那么接下来就要用到几个规定的语法格式实现对表查询

1、inner join：内连接 ：只连接两个表中都存在(有对应关系)的数据
	select * from emp inner join dep on emp.dep_id = dep.id;

2、left join : 左连接：以左边表为基准展示左表的所有数据没有对应则null填充
	select * from emp left join dep on emp.dep_id=dep.id;

3、reght join ：右连接：以右边表为基准展示右边表的所有数据没有对应则null填充
	select * from emp right join dep on emp.dep_id=dep.id;

4、union ：全连接：展示左右两个表中所有的数据没有对应则用null填充 (其实相当于一个连接符 把左右连接合起来)
	select * from emp left join dep on emp.dep_id = dep.id
        union
        select * from emp right join dep on emp.dep_id = dep.id;

2、多表查询方法之子查询

定义：
子查询：其实就是分布操作 将一张表的查询结果当作另外一条sql语句的查询条件

实例：

1、查询部门是技术部或者人力资源部的员工信息

第一种方法：分布式
先查询技术部和人力资源部的部门编号：
	select id from dep where name in('技术','人力资源');
根据部门的编号去员工表里筛选出对应的员工数据:
	select * from emp where dep_id in (200,201);    # 查询到部门编号在根据上一步结果判断 这样的话是比较繁琐的

子查询方法：将sql语句括号括起来充当查询条件
	select * from emp where dep_id in (select id from dep where name in('技术','人力资源'));  # 在不知道员工部门编号的情况下可一步实现查看部门员工信息
	
完善：
	select * from emp inner join dep on emp.dep_id = dep.id where dep_id in (select id from dep where name in('技术','人力资源'));  # 也可通过添加连表操作同时查看部门信息