SQL语法

通用语法

1.可以写多行
2.可以使用空格和缩进
3.不分大小写
4.使用 -- 或 # 来注释
5.使用 /* 内容 */ 来做多行注释

SQL分类

DDL: 数据定义语言，用来定义数据库对象（数据库，表，字段）
DML: 数据操作语言，用来对数据库中的数据进行增删改
DQL: 数据查询语言，用来查询数据库中的表记录
DCL: 数据控制语言，用平创建数据库用冢、控制数据库的访问权限

DDL 数据库操作

查询所有数据库

show databases;

查询当前数据库

select database();

创建数据库

# 规则
create database [if not exists] 数据名 [default charset 字符集] [collate 排序规则]

create database 数据库名;
create database if not exists 数据库名;
# 不建议用utf8,因为utf8为3字节，某些中文使用4字节表示，会出现，建议使用utf8mb4
create database if not exists 数据库名 default charset utf8mb4;

删除数据库

# 规则
drop database [if exists] 库名;
drop database 库名;

使用数据库

use 数据库名;

表的操作-查询

查询

show tables;

表结构

desc 表名;

查询指定表的建表语句

show create table 表名;

创建一个表

# 规则
create table 表名 (
	字段1 字段1类型 [comment 字段1注释],
    字段2 字段2类型(字段长度) [comment 字段2注释],
    字段3 字段3类型(字段长度) [comment 字段3注释],
)[comment 表注释]

# 例子 
create table test (
	id int "id值",
    username varchar(50) comment "用户名",
    `password` varchar(50) comment "密码" # 如果字段名和关键字有冲突，建议使用 `` 来包含
) comment "测试表";

数据类型

数值类型

类型    ->  存储大小(byte)  ->  有符号范围（signed）   ->   无符号范围（unsigned）
tinyint      1                -128~127                           0~255
smallint     2                 -32768~32767                      0~65535
mediumint    3                -8388608~8388607                   0~16777215
int          4                -2147483648~2147483647             0~4294967295
bigint       8                -2^63~2^63-1                       0,2^64-1
float        4                -3.402823466E+38~3.402823466351E38 0和1.175494351E-38,3.402823466E38   
double       8                略
decimal      特殊，需要提供 M数度，和D标度

注意：decimal 的M 是指整个数有多少位，如 1000.00 则 M=6，D是指小数有多少位，如 1000.00 则 D=2

文本及二进制类型

类型               ->             大小byte               ->      描述
char                             0~255                        定长字符串
varchar                          0~65535                      变长字符串
tinyblob                         0~255                        二进制数据
tinytext                         0~255                        短文本字符串
blob                             0~65535 
text                             0~65535
mediumblob                       0~16777215
mediumtext                       0~16777215
longblob                         0~4294967295
longtext                         0~4294967295

定长字符串：如果定义长度为10的char，那怕只存1个字母，也要使用10空间，性能好，占用大

变长字符串：如果定义长度为10的varchar，若只存1个字母，则只占用1个空间，占用少，性能差

日期类型

类型   ->   大小byte         ->             范围                   ->      格式         ->     描述
date          3                   1000-01-01 到 9999-12-31              YYYY-MM-DD           日期值
time          3                   -838:59:59 到 838:59:58               HH:MM:SS             时间值
year          1                   1901 到 2155                          YYYY                 年份值
datetime      8                   1000-01-01 00:00:00 到 9999-12-31     YYYY-MM-DD HH:MM:SS  日期时间值
timestamp     4                   时间戳                                                     日期时间值

表的操作-修改

添加字段

# 规则
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

# 例
alter table test add nickname varchar(20) comment "昵称"

修改字段

# 规则 (修改数据类型)
alter table 表名 modify 字段名 新的数据类型(长度)

# 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 新的数据类型(长度) [comment 注释]

# 例
alter table test change nickname username varchar(30)

删除字段

alter table 表名 drop 字段名;

修改表名

# 规则
alter table 表名 rename to 新表名;

# 例
alter table emp rename to employee;

删除表

drop table if extsis 表名 

# 删除表，并重新创建表名,原来的数据会被删除，只有一个空表
truncate table 表名;

DML数据操作

DML 用来对数据库中的表的数据记录进行增删改操作

添加数据 insert

# 规则
insert into 表名 (字段1 , 字段2 , ...) values (字段1数据, 字段2数据 , ...)

# 给所有字段添加数据
insert into 表名 values (字段1 , 字段2 , ...)

# 批量添加数据
insert into 表名 (字段1 , 字段2 , ...) values (字段1数据, 字段2数据 , ...), (字段1数据, 字段2数据 , ...)
insert into 表名 values (字段1 , 字段2 , ...),(字段1 , 字段2 , ...)

修改数据 update

# 规则
update 表名 set 字段名 = 值, 字段名 = 值 ... [where 条件]
# 若不写条件的话，会对全表进行修改，请慎用

# 例
update test set name = "new", password = "123456" where name = "old"

删除数据 delete

# 规则
delete from 表名 [where 条件]
# 注意，若不写条件的话，会对全表进行删除，请慎用

# 例
delete from test where name = "old"

DQL数据查询

DQL 数据查询语言，用来查询数据库中的表的记录

select 语句

# 查询规则
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数

基本查询

# 查询多个字段
select 字段1, 字段2, ... from 表名

# 查询所有字段
select * from 表名

# 设置别名查询（as 可以省略）
select 字段1 as 别名1, 字段2 别名2 ... from 表名

# 去除重复记录
select distinct 字段列表 from 表名

条件查询 where

where 条件有多种条件

比较运算                  功能
 >                       大于
 >=                      大于等于
 <                       小于
 <=                      小于等于
 =                       等于
 <> 或 !=                不等于
 between...and...        在某个范围之内（含最小、最大值）
 not between .. and..    不在某个范围内
 in(...)                 在 in 之后的列表中的值，多选一
 like                    模糊匹配（ _ 匹配单个字符，%匹配任意个字符）
 is null                 是 null 的
 is not null             不是 null 的

逻辑运算符中

运算符                     功能
and 或 &&                  并且（多个条件同时成立）
or 或 ||                   或者（多个条件任意一个成立）
not 或 !                   不是这个的

聚合函数 count max min avg sum

将一列数据作为一个整体，进行纵向计算

函数                     功能
count(字段)             统计数量
max(字段)               输出所有记录中的最大值
min(字段)               输出所有记录中的最小值
avg(字段)               输出所有记录中的平均值
sum(字段)               计算出所有记录中的和

# 例:取出所有员工的平均年龄
select avg(age) from emp;

注意：所有的null值都不参与计算

分组查询 group by

# 规则
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]

where 和 having 的区别

1.执行时机不同，where 是分组之前进行过滤，不满足where条件不参与分组，而having 是分组之后对结果进行分组

2.判断条件不同，where不能对聚合函数进行判断，而having可以。

where 和 having 条件区别
假如有4条记录
id    num
1     1
2     2
3     2
4     4
使用 sql 语句筛选 num <= 3的记录,并分组
select num from xx where num <= 3 group by num;
结果是
num
1
2
# where 是对数据记录中的条件查询，而having 是针对`结果`再进行筛选
使用 sql 语句筛选 num <= 3的记录,并分组,在查询出来的结果中，再筛选 num 为 2的记录
select num from xx where num <= 3 group by num having num = 2
# 上面的 sql 先进行 where 的条件查询数据表，查询的结果再轮到 having 进行二次筛选
# 如果sql 中包含了聚合函数，那么顺序是 where > 聚合函数 > having

注意：使用分组或聚合查询时，加入其它查询的字段是没有意义的，如下sql中的 username 是没有意义的

# username 是没有意义的，因为查询出来的结果是 sex的分组和 count 统计，统计包含多个username记录，但username只能显示一条记录
select username, sex, count(*) from emp group by sex;

排序查询 order by

# 规则
select 字段列表 from 表名 order by 字段1, 排序方式1, 字段2, 排序方式2

# 排序方式只有两种
asc  升序（默认）
desc 降序

# 注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询 limit

# 规则 
select 字段列 from 表名 limit 起始索引 , 查询记录数
# 起始索引从0开始，起始索引 = 页码-1 * 每页显示记录数，比如一页10个记录，那么第3页的记录为 3-1 * 10 = 20,即 limit 20,10
# 如果查询的是第一页，起始索引可以省略，直接 limit 查询记录数 即可

DQL 执行顺序

先执行的部分，后部分的能获取先执行的部分的结果，比如使用表别名，字段别名

第一步：from 表名

第二步：where 条件

第三步：group by 分组

第四步： select 查询

第五步：order by 排序

第六步：limit 分页

DCL数据库控制

DCL 用来管理数据库用户、控制数据库的访问权限，这类操作一般是DBA人员使用的比较多

查询用户

# 查询用户的信息
use mysql;
select * from users;

创建用户

# 规则
create user '用户名'@'主机名' identified by '密码';
# 创建任意主机上访问的用户,% 代表任意主机
create user '用户名'@'%' identified by '密码';

# 例
create user 'tzming'@'localhost' identified by '123456';
# 注意，创建完的用户没有任何权限，需要对新建用户进行权限分配

权限控制

MySQL中定义了很多种权限，常用的有以下几种

权限                                        说明
ALL, ALL Privileges                         所有权限
SELECT                                      查询权限
INSERT                                      插入权限
UPDATE                                      修改权限
DELETE                                      删除权限
ALTER                                       修改表权限
DROP                                        删除数据库、表、视图权限
CREATE                                      创建数据库、表权限

查询权限

# 规则,查询该用户的所有权限
show grants for '用户名'@'主机名';

授予权限

# 规则
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

# 权限列表中可以定义上一节中的关键字，也可以设置 ALL, 但是这个只局限于后面的数据库中的某个表名
# 数据库名 和 表名 都可以使用 * 来代替所有数据库或所有表

# 例：授予 abc库中的def表的所有权限
grant all on abc.def to 'test'@'localhost'

# 例：授予 所有库所有表 的所有权限
grant all on *.* to 'test'@'localhost'

撤销权限

# 规则
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

# 权限列表中可以定义上一节中的关键字，也可以设置 ALL, 但是这个只局限于后面的数据库中的某个表名
# 数据库名 和 表名 都可以使用 * 来代替所有数据库或所有表

修改用户密码

# 规则
alter user '用户名'@'主机名' identified with mysql_native_password by '新的密码'

删除用户

# 规则
drop user '用户名'@'主机名'

函数

函数是指MySQL已经内置的预设代码，可以直接被另一段程序调用的程序或代码

字符串函数

mysql 中常用的处理字符串函数如下表：

concat(s1,s2,...,sn)  ->       字符串拼接，将多个参数拼接为一个字串
lower(str)  ->                 将字符串转全小写
upper(str)  ->                 将字符串转全大写
lpad(str,n,pad)       ->       左填充，用字符串pad对str的左边进行填充，达到n个字符串的长度
rpad(str,n,pad)       ->       右填充，用字符串pad对str的右边进行填充，达到n个字符串的长度
trim(str)   ->                 去掉字符串头尾空格
substring(str, start, len)  -> 返回从字符串str从start位置起的len个长度的字符串

concat 拼接

# 作为输出拼接
select concat('hello', 'mysql');

lower 转小写

# 作为查询输出
select lower("ABC");

upper 转大写

# 作为查询输出
select upper('abc');

lpad 左填充

# 如果文本不足指定长度，则使用 pad 进行填充
select lpad("abc", 6, "-");
# 结果是 ---abc

rpad 右填充

# 如果文本不足指定长度，则使用 pad 进行填充
select rpad("abc", 6, "-");
# 结果是 abc---

trim 去除首尾空

# 只去除首尾的空格，不去除中间的空格
select trim("   abc  def   ");
# 结果 : abc   def

substring 子文本提取

# 提取 "hello mysql" 中的字符串，从第一个字符开始，取5个。
# 注意， substring 中的起始数从 1 开始计算（不是从0开始）
select substring("hello mysql", 1, 5)

数值函数

常见的数值函数如下

ceil(x)          ->    向上取整
floor(x)         ->    向下取整
mod(x,y)         ->    返回x/y的模
rand()           ->    返回0~1内的随机数
round(x,y)       ->    求参数x的四舍五入的值，保留y位小数

ceil 向上取整

# 只要小数不是0,都会向上取整
select ceil(1.1); 
# 结果 : 2

floor 向下取整

# 所有小数位都会被省略
select floor(1.9);
# 结果 1

mod 取模

# 取 x / 7 的余值
select mod(7,4);
# 结果 3 , 因为，7 / 4 = 1 余 3 

rand 随机数

# 获得一个随机小数，界于0~1的小数
select rand();

round 四舍五入

# 对 2.345 进行四舍五入，并保留2位小数
select round(2.345, 2)
# 结果 2.35 ,因为 千份位为 5，进一

select round(2.344 , 2)
# 结果 2.34

日期函数

常见的日期处理函数如下

curdate()                ->       返回当前日期
curtime()                ->       返回当前时间
now()                    ->       返回当前日期和时间
year(date)               ->       获取日期年份
month(date)              ->       获取日期月份
day(date)                ->       获取日期日
date_add(date, INTERVAL expr type)  ->  返回一个日期、时间值加上一个时间间隔expr后的时间值
datediff(date1, date2)   ->       返回起始时间 date1 和结束时间 date2 之间的天数

curdate() 当前日期

# 取当日日期
select curdate()
# 2026-02-27

curtime() 当前时间

# 取当前时间
select curtime()
# 10:47:06

now() 当时日期时间

# 取当前日期时间
select now()
# 2026-02-27 10:47:40

year() 取年份

select year('2026-02-27 10:47:40')
# 2026

month() 取月份

select month('2026-02-27 10:47:40')
# 2

day() 取日

select day('2026-02-27 10:47:40')
# 27

date_add 日期计算

# 使当前时间增加 70 年
# 注意：INTERVAL 是固定写法，type 是指时间单位，如 year，month，day，hour,minute,second 这些
select date_add(now(), interval 10 day )
# 2026-03-09 10:52:25

datediff 日期相差

select datediff('2026-02-01', '2026-03-01')
# -28 天,其实就是前面的日期，减去后面的日期

流程函数

流程函数也是很常用的一类函数，可以在sql语句中实现条件筛选

if(value, t ,f)                                    ->             如果value为 true 则返回 t，否则返回 f
ifnull(value1, value2)                             ->             如果 value1不为空，返回 value1 ，否则返回 value2
case when [val1] then [res1] ... else [default] end -> 如果 val1 为true,则返回res1,... 如果都不合否则返回default
case [expr] when [val1] then [res1] ... else [def] end -> 如果expr的值为val1,则返回 res1，... 否则返回 def 的值

if 语法 和 ifnull 语法

# 使用 if 来输出不同的值
select if(1=1,'ok','error')
# ok

# 使用 ifnull 判断第一个值是否为 null 如果不为null就输出另一个值
select ifnull('not null', 'value2')
# not null
select ifnull(null, "value2")
# value2

case when语法

# 规则1
select case when 值1 then 输出1 when 值2 then 输出2 else 默认值 end;
# 如果 值1 或 值2 都不为 null ，则会输出 [输出值]


# 规则 
select case 字段 when 符合1 then 'ok' when 符合2 then 'yes' else 'false' end;
# case 可以存在多个 when 字段
# 比如，北京和上海都是一线城市，所以我们可以这样判定
select case addr when '北京' then '一线城市' when '上海' then '一线城市' else '非一线城市' end;

通常我们会使用判断来对值进行输出

# 如果分数 > 80 分为 优秀，分数 >= 60 则为合格，分数 <60 分则为不合格
select (case when score > 80 then '优秀' when score >= 60 then '合格' else '不合格' end) as info

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据

目的是保证数据库中数据的正确、有效性和完整性

约束有以下几种：

非空约束：Not Null， 限制该字段的数据不能为null
唯一约束：Unique, 保证该字段的所有数据都是唯一、不重复的
主键约束：Primary Key , 主键是一行数据的唯一标识，要求非空且唯一
默认约束：Default, 保存数据时，如果未指定该字段的值，则采用默认的值
检查约束：Check, 保证字段值满足某一个条件
外键约束：Foreign key , 用来让两张表的数据之间建立连接，保证数据的一致性和完整性
自增约束：Auto_increment , 对于某个字段的值不需要用户插入，而是由mysql基于上一条记录的值自动增加值插入

常见约束案例

创建一个表，有如下字段，字段中有如下约束
id,int，主键且自增
name,varchar,不为空，且唯一
age,int,大于0,且小于120
status,char,如果没有值，则默认为1
gender,cahr, 无

# 对上面的要求进行建表
create table users(
    id int primary key auto_increment comment '表id',  # 使用了 primary key 和 auto_increment
    name varchar(50) not null unique comment '用户名',  # 使用了 not null 和 unique
    age int check ( age > 0 && age <= 120 ) comment '年龄', # 使用了 check
    status char(1) default 1 comment '状态', # 使用了 default
    gender char(1) comment '性别'
) comment '用户表'

外键约束 Foreign key

在关系数据库中，表与表之间难免会存在一些关联，比如员工表和部门表，员工表中通常会有一个字段用于描述部门表中的某一行记录，用于区分员工是那个部门的，但本身两个表之间的数据并没有强制绑定，部门表中的数据可以随意被删除，这使得员工表中的关联字段发现数据不存在的问题，外键约束是用于绑定表与表之间的某个字段的关联，禁止随意删除表数据，从而达到数据完整性。

# 举例，员工表中几条数据
用户名  部门id
张三    1
李四    1
王五    2

# 部门表中有几条数据
id    部门名称
1     开发部
2     业务部

# 正常来说，员工表中的 [部门id] 和部门表中的 [id] 字段互不相联，但是如果我们随意删除部门表中 id 为 1 的记录时，员工表中的【张三】和【李四】的部门信息可能会关去关联，此时我们就需要对它们两个字段做一个绑定

创建表时增加外键方法

# 创建一个外键，以绑定【员工表】中的【部门id】和【部门表】中的【id】
create table emp(
   字段名 类型
    ...
    constraint 外键名称 foreign key(主表要设置外键字段名) references 辅表（辅表列名）
)

# 讲解上面的主要代码
constraint 指的是增加一个约束
外键名称： 创建一个新的外键约束，需要创建一个外键名，因为外键英文字为 foreign key , 所以一般以 fk开头，再以主表，主表键作为命名，如 fk_emp_dept_id，即说明 emp中的dept_id字段带有一个外键约束
foreign key(主表要设置外键字段名) ：即传入主表要设置外键的字段名，例如 foreign key(dept_id)
references 辅表（辅表列名）：即要绑定的辅表名和字段名,例如 dept(id)

# 如果想让 emp表中的 dept_id 字段 绑定到 dept 表中的 id 字段，则完整代码为
constraint fk_emp_dept_id foreign key(dept_id) references dept(id)

如果表本身已创建，但是未作外键约束，可以通过修改表方式来为表增加一个外键

# 在已有的表基础上增加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) rederences dept(id);

当外键不需要了，我们需要删除这个外键

# 删除外键
alter table emp drop foreign key fk_emp_dept_id;

外键约束删改行为

对于已建立外建的两个表，当辅表中的记录发生修改或删除时，mysql会有几种策略来影响主表中绑定的字段的值。

no action 或 restrict : 如果删除辅表中的记录时，先检查主表中是否还含有关联数据，如果有，则不允许删除
cascade : 如果删除辅表中的记录时，先检查主表中是否含有关联数据，如果有，则把对应关联的主表记录也一并删除
set null : 允许直接删除辅表中的记录，但是对于主表中关联的记录，绑定的字段会设置为null（前提是主键的对应字段允许设置为null）
set default : 如果辅表发生改变，主表关联的数据的绑定字段设置为一个默认的值（Innodb不支持）

要修改外键删改行为时的操作，需要在定义外键时增加【修改】和【删除】时的行为动作

# 在已有的表基础上增加外键,并且当dept表发生修改时 同时修改主表对应的关联字段值，当删除时，把主表关联的记录字段设为null
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) rederences dept(id) on update cascade on delete set null;

# on update cascade on delete set null 为主要留意的代面孔

多表查询

多表关系

在项目开发中，设计表结构时，会根据业务需求及业务模块之间的关系，分析并设计表结构，有三种多表查询关系

一对多

一对多或多对一，如一个部门，有多个员工，一个班级有多个学生，或一个人有多种身份

一对多，通常使用外键创建，以一为辅表，多为主表

多对多

如学生与课程之间的关系可以看作多对多关系，如一个学生可以选多个课程，而一个课程也可以被多个学生选择，这种情况下需要使用一个中间表对两个表之间做维护关系

一对一

一对一，如用户与用户详情的关系，比如一个用户表字段太多，需要拆分多个表来存储时，就常用于一对一表，可以把一个人的分类信息以不同的表进行分表存放

一对一关系可在任意一方加入外键,关联另一方的主键，并设置外键为唯一的unique

多表查询

# 基本的多表查询,但是查询出来的结果数将为两张表的组合情况，即 表1 * 表2 个，称为笛卡尔乘积值
select * from 表1 , 表2;

# 使用字段关联来进行联表查询
select * from 表1 , 表2 where 表1.id = 表2.id;

内连接

内连接，指的是A表和B表中交集的部分的数据

隐式内连接

# 规则
select 字段列表 from 表1 , 表2 where 条件

# 不使用 inner join 直接拼合多个表一起查询，就属于隐式内连接

显式内连接

# 规则
select 字段列表 from 表1 inner join 表2 on 连接条件;
# 还可以给表给表名,如例子
select a.name, b.name from emp a inner join dept b on a.dept_id = b.id;
# inner 可以被省略
select a.name, b.name from emp a join dept b on a.dept_id = b.id;

外连接

左外连接

左外连接是指，查询左表所有数据，以及两张表交集部分数据

# 规则
select 字段列表 from 表1 left [outer] join 表2 on 条件 ...

相当于查询表1的所有数据，包含表1和表2交集部分的数据，没有被关联到的表2的数据，会以null显示

右外连接

右外连接是指，查询右表所有数据，以及两张表交集部分数据

# 规则
select 字段列表 from 表1 right [outer] join 表2 on 条件 ...

相当于查询表2的所有数据，包含表1和表2交集部分的数据，没有被关联到的表1的数据，会以null显示

自连接

自连接，就是自己连接自己来查询

# 规则
select 字段列表 from 表A 别名A join 表A 别名B on 条件

比如员工表中包含上级领导id时

# 员工的上级领导id，等于员工的id，则可以查询出来每个员工对应的上级名字
select a.name as empname, b.name as managename from emp a inner join emp b on a.managerid = b.id;

# 如果要把没有上级领导的员工，也要列出来，就需要使用左连或右连接,这样大老板也会被查询出来（即没有上级领导的员工）
select a.name as empname, b.name as managename from emp a left join emp b on a.managerid = b.id;

联合查询 union 或 union all

union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

# 例：把工资低于5000,和年龄大于50岁的员工都查出来
select * from emp where salary < 5000
union all
select * from emp where age > 50

# 有一种情况，是某些记录同时满足两个条件的，会被输出两个结果，如果希望把重复的合并为一条，则使用 union 不要 union all
select * from emp where salary < 5000
union
select * from emp where age > 50

注意：联合查询的查询字段列表必须要相同

子查询

SQL语句中嵌套select语句，称为嵌套查询，又称为子查询

# 规则
select * from t1 where column1 = (select column1 from t2);
# 父查询语句可以为 insert、update、delete、select的任何一个，但是子查询必定是select

子查询有4种情况

情况一：子查询结果为单个值（单行单列）

# 例：查询部门为‘开发部’的所有员工
# 分解查询：先查询‘开发部’的部门id，再查询符合该id的所有员工
select id from dept where name = '开发部'
select name from emp where dept_id = ?

# 通过子查询可以合并为父子查询
select name from emp where dept_id = (select id from dept where name = '开发部')

情况二：子查询结果为多行单列

多行单列需要配合多行操作符

操作符                  描述
in                 在指定的集合范围之内，多选一
not in             不在指定的集合范围之内
any                子查询返回列表中，有任意一个满足即可
some               与any等同，使用some的地方都可以使用any
all                子查询返回列表的所有值都必须满足

# 例：查询‘销售部’和‘市场部’的所有员工信息
# 分解查询：先查询‘销售部’和‘市场部’的部门id,再查询这两个id的所有员工
# 使用 in 来包含多个值的查询
select name from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部')

# 例：查询所有工资比‘财务部’所有人工资都高的员工
# 分解查询：a.先查询‘财务部’的部门id，b.查询所有‘财务部’的人员工次, c.查询所有员工中的工资比所有‘财务部’工资都高的员工
# 使用 all 来设置满足 比所有‘财务部’工资都高 的条件
a. select id from dept where name = '财务部' -> 获得财务部的id
b. select salary from emp where dept_id = ?  -> 通过财务部的id查询出所有财务部员工的工资
c. select * from emp where salary > ?  -> 通过满足工资大于所有财务部员工工资的条件查询符合的员工

# 最终sql，使用了 all 来同时满足查询的结果
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'))

情况三：子查询结果为单行多列

# 单行多列通常使用以下操作符
= / <> / in / not in

# 例：查询某员工的工资和领导相同的员工
# 分解查询：查询某员工的基本信息，如工资和他的领导。再查询这两个条件都满足的其它员工
select salary,managerid from emp where name = "xxx"
# 这里有一个技巧，当要同时满足两个以上条件的，可以有以下两种方式
方式一：select * from emp where salary = 12500 and managerid = 1;
方式二：select * from emp where (salary, managerid) = (12500, 1);
# 方式二中可以把条件字段和条件数据分组定义，所以单行多列时，我们可以使用方式二来配合查询
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = "xxx");

情况四：子查询结果为多行多列

多行多列中通常使用 in 来包含多条数据的查询
# 例：查询员工A和员工B的工资和职位相同的其它员工信息
# 分解查询：先查询出员工A和员工B的工资和职位，此时会有一个2x2的多行多列表，再使用 in 来匹配两行数据
a. select salay, job from emp where name in ('员工A', '员工B')
b. select * from emp where (salay, job) in (?) 
# 最终可以把查询出来的数据以 in 数据列代入查询
select * from emp where (salay, job) in ( select salay, job from emp where name in ('员工A', '员工B') ) 

# 例：查询入职时间大于 2020年1月1日的员工，并显示他们入职的部门
# 分解查询：先查询所有入职时间大于 2020年1月1日的员工 , 再以查询结果看作一个新的表，在该表基础上再联表查询部门名称
a. select * from emp where entrydate > '2020-01-01';  -> 查询所有符合入职条件的员工
b. select * from ? left join dept b on  a.dept_id = b.id  -> 通过联合‘部门’表查询部门名称
# 最终sql
select a.*, b.name from ( select * from emp where entrydate > '2020-01-01' ) left join dept b on a.dept_id = b.id

多表查询案例

案例一：查询员工的姓名，年龄，职位，部门信息（使用隐式内连接）

# 隐式内连接使用 , 号分开多个表
select * from emp, dept where emp.dept_id = dept.id;

案例二：查询年龄小于30岁的员工的姓名，年龄，职位，部门信息（使用显式内连接）

select * from emp e inner join dept d on e.dept_id = d.id where e.age < 30;

案例三：查询拥有员工的部门id、部门名称

select distinct d.id, d.name from emp e inner join dept d on e.dept_id = d.id;

案例四：查询所有年龄大于40岁的员工，及其归属的部门名称，如果员工没有分配部门，也需要展示出来

select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;

案例五：查询所有员工的工资，并按照工资等级表（salgrade）来评定每个员工的工资应该在什么级别

# 注意：salgrade 表结构如下
grade -> 等级
losal -> 等级最低工资下限
hisal -> 等级最高工资上限

# 分解查询：先查询所有员工的工资，再拿这些员工的工资联表查询其等级，条件是 losal 和 hisal 之间
# 可以使用 >= and <= 做多条件判断，也可以使用 between and 做数值区间判数
select e.*, s.grade from emp e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal; # 方法一
select e.*, s.grade from emp e, salgrade s where e.salary between s.losal and s.hisal; # between 方法

案例六：查询‘研发部’所有员工的信息及工资等级

select 
e.*,
d.name,
s.grade 
from emp e, dept d, salgrade s where 
( e.dept_id = d.id ) and 
( e.salary between s.losal and s.hisal ) and 
( d.name = '研发部' );

案例七：查询‘研发部’的员工平均工资

select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

案例八：查询工资比某员工高的员工信息

select * from emp where salary > ( select salary from emp where name = 'xxx' );

案例九：查询比平均薪资高的员工信息

select * from emp where salary > ( select avg(salary) from emp );

案例十：查询低于本部门平均工资的员工信息

select * from emp e1 where e1.salary < ( select avg(e2.salary) from emp e2 where e2.dept_id = 1 )

案例十一：查询所有的部门信息，并统计部门的员工人数

# 在字段中也可以出现子查询
select d.id, d.name, ( select count(e.id) from emp e where e.dept_id = d.id ) as '人数' from dept d;

案例十二：查询所有学生的选课情况，展示出学生名称，学号，课程名称

# 多对多 需要A表，B表，和一个中间表联合查询得出
select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

mysql是自动提交事务的，每一个数据操作会被当作为一个事务，如果希望把多个数据操作合成一个事务，需要手动开启和提交事务

事务控制

查看、设置事务提交方式

# 查看事务提交方式
select @@autocommit;

# 设置事务的提交方式
set @@autocommit = 0; # 关闭自动提交事务方式

提交事务

# 直接执行，即会对之前所有操作的数据进行修改
commit;

回滚事务

# 直接执行,所有数据的操作将会被回滚取消
rollback;

开启事务

对于上面的 set @@autocommit = 0; 而言，是全局性的关闭自动事务的，如果我只希望在某一段sql执行时以手动事务的方式进行操作，可以通过在sql中控制事务操作

# 开启手动事务
start transaction; 
# 或使用 
begin;

# 执行之后，就可以做正常的数据库操作了

# 完成所有sql操作的话，使用提交事务
commit;

# 如果有出错，则回滚
rollback;

事务的四大特性（ACID）

原子性 Atomicity

事务是不可分割的最小操作单元，要么全部成功，要么全部失败

一致性 Consistency

事务完成时，必须使所有的数据都保持一致状态

隔离性 Isolation

数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

持久性 Durability

事务一旦提交或回滚，它对数据库中的数据的改变是永久性的

并发事务问题

脏读

脏读是指，一个事务读到另一个事务还没有提交的数据

事务1                        事务2
| select id=1                          |
| update id=1     <---读取  select id=1|
| ...                                  |
| 未提交事务
# 在事务1对 id=1 进行 update 但未提交事务之前，事务2查询id=1的数据，此时会读的 事务1被修改的 id=1 的数据，此为脏读

不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

事务1                       事务2
| select id=1   -->                  |
| ...           <--      update id=1 |
| select id=1   -->                  |
|                   
# 事务1 在整个事务中有2次对id=1进行查询，但两次查询过之间，事务2对id=1进行事务提交，使得事务1的两次查询数据不一致，此为不可重复读

幻读

一个事务按照条件查询数据时，没有查到对应的数据行，于是可以插入数据，可是在插入数据时，又发现这行数据己经存在了

事务1                                                       事务2
| select id=1      <-- 查询发现没有数据
|                  -->插入 id=1                           insert id=1 |
| insert id=1      X<-- 此时id=1有数据不可插入
|

事务隔离级别

针对脏读、不可重复读、幻读问题，需要对事务做隔离级别

Read Uncommitted

读未提交隔离级别：此级别会产生脏读、不可重复读、幻读问题，但是性能是最高的

Read Committed (oracle 默认)

读已提交隔离级别：此级别会产生 不可重复读、幻读问题，事务1第一次查询时不会锁定表数据快照，因此事务2对表的修改，事务1再次查询时数据有可能发生改变，与第一次查询不符合。

Repeatable Read (mysql 默认)

可重复读隔离级别：此级别会产生 幻读问题，事务1第一次查询时会锁定表数据快照，即使事务2对表已经发生改变，事务1的查询依然是快照状态，但只快照查询，对写入不作快照，因此可能会出现事务1查询没数据重复，但却不能写入相应数据的情况。

Serializable

串行化隔离级别：能解决三种问题，但是性能是最差的，因为一旦事务1查询过的id，事务2在插入时，会等待事务1提交后，再继续，所以性能会很低

查看和设置事务隔离级别

# 查看数据库的隔离级别
select @@transaction_isolation

# 设置事务隔离级别
set [session | global] transaction isolation level {Read Uncommitted | Read Committed | Repeatable Read | Serializable}

# 其中 [session | global] 是表示这个隔离级别只局限本次会话，还是设置全局隔离级别
# level 则指的是要设置的隔离级别 可选 Read Uncommitted | Read Committed | Repeatable Read | Serializable
# 例
set session transaction isolation level Repeatable Read;

------- 高级篇 -------

存储引擎

存储引擎是基于表，而不是基于库，所以一个数据库中的不同表，可以设置不同的存储引擎，mysql 5.5之后默认使用 InnoDB 引擎

在创建表时，可以指定存储引擎

# 指定表的存储引擎
create table xxx (
 ...
) engine=InnoDB

也可以查询数据库支持什么方面的引擎

# 查询引擎具体支持信息
show engines;

InnoDB 存储引擎

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，Mysql 5.5 之后，InnoDB是默认的MySQL存储引擎

InnoDB 支持DML操作的 ACID模型，支持事务

支持行级锁，提高并发访问性能

支持外键 foreign key 约束，保证数据的完整性和正确性

以下是 InnoDB、MyISAM、和 Memory的区别

特点               InnoDB             MyISAM    重点区别     Memory
存储限制             64TB               有                    有
事务安全             支持                无        *           无
锁机制               行锁               表锁       *           表锁
B+树索引             支持               支持                   支持
Hash索引             无                 无                    支持
全文索引           5.6之后支持           支持                   不支持
空间使用              高                低                   不占用空间
内存使用              高                低                      中等
批量插入速度          低                 高                      高
支持外键             支持               不支持      *          不支持
# 区别在于：事务、外键、行级锁

索引（重点内容）

索引 Index 是帮助MySQL高效获取数据的数据结构（有序的）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

索引结构

MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种

B+树索引：最常见的索引类型，大部分引擎都支持B+树索引
Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引)：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)：是一种通过建立倒排索引，快速匹配文档的方式。类似Lucene,Solr,ES

# 各索引在不同存储引擎中的支持情况，如果不特访指定，通常都是使用B+树索引
索引           Innodb            myisam          memory
B+树            支持               支持            支持
Hash             不                 不             支持
R-tree           不                支持             不
Full-text       5.6后支持          支持              不

二叉树

二叉树的基本结构是，以一个数为顶节点，比上一个节点小的数放左边，比上一个节点大的数放右边，一个节点下最多只能包含2个分支

缺点是，当顺序插入时，会变成一个链表，大大降低查询速度

B-树

以一颗最大度数为5(5阶)的b-tree为例（每个节点最多存储4个key,5个指针）

B树的演变大概说明：

假如设置5阶的情况下：即每一组不能大于5个成员，如果大于，中间那一个数会被分裂出去
每一个成员下面包含特定的数据，每个成员之间包含一条指针，这个指针指向下一组数据，而这组数据大小为指针指向上一组数据的两个数的区间
比如，5 9 之间的下一组数据必定是介于5~9之间的。

B+树

以一颗最大度数为4(4阶)的B+树为例

B+树的上方作为标志，不存储数据，只有最下层才包含数据，以一个单向链表的方式存储

对于MySQL数据库而言，它增加了一个指针，使得排序变得容易

索引分类

索引有几种分类

分类                      含义                                     特点                            关键字
主键索引        针对于表中主键创建的索引                          默认自动创建，且一个表只能有一个      Primary
唯一索引        避免同一个表中某数据列中的值重复                   一个表中可以创建多个                 Unique
常规索引        快速定位特定数据                                 一个表中可以创建多个
全文索引        全文索引查找的是文本中的关键词，而不是比较索引中的值 一个表中可以创建多个                 Fulltext

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类                                        含义                                          特点
聚集索引（Clustered Index）   将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据      必须有，而且只有一个
二级索引（Secondary Index）   将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键      可以存在多个

# 聚集索引必须存在，默认会以主键作为聚集索引
# 如果表中没有主键则以第一个 Unique 唯一的列作为聚集索引
# 若表中既没有主键，也没有Unique列，则InnoDB会生成一个rowid作为隐藏聚集索引

聚集索引与二级索引的存储数据区别

二级索引保存的数据是这个表的主键值（或聚集索引值），使用二级索引查找后，需要选拿到主键值，再向聚集索引处查找行数据

索引语法

创建索引

# 规则 [unique | fulltext] 为可选项，如果加上的unique话，指这个索引是一个唯一索引
# 索引可以关联单个字段和多个字段，多个字段称为联合索引，多字段索引的字段排序是有关系的
create [unique | fulltext] index 索引名(通常以idx_表名_字段名 作为命名) on 表名 (字段1, 字段2, ...)

# 设置头部模糊索引
on 表名 (字段1(长度), 字段2(长度), ...)

# 设置索引的升降序索引,在创建索引时，可以设定该索引是升序索引，还是倒序索引，不设置默认为asc
on 表名 (字段1 asc, 字段2 desc, ...)
# 在查看表索引信息时的 Collation 值为 A 时则是 ASC 升序，为D 时即 DESC 降序

建议尽量创建联合索引，可提高索引查询速度

查看索引

show index from 表名;

删除索了

# 删除指定的索引
drop index 索引名 on 表名;

SQL 性能分析

查询sql执行计数

1.我们需要通过查询sql的增删改查次数，来判定这个表是写入多，还是查询多，从而按照相应办法做出优化

# 查询sql执行频率,查询所有 sql 的执行情况，可以查询当前会话，和查询总执行计算
show [session | global] status

# 通过查询增删改查的方式来判定该表的执行频率
show global status like "Com_______"

# 可以看到每一个操作的执行次数
Com_binlog	0
Com_commit	0
Com_delete	0
Com_import	0
Com_insert	0
Com_repair	0
Com_revoke	0
Com_select	339
Com_signal	0
Com_update	0
Com_xa_end	0

慢查询日志

2.慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位秒，默认10秒）的所有SQL语句的日志

# 查询是否开启慢查询日志功能
show variables like 'slow_query_log'

MySQL的慢查询日志默认没有开启，需要在MySQL配置文件中配置 my.ini(my.cnf)

# 开启慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志,重启生效。
long_query_time=2

profiles 时间耗费监测

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看当前MySQL是否支持

# 查看是否支持show profiles
select @@have_profiling;
# 默认 profiling 是关闭的，可以通过set语句在session/global级别开启profiles
select @@profiling;  # 查看是否有prifiles操作开关，为0指关闭

# 开启prifiles操作开关
set profiling = 1;

通过开启profiles 来监控sql的操作耗时信息

# 查看sql操作的执行耗时情况
show profiles;

# 查看指定query id 的耗时具体信息
show profiles for query id号
show profiles cpu for query id号 # 增加 cpu占用情况

explain 查看sql执行策略

4.explain sql的执行计划

explain 或者 desc 命令获取mysql如何执行 select 语句的信息，包括在select语句执行过程中表如何连接和连接的顺序

# 在所在查询语句前加 explain 或 desc 就可以查看到执行计划
explain select 字段列表 from 表名 where 条件

explain执行计划的字段含义
id:
select 查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行），通常是嵌入子查询才会让id不一样

select_type:
表示select的类型，常见的取值有
simple(简单表，即不使用表连接或者子查询)、
primary(主查询，即外层的查询)、
union(union中的第二个或者后面的查询语句)、
subquery(select/where之后包含了子查询)等

type:
表示连接类型，情能由好到差的连接类型为(性能由好到差) Null、system、const、eq_ref、ref、range、index、all
null 只有不占用所有表查询时才会达到 如 select 1
const 只有使用主键查询或唯一索引查询时才会达到
eq_ref 没有使用主键和唯一索引查询

possible_key:
显示可能应用在这张表上的索引，一个或多个

key:
实际使用的索引，如果为null,则没有使用索引

key_len:
表示索引中使用的字节数，访值为索引字身最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好，但会根据字段内容而定

ref:
每一个索引的执行性能，参考type值

rows:
MySQL认为必须要执行查询的行数（预估值），可能并不总是准确的

filtered:
表示返回结果的行数占需读取行数的百分比（即数据库扫描了多少行，最终筛选出多少行有效结果的比例），filtered的值越大越好

索引使用

最左前缀法则

如果索引了多列（也叫聚合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效（后面的字段索引失效）

# 假如表中设有一个聚合索引，索引中有三个字段作为索引
profiession, age, status

# 如果查询按顺序调用索引作为条件，则索引查询生效
select * from student where profiession="" and age=20 and status="1"

# 如果不使用所有索引字段查询，索引依然有交
select * from student where profiession="" and age=20
select * from student where profiession=""

# 如果跳过使用索引顺序作为条件，则索引失效（这里的顺序是profiession, age, status，这里直接跳过profiession了）
select * from student where age=20 and status="1"

# 这种情况，status索引不会生效
select * from student where profiession="" and status="1"

注意：sql中的查询条件位置不影响左前法则
如 select * from student where age=20 and status="1" and profiession="" 不影响

范围查询

联合索引中，一旦出现范围查询（ >.< 符号）,范围查询右侧的列索引失效

# 右边的 status 会失效,因为 age 使用了 > / < / . 其中一个了
select * from student where profession = "" and age > 30 and status = "1"

# 因此如果想要使用范围查询，请使用 >= <= 之类的

不要在索引列上运算

尽量不要在索引列上进行运算操作，否则索引会失效

# 对索引进行运算，则索引查询会失效(这里的 phone 字段做了索引)
select * from users where substring(phone,10,2) = "15"

字符串查询时不加引号索引失效

# 对于字符串的查询，需要加引号进行查询，否则索引会失效
select * from users where phone = 13450772721;  # 索引失效
select * from users where phone = '13450772721';  # 索引有效

头部模糊查询失效

# 对于模糊查询，如果使用尾部模糊查询，索引生效，如果是头部模糊查询，索引会失效
select * from users where username like "tzm%" # username 索引生效
select * from users where username like "%ing" # username 索引失效

or 连接的条件

用 or 分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

# 如果or 中带有没有索引的列，则整体的索引也失效
select * from users where id= 10 or age = 23; # 此处 id 字段是主键索引，而age没有索引，使得 id 也不会索引查询
select * from users where phone = 13450772721 or age = 23; # 此处 phone 字段是一般索引，而age没有索引，使得 phone 也不会索引查询

数据分布策略

在某些情况下，MySQL会对查询的数据量进行评估，如果sql查询出来的数据可能占居表中绝大多数行时，它会智能的选择全表扫描，而不是生硬的使用索引，只有当查询的数据可能只占表中少量记录时，才会选择使用索引查询。

SQL 提示（手动选择查询索引）

在某些情况下，一个字段可能有两个以上的索引，那么SQL在查询的时候，它会智能的挑选一个索引进行查询。

但是我们可以手动的告诉MySQL，我们需要用这个字段的哪一个索引作为查询索引

# 告诉 sql 用哪个索引(use 只是作为建议，至于mysql用不用，要看mysql权衡)
use index:
select * from users use index(idx_user_pro) where profession = ""

# 告诉 sql 不要用哪个索引
ignore index:
select * from users ignore index(idx_user_pro) where profession = ""

# 告诉 sql 必须要用哪个索引（强制mysql使用这个索引）
force index:
select * from users force index(idx_user_pro) where profession = ""

查询的结果字段都是索引字段时

MySQL对于查询索引是直接在B+树中拿到数据的，如果我们在查询表中时，所要查询的字段，都是索引字段时，这样的查询效率是最高的，但是如果万一查询结果字段中包含了没有做索引的字段时，mysql就得需要回表查询（即通过索引查出的id再从主键B+树中拿到对应的记录，再获取没有索引的字段数据）

# 假如表中包含了四个索引，id为主键索引，profession，age，status 为一个聚合索引
select id,profession,age,status from students where age = 30;
# 因为查询的结果字段中[id,profession,age,status] 都做了索引，那么在查询时，mysql直接从索引中取值了，效率高。

# 其中 name 字段没有做索引
select id,profession,age,status, name from students where age = 30;
# 此时mysql查到了 [id,profession,age,status] 都能在索引中拿到数据，但 name 字段没有索引，mysql就得拿到id值回到表中获取记录，才能取到 name 的值，效率降低。

# 对于主键索引而言，主键索引指针带着的数据是行记录数据（其它索引带着的是id值），如果通过主键查询，则使用 * 也没问题
# 但是如果你不是使用主键索引查询的话（如使用单行索引），使用 * ，会使得单行索引查询出来后，还要回表查询主键索引的记录

如果可以，建议把需要查询的字段，都做索引，以提高速度

前缀索引

当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，会浪费大量IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

# 创建规则，在定义索引字段时定义其长度
create index idx_tablename_columnname on tablename(column(长度))

# 例子: 只索引info字段的前10个字符
create inde idx_users_info on users(info(10))

在截取部分内容作为索引时，难免会出现一个问题，就是截取的索引越短，索引值存在相同的可能性越高，相同的索引值使得索引查询效率变低，但是越短的索引值查询效率又越高，所以我们需要权衡相同索引值与索引值长度的平衡，最好是索引值越短，同时相同率为0

# 举例：有一列数据，它们总体数据是不相同的，但是截短就可能有相同的情况
aaaaabcdde
aaaabcdeed
aaaefrfgdg
aagtreierg
# 上面四个数据中，如果截取字符数为 5 时，所有数据都不相同 即 count(distinct substring(str，1,5) ) / count(*) 是 1 (即4条数据，4条都不同)
# 但如果把截取字符数改为 3 时，则存在数据相同情况，即 count(distinct substring(str,1,3) ) / count(*) 是 0.5 (4条数据里，只有2条不同)，这会使得效率变差，但是索引字符只有3个，比5个字符效率高，所以需要衡量两者

索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引

2. 针对于常作为查询条件（where）、排序（order by）、分组（group by） 操作的字段建立索引

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引

5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以直接从索引中获取数据，节省存储空间，避免回表查询，提高查询效率

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率

7. 如果索引列不能存储null值，请在创建表时使用not null约束它，当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询。

SQL 优化

插入数据 insert 优化

insert 优化

1.不要单条插入，使用批量插入

# 不要使用单条多次插入
insert into table(1,"abc");

# 使用一次插多条
insert into table(1,"abc"),(2,"cde"),(3,"asd")

2.使用手动事务管理的方式进行插入

# 开启事务手动管理提交
begin;
insert into table(1,"abc"),(2,"cde"),(3,"asd");
insert into table(1,"abc"),(2,"cde"),(3,"asd");
insert into table(1,"abc"),(2,"cde"),(3,"asd");
commit;

3.使用顺序插入，不要乱序插入数据，因为顺序插入数据可以提高mysql排序效率。

4.如遇到大数据量需要导入，不要使用insert，而是使用 load 进行文件化导入

# 设置全局开启文件导入功能
set global local_infile = 1;

# 导入文件插入数据
load data local infile '/root/sql.txt' into table `tablename` fields terminated by ',' lines terminated by '\n'
# 表示使用 "/root/sql.txt" 文件作为数据源，以 ","进行字段间分割，以“\n”作为行间分割，导入到 `tablename`表中

主键优化

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（IOT）

页分裂

页分裂通常出现在乱序插入时候的情况

因为页存储数据是顺序存储的，页与页之间会有指针进行连接，如果页1存满了，页2也存满了，而此时插入一条需要存放于页1和页2之间的数据，这果Mysql会开辟一个页3的空间，并把新数据存到页3中，再断开页1与页2的指针，重新连接页1到页3，页3到页2的指针，这是比较耗费性能力

所以在插入数据时，应尽量顺序插入。

页合并

当表记录被删除时，mysql并不会马上把该条记录从磁盘中清除数据，而是给该条数据打上删标标记，当存放在该页的记录被删除标记的数量达到 MERGE_THRESHOLD(默认为 50%) 时，InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页的数据合并以优化空间

如果此时自己剩下的页空间大小满足靠近页的数据量时，靠近页的数据将全部移动到自己页位置上。

注意：MERGE_THRESHOLD 值是可以自己设置的，在创建表或者创建索引时指定

主键优化

1.主键尽量不要太长，因为二级索引中存储的数据都是主键数据，如果主键值太长，会使得二级索引占用空间变大，B+树层数变多，影响查询效率。

2.插入数据时，尽量选择顺序插入，使用AUTO_INCREMENT自增功能，因为主键默认会创建主键索引，也会存在于B+树中，乱序会额外增加B+树排序、页分裂和指针重生成操作，影响性能

3.尽量不要使用UUID做主键或者其它身然主键（如身份证号），因为这类数据也是需于无序数据，插入时也会触发乱序插入行为。

4.业务操作时，避免对主键的修改，因为修改主键，会触发主键索引B+树指针重新生成操作。

order by 优化

Using filesort

通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort排序，会相对比较慢。

Using index

通过有序索引顺序扫描直接返回有序数据，即为using index，不需要额外排序，会相对比较快。

Using filesort 和 Using index 的调优

# 我们尽量让查询不要触发 Using filesort ,而是尽量使用 Using index 
# 情况一：如果order by 的字段都有索引时，通常都会 Using index
# 情况二：如果 order by 两个以上的字段时，这些字段也需要有索引，也会 Using index
# 情况三：如果 order by 两个以上的字段时, 字段顺序与索引设置的字段顺序不一样时，会触发Using index 后再触发 Using filesort,因为顺序不对（如两个字段调转 order by 时），mysql 只能 Using index 第一个字段，第二个字的索引会失效只能 Using filesort了
# 情况四：如果 order by 两个以上的字段时，一个字段使用 asc 另一个字段使用 desc ,则会触发Using index 后再触发 Using filesort，除非这两个字段另外开辟一个 字段1 asc 字段2 desc 的索引。

# Using filesort 是需要存放到一个缓冲空间，kv名为 sort_buffer_size = 256kb ,如果超出了这个缓冲区，mysql会把数据放在磁盘中进行排序，速度会下降，可通过手动设置 sort_buffer_size 来增加缓冲空间
show variables  like 'sort_buffer_size'; # 查看缓冲区大小
set sort_buffer_size = 512000  # 设置缓冲区大小

group by 优化

group by 的优化与 order by 类似，在需要使用字段进行 group by 时，应把字段添加到索引中，并且group by 遵守最左前缀法则。

limit 优化

对于大数据表来说，limit 的值越往后，耗时越长，效率越低

这是因为mysql的背后操作是扫描表操作，如果获取 limit 2000000, 10 时，mysql 会扫描2000010个数据行，最后再把 2000000 行丢丢，查询代价很大。

我们可以换一个思路，因为主键id使用的是聚合索引，且顺序插入的情况下，使用 id 查询limit速度会更快，我们可以通过嵌入id查询的方式获取limit范围的id，再进行查询对应的行记录

# 先limit 出来对应的id
select id from datatable limit 2000000,10

# 再对这查询出来的id进行查表查询
select * from datatable where id in (?)

# 结合sql
select * from datatable where id in ( select id from datatable limit 2000000,10 )
# 或者，以 select id from datatable limit 2000000,10 查询出来的结果作为一个表，去联表查询
select d.* from datatable d inner join ( select id from datatable limit 2000000,10 ) s on d.id = s.id;

count 优化

对于MyISAM来说，它本身就把表的总行数动态记录了，所以如果不加where条件查总行数时速度很快

对于InnoDB来说就麻烦了，它本身不动态记录总行数，即使不加where条件依然要一条条记录进行计算。

count() 的不同使用性能对比

count(主键)：会把id值取出来并进行累加 性能消耗 ****
count(字段)：会把字段值取出来，如果字段有 not null 约束，则直接累加，性能 ****，如果没有 not null 约束，还需要判断是否为 null，性能 *****
count(1): 遍历整张表，但不取值，服务层累加，性能 **
count(*): 经过优化，不取值，服务层直接累加，性能 **
所以性能 count(*) = count(1) > count(主键) > count(字段)

update 优化

update 时我们就与并行隔离有关系了，当一个事务发生 update 时，以Mysql的事务隔离用的是Repeatable Read级别，这个级别是属于行锁级别。

情况一：当update的条件是id主键时，行会被锁住

情况二：当update的条件是没有索引的字段时，行锁会升级为表锁，把全表都锁住

这是因为，使用字段进行条件update时，mysql需要对全表的数据进行筛选，在还没有筛选出来结果之前，所有行都有可能会被update,因此mysql会把行锁升级为表锁

情况三：当update的条件是有索引的字段时，行锁被锁住，而不会升级为表锁

这是因为，使用了索引的字段，本身已经创建了一个B+树排列，而B+树中保存了该字段所在的主键id值，所以update带索引的字段时，mysql是明确知道需要update的行都有那些，它就只会锁住对应的行。

因此，在需要使用字段作为update条件时，应该为该字段添加一个索引。

视图、存储过程、触发器

视图

视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的sql逻辑，不保存查询结果。

所以我们在创建视图的时候，主要的工作就落在创建这条sql查询语句上。

视图的作用

1.简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作，那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件

因为视图可以预先设置条件，也可以进行嵌套，条件也会进行嵌套，这样相当于每一次查询或更新，都自带了预设的条件

2.安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。

可以通过视图，去蔽屏一些不想让用户看到的字段，就可以不加入到视图中，用户只允许操作视图时就无法对不允许的字段进行操作

3.数据独立：视图可以帮助用户屏蔽真实表结构变化带来的影响。

当表中的字段名字变更，或不希望用户看到原表中的字段名，可以通过 select 时添加别名生成视图，视图中的字段就不会是原表中的字段名而是经过别名后的字段名了

创建视图

# 规则
create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]

# 例：创建或替换一个视图，封装的数据是 select 中的语句中的数据
create or replace view users_v_id_name as select id,name from users where id<10;

关于 with [cascaded | local] check option

在我们创建一个视图时，我们定义了这个视图，不旦是由一条sql语句创建的数据作为视图的表，还会让sql语句中的查询条件作为约束这个表的容纳范围，下面举例视图的创建规则

# 创建一个视图，视图数据来自 users 表，且查询条件为 id < 20
create view v_users_1 as select * from users where id < = 20;
# 此时的视图将保存了由 select * from users where id < = 20; 查询出来的结果

# 如果在此时往视图插入一条数据
insert into v_users_1 values (10,"tzming",18);
# 此时在 users 表和 视图 v_users_1 中都能查询到这条新的记录。

# 但是如果此时往视图插入一条违背查询条件的数据
insert into v_users_1 values (30,"tzming",18); # id为30已经违背了创建视图时 id < 20 的条件
# 此时在 users 表能查询到这条新的记录,但视图 v_users_1 不会查询到这条记录，原因是它违背了创建视图时 id < 20 的条件，所以不会显示。

但是即使插入了一条背了创建视图时 id < 20 的条件的数据，视图却没有报错，这就是因为在创建视图时，没有对视图做限制，而 [with [cascaded | local] check option] 则是对插入视图数据时违背条件时的限制设定。

# 在创建视图时，加入 [with [cascaded | local] check option] 限制视图
create view v_users_1 as select * from users where id < = 20 with cascaded check option;

# 此时往视图插入一条违背查询条件的数据
insert into v_users_1 values (30,"tzming",18);
# 会报错，不允许插一条违背条件的数据。

cascaded 和 local 的区别

注意：默认为 cascaded

cascaded 又叫级联限制，如果视图是基于另一个视图创建的，若这个视图加上了 cascaded 限制后，那么它及以上的视图都会被限制

# 创建一个没有限制的视图
create view v_users_1 as select * from users where id <= 20;

# 创建一个有限制的，基于 v_users_1 视图的视图
create view v_users_2 as select * from v_users_1 where id >= 10 with cascaded check option;

# 此时，如果插入一个违背 视图1 的数据时，依然会出现报错
insert into v_users_2 values (30,"tzming",18); # 即使视图2满足条件，因视图2加了限制，且视图1不满足条件所以会报错

但是 cascaded 只会对它以及向上的视图进行限制，如果在下级没有做限制，那么下级也不会有限制

# 创建一个没有限制的视图
create view v_users_1 as select * from users where id <= 20;

# 创建一个有限制的，基于 v_users_1 视图的视图
create view v_users_2 as select * from v_users_1 where id >= 10 with cascaded check option;

# 创建一个没有限制的，基于 v_users_2 视图的视图
create view v_users_3 as select * from v_users_2 where id <= 15;

# 在这里如果插入一条违背 视图3 却不违背 视图1,2 时，依然能插入不报错
insert into v_users_2 values (16,"tzming",18); # id 是16 违背了 视图3 但没有违背 视图1和2 ,因视图3没有限制所以不报错

# 在这里如果插入一条违背 视图1的数据时，因视图2做了限制，即使视图1没有做限制，也会报错
insert into v_users_2 values (21,"tzming",18); # id 是 21,违背了视图3,但是视图3没有限缺所以不报错，没有违背视图2所以也不报错，但是违背了视图1,且视图2做了限制，所以报错了

local:本地限制，不级联限制

与 cascaded 不同的是，local 并不会让限制影响到它所依赖的上级视图，只对自身视图进行限制

# 创建一个没有限制的视图
create view v_users_1 as select * from users where id <= 20;

# 创建一个有限制的，基于 v_users_1 视图的视图
create view v_users_2 as select * from v_users_1 where id >= 10 with local check option;

# 此时，如果插入一个违背 视图1 的数据时，local 不会报错
insert into v_users_2 values (30,"tzming",18); # 因为只有视图2做了local限制，只要视图2通过了，那怕违背了视图1的条件，也依然不会报错。

查询视图

# 查看创建视图的语句
show create view 视图名称

# 查看视图数据
select * from 视图名称

修改视图

# 方式一：使用创建视图的方式
create or replace view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]

# 方式二：使用类似修改表的方式
alter view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]

删除视图

# 删除一个视图
drop view [if exists] 视图名称 [,视图名称2] ...;

不可更新的视图

可更新的视图，视图的数据必须与原表中的数据一一对应才可以，如果视图中的数据在原表中没有对应，则既不能更新也不能插入。

# 使用以下情况来创建的视图不可被插入和更新
1.聚合函数或窗口函数，如 sum()、min()、max()、count()等
2.distinct 去复操作
3.group by 分组
4.having 基于结果的筛选
5.union 或 union all 联合查询结果

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程的思想上很简单，就是数据库SQL语言层面的代码封装与重用。

创建存储过程

# 规则
create procedure 存储过程名称([参数列表])
begin
	-- sql 语句
end;

# 调用存储过程
call 存储过程名称([参数列表]);

在命令行中创建存储过程时，命令行会对带有 ; 号认为语句结束，这使得存储过程中的sql语句可能出现截断从而创建存储过程失败。

可以使用 delimiter 命令指定自定义结束符

# 自定义语句结束符
mysql > delimiter $$

# 这时就可以在命令行中输入带有 ; 号的sql语句了
mysql > create procedure p1()
     -> begin
     -> select * from users;
     -> end;$$   # 设置了$$作为结束符时，必须要以这个符做结尾

查看存储过程

# 查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema = "数据库名"

# 查询某个存储过程的定义
show create procedure 存储过程名称;

删除存储过程

# 删除存储过程
drop procedure [if exists] 存储过程名称;

使用存储过程

系统变量

系统变量是由 mysql服务器提供，不是用户定义的，属于服务器层面，分为全局变量（global）和会话变量（session）

global 变量即会影响所有的会话，所有的console 都会受影响

session只会影响自身的会话，即自身的console

注意：如果不指定 session 或 global 变量，则默认为 session

注意2:对于不管是 session 还是 global，只要服务器重启了，所有的变量都会重置，如果要永久配置该参数，只能修改my.ini配置文件了。

查看变量

# 查看所有系统变量
show [session | global] variables;

# 可以通过 like 模糊匹配方式查找变量
show [session | global] variables like '...'

# 查看指定变量的值,使用 @@变量 直接查看该变量的值
select @@[session | global] 系统变量名;
# 例
select @@session.autocommit;
select @@global.autocommit;
select @@autocommit; # 等价于 @@session.autocommit

设置变量值

# 设置变量值1
set [session | global] 系统变量名 = 值;

# 设置变量值2
set @@[session | global] 系统变量名 = 值;

用户变量

用户变量是指用户自定义的变量，用户变量不用提前声明，在用的时候直接用 @变量名 使用就可以。作用域为当前连接

读取未创建的变量时不会报错，值为 null

创建与赋值

# 创建变量并赋值
set @var_name = expr [, @var_name2 = expr2] ...;
set @var_name := expr [, @var_name2 := expr2] ...;
select @var_name := expr [, @var_name2 := expr] ...;
select 字段名 into @var_name from 表名; # 把查询结果给变量

# 例 
# = 与 := 的区别在于, = 既表示赋值也表示判断，而 := 只是赋值。为了明确区分这是赋值行为，建议使用 :=
set @name = 'tzming', @age = 19;
set @name := 'tzming', @age := 19;
select @gender := "男";
select count(*) into @mycount from users; # 这种为把查询表得出的数据赋值给变量 @mycount


# 要使用变量时
select @var_name;

# 例
select @name, @age;

局部变量

局部变量是根据需要定义在局部生效的变量，在使用之前需要用 declare 声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的 begin ... end 块中有效

# 声明一个局部变量
declare 变量名 变量类型 [default ...];
# 变量类型就是数据库字段类型
int bigint char varchar date time 等

# 举例：如何使用
create procedure p1()
begin 
	declare my_count; # 在这里定义
	set my_count := 0; # 在这里赋值
	select count(*) into my_count from users; # 在这里赋值
	select my_count; # 在这里读取
end;

# 赋值给局部变量
set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ... ;

if 判断

# 创建规则
if 条件1 then
	... 满足条件1的操作
elseif 条件2 then
	... 满足条件2的操作
else
	... 都不满足条件的操作
end if;

# 例
create procedure p1()
begin
	declare score int default 58;
	declare result varchar(10);
	
	# if 使用案例,传参与返回请看下一节
	if score >= 85 then
		set result := "优秀"
	elseif score >= 60 then
		set result := "合格"
	else 
		set result := "不合格"
	end if;
	select result;
end;

存储过程的参数

参数类型分为3类,分别如下

类型
in               该类参数作为输入，也就是需要调用时传入值             作为默认
out              该类参米作为输出，也就是该参数可以作为返回值  
inout            既可以作为输入数据，也可以作为输出参数

用法：

create procedure 存储过程名称 ([in/out/inout 参数名 参数类型]) # 如果不指定 则默认是 in
begin
	--sql语句
end;

使用案例(2个)

# 输入分数，输出合格或不合格(in 和 out 参数案例)
create procedure toScore(in score int, out result varchar(10))
begin
	# declare result varchar(10); 在参数中定义了，在局部中不用定义了
	
	if score >= 85 then 
		set result := "优秀"
	elseif score >= 60 then
		set result := "合格"
	else 
		set result := "不合格"
	end if;
	# 因为被返回了，所以不用 select result; 了
end;

# 调用, in 参数直接传入，out参数需要使用一个变量接收
call toScore(68, @result);
select @result;

----------------------------------------------------------------

# 把200分制改为100分制并输出 (inout 参数案例)
create procedure toScore(inout score double)
begin
	set score := score / 2; # 或 score * 0.5
end;
# 调用，inout 参数需要使用变量提交和接收
set @score := 78;
call toScore(@score);
select @score;

Case

case 是一个选择处理器

# 语法一
case case判断值
	when 值1 then 满足值1的sql
	when 值2 then 满足值2的sql ...
	else 都不满足条件的sql
end case;

# 语法二
case 
	when 值1条件判断 then 满足值1的sql
	when 值2条件判断 then 满足值2的sql
	else 都不满足条件的sql
end case;

使用案例

# 根据传入的月份，判断季节 (单case语法)
create procedure season(in month int)
begin
	declare result varchar(10);
	case 
		when month between 1 and 3 then
			set result := "第一季";
		when month between 4 and 6 then
			set result := "第二季"
		when month between 7 and 9 then
			set result := "第三季"
		when month between 10 and 12 then
			set result := "第四季"
		else 
			set result := "非法参数"
	end case;
	select concat("你输入的月份为：", month, "所属的季度为：", result);
end;

# 调用
call season(11);

while 循环

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的sql语句

# 规则
while 条件 do
	sql语句
end while;

# 使用 从1 + 到 100
create procedure add(out result int)
begin
	declare i int default 0;
	set result := 0;
	while (i <= 100) do
		set result := result + i;
		set i := i + 1;
	end while;
end;

repeat 条件退出循环

repeat 和 while 刚好相反，while 是有条件继续循环，而 repeat 则是满足条件退出循环

# 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足则续续下一次循环
repeat
	sql 语句
	until 条件 # 如果这里的条件为true则退出循环
end repeat;

使用案例：

# 案例：输入n，让1+到n,并得出值
create procedure num(in n int, out result int)
begin
    set result := 0;
    repeat
        set result := result + n; # 累加
        set n := n - 1; # n-1
    until n < 0  # 如果 n < 0 时退出循环
    end repeat;
    select result; # 既返回，也输出执行结果
end;

loop 循环

loop 是一个简单的循环，需要用户手动添加退出循环的指令，否则它会成为一个死循环

loop 可以配合以下两个语句使用：

leave: 配合循环使用，退出循环

iterate: 必须用在循环中，作为是跳过当前循环剩下的语句，直接进入下一次循环

# 使用规则
[begin_label:] loop
	sql语句
end loop [end_label];

leave label; # 退出指定标记的循环体
iterate label; # 直接进入下一个循环

使用案例：

# 例：输入 n 计算 1+到 n的值，当n = 20 时跳过累加20的操作
create procedure sum2(in n int)
begin
	declare total int default 0;
	num:loop
		# 使用if配合判断，条件成立时，使用 leave 进行退出
		if n < 0 then
			leave num;
		end if;
		
		# 当 n = 20 时，跳过当前的循环
		if n = 20 then
			set n := n - 1; # 这里也要让n-1 否则n永远停留在20
			iterate num;
		end if;
		
		set total := total + n;
		set n := n - 1;
	end loop num;
	select total;
end;

条件出错时的处理定义

在执行存储过程中，难免会有出现执行错误的情况出现，Mysql的存储过程提供了一种当执行出错时的固定操作方案，这样可以避免当存储过程执行出现错误时直接中止执行过程，而是能提供一种出错时能善后操作的处理方法

比如当使用死循环时读取出现数据错误时，不应直接报错中止执行，而是应该出错后对数据先做善后工作，再关闭执行过程，且不报错

# 声明出错处理定义
declare [handler_action动作] handler for [condition_value错误值1 [, condition_value错误值2, ...]] SQL语句;

# handler_action动作 的选项有两种：
continue: 忽略这一次的错误，继续往下执行程序
exit: 直接终止退出本次程序

# condition_value错误值 的选择有4种：
sqlstate 错误码：在执行时报错都会有一个错误码，即当执行时报出这个错误码时，会执行错误处理定义
sqlwarning: 所有以01开头的sqlstate错误码都会被激活
not found: 所有以02开头的sqlstate错误码都会被激活
sqlexception: 所有没有被 sqlwarning 和 not found 捕获的 sqlstate 代码都会被激活。

# 具体的错误码可以在以下网址具体查询
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

错误处理案例：

# 举例：使用一个死循环，当死循环中出现了报错时，给出提示，并退出程序，不作报错（本次使用 1 / 0 错误演示）
create procedure err()
begin
	# 定义一个固定的值，用于被除数
	declare num1 int default 1;
	# 定义一个递减的值，用于作为除数;
	declare num2 int default 100;
	
	# 定义一个当出现错误时，让mysql退出计算，并输出一句话
	declare exit handler for sqlstate 02000, 02001 select "程序出错，已停止运行。";
	# 只要定义了这个，当程序出现 02000, 02001 错误时，就会执行 【select "程序出错，已停止运行。"】 并退出
	
	# 创建一个必定会出错的死循环
	while true do
		# 直接计算 1 / 100递减,但总会有num2=0的时候，1 / 0 就会报错
		select  num1 / num2;
		# 让 num2 递减
		set num2 := num2 - 1;
	end while;

end;

游标（用于存储表结果记录的变量）

游标指的是 在存储过程中调用 select 查询出来的数据，存于一个变量中，但是一个变量它只能定义一个类型，如果我需要存储一整个查询表结果给到一个变量中时会出现报错。

这时我们就可以使用游标来保存整个查询的表结果，游标用于保存多类型数据的表数据，通过打开游标变量获取其中的数据来存储于普通变量中。

# 声明游标
declare 游标名称 cursor for 查询语句;

# 打开游标
open 游标名称;

# 获取游标记录
fetch 游标名称 into 变量 [, 变量2];

# 关闭游标
close 游标名称;

# 游标遍历

使用案例：使用游标，获取一个表中的部分数据，并插入到另一个表中。

# 把一个查询结果集存到游标中,使用游标创建一个新的表格数据
create procedure curcor()
begin
	# ！！注意：变量必须声明在游标之前，否则会报错
	# 声明变量，用于保存游标中的单独数据
	declare u_username varchar(10) default '';
	declare u_password varchar(255) default '';

	# 声明游标，并把查询的结果赋给游标变量中
	declare t_cursor cursor for select username,password from users;
	
	# 定义一个当出现错误时，让mysql退出插入，并退出游标
	declare exit handler for sqlstate 02000 close t_cursor;
	# 在本例中也可以使用 not found 作为报错标准
	# declare exit handler for not found close t_cursor;
	
	drop table if exists tb_new_users; # 先删除旧的表
	
	# 创建新表
	create table if exists tb_new_users(
        id int primary key auto_increment,
        username varchar(10),
        password varchar(255)
    ) commit '创建一个新的users表';
    
    # 运用游标：打开游标,打开游标后，就可以向游标中获取数据
    open t_cursor;
    # 运用游标：对游标进行遍历(死循环，若遍历失败后，有declare exit handler做错误处理)
    while true do
    	# 运用游标：获取游标一条数据，并把数据赋值给 u_username 和 u_password 中
    	fetch t_cursor into (u_username, u_password);
    	# 插入一条数据到新表中
    	insert into tb_new_users values (u_username, u_password);
    end while;
    
    # 运用游标：使用完游标后，应关闭游标
    close t_cursor;
	
end;

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是in类型的。存储函数用的比较少，因为功能比较局限，存储函数能用的存储过程的也能做。

# 创建规则
create function 存储函数名称([参数列表])
returns 返回的数据类型 [characteristic ...]
begin
	# sql 语句
end;

# 其中可选参数 characteristic 有三种情况：
deterministic: 相同的输入参数总是产生相同的结果
no sql : 不包含 sql 语句
reads sql data : 包含读取数据的语句，但不包含写入数据的语句

使用案例：通过存储函数计算1+到n的计算，并输出结果

create function sumfun(n int)
returns int deterministic # 定义相同的输入类型，也是相同的输出类型
begin
	declare total int default 0;
	
	while n > 0 do
		set total := total + n;
		set n := n - 1;
	end while;
	
	return total;
end;

# 调用函数
select sumfun(100);

触发器

触发器是与表有关的数据库对象，指在 insert / update / delete 之前或之后，触发并执行触发器中定义的sql语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

职用别名OLD和 NEW 来引用触发器中发生变化的记录内容，这与其它的数据库是相似的。现在触发器还只支持行级触发(即多少行被变动，就触发多少次触发器)，不支持语句级触发（即执行了一条sql语句触发一次触发器）。

触发器类型           NEW 和 OLD
insert触发器     NEW表示将要或者己经新增的数据，没有OLD,因为insert本身就没有OLD数据
update触发器     NEW表示修改之后或已经修改的数据，OLD表示修改之前的数据
delete触发器     OLD表示将要或已经删除的数据，没有NEW

创建触发器

# 规则
create trigger 触发器名字
before / after insert / update / delete
on 表名 for each row -- 行级触发器
begin
	触发器具体逻辑
end;

查看触发器

show triggers;

删除触发器

# 指定数据库的触发器，如果没有指定，则默认为当前数据库
drop trigger [schema_name.]trigger_name; 

使用触发器

案例：针对某个表的插入操作创建一个触发器，并把插入的数据记录在日志表中。

步骤：

1.创建一个用于保存日志的表

create table tb_user_log(
	id int primary key auto_increment comment "日志id",
    operation varchar(10) comment "操作类型",
    operate_time datetime comment "操作时间",
    operate_params varchar(100) comment "操作参数"
) comment "日志表";

2.创建一个触发器，指定 tb_users 表的插入时触发触发器

create trigger tb_users_trigger
after insert # 定义触发器的操作触发动作
on tb_users for each row
begin
	# 对表中的insert 操作触发，并获取其值，写入到日志表中
	insert into tb_user_log(operation, operate_time, operate_params) values ("insert", now(), 
      concat("操作插入，具体参数：id=", new.id, "username=", new.username, "password=", new.password)
     );
end;

根据上面的案例，可以举一反三处理 update 和 delete.

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（cpu，io, 内存）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更复杂。

按照锁的粒度分，有全局锁、表级锁、行级锁

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞

典型的使用场影是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

一般来说全局锁通常用在数据库备份上，备份前锁住所有表，以免出现数据一致性问题

# 开启全局锁
flush table with read lock;

# 并闭全局锁
unlock tables;

在InnoDB引擎中，我们可以在备份时在 mysqldump 上加上参数 --single-transaction 参数来完成不加锁的一致性数据备份

表级锁

每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在 MyISAM、InnoDB、BDB等存储引擎中

表锁

表共享读锁

表共享读锁，会对整张表进行锁定，锁表者可以读表，但不能写表，其它客户端也可以读表，但也不能写表

# 开启读锁
lock tables 表名 [, 表名2 ...] read

# 释放读锁
unlock tables; # 断开连接也会释放

表独占写锁

独占写锁，锁表者可以读表，也可以写表，但其它客户端既不能读表，也不能写表。

# 开启独写锁
lock tables 表名 [, 表名2 ...] write

# 释放读锁
unlock tables; # 断开连接也会释放

元数据锁

元数据锁加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。即当有数据写入时，表结构会被锁定

在DML写表数据时，会对表结构加上元数据读锁，而当表结构在修改时会对DML加上写锁

也就是说，当写表时，不允许改结构，当在改结构时，不允许写表。互斥性

意向锁

当一个A客户端对一个表开启了事务，并且对某一行进行写入操作时，该行会被锁定。在行锁定期间，另一个B客户端要求对表进行表锁，此时另一个客户端为了得到是否能锁表的可行性，需要对每一行记录进行查询加锁情况，这使得性能非常低

如果A客户端在对一行记录进行行锁时，顺便加上一个意向锁，那么B客户端在操作表锁之前，可通过查询意向锁快速获得是否能锁表的可行性。

意向共享锁（IS）

意向共享锁是指，我要对它进行写，只对写行锁定。但其它人可以对这一行进行读

意向共享锁与表锁共享锁（read）兼容，但与表锁排它锁（write）互斥

# 案例：客户端1中向表格中提供一个意向锁（即客户端1告诉表，我需要对这一行做读取锁定，所以共享锁住这一行）
select * from users where id = 1 lock in share mode; # 增加意向共享锁

# 客户端2中想向表格中进行共享读锁表（即其它人可读表不能写表的锁）
lock tables users read;
# 此时客户端2是可以进行锁表的，因为客户端1并没有对表进行独立锁定，所以客户端可以对表做共享锁定

--------------------

# 客户端2中想向表格中进行独立写锁表（即除了自己其它人都不能读不能写的锁）
lock tables users write;
# 此时客户端2的锁表行为会被阻塞，因为客户端1中有一行加作锁，其它人不能写这一行，因此客户端2也无法对全表进行独写锁。

意向排他锁 (IX)

意向排他锁与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥

当有一个客户端对一行做了排他锁时（即写锁），那么其它客户端即不能对表做只读锁，也不能对表做独写锁

# 案例：客户端1向表做一个修改操作，数据库自动为行添加一个排它锁
update users set username = "abc" where id = 1;

# 此时客户端2不管向表中申请共享锁，还是申请排他锁，都会被阻塞
lock tables users read;
lock tables users write;

# 但客户端2可以对表中申请意向锁
select * from users where id = 2 lock in share mode;
# 但是，如果客户端2申请的意向锁，正是客户端1排它锁被锁定的行时，即使客户端2申请的是共享意向锁，依然会阻塞
select * from users where id = 1 lock in share mode; # 阻塞

行级锁

行级锁每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实责的，而不是对记录加的锁。对于行级锁主要分为以下三类

1.行锁：锁定单个行记录的锁，防止其它事务对些行进舻update和delete.

2.间隙锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其它事务在这个间隙进行insert，产生幻读。

3.临键锁：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙区间。

注意：间隙是指，比如id是 1 2 3 4 5，1和5的间隙就是 2 3 4 。若id只有1和5，间隙锁定时，不能插入 id为2 3 4 的记录

行锁

各操作对行锁的行为

SQL 操作                                申请锁类型             说明
Insert                                 排他锁                自动加锁
Update                                 排他锁                自动加锁
Delete                                 排他锁                自动加锁
Select                                 不加锁                
Select lock in share mode              共享锁                需要手动在select 之后加 lock in share mode
Select for update                      他他锁                需要手动在select 之后加 for update

共享锁（S）

允许一个事务去读一行，阻止其它事务获得相同数据集的排它锁

即如果一个事务获得一个共享锁，其它事务依然可以获得共享锁，但不能获得排它锁

排他锁（X）

允许获取排他锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排他锁

即如果一个事务获得一个排他锁，其它事务既不能获取共享锁也不能获得排他锁

间隙锁 与 临键锁

间隙锁是指，表中并没有相应的id的记录，此时客户端向一个不存在的行进行修改时，mysql会对该区间的不存在的行进行锁定，但不包括自己行

# 案例：表中有id为 1,3,7,11,20 的记录。
# 此时我对id为 8 的记录进行修改
update users set name = "a" where id = 8;
# mysql 会对 7~11 两个id 记录之间的id值都会被锁定

# 客户端2 向表中插入一条数据，id为10,将会被阻塞（8,9,10都会被阻塞，而并非只阻塞8）
insert into users(id, name) values (10, "b"); # 会被阻塞 

临键锁行锁与间隙锁的结合，是指，它不但对自己结果行进行加锁，还会对结果行之后的不存在行也会加锁

# 案例：查找 id >= 10 的记录
# 此时mysql 除了会对 id=10 的行进行锁定，还会对 id > 10 的存在与不存在行也会锁定
select * from users where id >= 10 lock in share mode;

# 因为条件查询时，其它事务有可能会对 id > 10 的行进行插入，这就会引起幻读问题。
比如：表中只有id为 1,3,5,7,10,14,16。此时 id >= 10 查询出来的结果是 10 14 16 三条数据
如果此时其它事务插入一条 id = 12 的数据时，那实际的结果将会是 10 12 14 16，其中 id=12 的数据即为幻读数据
当锁定 id > 10 之后的行（不管存不存在），其它事务就不能往里插入数据，保证数据一致性。

同理：在查询非唯一索引的字段时，也会对自身行与其后的行进行锁定
比如，查询 age = 10 的数据，age是不唯一索引，mysql会对 age = 10 和 age > 10 到 age = 11 的行都会锁定，
这是因为，age 是不唯一索引，所以age 有可能会出现多个age=10的行，索引采用 B+树 **有序排列** ，因此mysql查询到第一条 age =10 的记录时，以防止B+树中还有其它 age=10 的节点,因此会对 age=10 到 age=11 之前的节点都锁定。
如果查询age >=10 条件，同时age=10之后没有节点了，mysql会锁定 age>10 后的无穷大空行。

MySQL 管理

原始数据库

MySQL数据库安装完成后，自带了四个数据库。

数据库
mysql               存储mysql服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
information_schema  提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限
performance_schema  为Mysql服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys                 包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

常用工具

mysql客户端工具
语法：mysql [option] [database]
选项：
	-u, --user=name      # 指定用户名
	-p, --passord[=pass] # 指定密码
	-h, --host=name      # 指定服务器ip或域名
	-P, --port=port      # 指定连接端口
	-e, --execute=sql    # 执行SQL语句并退出

mysqladmin 执行管理客户端程序
语法：mysqladmin --help 查看具体参数

mysqlbinlog 二进制日志管理工具
语法：mysqlbinlog [option] log-files1 log-files2 ...
选项：
	-d, --database=name              指定数据库名称，只列出指定的数据库相关操作
	-o, --offset=#                   忽略掉日志中的前几行命令
	-r, --result-file=name           将输出的文本格式日志输出到指定文件
	-s, --short-form                 显示简单格式，省略掉一些信息
	--start-datetime=date1 --stop-datetime=date2   指定日期间隔内的所有日志
	--start-position=pos1 --stop-position=pos2     指定位置间隔内的所有日志

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或索引
语法：mysqlshow [options] [db_name [table_name [col_name]]]
选项：
	--count     显示数据库及表的统计信息（数据库，表均可以不指定）
	-i          显示指定数据库或者指定表的状态信息
	
示例：
	# 查询每个数据库的表的数量及表中记录的数量
	mysqlshow -uroot -p1234 --count
	
	# 查询test库中每个表中的字段，及行数
	mysqlshow -uroot -p1234 test --count
	
	# 查询test库中book表的详细情况
	mysqlshow -uroot -p1234 test book --count

mysqldump 数据库备份恢复工具
语法：
mysqldump [options] db_name [tables] > sql文件地址
mysqldump [options] --database/-B db1 [db2 db3]
mysqldump [options] --all-database/-A
选项：
	-u, --user=name        用户名
	-p, password[=pass]    密码
	-h, --host=name        主机地址
	-P, --port=port        端口
输出选项：
	--add-drop-database    在每个数据库创建语句前加上 drop database 语句
	--add-drop-table       在每个表创建语句前加上 drop table 语句，默认开启（--skip-add-drop-table 关闭）
	-n, --no-create-db     不包含数据库的创建语句
	-t, --no-create-info   不包含数据表的创建语句
	-d, --no-data          不包含数据
	-T, --tab=name         自动生成两个文件，一个sql文件，创建表结构的语句，一个txt文件，数据文件

mysqlimport 数据导入工具，专门用于导入 txt 类的数据文件
语法： mysqlimport [options] db_name textfile1 [textfile2...]
示例：
	mysqlimport -uroot -p1234 test /tmp/city.txt
	# 如果要导入sql文件，可以使用以下方法
	mysql -uroot -p1234 source /tmp/city.sql

MVCC

当前读

通俗的讲，当前读就是直接读取当前表的现行数据。InnoDB的读取策略是【可重复】，意味着，普通 select 并不会锁定任何行数据，因此普通select查询所读取的是快照版本，除非select 启用了共享锁功能读。

# 在不使用共享锁时，innodb会对第一条select语句查询的数据进行快照，后续的查询都是读快照而不是读真正的表
begin;
select * from users;

# 再次查询依然从快照中读取，如果此刻有别的事务提交了修改，当前事务查询的依然是修改前的数据
select * from users where id = 1; # 依然是快照数据

# 若查询启用共享锁功能，则读的是当前表的数据而非快照数据
select * from users lock in share mode; # 如果别的事务发生提交，当前事务查询的数据会是最新的数据

# 对于 insert update delete 和 sellect for update 来说，都默认以当前读的模式进行读取表

快照读

简单的 select 就是快照读，读取的是记录数据的可见版本，有可能是历史数据，但是非阻塞读

隔离级别对快照读的区别

读已提交（Read Committed） : 每次select 都会生成一个快照读

可重复读（Repeatable Read） : 开启事务后第一个select语句才是快照读的地方

串行化（Serializable） : 快照读会退化为当前读

MVCC

全称 Multi Version Concurrency /control 多版本并发控制。指维护一个数据的多个版本，使得读操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView

记录中的隐藏字段：

每一个表格中innodb都会隐藏三个字段，分别是以下三个：

隐藏字段                           含义
DB_TRX_ID       最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_PTR     回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本
DB_ROW_ID       隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除

而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除

readView

ReadView 读视图是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id

字段                  含义
m_ids             当前活跃的事务ID集合
min_trx_id        最小活跃事务ID
max_trx_id        预分配事务ID，当前最大事务ID+1(因为事务ID是自增的)
creator_trx_id    ReadView创建者的事务id