关于建表、表字段的增删改等的语法及举例（字段类型、字段约束、建表语法、表字段增删改、注意事项等）

天行健，君子以自强不息；地势坤，君子以厚德载物。

---

每个人都有惰性，但不断学习是好好生活的根本，共勉！

---

文章均为学习整理笔记，分享记录为主，如有错误请指正，共同学习进步。

---

一、建表模板

建表语法

模板

CREATE TABLE if NOT EXISTS `tb_test_tool` (
	`id` INTEGER UNSIGNED AUTO_INCREMENT ,
	`tool_id` INTEGER NOT NULL ,
	`tool_name` VARCHAR(60) ,
	`tool_description` VARCHAR(100) ,
	`tool_version` VARCHAR(20) ,
	`tool_creater` VARCHAR(10) ,
	`tool_applicable_scope` VARCHAR(50) ,
	`tool_create_time` DATETIME ,
	PRIMARY KEY (`id`)
)ENGINE = INNODB default CHARSET = UTF8MB4 ;

二、字段

1. 字段类型

MySQL中的表字段类型主要有以下几种

类型名	作用	类型值
整型Integer	用于存储整数类型的值	如INT、BIGINT、TINYINT、SMALLINT、MEDIUMINT、BIT
字符型Text	用与存储字符串类型的值	如CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、JSON、UUID
日期时间型Date和Time	用于存储日期时间类型的值	如DATE、TIME、YEAR、DATETIME、TIMESTAMP
二进制文件类型	用于存储二进制类型的值	如BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
空间类型	用于存储空间类型的值	如POINT、LINESTRING、POLYGON、GEOMETRY、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION
浮点型	用于存储更为精确的数值	如FLOAT、DOUBLE、DECIMAL
其他类型	用于存储其他类型的值	如UNKNOWN、ENUM、SET

2. 字段约束

约束字段的取值范围和行为

约束名	解释	作用
DEFAULT	默认值约束	用于设置字段默认值
NOT NULL	非空约束	用于限制字段不能为空
PRIMARY KEY	主键约束	用于唯一标识表的每一行，保证每行的唯一性，一般都是默认的id，也可自定义
FOREIGN KEY	外键约束	用于与其他表建立关联关系的字段，保持数据的完整性
UNIQUE	唯一约束	用于限制字段的值在表中不能重复，表中该字段的值唯一

三、字段定义语法及举例

1. 指定字段的默认值

关于如何在建表时指定字段的默认值：
语法

`字段名` 字段类型 DEFAULT 默认值 

举例
将字段name的值默认为李白

`name` varchar(20) default "libai" 

注：在sql语法中字符串需要加双引号，数字不需要加

2. 指定字段的值唯一

建表时指定字段值唯一，该字段的值不可重复存入
语法

`字段名` 字段类型 unique 

举例
将字段name的值唯一，一旦存入一个值，该表中此字段的值不可重复，即相同的值不可以存入第二次

`name` varchar(20) unique 

3. 非空字段定义

语法

`字段名` 字段类型 not null 

举例
将字段name的值定义为不为空，则存入数据时该字段必须有值

`name` varchar(20) not null 

4. 混合使用

字段约束可以多个约束字段同时使用
语法

`字段名` 字段类型 not null unique 

举例
将字段name的值定义为不为空且值唯一，则存入数据时该字段必须有值，且不可重复

`name` varchar(20) not null unique

四、建表后对字段的操作

1. 新增字段

在已有表中新增字段
语法

alter table 表名 add 字段名 字段类型 ;

举例
如在已创建的表tb_test_user中新增字段address，类型为varchar，长度50

alter table tb_test_user add address varchar(50) ;

2. 修改字段

2.1 修改字段名

如果只是单纯修改字段的名字，可使用以下命令
语法

alter table 表名 rename column 原字段 to 新字段 ;

举例
如将表字段address改为address123

alter table tb_test_user rename column address to address123 ;

2.2 修改字段名和类型

修改表字段名和类型
在已有的表中修改现有表字段名
语法

alter table 表名 change 原字段 新字段 新字段类型 ;

举例
如将表字段address改为address123，类型改为varchar(50)

alter table tb_test_user change address address123 varchar(50) ;

注：该命令执行时会提示 mysql后续版本会移除该命令，也就是该命令在以后的版本中可能会不可用

3. 删除字段

使用drop移除字段
语法

alter table 表名 drop column 字段 ;

举例
如移除表字段address

alter table tb_test_user drop address ;

4. 修改或删除字段的默认值

4.1 修改字段的默认值

使用alter set关键字来修改字段默认值
语法

alter table 表名 alter column 字段 set default 默认值 ;

举例
将表tb_test_user中的字段age的默认值修改为30

alter table tb_test_user alter column age set default 30 ;

4.2 删除字段的默认值

语法

alter table 表名 alter column 字段 drop default ;

举例
将表tb_test_user中的字段age的默认值删除

alter table tb_test_user alter column age drop default ;

删除后插入数据时没有该字段的值时默认会变为null

五、拓展

1. VARCHAR和CHAR的区别

主要为长度的区别，CHAR是固定长度，VARCHAR是可变长度

• CHAR类型的值不满足定义长度时会以空格填充，直到填充为定义时的长度为止，当然，这样会占用额外的空间
• VARCHAR类型的值不满足长度时也不会填充，但是会有一个额外的值来记录字符长度，这种类型可能更灵活
• VARCHAR和CHAR后面括号中的值就是定义的最大字符长度的值，该值为字节byte的值，每个英文一个字节，每个中文为两个字节，如VARCHAR(50)表示该字段可存储字符英文为50个，或者中文25个，或者混合存储，加起来字节最多为50

2. INTEGER和INT的区别

无区别

3. INTEGER(10)警告

数值类型后无需加括号和值，如INTEGER

	`tool_id` INTEGER(10) NOT NULL ,

报警告，警告描述表示在后续的版本中会移除对INTEGER类型字符宽度的描述，也就是INTEGER后面不需要跟括号和值指定
这是一种多余的做法

修改为

	`tool_id` INTEGER NOT NULL ,

4. 字符集问题

以前建表定义字符集就是utf8，现在建议就是使用utf8mb4
如下

ENGINE = INNODB default CHARSET = UTF8 ;

警告描述，在后续的版本中将会移除utf8的使用，现在用的是utf8mb3后续会更新为utf8mb4，推荐使用utf8mb4

将字符集修改为UTF8MB4

ENGINE = INNODB default CHARSET = UTF8MB4 ;

---

感谢阅读，祝君暴富！