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

一：视图

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

     为什么使用视图：

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

     视图的规则和限制：

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

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

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

以下情况不能更新视图：

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

二：存储过程

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

为什么使用存储过程：

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

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

缺陷：

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

执行存储过程  

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语句，而是被该语句检索出来的结果集。在出处了解游标之后，应用程序可以根据需要滚动或浏览其中的数据。

使用游标：

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

创建游标：

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

创建触发器：

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

创建触发器：

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