1.今日内容概要
1.数据库存储引擎
2.字段类型之整型
3.字段类型之浮起点型
4.字段类型之字符类型
5.字段类型之日期类型
6.字段类型之枚举与集合
7.创建表的完整语法
2.sql_mode
# 如何查看严格模式
show variables like "%mode";
"""
模糊匹配/查询
关键字 like
%:匹配任意多个字符
_:匹配任意单个字符
"""
# 修改严格模式
set session 只在当前窗口有效
set global 全局有效
set global sql_mode="strict_trans_tables"; # 修改成严格模式
flush privileges;
set global sql_mode="NO_ENGINE_SUBSTITUTION"; # 修回非严格模式
flush privileges;
# 修改完之后重新进入服务器即可
"""
总结:以后尽量使用严格模式,能在应用程序解决的在应用程序解决,
虽然写程序麻烦但是扩展程序方便了,遇到扩展程序不用求人
数据库本着数据这一块的活自己揽下来
"""
# 实例
show variables like "%mode"; # char默认宽度是1
create table t7(name char(5)); # char默认宽度是1
insert into t7 values('wangmiaolu'); # 此时因为字符太长会报错
insert into t7 values('j'); # 正常存
# 针对不同的版本会出现不同的效果
# 非严格模式规定只能存一个字符,你给了多个字符,那么我会自动帮你截取
# 严格模式,一旦超出范围立刻报错
总结
方式1:命令临时修改
set session sql_mode='strict_trans_tables' 当前客户端操作界面有效
set global sql_mode='STRICT_TRANS_TABLES' 服务端不重启永久有效
方式2:配置文件(my.ini)永久修改法
[mysqld]
sql_mode='STRICT_TRANS_TABLES'
3.存储引擎
# 我们可以简单的理解为存储引擎就是针对相同的数据采用的不同的存取策略
show engines; # 查看所有的存储引擎
"""
什么是存储引擎?
mysql中建立的库===>文件夹
库中建立的表===>文件
现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等
存储引擎就是数据的不同处理机制
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
"""
# 需要掌握的存储引擎
1.MyISAM
MySQL5.5及之前版本默认的存储引擎
存取数据的速度快 但是功能较少 安全性较低
2.InnoDB
MySQL5.5之后版本默认的存储引擎
存取数据的速度没有MyISAM快 但是支持事务、行锁、外键等诸多功能
安全性较高
3.Memory
基于内存的存储引擎 存取数据极快 但是断电立刻丢失
4.BlackHole
黑洞 任何写进去的数据都会立刻丢失 类似于垃圾站
ps:MySQL中默认是大小写不敏感的(忽略大小写)
# 不同存储引擎之间底层文件的区别
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;
ps:windows cmd终端鼠标右键的意思就是粘贴
InnoDB
.frm 表结构
.ibd 表数据、表索引(加快数据查询)
MyISAM
.frm 表结构
.MYD 表数据
.MYI 表索引(加快数据查询)
Memory
.frm 表结构
BlackHole
.frm 表结构
insert into t1 values(111);
insert into t2 values(222);
insert into t3 values(333);
insert into t4 values(444);
4.表详细操作
# 创建表
create table t5(
id int,
name varchar(8) not null
);
# 插入记录
insert t5(name,id) values
("egon",2),
("tom",1);
insert t5(name) values
("xxx"),
("yyy");
# 修改表
alter table t5 rename t4;
# 复制表
create table t6 select host,user,password from db1.t5;
5.字段类型之整型
# 整型 有负号 没有负号
tinyint 1bytes(2^8) (-128,127) (0,255)
smallint 2bytes(2^16) (-32768,32767) (0,65535)
int 4bytes(2^32)
bigint 8bytes
'''不同类型的int能够存储的数字范围不一样'''
# 验证不同类型的int是否会空出一个存储正负号
create table t6(id tinyint);
insert into t6 values(256),(-129);
# 得出结论:tinyint默认就会空出一位存储正负号
"""其实所有的int类型默认都是空出一位存储正负号"""
create table t7(id tinyint unsigned); #unsigned是约束条件
insert into t7 values(256),(-129);
6.字段类型之浮点型
# 浮点型
float(255,30)
总共255位小数位占30位
double(255,30)
总共255位小数位占30位
decimal(65,30)
总共65位小数占30位
create table t8(id float(255,30));
create table t9(id double(255,30));
create table t10(id decimal(65,30));
insert into t8 values(1.1111111111111111111111111111);
insert into t9 values(1.1111111111111111111111111111);
insert into t10 values(1.1111111111111111111111111111);
'''三者精确度不同
decimal > double > float
公司里都是big decimal,decimal最准确
具体使用结合实际情况
'''
7.字段类型之字符类型
char 定长
char(4)
最大只能存储四个字符 如果超过范围则直接报错
如果不超出范围 则用空格填充至四个字符
varchar 变长
varchar(4)
最大只能存储四个字符 如果超过范围则直接报错
如果不超出范围 则有几位就存几位
验证两者的区别
create table t11(id int, name char(4));
create table t12(id int, name varchar(4));
1.结果验证 超出范围两者都会报错
注意sql_mode='strict_trans_tables'
2.验证定长和变长特性
char_length() # 统计字段数据的长度
select char_length(name) from t11; # 查看t11表中字符(name)到底占几个字符
"""
默认情况下char在存储的时候针对没有满足固定位数的字符会自动填充空格
然后在读取的时候又会自动将填充的空格移除 如果想取消该机制 需要sql_mode
set global sql_mode='strict_trans_tables,pad_char_to_full_length';
flush privileges;
上述目录是替换 不是新增 所以之前的配置也要写上
"""
3.char VS varchar
char
整存整取 速度快
浪费存储空间
varchar
节省存储空间
存取数据的速度慢于char
"""
char(4)
a空格空格空格 son空格 jack tom空格 lili
varchar(4)
1bytes+a1bytes+son1bytes+jack1bytes+tom1bytes+lili
存取数据都需要操作报头(耗时)
存储人的姓名>>>:varchar
"""
两者使用频率都很高 现在默认很多时候是varchar
总结:
|a |ab |abc |abcd| # 定长
|1byte+a|1byte+ab|1byte+abc|1byte+abcd| # 变长(1byte是报头表示数据的长度)
8.数字的含义
数字大部分情况下都是用来限制字段的存储长度 但是整型除外!!!
不是用来限制存储的长度 而是展示的长度
create table t13(id int(3));
create table t14(id int(3) zerofill); # 字段为空的话会拿0 '' 代替null
insert into t14 values(1),(12);
mysql> select * from t14;
+------+
| id |
+------+
| 001 |
| 012 |
+------+
总结
以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数
例如快递单号:
eg:
00000000013
00123123031
9.字段类型之枚举与集合
# 枚举
多选一
'''提前定义好数据之后 后续录入只能录定义好的内容之一'''
create table t1(
id int,
name char(6),
gender enum('male','female','others')
);
insert into t1 values(1,'jason','male'); # 正常
insert into t1 values(2,'kevin','男'); # 报错
# 集合
多选多(包含多选一)
create table t2(
id int,
name char(6),
hobbies set('basketball','football','doublecolorball')
);
insert into t2 values(1,'jason','basketball');
insert into t2 values(2,'kevin','football,doublecolorball');
insert into t2 values(3,'jkson','pingpongball');
10.字段类型之日期类型
"""
date 年月日
datetime 年月日时分秒
time 时分秒
year 年
"""
# 2024用字符串类型 2024-01-01
# 1900-99-99空的时间
create table t17(
id int,
name varchar(32),
birth date,
reg_time datetime,
study_time time,
join_time year
);
insert into t17 values(1,'jason','2022-11-11','2000-11-11 11:11:11','11:11:11','1995');
11.字段约束条件
"""
insert into 表名 vlaues() # 默认按照创建表的字段顺序添加
insert into 表名(字段) vlaues() # 可以自定义字段顺序
实操
create table t18(id int,name varchar(32));
insert into t18 values(1): # 报错
insert into t18(name,id) values('jason',1)
"""
1.unsigned 无负号
id int unsigned
2.zerofill 零填充
id int zerofill
3.not null 非空
name varchar(32) not null
4.default 默认值
name varchar(32) default 'jason'
"""
create table t20(id int,name varchar(32) not null default 'jason');
insert into t20(id) values(1);
"""
5.unique 唯一值
id int unique 单列唯一
host varchar(32)
port int
unique(host,port) 联合唯一
"""
create table server(id int,host varchar(32),port int,unique(host,port));
insert into server values(1,'127.0.0.1',8080)
insert into server values(2,'127.0.0.1',8081)
"""
12.创建表的完整语法
create table 表名(
字段名1 字段类型(数字) 约束条件,
字段名2 字段类型(数字) 约束条件,
字段名3 字段类型(数字) 约束条件
)engine=存储引擎; # 存储引擎一般默认innodb
# 注意事项
1.字段名和字段类型是必须的 数字和约束条件是可选
2.数字跟约束条件是可选的(可有可无)
3.约束条件可以写多个 空格隔开即可
4.最后一个字段结尾不能有逗号(不容易发现)
"""
create table t5(
id int,
name char(32),
salary float
);
"""