数据库之字段类型

一：数据库基础

【1】存储引擎：不同的数据类型对应不同的处理机制

（1）innodb:默认存储引擎 相比于myisam来说 读取速度较慢

（2）myisam:5.1之前用的版本 对数据的操控安全性不如innodb

（3）memory:临时存储引擎 数据存入内存 断电数据消失

（4）blackhole:黑洞引擎 无论存放什么数据 都会立马消失

# 查看存储引擎
show engines;

create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
create table t4(id int)engine=blackhole;

insert into t1 values(1);
insert into t2 values(2);
insert into t3 values(3);
insert into t4 values(4);

 

图解一:

Innodb:支持事务 行锁 外键 在安全性上比myisam更加高

 

图解二：

t1:

（1）frm:表结构

（2）ibd:真实存放的数据

t2:

frm:表结构

MYD:真实存放的数据

MYI：索引

PS：因为其有索引 所以查找数据速度比较快

t3/t4:

（1）frm：表结构

 

【1】创建表的完整语法结构

（1）语法 create table 表名(字段名 类型（宽度 约束条件）,字段名 类型（宽度 约束条件）);

PS:

（1）字段名 和类型必须要写

（2）宽度值和约束条件可写可不写

（3）字段名之间不能重复

（4）字段名最后一个千万不能加上逗号 

例如：

create table userinfo(id int,name char);

 

（2）约束条件

宽度：

（1）作用：限制数据的存储位数

PS:

（1）当数据超出存储位数 会按照从左到右截断数据

（2）如果在严格模式下 超出数据位数会直接报错

例如：

insert into userinfo(name) values('SR');   # 插入数据
select name from userinfo;  # S

PS:上述插入char宽度默认为1位 所以只能插入一个S

（1）对于整型来说宽度不是用来限制存储的个数 而是用来限制展示出来的个数

（2）如果超过宽度则直接显示出来 如果小于宽度则以0补齐宽度

（3）只要是整型 都不需要指定宽度 因为有默认的宽度（默认宽度为11位 而int最大数据长度为10位） 足够显示对应的数据

 

（2）条件约束：

not null不能为空

create table userinfo(name char(16));

alter table userinfo modify name char not null

insert into userinfo values(null);  # 报错

PS：

（1）字段类型约束的是数据从存储类型

（2）约束条件是在对字段类型之上在进行约束

 

二：字段类型

 （1）整形：TINYINT,SMALLINT, MEDIUMINT ,INT

TINYINT:

符号：默认有符号的

超出范围：会自动将值换成最先值或者最大值

例如：

create table t1(id TINYINT);
insert into t1 values(-129),(128);  # （-128），（127）

PS：如果有符号范围为（-128,127）

 

int

符号：默认有符号的

超出范围：会自动将值换成最先值或者最大值

例如：

create table t2(id int);
insert into t2 values(-2222222222222222222),(222222222222222222222);  # （-2147483648,2147483647）

PS:SMALLINT, MEDIUMINT 与上述情况一样

 

unsigned ：

作用：将上述整形由默认带符号变成无符号

符号：无符号

范围：如果超出范围最小值为0 最大值为255

例如：

create table t4(id TINYINT unsigned);
insert into t4 values(-129),(256);  # （0,255）

 

 （2）浮点型：

　（1）float(255,30) 总共255位 小数部分占30位
　（2）double(255,30) 总共255位 小数部分占30位
　（3）decimal(65,30) 总共65位 小数部分占30位

例如：

create table t9(x float(255,30));
create table t10(x double(255,30));
create table t11(x decimal(65,30));

insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);

PS：精准度 float < double < decimal

 

（3）字符类型（char）：

char:后面的数字是限制存储的数据长度的

 例如：

create table t5(name char);
insert into t5 values('SR');  # S

PS：上述存放5位数据 但是由于char默认只能存一位 所以最终在数据库中存入S

严格模式：

产生背景：

（1）对于上述例子中 虽然位数不够 其会帮我们截断进行存入数据库

（2）但是截断需要消耗数据库的操作 增大数据的压力

（3）而且截断的存入的数据 本身也不是我们所需要的数据

作用：

（1）可以通过设置严格模式 如果不符合我设置的规则 则会报错

# 查看严格模式
show variable like '%mode%'

# 模糊匹配
like
 % 匹配任意多个字符
 _ 匹配一个字符

set session  临时有效  只在你当前操作的窗口有效
set global   全局有效  终生有效
# 修改模式为严格模式
set global sql_mode = 'STRICT_TRANS_TABLES';

PS:上述模式修改之后 一定要重新打开窗口

create table t6(name char);
insert into t6 values('SR');   # 报错

 

char(宽度):宽度限制存放位数 如果超过则直接报错 如果不够自动用空格填充

varchar（宽度）:宽度限制存放位数 如果超过则直接报错 有几个存几个

create table t8(name char(10));
insert into t8 values('mysql');

create table t9(name varchar(10));
insert into t9 values('mysql');

# 查看某字段的长度
select char_length(name) from t8
select char_length(name) from t9

PS:

（1）在查看上述数据的时候 在取出来的时候sql会自动将空格给去除

（2）通过严格机制 修改不让取出来的时候 空格被去除

set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";

PS:一定在以前修改的尾部追加 如果不追加则会将原来的覆盖

char:

（1）浪费存储空间 因为不够加上空格符

（2）存取速度快

 

varchar：

（1）节省内存空间

（2）存储速度慢

　　（1）存储时候：添加一个固定包头

　　（2）读取时候：读取固定长度包头 由包头告诉真实数据

 

（4）时间类型：

date:年月日

datetime：年月日时分秒

time:时分秒

year：年

例如：

create table student(
    id int,
  name char(16),
  born_year year,
  birth date,
  study_time time,
  reg_time datetime
);
insert into student values(1,'SR','2019','2019-05-09','11:11:00','2019-11-11 11:11:11');

 

（5）枚举：

作用：限制某个字段存放的数据

PS：多选一

例如：

create table user(
    id int,
  name char(16),
  gender enum('male','female','others')
);
insert into user values(1,'jason','xxx')  # 报错
insert into user values(2,'egon','female')  # 正确！

集合：

作用：限制某个字段存放的数据

PS：多选多

例如：

create table teacher(
    id int,
  name char(16),
  gender enum('male','female','others'),
  hobby set('read','sleep','sanna','dbj')
);
insert into teacher values(1,'egon','male','read,sleep,dbj')  # 集合也可以只存一个

 

（6）约束条件：

not null + default

例如：

create table user(
    id int,
  name char(16)
);
insert into user values(1,null)  # 可以修改

alter table user modify name char(16) not null;
insert into user(name,id) values(null,2);  # 报错 插入数据可以在表名后面指定插入数据对应的字段

create table student(
    id int,
  name char(16) not null,
  gender enum('male','female','others') default 'male'
)
insert into student(id,name) values(1,'jason')  # 成功

 

（7）unique：

作用：限制某个字段是唯一的 不能重复

（1）单列唯一：

例如：

# 单列唯一
create table user1(
    id int unique, 
  name char(16)
);
insert into user1 values(1,'jason'),(1,'egon')  # 报错
insert into user1 values(1,'jason'),(2,'egon')  # 成功

# 联合唯一
create table server(
    id int,
  ip char(16),
  port int,
  unique(ip,port)
)
insert into server values(1,'127.0.0.1',8080);
insert into server values(2,'127.0.0.1',8080);  # 报错
insert into server values(1,'127.0.0.1',8081);

PS：联合唯一 一定要在字段的末尾

 

（8）primary key:

作用：

（1）从约束条件看 其等于  not all + unique

例如：

create table t11(id int primary key);insert into t11 values(1),(1);  # 报错
insert into t11 values(1),(2);

（2）其还是innodb搜索的索引

PS：

（1）innodb在创建表的时候 必须要有一个主键

（2）当没有创建主键的时候 其会按照代码从上到下搜索将非空且唯一的字段设置成unique

（3）当上述条件都不满足 innodb会采用自己内部的一个主键字段充当主键 （该主键在查询的时候无法使用 导致innodb无法通过索引取值）

例如：

create table t12(
    id int,
  name char(16),
  age int not null unique,
  addr char(16) not null unique
)engine=innodb;

 

（7）auto_increment:

作用：主键编号自动递增

例如：

create table t13(
    id int primary key auto_increment,
  name char(16)
);
insert into t13('jason'),('jason'),('jason');

PS：我们一般设置ID为我们的主键 因为ID一般用来标识一个数据的编号

 

delete from tb1;

作用：其可以将表中的某些数据删除 但是不会将主键的ID给重置为0

 

truncate:

作用：其会清空所有的数据 主键ID也会被重置为0

 

转载于:https://www.cnblogs.com/SR-Program/p/11379386.html