多表关系和多表查询

多表关系

多表分为三类：

一对多：

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

一的一方为父表，多的一方为主表

如何实现呢？

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

创建代码：

物理外键：

{

创建表时指定：

create table 表名( 字段名 数据类型, ... [constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名) );

建完表后，添加外键 alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

【alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);】

物理外键仅为了解，市场不在使用

原因：

1、影响增、删、改的效率（需要检查外键关系）。 2、仅用于单节点数据库，不适用与分布式、集群场景。 3、容易引发数据库的死锁问题，消耗性能。

}

一对一：

一对一属于只用一对多的特殊情况，创建方法和一对多相同，不同的是他可以在任何一个表中创建外键。

注意在设计外键的时候需要对外键设置唯一

多对多：

多对多的实现是在两个表中间创建一个中间表，在表中添加两个表的外键字段，并且关联两个表的主键字段。

多表查询

内连接

内连接查询的是两张表交集部分的数据

语法为

-- 1.隐式内连接 (常见) select 字段列表 from 表1 , 表2 where 连接条件 ...;

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

-- 1.给表起别名,来简化书写 select 字段列表 from 表1 as 别名1, 表2 as 别名2 where 条件 ...；

一旦为表指定了别名，就要通过别名来指定字段名，而不能再使用表名了。

外连接

外连接分为左外连接和右外连接。

对于外连接，常用的是左外连接，因为右外连接的SQL也可以改造成为左外连接（两张表调换个顺序）。

-- 1.左外连接 (常见) select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ...;

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

子查询：

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

形式：

select * from t1 where column1 = (select column1 from t2 …);

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

子查询的分类：

1、标量子查询：子查询返回的结果为单个值 2、列子查询：子查询返回的结果为一列 3、行子查询：子查询返回的结果为一行 4、表子查询：子查询返回的结果为多行多列

注意：子查询的要点是，先对需求做拆分，明确具体的步骤，然后再逐步编写SQL语句

代码的演示

-- 1. 查询 最早入职 的员工信息
​
select * from emp where entry_date = (select min(entry_date) from emp);
​
​
-- 2. 查询在 "阮小五" 入职之后入职的员工信息
​
select * from emp where entry_date>(select entry_date from emp where name = '阮小五');
​
​
-- 列子查询
-- 3. 查询 "教研部" 和 "咨询部" 的所有员工信息
​
select * from emp where job in (select dept.id from dept where name in ('教研部','咨询部'));
​
​
-- 行子查询
-- 4. 查询与 "李忠" 的薪资 及 职位都相同的员工信息 ;
​
select * from emp where (salary,job) = (select salary,job from emp where name = '李忠' );
​
​
​
-- 表子查询
-- 5. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
​
select a.* ,d.name from (select * from emp where entry_date >2006-01-01) a,dept d where a.dept_id = d.id;