mysql视图（一）

DROP view IF EXISTS v_dept;
create view v_dept as select deptno,dname from dept;
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
   VIEW view_name [(column_list)]
   AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

DESCRIBE view_name;  
DESC view_name;-- DESCRIBE一般都简写成 DESC
-- 显示了视图的字段定义、字段的数据类型、是否为空、是否为主/外键、默认值和额外信息

SHOW TABLE STATUS LIKE 'view_name';
-- COMMENT的值为VIEW说明该表为视图，其他的信息为NULL说明这是一个虚表，如果是基表那么会基表的信息，这是基表和视图的区别

SHOW CREATE VIEW view_name;
-- 结果显示视图的名称、创建视图的语句等信息
-- 对VIEWS表的查询可以查看数据库中所有视图的详细信息
SELECT * FROM `information_schema`.`VIEWS`;

select * from view_name;
• 1

-- CREATE OR REPLACE VIEW 语句修改视图
CREATE OR REPLACE VIEW view_name
AS SELECT column_name(s)
   FROM table_name
   WHERE condition

-- ALTER VIEW 语句修改视图
ALTER OR REPLACE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)]
AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
-- 该语句用于更改已有视图的定义
-- 其语法与CREATE VIEW类似。当视图不存在时创建，存在时进行修改
ALTER VIEW stu_class AS SELECT stuno FROM student;
alter view v_emp as select empno,ename,job,deptno from emp;
alter view v_emp(v1,v2,v3,v4) as select empno,ename,job,deptno from emp;

UPDATE view_name SET stuname='xiaofang' WHERE stuno=2

select * from view_name;

INSERT INTO view_name VALUES(6,'haojie')
 select * from view_name;
 DELETE FROM view_name WHERE stuno=1

DROP VIEW [IF EXISTS]
   view_name [, view_name] ...
   [RESTRICT | CASCADE]
DROP VIEW view_name;
DROP view IF EXISTS view_name;
SHOW CREATE VIEW view_name;

mysql> select * from dept;
+--------+-------+-----------+
| deptno | dname | loc       |
+--------+-------+-----------+
|     10 | bsc   | puyang    |
|     11 | bts   | xuchang   |
|     12 | 0521  | zhengzhou |
+--------+-------+-----------+
3 rows in set (0.01 sec)
mysql> create view v_dept as select deptno,dname from dept;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_dept;
+--------+-------+
| deptno | dname |
+--------+-------+
|     10 | bsc   |
|     11 | bts   |
|     12 | 0521  |
+--------+-------+
3 rows in set (0.23 sec)
mysql> update v_dept set dname='MSC' where deptno=12;（更新视图）
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from v_dept;
+--------+-------+
| deptno | dname |
+--------+-------+
|     10 | bsc   |
|     11 | bts   |
|     12 | MSC   |
+--------+-------+
3 rows in set (0.00 sec)
mysql> select * from dept;（基表对应的数据也更新）
+--------+-------+-----------+
| deptno | dname | loc       |
+--------+-------+-----------+
|     10 | bsc   | puyang    |
|     11 | bts   | xuchang   |
|     12 | MSC   | zhengzhou |
+--------+-------+-----------+
3 rows in set (0.00 sec)
删除视图的数据：
mysql> delete from v_dept where deptno=10;
Query OK, 1 row affected (0.00 sec)
mysql> select * from v_dept;
+--------+-------+
| deptno | dname |
+--------+-------+
|     11 | bts   |
|     12 | MSC   |
+--------+-------+
2 rows in set (0.00 sec)
mysql> select * from dept;（基表对应的记录也被删除了）
+--------+-------+-----------+
| deptno | dname | loc       |
+--------+-------+-----------+
|     11 | bts   | xuchang   |
|     12 | MSC   | zhengzhou |
+--------+-------+-----------+
2 rows in set (0.00 sec)

创建不可更新的视图：（使用临时表的算法）
mysql> create algorithm=temptable view v_dept2 as select * from dept;
Query OK, 0 rows affected (0.00 sec)
mysql> show create view v_dept2\G
*************************** 1. row ***************************
                View: v_dept2
         Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dept2` AS select `dept`.`deptno` AS `deptno`,`dept`.`dname` AS `dname`,`dept`.`loc` AS `loc` from `dept`
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.00 sec)
尝试更新视图，报错，（一定程度上保证了基表数据的安全性）
mysql> update v_dept2 set loc='shanghai' where deptno=10;
ERROR 1288 (HY000): The target table v_dept2 of the UPDATE is not updatable