|
| 1 | +/* |
| 2 | +CREATE [OR REPLACE] VIEW view |
| 3 | +[(alias[, alias]...)] |
| 4 | +AS subquery |
| 5 | +[WITH READ ONLY]; |
| 6 | + |
| 7 | +*/ |
| 8 | +--�����ͨ�û���һ�δ�����ͼ����ʾû��Ȩ�ޣ�Ҫʹ�ù���Աȥ��Ȩ�� |
| 9 | +grant create view to scott; |
| 10 | + |
| 11 | +--������ͼ |
| 12 | +create view v_emp as select * from emp where deptno = 30; |
| 13 | +--��ͼ��ʹ�� |
| 14 | +select * from v_emp; |
| 15 | +--����ͼ����������,ִ�гɹ�֮����Ҫ�ύ������ɫ��ʾ�ύ������������Ч����ɫ��ʾ�ع����������ݻָ�ԭ״̬ |
| 16 | +insert into v_emp(empno,ename) values(1111,'zhangsan'); |
| 17 | +select * from emp; |
| 18 | +--����������ͼ�Ƿ�ֻ����ͼ�Ļ�������ͨ����ͼ����в������ݣ������ֻ����ͼ�����Բ������� |
| 19 | +create view v_emp2 as select * from emp with read only; |
| 20 | +select * from v_emp2; |
| 21 | +--ֻ����ͼֻ�ṩ��ѯ��������������ɾ�IJ��� |
| 22 | +insert into v_emp2(empno,ename) values(1234,'lisi'); |
| 23 | +--ɾ����ͼ |
| 24 | +drop view v_emp2; |
| 25 | +--��ɾ����ͼ�е����ݵ�ʱ�����������Դ�ڶ�����������ʱ����ȫ������ɾ����ֻ��ɾ��һ�����е����� |
| 26 | + |
| 27 | +--����Ҫ��ƽ��нˮ�ĵȼ���͵IJ��ţ����IJ���������ʲô��������ȫʹ���Ӳ�ѯ |
| 28 | +--1����ƽ��нˮ |
| 29 | +select e.deptno,avg(e.sal) from emp e group by e.deptno; |
| 30 | +--2����ƽ��нˮ�ĵȼ� |
| 31 | +select t.deptno,sg.grade gd |
| 32 | + from salgrade sg |
| 33 | + join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t |
| 34 | + on t.vsal between sg.losal and sg.hisal; |
| 35 | +--3����ƽ��нˮ�ĵȼ���͵IJ��� |
| 36 | +select min(t.gd) from (select t.deptno,sg.grade gd |
| 37 | + from salgrade sg |
| 38 | + join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t |
| 39 | + on t.vsal between sg.losal and sg.hisal) t |
| 40 | +--4����ƽ��нˮ�ĵȼ���͵IJ��ŵIJ������� |
| 41 | + |
| 42 | +select d.dname, d.deptno |
| 43 | + from dept d |
| 44 | + join (select t.deptno, sg.grade gd |
| 45 | + from salgrade sg |
| 46 | + join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t |
| 47 | + on t.vsal between sg.losal and sg.hisal) t |
| 48 | + on t.deptno = d.deptno |
| 49 | + where t.gd = |
| 50 | + (select min(t.gd) |
| 51 | + from (select t.deptno, sg.grade gd |
| 52 | + from salgrade sg |
| 53 | + join (select e.deptno, avg(e.sal) vsal |
| 54 | + from emp e |
| 55 | + group by e.deptno) t |
| 56 | + on t.vsal between sg.losal and sg.hisal) t); |
| 57 | +--�鿴sql����ܹ����֣�sql���кܶ���ظ���sql�Ӳ�ѯ������ͨ����ͼ���ظ�������������� |
| 58 | +--������ͼ |
| 59 | +create view v_deptno_grade as select t.deptno, sg.grade gd |
| 60 | + from salgrade sg |
| 61 | + join (select e.deptno, avg(e.sal) vsal from emp e group by e.deptno) t |
| 62 | + on t.vsal between sg.losal and sg.hisal; |
| 63 | +--ʹ����ͼ�滻 |
| 64 | + |
| 65 | +select d.dname, d.deptno |
| 66 | + from dept d |
| 67 | + join v_deptno_grade t |
| 68 | + on t.deptno = d.deptno |
| 69 | + where t.gd = |
| 70 | + (select min(t.gd) |
| 71 | + from v_deptno_grade t); |
0 commit comments