MySQL应用实战：MySQL视图

2022-07-12 09:12:46

1. 视图定义

视图通过以定制的方式显示来自一个或多个表的数据
视图是一种数据库对象，用户可以像查询普通表一样查询视图
视图内其实没有存储任何数据，它只是对表的一个查询
视图的定义保存在数据字典内，创建视图所基于对表称为“基表”

2. 视图的作用与优点
作用：

控制安全
保存查询数据

优点：

提供了灵活一致级别安全性。
隐藏了数据的复杂性
简化了用户的SQL指令
通过重命名列，从另一个角度提供数据

3.视图的使用规则

视图必须有唯一命名
在mysql中视图的数量没有限制
创建视图必须从管理员那里获得必要的权限
视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY
视图不能索引，也不能关联触发器或默认值
视图可以和表同时使用

关于视图的语句

创建表

mysql> select * from idol_list;
+--------+----------------------------+--------+-----+-----------------+
| number | name                       | gender | age | work_type       |
+--------+----------------------------+--------+-----+-----------------+
|      1 | harrypotter                | 男     |  17 | 格里芬多        |
|      2 | 周杰伦                     | 男     |  36 | 歌手            |
|      3 | 丁禹兮                     | 男     |  24 | 演员            |
|      4 | 克里斯汀·斯图尔特          | 女     |  29 | 演员            |
|      5 | 王一博                     | 男     |  23 | 演员，歌手      |
|      6 | 刘亦菲                     | 女     |  34 | 演员            |
|      7 | 华晨宇                     | 男     |  31 | 歌手            |
|      8 | 沈腾                       | 男     |  41 | 演员            |
|      9 | 吴亦凡                     | 男     |  30 | 歌手            |
|     10 | 陈伟霆                     | 男     |  36 | 演员，歌手      |
+--------+----------------------------+--------+-----+-----------------+

创建视图

语句：
CREATE [OR REPLACE] VIEW 视图名 
	[(alias[, alias]...)]	#为视图字段指定别名 
	AS subquery（查询语句）
	[WITH READ ONLY];

示例1：创建视图v2, 要求查询年龄大于28的所有偶像信息
create view v2
    -> as select *
    -> from idol_list
    -> where age >28;

示例2：创建视图v3，要求查询工作类型为演员，只显示name、age、work_type。
mysql> create view v3(name,age,work_type) 
    -> as select name,age,work_type 
    -> from idol_list 
    -> where work_type='演员';

注意：在CREATE VIEW 语句中字段与子查询中的字段必须一一对应，否则就别指定别名，或在子查询中指定别名。

示例3：创建复杂视图
mysql> create view v6(姓名,最高工资,最低工资,平均工资) as 
    -> select e.name,max(salary),min(salary),avg(salary) 
    -> from salary e 
    -> inner join time d on e.gennder=d.gender;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v6;
+--------+--------------+--------------+--------------+
| 姓名   | 最高工资     | 最低工资     | 平均工资     |
+--------+--------------+--------------+--------------+
| 李刚   |       100000 |        10000 |   52647.0588 |
+--------+--------------+--------------+--------------+

5.7之后的版本可能会报 sql_mode=only_full_group_by 的错误：ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘liao.e.name’; this is incompatible with sql_mode=only_full_group_by

在这里修改，将 only_full_group_by 删除。

mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

修改视图

示例1：修改视图v2, 要求查询年龄修改为大于30的所有偶像信息

方法一：使用CREATE OR REPLACE VIEW 语句修改v2视图
mysql> create or replace view v2 
    -> as select * 
    -> from idol_list 
    -> where age >30;
方法二：使用ALTER VIEW 语句修改v2视图
mysql> alter view v2 
    -> as select * 
    -> from idol_list 
    -> where age >30;

示例2：使用ALTER VIEW 语句修改v3视图，为每个列指定别名
mysql> alter view v3(姓名,年龄,工作类型) 
    -> as select name,age,work_type 
    -> from idol_list 
    -> where work_type='演员';

删除视图

DROP VIEW 视图名;
mysql> DROP VIEW v3;

视图实战应用：

视图的ALGORITHM
ALGORITHM = MERGE/TEMPTABLE/UNDEFINED

MERGE：当使用视图时，会把查询视图的语句和创建视图的语句合并起来，形成一条语句，最后再从基表中查询；
TEMPTABLE：当使用视图时，会把创建视图的语句的查询结果当成一张临时表，再从临时表中进行筛选；
UNDEFINED：未定义，自动，让系统帮你选。

1.在数据库example下创建college表。College表内容如下所示：

字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 
number 学号 INT(10) 是 否 是 是 否 
name 姓名 VARCHAR(20) 否 否 是 否 否 
major 专业 VARCHAR(20) 否 否 是 否 否 
age 年龄 INT(5) 否 否 否 否 否 

CREATE TABLE college( 
    -> number INT(10) NOT NULL UNIQUE PRIMARY KEY COMMENT '学号', 
    -> name VARCHAR(20) NOT NULL COMMENT '姓名', 
    -> major VARCHAR(20) NOT NULL COMMENT '专业', 
    -> age INT(5) COMMENT '年龄' );

2.在college表上创建视图college_view。视图的字段包括student_num、student_name、 student_age和department。ALGORITHM设置为MERGE类型，并且为视图加上WITH LOCAL CHECK OPTION条件

create ALGORITH=MERGE VIEW 
	-> view college_view(student_num,student_name,student_age,department) 
    -> as select number,name,age,major 
    -> from college 
    -> with local check option;

3.更新视图，向视图中插入三条数据

INSERT INTO college_view VALUES(0801,'lily',20,'MBA');
INSERT INTO college_view VALUES(0802,'sirius',22,'CS');
INSERT INTO college_view VALUES(0803,'Jack',23,'CS');

4.修改视图，使其显示专业为计算机的信息，其他条件不变

方法一：
CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW
 	-> college_view(student_num,student_name,student_age,department) 
 	-> AS SELECT number,name,age,major 
 	-> FROM college 
 	-> WHERE major=’计算机’ WITH LOCAL CHECK OPTION;
方法二：
ALTER ALGORITHM=UNDEFINED VIEW
 	-> college_view(student_num,student_name,student_age,department) 
 	-> AS SELECT number,name,age,major 
 	-> FROM college 
 	-> WHERE major=’计算机’ WITH LOCAL CHECK OPTION;

5.删除视图

mysql> drop view college_view

码农公寓

关于视图的语句

视图实战应用：

相关文章