创建和使用视图及异名

2019独角兽企业重金招聘Python工程师标准>>>

• 视图的基本介绍

概念：

视图是一个虚拟的表，外观和行为上与类似，但是不需要实际的物理存储，它只是从表里引用数据

作用：

a、使用视图来简化数据访问

例如用户不知道如何去数据库使用结合获取数据时，创建一个视图来包含表的结合，让用户可以从这个视图获取数据

b、使用视图作为一种安全形式

例如一张表里有敏感信息，比如薪水，身份证等，姓名，年龄等，可以创建一个视图只包含姓名、年龄非敏感信息

c、使用视图维护摘要数据

 

• 创建视图

语法

CREATE VIEW  view_name AS
SELECT column1,column2
FROM table_name
WHERE [condition]

 

案例

 由CUSTOMERS 表创建名字、年龄字段的视图

CREATE VIEW CUSTOMERS_VIEW AS
SELECT NAME,
       AGE
FROM CUSTOMERS

可以像普通的数据表一样查询

SELECT *
FROM CUSTOMERS_VIEW

注：

视图可以从一个、多个表或者视图中被创建，语法都是类似的，基础数据的表或者视图被删除，上层的全部视图都不能使用了

• WITH CHECK OPTION

WITH CHECK OPTION 是一个 CREATE VIEW 语句的一个可选项，用于保证所有的 UPDATE 和 INSERT 语句都满足视图定义的条件，如果不满足则会返回错误

案例  

--创建一个视图，但是 WITH CHECK OPTION是打开的
CREATE VIEW COUSTOMERS AS
SELECT NAME
       AGE
FORM CUSTOMERS
WHERE AGE IS NOT NULL
WITH CHECK OPTION
-- AGE字段不能为 NULL

 

-- AGE字段不能为 NULL

注：

在基于视图创建另一个视图时， WITH CHECK OPTION有两个选线

1、默认选项CASCADE，其是 ANSI标准语法，但Micsoft SQL Server 和 Oracle稍有不同的CASCADE关键字，该项会选择所有底层视图、所有完整性约束，以及新视图的定义条件，因此使用CASCADED选项创建视图是更安全的做法，基表的引用完整性也得到保护

2、LOCAL 选项只检测两个视图的完整性约束和新视图的定义条件，不检查底层的表

• 从视图创建表

可以从视图创建一个表，就像从一个表创建另一个表（或从一个视图创建另一个视图）一样

语法

CREATE TABLE TABEL_NAME AS
SELECT COLUMN1,COLUMN2
FROM VIEW_NAME
[WHERE CONDITION1 [,CONDITION2]
[OREDER BY]

注

表与视图的主要区别在于表包含实际的数据、占据物理存储空间，而视图不包含数据，而且值需要保存视图定义（查询语句）

• 视图与ORDER BY 子句

CREATE VIEW 语句里不能包含 ORDER BY 子句，但是GROUP BY子句用于CREATE VIEW 语句时，可以起到类似 ORDER BY 子句的作用

CREATE VIEW NAME AS
SELECT COLUMN1,COLUMN2
FROM TABLE
GROUP BY COLUMN2,COLUMN1

 

• 通过视图更新数据

在一定的条件下，视图底层数据可以进行更新

书籍介绍

a、视图不包含结合

b、视图不包含 GROUP BY 子句

c、视图不包含 UNION 语句

d、视图不包含任何组函数

e、不能使用 DISTINCT 子句

f、WHERE 子句包含的嵌套的表表达式不能与 FROM 子句引用同一个表

g、视图可执行 INSERT、UPDATE和DELETE等语句

另外一种说法

a、SELECT 子句不能包含 DISTINCT 关键字

b、SELECT 子句不能包含任何汇总函数（summary functions）

c、SELECT 子句不能包含任何集合函数（set functions）

d、SELECT 子句不能包含任何集合运算符（set operators）

e、SELECT 子句不能包含 ORDER BY 子句

f、FROM 子句中不能有多个数据表

g、WHERE 子句不能包含子查询（subquery）

h、查询语句中不能有 GROUP BY 或者 HAVING

i、计算得出的列不能更新

j、视图必须包含原始数据表中所有的 NOT NULL 列，从而使 INSERT 查询生效

• 删除视图

DROP VIEW 命名用于从数据库里删除视图，它有两个选项

1、RESTRICT 如果这选项进行删除操作，而其他视图在约束里有所引用，删除操作就会出错

2、CASCADE 选项，而且其他视图或约束被引用，DROP VIEW 也会成功，而且底层的视图或约束也会被删除

语法

DROP VIEW NAMES2

 

• 异名

就是表和视图的另一个名称，异名可以创建为 PUBLIC 或 PRIVATE，PUBLIC的异名可以被数据库里的其他用户使用，而PRIVATE异名只能被所有者和拥有权限的用户使用

语法

CREATE [PUBLIC|PRIVATE] SYNONYM SYNONYM_NAME FOR TABLE|VIEW

 

案例

--为表创建一个异名
CREATE SYNONYM CUST FOR CUSTOMER_TBL
SELECT  CUST_NAME
FORM CUST

 

• 删除异名

语法

DROP [PUBLIC|PRIVATE] SYNONYM SYNONYM_NAME

案例

DROP SYNONYM CUST

注

异名并不属于ANSI SQL标准，MySQL不支持异名，但我们可以使用视图来实现同样的同年

 

转载于:https://my.oschina.net/u/1785519/blog/1566350