MySQL 数据库的结构操作之列属性/字段

MySQL 数据库的结构操作之列（字段）属性 DDL

[TOC]

## 1. 列属性参数
真正约束字段的是数据类型，但数据类型的约束过于单一，需要一些额外的约束来更加的保证数据的合法性

### 1.1 空/非空 NULL/NOT NULL

__说明：__可约束字符是否可以为空，默认为NULL，数据库设计时尽量保证数据不为空，空数据没有意义，空数据也没有办法参与运算，任何数据与空运算得到都是空

***

### 1.2 默认值 default
__说明：__设置字段的默认值，时间默认设置时注意使用`()`包裹函数，有默认值列在插入时可以不指定列名，直接使用关键default

```SQL
-- -- 学生表中有两列，分别是姓名和性别，性别默认是男
alter table my_student add sex varchar(20) NOT NULL default '男';
-- 不指定性别，执行下面语句就插入了一条名为张三，性别男的记录
insert into my_student values('张三'，default)
```

***

### 1.3 字段描述 comment

__说明：__对表结构的列添加文字描述

```SQL
-- 使用下面的语句可以查看表结构信息，信息包含了列的描述
show create table my_student
-- 给学生添加一个年级字段，并给出描述
alter table my_student add gender varchar(2) not null commnet "年级"；
```

***

### 1.4 主键 primary key
__说明：__一张表中最多只有一个主键，主键用来约束该字段里面的数据不能重复，主键列不允许为空
#### 1.4.1 新增主键
新增表时可以设置字段为主键，修改表添加新属性时可以设置主键，但要求表目前不存在主键，一张表只能有一个主键

```SQL
-- 新增一列id并设置为主键和自增长
alter table my_student add id int primary key；
```

#### 1.4.2 复合主键
__语法：__alter table 表名 primary key(字段1,字段2,……)；
至少两个字段组合，会对应一个唯一值，这样的多个字段可以设置为复合主键。

例如学生表中，一个学生一个学科只会有一个成绩，那么学生的学生编号和学科编号，就可以创建一个复合主键，示例代码如下

```SQL
-- 学生编号和学科编号，创建一个复合主键
create table my_studentScore(
number char(10) comment '学生编号'，
course char(10) comment '学科编号'，
sorce tinyint unsigned comment '分数',
primary key(number,course)
)
```

#### 1.4.3 追加主键
表已创建，但未设置主键，这样可以使用下面的语句追加主键，追加主键需要被追加的字段对应的数据是不重复的，modify修改表属性时，如果表已有主键了，不可以在字段后面添加 `primary key`

```SQL
-- 将id追加为主键
alter table my_student add primary key(id)；
```

#### 1.4.5 删除主键
语法：alter table 表名 drop primary key；

#### 1.4.6 主键分类
逻辑主键（字段没有业务含义，值可以是任意，例如给表添加额外的id）和业务主键（字段有业务含义，例如学号）

***

### 1.5 自增长 auto_increment
设置自增长的字段，不给值，或者给默认值，或者给的NULL时，会自动被系统出发，系统会从当前字段中已有的最大值再+1操作（自增长的第一个元素默认是1），然后赋值给新插入数据中的此字段，自增长通常与主键搭配使用

__自增长特点：__

+ 任何一个字段要做自增长，前提必须是，本身是一个索引（Key一栏有值）
 - 错误提示：ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
+ 自增长字段必须是整型
 - 错误提示：ERROR 1063 (42000): Incorrect column specifier for column 'id'
+ 一张表最多一个自增的字段

```SQL
-- 第一个特点：本身设置为索引验证，下面id未设置为主键
alter table test_date add id int auto_increment;
-- 第二个特点：自增长的字段必须是整型验证
alter table test_date add id varchar(2) primary key auto_increment;
```

__自增长配置：__使用下面的语句可以查看关于自增长的变量配置
```SQL
show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
```
__自增长系统变量：__ `auto_increment_increment` ：自增长的步长，默认是1，`auto_increment_offset` ：自增长的初始值，默认是1，修改步长变量 `set auto_increment_increment = 2`， 建议不要去修改系统的自增长变量

#### 1.5.1 新增自增长

```SQL
alter table my_student add id int primary key auto_increment；
```

#### 1.5.2 使用自增长
以下三条语句都可以插入成功

```SQL
insert into my_student(name) values('曹操')
insert into my_student(id,name) values(null,'曹植')
insert into my_student(id,name) values(default,'曹丕')
```
> __附件说明：__自增长如果对应的字段输入了值，那么自增长会失效，但下一次还是能够正确的自增长，从最大值+1开始，例如给的自增长值是10，插入成功后，下次自增长值就是11。查看下一次自增长的值，可以使用 `show create table my_student` 语句，在输出的AUTO_INCREMENT=11中

#### 1.5.3 修改自增长

+ 自增长的字段变更：修改前需要先删除目前的自增长属性，然后再创建，一张表中只能有一个自增长
+ 修改自增长已存在的值：修改只能比当前已有的自增长的最大值大，小的话不会报错但是不生效
 - __语法：__alter table 表 increment = 值

#### 1.5.3 删除自增长
通过modify来修改字段的自增长属性的方式来删除，保证字段没有auto_increment即可

__语法：__alter table 表 modify 字段 类型，不要给字段添加auto_increment即可

***

### 1.6 唯一键 unique key
唯一键约束和主键类似，唯一键同样不可以重复但可以为空，并可以存在多个空（空数据不参与唯一性比较），一张表只有一个主键，而表中又有其他字段需要值唯一时，就可以使用唯一键来进行唯一约束。

#### 1.6.1 新增唯一键
新增表时可以设置字段为唯一键，修改表添加新属性时可以设置唯一键，语句中 `unique key` 可以简写成 `unique`

```SQL
-- 新增一列学号num并设置为唯一键
alter table my_student add num varchar(20) unique；
```

#### 1.6.2 追加唯一键
__语法：__alter table 表名 add unique key(字段1)；

```SQL
-- 给学号num追加成唯一键
alter table my_student add unique key(num)；
```

#### 1.6.3 复合唯一键
__追加复合唯一键语法：__alter table 表名 add unique key(字段1,字段2,……)；
至少两个字段组合，会对应一个唯一值，这样的多个字段可以设置为复合唯一键

例如学生表中，一个学生一个学科只会有一个成绩，那么学生的学生编号和学科编号，就可以创建一个复合唯一键，示例代码如下

```SQL
-- 学生编号和学科编号，创建一个复合唯一键
create table my_studentScore(
number char(10) comment '学生编号'，
course char(10) comment '学科编号'，
sorce tinyint unsigned comment '分数',
unique key(number,course)
)
```

#### 1.6.4 删除唯一键
唯一键允许多个，删除的方式是根据唯一键的索引名称来进行删除，查看唯一键的索引名称，可以使用 `desc 表` 语句查看表结构，在UNIQUE KEY 后面的字符串就是索引名，唯一键的索引名默认是字段名

__语法：__alter table 表名 drop index 索引名；

```SQL
-- 删除学生学号唯一键属性，num为索引名，这里和字段名是一致的
alter table 表名 drop index num；
```

***
### 1.7 外键 foreign key
外键是指外部的键，键不在自己表中，如果一张表中有一个字段（非主键），指向另外一张表的主键，那么将该字段称为外键，一张表可以有多个外键，外键名称不能重名

外键要求字段本身必须是一个索引，非主键索引的普通索引，如果字段本身没有索引，外键会创建一个索引，然后才会创建外键本身

#### 1.7.1 新增外键
外键可以在创建表的时候或者创建表之后添加

##### 1.7.1.1  创建表的时候添加外键

__语法：__在所有的表字段之后，使用foreign key(外键字段) references 外部表(主键字段)

创建一张有外键的表 test_foreign_add

```SQL
create table test_foreign_add (
id int primary key auto_increment,
name varchar(20) not null ,
forid int ,
foreign key (forid) references my_student(id)
) charset utf8mb4;
```

desc test_foreign_add 获取创建后的表结构

| Field | Type        | Null | Key | Default | Extra          |
|-------|-------------|------|-----|---------|----------------|
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| forid | int         | YES  | MUL | NULL    |      &nbsp;  |

show create table test_foreign_add 获取创建的真实语句

```SQL
CREATE TABLE `test_foreign_add` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `forid` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `forid` (`forid`),
  CONSTRAINT `test_foreign_add_ibfk_1` FOREIGN KEY (`forid`) REFERENCES `my_student` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
```

##### 1.7.1.1  修改表的时候添加外键

__语法：__alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 外部表(主键字段)

```SQL
alter table my_studentClasssRelation add
constraint student_rela_1 -- 外键名称
foreign key (student)  -- 指定外键字段
references my_student(id); -- 引用外部表主键
```

desc my_studentClasssRelation 修改后的表结构

| Field      | Type          | Null | Key | Default   | Extra             |
|-------|-------------|------|-----|---------|----------------|
| id         | int           | NO   | PRI | NULL      | auto_increment    |
| student    | int           | NO   | MUL | 0         |                   |
| classs     | int           | NO   |     | 0         |                   |
| level      | int           | NO   |     | 0         |                   |
| createTime | date          | NO   |     | curdate() | DEFAULT_GENERATED |
| endTime    | date          | YES  |     | NULL      |                   |
| endReason  | varchar(2000) | YES  |     | NULL      |   &nbsp;  |

#### 1.7.2 删除外键
外键不可修改，只能先删除后再新增

__语法：__alter table 表名 drop foreign key 外键名字

```SQL
-- 删除名为student_rela_1的外键
alter table my_studentClasssRelation drop foreign key student_rela_1; -- student_rela_1 外键名称
```

desc my_studentClasssRelation 修改后的表结构

从上面的表结构看，student仍然是外键，但实际上已经删除了，这是因为外键还是普通的索引导致表结构还未发生变更，使用show create table my_studentClasssRelation查看表创建语句确认，从语句中可以看出，外键已经删除了

```SQL
CREATE TABLE `my_studentclasssrelation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student` int NOT NULL DEFAULT '0' COMMENT '学生id',
  `classs` int NOT NULL DEFAULT '0' COMMENT '班级id',
  `level` int NOT NULL DEFAULT '0' COMMENT '班级级别',
  `createTime` date NOT NULL DEFAULT (curdate()) COMMENT '创建日期',
  `endTime` date DEFAULT NULL COMMENT '结束日期',
  `endReason` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '结束原因',
  PRIMARY KEY (`id`),
  KEY `student_rela_1` (`student`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
```

#### 1.7.3 外键约束
##### 1.7.3.1 默认约束
外键默认的作用有两点，一个是对父表，父表指的是外键字段引用的主键字段所在的表，一个是对子表，子表指的是外键字段所在的表

__对子表约束：__子表数据进行写操作的时候，如果对应的外键字段在父表找不到对应的匹配，那么操作将失败

```SQL
-- 插入数据，外键字段在父表中不存在
insert into my_studentClasssRelation (student,classs,level) values(11,7,10);
```

上面的语句插入会失败：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ('study'.'my_studentclasssrelation', CONSTRAINT 'student_rela_1' FOREIGN KEY ('student') REFERENCES 'my_student' ('id'))，子表student外键字段，在父表主键中不存在

__对父表约束：__父表数据进行写操作时，如果删除和修改有操作到主键本身，并且对应的主键在子表中已经被数据所引用，那么此类的删除和修改将失败

```SQL
-- 删除一条记录，这条记录的主键，已经被作为外键引用了
delete from my_student where id = 2;
```

上面的语句会失败：ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ('study'.'my_studentclasssrelation', CONSTRAINT 'student_rela_1' FOREIGN KEY ('student') REFERENCES 'my_student' ('id'))，子表已经引用了student的主键id，所以操作失败

##### 1.7.3.2 定制约束模式
约束模式有三种，都是针对父表的约束

+ __District严格模式：__这是默认的模式，父表不能删除或更新一个已经被子表数据引用的记录
+ __Cascade级联模式：__父表的操作，对应子表关联的数据也跟着操作
+ __Set null置空模式：__父表的操作之后，子表对应的外键字段的数据被置空

比较合理的使用模式的做法是，删除的时候子表置空，更新的时候子表级联操作指定模式的语句

__语法：__foreign key(外键字段) references 父表(主键字段) on delete 模式 on update 模式

例如上面的合理做法语句就应该是 foreign key(外键字段) references 父表(主键字段) on delete Set null on update Cascade

```SQL
create table test_foreign_mode (
id int primary key auto_increment,
name varchar(20) not null ,
forid int ,
foreign key (forid) -- 添加外键
references my_student(id) -- 引用表的主键
on delete set null -- 指定删除模式
on update cascade -- 指定更新模式
) charset utf8mb4;
```

desc test_foreign_mode 获取创建的表结构

```SQL
-- 插入测试数据
insert into test_foreign_mode (name,forid) values ('孙权',2),('孙策',4),('孙坚',3),('孙尚香',5);
```

__插入操作后的结果__

| id | name   | forid |
|----|--------|-------|
|  1 | 孙权   |     2 |
|  2 | 孙策   |     4 |
|  3 | 孙坚   |     3 |
|  4 | 孙尚香 |     5 |

__更新父表的主键id__

```SQL
-- 将父表的主键id为2的记录修改为11
update my_student set id = 11 where id = 2;
```

__修改父表操作后的结果__

| id | birthday | name | gender | height | age | nation |
|----|--------|-------|-------|-------|-------|-------|
| 3 | 2021-11-12 | 刘禅 | 男 | 1.82 | 16 | 汉 |
| 4 | 1990-11-25 | 关羽 | 男 | 1.85 | 52 | 苗 |
| 5 | 1992-09-08 | 张飞 | 男 | 1.82 | 50 | 汉 |
| 6 | 1992-06-12 | 马超 | 男 | 1.77 | 46 | 苗 |
| 7 | 1993-10-22 | 甄宓 | 女 | 1.62 | 38 | 汉 |
| 8 | 1991-11-12 | 小乔 | 女 | 1.56 | 26 | 苗 |
| 9 | 1990-01-25 | 大乔 | 女 | 1.65 | 27 | 汉 |
| 10 | 1988-09-08 | 蔡文姬 | 女 | 1.82 | 30 | 苗 |
|11 | 1989-10-22 | 刘备 | 男 | 1.72 | 58 | 苗 |

| id | name | forid |
|----|--------|-------|
| 1 | 孙权 | 11 |
| 2 | 孙策 | 4 |
| 3 | 孙坚 | 3 |
| 4 | 孙尚香 | 5 |

从上面的结果可以看到，修改父表的主键值后，子表的外键值也跟着更新了，这是因为子表的外键设置了`更新时级联模式的约束`

__删除已被子表外键使用了的父表的记录__

```SQL
-- 删除id为11点的记录，11的主键被子表的外键已经使用了
delete from my_student where id = 11;
```

__删除父表操作后的结果__

| id | name | forid |
|----|--------|-------|
| 1 | 孙权 | NULL |
| 2 | 孙策 | 4 |
| 3 | 孙坚 | 3 |
| 4 | 孙尚香 | 5 |

从上面的结果可以看到，删除父表记录后，子表的外键值也跟着更新了，这是因为子表的外键还设置了`删除时set null置空模式的约束`，需要注意的是删除置空的约束模式，需要外键字段允许为空，否则置空约束的外键创建会失败

外键虽然强大，能进行各种约束，但外键约束降低了数据的可控性，例如父表的主键是多张表的外键，这样如果每张子表的外键约束规则不一致，它就会导致数据维护的可控性降低，通常在实际开发中，很少使用外键

#### 1.7.4 外键条件
+ 外键要存在，首先必须保证表的存储引擎是innodb，innodb是默认的存储引擎，如果不是innodb存储引擎，那么外键可以创建成功，但是没有约束效果
+ 外键字段的字段类型必须与父表的主键类型完全一致
+ 一张表中的外键名称不能重复
+ 增加外键的字段，数据已经存在，必须保证数据与父表的主键一致，如果子表已经有了主表主键不存在的数据，那么新增外键将失败

***

## 2. 位置参数

位置参数是指可以将字段指定存放在表中的任意位置

|参数|说明|
|--|--|
|first|放在表字段第一位最开始的位置|
|after|放在指定字段之后，例如`after age`将新增/修改的字段放在age字段之后，默认是在最后一个字段之后|

```SQL
-- 新增数据类型为date的birthday字段并放在表列的第一位
alter table my_student add birthday date first
-- 新增数据类型为decimal(18,2)的height字段并放在gender字段之后
alter table my_student add height decimal(18,2) after gender
-- 新增字段语句后面没有添加位置选项则新增的字段默认在最后的位置上
-- alter table my_student add weight decimal(18,2)
```

## 2. 字段字符集
charcter set/charset:设置字段的字符集，`设置时需要放在数据类型之后`

> __附加说明《MySQL中UTF8字符集》：__在MySQL中utf8分为了UTF8MB3和UTF8MB4，UTF8MB3是指每个字符最多三个字节，UTF8MB4则是每个字符最多四个字节，对于汉字而言显然UTF8MB4更为合适，所以在创建表字段或修改表字段时建议使用`charset utf8mb4`