if mysql sum 视图_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 ...