mysql建表的字段类型和约束条件

一 . 常见字段类型分类 

常见字段类型

类型	分类	描述
数值型	整型
	小数	①定点数 ②浮点数
字符型	较短的文本	①char ②varchar
	较长的文本	①text ②blob(二进制数据)
日期时间型

1.1数值型 

1.1.1 整型

特点：

1. 整数类型默认是有符号，如果想设置成无符号，需要在整数类型后添加 UNSIGNED
2. 如果插入的数值超出了整型的范围，会报错（out of range）,并且插入的值是临界值(具体的看数据库的版本)
3. 如果不设置长度，会有默认的长度

整型

整数类型	字节	有符号和无符号的表示	范围
Tinyint	1	有符号 Tinyint	-128~127
		无符号 Tinyint UNSIGNED	0~255
Smallint	2	有符号 Smallint	-32768 ~ 32767
		无符号  Smallint UNSIGNED	0~65535
Mediumint	3	有符号 Mediumint	-8388608 ~ 8388607
		无符号 Mediumint UNSIGNED	0 ~ 1677215
Int integer	4	有符号 Int	-2147483648 ~ 2147483647
		无符号 Int  UNSIGNED	0~ 4294967295
Bigint	8	有符号 Bigint	-9223372036854775808~9223372036854775807
		无符号 Bigint  UNSIGNED	0 ~ 2**64-1

1.1.1.1 设置整型字段的无符号和有符号 

语法：整数类型 unsigned   如：INT UNSIGNED

1.1.1.2字段类型长度的含义

描述：建表的时候，在设置字段类型时，一般会设置字段的长度。这里指的长度并不是指该字段的数值的表示范围，字段的表示范围是由字段的类型决定的，这里的长度，实际上指的是数据的显示宽度，即宽度不够时会在数值左边添加0来填充。

如： 字段类型 (长度)  ZEROFILL

注：ZEROFILL 自动将数值转换为无符号的整数

如下：

1.1.2小数 

特点：

1. M 表示  ： 整数部分 + 小数部分
2. D 表示 ： 小数部位
3. 当超过范围（当整数部分长度  > M-D  ），具体的报错警告得看具体的版本  mysql8版本 即 当整数部分长度  > M-D   就会报错
4. M和D都可以省略。decimal省略M和D,则M默认为10，D默认为0，即只会保留整数部分；float和double则按范围来决定精度
5. 定点型的精度较高，如果要求插入的数值精度较高则使用定点型，如货币运算

1.1.2.1 浮点数类型

浮点数类型

浮点数类型	字节	范围
float(M,D)	4	±1.75494321E-38 ~ ±3.402823466E+38
double(M,D)	8	±2.2250738585072014E-308 ~ ±1.7976931348623157E+308

1.1.2.2 定点数类型（精度更高）

特点：

定点数类型

定点数类型	字节	范围
DEC(M,D) decimal(M,D)	M+2	最大取值范围与double相同，给定的小数的有效取值范围由M和D决定

1.2字符型 

1.2.1 较短的文本

较短文本

字符类型	最多字符数	描述	特点	空间利用率	时间效率
char(M)	M  M可以省略，默认为1	M为 0~255之间的整数	固定长度的字符	比较消耗空间，空间利用率低	高
varchar(M)	M M不可以省略	M为0~65535之间的整数	可变长度的字符	比较节省空间，空间利用率高	低

1.2.1.1 char

1.2.1.2 varchar

1.2.2 较长的文本

1.2.2.1 text

1.2.2.2 blob

1.2.3 其他字符串

其他字符串

字符类型	分类	描述
二进制字符串	binary 保存较短的二进制字符串	类似于char和varchar,不同的是binary,varbinary只包含二进制字符串，不包含非二进制的字符串
	varbinary 保存较长的二进制字符串
enum类型	具体用法例一	又称枚举类型 要求插入的值必须属于列表中指定值之一 如：列表成员为1~255，则只需要1字节存储
set类型	具体用法例二	和enum类型类型类似，它可以保存0~64个成员 和enum类型的最大区别是set类型一次可以选择多个成员，而enum类型只能选择一个 根据成员个数的不同，存储占用的字节也不相同

例一：

例二：

1.3 日期时间型 

日期时间型数据

日期时间类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datatime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	19700101080001	2038年的某个时刻
time	3	-838:59:59	838:59:59
year	1	1901	2155

datetime和timestamp的区别

1. Timestamp支持的时间范围较小,取值范围:19700101080001 到 2038 年的某个时间 ；Datetime的取值范围: 1000-1-1 到 9999- 12-31

2. timestamp和实际时区有关,更能反映实际的日期，而datetime则只能反映出插入时的当地时区  如：例一

3. timestamp的属性受Mysq|版本和SQLMode的影响很大

例一：

 

二 .约束类型

描述：在创建表结构时的一种约束条件，用于限制表中的数据，目的时为了保证插入数据的可靠性和准确性 

分类（六大约束）：

1. NOT NULL:非空，用于保证该字段的值不能为空
2. DEFAULT:默认，用于保证该字段有默认值
3. PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
4. UNIQUE:唯一，用于保证该字段的值具有唯一-性，可以为空
5. CHECK:检查约束[mysql中不支持]
6. FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值

添加约束的时机：

1. 创建表时
2. 修改表时

约束的添加分类

1. 列级（字段）约束：六大约束在语法上都支持，但外键约束没有效果
2. 表级约束：除了非空(NOT NULL)，默认(DEFAULT)，其他都支持

CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型 列级约束,
...
字段名 字段类型 列级约束,
表级约束
);

主键和唯一的区别： 

主键和唯一的区别

约束类型	保证唯一性	是否允许为空	是否有多个	是否允许组合（联合键）
主键 PRIMARY KEY	√	×	至多有一个主键字段	可以（但不推荐使用）
唯一 UNIQUE	√	√	可以有多个唯一字段	可以（但不推荐使用）

2.1 列级约束

语法：

CREATE TABLE 表名(
字段名 字段类型 列级约束 列级约束 列级约束...,
字段名 字段类型 列级约束 列级约束 列级约束...,
...
字段名 字段类型 列级约束 列级约束 列级约束...,
);

注：

1. 只支持 非空，默认，唯一，主键
2. 一个字段可以有多个约束

例一：

CREATE TABLE stuinfo(
id INT PRIMARY KEY, #主键
stuname VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender="男" OR gender="女"),#检查
seat INT UNIQUE,#唯一
age TINYINT DEFAULT 18,#默认
majorid TINYINT REFERENCES major(id) #外键
);


CREATE TABLE major(
id TINYINT PRIMARY KEY,#主键
majorname VARCHAR(20)
);

2.2 表级约束 

语法：

CREATE TABLE 表名(
字段名 字段类型,
字段名 字段类型,
...
字段名 字段类型,


【CONSTRAINT 约束名】 约束类型(字段名),
【CONSTRAINT 约束名】 约束类型(字段名),
...
【CONSTRAINT 约束名】 约束类型(字段名),
【CONSTRAINT 约束名】 FOREIGN KEY(被关联的表名) REFERENCES 被关联的表名(被关联的字段名) #外键
);

例一：

CREATE TABLE stuinfo(
id INT, 
stuname VARCHAR(20) NOT NULL,#非空
gender CHAR(1),
seat INT,
age TINYINT DEFAULT 18, #默认
majorid TINYINT,

CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT ck CHECK(gender="男" OR gender="女"),#检查
CONSTRAINT uq UNIQUE(seat),#唯一
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
);

2.2.1 联合主键

联合主键：两个及以上的字段共同组成一个主键.

CREATE TABLE stuinfo (
	id INT,
	stuname VARCHAR ( 20 ) NOT NULL,#非空
	gender CHAR ( 1 ),
	seat INT,
	age TINYINT DEFAULT 18,#默认
	majorid TINYINT,
	
	PRIMARY KEY (id,stuname),#联合主键
	CHECK ( gender = "男" OR gender = "女" ),#检查
	UNIQUE ( seat ),#唯一
	FOREIGN KEY ( majorid ) REFERENCES major ( id ) #外键
);

如何判断主键是否重复 

2.2.2 联合唯一键 

CREATE TABLE stuinfo (
	id INT,
	stuname VARCHAR ( 20 ) NOT NULL,#非空
	gender CHAR ( 1 ),
	seat INT,
	age TINYINT DEFAULT 18,#默认
	majorid TINYINT,
	
	PRIMARY KEY (id),#主键
	CHECK ( gender = "男" OR gender = "女" ),#检查
	CONSTRAINT 联合唯一键 UNIQUE (seat,stuname),#联合唯一键
	FOREIGN KEY ( majorid ) REFERENCES major ( id ) #外键
);

 2.2.3 外键

特点：

1. 要求在从表设置外键关系
2. 从表外键字段类型要和主表关联字段类型一致或兼容，但从表外键字段名可以和主表关联字段名不同
3. 主表的关联字段必须时一个key(主键或唯一)
4. 插入数据时，先插入主表，再插入从表
5. 删除数据时，先删除从表，再删除主表

2.3 修改表时添加约束类型

语法：

①列级约束

ALTER TABLE 表名 MODIFY COLUMN 字段名 新字段类型 【新约束类型】;

如： ALTER TABLE 表名 MODIFY COLUMN id PRIMARY KEY; #添加主键约束

②表级约束

ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(字段名);

 如：ALTER TABLE 表名 ADD PRIMARY KEY(id);  #添加主键约束

2.3.1 添加非空约束

 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NOT NULL;

2.3.2 添加默认约束

 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 DEFAULT 默认值;

2.3.3 添加主键约束

①列级约束

ALTER TABLE 表名 MODIFY COLUMN 字段名 PRIMARY KEY;

②表级约束

ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 PRIMARY KEY(字段名);

2.3.4 添加唯一约束

①列级约束

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 UNIQUE;

②表级约束

ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】  UNIQUE(字段名);

2.3.5 添加外键

ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】  FOREIGN KEY (主表名)  REFERENCES  主表名( 关联字段名 );

2.4 修改表时删除约束类型

2.4.1 删除非空约束

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NULL;

2.4.2 删除默认约束

 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;

2.4.3 删除主键

ALTER TABLE 表名 DROP PRIMARY KEY

2.4.4 删除唯一键

ALTER TABLE 表名 DROP INDEX 唯一键字段名;

2.4.5 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名

三 .级联删除与级联置空

3.1 级联删除

在删除有外键的表时，一般先删除从表的相关数据，再删除主表的相关数据。

但硬要先删除主表的相关数据，则可以为从表设置级联关系

语法一：

级联删除：在删除主表相关数据的同时，从表相应的数据也会被删除

ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY(主表名) REFERENCES 主表名(外键字段) ON DELETE CASCADE;

3.2 级联置空

级联置空：在删除主表相关数据的同时，从表相应的数据不会被删除

ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY(主表名) REFERENCES 主表名(外键字段) ON DELETE SET NULL;