mysql-拓展学习-视图和索引

视图

什么是视图:视图是逻辑上来自一个或多个表的数据集合。
– 视图是虚拟的，不真实存在
– 限制其它用户对数据库表的访问,因为视图可以有选择性的显示数据库表的一部分；
– 容易实现复杂的查询；
– 对于相同的数据可以产生不同的视图；

– 视图的操作和表一样
– 简单的视图：数据来至于一张表
– 创建视图 :该视图只显示20号部门的员工信息
create or replace view emp_v_deptno20 as select * from emp where deptno=20;

– 访问视图
select * from emp_v_deptno20;
创建一个视图，通过该视图可以查询到工资在2000-5000内并且姓名中包含有A的员工编号，姓名，工资。
create or replace view emp_v_sal_ename as select empno,ename,sal from emp where (sal between 2000 and 5000) and 0<instr(ename,‘A’)
select ename from emp_v_sal_ename;
– 给视图的列名起列别名
create or replace view emp_v_sal_ename as select empno 编号,ename 姓名,sal 工资 from emp where (sal between 2000 and 5000) and 0<instr(ename,‘A’)
select * from emp_v_sal_ename where 2000<工资;

– 复杂的视图：数据来至于一张或多张表
– 子查询中有 group by、DISTINCT 或者多表连接
– 创建一个视图，通过该视图可以查看每个部门的名称，最低工资，最高工资，平均工资
create or replace view emp_V_FZ as select dname ,min(sal) minsal,max(sal) maxsal,avg(sal) avgsal
from emp e,dept d where e.deptno=d.deptno group by dname
select * from emp_V_FZ;

– 插入数据到视图中：
– ①插入视图只会影响基表中的数据不会影响视图
– ②以下情况不能插入视图：子查询中有GROUP函数、GROUP BY子句，DISTINCT关键字
– ③基表中未在视图中选择的其它列定义为非空并且没有默认值
create or replace view emp_v_deptno as select * from emp where deptno=20; – 简单的视图
insert into emp_v_deptno(empno,ename,job) values(7777,‘雷杰’,‘洗脚’);
insert into emp_v_deptno(empno,ename,job,comm) values(8888,‘雷杰1’,‘洗脚1’,9000);
insert into emp_v_deptno(empno,ename,job,comm) values(9999,‘雷杰2’,‘洗脚3’,null);
insert into emp_V_FZ(dname,minsal,maxsal) values(‘雷杰’,5475,100); – not ok

– 更新视图
– ①更新视图只会影响基表中的数据
– ②以下情况不能修改视图：子查询中有GROUP函数、GROUP BY子句，DISTINCT关键字
select* from emp_v_deptno;
update emp_v_deptno set job=‘洗头’ where deptno=20
update emp_V_FZ set minsal=20; – not ok

create or replace view emp_v_dept as select empno,ename,dname from emp e,dept d where e.deptno=d.deptno; – 复杂的视图
update emp_v_dept set ename=‘zhangsan’ where empno=7369;

– with check option
– 如果要确保在视图上执行的DML操作仅限于一定的范围，便可使用WITH CHECK OPTION子句；
– 限定不能修改条件列
create or replace view emp_v_deptno as select * from emp where deptno=20; – 简单的视图
update emp_v_deptno set deptno=10 where empno=7369

create or replace view emp_v_deptno as select * from emp where deptno=20 with check option;
update emp_v_deptno set deptno=10 where empno=7566 – CHECK OPTION failed ‘mytest.emp_v_deptno’
select * from emp_v_dept
select* from emp_v_deptno;
select * from emp;