文章目录
11. 视图是什么
视图就相当于一个虚拟的表,一张索引的表,其中的内容只是真实表中内容的引用。
视图的灵活使用能够极大的简化工作,比如可以表联结作为中间过程建立视图,以后的所有检索都在视图中进行,再比如需要检索结果结构化处理的,我先将所有数据按照结构化生成视图,作为中间过程,以后所有操作都在视图中进行,这样就减少了语句。
11.1 创建、删除、更新视图
创建视图
创建视图用CREATE VIEW子句
#CREATE VIEW viewname AS
# ...
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;
上述语句创建了一个联结三个表的视图,其中customers是客户表,orders是联系人表,orderitems是订购商品表。
那么对于订购某商品的客户相关信息查询,就直接从视图里面搜索就可以了,极大简化SQL语句。
删除视图
删除采用DROP VIEW viewname;
更新视图
更新视图可以删除再重建,也可以通过CREATE OR REPLACE VIEW来实现,这里的更新视图主要指的是视图结构。
11.2 视图格式化检索的结果
还记得对于某一个表搜索出来人名和国籍想要展示成name(nation)这种格式吗?如果我经常会用到,那岂不是每一次使用都需要先处理成这种形式,会很麻烦。
使用视图能够简化,只要建立如下视图,以后再视图里面进行搜索或者其他操作都能够满足name(nation)这种格式。
CREATE VIEW vendorlocation AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
AS vend_title
FROM vendors
ORDER BY vend_name;
12. 存储过程
12.1 创建、删除、调用过程
储存过程的概念类似于程序语言种的脚本或者函数之类,可以通过调用来实现其内部功能。
(也类似于将一定的语句封装成函数、子程序,以供调用。)
储存过程的创建如下
DELIMITER $$
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
DELIMITER ;
上例是创建了一个简单的过程,主要就是计算商品均价,其中
- 定义过程语句:CREATE PROCEDURE productpricing
- 定义过程体:BEGIN 到 END之间是过程体,BEGIN是过程体开始,END是过程体结束
- 重置语句终止符:由于在过程体中需要用到;号,所以需要先将语句的终止符切换成其他(delimiter $$),待定义完成后再切换回来(delimiter ;)
这组成了最简单的过程。
调用格式如下
CALL productpricing();
结果
+--------------+
| priceaverage |
+--------------+
| 166.6667 |
+--------------+
删除储存过程语句格式如下
DROP PROCEDURE productpricing;
- 注意调用格式和删除格式的区别。
12.2 存储过程详解
12.2.1 存储结构体
上例中是简单的过程,完整的过程结构如下所示
CREATE [DEFINER={user | current_user}]
PROCEDURE procedurename
([parameter:IN|OUT|INOUT param_name datatype])
[characteristic:COMMENT 'string'...]
[begin_label:]BEGIN
statement;
END[end_label];
其中[]内的参数都是可选参数,包括DEFINER关键词,过程参数等。
- definer:主要设定过程体的访问账户
- parameter:设定过程的传入返回参数
- characteristic:特性,比如comment:备注信息等
- begine_label/end_label:过程体的begin-end块中的标签,在嵌套块中能够增强代码层次
12.2.2 变量
局部变量定义,语句如下
DECLARE variable_name datatype [default value]
其中datatype为数据类型,如int,float,date等。
变量赋值,语句如下
SET 变量名 = 表达式值
#用户变量名要以@为开头
使用INTO关键词也能达到赋值的目的
SELECT expression INTO variable
12.2.3 控制语句
12.2.3.1 条件语句
if-then-else-end if
if condition then
statement;
else
statement;
end if;
case
case
when condition1 then
statement;
when condition2 then
statement;
else
end case
12.2.3.2 循环语句
while
while condition then
statement;
end while;
repeat end repeat
repeat
statement;
until condition
end repeat;
loop end loop
[loop_label:]loop
statement;
if condition then
leave loop_label;
end if;
end loop;
label
可以在关键词前添加label,使用leave就可以跳出循环。
12.2 范例:带参数的过程及使用
储存过程就和函数一样,能够传递参数(传入参数和返回参数),例如
CREATE PROCEDURE productpricing
( OUT p1 DECIMAL(8,2), OUT ph DECIMAL(8,2))
BEGIN
SELECT Min(prod_price) INTO p1
FROM products;
SELECT Max(prod_price) INTO ph
FROM products;
END;
其中()内就是传递的参数,传入用IN关键词,返回用OUT关键词,INOUT就是即是传入也是返回参数。
INTO关键词相当于赋值。
在进行调用的时候格式如下
CALL productpricing(@pricelow, @pricehigh)
#执行productpricing过程
SELECT @pricelow, @pricehigh;
#显示返回参数的值需要用上述语句
MySQL必知必会
MySQL菜鸟教程