mysql高级部分非常实用的不要分包含索引建立优化函数存储过程触发器及游标.docx

mysql高级部分非常实用的不要分包含索引建立优化函数存储过程触发器及游标.docx

《mysql高级部分非常实用的不要分包含索引建立优化函数存储过程触发器及游标.docx》由会员分享，可在线阅读，更多相关《mysql高级部分非常实用的不要分包含索引建立优化函数存储过程触发器及游标.docx（39页珍藏版）》请在冰豆网上搜索。

mysql高级部分非常实用的不要分包含索引建立优化函数存储过程触发器及游标

Mysql高级部分

（1）索引（index）1

（2）视图（view）2

（3）触发器（trigger）6

（4）游标（cursor）8

（5）事务（Transaction）10

（6）存储过程（StoredProcedure）12

（1）索引（index）

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

优点：

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

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

加速表和表之间的连接;

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

缺点：

索引需要占物理空间

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

更好的理解索引的提示：

✓如果经常使用表中的某一列或某几列为条件进行查询，且表中的数据量比较大时，可以创建索引，以提高查询的速度。

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

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

✓不管索引是否存在，都无需修改任何SQL语句的书写方式。

索引只是一种快速访问数据的途径，它只影响查询执行的效率。

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

✓创建索引时，将获取要创建索引的列，并对其进行排序。

然后，将一个指针连同每一行的索引值存储起来，组成键值对（目录名和页码）。

使用索引时，系统首先通过已排序的列值执行快速搜索，然后使用相关联的指针值来定位具有所要查找值的行。

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

✓只要修改了数据，如添加新行、更新现有行或删除行，MySQL都会自动更新索引。

✓但是为表创建过多的索引会降低更新、删除以及插入的性能，因为MySQL还必须更新与该表关联的索引。

索引的分类

Ø普通索引：

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

Ø唯一索引：

它与前面的普通索引类似，不同的就是：

索引列的值必须唯一，但允许有空值。

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

Ø主键索引（通过主键约束间接创建）：

它是一种特殊的唯一索引，不允许有空值。

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

Ø组合索引：

在表中的多个列上创建的索引。

组合索引中列的顺序是任意的，可以是相邻的列，也可以是不相邻的列。

索引的创建：

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

CREATE[UNIQUE]INDEXindex_nameONtbl_name（index_col_name,…）

index_col_name:

col_name[（length）][ASC|DESC]

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

Ø创建组合索引：

CREATEINDEXindex_nameONtbl_name（index_col_name1,index_col_name2,..）

基于（列A，列B）两列创建索引：

✧可应用索引的情况：

A;AB两列结合;

✧不可用索引的情况：

B

✧Createindexindex_nameontable_name（列A，列B）；

Ø其他创建索引的方式：

✧创建表时创建索引：

CREATETABLEtbl_name

（

列的定义，……，

INDEX|KEY[idx_name]（index_col_name）

）;

Eg:

CREATETABLEt1

（tidintprimarykey,#既创建约束，又创建索引

tnamevarchar（20）,

indexidx_tname（tname）,#创建一个普通索引

tbirthdaydate

）;

✧修改表时创建索引:

ALTERTABLEtbl_nameADDINDEX|KEY[idx_name]（indxe_col_name）;

Eg:

ALTERTABLEt1ADDKEY（tbirthday）;

Ø查看索引：

Showindex|keysfrom表名；（SHOWKEYSFROMt1;）

Ø删除索引：

dropindex索引名on表名。

（dropindexcont3;）

（2）视图（view）

从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。

从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表。

从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。

视图是一个虚拟表，其内容由查询定义。

概述:

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

✓视图是一种数据库对象，用户可以象查询普通表一样查询视图。

✓视图内其实没有存储任何数据，它只是对表的一个查询。

✓视图的定义保存在数据字典内。

创建视图所基于的表为“基表”。

✓视图一经定义以后，就可以像表一样被查询、修改、删除和更新

作用：

✓简化数据查询语句

✓使用户能从多角度看到同一数据

✓提高了数据的安全性

✓提供了一定程度的逻辑独立性

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

优点：

✓提供了另外一种级别的表安全性

✓隐藏的数据的复杂性

✓简化的用户的SQL命令

✓通过重命名列，从另一个角度提供数据

视图的创建：

CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]

   VIEWview_name[（column_list）]

   ASselect_statement

[WITH[CASCADED|LOCAL]CHECKOPTION]

说明：

●ORREPLACE：

给定了ORREPLACE子句，语句能够替换已有的同名视图。

●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.在定义中引用的表或视图必须存在；

●WITH[cascaded|local]CHECKOPTION:

在关于可更新视图的WITHCHECKOPTION子句中，当视图是根据另一个视图定义的时，LOCAL和CASCADED关键字决定了检查测试的范围。

LOCAL关键字对CHECKOPTION进行了限制，使其仅作用在定义的视图上，CASCADED会对将进行评估的基表进行检查。

如果未给定任一关键字，默认值为CASCADED。

WITHCHECKOPTION指出在可更新视图上所进行的修改都要符合select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。

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

✓SELECT语句不能包含FROM子句中的子查询。

✓SELECT语句不能引用系统或用户变量。

✓SELECT语句不能引用预处理语句参数。

✓在存储子程序内，定义不能引用子程序参数或局部变量。

✓在定义中引用的表或视图必须存在。

但是，创建了视图后，能够舍弃定义引用的表或视图。

要想检查视图定义是否存在这类问题，可使用CHECKTABLE语句。

✓在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

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

✓不能将触发程序与视图关联在一起。

修改视图：

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

   VIEWview_name[（column_list）]

   ASselect_statement

   [WITH[CASCADED|LOCAL]CHECKOPTION]

说明：

该语句用于更改已有视图的定义。

其语法与CREATEVIEW类似。

该语句需要具有针对视图的CREATEVIEW和DROP权限，也需要针对SELECT语句中引用的每一列的某些权限。

查看视图：

SHOWCREATEVIEWview_name

说明：

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

删除视图：

DROPVIEW[IFEXISTS]

   view_name[,view_name]...

[RESTRICT|CASCADE]

说明：

●DROPVIEW能够删除1个或多个视图。

必须在每个视图上拥有DROP权限。

●可以使用关键字IFEXISTS来防止因不存在的视图而出错。

●如果给定了RESTRICT和CASCADE，将解析并忽略它们。

更新视图：

概述：

Ø视图的使用与表一样，有增删改查四种操作，且语法也与表相同。

Ø在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE可以统称为“通过视图更新数据”。

Ø通过视图更新数据有如下限制：

✓一次只能修改一个底层的基表

✓如果修改违反了基表的约束条件，则无法更新视图

✓如果视图中的列不是表中的原始列（如创建视图时使用了连接操作符、聚合函数等），则不能通过视图更新。

视图更新操作：

Ø可更新的视图：

要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSET、UPDATE或DELETE等语句当中使用它们。

对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。

还有一些特定的其他结构，这类结构会使得视图不可更新。

如果视图包含下述结构中的任何一种，那么它就是不可更新的：

✓聚合函数；

✓DISTINCT关键字；

✓GROUPBY子句；

✓ORDERBY子句；

✓HAVING子句；

✓UNION运算符；

✓位于选择列表中的子查询；

✓FROM子句中包含多个表；

✓SELECT语句中引用了不可更新视图；

Ø插入数据：

使用INSERT语句通过视图向基本表插入数据

注意：

✓当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。

✓对INSERT语句还有一个限制：

SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。

Ø修改数据：

使用UPDATE语句可以通过视图修改基本表的数据

注意：

若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。

Ø删除数据：

使用DELETE语句可以通过视图删除基本表的数据

注意：

对依赖于多个基本表的视图，不能使用DELETE语句。

（3）触发器（trigger）

它是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行。

触发器经常用于加强数据的完整性约束和业务规则等。

触发器可以从DBA_TRIGGERS，USER_TRIGGERS数据字典中查到。

为什么要使用触发器：

✓可以使用它来检查或预防坏的数据进入数据库。

✓可以改变或取消INSERT、UPDATE、以及DELETE语句。

✓可以在一个会话中监视数据改变的动作。

创建触发器：

CREATE[DEFINER={user|CURRENT_USER}]

TRIGGER<触发器名称>

{BEFORE|AFTER}

{INSERT|UPDATE|DELETE}

ON<表名称>

FOREACHROW

<触发的SQL语句>

说明：

DEFINER：

TheDEFINERclausespecifiestheMySQLaccounttobeusedwhencheckingaccessprivilegesattriggeractivationtime.Ifauservalueisgiven,itshouldbeaMySQLaccountspecifiedas'user_name'@'host_name'。

触发器名称：

触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象

触发程序的动作时间：

BEFOREAFTER.可以设置为事件发生前或后.

事件：

指明了激活触发程序的语句的类型。

可以是下述值之一：

✓INSERT：

将新行插入表时激活触发程序，例如，通过INSERT、LOADDATA和REPLACE语句。

✓UPDATE：

更改某一行时激活触发程序，例如，通过UPDATE语句。

✓DELETE：

从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

表：

触发器是属于某一个表的:

当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活。

我们不能给同一张表的同一个事件安排两个触发器，而且必须引用永久性表，不能将触发程序与TEMPORARY表或视图关联起来。

触发间隔：

FOREACHROW通知触发器每隔一行执行一次动作，而不是对整个表执行一次。

关于旧的和新创建的列的标识

在触发器的SQL语句中，你可以关联表中的任意列。

但你不能仅使用列的名称去标识，那会使系统混淆，因为那里可能会有列的新名（这可能正是你要修改的，你的动作可能正是要修改列名），还有列的旧名存在。

因此你必须用这样的语法来标识：

"NEW.column_name"或者"OLD.column_name".这样在技术上处理（NEW|OLD.column_name）新和旧的列名属于创建了过渡变量（"transitionvariables"）。

对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。

下面是一个UPDATE中同时使用NEW和OLD的例子。

CREATETRIGGERt21_au

BEFOREUPDATEONt22

FOREACHROW

BEGIN

SET@old=OLD.s1;

SET@new=NEW.s1;

END;

触发的SQL语句：

是当触发程序激活时执行的语句。

如果你打算执行多个语句，可使用BEGIN...END复合语句结构。

这样，就能使用存储子程序中允许的相同语句。

删除触发器：

DROPTRIGGER[schema_name.]trigger_name

说明：

方案名称（schema_name）是可选的。

如果省略了schema（方案），将从当前方案中舍弃触发程序。

DROPTRIGGER语句需要SUPER权限。

查询触发器：

SHOWTRIGGERS[{FROM|IN}db_name]

[LIKE'pattern'|WHEREexpr]

mysql>SHOWTRIGGERSLIKE'acc%'\G

***************************1.row***************************

Trigger:

ins_sum

Event:

INSERT

Table:

account

Statement:

SET@sum=@sum+NEW.amount

Timing:

BEFORE

Created:

NULL

sql_mode:

Definer:

myname@localhost

character_set_client:

latin1

collation_connection:

latin1_swedish_ci

DatabaseCollation:

latin1_swedish_ci

∙Trigger：

Thenameofthetrigger.

∙Event：

Theeventthatcausestriggeractivation:

oneof'INSERT','UPDATE',or'DELETE'.

∙Table：

Thetableforwhichthetriggerisdefined.

∙Statement：

Thestatementtobeexecutedwhenthetriggerisactivated.ThisisthesameasthetextshownintheACTION_STATEMENTcolumnofINFORMATION_SCHEMA.TRIGGERS.

∙Timing：

Oneofthetwovalues'BEFORE'or'AFTER'.

∙Created：

Currently,thevalueofthiscolumnisalwaysNULL.

∙sql_mode：

TheSQLmodeineffectwhenthetriggerexecutes.

∙Definer：

Theaccountthatcreatedthetrigger

（4）游标（cursor）

是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。

每个游标区都有一个名字。

用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

概述：

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标总是与一条SQL选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。

当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

如果曾经用C语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。

对于游标而言，其道理是相同的。

可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

游标实现了对mysql的存储过程中循环读取数据表中的对象的过程。

游标的特性：

ØREADONLY只读，只能取值而不能赋值；

ØNOTSCROOLABLE不可回滚，只能顺序读取；

ØASENSITIVE敏感，不能在已经打开游标的表上执行update事务；

游标操作：

声明游标：

DECLAREcursor_nameCURSORFORselect_statement

这个语句声明一个光标。

也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。

打开游标:

OPENcursor_name

游标FETCH：

FETCHcursor_nameINTOvar_name[,var_name]...

这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针。

关闭游标CLOSE：

CLOSEcursor_name

这个语句关闭先前打开的光标。

如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。

使用游标的实例：

CREATEPROCEDUREcurdemo（tblNameVARCHAR（100））

BEGIN

DECLAREdoneINTDEFAULT0;

DECLAREb,cINT;

DECLAREcur1CURSORFORSELECTidFROMv_wondyfox;声明游标

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;

dropviewifexistsv_wondyfox;

set@sql=concat（"createviewv_wondyfoxasselect*from",tblName）;

PREPAREstmt1FROM@sql;

EXECUTEstmt1;

DEALLOCATEPREPAREstmt1;

OPENcur1;打开游标

REPEAT

FETCHcur1INTOb;获取游标内容

selectb;

UNTILdoneENDREPEAT;

CLOSEcur1;关闭游标

END

CREATEPROCEDUREcurdemo（）

BEGIN

DECLAREdoneINTDEFAULT0;

DECLAREaCHAR（16）;

DECLAREb,cINT;

DECLAREcur1CURSORFORSELECTid,dataFROMtest.t1;

DECLAREcur2CURSORFORSELECTiFROMtest.t2;

DECLARECONTINUEHANDLERFORSQLSTATE'02000'SETdone=1;

OPENcur1;

OPENcur2;

REPEAT

FETCHcur1INTOa,b;

FETCHcur2INTOc;

IFNOTdoneTHEN

IFb

INSERTINTOtest.t3VALUES（a,b）;

ELSE

INSERTINTOtest.t3VALUES（a,c）;

ENDIF;

ENDIF;

UNTILdoneENDREPEAT;

CLOSEcur1;

CLOSEcur2;

END

（5）事务（Transaction）

是访问并可能更新数据库中各种数据项的一个程序执行单元（unit）。

事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begintransaction和endtransaction语句（或函数调用）来界定。

事务由事务开始（begintransaction）和事务结束（endtransaction）之间执行的全体操作组成。

一个事务可以是一条SQL语句，一