MySQL入门学习笔记之五

存储过程

存储过程是为以后的使用而保存的一条或多条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;