MySQL-初识视图

mysql 视图

 假设现在有两张表，user和class，记录学生信息和班级。user表和class表结构如下：

-- table `user`
CREATE TABLE IF NOT EXISTS user (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL COMMENT '姓名',
    age TINYINT(3) UNSIGNED NOT NULL COMMENT '年龄',
    class_id BIGINT(20) UNSIGNED NOT NULL COMMENT '班级id',
    PRIMARY KEY(id)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- table `class`
CREATE TABLE IF NOT EXISTS class (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    number TINYINT(2) UNSIGNED NOT NULL COMMENT '班级号码',
    PRIMARY KEY(id)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

 数据如下：

-- 插入班级号码为1的班级记录
INSERT INTO class (id, number) VALUES (1, 1);

-- 插入姓名为Mike的学生，并关联班级记录
INSERT INTO user (id, name, age, class_id) VALUES (null, 'Mike', 18, 1);

 当我要查询id为1的用户姓名和班级号码时，通常查询方式如下：

SELECT user.id, name, number FROM user, class 
WHERE user.class_id=class.id AND user.id=1;

 感觉还好？那么再加一个表呢？现在再加上一张表，用来记录课程，如下：

-- table `lesson`
CREATE TABLE IF NOT EXISTS lesson (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL COMMENT '课程名称',
    PRIMARY KEY(id)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 添加数据
INSERT INTO lesson (id, name) VALUES (null, '数据库系统概念');

-- 修改class表，添加字段记录课程id 
ALTER TABLE class ADD lesson_id BIGINT(20) UNSIGNED NOT NULL COMMENT '课程id';

-- 修改1班数据，关联课程
UPDATE class SET lesson_id=1 WHERE id=1;

 那么现在有三个表，我需要获得id为1的学生姓名、年龄、班级、课程等信息，查询语句如下：

SELECT user.id, user.name, age, number, lesson.name FROM user, class, lesson
WHERE user.class_id=class.id AND class.lesson_id=lesson.id AND user.id=1;

 又臭又长的语句~，作为查询的人，我必须知道各个表的结构才能查到我想要的数据，真是不友好。怎么办？使用视图（PS：注意，MySQL5 之后才提供了对视图的支持，使用时请注意数据库版本）。

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

 怎么使用，如下：

-- 创建视图
CREATE VIEW view_userinfo AS
SELECT user.id, user.name, age, number, lesson.name FROM user, class, lesson
WHERE user.class_id=class.id AND class.lesson_id=lesson.id;

-- 查询数据
SELECT * FROM view_userinfo;
SELECT * FROM view_userinfo WHERE id=1;

-- 修改数据
UPDATE view_userinfo SET name='Michael Jackson' WHERE id=1;

 很明显，使用视图之后，简化重用复杂的SQL语句，对于一些复杂但是常用的查询操作来说比较方便。并且可以保护数据，可以授予用户表特定部分的访问权限而不是整个表。

 可以将视图作为一张表来进行操作，如DML操作、过滤或排序数据、联结其他视图或者表等。但是还是存在一些限制。比DML操作，当视图的定义中有以下操作时，视图不允许跟新：

• 分组（使用GROUP BY和HAVING）
• 联结
• 子查询
• 并
• 聚集函数（Min()、Count()、Sum()等）
• DISTINCT
• 导出（计算）列

 另外，当以对多个表的查询定义视图时，可能会造成性能的下降。

 视图的常见操作，如下：

-- 创建视图
CREATE VIEW view_name AS ...

-- 查看创建视图
SHOW CREATE VIEW view_name;

-- 删除视图
DROP VIEW view_name;

-- 修改视图
CREATE OR REPLACE VIEW ...