数据库存储引擎/创建表的完整语法/字段类型/字符类型/日期类型/约束条件

存储引擎

定义:不同的数据应该有不同的处理机制

mysql存储引擎

• innodb:默认的存储引擎,查询速度较myisam慢,但是更安全
• myisam:旧版本所用引擎
• memory:内存引擎(数据全部存在内存中)
• blackhole:无论存什么,都立马消失(黑洞)

研究每一个存储引擎存取数据的特点

# 查看存储引擎
show engines;
# 查看不同引擎存储表结构文件特点
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;
​
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

innodb

# innodb包含 
row-level locking  # 行锁
foreign keys  # 外键
Supports transactions  # 支持事务
# 创建两个文件
# 一个FRM文件,是表结构
# 一个IBD文件,一个是真实数据文件

myisam

# 创建三个文件
# 一个FRM文件,是表结构
# 一个IBD文件,一个是真实数据文件
# 一个MYI文件,是索引

注意:memory和blackhole都只创建一个表结构(FRM文件)

---

创建表的完整语法

create table 表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件],
    )
'''
1.字段名和字段类型是必须的,中括号内的参数都是可选参数
2.同一张表中字段名不能重复
3.最后一个字段后后面不能加逗号(否则会报错)
    create table t(
        id int,
        name char
        );
'''

宽度:

• 对存储数据的限制
• char(1)只能存一个字符,如果超了,mysql会自动帮你截取
  • 1.插入时mysql自动截取
  • 2.会直接报错(mysql严格模式)

alter table t modify name char not null;
# not null该字段不能为空

类型和中括号内的约束:

• 类型约束的是数据的存储类型
• 而约束是基于类型之上的额外限制

使用数据库的准则:

• 能尽量让它少干活就尽量少干活,推荐使用mysql严格模式

---

字段类型

整型:

SMALLINT　　TINYINT　　INT　　BIGINT

TINYINT

定义:默认是否有符号,默认是带有符号的(-128,127)

超出限制会如何?

• 超出之后只会存最大值或者最小值

create table t(id TINYINT);
insert into t values(-129),(256);
select * from t;
alter table t modify id TINYINT unsigned;
# not null 不能为空
# unsigned 无正负符号

补充知识点:

• not null　　不能为空
• unsigned　　无正负符号
• zerofill　　用0填充多余的位数

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

特例:

• 只有整型后面的数字不是用来限制存储数据的长度,而是用来控制展示的数据的位数
• int(8),超8位,有几位存几位,不够8位用空格填充

修改约束条件,不够8位时,用0填充

create table t(id int(8) zerofill);
insert into t values(1),(123456789);

强调:

• 对于整型来说,数据类型后的宽度并不是存储限制,而是显示限制,所以在创建表时,如果字段采用的是整型类型,完全无需指定显示宽度,默认的实现宽度,足够显示完整当初存放的数据
• 只要是整型,都不需要指定宽度,因为有默认的宽度,足够显示对应的数据

模糊匹配

like

• %匹配任意多个字符
• _匹配任意一个字符

操作语法:

• set session 临时有效,只在当前操作的窗口有效
• set global 全局有效,终生有效

set global sql_mode = 'STRICT_TRANS_TABLES';

注意:设置完后,你只需要重新退出客户端再次进入即可

浮点型

• float(255,30) 总共255位,小数部分占30位
• double(255,30) 总共255位,小数部分占30位
• decimal(65,30) 总共65位,小数部分占30位

create table t1(id FLOAT(255,30));
create table t2(id DOUBLE(255,30));
create table t3(id DECIMAL(65,30));
​
insert into t1 valuse(1.1...1);  # 小数点后30位
insert into t2 valuse(1.1...1);  # 小数点后30位
insert into t3 valuse(1.1...1);  # 小数点后30位
​
# 精确度
float < double < decimal

精确度

• float < double < decimal

---

字符类型

• char(4)最大只能存四个字符,超出来会直接报错,如果少了则会自动填充空格
• varchar(4)最大只能存四个字符,超出来会直接报错,如果少了则有几个就填鸡哥

create table t1(name char(4));
create table t2(name varchar(4));

注意:mysql在存储char类型字段时,硬盘上确实存的是固定长度的数据,但是再取出来的那一瞬间,mysql会自动将填充的空格去除

可以通过严格模式,来修改该机制,让其不做自动去除处理

char_length()
​
set global
sql_mode="srtict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";  # 核心是替换

char与varchar的区别

char定长:

• 1.浪费空间
• 2.存取速度快

varchar变长:

• 1.节省空间
• 2.存取速度慢(较于char慢)
  • 存的时候,需要给数据讲一个记录长度的报头
  • 取的时候,需要先读取报头才能读取真实数据

char(4):取的时候方便,直接按固定长度取即可

varchar(4):取的时候比较繁琐,无法知道数据到底有多长

---

日期类型

date　　datetime　　year　　time

---

枚举与集合类型

枚举(enum):限制某个字段能够存储的数据内容

集合(set):限制某个字段能够存储的数据内容

---

约束条件

not null:不能为空

default:给某个字段设置默认值(当用户写数据时使用用户的,用户没写时用默认值)

create table t(id int,name char(4) default 'XXX');
# 向表中插入数据时,可以指定字段进行插入,不需要全部插入
insert into t(name,id) values('xxx',2);

unique唯一

单列唯一

• 限制某一个字段是唯一的

联合唯一

• ip　　port

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

primary key主键

• 限制效果与not null + unique组合效果一致,非空且唯一

create table t(id int primary key);
insert into t values(null);

• primary keys也是innodb引擎查询必备的索引(目录)
• innodb引擎在创建表时,必须要有一个主键
  • 当你没有指定主键时
    • 1.会将非空且唯一的字段自动升级成主键
    • 2.当你的表中没有任何的约束条件,innodb会采用自己的内部默认的一个主键字段,该主键字段在你查询时是无法使用的,查询数据的速度就会很慢,类似于一页一页的翻书

create table t(
     id int,
    name char(6),
    age int not null unique,
    addr char(16) not null unique
);

主键字段到底设置给谁的?

• 通常每张表里都应该有一个id字段,并且应该将id设置为表的主键字段

联合主键:

• 多个字段联合起来作为表的一个主键,本质上还是一个主键!
• PS:innodb引擎中一张表有且仅有一个主键

create table t(
     ip char(16),
    port int,
    primary key(ip,port)
    );
    desc t20;

主键字段应该具备自动递增的特点

• 每次添加数据,不需要用户手动输入
• auto_increment自动递增

create table t1(id int primary key auto_increment,name varchar(16));
​
create table t2(id int primary key auto_increment,name varchar(16));

注意:

• delete from仅仅是删除数据,不会重置id
• truncate初始化表,会重置主键