MYSQL学习与数据库综合实验(四)——视图实验

4.视图实验

4.1视图的定义

​ MySQL 视图(View)是一种虚拟存在的表,同真实表一样,视图也由列和行构成,但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表,并且还是在使用视图时动态生成的。数据库中只存放了视图的定义,并没有存放视图中的数据,这些数据都存放在定义视图查询所引用的真实表中。

​ 使用视图查询数据时,数据库会从真实表中取出对应的数据。因此,视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变,显示在视图中的数据也会发生改变。视图可以从原有的表上选取对用户有用的信息,那些对用户没用,或者用户没有权限了解的信息,都可以直接屏蔽掉,作用类似于筛选。这样做既使应用简单化,也保证了系统的安全。

4.2创建视图

4.2.1基本语法

可以使用 CREATE VIEW 语句来创建视图。

语法格式如下:

CREATE VIEW <视图名> AS <SELECT语句>

语法说明如下。

①<视图名>`:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。

②<SELECT语句>`:指定创建视图的 SELECT 语句,可用于查询多个基础表或源视图。

4.2.2对于创建视图中的 SELECT 语句的指定存在以下限制:

①用户除了拥有 CREATE VIEW 权限外,还具有操作中涉及的基础表和其他视图的相关权限。

②SELECT 语句不能引用系统或用户变量。

③SELECT 语句不能包含 FROM 子句中的子查询。

④SELECT 语句不能引用预处理语句参数。
视图定义中引用的表或视图必须存在。但是,创建完视图后,可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。

​ 视图定义中允许使用 ORDER BY 语句,但是若从特定视图进行选择,而该视图使用了自己的 ORDER BY 语句,则视图定义中的 ORDER BY 将被忽略。

​ 视图定义中不能引用 TEMPORARY 表(临时表),不能创建 TEMPORARY 视图。WITH CHECK OPTION 的意思是,修改视图时,检查插入的数据是否符合 WHERE 设置的条件。

4.3查看视图的字段信息

查看视图的字段信息与查看数据表的字段信息一样,都是使用 DESCRIBE 关键字来查看的。具体语法如下:

DESCRIBE 视图名;

或简写成:

DESC 视图名;

4.4修改视图

4.4.1基本语法

可以使用 ALTER VIEW 语句来对已有的视图进行修改。

语法格式如下:

ALTER VIEW <视图名> AS <SELECT语句>

语法说明如下:

①<视图名>`:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。

②<SELECT 语句>`:指定创建视图的 SELECT 语句,可用于查询多个基础表或源视图。

需要注意的是,对于 ALTER VIEW 语句的使用,需要用户具有针对视图的 CREATE VIEW 和 DROP 权限,以及由 SELECT 语句选择的每一列上的某些权限。

​ 修改视图的定义,除了可以通过 ALTER VIEW 外,也可以使用 DROP VIEW 语句先删除视图,再使用 CREATE VIEW 语句来实现。

4.4.2修改视图内容

·视图是一个虚拟表,实际的数据来自于基本表,所以通过插入、修改和删除操作更新视图中的数据,实质上是在更新视图所引用的基本表的数据。

注意:对视图的修改就是对基本表的修改,因此在修改时,要满足基本表的数据定义。

某些视图是可更新的。也就是说,可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图,视图中的行和基本表的行之间必须具有一对一的关系。

​ 还有一些特定的其他结构,这些结构会使得视图不可更新。更具体地讲,如果视图包含以下结构中的任何一种,它就是不可更新的:

①聚合函数 SUM()、MIN()、MAX()、COUNT() 等。

②DISTINCT 关键字。

③GROUP BY 子句。

④HAVING 子句。

⑤UNION 或 UNION ALL 运算符。

⑥位于选择列表中的子查询。

⑦FROM 子句中的不可更新视图或包含多个表。

⑧WHERE 子句中的子查询,引用 FROM 子句中的表。

⑨ALGORITHM 选项为 TEMPTABLE(使用临时表总会使视图成为不可更新的)的时候。

4.5删除视图

4.5.1基本语法

可以使用 DROP VIEW 语句来删除视图。

语法格式如下:

DROP VIEW <视图名1> [ , <视图名2>]

其中:<视图名>指定要删除的视图名。DROP VIEW 语句可以一次删除多个视图,但是必须在每个视图上拥有 DROP 权限。

4.6查看视图中的数据

查看视图中的数据可以使用

select  ... from  视图名 ...

语句来查询,和查询表中数据一样用法。比如:

select age from v_em where name = "李丽";
select v_em.employeeID,v_dp.name ,workYear   from v_dp JOIN v_em ON v_dp.employeeID = v_em.employeeID ;

4.7示例

5.7.1创建DBEM数据库上的视图v_dp,包含department的全部信息

create view v_dp as select  from department with check option;

在这里插入图片描述

4.7.2创建DBEM数据库上的视图v_em,包含员工号码、姓名和实际收入

create view v_em(employeeID,name,realIncome) as select employee.employeeID,employee.name,(salary.income -\**** ***\*salary.outcome ) as realIncome from  employee  join  salary on employee.employeeID = salary.employeeID;

在这里插入图片描述

4.7.3从v_em视图中查询姓名为“李丽”的员工的实际收入

 select realIncome from v_em where name = "李丽";

在这里插入图片描述

4.7.4.向v_dp视图中插入一行数据:6,广告部,推广产品。执行完之后分别查看视图v_dp和表department中发生的变化。

insert into v_dp(departmentID,departName,comment) values(6,"广告部","推广产品");

在这里插入图片描述

可以发现,当在视图v_dp中插入一条数据时,department中也插入了一条数据。

4.7.5尝试向v_em视图中插入一行数据,看看会发生什么情况

insert into v_em("100020","赵柳",8000);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘“100020”,“赵柳”,8000)’ at line 1

在这里插入图片描述

插入数据时发生了错误。

4.7.6修改视图v_em中号码为102208的雇员的姓名为“赵柳“

update v_em set name = "赵柳" where employeeID = "102208";

在这里插入图片描述

4.7.7删除视图v_dp中部门号为“2”的数据

delete from v_dp where departmentID = 2;

在这里插入图片描述
在这里插入图片描述

4.8详细的实验资料以及指导PPT请访问笔者GitHub地址:https://github.com/LJF2402901363/database_experiment.git

4.9本博客已经同步到个人博客,如有需要请移步:http://moyisuiying.com/index.php/experiment/mysqlexperiment/371.html

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页