多表关系和建表原则（带例子）

一. 多表关系和建表原则

1、多表之间的关系

（1）一对多关系

• 部门和员工
• 一个部门有很多员工，一个员工属于一个部门
• 部门是一，员工是多

（2）多对多关系

• 学生和课程
• 一个学生可以选择多门课程，一门课程可以被多个学生选择

(3)一对一关系

• 人和身份证号
• 一个人只有一个身份证号，一个身份证号对应一个人

2、一对多关系建表

（2）在多的那一方创建字段，作为外键，关联一的那一方主键

3、多对多建表

4、一对一建表

（1）主键对应建表

5、以一对多为例创建表，使用外键

 # 创建部门表
CREATE TABLE dept (
  did INT PRIMARY KEY,
  dname VARCHAR(100)
)

### 创建员工表
CREATE TABLE emp (
  eid INT PRIMARY KEY,
  ename VARCHAR(100),
  age INT,
  edid INT,
  FOREIGN KEY(edid) REFERENCES dept(did) 
)
# 声明外键

6、外键主要作用：

（1）用于数据关联
（2）外键保证数据完整性和一致性

• 添加员工时候，添加部门id，这个部门id必须是部门表id
• 删除部门时候，如果部门下面有关联员工，部门不能删除的

7、实际创建表时候，一般外键不声明出来，把作为关联使用就可以了

二. Mysql多表关联查询操作

1、笛卡尔积

2、内连接查询

（1）获取两张表有关联数据

3、外连接-左外连接

（1）左边表所有数据，右边表关联数据

4、外连接-右外连接

（1）右边表所有数据，左边表关联数据

三、编写sql语句实现mysql多表关联查询

1、笛卡尔积

# 笛卡尔积

> SELECT * FROM dept,emp

2、内连接查询

# 内连接查询
# select 字段 from 表1  inner  join  表2  on  关联条件
SELECT * FROM dept INNER JOIN emp ON dept.did=emp.edid

SELECT * FROM dept,emp WHERE dept.did=emp.edid

3、外连接-左外连接

# 外连接-左外连接
# select 字段 from 表1 left outer join 表2  on  关联条件
SELECT * FROM dept LEFT OUTER JOIN emp ON dept.did=emp.edid

4、外连接-右外连接

# 外连接-右外连接
# select 字段 from 表1 right outer join 表2  on  关联条件
SELECT * FROM dept RIGHT OUTER JOIN emp ON dept.did=emp.edid

5、A表独有数据

# A表独有
SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid
WHERE emp.edid IS NULL

6、B表独有数据

# B表独有数据
SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid
WHERE dept.did IS NULL

7、AB全有（类似于全连接效果）

# AB全有
SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid
UNION
SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid

8、A独有 + B独有

# A独有 + B独有
SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid WHERE emp.edid IS NULL
UNION
SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid WHERE dept.did IS NULL