目录
下载、安装MySQL
数据类型
主要分三类:数值类型、字符串类型、日期时间类型
类型 | 大小(bytes) | 描述 | 有符号(signed)范围 | 无符号(unsigned)范围 | 备注 |
---|---|---|---|---|---|
tinyint | 1 | 小整数值 | (-128,127) | (0,255) | |
smallint | 2 | 大整数值 | |||
mediumint | 3 | 大整数值 | |||
int | 4 | 大整数值 | |||
bigint | 8 | 极大整数值 | |||
float | 4 | 单精度浮点数值 | float(5, 2):5表示整个数字长度,2表示小数位个数 | ||
double | 8 | 双精度浮点数值 | double(5, 2):5表示整个数字长度,2表示小数位个数 | ||
decimal | 小数值(精度更高) | decimal(5, 2):5表示整个数字长度,2表示小数位个数。 在浮点数计算中以字符串处理小数,从而避免精度损失 |
类型 | 大小(bytes) | 描述 | 备注 |
---|---|---|---|
char | 0-255 | 定长字符串 | char(10):最多只能存储10个字符,不足10个字符,占用字符空间 性能高,浪费空间 例如:phone char(11) |
varchar | 0-65535 | 储变长字符串 | varchar(10):最多只能存储10个字符,不足10个字符,按实际存, 性能低,更节省空间 例如:username varchar(55) |
linyblob | 0-255 | 不超过255个自字二进制数据 | |
tinytext | 0-255 | 短文本数据 | |
blob | 二进制形式的长文本数据 | ||
text | 长文本的数据 | ||
mediumblob | 二进制形式的中等长度文本数据 | ||
mediumtext | 中等长度的文本数据 | ||
longblob | 二进制形式的极大文本数据 |
类型 | 大小 | 范围 | 格式 | 描述 | 备注 |
---|---|---|---|---|---|
date | 3 | YYYY-MM-DD | 日期值 | 例如:birthday date | |
time | 3 | HH:MM:SS | 时间值或持续时间 | ||
year | 1 | YYYY | 年份值 | ||
datetime | 8 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 | 例如:update_time datetime | |
timestamp | 4 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
约束条件
作用于表中字段上的规则,用于限制存储在表中的数据。
default 默认约束
保存数据时,如果未指定该字段值,则采用默认值
not null 非空约束
限制该字段值不能为空
unique 唯一约束
保证字段的所有数据是唯一,不能重复的;添加唯一约束时,数据库实际上会添加唯一索引。
primary key 主键约束
唯一标识,要求非空且唯一;主键字段,在建表时,会自动创建主键索引。
foreign key 外键约束
让两张表的数据建立连接
外键约束
# 物理外键
-- 创建表时指定
create table 表名(
字段名 数据类型,
...
[ constraint ] [ 外键名称 ] foreign key(外键字段名) references 主表(字段名);
);
-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);
外键约束 | 物理外键 | 逻辑外键 |
---|---|---|
概念 | 使用foreign key定义外键关联另外一张表 | 在业务层逻辑中,解决外键关联 |
优缺点 | 影响增、删、改的效率(需要检查外键关系)。 仅用于单节点数据库,不适用于分布式、集群场景。 容易引发数据库的死锁问题,消耗性能。 | 没有物理外键的缺点。 |
其他约束
关键字 | 描述 |
---|---|
unsigned | 无符号,数据类型为非负数,可增加数据长度 |
auto_increment | 主键自增 |
聚合函数
Tip: null值不参与所有聚合函数运算
select
# 聚合函数(字段列表)
from
# 表名
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
事务
事务是一组MySQL操作的集合,它是一个不可分割的工作单位,事务会把所有操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。默认mysql的事务是自动提交的。
事务控制
--开启事务
start transaction; / begin;
--提交事务
commit;
--回滚事务
rollback;
事务的四大特性
- 原子性:事务是不可分割的小单元,要么全部成功,要么全部失败。
- 一致性:事务完成时,必须使所有的数据都保持一致状态。
- 隔离性:数据库系统提供的隔离机制,保证事务不受外部并发操作影响的独立环境下运行。
- 持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
并发事务问题
- 脏读:一个事务读到另一个事务还没有提交的数据。
- 不可重复度:一个事务先后读取同一条记录,但两次读取数据不同。
- 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。
解决方案:隔离级别
索引
可以高效获取数据的数据结构,提高查询效率。
优点 | 缺点 |
---|---|
提高数据查询的效率,降低数据库的IO成本。 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。 | 索引会占用存储空间。 索引大大提高了查询效率,同时却也降低了insert、update、delete的效率。 |
存储引擎
索引底层数据结构
MySQL数据库支持的索引,如Hash索引、B+Tree索引、Full-Text索引,默认指B+Tree结构组织的索引。
二叉搜索树
红黑树
Tip: 不采用二叉搜索数,红黑树的原因是大数据量情况下,层级深,检索速度慢。
B+Tree(多路平衡搜索树)二分查找
B+Tree的特点:
- 每一个节点,可以存储多个key(有n个key,就有n个指针)。
- 所有数据都存储在叶子节点,非叶子节点仅用于索引数据。
- 叶子节点形成了一颗双向链表,便于数据的排序及区间范围查询。
创建索引
create [unique] index 索引名 on 表名(字段名...);
查看索引
show index from 表名;
删除索引
drop index 索引名 on 表名;
索引失效
MySQL常用语句
规定:
以英文的分号结尾
不区分字母大小写
注释:
1.单行注释:-- 注释内容; # 注释内容;
(MySQL特有)
2.多行注释:/* 注释内容; */
开启mysql服务
Win+R输入services.msc
——数据定义语言——
用来定义数据库对象(数据库,表,字段)。
进入mysql
mysql -u root -p
Enter password: 1234
退出mysql
exit
查看所有数据库
show databases;
查看当前使用的是哪个数据库
select database();
使用数据库
use 数据库名;
创建数据库
create database [ if not exists ] 数据库名;
删除数据库
drop database [ if not exists ] 数据库名;
创建数据表
create table 表名( 字段1 字段数据类型 [ (数据长度) ] [ 约束 ] [ comment '字段1注释' ], 字段2 字段数据类型 [ (数据长度) ] [ 约束 ] [ comment '字段2注释' ] ) [ comment '表注释' ];
查看当前数据库下的数据表
show tables;
查看数据表的结构
desc 表名;
查看数据表的建表语句
show create table 表名;
添加字段
alter table 表名 add 字段名 数据类型 [ (数据长度) ] [ comment '注释' ] [ 约束 ];
修改字段类型
alter table 表名 modify 字段名 新数据类型 [ (数据长度) ];
修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 新数据类型 [ (数据长度) ] [ comment '注释' ] [ 约束 ];
删除字段
alter table 表名 drop column 字段名;
修改表名
rename table 表名 to 新表名;
删除数据表
drop table [ if exists ] 表名;
——数据操作语言——
用来对数据库表中的数据进行增删改。
添加数据(INSERT)
Tips:
1.插入数据时,指定的字段顺序需要与值得顺序时一一对应。
2.字符串和日期型数据应该包含在引号中。
3.插入的数据大小,应该在字段的规定范围内。
指定字段添加数据
insert into 表名 ( 字段名1, 字段名2 ) values ( 值1, 值2 );
全部字段添加数据
insert into 表名 values ( 值1, 值2, ... );
批量添加数据(指定字段)
insert into 表名 ( 字段名1, 字段名2 ) values ( a值1, a值2, ... ), ( b值1, b值2, ... );
批量添加数据(全部字段)
insert into 表名 values ( a值1, a值2, ... ), ( b值1, b值2, ... );
修改数据(UPDATE)
Tips: 如果没有条件,则会修改整张表的所有数据
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [ where 条件 ];
删除数据(DELETE)
Tips:
1.如果没有条件,则会删除整张表的所有数据。
2.不能删除某个字段的值(如果有这一需求,可以使用UPDATE,将该字段的值设置为NULL)。
delete from 表名 [ where 条件 ];
——数据查询语言——
用来查询数据库表中的记录。
-- 查询语法
select
# 字段列表
from
# 表名列表
where
# 条件列表
group by
# 分组字段列表
having
# 分组后条件列表
order by
# 排序字段列表
limit
# 分页参数
基本查询
查询多个字段
select 字段1, 字段2, 字段3 from 表名;
查询所有字段(通配符)
select * from 表名;
设置别名
select 字段1 [ as 别名1 ], 字段2 [ as 别名2 ], 字段3 [ as 别名3 ] from 表名 [ as 表别名 ];
去除重复记录
select distinct 字段列表 from 表名;
条件查询
select 字段列表 from 表名 where 条件列表;
条件构造
比较运算符 | 功能 |
---|---|
between…and… | 在某个范围之内(包含最小值、最大值) |
in(…) | 在in之后的列表中的值,多选一 |
like 占位符 | 模糊匹配(_表示匹配单个字符,%表示匹配任意个字符) |
is null | 是空值 |
逻辑运算符 | 功能 |
---|---|
and 或 && | 并且(多个条件同时成立) |
or 或 | | | 或者(多个条件任意一个成立) |
not 或 ! | 非,不是 |
分组查询
Tip:
1.分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
2.执行顺序:where > 聚合函数 > having
select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 [ having 分组后过滤条件 ];
where与having区别
1.执行时机不同:where分组之前过滤
2.判断条件不同:having可以对聚合函数进行判断
排序查询
select 字段列表 from 表名 [ where 条件 ] group by 分组字段名 order by 字段1 排序方式1, 字段2 排序方式2 ...;
排序方式
ASC:升序(默认)
DESC:降序
Tips: 如果要多个字段排序,只有当第一个字段字相同时,才会根据第二个字段进行排序,以此类推。
分页查询
select 字段列表 from 表名 limit 起始索引, 查询记录数;
Tips:
1.起始索引指的是要从0开始往后查询每一条记录。
起始索引 = (页码 - 1)* 每页展示记录数
2.查询记录数是这一页我需要展示多少条记录。
多表查询
指从多张表中查询数据,在多表查询时,需要消除无效的笛卡尔积,笛卡尔积:笛卡尔乘积是指两个集合(A集合和B集合)的所有集合情况。
——连接查询——
内连接
相当于查询A、B交集部分数据
--隐式内连接
select 字段列表 from 表1, 表2 where 条件...;
--显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
外连接
左外连接
查询左表所有数据(包含两张表交集部分数据)
select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
右外连接
查询右表所有数据(包含两张表交集部分数据)
select 字段列表 from 表1 right [outer] join 表2 连接条件...;
——子查询——
在查询当中又嵌套查询,子查询外部的语句可以是insert/update/delete/select,最常见的是select。
select * from t1 where column = (select column1 from t2...);
标量子查询
子查询返回的结果为单个值(数字、字符串、日期等)
常用的操作符:
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
列子查询
子查询返回的结果为一列(可以是多列)
常用的操作符:in、not in
行子查询
子查询返回的结果为一行
表子查询
子查询返回的结果为多行多列,常作为临时表
——MySQL优化——
如何去定位慢查询
慢查询出现的情况:
- 聚合查询 新增临时表
- 多表查询 优化sql语句结构
- 表数据量过大查询 添加索引
- 深度分页查询
表象:页面加载过慢,接口压测响应时间过长(超过1s)。
开源工具进行监听和调试
调试工具:Arthas
运维工具:Prometheus
慢日志查询(调试阶段)
Tips:开启会损耗mysql性能
# 进入mysql
mysql -u root -p
Enter passsword: 1234
mysql> show variables like 'slow_query%'; # 查看是否开启慢日志查询
mysql> show variables like '%data%'; # 查看data存放路径,一般my.ini文件在data文件的上一级
需要在MySQL的配置文件中配置如下信息:
# 开启MySQL慢日志查询开关
slow_query_log = 1
# 设置慢日志的时间为2秒,SQL语句执行时间操过2秒,就会视为慢查询,记录慢查询日志
long_query_time = 2
service mysqld restart # 重启mysql服务
SQL执行计划
可以采用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息。
-- 直接在select语句之前加入关键字explain/desc
explain select 字段列表 from 表名 where 条件;
possible_key 当前索引可能使用的索引
key 当前sql实际命中索引
key_len 索引占用的大小
通过key和key_len可以检查是否命中了索引
Extra 额外的优化建议
Extra | 含义 |
---|---|
Using where; Using Index | 查找使用了索引,需要的数据都在索引列中能找到,不需要回表查询数据 |
Using Index condition | 查找使用了索引,但需要回表查询数据 |
type sql的连接类型
性能由好到差为NULL > system > const > eq_ref > ref > range > index > all
type | 含义 |
---|---|
system | 查询系统中的表 |
const | 根据主键查询 |
eq_ref | 主键索引查询或唯一索引查询,只能返回一条数据 |
ref | 索引查询 |
range | 走的是索引,但范围查询 |
index | 检索索引树扫描 |
all | 不走索引,全盘扫描 |
索引
- 索引是帮助MySQL高效获取数据的数据结构(有序)。
- 提高数据检索的效率,降低数据库的IO成本(不需要全表扫描)。
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
索引的底层数据结构
MySQL的innoDB引擎采用B+树的数据结构来存储索引
- 阶数更多,路径更短
- 磁盘读写代价B+树更低,非叶子节点只存储指针,叶子节点存储数据
- B+树便于扫库和区间查询,叶子节点是一个双向链表
数据结构对比
B树与B+树对比
- 磁盘读写代价B+树更低
- 查询效率B+树更加稳定
- B+树便于扫库和区间查询