MySQL中的数据类型以及完整性约束

时间:2022-09-21 12:07:06

数据类型

  数据库mysql中也是分很多数据类型的,最常用的就是数字类型、字符类型、日期类型、枚举与集合类型

一、数字类型

  默认都是有符号的,即正负号,若想无符号,在创建表时加unsigned。指定宽度时,只影响显示宽度不影响存储,即只决定读不决定写。

注:其实我们完全没必要为整数类型指定显示宽度,使用默认的就可以了

  最常用的是int,常用的一般是tinyint和int

  tinyint:1个字节,有符号时可存(-128~127),无符号时可存(0~255),超过范围的数存成最大范围的那个数

  smallint:2个字节,有符号时可存(-2^15~2^15 -1),无符号时可存(0~2^16 -1)

  mediumint:3个字节,有符号时可存(-2^23~2^23 -1),无符号时可存(0~2^24 -1)

  int:4个字节,有符号是可存(-2^31~2^31 -1),无符号是可存(0~2^32 -1)

  bigint:8个字节,有符号时可存(-2^63~2^63 -1),无符号时可存(0~2^64 -1)

  另外还有浮点型:float、double和decimal

  float和double不精准,超过精准范围的小数会被削为0,但在日常情况下已足够使用。decimal是精准浮点数,但比较消耗内存,所以在一般情况下建议使用float。格式为float(x,y),其中的x代表数字总个数不算符号,y代表保留的小数点位数

  bit类型:用来存放二进制数,范围为1~64,不指定则默认1位

create table t1(id bit);#bit后可加括号指定范围,不指定默认为1
insert into t1 values(8);#在表中添加一个8
select bin(id),hex(id),oct(id) from t1;#查看时需要转换一下进制,需要什么进制呈现就用相应的函数

 

二、字符类型:宽度约束的是字符的个数

  char:定长,不够宽度时用空格补齐。字符长度范围:0~255

    特点:存取效率快,但浪费空间。存储时会在右侧填充空格以满足指定长度,但在查询时有自动删除空格。

  varchar:变长,在指定宽度内存什么就是什么。字符长度范围:0~65535

    特点:精准,节省空间但存储效率较慢。

注:虽然varchar更精准,但一般都是用char多一点,毕竟在精准度要求并没有特别高的情况下效率比较重要

#查看字符的长度
select x,char_length(x) from t1;

#查看字节的长度(utf-8中一个中文字符占三个字节)
select x,length(x) from t1;

#以上方式查看时,char会自动删除空格,需在查询前指定
set sql_mode='pad_char_to_full_length';
#才可以将char类型的数据完整的查看


#举例:
create table t1(name1 char(10),name2 varchar(5));
insert into t1 values('ladygaga','egon');
select name1,char_length(name1),nam2,char_length(name2) from t1;
select name1,length(name1),nam2,length(name2) from t1;
#查询结果为8,4
#指定set sql_mode='pad_char_to_full_length';后查询结果为10,4

 

三、时间类型:根据不同的需求选择不同的时间类型

  datetime:yyyy-mm-dd hh:mm:ss  范围为1000-01-01 00:00:00~9999-12-31 23:59:59

  year:yyyy  范围为1901~2155,无论指定何种宽度,最后都是4

  date:yyyy-mm-dd  范围为1000-01-01~9999-12-31

  time:hh:mm:ss  

  单独插入时间时需要以字符的形式,按照对应格式。插入年份时尽量使用4位值,如果用两位的话需要知道:当输入的2位<=69时,显示为20xx;当输入的的2位>=70时,显示为19xx。

举例:

create table t2(id int,born_date date,born_year year,reg_time datetime,class_time time);
insert into t2 values(1,'alex',now(),now(),now(),now());#now()函数自动插入当前时间
insert into t2 values(2,'egon','2017-09-11','2017','2017-09-11 17:17:17','17:17:17');#标准形式
insert into t2 values(3,'eva','20170911','2017','20170911171717','171717');#这种形式也支持

 

 四、枚举与集合: 字段只能在给定的范围内选择。枚举enum为单选,集合set为多选,可设定默认值         

 举例说明

create table t3 (id int primary key auto_increment,#自增id
name=char(5),
sex enum('male','female'),#枚举,二选一
hobbies set('eat','sleep','run','sing')#集合,多选
);
insert into t3 (name,sex,hobbies) values('egon','male','eat,sleep');

 

 


 

 

完整性约束

  not null 和null:不为空和可为空,创建时指定,不指定时默认为可空

  default:设置默认值的关键字,用来设置不传值时的默认值。若字段设置了默认值,插入空时填入的就是默认值

  unique:标识该字段是唯一的

#方法一:
create table t4(name char(10) unique);

#方法二:
create table t5(name char(10),constraint uk_name unique(name));

#联合唯一:表示多个字段联合唯一,只要有一个字段不同即可插入,只有在所有字段都相同是才不可插入
create t6(id int,name char(10),host char(20) not null,port int not null,unique(host,port));
#将host和port联合唯一,若host和port都一样则不准插入
insert into t6(1,'mao',192.168.0.0',8080);
insert into t6(2,'gou',192.168.0.1',8080);
insert into t6(3,'zhu',192.168.0.1',8081);#都可插入
insert into t6(4,'ji',192.168.0.1',8081);#不可插入

 

primary key:主键,不为空且唯一 

#方法一:
create table t7(id int not null unique);#遵循不为空且唯一,但不是主键的正确表达式
#方法二:
create table t7(id int primary key);
#方法三:
create table t7(id int,constraint pk_name primary key(id));

 

auto_increment:自增,被约束字段必须同时被key约束。对于自增的字段,delete删除后再插入值,该字段扔按照删除前的位置继续增长

  auto_increment_offset:偏移量,默认值为1

  auto_increment_increment:步长,默认值为1

  全局范围内设置步长set global auto_increment_increment=2;

  当前会话范围内设置步长set session auto_increment_increment=2;

  同时设置偏移量和步长的情况下,若偏移量的值>步长的值,则偏移量的值就会被忽略,即失效,即偏移量=1

 

foreign key:外键,用于表的关联(重点!查询语句中最难点)

  当有多张表,表1的多条记录可以对应到表2的一条记录时,就可以将其关联起来。此时必须保证表2的该记录是唯一的。

  可以一对一关联,也可以多对一关联,多对多关联

 

 

 

举例:将下面的表进行相应的关联

要求:学生表关联班级表,课程表关联老师表,成绩表关联所有表

注:先建立父表,即要被关联的表,再建立子表并建立外键关联父表,同步更新on update cascade,同步删除on delete cascade。插入记录时先操作父表,再操作字表。

 MySQL中的数据类型以及完整性约束

MySQL中的数据类型以及完整性约束MySQL中的数据类型以及完整性约束