MySQL数据库精研之旅第十二期：探秘视图，数据库中的 “虚拟表” 魔法

专栏：MySQL数据库成长记

个人主页：手握风云

目录

一、什么是视图

二、创建视图

三、视图的使用

四、修改数据

4.1. 注意事项

五、删除视图

六、视图的优点

一、什么是视图

        视图是一个虚拟的表，它是基于一个或多个基本表或其他视图的查询结果集，视图本身不存储数据，而是通过执行查询来动态生成数据。用户可以像操作普通表一样使用视图进行查询、更新和管理。视图本身并不占用物理存储空间，它仅仅是一个查询的逻辑表示，物理上它依赖于基础表中的数据。

select s.id, s.`name`, s.sno, s.age, s.gender, s.enroll_date, c.id, c.`name`, co.id, co.`name`, sc.id, sc.scorefrom student s, class c, course co, score scwhere s.class_id = c.idand sc.student_id = s.idand sc.course_id = co.idorder by s.id;

        如果我们在查询的时候遇到这样的情况：一个SQL语句用到多个表连接，此时就可以利用视图进行优化。

二、创建视图

-- 语法CREATE VIEW view_name [(column_list)] AS select_statement

三、视图的使用

• 查询用户的所有信息和考试分数

-- 查询用户的所有信息和考试成绩create view v_student_score AS (select s.id, s.`name`, s.sno, s.age, s.gender, s.enroll_date, c.id, c.`name`, co.id, co.`name`, sc.id, sc.scorefrom student s, class c, course co, score scwhere s.class_id = c.idand sc.student_id = s.idand sc.course_id = co.idorder by s.id);

        这里我们运行代码产生报错，原因是出现了重复的列名id和`name`，导致MySQL也不知道该去查询哪一个了，所以我们还得取别名。

-- 查询用户的所有信息和考试成绩create view v_student_score AS (select s.id, s.`name`, s.sno, s.age, s.gender, s.enroll_date, c.id as class_id, c.`name` as class_name, co.id as course_id, co.`name` as course_name, sc.id as score_id, sc.scorefrom student s, class c, course co, score scwhere s.class_id = c.idand sc.student_id = s.idand sc.course_id = co.idorder by s.id);

        我们还可以使用show tables来查看我们创建的视图。

select * from v_student_score;

        视图可以理解为把一个复杂的SQL进行了包装。我们没有为视图指定查询列表，视图会根据SQL返回的结果进行创建。

-- 为视图的查询列表指定别名create view v_student_score_v1(id, `name`,sno,age,gender,enroll_date,class_id,class_name,course_id,course_name,score_id,score)as select s.id, s.`name`, s.sno, s.age, s.gender, s.enroll_date, c.id, c.`name`, co.id, co.`name`, sc.id, sc.scorefrom student s, class c, course co, score scwhere s.class_id = c.idand sc.student_id = s.idand sc.course_id = co.id;

        具体的SQL不需要指定别名，但是SQL查询列表中的列要与视图中的列表顺序一一对应。

-- 查看表结构desc v_student_score;

• 查询用户的姓名和总分（隐藏学号和各科成绩）

        如果我们想查询用户的姓名和总分并且隐藏学号和各科成绩，使用真实表可以在前面添加一些字段来查到其他信息。那我们就可以通过视图来实现。

select s.`name`,sum(sc.score) total from student s, score sc where s.id = sc.student_idgroup by sc.student_id order by s.id;

create view v_student_total_point asselect s.`name`, sum(sc.score) total from student s, score scwhere s.id = sc.student_idgroup by sc.student_id order by s.id;select * from v_student_total_point;

• 视图和真实表进行表连接查询

        视图本身是一张虚拟表。虚拟表、临时表、真实表之间都可以进行表连接查询。

select * from v_student_total_point v, student s where v.id = s.id;

        客户端可以直接访问真实表，如果真实表中的表结构发生变化，客户端的SQL是不是也会发生变化？Java在获取结果集的时后有两种方式：1.根据返回结果集中列的下标；2.根据返回集中的列名。

        如果出现将列名进行移动或者修改，我们都将无法通过Java来正确获取到查询结果。为了和数据库中的列名匹配，Java应用程序就要修改代码，发布上线。这里我们就可以通过视图来解决，如果发生变化，只需把之前的视图删掉即可。

create view v_student (id, sno, `name`, age, gender, enroll_date, class_id) asselect id, sNumer, `name`,age, gender, enroll_date,class_id from student1;select * from v_student;

        通过视图把客户端和真实表之间进行了一层隔离，如果数据库的表发生了变化，只需要重新创建一个视图即可，实现了数据库和客户端之间的解耦。

四、修改数据

• 通过真实表修改数据，会影响视图

select * from score where student_id = 1 and course_id = 1;select * from v_student_score;update score set score = 90 where student_id = 1 and course_id = 1;

• 通过视图修改数据影响基表

update v_student_score_v1 set score = 80 where id = 1 and course_id = 1;

4.1. 注意事项

• 以下视图不可修改：

1. 创建视图时使用聚合函数；
2. 创建视图时使用DISTINCT；
3. 创建视图时使用GROUP BY以及HAVING子句；
4. 创建视图时使用UNION或者UNION ALL；
5. 查询列表含子查询；
6. FROM子句引用不可更新视图

五、删除视图

-- 语法drop view view_name;

六、视图的优点

1. 简单性：封装复杂查询，简化使用；

2. 安全性：隐藏敏感数据（如不含密码列的视图）；

3. 逻辑数据独立性：解耦应用与数据库，底层表结构变化仅需修改视图；

4. 重命名列：增强数据可读性