知识点:
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,创建的时候打错了):
查看存储引擎类型:
存储引擎要求满足
创建一个名为 users (子表)的数据表:
出现错误:pid 字段的数据类型和父表中 id 的数据类型不一致导致,所以要保证数据类型完全相同。
修改命令重新创建:
pid(外键列)和id(参照列)具有相同或相似的数据类型,在数字的情况下必须相同,字符的情况下可以不同。外键列和参照列必须创建索引。
查看是否存在索引:
父表id字段:
从Column_name可以看到参照列上已经存在索引。
子表pid字段:
pid字段系统自动创建了索引。
3.2 外键约束的参照操作
1.CASCADE:从父表删除或更新且自动删除或更新指标的匹配的行
2.SET NULL:从父表删除或更新行,并设置指标的外键列为NULL。如果使用该选项,必须保证值列表没有指定NOT NULL
3.RESTRICT:拒绝对父表的删除或更新操作
4.NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
例:CASCADE
数据表中首先要存在记录,必须先在父表中插入记录。
创建新表users1
插入记录:
父表:
子表:
中间出现错误原因:父表总并不存在‘7’这样的id,所以书写是错误的。
子表记录不是1,2,3,4,原因在于:写记录时该条没有编写成功,但是id已经自动增加了1。
删除记录3:
当从父表中删除记录的时候,子表中的记录也被删除。
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,新建的列将位于所有列的最后。
添加多列:
ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);
注:无法指定位置,只能添加在原数据后面。删除列:
ALTER TABLE tb1_name DROP[COLUMN] col_name;
多个操作可以同时操作,用逗号分开(在删除列的同时页可以新列)
3.5 修改数据表——添加约束
添加主键约束(只能添加一个):
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
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] ...