你的位置:首页 > 数据库

[数据库]Mysql中的视图


一、什么是视图

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

二、视图的特性

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

可以跟基本表一样,进行增删改查操作(ps:增删改操作有条件限制);

三、视图的作用

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

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

四、使用场合

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

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

五、视图实例

现有三张表:用户(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');

View Code

这时,当我们想要查询小张上的所以课程相关信息的时候,需要这样写一条长长的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 VIEWIF 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.coursenameFROM  view_user_course vucWHERE   vuc.username = '小张'