二、准备条件

三、视图语法及参数

1、CREATE VIEW语法格式

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ]

[ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ]

AS query;

• OR REPLACE：如果视图已存在，则重新定义。
• TEMP | TEMPORARY：创建临时视图。
• view_name：要创建的视图名称。可以用模式修饰。
• column_name：可选的名称列表，用作视图的字段名。如果没有给出，字段名取自查询中的字段名。
• view_option_name [= view_option_value]：该子句为视图指定一个可选的参数。目前view_option_name支持的参数仅有security_barrier，当VIEW试图提供行级安全时，应使用该参数。
• Query：为视图提供行和列的SELECT或VALUES语句。

2、DROP VIEW语法格式

DROP VIEW [ IF EXISTS ] view_name [, ...] [ CASCADE | RESTRICT ];

• IF EXISTS：如果指定的视图不存在，则发出一个notice而不是抛出一个错误。
• view_name：要删除的视图名称。
• CASCADE | RESTRICT：CASCADE：级联删除依赖此视图的对象（比如其他视图）。RESTRICT：如果有依赖对象存在，则拒绝删除此视图。此选项为缺省值。

四、操作示例

1、创建基础表

--删除表 order_info

DROP TABLE IF EXISTS order_info;

--创建表 order_info

CREATE TABLE order_info

(

order_id int PRIMARY KEY

,order_date date not null

,supplier_id int not null

,goods_id char(20) not null

,goods_name char(20) not null

,goods_home varchar(100) not null

,goods_number int not null

,goods_amount int not null

);

--删除表 sell_list_info

DROP TABLE IF EXISTS sell_list_info;

--创建表 sell_list_info

CREATE TABLE sell_list_info

(

sell_id int PRIMARY KEY

,sell_date date not null

,goods_id char(20) not null

,goods_name char(20) not null

,goods_number int not null

,sell_goods_amount int not null

);

--删除表 goods_info

DROP TABLE IF EXISTS goods_info;

--创建表 goods_info

CREATE TABLE goods_info

(

goods_id char(20) PRIMARY KEY

,goods_code varchar(50) not null

,goods_name char(20) not null

,goods_home varchar(100) not null

,goods_number int not null

,purchase_goods_amount int not null

,sell_goods_amount int not null

,supplier_id int not null

,warehouse_id int not null

);

2、设置视图应用场景

示例一，方便数据分析

--删除视图

DROP VIEW IF EXISTS order_info_view;

--依据订单表，按商品名称及订货日期统计商品总数和总价、平均价格。

CREATE VIEW order_info_view

AS

SELECT goods_name

,order_date

,SUM(goods_number) AS total_number

,SUM(goods_number * goods_amount) AS total_amount

,AVG(goods_amount) AS avg_amount

FROM order_info

GROUP BY goods_name, order_date;

示例二，生成月度、年度报表

--删除视图

DROP VIEW IF EXISTS sell_list_sum_view;

--依据销售表，按年度统计所有销售商品的销售总数量和总销售额，以及其对应的仓库编号和供应商编号。

CREATE VIEW sell_list_sum_view

AS

SELECT t1.goods_id

,t1.goods_name

,t1.s_year

,t1.total_number

,t1.total_amount

,t2.supplier_id

,t2.warehouse_id

FROM

(SELECT goods_id

,EXTRACT(YEAR FROM sell_date) AS s_year

,goods_name

,SUM(goods_number) AS total_number

,SUM(goods_number * sell_goods_amount) AS total_amount

FROM sell_list_info

GROUP BY goods_id

,EXTRACT(YEAR FROM sell_date)

,goods_name

) t1

LEFT JOIN

(SELECT goods_id

,supplier_id

,warehouse_id

FROM goods_info

) t2

ON t1.goods_id =t2.goods_id

示例三，实现数据安全

--删除视图

DROP VIEW IF EXISTS goods_info_view;

--只允许某个用户或角色访问某些数据（指定列）

--例如，给用户zhangsan 访问“商品信息管理表（无此表访问权限）”的字段“商品名称、商品存量、商品售价”。

--赋予zhangsan访问此视图的权限

CREATE VIEW goods_info_view

AS

SELECT goods_name

,goods_number

,sell_goods_amount

FROM goods_info;

--赋予zhangsan查询权限

GRANT SELECT ON goods_info_VIEW TO zhangsan;

示例四，简化复杂的查询

--删除视图

DROP VIEW IF EXISTS student_c_view;

--基于上一篇文章中的学生课程成绩表，生成一张视图：获取学生的姓名、课程、成绩、代课老师的信息汇总数据

CREATE VIEW student_c_view

AS

SELECT t2.sname

,t3.cname

,t1.scgrade

,t3.cteacher

FROM

(SELECT sno

,cno

,scgrade

FROM sc

) t1

LEFT JOIN students t2

ON t1.sno=t2.sno

left join course t3

ON t1.cno=t3.cno;

--查看结果

SELECT * FROM student_c_view;

五、小结