chap03 约束以及修改数据表

1.回顾和概述

数据类型：

• 整型
• 浮点型
• 字符型
• 日期时间型

数据表操作

• 如何创建数据表
• PRIMARY KEY(主键约束)
• UNIQUE KEY(唯一约束)
• DEFAULT(默认约束)
• NOT NULL(非空约束)
• 记录插入
• 查找记录

2.MySQL外键约束的要求解析

约束

1. 约束保证数据的完整性和一致性
2. 约束分为表级约束和列级约束
3. 约束类型包括：

• NOT NULL 非空约束
• PRIMARY KEY 主键约束
• UNIQUE KEY 唯一约束
• DEFAULT 默认约束
• FOREIGN KEY 外键约束

备注：表级约束针对两个或两个以上的字段，列级约束只针对一个字段。

FORENIG KEY
保持数据的一致性，完整性。
事项一对一或一对多的关系。

外键约束的要求：

1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2. 数据表的存储引擎只能为InnoDB
3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或者是否有符号位必须相同；而符号位的长度则可以不同。
4. 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

备注：
父表：子表所参照的表
子表：具有外键列的表
外键列：加上FOREIGN关键字的一列。
参照列：外键列所参照的一列。
参照列无索引时，MySQL不会创建，外键列无索引时，MySQL才会自动创建。

创建province表 数据类型不一致时无法添加

province表中id字段的属性为SMALLINT UNSIGNED不写符号位则默认为有符号位。
子表：有外键约束的表---users
父表：子表所参照的表---province
pid为外键列，id为参照列。

添加成功

验证索引显示
SHOW INDEX FROM province
SHOW INDEX FROM province\G

验证索引显示 image.png image.png image.png

3.MySQL外键约束的参照操作

1. CASCADE ：从父表删除或更新且自动删除或更新子表中匹配的行
2. SET NULL : 从父表中删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
3. RESTRICT : 拒绝对父表的删除或更新操作
4. NO ACTION: 标准SQL关键字，在MySQL中与RESTRICT同义。

image.png

父表更新记录时，子表中响应的行也会更新。

image.png image.png 展示当前父表

id字段自动编号

子表中插入元素 image.png

删除记录

删除父表中id为3的元素并显示 子表中id为3的元素也同时被删除

从父表中删除元素时子表中的记录也被随之删除。

4.MySQL表级约束和列级约束

• 对一个数据列的约束，成为列级约束。
• 对多个数据列的约束，称为表级约束。
• 列级约束既可以在列定义是声明，亦可以在列定义后声明。
• 表级约束只能在列定义后声明。

一般是列级约束

5.MySQL修改数据表--添加/删除列

• 添加单列
  ALTER TABLE tb1_name ADD [ COLUMN ] col_name column_definition [ FIRST | AFTER col_name]
  新插入的列在最前面或者在某一指定列的后面，如果省略，则默认插到最后一个位置。

初始列的情况 添加操作 添加后的情况 添加到FIRST

• 添加多列
  ALTER TABLE tb1_name ADD [ COLUMN ] (col_name col_definition)
  不能指定位置关系，只能在表的最后添加。

• 删除列
  ALTER TABLE DROP tb1_name DROP [ COLUMN ] col_name
  ALTER TABLE user1 DROP truename;

删除操作 删除的结果

6.MySQL修改数据表--添加约束

添加主键约束

ALTER TABLE tb1_name ADD [ CONSTRAINT [ symbol ] ] PRIMARY KEY [ index_type ] (index_col_name,...)

无主键 添加id字段 将id字段设置为主键

添加唯一约束

ALTER TABLE tb1_name ADD [ CONSTRAINT [ symbol ]] UNIQUE [ INDEX | KEY ] [ index_name ] [ index_type ] (index_col_name,...)

为username添加唯一约束 添加外键 image.png

添加、删除默认约束

ALTER TABLE tb1_name ALTER [ COLUMN ] col_name { SET DEFAULT literal | DROP DEFAULT }

Ex: ALTER TABLE user2 ADD age TINYINT UNSIGNED NOT NULL;

先添加字段 将user2表的age字段设置为默认值15 删除默认值

7.MySQL修改数据表--删除约束

删除主键约束

ALTER TABLE tb1_name DROP PRIMARY KEY

删除主键约束

主键约束一张表唯一，故不需要指定名字。

删除唯一约束

ALTER TABLE tb1_name DROP { INDEX | KEY } index_name

查看约束名 删除唯一约束

删除外键约束

image.png

ALTER TABLE tb1_name DROP FOREIGN KEY fk_symbol

image.png

删除index---pid之后的结果

8.MySQL修改数据表--修改列定义和更名数据表

修改列定义

ALTER TABLE tb1_name MODIFY [ COLUMN ] col_name col_definition [ FIRST | AFTER col_name ]

适合场景：数据类型或者位置有问题，
字段的排序 -- 字段挪动

ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST ;

修改id字段位置到FIRST 修改字段类型

由大类型到小类型可能会造成数据丢失

修改列名称

ALTER TABLE tb1_name CHANGE [ COLUMN ] old_col_name new_col_name column_definition [ FIRST | AFTER col_name]

修改列名称

数据表更名

• 方法1
  ALTER TABLE tb1_name RENAME [ TO | AS ] new_tb1_name

• 方法2
  RENAME TABLE tb1_name To new_tb1_name [ , tb1_name2 To new_tb1_name ]

数据表更名：法一 数据表更名：法二

小结

约束

• 功能

1. NOT NULL(非空约束)
2. PRIMARY KEY(主键约束)
3. UNIQUE KEY(唯一约束)
4. DEFAULT KEY(默认约束)
5. FOREIGN KEY(外键约束)

• 修改数据表

1. 列级约束
2. 表级约束

修改数据表

1. 针对字段的操作：添加/删除字段，修改列定义，修改列名称
2. 针对约束的操作：添加删除各种约束
3. 针对数据表的操作：数据表更名(两种方式)