SQL基础语法—createview语句
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 |
mysql中delete语句+------+-------+------+
| 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。