mysql视图，存储过程，游标，触发器，事务简单了解

一：视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时检索数据的查询。

为什么使用视图：

重用sql语句
简化复杂的sql操作，在编写查询后可以方便的重用而不必知晓细节
使用表的组成部分而不是整个表
保护数据，可以给用户授予表的特定权限而不是整个表的访问权限
更改数据格式和表示。视图可以返回与底层表的表示和格式不用的数据

视图的规则和限制：

与表一样，视图必须命名唯一
对于创建的视图数目无限制
为了创建视图必须有足够的访问权限。这些限制是有数据库管理人员授予的。
视图可以嵌套，既可以利用从其他视图总检索数据的查询来构造一个视图
order by 可以出现在视图中，但如果总该视图检索数据的select语句中也含有order by，那么该视图中的order by 将被覆盖
视图不能索引，也不能有关联的触发器或默认值
视图可以和表一起使用。例如编写一个连接表和视图的select语句

视图创建：

CREATE VIEW productcustomers AS SELECT
    c.cust_name,
    c.cust_contact,
    oi.prod_id
FROM
    customers AS c,
    orders AS o,
    orderitems AS oi
WHERE
    c.cust_id = o.cust_id
AND oi.order_num = o.order_num;

视图结果：
Coyote Inc.    Y Lee    ANV01
Coyote Inc.    Y Lee    ANV02
Coyote Inc.    Y Lee    TNT2
Coyote Inc.    Y Lee    FB
Coyote Inc.    Y Lee    FB
Coyote Inc.    Y Lee    OL1
Coyote Inc.    Y Lee    SLING
Coyote Inc.    Y Lee    ANV03
Wascals    Jim Jones    JP2000
Yosemite Place    Y Sam    TNT2
E Fudd    E Fudd    FC

用视图重新格式化检索出的数据
用视图过滤不想要的数据
使用视图与计算字段
更新视图

更新视图必须能正确地确定被更新的基数据。

以下情况不能更新视图：

分组（order by ， having）
联结
子查询
并
聚集函数（min(),count(),sum()等）
distinct
导出（计算）列

二：存储过程

存储过程就是为了以后的使用而保存的一条或者多条mysql语句的集合。

为什么使用存储过程：

通过把处理封装在容易使用的单元中，简化复杂操作
不要求反复建立一系列处理步骤，保证数据的完整性。防止错误也保证了数据的一致性
简化对变动的管理。特性延伸就是安全性。
提高性能。因为使用存储过程比单独使用sql语句要快
存在一些只能在单个请求中的mysql元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

换句话说使用存储过程有3个主要好处。简单，安全，高性能。

缺陷：

存储过程编写更复杂，需要更高的技能，更丰富的经验。
创建存储过程的安全访问权限

执行存储过程

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

创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
 SELECT AVG(prod_price) as priceaverage
 FROM products;
END; 
#mysql命令行客户端的分隔符
另一种表示方法：

DELIMITER //

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

DELIMITER;

调用以上存储过程是：

CALL productpricing ();

priceaverage
16.133571

删除存储过程：

DROP PROCEDURE productpricing;

使用参数：

变量(variable)内存中一个特定对的位置，用来临时存储数据。

productpricing的修改版本：

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

调用该存储过程：

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

查找变量：

SELECT @priceaverage;  
#SELECT @priceaverage ,@pricelow ,@pricehigh;  查询全部调用存储过程中全部数据

再写一个有in 和out参数的存储过程。

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

调用：

CALL ordertotal(20005 ,@total);

SELECT @total;#查找结果

总结：存储过程和编程中方法类似。输入输出。参数变量等。

游标：(只能用于存储过程ps：mysql5.1特性是这样。但是现在已经5.7发布了不知道现在api是怎么设置的)

游标是一个存储在mysql服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。在出处了解游标之后，应用程序可以根据需要滚动或浏览其中的数据。

使用游标：

在能够使用游标前，必须声明它。这个过程实际上没有检索数据。它只是定义要使用的select语句。
一旦声明后，必须打开游标以供使用。这个过程用前面定义的select语句把数据实际检索出来。
对于填有数据的游标，根据需要取出（检索）各行。
在结束游标使用时，必须关闭游标。

创建游标：

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

打开游标

OPEN ordernumbers;

游标处理完成，使用关闭游标

close ordernumbers;

再改造之后如下：

DROP PROCEDURE processorders;#删除之前建立的存储过程

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

再写一个游标简单例子：

CREATE PROCEDURE processorderss()
BEGIN
 DECLARE done Boolean DEFAULT 0;
 DECLARE o INT;
 DECLARE t DECIMAL(8,2);

 DECLARE ordernumbers CURSOR
 FOR
 SELECT order_num FROM orders;

 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) value(o,t);
     UNTIL done END REPEAT;

   CLOSE ordernumbers;

END;

此例子包含存储过程，游标，逐行处理以及存储过程调用其他存储过程的一个完整的工作样例。

触发器(只有表支持触发器)：

触发器是mysql响应一下任意语句而自动执行的一条mysql语句：

delete
insert
update

创建触发器：

唯一的触发器名
触发器关联的表
触发器应该响应的活动（delete,insert,update）
触发器何时执行

创建触发器：

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT
    'product added';

删除触发器：

DROP TRIGGER newproduct ;

事务处理(transaction processing)：

事务用来维护数据库的完整性。

事务(transaction)指一组sql语句
回退(rollback) 指撤销指定sql语句的过程
提交(commit)指将未存储的sql语句结果写入数据库
保留点(savepoint)指事务处理中设置的临时占位符(placeholder),可以对它发布回退。

mysql标示事务开始：

start transaction;

rollback;#事务回滚，只能在一个事务处理中使用

commit;#事务处理中，明确提交。

当执行rollback，commit后事务会隐性关闭。

使用保留点：

savepoint delete1;

rollback to delete1;#回滚到delete1保存点。

默认的提交行为：

set autocommit=0;

autocommit标志是否自动提交更改。

数据库脚本文件地址：http://pan.baidu.com/s/1i56i0ct