mysql视图之管理视图(增删改查)

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/luyaran/article/details/81019091

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 VIEWCREATE 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     | dmurphy@yiibai.com             |
| Mary      | Hill      | x4611     | mary.hill@yiibai.com           |
| Jeff      | Firrelli  | x9273     | jfirrelli@yiibai.com           |
| William   | Patterson | x4871     | wpatterson@yiibai.com          |
| Gerard    | Bondur    | x5408     | gbondur@gmail.com              |
| Anthony   | Bow       | x5428     | abow@gmail.com                 |
| Leslie    | Jennings  | x3291     | ljennings@yiibai.com           |
.............. 此处省略了many many数据 ..................................
| Martin    | Gerard    | x2312     | mgerard@gmail.com              |
| Lily      | Bush      | x9111     | lilybush@yiiibai.com           |
| John      | Minsu     | x9112     | johnminsu@classicmodelcars.com |
+-----------+-----------+-----------+--------------------------------+
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     | dmurphy@yiibai.com             | President            |
| Mary      | Hill      | x4611     | mary.hill@yiibai.com           | VP Sales             |
| Jeff      | Firrelli  | x9273     | jfirrelli@yiibai.com           | VP Marketing         |
...................  此处省略了一大波数据 ....................................................
| Yoshimi   | Kato      | x102      | ykato@gmail.com                | Sales Rep            |
| Martin    | Gerard    | x2312     | mgerard@gmail.com              | Sales Rep            |
| Lily      | Bush      | x9111     | lilybush@yiiibai.com           | IT Manager           |
| John      | Minsu     | x9112     | johnminsu@classicmodelcars.com | 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/文件夹获取其备份。

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

如果感觉不错的话,请多多点赞支持哦。。。

展开阅读全文

没有更多推荐了,返回首页