06 | 多表查询

6.1 外键约束

主键：数据表A中有一列，这一列可以唯一的标识一条记录
外键：数据表A中有一列，这一列指向了另外一张数据表B的主键

1）什么是外键
外键指的是在 从表 中 与 主表 的主键对应的那个字段，比如员工表的 dept_id，就是外键。
使用外键约束可以让两张表之间产生一个对应关系，从而保证主从表的引用的完整性。

多表关系中的主表和从表
主表：主键id 所在的表, 约束别人的表
从表：外键 所在的表, 被约束的表
添加外键：

create table department(
    id int primary key,
    dep_name varchar(10),
    dep_location varchar(10)
);
insert into department values (1,'开发部','北京');
insert into department values (2,'销售部','上海');
CREATE TABLE employee(
        eid INT PRIMARY KEY AUTO_INCREMENT,
        ename VARCHAR(20),
        age INT,
        dept_id INT,  -- 外键字段数据类型要和主表的主键字段数据类型保持一致
        -- 添加外键约束
        CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

删除外键：

# 语法格式：
alter table 从表 drop foreign key 外键约束名称
# 举例：
alter table employee drop foreign key emp_dept_fk

再将外键添加回来：

alter table employee add foreign key(dept_id) REFERENCES department(id)

外键约束的注意事项:
① 从表外键类型必须与主表主键类型一致，否则创建失败。
② 添加数据时，应该先添加主表中的数据。
③ 删除数据时，应该先删除从表中的数据。

级联删除操作(了解)
如果想实现删除主表数据的同时，也删除掉从表数据，可以使用级联删除操作。
关键字：ON DELETE CASCADE

-- 重新创建添加级联操作
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT,
    CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
    -- 添加级联删除
    ON DELETE CASCADE
);

6.2 多表查询

1）内连接查询
内连接的特点:
通过指定的条件去匹配两张表中的数据，匹配上就显示，匹配不上就不显示。
比如通过：从表的外键 = 主表的主键 方式去匹配
① 隐式内连接
form子句 后面直接写 多个表名 使用 where 指定连接条件的，这种连接方式是 隐式内连接。
使用 where 条件过滤无用的数据。
② 显式内连接
使用 inner join …on 这种方式，就是显式内连接

2） 外连接查询
① 左外连接

• 左外连接，使用 left outer join，outer 可以省略
• 特点：
  • 以左表为基准，匹配右边表中的数据，如果匹配的上，就显示匹配到的数据
  • 如果匹配不到，左表中的数据正常展示，右边表展示为NULL
• 语法格式：
  • select 字段名 from 左表 left join 右表 on 条件

② 右外连接

• 右外连接 , 使用 right outer join，outer可以省略
• 特点：
  • 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
  • 如果匹配不到，右表中的数据正常展示，左边展示为NULL
• 语法格式：
  • select 字段名 from 左表 right join 右表 on 条件

3）各种连接方式的总结

内连接：inner join , 只获取两张表中 交集部分的数据
左外连接：left join , 以左表为基准 ,查询左表的所有数据，以及与右表有交集的部分。
右外连接：right join , 以右表为基准,查询右表的所有的数据，以及与左表有交集的部分。
内连接和左外连接使用居多

6.5 合并查询

1）UNION
UNION 操作符用于合并两个或多个 SELECT 语句的结果集，并消除重复行。
注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同
时，每条 SELECT 语句中的列的顺序必须相同。

SELECT Id,Name,Amount,Date
FROM customers
LEFT JOIN orders
on customers.Id = orders.Customers_Id
UNION
SELECT Id,Name,Amount,Date
from customers
RIGHT JOIN orders
on customers.Id = orders.Customers_Id;

注意：
1. 选择的列数必须相同；
2. 所选列的数据类型必须在相同的数据类型组中（如数字或字符）
3. 列的名称不必相同
4. 在重复检查期间，NULL值不会被忽略
2）UNION ALL
UNION ALL 运算符用于将两个 SELECT 语句的结果组合在一起，重复行也包含在内。
UNION ALL 运算符所遵从的规则与 UNION 一致。
总结：
UNION 和 UNION ALL 关键字都是将两个结果集合并为一个，但也有区别。
1、重复值：UNION 在进行表连接后会筛选掉重复的记录，而 UNION All不会去除重复记录。
2、UNION ALL 只是简单的将两个结果合并后就返回。
3、2）UNION ALL
UNION ALL 运算符用于将两个 SELECT 语句的结果组合在一起，重复行也包含在内。
UNION ALL 运算符所遵从的规则与 UNION 一致。
总结：
UNION和UNION ALL关键字都是将两个结果集合并为一个，也有区别。
1、重复值：UNION在进行表连接后会筛选掉重复的记录，而Union All不会去除重复记录。
2、UNION ALL只是简单的将两个结果合并后就返回。
3、在执行效率上，UNION ALL 要比UNION快很多，因此，若可以确认合并的两个结果集中不
包含重复数据，那么就使用UNION ALL。

6.6子查询

1）什么是子查询

• 概念：
  • 一条 select 查询语句的结果，作为另一条 select 语句的一部分
• 特点：
  • 子查询必须放在小括号中
  • 整个 sql 语句中至少会有两个 select 关键字
• 子查询常见分类：
  • where 型子查询：将子查询的结果作为 where表达式 的条件
  • from 型子查询：将子查询的结果作为一张表，提供给父查询使用
  • exits 型子查询：子查询的结果是单列多行，类似一个数组，父层查询使用 IN 函数，即 字段 in (子查询结果)

2）查询的结果作为查询条件
3）子查询的结果作为一张表
4）子查询结果是单列多行