MySQL多表关系及多表查询

1. 多表关系

1.1 多表关系分类与实现

（1）一对一

• 如：人和身份证
• 分析：一个人只有一张身份证，一张身份证也只能对应一个人。
• 实现方式：可以在任意一方添加唯一外键（unique）指向另一方的主键。

（2）一对多（多对一）

• 如：部门和员工
• 分析：一个部门有多个员工，一个员工只能对应一个部门。
• 实现方式：在多的一方建立外键，指向一的一方的主键。

（3）多对多

• 如：学生和课程
• 分析：一个学生可以选择多门课程，一门课程也可以被很多学生选择。
• 实现方式：需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

1.2 数据库设计的范式

（1）概念：设计数据库时，需要遵循的一些规范。要遵循后面的范式要求，必须先遵循前面的所有范式要求。
（2）分类

• 普通表（不是其中一类，仅拿出来做对比）
  
• 第一范式（INF）
  定义：每一列都是不可分割的原子数据项。
  分析：在普通表中，不满足第一范式要求的 每一列都是不可分割的原子数据项。如：系 可以分为 系名和系主任两列原子数据项。
  
• 第二范式（2NF）
  定义：在1NF基础上，非码属性必须完全依赖于码。（在1NF基础上，消除非主属性对主码的部分函数依赖。）
  几个概念：
  ① 函数依赖： A–>B，如果通过A属性（属性组）的值，可以确定唯一B属性的值，则称B依赖于A。
    例如：学号–>姓名，即通过学号可以确定唯一的学生，此时姓名依赖于学号。
    或者：（学号，课程名称）–>分数，即通过学号和课程名称这个属性组唯一确定分数，此时分数依赖于（学号，课程名称）属性组。
  ② 完全函数依赖： A–>B，如果A为属性组，则B属性值的确定需要依赖A属性组的所有属性值。
    例如：（学号，课程名称）—>分数，即分数的确定需要依赖属性组中的学号和课程名称，单独用其中某个属性不能确定分数，此时就称分数 完全依赖 于（学号，课程名称）属性组。
  ③ 部分函数依赖： A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某些值即可。
    例如：（学号，课程名称）—>姓名，即通过学号就可以确定唯一的姓名，不需要课程名称。这就称姓名 部分依赖 于（学号，课程名称）属性组。
  ④ 传递函数的依赖： A–>B，B–>C。如果通过A属性（属性组）的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称C 传递函数依赖于 A。
  ⑤ 码： 如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码。
    例如：该表中码为属性组（学号，课程名称）。
  ⑥ 主属性： 码属性组中的所有属性。
  ⑦ 非主属性： 除了码属性组的属性。
  分析：
    满足1NF的表中存在非常严重的数据冗余（重复）：如姓名、系名、系主任三列；
    满足1NF的表中数据添加存在问题：如添加一个新开设的系和系主任时，数据不合法；
    满足1NF的表中数据删除存在问题：如张无忌同学毕业，删除该同学数据，会将系的数据一起删除。
    因此，在1NF的基础上，消除非主属性对主码的部分函数依赖。可以将满足1NF的表分为 选课表 和 学生表，也即消除非主属性对主码（学号，课程名称）的部分函数依赖。
  
• 第三范式（3NF）
• 定义：在2NF的基础上，任何非主属性不依赖于其他非主属性。（在2NF基础上消除函数传递依赖）
• 分析：
    满足2NF的表中数据添加存在问题：如添加一个新开设的系和系主任时，数据不合法；
    满足2NF的表中数据删除存在问题：如张无忌同学毕业，删除该同学数据，会将系的数据一起删除。
    因此，在2NF的基础上，消除函数传递依赖。可以将满足2NF的学生表分为 学生表 和 系表，也即消除非主属性对其他非主属性的函数传递依赖。此时，系主任在系表中只依赖于系名；系名在学生表中只依赖于学号。而非2NF中，系主任在学生表中依赖系名，系名在学生表中依赖于学号，而造成学生表中产生函数传递依赖。
  

2. 多表查询

（1）以下各查询方法的实例演示基于 部门表dept 和 员工表emp。其中员工表中的外键关联部门表的主键id。

# 创建部门表
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
# 向部门表插入数据
INSERT INTO dept(NAME)VALUE('开发部'),('市场部'),('财务部');

# 查询部门表
SELECT * FROM dept;

# 创建员工表
CREATE TABLE emp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    gender VARCHAR(10),    -- 性别
    salary DOUBLE,        -- 工资
    join_date DATE,        -- 日期
    dept_id INT,
    FOREIGN KEY(dept_id) REFERENCES dept(id) -- 外键,关联部门表（部门表的主键）
);

# 查询员工表
SELECT * FROM emp;

# 向员工表插入数据
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-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('唐僧','男',9000,'2008-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('沙悟净','男',8000,'2012-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('白骨精','女',5000,'2011-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('蜘蛛精','女',1200,'2019-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('小白龙','男',3000,'2018-02-24');

（2）以下分别是 部门表dept 和 员工表emp 查询结果。

2.1 笛卡尔积

（1）笛卡尔积也就是取两张表的所有组成情况。

# 笛卡尔积 emp,dept
SELECT * FROM emp,dept;

（2）emp 和 dept表笛卡尔积查询结果：

2.2 内连接查询

2.2.1 隐式内连接

（1）语法

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

（2）示例

# 查询所有员工信息和对应部门信息
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;

# 查询员工表的姓名，性别和部门表的部门名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

# 查询员工表的姓名，性别和部门表的部门名称（用别名，且推荐此sql编写方式）
SELECT
    t1.name,
    t1.gender,
    t2.name
FROM
    emp t1,
    dept t2
WHERE
    t1.dept_id = t2.id;

（3）查询结果

2.2.2 显式内连接

（1）语法

select 字段列表 from 表名1 [inner] join 表名2 on 条件;  #（inner可以省略）

（2）示例

# 显式内连接 查询员工表的姓名，性别和部门表的部门名称（起别名）
SELECT
    t1.name,
    t1.gender,
    t2.name
FROM
    emp t1
JOIN
    dept t2
ON
    t1.dept_id = t2.id;

（3）查询结果

2.3 外连接查询

2.3.1 左外连接

（1）左外连接查询的是 左表所有数据以及其交集部分（交集部分也就是满足条件的数据）。（而内连接本质上是只查询交集部分）
（2）语法

select 字段列表 from 表名1 left [outer] join 表2 on 条件;  #outer可省略

（3）示例

# 左外连接
SELECT
    t1.name,
    t1.gender,
    t2.name
FROM
    emp t1
LEFT JOIN
    dept t2
ON
    t1.dept_id = t2.id;

（4）查询结果

2.3.2 右外连接

（1）右外连接查询的是 右表所有数据以及其交集部分（交集部分也就是满足条件的数据）。
（2）语法

select 字段列表 from 表名1 right [outer] join 表2 on 条件;  #outer可省略

（3）示例

# 左外连接
# 右外连接
SELECT
    t1.name,
    t1.gender,
    t2.name
FROM
    emp t1
RIGHT JOIN
    dept t2
ON
    t1.dept_id = t2.id;

（4）查询结果

2.4 子查询

（1）概念
  查询中的嵌套查询，称嵌套查询为子查询。
（2）子查询的不同情况

• 子查询的结果是单行单列的，则子查询可以作为条件，使用运算符去判断。如：> >= < <= = != <>

# 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

• 子查询的结果是多行单列的，则子查询可以作为条件使用运算符in来判断。

# 查询’财务部'和'市场部'所有的员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

• 子查询的结果是多行多列的，则子查询可以作为虚拟表参与查询

# 查询员工的入职日期是2011-1-1之后的员工信息和部门信息（此sql仅仅为了测试子查询结果是多行多列的情况）
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-1-1') t2 WHERE t1.id = t2.dept_id;

# 上面的查询用普通的内连接方式更简便
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-1-1') t2 WHERE t1.id = t2.dept_id;