MySql（十）视图

特点：

①视图是一个虚拟表，限制对数据的访问

②很容易的写成复杂的查询

③允许数据的独立性

④不同的视图可获得相同的数据

（一）创建视图

CREATE VIEW 视图名字 AS SELECT 属性，属性... FROM 某列表名 WHERE  条件

（二）修改视图

CREATE OR REPLACE VIEW   视图名字 （属性别名，属性别名...）AS SELECT  别名对应属性，别名对应属性... FROM  列表名  WHERE  条件

（三）创建复杂的视图（多列表）

CREATEVIEW  dept_sum_vu  (name,minsal, maxsal, avgsal)

 AS SELECT  d.dname,MIN(e.sal),MAX(e.sal),AVG(e.sal)

 FROM  emp e,dept d

 WHERE  e.deptno = d.deptno

 GROUP BY  d.dname;

（四）删除视图    

DROP VIEW 视图名字

小结

视图可从其他的表或视图中获取数据.

视图提供如下优点:

①限制数据的访问

②简化查询

③提供独立的数据

④允许多个视图使用相同的数据

⑤删除时不影响基表

---------------------------------------------

#1查询薪金(工资+奖金)比“ALLEN”多的所有员工

SELECT ename FROM empWHERE sal+IFNULL(comm,0)>(SELECT sal+IFNULL(comm,0) FROM emp WHERE ename = 'ALLEN')

#2查询在部门销售部工作的员工姓名、工资、职位

SELECT ename,sal,job FROM emp WHERE deptno = (SELECT deptno FROM dept  WHERE dname = 'sales')

#3使用子查询，找出哪个部门下没有员工

SELECT dname FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp GROUP BY deptno)

#4使用子查询，找出那些工资低于平均工资的员工姓名、工资、职位、部门号

SELECT ename,sal,job,deptno FROM emp WHERE sal<(SELECT AVG(sal) FROM emp)

#5使用子查询，找出那些工资低于其中任意一个部门的平均工资的员工

SELECT ename,sal FROM emp

WHERE sal < ANY(SELECT AVG(sal) FROM emp GROUP BY deptno)

#6查询每个部门入职最晚的员工的部门编号、员工姓名及其工资的信息  ***用虚表

SELECT emp.deptno,ename,sal,hiredate FROM emp,(SELECT deptno,MAX(hiredate)da FROM emp GROUP BY deptno) b

WHERE emp.deptno = b.deptno

AND hiredate = b.da

#7查询每个部门的部门号、部门名称、部门人数(没有员工的部门人数显示为0)***

SELECT d.dname,d.deptno,COUNT(e.deptno)

FROM dept d LEFT JOIN  emp e  ON d.deptno = e.deptno

GROUP BY e.deptno

#8查询高于自己部门平均工资的员工名字,部门号,工资,平均工资***  虚表

SELECT emp.ename,emp.deptno,emp.sal, AVG(emp.sal)

FROM emp,(SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ) b

WHERE emp.deptno=b.deptno AND sal>b.avgsal

#9查询工资排名前三的员工信息 ***

SELECT ename,sal FROM emp

ORDER BY sal DESC

LIMIT 3

#10在EMP表上创建一个视图emp_vu20，要求包括CLERK职位所有员工的信息

CREATE VIEW emp_vu20 AS SELECT * FROM emp WHERE job = 'CLERK'

#11分别创建两个视图emp_deptview、emp_jobview，按部门、职位分别统计员工的年薪.

CREATE VIEW emp_deptview AS SELECT 12*(sal+IFNULL(comm,0)) FROM emp

GROUP BY deptno

CREATE VIEW emp_jobview AS SELECT 12*(sal+IFNULL(comm,0)) FROM emp

GROUP BY job

SELECT * FROM emp_jobview