mysql查询语法和执行步骤，以及索引，视图和触发器

查询语法和执行步骤

查询语法和执行步骤传送门

索引

索引是什么？

索引是帮助mysql高效获取数据的排好序的数据结构
（类似于字典页）

索引数据结构

• 二叉树
  不适合有序key进行索引查找，有序key会退化成链表
• 平衡二叉树（红黑树）
  改进了二叉树的的退化成链表，但是I/O次数还是很多，为logn（以2为底）次
• 平衡多叉树（B树，B+树）
  B树：在平衡二叉树的基础上将二叉变为多叉，也就是不再是一次提取一个key进内存进行搜索，而是一次提取多个key进内存进行搜索，当key个数固定的时候，树的高度变小，有效减少了I/O次数。（若为m叉树，那么I/O次数为logn（以m为底）），但仍然保留了平衡二叉树的一个key就要带上key所在的key的记录的物理地址（但是没有key的冗余），这样的话降低了有限的空间内存储key的密度，因此放入内存的索引会降低key的存储密度。
  B+树：在B树上进行了两个改进：1.允许冗余key的存在，并将key所在的记录的物理地址放在叶子节点上。在子节点中选取key放入父结点中，提高了key的存储密度，这样就能在key个数固定的情况下相同的节点空间就能存储更多的key，就能减少树的高度，这样就能减少I/O次数。2.将各个叶子节点用指针相连，这样，就能支持区间查找。
• hash表
  根据key的值进行散列，理论上查找单个值最优秀，但是会有冲突产生，也不支持区间查找（因为当条件子句中含有区间查找>6等时，就会无法进行查找，而B+树叶子节点按照有序排列，因此找到符合结点之后，可以根据区间合理查找）

为什么索引可以高效获取数据呢？
我们要知道首先，数据库的数据都存储在磁盘上，如果我们把所有数据库的数据都放入内存，会造成内存空间不够的情况，因此我们只能将数据库的数据存放在磁盘上，内存中加载一定的索引机制，这样能有效减少I/O次数。

先假设一个问题，如果没有索引，就按照表的顺序从第一条开始查找，那么就相当于是一个链式查找的效率，并且由于表都存放在磁盘上，因此我们每查一条数据就需要I/O一次，会造成效率十分低下。

因此我们需要为表建立合适索引，利用对表的某些属性为key建立合适的索引数据结构，就能提高查找的效率。

我们在数据结构中已经学过了二分查找，也就是二叉树查找，二叉树查找可以将查询时间进行一定的优化，那么我们就可以建立二叉树这样的索引结构，具体如下：

typedef struct TNode{//二叉树结点
	int key;//以某个属性为关键字
	int dizhi;//存储该关键字key所在的那条记录的地址
	struct TNode * lchild;//指向的左孩子
	struct TNode * rchild;//右孩子
}

我们只需要对key进行二分查找（二叉树搜索），就能减少查找的时间，然后得到该key所在的表的记录所在的物理地址，就能找到该记录了，这样可以从n的时间复杂度变成logn的时间复杂度，并且I/O次数也相对于链表的n次减少了很多，只需要logn次I/O即可，大大节省了开销。
但是，当碰到有序的key时，就会退化成链表，因此，不适合作为数据库的索引结构。

因为二叉树的这些缺点，所以大佬们针对二叉树进行了一定的改造，变成了平衡二叉树（红黑树）

红黑树规定了左右子树高度之差不能超过1，因此有效的防止了二叉树退化成链表，提高了查询效率，但是大佬们还不满足，虽然红黑树已经能减少把I/O次数减少到logn（以二为底）了，但是当数据很大的时候，查找还是需要很多次的I/O，为了解决I/O次数过多的问题，B树应运而生


B树在平衡二叉树的基础上增加了叉树，也增加了一个树节点所能容纳的key的个数，这样就能一次多读取几个key进入内存，从而减少了树的高度，减少了I/O次数。缺点：因为还是在二叉树的基础上改进的，每一个key还是对应有一个dizhi，这样在一个树节点的有效空间内，key的存储密度就下降了，并且，B树不支持区间查找，因为key并不是都在叶子节点上，查找的key可能在不同层上，所以没法有效做到区间查询。
那么如何解决呢？ 大佬们又提出了B+树，B树2.0.


我们可以看到，所有的关键字key都在叶子层出现了，并且有指针有序相连，这样我们在进行区间查询的时候，我们只需要固定区间的两端key，然后按照在叶子结点找到的key开始遍历区间中的所有结点就能找到所有记录，因此B+树在B树的基础上进行了区间查找的优化。
此外，因为B树是每个结点都有key和dizhi，这样降低了key的存储密度，B+树只有叶子节点处，才保存有dizhi,所以B+树节点中的key密度会更高，因此I/O次数也会更少。

所以综上所述，B+树是最优秀的索引结构，一方面I/O次数足够优秀，另一方面支持区间查找。

聚集索引和非聚集索引

非聚集索引：以该属性为key的B+树索引结构中，叶子节点中保存的是该key关键字所在的记录的磁盘地址

聚集索引：以该属性为key的B+树索引结构中，叶子节点中保存的是该key关键字以及该条记录的其余属性值

为什么索引必须有主键，并且推荐使用整型的自增主键？
因为key的搜索时，整型所占空间较小，并且比较起来开销小，如果是字母比较还需要转成ASCII码比较，而且要比较每一位。所以使用整型
而又因为自增的key在构建B树的时候所要进行的分裂和合并开销最小，如果不是自增的插入key的话，那么就会因为合并和分裂产生大量的开销，并且，叶子节点处也要求是自增有序，因此推荐使用整型的自增主键。

联合索引

联合索引实质上类似于结构体排序，将整个索引看成一个结构体然后按照结构体排序来进行排序

索引优缺点

优点：能够提升查找的效率（联想查字典）
缺点：因为不止要维护一个表，还需要维护索引，因此插入删除修改等都会造成效率的降低

什么时候创建索引：

1. 当表项数据很少的时候不需要创建索引，如果只有一行数据创建索引没有必要
2. 主键自带聚集索引，因此没有必要再围绕主键建立索引

索引语法

语法如下：

创建索引
//修改表，给他增加索引
1.alter table 表名 add [unique] index 索引名(表的列名)
//创建索引在表上
2.create [unique](可选) index 索引名 on 表名（列名1，列名2，....）；

删除索引
1.drop index 索引名 on 表名
2.alter table 表名 drop index 索引名

注意：

1. unique是可选的，添加以后表示当前列是唯一索引
2. 可以同时为多个列建立索引，称为复合索引，建立好复合索引以后，只有这些列同时出现在where的后面，索引才会起作用。，不推荐使用
3. 索引名字除在删除索引时有用外，其他时候没有作用，但是不能重复。

视图

视图是什么？

视图的定义
视图是基于一个或多个表或视图的逻辑表，本身不含数据，通过它可以对表里面的数据进行查询和修改。
视图基于的表称为基表。
视图是存储在数据字典里的一条select语句。
通过创建视图可以提取数据的逻辑上的集合或组合。

视图的作用：
1.当我们多次使用同一个select语句时，我们可以把该select语句包装起来，创建成视图，这样下次再用的时候，我们就可以直接对视图进行操作了，不用再写一长串sqll语句了。而用户可以不知道封装的底层细节，可以直接拿来用即可。
2.因为用户可以不知道封装的底层细节，所以可以对用户屏蔽掉机密的表，只让他操作视图中可以操作的表即可。

视图语法

• 查询视图

create view 视图名称 as 
子查询

• 修改视图

replace view 视图名称 as 查询语句
alter view 视图名称 as 查询语句

• 删除视图

drop view 视图名称

别的增删改查数据可以将视图当成表用

触发器

触发器的定义
在mysql中也就是对某一个表的一定的操作，触发某种条件（insert，update，delete等），从而自动执行的一段程序，就像传感器一样。

触发器的语法

• 创建触发器

create trigger 触发器名称  //创建触发器，命名
after/before             //选择触发时机
insert/update/delete on 表名//作用在哪个表上
for each row //对每一行
程序体（可以是begin...end（保证操作的原子性）,也可以是一句sql）

注：
new是新值，代表新行，
old是旧值，代表旧行，
insert只有new
update有new和old
delete只有old
----------------------------------------------------------------------------------------------------------------------------------------------------
example：
创建两种表t1，t2两种表中都有id，name字段，新加t1表记录后自动添加t2

create trigger t
after insert
on t1
for each row
insert into t2(name) values(new.name);
//其中new代表新插入的行记录，新插入的行中的name字段

存储过程

存储过程的语法

delimiter//前后各一个，用来标识哪个地方开始和结束
create procedure 过程名(in 输入参数 输入参数类型， out 输出参数 输出参数类型)
//若有inout x int，表示x既可以做输入，也可以做输出，详情看例子
begin//原子操作开始
	过程实现
end
delimiter

注：mysql中的变量前面加上@

example
存储查询给定部门的平均工资的过程

delimiter
create procedure emp_avg_sal(in dept_no int,out avg_sal decimal(8,2))
begin
	select avg(e.sal) into avg_sal from emp e where e.deptno=dept_no;
end
delimiter;
call emp_avg_sal(10,@result);
select @result;

create procedure pro(inout x int)
begin
	set x=x+1；
end

//调用
set @x=10;
select @x;
call pro(@x);
select @x;

创建函数

创建函数语法

create or replace function 函数名(参数 参数类型)
returns 返回值类型
begin
	return 返回值;
end
//调用
select 函数名称(参数列表)

可以看出，函数在存储过程的基础上，取消了out输出参数，将输出参数改成了返回值

example：
将下述存储过程转换成函数

存储过程

delimiter
create procedure emp_avg_sal(in dept_no int,out avg_sal decimal(8,2))
begin
	select avg(e.sal) into avg_sal from emp e where e.deptno=dept_no;
end
delimiter;
call emp_avg_sal(10,@result);
select @result;

函数

delimiter
create function emp_avg_sal_fun(dept_no int)
returns decimal(8,2);
begin
	return (select avg(e.sal) from emp e where e.deptno=dept_no);
end
delimiter
//函数调用
select emp_avg_sal_fun(10);