MYSQL数据库知识点:\G 变换格式

https://www.iqiyi.com/v_19rtyozspk.html#curid=7767690100_2c1d99955c087eeb752bb181fea43151

索引

索引概述

mysql官方对索引的定义为：索引是帮助mysql高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现查找算法，这种数据结构就是索引

左边是数据集，一共由两列七条数据，最左边的数据记录的物理地址（注意逻辑上相邻的记录在磁盘上并不是一定物理相邻的），为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获得相应数据。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上，索引是数据库用来提高性能的最常用方法的工具

索引数据结构

索引是在mysql的存储引擎层中实现的，而不是在服务器层实现的，所以每种存储引擎都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型
* Btree 最常用的索引类型，大部分索引都支持B树索引
* Hash索引只有Memory引擎支持，使用场景简单
* R-tree(空间索引) 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间类型，比较少用
* FullText(全文索引) 全文索引也是MyISAM的一个特殊索引类型，主要是用于全文索引，InnoDB

索引的优势和劣势

优点

类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势

实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列是要占磁盘空间的
虽然索引大大提高了查询效率，同时却降低更新表的速度，如对表进行insert、update、delete。因为更新表时，Mysql不仅要保存数据，还要保存以下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

图片说明
我们平时所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引，其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引

Btree结构

Btree又叫多路平衡树，一棵m叉的Btree特征如下

树种每个节点最多包含m个孩子
除根节点与叶子节点，每个节点至少由[cell(m/2)]个孩子
若根节点不是叶子节点，则至少有两个孩子
所有的叶子节点都在同一层
每个非叶子节点由n个key与n+1个指针组成，其中ceil(m/2)<=n<=m-1 最接近的较大整数

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例

图片说明

同样数据量 B树的效率>二叉树 B树层次小于二叉树

B+树

B+tree为Btree的变种，B+Tree与Btree的区别

n叉B+Tree最多含有n个key，而Btree最多含有n-1个key
B+Tree的叶子节点保存所有的key信息，在key大小顺序排序
所有的非叶子节点都可以看作是key的索引部分

图片说明

mysql种的B+tree

Mysql索引数据结构对经典的B+tree进行优化。在原B+tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能
图片说明

索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列

索引语法

索引在创建表的时候，可以同时创建，也可以随时增加新的索引
默认创建主键索引

create [unique|fulltext|spatial] INDEX index_name索引的名称
[USING index_type] # 默认是B树
on tb1_name(index_col_name...)
index_col_name : column_name[(length)][ASC | DESC]
# 创建一个products索引
CREATE INDEX idx_pro_name on products(prod_id);

查看索引

show index from table_name
show index from products;

图片说明
删除索引删除那个表那个index

drop index idx_pro_name on products

Alter命令

alter table product add primary key idx_primary(prod_id); # 增加主键索引，这意味着索引值必须是唯一的，且不能为null
# alter table products add unique idx_unique(prod_id); # 这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)
# alter table products add fulltext idx_text_name(prod_desc); # 该语句指定了索引为FULLTEXT 用于全文索引
# alter table products add index idx_pro_name(vend_id);  # 添加普通索引，索引值可以出现多次
show index from products;

索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量符合这些原则，便于提升索引的使用效率，最搞笑的使用索引

对查询频率较高，且数据量比较大的表创建索引
索引字段的选择，最佳候选列应当从where子句的条件种提取，如果where子句的组合比较多，那么应当挑选最常用，过滤效果最好的组合
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越***护索引的代价自然高，对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应的操作时间消耗，另外索引过多的话，MYSQL也会犯选择困难症，虽然最终仍然会找到一个可用的索引，但无疑提高了选择代价
使用短索引，索引创建之后也是使用硬盘来存储，因此提升索引访问的I/O效率，也可以提升总体的访问效率，假如构成索引的字段总长度较短，那么在给定大小的存储块内可以存储更多的索引值，相对应的可以有效的提升MYSQL访问索引的I/O效率。
利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时WHERE子句中使用了组合该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率
```
create index idx_name_email ON tb_seller(sname, seamil,sstatus);
# 相当于
对 sname 创建索引
对 sname , seamil 创建索引
对 sname, seamil,sstatus 创建索引
```

视图

视图概述

视图是一种虚拟存在的表，视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，通俗的讲，视图就是一条select语句执行后返回的结果集，所以我们在创建视图的时候，主要的工作就落在创建这条sql查询上。视图相对于普通的表的优势主要包括以下几项。

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经时过滤好的符合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列。但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，原表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的
存储过程和函数的区分再与函数有返回值，存储过程没有
函数：是一个由返回值的过程
过程：是一个没有返回值的函数

# show procedure status; # 查询存储过程的状态信息
show create procedure ordertotal; 查看某个存储过程
变量定义在begin中
declare var_name type

delimiter //
create procedure myproce()
begin 
    declare num int default 10;
    set num = num + 10 ;
    select num;
end //
delimiter ;
call myproce();
select count(*) into xxx;

if条件判断输入输出

drop procedure myproce;
delimiter //
create procedure myproce()
begin 
    declare boo int default 2;
    declare num int;
    if boo = 0 then 
        set num = 0;
    elseif boo = 1 then
        set num = 1;
    else
        set num = 2;
    end if;
    select num ;
end //
delimiter ;
call myproce();

IN：该参数可以作为输入，也就是需要调用方传入值，默认
OUT：该参数作为输出，也就是该参数可以作为返回值
INOUT：既可以作为输入参数，也可以作为输出参数

call pro_text(180,@description) @来定义一个值用于接收值
select @description @用户会话变量
@@ 系统变量

case结构需要放在存储过程中

伪代码
case case_value
    WHEN when_value THEN statement_list 
    WHEN when_value THEN statement_list
    ELSE statement_list
end case;

delimiter //
create procedure myproce2(num int)#默认为in
begin
    case
        when num >= 3 then select '>=3';
        when num >=2 then select '>=2';
        when num >=1 then select '>=1';
        when num >=0 then select '>=0';
        else 
            select '....';
    end case;
end //
delimiter ;
call myproce2(1); # 执行一次就跳出了

where循环

delimiter //
create procedure myproce(num int)
begin 
    declare count int default 0;
    declare i int default 0;
    while i <= num  do
        set count = count + i;
        set i = i + 1;
    end while ;
    select count;
end //
delimiter ;
call myproce(10);

repeat循环

drop procedure myproce;
delimiter //
create procedure myproce(num int)
begin 
    declare count int default 0;
    repeat
        set count = count + num;
        set num = num - 1;
        until num = 0 # 满足条件退出循环
    end repeat ;
    select count;
end //
delimiter ;
call myproce(10);

loop循环

drop procedure myproce;
delimiter //
create procedure myproce(num int)
begin 
    declare count int default 0;
    # 满足条件退出循环
    c:loop  # c是别名
        set count = num + count;
        set num = num - 1;
        if num <= 0 then
            leave c;
        end if;
    end loop c;
    select count;
end //
delimiter ;
call myproce(10);

游标

代码 : 超过次数会报错

SELECT order_num FROM orders;
DROP PROCEDURE processorders;
delimiter //
CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernum int;
    DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
    OPEN ordernumbers;
    FETCH ordernumbers INTO ordernum;   # 传入到ordernum 也可以传入多个值 用,隔开
    SELECT ordernum;                     # 选择一行 重新执行则重新开始
    FETCH ordernumbers INTO ordernum;    # 同一次执行中的次次
    SELECT ordernum;
    CLOSE ordernumbers;
END //
delimiter ;
call processorders();                   # 用call调用

升级代码：解决没有数据还报错，也解决用多个语句来执行

count(*) - > num
变量  -> 边界变量
SELECT order_num FROM orders;
DROP PROCEDURE processorders;
delimiter //
CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernum int;
    DECLARE has_data int default 1;

    DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
    DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;  # 位置需要在定义游标下面 意思为如果找不到则将has_data 设置为0

    OPEN ordernumbers;
    repeat                           # 循环
        FETCH ordernumbers INTO ordernum;
        SELECT ordernum;
        until has_data = 0            # 终止条件
    end repeat;               
    CLOSE ordernumbers;
END //
delimiter ;
call processorders();

存储函数

存储函数有返回值
https://blog.csdn.net/ty_soft/article/details/6940190 ：创建function报错解决

set global log_bin_trust_function_creators=TRUE;
delimiter //
create function myfunction () 
returns int
begin
    declare cnum int ;
    select count(*)into cnum from products;
    return cnum;
end // 
delimiter ;
select myfunction();

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或者之后，触发并执行触发器中定义的sql语句集合。触发器的这种特征可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的，现在触发器还只支持行级触发，不支持语句级触发
图片说明

delimiter //
create trigger trigger_prod
before/after insert/update/delete
on products
[for each row]
trigger_stmt;

# 首先创建一张日志表
create table prod_logs(
    id int(11) not null AUTO_INCREMENT primary key,
    operation varchar(20) not null comment'操作类型，insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作表的ID',
    operate_params varchar(500) comment '操作参数'
)engine = innodb default charset = utf8;

delimiter //
create trigger trigger_prod
after insert
on products
for each row
begin
    insert into prod_logs (operation,operate_time,operate_id,operate_params) VALUES('insert',now(),new.prod_id(),
    Concat('插入后(prod_id:',new.prod_id,',vend_id:',new.vend_id,',prod_name:',new.prod_name,',prod_desc:',new.prod_desc,')'));
end //
delimiter ;
更新
insert into prod_logs (operation,operate_time,operate_id,operate_params) VALUES('update',now(),new.prod_id(),
    Concat('插入前(prod_id:',old.prod_id,',vend_id:',old.vend_id,',prod_name:',old.prod_name,',prod_desc:',old.prod_desc,')',
    '插入后(prod_id:',new.prod_id,',vend_id:',new.vend_id,',prod_name:',new.prod_name,',prod_desc:',new.prod_desc,')'));
删除
insert into prod_logs (operation,operate_time,operate_id,operate_params) VALUES('delete',now(),new.prod_id(),
    Concat('删除后(prod_id:',old.prod_id,',vend_id:',old.vend_id,',prod_name:',old.prod_name,',prod_desc:',old.prod_desc,')');

删除查看触发器

show triggers;
drop triggers

declare CONTINUE HANDLER FRO SQLSTATE '02000' SET done = 1
02000 主要代表的意思可以理解为：
发生下述异常之一：
SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。
在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。
在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
就是说你定义了，当fetch游标到了数据库表格最后一行的时候，设置done=1.