Oracle基础知识5----高级查询(分组查询、多表连接、子查询、案例)

时间:2022-09-16 13:09:09

测试数据来源:http://blog.csdn.net/ochangwen/article/details/51297893

还要准备一张表:工资级别表 losal:最低级别,hisal *别
Oracle基础知识5----高级查询(分组查询、多表连接、子查询、案例)

一、分组查询

    分组函数(也叫聚合函数、多行函数,组函数)作用于一组数据,并对一组数据返回一个值。聚合函数忽略空值
    常用分组函数avg,sum,min, max, count, wm_concat(拼接函数) ,variance(标准差),stddev(方差)

SQL> select  deptno 部门号, wm_concat(ename) 部门中员工的笱 from emp group by deptno;
部门号 部门中员工的笱
----------- --------------------------------------------------------------------------------
1 TOM,Steve
2 曹仁人,酱油哥,曹操操
3 刘备备,张飞飞,SCOTT
6 SMITH
8 宋祖英
//返回总记录数   //* 代表的是:一条记录SQL> select count(*) from emp;//返回comm不为空的总记录数SQL> select count(comm) from emp;//COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数SQL> select count(distinct(sal)) from emp;注意:组函数忽略空值。//返回所有员工的平均工资   SQL> select avg(nvl(sal,0)) from emp;注意:NVL函数使分组函数无法忽略空值//求该月本公司发出的工资总额SQL> select sum(comm)+sum(sal) from emp;SQL> select sum(nvl(sal,0)+nvl(comm,0)) from emp;
注意:select a,b,c ,组函数(x)
          from emp
          group by a,b,c;
    select里有多少字段,group by后面就有多少字段,但可以多于select里的字段。反之则不一定
注意
:group by子句一定要与分组函数结合使用,否则没有意义。
-- 1、所用包含于SELECT 列表中,而未包含于组函数中的列都必须包含于 GROUP BY 子句中。
--举例:
SQL> select empno,count(job) from emp;--执行会有异常
-- 正确写法如下:
SQL> select empno,count(job) from emp group by empno;

-- 2、不能在 WHERE 子句中使用组函数(注意)。
SQL> select deptno from emp where count(job)>0 group by deptno;
备注:ORA-00934: 此处不允许使用分组函数
where与having的区别
  where子句中不能使用分组函数
  如果子句中没有分组函数,两者都可以用
注意:从SQL优化的角度上看,尽量使用where效率会高
  having 是先分组再过滤,而where相反
--查询部门3 的平均工资
select deptno, avg(sal) from emp group by deptno having deptno=3;
-- 或者
select deptno, avg(sal) from emp where deptno=3 group by deptno ;
语法:
    SELECT    column, group_function    
    FROM    table
   [ WHERE    condition]
   [GROUP BY    group_by_expression]
   [ HAVING    group_condition]

   [ORDER BY    column];

order by 中列,应该出现在group by 子句中。这是一个很显然的道理。

SELECT dep.department_name, MAX(emp.salary), MIN(emp.salary) 
FROM departments dep
LEFT JOIN employees emp
ON (dep.department_id = emp.department_id)
GROUP BY dep.department_name
--order by 的列,必须是出现在group by 子句里的列
ORDER BY dep.department_name DESC

二、多表连接

2-1.连接的类型

1).等值连接
  连接中有“=”。

-- 示例:查询员工信息,要求显示:员工号,姓名,月薪,部门名称
select e.deptno,e.ename,e.sal,d.dname
from emp e, dept d where e.deptno=d.deptno;
2).不等值连接
-- 示例:查询员工信息,要求显示:员工号,姓名,月薪,薪水级别
select e.deptno,e.ename,e.sal,s.grade
from emp e, salgrade s where e.sal between s.losal and s.hisal;
3).外连接
  核心:通过外连接, 把对于连接条件不成立的记录,仍然包含在最后的结果中
  左外链接:当连接条件不成立的时候,等号左边的表仍然被包含。
  右外链接:当连接条件不成立的时候,等号右边的表仍然被包含。
-- 示例:按部门统计员工人数,要求显示:部门号,部门名称,人数
select d.deptno,d.dname ,count(e.empno)
from emp e, dept d where e.deptno=d.deptno group by d.deptno,d.dname;
-- 上面的查询如果部门的人数为0就没有结果显示

--右外链接
select d.deptno,d.dname ,count(e.empno)
from emp e, dept d where e.deptno (+)=d.deptno group by d.deptno,d.dname;

-- 如果是左外链这么写:e.deptno =d.deptno(+)
4).自连接
  核心:通过别名,将同一张表视为多张表
-- 示例:查询员工姓名和员工的老板姓名
select e1.ename, e1.mgr
from emp e1, emp e2
where e1.mgr=e2.ename;
  不适合操作大表,原因获取笛卡尔集是平方数据太大
  解决办法:层次查询
5).层次查询
  某些情况下,可以代替自连接
  本质情况下,是一个单表查询

三、子查询

3-1.子查询排序问题

   rownum 行号,伪列
  行号需要注意的两个问题
    1.行号永远按照默认的顺序
    2.行号只能使用<, <=; 不能使用> ,>=

-- 找到员工表中工资最高的前三名
select rownum,empno, sal
from emp
where rownum<=3
order by sal;
ROWNUM EMPNO SAL
---------- ----------- -----------
1 2 2000
3 4 3000
2 3 9000

select rownum,empno, sal
from (select * from emp order by sal desc)
where rownum<=3;
ROWNUM EMPNO SAL
---------- ----------- -----------
1 5 80000
2 3 9000
3 4 3000
Oracle基础知识5----高级查询(分组查询、多表连接、子查询、案例)
-- 查询工资比10号部门任意一个员工高的员工信息,

-- >是单行操作符,
select * from emp
where sal > any(select sal from emp where deptno=3);
--等同于大于最小值就行
select * from emp
where sal > (select min(sal) from emp where deptno=3);

四、案例

案例1:

Oracle基础知识5----高级查询(分组查询、多表连接、子查询、案例)

-- mysql用的是limit
select r,empno,ename,sal
from (select rownum r,empno,ename,sal
from (select rownum,empno,ename,sal
from (select * from emp order by sal desc)
where rownum <=8) e1 ) e2
where r>=5
案例2: