mysql视图之管理视图实例详解【增删改查操作】

本文实例讲述了mysql视图之管理视图操作。分享给大家供大家参考，具体如下：

mysql提供了用于显示视图定义的show create view语句，我们来看下语法结构：

show create view [database_name].[view_ name];

要显示视图的定义，需要在show create view子句之后指定视图的名称，我们先来根据employees表创建一个简单的视图用来显示公司组织结构，完事在进行演示：

create view organization as
  select 
    concat(e.lastname, e.firstname) as employee,
    concat(m.lastname, m.firstname) as manager
  from
    employees as e
      inner join
    employees as m on m.employeenumber = e.reportsto
  order by manager;

从以上视图中查询数据，得到以下结果：

mysql> select * from organization;
+------------------+------------------+
| employee     | manager     |
+------------------+------------------+
| bondurloui    | bondurgerard   |
| castillopamela  | bondurgerard   |
| jonesbarry    | bondurgerard   |
| hernandezgerard | bondurgerard   |
.......此处省略了many many数据.......
| katoyoshimi   | nishimami    |
| kingtom     | pattersonwilliam |
| marshpeter    | pattersonwilliam |
| fixterandy    | pattersonwilliam |
+------------------+------------------+
24 rows in set

要显示视图的定义，请使用show create view语句如下：

show create view organization;

我们还可以使用任何纯文本编辑器(如记事本)显示视图的定义，以打开数据库文件夹中的视图定义文件。例如，要打开organization视图定义，可以在数据库文件夹下的data文件夹中找到你数据库文件夹，完事进入其中按着你视图名称找.frm文件。

我们再来通过alter view和create or replace view来尝试修改视图，先来看下alert view语法：

alter
 [algorithm = {merge | temptable | undefined}]
 view [database_name]. [view_name]
  as
 [select statement]

以下语句通过添加email列来演示如何修改organization视图：

alter view organization
 as 
 select concat(e.lastname,e.firstname) as employee,
     e.email as employeeemail,
     concat(m.lastname,m.firstname) as manager
 from employees as e
 inner join employees as m
  on m.employeenumber = e.reportsto
 order by manager;

要验证更改，可以从organization视图中查询数据，咱就不赘述了，完事来看下另一个语法结构：

create or replace view v_contacts as
  select 
    firstname, lastname, extension, email
  from
    employees;
-- 查询视图数据
select * from v_contacts;

我们要注意，在我们修改的时候，如果一个视图已经存在，mysql只会修改视图。如果视图不存在，mysql将创建一个新的视图。好啦，我们来看下上述sql执行的结果：

+-----------+-----------+-----------+--------------------------------+
| firstname | lastname | extension | email             |
+-----------+-----------+-----------+--------------------------------+
| diane   | murphy  | x5800   | [e-mail]       |
| mary   | hill   | x4611   | [e-mail]      |
| jeff   | firrelli | x9273   | [e-mail]      |
| william  | patterson | x4871   | [e-mail]     |
| gerard  | bondur  | x5408   | [e-mail]       |
| anthony  | bow    | x5428   | [e-mail]         |
| leslie  | jennings | x3291   | [e-mail]      |
.............. 此处省略了many many数据 ..................................
| martin  | gerard  | x2312   | [e-mail]       |
| lily   | bush   | x9111   | [e-mail]      |
| john   | minsu   | x9112   | [e-mail] |
+-----------+-----------+-----------+--------------------------------+
25 rows in set

假设我们要将职位(jobtitle)列添加到v_contacts视图中，只需使用以下语句：

create or replace view v_contacts as
  select 
    firstname, lastname, extension, email, jobtitle
  from
    employees;
-- 查询视图数据
select * from v_contacts;

执行上面查询语句后，可以看到添加一列数据：

+-----------+-----------+-----------+--------------------------------+----------------------+
| firstname | lastname | extension | email             | jobtitle       |
+-----------+-----------+-----------+--------------------------------+----------------------+
| diane   | murphy  | x5800   | [e-mail]       | president      |
| mary   | hill   | x4611   | [e-mail]      | vp sales       |
| jeff   | firrelli | x9273   | [e-mail]      | vp marketing     |
................... 此处省略了一大波数据 ....................................................
| yoshimi  | kato   | x102   | [e-mail]        | sales rep      |
| martin  | gerard  | x2312   | [e-mail]       | sales rep      |
| lily   | bush   | x9111   | [e-mail]      | it manager      |
| john   | minsu   | x9112   | [e-mail] | svp marketing    |
+-----------+-----------+-----------+--------------------------------+----------------------+
25 rows in set

完事我们来看使用drop view语句将视图删除，先来看下语法结构：

drop view [if exists] [database_name].[view_name]

上述sql中，if exists是语句的可选子句，它允许我们检查视图是否存在，用来避免删除不存在的视图的错误。完事我们来删除organization视图：

drop view if exists organization;

我们得注意下，每次修改或删除视图时，mysql会将视图定义文件备份到/database_name/arc/目录中。 如果我们意外修改或删除视图，可以从/database_name/arc/文件夹获取其备份。

好啦，本次记录就到这里了。