视图

  • 视图是虚拟的表

视图的作用

  • 重用SQL语句
  • 简化复杂的SQL操作
  • 使用表的组成部分而不是整个表
  • 保护数据。可以给用户授予部分权限
  • 更改数据格式和表示

视图的规则和限制

  • 和表一样，视图必须唯一命名
  • 对于可以创建的视图数目没有限制
  • 为了创建视图，必须具有足够的访问权限
  • 视图可以嵌套
  • ORDER BY 可以用在视图中，若 SELECT 语句中也有ORDER BY，则会覆盖视图中的 ORDER BY
  • 视图不能索引，也不能有关联的触发器或默认值
  • 视图可以和表一起使用

创建视图

1
2
3
4
5
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;
1
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

Tips

  • 视图一般只用于检索，而不用于更新

存储过程

简单、安全、高性能

存储过程的作用

  • 通过把处理封装在容易使用的单元中，简化复杂的操作
  • 由于不要求反复建立一系列处理步骤，这保证了数据的完整性
  • 简化对变动的管理
  • 提高性能。使用存储过程比单独使用的SQL语句更快
  • 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
end;

-- 调用存储过程
CALL productpricing();

-- 删除存储过程
DROP PROCEDURE productpricing;

-- 使用参数
CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8, 2),
    OUT ph DECIMAL(8, 2),
    OUT pa DECIMAL(8, 2)
)
BEGIN
    SELECT Min(prod_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT Avg(prod_price)
    INTO pa
    FROM products;
END;

-- 调用，所有MySQL变量名都必须以 @ 开始
CALL productpricing(@pricelow, @pricehigh, @priceaverage);

-- 显示
SELECT @pricelow, @pricehigh, @priceaverage;

-- IN、OUT 参数
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);

Tips

  • IN、OUT 参数应该就是相当于高级语言中的函数的传值和传引用
  • 用 DECLARE 定义局部变量
  • SHOW PROCEDURE STATUS; 可以查看存储过程列表