mysql_view

概述：
视图即是虚拟表，也称为派生表，因为它们的内容都派生自其它表的查询结果。虽然视图看起来感觉和基本表一样，但是它们不是基本表。基本表的内容是持久的，而视图的内容是在使用过程中动态产生的。——摘自《SQLite权威指南》

使用视图的优点：
1.可靠的安全性
2.查询性能提高
3.有效应对灵活性的功能需求
4.轻松应对复杂的查询需求

视图的基本使用：
创建：
例如我们本身有一个这样的基本表：
mysql> select * from students;
+------+----------------+-------+
| id | name | age |
+------+----------------+-------+
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
+------+----------------+-------+
那么就可以像这样来创建一个视图：
CREATE VIEW stu_view AS SELECT name FROM students;
Query OK, 0 rows affected (0.01 sec)
创建完一个视图，可以通过查看数据库中的全部数据表来查看：
MySQL> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| stu_view |
| students |
+-------------------+
2 rows in set (0.00 sec)
可以看到当前数据库中已经把刚刚创建的视图放进数据库的表集合中了。因为视图也是一种表，是虚拟表。

查询：
视图的查询和基本表的查询一样，因为视图也是一种数据表，所以你可以像这样的来查询它
mysql> select * from stu_view;
+----------------+
| name |
+----------------+
| bumblebee |
| king of monkey |
+----------------+

删除：
DROP VIEW stu_view;
删除之后可以再次查询进行验证：
mysql> select * from stu_view;
ERROR 1146 (42S02): Table 'student.stu_view' doesn't exist

接下来我们看看如果我们变动了原始的基本表，视图会有什么改变：
mysql> INSERT INTO students(id, name, age) VALUES (2, 'Zeus', 100000);
Query OK, 1 row affected (0.00 sec)
检查基本表：
mysql> SELECT * FROM students;
+------+----------------+--------+
| id | name | age |
+------+----------------+--------+
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
| 2 | Zeus | 100000 |
+------+----------------+--------+
3 rows in set (0.00 sec)
检查视图：
mysql> SELECT * FROM stu_view;
+----------------+
| name |
+----------------+
| bumblebee |
| king of monkey |
| Zeus |
+----------------+
3 rows in set (0.00 sec)

更新：
mysql> CREATE VIEW stu_view2 AS SELECT id, name FROM students;
Query OK, 0 rows affected (0.01 sec)
验证：
mysql> select * from stu_view2;
+------+----------------+
| id | name |
+------+----------------+
| 1 | bumblebee |
| 1 | king of monkey |
| 2 | Zeus |
+------+----------------+
3 rows in set (0.00 sec)
更新视图：
mysql> UPDATE stu_view2 SET name='Medusa' WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
检查视图更新结果：
mysql> SELECT * FROM stu_view2;
+------+----------------+
| id | name |
+------+----------------+
| 1 | bumblebee |
| 1 | king of monkey |
| 2 | Medusa |
+------+----------------+
3 rows in set (0.00 sec)
检查基本表更新结果：
mysql> SELECT * FROM students;
+------+----------------+--------+
| id | name | age |
+------+----------------+--------+
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
| 2 | Medusa | 100000 |
+------+----------------+--------+
3 rows in set (0.00 sec)

关联多表的视图：
以上都是基于单表的操作，接下来我们从两张表中来做一些实战。
我们额外创建一个info表作为辅助的数据表，如下：
mysql> select * from info;
+----+--------+---------------------------------+
| id | stu_id | info |
+----+--------+---------------------------------+
| 1 | 1 | A member of the deformed steel. |
| 2 | 2 | Hero in Chinese Mythology. |
| 3 | 3 | In Greek mythology the Gorgon. |
+----+--------+---------------------------------+
3 rows in set (0.00 sec)

我们创建一个连接了两张基本表的视图stu_view3
mysql> CREATE VIEW stu_view3 AS SELECT s.id, s.name, s.age, i.info FROM students s, info i WHERE i.stu_id=s.id;
Query OK, 0 rows affected (0.00 sec)
验证过程：
mysql> select * from stu_view3;
+------+----------------+--------+---------------------------------+
| id | name | age | info |
+------+----------------+--------+---------------------------------+
| 1 | bumblebee | 200 | A member of the deformed steel. |
| 2 | king of monkey | 10000 | Hero in Chinese Mythology. |
| 3 | Medusa | 100000 | In Greek mythology the Gorgon. |
+------+----------------+--------+---------------------------------+
3 rows in set (0.00 sec)

对连接了两张基本表的视图stu_view3进行更新操作：
mysql> UPDATE stu_view3 SET age=800 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

验证视图stu_view3：
mysql> select * from stu_view3;
+------+----------------+--------+---------------------------------+
| id | name | age | info |
+------+----------------+--------+---------------------------------+
| 1 | bumblebee | 800 | A member of the deformed steel. |
| 2 | king of monkey | 10000 | Hero in Chinese Mythology. |
| 3 | Medusa | 100000 | In Greek mythology the Gorgon. |
+------+----------------+--------+---------------------------------+
3 rows in set (0.00 sec)

验证基本表：
mysql> select * from students;
+------+----------------+--------+
| id | name | age |
+------+----------------+--------+
| 1 | bumblebee | 800 |
| 2 | king of monkey | 10000 |
| 3 | Medusa | 100000 |
+------+----------------+--------+
3 rows in set (0.00 sec)

总结：

1.在使用视图的时候，就是与使用表的语法一样的
2.创建视图的时候，该视图的名字如果与已经存在表重名的话，那么会报错，不允许创建。视图就是一种特殊的表

其实mysql视图的原理就是把自己想要的数据查询出来作为一个独立的表（虚拟表），在去操作这个表的数据，自己的理解如下：

SELECT uid FROM(SELECT id,song_name,scores,uid,like_num,play_num FROM vk_member_rec WHERE is_publish=2 AND privacy=1 AND is_pass=1 AND STATUS=1 AND ulist=1 ORDER BY scores DESC,like_num DESC,play_num DESC) vk_member_rec GROUP BY uid ORDER BY scores DESC,like_num DESC,play_num DESC LIMIT 0,100

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有