Mysql的视图

参考：https://www.cnblogs.com/chenpi/p/5133648.html

1、什么是视图

　　通俗的讲，视图就是一条select语句执行后返回的结果集。所以，我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

2、视图的特性

　　视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）

　　可以跟基本表一样，进行增删改查操作（增删改操作有条件限制）

3、视图的作用

　　方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

　　更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别。

4、使用场合

　　权限控制的时候，不希望用户访问表中某些含敏感信息的列，比如salary。

　　关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作。

实例1：

现有三张表：用户（user）、课程（course）、用户课程中间表（user_course），表结构及数据如下：

 

-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` varchar(500) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'JAVA', 'JAVA课程');
INSERT INTO `course` VALUES ('2', 'C++', 'C++课程');
INSERT INTO `course` VALUES ('3', 'C语言', 'C语言课程');

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `others` varchar(200) DEFAULT NULL,
  `others2` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'user1', '小陈', '美国', '1', '1');
INSERT INTO `user` VALUES ('2', 'user2', '小张', '日本', '2', '2');
INSERT INTO `user` VALUES ('3', 'user3', '小王', '中国', '3', '3');

-- ----------------------------
-- Table structure for `user_course`
-- ----------------------------
DROP TABLE IF EXISTS `user_course`;
CREATE TABLE `user_course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `userid` bigint(20) NOT NULL,
  `courseid` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_course
-- ----------------------------
INSERT INTO `user_course` VALUES ('1', '1', '2');
INSERT INTO `user_course` VALUES ('2', '1', '3');
INSERT INTO `user_course` VALUES ('3', '2', '1');
INSERT INTO `user_course` VALUES ('4', '2', '2');
INSERT INTO `user_course` VALUES ('5', '2', '3');
INSERT INTO `user_course` VALUES ('6', '3', '2');

 

表内容：

 

 

当我们要查询小张上的所有课程相关信息的时候，需要这样写一条长长的SQL语句，如下：

 

 

SELECT
    `uc`.`id` AS `id`,
    `u`.`name` AS `username`,
    `c`.`name` AS `coursename`
FROM
    `user` `u`
LEFT JOIN `user_course` `uc` ON ((`u`.`id` = `uc`.`userid`))
LEFT JOIN `course` `c` ON ((`uc`.`courseid` = `c`.`id`))
WHERE
    u.`name` = '小张';

 

我们可以通过视图简化操作，创建视图view_user_course如下：

-- ----------------------------
-- View structure for `view_user_course`
-- ----------------------------
DROP VIEW
IF EXISTS `view_user_course`;

CREATE ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` SQL SECURITY DEFINER 
VIEW `view_user_course` AS (
    SELECT
        `uc`.`id` AS `id`,
        `u`.`name` AS `username`,
        `c`.`name` AS `coursename`
    FROM
        (
            (
                `user` `u`
                LEFT JOIN `user_course` `uc` ON ((`u`.`id` = `uc`.`userid`))
            )
            LEFT JOIN `course` `c` ON ((`uc`.`courseid` = `c`.`id`))
        )
);

几点说明（MySQL中的视图在标准SQL的基础上做了扩展）

ALGORITHM=UNDEFINED：指定视图的处理算法

DEFINER=`root`@`localhost`：指定视图创建者

SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式

创建好视图之后，我们可以直接用以下SQL语句在视图上查询小张上的所有课程的信息，如下：

SELECT
    vuc.username,
    vuc.coursename
FROM
    view_user_course vuc
WHERE
     vuc.username = '小张';

查询结果：