MySQL进阶(视图)---py全栈
mysql进阶(视图)---py全栈
一、什么是视图?
视图是从一个或几个基本表中导出的表,它与基本表不同,是一个虚表。也就是说是一个动态表,所有的数据依然保存在基本表中,基本表发生变化,视图的结果也相应发生变化。
二、视图的特性
视图是对若干张基本表的引用,一张虚表,查询语句执行的结果,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变);
可以跟基本表一样,进行增删改查操作(ps:增删改操作有条件限制,基本上用于查找操作);
视图和基本表一样,使用show tables
;可以看到视图表。
三、视图的优点
-
视图能够简化用户操作,
视图机制用户可以将注意力集中在关心的数据上,如果这些数据不是直接来自基本表,则可以通过定义视图,使数据库看起来结构简单、清晰,并且可以简化用户的数据查询操作。可以极大的减小查询语句的复杂程度。
-
视图使用户能以不同的角度看待同样的数据
对于固定 的一些基本表,我们可以给不同的用户建立不同的视图,这样不同的用户看到自己需要的信息就可以了。
-
视图对重构数据库提供了一定程度上的方便
如果原来A表的数据被分割成了B表和C表,我们仍然可以在B表和C表的基础上构建一个视图A,而使用该数据表的程序可以不变(使用同样的方式去访问,透明访问)
-
视图能够对机密数据提供安全保护
我们希望不同的用户只能看到自己的东西,其他的东西无法看到,这样我们可以通过视图去为每个用户建立一个视图。
四、使用场合
1、权限控制的时候,不希望用户访问表中的某些信息的时候
2、关键信息来源于多个复杂的关联表,可以创建视图提取我们需要的信息,简化操作。
五、视图基本操作
-
创建视图
--格式:CREATE VIEW 视图名称 AS SQL语句 CREATE VIEW v1 AS SELET nid, name FROM A WHERE nid > 4
-
删除视图
--格式:DROP VIEW 视图名称 DROP VIEW v1
-
修改视图
-- 格式:ALTER VIEW 视图名称 AS SQL语句 ALTER VIEW v1 AS SELET A.nid, B. NAME FROM A LEFT JOIN B ON A.id = B.nid LEFT JOIN C ON A.id = C.nid WHERE A.id > 2 AND C.nid < 5
-
使用视图
使用视图的时候,可以将其当做表进行操作即可,
-- 我们已经创建了v1视图 select * from v1
六、案例
使用如下代码创建数据库结构:
-- 创建表结构
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`description` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- 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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`others` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`others2` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- 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`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- 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);
SET FOREIGN_KEY_CHECKS = 1;
当我们要向查询小张的所有课程相关信息的时候,需要这样写:
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 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`))
)
);
# 查询小张所有信息
SELECT
vuc.username,
vuc.coursename
FROM
view_user_course vuc
WHERE
vuc.username = '小张'
总结:
视图中的查询语句性能需要进行调优,并且视图功能建议只用作查询操作,不要对视图进行增删改查。因为涉及到多张表的数据。