渐写到别来，此情深处，红笺为无色。

——晏几道《思远人》

MySQL数据库-多表查询和子查询

1. 多表查询

数据库在单个表里操作其实很简单，但是涉及在多张表里寻找数据的时候，难度会大大增加，这里解释一些多表联合查询常用的操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 创建部门表
create table dept(
    id int primary key auto_increment,
	name varchar(20)
)
-- 插入部门表信息
insert into dept (name) values ('开发部'),('市场部'),('财务部');


-- 创建员工表
create table emp (
    id int primary key auto_increment,
    name varchar(10),
    gender char(1),	-- 性别
    salary double,	-- 工资
    join_date date,	-- 入职日期
    dept_id int,
    foreign key (dept_id) references dept(id) 
    -- 外键，关联部门表(部门表的主键)
）;

insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女 ',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女 ',4500,'2011-03-14',1);

多表查询：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。

如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。

1.1 多表查询分类

1
2
3
4
5
6
7
graph TD;
多表查询-->内连接;
多表查询-->外连接;
内连接-->显示内连接;
内连接-->隐式内连接;
外连接-->左外连接;
外连接-->右外连接;

1.2 笛卡尔积现象

1.2.1 什么是笛卡尔积现象

1
2
--	需求：查询所有的员工和所有的部门 
select * from emp,dept;

结果分析

1.2.2 清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

1
2
3
4
5
-- 设置过滤条件 Column 'id' in where clause is ambiguous select * from emp,dept where id=5;
select * from emp,dept where emp.dept_id = dept.id;

-- 查询员工和部门的名字
select emp.name, dept.name from emp,dept where emp.dept_id = dept.id;

1.3 内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

1.3.1 显式内连接

使用 inner join… on语句, 可以省略 inner

1
select 字段名 from 左表 [inner] join 右表 on 条件;

1.3.2 隐式内连接

看不到 join关键字，条件使用 where指定

1
2
3
4
select 字段名 from 左表,右表 where 条件;

-- 演示
select * from emp,dept where emp.dept_id = dept.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- 创建一个dept表，设置id字段为主键，并且设置主键自增
create table dept(
	id int primary key auto_increment,
    name varchar(20)
)

-- 向dept表插入数据
insert into dept values(null,'开发部'),(null,'市场部'),(null,'财务部'),(null,'销售部')

-- 创建emp1表，设置id字段为主键,并且设置主键自增
create table emp1(
	id int primary key auto_increment,
	name varchar(20),
    gender varchar(10),
    salary double,
    join_date datetime,
    dept_id int
)

-- 向emp1表插入数据
insert into emp1 values(null,'孙悟空','男',888.8,null,1);
insert into emp1 values(null,'唐僧','男',888.8,null,2);
insert into emp1 values(null,'八戒','男',888.8,null,3);
/*
	查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称.
	我们发现需要联合 2 张表同时才能查询出需要的数据，
	使用内连接  inner join on 
*/

select * from emp1 inner join dept; -- 这个查到12条记录
-- 确定表连接的条件，即  员工表.dept_id = 部门表.id 的数据才是我们向查到的
select * from emp1 e inner join dept d on e.dept_id = d.id; -- 这个查到三条数据

-- 确定查询条件，我们查询的是唐僧的信息，  员工表.name='唐僧'
select * from emp1 e inner join dept d on e.dept_id = d.id where e.name='唐僧 ';

-- 确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称
select e.id,e.name,e.gender,e.salary,d.name from emp1 e inner join dept d on e.dept_id = d.id where e.name='唐僧';


-- 我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名
select e.id 编号,e.name 姓名,e.gender 性别,e.salary 工资,d.name 部门名字 from emp1 e inner join dept d on e.dept_id= d.id where e.name='唐僧';

1.3.3 内连接总结

  1. 确定查询哪些表
  2. 确定表连接的条件 e.dept_id = d.id
  3. 确定查询的条件 where e.name=’唐僧‘
  4. 确定查询的字段 ** ,e.name,e.gender,e.salary,d.name**

1.4 外连接

1.4.1 左外连接

left outer join … on ，outer可以省略

用左边表的记录去匹配右边表的记录，

如果符合条件的则显示；

否则，显示 NULL

可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

1
2
3
4
5
6
7
8
9
10
11
select 字段名 from 左表 left [outer] join 右表 on 条件;

--	在部门表中增加一个销售部
insert into dept (name) values ('销售部');


--	使用内连接查询
select * from dept d inner join emp1 e on d.id = e.dept_id;

--	使用左外连接查询
select * from dept d left join emp1 e on d.id = e.dept_id;

1.4.2 右外连接

right outer join … on ,outer 可以省略

用右边表的记录去匹配左边表的记录，

如果符合条件的则显示；

否则，显示 NULL 可以理解为：在内连接的基础上保证右表的数据全部显示

1
2
3
4
5
6
7
8
9
-- 在员工表中增加一个员工
insert into emp1 values (null,'沙僧','男',6666,'2013-12-05',null); select * from emp1;

-- 使用内连接查询
select * from dept inner join emp1 on dept.id = emp1.`dept_id`;


-- 使用右外连接查询
select * from dept right join emp1 on dept.id = emp.dept_id;

2. 子查询

子查询的概念：

  • 一个查询的结果做为另一个查询的条件
  • 有查询的嵌套，内部的查询称为子查询
  • 子查询要使用括号
1
2
-- 查询开发部中有哪些员工
select * from emp1 where dept_id = (select id from dept where name='市场部');

2.1 子查询结果的三种情况

2.1.1 子查询的结果是单行单列

子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，

父查询使用：比较运算符，如：> 、<、<>、= 等

==select 查询字段 from 表 where 字段=(子查询);==

2.1.2 子查询的结果是多行单列

子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符

==select 查询字段 from 表 where 字段 in （子查询）;==

2.1.3 子查询的结果是多行多列

子查询结果只要是多列，肯定在 from 后面作为表

==select 查询字段 from （子查询） 表别名 where 条件;==

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

2.1.4 子查询总结

  • 子查询结果只要是单列，则在 where后面作为条件
  • 子查询结果只要是多列，则在 from 后面作为表进行二次查询