oracle复杂查询（二）

（一）函数

在实际应用中，经常使用执行复杂查询的数理统计，经常需要你显示多张表的数据，利用数据分组MAX  ,MIN  AVG,SUM,COUNT 

如何显示所有员工中最高工资和最低工资？

SQL> select max(sal), min(sal) from emp ;

如何显示最高工资的员工姓名？

select ename ，sal  from emp where sal=(select max(sal)from emp );

SQL> select ename ，sal  from emp where sal=(select max(sal) from emp ) or sal =
(select min(sal)  from emp);

显示所有员工的平均工资和工资总和？

   select sal  "平均工资"，（sal+nvl(comm,0)）*12  "工资总和"      from emp；

计算有多少员工？

select  count (*) from emp;

显示工资高于平均工资的的员工的信息

select ename  ,sal,  deptno   from emp  where  sal >(select  avg(sal)  from  emp)   order by  sal;

（2）group by 和having子句

group  by 用于查询的结果分组统计

having 子句用于限制分组显示结果

如何显示每个部门的平均工资和最高工资？

select     deptno，max（sal） "部门最高工资"，avg（sal） "部门平均工资"  from  emp  group by  deptno ；

显示每个部门的每种岗位的平均工资和最低工资？

select  deptno，job，avg(sal) "部门平均工资",min(sal) "部门最低工资"  from   emp  group by  job，deptno;

显示平均工资低于2000的部门号和它的平均工资

select  avg(sal) "部门平均工资"   deptno   from emp    group  by   deptno  having  avg（sal）<2000;

总结：对数据分组

1·分组函数只能出现在选择列表，having，order by 子句中

2、如果select语句中同时包含有group  by，having，order by，那他们的出场顺序是group by ，having，order by 

3、在选择列中如果有列，表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错  ！！！

    例如：select  avg(sal) "部门平均工资"   deptno   from emp    group  by   deptno  having  avg（sal）<2000;

三、多表查询（多表查询的条件 =不能少于表的个数—1）

多表查询指的是基于两个和两个以上的表或是视图的查询，在实际中，查询单个表不能满足你的要求时（如显示sales部门位置和其员工姓名），这种情况下需要使用到dept表和emp表

显示雇员名，雇员工资及所在部门的名字[笛卡尔集]

select  emp.ename，emp.sal    ,dept.dname    from  emp a1 ，dpt a2    where a1.deptno = a2.deptno；【笛卡尔集】

注意：如果没有条件where a1.deptno = a2.deptno则会产56条记录

如何显示部门号为10的部门名，员工名和工资

select   dname，ename，sal  from  emp,dept  where  deptno=10;

显示各个员工的姓名，工资及其工资的级别

SQL> select  a1.ename,a1.sal,a2.grade  from  emp a1 ,salgrade a2   where a1.sal
between  a2.losal and a2.hisal ;

显示雇员名，雇员工资及所在部门的名字，并按部门排序。

SQL> select  a1.ename ，a1.sal, a2.dname   from   emp  a1 ,dept  a2   where a1.d
eptno = a2.deptno  order by  a2.deptno  ;

自连接：在同一张表的连接查询。

显示某个员工的上级领导的姓名？

SQL> select      a1.ename,  a2.empno from  emp a1, emp a2  where a1.mgr = a2.empno   and  a1.ename = 'FORD';

     

什么是子查询？

子查询就是嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询：指的是只返回一行数据的子查询语句

如何显示与SMITH同一个部门的所有员工？

select  ename  from  emp where deptno = (select  deptno from emp   where ename='SMITH');

多行子查询：指的是返回多行数据的子查询  

如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号？

select ename ,sal,deptno,job        from emp   where job in (select  job from emp where deptno='10');

多行子查询中使用all操作符和any操作符

请思考：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

  SQL>select   ename，sal，deptno from  emp  where  sal> all(select  sal  from  emp   where deptno = 30);

或者如下：

SQL> select   ename，sal，deptno from  emp  where  sal> (select  max(sal)  from emp   where deptno = 30); 

如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

SQL>select   ename，sal，deptno from  emp  where  sal> any(select  sal  from  emp   where deptno = 30);

或者如下：

SQL>select   ename，sal，deptno  from  emp  where  sal> (select min（sal）   from  emp   where deptno = 30);

多列子查询：

单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的而多列子查询则是指查询返回多个列的数据的子查询语句

 请思考：如何查询与SMITH的部门和岗位完全相同的所有雇员

select  * from  emp where (deptno ,job)= (select deptno ,job from emp  where ename='SMITH');

或者

SQL> select  * from  emp where (deptno ,job)= (select deptno ,job from emp  where ename='SMITH') ;

如何显示高于自己部门的平均工资的员工的信息？？？？？？

select  * from   emp , (select  deptno,avg(sal)    mysal  from emp  group  by   deptno )  a1   where emp.sal >a1.mysal  and emp.deptno = a1.deptno;