Oracle 高级查询

分组查询：(分组函数会自动过滤空值)

常用的分组函数：AVG,SUM,MIN,MAX,COUNT,WM_CONCAT

SELECT AVG(SALARY),SUM(SALARY) FROM EMPLOYEE; -求工资的平均值和和

select max(salary),min(salary) from employee; --求最大最小值

select count(*) from employee;

select count(distinct deptno) from employee; -查询不重复部门号的个数

select sum(sal)/count(*) as 一,

            sum(sal)/count(sal) as 二,

            avg(sal) as 三 

from emp;

select avg(nvl(sal,0)) from emp; -nvl函数表示如果sal为空，则返回0，否则返回sal

 

select deptno,avg(salary)

from emp

group by depno; -求各个部门的平均工资

select sum(salary)

from emp

group by deptno,job; -求各个部门、各个职位的工资总和

select avg(salary)

from emp

group by deptno,job

having avg(salary)>2000; -having过滤结果，where子句中不能使用分组函数

select avg(salary)

from emp

group by deptno,job

order by avg(salary); -按照部门、职位查询平均工资，并按照平均工资排序

group by语句的增强：语法  group by (a,b); 主要用在报表功能中

select deptno,job,sum(salary) from emp group by rollup(deptno,job);

SQL/plus的报表功能：

*****************************************************************************************************************************************************************************

多表查询：

笛卡尔集的应用！！！

select e.eid,e.username.e.salary,d.deptname

from emp e,dept d

where e.deptno=d.deptno;  -等值连接（连接条件）

外连接：（核心：通过外连接，可以把对于连接条件不成立的记录，仍然包含在最后的结果中）

左外连接：（当连接条件不成立的时候，等号左边的表仍然被包含在记录当中）写法：在等号的右边写 (+)    eg:where e.deptno=d.deptno(+);

右外连接：（当连接条件不成立的时候，等号右边的表仍然被包含在记录当中） 写法：在等号的左边写 (+)   eg:where e.deptno(+)=d.deptno;

select d.name,d.job

from emp e,dept d

where e.deptno(+)=d.deptno;

自连接：（核心：通过别名，将同一张表视为多张表）（注意：自连接不适合操作大表）

select e.ename 员工姓名,b.ename 老板姓名

from emp e,emp b

where e.mgr=b.empno; 

层次查询：（本质上是单表查询，一定情况下可以替代自连接查询）

*****************************************************************************************************************************************************************************

子查询：(select语句的嵌套)

select salary 

from emp 

where salary > (select salary 

                             from emp 

                             where ename = 'scott');

注：子查询可以放在select、where、having、from后面，但不能放在group by后面

select *

from (select eno,ename,salary,salary*12 年薪 from emp);

单行子查询(使用单行操作符：=，>,<等)：返回一条记录的子查询

select * 

from emp

where job = (select job from emp where empno = 7566) and

            salary > (select salary from emp where empno = 7782);

多行子查询（使用多行操作符：in，any，all）：返回多条记录的子查询

select * 

from emp

where deptno in (select deptno from dept where dname = 'sales' or dname = 'accounting');

select *

from emp

where salary > any (select salary from emp where dno = 30);

多行子查询的空值问题：

select * 

from emp

where eno not in (select mgr from emp); -子查询中有空值，所以返回不了记录

select * 

from emp

where eno not in (select mgr from emp where mgr is not null); -去掉子查询里面的空值，就可以了

***************************************************************************************************************************************************************************

综合案例：

1、分页查询 显示员工信息：员工号、姓名、月薪

-每页显示四条记录

-显示第二页的员工

-按照月薪排序

select r,eno,ename,salary

from(

    select rownum r,eno,ename,salary

    from (select rownum,eno,ename,salary

              from emp 

              order by salary desc) e1

              where rownum <= 8) e2

where r>=5