《【精选】mysql高级部分(非常实用的_不要分)包含索引建立优化_函数_存储过程_触发器_及游标》由会员分享，可在线阅读，更多相关《【精选】mysql高级部分(非常实用的_不要分)包含索引建立优化_函数_存储过程_触发器_及游标（28页珍藏版）》请在金锄头文库上搜索。
1、Mysql 高级部分（1） 索引（index） .1（ 2） 视图（view） .2（3） 触发器（trigger） .6（4） 游标（cursor） .8（5） 事 务 (Transaction).10（6） 存储过程（Stored Procedure ） .12（1） 索引（index）索 引 是 一 个 单 独 的 、 物 理 的 数 据 库 结 构 ， 它 是 某 个 表 中 一 列 或 若 干 列 值 的 集 合和 相 应 的 指 向 表 中 物 理 标 识 这 些 值 的 数 据 页 的 逻 辑 指 针 清 单 。优 点 ：大大加快数据的检索速度;创建唯一性索引，保证数据库表中每一
2、行数据的唯一性;加速表和表之间的连接;在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的 时 间 。缺点：索引需要占物理空间当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降 低 了 数 据 的维 护 速 度 。更好的理解索引的提示： 如果经常使用表中的某一列或某几列为条件进行查询，且表中的数据量比较大时，可以创建索引，以提高查询的速度。 索引是与表关联的可选结构。 通过有目的的创建索引，可以加快对表执行 SELECT 语句的速度。 不管索引是否存在，都无需修改任何 SQL 语句的书写方式。索引只是一种快速访问数据的途径，它只影响查询执行的效率。 可以使用 CRE
3、ATE INDEX 命令在一列或若干列的组合上创建索引。 创建索引时，将获取要创建索引的列，并对其进行排序。然后，将一个指针连同每一行的索引值存储起来，组成键值对（目录名和页码） 。使用索引时，系统首先通过已排序的列值执行快速搜索，然后使用相关联的指针值来定位具有所要查找值的行。 一旦创建了索引，MySQL 会自动维护和使用它们。 只要修改了数据，如添加新行、更新现有行或删除行， MySQL 都会自动更新索引。 但是为表创建过多的索引会降低更新、删除以及插入的性能，因为 MySQL 还必须更新与该表关联的索引。 索引的分类 普通索引：这是最基本的索引，它没有任何限制 唯一索引：它与前面的普通索
4、引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一 主键索引（通过主键约束间接创建）：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引 组合索引：在表中的多个列上创建的索引。组合索引中列的顺序是任意的，可以是相邻的列，也可以是不相邻的列。索引的创建： 普通（唯一）索引的创建：CREATE UNIQUE INDEX index_name ON tbl_name(index_col_name,)index_col_name: col_name(length)ASC|DESC对于字符类型的列，可以编制“前缀索引”，Length 表示按照
5、列的指定长度的字符串索引 创建组合索引：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 pri
6、mary 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）
7、 视图（view）从用户角度来看，一个视图是从一个特定的角度来查看数据 库 中 的 数 据 。 从 数 据库 系 统 内 部 来 看 ， 一 个 视 图 是 由 SELECT 语 句 组 成 的 查 询 定 义 的 虚 拟 表 ， 视 图 是 由 一张 或 多 张 表 中 的 数 据 组 成 的 ， 从 数 据 库 系 统 外 部 来 看 ， 视 图 就 如 同 一 张 表 一 样 ， 对 表能 够 进 行 的 一 般 操 作 都 可 以 应 用 于 视 图 ， 例 如 查 询 ， 插 入 ， 修 改 ， 删 除 操 作 等 。 视图 是 一 个 虚 拟 表 ， 其 内 容 由 查 询 定 义
8、 。概 述 : 视 图 以 经 过 定 制 的 方 式 显 示 来 自 一 个 或 多 个 表 的 数 据 视 图 是 一 种 数 据 库 对 象 ， 用 户 可 以 象 查 询 普 通 表 一 样 查 询 视 图 。 视 图 内 其 实 没 有 存 储 任 何 数 据 ， 它 只 是 对 表 的 一 个 查 询 。 视 图 的 定 义 保 存 在 数 据 字 典 内 。 创 建 视 图 所 基 于 的 表 为 “基 表 ”。 视 图 一 经 定 义 以 后 ， 就 可 以 像 表 一 样 被 查 询 、 修 改 、 删 除 和 更 新作 用 ： 简 化 数 据 查 询 语 句 使 用 户 能
9、 从 多 角 度 看 到 同 一 数 据 提 高 了 数 据 的 安 全 性 提 供 了 一 定 程 度 的 逻 辑 独 立 性 减 少 带 宽 流 量 、 优 化 后 还 可 提 高 执 行 效 率优 点 ： 提 供 了 另 外 一 种 级 别 的 表 安 全 性 隐 藏 的 数 据 的 复 杂 性 简 化 的 用 户 的 SQL 命 令 通 过 重 命 名 列 ， 从 另 一 个 角 度 提 供 数 据视 图 的 创 建 ：CREATE OR REPLACE ALGORITHM = UNDEFINED | MERGE | TEMPTABLEVIEW view_name (column_li
10、st)AS select_statementWITH CASCADED | LOCAL CHECK OPTION说明： OR REPLACE：给定了 OR REPLACE 子句，语句能够替换已有的同名视图。 ALGORITHM：可选的 mysql 算法扩展，算法会影响 MySQL 处理视图的方式。有以下三个值：UNDEFINED-MySQL 将选择所要使用的算法。如果可能，它倾向于MERGE 而不是 TEMPTABLE，这是因为 MERGE 通常更有效，而且如果使用了临时表，视图是不可更新的。MERGE-会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。TE
11、MPTABLE-视图的结果将被置于临时表中，然后使用它执行语句。 veiw_name:视图名。 column_list：要想为视图的列定义明确的名称，列出由逗号隔开的列名。column_list 中的名称数目必须等于 SELECT 语句检索的列数。若使用与源表或视图中相同的列名时可以省略 column_list。 select_statement: 用来创建视图的 SELECT 语句，可在 SELECT语句中查询多个表或视图。但对 SELECT 语句有以下的限制：1. 定义视图的用户必须对所参照的表或视图有查询（即可执行 SELECT语句）权限；2. 在定义中引用的表或视图必须存在； WITH
12、 cascaded|local CHECK OPTION: 在关于可更新视图的WITH CHECK OPTION 子句中，当视图是根据另一个视图定义的时，LOCAL和 CASCADED 关键字决定了检查测试的范围。LOCAL 关键字对 CHECK OPTION 进行了限制，使其仅作用在定义的视图上，CASCADED 会对将进行评估的基表进行检查。如果未给定任一关键字，默认值为 CASCADED。WITH CHECK OPTION 指出在可更新视图上所进行的修改都要符合 select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。 视图定义服从下述限制：
13、 SELECT 语句不能包含 FROM 子句中的子查询。 SELECT 语句不能引用系统或用户变量。 SELECT 语句不能引用预处理语句参数。 在存储子程序内，定义不能引用子程序参数或局部变量。 在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用 CHECK TABLE 语句。 在定义中不能引用 TEMPORARY 表，不能创建 TEMPORARY 视图。 在视图定义中命名的表必须已存在。 不能将触发程序与视图关联在一起。修 改 视 图 ：ALTER ALGORITHM = UNDEFINED | MERGE | TEMP
14、TABLEVIEW view_name (column_list)AS select_statementWITH CASCADED | LOCAL CHECK OPTION说 明 ：该语句用于更改已有视图的定义。其语法与 CREATE VIEW 类似。该语句需要具有针对视图的 CREATE VIEW 和 DROP 权限，也需要针对 SELECT 语句中引用的每一列的某些权限。查 看 视 图 ：SHOW CREATE VIEW view_name说 明 ：该语句给出了 1 个创建给定视图的 CREATE VIEW 语句。删 除 视 图 ：DROP VIEW IF EXISTSview_name , view_name .RESTRICT | CASCADE说 明 ： DROP VIEW 能够删除 1 个或多个视图。必须在每个视图上拥有 DROP 权限。 可以使用关键字 IF EXISTS 来防止因不存在的视图而出错。 如果给定了 RESTRICT 和 CASCADE，将解析并忽略它们。更 新 视 图 ：概述： 视图的使用与表一样，有增删改查四种操作，且语法也与表相同。 在视图上也可以使用修改数据的 DML 语句，如 INSERT、UPDATE 和DELETE 可以统称为“通过视图更新数
