MySQL常用操作（五）

MySQL常用操作

一、使用视图

1.视图

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

此查询用来检索订购了某个特定产品的用户

SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';

假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以如下轻松地检索出相同的数据

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

这就是视图的作用。productcustomers是一个视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询

• 为什么使用视图

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

• 重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的，在添加或更改这些表中的数据时，视图将返回改变过的数据

• 性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试

2.视图的规则和限制

• 下面是关于视图创建和使用的一些最常用的规则和限制
  • 与表一样，视图必须唯一命名
  • 对于可以创建的视图数目没有限制
  • 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
  • ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句也包含ORDER BY，那么该视图中的ORDER BY将被覆盖
  • 视图不能索引，也不能有关联的触发器或默认值
  • 视图可以和表一起使用。例如，编写一条联结表 和视图的SELECT语句

3.使用视图

• 视图用CREATE VIEW语句创建
• 使用SHOW CREATE VIEW viewname查看创建视图的语句
• 使用DROP VIEW viewname删除视图
• 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则会创建一个视图，如果要更新的视图存在，则会替换原有视图

1. 利用视图简化复杂的联结

视图最常见的应用之一时隐藏复杂的SQL，这通常都会涉及联结

CREATE VIEW procudtcustomers AS
SELECT cust_name,cust_contact,pro_id
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

可以看出，视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用

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

视图另一个常见的用途是重新格式化检索出的数据

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
FROM vendors
ORDER BY vend_name;

3. 用视图过滤不想要的数据

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

• WHERE子句与WHERE子句：如果从视图检索数据时使用了一条WHERE子句，则两组子句将自动组合

4. 使用视图与计算字段

CREATE VIEW orderitemsexpanded AS
SELECT order_num,prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM orderitems;

5. 更新视图

• 通常，视图是可以更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。更新一个视图将更新其基表
• 但是，并非所有视图都是可更新的，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。如果视图定义中有以下操作，则不能进行视图的更新：
  • 分组（使用GROUP BY和HAVING）
  • 联结
  • 子查询
  • 并
  • 聚集函数
  • DISTINCT
  • 导出（计算）列
• 将视图用于检索：一般，应该将视图用于检索而不用于更新

二、使用存储过程

1.存储过程

• 存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不限于批处理

2.使用存储过程

1. 执行存储过程

• MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。

CALL productpricing(@pricelow,@pricehigh,@priceaverage);

2. 创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
	SELECT AVG(prod_price) AS priceaverage
	FROM products;
END;

• 如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL语句出现语法错误。解决办法是临时更改命令行实用程序的语句分隔符。如下所示：

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
	SELECT AVG(prod_price) AS priceaverage
	FROM products;
END //
DELIMITER ;

3. 删除存储过程

• 存储过程在创建之后，被保存在服务器上以供使用，直至被删除

DROP PROCEDURE productpricing;

这条语句删除创建的存储过程，注意后面没有（），只给出存储过程名

• 仅当存在时删除：如果过程不存在，那么DROP PROCEDURE会报错，可以使用DROP PROCEDURE IF EXISTS（如果过程不存在，也不产生错误）

4. 使用参数

• 一般，存储过程并不显示结果，而是把结果返回给指定的变量
• 变量：内存中一个特定的位置，用来临时存储数据

CREATE PROCEDURE productpricing(OUT p1 DECIMAL(8,2),OUT p2 DECIMAL(8,2),OUT p2 DECIMAL(8,2))
BEGIN
	SELECT MIN(prod_price)
	INTO p1
	FROM products;
	SELECT MAX(prod_price)
	INTO p2
	FROM products;
	SELECT AVG(prod_price)
	INTO p3
	FROM products;
END;

这是product pricing的修改版本。存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量中（通过指定INTO关键字）

• MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）、INOUT（对存储过程传入和传出）类型的参数

为调用修改过的存储过程，必须指定3个变量名，如下所示：

CALL productpricing(@pricelow,@pricehigh,@priceaverage);

• 所有MySQL变量都必须以@开始

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;

5. 检查存储过程

• 为获得存储过程的详细信息，使用SHOW PROCEDURE STATUS;