一、视图
视图是虚拟表,与包含数据表不一样,视图只包含使用时动态检索数据的查询。(视图本身并不存储数据,实际上视图保存的是 SELECT 语句)。
1、为什么使用视图
- 重用SQL语句。
- 简化复杂的SQL操作。
- 使用表的 组成部分而不是整个表。
- 保护数据,可以给用户授权表的特定部分。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
创建视图需要使用 CREATE VIEW 语句。 删除视图需要使用 DROP VIEW 语句。
2、视图和表![](https://img-blog.csdnimg.cn/direct/66d3d7d4ecac4175a1728ad83d771268.png)
3、视图的优点
1、第一点是由于视图无需保存数据,因此可以节省存储设备的容量。
2、可以将频繁使用的 SELECT 语句保存成视图,这样不用每次都重新书写。
4、视图的限制
- 因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任何一个检索。如果使用多个联结表和过滤时,会使性能大大降低。
- 视图不能被索引,也不能有关联的触发器或默认值。
- 定义视图时不能使用ORDER BY子句。因为视图和表一样,数据行都是没有顺序的。
- 视图和表需要同时进行更新,因此通过汇总得到的视图无法进行更新。
- 多重视图会降低 SQL 的性能,应该避免在视图的基础上创建视图。
5、创建和删除视图
CREATE VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
AS
<SELECT语句>
CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
-- 使用视图
SELECT product_type, cnt_product
FROM ProductSum;
CREATE VIEW ProductSumJim (product_type, cnt_product)
AS
SELECT product_type, cnt_product
FROM ProductSum
WHERE product_type = '办公用品';
-- 使用视图
SELECT product_type, cnt_product
FROM ProductSumJim;
DROP VIEW 视图名称 (< 视图列名 1>, < 视图列名 2>, …… )
DROP VIEW ProductSum;
使用CASCADE选项来删除关联视图
DROP VIEW ProductSum CASCADE;
二、存储过程
1、为什么要使用存储过程
-
通过把处理封装在容易使用的单元中,简化复杂的操作。
-
由于不要求反复建立一系列处理步骤,这保证了数据的完整性。 如果所有开发人员和应用程序都使用同一(试验和测试)存储过 程,则所使用的代码都是相同的。 这一点的延伸就是防止错误。需要执行的步骤越多,出错的可能性就越大。防止错误保证了数据的一致性。
-
简化对变动的管理。如果表名、列名或业务逻辑(或别的内容) 有变化,只需要更改存储过程的代码。
-
提高性能。因为使用存储过程比使用单独的SQL语句要快。
-
存在一些只能用在单个请求中的MySQL元素和特性,存储过程可 以使用它们来编写功能更强更灵活的代码。
-
存储过程的编写比基本的SQL语句更复杂,编写存储过程需要更高的技能,更丰富的经验。
2、创建及执行存储过程
-
MySQL 称存储过程的执行为调用,因此 MySQL 执行存储过程的语句为 CALL。
CALL productpricing( )
执行刚创建的存储过程并显示返回的结果。 - 创建一个存储过程,储过程名为productpricing
CREATE PROCEDURE productpeicing() BEGIN SELECT AVG(price) as priceaverage FROM Products; END;
- 删除存储过程
注意存储过程名后面并没有使用(),仅给出了存储过程名。DROP PROCEDURE productpricing;
- 使用存储过程参数
一般,存储过程并不显示结果,而是把结果返回给你指定的变量。-- 创建存储过程参数 CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT pn DECIMAL(8,2), OUT pm DECIMAL(8,2) ) BEGIN SELECT MIN(price) INTO pl FROM products; SELECT MAX(price) INTO pn FROM products; SELECT AVG(price) INTO pm FROM products; END ;
关键字OUT指出相应的参数用来从存储过程传出一个值。Mysql支持IN(传递给存储过程)、OUT(从存储过程传出)和INOUT(对存储过程传入和传出)类型的参数。
存储过程的参数允许的数据类型与表中使用的数据类型相同。
注意:1、记录集不是允许的类型,因此,不能通过一个参数返回多个行和列。
2、变量名:所有MySQL变量都必须以@开始。 -
CREATE PROCEDURE ordertotal( IN onumber INT, -- 订单号被传入存储过程 oUT ototal DECIMAL(8,2) -- ototal定义为OUT,因为要从存储过程返回合计. ) BEGIN SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal; -- INTO使用ototal存储计算出来的合计。 END; -- 调用ordertotal存储过程 CALL ordertotal (20005,@total); -- 显示合计 SELECT @total ;
注意:必须给ordertotal传递两个参数;第一个参数为订单号,第二个参数为包含计算出来的合计的变量名。
3、建立智能化的存储过程
代码如下:
-- 智能化的存储过程
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total,optionally adding tax'
-- COMMENT关键字用于在存储过程或函数中添加注释
BEGIN
-- 使用"total"变量来存储和操作精确的小数值
DECLARE total DECIMAL(8,2);
-- 只能存储整数值,不能存储小数或分数
DECLARE taxrate INT DEFAULT 6;
SELECT SUM(price * quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF;
SELECT total INTO ototal;
END;
4、检查存储过程
为显示用来创建一个存储过程的CREATE语句;
SHOW CREATE PROCEDURE ordertotal ;
SHOW PROCEDURE STATUS LIKE 'ordertotal';
三、游标
-- 游标的使用
CREATE PROCEDURE processorders ()
BEGIN
-- 声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- 命名和定义游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- 声明继续处理程序
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
-- 创建一个表去存储结果
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,tota1)VALUES(o,t);
-- 结束循环
UNTIL done END REPEAT;
-- 关闭游标
CLOSE ordernumbers;
END;
四、触发器
触发器是MySQL响应以下任意语句而 自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句)。
DELETE、INSERT和UPDATE语句支持触发器。
1、创建触发器需要满足:
- 唯一的触发器名;
- 触发器关联的表;
- 触发器应该响应的活动(DELETE、INSERT或UPDATE);
- 触发器何时执行(处理之前或之后)。
注意:Ⅰ、保持每个数据库的触发器名唯一。
Ⅱ、仅支持表 只有表才支持触发器,视图不支持(临时表也不支持)。
CREATE TRIGGER newproduct
AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' ;
触发器按每个表每个事件每次地定义,每个表每个事件每次只允许一个触发器。因此,每个表最多支持6个触发器(每条INSERT、UPDATE 和 DELETE的之前和之后)。
2、删除触发器
DROP TRIGGER newproduct;
3、使用触发器
1、INSERT触发器
CREATE TRIGGER neworder
AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
-- 插入新的行
INSERT INTO orders(order_date,cust_id)
VALUES(Now(),10001);
2、 DELETE触发器
3、UPDATE触发器