MySQL
基本语法
CREATE VIEW AS :指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。
:指定创建视图的 SELECT 语句,可用于查询多个基础表或源视图。
用户除了拥有 CREATE VIEW 权限外,还具有操作中涉及的基础表和其他视图的相关权限。
SELECT 语句不能引用系统或用户变量。
SELECT 语句不能包含 FROM 子句中的子查询。
SELECT 语句不能引用预处理语句参数。
创建基于单表的视图
mysql> SELECT * FROM tb_students_info;
+----+--------+---------+------+------+--------+------------+
| id | name | dept_id | age | sex | height | login_date |
+----+--------+---------+------+------+--------+------------+
| 1 | Dany | 1 | 25 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.00 sec)mysql> CREATE VIEW view_students_info
-> AS SELECT * FROM tb_students_info;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM view_students_info;
+----+--------+---------+------+------+--------+------------+
| id | name | dept_id | age | sex | height | login_date |
+----+--------+---------+------+------+--------+------------+
| 1 | Dany | 1 | 25 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.04 sec)
mysql> CREATE VIEW v_students_info
-> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
-> AS SELECT id,name,dept_id,age,sex,height,login_date
-> FROM tb_students_info;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM v_students_info;
+------+--------+------+-------+-------+----------+------------+
| s_id | s_name | d_id | s_age | s_sex | s_height | s_date |
+------+--------+------+-------+-------+----------+------------+
| 1 | Dany | 1 | 24 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+------+--------+------+-------+-------+----------+------------+
10 rows in set (0.01 sec)
创建基于多表的视图
mysql> CREATE VIEW v_students_info
-> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
-> AS SELECT id,name,dept_id,age,sex,height,login_date
-> FROM tb_students_info;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM v_students_info;
+------+--------+------+-------+-------+----------+------------+
| s_id | s_name | d_id | s_age | s_sex | s_height | s_date |
+------+--------+------+-------+-------+----------+------------+
| 1 | Dany | 1 | 24 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+------+--------+------+-------+-------+----------+------------+
10 rows in set (0.01 sec)
查询视图
使用视图重新格式化检索出的数据。
使用视图简化复杂的表连接。
使用视图过滤数据。
DESCRIBE 视图名;mysql> DESCRIBE v_students_info;
+----------+---------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+------------+-------+
| s_id | int(11) | NO | | 0 | |
| s_name | varchar(45) | YES | | NULL | |
| d_id | int(11) | YES | | NULL | |
| s_age | int(11) | YES | | NULL | |
| s_sex | enum('M','F') | YES | | NULL | |
| s_height | int(11) | YES | | NULL | |
| s_date | date | YES | | 2016-10-22 | |
+----------+---------------+------+-----+------------+-------+
7 rows in set (0.04 sec)注意:DESCRIBE 一般情况下可以简写成 DESC,输入这个命令的执行结果和输入 DESCRIBE 是一样的。