首页 > 代码库 > MySQL必知必会读书笔记-8（表的操纵，视图，存储过程，游标，触发器）
MySQL必知必会读书笔记-8（表的操纵，视图，存储过程，游标，触发器）
1--表的操作
创建一个表
在创建表的时候应确保这个表之前不存在，若之前存在则应删除之后再创建
AUTO_INCREMENT表示自动增加
使用默认值的话，在列定义后加DEFAULT 常量，表示默认初始化为一个常量
更新一个表
添加一个列
删除一个列
定义外键
删除表
重命名一个表
2--视图
什么是视图
视图是一个虚拟的表，它与普通的表不一样，视图只包含使用时动态数据的查询。
举例：
搜索买了某个产品的客户的信息的查询语句，我们可以将其包装成为一个虚拟表productcustomers，
使用这个虚拟表可以轻松检索出相同的数据
这就是视图
为什么使用视图？
视图的规则和限制
3--视图的使用
创建视图
示例：
创建一个视图，这个视图是包含了顾客对应的订单以及其对应的订单内容的虚拟表
CREATE VIEW productcustomers ASSELECT cust_name, cust_contact, prod_idFROM customers, orders, orderitemsWHERE customers.cust_id = orders.cust_idAND orderitems.order_num = orders.order_num
通过这个视图搜索订购了TNT的顾客
SELECT cust_name, cust_contactFROM productcustomersWHERE prod_id = ‘TNT2‘;
由此可见视图简化了复杂SQL代码的使用，同时也提高了代码的复用性。
使用视图重新格式化检索出的数据
使用视图过滤数据
过滤没有邮箱的顾客
使用视图与计算字段
列出某个订单物品表中的每一种物品的总价
CREATE VIEW orderitemsexpanded ASSELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_priceFROM orderitems
SELECT *FROM orderitemsexpandedWHERE order_num = 20005;
4--视图更新
一个表可以进行更新操作（insert，update和delete）
虚拟表即视图同样可以，但是受到了一些限制
若视图中有以下操作则不可以更新
5--存储过程
存储过程就是为以后的使用而保存的一个SQL语言的集合，就像一个代码块
作用
编写存储过程
写一个返回产品的平均价格的存储过程
CREATE PROCEDURE productpricing()BEGIN SELECT Avg(prod_price) AS priceaverage FROM products;END;
执行它
CALL productpricing();
存储过程的删除
带参数的存储过程
计算产品表中的最低，最高和平均价格（只传进去参数，不传出来）
CREATE PROCEDURE productpricing( OUT p1 DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa 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 pa FROM products;END;
其中关键字OUT代表传出这个参数，如果是IN的话代表传入参数，INOUT则代表传入和传出
//执行存储过程
CALL productpricing(@pricelow, @pricehigh, @priceaverage)
SELECT @priceaverage
输入订单号返回订单的总价（参数的传入和传出）
CREATE PROCEDURE ordertotal ( IN number INT, OUT total DECIMAL(8, 2))BEGIN SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = number INTO total;END;
CALL ordertotal(20005, @total);SELECT @total;
6--带条件语句的存储过程
假如我们要计算订单的总价，要对总价增加营业税，不过这个营业税只针对符合某些条件的顾客
CREATE PROCEDURE ordertotal( IN number INT, IN taxable BOOLEAN, #布尔值，决定是否加税 OUT total DECIMAL(8, 2))BEGIN DECLARE ttotal DECIMAL(8,2); DECLARE taxrate INT DEFAULT 6;#税率初始化 SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = number INTO ttotal; IF taxable THEN SELECT ttotal+(ttotal/100*taxrate) INTO ttotal; END IF; SELECT ttotal INTO total;END;
CALL ordertotal(20005, FALSE, @total);SELECT @total;
CALL ordertotal(20005, TRUE, @total);SELECT @total;
7--检查存储过程
8--游标
MySQL中，游标只适用于存储过程和函数。
游标是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集，
在存储了游标之后，应用程序可以根据需要滚动或者浏览其中的数据。
游标的使用
查询所有订单的总价
1 CREATE PROCEDURE processorders() 2 BEGIN 3 DECLARE done BOOLEAN DEFAULT FALSE; 4 DECLARE o INT; 5 DECLARE t DECIMAL(8, 2); 6 7 DECLARE ordernumbers CURSOR #定义一个游标，读取出orders中的所有order_num 8 FOR 9 SELECT order_num FROM orders;10 11 DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done = TRUE;#‘02000‘是一个未找到条件，达到这个条件之后done就被设置为true了12 13 CREATE TABLE IF NOT EXISTS ordertotals14 (order_num INT, total DECIMAL(8,2));15 16 OPEN ordernumbers; #使用游标前必须打开它，使用之后要关闭17 18 REPEAT #反复执行19 FETCH ordernumbers INTO o; #使用FETCH读取这个游标的一行，再次执行的时候读取其下一行20 CALL ordertotal(o, 1, t); #这个存储过程是上一节的例子21 INSERT INTO ordertotals(order_num, total) 22 VALUES(o,t);23 UNTIL done END REPEAT;24 25 CLOSE ordernumbers;26 END;27 28 CALL processorders();29 30 SELECT * 31 FROM ordertotals;
这段代码就是通过游标查出所有订单号，通过循环一个个输出来，计算每个订单号的总额，然后将数据添加到新表ordertotals中。
9--触发器
MySQL中，触发器是在执行DELETE， INSERT， UPDATE语句时可以自动响应的一个处理动作。
比如每当我们从仓库中订购一个产品，仓库中的对应产品数量就会少一个。
创建触发器
每一个触发器名字必须是唯一的，并且只有基本表支持触发器
示例：
每有一个订单被删除，这个订单就会被添加到订单记录表里去
#新建订单记录表
CREATE TABLE archive_orders( order_num int NOT NULL , order_date datetime NOT NULL , cust_id int NOT NULL , PRIMARY KEY (order_num)) ENGINE=InnoDB;CREATE TRIGGER deleteorder BEFORE DELETE ON ordersFOR EACH ROWBEGIN INSERT INTO archive_orders(order_num, order_date, cust_id) VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);END;
由于订单表中有外键的限制，这些语句不可以直接使用，这里仅仅作为一个示范。
MySQL必知必会读书笔记-8（表的操纵，视图，存储过程，游标，触发器）