-- 1. 查询 "教研部" 和 "咨询部" 的部门ID select id from dept where name = '教研部' or name = '咨询部'; -- 结果: 3,2 -- 2. 根据上面查询出来的部门ID, 查询员工信息 select * from emp where dept_id in(3,2); -- 3. 合并SQL为一条SQL语句 select * from emp where dept_id in (select id from dept where name = '教研部' or name = '咨询部');

一，多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)

• 多对多

• 一对一

一对多

场景：部门与员工的关系（一个部门下有多个员工）。

由于一个部门下，会关联多个员工。 而一个员工，是归属于某一个部门的 。那么此时，我们就需要在emp表中增加一个字段dept_id来标识这个员工属于哪一个部门，dept_id关联的是dept的id。 如下所示：

上述的emp员工表的dept_id字段，关联的是dept部门表的id。部门表是一的一方，也称为父表，员工表是多的一方，称之为子表。

一对多的表关系，在数据库层面实现方式

在数据库表中多的一方，添加字段，来关联一的一方的主键 。

一对一

一对一关系表在实际开发中应用起来比较简单，通常是用来做单表的拆分，也就是将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表当中，将其他的字段放在另外一张表当中，以此来提高数据的操作效率。

一对一的应用场景： 用户表(基本信息+身份信息)

• 基本信息：用户的ID、姓名、性别、手机号、学历

• 身份信息：民族、生日、身份证号、身份证签发机关，身份证的有效期(开始时间、结束时间)

如果在业务系统当中，对用户的基本信息查询频率特别的高，但是对于用户的身份信息查询频率很低，此时出于提高查询效率的考虑，我就可以将这张大表拆分成两张小表，第一张表存放的是用户的基本信息，而第二张表存放的就是用户的身份信息。他们两者之间一对一的关系，一个用户只能对应一个身份证，而一个身份证也只能关联一个用户。

通过外键来体现一对一之间的关系，只需要在任意一方来添加一个外键就可以了。

多对多

多对多的关系在开发中属于也比较常见的。比如：学生和老师的关系，一个学生可以有多个授课老师，一个授课老师也可以有多个学生。在比如：学生和课程的关系，一个学生可以选修多门课程，一个课程也可以供多个学生选修。

案例：学生与课程的关系

• 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

• 实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

多对多 ：需要建立一张中间表，中间表中有两个外键字段，分别关联两方的主键。

二，多表查询

1.介绍

多表查询：查询时从多张表中获取所需数据

单表查询的SQL语句：select 字段列表 from 表名;

那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;

查询用户表和部门表中的数据：

select * from emp , dept;

此时，我们看到查询结果中包含了大量的结果集，总共180条记录，而这其实就是员工表所有的记录(30行)与部门表所有记录(6行)的所有组合情况，这种现象称之为笛卡尔积。

笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。

在多表查询时，需要消除无效的笛卡尔积，只保留表关联部分的数据。

在SQL语句中，去除无效的笛卡尔积，只需要给多表查询加上连接查询的条件即可。

select * from emp , dept where emp.dept_id = dept.id ;

2.分类

多表查询可以分为：

1. 连接查询

   1. 内连接：相当于查询A、B交集部分数据

   2. 外连接

      • 左外连接：查询左表所有数据(包括两张表交集部分数据)

      • 右外连接：查询右表所有数据(包括两张表交集部分数据)

2. 子查询

3.内连接

内连接查询：查询两表或多表中交集部分数据。

内连接从语法上可以分为：

• 隐式内连接

• 显式内连接

隐式内连接语法：

select 字段列表 from 表1 , 表2 where 条件 ... ;

显式内连接语法：

select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;

• 案例1：查询所有员工的ID，姓名，及所属的部门名称

隐式内连接实现

select emp.id, emp.name, dept.name from emp , dept where emp.dept_id = dept.id;

显式内连接实现

select emp.id, emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;

在多表联查时，我们指定字段时，需要在字段名前面加上表名，来指定具体是哪一张的字段。 如：emp.dept_id

给表起别名简化书写：

select 字段列表 from 表1 as 别名1 , 表2 as 别名2 where 条件 ... ; select 字段列表 from 表1 别名1 , 表2 别名2 where 条件 ... ; -- as 可以省略

4.外连接

外连接分为两种：左外连接 和 右外连接。

左外连接语法：

select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

右外连接语法：

select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

案例1：查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)

-- 左外连接：以left join关键字左边的表为主表，查询主表中所有数据，以及和主表匹配的右边表中的数据 select e.name , d.name from emp as e left join dept as d on e.dept_id = d.id ;

案例2：查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)

-- 右外连接：以right join关键字右边的表为主表，查询主表中所有数据，以及和主表匹配的左边表中的数据 select e.name , d.name from emp as e right join dept as d on e.dept_id = d.id;

注意事项：

左外连接和右外连接是可以相互替换的，只需要调整连接查询时SQL语句中表的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

5.子查询

5.1.介绍

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ... );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

根据子查询结果的不同分为：

1. 标量子查询（子查询结果为单个值 [一行一列]）

2. 列子查询（子查询结果为一列，但可以是多行）

3. 行子查询（子查询结果为一行，但可以是多列）

4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询可以书写的位置：

1. where之后

2. from之后

3. select之后

子查询的要点是，先对需求做拆分，明确具体的步骤，然后再逐条编写SQL语句。 最终将多条SQL语句合并为一条。

5.2.标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。

常用的操作符： = <> > >= < <=

案例1：查询 最早入职 的员工信息

-- 1. 查询最早的入职时间 select min(entry_date) from emp; -- 结果: 2000-01-01 -- 2. 查询入职时间 = 最早入职时间的员工信息 select * from emp where entry_date = '2000-01-01'; -- 3. 合并为一条SQL select * from emp where entry_date = (select min(entry_date) from emp);

5.3.列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。

常用的操作符：

案例1：查询 "教研部" 和 "咨询部" 的所有员工信息

-- 1. 查询 "教研部" 和 "咨询部" 的部门ID select id from dept where name = '教研部' or name = '咨询部'; -- 结果: 3,2 -- 2. 根据上面查询出来的部门ID, 查询员工信息 select * from emp where dept_id in(3,2); -- 3. 合并SQL为一条SQL语句 select * from emp where dept_id in (select id from dept where name = '教研部' or name = '咨询部');

5.4.行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

案例1：查询与 "李忠" 的薪资 及 职位都相同的员工信息

-- 1. 查询 "李忠" 的薪资和职位 select salary , job from emp where name = '李忠'; -- 结果: 5000, 5 -- 2. 根据上述查询到的薪资和职位 , 查询对应员工的信息 select * from emp where (salary, job) = (5000,5); -- 3. 将两条SQL合并为一条SQL select * from emp where (salary, job) = (select salary , job from emp where name = '李忠');

5.5表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

案例：获取每个部门中薪资最高的员工信息

-- a. 获取每个部门的最高薪资 select dept_id, max(salary) from emp group by dept_id; -- b. 查询每个部门中薪资最高的员工信息 select * from emp e , (select dept_id, max(salary) max_sal from emp group by dept_id) a where e.dept_id = a.dept_id and e.salary = a.max_sal;