12.视图

目录

 1.视图的含义与作用

2.视图的创建与查看

1.创建视图的语法形式

2、查看视图：

1.使用DESCRIBE语句查看视图基本信息

2.使用SHOW TABLE STATUS语查看视图基本信息查看视图的信息

3.使用SHOW CREATE VIEW语查看视图详细信息

4.在views表中查看视图详细信息

3.视图的修改与更新

1.使用CREATE OR REPLACE VIEW语句修改视图MYSQL中如果要修改视图

2.使用ALTER语修改视图

1、使用UPDATE语句更新视图view_t，

2、使用INSERT语句在基本表t中插入一条记录

3、使用DELETE语删除视图view_t2中的一条记录，

4.删除视图：

5.练习题

---

 1.视图的含义与作用

数据库中的视图是一个虚拟表。同真实的表一样,视图包含一系列带有名称的行和列数据。

行和列数据来自由定义视图查询所引用的表，并且在引用视图时动态生成。

在视图中用户可以使用SELECT语查询数据，以及使用INSERT、UPDATE和DELETE修改记录。

从MySOL 5.0开始可以使用视图，视图可以使用户操作方便，而且可以保障数据库系统的安全。

视图的含义：

视图一经定义便存储在数据库中,与其相对应的数据并没有像表那样在数据库中再存储一份，

通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以

对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化;同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

下面有个student表和stu_info表，在student 表中包含了学生的id 号和姓名，stu_info

表中包含了学生的id 号、班级和家庭住址，而现在公布分班信息，只需要id 号、姓名和班

级，这该如何解决?通过学习后面的内容就可以找到完美的解决方案。

表设计如下:

CREATE TABLE student 
( 
 s_id INT, 
 name VARCHAR(40) 
); 
CREATE TABLE stu_info 
( 
 s_id INT, 
glass VARCHAR(40), 
 addr VARCHAR(90) 
); 

通过 DESC 命令可以查看表的设计，可以获得字段、字段的定义、是否为主键、是否

为默认值和扩展信息。

视图提供了一个很好的解决方法，创建视图的信息来自表的部分信息，只取需要的信息。

这样既能满足要求也不破坏表原来的结构。

视图的作用：

与直接从数据表中读取相比，视图有以下优点:

1.简单化

看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件

2.安全性

通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见

也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，

但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同

子集上:

(1)使用权限可被限制在基表的行的子集上。

(2)使用权限可被限制在基表的列的子集上。

(3)使用权限可被限制在基表的行和列的子集上。

(4)使用权限可被限制在多个基表的连接所限定的行上。

(5)使用权限可被限制在基表中的数据的统计汇总上。

(6)使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上

3.逻辑数据独立性

视图可帮助用户屏蔽真实表结构变化带来的影响。

2.视图的创建与查看

1.创建视图的语法形式

创建视图使用CREATE VIEW语句，基本语法格式如下:

CREATE [OR REPLACE] [ALGORITHM = { UNDEFINED | MERGE | TEMPTABLEI]

VIEW view_name [ (column list)]

AS SELECT_statement

[ WITH [CASCADED | LOCAL ] CHECK OPTION]

其中，CREATE表示创建新的视图，REPLACE表示替换已经创建的视图:

ALGORITHM表示视图选择的算法;view_name 为视图的名称，

column_list 为属性列;SELECT_statement表示SELECT语句

WITH [CASCADED | LOCAL] CHECK OPTION 参数表示视图在更新时保证在视图的权限

范围之内。

ALGORITHM的取值有3个分别是UNDEFINED|MERGE | TEMPTABLE ,UNDEFINED表示

MySOL将 自动选择算法:MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某 一部分取代语句对应的部分;TEMPTABLE 表示将视图的结果存入临时表，然后用临时表来执行语句。

CASCADED与LOCAL 为可选参数，CASCADED 为默认值，表示更新视图时要满足所有

相关视图和表的条件;

LOCAL表示更新视图时满足该视图本身定义的条件即可。该语句要求具有针对视图的

CREATE VIEW权限，以及针对由SELECT语选择的每一列上的某些权限。对于在SELECT 语句中其他地方使用的列，必须具有SELECT 权限。如果还有ORREPLACE子句，必须在视图上具有DROP权限。

视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，

创建时应将名称指定为db_name.view_name。

在单表上创建视图,MySQL可以在单个数据表上创建视图.

举例：

在t表格上创建一个名为view_t的视图，代码如下：

create table t (qty int, price int); /*创建基本表t*/ 
insert into t values(3, 50); /*插入记录*/ 
create view view_t as select qty, price, qty *price from t; /*创建视图view_t*/ 
select * from view_t; 

默认情况下创建的视图和基本表的字段是一样的 也可以通过指定视图字段的名称来创建视图。

举例2：在t表格上创建一个名为view_t2的视图，代码如下：

create view view_t2(qty, price, total ) as select qty, price, qty *price from t; 
select * from view_t2; 

可以看到，view_t2和view_t两个视图中字段名称不同，但数据却是相同的。

因此，在使用视图的时候，可能用户根本就不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。

在多表上创建视图 MySOL中也可以在两个或者两个以上的表上创建视图可以使用CREATE VIEW语句实 现.

举例：

在表student和表stu_info上创建视图stu_glass，代码如下：

create table student(
id int,
name char(50)
);

create table stu_info(
id int,
glass char(50),
place char(50)
);

insert into student values(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai');
insert into stu_info values(1, 'wuban','henan'),(2,'liuban','hebei'), (3,'qiban','shandong');                
create view stu_glass (id,name, glass) 
as select student.id,student.name,stu_info.glass from student,stu_info 
where student.id=stu_info.id; 
select * from stu_glass; 

这个例子就解决了刚开始提出的那个问题，通过这个视图可以很好地保护基本表中的

数据。

这个视图中的信息很简单，只包含了id、姓名和班级，id 字段对应student 表中的s_id

字段，

name字段对应student 表中的name 字段，glass字段对应stu_info表中的glass 字段。

2、查看视图：

查看视图是查看数据库中已存在的视图的定义。查看视图必须要有 SHOW VIEW的权

限MySQL

数据库下的 user 表中保存着这个信息。查看视图的方法包括:DESCRIBE、SHOW

TABLE STATUS和SHOW CREATE VIEW，本节将介绍查看视图的各种方法。

1.使用DESCRIBE语句查看视图基本信息

DESCRIBE可以用来查看视图，具体的语法如下:

DESCRIBE 视图名;

举例：通过DESCRIBE语句查看视图view_t的定义，代码如下：

describe view_t; 

结果显示出了视图的字段定义、字段的数据类型、是否为空、是否为主/外键、默认值

和额外信息。

DESCRIBE一般情况下都简写成DESC，输入这个命的执行结果和输入DESCRIBE的执行

结果是一样的。

2.使用SHOW TABLE STATUS语查看视图基本信息查看视图的信息

可以通过SHOW TABLE STATUS 的方法，具体的语法如下:

SHOW TABLE STATUS LIKE ‘视图名’

下面将通过一个例子来学习使用SHOW TABLE STATUS命令查看视图信息，代码如下：

show table status like 'view_t' \G;

show table status like 't' \G;

执行结果显示，表的说明Comment 的值为 VIEW说明该表为视图，其他的信息为NULI

说明这是一个虚表。

用同样的语句来查看一下数据表t的信息，从查询的结果来看，这里的信息包含了存储引擎、创建时间等，

Comment 信息为空，这就是视图和表的区别。

3.使用SHOW CREATE VIEW语查看视图详细信息

使用SHOW CREATE VIEW语可以查看视图详细定义，语法如下:

SHOW CREATE VIEW 视图名 ;

举例：SHOW CREATE VIEW查看视图的详细定义，代码如下：

show create view view_t \G;

执行结果显示视图的名称、创建视图的语句等信息。

4.在views表中查看视图详细信息

在MySOL中，information_schema数据库下的views 表中存储了所有视图的定义。

通过对 views表的查询，可以查看数据库中所有视图的详细信息，查询语句如下:

select * from information_schema.views \G; 

查询的结果显示当前以及定义的所有视图的详细信息，在这里也可以看到前面定义的

3个名称为stuglass、viewt和view t2视图的详细信息。

3.视图的修改与更新

修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，

可以通过修改视图来保持与基本表的一致性。

MySOL 中通过CREATE OR REPLACE VIEW语和ALTER语句来修改视图。

1.使用CREATE OR REPLACE VIEW语句修改视图MYSQL中如果要修改视图

语法如下 ：

CREATE [ OR REPLACE ] (ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS SELECT_statement

[WITH [CASCADED | LOCAL ] CHECK OPTION ]

可以看到，修改视图的语句和创建视图的语句是完全一样的。当视图已经存在时，

修改语句对视图进行修改;当视图不存在时，创建视图。下面通过一个实例来说明。

修改视图view_t，代码如下：

desc view_t;
create or replace view view_t as select * from t;
desc view_t;

从执行的结果来看。相比原来的视图 view_t，新的视图 view_t少了1行数据。

2.使用ALTER语修改视图

ALTER语句是MySOL提供的另外一种修改视图的方法，语法如下:

ALTER [ ALGORITHM = { UNDEFINED | MERGE | EPTABLE}]

TVIEW view_name[(column list)]

AS SELECT_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

这个语法中的关键字和前面视图的关键字是一样的，这里就不再介绍

具体操作举例：

使用ALTER语句修改视图view_t，代码如下：

desc view_t;
alter view view_t as select quantity from t;
desc view_t;

通过ALTER语句同样可以达到修改视图 view_t的目的，

从上面的执行过程来看，视图viewt只剩下1个qty字段，修改成功。

更新视图：

更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，

其中没有数据。通过视图更新的时候都是转到基本表上进行更新的，

如果对视图增加或者删除记录,实际上是对其基本表增加或者删除记录。

本节将介绍视图更新的3种方法:INSERT UPDATE和 DELETE。

1、使用UPDATE语句更新视图view_t，

代码如下：

select * from view_t; /*查看更新之前的视图*/ 
select * from t; /*查看更新之前的表*/ 
update view_t set qty=5; /*更新视图*/ 
select * from t; /*查看更新之后的表*/ 
select * from view_t; /*查看更新之后的视图*/ 
select * from view_t2; 

对视图view_t更新后，基本表t的内容也更新了，

同样当对基本表t更新后，另外一个视图view_t2中的内容也会更新。

2、使用INSERT语句在基本表t中插入一条记录

代码如下：

insert into t values(3,5);
select * from t;
select * from view_t2; 

向表t中插入一条记录，通过SELECT 查看表t和视图 view_t2，可以看到其中的内容也

跟着更新，

视图更新的不仅仅是数量和单价，总价也会更新。

3、使用DELETE语删除视图view_t2中的一条记录，

代码如下

delete from view_t2 where price=5;

DELETE FROM view_t2 WHERE price=5;

执行结果如下:

在视图view_t2中删除price=5的记录，视图中的删除操作最终是通过删除基本表中相

关的记录实现的，

查看删除操作之后的表 t和视图 view_t2，可以看到通过视图删除其所依赖的基本表中

的数据。

当视图中包含有如下内容时，视图的更新操作将不能被执行:

(1)视图中不包含基表中被定义为非空的列。

(2)在定义视图的SELECT语后的字段列表中使用了数学表达式。

(3)在定义视图的SELECT语句后的字段列表中使用聚合函数

(4)在定义视图的SELECT 语中使用了 DISTINCT，UNION，TOP，GROUP BY HAVING子句。

4.删除视图：

当视图不再需要时，可以将其删除，删除一个或多个视图可以使用 DROP VIEW 语

句，语法如下:

DROP VIEW [IF EXISTS]

view_name[，view_name]

[RESTRICT | CASCADE]

其中，view_name是要删除的视图名称，可以添加多个需要删除的视图名称，

各个名称之间使用逗号分隔开。删除视图必须拥有DROP权限。

restrict和casecade都是数据库外键约束，但它们之间存在一定的区别：

restrict是不允许删除或更新一条记录，而casecade则是在删除或更新一条记录时，

会同时删除或更新所有引用该记录的外键。

举例：删除stu_glass视图，代码如下：

drop view if exists stu_glass;
show create view stu_glass;

5.练习题

注意事项：

疑问:MySQL中视图和表的区别以及联系是什么?

1.两者的区别

(1)视图是已经编译好的SOL语句，是基于SOL语句的结果集的可视化的表，而表不是

(2)视图没有实际的物理记录，而表有。

(3)表是内容，视图是窗口。

(4)表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改。

(5)视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL语句的集合。从安全的角度来说，视图可以防止用户接触数据表。

(6)表属于全局模式中的表，是实表:视图属于局部模式的表，是虚表。

(7)视图的建立和删除只影响视图本身，不影响对应的基本表。

2.两者的联系

视图(view)是在基本表之上建立的表，它的结构(即所定的列)和内容(即所有记录)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

练习题：

(1)如何在一个表上创建视图?

create table ts (qty int, price int); /*创建基本表ts*/
insert into ts values(4, 32); /*插入记录*/
create view view_ts as select qty,price,qty*price from ts;/*创建视图view_ts*/
select * from  view_ts; /*查看视图 作用是 减少输入操作*/

默认情况下创建的视图和基本表的字段是一样的 也可以通过指定视图字段的名称来创

建视图。

create view view_ts1(qty,price,total) as select qty,price,qty * price from ts;

select * from  view_ts1;

(2)如何在多个表上建立视图?

create table student(
id int,
name char(50)
);

create table stu_info(
id int,
glass char(50),
place char(50)
);

insert into student values(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai');
insert into stu_info values(1, 'wuban','henan'),(2,'liuban','hebei'), (3,'qiban','shandong');                
create view stu_glass (id,name, glass) 
as select student.id,student.name,stu_info.glass from student,stu_info 
where student.id=stu_info.id; 
select * from stu_glass; 

(3)如何更改视图?

方法一

create or replace view stu_v_1 as select id,name,age from student where id <= 10;

方法二

alter view stu_v_1 select id,name from student  ;

(4)如何去查看视图的详细信息?

1.使用DESCRIBE语句查看视图基本信息

describe view_t; 

2.使用SHOW TABLE STATUS语查看视图基本信息查看视图的信息

show table status like 'view_t' \G;

3.使用SHOW CREATE VIEW语查看视图详细信息

show create view view_t \G;

4.在views表中查看视图详细信息

select * from information_schema.views \G; 

(5)如何更新视图的内容?

select * from view_t; /*查看更新之前的视图*/ 
select * from t; /*查看更新之前的表*/ 
update view_t set qty=5; /*更新视图*/ 
select * from t; /*查看更新之后的表*/ 
select * from view_t; /*查看更新之后的视图*/ 
select * from view_t2; 

表结构

/*员工人事表*/
create table employee (
 emp_no char(5) Not null primary key check (emp_no LIKE 'E%' AND LEN(emp_no)=5),
 emp_name varchar(10) Not null,
 sex char(2) Not null check(sex IN('M','F')),
 dept varchar(4)	Not null,
 title varchar(6) Not null,
 date_hired datetime Not null,
 birthday datetime Null,
 salary	int	 Not null,
 addr varchar(50) null
);
/*客户表*/
create table customer(
 cust_id char(5) Not null primary key,
 cust_name varchar(20) Not null,	
 addr varchar(40) Not null,	
 tel_no varchar(10) Not null,	
 zip char(6) null
);

/*销售主表*/
create table sales( 
 order_no int Not null primary key,
 cust_id char(5) Not null,
 sale_id char(5) Not null,
 tot_amt numeric(9,2) Not null,	
 order_date datetime Not null,
 ship_date datetime Not null,
 invoice_no char(10) UNIQUE
);

/*销货明细表*/
create table sale_item(
 order_no int Not null,
 prod_id char(5) Not null,
 qty int Not null,
 unit_price numeric(7,2) Not null,
 order_date datetime null
);
alter table sale_item add primary key(order_no,prod_id); 

/*产品名称表*/
create table product(
 prod_id char(5) Not null primary key,
 prod_name varchar(20) Not null	
);

1、利用存储过程，给Employee表添加一条业务部门员工的信息。

2、利用存储过程输出所有客户姓名、客户订购金额及其相应业务员的姓名。

3、利用存储过程查找某员工的员工编号、订单编号、销售金额。

4、编写存储过程完成插入一条员工记录，判断员工编号是否存在。