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,让数据库执行性能更高更快

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

偷浪漫

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值