MySQL编程基础

一、存储例程

1、存储例程是存储在服务器中的一组指定的SQL语句：客户机无需不断重新发出单个语句，而可以改为引用存储例程。

2、存储例程类型：

（1）存储过程：通过call语句调用过程，这些过程可以输出变量或结果集传回值。

（2）存储函数：在语句中调用函数，函数可以返回标量值。

二、

三、存储例程中存在的问题：

1、增加了服务器负载

2、开发工具有限

3、语言功能和速度有限

4、调试和概要分析功能有限

四、示例

1、存储过程

delimiter //

create procedure p1()

begin

select 'lastname',count(*) from employees;

select 'corderdate',count(*) from employees;

select 'city count',count(*) from offices;

end //

2、存储函数

delimiter //

create function pay (gross_pay float(9,2),tax_rate float(3,2))

returns float(9,2)

no sql

begin

declare net_pay float(9,2)

default 0;

set net_pay=gross_pay-gross_pay*tax_rate;

return net_pay;

end //

--查询系统中的过程与函数：

select routine_name,routine_type from information_schema.routines;

3、存储例程和执行安全性

4、触发器

（1）创建触发器

CREATE TRIGGER   emp_ad  AFTER DELETE

ON detail

FOR EACH ROW

INSERT INTO emp

VALUES(OLD.orderNumber,

OLD.productCode,

old.quantityOrdered,

old.priceEach,

old.orderLineNumber);

（2）检查触发器

show create trigger emp_ad;

show triggers;

select trigger_name,trigger_schema from information_schema.triggers;

（3）删除触发器

（4）对触发器的限制

五、事件

1、MySQL 事件是按调度表运行的任务。这些事件可称为“调度事件”。创建事件时，会将其创建为命名数据库对象，其中包含在特定时间执行或定期重复发生的 SQL 语句（或存储过程）。这在概念上与 Linux/UNIX crontab（也称为“cron 作业”）或 Windows 任务调度程序的理念类似。

（1）必需项：

event_name：事件是模式对象，与表、存储过程和触发器一样。event_name 必须是有效的标识符，且可以按正常方式通过模式名称进行引用和/或限定。事件名称在模式中必须是唯一的。

schedule：调度表是一种规则，用于指定 MySQL 执行与事件相关联的操作的时间。

sql_statement：您必须包含按调度表执行的有效 SQL 语句或存储过程语句。此语句受到的限制与适用于存储函数和动态 SQL 的限制相同。例如，该语句无法返回结果集（如 SELECT 或 SHOW 所生成的结果集）。通常会使用 CALL 语句调用过程来执行实际操作。

（2）事件调度程序

创建事件后，该事件将存储在数据库中，以便按调度表执行。event_scheduler 线程会监视所有事件的调度表，在到达调度表中的时间时，该线程会启动一个新的线程来执行每个事件。默认情况下，event_scheduler 线程设置为 OFF。您必须显式启用该线程，方法为修改全局 event_scheduler 服务器变量的值，将其设置为 ON。还可以通过将服务器变量添加到选项文件中（以便更改在启动时生效）或动态使用 SET 语法来实现。如果在event_scheduler 设置为 DISABLED 的情况下启动服务器，则无法在 MySQL 正在运行时通过 SET 语句将其启用。而必须停止 MySQL 并在启用该选项的情况下重新启动MySQL。启用 event_scheduler 线程后，您可以在 SHOW PROCESSLIST 的输出（以及INFORMATION_SCHEMA 等效项 PROCESSLIST）中看到该线程。

（3）更改事件

可以使用 ALTER EVENT 语法更改 EVENT。可以通过该语法更改 EVENT 的每个元素。因此，ALTER EVENT 的语法模型几乎与 CREATE EVENT 语句的语法模型完全相同。

2、

（1）事件调度程序

事件调度程序是 mysqld 进程中的一个单独线程，负责执行调度事件。调度程序会检查是否应执行事件；如果应执行事件，则会创建新的连接来执行操作。

（2）事件调度程序使用案例

使用事件来自动地定期执行（维护）任务，如更新汇总表或刷新查询中的表（物化视图仿真），或者执行夜间作业。例如，处理白天的工作、加载数据仓库或者将数据导出到文件中。

3、

4、

用户可以为数据库classicmodels创建事件，但由于没有其他对于数据库操作的权限，所以该事件无法执行。

5、创建事件

（1）

CREATE TABLE `USER` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `name` varchar(200) NOT NULL,

  `address` varchar(500) NOT NULL,

  `addtime` datetime NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（2）

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` EVENT `event_minute`

ON SCHEDULE EVERY 1 MINUTE

STARTS '2018-08-20 17:50:00'

ON COMPLETION NOT PRESERVE ENABLE DO

BEGIN

INSERT INTO USER(name, address,addtime) VALUES('test1','test1',now());

INSERT INTO USER(name, address,addtime) VALUES('test2','test2',now());

END

;;

DELIMITER ;

（3）

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` EVENT `event_at`

ON SCHEDULE AT '2018-08-18 17:55:30'

ON COMPLETION NOT PRESERVE ENABLE DO

BEGIN

    INSERT INTO USER(name, address,addtime) VALUES('AT','AT',now());

END;;

DELIMITER ;

6、检查事件

7、删除事件