【Mysql】六、Mysql基础篇 --- 数据类型和约束

一、数据类型

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

数值型

整型、小数（定点型、浮点型）

1. 整型

	DROP TABLE IF EXISTS int_test
	CREATE TABLE IF NOT EXISTS int_test(
		-- 	默认int
		i1 INT ,
		-- 	无符号int
		i2 INT UNSIGNED,
		-- 	最大值为2^53次方最大值
		i3 BIGINT,
		-- 不足补0 ZEROFILL
		i4 INT(5) ZEROFILL
	)

1、无符号报错

	-- 	错误
	INSERT INTO int_test VALUES(-1111, -1111)
	-- 	正确
	INSERT INTO int_test VALUES(-1111, 111)

2、超出INT范围报错

	-- 错误
	INSERT INTO int_test VALUES(1111111111111111111, 111, 1111111111111111111)
	-- 正确
	INSERT INTO int_test VALUES(1, 111, 1111111111111111111)

3、不足补0

	INSERT INTO int_test(i4) VALUES(1)

2. 浮点型、定点型（精度更高）
float(M, D)、double(M, D)、dec(M, D)、decimal(M, D)
M：数字总长度（整数部分+小数点）
D：小数点的精度
案例1：三种类型的特点

-- 创建表
CREATE TABLE f_test (
		f1 FLOAT(5,2),
		f2 DOUBLE(5,2),
		f3 DECIMAL(5,2)
	)

演示插入1：正常插入
INSERT INTO f_test VALUES(123.45,123.45,123.45)

演示插入2：超出小数点位数插入
INSERT INTO f_test VALUES(123.456,123.456,123.456)

演示插入3：少于小数点位数的插入
INSERT INTO f_test VALUES(123.4,123.4,123.4) 长度不足补0

演示插入4：超出总长度插入
INSERT INTO f_test VALUES(11123.4,11123.4,11123.4)

案例2：三种类型的特点

-- 默认
CREATE TABLE t2_test(
	f1 FLOAT,
	f2 DOUBLE,
	f3 DECIMAL
)
DESC t2_test

FLOAT、DOUBLE 默认依据插入值来决定精度
DECIMAL 默认(10, 0)，不可插入小数点，精度最大，但是要设置精度的值

字符型

短文本(char、varchar) 长文本(text、blob)
char、varchar都是字符类型（一个字母、一个汉字都是一个字符）
char：长度固定，长度范围是0～255
varchar：可变长度，长度范围0～65535

ENUM 枚举类型

DROP TABLE IF EXISTS e_tst
CREATE TABLE IF NOT EXISTS e_tst(
 id INT NOT NULL auto_increment,
 e1 ENUM('a','b')
 PRIMARY KEY (id)
)
SELECT * FROM e_tst
-- 只可以插入枚举中的字符
INSERT INTO e_tst SET e1 = 'a'

日期型

DATE、DATETIME、TIMESTAMP

CREATE TABLE d_test(
 -- 日期 2020-1-1 
 date_date DATE,
 -- 时间 19:22:10
 date_time TIME,
 -- 年 2020
 date_year YEAR,
 -- 2020-1-1 19:22:10    8字节 存储日期不改变
 date_datetime DATETIME,
 -- 2020-1-1 19:22:10    4字节  存储是世界标准时间 =》取出数据按照客户端时区转化当前时区时间
 date_timestamp TIMESTAMP
)

二、约束

CREATE TABLE 表名(
   字段名 字段类型 约束
)

六大约束

列级约束：除外键约束外都可
表级约束：除非空、默认都可以设置为表级约束

1. NOT NULL：非空约束

CREATE TABLE users(
	id INT NOT NULL
)

2. DEFAULT：默认约束

CREATE TABLE users(
	name VARCHAR(6) DEFAULT('章三'),
	name VARCHAR(6) DEFAULT '章三'
)

3. PRIMARY KEY：主键约束

-- 列级约束
CREATE TABLE users(
	id INT NOT NULL PRIMARY KEY
)

-- 表级约束
CREATE TABLE users(
	id INT NOT NULL,
	PRIMARY KEY(id)
)

4. FOREIGN KEY：外键约束

-- 部门表
CREATE TABLE depmenmt (
	id INT NOT NULL PRIMARY KEY
)

-- 列级约束
CREATE TABLE users(
	dep_id INT CONSTRAINT dep_id FOREIGN KEY(dep_id) REFERENCES depmenmt(id),	
)

-- 表级约束
CREATE TABLE users(
	dep_id INT,
	CONSTRAINT dep_id FOREIGN KEY(dep_id) REFERENCES depmenmt(id)
)

6. UNIQUE：唯一约束（可以为null）

-- 列级约束
CREATE TABLE users(
	-- 身份证号 唯一约束
	id_number VARCHAR(18) UNIQUE
)

-- 表级约束
CREATE TABLE users(
	-- 身份证号 唯一约束
	id_number VARCHAR(18),
	UNIQUE(id_number)
)

6. CHECK：检查约束（可以为null）mysql不支持

-- 限制性别必须为 男 / 女

-- 列级约束
CREATE TABLE users(
	sex CHAR(1) CHECK(sex='男' OR sex='女')
)

-- 表级约束
CREATE TABLE users(
	sex CHAR(1),
	CHECK(sex='男' OR sex='女')
)

案例1：外键无法找到问题

-- 部门表
CREATE TABLE depmenmt (
	id INT NOT NULL PRIMARY KEY
)

-- 员工表
DROP TABLE IF EXISTS userss
CREATE TABLE IF NOT EXISTS userss(
	id INT NOT NULL auto_increment, 
	dep_id INT,
	name VARCHAR(6) DEFAULT '章三' ,
	PRIMARY KEY(id),
	FOREIGN KEY(dep_id) REFERENCES depmenmt(id)
)
DESC userss
SHOW INDEX FROM userss

执行：INSERT INTO userss(dep_id, name) VALUES(10, 'jjj') 报错

大致意思：无法添加或更新子行：外键约束失败，userss表的dep_id无法找到depmenmt的id

添加约束

添加主键约束：ALTER TABLE users ADD PRIMARY KEY(id)
添加唯一约束（表级约束）：ALTER TABLE users ADD UNIQUE(id_number)
添加唯一约束（列级约束）：ALTER TABLE users ADD COLUMN id_number INT UNIQUE

修改约束

修改主键约束&非空约束：ALTER TABLE users MODIFY COLUMN id INT NOT NULL

删除约束

删除主键约束：ALTER TABLE users DROP PRIMARY KEY
删除唯一约束：ALTER TABLE users DROP INDEX 键名

约束案例总结

-- 将emp2的id设置为主键，分别添加 列级约束和表级索引
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY
ALTER TABLE emp2 ADD PRIMARY KEY id
-- 向emp2添加列dep_id  并且将dep_id设置dep2的外键索引
ALTER TABLE emp2 ADD COLUMN dep_id INT
ALTER TABLE emp2 ADD CONSTRAINT dep_id FOREIGN KEY dep_id REFERENCES dep2(id)