MySQL初级入门篇之视图的相关概念及应用实例

来源：脚本之家时间：2022-04-19 13:50:40

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 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。

实际项目中，如果视图过多，会导致数据库维护成本的问题。