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

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

15、, view_name .RESTRICT | CASCADE说 明 ： DROP VIEW 能够删除 1 个或多个视图。必须在每个视图上拥有 DROP 权限。 可以使用关键字 IF EXISTS 来防止因不存在的视图而出错。 如果给定了 RESTRICT 和 CASCADE，将解析并忽略它们。更 新 视 图 ：概述： 视图的使用与表一样，有增删改查四种操作，且语法也与表相同。 在视图上也可以使用修改数据的 DML 语句，如 INSERT、UPDATE 和DELETE 可以统称为“通过视图更新数据”。 通过视图更新数据有如下限制： 一次只能修改一个底层的基表 如果修改违反了基表的约束条件，则无

16、法更新视图 如果视图中的列不是表中的原始列（如创建视图时使用了连接操作符、聚合函数等），则不能通过视图更新。视图更新操作： 可更新的视图：要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在 INSET、UPDATE 或 DELETE 等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。如果视图包含下述结构中的任何一种，那么它就是不可更新的： 聚合函数； DISTINCT 关键字； GROUP BY 子句； ORDER BY 子句； HAVING 子句； UNION 运算符； 位于选择列表中的子查询

17、； FROM 子句中包含多个表； SELECT 语句中引用了不可更新视图； 插入数据：使用 INSERT 语句通过视图向基本表插入数据注意： 当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。 对 INSERT 语句还有一个限制：SELECT 语句中必须包含 FROM 子句中指定表的所有不能为空的列。 修改数据：使用 UPDATE 语句可以通过视图修改基本表的数据注意：若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。 删除数据：使用 DELETE 语句可以通过视图删除基本表的数据注意：对依赖于多个基本表的视图，不能使用 DELETE 语句。（3

18、） 触发器（trigger）它是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。为什么要使用触发器： 可以使用它来检查或预防坏的数据进入数据库。 可以改变或取消 INSERT、UPDATE、以及 DELETE 语句。 可以在一个会话中监视数据改变的动作。创建触发器：CREATE DEFINER = user | CURRENT_USER TRIG

19、GER BEFORE | AFTER INSERT | UPDATE | DELETE ON FOR EACH ROW说明：DEFINER：The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MySQL account specified as user_namehost_name。触发器名称：触发器必须有名字，最多 64 个字符，

20、可能后面会附有分隔符.它和 MySQL 中其他对象的命名方式基本相象触发程序的动作时间：BEFORE AFTER. 可以设置为事件发生前或后.事件：指明了激活触发程序的语句的类型。可以是下述值之一： INSERT：将新行插入表时激活触发程序，例如，通过 INSERT、LOAD DATA和 REPLACE 语句。 UPDATE：更改某一行时激活触发程序，例如，通过 UPDATE 语句。 DELETE：从表中删除某一行时激活触发程序，例如，通过 DELETE 和REPLACE 语句。表：触发器是属于某一个表的:当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活。我们不能给同一张表的同一个

21、事件安排两个触发器，而且必须引用永久性表，不能将触发程序与 TEMPORARY 表或视图关联起来。触发间隔：FOR EACH ROW 通知触发器每隔一行执行一次动作，而不是对整个表执行一次。关于旧的和新创建的列的标识在触发器的 SQL 语句中，你可以关联表中的任意列。但你不能仅使用列的名称去标识，那会使系统混淆，因为那里可能会有列的新名（这可能正是你要修改的，你的动作可能正是要修改列名），还有列的旧名存在。因此你必须用这样的语法来标识： “NEW . column_name“或者“OLD . column_name“.这样在技术上处理（NEW | OLD . column_name）新和旧的列

22、名属于创建了过渡变量（“transition variables“）。 对于 INSERT 语句,只有 NEW 是合法的；对于 DELETE 语句，只有 OLD 才合法；而UPDATE 语句可以在和 NEW 以及 OLD 同时使用。下面是一个 UPDATE 中同时使用 NEW和 OLD 的例子。CREATE TRIGGER t21_auBEFORE UPDATE ON t22FOR EACH ROWBEGINSET old = OLD . s1;SET new = NEW.s1;END;触发的 SQL 语句： 是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用BEGIN . END

23、复合语句结构。这样，就能使用存储子程序中允许的相同语句。删除触发器：DROP TRIGGER schema_name.trigger_name说明：方案名称（schema_name）是可选的。如果省略了 schema（方案） ，将从当前方案中舍弃触发程序。DROP TRIGGER 语句需要 SUPER 权限。查询触发器：SHOW TRIGGERS FROM | IN db_nameLIKE pattern | WHERE exprmysql SHOW TRIGGERS LIKE acc%G* 1. row *Trigger: ins_sumEvent: INSERTTable: account

24、Statement: SET sum = sum + NEW.amountTiming: BEFORECreated: NULLsql_mode:Definer: mynamelocalhostcharacter_set_client: latin1collation_connection: latin1_swedish_ciDatabase Collation: latin1_swedish_ci Trigger ：The name of the trigger. Event ：The event that causes trigger activation: one of INSERT,

25、UPDATE, or DELETE. Table ：The table for which the trigger is defined. Statement ：The statement to be executed when the trigger is activated. This is the same as the text shown in the ACTION_STATEMENT column of INFORMATION_SCHEMA.TRIGGERS. Timing ：One of the two values BEFORE or AFTER. Created ：Curre

26、ntly, the value of this column is always NULL. sql_mode ：The SQL mode in effect when the trigger executes. Definer ：The account that created the trigger（4） 游标（cursor）是系统为用户开设的一个数据缓冲区，存放 SQL 语句的执行结果。每个游标区都有一个名字。用户可以用 SQL 语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。概述：游 标 提 供 了 一 种 对 从 表 中 检 索 出 的 数 据 进 行 操 作 的 灵

27、活 手 段 ， 就 本 质 而 言 ， 游标 实 际 上 是 一 种 能 从 包 括 多 条 数 据 记 录 的 结 果 集 中 每 次 提 取 一 条 记 录 的 机 制 。 游 标 总 是与 一 条 SQL 选 择 语 句 相 关 联 因 为 游 标 由 结 果 集 （ 可 以 是 零 条 、 一 条 或 由 相 关 的 选 择 语句 检 索 出 的 多 条 记 录 ） 和 结 果 集 中 指 向 特 定 记 录 的 游 标 位 置 组 成 。 当 决 定 对 结 果 集 进 行处 理 时 ， 必 须 声 明 一 个 指 向 该 结 果 集 的 游 标 。 如 果 曾 经 用 C 语 言

28、写 过 对 文 件 进 行 处理 的 程 序 ， 那 么 游 标 就 像 您 打 开 文 件 所 得 到 的 文 件 句 柄 一 样 ， 只 要 文 件 打 开 成 功 ， 该 文件 句 柄 就 可 代 表 该 文 件 。 对 于 游 标 而 言 ， 其 道 理 是 相 同 的 。 可 见 游 标 能 够 实 现 按 与 传 统程 序 读 取 平 面 文 件 类 似 的 方 式 处 理 来 自 基 础 表 的 结 果 集 ， 从 而 把 表 中 数 据 以 平 面 文 件 的形 式 呈 现 给 程 序 。游 标 实 现 了 对 mysql 的 存 储 过 程 中 循 环 读 取 数 据 表 中

29、 的 对 象 的 过 程 。游 标 的 特 性 ： READ ONLY 只读，只能取值而不能赋值； NOT SCROOLABLE 不可回滚，只能顺序读取； ASENSITIVE 敏感，不能在已经打开游标的表上执行 update 事务；游 标 操 作 ：声明游标： DECLARE cursor_name CURSOR FOR select_statement这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。打开游标: OPEN cursor_name游标 FETCH： FETCH cursor_name INTO var_name , var_name

30、 .这个语句用指定的打开光标读取下一行（如果有下一行的话） ，并且前进光标指针。关闭游标 CLOSE： CLOSE cursor_name这个语句关闭先前打开的光标。如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。使 用 游 标 的 实 例 ：CREATE PROCEDURE curdemo(tblName VARCHAR(100)BEGINDECLARE done INT DEFAULT 0; DECLARE b,c INT; DECLARE cur1 CURSOR FOR SELECT id FROM v_wondyfox; 声明游标DECLARE CONTINUE HANDLE

31、R FOR NOT FOUND SET done =1; drop view if exists v_wondyfox ; set sql=concat(“create view v_wondyfox as select * from “, tblName); PREPARE stmt1 FROM sql;EXECUTE stmt1 ; DEALLOCATE PREPARE stmt1; OPEN cur1; 打开游标REPEAT FETCH cur1 INTO b; 获取游标内容select b; UNTIL done END REPEAT; CLOSE cur1; 关闭游标ENDCREAT

32、E PROCEDURE curdemo()BEGINDECLARE done INT DEFAULT 0;DECLARE a CHAR(16);DECLARE b,c INT;DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;DECLARE CONTINUE HANDLER FOR SQLSTATE 02000 SET done = 1;OPEN cur1;OPEN cur2;REPEATFETCH cur1 INTO a, b;FETCH cur2

33、 INTO c;IF NOT done THENIF b c THENINSERT INTO test.t3 VALUES (a,b);ELSEINSERT INTO test.t3 VALUES (a,c);END IF;END IF;UNTIL done END REPEAT;CLOSE cur1;CLOSE cur2;END（5） 事 务 (Transaction)是 访 问 并 可 能 更 新 数 据 库 中 各 种 数 据 项 的 一 个 程 序 执 行 单 元 (unit)。 事 务 通 常由 高 级 数 据 库 操 纵 语 言 或 编 程 语 言 （ 如 SQL， C+或 Jav

34、a） 书 写 的 用 户 程 序 的 执 行 所引 起 ， 并 用 形 如 begin transaction 和 end transaction 语 句 （ 或 函 数 调 用 ） 来 界 定 。 事务 由 事 务 开 始 (begin transaction)和 事 务 结 束 (end transaction)之 间 执 行 的 全 体 操 作 组成 。 一 个 事 务 可 以 是 一 条 SQL 语 句 ， 一 组 SQL 语 句 或 整 个 程 序 。特 性 ：事 务 应 该 具 有 4 个 属 性 ： 原 子 性 、 一 致 性 、 隔 离 性 、 持 续 性 。 这 四 个 属

35、性 通 常称 为 ACID 特 性 ： 原 子 性 （ atomicity） ： 一 个 事 务 是 一 个 不 可 分 割 的 工 作 单 位 ， 事 务 中 包 括 的 诸 操作 要 么 都 做 ， 要 么 都 不 做 。 就 是 说 不 允 许 事 务 部 分 的 执 行 。 即 使 因 为 故 障 而 使 事务 不 能 完 成 ， 在 rollback 时 也 要 消 除 对 数 据 库 的 影 响 ！ 一 致 性 （ consistency） ： 事 务 必 须 是 使 数 据 库 从 一 个 一 致 性 状 态 变 到 另 一 个 一 致性 状 态 。 一 致 性 与 原 子 性 是 密 切 相 关 的 。 就 拿 网 上 购 物 来 说 ， 你 只 有 既 让 商 品 出库 又 让 商 品 进 入 顾 客 的 购 物 车 才 能 构 成 事 务 。 隔 离 性 （ isolation） ： 一 个 事 务 的 执 行 不 能 被 其 他 事 务 干 扰 。 即 一 个 事 务 内 部 的 操作 及 使 用 的 数 据 对 并 发 的 其 他 事 务 是 隔 离 的 ， 并 发 执 行 的 各 个 事 务 之 间 不 能 互 相 干