MySQL操作
引擎介绍：
InnoDB
用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。
MySaim
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。
Memory
将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
关于引擎相关的sql语句：
查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";
查询当前数据库支持的存储引擎
mysql> show engines \G;
需要在建表时指定：
ENGINE=引擎名
引擎在配置文件中指定：
#my.ini文件
[mysqld]
default-storage-engine=INNODB
别名：
- 文件夹【数据库】
- 文件【表】
- 数据行【行】
操作文件夹【数据库】
show databases ；# 查看所有数据库 create database 文件名 [default charset utf8]；# 创建数据库#加上default charset utf8 就可以创建包含中文的数据库； # 容错创建 create databases if not exists 库名 [default charset utf8] # 表示如果没有这个数据库进行创建 drop database 文件名 ;# 删除数据库 # 容错删除 drop database if exists 数据库名称 ; # 容错删除输入库 如果存在删除 如果不存在不删除 use 数据库；# 进入数据库 select database() # 查看当前所在的数据库位置
备份数据库：数据表结构+数据
mysqldump -u 用户名 -p 需要备份的数据库名>导出文件路径.dump (其他后缀也是可以的)
备份数据库：数据表结构
mysqldump -u 用户名 -d -p 需要备份的数据库名>导出文件路径.dump (其他后缀也是可以的)
导入数据库：
mysqldump -u 用户名 -p 导入数据库的文件名<导出文件路径 #导入前需要先创建数据库 或已登录 可以直接用 source 导出文件路径
操作文件【表】
show tables；# 查看当前库的所有的表； show tables from 库名; 查看其他库的所有表 show create table 表名;查看表示如何创建的(以表格的形式显示)； \G；将表格显示形式反转； desc 表名；#查看表的信息； 临时表：() as name；#在已有的表中提取一段数据加上括号as name就是一个临时表； select 后可以跟一个动态结果(取得列只有一列)
操作表相关
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件],
[表级约束]
) [ENGINE=引擎][DEFAULT CHARSET=utf8];
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
4.加入default charset=utf8；创建默认utf-8的表
# 表级约束除了非空,默认其余的都支持
# 语法：
constraint 约束名 约束类型(字段名)
# create 容错模式
create table if not exists 表名...
# 表的复制
1.复制表的结构
CREATE TABLE 新表 LIKE 旧表; # 复制旧表 仅结构
2.复制表的结构+数据
CREATE TABLE 新表 (select * from 旧表)
3.进阶 复制部分表结构
CREATE TABLE 新表 (select 字段1,字段2... from 旧表 where 0) # where 条件全为false 不复制数据
alter table 表名 auto_increment=num修改(自增初始值)
show session variables like 'auto_inc%';#(查看步长 会话级别)
set session auto_increment_increment=num;#(修改步长 会话级别)
set session auto_increment_offset=num；#(修改起始值 会话级别)
show global variables like 'auto_inc%';#(查看步长 全局级别)
set global auto_increment_increment=num;#(修改步长 全局级别不推荐使用)
set global auto_increment_offset=num;#(修改起始值 全局级别不推荐使用)
delete from 表名；#清空表 不会清空约束条件自增 下次插入会延续自增；
truncate table 表名；#清空 会清空自增 速度快；
drop table 表名；#删除表；
# 改表名
alter table 表名 rename to 新表名;
添加列：alter table 表名 add 列名 类型; # 默认是添加到最后
alter table 表名 add 列名 类型 [first|after 字段名]; first添加到首列,after 字段名添加到字段名之后
删除列：alter table 表名 drop column 列名
修改列：
alter table 表名 modify column 列名 类型; -- 类型
alter table 表名 change 原列名 新列名 类型; -- 列名，类型
添加主键：
alter table 表名 add primary key(列名);
删除主键：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
添加外键：
alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段) [ON DELETE CASCADE|SET NULL];(从表 被限制的表) [ON DELETE CASCADE] 代表级联 删除主表 从表记录也同时删除 [ON DELETE SET NULL] 代表级联置空,删除主表从表外键字段置空
删除外键：
alter table 表名 drop foreign key 外键名称
修改默认值：
ALTER TABLE 表名 ALTER 列名 SET DEFAULT num;
删除默认值：
ALTER TABLE 表名 ALTER列名 DROP DEFAULT;
插入数据
1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入其他表的内容
语法：
INSERT INTO 表名(字段1,字段2,字段3…字段n)
SELECT 字段1,字段2,字段3…字段n FROM 表2
WHERE …;
5.使用set插入 不支持批量插入并且不支持子查询条件
语法：
INSERT INTO 表名
SET 字段=value1,字段2=value2;
删除更新数据
delete from 表名 [where 条件]；#把满足条件的内容删除 # delete 多表级联删除 delete 别名1,别名2 from 表1 别名 inner|left|join 表2 on 连接条件 where 条件 truncate table 表名；#清空表 会清空自增 速度快 没有返回值且不能回滚； update 表名 set 需要修改的列名=需要修改的内容,需要修改的列名=需要修改的内容 [where 条件] ;#把满足条件的内容修改成需要修改的内容； # update修改多表 update 表名 inner|left|right join 表名2 on 连接条件 set 列=值 where 条件
查询数据
select *(*代表所有 或者单列的列名)[as 别名][多加额外的列，当列所有数据相同] from 表名[where]；#查看表的数据 (*查看所有 ,或者单列出来的相应名称对应的数据 可多选) *select 后可以跟 列名，常量，表达式，函数 select的查询结果(结果列唯一)查询结果的条件可以从外层的行数据查找； 单表查询语法 SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数
子查询
# 出现在其他语句中的select语句，称为子查询或内查询
# 出现在外部的查询语句，称为主查询或外查询
# 分类
# 按子查询出现的位置
# select后面 - 仅仅支持标量子查询
# from后面 - 支持表子查询
# where或having后面 - 标量子查询、列子查询、行子查询
# exists后面（相关子查询）- 表子查询 # exists如果子查询有值返回1否则返回0
# 按结果集的行列数不同
# 标量子查询（结果集只有一行一列）
# 列子查询（结果集只有一行多列）
# 行子查询（结果集有一行多列）
# 表子查询（结果集一般为多行多列）
# 特点：
# 子查询放在小括号内
# 子查询一般放在条件的右侧
# 标量子查询，一般搭配着着多行操作符使用
# 使用跟多的操作符
# IN/NOT IN 等于列表中的任意一个
# ANY/SOME 和子查询返回的某一个值比较
# ALL 和子查询返回的所有值比较
关键字执行的优先级
from 1.找到表：from； where 2.拿着where指定的约束条件，去文件/表中取出一条条记录； group by 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组； having 4.将分组的结果进行having过滤； select 5.执行select； distinct 6.去重；select distinct 列1, 列2 from 表 # distinct必须在前面 order by 7.将结果按照条件排序 order by limit 8.限制结果的显示条数
where 约束
1. 比较运算符：= > < >= <= <> != is <=> # is或<=>用来判断是为为NULL <=>也可以判断非NULL的值 等价于=
2. between num1 and num2 值在num1到num2之间
3. in(num1,num2,num3) 值是num1或num2或num3；in后也可以跟select其他表的内容；
4.
like 列名 'egon%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
group by 分组
单独使用GROUP BY关键字分组
SELECT 列名 FROM 表名 GROUP BY 列名;
注意：我们按照列名字段分组，那么select查询的字段只能是列名，想要获取组内的其他相关信息，需要借助函数
GROUP BY 分组1,分组2 # 支持多个字段分组 相当于联合索引
GROUP BY关键字和GROUP_CONCAT()函数一起使用；
将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
GROUP BY与聚合函数一起使用
聚合函数：
COUNT(name)；#计数 MAX(name)；#最大值 MIN(name)；#最小值 AVG(name)；#平均值 SUM(name)；#和 # AVG SUM 一般用于处理数值型 # 聚合函数忽略NULL值 # 可以和distinct搭配使用 例如 count(distinct xxx) # count(*) 效率略高于count(1) # 和聚合函数一同查询的的字段要求是group by后的字段
HAVING过滤
HAVING与WHERE不一样的地方在于!!!!!! #！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
ORDER BY 查询排(可以指定多个列名进行不同的排序，按照先后顺序)
ORDER BY 单个字段、多个字段、表达式、函数、别名；#按照列名排序 ORDER BY 列名 ASC；#按照类名从小到大排序 ORDER BY 列名 DESC；#按照类名从大到小排序
多表连接查询
# 等值连接 SELECT 字段列表1,字段列表2 FROM 表1,表2 WHERE 表1.字段=表2.字段 # 非等值连接 where 条件卡控 # 自连接(适用于一张表有相同数据关联) SELECT 字段列表1,字段列表2 FROM 表1,表1 WHERE 表1.字段=表1.字段 #重点：外链接语法 SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;(left join 左边的全部显示 right join 右边的全显示 inner 将出现null时的整行隐藏) #补充:cross CROSS JSON #相当于笛卡尔积 两表两两相结合 #上下连表 SELECT 字段列表 FROM 表1 UNION/UNION ALL 表2 ON 表1.字段 = 表2.字段;(union 自动去重 union all 不去重)
LIMIT 限制查询的记录数
LIMIT num；#查询num条数； LIMIT num1,num2; #从第num1开始，即先查询出第num1+1条，然后包含这一条在内往后查num2条； # 起始索引从0开始
约束条件：
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：
NOT NULL ：非空约束，指定某列不能为空；
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
DEFAULT:默认
我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
UNIQUE : 唯一约束，指定某列或者几列组合不能重复
CHECK:检查约束 [mysql 不支持 但不报错]
PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。
AUTO_INCREMENT：#
约束字段为自动增长，被约束的字段必须同时被key约束
FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性，如果主键是多个的时候可以关联多个；
foreign key(需要关联的字段名)
references 表名(要被关联的字段名)
补充：Mysql中的`+`仅作为运算符使用，
如果两边都为数值型，则进行加法运算，
如果一方为数值型，另一方为字符型，则试图进行转化，如果转化成功则进行加法运算，如果失败则转化为0继续进行加法运算，
如果有一方为NULL则结果为NULL
数据类型：
# 选择原则
# 所选择的类型越简单越好,能保存数值的类型越小越好
bit[(M)]
二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
整数部分[m] 指的是显示为数 要配合[zerofill]一起使用 如果使用[zerofill]自动升级为无符号类型且多出来的位数用0占位
tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127.
无符号：
0 ～ 255
特别的： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615
双精度定点数如果省略[m]则默认为(10,0) 不能保存小数,而float和double省略则适应所有
decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
特别的：对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。 decimal(65,30) 表示35个小数点前30个后
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符号：
0
1.175494351E-38 to 3.402823466E+38
**** 数值越大，越不准确 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
有符号：
0
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 数值越大，越不准确 ****
char (m)
char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。 查找快，char列最好放在前面，速度快，因为总是m个字符。
PS: 即使数据小于m长度，也会占用m长度
char的(m)可以省略默认是1
varchar(m)
varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
text
text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 ? 1)个字符。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 ? 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.
binary
varbinary
# 类似于char和varchar,不同的是它们包含二进制字符串而不包含非二进制字符串
enum
枚举类型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
集合类型
A SET column can have a maximum of 64 distinct members.
示例：
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME(8个字节)
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP(4个字节)
# 和实际时区有关,更能反映实际的日期,而datetime则只能反映出插入时的当地时区、timesamp的属性受mysql版本和sqlmode影响很大
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
变量
# 变量
# 系统变量
注意:如果是全局级别需要加GLOBAL,如果是会话级别则需要加SESSION,如果不写默认为SESSION
1.查看所有的系统变量
SHOW VARIABLES; # 查看所有变量
SHOW GLOBAL|[SESSION] VARIABLES; # 查看全局变量或会话变量 session可以忽略不写
2.查看满足条件的部分系统变量
SHOW GLOBAL|[SESSION] like '%关键字%' # SESSION可以忽略不写
3.查看指定的某个系统变量的值
SELECT @@GLOBAL|[SESSION].系统变量名;
4.为某个系统变量赋值
SET GLOBAL|[SESSION] 系统变量名=值;
SET @@GLOBAL|[SESSION].系统变量名=值
# 全局变量
# 作用域：服务器每次启动将所有的全局变量赋初始值,针对于所有的会话(连接)有效,但不能跨重启
# 会话变量
# 作用域：仅仅针对当前会话(连接)有效
# 自定义变量
# 用户变量
# 作用域：针对当前会话(连接)有效,同于会话变量的作用域
1.声明并初始化
SET @用户名变量=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
2.赋值 通过SET或SELECT
SET @用户名变量=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
# 局部变量
# 作用域：仅仅在定义它的begin end中有效
# 应用在begin end 中的第一句话
1.声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
2.赋值
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;
SELECT 字段 INTO 局部变量名 FROM 表;
3.使用
SELECT 局部变量名
用户管理：
查看用户信息 use mysql; select host,user,password from user; 创建用户： create user '用户名'@'IP' identified by '密码'；# 在IP地址可以使用%站位，例如 192.168.1.% 只要是192.168.1的机器都可以登录该用户 或者IP地址为 % 代表所有的机器都可以登录该用户； 删除用户： drop user '用户名'@'IP地址'； 修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址'; 修改密码 set password for '用户名'@'IP地址' = Password('新密码')
授权管理：
show grants for '用户'@'IP地址' ；# 查看权限 grant 权限 on 数据库/表(*号代表所有 (数据库.*) 该数据库下所有的表) to '用户名'@'IP'(用户) ；#给用户授权 revoke 权限 on 数据库.表 from '用户'@'IP地址'；# 取消权限
如果root用户授予权限后无法远程登录 # 远程连接设置哦设置所有库，所有表的所有权限，赋值权限给所有ip地址的root用户 # mysql > grant all privileges on *.* to root@'%' identified by 'password';
权限对应表：
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用
对于权限：
对于目标数据库以及内部其他： 数据库名.* 数据库中的所有 数据库名.表 指定数据库中的某张表 数据库名.存储过程 指定数据库中的存储过程 *.* 所有数据库 用户名@IP地址 用户只能在改IP下才能访问 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意) 用户名@% 用户可以再任意IP下访问(默认IP地址为%) 特殊的： flush privileges，将数据读取到内存中，从而立即生效。
进阶
事物条件
case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1; #ps 如果是值不加分号 否则case语句结束 when 常量2 then 要显示的值2或语句2; ... else 表示默认的值n或语句n; end case when 情况条件1 then 满足结果 when 情况条件2 then 满足结果 else 不满足结果 end IF( expr1 , expr2 , expr3 ) expr1 的值为 TRUE，则返回值为 expr2 expr1 的值为FALSE，则返回值为 expr3
流程控制结构
顺序结构
程序从上往下依次执行
分支结构
============== 1.IF函数 # 时间简单的双分支 SELECT IF(表达式1,表达式2,表达式3); # 如果表达式1成立返回表达式2的值,否则返回表达式3的值 ============== 2.CASE结构 # 特点 # 可以做为表达式,嵌套在其他语句中使用,可以放在任何地方, BEGIN END中或BEGIN END的外面 # 可以做为独立的语句去使用,只能放在BEGIN END这中 # 如果WHERE中的值满足或条件成立,则执行对应THEN后面的语句,并结束case # case作为表达式 case 变量|表达式|字段 when 常量1 then 要显示的值1或语句1; #ps 如果是值类型不加分号 否则case语句结束 when 常量2 then 要显示的值2或语句2; ... else 表示默认的值n或语句n; end; case when 情况条件1 then 满足结果 when 情况条件2 then 满足结果 else 不满足结果 end; #demo SELECT CASE WHEN 1 THEN 1 ELSE 2 END ; # 作为独立的语句 只能放在bengin end中 case 变量|表达式|字段 when 常量1 then 要显示的值1或语句1; #所有都要加分号; when 常量2 then 要显示的值2或语句2; ... else 表示默认的值n或语句n; end case; # end后要加case case when 情况条件1 then 满足结果 when 情况条件2 then 满足结果 else 不满足结果 end case; # demo delimiter // CREATE PROCEDURE test_case ( IN score INT ) BEGIN CASE WHEN score > 90 THEN SELECT "A"; WHEN score > 80 THEN SELECT "B"; WHEN score > 60 THEN SELECT "C"; ELSE SELECT "D"; END CASE; END // delimiter; ============== 3.IF结构 # 实现多重分支 # 只能应用在BEGIN END 中 IF 条件1 then 语句1; ELSEIF 条件2 then 语句2; .... [ELSE 语句n;] END IF; # demo:
delimiter // CREATE FUNCTION test_if ( score INT ) RETURNS CHAR BEGIN IF score > 90 THEN RETURN "A"; ELSEIF score > 80 THEN RETURN "B"; ELSEIF score > 60 THEN RETURN "C"; ELSE RETURN "D"; END IF ; END // delimiter ;
循环结构
# 必须在BEGIN END中
# 分类 WHILE、LOOP、REPEAT
# 循环控制
# iterate类似 contine 结束本次循环继续下一次
# leave 类似于break 跳出当前循环
# 标签的作用是用于控制语句跳出循环使用的
# 先判断在执行
[tag标签:] WHILE 循环条件 do
循环体;
END WHILE [tag标签];
# demo 批量插入
delimiter \\
CREATE PROCEDURE test_While1 ( IN NUM INT )
BEGIN
WHILE
NUM > 0 DO
INSERT INTO major ( majorname )
VALUES (NUM);
SET NUM = NUM - 1;
END WHILE;
END \\
delimiter;
# 批量插入2 带条件语句
CREATE PROCEDURE test_While2 ( IN NUM INT ) BEGIN
a :
BEGIN
a :
WHILE
NUM > 0 DO
INSERT INTO major ( majorname )
VALUES
( NUM );
SET NUM = NUM - 1;
IF
NUM =5 THEN
SET NUM = NUM - 1;
ITERATE a;
ELSEIF NUM = 3 THEN
LEAVE a;
END IF;
END WHILE;
END \\ delimiter;
# 死循环 需要依靠leave跳出
[tag标签:] LOOP
循环体;
END LOOP [tag标签];
# 先执行后判断
[tag标签:] REPEAT 循环条件
循环体;
until 结束循环的条件
END REPEAT [标签tag];
视图： (临时使用 不常用)
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
# 具有以下特点的视图不允许更新
# 1.包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
# 2.select中包含子查询
# 3.join
# 4.from一个不能更新的视图
# 5.where子句的子查询引用了from子句中的表
创建视图：
create view 视图名称 as sql语句
删除视图：
drop view 视图名称
修改视图：
create or replace view 视图名称 as sql语句 # 如果有视图则修改,没有则创建
alter view 视图名称 as sql语句
使用视图
select * from 视图名称,视图名称2... # 可批量删除
# 视图和表的区别
# 视图
只保存了sql逻辑
# 表
保留了数据
触发器：#数据级别做的操作，通过代码也可以实现
delimiter 新符号 #更改'；'结尾 遇到新符号结束sql语句； NEW表示即将插入的数据行 OLD表示即将删除的数据行，用在delete和update 创建触发器：(each row 每一次执行都会触发触发器) ***触发器中执行的sql 语句可以使用new/old关键字 new表示即将插入的数据行,old表示即将删除的数据行 在触发器中需要更改delimiter delimiter // # 表示以//为终止符 ...触发器 delimiter ; # 改回以;为终止符 在插入之前 create trigger 触发器名称 befforer insert on 表名 for each row begin 执行语句;#需要delimiter更改'；'结尾 end 在插入之后 create trigger 触发器名称 after insert on 表名 for each row begin 执行语句;#需要delimiter更改'；'结尾 end 在删除之前 create trigger 触发器名称 befforer delete on 表名 for each row begin 执行语句;#需要delimiter更改'；'结尾 end 在删除之后 create trigger 触发器名称 after delete on 表名 for each row begin 执行语句;#需要delimiter更改'；'结尾 end 在更新之前 create trigger 触发器名称 befforer update on 表名 for each row begin 执行语句;#需要delimiter更改'；'结尾 end 在更新之后 create trigger 触发器名称 after update on 表名 for each row begin 执行语句;#需要delimiter更改'；'结尾 end 删除触发器： drop trigger 触发器名称
函数：#会导致sql运行变慢
select 函数()；#执行函数
内置函数：
length(str) # 获取str的字节个数
CHAR_LENGTH(str)#计算字符串的长度；
concat(str1,str2...)#字符串拼接；
concat_ws(符号,str1,str2...)#(自定义链接字符)字符串拼接；
conv(待转换字符,现在的进制,转化后的进制)#进制转换
FORMAT(X,D)#将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替换位置起始位置
len：替换的长度
newstr：新字符串
IFNULL(expr1,expr2) # 判断expr1是否为NULL 如果为NULL返回expr2 可以为常数等
ISNULL(expr1) # 判断expr1是否为NULL如果为NULL返回1否则返回0
特别的：
如果pos超过原字符串长度，则返回原字符串
如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)#返回字符串 str 中子字符串的第一个出现位置。
LEFT(str,len)#返回字符串str 从开始的len位置的子序列字符。
LOWER(str)#变小写
UPPER(str)#变大写
LTRIM(str)#返回字符串 str ，其引导空格字符被删除。
RTRIM(str)#返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)#获取字符串子序列 索引从1开始
LOCATE(substr,str,pos)#获取子序列索引位置
REPEAT(str,count)#返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。
TRIM([remstr FROM] str) # remstr 填想要去除的内容 FROM str 去除两边想要去除的内容 如果不填 默认是去空格
LPAD(str,len,padstr) # 如果str长度不为len 则用padstr进行左填充(前部填充)；如果长度大于len则截取长度为len
RPAD(str,len,padstr) # 与LPAD类似 向右填充(后部填充)
REPLACE(str,from_str,to_str)#返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)#返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)#从字符串str 开始，返回从后边开始len个字符组成的子序列
SPACE(N)#返回一个由N空格组成的字符串。
------数学相关
ROUND(x,D)# 四舍五入 如果D不填 默认保留0位小时，D为小数点保留位数
CEIL(x) # 向上取整，返回>=该参数的最小整数
FLOOR(x) # 向下取整
TRUNCATE(x,D) # 截断 保留D位小数
MOD(x,y) #取摸
------日期相关
NOW()#现在的日期+时间
CURDATE()#获取当前日期 不包含时间；
CURTIME() #获取当前时间 不包含日期
YRER(data) #指定获取年
MONTH(data) #获取指定月
DAT(data) #获取指定日
DATE_FORMAT()# 将日期转化为字符串；
STR_TO_DATE() # 将日期格式的字符转化成指定格式的日期
DATEDIFF(date1,tate2) # 两日期求差
------其他
version() # 当前数据库服务器的版本
database() # 当前打开的数据库
user() # 当前用户
password("字符串") # 返回该字符的密码形式
md5("字符串") # 返回md5加密密文
函数官方网址：
https://dev.mysql.com/doc/refman/5.7/en/functions.html
自定义函数：(有返回值)
# 函数体仅有一句话,则begin end可以省略
# 函数有且仅有一个返回,适合做处理数据后返回一个结果
delimiter \\
create function 函数名(
参数1 数据类型,
参数2 数据类型)
returns 数据类型(#返回的数据类型)
BEGIN
declare 变量 数据类型;(声明变量为什么数据类型)
set 变量 =操作;
return(变量);
END \\
delimiter ;
delimiter \\
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END \\
delimiter ;
-- 相当于：
-- def f1(i1,i2):
-- num=i1+i2
-- return(num)
存储过程：
#存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行 # 可以理解为函数的进阶版,对结果集合返回值进行复杂操作
# 存储过程
# 一组预先编译好的SQL语句的集合,理解成批处理语句
# 提高代码的重要性
# 简化操作
# 减少了编译次数并且减少了和数据库服务器的链接次数,提高了效率
# 补充 如果是批量插入等记得要在begin后设置不自动提交
SET autocommit = 0;
# 在批量只想完成后 手动提交
COMMIT; # 提交
1.-- 无参数存储过程-- 创建存储过程
#* delimiter 符号 更改mysql结束符
delimiter //
create procedure 存储过程名()
begin
执行的sql语句 # 如果执行的sql语句仅有一句话,则begin end可以省略
end //
delimiter ;
# 执行存储过程
call 存储过程名()
2.-- 有参数存储过程-- 创建存储过程
对于存储过程，可以接收参数，其参数有三类：
in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值
delimiter //
create procedure 存储过程名(
in/out/onout 参数名 数据类型(长度)，
in/out/onout 参数名 数据类型(长度)，
...
)
begin
执行的sql语句 # 使用变量赋值 例如 into赋值给out或inout变量用于返回
end //
delimiter ;
执行存储过程
如果参数是 out 或者 inout是需要传变量需要先定义
# 参数定义可以看上面
set @变量名1 = 初始值
set @变量名2 = 初始值
call 存储过程名(num,@变量名1,@变量名2)
select @变量名1 @变量名2 (#拿回out和inout的结果)
事务
事务
# 事务的隔离级别
read uncommitted; # 出现脏读,幻读,不可重复读
read committed; # 避免脏读,会出现幻读和不可重复读
repeatable read; # 避免脏读,不可重复读会出现幻读 mysql默认级别
serializable; 避免以上所有,效率慢 串行
# 查看隔离级别
select @@tx_isolation;
# 设置隔离级别
set session|global transaction isolation lever 隔离级别
# 显式事务流程:
# 1.开启事务
set autocommit=0; # 关闭系统自动提交功能
start transaction; # 可选
# 2.编写事务中的sql语句
# ...
# 3.提交事务或回滚
commit; # 提交
rollback; # 回滚
# 配合rollback有一个临时保存savepoint name # 设置保存点
savepoint name # 设置保存点
rollback name # 回滚到保存点的状态
delimiter \\
create PROCEDURE 事务名(OUT 参数名 数据类型)
BEGIN
DECLARE exit handler for sqlexception #声明如果出现异常执行以下代码
BEGIN
参数名=num;
rollback;#关键字 事务回滚
END;
START TRANSACTION;#开始事务
数据操作
COMMIT;#提交数据
参数名 = num2;
END\\
delimiter ;
锁
锁 # innodb支持两种锁(行锁,表锁) mysaim只支持表锁
# 锁是计算机协调多个进程或线程并发访问某一资源的机制。
# 在数据库中,除传统的计算资源(如CPU,RAM,1O等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
# 锁的分类：
1.对数据操作的类型区分-分为读锁、写锁
# 读锁会阻塞写,但是不会阻塞读;而写锁则会把读和写都堵塞
读锁(共享锁)：针对同一份数据,多个读操作可以同时进行而不会互相影响。
写锁(互斥锁)：当前写操作没有完成前,它会阻断其他写锁和读锁。
2.对数据操作的粒度分-分为表锁、行锁、页锁
表锁(偏读)
# 偏向MyISAM存储引擎,开销小,加锁快:无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
#两个变量说明如下：
Table_locks_immediate:产生表级锁的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1;
Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1),此值高则说明存在着较严重
# 查勘表上加过的锁
SHOW OPEN TABLES;
# 手动增加表锁
LOCK TABLE 表名 read|write, 表名2 read|write
# 释放表锁
UNLOCK TABLES;
行锁(偏写)
# 偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。
# innodb天生支持行锁
# 需要先将自动提交关闭
set autocommit=0
#执行完操作手动提交
commit
# 无索引行锁升级为表锁
show status like 'innodb_row_lock%'
#两个变量说明如下：
Innodb_row_lock_current_wait:当前正在等待锁定的数量
Innodb_row_lock_time:从系统启动到现在锁定总时间长度 ！
Innodb_row_lock_time_avg:每次等待所花平均时间 ！
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_wait:系统启动后到现在总等待的次数 ！
# 间隙锁的危害
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时, InnoDB会给符合条件的已有数据记录的索 项加锁;对于键值在条件范围内但并不存在的记录,叫做"间隙(GAP)" ,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)
因为Query执行过程中通过过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值并不存在间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
# 单行锁定
终端:
begin # 开始
select .... for update; # for update 加锁
commit # 解锁
pymysql:
cursor.execute("sql for update")
django:
from django.db import transaction#引入事务
with transaction.atomic():#如果以下缩进语句中出现报错则启动事务进行数据回滚操作
model.User.object.all().for_update()
游标
游标
delimiter //
create procedure 存储过程名()#创建一个存储过程
begin
declare 变量名1 数据类型; #声明变量名1
declare 变量名2 数据类型(长度); #声明变量名1
DECLARE 变量名3 INT DEFAULT FALSE;#声明变量名3默认值为false 此变量用户判断循环数据是否为空
DECLARE 游标名称 CURSOR FOR 列名 from 表;#声明游标去表中找列
DECLARE CONTINUE HANDLER FOR NOT FOUND SET 变量名称3 = TRUE;#声明当游标中没有值得时候 变量名称3=true
open 游标名称;#打开游标
xxoo(随便写): LOOP#开始循环
fetch 游标名称 into 列名;#从游标中获取列名
if 变量名称3 then #如果变量名称=True 执行结束循环
leave xxoo;#结束循环
END IF;
需要执行的sql语句
end loop xxoo;#终止循环
close 游标名称;#关闭游标
end //
delimter ;
执行游标
call 存储过程名()
动态执行（防SQLZ注入）
# 动态执行SQL(防SQL注入)
delimiter \\
creater procedure 存储过程名(
in 变量名1 数据类型(数据长度)
in 变量名2 数据类型(数据长度)
)
begin
set @变量名2=变量名2;
prepare prod from
execute prod using @变量名2;
deallocate prepare prod;
end \\
delimiter ;
索引
#索引，是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
-a.额外的文件保存特殊的数据结构
-b.查询快；插入更新删除慢
-c.命中索引(创建索引要正确使用索引)
索引种类：(某种格式存储)
hash索引：#不是很常用
查找单值快
查找范围慢——因为hash索引存储是无序的查找范围时会很慢
二叉树
- 搜索效率不足(一般来说,在树结构中数据处的深度决定着它的搜索时IO次数)
- 节点数据内容太少(每个磁盘块节点/顶点保存的关键字数量太小了,没有很好的利用操作系统和磁盘的数据交换特性和磁盘预读能力(空间局部性原理))
btree索引(多路平衡查找树)
- 多路降低了二叉树树的高度,降低了搜索时IO次数
- 节点数据可以保存多个
b+tree
- B+节点关键字搜索采用闭合区间
- B+非叶节点不保存数据相关信息,只保存关键字和子节点的引用
- B+关键字对应的数据保存在叶子节点中
- B+叶子节点是顺序排序的,并且相邻节点具有顺序引用的关系
主键索引: # 加速查找,不重复，非空
普通索引：# 加速查找
在创建表时创建：
index 索引名(列名)
创建普通索引
create index 索引名 on 表名(列名)；#类名(num)可以指定索引的长度
查看索引：
show index from 表名；
删除索引：
drop index 索引名 on 表名；
唯一索引：#加速查找 不能相同
在创建表时创建：
unique 索引名(列名)
创建唯一索引
create unique index 索引名 on 表名(列名)；
查看索引：
show index from 表名；
删除索引：
drop unique index 索引名 on 表名；
联合索引/联合唯一索引：#对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并
创建联合索引/联合唯一索引
create index/unique index 索引名 on 表名(列名1,列名2)；
联合索引/联合唯一索引 遵守最左前缀匹配
列名1 and 列名2 --使用索引
列名1 --使用索引
列名2 --不适用索引
查看索引：
show index from 表名；
删除联合索引/联合唯一索引：
drop index/unique index 索引名 on 表名；
名词解释：
覆盖索引：
在索引文件中直接获取数据
例如：select 数据 from 表 where 带有索引的条件；
索引合并：#索引合并没有组合索引效率高
把多个单列索引合并使用
例如：select * from 表 where 带有索引1的条件，带有索引2的条件；
命中索引：(以下情况会不走索引导致变慢)
-like；
-使用函数；
-or；#特别的：当or条件中有未建立索引的列才失效；#or未建立索引的列and索引的列不会失效；
-类型不一致；#如果列是字符串类型，传入条件是必须用引号引起来,否则失效；
-!=；#特别的：如果是主键，则还是会走索引；
->；#特别的：如果是主键或索引是整数类型，则还是会走索引；
-order by；#当根据索引排序时候，选择的映射如果不是索引，则不走索引；特别的：如果对主键排序，则还是走索引
-最左前缀；
其他注意事项：
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
补充:
- 内存代替表,如:性别等
- 读写分离(copy数据库编程两个一模一样的库进行配置同步后进行读写分离减轻压力)
- 分库
- 分表
- 水平分表 (例如 onetoone)
- 垂直分表 (例如 时间切分)
- 命中索引
- 如果取一条数据时,使用limit = 1
# 小技巧
1.左连接加右索引；右连接加左索引；使用可以适当的对调
2.尽可能减少join语句中nestedloop的循环总次数；“永远用小结果集驱动大结果集”
3.优先优化NestedLoop的内层循环
4.保证Join语句中被驱动表上Join条件字段已经被索引
5.当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝啬JoinBuffer的设置
# 如何避免索引失效
1.全值匹配我最爱
2.最佳左前缀法则 - 如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换) ,会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致),减少select *
6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
7.is null,is not null也无法使用索引
8.like以通配符开头(%oab.)mysq索引失效会变成全表扫描的操作 - 如何解决like '%字符串%'时索引不被使用的方法 —— 利用覆盖索引解决
9.字符串不加单引号索引失效
10.少用or,用它来连接时会索引失效
#
定值、范围还是排序，一般order by 是给个范围
group by基本上都会进行排序，会有临时表产生
# 一般性建议
对于单键索引,尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
# ORDER BY优化策略
1.ORDER BY子句尽量使用index方式排序,避免使用FileSort方式排序
1.ORDER BY使用索引最左前列
2.使用WHERE子句与ORDER BY子句条件组合满足索引最左前列
2.如果不在索引列上,FileSort有两种算法:Mysql就要启动双路排序和单路排序
1.双路排序
MySQL4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order列,对它们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段,在Buffer进行排序,再从磁盘取其他字段
2.单路排序
从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中了。
# 单路排序的问题
在sort bufferp,方法B比方法A要多占用很多空间,因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort _buffer容量大小的数据,进行排序(创建tmp文件,多路合并) ,排完再取取sort_buffer容量大小,再排 从而多次I0
本来想省一次I0操作,反而导致了大量的I0操作,反而得不偿失。
# 优化策略
1.增大sort_buffer_size参数设置
2.增大max_length_for_sort_data参数设置
3.不要使用 select * 多余的字段会占用buff
# 总结
MySql两种排序方式:文件排序或扫描有序索引排序
Mysgl能为排序与查询使用相同的案引
# group by
# 与order by一致
执行计划
# Explain能干什么
# 表的读取数据
# 数据读取操作的操作类型
# 那些索引可以使用
# 那些索引被实际引用
# 表之间的引用
# 每张表有多少行被优化器查询
explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化；
id # id相同,执行顺畅由上往下；如果是子查询，id越高优先级越高；平级顺序执行
select_type#查询类型
SIMPLE 简单查询-查询中不包含子查询或UNION
PRIMARY 最外层查询-查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY
SUBQUERY 映射为子查询-在SELECT或WHERE列表中包含了子查询
DERIVED 衍生(虚拟表)-在FROM列表中包含的子查询被标记为DERIVED(衍生)MYSQL会递归执行这些子查询,把结果放在临时表
UNION 联合-若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT被标记位DERIVED
UNION RESULT 使用联合的结果
table # 显示这一行的数据是关于那张表的
type#查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
all：#全局扫描，对于数据表头到尾找一遍；
#特别的：如果有limit限制，则找到之后就不在继续向下扫描
INDEX：#全索引扫描，对索引从头到尾找一遍；
RANGE：#对索引列进行范围查找
INDEX_MERGE：#合并索引，使用多个单列索引搜索
REF：# 非唯一性索引，返回匹配某个单独值的所有行。本质上也是一种索引访问，根据索引查找一个或多个值
EQ_REF：#唯一索引扫描，对于每个索引建，表中只有一条记录与之匹配，连接时使用primary key 或 unique类型
CONST：#常量-表示通过索引一次就找到了,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快
SYSTEM ：# 系统表(表只有一行记录),这是const类型的特例,平时不会出现,这个可以忽略不计
possible_keys#可能使用的索引
key#真实使用的索引
key_len#显示的值为索引字段的最大可能长度,并非实际长度
ref # 显示索引的那一列被使用了,如果可能的话是一个常数。那些列或常数被用于查找索引列上的值
rows#mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
extra#该列包含MySQL解决查询的详细信息
“Using index”
效率不错;如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
表名使用了where过来
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表.常用与排序order by 和分组查询group by.
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using join buffer” 使用了连接缓存
“Using filesort”
说明MYSQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行排序 - 会慢
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“impossible where”
where 子句的值总是false,不能用来获取任何元祖
“select table optimized away”
在没有GroupBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段在进行计算,查询执行计划生成的阶段即完成优化
“distinct”
优化distinc操作,在找到第一匹配的元祖后既停止找同样值的动作
“Covering index” - 索引覆盖
理解1：就是select的数据列只用从索引中就能取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要所建的索引覆盖
“Range checked for each record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
Show Profile
# 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于sql调优的测量
# 官网
# http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
# 默认情况下,5.5以后默认开启,并保存最近15次的运行结果
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL # 显示所有的开销信息
| BLOCK IO # 显示块IO相关开销
| CONTEXT SWITCHES # 上下文切换相关开销
| CPU # 显示CPU相关开销信息
| IPC # 显示发送和接受相关开销信息
| MEMORY # 显示内存相关开销信息
| PAGE FAULTS # 显示页面错误相关开销信息
| SOURCE # 显示和Source_function,Source_file,Source_line相关的开销信息
| SWAPS # 显示交换次数相关开销的信息
}
分析步骤:
1.运行SQL
2.查看结果：
show profiles;
3.诊断SQL
show profile cpu,block io for query 加查询结果query_id
4.status 出现如下异常:
converting HEAP to MyISAM # 查询结果太大,内存都不够用了往磁盘上搬了
Creating tmp table # 创建临时表 - 拷贝数据到临时表 用完再删除
Copying to tmp table on disk # 把内存中临时表复制到磁盘,危险！！！
locked # 锁定
查询优化
1.开启慢日志，设置阈值，比如超过5秒就是慢SQL，并将其抓取出来 2.explain+慢SQL分析 3.show profile 查询SQL在MySQL服务器里面的执行细节和生命周期情况 4.SQL数据库服务器的参数调优
优化原则 1.小表驱动大表,即小的数据集驱动大的数据集 SELECT * FROM table WHERE EXISTS (subquery) # 当subquery小于table时用in优与exists # 当table小于subquery时用exists优与in # 该语句可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否保留 # 提示: 1.EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT*也可以是SELECT 1或select x,官方说法是实际执行时会忽略SELECT清单,因此没有区别 2.EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比,如果担忧效率问题,可进行实际检验以确定是否有效率问题。 3.EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代,何种最优需要具体问题具体分析 ORDER BY关键字优化 GROUP BY关键字优化
慢日志
查看当前配置信息：
show variables like '%query%'
修改当前配置：#在内存中更改，立即生效不需要重启
set global slow_query_log = 1 # 开启
配置MySQL自动记录慢日志
slow_query_log = OFF; 是否开启慢日志记录
long_query_time = 2; 时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log; 日志文件
log_queries_not_using_indexes = OFF; 为使用索引的搜索是否记录
或者创建配置文件：#修改配置文件之后，需要重启服务；
mysqld --defauts-file='配置文件路径'#启动服务时找配置文件
配置文件内容：
slow_query_log = OFF 是否开启慢日志记录
long_query_time = 2 时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log 日志文件
log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
# 全局查询日志
# 永远不要在生产环境开启这个功能
# 配置启用
general_log=1 # 开启
general_log_file=/path # 记录日志文件路径
log_output=FILE # 输出格式
# 编码启用
set global log_output='TABLE'; # 此后你编写的sql语句,将会记录到mysql库里的general_log表
select * from mysql.general_log; # 查看
*****分页
单纯使用limit 数据量小的情况下是没有问题的，如果数据量大越往后速度越慢，因为limit每次分页是从头开始计算
解决方法：1.限制显示页数；2.从索引表中扫描
实际解决方法：在代码级别记录当前最大或最小ID 用where筛选后再使用limit分页
*****补充
select sleep(num) # 执行数据库睡眠num秒
Songzhibin
