likes
comments
collection
share

数据库基础知识

作者站长头像
站长
· 阅读数 31

第一二三范式

关系型数据库的范式

范式可以理解为设计标准

第一范式 1NF

定义

字段不可再分

举例

  • 我们要测出体检者的双眼视力
  • 那么应该存为左眼视力和右眼视力两个字段
  • 即user表里应该有left_eye 和 right_eye
  • 而不能把它们存在一个字段

第一范式的缺点

数据库基础知识

图片来自知乎

这是一个学生选课表，没有违反第一范式(字段不可再分)，但是存在如下问题：

数据冗余(如：李小明、经济系和系主任王强重复了三次，只用写一次就可以了)、创建系时插入异常(创建的系必须要有学生，否则创建不成功，就得造假的数据)、删除学生会导致系消失(系下面的学生没了，系也消失了)、学生转移时改动多处(如所有出现高芳芳的法律系都要改为经济系)

结论： 第一范式不够强

第二范式 2NF

定义(不标准)

  • 在1NF的基础上，要有键(键可由多个字段组合)
  • 所有字段分别完全依赖于键(学号是键，李小明是字段)
  • 如果键是多个字段组合，则不允许部分依赖于该键(如学号和法学基础这门课就知道考了多少分，对于分数来说，则完全依赖于学号和课名，学号和课名是2个字段组成的一个键)

依赖关系

  • 给出键，就能唯一确定字段的值
  • 如给出学号，就能唯一确定姓名，反之则不行(因为有可能出现重名)
  • 则称姓名依赖于学号

不满足第二范式的地方

  • 上表的键为(学号，课名)
  • 但存在部分依赖： 姓名依赖于学号，但是不依赖课名(因为随便给一个课名，可以对应多个姓名)，如：李小明只部分依赖于学号，因此上图的表就不符合第二范式

改进为符合第二范式

  • 选课表(学号、课名、分数)
  • 学生表(学号、姓名、系名、系主任)

数据库基础知识

第一个表的键是： 学号和课名，它们两就可以确定唯一的分数，因此这张表的主键就是学号 + 课名

为什么第一个表的键不只是学号呢？ 因为同一个学号不能得到唯一的课名，而是得到几个不同课名

第二个表的键是序号，因为唯一的学号就对应姓名、系名、系主任。有的时候一个表有多个key，假如在第二张表中加入一个身份证号码的key，那么一个学号就对用一个身份证号码。此时一个表就有多个key，这张表的主键就是学号

上面表存在的问题：

  • 如果某个系的系主任换了，我们就得要更新两个地方，如果有100第学生，那么100个地方的系主任的名字都要改

  • 如果把李小明和张莉莉删了，那么经济系就不存在了

第三范式 3NF

定义(不标准)

  • 一个表里不能有两层依赖(如上图中，系名依赖于学号，系主任依赖于系名，所以系主任两层依赖于学号，因此就存在间接依赖)
  • 给出学号，就能确定系名：系名依赖于学号
  • 给出系名，就能确定系主任： 系主任依赖于系名
  • 所以，系主任间接依赖于学号

如果有间接依赖，则不满足第三范式

解决办法

  • 把系名和系主任单独建表

数据库基础知识

总结

第一范式

  • 属性不可分割

第二范式

  • 字段完全依赖于键(不允许部分依赖)

第三范式

  • 字段没有间接依赖于键

BC范式

  • 键中的属性也不存在间接依赖

一对一、一对多、多对多表设计

数据库设计经验

高内聚

  • 把相关的字段放到一起，不相关的分开建表(如：系名和系主任是相关的可以放在一个表里，系主任的名字和学生的名字没有强的相关性，因此不应该把学生姓名和系主任姓名放在一个表里)
  • 如果两个字段能单独建表，就单独建表

低耦合

  • 如果两个表之间有弱关系
  • 一对一可放在一个表，也可以两个表加外键
  • 一对多一般用外键
  • 多对多一般建中间表

一对一

假设一个学生只能加入一个班级

可以把班级放在学生表里

  • 学生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
  • 出版：id:2002 作者id：1001 书id:4003,出版社id
  • 书id：4002 名称：JS入门

多对多

  • 假设一个学生可以加入多个班级(N个班级)
  • 当然每个班级也能有多个学生(N个学生)

可以把班级放在学生表里吗？

  • 某些DBMS支持数组就可以放
  • 如果不支持数组，就不能放了

单独建立表关系(推荐)

  • 学生id：1001 姓名：小明
  • 学生班级： id:2001 学生id：1001 班级id：4002，有效期
  • 学生班级： id:2022 学生id： 1001 班级id：4003，有效期
  • 班级id：4002 名称：入门1班

什么时候建关联表？

当关联自身存在属性时

  • 比如关联的有效期，有效期为一年
  • 比如关联的级别，不同的店铺有不同的会员又分为vip1~6

join把表连接起来

join

连接表

  • inner join
  • left join
  • right join
  • full outer join

看图技巧

数据库基础知识

参考文章

语法

把表名改为

T1 {[INNER]|{LEFT|RIGHT|FULL}[OUTER]}IOIN T2 ON boolean _expression

例如：

SELECT A.PK AS A_PK,B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK;

备注

  • PK的意思就是逐渐
  • AS的意思就是起一个别名

试试看

场景： 超市员工卖东西给消费者

启动mysql

  • docker container start mysql1
  • 或者
  • docker run --name mysql1 -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql:5.7.27

进入mysql

  • winpty docker exec -it mysql1 bash
  • mysql -u root -p
  • 输入密码123456

创建数据库

  • CREATE DATABASE db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • show databases;
  • use db1;

创建表

  • create table 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);

创建记录

  • insert into users (name) values ('XiaoMing');
  • insert into staffs (name) values ('XiaoHong');
  • insert into orders (user_id,staff_id,amount) value (1,1,100);

创建的表的结果是：

数据库基础知识

数据库基础知识

数据库基础知识

但是我现在想看是哪个用户多少钱的东西？？如：XiaoMing 100

使用 inner join

  • select users.name as uname,orders.amount as amount from users inner join orders on users.id = orders.user_id;
  • 得到XiaoMing 100

数据库基础知识

使用 left join

  • insert into users (name) values ('HuangXiaoMing');

  • select users.name as uname,orders.amount as amount from users left join orders on users.id = orders.user_id;

数据库基础知识

为什么要使用inner join 或 left join？

因为，两个表合起来的时候，会出现左边的边有多的数据或者右边的表有多的数据

如上图所示，当users表和orders表合起来的时候，由于users表中多了一条数据，这时合并完就会涉及到如何展示合并的结果？

如果用innder join 那么多的数据都不要，只要2个表都有的数据

如果用left join，那么左边表如果数据多了，就保留下来

如果用right join，那么右边表如果数据多了，就保留下来

如果用full join，那么两边的表数据如果都多了，就保都留下来

总结

inner join

只保留两边都有的显示

left join

会保留右边的null，以保证左边都显示

right join

会保留左边的null，以保证右边都显示

Full outer join

保留两边的null，以保证两边都显示

缓存字段、事务、存储引擎InnoDB

缓存字段

假设一个博客blog包含多个评论 comments

如何获取博客的评论数

  • select count (id) from comments where blog_id = 8
  • 这样太慢了
  • 可不可以在blog表上加一个comment-count字段
  • 每次添加 comment 则+1
  • 每次删除 comment 则-1
  • 这样就可以快速的获取博客的评论数

事务

有些操作必须一次完成

  • 用户评论之后，要做两件事情
  • 第一步，在comments 表新增记录
  • 第二步，在blogs表将对应的 comment_count + 1
  • 如果第一步执行了，第二步没有执行怎么办？(两步之间发生意外，导致第二步没执行，如本地磁盘已满，突然停电等)
  • 数据就乱了

使用事务

菜鸟教程


start transaction;

语句1； 语句2； 语句3；

commit;

整个作为一条语句来执行

只要有一句出错，则全都不生效

MySQL 存储引擎

命令 SHOW ENGINES

数据库基础知识

常见的

  • InnoDB - 默认，目前版本是新版InnoDB
  • MyISAM - 拥有较高的插入、查询速度，但不支持事务
  • Memory - 内存中，快速访问数据
  • Archive - 只支持insert 和 select

InnoDB

InnoDB是事务型数据库的首选，支持事务、遵循ACID、支持行锁和外键

索引

语法

  • CREATE [UNIQUE] INDEX index1 ON users(name(100))
  • show index in users;
  • 菜鸟教程有兴趣可以看看

用途

  • 提高搜索效率
  • where xxx > 100 ,那么我们可以创建xxx的索引
  • where xxx > 100 and yyy>200,创建xxx,yyy的索引
转载自:https://juejin.cn/post/7252171148758253623
评论
请登录