多表查询
#建表
create table dep(
id int,
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,'运营');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',38,201),
('nick','female',28,202),
('owen','male',18,200),
('jerry','female',18,204)
;
# 当初为什么我们要分表，就是为了方便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成一张表进行查询才合理
表查询
select * from emp,dep; # 左表一条记录与右表所有记录都对应一遍>>>笛卡尔积
# 代码示例：
mysql> select * from emp,dep;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 1 | jason | male | 18 | 200 | 201 | 人力资源 |
| 1 | jason | male | 18 | 200 | 202 | 销售 |
| 1 | jason | male | 18 | 200 | 203 | 运营 |
| 2 | egon | female | 48 | 201 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 2 | egon | female | 48 | 201 | 202 | 销售 |
| 2 | egon | female | 48 | 201 | 203 | 运营 |
| 3 | kevin | male | 38 | 201 | 200 | 技术 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 202 | 销售 |
| 3 | kevin | male | 38 | 201 | 203 | 运营 |
| 4 | nick | female | 28 | 202 | 200 | 技术 |
| 4 | nick | female | 28 | 202 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 4 | nick | female | 28 | 202 | 203 | 运营 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
| 5 | owen | male | 18 | 200 | 201 | 人力资源 |
| 5 | owen | male | 18 | 200 | 202 | 销售 |
| 5 | owen | male | 18 | 200 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | 200 | 技术 |
| 6 | jerry | female | 18 | 204 | 201 | 人力资源 |
| 6 | jerry | female | 18 | 204 | 202 | 销售 |
| 6 | jerry | female | 18 | 204 | 203 | 运营 |
+----+-------+--------+------+--------+------+--------------+
24 rows in set (0.00 sec)
# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据
# 查询员工及所在部门的信息
select * from emp,dep where emp.dep_id = dep.id;
# 代码示例：
mysql> select * from emp,dep where emp.dep_id=dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
# 查询部门为技术部的员工及部门信息
select * from emp,dep where emp.dep_id = dep.id and dep.name = '技术';
# 代码演示：
mysql> select * from emp,dep where emp.dep_id=dep.id and dep.name='技术';
+----+-------+------+------+--------+------+--------+
| id | name | sex | age | dep_id | id | name |
+----+-------+------+------+--------+------+--------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
+----+-------+------+------+--------+------+--------+
2 rows in set (0.00 sec)
# 将两张表关联到一起的操作，有专门对应的方法
# 1、内连接：只取两张表有对应关系的记录
select * from emp inner join dep on emp.dep_id = dep.id;
# 代码演示：
mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技术";
# 代码演示：
mysql> select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技术";
+----+-------+------+------+--------+------+--------+
| id | name | sex | age | dep_id | id | name |
+----+-------+------+------+--------+------+--------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
+----+-------+------+------+--------+------+--------+
2 rows in set (0.00 sec)
# 2、左连接: 在内连接的基础上保留左表没有对应关系的记录
select * from emp left join dep on emp.dep_id = dep.id;
# 代码演示：
mysql> select * from emp left join dep on emp.dep_id=dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
+----+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
# 3、右连接: 在内连接的基础上保留右表没有对应关系的记录
select * from emp right join dep on emp.dep_id = dep.id;
# 代码演示：
mysql> select * from emp right join dep on emp.dep_id=dep.id;
+------+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
# 4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录
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;
# 代码演示：
mysql> 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;
+------+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)
子查询
# 就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用
1.查询 部门是技术或者人力资源的员工信息
"""
先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息
"""
select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");
2.每个部门最新入职的员工 # 表数据 参考 单表查询 的例表
# 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;
"""
记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询
"""
select * from emp inner join dep on emp.dep_id = dep.id;
为子查询中 第二题提供的表数据：
# 建表
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tank','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('nick','male',18,'19000301','teacher',30000,401,1),
('sean','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
2.查看每个部门最新入职的员工
# 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date;
"""
记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询
"""
# 代码示例：
mysql> select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
-> inner join
-> (select post,max(hire_date) as max_date from emp group by post) as t2
-> on t1.post = t2.post
-> where t1.hire_date = t2.max_date;
+----+--------+------------+---------------------+-----------------------------+------------+
| id | name | hire_date | post | post | max_date |
+----+--------+------------+---------------------+-----------------------------+------------+
| 1 | jason | 2017-03-01 | 张江第一帅形象代言 | 张江第一帅形象代言 | 2017-03-01 |
| 2 | egon | 2015-03-02 | teacher | teacher | 2015-03-02 |
| 13 | 格格 | 2017-01-27 | sale | sale | 2017-01-27 |
| 14 | 张野 | 2016-03-11 | operation | operation | 2016-03-11 |
+----+--------+------------+---------------------+-----------------------------+------------+
4 rows in set (0.00 sec)
