文章转载自: http://www.2cto.com/database/201508/427083.html
被面试官问到视图,一脸懵逼,回来赶紧找谷歌,搞清楚。自己是个小白,所以转载的这篇文章是一些基础。以防忘了回来再熟悉,所以转载博主的这篇文章,感谢博主!
一 什么是数据库视图
视图即是虚拟表,也称为派生表,因为它们的内容都派生自其它表的查询结果。虽然视图看起来感觉和基本表一样,但是它们不是基本表。基本表的内容是持久的,而视图的内容是在使用过程中动态产生的。
二 数据准备
员工表
CREATE TABLE t_employee(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME CHAR(30) NOT NULL,
SEX CHAR(2) NOT NULL,
AGE INT NOT NULL,
DEPARTMENT CHAR(10) NOT NULL,
SALARY INT NOT NULL,
HOME CHAR(30),
MARRY CHAR(2) NOT NULL DEFAULT '否',
HOBBY CHAR(30)
);
插入数据:
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'小红','女',20,'人事部','4000','广东','否','网球');
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'明日','女',21,'人事部','9000','北京','否','网球');
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'天天','男',22,'研发部','8000','上海','否','音乐');
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'大大','女',23,'研发部','9000','重庆','否','无');
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'王下','女',24,'研发部','9000','四川','是','足球');
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'无名','男',25,'销售部','6000','福建','否','游戏');
INSERT INTO t_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'不知道','女',26,'销售部','5000','山西','否','篮球');
插入的结果:
然后再定义一张员工信息表:
create TABLE t_employee_detail(
ID INT PRIMARY KEY,
POS CHAR(10) NOT NULL,
EXPERENCE CHAR(10) NOT NULL,
CONSTRAINT `FK_ID` FOREIGN KEY(ID) REFERENCES t_employee(ID)
);
插入如下:
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(1,'人事管理','工作二年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(2,'人事招聘','工作二年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(3,'初级工程师','工作一年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(4,'中级工程师','工作二年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(5,'高级工程师','工作三年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(6,'销售代表','工作二年');
INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(7,'销售员','工作一年');
内容:
三 使用案例
1. 语法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name.]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
通过该语句可以创建视图,若给定了[OR REPLACE],则表示当已具有同名的视图时,将覆盖原视图。select_statement是一个查询语句,这个查询语句可从表或其它的视图中查 询。视图属于数据库,因此需要指定数据库的名称,若未指定时,表示在当前的数据库创建新视图。
表和数据库共享数据库中相同的名称空间,因此,数据库不能包含相同名称的表和视图,并且,视图的列名也不能重复。
在创建视图前应先看看是否有权限:
SELECT SELECT_priv,create_view_priv from mysql.user WHERE user='root'
Y表示有创建的权限
2、单表上创建视图
在员工表是创建视图
CREATE VIEW V_VIEW1(ID, NAME, SEX, AGE,DEPARTMENT) AS SELECT ID, NAME, SEX, AGE,DEPARTMENT
FROM learning.t_employee;
然后是显示内容:
SELECT * FROM V_VIEW1
3、多表上创建视图
SELECT * FROM V_VIEW2
4、查看视图
(1)DESCRIBE 命令
DESCRIBE V_VIEW2
![\](http://www.2cto.com/uploadfile/Collfiles/20150801/2015080109442047.png)
(2)SHOW TABLE STATUS
show CREATE view V_VIEW2
![\](http://www.2cto.com/uploadfile/Collfiles/20150801/2015080109442050.png)
5、修改视图
(1)CREATE OR REPLACE命令
CREATE OR REPLACE VIEW V_VIEW1(ID, NAME, SEX) AS SELECT ID, NAME, SEX FROM learning.t_employee;
![\](http://www.2cto.com/uploadfile/Collfiles/20150801/2015080109442051.png)
(2) ALTER 命令
ALTER VIEW V_VIEW1(ID, NAME) AS SELECT ID, NAME FROM learning.t_employee;
SELECT * FROM learning.v_view1
![\](http://www.2cto.com/uploadfile/Collfiles/20150801/2015080109442052.png)
6、更新视图
在MySQL中,更新视图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)表中的数据。因为视图是一个虚拟表,其中没有数据,所以通过视图更新时,都是转换到基本表来更新。
更新视图时,只能更新权限范围内的数据。超出了范围,就不能更新。
更新前:
更新后:
UPDATE V_VIEW2 SET POS='高级工程师' WHERE NAME='天天'
![\](http://www.2cto.com/uploadfile/Collfiles/20150801/2015080109442054.png)
对应 的真实表上的数据也发生改变 了
SELECT * FROM learning.t_employee_detail WHERE t_employee_detail.ID=3
![\](http://www.2cto.com/uploadfile/Collfiles/20150801/2015080109442055.png)
不可更新的视图:
某些视图是可更新的。也就是说,可以在诸如UPDATE、DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可更新的视图,在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构,这类结构会使得视图不可更新。更具体地讲,如果视图包含下述结构中的任何一种,那么它就是不可更新的:
· 聚合函数(SUM(), MIN(), MAX(), COUNT()等)。
· DISTINCT
· GROUP BY
· HAVING
· UNION或UNION ALL
· 位于选择列表中的子查询
· Join
· FROM子句中的不可更新视图
· WHERE子句中的子查询,引用FROM子句中的表。
· 仅引用文字值(在该情况下,没有要更新的基本表)。
· ALGORITHM = TEMPTABLE(使用临时表总会使视图成为不可更新的)。
注意
视图中虽然可以更新数据,但是有很多的限制。一般情况下,最好将视图作为查询数据的虚拟表,而不要通过视图更新数据。因为,使用视图更新数据时,如果没有全面考虑在视图中更新数据的限制,就可能会造成数据更新失败。
CASCADED和LOCAL能不能决定视图是否能更新?
WITH[CASCADED|LOCAL] CHECK OPTION能不能决定视图是否能更新?这两个参数的基本定义如下:
LOCAL参数表示更新视图时只要满足该视图本身定义的条件即可。
CASCADED参数表示更新视图时需要满足所有相关视图和表的条件。没有指明时,该参数为默认值。
With check option的用法:
(with check option对于没有where条件的视图不起作用的)
1
|
CREATE
VIEW
V_VIEW3(ID,
NAME
,SEX,AGE,DEPARTMENT,SALARY, HOME, MARRY, HOBBY) AS SELECT ID, NAME, SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY FROM learning.t
_employee
WHERE
DEPARTMENT=
'人事部'
WITH
LOCAL
CHECK
OPTION
;
|
然后插入一条:
INSERT INTO learning.V_VIEW3(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'会会会会','女',20,'人事部','4500','广东','否','网球');
看下结果:
SELECT * FROM learning.V_VIEW3
![\](http://www.2cto.com/uploadfile/Collfiles/20150801/2015080109442157.png)
同时看真实表中的数据:
再来插入一条:
INSERT INTO learning.V_VIEW3(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'qqqqvasvas','女',20,'研发部','4500','上海','否','网球');
![\](http://www.2cto.com/uploadfile/Collfiles/20150801/2015080109442159.png)
结果显示插入失败
对于with check option用法,总结如下:
通过有with check option选项的视图操作基表(只是面对单表,对连接多表的视图正在寻找答案),有以下结论: 插入后的数据,通过视图能够查询出来就符合WITH CHECK OPTION 否则就不符合;
首先视图只操作它可以查询出来的数据,对于它查询不出的数据,即使基表有,也不可以通过视图来操作。
1.对于update,有with check option,要保证update后,数据要被视图查询出来
2.对于delete,有无with check option都一样
4.对于insert,有with check option,要保证insert后,数据要被视图查询出来
对于没有where 子句的视图,使用with check option是多余的
7、删除视图
DROP VIEW IF EXISTS 视图名