欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql视图之创建可更新视图的方法详解

程序员文章站 2023-11-14 18:31:34
本文实例讲述了mysql视图之创建可更新视图的方法。分享给大家供大家参考,具体如下: 我们知道,在mysql中,视图不仅是可查询的,而且是可更新的。这意味着我们可以使用insert或...

本文实例讲述了mysql视图之创建可更新视图的方法。分享给大家供大家参考,具体如下:

我们知道,在mysql中,视图不仅是可查询的,而且是可更新的。这意味着我们可以使用insert或update语句通过可更新视图插入或更新基表的行。 另外,我们还可以使用delete语句通过视图删除底层表的行。但是,要创建可更新视图,定义视图的select语句不能包含以下任何元素:

  • 聚合函数,如:min,max,sum,avg,count等。
  • distinct子句
  • group by子句
  • having子句
  • 左连接或外连接。
  • union或union all子句
  • select子句中的子查询或引用该表的where子句中的子查询出现在from子句中。
  • 引用from子句中的不可更新视图
  • 仅引用文字值
  • 对基表的任何列的多次引用

我们如果使用temptable算法创建视图,则无法更新视图,不过有时可以使用内部连接创建基于多个表的可更新视图。废话不多说,让我们先来看看如何创建一个可更新的视图。我们先来尝试基于offices表创建一个名为officeinfo的视图,它指的是offices表中的三列:officecode,phone 和 city:

create view officeinfo
 as
  select officecode, phone, city
  from offices;

接下来,使用以下语句从officeinfo视图中查询数据:

select
  *
from
  officeinfo;

执行上面查询语句,得到以下结果:

mysql> select * from officeinfo;
+------------+------------------+---------------+
| officecode | phone      | city     |
+------------+------------------+---------------+
| 1     | +1 650 219 4782 | san francisco |
| 2     | +1 215 837 0825 | boston    |
| 3     | +1 212 555 3000 | nyc      |
| 4     | +33 14 723 4404 | paris     |
| 5     | +86 33 224 5000 | beijing    |
| 6     | +61 2 9264 2451 | sydney    |
| 7     | +44 20 7877 2041 | london    |
+------------+------------------+---------------+
7 rows in set

然后,使用以下update语句通过officeinfo视图更改officecode的值为:4的办公室电话号码:

update officeinfo
set
  phone = '+86 089866668888'
where
  officecode = 4;

最后,验证更改结果,通过执行以下查询来查询officeinfo视图中的数据:

mysql> select
  *
from
  officeinfo
where
  officecode = 4;

+------------+------------------+-------+
| officecode | phone      | city |
+------------+------------------+-------+
| 4     | +86 089866668888 | paris |
+------------+------------------+-------+
1 row in set

完事我们可以通过从information_schema数据库中的views表查询is_updatable列来检查数据库中的视图是否可更新,比如,我们来查询luyaran数据库获取所有视图,并显示哪些视图是可更新的:

select
  table_name, is_updatable
from
  information_schema.views
where
  table_schema = 'luyaran';

执行上面查询语句,得到以下结果:

+------------------+--------------+
| table_name    | is_updatable |
+------------------+--------------+
| aboveavgproducts | yes     |
| bigsalesorder  | yes     |
| customerorders  | no      |
| officeinfo    | yes     |
| saleperorder   | no      |
+------------------+--------------+
5 rows in set

我们再来尝试通过视图删除行,首先,创建一个名为items的表,在items表中插入一些行,并创建一个查询包含价格大于700的项的视图:

use testdb;
-- create a new table named items
create table items (
  id int auto_increment primary key,
  name varchar(100) not null,
  price decimal(11 , 2 ) not null
);
-- insert data into the items table
insert into items(name,price)
values('laptop',700.56),('desktop',699.99),('ipad',700.50) ;
-- create a view based on items table
create view luxuryitems as
  select
    *
  from
    items
  where
    price > 700;
-- query data from the luxuryitems view
select
  *
from
  luxuryitems;

执行上面查询语句后,得到以下结果:

+----+--------+--------+
| id | name  | price |
+----+--------+--------+
| 1 | laptop | 700.56 |
| 3 | ipad  | 700.5 |
+----+--------+--------+
2 rows in set

完事使用delete语句来删除id为3的行:

delete from luxuryitems
where
  id = 3;

mysql返回一条消息,表示有1行受到影响:

query ok, 1 row affected

我们来再次通过视图检查数据:

mysql> select * from luxuryitems;
+----+--------+--------+
| id | name  | price |
+----+--------+--------+
| 1 | laptop | 700.56 |
+----+--------+--------+
1 row in set

我们还可以从基表items查询数据,以验证delete语句是否实际删除了该行:

mysql> select * from items;
+----+---------+--------+
| id | name  | price |
+----+---------+--------+
| 1 | laptop | 700.56 |
| 2 | desktop | 699.99 |
+----+---------+--------+
2 rows in set

我们可以看到,id为3的行在基表中被删除。

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