数据分组 – max,min,avg,sum,count
Eg: 数据分组查询,熟练应用max,min,avg,sum,count | |
1. (min max) | SQL> select min(sal),max(sal) fromemp; |
2. (min max) | SQL> ENAME KING |
3. (avg sum) | SQL> select avg(sal),sum(sal) fromemp; 2073.21428 |
4. (count) | SQL> select count(ename) from emp; COUNT(ENAME) |
5. (avg 子查询) | SQL> select * from emp where sal>(selectavg(sal) from emp); EMPNO ENAME 7566 JONES |
Groupby 和having子句
1.
2.
Eg:groupby和having语句的熟练应用。注意group by后可以跟多个条件 | |
1.( Group by) | SQL> selectDEPTNO,avg(sal),sum(sal) from emp group by DEPTNO order byDEPTNO; DEPTNO 10 20 |
2. (group | SQL> selectDEPTNO,avg(sal),job from emp group by DEPTNO,job order by DEPTNO; DEPTNO 10 10 10 |
3. (group By, | SQL> selectdeptno,avg(sal) from emp group by deptno having avg(sal)<(select avg(sal) fromemp); DEPTNO 30 1566.66666 |
3.
4.
5.
Oracle的多表查询
---基于2个或两个以上的表的查询
Eg:表的联力查询 | |
1. (a1.no= a2.no) | 显示雇员名,雇员工资和所在部门的名称 |
SQL> select ename,sal,DNAME from emp,dept whereemp.DEPTNO = dept.DEPTNO; ENAME CLARK SCOTT WARD | |
2. (and ) | 显示部门号为10的部门名和员工和工资 |
SQL> select ename,sal,DNAME from emp,dept whereemp.DEPTNO = dept.DEPTNO and emp.deptno=10; ENAME CLARK | |
3. (between and) | 显示雇员的性命,工资及其工资的级别 – (添加表salgrade) |
SQL> select ename,sal,grade from emp,salgradewhere emp.sal between salgrade.losal andsalgrade.hisal; ENAME SMITH MILLER ALLEN JONES KING 注:where内容可where (emp.sal>salgrade.losal andemp.sal | |
4. (order by) | 显示雇员名,工资以及部门名,并按部门排序 |
SQL> select ename,sal,dname from emp,dept whereemp.deptno=dept.deptno order by dept.dname; ENAME MILLER KING FORD |
自连接查询
-
自连接是指在同一张表的连接查询
Eg:显示员工的上级领导的姓名--- 同表联立当成2个表来考虑就可以了 |
SQL> selecta1.empno,a1.ename,a1.mgr,a2.empno,a2.ename from emp a1,emp a2 wherea1.mgr=a2.empno; EMPNO ENAME 7902 FORD 7788 SCOTT |
-
子查询
---- 子查询是指嵌入在其他sql语句中的select语句
单行子查询
---- 只返回单行数据的子查询
Eg:查询与SMITH同一部门的所有员工 |
SQL> select ename,DEPTNO from emp whereDEPTNO=(select DEPTNO from emp whereename='SMITH'); ENAME SMITH SCOTT |
多行子查询
---- 返回多行数据的子查询
Eg:查询和部门10的工作相同的雇员的姓名、工作和部门号 |
SQL> select ename,job,deptno from empwhere job in (select distinct job from empwhere deptno=10); ENAME CLARK BLAKE JONES |
---- 使用all操作符
Eg:查询出工资比部门20的所有员工的工资高的员工 |
SQL> select ename,sal,deptno from emp wheresal> all (select sal from empwhere deptno=30); ENAME JONES SCOTT |
--- 使用any操作符
Eg:显示工资比20部门任意一个员工工资的员工 |
SQL> select ename,sal,deptno from emp wheresal> any (select sal from empwhere deptno=30); ENAME KING FORD |
多列子查询
---
Eg:查询出与'SMITH'部门和岗位一样的员工信息 |
SQL> selectename,job,deptno from emp where (deptno,job)=(select deptno,job from emp whereename='SMITH'); ENAME ADAMS |
--- 在from子句中使用子查询
Eg:显示高于自己部门平均工资的员工信息 |
SQL> selectename,sal,mysal,a1.deptno from emp a1,(select deptno,avg(sal) mysal from emp group bydeptno) a2 wherea1.deptno=a2.deptno anda1.sal>a2.mysal; ENAME ALLEN JONES |
分页查询 ----- 非常重要
- Rownum分页
a.
b.
Eg | SQL> select ename,job,sal,rownum rn from(select * from emp) a1; ENAME SMITH ALLEN WARD |
c.
d.
e.
f.
Eg: 在第一次的筛选中要用rownum,第二次要用别名rn |
SQL> select * from (select a1.*,rownum rn from (selectename,job,sal from emp) a1 where rownum<=10) wherern>=6; ENAME BLAKE CLARK SCOTT |
- 根据rowid分页
- 按分析函数分页
利用查询结果来创建新表
Eg:创建一个部门平均工资表从emp表中生成 |
SQL> create table avgsal (deptno,avgsal) as select deptno,avg(sal) fromemp group by deptno; Table created SQL> select * from avgsal; DEPTNO 30 20 10 |
合并查询 ---- 比较麻烦,但效率较高
Ag:在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号:union all,union,intersect,minus
a. Union
Union用于取得两个结果集的合集
Eg:查询sal大于2500并且部门为20的员工,实际效果等同于and |
SQL> select ename,deptno,sal from emp wheresal>2500 ENAME ADAMS BLAKE FORD JONES KING |
b. Unionall
注: union all与union用法相同,但他的效果是缺两个集合的全集(不会合并相同的)
c. Intersect
Intersect是用于取两个集合的交集
Eg: 对于分页查询同样可以采用这种方法 |
SQL> select * from (select ename,job,sal,rownumrn from emp) where rownum<=10 ENAME BLAKE CLARK KING SCOTT TURNER |
d. Minus
Minus是取两个集合的差集(即取A集合减去与B集合重复的部分)
union all, union,intersect,minus