建模

概念建模-理解需求

只关心实体内部需要有什么以及和谁有关系(具体关系不用想) 关系的事件

image-20220712190020673

逻辑建模-表的实体关系

确定不依赖于具体数据库的通用类型和具体的表关系

image-20220712190253373

物理模型-依赖于特定数据库

image-20220712194049120

外键约束介绍：

外键就是在表中引用了别的表中的主键

参考

在enrollments表中，通过student_id字段，可以把注册数据与另一张表students关联起来，则该字段称之外键，语法如下：

1
2
3
4
ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(id);

定义外键约束，关系数据库可以保证无法插入无效的数据。即加入如果students表不存在id=99的记录，enrollments表就无法插入student_id=99的记录

拥有外键的为从表 指向的表为主表，从表只能添加主表中存在的值，这样维护了从表的数据完整性(防止无用数据)

在一对多关系中，一即为主表 多即为从表 从表中有外键

下面两个限制就是告诉从表，当主表发生改变时，从表需要做些什么

一般更新操作级联 删除操作则拒绝主表的删除操作(也就是想删除主表的数据时 要先保证从表的已经被删除)

image-20220712193736278

设计范式

image-20220712194203685

1NF

image-20220712195234135

每个字段不可再分

tag标签明显不符合，因为会有很多种标签

image-20220712194411858

则需要拆分出来

image-20220712194737785

可以发现二者是多对多的关系，但数据库无法表示多对多的，只有一对一和一对多，故而需要新建一个关系表来表示

image-20220712195021018

2NF

image-20220712195539588

每个表只能表示一种实体类型，这个表的所有字段都是描述这个实体的

比如这个表，假如添加了注册日期的字段，显然就不符合了，前四条均是描述课程本身的属性，但课程的注册日期则不是，因为一个课程可以有很多注册日期的(被学生在不同时间注册)，因此注册日期应该属于注册事件这个表的

image-20220712195732099

再来看一个订单表，也是不符合第二范式的

image-20220712200054468

因为客户名描述的是客户的信息，而不是订单，且容易造成数据重复，修改用户名困难

image-20220712200352574

故而需要移出，用客户id代替，这样即便客户id重复 也是最小重复 比之前省了很多空间，且很容易修改客户名

image-20220712200419020

再来看这个表，符合第二范式码？

image-20220712200801137

显然，教师名不属于课程的属性，而是教师表，教师和课程应该是一对多的关系，修改后如下

image-20220712201106789

3NF

image-20220712201330458

字段之间独立，消除重复性

如下所示，最后一个字段可由前两个推导而出，则没必要存在

image-20220712201408070

忠告

在实际的表设计中不用管这几个范式，专注于消除重复性即可

image-20220712201634476

image-20220712201646519

例如 用户和用户地址，当一个用户有多个地址时，会有大量重复

image-20220712201806806

这样就合理了

image-20220712201849581

最后

image-20220712201927363

不要过度考虑目前用不到的东西

image-20220712202609910

image-20220712202630781

image-20220712202712964

image-20220712202809390

转为真实数据库表结构

image-20220712203009607

得到sql脚本执行，即可创建完成

image-20220712203233193

已有数据库逆向为可视化设计

image-20220712204236823

当修改字段时，可同步到所有的服务器数据库上

例如添加coupon字段

image-20220712203450746

image-20220712203531131

image-20220712203612030

实战

例一

image-20220712204728321

这是一张飞机票的信息，根据该信息设计数据库

1.概念建模+逻辑建模

image-20220712211050714

例二

租赁管理系统，不同用户有不同的权限

  • 店长：可添加、更新、删除电影，还可修改如电影库存、日租金
  • 收营员：对电影只读权限，可以管理客户以及他们租赁的电影

结账时 客户租赁若干个电影，收营员会根据客户的电话号码查询，若客户是第一次租赁，则需要提供全名、email、手机号码，然后会扫描所租赁的电影的条形码，然后收营员会更新该商品库存

客户回到店里，会带来租赁的电影，若电影丢失了，则需要支付该电影日租金的5倍费用，其它客户按照日租金和租赁时间来付费，顾客在归还电影时可得到打折券，最后收营员会更新该商品库存

最后需要统计出最受欢迎的电影，总花费最多的顾客，每日的收入

1.概念建模

image-20220712213420489

image-20220712213435749

2.逻辑建模

image-20220712214620358

image-20220712214550550

手动代码创建数据库

1
2
3
4
#创建数据库
create database if not exists sql_store2;
#删除数据库
drop database if exists sql_store2;

创建数据库及表结构

image-20220712215304424

image-20220712215954563

修改表字段

image-20220712215656570

修改表属性

image-20220712220243224

字符集

显示支持的字符集

image-20220712220446546

image-20220712220539624

修改字符集

image-20220712221010560

存储引擎

显示支持的引擎

image-20220712221108457

修改表的存储引擎

image-20220712221338745

索引

基本概念

  • 给某些列加索引，可加速查询，但同时也会增加存储消耗

image-20220712223800337

  • 要根据查询内容来创建索引，索引是为查询而生的，乱添加会增加库的大小，并且降低查询速度

    image-20220712223913220

索引分析

explain可查看查询的具体方式

image-20220712225446983

​ ALL表示全表扫描 rows表示扫描了多少行(总数据就是1010)

创建索引后，再查询

image-20220712225855333

明显加快很多 ，passible_keys 表示可能用到的索引，key表示真实使用的索引

删除索引

image-20220712233237799

查看表中的索引

先执行analyze可更新待会显示索引的唯一值数量(Cardinality)

image-20220712230414380

mysql会自动为外键创建索引，这样可以快速连表

索引字符串时，通常索引其前几位就可以

image-20220712230928764

如何找到最佳的位数呢？

image-20220712231045306

image-20220712231209360

全文索引

当需要为博客创建搜索引擎，查找中是否有关于'react redux'的帖子，则以下很慢且不可靠(因为可以不完全匹配 亦可以顺序颠倒)

image-20220712231526735

此时可以通过全文索引来实现，它会去除所有介词，对所有词索引，然后再去匹配，包含任一个都视为匹配成功

image-20220712231909336

还可以显示出关联度

image-20220712232158815

组合索引

image-20220712232505996

可以看到虽然有两个索引，但最终mysql只会选择一个索引执行

因此可以把两个字段组合成一个索引 从而使得该类查询更快

image-20220712232739750

image-20220712232855335

可以看到选择了更优的组合索引

注意 主索引(主键)会自动添加到从索引中 因此没必要的索引可删除 根据情况 使用组合索引

组合索引使用准则

j最常用的列放前面，把唯一性更高的字段放在前面，然后研究不同顺序不同列的优化效果

image-20220712234213663

  • 强制使用指定的索引执行查询

    image-20220712233909826

  • 索引用于排序 查询代价

    image-20220712234822815

    已有(a,b)索引 则以下查询或排序会使用索引的情况

    image-20220712234943122

    不要重复建索引

image-20220712235742702