多表查询的两种方法
为什么要用多表查询?
因为我们在涉及表的时候肯定不止一张表。
数据准备:
# 建表
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('技术','人力资源')); # 也可通过添加连表操作同时查看部门信息