通用资料

第11章_数据库的设计规范.pdf · 资料文件 · 语雀

1 常见命令

登录
方式一

  1. C:\Windows\system32>mysql -h localhost -P 3306 -u root -p
  2. Enter password: ****
  3. -h localhost：主机地址(可简化成-hlocalhost)
  4. -P 3306 主机端口号(可简化成-P3306)
  5. -u root ：登录名(可简化成-uroot)
  6. -p 然后换行:密码(可简化成-padmin，不可-p admin，即不能使用-p空格再admin)

1 基础知识 - 图1
说明：mysql -h localhost -P 3306 -u root黄底部分中间可以省略空格，即变成-hlocalhost -P3306 –uroot。-p密码的写法要么换行，要么-p密码，中间不能省略空格。
简略写法建议

mysql -uroot –p Enter password：*

1 基础知识 - 图2
退出

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.15    |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye

查看mysql的版本
方法一：登录后可以自动显示版本信息
1 基础知识 - 图3
方法二：使用status命令
1 基础知识 - 图4
方法三：使用Dos命令
1 基础知识 - 图5
方法四：如果是在navicat命令行工具或navicat查询编辑器下或Dos命令行下
使用select version() from dual;
1 基础知识 - 图6
1 基础知识 - 图71 基础知识 - 图8
启动和暂停服务
C:\Windows\system32>net stop mysql56
MySQL0815 服务正在停止.
MySQL0815 服务已成功停止。

C:\Windows\system32>net start mysql56
MySQL0815 服务正在启动 .
MySQL0815 服务已经启动成功。
1 基础知识 - 图9
查看当前Mysql的安装路径
mysql> show variables like '%char%';
1 基础知识 - 图10
修改密码

//1.0 mysql<5.7版本修改
mysql>use mysql;
mysql> update user set password=passworD("新密码") where user='root';
mysql> flush privileges;
mysql> exit; 

//2.0 mysql>=5.7修改
mysql>use mysql;
mysql> update user set authentication_string=password("新密码") where user='root';
mysql> flush privileges;
mysql> exit;

//3.0 mysql>=8.0修改
mysql> use mysql;
Database changed
mysql> update user set authentication_string='cgwangdb' where user='root';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected

1 基础知识 - 图11 1 基础知识 - 图12

显示数据表结构
desc 表名;
1 基础知识 - 图13
查看表的所有字段
desc user;
1 基础知识 - 图14
navicat for MySQL使用命令行工具
注意句尾要加;
参考文档

2 语言的基础知识

语法规范

  1. mysql不区分大小写(即使在linux下也不区分)，但建议关键字大写，表名、列名小写
  2. 每条命令最好用分号结尾
  3. 每条命令根据需要，可以进行缩进 或换行
  4. 注释

单行注释：#注释文字
单行注释：— 注释文字
多行注释：/ 注释文字 /

  1. +的作用

java中的+号：

  • 运算符，两个操作数都为数值型(75+30，值为105)
  • 连接符，只要有一个操作数为字符串(“abc”+30,值为abc30)

mysql中的+号：
仅仅只有一个功能：运算符(会将值转换为数值，如果转换失败则为0)

例子：
select 100+90; 两个操作数都为数值型,直接做加法运算。值为190.
select ‘100’+90;只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算。值为190
select ‘john’+90; 如果转换失败，则将字符型数值转换成0。值为90.
select ‘john’+’kevin’;如果转换失败，则将字符型数值转换成0。值为0.

另外：
select null+10; 只要其中一方为null，则结果肯定为null

SQLServer下的使用(只要一方是数字类型即为相加，否则为拼接)

  1. 数字类型(int /decimal/byte/bit等)+字符类型

会判断字符串能否转换为数字，如果能就转换将两边数字相加。如果不能就报错，所以需要使用+为拼接效果必须将数字一方通过cast或convert转换为字符串。
例子1：select ‘a’+1
1 基础知识 - 图15
例子2：select ‘1’+1.02
1 基础知识 - 图16

  1. 字符串+字符串，直接字符串拼接

例子1：select ‘1’+’a’
1 基础知识 - 图17
例子2：select ‘1’+’1.02’
1 基础知识 - 图18

  1. 有一边为null，则结果也为null

select ‘12’+null;
select null+null;
select 12+null;

使用concat做连接
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
如果concat中有任何一个拼接值为null，则整个结果也为null
select CONCAT(‘a’,’b’,null)：此语句为null

配符
%：任意多个字符,包含0个字符
例子：找到语句中有包含a和e的单词并且a在e的前面
select * from IdName where value like ‘%a%e%’
_：任意单个字符

转义
select * from sys_menu where menu_name like ‘%\%%’;
使用了\转义字符，中间的%将被当成字符%，而不是关键类型%。

select * from sys_menu where menu_name like ‘%$%%’ ESCAPE(“$”);
使用了ESCAPE(“$”)，like里面的$被当成了转义字符。

between and
使用between and 可以提高语句的简洁度，其包含临界值
如下两个语法相同：
SELECT FROM employees WHERE employee_id >=100 AND employee_id<=120;
SELECT FROM employees WHERE employee_id BETWEEN 100 AND 120;

判断
=、<>、!=不能用于判断null值
is null或is not null 仅可以判断null值
<=>既可以判断NULL值，又可以判断普通的数值，可读性较低(一般用的较少)

3 数据库设计规范

六大常见约束

六大约束类型(KND)

  1. KEY->PRIMARY KEY：主键，该字段值具有唯一性，并且非空
  2. KEY->UNIQUE：唯一性，用于保证该字段值具有唯一性，可以为空
  3. KEY->FOREIGN KEY：外键约束
  4. NOT NULL：非空
  5. DEFAULT：默认
  6. CHECK：检查约束(mysql不支持，sqlserver和oracle支持)

显示所有的键
SHOW INDEX FROM user;
1 基础知识 - 图19
主键和唯一键的区别
如下对比，MySQL和SQLServer规则是一样的

主键 外键
唯一性 √ √
是否允许为空 × √
数据表中的个数 一个表中最多只能有一个 一个表中可以有多个
是否允许组合 支持 支持

外键
外键：

  1. 要求在从表设置外键关系
  2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
  3. 主表的关联列必须是一个key（一般是主键或唯一）
  4. 插入数据时，先插入主表，再插入从表
  5. 删除数据时，先删除从表，再删除主表

约束的分类
列级约束：六大约束语法上都支持，但是外键约束和CHECK约束没有效果
表级约束：除了非空Not NULL、默认DEFAULT和CHECK约束，其他的都支持
自增长标识列

  1. 标识列必须和主键搭配吗？

不一定，但要求必须是一个key(pk或uq或自定义key等等)

注：fk不可以设置为自增长 1 基础知识 - 图20

  1. 一个表可以有几个标识列？至多一个！
  2. 标识列的类型只能是数值型

  3. 可以通过SHOW VARIABLES LIKE '%auto_increment%'查询步长和起始步

    其中可以通过SET auto_increment_increment=n设置步长 但是设置起始步不能通过SET auto_increment_offset来设置，只能通过手动插入值设置起始步 1 基础知识 - 图21

  4. 和SQLServer不同的是，SQLServer的identity是不能显示指定插入值的

但是MySQL是可以手动指定插入值，也可以不指定，如果不指定或者设置为null，则为从上一个数据后的自增长值。
如user表的列id为自增长，那也可以手动插入id=10,那么在这条数据之后的数据，如果不手动设置id值或设置为null，则id就是从10开始的自增长。

MySQL insert into user(id,stuname) value(10,’jack’); insert into user(id,stuname) value(null, ‘lucy’); insert into user(stuname) value(‘lily’); 1 基础知识 - 图22

//1.0 新增表时设置
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
    id INT  ,
    NAME FLOAT UNIQUE AUTO_INCREMENT,
    seat INT 
);

//2.0 修改表时设置(和设置约束一样)
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

//3.0 删除表的自增长
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

键和属性

范式的定义会使用到主键和候选键，数据库中的键（Key）由一个或者多个属性组成。数据表中常用的几种键和属性的定义：

  • 超键：能唯一标识元组的属性集叫做超键。表的唯一键属性集。
  • 候选键：如果超键不包括多余的属性，那么这个超键就是候选键。一般可以简单理解为最小的表的唯一键。
  • 主键：用户可以从候选键中选择一个作为主键。表的主键。
  • 外键：如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键。
  • 主属性：包含在任一候选键中的属性称为主属性。
  • 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性。

通常，我们也将候选键称之为“码”，把主键也称为“主码”。因为键可能是由多个属性组成的，针对单个属性，我们还可以用主属性和非主属性来进行区分。

这里有两个表 球员表(player) ：球员编号、姓名、身份证号、年龄、球队编号 球队表(team) ：球队编号、主教练、球队所在地

  • 超键 ：对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如（球员编号）、（球员编号，姓名）、（身份证号，年龄）等。
  • 候选键 ：就是最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）。
  • 主键 ：我们自己选定，也就是从候选键中选择一个，比如（球员编号）。
  • 外键 ：球员表中的球队编号。
  • 主属性 、 非主属性 ：在球员表中，主属性是（球员编号）（身份证号），其他的属性（姓名）、（年龄）、（球队编号）都是非主属性。

三大范式

第一范式（1st NF）：必须为原子性
第一范式主要是确保数据表中每个字段的值必须具有原子性，也就是说数据表中每个字段的值为不可再次拆分的最小数据单元。即我们在设计某个字段的时候，对于字段X来说，不能把字段X拆分成字段X-1和字段X-2。事实上，任何的DBMS都会满足第一范式的要求，不会将字段进行拆分。
image.png
第二范式（2nd NF）：所有非主键必须完全依赖于主键
第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的，即所有非主键字段，都必须完全依赖主键（不能只依赖主键的一部分）。
小结：第二范式要求实体的属性完全依赖于主关键字。如果存在不完全依赖，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与元实体之间是一对多的关系。

案例：比赛表 player_game ，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这里候选键和主键都为（球员编号，比赛编号），可以通过候选键（或主键）来决定如下的关系 (球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分) 但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系： (球员编号) → (姓名，年龄) (比赛编号) → (比赛时间，比赛场地) 对于非主属性来说，并非完全依赖候选键。这样会产生怎样的问题呢？

  • 数据冗余 ：如果一个球员可以参加 m 场比赛，那么球员的姓名和年龄就重复了 m-1 次。一个比赛也可能会有 n 个球员参加，比赛的时间和地点就重复了 n-1 次。
  • 插入异常 ：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。
  • 删除异常 ：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。
  • 更新异常 ：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。

第三范式（3rd NF）：非主键间不能有互相依赖
第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，即数据表中的所有非主键字段不能依赖于其他非主键字段。通俗地讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。这里的主键可以拓展为候选键。
小结：符合3NF后的数据模型通俗地讲，每个非键属性完完全全的依赖于整个键，并且除了键别无其他依赖。

案例：球员player表 ，包括属性球员编号、球员姓名、球队名称和球队主教练。能看到球员编号决定了球队名称，同时球队名称决定了球队主教练，非主属性球队主教练就会传递依赖于球员编号，因此不符合 3NF 的要求。 image.png

总结
关于数据表的设计，有三个范式要遵循。
第一范式(1NF)，确保每列保持原子性
第二范式(2NF)，确保每列都和主键完全依赖，尤其在复合主键的情况下，非主键部分不应该依赖于部分主键。
第三范式(3NF)，确保每列都和主键列直接相关，而不是间接相关
范式的优点：数据的标准化有助于消除数据库中的数据冗余，第三范式(3NF）通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。
范式的缺点∶范式的使用，可能降低查询的效率。因为范式等级越高，设计出来的数据表就越多、越精细，数据的冗余度就越低，进行数据查询的时候就可能需要关联多张表，这不但代价昂贵，也可能使一些索引策略无效。范式只是提出了设计的标准，实际上设计数据表时，未必一定要符合这些标准。开发中，我们会出现为了性能和读取效率违反范式化的原则，通过增加少量的冗余或重复的数据来提高数据库的读性能，减少关联查询，join表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。

巴斯规范（BCNF）

人们在3NF的基础上进行了改进，提出了巴斯范式(BCNF)，也叫做巴斯-科德范式(Boyce-Codd NormalForm)。BCNF被认为没有新的设计规范加入，只是对第三范式中设计规范要求更强，使得数据库冗余度更小，所以，其称为是修正的第三范式，或扩充的第三范式，BCNF不被称为第四范式。

  • 内容：所有的主属性对每一个不包含它的候选键，也是完全函数依赖；没有任何属性（尤其是主属性）完全函数依赖于非候选键的任何一组属性。
  • 一般来说，一个数据库设计符合3NF或BCNF就可以了。

    案例：学生导师表 ，其中包含字段：学生ID，专业，导师，专业GPA，这其中学生ID和专业是联合主键。 这个表的设计满足三范式，但是这里存在另一个依赖关系，“专业”依赖于“导师”，也就是说每个导师只做一个专业方面的导师，只要知道了是哪个导师，我们自然就知道是哪个专业的了。 所以这个表的部分主键Major依赖于非主键属性Advisor，那么我们可以进行以下的调整，拆分成2个表。学生导师表（学生ID，专业，专业GPA），导师表（导师，专业）

反范式化（反三范式化）

有的时候不能简单按照规范要求设计数据表，因为有的数据看似冗余，其实对业务来说十分重要。这个时候，我们就要遵循业务优先的原则，首先满足业务需求，再尽量减少冗余。
如果数据库中的数据量比较大，系统的UV和PV访问频次比较高，则完全按照MysQL的三大范式设计数据表，读数据时会产生大量的关联查询，在一定程度上会影响数据库的读性能。如果我们想对查询效率进行优化，反范式优化也是一种优化思路。此时，可以通过在数据表中增加冗余字段来提高数据库的读性能。
反范式化的适用场景

  • 当冗余信息有价值或者能大幅度提高查询效率的时候，我们才会采取反范式的优化。
  • 增加冗余字段一定要符合如下两个条件。只有满足这两个条件，才可以考虑增加冗余字段。
    • 这个冗余字段不需要经常进行修改
    • 这个冗余字段查询的时候不可或缺

反范式化的问题

  • 存储空间变大了
  • 一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则数据不一致
  • 若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常消耗系统资源
  • 在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂

    4 数据库对象编写建议

    关于库

  1. 【强制】库的名称必须控制在32个字符以内，只能使用英文字母、数字和下划线，建议以英文字母开头。
  2. 【强制】库名中英文一律小写，不同单词采用下划线分割。须见名知意。
  3. 【强制】库的名称格式：业务系统名称_子系统名。
  4. 【强制】库名禁止使用关键字（如type,order等）。
  5. 【强制】创建数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4。创建数据库SQL举例：CREATE DATABASE crm_fund DEFAULT CHARACTER SET 'utf8';
  6. 【建议】对于程序连接数据库账号，遵循权限最小原则。使用数据库账号只能在一个DB下使用，不准跨库。程序使用的账号原则上不准有drop权限。

  7. 【建议】临时库以tmp_为前缀，并以日期为后缀；备份库以bak_为前缀，并以日期为后缀。

    关于表、列

  8. 【强制】表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议以英文字母开头。

  9. 【强制】 表名、列名一律小写，不同单词采用下划线分割。须见名知意。
  10. 【强制】表名要求有模块名强相关，同一模块的表名尽量使用统一前缀。比如：crm_fund_item
  11. 【强制】创建表时必须显式指定字符集为utf8或utf8mb4。
  12. 【强制】表名、列名禁止使用关键字（如type,order等）。
  13. 【强制】创建表时必须显式指定表存储引擎类型。如无特殊需求，一律为InnoDB。
  14. 【强制】建表必须有comment。
  15. 【强制】字段命名应尽可能使用表达实际含义的英文单词或缩写。如：公司 ID，不要使用 corporation_id, 而用corp_id 即可。
  16. 【强制】布尔值类型的字段命名为is_描述。如member表上表示是否为enabled的会员的字段命名为 is_enabled。
  17. 【强制】禁止在数据库中存储图片、文件等大的二进制数据。通常文件很大，短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息。
  18. 【建议】建表时关于主键：表必须有主键
    1. 强制要求主键为id，类型为int或bigint，且为auto_increment 建议使用unsigned无符号型。
    2. 标识表里每一行主体的字段不要设为主键，建议设为其他字段如user_id，order_id等，并建立unique key索引。因为如果设为主键且主键值为随机插入，则会导致innodb内部页分裂和大量随机I/O，性能下降。
  19. 【建议】核心表（如用户表）必须有行数据的创建时间字段（create_time）和最后更新时间字段（update_time），便于查问题。
  20. 【建议】表中所有字段尽量都是NOT NULL属性，业务可以根据需要定义DEFAULT值。 因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。
  21. 【建议】所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。
  22. 【建议】中间表（或临时表）用于保留中间结果集，名称以tmp_开头。备份表用于备份或抓取源表快照，名称以bak_开头。中间表和备份表定期清理。

  23. 【示范】一个较为规范的建表语句： 

    CREATE TABLE user_info ( 
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', 
`user_id` bigint(11) NOT NULL COMMENT '用户id', 
`username` varchar(45) NOT NULL COMMENT '真实姓名', 
`email` varchar(30) NOT NULL COMMENT '用户邮箱', 
`nickname` varchar(45) NOT NULL COMMENT '昵称', 
`birthday` date NOT NULL COMMENT '生日', 
`sex` tinyint(4) DEFAULT '0' COMMENT '性别', 
`short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字', 
`user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址', 
`user_register_ip` int NOT NULL COMMENT '用户注册时的源ip', 
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', 
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', 
`user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未 通过，4为还未提交审核',
PRIMARY KEY (`id`), 
UNIQUE KEY `uniq_user_id` (`user_id`), 
KEY `idx_username`(`username`), 
KEY `idx_create_time_status`(`create_time`,`user_review_status`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息'

  24. 【建议】创建表时，可以使用可视化工具。这样可以确保表、字段相关的约定都能设置上。实际上，我们通常很少自己写 DDL 语句，可以使用一些可视化工具来创建和操作数据库和数据表。可视化工具除了方便，还能直接帮我们将数据库的结构定义转化成 SQL 语言，方便数据库和数据表结构的导出和导入。

    关于索引

  25. 【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值禁止被更新。

  26. 【强制】InnoDB和MyISAM存储引擎表，索引类型必须为BTREE。
  27. 【建议】主键的名称以pk_开头，唯一键以uni_或uk_开头，普通索引以idx_开头，一律使用小写格式，以字段的名称或缩写作为后缀。
  28. 【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如: sample 表 member_id 上的索引：idx_sample_mid。
  29. 【建议】单个表上的索引个数不能超过6个。
  30. 【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。
  31. 【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。

  32. 【建议】建表或加索引时，保证表里互相不存在冗余索引。 比如：如果表里已经存在key(a,b)， 则key(a)为冗余索引，需要删除。

    SQL编写

  33. 【强制】程序端SELECT语句必须指定具体字段名称，禁止写成 *。

  34. 【建议】程序端insert语句指定具体字段名称，不要写成INSERT INTO t1 VALUES(…)。
  35. 【建议】除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。
  36. 【建议】INSERT INTO…VALUES(XX),(XX),(XX).. 这里XX的值不要超过5000个。 值过多虽然上线很快，但会引起主从同步延迟。
  37. 【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。
  38. 【建议】线上环境，多表 JOIN 不要超过5个表。
  39. 【建议】减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
  40. 【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。
  41. 【建议】对单表的多次alter操作必须合并为一次。对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整合在一起。 因为alter table会产生表锁，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。
  42. 【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep。
  43. 【建议】事务里包含SQL不超过5个。因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
  44. 【建议】事务里更新语句尽量基于主键或UNIQUE KEY，如UPDATE… WHERE id=XX;否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。