mysql设计表的外键_MySQL - 多表&外键&数据库设计

MySQL - 多表&外键&数据库设计

1 多表

1.1 多表的意义

实际开发中，一个项目通常需要很多张表才能完成。

例如一个商城项目的数据库,需要有很多张表：用户表、分类表、商品表、订单表....

1.2 单表的缺点

1.2.1 冗余

同一个字段中出现大量的重复数据

1.2.2 多表方式设计解决

department 部门表 : id, dep_name, dep_location employee

员工表: eid, ename, age, dep_id

拥有外键的员工表 被称为 从表 , 与外键对应的主键所在的表叫做 主表

1.3 多表设计上的问题

可以插入部门表不存在的id数据

可以使用外键约束解决

1.3.1 多表中主表和从表

主表: 主键id所在的表, 约束别人的表

从表: 外键所在的表多, 被约束的表

1.4 外键约束

1.4.1 外键约束的概念

外键指的是在 从表 中 与 主表 的主键对应的那个字段,比如员工表的 dept_id,就是外键

使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性

1.4.2 创建外键约束

1.4.2.1 新建表时添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

1.4.2.2 已有表添加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

示例：

-- 重新创建 employee表,添加外键约束

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)

);

1.4.3 删除外键约束

语法格式：

alter table 从表 drop foreign key 外键约束名称

示例：

-- 删除employee 表中的外键约束,外键约束名 emp_dept_fk

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

1.4.4 外键约束的注意事项

从表外键类型必须与主表主键类型一致 否则创建失败.

添加数据时, 应该先添加主表中的数据.

删除数据时,应该先删除从表中的数据.

1.4.5 级联删除操作

如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作

语法格式：

级联删除

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

);

2 多表关系设计

2.1 表与表之间的三种关系

表与表之间的三种关系

一对多关系: 最常见的关系, 学生对班级,员工对部门

多对多关系: 学生与课程, 用户与角色

一对一关系: 使用较少,因为一对一关系可以合成为一张表

2.2 一对多关系

2.2.1 一对多建表原则

在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

2.3 多对多关系

2.3.1 多对多建表原则

需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

2.4 一对一关系

2.4.1 一对一建表原则

外键唯一 主表的主键和从表的外键(唯一)，形成主外键关系，外键唯一 UNIQUE

3 多表查询

3.1 多表查询的概念

DQL: 查询多张表,获取到需要的数据

比如 我们要查询家电分类下 都有哪些商品,那么我们就需要查询分类与商品这两张表

3.2 笛卡尔积

交叉连接查询,因为会产生笛卡尔积,所以基本不会使用

语法格式：

SELECT 字段名 FROM 表1, 表2;

笛卡尔积：

假设集合A={a, b}，集合B={0, 1, 2}

则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

3.3 多表查询的分类

3.3.1 内连接查询

内连接的特点：

通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示

比如通过: 从表的外键 = 主表的主键 方式去匹配

3.3.1.1 隐式内连接

from子句后面直接写多个表名 使用where指定连接条件的 这种连接方式是 隐式内连接. 使用where条件过滤无用的数据

语法格式：

SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;

示例：

# 隐式内连接

SELECT * FROM products,category WHERE category_id = cid;

3.3.1.2 表起别名

可以通过给表起别名的方式, 方便我们的查询(有提示)

示例：

SELECT

p.`pname`,

p.`price`,

c.`cname`

FROM products p , category c WHERE p.`category_id` = c.`cid`;

3.3.1.3 显示内连接

使用 inner join ...on 这种方式, 就是显式内连接

语法格式:

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

-- inner 可以省略

3.3.2 外连接查询

3.3.2.1 左外连接

左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略

左外连接的特点：

以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据

如果匹配不到, 左表中的数据正常展示, 右边的展示为null

语法格式:

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

示例：

-- 左外连接查询

SELECT * FROM category c LEFT JOIN products p ON c.`cid`= p.`category_id`;

3.3.2.2 右外连接

右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略

右外连接的特点：

以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据

如果匹配不到，右表中的数据正常展示, 左边展示为null

语法格式：

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

示例：

-- 右外连接查询

SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

3.3.3 各种连接的总结

内连接: inner join , 只获取两张表中 交集部分的数据.

左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分

右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分

4 子查询

4.1 什么是子查询

4.1.1 概念

一条select 查询语句的结果, 作为另一条 select 语句的一部分

4.1.2 特点

子查询必须放在小括号中

子查询一般作为父查询的查询条件使用

4.1.3 常见类型

where型子查询: 将子查询的结果, 作为父查询的比较条件

from型子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用

exists型子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

4.1.4 子查询结果作为查询条件 (where)

语法格式：

SELECT 查询字段 FROM 表 WHERE 字段=(子查询);

示例：

# 通过子查询的方式, 查询价格最高的商品信息

-- 1.先查询出最高价格

SELECT MAX(price) FROM products;

-- 2.将最高价格作为条件,获取商品信息

SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

4.1.5 子查询结果作为一张表 (from)

语法格式：

SELECT 查询字段 FROM (子查询)表别名 WHERE 条件;

当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段。

示例：

-- 1. 先查询分类表的数据

SELECT * FROM category;

-- 2.将上面的查询语句 作为一张表使用

SELECT

p.`pname`,

p.`price`,

c.cname

FROM products p

-- 子查询作为一张表使用时 要起别名 才能访问表中字段

INNER JOIN (SELECT * FROM category) c

ON p.`category_id` = c.cid WHERE p.`price` > 500;

4.1.6 子查询结果是单列多行 (exists)

语法格式：

SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);

子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

4.1.7 子查询总结

子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.

子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名)

5 数据库设计

5.1 数据库三范式

概念：

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据 库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关 系型数据库，必须满足一定的范式

满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的 称为第二范式(2NF) ， 其余范式以此类推。一般说来，数据库只需满足第三范式(3NF)就 行了

5.1.1 第一范式 1NF

概念：

原子性, 做到列不可拆分

第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个 字段都是不可再分的最小数据单元，则满足第一范式

5.1.2 第二范式 2NF

概念：

在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关

一张表只能描述一件事

5.1.3 第三范式 3NF

概念：

消除传递依赖

表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放

5.2 数据库反三范式

5.2.1 概念

反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能

浪费存储空间,节省查询时间 (以空间换时间)

5.2.2 冗余字段

设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本 来所属表的意义表示，那么这个字段就是一个冗余字段

5.2.3 总结

创建一个关系型数据库设计，我们有两种选择：

尽量遵循范式理论的规约，尽可能少的冗余字段

合理的加入冗余字段这个润滑剂，减少join，让数据库执行性能更高更快