03 MySQL数据库（存储引擎+基本数据类型）
目录
03 MySQL数据库（存储引擎+基本数据类型）
一、存储引擎
存储引擎简介
# 存储引擎可以看成是处理数据的不同方式
# 查看存储引擎的方式
show engines;
四个存储引擎(重要)
MyISAM
MySQL5.5之前默认的存储引擎
不支持事务、行级锁和外键 针对数据的操作较于InnoDB不够安全
但是数据的存取速度较于InnoDB更快
InnoDB
MySQL5.5之后默认的存储引擎
支持事务、行级锁和外键 针对数据的操作更加的安全
memory
基于内存存取数据
速度最快但是断电立刻丢失
blackhole
写入其中的数据都会立刻消失 类似于垃圾处理站
存储引擎创建表的不同点
create table t1(id int) engine=myisam;
create table t2(id int) engine=innodb;
create table t3(id int) engine=memory;
create table t4(id int) engine=blackhole;
"""
MyISAM会创建三个文件
.frm 表结构文件
.MYD 表数据文件
.MYI 表索引文件(索引是用来加快数据查询的)
InnoDB会创建两个文件
.frm 表结构文件
.ibd 表数据和表索引文件
memory
.frm 表结构文件
blackhole
.frm 表结构文件
"""
二、MySQL基本数据类型
1.整型（int bigint 使用频率最高）
tinyint smallint int bigint
不同的int类型能够存储的数字范围是不一样的
1.要注意是否存负数(正负号需要占一个比特位)
2.针对手机号码只能用bigint
'''研究默认是否需要正负号'''
create table t5(id tinyint);
insert into t5 values(-999),(999);
# 结论:所有的int类型默认都需要正负号
create table t6(id tinyint unsigned); # 移除正负号
insert into t6 values(-999),(999);
整型中括号内数字的作用
create table t13(id int(3));
insert into t13 values(4444444);
"""
在整型中括号内的数字并不是用来限制存储的长度 而是用来控制展示的长度
我们以后在定义整型字段的时候 不需要自己添加数字 使用默认的就可以
"""
create table t14(id int(3) zerofill);
insert into t13 values(4);
# 结论:整型比较的特殊 是唯一个不是用来限制存储长度的类型
2.浮点型（float频率最高）
float double decimal
float(255,30) # 总共255位 小数位占30位
double(255,30) # 总共255位 小数位占30位
decimal(65,30) # 总共65位 小数位占30位
"""研究三者的不同"""
create table t7(id float(255,30));
create table t8(id double(255,30));
create table t9(id decimal(65,30));
insert into t7 values(1.11111111111111111111111);
insert into t8 values(1.11111111111111111111111);
insert into t9 values(1.11111111111111111111111);
# 结论:三者的精确度不一样
float < double < decimal
"""
到底使用哪个
一般情况下小数点后面只保留两位 所以float足矣
如果是从事高精密业务 则需要考虑更高的精确度
ps:有时候很多看似需要用数字存储的数据 可能都是存的字符串
课外扩展:python本身对数字的精确度很低 之所以能够从事人工智能和数据分析完全得益于功能强大的模块
"""
3.字符类型(char+varchar)
char(4)
定长类型 最多只能存四个字符 多了报错少了自动空格填充至四个
varchar(4)
变长类型 最多只能存四个字符 多了报错少了有几个则存几个
"""研究上述特征"""
create table t10(id int,name char(4));
create table t11(id int,name varchar(4));
insert into t10 values(1,'jason');
insert into t11 values(1,'jason');
# 针对5.6版本超出范围不会报错 而是自动帮你截取并保存(此行为不合理)
方式1:修改配置文件(永久)
方式2:命令修改(暂时)
show variables like '%mode%'
set session # 当前窗口有效
set global # 当前服务端有效
set gloabl sql_mode = 'strict_trans_tables'
修改完毕后退出客户端重新进入即可
再次执行上述插入命令 会直接报错
# 研究定长与变长特性
insert into t10 values(1,'j');
insert into t11 values(1,'t');
# 统计某个字段数据的长度 char_length()
'''底层确实会填充 但是取出来的时候又会自动去除'''
set global sql_mode = 'strict_trans_tables,pad_char_to_full_length'
char与varchar的对比
char;
优势:整存整取 速度快
劣势:浪费存储空间
varchar
优势:节省存储空间
劣势:存取数据的速度较char慢
"""
char(5)
jasontony kevintom oscartank sean jerry
varchar(5)
1bytes+jason1bytes+tony1bytes+kevin1bytes+tom
存:先计算数据的长度
取:先获取报头的数据
"""
# 以前几乎都是char 现在很多情况下使用varchar
进了公司之后 会通过右键告诉你每个字段的英文名和中文名及类型等项目的诸多信息
补充:在创建字段的时候可以加上相应的注释
create table t12(
id int comment '序号',
name char(4) comment '姓名'
);
4.枚举与集合类型
枚举
多选一
enum()
create table user(
id int,
name varchar(32),
gender enum('male','female','others')
);
insert into user values(1,'jason','男'); # 报错
insert into user values(1,'jason','male'); # 正常
集合
多选多(包含了多选一)
set()
create table userinfo(
id int,
name char(16),
hobby set('basketball','football','doublecolorball')
);
5.日期类型
date 年月日
datetime 年月日时分秒
time 时分秒
year 年份
create table client(
id int,
name varchar(32),
reg_time date,
birth datetime,
study_time time,
join_time year
);
insert into client values(1,'jason','2000-11-11','2000-1-21 11:11:11','11:11:11',1995);
三、创建表的完整语法
create table 表名(
字段名1 字段类型(数字) 约束条件1 约束条件2,
字段名2 字段类型(数字) 约束条件,
字段名3 字段类型(数字) 约束条件
);
"""
1.字段名和字段类型是必须的
2.数字和约束条件是可选的 并且 约束条件可以有多个空格隔开即可
3.最后一个语句的结尾不要加逗号
"""
四、约束条件（新增表数据的新方式）
"""
约束条件相当于是在字段类型的基础之上添加的额外约束
eg: id int unsigned
"""
unsigned 让数字没有正负号
zerofill 多余的使用数字0填充
not null（# 使用非常之高，非常频繁） 非空
比如，用户名和密码呀，不填写怎么能行呢。
"""
新增表数据的方式
方式1: 按照字段顺序一一传值
insert into t1 values(1,'jason');
方式2: 自定义传值顺序 甚至不传
insert into t1(name,id) values('jason',1);
insert into t1(id) values(1);
在MySQL中不传数据 会使用关键字NULL填充意思就是空 类似于python的None
"""
create table t2(
id int,
name varchar(32) not null
);
default 默认值
"""
所有的字段都可以设置默认值
用户不给该字段传值则使用默认的 否则使用传了的
create table t3(
id int default 911,
name varchar(16) default 'jason'
);
"""
unique 唯一值
"""
单列唯一
create table t4(
id int,
name varchar(32) unique
);
联合唯一
create table t5(
id int,
host varchar(32),
port int,
unique(host,port)
);
"""
primary key 主键
"""
但从约束层面上来说 相当于是 not null + unique(非空且唯一)
在此基础之上还可以加快数据的查询
InnoDB存储引擎规定了一张表必须有且只有一个主键
因为InnoDB是通过主键的方式来构造表的
如果没有设置主键
情况1:没有主键和其他约束条件
InnoDB会采用隐藏的字段作为主键 不能加快数据的查询
情况2:没有主键但是有非空且唯一的字段
自动将该字段升级为主键
create table t6(
id int,
age int not null unique,
pwd int not null unique
);
结论:
以后我们在创建表的时候一定要设置主键
并且主键字段一般都是表的id字段(uid sid pid cid)
create table user(
id int primary key,
name varchar(32)
);
"""
auto_increment 自增
"""
由于主键类似于数据的唯一标识 并且主键一般都是数字类型
我们在添加数据的时候不可能记住接下来的序号是多少 太麻烦
create table user1(
id int primary key auto_increment,
name varchar(32)
);
"""
自增的特性
自增不会因为删除操作而回退
delete from无法影响自增
如果想要重置需需要使用truncate关键字
truncate 表名; # 清空表数据并且重置主键值
truncate t1;
delete from t1; 清空表内的数据
insert into t1(name) values('jason');
select * from t1;
这时会看到表的id号顺序 是从数字1开始的。
