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

本文实例讲述了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的行在基表中被删除。

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

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》

希望本文所述对大家MySQL数据库计有所帮助。

(0)

相关推荐

  • 基于mysql事务、视图、存储过程、触发器的应用分析

    一 ,mysql事务 MYSQL中只有INNODB类型的数据表才能支持事务处理. 启动事务有两种方法 (1) 用begin,rollback,commit来实现 复制代码 代码如下: begin 开始一个事务rollback   事务回滚commit    事务确认 (2)直接用set来改变mysql的自动提交模式 复制代码 代码如下: set autocommit=0 禁止自动提交set autocommit=1 开启自动提交 demo 复制代码 代码如下: header("Content-t

  • mysql视图功能与用法实例分析

    本文实例讲述了mysql视图功能与用法.分享给大家供大家参考,具体如下: 通俗的讲,视图就是一条SELECT语句执行后返回的结果集. //单表视图 CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名 [(属性清单)] AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION]; //将查询到的结果返回为一个虚拟表,会根据数据库变化而变化 CREATE VIEW work_view(ID, Nam

  • MySQL中视图的使用及多表INNER JOIN的技巧分享

    创建视图   Sql代码 CREATE VIEW view_name AS SELECT t1.xxx, t2.xxx, t3.xxx FROM (table1 t1 INNER JOIN table2 t2 ON t1.fid = t2.fid) INNER JOIN table3 t3 ON t1.mid = t3.mid; 这里使用了3表关联,对于多表关联的 INNER JOIN 写法有一个技巧 1. 先写最简单的2表关联 INNER JOIN 2. 然后使用 () 从 FROM 之后到语

  • MySQL中索引与视图的用法与区别详解

    前言 本文主要给大家介绍了关于MySQL中索引与视图的使用与区别的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧. 索引 一.概述 所有的Mysql列类型都可以被索引. mysql支持BTREE索引.HASH索引.前缀索引.全文本索引(FULLTEXT)[只有MyISAM引擎支持,且仅限于char,varchar,text列].空间列索引[只有MyISAM引擎支持,且索引的字段必须非空],但不支持函数索引. MyISAM和InnoDB存储引擎的表默认创建BTREE索引,

  • MySQL视图原理与基本操作示例

    本文实例讲述了MySQL视图原理与基本操作.分享给大家供大家参考,具体如下: 概述 视图是一个虚拟表,其内容由查询定义.同真实的表一样,视图包含一系列带有名称的列和行数据.但是视图并不在数据库中以存储的数据值集形式存在.行和列数据来自由定义视图的查询所引用的表,并在引用视图时动态生成. 对其中所引用的基础表来说,视图的作用类似于筛选. 基本操作 建立视图 CREATE VIEW view_test(qty,price,total) AS SELECT quantity,price,quantit

  • mysql视图原理与用法实例小结

    本文实例总结了mysql视图原理与用法.分享给大家供大家参考,具体如下: 一.什么是视图 视图是指计算机数据库中的视图,是一个虚拟表,其内容由查询定义.同真实的表一样,视图包含一系列带有名称的列和行数据.但是,视图并不在数据库中以存储的数据值集形式存在.行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成.简单的来说视图是由其定义结果组成的表: 例子:定一班级表class(ID,name) 学生表 student(id,class_id,name): 当数据表结构很复杂,但我们只关

  • MySQL在多表上创建视图方法

    MySQL中,在两个或者以上的基本表上创建视图 在student表和stu_info表上,创建stu_class视图,查询出s_id号.姓名和班级 首先,创建stu_info表,并向表中插入数据 查看表中的数据 创建stu_class视图 查看视图 可以看出,创建的视图包含id.name和class字段 其中,id字段对应student表中的s_id字段,name字段对应student表中的name字段,class字段对应stu_info表中的class字段

  • MySQL视图简介及基本操作教程

    前言 视图是数据库系统中一种非常有用的数据库对象.MySQL 5.0 之后的版本添加了对视图的支持. 认识视图 视图是一个虚拟表,其内容由查询定义.同真实表一样,视图包含一系列带有名称的列和行数据,但视图并不是数据库真实存储的数据表. 视图是从一个.多个表或者视图中导出的表,包含一系列带有名称的数据列和若干条数据行. 视图并不同于数据表,它们的区别在于以下几点: 视图不是数据库中真实的表,而是一张虚拟表,其结构和数据是建立在对数据中真实表的查询基础上的. 存储在数据库中的查询操作 SQL 语句定

  • Mysql数据库高级用法之视图、事务、索引、自连接、用户管理实例分析

    本文实例讲述了Mysql数据库高级用法之视图.事务.索引.自连接.用户管理.分享给大家供大家参考,具体如下: 视图 视图是对若干张基本表的引用,一张虚表,只查询语句执行结果的字段类型和约束,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变),方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性. 1.----创建视图: create view 视图名称(一般以v_开头) as 查询语句; 2.----查看视图: select * from 视图名称; 3.----删除视图: dro

  • MySQL笔记之视图的使用详解

    什么是视图 视图是从一个或多个表中导出来的表,是一种虚拟存在的表. 视图就像一个窗口,通过这个窗口可以看到系统专门提供的数据. 这样,用户可以不用看到整个数据库中的数据,而之关心对自己有用的数据. 数据库中只存放了视图的定义,而没有存放视图中的数据,这些数据存放在原来的表中. 使用视图查询数据时,数据库系统会从原来的表中取出对应的数据. 视图中的数据依赖于原来表中的数据,一旦表中数据发生改变,显示在视图中的数据也会发生改变. 视图的作用 1.使操作简单化,可以对经常使用的查询定义一个视图,使用户

  • Mysql事项,视图,函数,触发器命令(详解)

    事项开启和使用 //修改表的引擎 alter table a engine=myisam; //开启事务 begin; //关闭自动提交 set autocommit=0; //扣100 update bank set money=money-100 where bid=1; //回滚,begin开始的所有sql语句操作 rollback; //开启事务 begin; //关闭自动提交 set autocommit=0; //扣100 update bank set money=money-10

随机推荐