修改视图
2种⽅式。
⽅式1
如果该视图存在，就修改，如果不存在，就创建新的视图。
create or replace view 视图名
as
查询语句;
示例
CREATE OR REPLACE VIEW myv3
AS
SELECT
job_id,
AVG(salary) javg
FROM employees
GROUP BY job_id;
⽅式2
alter view 视图名
as
查询语句;
示例
ALTER VIEW myv3
AS
SELECT *
FROM employees;
删除视图
语法
drop view 视图名1 [,视图名2] [,视图名n];可以同时删除多个视图，多个视图名称之间⽤逗号隔开。
示例
mysql> drop view myv1,myv2,myv3;
Query OK, 0 rows affected (0.00 sec)
查询视图结构
/*⽅式1*/
desc 视图名称;
/*⽅式2*/
show create view 视图名称;
如：
mysql> desc myv1;
----------------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
----------------- ------------- ------ ----- --------- -------
| last_name | varchar(25) | YES | | NULL | |
| department_name | varchar(3) | YES | | NULL | |
| job_title | varchar(35) | YES | | NULL | |
----------------- ------------- ------ ----- --------- -------
3 rows in set (0.00 sec)
mysql> show create view myv1;
------
---------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-------------- ---------------------- ----------------------
| View | Create View
| character_set_client | collation_connection |
------
---------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------
-------------- ---------------------- ----------------------
| myv1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `myv1` AS select `t1`.`last_name` AS
`last_name`,`t2`.`department_name` AS
`department_name`,`t3`.`job_title` AS `job_title` from ((`employees`
`t1` join `departments` `t2`) join `jobs` `t3`) where
((`t1`.`department_id` = `t2`.`department_id`) and (`t1`.`job_id` =
`t3`.`job_id`)) | utf8 | utf8_general_ci |
------
---------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
-------------- ---------------------- ----------------------
1 row in set (0.00 sec)
show create view显⽰了视图的创建语句。
更新视图【基本不⽤】
视图的更新是更改视图中的数据，⽽不是更改视图中的sql逻辑。
当对视图进⾏更新后，也会对原始表的数据进⾏更新。
为了防⽌对原始表的数据产⽣更新，可以为视图添加只读权限，只允许读视图，
不允许对视图进⾏更新。
⼀般情况下，极少对视图进⾏更新操作。
示例
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,email
from employees;
/*插⼊*/
insert into myv4 VALUES ('路⼈甲Java','javacode2018@163.com');
SELECT * from myv4 where email like 'javacode2018%';/*修改*/
UPDATE myv4 SET last_name = '刘德华' WHERE last_name = '路⼈甲Java';
SELECT * from myv4 where email like 'javacode2018%';
/*删除*/
DELETE FROM myv4 where last_name = '刘德华';
SELECT * from myv4 where email like 'javacode2018%';
注意：视图的更新我们⼀般不使⽤，了解即可。
总结
1. 了解视图的⽤途及与表的区别。
2. 掌握视图的创建、使⽤、修改、删除。
