MySQL必知必会(六)阅读笔记

第二十二章 使用视图

视图

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

为什么使用视图

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

使用视图

 视图用CREATE VIEW语句来创建。
 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
 用DROP删除视图，其语法为DROP VIEW viewname;。
 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE ORREPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

利用视图简化复杂的联结

CREATE VIEW ppp AS 
SELECT cust_name,cust_contact,prod_id
FROM customers c
INNER JOIN orders o ON c.cust_id = o.cust_id
INNER JOIN orderitems os ON o.order_num = os.order_num

用视图重新格式化检索出的数据

CREATE VIEW PPT AS
SELECT Concat(Trim(vend_name),'(',Trim(vend_country),')') AS ppt
FROM vendors
ORDER BY vend_name

用视图过滤不想要的数据

CREATE VIEW PPO AS
SELECT cust_id,cust_name
FROM customers
WHERE cust_email IS NOT NULL;

更新视图

1. 如果视图定义中有以下操作，则不能进行视图的更新：
    分组（使用GROUP BY和HAVING）；
    联结；
    子查询；
    并；
    聚集函数（ Min()、 Count()、 Sum()等）；
    DISTINCT；
    导出（计算）列。

第二十三章 使用存储过程

存储过程

1. 为什么要使用存储过程
    通过把处理封装在容易使用的单元中，简化复杂的操作
    由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
    简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
    提高性能。因为使用存储过程比使用单独的SQL语句要快。
    存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码
   换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。显然，它们都很重要。
2. 它的一些缺陷。
    一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
    你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

使用存储过程

1. 创建存储过程

//创建
CREATE DEFINER=`root`@`localhost` PROCEDURE `productpricing`()
BEGIN
	SELECT AVG(prod_price) AS pri
	FROM products;
END
//使用
CALL productpricing();
//删除存储过程
DROP PROCEDURE productpricing;

使用参数

变量（variable） 内存中一个特定的位置，用来临时存储数据
MySQL支持IN（传递给存储过程）、 OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。

CREATE DEFINER=`root`@`localhost` PROCEDURE `productpricing`(OUT `p1` decimal,OUT `ph` decimal,OUT `pa` decimal)
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
//使用
CAll productpricing(@pl,@ph,@pa);
//查询
SELECT @pl,@ph,@pa

CREATE DEFINER = CURRENT_USER PROCEDURE `pptt`(IN `prodid` int,OUT `ototal` smallint)
BEGIN
	SELECT SUM(item_price*quantity)
	FROM orderitems
	WHERE order_num = prodid
	INTO ototal
END;

IF语句 这个例子给出了MySQL的IF语句的基本用法。 IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）。在以后章节中我们将会看到IF的其他用法（以及其他流控制语句)

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`(IN `orderNum` int,IN `taxable` TINYINT,OUT `ototal` double)
BEGIN
	DECLARE total DOUBLE;
	DECLARE taxrate INT DEFAULT 6;
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = orderNum
	INTO total;
	IF taxable THEN
		SELECT total+(total/100*taxrate) INTO ototal;
	END IF;
		SELECT total INTO ototal;

END

检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：