对应课程地址
-- 视图-- 准备数据create database if not exists mydb6_view;use mydb6_view;create table dept(deptno int primary key,dname varchar(20),loc varchar(20));insert into dept values(10, '教研部','北京'),(20, '学工部','上海'),(30, '销售部','广州'),(40, '财务部','武汉');create table emp(empno int primary key,ename varchar(20),job varchar(20),mgr int,hiredate date,sal numeric(8,2),comm numeric(8, 2),deptno int,-- FOREIGN KEY (mgr) REFERENCES emp(empno),FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE);insert into emp values(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, null, 20),(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 30),(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),(1008, '诸葛亮', '分析师', 1004, '-04-19', 30000.00, null, 20),(1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),(1011, '周泰', '文员', 1008, '-05-23', 11000.00, null, 20),(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);create table salgrade(grade int primary key,losal int,hisal int);insert into salgrade values(1, 7000, 12000),(2, 1, 14000),(3, 14010, 20000),(4, 20010, 30000),(5, 30010, 99990);-- 创建视图CREATE OR REPLACE VIEW view1_emp AS SELECTename,job FROMemp;-- 查看表和视图show tables;show full tables;-- 查看视图结构DESCRIBE view1_emp;-- 查询视图SELECT* FROMview1_emp;-- 修改视图结构ALTER VIEW view1_emp AS SELECTa.deptno,a.dname,a.loc,b.ename,b.sal FROMdept a,emp b WHEREa.deptno = b.deptno;-- 更新视图：修改原表数据-- 先创建一个视图CREATE OR REPLACE VIEW view1_emp AS SELECTename,job FROMemp;-- 查询一下视图SELECT* FROMview1_emp;-- 更新视图UPDATE view1_emp SET ename = '鲁肃' WHEREename = '谢逊';-- 1、插入数据时，视图只是引用表格中的某些字段，-- 而另外一些字段又没有指定默认值时会插入失败INSERT INTO view1_empVALUES( '周瑜', '文员' );-- 2、视图包含聚合函数不可更新CREATE OR REPLACE VIEW view2_emp AS SELECTcount(*) cntFROMemp;SELECT* FROMview2_emp;INSERT INTO view2_empVALUES( 100 );UPDATE view2_emp SET cnt = 100;-- 3、视图包含distinct不可更新CREATE OR REPLACE VIEW view3_emp AS SELECT DISTINCTjob FROMemp;SELECT* FROMview3_emp;INSERT INTO view3_empVALUES( '财务' );-- 4、视图包含group by、having不可更新CREATE OR REPLACE VIEW view4_emp AS SELECTdeptno FROMemp GROUP BYdeptno HAVINGdeptno > 10;SELECT* FROMview4_emp;insert into view4_emp values(40);-- 5、视图包含union、union all不可更新-- union all不去重，union会去重CREATE OR REPLACE VIEW view5_emp AS SELECTempno,ename FROMemp WHEREempno <= 5 UNION SELECT empno, ename FROM emp WHERE empno > 8;SELECT* FROMview5_emp;INSERT INTO view5_empVALUES( 1015, '宋江' );-- 6、视图包含子查询不可更新CREATE OR REPLACE VIEW view6_emp AS SELECTempno,ename,sal FROMemp WHEREsal = (SELECTmax( sal ) FROMemp);SELECT* FROMview6_emp;INSERT INTO view6_empVALUES( 1015, '血刀老祖', 30000.0 );-- 7、视图包含join不可更新CREATE VIEW view7_emp AS SELECTdname,ename,sal FROMemp aJOIN dept b ON a.deptno = b.deptno;INSERT INTO view7_empVALUES( '行政部', '韦小宝', 6500.00 );-- 8、视图包含常量文字值不可更新CREATE OR REPLACE VIEW view8_emp AS SELECT'行政部' dname,'杨过' ename;INSERT INTO view8_empVALUES( '行政部', '韦小宝' );CREATE OR REPLACE VIEW view9_emp AS SELECT* FROMemp;-- 重命名视图RENAME TABLE view9_emp TO view9_1_emp;-- 删除视图DROP VIEWIFEXISTS view9_1_emp;-- 视图练习-- 1：查询部门平均薪水最高的部门名称-- 最原始做法：不使用开窗函数，不使用视图SELECTdname FROMdept WHEREdeptno = (SELECTb.deptno FROM(SELECTa.deptno,max( a.avg_sal ) FROM( SELECT deptno, avg( sal ) avg_sal FROM emp GROUP BY deptno ) a ) b );-- 1：增加一点难度查询部门平均薪水处于最高两位的部门名称-- 使用开窗函数，不使用视图-- 1.1 先查出每个部门编号对应的平均薪水-- 1.2 用开窗函数进行排序-- 1.3 找到rank小于等于2的deptno-- 1.4 再跟dept表联合查找出dnameSELECTdname FROMdept d,(SELECTdeptno FROM(SELECT*,rank() over ( ORDER BY avg_sal DESC ) rn FROM( SELECT deptno, avg( sal ) avg_sal FROM emp GROUP BY deptno ) a ) b WHERErn = 1 ) c WHEREd.deptno = c.deptno;-- 1：增加一点难度查询部门平均薪水处于最高两位的部门名称-- 使用开窗函数，并且使用视图-- 1.1 先查出每个部门编号对应的平均薪水，创建一个视图CREATE OR REPLACE VIEW view_dept_avg_sal AS SELECTdeptno,avg( sal ) avg_sal FROMemp GROUP BYdeptno;-- 1.2 用开窗函数进行排序，创建一个视图CREATE OR REPLACE VIEW view_dept_avg_sal_rank AS SELECT*,rank() over ( ORDER BY avg_sal DESC ) rn FROMview_dept_avg_sal;-- 1.3 找到rank小于等于2的deptno，创建一个视图CREATE OR REPLACE VIEW view_dept_avg_sal_top2 AS SELECT* FROMview_dept_avg_sal_rank WHERErn <= 2;-- 1.4 再跟dept表联合查找出dnameCREATE OR REPLACE VIEW view_dept_avg_sal_top2_dname AS SELECTdname FROMdept a,view_dept_avg_sal_top2 b WHEREa.deptno = b.deptno;-- 2：查询员工比所属领导薪资高的部门名、员工名、员工领导编号-- 最原始做法：不使用视图SELECTdname,ename,mgr FROMdept b,(SELECTe1.deptno,e1.ename,e1.sal,e1.mgr,e2.sal mgr_sal FROMemp e1,emp e2 WHEREe1.mgr = e2.empno AND e1.sal > e2.sal ) a WHEREb.deptno = a.deptno;-- 2：查询员工比所属领导薪资高的部门名、员工名、员工领导编号-- 使用视图-- 2.1查询员工比所属领导薪资高的部门号，然后创建一个视图CREATE OR REPLACE VIEW view_deptno_ename_mgr AS SELECTe1.deptno,e1.ename,e1.mgr FROMemp e1,emp e2 WHEREe1.mgr = e2.empno and e1.sal > e2.sal;-- 2.2将上一步查询出来的部门号和部门表进行连表查询CREATE OR REPLACE VIEW view_dname_ename_mgr AS SELECTdname,ename,mgr FROMdept a,view_deptno_ename_mgr b WHEREa.deptno = b.deptno;-- 3：查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，-- 并查询出薪资在前2名的员工信息-- 最原始做法，不使用视图SELECTempno,ename,sal FROM(SELECTempno,ename,sal,rank() over ( ORDER BY sal DESC ) rn FROMemp e,dept d,salgrade s WHEREe.deptno = d.deptno AND YEAR ( hiredate ) >= '2000' AND loc = '上海' AND grade = 4 AND sal BETWEEN losal AND hisal ) a WHERErn <= 2;-- 3：查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，-- 并查询出薪资在前2名的员工信息-- 使用视图-- 3.1 查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，创建一个视图CREATE OR REPLACE VIEW view_ename_sal_after2000_grade4 AS SELECTempno, ename, sal FROMemp e,dept d,salgrade s WHEREe.deptno = d.deptno AND grade = 4 AND sal BETWEEN losal AND hisal AND loc = '上海' AND YEAR ( hiredate ) > '2000'-- 此处三张表联查还可以用join，逻辑会更清晰SELECTempno,ename,sal FROMemp eJOIN dept d ON e.deptno = d.deptno AND loc = '上海' AND YEAR ( hiredate ) > '2000';JOIN salgrade s ON grade = 4 AND ( sal BETWEEN losal AND hisal )-- 3.2 查询出薪资在前2名的员工信息SELECTempno,ename,sal FROM( SELECT *, rank() over ( ORDER BY sal DESC ) rn FROM view_ename_sal_after2000_grade4 ) aWHERErn <= 2;
