MySQL数据库，详解视图，高手必备(二)

修改视图

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. 掌握视图的创建、使⽤、修改、删除。