MySqL
MySqL
第01章 简介
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据管理系统之一，在 Web应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的 SQL语言是用于访问数据库的最常用标准化语言。MySQL软件采用了双授权政策，其分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择MySQL作为网站数据库。
第02章 作用
| 作用 | 解释 |
|---|---|
| 数据共享 | 数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据库，并提供数据共享。 |
| 减少数据的冗余度 | 同文件系统相比，由于数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性。 |
| 数据的独立性 | 数据的独立性包括逻辑独立性（数据库中数据库的逻辑结构和应用程序相互独立）和物理独立性（数据物理结构的变化不影响数据的逻辑结构）。 |
| 数据实现集中控制 | 文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过数据模型表示各种数据的组织以及数据间的联系。 |
| 数据一致性和可维护性，以确保数据的安全性和可靠性 | 主要包括：①安全性控制：以防止数据丢失、错误更新和越权使用；②完整性控制：保证数据的正确性、有效性和相容性；③并发控制：使在同一时间周期内，允许对数据实现多路存取，又能防止用户之间的不正常交互作用。 |
| 故障恢复 | 由数据库管理系统提供一套方法，可及时发现故障和修复故障，从而防止数据被破坏。数据库系统能尽快恢复数据库系统运行时出现的故障，可能是物理上或是逻辑上的错误。比如对系统的误操作造成的数据错误等 |
第03章 概念
| 名称 | 说明 | 简称 |
|---|---|---|
| 数据库 | 存储数据的仓库，数据是有组织的进行存储 | DataBase(DB) |
| 数据库管理系统 | 操纵和管理数据库的大型软件 | DataBase Management System(DBMS) |
| SQL | 操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。 | Structured Query Language(SQL) |
第04章 关系型数据库
主流的关系型数据库
关系型数据库实际就是二维表结构
第05章 官网
下载地址：https://dev.mysql.com/downloads/installer/
第06章 安装
版本
社区版-免费
商业版
下载视频教程
03. 基础-概述-MySQL安装及启动_哔哩哔哩_bilibili
第07章 基础篇
MySQL服务的启动和停止命令
注意
cmd要以管理员权限运行
| 动作 | 命令 |
|---|---|
| 启动 | net start mysql80 |
| 停止 | net stop mysql80 |
通用语法
单行注释 -- 或者 #
多行注释 /* */
SQL语句可以单行或多行书写，以分号结尾
SQL语句可以使用空格缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库、表、字段) |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
数据库操作
创建数据库
普通创建：create database xxx;
如果没有则创建：create database if not exists xxx;
创建数据库并且指定字符集为utf8mb4：create database xxx default charset utf8mb4
使用数据库
use xxx;
删除数据库
普通删除：drop database xxx;
如果存在则删除：drop database if exists xxx;
显示所有数据库：
show databases;
显示当前使用的数据库
select database();
表操作
创建表
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role`
(
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色编号',
`role_code` varchar(50) NOT NULL COMMENT '角色编码',
`role_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色名称',
`create_user` bigint(20) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '修改时间',
`remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',
`is_delete` tinyint(4) DEFAULT '0' COMMENT '是否删除(0-未删除，1-已删除)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
COMMENT 注释
ENGINE 存储引擎
CHARSET 字符集
COLLATE 排序顺序
删除表
普通删除：drop table xxx;
如果存在则删除：drop table if exists xxx;
删除指定表，并重新创建该表，此时表中就没有数据了：truncate table 表名;
修改表名
alter table 表名 rename to 新的表名;
表中增加字段
alter table 表名 add 字段 类型(长度) 注释 约束;
修改表中字段的数据类型
修改数据类型：alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型(长度) 注释 约束;
删除字段
alter table 表名 drop 字段名;
查询表结构
desc xxx;
查询指定表的建表语句
show create table xxx;
查询当前数据库所有表
show tables;
表中的数据类型
| 名称 | 类型 | 说明 |
|---|---|---|
| tinyint | 整型 | 1 字节，范围 -128 到 127 |
| smallint | 整型 | 2 字节，范围 -32,768 到 32,767 |
| mediumint | 整型 | 3 字节，范围 -8,388,608 到 8,388,607 |
| int | 整型 | 4 字节，范围 -2,147,483,648 到 2,147,483,647 或 0 到 4,294,967,295 |
| bigint | 整型 | 8 字节，范围 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 |
| float | 浮点型 | 4 字节，单精度浮点数 |
| double | 浮点型 | 8 字节，双精度浮点数 |
| decimal | 定点型 | 指定精度和小数位数，例如 DECIMAL(10,2)表示一共10位，其中小数2位 |
| char | 字符型 | 固定长度字符串，最大 255 字节 |
| varchar | 字符型 | 可变长度字符串，最大 65,535 字节 |
| text | 字符型 | 可变长度字符串，最大 65,535 字节 |
| tinytext | 字符型 | 最大 255 字节的字符串 |
| mediumtext | 字符型 | 最大 16,777,215 字节的字符串 |
| longtext | 字符型 | 最大 4,294,967,295 字节的字符串 |
| date | 日期型 | 日期，格式为 'YYYY-MM-DD' |
| datetime | 日期型 | 日期和时间，格式为 'YYYY-MM-DD HH:MM:SS' |
| timestamp | 时间戳 | 格式与 datetime 相同 |
| time | 时间型 | 时间，格式为 'HH:MM:SS' |
| year | 年型 | 年份，格式为 'YYYY' |
| binary | 二进制 | 固定长度的二进制字符串，最大 255 字节 |
| varbinary | 二进制 | 可变长度的二进制字符串，最大 65,535 字节 |
| blob | 二进制 | 可变长度的二进制数据，最大 65,535 字节 |
| tinyblob | 二进制 | 最大 255 字节的二进制数据 |
| mediumblob | 二进制 | 最大 16,777,215 字节的二进制数据 |
| longblob | 二进制 | 最大 4,294,967,295 字节的二进制数据 |
| enum | 字符型 | 字符串对象，值为事先定义的枚举值之一 |
| set | 字符型 | 字符串对象，可以包含零个或多个事先定义的值 |
增加数据
给指定字段添加数据
Insert into 表名(字段1，字段2.....) values(值1，值2.....);
给全部字段添加数据
Insert into 表名 values(值1，值2.....);
删除数据
delete from 表名 where 条件;
修改数据
update 表名 set 字段名1=值1，字段名2=值2 ..... where 条件;
查询数据
查询表中的所有数据
select * from 表名
查询表中指定字段名的数据
select 字段1 as 别名1，字段2.... from 表名
去重查找
select distinct 字段列表 from 表名;
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
聚合函数 count max min avg sum
select 聚合函数(字段列表) from 表名;
条件查询
select 字段列表 from 表名 where 条件列表;
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| between...and... | 在某个范围之内(含最小值、最大值) |
| in(...) | 在in之后的列表中的值，多选一 |
| like 占位符 | 模糊匹配( '_' 匹配单个字符，'%' 匹配任意个字符) |
| is null | 是null |
| 逻辑运算符 | 功能 |
|---|---|
| and 或 && | 并且(多个条件同时成立) |
| or 或 || | 或者(多个条件任意一个成立) |
| not 或 ! | 非 不是 |
分组查询
select 字段列表 from 表名 where 条件 group by 分组字段名 having 分组后过滤条件;
where与having区别
执行时机不同：where是分组前进行过滤，不满足where条件不参与分组，having是分组之后对结果进行过滤
判断条件不同：where不能对聚合函数进行判断，而having可以
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;
asc 升序-默认值
desc 降序
分页查询
select 字段列表 from 表名 limit 起始索引，查询记录数;
多表查询
多表关系
| 关系 | 建立表的规则 |
|---|---|
| 一对一 | 任意选取一方建立外键 |
| 一对多 | 在多的一方建立外键 |
| 多对多 | 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键 |
//笛卡尔积
//产生的数据为emp的数量乘以dept的数量
select * from emp,dept;
连接方式
| 连接方式 | 解释 |
|---|---|
| 内连接 | 相当于查询A B交集的部分数据 |
| 左外连接 | 查询左表所有数据，以及两张表交集部分数据 |
| 右外连接 | 查询右表所有数据，以及两张表交集部分数据 |
| 自连接 | 当前表与自身的连接查询，自连接必须使用表别名 |
内连接
隐式内连接
select 字段列表 from 表1，表2 where 条件；
显式内连接
select 字段列表 from 表1 inner join 表2 on 条件；
左外连接
select 字段列表 from 表1 left join 表2 on 条件;
右外连接
select 字段列表 from 表1 right join 表2 on 条件;
自连接
select 字段列表 from 表A 别名A join 表A 别名B on 条;
联合查询
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...
单独的union会去重
union all 不会去重
子查询
select * from t1 where column1 = (select column1 from t2);
管理用户
查询用户
use mysql;
select *
from user;
创建用户
create user ‘用户名’@‘主机名’ identified by ‘密码’；
主机名为localhost则只能在当前主机访问
主机名为%则可以在任意主机访问该数据库
修改用户密码
alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’；
删除用户
drop user ‘用户名’@‘主机名’；
权限控制
查询权限
show grants for '用户名'@'主机名'
授予权限
grant 权限列表 on 数据库.表名 to '用户名'@'主机名'
撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'
数据库.* 表示所有表
函数
字符串函数
| 函数 | 功能 |
|---|---|
| concat(S1, S2, ...Sn) | 字符串拼接，将S1, S2, ...Sn拼接成一个字符串 |
| lower(str) | 将字符串str全部转为小写 |
| upper(str) | 将字符串str全部转为大写 |
| lpad(str, n, pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| rpad(str, n, pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| trim(str) | 去掉字符串头部和尾部的空格 |
| substring(str, start, len) | 返回从字符串str从start位置起的len个长度的字符串 |
例子
将企业员工的工号不足五位的在前面补0,
update emp set workno = lpad(workno,5,'0');
数值函数
| 函数 | 功能 |
|---|---|
| ceil(x) | 向上取整 |
| floor(x) | 向下取整 |
| mod(x, y) | 返回x%y |
| rand() | 返回0~1内的随机数 |
| round(x, y) | 求参数x的四舍五入的值，保留y位小数 |
日期函数
| 函数 | 功能 |
|---|---|
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| year(date) | 获取指定date的年份 |
| month(date) | 获取指定date的月份 |
| day(date) | 获取指定date的日期 |
| date_add(date, interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| datediff(dateOne, dateTwo) | 返回起始时间dateOne和结束时间dateTwo之间的天数 |
流程控制函数
| 函数 | 功能 |
|---|---|
| if(value, t, f) | 如果value为true, 则返回t, 否则返回f |
| ifnull(valueOne, valueTwo) | 如果valueOne不为空，返回valueOne，否则返回valueTwo |
| case when [val1] then [res1] ...else [default] end | 如果val1为true，返回res1，...否则返回default默认值 |
| case [expr] when [val1] then [res1] ...else [default] end | 如果expr的值等于val1，返回res1，...否则返回default默认值 |
约束
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段都数据不能为null | not null |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据到唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | default |
| 检查约束(8.0.16版本之后) | 保证字段值满足某一条件 | check |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 | foreign key |
事务
简介
事物是一组操作的集合，它是一个不可分割的工作单位，事物会把所有的操作作为一个整体一起向系统提交或撤销操作请求，既这些操作要么同时成功，要么同时失败。
案例
转账业务中的A向B转账，需要A账户的余额减的同时B账户的余额加，这是捆绑操作，要么同时失败，要么同时成功。
查看事务提交方式
select @@autocommit;
结果为1则是自动提交 为0则是手动提交
设置事务提交方式
set @@autocommit = 0;
开始事务
start transactiono； 或 begin；
提交事务
commit;
回滚事务
rollback;
事务四大特性
| 特性 | 解释 |
|---|---|
| 原子性 | 事务是不可分割的最小单元，要么全部成功要么全部失败 |
| 一致性 | 事务完成时，必须是所有的数据都保持一致状态 |
| 隔离性 | 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行 |
| 持久性 | 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的 |
并发事务问题
| 问题 | 解释 |
|---|---|
| 脏读 | 一个事务读到另外一个事务还没有提交的数据 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。 |
事务隔离级别
作用
解决三种并发事务问题
四种隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | √ | √ | √ |
| 读已提交 (Oracle默认) | × | √ | √ |
| 可重复读 (MySql默认) | × | × | √ |
| 串行化 | × | × | × |
√ 表示存在问题
查看事务的隔离级别
select @@transaction_isolation;
设置事务的隔离级别
set [session | global] transaction isolation level
{read uncommitted|read committed|repeatable read|serializable}
//session 当前会话窗口
//global 全部会话窗口
第08章 进阶篇
存储引擎
MySql默认存储引擎InnoDB
介绍
MySql默认存储引擎：InnoDB
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySql 5.5之后，InnoBD是默认的MySql存储引擎。之前是MyISAM存储引擎。
特点
- DML(增删改查)操作遵循ACID(原子性 一致性 隔离性 持久性)模型，支持事务
- 行级锁，提高并发访问性能
- 支持外键 foreign key 约束，保证数据的完整性和正确性
文件
xxx.ibd：xxx表示表名
InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm，sdi)、数据和索引
存储引擎简介
引擎就是发动机，不同的事物使用的引擎不同就比如坦克用不了战斗机的引擎。存储引擎就是储存数据，建立索引，更新查询数据等技术的实现方式。不同的存储引擎对于这些技术的实现是不同的。存储引擎是基于表的，而不是库，所有存储引擎也可被称为表类型。
在创建表时，指定存储引擎
create table 表名(
...
...
)engine=InnoDB
查看当前数据库支持的存储引擎
show engines;
MySql体系结构
连接层
最上层是一些客户端和链接服务，主要完成一些类似于连接处理，授权认证及相关的安全方案。服务器也会为安全接入的每个客户端 验证它所具有的操作权限。
服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程，函数等。
引擎层
存储引擎真正的负责了MySql中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，可以根据需要选取合适的存储引擎。
存储层
主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
索引
介绍
索引(index) 是帮助MySql高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
优缺点
优点
提高数据检索的效率，降低数据库的IO成本。通过索引列对数据进行排序，，降低数据排序成本，降低CPU的消耗。
缺点
索引列需要占据空间，索引大大提高了查询效率，同时也降低更新表的速度，如对表进行insert，update，delete时，效率降低。
索引结构
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+Tree索引 |
| Hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R+Tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text(全文索引) | 是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene，Solr，ES |
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-tree索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本之后支持 | 支持 | 不支持 |
思考
问：为什么InnoDB存储引擎选择使用B+tree索引结构
答：
相对于二叉树，层级更少，搜索效率高。
对于B-tree树，无论是叶子节点，还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
相对于Hash索引，B+tree支持范围匹配及排序操作。
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | primary |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | unique |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | fulltext |
索引语法
创建索引
create [unique|fulltext] index index_name on table_name(index_col_name,...);
unique | fulltext 选择索引类型
index_name 为索引起名
table_name 表名
查看索引
show index from table_name;
删除索引
drop index index_name on table_name;
SQL性能分析
SQL执行频率
MySql客户端连接成功之后，通过命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的insert，update，delete，select的访问频次
show [global|session] status like 'com_______'; //com后面七个下划线
global|session 全局或者是当前会话
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒,默认10秒)的所有SQL语句的日志。MySql的慢查询日志默认没有开启，需要在MySql的配置文件(/etc/my.cnf)中配置如下信息:
// 开启MySQL慢日志查询开关
slow_query_log = 1
// 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2
查看慢日志是否开启
show variables like 'slow_query_log';
profile
帮助我们了解执行SQL语句时的时间都耗费到哪里去了。
查看当前数据库是否支持profile
select @@have_profiling;
查看是否开启profile
select @@profiling;
开启profile
set profiling = 1;
查看所有SQL语句的耗时情况
show profiles;
explain执行计划
explain或者desc命令获取MySql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
explain select 字段列表 from 表名 where 条件;
desc select 字段列表 from 表名 where 条件;
最左前缀法则
如果索引了多列，要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列。索引将部分失效(后面的字段索引失效)
索引失效
不要在索引列上进行运算操作，索引将失效。
字符串类型字段使用时，不加引号，索引将失效。