【SQL篇章】【SQL语句梳理 :--基于MySQL5.6】【已梳理：DATABASE/EVENTS】【会坚持完善】
目录:
1. Data Definition Statements:
1.1 create database, alter database, show databases
1.2 create event, alter event, show events
1. Data Definition Statements:
1.1
CREATE DATABASE
格式：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] create_specification :
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
事例 :
CREATE DATABASE db2; CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */
查看：
SHOW DATABASES;
ALTER DATABASE
格式：
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
事例操作：
<1> -->db1:utf8
ALTER DATABASE db1 CHARACTER SET = utf8;
查看：
mysql> SELECT * FROM information_schema.`SCHEMATA` WHERE schema_name='db1';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def | db1 | utf8 | utf8_general_ci | NULL |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.04 sec)
<2> -->db1:utf8-->latin1
ALTER DATABASE db1 CHARACTER SET = latin1;
查看：
mysql> SELECT * FROM information_schema.`SCHEMATA` WHERE schema_name='db1';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def | db1 | latin1 | latin1_swedish_ci | NULL |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)
<3> --创建表t5: <db1:latin1>
CREATE TABLE t5(id int);
查看所建立表的字符集
mysql> SHOW CREATE TABLE db1.t5;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| t5 | CREATE TABLE `t5` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
测试发现：
1.db的character改变后，collation随之改变。 2.db中旧表character不会改变。新建表character默认为与当前db相同。
SHOW DATABASES;
格式：
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]
DROP DATABASE;
格式：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
1.IF EXISTS : DB不存在，不会报错
2.DB drop掉，对应的物理目录也会删除。但是DB对应目录下有其它文件，无法执行drop DB的操作，报错
mysql> drop database wb; ERROR 1010 (HY000): Error dropping database (can't rmdir './wb/', errno: 17)
删除非数据库目录或文件：
mysql> drop database wb; Query OK, 0 rows affected (0.00 sec)
1.2
CREATE EVENT
格式：
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
注意事项：
- 创建event需要：event，super权限，建好之后，必须是enable状态。
- ON SCHEDULE ： 决定什么时间或者多长时间，时间执行一次
- DO :包含被event执行的SQL语句
默认 EVENT 支持是没有启动的，可以通过下面的命令来查看状态：
Select @@event_scheduler;
如果返回 OFF ，则需要执行下面的命令启动：
SET GLOBAL event_scheduler = ON;
好了，上面虽然启动了 EVENT ，但是每次重启 mysql 之后 EVENT 并没有自动启动，那么如何让它自动启动呢？
方法一：找到当前使用的 .cnf 文件
[mysqld] event_scheduler=1
方法二：启动 mysql 的时候增加 --event_scheduler=1
mysql start --event_scheduler=1
事例：
1.只执行一次
CREATE
DEFINER = CURRENT_USER
EVENT IF NOT EXISTS myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
DO
INSERT INTO db1.t1 VALUES(1);
2.每隔一秒执行一次
CREATE
DEFINER = CURRENT_USER
EVENT IF NOT EXISTS myevent
ON SCHEDULE EVERY 1 SECOND
DO
INSERT INTO db1.t1 VALUES(1);
3.event的执行时间是过去时，在创建时，就drop掉了，当前创建的event是没有显示的。
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2016-11-16 10:01:39 |
+---------------------+
1 row in set (0.00 sec)
mysql> CREATE EVENT event_1
-> ON SCHEDULE AT '2006-02-10 23:59:00'
-> DO INSERT INTO test.totals VALUES (NOW());
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGSG
*************************** 1. row ***************************
Level: Note
Code: 1588
Message: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
1 row in set (0.00 sec)
ON SCHEDULE 解析：
1.
'two minutes and three seconds from now'
AT CURRENT_TIMESTAMP + INTERVAL '2:3' MINUTE_SECOND
'three weeks and two days from now'
AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY
2.
间隔值频率相同，可以用EVERY，不与'+INTERVAL'同时存在
ON SCHEDULE EVERY 6 WEEK
STARTS: 后面紧接timestamp,指示从什么时间开始执行repeating，可以用：+ INTERVAL interval 指示：从现在开始经多长时间后开始执行repeating.
eg1:'every three months, beginning one week from now':从现在开始1周之后，开始执行，每隔3月的重复操作。
EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means
eg2:'every two weeks, beginning six hours and fifteen minutes from now'
EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE.
ENDS : 同starts相反，指示从什么时间停止执行repeating。用法同starts
eg: 'every twelve hours, beginning thirty minutes from now, and ending four weeks from now'
EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK
备注：STARTS,ENDS,可以同时使用，也可用其一，也可以使用复杂的时间单元
[ON COMPLETION [NOT] PRESERVE] 解析：
- 正常来说，一旦event过期，会立即drop掉。
- 可以通过设置：ON COMPLETION PRESERVE,来禁止drop操作。此时，event的状态从：ENABLE---->DISABLE。EVENT停止执行，保留存在。
- 可以通过设置：ON COMLETION NOT PRESERVE,不禁止drop操作，此时，event执行完毕，会立即drop掉。此时看不到event了。
- 在不指定时，默认：COMPLETION NOT PRESERVE ENABLE .也就是说，event过期后会自动drop。
测试事例：
eg:
CREATE DEFINER=CURRENT_USER EVENT IF NOT EXISTS myevent ON SCHEDULE EVERY 1 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 3 MINUTE DO INSERT INTO db1.`t1`(id) VALUES(5);
创建完成，再次查看event：
DELIMITER $$ ALTER DEFINER=`admin`@`%` EVENT `myevent`
ON SCHEDULE EVERY 1 SECOND STARTS '2016-11-30 16:42:02' ENDS '2016-11-30 16:44:02'
ON COMPLETION NOT PRESERVE ENABLE
DO INSERT INTO db1.`t1`(id) VALUES(5)$$ DELIMITER ;
[ENABLE | DISABLE | DISABLE ON SLAVE]解析；
通过设置：ENABLE：启动event；DISABLE：停止event。在ALTER EVENT时，很常用。
DISABLE ON SLAVE : 设置主从复制时，标示从库的event的状态。event会在master上创建，并复制到从库，但是不会在从库上执行。
测试事例：
CREATE DEFINER=CURRENT_USER EVENT IF NOT EXISTS myevent ON SCHEDULE EVERY 1 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 3 MINUTE ON COMPLETION PRESERVE ENABLE DO INSERT INTO db1.t1(id) VALUES(1);
观察master 和 slave上event的结构：
MASTER:
DELIMITER $$ ALTER DEFINER=`admin`@`%` EVENT `myevent`
ON SCHEDULE EVERY 1 SECOND STARTS '2016-11-16 01:35:40' ENDS '2016-11-16 01:37:40'
ON COMPLETION PRESERVE ENABLE DO INSERT INTO db1.t1(id) VALUES(1)$$ DELIMITER ;
SLAVE:
DELIMITER $$ ALTER DEFINER=`admin`@`%` EVENT `myevent`
ON SCHEDULE EVERY 1 SECOND STARTS '2016-11-16 01:35:40' ENDS '2016-11-16 01:37:40'
ON COMPLETION PRESERVE DISABLE ON SLAVE DO INSERT INTO db1.t1(id) VALUES(1)$$ DELIMITER ;
通过系统表EVENTS查看状态
MASTER:
mysql> SELECT EVENT_SCHEMA,EVENT_NAME,STATUS FROM `information_schema`.`EVENTS` WHERE EVENT_SCHEMA='db1';
+--------------+------------+----------+
| EVENT_SCHEMA | EVENT_NAME | STATUS |
+--------------+------------+----------+
| db1 | myevent | DISABLED |
+--------------+------------+----------+
1 row in set (0.00 sec)
SLAVE:
mysql> SELECT EVENT_NAME,STATUS FROM `information_schema`.`EVENTS`;
+------------+--------------------+
| EVENT_NAME | STATUS |
+------------+--------------------+
| myevent | SLAVESIDE_DISABLED |
+------------+--------------------+
[COMMENT 'comment']解析：
描述EVENT,最多64字符，用引号引起来。
DO 解析：
指定EVENT所执行的动作，可以是任何SQL。
SQL_MODE 解析:
mysql> SELECT SQL_MODE FROM information_schema.`EVENTS`; +--------------------------------------------+ | SQL_MODE | +--------------------------------------------+ | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+
对EVENT执行create ，alter操作时，MYSQL存储的SQL_MODE系统变量总是被强制设置。
regardless of the current server SQL mode when the event begins executing.
DO 中使用复合语句
select,show 在event中是没有效果的。但是可以使用：select. . . insert, insert into . . . select
复合语句在event中，可以使用EBGIN,END关键字：
1.
DELIMITER $$ CREATE EVENT e_count1 ON SCHEDULE EVERY 4 SECOND COMMENT 'Saves total number of tb1' DO BEGIN INSERT INTO t2(TIME,total) SELECT CURRENT_TIMESTAMP,COUNT(*) FROM t1; DELETE FROM t1; END $$ DELIMITER ;
2.下面的event中应用了：本地变量、错误处理、流控制结构
DELIMITER $$ CREATE EVENT e ON SCHEDULE EVERY 5 SECOND DO BEGIN DECLARE v INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET v = 0; WHILE v < 5 DO INSERT INTO t3 VALUES(0); UPDATE t3 SET s1 = s1 + 1; SET v = v + 1; END WHILE; END $$ DELIMITER ;
EVENT中调用存储过程
CREATE EVENT e_call_myproc ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO CALL myproc(5, 27);
ALTER EVENT;
格式：
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO event_body]
事例操作：
主干语句：ALTER EVENT event_name；
1.前提EVENT必须存在
mysql> alter event event1 on schedule every '2:3' DAY_HOUR ; ERROR 1539 (HY000): Unknown event 'event1'
2.
ALTER EVENT myevent ON SCHEDULE EVERY 2 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ENABLE DO INSERT INTO t2(TIME,total) VALUES(CURRENT_TIMESTAMP,5);
3.停止event
ALTER EVENT myevent DISABLE;
4.EVENT改名
ALTER EVENT olddb.myevent RENAME TO newdb.myevent;
SHOW EVENTS:
格式：
SHOW EVENTS [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr]
事例：
<1>
show events; 列出当前DB中所有events。
mysql> select current_user(),schema(); +-----------------+----------+ | current_user() | schema() | +-----------------+----------+ | admin@localhost | db1 | +-----------------+----------+ 1 row in set (0.00 sec)
mysql> show eventsG
*************************** 1. row ***************************
Db: db1
Name: myevent
Definer: admin@%
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 2
Interval field: SECOND
Starts: 2016-11-16 08:37:26
Ends: 2016-11-16 01:44:11
Status: DISABLED
Originator: 5
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.07 sec)
<2>
列出指定DB下的events
SHOW EVENTS FROM wb; SHOW EVENTS FROM wb like '%wb';
<3> show events输出关键词解析：
Type: EVENT的重复执行类型 ONE TIME (transient) or RECURRING (repeating).
Execute： 执行一次的event显示：AT
重复执行的event显示NULL
Interval value: 2 一次执行完毕到下次执行时的间隔。
Interval field: SECOND 执行间隔时间单位
Status: event的状态
Originator MySQL server 的 ID
<4>
SHOW CREATE EVENT event_name;
mysql> show create event myeventG *************************** 1. row *************************** Event: myevent sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION time_zone: SYSTEM Create Event: CREATE DEFINER=`admin`@`%` EVENT `myevent`
ON SCHEDULE EVERY 2 SECOND STARTS '2016-11-16 08:37:26' ENDS '2016-11-16 01:44:11'
ON COMPLETION PRESERVE DISABLE
DO insert into t2(Time,total) values(current_timestamp,5) character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
DROP EVENTS
格式：
DROP EVENT [IF EXISTS] event_name
1.删除不存在的EVENT,报错
mysql> DROP EVENT E; ERROR 1539 (HY000): Unknown event 'E'
2.EVENT不存在，消除报错
mysql> DROP EVENT IF EXISTS E; Query OK, 0 rows affected, 1 warning (0.00 sec)
3.EVENT存在，正常删除
mysql> DROP EVENT e_1; Query OK, 0 rows affected (0.00 sec)