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

MySQL/MariaDB表表达式(3):视图

程序员文章站 2023-11-01 18:35:04
本文目录:1.创建、修改视图2.关于视图中的order by3.视图算法merge、temptable4.删除、查看视图信息5.检查无效视图 视图是表表达式的一种,所以它也是虚拟表。对视图操作的时候会通过语句动态的从表中临时获取数据。 1.创建、修改视图 当使用or replace时,如果视图存在则 ......

本文目录:
1.创建、修改视图
2.关于视图中的order by
3.视图算法merge、temptable
4.删除、查看视图信息
5.检查无效视图

视图是表表达式的一种,所以它也是虚拟表。对视图操作的时候会通过语句动态的从表中临时获取数据。

1.创建、修改视图

CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW [IF NOT EXISTS] view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

ALTER 
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

当使用or replace时,如果视图存在则此语句相当于alter view,如果视图不存在,则等价于create view。

关于algorithm,后文详细说明。

with [local|cascaded] check option:它的对象是可更新视图(即merge算法的视图)。对于可更新视图,可给定WITH CHECK OPTION子句来防止插入或更新非法记录,除非作用在行上的select_statement中的WHERE子句为"true"。其中local表示只要满足本视图的筛选条件即可插入或更新,cascaded表示必须满足所有视图的筛选条件才可插入或更新。默认是with cascaded check option

例如,下面的语句定义了3个视图,其中后两个视图是以第一个视图作为基表创建的。在向view2和view3插入记录的时候,如果记录中字段a=10:由于view2默认使用的是cascaded选项,a=10不满足view1的条件,所以插入失败;而view3使用的是local选项,只需满足view3的条件即可,所以a=10满足条件,即可以成功插入。

create view view1 as select * from t where a<10;
create view view2 as select * from view1 where a>5;
create view view3 as select * from view1 where a>5 with local check option;

MySQL/MariaDB中视图创建后,列的定义是"已固化"状态。也就是说,如果视图定义语句中的select语句中使用了星号"*"表示所有列,在创建视图的时候会转化为对应的列名存储在视图定义语句中,所以如果基表中新增了列将不会被视图的SQL语句检索到。

例如:

create or replace view v_city
as
select * from world.city where id>200;

查看视图的定义语句:可以看到,select语句中的星号是替换为了对应的列名来表示的。

mysql> mysql> show create view v_city\G
*************************** 1. row ***************************
                View: v_city
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.100.%` SQL SECURITY DEFINER VIEW `v_city` AS select `city`.`ID` AS `ID`,`city`.`Name` AS `Name`,`city`.`CountryCode` AS `CountryCode`,`city`.`District` AS `District`,`city`.`Population` AS `Population` from `city` where (`city`.`ID` > 200)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

在MySQL/MariaDB中视图定义语句中的select部分中,from后面不能是子查询。在这一点上MySQL/MariaDB和其他类型的数据库有些不一样。如果在某种条件下,视图的定义语句from字句正好需要的是子查询,可以将这个子查询先定义成视图,再将视图放在from字句中。 更新视图时,实际上是转到对应的基表上进行更新。

2.关于视图中的order by子句

按照标准SQL的规则,在视图定义语句的select语句中不允许出现order by子句,除非使用了TOP(limit),但这时候的ORDER BY只是为top挑选满足数量的行。因为视图是表表达式的一种,既然是表表达式,它是一种表,尽管是虚拟表。而表是不允许有序的(在关系引擎看来表总是无序的,在优化器看来表可以有序)。 在SQL Server中,如果在视图定义语句中使用了order by但却没有使用top子句,则直接报错。

MySQL/MariaDB表表达式(3):视图

但在MySQL/MairaDB中的视图定义语句中允许使用order by(又是违反标准的行为)。它认为视图中的order by会在引用视图时直接作用于基表。如果在引用视图时也使用了order by子句,则视图引用语句中的order by覆盖视图定义语句中的order by。例如:

CREATE OR REPLACE VIEW my_view AS SELECT * FROM t ORDER BY id DESC ;
SELECT * FROM my_view ORDER BY id ASC;

3.视图算法merge、temptable

algorithm={undefined|merge|temptable}是视图选择算法。视图的算法会影响MySQL/MariaDB处理视图的方式:

  1. merge会将引用视图的语句与视图定义语句合并起来,使得视图定义的某一部分取代语句的对应部分。例如在引用视图时会将视图名替换成基表名,将查询涉及的列替换成基表中的列名等。
  2. temptable将视图的结果放入临时表中,然后使用该表的数据执行对应语句操作。
  3. undefined是让MySQL/MariaDB自己选择merge还是temptable,它更倾向于merge。这是未指定algorithm时的默认值。

例如,以下是merge的一个特殊例子,很能说明merge算法:

MariaDB [test]> create or replace table t
                (id int auto_increment,
                 name char(20),
                 age int,
                 primary key(id));
MariaDB [test]> insert into t(name,age) values
     ('chenyi',21),
     ('huanger',22),
     ('zhangsan',23),
     ('lisi',24),
     ('wangwu',25),
    ('zhaoliu',26);
MariaDB [test]> select * from t;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | chenyi   |   21 |
|  2 | huanger  |   22 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   24 |
|  5 | wangwu   |   25 |
|  6 | zhaoliu  |   26 |
+----+----------+------+ 
# 创建一个id<5的视图my_view
MariaDB [test]> create or replace algorithm=merge view my_view(vf1,vf2) as 
                select id,name from t where age<24;
MariaDB [test]> select * from my_view;
+-----+----------+
| vf1 | vf2      |
+-----+----------+
|   1 | chenyi   |
|   2 | huanger  |
|   3 | zhangsan |
+-----+----------+

返回的结果是3行记录。

由于是merge算法的视图,在引用视图(此处是查询操作)的时候,会将视图中的各项替换为基表t中的各项。包括:

  1. "*"号替换为vf1和vf2,它们又替换为t表中的id和name。
  2. from子句中的my_view替换为表t。
  3. 加上视图定义语句中的where子句。

因此,select * from my_view;在执行的时候,会转换为下面的查询语句:

select id,name from t where age<24;

如果查询my_view的时候,使用下面的语句:

MariaDB [test]> select * from my_view where vf1<2; 
+-----+--------+
| vf1 | vf2    |
+-----+--------+
|   1 | chenyi |
+-----+--------+

在执行的时候,该语句将替换为下面的语句:

select id,name from t where id<2 and age<24;

只有使用merge算法的时候,视图才是可更新视图,因为temptable算法操作的是填充到临时表中的数据,无法结合基表进行数据更新。

因为merge算法结合了基表,因此它有一些限制,出现了以下情况时不能使用merge算法:

  1. HAVING
  2. LIMIT
  3. GROUP BY
  4. DISTINCT
  5. UNION
  6. UNION ALL
  7. 使用了聚合函数,如MAX(), MIN(), SUM() or COUNT()
  8. 在select列表中有子查询
  9. 没有基表,因为可能引用的是纯值,例如create view va as select 2。

之所以有以上限制,是因为使用了它们之后,视图的结构和基表的机构不一致,无法和基表一一对应,也就无法作为可更新视图。

4.删除、查看视图

可以一次性删除多个视图。

DROP VIEW [IF EXISTS] view_name [, view_name] ...

MySQL/MariaDB中不存在show view status语句。可以使用show table status表和视图的状态信息,使用show tables显示出数据库中的表和视图。

SHOW TABLE STATUS LIKE  'v_city';

查看视图定义语句:

show create view view_name;

还可以从information_schema.views表中查看相关信息,但是要注意的是,在views表中视图名所在的字段称为table_name而不是view_name。如下:

select * from information_schema.views where table_name='view_name';

5.检查无效视图

在创建视图的时候,要求它的基表已存在,否则会报错。但是在视图创建成功后,视图的基表可能会删除掉,或者更新基表中的引用字段。这时视图就已经是无效视图。

如何检测这些无效视图?

可以先在information.schema中查找出有哪些视图,然后再使用check table语句检测。

例如:

check table my_view,my_view2

以下是无效视图检查结果:

MariaDB [test]> check table my_view\G
*************************** 1. row ***************************
   Table: test.my_view
      Op: check
Msg_type: Error
Msg_text: Table 'test.t' doesn't exist
*************************** 2. row ***************************
   Table: test.my_view
      Op: check
Msg_type: Error
Msg_text: View 'test.my_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
   Table: test.my_view
      Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.000 sec)

 

回到Linux系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到网站架构系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到数据库系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7586194.html
转载请注明出处:http://www.cnblogs.com/f-ck-need-u/p/8870908.html

注:若您觉得这篇文章还不错请点击右下角推荐,您的支持能激发作者更大的写作热情,非常感谢!