MySQL基本知识（索引、存储过程、触发器、视图和游标）

• 索引

1. 什么是索引

索引将一个文件的每个记录在某个或某些域（或称为属性）上的取值与该记录的物理地址直接联系起来，提供了一种根据记录域的取值快速访问文件记录的机制。索引技术的关键是建立记录域取值到记录的物理地址间的映射关系，这种关系被称为索引。索引的数据结构是B+树。

 

1. 索引的优缺点

索引大幅度提高了查询速度；但是①占用了存储空间，另外，②对数据进行增删改操作时，为了使索引与数据保持一致，需要对索引进行维护。

 

1. 索引的创建原则

对查询频率高的字段创建索引；

对排序、分组、联合查询频率高的字段创建索引；

选择唯一性索引、若在实际中，需要将多个列设置索引时，可以采用多列索引；

尽量使用数据量少的索引、使用前缀来索引；

索引的数目不宜太多、删除不再使用或者很少使用的索引。

具有大量写操作的OLTP系统不适合创建索引；

具有大量更新操作的数据库不适合创建索引；

不涉及聚合或连接的查询不适合创建索引；

GROUP BY列具有高基数度的数据聚合不适合创建索引，高基数度表示列包含许多不同的值。

 

1. 创建和删除索引

例：在Table表的Cname列和CardID列上创建一个唯一性聚集索引，要求索引键值按Cname升序和CardID降序排序。

CREATE UNIQUE CLUSTERED INDEX COMP_ind ON Table(Cname ,CardID desc);

例：删除Table表中的COMP_ind索引。

DROP INDEX COMP_ind;

 

 

 

• 存储过程

1. 什么是存储过程？

SQL语句需要先编译后执行，而存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。存储过程本质上是存储在数据库中供所有用户程序调用的子程序。

 

1. 存储过程的作用

模块化设计，存储过程创建一次，多次调用；存储过程是一个编译过的代码块，执行效率要比T-SQL语句高；一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率；通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

 

1. 存储过程的使用

3.1 创建存储过程

create procedure proc_name(参数列表)

begin

...SQL语句...

end

3.2 调用存储过程

call proc_name();

注意：括号不能省略

3.3 删除存储过程

drop procedure proc_name;

 

 

 

 

• 触发器

1. 什么是触发器

触发器是一种特殊的存储过程，其特殊性在于它不需要由用户来直接调用，而是在对表中的数据进行增删改操作时自动触发执行。

 

1. 触发器的作用

触发器通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束的功能。

 

1. 创建触发器

CREATE TRIGGER trigger_name

    trigger_time

    trigger_event ON tbl_name

    FOR EACH ROW

    begin

      ……

    end

参数解释：

trigger_name 为将定义的触发器名字

trigger_time 触发器的执行时机（AFTER或者BEFORE）。before就是在sql执行之前，先执行触发器；after相反。

trigger_event 触发器的触发事件（常见的有3种：insert、uodate、delete）

tbl_name 则是触发器要加在哪张表中

begin end之间写程序体，如果仅有1条语句，则可以不用begin end，直接将语句写在后面。

 

 

4、查看触发器

语法：SHOW TRIGGERS FROM [库名]

将查出来整个库下面的所有触发器：

show triggers from xl_test

 

5、删除触发器

语法：DROP TRIGGER [库名].[触发器名称]

DROP TRIGGER xl_test.tri_updateInviteCount;  

 

6、前触发型触发器和后触发型触发器

前触发型触发器：使用INSTEAD OF定义，执行触发器指定的内容而不是执行引发触发器执行的SQL语句；

后触发型触发器：使用FOR或AFTER定义，只有在引发触发器执行的SQL语句都已经成功执行，并且所有的约束检查也成功完成后，才执行触发器。

 

• 视图

1. 什么是视图？

视图是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。在数据库中，存放的只是视图的定义，视图并不在数据库中实际存在，行和列数据来自由定义视图查询所引用的表，并且在引用视图时动态生成。

 

1. 视图的作用

简单：使用视图的用户完全不需要关系后面对应的表结构、关联条件和筛选条件，对用户来说已经是过滤好的符合条件的结果集；

安全：使用视图的用户只能访问他们被允许的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现；

数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

 

1. 视图的使用

3.1 视图的创建

CREATE [OR REPLACE] VIEW 视图名(列1，列2...)

AS select语句

[WITH [CASCADED|LOCAL] CHECK OPTION]

3.2 删除视图

DROP VIEW view_name;

 

1. 注意事项

MySQL不支持物化视图（将视图结果数据存放在一个可以查看的表中，并定期充原始表中刷新数据到这个表中）、也不支持在视图中创建索引。

 

 

• 游标

1. 什么是游标？

 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。

 

1. 游标的作用

游标总是与一条SQL选择语句相关联，用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

 

1. 游标的使用

3.1 声明游标

DECLARE cursor_name CURSOR FOR select_statement;

select_statement 的结果集对应当前定义的游标。

这时还没有开始检索数据。select_statement 中不能有INTO子句。

3.2 打开游标

OPEN cursor_name;

在定义了游标之后就可以使用 “OPEN” 关键字打开游标，这时会执行（select_statement）查询得到结果集。

默认情况，游标的指针一开始是指向结果集的第一行之前的。

3.3 使用游标

FETCH cursor_name INTO var_name [, var_name] ...

使用 “FETCH” 和 “INTO” 关键字将当前游标所指向的记录的字段值赋值给定义好的变量中。每执行一次 “FETCH” 语句，指针向下移动一行。

select_statement 结果集的字段数（列数）必须要和 “INTO” 后面的变量一致，否则会报错。

3.4 关闭游标

CLOSE cursor_name;

“CLOSE” 将释放游标使用的所有内部内存和资源。如果游标未被明确地关闭，游标将在它被声明的复合语句的末尾（END）被自动关闭。

游标关闭后就不可以继续使用了，除非使用 “OPEN” 再次打开该游标。存储过程结束后，游标也就消失了。