常见数据类型
数值型:
整形
小数:
定点数
浮点数
字符型
较短的文本:char、varchar
较长的文本:text、blob (较长的二进制数据)
日期型:
一、整形
整数类型 | 字节 | 范围 |
---|---|---|
Tinyint | 1 | 有符号:-128~127 无符号:0~255 |
Smallint | 2 | 有符号:-32768~32767 无符号:0~65535 |
Mediumint | 3 | 有符号:-8388608~8388607 无符号:0~1677215 (好吧,反正很大,不用记住) |
Int、integer | 4 | 有符号:- 2147483648~2147483647 无符号:0~4294967295 (好吧,反正很大,不用记住) |
Bigint | 8 | 有符号: -9223372036854775808~9223372036854775807 无符号:0~9223372036854775807*2+1 (好吧,反正很大,不用记住) |
分类:
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
二、小数
浮点数数类型 | 字节 | 范围 |
---|---|---|
float | 4 | ±1.75494351E-38~±3.402823466E+38 (好吧,反正很大,不用记住) |
double | 8 | ±2.2250738585072014E-308~ ±1.7976931348623157E+308 (好吧,反正很大,不用记住) |
定点数类型 | 字节 | 范围 |
DEC(M,D) DECIMAL(M,D) | M+2 | 最大取值范围与double相同,给定decimal的有效取值范围由M和D决定 |
分类:
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
字符串类型 | 最多字符数 | 描述及存储需求 |
---|---|---|
char(M) | M | M为0~255之间的整数 |
varchar(M) | M | M为0~65535之间的整数 |
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只能选一个
根据成员个数不同,存储所占的字节也不同
成员数 | 字节数 |
---|---|
1~8 | 1 |
9~16 | 2 |
17~24 | 3 |
25~32 | 4 |
33~64 | 8 |
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');
# 不区分大小写
四、日期型
日期和时间类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
date | 4 | 1000-01-01 | 9999-12-31 |
datetime | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
timestamp | 4 | 19700101080001 | 2038年的某个时刻 |
time | 3 | -838:59:59 | 838:59:59 |
year | 1 | 1901 | 2155 |
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;
练习
- 向表 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);
- 向表 dept2 的 id 列中添加 PRIMARY KEY 约束(my_dept_id_pk)
alter table dept2 add primary key(id);
- 向表 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);
位置 支持数据类型 是否可以起约束名
列级约束: 列的后面 语法都支持,但外键没有 不可以
表级约束: 所有列的下面 默认和非空不支持,其他支持 可以(但没有效果)