MySQL中视图
2018-07-19 05:46:59来源：博客园 阅读 ()
转载自：http://www.cnblogs.com/chenpi/p/5133648.html
什么是视图
通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图的特性
视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
可以跟基本表一样，进行增删改查操作(ps:增删改操作有条件限制)；
视图的作用
方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；
更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；
使用场合
权限控制的时候，不希望用户访问表中某些含敏感信息的列，比如salary...
关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作；
视图实例1-创建视图及查询数据操作
现有三张表：用户(user)、课程(course)、用户课程中间表(user_course)，表结构及数据如下：
表定义：
1 -- ---------------------------- 2 -- Table structure for `course` 3 -- ---------------------------- 4 DROP TABLE IF EXISTS `course`; 5 CREATE TABLE `course` ( 6 `id` bigint(20) NOT NULL AUTO_INCREMENT, 7 `name` varchar(200) NOT NULL, 8 `description` varchar(500) NOT NULL, 9 PRIMARY KEY (`id`) 10 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; 11 12 -- ---------------------------- 13 -- Records of course 14 -- ---------------------------- 15 INSERT INTO `course` VALUES ('1', 'JAVA', 'JAVA课程'); 16 INSERT INTO `course` VALUES ('2', 'C++', 'C++课程'); 17 INSERT INTO `course` VALUES ('3', 'C语言', 'C语言课程'); 18 19 -- ---------------------------- 20 -- Table structure for `user` 21 -- ---------------------------- 22 DROP TABLE IF EXISTS `user`; 23 CREATE TABLE `user` ( 24 `id` bigint(20) NOT NULL AUTO_INCREMENT, 25 `account` varchar(255) NOT NULL, 26 `name` varchar(255) NOT NULL, 27 `address` varchar(255) DEFAULT NULL, 28 `others` varchar(200) DEFAULT NULL, 29 `others2` varchar(200) DEFAULT NULL, 30 PRIMARY KEY (`id`) 31 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; 32 33 -- ---------------------------- 34 -- Records of user 35 -- ---------------------------- 36 INSERT INTO `user` VALUES ('1', 'user1', '小陈', '美国', '1', '1'); 37 INSERT INTO `user` VALUES ('2', 'user2', '小张', '日本', '2', '2'); 38 INSERT INTO `user` VALUES ('3', 'user3', '小王', '中国', '3', '3'); 39 40 -- ---------------------------- 41 -- Table structure for `user_course` 42 -- ---------------------------- 43 DROP TABLE IF EXISTS `user_course`; 44 CREATE TABLE `user_course` ( 45 `id` bigint(20) NOT NULL AUTO_INCREMENT, 46 `userid` bigint(20) NOT NULL, 47 `courseid` bigint(20) NOT NULL, 48 PRIMARY KEY (`id`) 49 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; 50 51 -- ---------------------------- 52 -- Records of user_course 53 -- ---------------------------- 54 INSERT INTO `user_course` VALUES ('1', '1', '2'); 55 INSERT INTO `user_course` VALUES ('2', '1', '3'); 56 INSERT INTO `user_course` VALUES ('3', '2', '1'); 57 INSERT INTO `user_course` VALUES ('4', '2', '2'); 58 INSERT INTO `user_course` VALUES ('5', '2', '3'); 59 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 = '小张'
视图实例2-增删改数据操作
继续，我们可以尝试在视图view_user_course上做增删改数据操作，如下：
update view_user_course set username='test',coursename='JAVASCRIPT' where id=3
遗憾的是操作失败，提示错误信息如下：
[SQL] update view_user_course set username='test',coursename='JAVASCRIPT' where id=3
[Err] 1393 - Can not modify more than one base table through a join view 'demo.view_user_course'
因为不能在一张由多张关联表连接而成的视图上做同时修改两张表的操作；
那么哪些操作可以在视图上进行呢？
视图与表是一对一关系情况：如果没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行增删改数据操作；
如我们创建用户关键信息视图view_user_keyinfo，如下：
-- ----------------------------
-- View structure for `view_user_keyinfo`
-- ----------------------------
DROP VIEW
IF EXISTS `view_user_keyinfo`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `view_user_keyinfo` AS SELECT
`u`.`id` AS `id`,
`u`.`account` AS `account`,
`u`.`name` AS `username`
FROM
`user` `u`;
进行增删改操作如下，操作成功（注意user表中的其它字段要允许为空，否则操作失败）：
INSERT INTO view_user_keyinfo (account, username)
VALUES
('test1', 'test1');
DELETE
FROM
view_user_keyinfo
WHERE
username = 'test1';
UPDATE view_user_keyinfo
SET username = 'updateuser'
WHERE
id = 1
视图与表是一对多关系情况：如果只修改一张表的数据，且没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行改数据操作，如以下语句，操作成功；
update view_user_course set coursename='JAVA' where id=1;
update view_user_course set username='test2' where id=3;
以下操作失败：
delete from view_user_course where id=3;
insert into view_user_course(username, coursename) VALUES('2','3');
其它
视图中的查询语句性能要调到最优；
修改操作时要小心，不经意间你已经修改了基本表里的多条数据；
其它性能相关方面待实践体会...
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
下一篇：MySQL中的触发器
- MySQL replace函数怎么替换字符串语句 2020-03-09
- PHP访问MySQL查询超时怎么办 2020-03-09
- mysql登录时闪退 2020-02-27
- MySQL出现1067错误号 2020-02-27
- mysql7.x如何单独安装mysql 2020-02-27
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
