MySQL視圖的相關概念及應用實例分析

本篇內容主要講解“MySQL視圖的相關概念及應用實例分析”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL視圖的相關概念及應用實例分析”吧!

1.淺談數據庫對象

2.什么是視圖？

視圖一方面可以幫我們使用表的一部分而不是所有的表，另一方面也可以針對不同的用戶制定不同的查詢視圖。比如，針對一個公司的銷售人員，我們只想給他看部分數據，而某些特殊的數據，比如采購的 價格，則不會提供給他。再比如，人員薪酬是個敏感的字段，那么只給某個級別以上的人員開放，其他 人的查詢視圖中則不提供這個字段。
視圖是一種 虛擬表 ，本身是 不具有數據 的，占用很少的內存空間，它是 SQL 中的一個重要概念。
視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表。
視圖的創建和刪除只影響視圖本身，不影響對應的基表。但是當對視圖中的數據進行增加、刪除和修改操作時，數據表中的數據會相應地發生變化，反之亦然。
視圖，是向用戶提供基表數據的另一種表現形式。通常情況下，小型項目的數據庫可以不使用視 圖，但是在大型項目中，以及數據表比較復雜的情況下，視圖的價值就凸顯出來了，它可以幫助我 們把經常查詢的結果集放到虛擬表中，提升使用效率。理解和使用起來都非常方便。

3.視圖操作相關SQL

創建視圖

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW 視圖名稱 [(字段列表)] 
AS 
查詢語句 [WITH [CASCADED|LOCAL] CHECK OPTION]

查看數據庫中的表對象、視圖對象

SHOW TABLES;

查看視圖的結構

DESC / DESCRIBE 視圖名稱;

查看視圖的屬性信息

SHOW TABLE STATUS LIKE '視圖名稱'\G

查看視圖的詳細定義信息

SHOW CREATE VIEW 視圖名稱;

關于更新視圖，要使視圖可更新，視圖中的行和底層基本表中的行之間必須存在 一對一 的關系。另外當視圖定義出現如 下情況時，視圖不支持更新操作：

在定義視圖的時候指定了 “ALGORITHM = TEMPTABLE” ，視圖將不支持 INSERT 和 DELETE 操作；
視圖中不包含基表中所有被定義為非空又未指定默認值的列，視圖將不支持 INSERT 操作；
在定義視圖的 SELECT 語句中使用了 JOIN 聯合查詢 ，視圖將不支持 INSERT 和 DELETE 操作；
在定義視圖的 SELECT 語句后的字段列表中使用了 數學表達式 或 子查詢 ，視圖將不支持 INSERT ，也 不支持 UPDATE 使用了數學表達式、子查詢的字段值；
在定義視圖的 SELECT 語句后的字段列表中使用 DISTINCT 、 聚合函數 、 GROUP BY 、 HAVING 、 UNION 等，視圖將不支持 INSERT 、 UPDATE 、 DELETE ；
在定義視圖的 SELECT 語句中包含了子查詢，而子查詢中引用了 FROM 后面的表，視圖將不支持 INSERT 、 UPDATE 、 DELETE ；
視圖定義基于一個 不可更新視圖 ；
常量視圖。
雖然可以更新視圖數據，但總的來說，視圖作為 虛擬表 ，主要用于 方便查詢 ，不建議更新視圖的 數據。 對視圖數據的更改，都是通過對實際數據表里數據的操作來完成的。

使用 CREATE OR REPLACE VIEW 子句 修改視圖
刪除視圖只是刪除視圖的定義，并不會刪除基表的數據。
刪除視圖的語法是：（ 說明：基于視圖 a 、 b 創建了新的視圖 c ，如果將視圖 a 或者視圖 b 刪除，會導致視圖 c 的查詢失敗。這 樣的視圖 c 需要手動刪除或修改，否則影響使用。 ）

DROP VIEW IF EXISTS 視圖名稱;

4.視圖實操SQL

首先，我們會基于employees表來創建新的視圖，下面先給出這個表的sql腳本代碼。

/*Table structure for table `employees` */
 
DROP TABLE IF EXISTS `employees`;
 
CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL DEFAULT '0',
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` varchar(10) NOT NULL,
  `salary` double(8,2) DEFAULT NULL,
  `commission_pct` double(2,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `emp_email_uk` (`email`),
  UNIQUE KEY `emp_emp_id_pk` (`employee_id`),
  KEY `emp_dept_fk` (`department_id`),
  KEY `emp_job_fk` (`job_id`),
  KEY `emp_manager_fk` (`manager_id`),
  CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `emp_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`),
  CONSTRAINT `emp_manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `employees` */
 
insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`hire_date`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`) values (100,'Steven','King','SKING','515.123.4567','1987-06-17','AD_PRES',24000.00,NULL,NULL,90),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','1989-09-21','AD_VP',17000.00,NULL,100,90),(102,'Lex','De Haan','LDEHAAN','515.123.4569','1993-01-13','AD_VP',17000.00,NULL,100,90),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','1990-01-03','IT_PROG',9000.00,NULL,102,60),(104,'Bruce','Ernst','BERNST','590.423.4568','1991-05-21','IT_PROG',6000.00,NULL,103,60),(105,'David','Austin','DAUSTIN','590.423.4569','1997-06-25','IT_PROG',4800.00,NULL,103,60),(106,'Valli','Pataballa','VPATABAL','590.423.4560','1998-02-05','IT_PROG',4800.00,NULL,103,60),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','1999-02-07','IT_PROG',4200.00,NULL,103,60),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','1994-08-17','FI_MGR',12000.00,NULL,101,100),(109,'Daniel','Faviet','DFAVIET','515.124.4169','1994-08-16','FI_ACCOUNT',9000.00,NULL,108,100),(110,'John','Chen','JCHEN','515.124.4269','1997-09-28','FI_ACCOUNT',8200.00,NULL,108,100),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','1997-09-30','FI_ACCOUNT',7700.00,NULL,108,100),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','1998-03-07','FI_ACCOUNT',7800.00,NULL,108,100),(113,'Luis','Popp','LPOPP','515.124.4567','1999-12-07','FI_ACCOUNT',6900.00,NULL,108,100),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','1994-12-07','PU_MAN',11000.00,NULL,100,30),(115,'Alexander','Khoo','AKHOO','515.127.4562','1995-05-18','PU_CLERK',3100.00,NULL,114,30),(116,'Shelli','Baida','SBAIDA','515.127.4563','1997-12-24','PU_CLERK',2900.00,NULL,114,30),(117,'Sigal','Tobias','STOBIAS','515.127.4564','1997-07-24','PU_CLERK',2800.00,NULL,114,30),(118,'Guy','Himuro','GHIMURO','515.127.4565','1998-11-15','PU_CLERK',2600.00,NULL,114,30),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','1999-08-10','PU_CLERK',2500.00,NULL,114,30),(120,'Matthew','Weiss','MWEISS','650.123.1234','1996-07-18','ST_MAN',8000.00,NULL,100,50),(121,'Adam','Fripp','AFRIPP','650.123.2234','1997-04-10','ST_MAN',8200.00,NULL,100,50),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','1995-05-01','ST_MAN',7900.00,NULL,100,50),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','1997-10-10','ST_MAN',6500.00,NULL,100,50),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','1999-11-16','ST_MAN',5800.00,NULL,100,50),(125,'Julia','Nayer','JNAYER','650.124.1214','1997-07-16','ST_CLERK',3200.00,NULL,120,50),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','1998-09-28','ST_CLERK',2700.00,NULL,120,50),(127,'James','Landry','JLANDRY','650.124.1334','1999-01-14','ST_CLERK',2400.00,NULL,120,50),(128,'Steven','Markle','SMARKLE','650.124.1434','2000-03-08','ST_CLERK',2200.00,NULL,120,50),(129,'Laura','Bissot','LBISSOT','650.124.5234','1997-08-20','ST_CLERK',3300.00,NULL,121,50),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','1997-10-30','ST_CLERK',2800.00,NULL,121,50),(131,'James','Marlow','JAMRLOW','650.124.7234','1997-02-16','ST_CLERK',2500.00,NULL,121,50),(132,'TJ','Olson','TJOLSON','650.124.8234','1999-04-10','ST_CLERK',2100.00,NULL,121,50),(133,'Jason','Mallin','JMALLIN','650.127.1934','1996-06-14','ST_CLERK',3300.00,NULL,122,50),(134,'Michael','Rogers','MROGERS','650.127.1834','1998-08-26','ST_CLERK',2900.00,NULL,122,50),(135,'Ki','Gee','KGEE','650.127.1734','1999-12-12','ST_CLERK',2400.00,NULL,122,50),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','2000-02-06','ST_CLERK',2200.00,NULL,122,50),(137,'Renske','Ladwig','RLADWIG','650.121.1234','1995-07-14','ST_CLERK',3600.00,NULL,123,50),(138,'Stephen','Stiles','SSTILES','650.121.2034','1997-10-26','ST_CLERK',3200.00,NULL,123,50),(139,'John','Seo','JSEO','650.121.2019','1998-02-12','ST_CLERK',2700.00,NULL,123,50),(140,'Joshua','Patel','JPATEL','650.121.1834','1998-04-06','ST_CLERK',2500.00,NULL,123,50),(141,'Trenna','Rajs','TRAJS','650.121.8009','1995-10-17','ST_CLERK',3500.00,NULL,124,50),(142,'Curtis','Davies','CDAVIES','650.121.2994','1997-01-29','ST_CLERK',3100.00,NULL,124,50),(143,'Randall','Matos','RMATOS','650.121.2874','1998-03-15','ST_CLERK',2600.00,NULL,124,50),(144,'Peter','Vargas','PVARGAS','650.121.2004','1998-07-09','ST_CLERK',2500.00,NULL,124,50),(145,'John','Russell','JRUSSEL','011.44.1344.429268','1996-10-01','SA_MAN',14000.00,0.40,100,80),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','1997-01-05','SA_MAN',13500.00,0.30,100,80),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','1997-03-10','SA_MAN',12000.00,0.30,100,80),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','1999-10-15','SA_MAN',11000.00,0.30,100,80),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','2000-01-29','SA_MAN',10500.00,0.20,100,80),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','1997-01-30','SA_REP',10000.00,0.30,145,80),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','1997-03-24','SA_REP',9500.00,0.25,145,80),(152,'Peter','Hall','PHALL','011.44.1344.478968','1997-08-20','SA_REP',9000.00,0.25,145,80),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','1998-03-30','SA_REP',8000.00,0.20,145,80),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','1998-12-09','SA_REP',7500.00,0.20,145,80),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','1999-11-23','SA_REP',7000.00,0.15,145,80),(156,'Janette','King','JKING','011.44.1345.429268','1996-01-30','SA_REP',10000.00,0.35,146,80),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','1996-03-04','SA_REP',9500.00,0.35,146,80),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','1996-08-01','SA_REP',9000.00,0.35,146,80),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','1997-03-10','SA_REP',8000.00,0.30,146,80),(160,'Louise','Doran','LDORAN','011.44.1345.629268','1997-12-15','SA_REP',7500.00,0.30,146,80),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','1998-11-03','SA_REP',7000.00,0.25,146,80),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','1997-11-11','SA_REP',10500.00,0.25,147,80),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','1999-03-19','SA_REP',9500.00,0.15,147,80),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','2000-01-24','SA_REP',7200.00,0.10,147,80),(165,'David','Lee','DLEE','011.44.1346.529268','2000-02-23','SA_REP',6800.00,0.10,147,80),(166,'Sundar','Ande','SANDE','011.44.1346.629268','2000-03-24','SA_REP',6400.00,0.10,147,80),(167,'Amit','Banda','ABANDA','011.44.1346.729268','2000-04-21','SA_REP',6200.00,0.10,147,80),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','1997-03-11','SA_REP',11500.00,0.25,148,80),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','1998-03-23','SA_REP',10000.00,0.20,148,80),(170,'Tayler','Fox','TFOX','011.44.1343.729268','1998-01-24','SA_REP',9600.00,0.20,148,80),(171,'William','Smith','WSMITH','011.44.1343.629268','1999-02-23','SA_REP',7400.00,0.15,148,80),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','1999-03-24','SA_REP',7300.00,0.15,148,80),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','2000-04-21','SA_REP',6100.00,0.10,148,80),(174,'Ellen','Abel','EABEL','011.44.1644.429267','1996-05-11','SA_REP',11000.00,0.30,149,80),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','1997-03-19','SA_REP',8800.00,0.25,149,80),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','1998-03-24','SA_REP',8600.00,0.20,149,80),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','1998-04-23','SA_REP',8400.00,0.20,149,80),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','1999-05-24','SA_REP',7000.00,0.15,149,NULL),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','2000-01-04','SA_REP',6200.00,0.10,149,80),(180,'Winston','Taylor','WTAYLOR','650.507.9876','1998-01-24','SH_CLERK',3200.00,NULL,120,50),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','1998-02-23','SH_CLERK',3100.00,NULL,120,50),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','1999-06-21','SH_CLERK',2500.00,NULL,120,50),(183,'Girard','Geoni','GGEONI','650.507.9879','2000-02-03','SH_CLERK',2800.00,NULL,120,50),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','1996-01-27','SH_CLERK',4200.00,NULL,121,50),(185,'Alexis','Bull','ABULL','650.509.2876','1997-02-20','SH_CLERK',4100.00,NULL,121,50),(186,'Julia','Dellinger','JDELLING','650.509.3876','1998-06-24','SH_CLERK',3400.00,NULL,121,50),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','1999-02-07','SH_CLERK',3000.00,NULL,121,50),(188,'Kelly','Chung','KCHUNG','650.505.1876','1997-06-14','SH_CLERK',3800.00,NULL,122,50),(189,'Jennifer','Dilly','JDILLY','650.505.2876','1997-08-13','SH_CLERK',3600.00,NULL,122,50),(190,'Timothy','Gates','TGATES','650.505.3876','1998-07-11','SH_CLERK',2900.00,NULL,122,50),(191,'Randall','Perkins','RPERKINS','650.505.4876','1999-12-19','SH_CLERK',2500.00,NULL,122,50),(192,'Sarah','Bell','SBELL','650.501.1876','1996-02-04','SH_CLERK',4000.00,NULL,123,50),(193,'Britney','Everett','BEVERETT','650.501.2876','1997-03-03','SH_CLERK',3900.00,NULL,123,50),(194,'Samuel','McCain','SMCCAIN','650.501.3876','1998-07-01','SH_CLERK',3200.00,NULL,123,50),(195,'Vance','Jones','VJONES','650.501.4876','1999-03-17','SH_CLERK',2800.00,NULL,123,50),(196,'Alana','Walsh','AWALSH','650.507.9811','1998-04-24','SH_CLERK',3100.00,NULL,124,50),(197,'Kevin','Feeney','KFEENEY','650.507.9822','1998-05-23','SH_CLERK',3000.00,NULL,124,50),(198,'Donald','OConnell','DOCONNEL','650.507.9833','1999-06-21','SH_CLERK',2600.00,NULL,124,50),(199,'Douglas','Grant','DGRANT','650.507.9844','2000-01-13','SH_CLERK',2600.00,NULL,124,50),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','1987-09-17','AD_ASST',4400.00,NULL,101,10),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','1996-02-17','MK_MAN',13000.00,NULL,100,20),(202,'Pat','Fay','PFAY','603.123.6666','1997-08-17','MK_REP',6000.00,NULL,201,20),(203,'Susan','Mavris','SMAVRIS','515.123.7777','1994-06-07','HR_REP',6500.00,NULL,101,40),(204,'Hermann','Baer','HBAER','515.123.8888','1994-06-07','PR_REP',10000.00,NULL,101,70),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','1994-06-07','AC_MGR',12000.00,NULL,101,110),(206,'William','Gietz','WGIETZ','515.123.8181','1994-06-07','AC_ACCOUNT',8300.00,NULL,205,110);

首先，我們基于上表創建一個新的視圖。

CREATE TABLE emps
AS 
SELECT *
FROM employees;

創建視圖 emp_v1, 要求查詢電話號碼以 ‘011’ 開頭的員工姓名和工資、郵箱

CREATE OR REPLACE VIEW emp_v1
AS 
SELECT last_name, salary, email
FROM emps
WHERE phone_number LIKE '011%';

要求將視圖 emp_v1 修改為查詢電話號碼以 ‘011’ 開頭的并且郵箱中包含 e 字符的員工姓名和郵箱、電話號碼

CREATE OR REPLACE VIEW emp_v1
AS 
SELECT last_name, salary, email, phone_number
FROM emps
WHERE phone_number LIKE '011%' AND email LIKE '%e%';

向 emp_v1 插入一條記錄，是否可以？

DESC emps; 
DESC emp_v1; 
 
INSERT INTO emp_v1(last_name,salary,email,phone_number) 
VALUES('Tom',2300,'tom@126.com','1322321312'); 
 
#實測不可以

修改 emp_v1 中員工的工資，每人漲薪 1000

UPDATE emp_v1
SET salary = salary + 1000;
 
SELECT * FROM emp_v1;
SELECT * FROM emps;

刪除 emp_v1 中姓名為 Olsen 的員工

DELETE FROM emp_v1
WHERE last_name = 'Olsen';
 
SELECT *
FROM emp_v1
WHERE last_name = 'Olsen';

創建視圖 emp_v2 ，要求查詢部門的最高工資高于 12000 的部門 id 和其最高工資

CREATE OR REPLACE VIEW emp_v2
AS
SELECT department_id, MAX(salary) max_sal
FROM emps
GROUP BY department_id
HAVING MAX(salary) > 12000;
 
SELECT * FROM emp_v2;

向 emp_v2 中插入一條記錄，是否可以？

INSERT INTO emp_v2
VALUES(400, 18000);

刪除剛才的 emp_v2 和 emp_v1

DROP VIEW IF EXISTS emp_v1, emp_v2;
 
SHOW TABLES;

5.視圖的優缺點

1. 操作簡單

將經常使用的查詢操作定義為視圖，可以使開發人員不需要關心視圖對應的數據表的結構、表與表之間 的關聯關系，也不需要關心數據表之間的業務邏輯和查詢條件，而只需要簡單地操作視圖即可，極大簡 化了開發人員對數據庫的操作。

2. 減少數據冗余

視圖跟實際數據表不一樣，它存儲的是查詢語句。所以，在使用的時候，我們要通過定義視圖的查詢語 句來獲取結果集。而視圖本身不存儲數據，不占用數據存儲的資源，減少了數據冗余。

3. 數據安全

MySQL 將用戶對數據的 訪問限制 在某些數據的結果集上，而這些數據的結果集可以使用視圖來實現。用 戶不必直接查詢或操作數據表。這也可以理解為視圖具有 隔離性 。視圖相當于在用戶和實際的數據表之 間加了一層虛擬表。 同時， MySQL 可以根據權限將用戶對數據的訪問限制在某些視圖上， 用戶不需要查詢數據表，可以直接 通過視圖獲取數據表中的信息 。這在一定程度上保障了數據表中數據的安全性。

4. 適應靈活多變的需求 當業務系統的需求發生變化后，如果需要改動數據表的結構，則工作量相對較 大，可以使用視圖來減少改動的工作量。這種方式在實際工作中使用得比較多。

5. 能夠分解復雜的查詢邏輯 數據庫中如果存在復雜的查詢邏輯，則可以將問題進行分解，創建多個視圖 獲取數據，再將創建的多個視圖結合起來，完成復雜的查詢邏輯。

如果我們在實際數據表的基礎上創建了視圖，那么， 如果實際數據表的結構變更了，我們就需要及時對相關的視圖進行相應的維護 。特別是嵌套的視圖（就是在視圖的基礎上創建視圖），維護會變得比較復 雜， 可讀性不好 ，容易變成系統的潛在隱患。因為創建視圖的 SQL 查詢可能會對字段重命名，也可能包 含復雜的邏輯，這些都會增加維護的成本。

實際項目中，如果視圖過多，會導致數據庫維護成本的問題。

到此，相信大家對“MySQL視圖的相關概念及應用實例分析”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！