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; ```