Oracle 之高级查询

分组查询

分组函数:作用于一组数据,并对一组数据返回一个值。

1、常用的分组函数:AVG,SUM,MIN,MAX,COUNT,WM_CONCAT

AVG:平均值,SUM:最大值,MIN:最大值,MAX:最小值,COUNT:计算,WM_CONCAT:行转列select avg(sal),sum(sal) from emp;select max(sal),min(sal) from emp;select count(empno) from emp;select count(distinct depno) from emp;set linesize 200col 部门中员工的编号 for a60select deptno 部门号,wm_concat(ename) 部门中员工的编号 from emp group by deptno;

2、分组函数和空值:

select sum(comm)|count(*) avg_comm,sum(comm)/count(comm) avg_comm,avg(comm) avg_comm from emp;分组函数会自动过滤掉空值,所以执行结果不一样。oracle中使用NVL函数使分组函数无法忽略空值。NVL(value,def_value):当value为空时,返回def_value。select count(comm),count(nvl(comm,0)) from emp;

3、group by子句:

select a,b,c,组函数(X) from table group by a,b,c;先按a进行分组,a相同的看b,b相同的看c,如果都相同,则为一组。注意:在select列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中;包含在GROUP BY子句中的列不必包含在SELECT列表中。显示部门的平均工资:部门号,平均工资select deptno,avg(sal) from emp group by deptno;按部门不同的职位,统计员工的工资总额select deptno,job,sum(sal) from emp group by deptno,job order by deptno;非法使用组函数:select deptno,count(ename) from emp;错误:所有包含于select列表中,而未包含于组函数中的列都必须包含在GROUP BY子句中。

4、过滤分组——having子句

HAVING group_condition平均工资大于2000的部门:select deptno,sum(sal) from emp group by deptno having sum(sal)>2000;同样都是过滤数据,where和having的区别:不能在where子句中使用组函数,可以在having子句中使用组函数。select deptno,avg(sal) from emp group by deptno having deptno=10;select deptno,avg(sal) from emp where deptno=10 group by deptno;如果过滤条件中没有分组函数时,where与having通用,那么从sql优化的角度来讲,where的效率更高,因为having是先分组再过滤,而where是先过滤再分组,所以,同等条件下,尽量使用where。

5、使用order by子句进行排序

select deptno,avg(sal) from emp group by deptno order by 2 –select表达式的数目–a命令 append,追加到上一个命令后面。注意必须添加两个及两个以上的空格a desc

6、嵌套分组函数

求出平均工资的最大值select max(avg(sal)) from emp group by deptno;

7、group by语句增强–适用于报表

group by rollup(a,b)–先对a,b分组;在对a分组;最后不分组select deptno,job,sum(sal) from emp group by rollup(deptno,job);= select deptno,job,sum(sal) from emp group by deptno,job+select deptno,job,sum(sal) from emp group by deptno+select deptno,job,sum(sal) from empbreak on deptno skip 2–相同的部门号只显示一次,不同的部门号空两行

8、SQL/PLUS报表功能

ttitle col 15 ‘我的报表’ col 35 sql.pnocol deptno heading 部门号col job heading 职位col sum(sal) heading 工资总额break on deptno skip 1将设置保存为.sql格式的文件,把它保存到一个目录下,然后我们可以在sqlplus中把这个文件用get语句加上路径读取进来,然后我们要执行的话就输入一个@然后加上路径,这样格式就设置好了,我们就可以执行sql语句了,执行sql语句后就会显示成我们设置的格式。

多表查询

多个表连接进行查询,数学理论——笛卡尔积。

1、等值连接

连接条件是‘=’号select e.empno,e.ename,e.sal,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno–等号连接,等值连接

2、不等值连接

连接条件不是‘=’号select e.empno,e.ename,s.gradefrom emp e,salgrade swhere e.sal betweem s.losal and s.hisal–between and 小值在前,大值在后

3、外连接

通过外连接,把对于连接条件不成立的记录,仍然包含在最好的结果中,分为左外连接和右外连接。左外连接:当条件不成立的时候,等号左边的表仍然被包含。右外连接:当条件不成立的时候,等号右边的表仍然被包含。特别注意左外连接和右外连接的写法,位置与名字相反,符号用‘(+)’表示。左外连接where e.deptno=d.deptno(+);右外连接where e.deptno(+)=d.deptno;按部门统计员工人数select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数from emp e,dept dwhere e.deptno(+)=d.deptnogroup by d.deptno,d.name

4、自连接

通过表的别名,将一张表视为多张表查询员工姓名和员工的老板姓名select e.ename 员工姓名,b.ename 员工老板from emp e,emp bwhere e.ename = b.ename问题:不适合操作大表,原因是自连接至少有两张表参与,并进行笛卡尔全集,连接之后的记录数就是单张表记录数的平方(笛卡尔积行数是两张表行数的乘积)————解决办法:层次查询。层次查询:可以替代自连接,本质是一个单表查询。select level,e.empno,e.ename,e.sal,e.mgr–leval伪列from emp econnect by prior empno=mgrstart with mgr is null–只有根节点才可以这么表示order by 1;层次查询是单表查询,不产生笛卡尔积,但是得到的结果不够直观。 Continue reading »
1 2 3 9