本文详细介绍了SQL的分类（DQL、DDL、DML、DCL、TPL、CCL），数据表的三范式及其在设计中的应用，以及数据库表和字段的命名规范，包括主键设计原则以及一对多、多对多和一对一设计的方法。
1. 定义
1.1 SQL的分类
- DQL
- 数据查询语言（Data Query Language, DQL）负责进行数据查询而不会对数据本身进行修改的语句。
SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。 - DDL
- 数据定义语言 (Data Definition Language, DDL) 负责数据结构定义与数据库对象定义
CREATE、ALTER、DROP - DML
- 数据操纵语言（Data Manipulation Language, DML）负责对数据库对象运行数据访问工作
INSERT、UPDATE、DELETE - DCL
- 数据控制语言 (Data Control Language) 负责对数据访问权进行控制，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权
GRANT、REVOKE 两个指令组成。 - TPL
- 数据事务管理语言（Transaction Processing Language）负责确保被DML语句影响的表的所有行及时得以更新。
BEGIN TRANSACTION、COMMIT、ROLLBACK。 - CCL
- 指针控制语言（Cursor Control Language），它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。 - DBMS、SQL、DB之间的关系
- DBMS通过执行SQL来操作DB中的数据。
1.2 SQL语句执行顺序
书写顺序：SELECT -> FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> UNION -> ORDER BY ->LIMIT
但执行顺序为：
-
FORM：选择from后面跟的表，产生虚拟表1。
-
ON：ON是JOIN的连接条件，符合连接条件的行会被记录在虚拟表2中。
-
JOIN：如果指定了LEFT JOIN，那么保留表中未匹配的行就会作为外部行添加到虚拟表2中，产生虚拟表3。如果有多个JOIN链接，会重复执行步骤1~3，直到处理完所有表。
-
WHERE：对虚拟表3进行WHERE条件过滤，符合条件的记录会被插入到虚拟表4中。
-
GROUP BY：根据GROUP BY子句中的列，对虚拟表2中的记录进行分组操作，产生虚拟表5。
-
SELECT 中的分组函数
-
HAVING：对虚拟表5进行HAVING过滤，符合条件的记录会被插入到虚拟表6中。
-
SELECT：SELECT到一步才执行，选择指定的列，插入到虚拟表7中。
-
UNION：UNION连接的两个SELECT查询语句，会重复执行步骤1~7，产生两个虚拟表7，UNION会将这些记录合并到虚拟表8中。
-
ORDER BY: 将虚拟表8中的记录进行排序，虚拟表9。
-
LIMIT：取出指定行的记录，返回结果集。
2. 三范式
- 三大范式是 MS 数据库设计表结构所遵循的规范，目的是为了减少冗余，建立结构合理的数据库。
- 三大范式之间是具有依赖关系的，比如第二范式是在第一范式的基础上建设的、第三范式是在第二范式的基础上建设的。
- 除了三大范式，还有巴斯-科德范式（BCNF）、第四范式(4NF）、第五范式（5NF，又称“完美范式"）。
- 并不存在一定要采用第几种范式的情况，尽管高范式的数据冗余少（占用存储空间少），但有些客户相对于存储空间，他们可能更关注速度，即采取空间换时间的方案。
2.1 第一范式
- 任何一张表都应该有主键，且每个字段都具有原子性（字段不可再分）
2.2 第二范式
- 任何一张表都应该有主键，且每个字段都具有原子性（字段不可再分）
- 所有非主键字段完全依赖主键，不能产生部分依赖
在这张表中，学生编号和教师编号组成联合主键，而实际上，学生姓名仅依赖于学生编号，教师姓名仅依赖于教师编号，二者都产生了部分依赖。于是不符合第二范式。
从数据存储、处理的角度，这样的设计当然不好。可以将表拆分为三张表，分别是学生表、教师表、学生教师关系表。
代价是什么呢？
当我们想要查询某一学生的教师时，现在需要多表联查，而多表联查的笛卡尔积是很大的，导致查询速度变慢。
而在我们最初，不符合第二范式的设计中，我们只要查询一张表即可得到所有信息，查询的速度有保证。
2.3 第三范式
- 任何一张表都应该有主键，且每个字段都具有原子性（字段不可再分）
- 所有非主键字段完全依赖主键，不能产生部分依赖
- 所有非主键字段不能传递依赖于主键字段
在这张表中，学生编号是主键，因此学生姓名、班级编号、班级姓名三个字段都完全依赖于学生编号。
同时显而易见的是，班级名称依赖于班级编号。
这样就形成了传递依赖：班级名称–班级编号–学生编号
3. 数据库表、字段命名规范
3.1 字段规范
3.1.1 字段命名规范
- 字段名必须以下划线或字母开始，且只能由下划线、字母、数字组成。若字段名字中包含其他符号，对于该字段创建及后续操作时，必须以反引号包裹字段名，以让 MySQL 识别。
create table Employee(
`person-name` varchar(255) primary key,
hometown varchar(255)
);
- 字段名中的多个单词使用下划线’_'分隔
采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’‘组成，命名简洁明确，多个单词用下划线’'分隔。
正例：user_name”、 user_id、 is_friend、 is_good。
反例：username、userid、isfriend、isgood。
- 字段名中的所有单词必须小写
反例：userID、houseID。
-
字段名中禁止出现表名
例如，在名employe的表中禁止使用名为employee_lastname的字段。 -
表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1表示是，0表示否）。
正例：表达逻辑删除的字段名is_deleted，1表示删除，0表示未删除。
- 禁止在命名字段时，包含数据类型。
3.1.2 字段类型规范
（1）如果存储的字符串长度几乎相等，则使用char定长字符串类型。
例如，11位手机号，邮编(postcode)。
这种固定长度的纯数字，也不要用int类型或long类型，因为
（2）仅当数字参与了运算，才用数值型；否则即使是纯数字，也不要使用 int/long 类型
反例：年龄使用 int 存储
（3）小数使用 decimal 存储，禁止使用float和double类型。
在存储时，float和double类型存在精度损失的问题。
如果存储的数据范围超过decimal的范围，那么建议将数据拆成整数和小数并分开存储
（4）整型int定义中不添加长度
比如使用INT，而不是INT(4)。
（5）禁止使用varchar类型作为主键。
（6）禁止使用 MySql 中的任何类型保存大文本、文件、图片
一列需要占很大空间的字段，一定要单独拎出来，不要和常用信息放一张表。
举个例子： 文章的信息和文章的内容，这一定要分成两个表。否则会给你的文章性能带来极大的挑战。因为很多情况下，查看文章列表，根本不需要查看到文章的内容。
（7）datetime、smalldatetime类型的字段没有默认值，必须为NULL。
（8）除此之外所有字段在设计时，必须有默认值
字符型的默认值为一个空字符值串‘’，数值型的默认值为数值0，逻辑型的默认值为数值0。
系统中所有逻辑型中数值0表示为“假”，数值1表示为“真”，
（9）IP地址使用unsigned int类型，这样比较节约存储空间。
（10）避免使用NULL字段
NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效
（10）不建议使用ENUM类型，使用TINYINT来代替。
1)、假如一个设计不合理的ENUM字段，给程序员带来的就完全是梦魇了，比如一个enum字段的范围是(‘0’,‘1’,‘2’,‘3’,‘4’,‘5’)，而enum的枚举值对应的索引是从1开始的，因此，insert into table (enum)values(1)，插入的并不是1，而是0
(2)、另外假如你在设计好enum的枚举字段范围并使用了一段时间后，再到字段范围中加一个枚举值，并且不是加在最后，那么也就相当于把原来的范围都改变了索引值，也就是当你在查询的时候直接查询值(并加上单引号)，将不会使用enum自身隐藏的索引值来获取结果了
(3)、如果是纯数值型，还是建议采用tinyint字段吧，毕竟它也只占一个字节，即使出现赃数据，也可以被接受，不象enum，如果纯数字型范围，更改了索引，你就不知道你查询的值是否正确了
(4)、如果字段是字符串，并且长度固定，可以尝试用char，如果是数值型，还是用tinyint吧，比较安全稳定，而且即使迁移，也不会出现太多问题
3.1.3 主键设计原则：
- 主键值不要太长，二级索引叶子结点上存储的是主键值，主键值太长会导致索引占用过多空间。
- 尽量使用 auto_increment 生成主键，而非 uuid。因为顺序的主键能有效加速 SQL 执行。
- 主键值修改，聚集索引一定会重新排序。因此不要使用业务主键，因为业务的变化会导致主键值的频繁修改。
- 在插入数据时，主键值最好是顺序插入，不要乱序插入，因为乱序插入可能会导致B+树叶子结点频繁的进行页分裂与页合并操作，效率较低。
- 主键值对应聚集索引，插入主键值如果是乱序的，B+树叶子结点需要不断的重新排序，重排过程中还会频繁涉及到页分裂和页合并的操作，效率较低。
- B+ 树上的每个节点都存储在页（page）中。一个页面中存储一个节点。
- MS 的 InnoDB 存储引擎一个页可以存储 16KB 的数据。
3.2 表命名规范
（1）表名必须以下划线或字母开始，且只能由下划线、字母、数字组成
MySQL在Windows系统中不区分大小写，但在Linux系统中默认区分大小写。
正例：aliyun_admin,level_name
反例：AliyunAdmin,levelName
（2）表名不使用复数名词。
正例：user,employee
反例：users,employees
（3）表的命名最好遵循“业务名称_表的作用”原则
正例：alipay_task，trade_config
反例：yy_all_live_category、yy_alllive_comment_user。
//说明：去除项目名，统一命名规则，均为”yy_alllive_”开头即可。
（4）明细表的名称为：主表的名称+字符dtl（detail缩写）
例如：采购定单的名称为：po_order，则采购定单的明细表为：po_orderdtl。
（5）临时库/表必须以tmp为前缀并以日期为后缀。
（6）备份库/表必须以bak为前缀并以日期为后缀。
（7）通用表要加前缀“all_”，示例：all_user。
（8）表必须填写描述信息（使用SQL语句建表时）
（9）一个项目一个数据库，多个项目慎用同一个数据库。
3.3 表设计规范
表的设计中，遵循范式是一方面，客户的需求才更重要。实际中往往会牺牲范式准则，浪费一定存储空间，加快查询速度。
3.3.1 一对多设计
口诀：一对多两张表，多的表加外键。
3.3.2 多对多设计
口诀：多对多三张表，关系表添加外键。
3.3.3 一对一设计
一对一设计的实际应用：
在用户信息表中，所涉及的表项可能非常多。
但是在登录阶段，我们仅需核对用户名、账号、密码等。
因此将用户信息分为几张表存储，但不同表的表项必须保持一对一对应关系，因为这些信息是同一个用户的。
两种方案：
- 第一种：主键共享
- 第二种：外键唯一
2808
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
