使用视图
视图是虚拟的表,只包含使用时动态检索数据的查询
视图不包含数据,每次使用视图时,都必须处理查询执行时所需的任一个检索
应用:
创建不受特定数据限制的视图,简化复杂的sql操作,可以一次性编写基础的sql,然后根据需要多次使用。例如:
创建视图,联结三个表,返回已订购了任意产品的所有客户列表:
CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
检索订购了产品TNT2的客户:
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
使用视图过滤不想要的数据,使用表的组成部分而不是整个表。例如:
创建视图过滤没有电子邮件地址的用户:
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
查询没有email的用户:
SELECT *
FROM customeremaillist;
保护数据,给用户授予表的特定部分访问权限
使用视图重新格式化检索出的数据。例如:
创建视图返回供应商名+位置
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;
规则:
视图命名唯一,不能与其他视图重名
创建视图需要有足够的访问权限
视图可以嵌套,可以利用其他视图中检索数据的查询来构造一个视图
可以用ORDERBY
视图不能索引,也不能有关联的触发器或默认值
视图可以和表一起使用,如编写一条联结表和视图的select语句
使用:
CREATE VIEW
SHOW CREATE VIEW viewname; 查看创建视图的语句
DROP VIEW viewname;
更新视图:先DROP再CREATE,或者用CREATE OR REPLACE VIEW
通常视图是可以更新的(INSERT\UPDATE\DELETE),更新一个视图将更新其基表
如果视图中有以下操作,不能进行视图的更新:
分组(GROUP BY\HAVING)
联结
子查询
并
聚集函数(Min\Count\Sum)
DISTINCT
导出(计算)列
视图主要用来数据检索
使用存储过程
创建存储过程,是为以后的使用而保存的一条或多条Mysql语句的集合。
使用原因:简单、安全、高性能:
把处理封装在容易使用的单元中,简化复杂的操作
不需要程序员反复进行一系列处理步骤,保证数据的完整性、一致性,防止执行多个步骤出错
简化对变动的管理
安全性,限制对基础数据的访问,减少无意识或别的原因导致的数据错误
提高性能,比单独sql语句块
缺点:
编写复杂
可能没有创建存储过程的安全访问权限
使用:
执行:CALL productpricing(@pricelow,@pricehige,@priceaverage);
创建:CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
命令行使用存储过程:(DELIMITER指定新的语句分隔符,可用除了\之外任何字符)
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END //
删除:DROP PROCEDURE productprcing (IF EXISTS);
使用参数:
IN 入参,OUT 出参,不能返回记录集(多个行和列)
CREATE PROCEDURE productpricing(
OUT pl 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;
CALL productpricing(@pricelow,@pricehige,@priceaverage);
显示检索出的平均价格(Mysql所有变量以@开始)
SELECT @pricelow,@pricehige,@priceaverage;
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;
智能存储:
检查存储过程:SHOW CREATE PROCEDURE ordertotal;