SQL基础语法—create view语句

1 create view语句介绍

create view语句是将某个查询数据的定义保留下来，以便随时调用，这就是所谓的视图。视图本身不存储查询结果，只是一个定义。

Syntax:
CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

or replace关键词表示当创建的视图已经存在时，执行替换命令；

select_statement子句则是创建视图的select语句，可以是从表中查询数据，也可以从其他视图中查询数据；

2 create view语句注意事项

当视图被创建之后，则其定义就已经固定不会再改变，比如一个视图是由select *创建的，则后续对表增加的字段不会成为视图的一部分，而后续对表删除字段则会导致查询视图失败；

创建的视图默认情况下是属于当前数据库的，当要创建到另外的数据库是则要在视图名前面加上数据库名：

CREATE VIEW test.v AS SELECT * FROM test2.t;

order by子句在创建视图过程中是允许的，但当后续的查询视图的语句中有自己 的order by子句时则会被忽略掉；

视图在满足特定条件时是可以执行insert/update/delete语句的，条件就是视图中的每一行和视图对应的表中的每行数据都能一一对应起来。

mysql> insert into v_students_male values(3,'ddd',0);
Query OK, 1 row affected (0.00 sec)
mysql> update v_students_male set sname='eee' where sid=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from v_students_male where sid=3;
Query OK, 1 row affected (0.01 sec)

3 create view语句的应用

CREATE VIEW v_today (today) AS SELECT CURRENT_DATE; ##指定字段名
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;

mysql> create view v_students_male as select sid,sname from students where sex=0;
Query OK, 0 rows affected (0.00 sec)
Select * from v_students_male
+------+-------+
| sid | sname |
+------+-------+
| 	1 | aaa |

mysql> create view v_students_female as select sid,sname from students where sex=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_students_female where sid>1;
+------+-------+
| sid | sname |
+------+-------+
| 	2 | ccc |

mysql> create view v_students_male as select sid,sname,sex from students where sex=0; ##创建相同名称的视图会事变
ERROR 1050 (42S01): Table 'v_students_male' already exists

mysql> create or replace view v_students_male as select sid,sname,sex from students where sex=0; ##添加or replace参数后，原视图被替换
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_students_male;
+------+-------+------+
| sid | sname | sex |
+------+-------+------+
| 1 | aaa | 0 |

mysql> create view v_test as select * from students;
mysql> alter table students add test int; ##表增加一个字段
mysql> select * from v_test; ##查询视图依然是之前的三个字段结果
+------+-------+------+
| sid | sname | sex |
+------+-------+------+
| 1 | aaa | 0 |
| 2 | ccc | 1 |
mysql> alter table students drop column sex; ##表删除原来的一个字段
mysql> select * from v_test; ##查询视图失败
ERROR 1356 (HY000): View 'test.v_test' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them