多表查询
准备员工表和部门表
# 部门表
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
("haha","male",18,200),
("xixi","female",48,201),
("dudu","male",38,201),
("lala","female",18,202),
("nana","male",16,200),
("bubu","female",18,204);
# 查看表结构
desc dep;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
# 查看表数据
select * from emp;
+----+------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------+--------+------+--------+
| 1 | haha | male | 18 | 200 |
| 2 | xixi | female | 48 | 201 |
| 3 | dudu | male | 38 | 201 |
| 4 | lala | female | 18 | 202 |
| 5 | nana | male | 16 | 200 |
| 6 | bubu | female | 18 | 204 |
+----+------+--------+------+--------+
select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
连接查询
将多个select语句生成的虚拟表进行拼接，拼接成一张表进行查询！！！
交叉连接：不适用任何匹配条件，生成笛卡尔积
笛卡尔积
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0),(b, 1), (b, 2)}
select * from emp,dep;
+----+------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------+--------+------+--------+------+--------------+
| 1 | haha | male | 18 | 200 | 200 | 技术 |
| 1 | haha | male | 18 | 200 | 201 | 人力资源 |
| 1 | haha | male | 18 | 200 | 202 | 销售 |
| 1 | haha | male | 18 | 200 | 203 | 运营 |
| 2 | xixi | female | 48 | 201 | 200 | 技术 |
| 2 | xixi | female | 48 | 201 | 201 | 人力资源 |
| 2 | xixi | female | 48 | 201 | 202 | 销售 |
| 2 | xixi | female | 48 | 201 | 203 | 运营 |
| 3 | dudu | male | 38 | 201 | 200 | 技术 |
| 3 | dudu | male | 38 | 201 | 201 | 人力资源 |
| 3 | dudu | male | 38 | 201 | 202 | 销售 |
| 3 | dudu | male | 38 | 201 | 203 | 运营 |
| 4 | lala | female | 18 | 202 | 200 | 技术 |
| 4 | lala | female | 18 | 202 | 201 | 人力资源 |
| 4 | lala | female | 18 | 202 | 202 | 销售 |
| 4 | lala | female | 18 | 202 | 203 | 运营 |
| 5 | nana | male | 16 | 200 | 200 | 技术 |
| 5 | nana | male | 16 | 200 | 201 | 人力资源 |
| 5 | nana | male | 16 | 200 | 202 | 销售 |
| 5 | nana | male | 16 | 200 | 203 | 运营 |
| 6 | bubu | female | 18 | 204 | 200 | 技术 |
| 6 | bubu | female | 18 | 204 | 201 | 人力资源 |
| 6 | bubu | female | 18 | 204 | 202 | 销售 |
| 6 | bubu | female | 18 | 204 | 203 | 运营 |
+----+------+--------+------+--------+------+--------------+
inner join 内连接
inner join 将表进行拼接，找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
# dep没有204这个部门，因而emp表中关于204这条员工信息没有匹配出来
select emp.id,emp.name,emp.sex,emp.age,emp.dep_id,dep.name from emp inner join dep on dep.id=emp.dep_id;
+----+------+--------+------+--------+--------------+
| id | name | sex | age | dep_id | name |
+----+------+--------+------+--------+--------------+
| 1 | haha | male | 18 | 200 | 技术 |
| 2 | xixi | female | 48 | 201 | 人力资源 |
| 3 | dudu | male | 38 | 201 | 人力资源 |
| 4 | lala | female | 18 | 202 | 销售 |
| 5 | nana | male | 16 | 200 | 技术 |
+----+------+--------+------+--------+--------------+
# dep没有204这个部门，因而emp表中关于204这条员工信息没有匹配出来
select * from emp inner join dep on emp.dep_id=dep.id;
+----+------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------+--------+------+--------+------+--------------+
| 1 | haha | male | 18 | 200 | 200 | 技术 |
| 2 | xixi | female | 48 | 201 | 201 | 人力资源 |
| 3 | dudu | male | 38 | 201 | 201 | 人力资源 |
| 4 | lala | female | 18 | 202 | 202 | 销售 |
| 5 | nana | male | 16 | 200 | 200 | 技术 |
+----+------+--------+------+--------+------+--------------+
left join 左连接
left join 将表进行拼接，以左表为准，优先显示左表全部记录
# 以左表为准，即找出所有员工信息，当然包括没有部门的员工信息
select emp.id,emp.name,emp.sex,emp.age,emp.dep_id,dep.name from emp left join dep on emp.dep_id=dep.id;
+----+------+--------+------+--------+--------------+
| id | name | sex | age | dep_id | name |
+----+------+--------+------+--------+--------------+
| 1 | haha | male | 18 | 200 | 技术 |
| 5 | nana | male | 16 | 200 | 技术 |
| 2 | xixi | female | 48 | 201 | 人力资源 |
| 3 | dudu | male | 38 | 201 | 人力资源 |
| 4 | lala | female | 18 | 202 | 销售 |
| 6 | bubu | female | 18 | 204 | NULL |
+----+------+--------+------+--------+--------------+
# 以左表为准，即找出所有员工信息，当然包括没有部门的员工信息
select * from emp left join dep on emp.dep_id=dep.id;
+----+------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------+--------+------+--------+------+--------------+
| 1 | haha | male | 18 | 200 | 200 | 技术 |
| 5 | nana | male | 16 | 200 | 200 | 技术 |
| 2 | xixi | female | 48 | 201 | 201 | 人力资源 |
| 3 | dudu | male | 38 | 201 | 201 | 人力资源 |
| 4 | lala | female | 18 | 202 | 202 | 销售 |
| 6 | bubu | female | 18 | 204 | NULL | NULL |
+----+------+--------+------+--------+------+--------------+
right join 右连接
right join 将表进行拼接，以右表为准，优先显示右表全部记录
# 以右表为准，即找出所有部门信息，包括没有员工的部门
select emp.id,emp.name,emp.sex,emp.age,emp.dep_id,dep.name from emp right join dep on emp.dep_id=dep.id;
+------+------+--------+------+--------+--------------+
| id | name | sex | age | dep_id | name |
+------+------+--------+------+--------+--------------+
| 1 | haha | male | 18 | 200 | 技术 |
| 2 | xixi | female | 48 | 201 | 人力资源 |
| 3 | dudu | male | 38 | 201 | 人力资源 |
| 4 | lala | female | 18 | 202 | 销售 |
| 5 | nana | male | 16 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 运营 |
+------+------+--------+------+--------+--------------+
# 以右表为准，即找出所有部门信息，包括没有员工的部门
select * from emp right join dep on emp.dep_id=dep.id;
+------+------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------+--------+------+--------+------+--------------+
| 1 | haha | male | 18 | 200 | 200 | 技术 |
| 2 | xixi | female | 48 | 201 | 201 | 人力资源 |
| 3 | dudu | male | 38 | 201 | 201 | 人力资源 |
| 4 | lala | female | 18 | 202 | 202 | 销售 |
| 5 | nana | male | 16 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------+--------+------+--------+------+--------------+
union 全外连接
union 将左连接的表数据和右连接的表数据进行拼接，显示左右两个表全部记录
# 将左连接和右连接进行拼接
select emp.id,emp.name,emp.sex,emp.age,emp.dep_id,dep.name from emp left join dep on emp.dep_id=dep.id
union
select emp.id,emp.name,emp.sex,emp.age,emp.dep_id,dep.name from emp right join dep on emp.dep_id=dep.id;
+------+------+--------+------+--------+--------------+
| id | name | sex | age | dep_id | name |
+------+------+--------+------+--------+--------------+
| 1 | haha | male | 18 | 200 | 技术 |
| 5 | nana | male | 16 | 200 | 技术 |
| 2 | xixi | female | 48 | 201 | 人力资源 |
| 3 | dudu | male | 38 | 201 | 人力资源 |
| 4 | lala | female | 18 | 202 | 销售 |
| 6 | bubu | female | 18 | 204 | NULL |
| NULL | NULL | NULL | NULL | 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;
+------+------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------+--------+------+--------+------+--------------+
| 1 | haha | male | 18 | 200 | 200 | 技术 |
| 5 | nana | male | 16 | 200 | 200 | 技术 |
| 2 | xixi | female | 48 | 201 | 201 | 人力资源 |
| 3 | dudu | male | 38 | 201 | 201 | 人力资源 |
| 4 | lala | female | 18 | 202 | 202 | 销售 |
| 6 | bubu | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------+--------+------+--------+------+--------------+
连接查询案例
# 找出年龄大于18岁的员工以及员工所在的部门
select emp.id,emp.name,emp.age,dep.name from emp inner join dep on emp.dep_id = dep.id where emp.age>18;
+----+------+------+--------------+
| id | name | age | name |
+----+------+------+--------------+
| 2 | xixi | 48 | 人力资源 |
| 3 | dudu | 38 | 人力资源 |
+----+------+------+--------------+
# 以内连接的方式查询emp和dep的表，并且以age字段的升序方式显示
select emp.id,emp.name,emp.age,dep.name from emp inner join dep on emp.dep_id = dep.id order by age;
+----+------+------+--------------+
| id | name | age | name |
+----+------+------+--------------+
| 5 | nana | 16 | 技术 |
| 1 | haha | 18 | 技术 |
| 4 | lala | 18 | 销售 |
| 3 | dudu | 38 | 人力资源 |
| 2 | xixi | 48 | 人力资源 |
+----+------+------+--------------+
子查询
将一个select语句的查询结果，当作条件给另一个select语句使用！！！
1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：in、not in、any、all、exists 和 not exists等关键字
4：还可以包含比较运算符：= 、 !=、> 、<等
带in关键字的子查询
# 查询平均年龄在25岁以上的部门名
select id,name from dep where id in (select dep_id from emp group by dep_id having avg(age)>25);
+------+--------------+
| id | name |
+------+--------------+
| 201 | 人力资源 |
+------+--------------+
# 查询技术部员工姓名
select dep_id "技术部",name from emp where dep_id in (select id from dep where name = "技术");
+-----------+------+
| 技术部 | name |
+-----------+------+
| 200 | haha |
| 200 | nana |
+-----------+------+
# 查看不足1人的部门名(子查询得到的是有人的部门id)
select * from dep where id not in (select distinct dep_id from emp);
+------+--------+
| id | name |
+------+--------+
| 203 | 运营 |
+------+--------+
sql语句中 not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理
# 插入一条不属于任何组的员工数据
insert into emp(name,sex,age,dep_id) values("biubiu","male",26,null);
select * from emp;
+----+--------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+--------+--------+------+--------+
| 1 | haha | male | 18 | 200 |
| 2 | xixi | female | 48 | 201 |
| 3 | dudu | male | 38 | 201 |
| 4 | lala | female | 18 | 202 |
| 5 | nana | male | 16 | 200 |
| 6 | bubu | female | 18 | 204 |
| 7 | biubiu | male | 26 | NULL |
+----+--------+--------+------+--------+
# not in无法查询字段名包含null的数据
select * from dep where id not in (select distinct dep_id from emp);
# Empty set (0.00 sec)
select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
+------+--------+
| id | name |
+------+--------+
| 203 | 运营 |
+------+--------+
# 删除新插入的数据
delete from emp where id=7;
带any关键字的子查询
any必须和其他的比较运算符共同使用，而且any必须将比较运算符放在any关键字之前，所比较的值需要匹配子查询中的任意一个值。
= any 和 in 的用法一样 (any后面不能直接跟结果集，只能跟sql语句)
> any 大于任意一个(即范围内大于最小)
< any 小于任意一个(即范围内小于最大)
# 查询技术部门和销售部门的员工
select * from emp where dep_id in (select id from dep where name = "技术" or name = "销售");
+----+------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------+--------+------+--------+
| 1 | haha | male | 18 | 200 |
| 4 | lala | female | 18 | 202 |
| 5 | nana | male | 16 | 200 |
+----+------+--------+------+--------+
# =any跟in的用法一样(等于任意一个)
select * from emp where dep_id =any (select id from dep where name = "技术" or name = "销售");
+----+------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------+--------+------+--------+
| 1 | haha | male | 18 | 200 |
| 4 | lala | female | 18 | 202 |
| 5 | nana | male | 16 | 200 |
+----+------+--------+------+--------+
# 每个部门的平均年龄
select dep_id,avg(age) from emp group by dep_id;
+--------+----------+
| dep_id | avg(age) |
+--------+----------+
| 200 | 17.0000 |
| 201 | 43.0000 |
| 202 | 18.0000 |
| 204 | 18.0000 |
+--------+----------+
# 查询员工表中所有员工年龄大于任意部门平均年龄的员工信息(即大于平均年龄最小的部门)
select * from emp where age >any (select avg(age) from emp group by dep_id);
+----+------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------+--------+------+--------+
| 1 | haha | male | 18 | 200 |
| 2 | xixi | female | 48 | 201 |
| 3 | dudu | male | 38 | 201 |
| 4 | lala | female | 18 | 202 |
| 6 | bubu | female | 18 | 204 |
+----+------+--------+------+--------+
# 查询员工表中所有员工年龄小于任意部门平均年龄的员工信息（即小于平均年龄最大的部门）
select * from emp where age <any (select avg(age) from emp group by dep_id);
+----+------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------+--------+------+--------+
| 1 | haha | male | 18 | 200 |
| 3 | dudu | male | 38 | 201 |
| 4 | lala | female | 18 | 202 |
| 5 | nana | male | 16 | 200 |
| 6 | bubu | female | 18 | 204 |
+----+------+--------+------+--------+
带all关键字的子查询
all用法和any类似，只不过all表示的是所有，any表示任一个值。
> all 大于全部(即范围内大于最大) < all 小于全部(即范围内小于最小)
# 查询员工表中所有员工年龄大于全部部门平均年龄的员工信息（即大于平均年龄最大的部门）
select * from emp where age >all (select avg(age) from emp group by dep_id);
+----+------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------+--------+------+--------+
| 2 | xixi | female | 48 | 201 |
+----+------+--------+------+--------+
# 查询员工表中所有员工年龄小于全部部门平均年龄的员工信息（即小于平均年龄最小的部门）
select * from emp where age < all (select avg(age) from emp group by dep_id);
+----+------+------+------+--------+
| id | name | sex | age | dep_id |
+----+------+------+------+--------+
| 5 | nana | male | 16 | 200 |
+----+------+------+------+--------+
exists布尔值判断(了解即可)
只返回布尔值 True or False 返回True的时候外层查询语句执行 返回False的时候外层查询语句不再执行
# 查询哪一个部门里面是没有员工的
# 查询结果跟原表做对比,203运营部门里面是没有人的
select * from dep where exists(select * from emp where emp.dep_id=dep.id);
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
+------+--------------+
exist可以这么去理解:
for line in dep: # 读出dep表中的每一行
for lines in emp: # 读出emp表中的每一行
where emp.dep_id = dep.id # 取出两张表的部门id做对比,判断有没有输出结果(true或者false)
多表查询总结
多表查询就两种方式
1. 连接查询：先拼接，再查询
2. 子查询：一步一步来
714
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
