《Mysql必知必会》高级部分

《Mysql必知必会》高级部分

第22章 视图的使用

视图是虚拟的表，它们没有实际的数据，真正的数据在基表中。视图是对SELECT语句的封装，有几个优点：重用SQL语句、简化复杂的SQL操作、使用表的组成部分而不是整个表、保护数据、更新数据格式和表示。

一般，应该将视图用于检索（SELECT语句），而不是用于更新（INSERT、UPDATE和DELETE）。

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems;

使用上述已经创建的视图

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;

第23章 存储过程的使用
存储过程简单来说，就是为以后的使用而保存的一条或多条Mysql语句的集合。使用存储过程可以封装一系列过程简化调用、保证数据完整性、简化对变动的管理、提高性能等。

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

调用

CALL productpricing();

删除存储过程

DROP PROCEDURE productpricing;

使用参数创建智能存储过程，参数有OUT（从存储过程中传出）、IN（传进）、INOUT三种。变量前加@符号。

返回带税或不带税的合计

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)COMMENT '*****'
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 ototal;
END;

调用

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

检查存储过程

显示CREATE语句

SHOW CREATE PROCEDURE ordertotal;

获得何时、由谁创建等详细信息的存储过程列表

SHOW PROCEDURE STATUS;

第24章 游标的使用

游标是一个存储在Mysql服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
步骤：声明游标、打开游标、取出各行处理、关闭游标。如果不关闭，MYSQL会在到达存储过程的END语句时自动关闭它。

游标只能在存储过程中使用，用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义。

CREATE PROCEDURE processorders()
BEGIN
    --声明局部变量
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    
    --声明游标
    DECLARE ordernumbers CURSOR
    FOR 
    SELECT order_num FROM orders;

    --声明continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    --创建表存储结果
    CREATE TABLE IF NOT EXISTS ordertotals
     (order_num INT,total DECIMAL(8,2));

    --打开游标
    OPEN ordernumbers;

    --循环所有行
    REPEAT
        --获取游标中数据
        FETCH ordernumbers INTO o;
        --获取该订单的合计
        CALL ordertotal(o,1,t);
        --插入数据
        INSERT INTO ordertotals(order_num,total) VALUES(o,t);
    --结束循环
    UNTIL done END REPEAT;
    
    --关闭游标
    CLOSE ordernumbers;
END;

第25章 触发器的使用

Mysql语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句自动执行，怎么办呢？那就是使用触发器。触发器可以响应INSERT、DELETE、UPDATE三种语句，既可以在BEFORE执行也可以在AFTER执行。

INSERT触发器

可以引用一个名为NEW的虚表，访问被插入的行

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

DELETE触发器

可以引用一个名为OLD的虚表，访问被删除的行，OLD中的值全都是只读的，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN 
    INSERT INTO archive_orders(order_num,order_date,cust_id)
    VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

UPDATE触发器

可以引用一个名为OLD的虚表访问更新前的值，引用一个名为NEW的虚表访问新值。NEW值可以被更新，OLD只读。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW
SET NEW.vendor_state = Upper(NEW.vendor_state);

第26章 事物处理
事物处理是一组SQL操作，是一种用来管理成批MYSQL操作的机制，以保证数据库不包含不完整的操作结果。这组SQL语句要么执行完整，要么不执行。
事物  指一组SQL语句

回退  指撤销指定SQL语句的过程

提交  指将未存储的SQL语句结果写入数据库表

保留点  指事物处理过程中设置的临时占位符，方便回退

事物的开始

START TRANSACTION

回退

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

提交，一般的SQL语句是隐含提交的，但是在事物处理中，不会隐含提交

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

保留点

SAVEPOINT deletel;

ROLLBACK TO deletel;

第28章 访问控制 
赋予用户特殊的权限进行访问控制和保护MYSQL服务器。

用户存储在mysql数据库中的user表中。

USE mysql;

SELECT * FROM user;
--创建一个用户
CREATE USER ben IDENTIFIED BY '111111';
--重命名用户
RENAME USER ben TO bendan;
--删除用户
DROP USER bendan;

访问权限

--显示用户权限
SHOW GRANTS FOR ben;

--授予权限
GRANT SELECT ON database.* TO ben;

--撤销权限
REVOKE SELECT ON database.* FROM ben;

整个服务器使用GRANT ALL和REVOKE ALL;

整个数据库使用ON database.*;

特定的表使用ON database.table;

特定的列或存储过程

--更改口令
SET PASSWORD FOR ben = Password('***');