MySQL进阶

最新推荐文章于 2024-05-30 14:59:09 发布

杪＆秋

最新推荐文章于 2024-05-30 14:59:09 发布

阅读量134

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/m0_51435825/article/details/126406588

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、数据库范式

数据库范式是指设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同

的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

二、范式概念

范式来自英文Normal form，简称NF。要想设计一个好的关系，必须使关系满足一定的约束条件，

此约束已经形成了规范，分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁

的、结构明晰的，同时，不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是

乱七八糟，不仅给数据库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信

息。

三、范式分类

目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式

(BCNF)、第四范式(4NF)和第五范式(5NF，又称完美范式)。一般来说，数据库只要满足第三范式

即可。

四、数据库范式

第一范式

定义

属于第一范式关系的所有属性都不可再分，即数据项不可分。在任何一个关系数据库中，第一范式

(1NF) 是对关系模型的基本要求，不满足第一范式(1NF)的数据库就不是关系数据库。

理解

第一范式(1NF)最指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实

体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个

新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式1NF中表

的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列

规范化

一个低一级的关系模式通过模式分解可以转化为若干个高-级范式的关系模式的集合，这个过程叫

做规范化。

第二范式

第二范式(Second Normal Form,2nd NF)是指每个表必须有主关键字(Primary key),其他数据元素与

主关键字一一对应。通常称这种关系为函数依赖(Functional dependence)关系，即表中其他数据

元素都依赖于主关键字,或称该数据元素惟一地被主关键字所标识。

理解

第二范式是指每个表必须有一个(有且仅有一个)数据项作为关键字或主键( primarykey)，其他数据

项与关键字或者主键一一对应，即其他数据项完全依赖于关键字或主键。由此可知单主属性的关

系均属于第二范式。

第三范式

第三范式(Third Normal Form,3rd NF)就是指表中的所有数据元素不但要能唯一地被主关键字所标

识，而且它们之间还必须相互独立,不存在其他的函数关系。也就是说，对于一个满足2ndNF的数

据结构来说，表中有可能存在某些数据元素依赖于其他非关键字数据元素的现象,必须消除。

概念

第三范式和第二范式不同的地方在于，在第三范式里，所有的非键属性都必须和每个候选键有直接

相关。如果再对第三范式做进一步加强就成了BC范式，它所强调的重点就在于“数据间的关系是奠

基在键上、以整个键为考量、而且除了键之外不考虑其他因素"。

理解

第三范式要求在满足第二范式的基础上，任何非主属性不依赖于其他非主属性，即在第二范式的基

础上，消除了传递依赖。

总结

去掉传递依赖的优势主要有二点。首先，数据的重复的量减少，因此，数据库变得更小。第二个优

点是数据的完整性。当重复数据的变化，还有只更新一些数据，尤其是如果它摊开在多个数据库中

不同的地方的一大隐患。

巴斯-科德范式

某些特殊情况下，即使关系模式符合3NF的要求，仍然存在着插入异常，修改异常与删除异常的问

题。BCNF 由Boyce与Codd提出，通常情况下，巴斯-科德范式被认为没有新的设计规范加入，只

是对第二范式与第三范式中设计规范要求更强，因而被认为是修正第三范式。

理解

巴斯-科德范式即在在3NF基础上，消除主属性对候选码的部分函数依赖和传递函数依赖。

BC范式既检查非主属性，又检查主属性。当只检查非主属性时，就成了第三范式。满足BC范式的

关系都必然满足第三范式。或者还可以换一种说法:若一个关系达到了第三范式，并且它只有一个

候选码，或者它的每个候选码都是单属性，则该关系自然达到BC范式。

总结

所有非主属性对每一个码都是完全函数依赖。

所有主属性对每-一个不包含它的码也是完全函数依赖。

没有任何属性完全函数依赖于非码的任何一组属性。

若R∈BCNF,按定义排除了任何属性对码的传递依赖与部分依赖，所以R∈3NF。

一般关系型数据库设计中，达到BCNF就可以了!

第四范式

定义

第四范式的定义很简单:已经是BC范式，并且不包含多值依赖关系。

对于依赖性A→B，如果对于当个的A值，存在多个B值，则该关系将是多值依赖性。

总结

第四范式的定义和BCNF定义唯一的不同点是后者研究非平凡多值依赖的域。由于函数依赖是多值

依赖的特定情况，因此，这直观地显然，若关系属于4NF，则它必属于BCNF;而属于BCNF的关系

不一定属于4NF。说明了4NF比BCNF更强的原因。

第五范式

如果它在4NF中并且不包含任何连接依赖关系并且连接应该是无损的，则关系在5NF中。当所有表

都被分成尽可能多的表以便避免冗余时，满足5NF。5NF也称为项目连接正常形式(PJ/NF)。

定义

如果关系模式R中的每一个连接依赖均由R的候选码所隐含，则称此关系模式符合第五范式。所谓

“R中的每一个连接依赖均由R的候选码所隐含”是指在连接时，所连接的属性均为候选码。

总结

一、第五范式有以下要求:

(1)必须满足第四范式

(2)表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键。

二、第五范式是在第四范式的基础上做的进-步规范化。第四范式处理的是相互独立的多值情况，

而第五范式则处理相互依赖的多值情况。

五、SQL约束

概念

数据库约束是对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。约束通常与

一个表相关联，并使用CREATE CONSTRAINT或CREATE ASSERTIONSQL语句创建。所有的关

系数据库都支持对数据表使用约束，通过约束可以更好地保证数据表里数据的完整性。是表上强制

执行的校验规则，除此之外，当表中数据存在相互依赖性时，可以保护相关数据不被删除。约束通

常无法修改。

分类

1. not null:非空，用于保证该字段的值不能为空

2. default: 默认值，用于保证该字段有默认值

3. primary key:主键，用于保证该字段的值具有唯一-性，并且非空

4. unique:唯一，用于保证该字段的值具有唯一性，可以为空，但只能有一个

5. check: 检查约束，【mysql8. 0之后开始支持】，用于该字段的值添加条件，如年龄大于0

6. foreign key:外键，用于限制两个表的关系。用于保证该字段的值必须来自于主表的关联列的

值，在从表中添加外键列，用于引用主表中某列的值

添加分类

a.列级约束

创建表添加字段时跟在字段后面的约束

六大约束在语法上都支持，但是外键约束没有效果

b.表级约束

创建表时添加完字段后再进行添加的约束

除了非空、默认，其他约束都支持

主键和唯一约束对比

约束	唯一性	非空性	是否允许组合
主键	是	是	是
唯一	是	否	是

外键使用注意点

1.要求在从表设置外键关系

2.从表的外键列的类型和主表的关联列的类型要求一致或兼容。名称无要求

3.主表的关联列必须是一个key(一般是主键或唯一)

4.插入数据时，先插入主表，再插入从表

5.删除数据时，先删除从表，再删除主表

六、MySQL索引

概念

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引

的MySQL就是一个人力三轮车。

拿汉语字典的目录页(索引)打比方，我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查

找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，

但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式:

CREATE INDEX indexName ON table_ name (co lumn_ name)
如果是CHAR，VARCHAR类型， I ength可以小于字段实际长度;如果是BLOB和TEXT类型，必须

指定length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName (co I umnName)

唯一索引

它与前面的普通索引类似，不同的就是:索引列的值必须唯一，但允许有空值。如果是组合索引，

则列值的组合必须唯一。

它有以下几种创建方式:

1.创建索引

CREATE UNIQUE INDEX indexName ON mytable (user name (I ength) )

2.修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username (I ength))

索引分类

MYSQL的索引主要分为主键索引(PRIMARY KEY), 唯-索引(UNIQUE) , 普通索引(INDEX)和全文索

引(FULLTEXT)。

主键索引:它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建

主键索引，CREATE INDEX不能用来创建主键索引，使用ALTER TABLE来代替。

唯一索引:与普通索引类似，不同的就是:索引列的值必须唯一，但允许有空值。如果是组合索引，

则列值的组合必须一。

普通索引:这是最基本的索引，它没有任何限制。

全文索引: FULLTEXT索引用于全文搜索。只有InnoDB和MyISAM存储引擎支持FULLTEXT索引和

仅适用于CHAR， VARCHAR和 TEXT列。

七、分页查询

分页

当要显示的数据一页显示不全，需要分页向数据库提交SQL请求

示例

#查询前5条员工的信息
select * from employees limit 0,5; select * from employees limit 5;
#查询第11到25条员_工信息
select * from employees limit 10,15;
#查询有奖金的员工信息，按照工资较高的前10个显示
select * from employees commission_ _pct is not null order by salary
desc limit 10;