Mysql 基础（九） -- 类型和约束

类型

数值型
整型 : trinyint, smallint, mdiumint ,int , bigint

特点：
① 如何不设置无符号还是有符号，默认是有符号， 如果想设置无符号，需要添加 unsigned 关键字
②如果插入的数值超出了整型的范围，会报 out of range 异常，并插入临界值
③如果不设置长度，会有默认的长度，长度代表了显示的最大宽度。如果不够会使用0 在左边填充，但必须搭配 zerofill 使用

如何设置无符号和有符号
CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED  // 设置无符号 UNSIGNED
)

小数：
定点数 : dec(M,D), decimal(M,D)
浮点数 : float(M,D), double(M,D)

特点：
① M 和 D ,M 整数部位 + 小数 部位， D: 小数部门，如果超出范围，则插入临界值。 如果M 和 D都省略，可以插入默认值
② M 和 D 都可以省略，如果是 decimal 则 M 默认为 10，D默认为0， 如果是 float 和 double 会根据插入的数值的精度来决定精度
③ 定点型的精确度较高，则

字符型
较短的文本： char 、varchar

	写法	M的意思	特点	空间的耗费	效率
char	char(M)	最大的字符数,可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar(M)	最大的字符数	可变长度的字符	比较节省	低

char 类型 可以设置 枚举 类型 , 不区分大小写

CREATE TABLE tab_enum(
	c1 ENUM('a','b','c')
)

char类型 set类型

CREATE TABLE tab_set
	s1 SET('a','b','c') 

添加数据时，只可以添加 a b c 三个。如果不对那么默认为空

较长的文本： `text`、 `blob`

日期型


分类：
date 只保存 日期
time 只保存时间
year 只保存年份

datetime 保存日期 + 时间
timestamp 保存日期 + 时间

特点：

	字节	范围	时区的影响
datetime	8	1000-9999	不受
timestamp	4		受

CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
)

INSERT INTO tab_date VALUES (NOW(),NOW())
查看时区
SHOW VARIABLES LIKE 'time_zone'
更改时区
SET time_zone = '+9:00'

约束

含义: 一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

常见约束：六大约束
NOT NULL ： 非空 ，用于保证该字段的值不能为空
DEFAULT 默认： 用于保证该字段有默认值
PRIMARY KEY : 主键 用于保证该字段的值具有唯一性,并且非空
UNIQUE : 唯一约束，用于保证该字段的值具有唯一性。但是可以为空
CHECK : 检查约束， [mysql 中不支持]
FORIGN KEY ： 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值

约束的添加分类：
列级约束：
六大约束语法上都支持，但是外键约束没有效果
表级约束：
除了非空，和默认其他都支持

案例1：添加列级约束
语法：

直接在字段名 和 类型后面追加 约束类型即可，只支持 默认， 非空， 主键， 唯一

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,
	stuName VARCHAR(20) NOT NULL, # 非空
	gender CHAR(1) CHECK(gender='男' OR gender = '女'), # 检查
	seat INT UNIQUE, # 唯一约束
	age INT DEFAULT 18, # 默认约束
	majorId INT FOREIGN KEY REFERENCES major(id) # 外键
)

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
)

SHOW INDEX FROM stuinfo;

语法：

CONSTRAINT 约束名 约束类型(字段名)

案例2： 添加表记约束

CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	majorid INT,
	CONSTRAINT pk PRIMARY KEY (id), # 主键
	CONSTRAINT uq UNIQUE(seat), # 唯一
	CONSTRAINT ck CHECK(gender = '男' OR gneder = '女'), # 检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 外键
)

通用的写法

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20) NOT NULL,
	sex INT DEFAULT 18,
	seat INT UNIQUE,
	majorId INT,
	CONSTRAINT fk_stu_major FOREIGN KEY(majorId) REFERENCES marjor(id)
)

主键和唯一对比

	保证唯一性	是否为空	一个表中可以有多少个	是否可以组合
主键	√	×	最多有一个	√， 但是不推荐
唯一	√	√	可以有多个	√ ，但是不推荐

外键：

1. 要求在从表设置外键关系
2. 从表的外键列的类型和主键的关联列的类型要求一致或者兼容，名称无要求
3. 主键的关联列必须是一个 key （一般是：主键，唯一）
4. 插入数据时候，先插入主表，再插入从表
5. 删除数据时，先删除从表，在删除主表

方式一： 外键添加级联删除

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY (majorid) REFERENCES major(id) ON DELETE CASCADE;

方式二： 级联置空

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY (majorid) REFERENCES major(id) ON DELETE SET NULL;

修改表时添加约束

语法：
① 添加列级约束

ALTER table 表名 MODIFY COLUMN 字段名 约束类型

②添加表级约束

ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(COLUMN) REFERENCES 关联表(关联表id)

案例1. 添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL; # 添加
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

案例2: 添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

案例2：添加主键

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

案例4: 添加唯一

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

ALTER TABLE stuinfo ADD UNIQUE(seat);

案例5：添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id);

案例6：修改表时删除约束
① 删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

② 删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT;

③ 删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

④删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

⑤删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但是外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其他支持	可以

标识列

含义： 又称为自增长列，可以不用手动的插入值，系统提供默认的序列值

特点：

1. 标识列不一定要和主键搭配使用，但是一定是一个KEY
2. 一个表可以有几个标识列？ 至多一个
3. 标识列的类型只能是数值型
4. 标识列可以通过set auto_increment_increment=3; 设置步长
5. 通过插入值来设置起始值

SHOW VARIABLES LIKE "%auto_increment%";

SET auto_increment_increment = 3; # 设置每次增长

修改表时 设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

修改表时删除标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT;