mysql高级特性

视图

视图是虚拟的表，本身不存储数据，而是按照指定的方式进行查询，只包含使用时动态索引数据的查询。

作用

1. 重用sql语句
2. 简化复杂的sql操作
3. 使用表的组成部分而不是整个表
4. 保护数据
5. 更改数据格式和表示

原则

1. 与表名一样视图必须唯一命名
2. 数目没有限制视图可以嵌套
3. ORDER BY可能会被selcect中的覆盖
4. 不能索引不能关联触发器和默认值
5. 可以和表一起使用

使用流程

1. CREATE VIEW 创建
2. DROP VIEW viewname 删除
3. SHOW CREATE VIEWW viewname 查看创建语句
4. CREATE OR REPLACE VIEW 删除并且创建

//创建视图
CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems
WHERE customers.cust_id = irders.cust_id AND orderitems.order_num = orders.order_num;

//从视图索引数据
SELECT cust_name FROM productcustomers WHERE prod_id = 'TNT2';

//使用视图格式化数据
CREATE VIEW fmtlocation AS SELECT Concat(RTrim(vend_name), '(', RTim(vend_country) ,')') AS vend_tittle FROM vendors;
SELECT * FROM fmtlocation;

//使用视图过滤数据
CREATE VIEW customemil AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;

//使用视图作为计算字段
CREATE VIEW expanded AS SELECT order_num,id,name, count, price, count*price AS expand FROM orders;
SELECT * FROM expanded WHERE order_num = 2005;

视图更新

视图中有分组GROUP BY HAVING、链接、子查询、并、集聚函数、DISTINCT、导出列则不能更新视图，视图主要用于数据检索。

存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。 通过把处理封装在容易使用的单元中，简化复杂的操作。提高性能。因为使用存储过程比使用单独的SQL语句要快。 OUT指定参数有存储存储过程的查询输出，IN指定存储过程的输入，BEGIN END包含多条查询语句，CALL执行存储过程。 INTO 将结果放入OUT参数中返回。

//创建存储过程
CREATE PROCEDURE productpro(
    IN  number INT,
    OUT p1 DECIMAL(8, 2),
    OUT ph DECIMAL(8, 2),
    OUT pa DECIMAL(8, 2),
    OUT total DECIMAL(8, 2),
)
BEGIN
    SELECT Min(prod_price) INTO p1 FROM products;
    SELECT Max(prod_price) INTO ph FROM products;
    SELECT Avg(prod_price) INTO p1 FROM products;
    SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = number INTO total;
END;

//调用存储过程
CALL productpro(20, @pricemin, @pricemax, @priceavg, @total)

//检索结果
SELECT @pricemin, @pricemax, @priceavg, @total;

//显示创建详情
SHOE CREATE PROCEDURE productpro;

//删除存储过程
DROP PROCEDURE productpro;

游标

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
MySQL游标只能用于存储过程（和函数）

//DECLARE CURSOR 创建游标, 存储过程处理完后有效小时
CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    DECLARE o INT;

    //在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动
    OPEN ordernumbers;

    //在一个游标被打开后，可以使用FETCH语句分别访问它的每一行
    //它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行
    FETCH ordernumbers INTO o;

    //游标处理完成后，应当使用如下语句关闭游标
    CLOSE ordernumbers;
END;

触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：DELETE 、INSERT、UPDATE。
只有表才支持触发器，视图不支持

//创建触发器
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'product added';// 触发器将在INSERT语句执行成功后 对每行插入都显示一条文本消息

//在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;//触发器返回插入行的order_num

//在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
//删除之前将删除的值放入另外一个表
CREATE TRIGGER delleteorder 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的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值,old是readonly
CREATE TRIGGER uodateorder BEFORE UPDATE ON orders FOR EACH ROW SET NEW.order_state = Upper(NEW.order_state);//触发器返回插入行的order_num

//删除触发器
DROP TRIGGER newproduct;

事务

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。
默认的MySQL行为是自动提交所有更改。换句话说，任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效

SET autocommit = 0; //不自动提交
START TRANSACTION;
DELECT FROM orders;
SELECT * FROM orders;
ROLLBACK; //当COMMIT或ROLLBACK语句执行后，事务会自动关闭
START TRANSACTION;
DELECT FROM orders;
SELECT * FROM orders;
COMMIT;
SAVEPOINT delete1; //保留点保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放
DELECT FROM orders;
ROLLBACK TO delete1;