澳门金沙vip 4

深入解析MySQL视图VIEW

一. 什么是视图
视图是一种虚拟存在的表,行和列数据来自,定义视图的查询中使用的表,并且是在使用视图时动态生成的。优势有:
  简单:
使用视图的用户完全不需要关心后面对应的表的结构,关联条件,筛选条件。
  安全:使用视图的用户只能访问他们被允许查询的结果集。
  数据独立: 源表增加列对视图没有影响,源表修改列名,则通过修改视图
对应好源表的列名来解决,不会造成对访问者的影响。

  style=”font-size: 18px; font-family: "Microsoft YaHei"”> style=”font-size: 14pt; color: #008000″>阅读目录— style=”color: #ff6600″>深入解析MySQL视图

style=”font-size: 18px; font-family: "Microsoft YaHei"”>1、创建视图

style=”font-size: 18px; font-family: "Microsoft YaHei"”>2、 style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #33cccc”>查看视图

style=”font-size: 18px; font-family: "Microsoft YaHei"”>3、 style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #33cccc”>视图的更改

style=”color: #000000; font-family: "Microsoft YaHei"; font-size: 18px; line-height: 1.5; background-color: initial”>  1、create
or replace view

style=”color: #000000; font-family: "Microsoft YaHei"; font-size: 18px; line-height: 1.5; background-color: initial”>  2、 style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #000000″>alter

style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #000000″>  3、 style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #000000″>DML

style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #000000″>  4、 style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #000000″>drop

style=”font-size: 18px; font-family: "Microsoft YaHei"”>4、 style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #33cccc”>使用with check
option约束

style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #33cccc”>   style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #33cccc”>嵌套视图

style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #33cccc”> style=”color: #000000″>5、定义视图时的其他选项: style=”color: #000000″>algorithm、definer、SQL
security视图权限

style=”font-size: 18px; font-family: "Microsoft YaHei"”>6、 style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #33cccc”>视图查询语句的处理

style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #33cccc”>   style=”color: #000000″>1、 style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #000000″>替代方法

style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #33cccc”> style=”color: #000000″>  2、 style=”font-size: 18px; font-family: "Microsoft YaHei"; color: #000000″>具体化方法

什么是视图

视图(View)是一种虚拟存在的表,对于使用视图的用户来说基本上是透明的。视图并
不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时
动态生成的

视图相对于普通的表的优势主要包括以下几项:

  • 简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集
  • 安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现
  • 数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响

1.1 创建视图的操作

Q:什么是视图?视图是干什么用的?

创建或者修改视图

创建视图需要有 CREATE VIEW 的权限,并且对于查询涉及的列有 SELECT
权限。如果使用 CREATE OR REPLACE 或者 ALTER
修改视图,那么还需要该视图的 DROP 权限

创建视图的语法为:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 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]

视图的可更新性和视图中查询的定义有关系,以下类型的视图是不可更新的

  • 包含以下关键字的 SQL 语句:聚合函数(SUM、MIN、MAX、COUNT
    等)、DISTINCT、GROUP BY、HAVING、UNION 或者 UNION ALL
  • 常量视图
  • SELECT 中包含子查询
  • JOIN
  • FROM 一个不能更新的视图
  • WHERE 字句的子查询引用了 FROM 字句中的表

WITH [CASCADED | LOCAL] CHECK OPTION
决定了是否允许更新数据使记录不再满足视图的条件。这个选项与 Oracle
数据库中的选项是类似的,其中:

  • 澳门金沙vip,LOCAL 是只要满足本视图的条件就可以更新;
  • CASCADED 则是必须满足所有针对该视图的所有视图的条件才可以更新

如果没有明确是 LOCAL 还是 CASCADED,则默认是 CASCADED

 -- 创建视图
CREATE  OR REPLACE VIEW view_city
  AS 
  SELECT * FROM city;
 -- 查询视图
 SELECT * FROM view_city;

A:

删除视图

用户可以一次删除一个或者多个视图,前提是必须有该视图的 DROP 权限

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

澳门金沙vip 1

  视图(view)是一种虚拟存在的表,是一个逻辑表,本身并不包含数据。作为一个select语句保存在数据字典中的。

查看视图

  • show tables;
  • show table status like '***';
  • show create view ***;
  • information_schema.views

1.2 修改视图

  通过视图,可以展现基表的部分数据;视图数据来自定义视图的查询中使用的表,使用视图动态生成。

-- 修改视图
ALTER VIEW view_city
 AS 
 SELECT  cityname FROM city;

基表:用来创建视图的表叫做基表base
table

澳门金沙vip 2

Q:为什么要使用视图?

1.3 限制 

A:因为视图的诸多优点,如下

视图一般只是用来做查询使用,如果要对视图做修改有如下限制不能更新:
包含关键字 聚合函数(sum,min,max,count等),distinct,group by ,
having,union ,union all。
常量视图。
select 中包含子查询。
jion。
from 一个不能更新的视图。
where 字句的子查询引用了from字句的表。

  1)简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。

   LOCAL 是只要满足本视图的条件就可以更新, CASCADED
则是必须满足所有针对该视图的所有视图的条件才可以更新,
如果没有明确是LOCAL
还是CASCADED,则默认是CASCADED,由于视图基本不做修改,这里就不在演示。

  2)安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。

 -- local与CASCADED 语法
  CREATE  OR REPLACE VIEW view_city
  AS 
  SELECT * FROM city;
  -- with(local | CASCADED)   CHECK OPTION;

  3)数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。

1.4  删除视图

总而言之,使用视图的大部分情况是为了保障数据安全性,提高查询效率。

drop view view_city;

 

1.5 查看视图

一、创建视图

--  查看视图的定义   
SHOW CREATE VIEW view_city
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

澳门金沙vip 3

1)OR REPLACE:表示替换已有视图

-- 查看视图的定义
SELECT * FROM information_schema.views WHERE table_name = 'view_city'

2)ALGORITHM:表示视图选择算法,默认算法是UNDEFINED(未定义的):MySQL自动选择要使用的算法
;merge合并;temptable临时表

澳门金沙vip 4

3)select_statement:表示select语句

 

4)[WITH [CASCADED | LOCAL] CHECK
OPTION]:表示视图在更新时保证在视图的权限范围之内

  cascade是默认值,表示更新视图的时候,要满足视图和表的相关条件

  local表示更新视图的时候,要满足该视图定义的一个条件即可

TIPS:推荐使用WHIT [CASCADED|LOCAL] CHECK
OPTION选项,可以保证数据的安全性 

基本格式:

  create
view <视图名称>[(column_list)]

     
 as select语句

     
 with check option;

1、在单表上创建视图

mysql> create view v_F_players(编号,名字,性别,电话)
    -> as
    -> select PLAYERNO,NAME,SEX,PHONENO from PLAYERS
    -> where SEX='F'
    -> with check option;
Query OK, 0 rows affected (0.00 sec)

mysql> desc v_F_players;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 编号    | int(11)  | NO   |     | NULL    |       |
| 名字    | char(15) | NO   |     | NULL    |       |
| 性别    | char(1)  | NO   |     | NULL    |       |
| 电话    | char(13) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from  v_F_players;
+--------+-----------+--------+------------+
| 编号    | 名字      | 性别    | 电话        |
+--------+-----------+--------+------------+
|      8 | Newcastle | F      | 070-458458 |
|     27 | Collins   | F      | 079-234857 |
|     28 | Collins   | F      | 010-659599 |
|    104 | Moorman   | F      | 079-987571 |
|    112 | Bailey    | F      | 010-548745 |
+--------+-----------+--------+------------+
5 rows in set (0.02 sec)

2、在多表上创建视图

mysql> create view v_match
    -> as 
    -> select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION
    -> from 
    -> PLAYERS a,MATCHES b,TEAMS c
    -> where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v_match;
+----------+-----------+---------+-----+------+--------+----------+
| PLAYERNO | NAME      | MATCHNO | WON | LOST | TEAMNO | DIVISION |
+----------+-----------+---------+-----+------+--------+----------+
|        6 | Parmenter |       1 |   3 |    1 |      1 | first    |
|       44 | Baker     |       4 |   3 |    2 |      1 | first    |
|       83 | Hope      |       5 |   0 |    3 |      1 | first    |
|      112 | Bailey    |      12 |   1 |    3 |      2 | second   |
|        8 | Newcastle |      13 |   0 |    3 |      2 | second   |
+----------+-----------+---------+-----+------+--------+----------+
5 rows in set (0.04 sec)

视图将我们不需要的数据过滤掉,将相关的列名用我们自定义的列名替换。视图作为一个访问接口,不管基表的表结构和表名有多复杂。

   如果创建视图时不明确指定视图的列名,那么列名就和定义视图的select子句中的列名完全相同;

  如果显式的指定视图的列名就按照指定的列名。

注意:显示指定视图列名,要求视图名后面的列的数量必须匹配select子句中的列的数量。

【回到顶部】

二、查看视图

1、使用show
create view语句查看视图信息

mysql> show create view v_F_players\G;
*************************** 1. row ***************************
                View: v_F_players
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_F_players` AS select `PLAYERS`.`PLAYERNO` AS `编号`,`PLAYERS`.`NAME` AS `名字`,`PLAYERS`.`SEX` AS `性别`,`PLAYERS`.`PHONENO` AS `电话` from `PLAYERS` where (`PLAYERS`.`SEX` = 'F') WITH CASCADED CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

2、视图一旦创建完毕,就可以像一个普通表那样使用,视图主要用来查询

mysql>
select * from view_name;

3、有关视图的信息记录在information_schema数据库中的views表中

mysql> select * from information_schema.views 
    -> where TABLE_NAME='v_F_players'\G;
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: TENNIS
          TABLE_NAME: v_F_players
     VIEW_DEFINITION: select `TENNIS`.`PLAYERS`.`PLAYERNO` AS `编号`,`TENNIS`.`PLAYERS`.`NAME` AS `名字`,`TENNIS`.`PLAYERS`.`SEX` AS `性别`,`TENNIS`.`PLAYERS`.`PHONENO` AS `电话` from `TENNIS`.`PLAYERS` where (`TENNIS`.`PLAYERS`.`SEX` = 'F')
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
1 row in set (0.00 sec)        

【回到顶部】