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 empsas select *from employees;
创建视图 emp_v1, 要求查询电话号码以 ‘011’ 开头的员工姓名和工资、邮箱
create or replace view emp_v1as select last_name, salary, emailfrom empswhere phone_number like '011%';
要求将视图 emp_v1 修改为查询电话号码以 ‘011’ 开头的并且邮箱中包含 e 字符的员工姓名和邮箱、电话号码
create or replace view emp_v1as select last_name, salary, email, phone_numberfrom empswhere 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_v1set salary = salary + 1000; select * from emp_v1;select * from emps;
删除 emp_v1 中姓名为 olsen 的员工
delete from emp_v1where last_name = 'olsen'; select *from emp_v1where last_name = 'olsen';
创建视图 emp_v2 ，要求查询部门的最高工资高于 12000 的部门 id 和其最高工资
create or replace view emp_v2asselect department_id, max(salary) max_salfrom empsgroup by department_idhaving max(salary) > 12000; select * from emp_v2;
向 emp_v2 中插入一条记录，是否可以？
insert into emp_v2values(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视图的相关概念及应用实例分析的详细内容。