最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
mysql视图如何实现增删改查 mysql视图实现增删改查代码实例
时间:2022-06-29 09:01:43 编辑:袖梨 来源:一聚教程网
本篇文章小编给大家分享一下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 | [email protected] | | Mary | Hill | x4611 | [email protected] | | Jeff | Firrelli | x9273 | [email protected] | | William | Patterson | x4871 | [email protected] | | Gerard | Bondur | x5408 | [email protected] | | Anthony | Bow | x5428 | [email protected] | | Leslie | Jennings | x3291 | [email protected] | .............. 此处省略了many many数据 .................................. | Martin | Gerard | x2312 | [email protected] | | Lily | Bush | x9111 | [email protected] | | John | Minsu | x9112 | [email protected] | +-----------+-----------+-----------+--------------------------------+ 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 | [email protected] | President | | Mary | Hill | x4611 | [email protected] | VP Sales | | Jeff | Firrelli | x9273 | [email protected] | VP Marketing | ................... 此处省略了一大波数据 .................................................... | Yoshimi | Kato | x102 | [email protected] | Sales Rep | | Martin | Gerard | x2312 | [email protected] | Sales Rep | | Lily | Bush | x9111 | [email protected] | IT Manager | | John | Minsu | x9112 | [email protected] | 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/文件夹获取其备份。
相关文章
- 以闪亮之名黯影分解商店怎么样 11-02
- 以闪亮之名无烬之誓有什么更新 11-02
- 以闪亮之名无烬之誓一怎么玩 11-02
- 以闪亮之名瑰梦漫游怎么玩 以闪亮之名白玫夜曲套装活动介绍 11-02
- 以闪亮之名无烬之誓一怎么玩 以闪亮之名活动返场制作活动介绍 11-02
- 以闪亮之名无烬之誓有什么更新 无烬之誓版本更新内容介绍 11-02