MySQL之存储引擎，数据类型，约束条件

---恢复内容开始---

 

存储引擎：

不同的数据应该有不同的存储机制

Innodb：mysql 5.5含5.5版本以上使用，现在我们使用的默认都是innodb，其特点是支持事务，支持行锁，支持外键，但不支持全文索引，所以说优点是：存储数据安全性高，缺点查询速度慢

myisam:5.5版本一下使用的老版本引擎，支持全文索引，不支持事务，所以优点：查询速度快，缺点：安全性低

memory:内存引擎，是为暂时性存储数据的引擎，因为数据全部是存在内存中的，所以一旦mysql服务端重新启动，会先将内存中的数据清空。

blackhole:黑洞引擎，存什么都立马消失。

四个存储引擎存储数据创建的文件：

innodb:后缀为.frm的为表的结构，.ibd为表的数据

myisam:后缀为.frm为表的结构，.MYD为表的数据，.MYI为索引

memory:后缀为.frm为表的结构

blackhole:后缀为.frm为表的结构。

创建表的完整语法：
create table 表名(字段名1，类型[(宽度），（约束条件）]，

字段名2，类型[(宽度），（约束条件）]，

字段名3，类型[(宽度），（约束条件）]）；

注意：

在同一张表中，字段名不能重复，宽度和约束条件不是必须的，但字段名和类型则是必须。最后一个字段后不能加逗号。

 

 宽度指的是对存储数据长度的控制，但整型的宽度不是对存储的控制，而是对显示位数的控制。

基本数据类型:

整型：

分类：TINYINT SMALLINT MEDIUMINT INT BIGINT

TINYINT ：

　　默认是带有符号的(-128,127),超出限制只会存储最大值或最小值。

再次强调：对于整型来说，数据类型后面跟着的宽度并不是控制存储的，而是控制显示位数的，所以在建表的时候，如果字段是整型，那么无需指定显示宽度，因为会有一个默认值，足够完整显示当初存放的数据。

浮点型：
float(255,30):总共255位，小数占30位

double（255，30）：总共255位，小数占30位

decimal（65,30）：总共65位，小数占30位

 区别：精确度不同

float<double<decimal

字符类型:

char(定长）：

char（4）：最多只能存4个，如果存的多了，不是安全模式下，会只存储允许的最大字符，导致数据不完整，安全模式下报错。如果存的少了会填充空格，就是固定了长度。

varchar（变长）：

varchar（4）：

最多只能存4个，如果存的多了，不是安全模式下，会只存储允许的最大字符，导致数据不完整，安全模式下报错。如果存的少了,存多少那么就还是会存多少。

安全模式设置：

show variables like "%mode%"；匹配数据库配置变量名中包含mode的配置参数。

like通配符：

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

set session 临时模式，只在当前窗口有效

set global sql_mode = 'STRICT_TRANS_TABLES'

修改完后退出客户端重新进入就可以了，就是安全模式。

char和varchar的不同之处：

char是固定长度存储，所以取的时候按照固定长度存取就可以了，所以存取速度快，但是浪费空间，因为不满会以空格填充，varchar在存储的时候会带一个数据长度的报头，在取的时候按这个报头的长度取好了，所以存取速度慢，但节省资源，现在通常是varchar，因为能减少数据库的压力尽量减少。

reate table t10(name char(4))  # 超出四个字符报错，不够四个字符空格补全
create table t11(name varchar(4))  # 超出四个字符报错，不够四个有几个就存几个

# 验证存储限制
insert into t12 values('hello');
insert into t13 values('hello');
# 验证存储长度
insert into t12 values('a'); #'a    '
insert into t13 values('a'); #'a'
select * from t12
select * from t13  # 无法查看真正的结果

select char_length(name) from t12
select char_length(name) from t13  # 仍然无法查看到真正的结果

"""首先应该肯定的是在硬盘上存的绝对是真正的数据，但显示的时候mysql会自动将末尾的空格取掉"""
# 如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
# 退出客户端重新登陆
select char_length(x) from t12; #4
select char_length(y) from t13; #1

# 针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格

时间类型：

 date：年月日

datetime：年月日，时分秒

year：年

time：时分秒

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

枚举 和集合：
枚举：多选一enum

集合：多选多。

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')  # 正确！


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')  # 集合也可以只存一个

约束条件：

primary key 主键，要唯一的标识记录

foreign key 外键

not null  表示该字段不能为空

unique key 表示该字段的值时唯一

auto_increment 表示该字段的值是自动增长的

default 为字段设置默认值。

unique唯一

单列唯一，表示该字段的值是唯一的

联合唯一：单个的可以重复，但2个或多个加在一起的就不能重复。

联合唯一要写在语句的最后，

# 单列唯一
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);

 

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')  # 成功

primary key  主键

限制效果等于not null + unique 组合效果一致，非空且唯一。

create table t18(id int primary key);

primary key 也是innodb引擎查询必备的索引。索引就是加快查询的速度。

innodb创建表必须要有一个主键，如果没有设置会怎么样呢？

1.会将非空且唯一的字段升级为主键。

2.当表中没有任何约束条件的时候，会用内置的，但这个内置的在查询数据上不会有任何帮助，查询数据的速度就会慢。

 那么主键一般给谁呢？

通常每张表中会有一个id字段，并且将id字段设置为主键字段。

联合主键，是将多个字段联合起来作为表的主键，但本质上只有一个主键

 

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

而主键作为数据的编号，应该最好具有自增的功能：

create table t13(
    id int primary key auto_increment,
  name char(16)
);
insert into t13('jason'),('jason'),('jason');  # id字段自动从1开始递增
# 注意：auto_increment通常都是加在主键上，并且只能给设置为key的字段加

补充：

delete from 表名;删除表中所有数据，但再次输入id依然是延续上一次的id

truncate 表名：初始化表，id也重置。

 

 

 

 

 

 

束---