MySQL学习笔记(2)约束以及修改数据表

M

/*本笔记依托于与MySQL的零距离接触(慕课网)*/

按照标题的顺序,我想首先记录一下约束的相关知识。为什么需要约束:约束是为了保证数据的完整性和一致性。同时根据约束针对的字段多少将约束分为表级约束列级约束。如果约束只对一个字段起作用,那么就称这样的约束是列级约束。如果约束对于两个或两个以上字段起作用,这样的约束就称之为表级约束。说到这里估计都糊涂了,到底什么是约束,如果我说出来约束在功能上的划分你们就明白了。约束在功能上划分为:NOT NULL(非空约束)PRIMARY KEY(主键约束)UNIQUE KEY(唯一约束)DEFAULT(默认约束)FOREIGN KEY(外键约束),这样就熟悉了很多,因为前面四种在上一篇笔记中已经提到并且使用过了,这次主要讨论的就是FOREIGN KEY。

FOREIGN KEY的作用就是保证数据表数据的一致性、完整性,同时实现一对一、一对多关系。正是因为有了FOREIGN KEY MySQL才能够被称之为关系型数据库。

哇,FOREIGN KEY听起来好牛逼啊,那怎么用呢?让我们来扒拉扒拉使用说明

WTF?看都看不懂啊,我们来慢慢解析,一条一条的看

首先第一条中提到了父表和子表的,所谓的子表就是具有外键列的表,子表所参照的表称为父表。假设我们有一个商品价格表(有商品编号和价格),和一个商品详细信息(不包含价格)表,这个时候设定商品的编号为FOREIGN KEY,那么商品价格表就是子表,因为他具有外键列,而详细信息表就是父表,因为子表中的商品编号需要到父表中查询详细信息,第一二条可以合并在一起,也就是说子表和父表的存储引擎只能为InnoDB,你会说妈妈呀,这咋又出来个InnoDB。关于存储引擎暂时不讨论,十分好奇的可以自行百度,至于禁止使用临时表这一点,现在可以先理解成为子表和父表必须是CREATE创建的

接下来看第三条,外键列就是被加上了FOREIGN KEY的那一列,外键列参照的那一列我们成为参照列,也就是说外键列属于子表,参照列属于父表,结合上面的例子应该不是很难懂,剩下的FOREIGN KEY的说明书里说的很详细了

最后是第四条,这里写的稍微有一点模糊,外键列上没有索引的话MySQL才会自动创建索引,但参考列上没有索引MySQL就不会自动创建索引,至于什么是索引这里暂且不深入

看完说明书之后我们就要根据说明书的要求来使用FOREIGN KEY

首先来看一下如何更改MySQL的存储引擎,MySQL的配置文件主要就是一个my.ini,我们打开my.ini文件找到

这个参数就是我们MySQL的存储引擎,这里我得是MyISAM,如果需要使用FOREIGN KEY则需要改成InnoDB,更改完成后需要重启MySQL来生效设置。

这样我们就创建了FOREIGN KEY的第一个表,也就是父表

我们使用这条命令来查看我们刚才创建的表的完整SQL语句

这里我们可以看出存储引擎确实是InnoDB,接下来我们要继续创建子表。

这里可以自行测试外键列与参照列数据类型不同会有什么问题,注意一定要完全相同即使缺少UNSIGNED也不行。在这里pid为外键列,id为参照列。等等好像这样就成功了啊,好像没有索引什么事情啊0.0下面我们要看一下第四条是否生效,其实这里已经创建了索引,只不过没有察觉而已,PRIMARY KEY会自动为所在字段添加索引,啥?你不信?-。-那就证明一下吧

这条命令可以显示出对应表的索引

信了吧,这里显示在fk1表里有一个索引,索引位置在id字段上,同样我们也可以通过相同的命令来查看fk2上有没有索引

我们会发现这里有两个索引,第一个id存在的原因是使用了主键约束,而第二个pid存在索引的原因是因为它是外键列,外键列没有索引时会自动创建索引,如果你想的话可以显示一下生成的SQL

上面写了这么多,现在就可以创建一些简单的外键约束的实例了,接下来要讨论的是外键约束的四个参照操作:CASCADESET NULLRESTRICTNO ACTION。何为参照操作:就是当父表进行了更新操作时子表如何进行更新

这样我们就创建了一个参照操作时CASCADE的外键约束,接下来我们需要实验一下CASCADE的效果,我们需要在fk1和fk3中插入记录,这里需要注意的时,我们需要先在父表插入记录然后再在子表中插入记录,如果父表中没有数据,子表上哪查去

上述SQL语句是测试CASCADE的效果,其中涉及到DELETE语句下一篇笔记会详细讨论,这里可以举一反三,更新和SET NULL、RESTRICT的行为就可以理解了,不过在使用SET NULL时需要注意被操作列没有设置为NOT NULL,不然就会自相矛盾

不过在实际的开发中很少使用物理上的外键约束,通常使用逻辑上的外键约束取而代之,因为外键约束只对InnoDB存储引擎生效,如果我们使用的时MyISAM则完全无法使用FOREIGN KEY,所谓逻辑上的外键约束就是保持结构不变的前提下不适用FOREIGN KEY关键字。所谓结构不变指的是满足上面的外键约束的条件

前面的坑好像还有一个表级约束和列级约束没有填,列级约束既可以在列定义的时候生命,也可以在列定义后生命。但表级约束只能在列定义后声明

上述语句就属于定义在列定义的后面,在实际开发中列级约束用的比较多,表级约束使用的较少,但并不是所有的约束都存在表级约束和列级约束,例如DEFAULT和NOT NULL就不存在表级约束,只存在列级约束。而其他的约束可以存在表级约束和列级约束

About the author

NOBUG.IN

Add comment

By NOBUG.IN

Your sidebar area is currently empty. Hurry up and add some widgets.