概述今天介紹下mysql視圖方面的內(nèi)容，通俗的講，視圖就是一條SELECT語句執(zhí)行后返回的結果集。所以我們在創(chuàng)建視圖的時候，主要的工作就落在創(chuàng)建這條SQL查詢語句上。 01特性視圖是對若干張基本表的引用，一張?zhí)摫?，查詢語句執(zhí)行的結果，不存儲具體的數(shù)據(jù)（基本表數(shù)據(jù)發(fā)生了改變，視圖也會跟著改變）； 可以跟基本表一樣，進行增刪改查操作(ps:增刪改操作有條件限制)； 02作用方便操作，特別是查詢操作，減少復雜的SQL語句，增強可讀性； 更加安全，數(shù)據(jù)庫授權命令不能限定到特定行和特定列，但是通過合理創(chuàng)建視圖，可以把權限限定到行列級別； 03使用場合權限控制的時候，不希望用戶訪問表中某些含敏感信息的列，比如salary... 關鍵信息來源于多個復雜關聯(lián)表，可以創(chuàng)建視圖提取我們需要的信息，簡化操作； 04實例1-創(chuàng)建視圖及查詢數(shù)據(jù)操作現(xiàn)有三張表：用戶(user)、課程(course)、用戶課程中間表(user_course)，表結構及數(shù)據(jù)如下： 表定義： -- ----------------------------
-- 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');
表數(shù)據(jù)： 當我們想要查詢小張上的所以課程相關信息的時候，需要寫一條很長的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` = '小張' 但是我們可以通過視圖簡化操作，例如創(chuàng)建視圖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`：指定視圖創(chuàng)建者； SQL SECURITY DEFINER：指定視圖查詢數(shù)據(jù)時的安全驗證方式； 創(chuàng)建好視圖之后可以直接用以下SQL語句在視圖上查詢小張上的所以課程相關信息，同樣可以得到所需結果： SELECT vuc.username, vuc.coursename FROM view_user_course vuc WHERE vuc.username = '小張' 05實例2-增刪改數(shù)據(jù)操作嘗試在視圖view_user_course上做增刪改數(shù)據(jù)操作，如下： 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' 因為不能在一張由多張關聯(lián)表連接而成的視圖上做同時修改兩張表的操作； 那么哪些操作可以在視圖上進行呢？ 視圖與表是一對一關系情況：如果沒有其它約束（如視圖中沒有的字段，在基本表中是必填字段情況），是可以進行增刪改數(shù)據(jù)操作； 如創(chuàng)建用戶關鍵信息視圖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表中的其它字段要允許為空，否則操作失?。?/p> 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
視圖與表是一對多關系情況：如果只修改一張表的數(shù)據(jù)，且沒有其它約束（如視圖中沒有的字段，在基本表中是必填字段情況），是可以進行改數(shù)據(jù)操作，如以下語句，操作成功； update view_user_course set coursename='JAVA' where id=1;
update view_user_course set username='test2' where id=3;
以下操作失?。?/p> delete from view_user_course where id=3;
insert into view_user_course(username, coursename) VALUES('2','3');
總結視圖這個概念還是比較容易理解的，也沒啥好講，主要注意一下視圖中的查詢語句性能要調(diào)到最優(yōu)，修改視圖操作時要小心，不經(jīng)意可能會修改了基本表里的多條數(shù)據(jù)。。。 |
|
|
來自： 東西二王 > 《數(shù)據(jù)技術》
