MySQL ——约束以及修改数据表

时间:2021-06-11 14:28:01

知识点:

MySQL ——约束以及修改数据表


3.1 外键约束的要求解析

1. 外键约束:
1)保证数据一致性,完整性;
2)实现一对一或一对多的关系;
3)外键约束的要求:
a. 父表和子表必须使用相同的存储引擎,而且禁止使用临时表;

      父表:子表参照的表

      子表:具有外键列的表

b. 数据表的存储引擎只能为INNODB;
c. 外键列和参照列必须具有相似的数据类型,其中数字的长度、是否有符号位必须相同;而字符的长度可以不同;

      外键列:加过FOREIGN KEY的列

      参照列:外键列参照的列
d. 外键列和参照列必须创建索引,如果外键列不存在索引的话,MySQL将自动创建索引。

注:参照列没有索引,MySQL自动创建;外键列没有索引,MySQL不会创建。


2.编辑数据表的默认存储引擎

mysql配置文件:default-storage-engine=INNODB (在my.ini查看修改)

创建一个名为省份的数据表privince(父表)(应该是province,创建的时候打错了):

MySQL ——约束以及修改数据表


查看存储引擎类型:

MySQL ——约束以及修改数据表

存储引擎要求满足


创建一个名为 users (子表)的数据表:

MySQL ——约束以及修改数据表

出现错误:pid 字段的数据类型和父表中 id 的数据类型不一致导致,所以要保证数据类型完全相同。


修改命令重新创建:

MySQL ——约束以及修改数据表

pid(外键列)和id(参照列)具有相同或相似的数据类型,在数字的情况下必须相同,字符的情况下可以不同。外键列和参照列必须创建索引。


查看是否存在索引:

父表id字段:

MySQL ——约束以及修改数据表

从Column_name可以看到参照列上已经存在索引。


子表pid字段:

MySQL ——约束以及修改数据表

pid字段系统自动创建了索引。


3.2 外键约束的参照操作

1.CASCADE:从父表删除或更新且自动删除或更新指标的匹配的行
2.SET NULL:从父表删除或更新行,并设置指标的外键列为NULL。如果使用该选项,必须保证值列表没有指定NOT NULL
3.RESTRICT:拒绝对父表的删除或更新操作
4.NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
例:CASCADE
数据表中首先要存在记录,必须先在父表中插入记录。
创建新表users1
MySQL ——约束以及修改数据表

插入记录:

父表:

MySQL ——约束以及修改数据表

子表:

MySQL ——约束以及修改数据表

中间出现错误原因:父表总并不存在‘7’这样的id,所以书写是错误的。

子表记录不是1,2,3,4,原因在于:写记录时该条没有编写成功,但是id已经自动增加了1。


删除记录3:

MySQL ——约束以及修改数据表

当从父表中删除记录的时候,子表中的记录也被删除。


3.3 表级约束与列级约束

列级约束:对于一个列所创建的约束。

表级约束:对于两个或两个以上的列所创建的约束。
列级约束在使用时,既可以在列定义的时候声明,也可以在列定义以后声明,而表级的约束只能在列定义以后来声明。


在实际开发中,用列级约束比较多,表级约束很少用,
在所有的约束中,并不是说每种约束都存在着表级或列级约束。其中,NOT NULL 非空约束,DEFAULT约束,这两种约束就不存在表级约束,它们只有列级约束,而对于其他的三种,像主键,唯一,外键,它们都可以存在表级和列级约束。


3.4 修改数据表——添加/删除列

添加单列:

ALTER TABLE tbl_name ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name];
FIRST(最前面)|AFTER col_name(插入的列位于指定列的后方);如果省略FIRST|AFTER col_name,新建的列将位于所有列的最后。
MySQL ——约束以及修改数据表

MySQL ——约束以及修改数据表

MySQL ——约束以及修改数据表


添加多列:

ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);
注:无法指定位置,只能添加在原数据后面。

删除列:

ALTER TABLE tb1_name DROP[COLUMN] col_name;

多个操作可以同时操作,用逗号分开(在删除列的同时页可以新列)
MySQL ——约束以及修改数据表

MySQL ——约束以及修改数据表

3.5 修改数据表——添加约束

添加主键约束(只能添加一个):

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
MySQL ——约束以及修改数据表
constraint:可以给主键命名,如:PK_users2_id


添加唯一约束(可以添加多个):

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [index_type] (index_col_name,...)

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_type] (index_col_name,...) reference_definition

添加 / 删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DAFAULT literal |DROP DEFAULT

3.6 修改数据表——删除约束

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY;

不用选择字段:一张表有且只有一个主键。

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

在删除之前要先找到外键名,用‘show create table 数据表名;’来查看。

3.7 修改数据表——修改列定义和更名数据表

修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST |AFTER col_name];

修改列名称

ALTER TABLE tbl_name CHANGE [COLUMN] col_name new_col_name column_definition [FIRST|AFTER col_name];

数据表更名

法1:

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
法2:

RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2] ...