【SQL篇章--DATABASE/EVENTS】
2018-06-17 23:15:08来源：未知 阅读 ()
【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 WARNINGS\G
*************************** 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 events\G
*************************** 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 myevent\G *************************** 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)
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
相关文章
- MySQL replace函数怎么替换字符串语句 2020-03-09
- PHP访问MySQL查询超时怎么办 2020-03-09
- mysql登录时闪退 2020-02-27
- MySQL出现1067错误号 2020-02-27
- mysql7.x如何单独安装mysql 2020-02-27
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
热门词条
最新资讯
热门关注
热门标签