MYSQL数据库高级应用宝典含实例(索引、视图、触发器、游标和存储过程)

96skill技能培训中心 http://wendang.chazidian.com

9yuib声明：此文档是小弟学习时收集而来，感觉比较经典，特分享出来，大家可以共勉。

MYSQL数据库的索引、视图、触发器、游标和存储过程

（1） 索引（index） ---------------------------------------------------------- 2

（2） 视图（view） ----------------------------------------------------------- 3

（3） 触发器（trigger） ---------------------------------------------------- 7

（4） 游标（cursor） ---------------------------------------------------------- 9

（5） 事务(Transaction) ---------------------------------------------------- 11

（6） 存储过程（Stored Procedure） ------------------------------------- 13 1

96skill技能培训中心 http://wendang.chazidian.com

（1） 索引（index）

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

优点：

大大加快数据的检索速度;

创建唯一性索引，保证数据库表中每一行数据的唯一性;

加速表和表之间的连接;

在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

索引需要占物理空间

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

更好的理解索引的提示：

? 如果经常使用表中的某一列或某几列为条件进行查询，且表中的数据量比较大时，

可以创建索引，以提高查询的速度。

? 索引是与表关联的可选结构。

? 通过有目的的创建索引，可以加快对表执行SELECT语句的速度。

? 不管索引是否存在，都无需修改任何SQL语句的书写方式。索引只是一种快速访

问数据的途径，它只影响查询执行的效率。

? 可以使用CREATE INDEX命令在一列或若干列的组合上创建索引。

? 创建索引时，将获取要创建索引的列，并对其进行排序。然后，将一个指针连同每

一行的索引值存储起来，组成键值对（目录名和页码）。使用索引时，系统首先通过已排序的列值执行快速搜索，然后使用相关联的指针值来定位具有所要查找值的行。

? 一旦创建了索引，MySQL会自动维护和使用它们。

? 只要修改了数据，如添加新行、更新现有行或删除行， MySQL都会自动更新索引。 ? 但是为表创建过多的索引会降低更新、删除以及插入的性能，因为MySQL还必须

更新与该表关联的索引。

索引的分类

普通索引：这是最基本的索引，它没有任何限制

唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允

许有空值。如果是组合索引，则列值的组合必须唯一

主键索引（通过主键约束间接创建）：它是一种特殊的唯一索引，不允许有空值。

一般是在建表的时候同时创建主键索引

组合索引：在表中的多个列上创建的索引。组合索引中列的顺序是任意的，可以

是相邻的列，也可以是不相邻的列。

2

96skill技能培训中心 http://wendang.chazidian.com

索引的创建：

普通（唯一）索引的创建：

CREATE [UNIQUE] INDEX index_name ON tbl_name(index_col_name,…)

index_col_name: col_name[(length)][ASC|DESC]

对于字符类型的列，可以编制“前缀索引”，Length表示按照列的指定长度的字符串索引

创建组合索引：

CREATE INDEX index_name ON tbl_name(index_col_name1,index_col_name2,..) 基于（列A，列B）两列创建索引：

? 可应用索引的情况：A;AB两列结合;

? 不可用索引的情况：B

? Create index index_name on table_name(列A，列B)；

其他创建索引的方式：

? 创建表时创建索引：

CREATE TABLE tbl_name

(

列的定义，……，

INDEX|KEY [idx_name](index_col_name)

);

Eg:

CREATE TABLE t1

(tid int primary key, #既创建约束，又创建索引

tname varchar(20),

index idx_tname(tname), #创建一个普通索引

tbirthday date

);

? 修改表时创建索引:

ALTER TABLE tbl_name ADD INDEX|KEY [idx_name](indxe_col_name);

Eg:

ALTER TABLE t1 ADD KEY (tbirthday);

查看索引：Show index|keys from 表名；（SHOW KEYS FROM t1;）

删除索引：drop index 索引名 on 表名。（drop index c on t3;）

（2） 视图（view）

从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表。从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。 视图是一个虚拟表，其内容由查询定义。

概述:

3

96skill技能培训中心 http://wendang.chazidian.com

? 视图以经过定制的方式显示来自一个或多个表的数据

?

?

?

?

视图是一种数据库对象，用户可以象查询普通表一样查询视图。 视图内其实没有存储任何数据，它只是对表的一个查询。 视图的定义保存在数据字典内。创建视图所基于的表为“基表”。 视图一经定义以后，就可以像表一样被查询、修改、删除和更新 作用：精华志 京华志 毕业设计辅导咨询 9361235@http://wendang.chazidian.com ?

?

?

? 简化数据查询语句 使用户能从多角度看到同一数据 提高了数据的安全性 提供了一定程度的逻辑独立性

? 减少带宽流量、优化后还可提高执行效率

优点：

?

?

?

? 提供了另外一种级别的表安全性 隐藏的数据的复杂性 简化的用户的SQL命令 通过重命名列，从另一个角度提供数据

视图的创建：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

说明：

? OR REPLACE：给定了OR REPLACE子句，语句能够替换已有的同名视图。 ? ALGORITHM：可选的mysql算法扩展，算法会影响MySQL处理视图的方

式。有以下三个值：

UNDEFINED--MySQL将选择所要使用的算法。如果可能，它倾向于

MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

MERGE--会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

TEMPTABLE--视图的结果将被置于临时表中，然后使用它执行语句。

? veiw_name:视图名。

? column_list：要想为视图的列定义明确的名称，列出由逗号隔开的列

名。column_list中的名称数目必须等于SELECT语句检索的列数。若

使用与源表或视图中相同的列名时可以省略column_list。

? select_statement: 用来创建视图的SELECT语句，可在SELECT语

句中查询多个表或视图。但对SELECT语句有以下的限制：

1. 定义视图的用户必须对所参照的表或视图有查询（即可执行SELECT

语句）权限；

2. 在定义中引用的表或视图必须存在；

4

96skill技能培训中心 http://wendang.chazidian.com

? WITH [cascaded|local] CHECK OPTION: 在关于可更新视图的WITH

CHECK OPTION子句中，当视图是根据另一个视图定义的时，LOCAL和

CASCADED关键字决定了检查测试的范围。LOCAL关键字对CHECK OPTION

进行了限制，使其仅作用在定义的视图上，CASCADED会对将进行评估的基表

进行检查。如果未给定任一关键字，默认值为CASCADED。WITH CHECK

OPTION指出在可更新视图上所进行的修改都要符合select_statement所指定的

限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。

? 视图定义服从下述限制：

?

?

?

?

? SELECT语句不能包含FROM子句中的子查询。 SELECT语句不能引用系统或用户变量。 SELECT语句不能引用预处理语句参数。 在存储子程序内，定义不能引用子程序参数或局部变量。 在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的

表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。 ? 在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

? 在视图定义中命名的表必须已存在。

? 不能将触发程序与视图关联在一起。 修改视图：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

说明：

该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。该语句需要具有针对视图的CREATE VIEW和DROP权限，也需要针对SELECT语句中引用的每一列的某些权限。

查看视图：精华志 京华志 毕业设计辅导咨询 9361235@http://wendang.chazidian.com

SHOW CREATE VIEW view_name

说明：

该语句给出了1个创建给定视图的CREATE VIEW语句。

删除视图：

DROP VIEW [IF EXISTS]

view_name [, view_name] ...

[RESTRICT | CASCADE]

说明：

? DROP VIEW能够删除1个或多个视图。必须在每个视图上拥有DROP权限。 ? 可以使用关键字IF EXISTS来防止因不存在的视图而出错。

5