MySQL二级等级考试归纳——高级指令(上）

过了这么多天终于又有空啃了，还有几天就考了，接上文继续归纳来了。原来这本书内容还真不少，本来还想只分两篇，后来越分越多……

本篇对应书本6-9章，包括的指令有：索引篇，视图篇，触发器篇，事件篇

下一篇将包括：存储过程，存储函数，访问控制与安全管理，备份恢复等

http://blog.csdn.net/ycisacat/article/details/50933836 概念篇

http://blog.csdn.net/ycisacat/article/details/50935840基础指令篇

接上一篇的实例，已经选好了数据库，建好表tb_person,字段名有id(主键)，name, birthday, score等 

（未选定数据库时，所有对表操作要加上数据库名称前缀：db_name.tb_name)

一，索引

1.查看索引：

SHOW {INDEX|INDEXES|KEYS} FROM tb_person \G

2.创建索引：

1) CREATE TABLE 的同时创建： 

CREATE TABLE ...[CONSTRAINT index_name] [UNIQUE] [INDEX|KEY] [index_name] (index_col_name[length] [ASC|DESC]）

例如：CREATE TABLE ...INDEX(name));  

基础篇中UNIQUE, PRIMARY KEY等的写法即创建了索引

2) CREATE INDEX index_person ON tb_person(name(6), score ASC）;   //(6)指按name字段值前3个字符和score字段值建立组合索引，ASC指升序索引，DESC指降序索引。字段名，长度，升降序都包含在括号内。有多个字段组合索引时写在括号内，用逗号隔开

3）ALTER TABLE tb_person ADD INDEX index_person(name(6),score ASC);  //效果同上

删除索引：

DROP INDEX index_person ON tb_person;

或

ALTER TABLE DROP INDEX index_person;

二，视图

创建视图：

CREATE [ON REPLACE] VIEW view_person(name, score_avg) AS SELECT name, AVG(score) FROM tb_person WITH [CASCADED| LOCAL] CHECK OPTION;

   //SELECT语句不能有from 子句。with check option子句用于指定可更新视图上所进行的修改都要符合select语句中所指定的限制条件，视图是根据另一个视图定义的时，cascaded对所有视图进行检查，local只对定义的视图进行检查

查看视图定义：

SHOW CREATE VIEW view_person \G

删除视图：

DROP VIEW [IF EXISTS] view_person;

修改视图定义：

ALTER VIEW view_person(name, birthday) AS SELECT name,birthday FROM tb_person WHERE score>90 WITH CHECK OPTION;

CREATE ON REPLACE 代替上面的语句中的ALTER也可

更新,查看视图数据：

使用INSERT,UPDATE,DELETE，SELECT与对TABLE的指令相似，把tb_person改为view_person即可，前提是视图可更新（视图不含：聚合函数，DINSTINCT,GROUP BY,ORDER BY,HAVING,UNION,位于选择列表的子查询，FROM子句包含多个表，SELECT引用不可更新视图，WHERE子句中的子查询引用FROM子句中的表）

三，触发器

创建触发器：

CREATE TRIGGER trigger_person {AFTER|BEFORE} {INSERT|UPDATE|DELETE} ON tb_person FOR EACH ROW SET [BEGIN] @str='trigger body' [END];

trigger time(触发时间分为after和before，表示触发事件的前或后触发；触发事件分为insert，update和delete，一个表最多支持6个触发器（这三个事件的前后）。begin和end之间的为触发器动作主体.触发器涉及对表自身的更新操作时，只能使用BEFORE UPDATE,不能用AFTER UPDATE.

查看数据库中已有触发器：

SHOW TRIGGERS [{FROM |IN} db_name];

删除触发器：

DROP TRIGGERS [IF EXISTS] trigger_person;

使用触发器：

创建触发器后，执行insert，update，delete的前后触发事件主体会自动执行。可以用SELECT查看结果，如：SELECT @str；（上例）

四，事件

查看是否已开启事件调度器：

SHOW VARIABLES LIKE 'EVENT_SCHEDULER';

或

SELECT @ @EVENT_SCHEDULER;

开启事件调度器：

SET GLOBAL EVENT_SCHEDULER=1;

SET GLOBAL EVENT_SCHEDULER=TRUE; (0，FALSE表示关）

创建事件：

DELIMITER $$                                             //把结束符；改为$$

CREATE EVENT IF NOT EXISTS event_insert              

ON SCHEDULE                                          //schedule子句为时间调度，指定时间何时发生或多久发生一次，除了every子句还可以用at子句，表示从某时间点开始

EVERY 1 MONTH STARTS CURDATE() +INTERVAL 1 MONTH ENDS '2016-12-31'    //every子句，此处表示从下个月开始到2016年12月31日结束，间隔为1个月

[ENABLE | DISABLE | DISABLE ON SLAVE]      //可选项，enable表事件是活动的，disable表事件关闭，disable on slave 表在从机中关闭，默认enable 

DO                                                                 //DO之后为事件主体

BEGIN 

IF YEAR(CURDATE())<2016 THEN

INSERT INTO tb_person (name,socre) VALUES('Jenny',90);

END IF;

END $$

查看事件详细信息：

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_person' [AND EVENT_SCHEMA='db_name'];

查看所有事件：

SHOW EVENTS;

查看事件创建信息：

SHOW CREATE EVENT event_person;

修改事件：

ALTER EVENT event_person       //接以下一个或几个[ ]

[ON SCHEDULE...]                         //修改事件的时间调度

[RENAME TO new_name]            //重命名

[ENABLE | DISABLE | DISABLE ON SLAVE]             //修改事件活动性

[DO ...]                           //修改事件主体

删除事件：

DROP EVENT IF EXISTS event_person;