存储过程

存储过程是为以后的使用而保存的一条或多条MySQL语句的集合。可以视为批文件。

为什么使用

1 封装子容易使用的单元中，简化复杂的操作 2 不需要反复建立操作，保证数据的完整性 3 安全性—业务修改时只需修改存储过程的代码 4 提高性能—运行比基本SQL语句快

缺陷： 1 存储过程的编写比基本SQL语句复杂 2 可能没有创建存储过程的安全访问权限。

使用存储过程

执行存储过程—CALL（调用）

CALL 接受存储过程的名字和参数

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

创建存储过程

返回平均价格的存储过程

CREATE PROCEDURE productpricing() BEGIN SELECT AVG(prod_price) AS priceaverage FROM products; END;

【临时更改命令行实用程序的分隔符】

DELIMITER // CREATE PROCEDURE productpricing() BEGIN SELECT AVG(prod_price) AS priceaverage FROM products; END// DELIMITER ;

命令行实用程序使用//作为语句结束符。

CALL productpricing();

删除存储过程

DROP PROCEDURE productpricing;

只给出存储过程名即可。

存在才能删除

DROP PROCEDURE IF EXISTS

使用参数

CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2) ) BEGIN SELECT min(prod_pice) INTO pl FROM products; SELECT max(prod_price) INTO ph FROM products; SELECT avg(prod_price) INTO pa FROM products; END;

MySQL支持3中类型参数： IN（传递给存储过程） OUT（从存储过程中传出） INOUT（对存储过程传入传出）

调用

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

所有MySQL变量都必须以@开始。

上面的存储过程中的变量是OUT类型（输出）

SELECT @priceaverage;

输入输出例子

接受订单，返回订单合计

CREATE PROCEDURE ordertotal( IN onumber INT, OUT ototal DECIMAL(8,2) ) BEGIN SELECT sum(iter_price * quantity) FROM orderitems WHERE order_num = onumber INTO ototal; END;

调用

CALL ordertotal(20005, @total); SELECT @total;

智能存储过程

1 获取合计 2 把营业税有条件地添加到合计 3 返回合计

CREATE PROCEDURE ordertotal( IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8,2) )COMMENT 'Obtain order total, optionally adding tax' BEGIN // 声明局部变量 DECLARE total DECIMAL(8,2); DECLARE taxrate INT DEFAULT 6; SELECT sum(item_price * quantity) FROM orderitems WHERE order_num = onumber INTO total; IF taxable THEN SELECT total + (total/100*taxrate) INTO total; END IF; SELECT total INTO total; END;

调用

CALL ordertotal(20005, 0, @total) SELECT @total;

BOOLEAN 1表示真，0表示假

检查存储过程

SHOW CREATE PROCEDURE ordertotal;

获取详细信息

SHOW PROCEDURE STATUS LIKE 'ordertotal';

游标

游标是存储在MySQL服务器上的数据库查询，不是select语句，而是被该语句，而是被语句检索出来的结果集。

使用游标

创建游标

CREATE PROCEDURE processorders() BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; END;

打开关闭游标

打开

OPEN odernumbers;

关闭

CLOSE ordernumbers;

结束游标使用，必须关闭游标。

使用游标数据

游标被打开，可以使用FETCH语句访问每一行。

例：从游标中检索单个行

CREATE PROCEDURE processorders() BEGIN DECLARE o INT; DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; OPEN ordernumbers; FETCH ordernumbers INTO o; CLOSE ordernumbers; END;

循环检索每一行

CREATE PROCEDURE processorders() BEGIN DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; // 游标 DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; // 将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; // 打开游标 OPEN ordernumbers; // 循环所有列 REPEAT // 获取行数据 FETCH ordernumbers INTO o; // 循环结束 UNTIL done END REPEAT; // 关闭游标 CLOSE ordernumbers; END;

SQLSTATE ‘02000’ 是未找到条件

触发器

触发器是MySQL响应以下任意语句而自动执行的MySQL语句（或者BEGIN和END之间的一组语句）。 DELETE INSERT UPDATE 其它语句不支持触发器

创建触发器

需要给出的信息： 1 唯一的触发器名 2 关联的表 3 响应的活动（DELETE/INSERT/UPDATE） 4 何时执行–之前或之后

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'; AFTER INSERT 触发器在INSERT语句成功之后执行 FOR EACH ROW 对每个插入行执行

只有表才支持触发器，视图和临时表不支持

删除触发器

DROP TRIGGER newproduct;

使用触发器

INSERT触发器

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

【BEFORE】BEFORE用于数据验证（保证插入表的数据是需要的数据）

DELETE

UPDATE

事务

使用InnoDB数据库引擎

事务处理

维护数据库的完整性，操作要么完全执行，要么完全不执行

术语： 1 事务（transaction）——一组SQL语句 2 回滚（rollback） 3 提交（commit） 4 保留点（savepoint）——事务处理中设置的临时占位符，可以对其发布回滚。

控制事务处理

使用ROLLBACK–回滚（撤销）MySQL语句

SELECT * FROM ordertotals; // 显示表非空 START TRANSACTION; // 标识事务开始 DELETE FROM ordertotals; // 删除表中数据 SELECT * FROM ordertotals; // 确定表为空 ROLLBACK; // 回滚 SELECT * FROM ordertotals; // 显示表不为空

【回滚】 事务处理用来管理INSERT、UPDATE、DELETE语句，不能回退SELECT语句、不能回退CREATE、DROP操作（可以使用，但不被撤销）。

COMMIT

隐含提交（implicit commit），提交（写、保存）操作自动进行。

事务中，提交不会隐含进行。需使用COMMIT.

START TRANSACTION; DELETE FROM orderitems WHERE order_num = 20010; COMMIT;

【隐含事务关闭】 当COMMIT或ROLLBACK语句执行后，事务自动关闭。

使用保留点、占位符

SAVEPOINT delete1; // 创建占位符 ROLLBACK TO delete1; // 回退到保留点 RELEASE SAVEPOINT // 释放保留点

更改默认的提交行为

使得MySQL不自动提交更改

SET autocommit = 0; 针对每个连接，而不是服务器。

全球化和本地化

字符集和校对顺序

查看所支持的字符集完整列表

SHOW CHARACTER SET;

查看所支持校对的完整列表

SHOW COLLATION; SHOW VARIABLES LIKE 'character%' SHOW VARIABLES LIKE 'collation%'

创建表时，指定字符集和校对

CREATE TABLE mytable ( column1 INT, column2 VARCHAR(20) column3 VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_general_ci // 可以给列单独设置字符集和校对 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

安全管理

访问控制

安全基础：用户对他们需要的数据具有适当的访问权。

管理用户

USE mysql; SELECT user FROM user;

创建用户账号

CREATE USER ben IDENTIFIED BY 'p@$$w0rd'; // 口令

重命名用户账号

RENAME USER ben TO newname;

删除用户账号

DROP USER newname;

设置访问权限

查看用户账号权限

SHOW GRANTS FOR newname;

授予用户newname在crashcourse.*（所有表）上使用SELECT

GRANT SELECT ON crashcourse.* TO newname;

撤销某特定权限

REVOKE SELECT ON crashcourse.* FROM newname;

各层次上权限

整个服务器，使用GRANT ALL, REVOKE ALL数据库，使用ON database.*特定表，使用ON database.table特定列特定存储过程

另一种创建用户的方法

创建一个数据库用户z1，具有对sakila数据库中所有表的SELECT/INSERT权限 grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';

更改用户口令

SET PASSWORD FOR newname = password('n3w p@$$w0rd');

更改当前用户口令

SET PASSWORD = password('n3w p@$$w0rd');

数据库维护

备份数据

备份前使用FLUSH TABLES，将所有数据写入到磁盘。

mysqldump，可将所有数据库内容存储到某个外部文件 mysqlhotcopy MySQL的BACKUP TABLE、SELECT INTO OUTFILE转存所有数据到外部文件。通过RESTORE TABLE复原。

维护

检查表键是否正确

ANALYZE TABLE orders;

检查问题

CHECK TABLE orders, orderitems;