Mysql学习笔记(9):数据类型和约束

1 数据类型

1.1 分类

数值型：整型、小数(定点数、浮点数)
字符型：短文本(char、varchar)、长文本(text、blob(较长的二进制数据))

1.2 整型

整数类型	字节
Tinyint	1
Smallint	2
Mediumint	3
int、integer	4
Bigint	8

特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号

CREATE TABLE test(
	t1 INT,
	t2 INT UNSIGNED
);

②如果超出了范围，会报out or range异常，插入临界值
③长度可以不指定，默认会有一个长度(该长度为数字的位数)。长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) ZEROFILL 

);

1.3 浮点型和定点数

浮点型	字节
float(M,D)	4
double(M,D)	8

定点型	字节
DEC(M,D)	M+2
DECIMAL(M,D)	M+2

特点：
①
M：整数部位+小数部位的总长度
D：小数部位的长度(即保留几位小数)
②
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
③
定点型精确度较高，若进行货币运算等精度较高的数值时，考虑使用这种类型

1.4 字符型

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

binary和varbinary与上面类似，但它们包含二进制字符串而不包含非二进制字符串

1.5 枚举类型

列表成员1-255，需要1个字节存储
列表成员255-65535，需要2个字节存储（最多65535个成员）

CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);

INSERT INTO tab_char VALUES('a')
INSERT INTO tab_char VALUES('b')
INSERT INTO tab_char VALUES('c')
INSERT INTO tab_char VALUES('d')
INSERT INTO tab_char VALUES('A')

SELECT * FROM tab_char

Set类型（集合）与Enum类型类似，可以保存0-64个成员，Set类型可以一次选取多个成员

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

INSERT INTO tab_set VALUES('a')
INSERT INTO tab_set VALUES('A,B')
INSERT INTO tab_set VALUES('a,c,d')
SELECT * FROM tab_set

1.6 日期型

1、Timestamp支持的时间范围较小，取值范围：19700101080001——2038年的某个时间
Datetime的取值范围：1000-1-1 ——9999—12-31
2、timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时的当地时区
3、timestamp的属性受Mysql版本和SQLMode的影响很大

1.7 类型选择的原则

原则：选择的类型越简单越好，能保存的数值类型越小越好

2 约束

2.1 含义

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

2.2 六大约束

NOT NULL：非空，用于保证该字段的值不能为空（比如姓名、学号等）
DEFAULT:默认，用于保证该字段有默认值（比如性别）
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空（比如学号、员工编号等）
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空（比如座位号）
CHECK:检查约束【mysql中不支持】（比如年龄、性别）
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值（比如学生表的专业编号，员工表的部门编号，员工表的工种编号）

2.3 添加约束的时机

创建表时，修改表时

2.4 分类

列级约束：六大约束都可，但外键约束没有效果
表级约束：除了非空、默认，其他都支持

2.5 使用

创建列级约束

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 19, # 默认
	majorID INT REFERENCES major(id) # 外键
);

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

DESC stuinfo;
#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

创建表级约束

CREATE TABLE stuinfo2(
	id INT,
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorID INT,

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

通用写法

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

2.6 主键和唯一的对比

1、区别：
①、一个表至多有一个主键，但可以有多个唯一
②、主键不允许为空，唯一可以为空
2、相同点
都具有唯一性
都支持组合键，但不推荐

2.7 外键特点

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

2.8 修改表添加约束

1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;

#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

#3.添加主键
#列级约束
ALTER TABLE stuinfo MODIFY COLUMN majorID INT PRIMARY KEY;
#表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(majorID);

#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(majorID) REFERENCES major(id)

2.9 修改表删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

2.10 标识列

定义：可以不用手动的插入值，系统提供默认的序列值。又称为自增长列
特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值，设置起始值

#1.创建标识列
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name_n VARCHAR(20)
);
INSERT INTO tab_identity VALUES(NULL,'Jhon');
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';
#设置步长
SET auto_increment_increment=3;

#2.修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

#3.修改表示删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;

参考资料：
《尚硅谷MySQL数据库教程》