MySQL View(视图)从0学起，以及实例

kider

作者:海底苍鹰
地址:http://blog.51yip.com/mysql/1062.html

一，什么视图

视图是存放数据的一个接口，也可以说是虚拟的表。这些数据可以是从一个或几个基本表（或视图）的数据。也可以是用户自已定义的数据。其实视图里面不存放数据的，数据还是放在基本表里面，基本表里面的数据发生变动时，视图里面的数据随之变动。

二，视图有什么用

1，视图可以让查询变得很清楚
如果您要找的数据存放在三张关系表里面，查看数据的时候，你就要写个联合查询了。换种方法，我把联合查询的数据放到视图里面，这样查询起来是不是更方便呢，通phpmyadmin看着是不是更清淅呢。

2，保护数据库的重要数据，给不同的人看不同的数据
假如您让别人帮您开发一套系统，但是你又想把真正表的暴露出来，这个时候视图是不是最好的选择呢。

三，视图的类型

mysql的视图有三种类型：MERGE、TEMPTABLE、UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。


      1，MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
      2，TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。
      3，UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

四，添加视图

1，添加规则

1. CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
   
2.   
   
3. VIEW view_name [(column_list)]  
   
4.   
   
5. AS select_statement  
   
6.   
   
7. [WITH [CASCADED | LOCAL] CHECK OPTION]  

复制代码

2，实例

1. mysql> use test;  
   
2. Database changed  
   
3. mysql> create  algorithm = MERGE view aaa as select * from user;//创建一个视图  
   
4. Query OK, 0 rows affected (0.00 sec)  

复制代码

上面我们说了，视图里面没有真正的数据，那在数据仓库里面，他的存储是什么样子的呢。看下面

1. [root@BlackGhost test]# cat /usr/local/mysql/data/test/aaa.frm  
   
2. TYPE=VIEW  
   
3. query=select `test`.`user`.`id` AS `id`,`test`.`user`.`name` AS `name`,`test`.`user`.`sex` AS `sex` from `test`.`user`  
   
4. md5=04d5ab2cc3ffcf3376a5e9c946f858ab  
   
5. updatable=1  
   
6. algorithm=2  
   
7. definer_user=  
   
8. definer_host=  
   
9. suid=2  
   
10. with_check_option=0  
    
11. revision=1  
    
12. timestamp=2010-10-20 19:59:34  
    
13. create-version=1  
    
14. source=select * from user  
    
15. client_cs_name=utf8  
    
16. connection_cl_name=utf8_general_ci  
    
17. view_body_utf8=select `test`.`user`.`id` AS `id`,`test`.`user`.`name` AS `name`,`test`.`user`.`sex` AS `sex` from `test`.`user`

复制代码

创建的视图，他没有.MYD,.MYI这二个文件，一个是存放数据的，一个存放索引的。在这儿就可以说明数据是存在基本表里面的。

五，修改视图

1，修改规则

1. ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
   
2.   
   
3. VIEW view_name [(column_list)]  
   
4.   
   
5. AS select_statement  
   
6.   
   
7. [WITH [CASCADED | LOCAL] CHECK OPTION]  

复制代码

2，实例

1. mysql> alter view aaa as select * from user where id <> any  
   
2. -> (select u_id from comment);  
   
3. Query OK, 0 rows affected (0.00 sec)  

复制代码

六，查看，删除视图

1. mysql> select * from aaa;    //查看视图数据  
   
2. +----+------+-----+  
   
3. | id | name | sex |  
   
4. +----+------+-----+  
   
5. |  3 | tank |   0 |  
   
6. |  4 | tank |   0 |  
   
7. +----+------+-----+  
   
8. 2 rows in set (0.00 sec)  
   
9.   
   
10. mysql> show create view aaa\G;  //查看视图  
    
11. *************************** 1. row ***************************  
    
12. View: aaa  
    
13. Create View: CREATE ALGORITHM=MERGE DEFINER=``@`` SQL SECURITY DEFINER VIEW `aaa` AS select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`sex` AS `sex` from `user` where `user`.`id` <> any (select `comment`.`u_id` AS `u_id` from `comment`)  
    
14. character_set_client: utf8  
    
15. collation_connection: utf8_general_ci  
    
16. 1 row in set (0.00 sec)  
    
17.   
    
18. mysql> drop view aaa;   //删除视图  
    
19. Query OK, 0 rows affected (0.00 sec)  

复制代码

kider

备忘

gabriell18

"That was their policy," he explained In a period of sentimental optimism, offervid enthusiasms and tearful philanthropy, he represented thepleasure-loving prince of the Renaissance, crushing his people withtaxes but dazzling them with festivities; infuriating them by hisdisregard of the public welfare,chloesunglassescheap9, but fascinating them by his good looks,Chloe Sunglasses Sale,his tolerance of old abuses, his ridicule of the monks, and by thecareless libertinage which had founded the fortunes of more than onemiddle-class husband and father--for the Duke always paid well for whathe appropriated How far do you call Sunk Creek now?""Well," said the Virginian, calculating,returning from Turin,Cheap Chloe Sunglasses, "it's mighty nigh fo'ty miles from Muddy Crossin', an' I reckon we've come eighteen"She drew back,http://chloesunglassesuk9.monwebeden.fr/, gently releasing herselfCover thecrystal, I say!"He sank back, his forehead damp with perspiration The sky above and to the west was still dark with thunder,http://chloesunglasses20139.moonfruit.com/, and lightning far away flickered among the tops of hidden hills As to Aramis, he had asked for leave of absence for five days,Gentlemen, and was gone, it was said, to Rouen on family business

fbzds

给搂搂顶