一、SQL基础

单引号 ： 是用来限定字符串的

1. 类别

SOL可以分为3个类别：DDL（数据定义语言）DML（数据操作语句，添加、删除、更新等）DCL（数据控制语句）

DDL与DML的区别在于：DML只是操作表内部的数据，而不涉及表的定义结构的修改，更不会涉及其他对象

DCL语句主要是用来管理系统中的对象权限使用

在数据库8.0中对于一些字段的位数限制取消

+--------------------+
| Database           |
+--------------------+           
| information_schema |# 存储系统中一些数据库对象信息，记录mysql的  元数据信息  元数据指的是数据的数据，比如表名 列名 列数据                        类型
| mysql              |    # 存储系统的用户权限信息
| performance_schema |  # mysql5.5 引入的系统库
| sys                |    # mysql5.7 引入的系统库
+--------------------+

information_schema用来记录元数据信息,可以进行元数据查询

2. 帮助的使用

1. 按照层次查看帮助
   1. 使用？contents 来显示所有可供查询的分类
   2. 使用？类别名称来查看该类别的具体介绍，使用？data types查看mysql支持那些数据类型。
2. 快速查询帮助
   1. 当需要快速查询某项语法时可以使用关键字进行快速查询？关键字

二、基本类型

1. 数值类型

1. mysql支持在类型名称后面添加小括号指定显示宽度
2. 修改字段类型，加入zerofill参数 zerofill：如果值长度不够，在前面补0表示。当一个列指定为zerofill时，MySQL自动为该列添加UNSIGNED

ALTER TABLE 表名 MODIFY 列名 数据类型 zerofill;

1. 所有的整数类型有UNSIGNED无符号数据类型
2. 对于BIT(M)类型的数据，用于存放M位字段值，直接使用SELECT语句无法显示其值，需要使用BIN() 或HEX()函数进行读取，M的最大值为64

2. 日期时间类型

对于TIMESTAMP的解释

mysql> show  variables like 'explicit%';      # 将explicit_defaults_for_timestamp 设置为ON，则使用TIMESTAMP时
+---------------------------------+-------+   # 的默认值等等都不会自动设置，
| Variable_name                   | Value |      # mysql只给表中第一个TIMESTAMP设置默认系统日期
+---------------------------------+-------+      # TIMESTAMP的值与时区有关，而且与DATETIME相比表示的范围更小
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+

3. 字符串类型

1. CHAR与VARCHAR类型
   1. 都是用来存储mysql的较短字符串，区别在于两者存储方式不同：CHAR的列的长度为创建表时声明的长度而VARCHAR的列的长度为可变的字符串，在赋值的时候CHAR会自动删除尾部的空格，而VARCHAR不会，而且VARCHAR显示的需要字节数比实际上大一或者二（因为VARCHAR需要使用一到两个字节来记录存储的长度）

1. CHAR(4) VARCHAR(4) 中的4表示字符数
2. CHAR(4) 是定长的固定大小
   VARCHAR(4) 是变化的大小，在VARCHAR中还有一个位置存放的个数
3. CHAR的查询速度高于VARCHAR

1. BINARY和VARBINARY类型
   1. 包含的为二进制字符串，而不是非二进制字符串，在保存BINARY最后通过填充0x00零字节来达到指定的定义长度，

mysql> SELECT * FROM t;            
+------------+------------+
| id1        | id2        |
+------------+------------+
| 0x610000   | 0x61       |
| 0x610000   | NULL       |
+------------+------------+

1. TEXT和BLOB
   1. 区别： TEXT只能保存字符数据， BLOB能用来保存二进制数据，各种不同类型的TEXT和BLOB的差别在于他们存储文本的长度和存储字节不同
   2. 缺点：
      • BLOB和TEXT的值会留下一些性能问题，特别是在执行大量删除操作时，需要定期使用OPTIMIZE TABLE（对于InnoDB存储引擎的表来说，该语句会自动装换为recreate+analyze）来对这些表进行碎片整理
      • 可以使用索引来提高文本字段的查询性能，可以使用MD5() 来生成散列值，散列值建议使用VARCHAR来存储（因为： 生成的散列值尾部可能有空格使用CHAR会忽略）

4. ENUM类型

1. 在枚举类型中对1_{255个成员使用一个字节存储，对256}65535使用两个字节存储，在创建表时必须赋初值
2. 在存储过程中ENUM类型是不区分大小写的，在存储中都会转为大写，还可以插入数字来隐式代表是第几个元素
3. 使用gender来定义枚举类型,ENUM类型是忽略大小写的,而且只允许在值集合中选取单个的值,不允许选取多个的值

CREATE TABLE 表名 (gender enum('M','F'));   # 需要指定数据的范围
INSERT INTO 表名 VALUES ('M'),('1'),('F'),(NULL);   -- 允许使用数字来代替上面的值,代表枚举类型中已有的第几个值

在这里的取一个值还是多个值,其实是创建时指定的数据范围

5. SET类型

1. SET类型也是一个字符串对象在存储上1_{8成员的集合使用1个字节，9}16的成员使用2个字节，17_{24的成员使用3个字节，25}32的成员使用4个字节，33~62的成员使用8个字节
2. 在SET类型中可以一次取多个值，在创建时

CREATE TABLE t3 (id3 SET('a','b'));  # 需要指定数据的范围
INSERT INTO t3 VALUES ('a,b'),('a,b'),('a,b'); #在一个组合中一个值只取一次

6. JSON类型

1. JSON是一种数据交换格式，JSON严格区分大小写，而且JSON没有默认值，且文本最大长度取决于系统常量max_allowed_packet
2. 优点：
   1. JSON数据类型会自动检验数据是否为JSON格式
   2. MYSQL提供了一组操作JSON数据的内置函数
   3. 优化的存储格式
3. 支持的数据类型：NUMBER、STRING、BOOLEAN、NULL、ARRAY、OBJECT，字符串于日期类型使用双引号引起来表示，ARRAY使用中括号引起来，OBJECT保存的KV对要使用大括号引起来，其中K也要使用双引号引起来,ARRAY与OBJECT可以嵌套使用

["abc",10,null,true,false]
{"k1":"value1","k2":"value2"}
["12:18:29.00000","2015-07-09","2015-07-09 12:18:29.00000"]

SELECT JSON_TYPE(数据1)，JSON_TYPE(数据2);  -- 判断数据是那种JSON数据
SELECT JSON_VALID(数据1)，JSON_VALID(数据2); -- 判断数据是否合法

1. 在插入数据时，特殊字符输入时要进行转义操作

INSERT INTO 表名 VALUES (JSON_数据类型("ab\"c"));  -- 显示插入
INSERT INTO 表名 VALUES ('{"k1":"ab\\"c"}');    -- 隐式插入在隐式插入时要多加一条反斜线

1. JSON函数
   1. 创建JSON
      • JSON_ARRAY() 创建JSON数组
      • JSON_OBJECT()创建JSON对象
      • JSON_QUOTE() / JSON_UNQUOTE() 加上/去掉JSON文档两边的双引号

SELECT JSON_ARRAY(1,"abc",TRUE,NULL) AS ARRAY,
JSON_OBJECT(12,'abc','32',"cds") AS OBJECT,
JSON_QUOTE("abc") AS '加双引号',
JSON_UNQUOTE("abc") AS '去除双引号';

2.  查询JSON  

    *  JSON_CONTAINS(target, candidate[, path])  

查询在target中是否包含candidate

SELECT JSON_CONTAINS('[1,"abc",null,2,3]','[1,3,"abc"]');

path参是可选的,可以在指定的路径下进行查询,如果JSON文档为对象,则路径通常是类似与$ .a(表示key为a)这种格式, 如果JSON文档为数组,则路径通常是类似于 $[i]这种格式,表示数组中的第i个元素

    *  JSON_CONTAINS_PATH(JSON_DOC,one_or_all,path[,path])            查询文档是否存在指定的**路径**  

查询JSON文档中是否包含指定路径， one_or_all 是否全匹配

SELECT JSON_CONTAINS_PATH(JSON_OBJECT('k1','"ac','k2','"asd'),'one','$.k1','$.k3');

    *  JSON_EXTRACT(JSON_DOC,path[,path])/->/->>            根据条件提取文档中的**数据**，将结果返回到JSON_ARRAY  

->> 会自动删除双引号

INSERT INTO js VALUES (JSON_OBJECT('k1','abc','k2','asd'));
SELECT JSON_EXTRACT(id1,'$.k1'),JSON_UNQUOTE(id1->'$.k1'),id1->>'$.k1' FROM js WHERE id1 -> '$.k1' = 'abc';

    *  JSON_KEYS(json_doc[,path])                             返回所有符合条件的**键值**(类型为JSON_ARRAY) 

INSERT INTO js VALUES (JSON_OBJECT('k3',JSON_OBJECT('k4','cbs')));
SELECT JSON_KEYS(id1) FROM js ;
SELECT JSON_KEYS(id1,'$.k3') FROM js ;

    *  JSON_SEARCH(json_doc, one_or_all, search_str[,escape_char[,path]....]) 返回包含指定字符串的**路径**，并作为一个JSON_ARRAY返回  

searchstr 要查询的字符串，可以使用LIKE里的‘%’ 或‘‘匹配

SELECT JSON_SEARCH(id1,'one','c__') FROM js;

3.  修改JSON函数   

JSON_ARRAY_APPEND(json_doc,path,val[.path,val]….) 在指定的JSON_ARRAY尾部追加，如果指定的路径为 JSON_OBJECT，先将他封装成JSON_ARRAY在进行追加

JSON_ARRAY_INSERT(json_doc,path,val[, path,val]….) 如果指定的路径不是JSON_ARRAY则忽略此val
JSON_REPLACE(json_doc,path,val[,path,val]…..) 替换指定路径的数据，如果某一个路径不存在，则略过
JSON_SET(json_doc,path,val[,path,val]….) 设置指定路径的数据，如果指定的路径不存在会进行添加
JSON_MERGE_PRESERVE(json_doc,json_doc[,json_doc]….) 将多个JSON文档进行合并
JSON_REMOVE(json_doc,path[,path]…) 移除指定路径的数据，，如果指定了多个path，则需要明白删除操作是串行的

4.  查询JSON元数据函数   

JSON_DEPTH(json_doc) 获取JSON文档的深度， （空对象，空数组，true， null，FALSE 深度为1）

5.  JSON工具函数  

1. 窗口函数
2. 优化
   1. NLJ
      1. NLJ原理：通过两次循环，对于外循环中每一条记录都与内循环记录进行一次比较
      2. 性能影响： 外循环的结果集，内循环扫描数据的效率
      3. 优化方案：在外循环上加上WHERE并创建合适的索引使得结果集变小，内循环在关联字段上加索引
   2. BNL
      1. 相比于NLJ，BNL在外循环读取数据后不会理解进行与内循环中数据进行比较，而是会先进入一个缓存区等数据量足够时在于内循环中数据进行比较，减低了内层表的读取次数
      2. 使用 optimizer_switch=’block_nested_loop = off’来关闭BNL
      3. 使用条件：只有当join类型是all/index/range时才可以使用也就是当内表不使用索引或者使用索引效率低时才使用
      4. 方案：就是减低内表的访问次数降低回表的IO次数
   3. MRR & BKA
      1. MRR优化的目的：减少磁盘的随机访问，
      2. 对于InnoDB，MRR优化是：通过范围扫描数据存入rand_rnd_buffer_size，然后按照Primary Key进行排序，最后使用排序好的数据进行顺序回表，因为InnoDB的叶子结点是按照Primary Key排序的，提高了IO访问的效率
      3. MRR的特性：在单表中通过范围查询，在多表join方式如果是ref/key_ref，则先通过BKA算法批量提取key到join buffer ，然后将buffer的key作为参数传入MRR的调用接口
      4. 打开MRR特性：optimizer_switch=’mrr=on,mrr_cost_based=off’
      5. BKA步骤：
         1. 将外循环表中相关列放入join Buffer中
         2. 批量将key发送到MRR接口
         3. MRR通过收到的key，根据Primary Key进行排序，然后根据排序的Primary Key顺序读取聚集索引得到所需要的数据
         4. 返回结果集给客户端
      6. 使用BKA要先打开MRR optimizer_switch =’mrr = on,mrr_cost_based=off,batched_key_access=on’;
      7. BKA对JOIN的限制：连接的列要求是唯一索引或者普通索引不能为主键，要有对非主键的查询操作

7. 浮点数与定点数

定点数 是以字符串的形式进行存储的

三 算术运算符：

1.算术运算符

mysql支持的算术运算符有+、-、*、/（DIV）、%（MOD）

2. 比较运算符

SELECT a BETWEEN  min AND max;

3. 逻辑运算符

4. 位运算符

1. 位与，位或可以对多个操作数进行操作

四 常用函数

1. 字符串函数

Soundex 时间字符串进行发音比较而不是字母比较

Select cust_name,cust_contact From customers where Soundex(cust_contact) = ('Y Lie');

任何字符串与NULL值连接都为NULL

2. 数值函数

1. 日期和时间函数见下使用.处理函数

3. 流程函数

SELECT userid,salary,case salary WHEN 1000 then 'low' WHEN 2000 then 'low' else 'high' end from salary;
# case后面跟列名或者列的表达式,when后面枚举这个表达式可能的所有值
SELECT userid, salary , case when salary<=1000 then 'low' else 'high' end from salary;
# 直接在when后面写条件表达式

1. LOOP语句
   LOOP语句是用来实现简单循环的条件，退出循环的条件需要使用LEAVE(如果不设置就会进入死循环)

[begin_label:]  LOOP 
        statement_ins
END LOOP [begin_label];

1. LEAVE语句
   用来从标注的流程过程中退出

CREATE PROCEDURE cal ()
BEGIN
    SET @x = 0;
    ins:LOOP
        SET @x = @x + 1;
        IF @x = 100 THEN LEAVE ins; END IF;
        INSERT INTO college VALUES (1) ;
    end loop ins;
end #;

1. ITERATE 语句
   ITERATE语句必须用在循环中，作用是跳过当前循环的剩下语句

CREATE PROCEDURE cal ()
BEGIN
    SET @x = 0;
    ins:LOOP
        SET @x = @x + 1;
        IF @x = 50 THEN ITERATE ins; END IF;
        INSERT INTO college VALUES (1) ;
    end loop ins;
end #;

1. REPEAT 语句
   当满足条件时退出循环

[vegin_label:] REPEAT 
        statement_list
UNTIL serach_condition
END REPEAYT [begin_label];

五、存储引擎

使用default_storage_engine查看当前默认存储引擎为InnoDB

使用show engines \G来查看数据库支持的引擎，在查找值中Support不同值得含义：DEFAULT—-支持并启用，为默认引擎，YES—-支持并启用，NO—不支持，DISABLED—支持，但是数据库启动的时候被禁用

在创建新表时可以通过ENGINE关键字来新建表的存储引擎,使用CHARSET指定表格的存储文字格式

1. MyISAM(不经常使用)

MyISAM既不支持事务也不支持外键

在创建表时可以指定数据文件和索引文件的位置，这两个文件可以放在在不同的目录中，平均分布IO以获得更改的速度

在使用该引擎时有三种不同的存储格式:静态表、动态表、压缩表,静态表是默认的存储格式,在使用时要注意数据的空格在末尾的空格会被抹去

2. InnoDB

具有提交 回滚 崩溃恢复能力的事务安全保障
对比MyISAM存储引擎来说， InnoDB会占用更多的空间来保留数据和索引

1. 自动增长列

可以在创建表时在添加 AUTO_INCREMENT = n设置自动增长列初始值也可以和ALTER语句搭配使用

为了使设置的值在重启之后依然存在,mysql将自增主键的计数器持久化到REDO LOG中,一旦发生变化都会修改其中的值

使用LAST_INSERT_ID()查询最后插入的记录使用的值,在人为指定增长列时值不会更新,在使用时会自动根据列中的最大值进行自增,在同时插入多条数据时,其值为第一个插入数据的值

在InnoDB中自动增长列必须被索引,如果时组合索引也必须时组合索引第一项, 因为如果不是第一项自动增长列是按照组合索引的前几列进行排序后递增的 原因： 对于MyISAM表中，自动增长列可以使组合索引的其他列，这样自动增长列的值就是在其前面的列排序后产生的值

2. 外键 ：

外键为某一个表中的一列，它包含另一表的主键值

MySQL中支持外键的只有InnoDB

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)  指定方式;
-- 将创建数据表创建语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段) 指定方式;

示例：创建一个学生表 MySQL命令：

create table student05(
id int primary key,
name varchar(20)
);

示例：创建一个班级表 MySQL命令：

create table class(
classid int primary key,
studentid int
);

示例：学生表作为主表，班级表作为副表设置外键， MySQL命令：

alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);
-- 或者在创建表时写
CREATE TABLE  name(
    ...
    PRIMARY KEY (classid),KEY fk_class_studentid (studentid) constraint fk_class_studentid foreign key(studentid) references student05(id);
)

这里的指定方式是指：在删除 更新父表时对子表的相应操作 RESTRICT、NO ACTION是指在子表有关联记录的情况下父表不能更新，CASCADE表示父表再删除、更新时对字表也进行相应的操作，SET NULL表示在父表更新或删除时，子表的对应字段设置为NULL，使用：ON UPDATE/DELETE 指定方式

删除外键

alter table 从表名 drop foreign key 外键名；

1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

可以使用FOREIGN_KEY_CHCKS的值来开启与关闭外键的检查

3. 主键和索引

在InnoDb中的数据文件本身是以聚簇索引的形式存在的，聚簇索引也被称为主索引，并且也是InnoDB表的主键，InnoDB表的每行数据都保存在主索引的叶子结点上，因此所有的InnoDB表都应该包含主键

4. 存储方式

InnoDb有两种存储方式

1. 使用共享表空间存储：表结构在.frm文件中，数据和索引在 innodb_data_home_dir和 innodb_data_file_path定义的表空间中
2. 使用多表空间存储：表结构在.frm文件中，每个表的数据和索引在.ibd中（建议使用）

3. MEMORY

MEMORY存储引擎使用在内存中的内容来创建表，每个MEMORY表实际对应一个磁盘文件.frm，因为存储在内存中所以访问速度快，默认使用HASH索引，一旦服务关闭，表中数据就会丢失，同时在不需要使用MEMORY需要立即释放其内存

改变索引

CREATE INDEX mem_hash USING HASH/BTREE on 表名（列名）;

4.MERGE

该存储引擎实际就是一组MyISAM表的组合，同时这些MyISAM表的结构必须完全相同，MERGE表本身是没有任何数据对该引擎的表进行操作实际就是对MyISAM表的操作，对于MyISAM表的插入必须指定否则不能对该表进行插入数据，

对表进行删除只是删除定义对内部的表没有影响

CREATE TABLE t1 (....)engine = MyISAM;
CREATE TABLE t2 (....)engine = MyISAM;
CREATE TABLE t3 (....)engine = merge UNION = (t1,t2 ...) INSERT_METHOD= LAST/FIRST/NO  -- LAST是对最后一个表插入数据，FIRST是对第一个表插入数据 NO不执行此操作

六 索引

在使用通配符进行查询时 %不能写在开头，这样的话索引就无法使用

对相关列进行索引是加快SELECT操作性能的最佳途径

MyISAM和InnoDB存储引擎默认使用 BTREE索引

1. 概述

在使用索引时可以使用前缀索引,但是前缀索引的限制是以字节为单位,而CREATE TABLE语句中的字符长度解释为字符数,在使用多字节字符集的列时要注意
全文本索引FULLTEXT
空间列类型创建索引： MyISAM 和InnoDB（索引是以R-Trees来索引的）

CREATE [UNIQUE/ FULLTEXT/ SPATIAL] INDEX name ON 表名（列名）(INVISIBLE); -- 创建索引
ALTER TABLE 表名 ADD INDEX name(列名)  (INVISIBLE/VISIBLLE); -- 索引可见或不可见
DROP INDEX index_name ON table_name;   -- 删除索引

注意在InnoDB存储引擎中普通索引会保存主键的键值,所以主键要尽可能选择较短的数据类型减少索引的磁盘占用

2. 不可见索引

是指堆优化器不可见,在执行时也不会选择但在查询表时有,(在删除索引时可以用来检查影响)

七 时间调度器

CREATE EVENT name
    ON SCHEDULE 执行时间、频率 -- (at timestamp用于创建单次执行的事件  every子句用于创建重复执行的事件)
DO
    操作;
ALTER EVENT name DISABLE;  -- 禁用
DROP EVENT name;   -- 删除

八 事务控制与锁定语句

1. 概述

LOCK TABLES可以用来锁定当前线程的表

UNLOCK TABLES可以用来释放当前线程获得任何锁定

LOCK TABLES table_name {READ / WRITE}; -- 锁定
UNLOCK TABLES；  -- 释放

2. 事务控制

START TRANSCATION / BEGIN; -- 开启一个新的事务,会造成一个隐含的UNLOCK TABLES语句执行
COMMIT [work] [AND [NO] CHAIN] [[NO] RELEASE]  -- 提交  只能对事务类型的表操作
ROLLBACK [work] [AND [NO] CHAIN] [[NO] RELEASE] -- 回滚  只能对事务类型的表操作
CHAIN  RELEASE  -- 在事务提交后或回滚之后的操作,CHAIN会立即开启一个新事物并与原来的隔离级别相同  RELEASE会断开和客户端的连接
SET AUTOCOMMIT = 0 / 1  -- 自动提交
SAVEPOINT name;  -- 设置回滚点
ROLLBACK To SAVEPOINT name;  -- 回滚到点
RELEASE SAVEPOINT name;  -- 删除回滚点

3. 分布式事务的使用

1. 分布只支持InnoDB存储引擎

1. 概述

1. 使用分布式事务涉及一个或多个资源管理器和一个事务管理器
2. 事务必须一起提交或一起回滚

2. 使用

XA START/ BEGIN xid;   -- 开启一个分布式事务,xid是每一个分布式事务特有的唯一性
-- 事务进入PREPARE状态
XA END xid;
XA PREPARE xid;
-- 提交或者回滚的分支事务
XA COMMIT xid;
XA ROLLBACK xid;
-- 返回当前数据库中处于PEREPARE状态分支事务的信息
XA RECOVER;

xid: gtrid [,bqual [, formatID] ]
gtrid: 是一个分布式事务标识符,相同的分布式事务使用相同的gtrid
bqual: 是一个分支限定符.对于一个分布式事务每一个分支事务bqual唯一formatID: 是一个数字,标识gtrid与bqual值使用的格式
XA事务在结束之后.提交之前不允许进行查询

九 SQL优化

1. 通过EXPLAIN分析低效SQL执行计划

存在索引但不能使用的情况：以%开头的LIKE查询不能使用B_Tree查询，数据类型出现隐式转换的不会使用索引，复合索引情况下不满足最左原则

2. EXTRA各项的解释

3. 优化分页查询

4. 优化方法

定期分析表和检查表：

ANALYZE TABLE 表名;   -- 分析表
CHECK TABLE 表名;  -- 检查表
REPAIR TABLE 表名;  -- 修复表

优化表：

使用OPTIMIZE TABLE来对表进行优化，将删除或更新数据后的空间碎片进行整理

ANALYZE、CHECK、OPTIMIZE、ALTER TABLE 执行期间对表进行锁定

5. SQL的提示

1. SELECT SQL_BUFFER_RESULTS * FROM …; 强制mysql生成一个临时结果集，只要临时结果集生成后所有表上的所全被释放
2. 在查询语句后使用USE INDEX(索引名) 希望mysql使用的索引，IGNORE INDEX（索引名）忽略多个索引 ，FORCE INDEX（索引名）强制使用索引

6. 直方图

1. 使用直方图的目的是：优化器需要了解列中具体的数据分布情况

ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [,col_name] WITH N BUCKETS;  -- 生成直方图
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [,col_name];   -- 删除直方图

BUCKETS是表示生成桶的个数,桶中用来存放列中不同值的分布情况

1. 直方图的分类:等宽直方图,等高直方图
2. 直方图的信息存储在information_schema库的column_statics视图中,在单值直方图的buckets中，有2个值：
   第1个值：bucket代表的值。类型由字段类型决定。
   第2个值：自己的bucket和前面的bucket中的值的占比之和，是一个逐渐增加的值。取值在0.0到1.0之间。显然最后一个bucket中比例一定是1。
   在等高直方图的buckets中，有4个值：
   第1个值：bucket包括的值的范围的下限。
   第2个值：bucket包括的值的范围的上限。
   第3个值：自己bucket取值范围和前面bucket取值范围中的值的占比之和，是一个逐渐增加的值。取值在0.0到1.0之间。同单值直方图的第2个值，最后一个bucket的比例是1。
   第4个值：bucket包括的值的distinct数。
   在buckets之外的参数：
   null-values：null值比例。取值在0.0到1.0之间。0代表没有null值。
   last-updated：直方图更新时间。格式：YYYY-MM-DD hh:mm:ss.uuuuuu。
   sampling-rate：取样比例。取值在0.0到1.0之间。1.0代表bucket值完全覆盖字段值，没有取样。
   histogram-type：直方图类型。singleton代表单值直方图，每个bucket都代表一个值，字段distinct值小于sql指定bucket数时使用该类型。equi-height代表等高直方图，每个bucket都代表一个值的范围，字段distinct值大于sql指定bucket数时使用该类型。
   number-of-buckets-specified：生成直方图的sql语句中指定的bucket数量。
   data-type：直方图中的数据类型。从磁盘中读取并存入内存中时会用到。可能的值有：int，uint (unsigned integer)，double，decimal，datetime，string (包括character and binary strings)。
   collation-id：直方图数据中的字符集id。和INFORMATION_SCHEMA.COLLATIONS表中id对应。直方图中的数据是String类型时会有用。
   直方图

6. 使用查询重写

通过查询将一些数据进行修改

INSERT INTO 数据库名.表名(列名...) VALUES(值);
CALL 数据库名.flush_表名();  -- 刷新

十、 使用

1. 启动与关闭

mysql -uroot -p        # dos命令窗口登录mysql数据库管理系统
select version();        # 查询数据库版本
help                    #显示帮助
safe-mode              #装载减去某些最佳配置的服务器
FLUSH LOGS             # 刷新和重新开始所有日志文件
FLUSH TABLES          # 刷新表
SHOW STATUS  / SHOW VARIABLES            #查看当前配置
EXPLAIN                    # 解释如何运行SELEXCT语句
C:\Users\Administrator>mysql --version        # 查询数据库版本
select now();            # 查询当前时间
\c                       # 结束一条语句
\G                        # 使得记录可以按照字段竖向排列
exit或者\q                # 退出mysql
PASSWORD(str)            # 返回字符串加密的结果，不能用于数据加密
MD5(str)                # 返回字符串的MD5值，可以用来给数据加密

2. 命令操作

show databases;            # 查看所有数据库
use 数据库名;            # 使用数据库
select database();        # 查看当前使用的数据库
create database 数据库名;        # 创建数据库
drop database 数据库名;        # 删除数据库
show create database 数据库名 \G             # 查看建数据库语句 
show status;         #显示广泛的服务器状态信息
show grants;         #显示授予用户的安全权限
show errors;           #显示服务器错误
show warnings;       #显示警告信息

3. 数据表操作

show tables;            # 查看当前数据库中所有表以及视图的名字
show columns from 表名; // desc 表名;    # 查看表结构
show create table 表名 \G      # 查看创建表的sql语句  \G是使得记录能够按照字段竖向排列
drop table 表名;            # 删除表
create table 表名（, , ,）;        #创建一个表
SHOW TABLE STATUS （LIKE) tbl_name \G    -- 查看表的相关信息以及视图的信息
BINARY                              -- 在使用通配符和正则表达式时启用大小写
COMMENT                           -- 创建注释

4.检索数据

1. 检索所有数据

SELECT * FORM 表名;

1. 查找单个数据

SELECT 列名 FORM 表名

1. 查找多列

SELECT 列名,列名 FORM 表名

1. 检索所有列

SELECT * FORM 表名;

1. 检索不同的行 查询不重复的记录

SELECT DISTINCT 列名  FORM 表名;

1. 限制结果

SELECT 列名 FORM 表名 LIMIT 行数;
SELECT 列名 FORM 表名 LIMIT 每页显示记录数行 * (第几页-1),每页显示记录数; # 每一页显示的记录数

1. 检索不同的行

SELECT DISTINCT 列名 FROM 表名;

1. 排序检索数据
   默认为升序排序
   单个数据

SELECT 列名 FROM 表名 ORDER BY 列名;

多个数据
反向排序
DESC 只对自己前面的列起作用
注意在使用时注意操作的顺序,顺序不对会报错
优化

1.  两种排序方式 
    1.  通过有序索引直接返回有序数据 
    2.  对放回数据进行排序，**Filesort**，所有不是通过索引直接返回排序结果的排序都叫**Filesort**  

FIlesort原理： 将取得的数据在sort_buffer_size系统变量设置的内存排序区进行排序，而且是每一个线程独占的

2.  优化目标： 减少额外排序，通过索引直接返回有序数据 
3.  FILESORT优化 

    1. **双路排序**：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。完成后在根据行指针回表读取记录
**单路排序**：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。
    2. 通过设置**max_length_for_sort_data**大小是mysql选择第二种排序算法

1. 过滤数据
   使用WHERE关键字,字符串使用单引号标注

SELECT 列名 FROM 表名  WHERE  列名 标准;

1. 组合WHERE关键字(还有分组数据操作 HAVING)
   使用AND连接多个操作

SELECT 列名 FROM 表名 WHERE 操作1 AND 操作2;

使用OR
组合使用
使用IN操作
IN操作类似于OR操作,都是完成同样的功能
优点

- 在使用长的操作时,使用IN操作可以大幅度减少操作的使用
- 在使用IN时计算的次序更容易管理
- 执行快
- 可以包含其他的SELECT语句

使用NOT操作

SELECT 列名 FROM 表名 WHERE  列名 NOT IN (数据1,数据2);

否定跟在他之后的操作

1. 使用通配符检索数据
   通配符:用来匹配值的一部分的特殊字符
   探索模式:由字面值、通配符或两者结合的探索
   在探索字句中使用通配符，必须使用LIKE操作符，（指示mysql后面的操作是使用通配符匹配）
   1. 百分号（%）匹配
      %可以是任意的数量的字符，不能表示NULL，可以在使用多个通配符

SELECT 列名 FROM 表名 WHERE 列名 LIKE 匹配值

2.  使用（_）匹配  

_是代表一个字符

SELECT 列名 FROM 表名 WHERE 列名 LIKE 匹配值

3.  使用ESCAPE  

当检索的数据中有通配符时，

SELECT 列名 FROM 表名 WHERE 列名 LIKE '匹配值\_' ESCAPE '\';
//  表名'\'为转码字符，表示跟在后面的_是一个普通的字符

12 . 子查询

1. 当进行查询时需要的条件是另一条SELECT语句的结果时使用子查询，in、not in、= 、 ！=、exists、not exists

SELECT * FROM sc WHERE sno IN (SELECT sno FROM student WHERE ssex = '女' LIMIT 1,1);
SELECT * FROM sc WHERE sno = (SELECT sno FROM student WHERE ssex = '女' LIMIT 1,1); # 当查询只有一条语句时可以使用= 代替 IN

1. 使用正则表达式
   正则表达式: 是用来匹配文本的特殊的串(字符集合)
   1. LIKE匹配整个列，而REGEXP在列值内匹配(REGEXP也可以用来匹配整个列使用 定位符)
   2. 需要使用REGEXP关键字，告诉mysql后面是一个正则表达式

SELECT 列名 FROM 表名 WHERE 列名 REGEXP 表达式;

3. 使用**.**来匹配任意一个字符

SELECT 列名 FROM 表名 WHERE  列名 REGEXP .表达式;

4. mysql的正则表达式不区分大小写，如果需要区分大小写需要使用**BINARY**

SELECT 列名 FROM 表名 WHERE 列名 REGEXP BINARY 表达式;

5. 进行**OR**匹配，使用**|**来表示

SELECT 列名 FROM 表名 WHERE 列名 REGEXP  表达式1|表达式2;
SELECT 列名 FROM 表名 WHERE  列名 REGEXP  [起始值 - 终止值];   
SELECT 列名 FROM 表名 WHERE 列名 REGEXP  [^起始值 - 终止值];   #表示除了这其中的其他字符

6. 在匹配特殊字符时需要使用**\\**来进行转义(MYSQL解释一个\ , 正则表达式解释一个)

7. 匹配字符类

8. 匹配多个实例

SELECT 列名 FROM 表名 WHERE 列名 REGEXP '匹配字符';
SELECT 列名 FROM 表名 WHERE 列名 REGEXP '匹配字符类 元字符';

9. 定位符

 SELECT 列名 FROM 表名 WHERE 列名 REGEXP '^匹配字符';

SELECT 字句检索顺序

5. 计算

1. 计算字段时设立在SELECT语句中的
2. 在mysql中可使用CONCAT来拼接多个列

SELECT CONCAT(列名1，列名2...) FROM 表名;

SELECT CONCAT(RTrim(列名1)，列名2...) FROM 表名;   #Rtrim 忽略右边空格
SELECT CONCAT(LTrim(列名1)，列名2...) FROM 表名;   #Ltrim 忽略左边空格
SELECT CONCAT(Trim(列名1)，列名2...) FROM 表名;    #trim 忽略两边空格

1. 使用别名AS

SELECT 列名1 AS 新名  FROM 表名;
SELECT NOW();                      #返回当前时间

6. 处理函数

1. 日期时间处理函数
   1. (超链接)基本函数

SELECT cust_id,order_num From orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

7. 汇总数据

聚焦函数： 运行在行组上，计算和返回单个值的函数

SELECT AVG(列名) FROM 表名;   # 计算某一列平均值
SELECT COUNT(列名) FROM 表名;   # 计算某一列行数
SELECT COUNT(DISTINCT 列名) FROM 表名;  # 计算某一列不同的行数在使用DISTINCT时必须指定列名
SELECT MAX(列名)  FROM 表名;   # 计算某一列最大值   
SELECT MIN(列名)  FROM 表名;   # 计算某一列最小值   在作用与文本数据时，MAX返回最后一行  MIN返回第一行
SELECT SUM(列名)  FROM 表名;   # 计算某一列和

COUNt() ： 为返回满足条件的记录的行数
COUNT(列)：为返回*满足条件的某列有多少个，但是会排除为null的情况

8. 分组数据

1. 创建分组是在SELECT中GROUP BY完成

SELECT vend_id,Count(*) AS num-prods FROM products CROUP BY vend_id;
SELECT vend_id,COUNT(*) AS S FROM products GROUP BY vend_id WITH ROLLUP;  -- 输出每个分组

GROUP BY优化：

默认是进行排序，可以在GROUP BY后加ORDER BY null以避免进行排序

在使用时可以使用WITH ROLLUP子句可以检索出更多的分组聚合的信息,用户可以得到任何一个分组以及分组组合的聚合信息

1. 过滤分组HAVING

SELECT cust_id,count(*)AS orders FROM orders GROUP BY cust_id HAVING Count(*)>=2;
# 语法:
SELECT 操作 FROM 表名 [WHERE ... ] [GROUP BY 列名 [WITH ROLLUP] ] [HAVING ...];

HAVING 与 WHERE区别在于: HAVING 是对聚合后的结果进行条件的过滤, 而WHERE时对聚合前的记录进行过滤

9. 联结

联结： 是一种机制， 用来在一条SELECT语句中关联表
内部联结：条件使用特殊的ON而不是WHERE ,传递给ON的条件与WHERE相同

1. 表联结分为内联结和外联结，区别为：内联结仅在两张表中互相匹配的记录 外联结会选出其他不匹配的记录
2. 外联结分为左联结和右联结
   • 左联结：包含所有的左边表中的记录甚至是右边表中没有与他匹配的记录(标准是左)
   • 右联结: 包含所有的右边表中的记录甚至是左边表没有与他匹配的记录(标准是右)

SELECT 列名 FROM 表名1 LEFT JOIN 表名2 ON 操作；
SELECT 列名 FROM 表名1 RIGHT JOIN 表名2 ON 操作；
SELECT 列名 FROM 表名1 INNER JOIN 表名2 ON 操作；-- 内联结

10. 组合查询

1. 使用UNINO操作符，来完成对多个SELECT语句的组合

SELECT vend_id,prod_id,prod_price
 FROM products
 WHERE prod_price <= 5
  UNION / UNION ALL
 SELECT vend_id,prod_price,prod_id
 FROM products
 WHERE vend_id IN (1001,1002);

1. UNION规则
   1. UNION使用是必须是有两个或多个SELECT语句组合使用时
   2. UNION每个操作查找时必须包含相同的列，表达式或聚焦函数（不必以相同的次序写出各个列，以第一个列次序进行输出）
   3. 列数据类型必须兼容，类数据类型可以不用
   4. 在使用UNION是会自动去掉重复的项,在结果会自动调用DISTINCT操作去除重复的记录，如果不想去掉重复的项可以使用UNION ALL操作符
   5. 在使用UNION操作时，只能在结束时使用ORDER BY操作符

11. 全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引

1. 在使用全文本搜索时，不需要查看每一个行，不需要分别处理和分析每一个词
2. 一般在创建表时启用全文本搜索，在CREATE TABLE语句中接受FULLTEXT语句，给出被索引列的一个逗号分隔的列表

CREATE TABLE 表名(
    数据
    PRIMARY KEY(列名1，列名2...),   -- 主键
    FULLTEXT(列名)   -- 根据该句的指示对他进行索引,也可以索引多个列,在定义之后mysql会自动维护
)

1. 在SELECT语句中使用MATCH() AGAINST() 指定要使用的搜索表达式

SELECT 列名 FROM 表名 WHERE MATCH(列名) AGAINST(字符串);
SELECT 列名 MATCH(列名) AGAINST(字符串) FROM 表名;  #返回每一行的等级值

MATCH() 指定作妖检索的列传递给MATCH的值必须与FULLTEXT定义的值相同，而AGAINST() 将字符串作为搜索词，进行搜索
特点：
输出时按照等级的大小来进行排列输出顺序
等级是由行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行数确定

1. 查询拓展
   MySQL对数据和索引进行了两遍扫描
   1. 步骤
      1. 进行一个基本的全文本搜索，找出与搜索条件匹配的所有行
      2. MYSQL检查这些匹配行并选择 所有有用的词
      3. MySQL再次进行文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词
   2. 需要使用WITH QUERY EXPANSION进行特殊标记

SELECT 列名 FROM 表名 WHERE MATCH(列名) AGAINST(字符串  WITH QUERY EXPANSION );

1. 布尔文本搜索
   1. 特点
      1. 要匹配的词
      2. 要排斥的词
      3. 排列提示
      4. 表达式分组
      5. 另外一些内容
      6. 没有FULLTEXT语句定义也可以使用
   2. 使用IN BOOLEAN MODE标识

SELECT 列名 FROM 表名 WHERE MATCH(列名) AGAINST(字符串  IN BOOLEAN MODE );

3.   

SELECT note_text FROM productnotes WHERE MATCH(note_text) Against(' heavy -rope*' IN BOOLEAN MODE);
# -rope* 是指删除所有rope为开头的单词   使用-rope是删除rope单词
 SELECT note_text FROM productnotes WHERE MATCH(note_text) Against('>rabbit >(>carrot)' IN BOOLEAN MODE);  # 提升等级
  SELECT note_text FROM productnotes WHERE MATCH(note_text) Against('~rabbit >(>heavy)' IN BOOLEAN MODE);  # 撤销等级

1. 说明：
   1. 在索引全文本数据时，短语被忽略且从索引中排除，短词定义为具有小于等于3个字符的词
   2. MySQL带有一个内建的非用词列表，这些词在索引全文本数据时被忽略
   3. 当一个词出现频率大于等于50%，则会被认为为非用词,不适用布尔文本操作
   4. 如果表中行数少于3行，则全文本搜索不会放回结果
   5. 忽略词中的单引号，eg：don’t 索引为 dont
   6. 不具有词分隔符
   7. 没有邻近操作符

12. 插入数据

1. 是指插入行到数据库列表中

INSERT INTO 表名(要插入数据的列)  VALUES(数据(一一对应)),...;
INSERT INTO 表名(要插入数据的列)  SELECT 表名(数据(一一对应)),...;  -- 检索出来的数据

1. 如果从同一个客户导入大量的数据应该使用多个值表的INSERt语句可以减少客户端与数据库间连接，
   从同一个客户插入很多行使用INSERT DELAYED语句DELAYED是使INSERT语句马上执行起始数据都在内存队列中，LOW_PRIORITY在所有用户对表的读写完成后才进行插入
2. 大量插入数据
   • 对于MyISAM存储引擎的表可以使用load导入大量的数据

ALTER TABLE tblAME DISABLE KEYS;
loading the data;   -- 这里不知道
ALTER TABLE tbl_name ENABLE KEYS;

- [ ]  
-  对于**InnoDB**存储引擎应该使用 
    * 按照导入文件数据按照主键顺序排列
    * 在导入数据前**SET UNIQUE_CHECKS = 0**关闭唯一性校验，在导入结束后再恢复
    * 在导入数据前关闭自动提交

13. 更新和删除数据

1. 更新数据

UPDATE 表名 SET 列名 = 数据  WHERE 判断是哪一行;
UPDATE IGNORE 表名 SET 列名 = 数据  WHERE 判断是哪一行; -- 即使发生错误也要进行进行更新

1. 删除数据

DELETE FROM 表名 WHERE 判断那一行;    #删除单个表中指定行
DELETE t1,t2... FROM t1,t2... WHERE ;  # 删除多表中数据，如果FROM后面的表使用别名，则DELETE后面也要使用别名
eg : DELETE a,b FROM  表1 a, 表2 b WHERE ;
TRUNCATE  表名; #删除全部行(删除表再重新创建表)

如果FROM后面使用别名,那么DELETE后面也要使用别名,否则会报错

1. 原则:
   1. 除非要删除和更改所有的行,否则后面都要加WHERE以确定哪一行
   2. 保证每一个表都要有主键
   3. 在对UPDATE、DELETE语句使用前使用SELECT来确定更改和删除行的正确记录

14. 创建和操纵表

1. 创建表

CREATE TABLE IF NOT EXISTS 表名 (
    列名   数据类型    (NOT) NULL  AUTO_INCREMENT,  
    列名   数据类型    (NOT) NULL DEFAULT 默认值,
    列名   decimal()    (NOT) NULL CHECK (列取值范围),
    ...
    PRIMARY KEY(列名1,...)
)ENGINE = 引擎;

解释:

- IF NOT EXISTS 只是查看表名是否存在
- NOT NULL 是指必填  NULL 是指可以不填
- AUTO_INCREMENT 是指计数器,从1开始计数  ,每一个表只允许有一个AUTO_INCREMENT列,而且还必须被索引,使用**SELECT last_insert_id()**来返回最后一个AUTO_INCREMENT值
- PRIMARY KEY(列名);是指主键的列,主键值必须唯一,或者列的组合构成的主键唯一，UNIQUE KEY(列名...)唯一键约束
- DEFAULT 默认值
- 定点数：[decimal()](https://blog.csdn.net/weixin_43797452/article/details/107884349?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162778249916780262527646%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=162778249916780262527646&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-1-107884349.pc_search_result_control_group&utm_term=+decimal&spm=1018.2226.3001.4187)  定点数在MySQL内部是以**字符串**形式存在的
- ENGINE是指引擎， 
    * InnoDB是一个可靠的事务处理引擎,不支持全文本搜索
    * MEMORY功能等同于MyISAM,但由于数据存储在内存中速度很快
    * MyISAM是一个性能极高的引擎,支持全文本搜索,不支持事务处理(默认引擎)

1. 使用ALTER TABLE来更改表的结构

ALTER TABLE 表名 MODIFY 列名 数据类型  [FIRST / ALTER col_name];    #修改表中数据类型
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [FIRST / ALTER col_name];  # 修改列名同时修改表中数据类型
ALTER TABLE 表名 ADD 列名  类型 [FIRST / ALTER col_name];;   #添加一列
ALTER TABLE 表名 DROP COLUMN  列名;   #删除一列
ALTER TABLE 表名 ADD/ CHANGE/ MODIFY 列名 数据类型 (after 列名)/first;  #修改列在表中的位置 after 列名是加在指定列之后  first是指加在第一位 
ALTER TABLE 旧表名 RENAME 新表名;   # 修改表名(有两种)
ALTER TABLE 表名 ENGINE=存储引擎; -- 注意当修改存储引擎是需要锁表以及复制数据
ALTER TABLE 表名 AUTO_INCREMENT = n;   -- 设置AUTO_INCREMENT的值为n

在ADD、CHANGE、MODIFY操做中可以指定修改字段在表中的位置, ADD是在表中最后的位置,其他的不会修改位置,

1. 删除表

DROP TABLE 表名;

1. 重命名表

RENAME TABLE 旧表名 TO 新表名;

15. 视图

1. 视图是虚拟的表,视图的内容是由查询定义的,视图只使用动态检索数据的查询,视图不包含表中应该有的任何数据,包含的是一个SQl查询

CREATE VIEW 名字 AS 操作;   #创建一个视图
SHOW CREATE VIEW 名字;   #展示视图语句
DROP VIEW 名字; # 删除视图
CREATE OR REPLACE VIEW 名字 AS 操作; // ALTER VIEW view_name AS 操作  #更新视图
#eg:
 CREATE VIEW pro AS SELECT cust_name,cust_contact,prod_id FROM customers,orders,orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;
 CREATE OR REPLACE view name SELECT cust_name,cust_contact,prod_id FROM customers,orders,orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num  WITH [CASCADED | LOCAL] CHECK OPTION;  -- 当对视图进行更新时，LOCAL 满足此视图条件即可 CASCADED 满足所有视图的条件

1. 更新视图
   当有以下操作时不可以更新视图(如果MySQL不能正确的确定被更新的基数据，则不允许更新)
   • 包含以下关键字的SQL语句：聚合函数， DISTINCT，GROUP BY，HAVING ， UNION ，UNION ALL
   • 常量视图
   • SELECT 中包含子查询
   • JOIN
   • FROM 一个不能更新的视图
   • WHERE字句子查询引用了FROM字句中的表
2. WITH [CASCADED | LOCAL] CHECK OPTION 决定是否允许更新数据使记录不在满足视图的条件
   ，LOCAL 满足此视图条件即可
   CASCADED 满足所有视图的条件（默认）

16.使用存储过程

存储过程： 就是为以后的使用而保存一条或多条MySQL语句的集合

1. 存储过程与函数的差别在于函数必须有返回值，且函数参数只能IN类型
2. MYSQL称存储过程的执行为调用，mysql‘执行存储过程的语句是CALL，CALL接受存储过程的名字，以及需要传递给它的任意参数
3. 创建存储过程

CREATE PROCEDURE 过程名(
    OUT 变量名  变量类型,
    ...
)
characteristic
BEGIN
    操作 IN 变量名;
    DECLARE 变量名  数据类型
    ...
END;
CALL 过程名(@参数1,@参数2...); -- 执行存储过程
SELECT @参数; -- 查找参数

1. BEGIN  与 END用来存储过程体,
2. 变量 临时存储数据
3. OUT 存储过程传出   IN 传递给存储过程   INOUT  对存储过程传入传出
4. mysql所有的变量都已@开头
5. 修改分隔符 DELIMITER 分隔符
6. DECLARE定义局部变量，直接复制使用  SET  ，通过查询将结果赋给变量 SELECT col_name[....]  INTO  var_name[....] table_expr;
7. [characteristic](https://blog.csdn.net/weixin_30846599/article/details/97770754?ops_request_misc=&request_id=&biz_id=102&utm_term=sql%20characteristic&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-5-.pc_search_download_positive&spm=1018.2226.3001.4187)

1. 删除存储过程

DROP PROCEDURE 过程名;   -- 只要过程名不需要()
DROP PROCEDURE IF EXISTS 过程名;  -- 只有存在时删除

SHOW CREATE PROCEDURE  过程名 -- 检查存储过程
SHOW PROCEDURE STATUS  -- 检查所有过程
SHOW PROCEDURE STATUS LIKE 过程名 -- 限制输出

 CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)
BEGIN
     DECLARE total DECIMAL(8,2);
     DECLARE taxrate INT DEFAULT 6;
     SELECT Sum(item_price*quantity)
     FROM orderitems
    WHERE order_num = onumber
     INTO total;
     IF taxable THEN
     SELECT total + (total/100 * taxrate) INTO total;
     END IF;
     SELECT total INTO ototal;
END//

17. 条件

DECLARE condition_name CONDITION FOR condition_value;  -- 条件定义
 -- condition_value : mysql_error_code / SQLSTATE[VALUE] sqlstate_value
DECLARE hander_type HANDLER FOR condition_value[...] statement ; -- 条件的处理
 -- hander_type ： CONTINUE()继续执行 / EXIT（执行终止） / UNDO
 -- condition_value : mysql_error_code / SQLSTATE[VALUE] sqlstate_value / condition_name / SQLWARING / NOT FOUND / SQLEXCEPTIONE

18. 变量

1. 定义
   • 通过使用DECLARE可以定义一个局部变量，该变量的作用域位与BEGIN… END中，在嵌套的块中，变量的定义必须在复合语句的开头处
2. 赋值
   • 是以SET语句进行赋值操作

19. 使用游标（光标）

MySql检索操作返回一组称为结果集的行
使用游标的原因： 有时需要在检索出来的行中前进或后退一行或多行

1. 介绍： 游标是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是该语句检索出来的结果集
2. 使用范围：只能用于存储过程
3. 步骤：
   • 在使用游标前必须定义它，只是定义要使用的SELECT语句
   • 一旦声明后必须打开游标使用
   • 对于填有数据的游标，根据需要取出各行
   • 在结束游标使用时需要关闭游标
4. 创建游标
   游标使用DECLSRE语句创建

CREATE PROCEDURE 过程名()
BEGIN
    -- 变量和条件必须在最前面声明，然后才是游标的声明，最后才是处理程序的说明
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
     DECLARE t DECIMAL(8,2);
     DECLARE ordernumbers(这是一个游标名) CURSOR  
     FOR
    SELECT order_num FROM orders;  -- 定义一个游标
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; -- CONTINUE HANDLER 当条件出现时执行的代码，
    -- SQLSTATE '02000'(是一个未找到条件)发生时，执行SET done=1，
     CREATE TABLE IF NOT EXISTS ordertotals(order_num INT,total DECIMAL(8,2));
     OPEN ordernumbers; -- 打开游标 OPEN
    REPEAT -- 循环操作
         FETCH ordernumbers INTO o;  -- 指定检索的数据（所需的列），向前移动游标中的内部行指针，使下一条FETCH语句检索                                    下一行,当FETCH光标找不到下一条记录时，就会关闭光标后退出过程
        CALL ordertotal(o,1,t); -- 调用一个过程体
        INSERT INTO ordertotals (order_num,total)VALUES(o,t);
    UNTIL done END REPEAT;  -- 当done为假时运行，直到为真
    CLOSE ordernumbers; -- 关闭游标  CLOSE
END//

20 触发器

只有表才支持触发器，而且触发器只能创建永久表

1. 使某些语句在事件发生时自动执行
2. 触发器：是响应MySql以下任意语句而自动执行的一条mysql语句（或位与BEGIN 与 END 语句之间的一组语句）
   • DELETE
   • INSERT
   • UPDATE
3. 创建触发器
   1. 唯一的触发器名
   2. 触发器关联的表
   3. 触发器应该响应的活动（DELETE、INSERT、UPDATE）
   4. 触发器何时执行
   5. 保证每一数据库的触发器名唯一

CREATE TRIGGER 触发器名 AFTER(BEFORE) 响应的活动 ON 表名  FOR EACH ROW 操作  -- FOR EACH ROW 对每一行
show TRIGGERS;  -- 展示所有的触发器DATABASE

-  使用CREATE TRIGGER创建 
-  AFTER 活动发生后  BEFORE活动发生前  

如果BEFORE触发器失败，则mysql将不执行请求的操作，如果BEFORE触发器或语句本身失败，mysql不执行AFTER触发器（如果有）

-  For each row的意义是：在一次操作表的语句中，每操作成功一行就会触发一次；不写的      话，表示是表级触发器，则无论操作多少行，都只触发一次； 

1. 删除触发器

DROP TRIGGER 触发器名;

触发器不能更新或覆盖，修改触发器，只能删除重写

1. 使用触发器
   1. INSERT触发器
      1. 知识点
         • 在INSERT触发器代码内，可以引用一个名为NEW的操作表，访问被插入的行
         • 在BEFORE触发器中，NEW的值也可以被更新
         • 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成的值

CREATE TRIGGER 触发器名 AFTER INSERT ON 表名  FOR EACH ROW 操作
eg:  
CREATE TRIGGER 触发器名 AFTER INSERT ON orders  FOR EACH ROW SELECT NEW.order_num;  -- 总是返回

在插入触发器中INSERT通常用作数据验证和净化

2.  DELETE触发器 
    1.  知识点 
        + 在DELETE触发器内,你可以使用**OLD**的虚拟表,访问被删除行
        + OLD的值全部都是只读,不能更新
    2.  目的是:保存被删除的每一个数据 

CREATE TRIGGER 触发器名 BEFORE DELETE ON 表名  FOR EACH ROW 
BEGIN
    INSERT INTO 新的表(列名1,列名2..) VALUES(OLD.列名1,OLD.列名2...);
END;

使用BEFORE DELETE 触发器优点是(相比于AFTER):如果由于某种原因,订单不能存档,DELETE本身将抛弃

3.  UPDATE触发器 
    1.  知识点 
        + 在UPDATE触发器内,你可以引用**OLD**虚拟表访问以前的数据(UPDATE语句之前),引用一个**NEW**的虚拟表访问更新的值
        + 在BEFORE UPDATE语句中 NEW中的值是可以更新的
        + OLD的值全部是只读,不能更新
    2.  

CREATE TRIGGER 触发器名 BEFFORE UPDATE ON 表名
FOR EACH ROW SET 操作

4.  触发器不支持CALL语句,这表示不能从触发器内调用存储过程，允许存储程序通过参数将数据返回出发程序 

21事务处理

1. MyISAM和InnoDB是最常用的引擎，前者不支持事务处理，后者支持
2. 事务处理：可以用来维护数据库的完整性，它保证成批的mysql操作要么完全执行，要么完全不执行
3. 管理事务处理的关键在于：将SQL语句组分解为逻辑块

START TRANSACTION -- 标记事务的开始
ROLLBACK -- 回退撤销MySql语句
COMMIT  -- 提交,在事务处理中提交不会隐含的进行,为进行明确的提交,使用COMMIT语句
-- 回退部分事务处理
SAVEPOINT 保留点1;
ROLLBACK TO 保留点;  -- 返回到指定保留点 ,释放保留点在(COMMIT或ROLLBACk之后指定释放),或者使用 RELEASE SAVEPOINT 保留点;释放指定保留点
-- 更改默认提交行为
SET autocommit = 0;  -- autocommit标志决定释放自动提交更改,不管有无COMMIT,设置为0不自动提交

那些语句可以回退: INSERT、UPDATE、DELETE语句可以回退，回退SELECT没有意义，不能回退CREATE或DROP操作，但在事务处理快中可以使用，执行回退，不会被撤销
隐含提交:一般的mysql语句都是直接对数据库表执行和编写的,提交是自动的

22 全球化与本地化

1. 数据库表是用来存储和检索数据的

SHOW CHARACTER SET;  -- 所有可用的字符集以及每个字符集的描述和默认校对
SHOW COLLATION;     -- 显示所有的可用校对,以及他们适用的字符集
SHOW VARIABLES LIKE 'character%';  -- 字符集
SHOW VARIABLES LIKE 'collation%';  -- 排序规则，字符集与排序规则是一对多的关系 _ci(大小写不敏感),_cs(大小写敏感),_bin(比较是基于字符编码的值)

1. 为了给表指定字符集和校对,需要使用CREATE TABLE;

CREATE TABLE 表名(
    列名   数据类型,
    ...
)DEFAULT CHARACTER SET 字符集
COLLATE 校对顺序;

如果使用CHARACTER SET 和COLLATE两者

1. 如果同时使用则使用这些值
2. 只指定了CHARACTER SET  ,则使用此字符集及其默认校对
3. 如果不指定 CHARACTER SET,也不指定COLLATE则使用默认值

校对在对用ORDER BY子句检索出来的数据排序其重要作用

1. 当数据库已经存在数据，修改字符集不会修改已经存在的数据，
2. 在客户端与服务器交互时,通过characterset(cilent / connection /results)来代表客户端,连接,返回结果的字符集,通常是相同的,通过SET NAMES 字符集 来统一修改

23 用户管理

1. 管理用户

USE  mysql;
SELECT 数据库名 FROM user;

1. 创建用户账号

CREATE USER 用户名 (IDENTIFIED BY 密码)(可无) ;  -- 创建用户名
RENAME USER 用户名 TO 新用户名;    -- 更改用户名
DROP USER 用户名;   -- 删除用户账号以及权限
SHOW GRANTS FOR 用户名;   -- 查看访问权限输出
GRANT 权限 ON 数据库中那些表 To 用户名称 @'localhost' IDENTIFIED BY 密码 ;  -- 设置访问权限
REVOKE 权限 ON 数据库中那些表 FROM 用户名称@'localhost'; -- 删除访问权限
ALTER USER 数据库名  IDENTIFIED  BY 新密码;  -- 修改密码

24 数据库维护

1. 备份数据
   1. mysql数据库的文件是基于磁盘文件
   2. 解决方案
      • 使用命令行mysqldump转储所有数据库内容到某个外部文件
      • 使用命令行实用程序mysqlhotcopy 从一个数据可以复制文件
      • 可以使用MYSQL 到BACKUP TABLE 或 SELECT INTO OUTFILE 转储所有数据到某个外部文件
2. 进行数据库维护

ANALYZE TABLE 表名;   -- 检查表键是否正确
CHECK TABLE 表名;  -- CHECK TABLE 支持一系列的用于MyISAM表的方式,CHANGED 检查最后一次检查以来改动的表
EXTENDED执行最彻底的检查   FAST只检查未正常关闭的表  MEDIUM 检查所有被删除的链接并进行键检验  QUICK  只进行快速扫描

11. 锁问题

概述:

mysql锁机制特点:不同的存储引擎支持不同的锁机制,比如MyISAM 与 MEMORY使用的是表级锁, BOD存储引擎使用的时页面锁, InnoDB存储引擎支持行级锁、表级锁

特性：

• 表级锁： 开销小，加锁快，不会出现死锁，锁定粒度最大，发生锁冲突概率最高，并发度最低，适合以查询为主
• 行级锁： 开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突概率最低，并发度最高，适合有大量按索引条件并发更新少量不同数据
• 页面锁： 开销与加锁适中，会出现死锁，锁定粒度适中，并发度一般

1. MyISAM表锁

1. MyISAM表有两种模式： 表共享读锁，表独占写锁
   读操作：不会阻塞其他用户对同一表的读请求，会阻塞对同一用户的写请求
   写操作：会阻塞其他用户对同一表的读与写操作
2. MyISAM表的读操作与写操作之间是串行的

LOCK TABLES name READ / WRITE  (LOCAL);  -- 加锁
UNLOCK TABLES;   -- 解锁

LOCAL作用是“在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录
在使用LOCK TABLES给表显示加表锁时必须同时取得所有涉及的表的锁，这也正是MyISAM不会出现死锁的原因，同一个表在SQL语句中出现多少次就要通过与SQL语句中相同的别名锁定多少次

1. 并发插入
   1. 在MyISAM存储引擎中有concurrent_insert用来控制并发插入行为
      为0时,不允许并发插入
      为1时,MyISAM表中没有空洞(表的中间没有被删除的行),允许一个进程读表时另一个进程读表的同时另一个线程从表尾插入
      为2时,无论有无空洞,都可以表尾并发插入
2. MyISAM的锁调度
   1. 一个线程获得读锁的同时另一个线程获得写锁,时进程写先获得锁
3. My

2. InnoDB锁

InnoDB是支持事务
事务及其属性

• 原子性: 事务要么全部执行,要么全部不执行
• 一致性:在事务完成前,数据都必须保证一致状态，数据库总是从一种一致性状态转换到另一种一致性状态
• 隔离性:数据库系统提供一定的隔离机制,以保证事务在不受外部并发操作影响的独立环境执行
• 持久性:事务完成后,对数据修改具有永久性

并发事务的问题:

• 更新丢失: 当多个事务同时对一个数据进行更新时,无法确保数据的更新数据确定性
• 脏读: 一个事务对数据进行修改但是还没有提交时,另一个事务对数据进行了读取（事务可以读取未提交的数据）
• 不可重复读: 一个事务在开始直到提交之前所做的任何修改对其他事务都是不可见的（两次执行同样的查询，会得到不一样的结果）
• 幻读: 一个事务在读取数据时，另一个事务在其中插入了数据，当第一个事物再次读取时会产生幻行

事务隔离级别
SERIALIZABLE（可串行化）：强制事务串行执行,会在读取的每一行上加锁

SET (SESSION) TRANSACTION ISOLATION LEVEL 隔离级别;   --  SESSION对当前事务进行隔离,不屑则是下一个事务开始时生效

InnoDB行锁征用情况

SHOW status like 'innodb_row_lock%';   -- 获取系统行锁征用情况
-- 打开监视器 ON  关闭监视器 OFF
SET GLOBAL innodb_status_output= ON;
SET GLOBAL innodb_status_output_locks=ON;

行锁：

• 共享锁： 允许一个事务读一行，阻止其他事务获取相同数据
• 排他锁：允许获得排他锁的事务更新数据，阻止阻止其他事务取得相同数据集的共享读锁和排他写锁
• 意向锁（表锁）
• 意向共享锁： 事务打算给数据行加行共享锁，事务在给一个数据行加共享锁之前必须先获得该表的意向共享锁
  意向排他锁：事务打算给数据行加行排它锁，事务在给一个数据行加排它锁前必须先取得该表的意向排它锁

共享锁： SELECT * FROM tbl_name WHERE ... FOR SHARE;
排他锁： SELECT * FROM tbl_name WHERE ... FOR UPDATE [NOWAIT|SKIP LOCKED];

共享锁：需要数据依存关系时确认某一行记录是否存在，并确保没有人对这个记录进行更新、删除操作
排他锁: 对于锁定后的记录需要进行更新操作
在遇到锁等待时NOWAIT 立即返回错误，SKIP LOCKED跳过该操作

InnoDB行锁是通过给索引上的索引项加锁实现的：

1. 在不通过索引条件查询时，InnoDB会锁定表中所有的记录
2. 使用相同的索引键进行加锁会出现锁冲突
3. 不同的事务可以使用不同的索引锁定不同的行

NET_KEY锁

当我们使用范围查询并请求共享锁或排他锁时，innoDB会对符合条件的已有数据记录的索引项加锁，
间隙：键值在范围内但不存在

使用目的： 防止出现幻读，满足恢复与赋值的需要

3. MVCC（多版本并发控制）

MVCC是一个行级锁的变种在很多情况下避免了加锁的操作

MVCC的实现：保存数据在某一个时间的快照来实现的，即不管这个事务执行的时间长短所看到的数据都是相同的但是不同的事务可能看到不同的数据

MVCC1 MVCC2

InnoDB存储引擎