数据库：常见数据类型和常见约束

• 常见数据类型介绍

#常见的数据类型
/*
数值型:
					整型
					小数:
									定点数
									浮点数
字符型:
					较短的文本:char、varchar
					较长的文本:text、blob(较长的二进制数据)
日期型:
					date,time,year,datetime,timestamp
*/

#一、整型
/*
分类:
tinyint,smallint,mediumint,int/integer,bigint
1  			2				 3			   4	 	liang			8

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

#1.如何设置无符号和有符号

drop table if exists tab_int;
create table tab_int(
				t1 int,
				t2 int unsigned
);

desc tab_int;

insert into tab_int values(-123456);

insert into tab_int values(-123456,-123456);

insert into tab_int values(-123456);
select* from tab_int;

#二、小数
/*
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)

特点:
M:整数部位+小数部位
D:小数部位
① M和D都可以省略，如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度

③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用 	
*/

drop table tab_float;
create table tab_float(
				f1 float,
				f2 double,
				f3 decimal
);
desc tab_float;
insert into tab_float value(123.4523,123.4523,123.4523);
insert into tab_float value(123.456,123.456,123.456);
insert into tab_float value(123.456,123.456,123.456);
insert into tab_float value(123.4,123.4,123.4);
insert into tab_float value(1523.45,1523.45,1523.45);
select * from tab_float;

#原则:
/*
所选择的类型越简单越好，能保存数值的类型越小越好
*/

#三、字符型
/*
较短的文本：
char
varchar

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

较长的文本:
text
blob(较大的二进制)

特点:

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

varchar varchar(M) 最大的字符数，不可以省略		   可变长度的字符		比较节省		 低

*/

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');
select * from tab_char;

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;

#四、日期型

/*
分类:
date只保存日期
time只保存时间
year只保存年

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

特点:			  字节			 范围			  时区等的影响
datetime		8			   1000-9999	  不受
timestamp		4			   1970-2038	  受

*/

create table tab_date(
				t1 datetime,
				t2 timestamp
);

insert into tab_date values(NOW(),NOW());

select * from tab_date;

show variables like 'time_zone';

set time_zone = '+9:00';

• 常见约束

#常见约束
/*
含义:一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类:六大约束
			not null:非空约束,用于保证该字段的值不能为空
			比如姓名、学号等
			default:默认，用于保证该字段有默认值
			比如性别
			primary key:主键，用于保证该字段的值具有唯一性，并且非空
			比如学号、员工编号等
			unique:唯一，用于宝恒该字段的值具有唯一性，可以为空
			比如座位号
			check:检查约束[mysql中不支持]
			比如年龄、性别
			foreign key:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
							在从表添加外键约束，用于引用主表中某列的值
			比如学生表的专业编号、员工表的部门编号，员工表的工种编号
			
添加约束的时机:
					1.创建表时
					2.修改表时

约束的添加分类:
					列级约束：
									六大约束语法上都支持，但外键约束没有效果
					
					表级约束:
									除了非空、默认，其他的都支持
									
主键和唯一的对比:
				 保证唯一性		 是否允许为空         一个表中可以有多少    是否允许组合					主键      可以			  不可以		         至多有一个        允许，但不推荐
		唯一		可以			  可以(不能有多个空)	  可以有多个		   允许，但不推荐
		
外键:
		1、要求在从表设置外键关系
		2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
		3、主表的关联列必须是一个key(一般是主键、唯一)
		4、插入数据时，先插入主表，再插入从表
		删除数据时，先删除从表，再删除主表

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

#一、创建表时添加约束
#1.添加列级约束

/*
语法:
直接在字段命和类型后面追加 约束类型即可

只支持:默认、非空、主键、唯一

*/
create database students;
use students;

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

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

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

#2.添加表级约束
/*
语法:在各个字段的最下面
[constraint 约束名] 约束类型(字段名)

*/
drop table if exists stuinfo;
create table stuinfo(
				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_stuinfo_major foreign key(majorid) REFERENCES major(id)#外键
);

show index from stuinfo;

#通用的写法:
create table if not exists stuinfo(
				id int primary key,
				stuName varchar(20) not null,
				sex char(1),
				age int default 18,
				seat int unique,
				majorId int,
				constraint fk_stuinfo_major foreign key(majorid) references major(id)
)

#二、修改表时添加约束
/*
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 id int primary key;
alter table stuinfo add primary key(id);

#4.添加唯一
alter table stuinfo modify column seat int unique;
alter table stuinfo add column unique(seat);

#5.添加外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

#三、修改表时删除约束

#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;

#标识列
/*
又称为自增长列
含义:可以不用手动的插入值，系统提供默认的序列值

特点:
1、标识列必须和主键搭配吗？ 不一定，但要求是一个key
2、一个表可以有几个标识列？ 至多一个
3、标识列的类型只能是数值型
4、标识列可以通过 set auto_increment_increment=3;设置步长，也可以通过手动插入值设置起始值
*/

#一、创建表时设置标识列
drop table if exists tab_identity;
create table tab_identity(
				id int primary key auto_increment,
				name varchar(20)
);

insert into tab_identity values(null,'John');

insert into tab_identity values(2,'Paul');

insert into tab_identity values(4,'YXZ');

select * from tab_identity;

truncate table tab_identity;

set auto_increment_increment=1;#调整步长

#二、修改表时设置标识列
alter table tab_identity modify column id int primary key auto_increment;

#三、修改表时删除标识列
alter table tab_identity modify column id int;