[大数据]MySQL的DDL语言：2、常见的数据类型和约束

常见数据类型

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

一、整形

分类：
tinyint、smallint、mediumint、int / integer、bigint
	1		2			3			4			8

特点：
	1、如果不设置无符号还是有符号，默认为有符号，若想设置有符号，则需要追加unsigned关键字
	2、如果插入的数值超出了整形的范围，会报out of range 异常，并且插入临界值
	3、如果不设置长度，会有默认的长度
		长度代表了显示的最大宽度，如果不够会用0来填充，但必须搭配 zerofill 来使用

#如何设置无符号和有符号
create table tab_int(
	t1 int #创建一个表，设置一个 int 类型的字段
);
insert into tab_int values(-123456);#若为无符号，则不能插入负数，若有符号，则可以插入负数
#此时支持负数，为有符号，即默认为有符号数

#那如何设置有符号？
create table tab_int(
	t1 int, # 默认为有符号
	t2 int unsigned # 在类型后添加一个 unsigned 关键字，即可设置无符号数
);
insert into tab_int values(-123456,-123456); #此时报错

insert into tab_int values(2147483648,4294967296);#此时这两个值均超出 int 类型的范围，报异常，但是插入的值为临界值

#零填充 zerofill
create table tab_int(
	t1 int(7) zerofill,#在插入的整形没有满足足够的位数时，前面由0来填充
	t2 int(7) zerofill unsigned 
);
insert into tab_int values(123,123);#0000123,0000123

二、小数

分类：
	1、浮点型
		float(M,D)
		double(M,D)
	2、定点型
		dec(M,D)
		decimal(M,D)

特点：
	1、M ：整数部位 + 小数部位
		D：小数部位
		如果超过范围，则插入临界值
	2、M 和 D 都可以省略
		如果是decimal,则M默认为10，D默认0，
		如果是float和double，则会根据插入的数值的精度来决定精度
	3、定点型的精度较高，如果要求插入的数值的精度较高如货币运算，则考虑使用定点型

# 测试 M 和 D
create table tab_float(
	f1 float(5,2),
	f2 double(5,2),
	f3 decimal(5,2)
);
insert into tab_float values(123.45,123.45,123.45);

insert into tab_float values(123.456,123.456,123.456);#此时f3报错，插入的数据全部变成123.46(四舍五入)，故D为小数点后的位数

insert into tab_float values(1234.456,1234.456,1234.456);#此时插入的数值全部变成999.99，故M代表的是整数部分和小数部分的总位数

#M 和 D 都可以省略
create table tab_float(
	f1 float,
	f2 double,
	f3 decimal
);
insert into tab_float values(123.45,123.45,123.45);
insert into tab_float values(123.456,123.456,123.456);
insert into tab_float values(1234.456,1234.456,1234.456);
# 此时只有f3报错，decimal 的M 和 D默认为(10,0),是没有小数的，而float和double没有默认条件，只要满足位数范围即可

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

三、字符型

较短的文本：
	char
	varchar
较长的文本：
	text
	blob(较大的二进制)

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

其他
	binary 和 varbinary 用于保存较短的二进制
	enum 用于保存枚举
	set用于保存集合

char 和 varchar

binary和varbinary类型
说明：类似于char和varchar，不同的是它们包含二进制字符串而不包含非二进制字符串。

Enum类型
说明:又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。
如果列表成员为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('m');
insert into tab_char values('A');
#插入后a，b，c和A的值都能被插入，但是m不能，体现了枚举，只能插入a,b,c，不区分大小写

Set类型
说明：和Enum类型类似，里面可以保存0~64个成员。和Enum类型最大的区
别是：SET类型一次可以选取多个成员，而Enum只能选一个
根据成员个数不同，存储所占的字节也不同

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,b,c');
# 不区分大小写

四、日期型

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

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不支持】，添加限制，如年龄、性别等
	foreign key : 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
				【从表添加外键约束，引用主表的某字段的值，两列的类型一致】，如专业编号、部门编号、工种编号

添加约束的时机：
		1、创建表时
		2、修改表时（在添加元素之前）
	
约束的添加分类：
		列级约束：六大约束语法上都支持，但外键约束没有效果。
		表级约束：除了非空和默认其他都支持。

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

create table 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束
)

创建表时添加约束

1、添加列级约束

create database students;

use students;

create table syuinfo(
	id int primary key, #主键
	stuName varchar(20) not null, #非空
	gender char(1) check(gender='男'or gender='女'), #检查
	seat int unique,#唯一
	age int default #默认
	majorId int references major(id) #外键，引用major表中的id，foreign key
);


create table table major(
	id int primary key,
	majorName varchar(20)
);

desc stuinfo;
show index from stuinfo;#查看stuinfo表中所有的的索引，包括主键、外键、唯一

2、添加表级约束

语法：在各个字段的最下面
[constraint 约束名] 约束类型(字段名);	#[其中constrain 约束名] 可以省略，有默认名



create table syuinfo(
	id int,
	stuName varchar(20),
	gender char(1),
	seat int,
	age int,
	majorId int,
	/*
	constraint pk primary key(id),#为id添加主键
	constraint uq unique(seat), #为seat添加非空约束
	constraint ck check(gender='男' or gender='女')#为gender添加check约束
	constraint fk_stuinfo_major foreign key(majorid) references major(id) #外键
	*/
	primary key(id),#为id添加主键
	unique(seat), #为seat添加非空约束
	check(gender='男' or gender='女')#为gender添加check约束
	foreign key(majorid) references major(id) #外键
)
show index from stuinfo


通用写法:将外键连接添加在最后面，其他在前面。
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 mojor(id)
)

关于主键和唯一的对比

		保证唯一性	是否允许为空		一个表中可以有多少个	是否可以组合
主键			√			×				主键至多有一个		√（不推荐）
非空			√			√				唯一可以有多个		√（不推荐）

insert into major values(1,'java');
insert into major values(2,'h5');
insert into stuinfo values(1,'john','男',null,19,1);
insert into stuinfo values(1,'java','lily',null,19,2);
#唯一可以为空，但是只能出现一个为空的

#可以将多个字段组合成一个字段
primary key(id,stuname);#此时两个当中可以出现一个重复，但不能两个都重复。
#组合唯一键也一样，如上所述。

外键的特点

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

create table table major(
	#id int, #报错，关联列必须是个key
	id int primary key,
	majorName varchar(20)
);


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 mojor(id)
)

列的约束可以添加多个，使用空格隔开，没有顺序要求

create table if not exists stuinfo(
	id int primary key,
	stuName varchar(20) not null unique
)

修改表时修改约束

1、添加约束

1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 约束类型(字段名) 【外键引用】
# 创建没有约束的表
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 mojor(id)
);
1、添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
2、添加默认约束
alter table stuinfo modify column age int default 19;
3、添加主键
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 foreign key(majorid) references major(id);#只支持表级约束

2、删除约束

# 删除非空约束
alter table stuinfo modify column stuname varchar(20) null;
# 删除默认约束
alter table stuinfo modify column age int;
# 删除主键
alter table stuinfo modify column id int;
alter table stuinfo drop primary key; 
# 删除唯一键
alter table stuinfo drop index seat;
# 删除外键约束
alter table stuinfo drop foreign key fk_stuinfo_major;

练习

1. 向表 emp2 的 id 列中添加 PRIMARY KEY 约束（my_emp_id_pk）

alter table emp2 modify column id int primary key;
alter table emp2 add constraint my_emp_id_pk primary key(id);

2. 向表 dept2 的 id 列中添加 PRIMARY KEY 约束（my_dept_id_pk）

alter table dept2 add primary key(id);

3. 向表 emp2 中添加列 dept_id，并在其中定义 FOREIGN KEY 约束，与之相关联的列是dept2 表中的 id 列。

alter table emp2 add column dept_id int;
alter table emp2 add constraint fk_emp2_dept2 foreign key(dept_id) references dept2(id);

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