关系型数据库的范式
范式可以理解为设计标准
第一范式 1NF
- 定义
字段不可再分
- 举例
存储体检者的双眼视力
那么应该存为左眼视力和右眼视力两个字段
即user表里应该有 left_eye 和 right_eye
而不能把它们存在一个字段
第一范式 的缺点
image.png
存在如下问题：
- 数据冗余
- 创建系时插入异常
- 删除学生会导致系消失
- 学生转系时改动多处
结论：第一范式不够强
第二范式 2NF
- 定义（不准确）
在1NF的基础上，要有键（键可由多个字段组成）
所有字段分别完全依赖于键
如果键是多个字段组合，则不允许部分依赖于该键
- 依赖关系
给出键，就能唯一确定字段的值
如给出学号，就能唯一确定姓名，反之则不行
则称姓名依赖于学号
- 上表不满足第二范式的地方
上表的键为（学号，课名）
但存在部分依赖：姓名依赖于学号
- 改进
选课表（学号、课名、分数）
学生表（学号、姓名、系名、班主任）
image.png
第三范式 3NF
- 定义（不准确）
一个表里不能有两层依赖
给出学号，就能确定系名：系名依赖于学号
给出系名，就能确定系主任：系主任依赖于系名
所以，系主任间接依赖于学号
- 解决办法
把系名和系主任单独建表
image.png
总结
- 第一范式：属性不可分割
- 第二范式：字段完全依赖于键
- 第三范式：字段没有间接依赖于键
- BC范式：键中的属性也不存在间接依赖(多个键之间互相依赖)
数据库设计经验
- 高内聚
把相关的字段放在一起，不相关的分开建表
如果两个字段能够单独建表，那就单独建表
- 低耦合
如果两个表之间有弱关系
一对一可放在一个表，也可两个表加外键
一对多一般用外键
多对多一般建中间表
一对一
假设一个学生只能加入一个班
- 可以把班级放在学生表里
学生id：1001； 姓名：小明；班级id：4002
班级id：4002； 名称：入门1级
- 也可以单独建立关联表
如果两个表之间有弱关系
一 学生id：1001； 姓名：小明；
一 学生班级id：2003； 学生id：1001；班级id：4002
班级id：4002； 名称：入门1级
一对多
假设一个作者能写多本书
- 可以把书放在作者表立吗？
某些DBMS支持数组，可以存两个id到一个字段
作者id：1001；姓名：大牛；books：[2001，3002]
如果不支持数组，就不能这么做了
- 单独建立关联表（推荐）
作者id：1001；姓名：大牛
出版id：2001；作者id：1001；书id：4002
出版id：2002；作者id：1001；书id：4003
书id：4002；名称：JS入门
什么时候建关联表
- 当关联自身存在属性时
比如关联的有效期，有效期为一年
比如关联的级别，店铺会员分为VIP1～6
JOIN
- 连接表
inner join
left join
right join
full outer join
-
看图巧记
image.png
image.png
语法
把表名改为
T1{INNER|{LEFT|RIGHT|FULL}[OUTER]} JOIN T2 ON boolean_expression
例如
SELECT A.PK AS A_PK, B.PK AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK;
试试看1
- 创建数据库
CREATE DATABASE IF NOT EXISTS han DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_520_ci; - 创建表
create tables users(id serial, name text);
create table staffs(id serial, name text);
create table orders(id serial, user_id bigint unsigned, staff_id bigint unsigned, amount int unsigned);
image.png
试试看2
- 创建记录
insert into users (name) values ('XiaoMing');
insert into staffs (name) values ('XiaoHong');
insert into orders (user_id,staff_id,amount) values (1,1,100);
image.png
- 使用inner join
select users.name, orders.amount from users inner join orders on users.id=orders.user_id;
image.png
- 使用left join
select users.name, orders.amount from users left join orders on users.id = orders.user_id;
image.png
其他 join
- Left join
会保留右边的null，以保证左边都显示
- Right join
会保留左边的null，以保证右边都显示
- Full outer join
会保留两边的null，以保证两边都显示
缓存字段
假设一个博客包含多个评论comments
- 如何获取博客的评论数
select count(id) from comments where blog_id=8
这样太慢了
可不可以在blog表上加一个comment_count字段
每次添加comment则+1
每次删除comment则-1
可以的
事务
- 有些操作必须一次完成
用户评论之后，要做两件事
第一步，在comment表新增记录
第二部，在blogs表将对应的comment_count+1
如果第一步执行了，第二步没执行怎么办
数据就乱了
使用事务
create table runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表
begin; # 开始事务
语句1；语句2；语句3；
commit; # 提交事务
rollback; # 回滚
select * from runoob_transaction_test; # 因为回滚所以数据没有插入
只要有一句出错，则全都不生效
image.png
索引
- 语法
create unique index index1 on users(name(100)); //这里的100是索引长度，规定不能超过该字段类型的最大字符长度
show index in users;
image.png
- 用途
提高搜索效率
where xxx>100 那么我们可以创建xxx的索引
where xxx>100 and yyy>200, 创建xxx,yyy的索引
网友评论