数据分组 – max,min,avg,sum,count
| Eg: 数据分组查询，熟练应用max,min,avg,sum,count |
|
| 1. (min max) |
SQL> select min(sal),max(sal) fromemp; MIN(SAL) MAX(SAL) 800 5000 查询出最大工资和最少工资 |
| 2. (min max) |
SQL> select ename,sal fromemp where sal=(select max(sal) from emp); ENAME SAL KING 5000.00 查询出员工中工资最高的人（子查询） |
| 3. (avg sum) |
SQL> select avg(sal),sum(sal) fromemp; AVG(SAL) SUM(SAL) 2073.21428 29025 查询员工的平均工资和总工资 |
| 4. (count) |
SQL> select count(ename) from emp; COUNT(ENAME) 14 查询员工总人数 |
| 5. (avg 子查询) |
SQL> select * from emp where sal>(selectavg(sal) from emp); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 查询出工资高于平均工资的人的信息 |
Groupby 和having子句
1. Groupby 用于对查询的结果分组统计
2. Having 子句用于限制分组的显示结果
| Eg:groupby和having语句的熟练应用。注意group by后可以跟多个条件 |
|
| 1.( Group by) |
SQL> selectDEPTNO,avg(sal),sum(sal) from emp group by DEPTNO order byDEPTNO; DEPTNO AVG(SAL) SUM(SAL) 10 2916.66666 8750 20 2175 10875 查询每个部门的平均工资和最高工资 |
| 2. (group by) |
SQL> selectDEPTNO,avg(sal),job from emp group by DEPTNO,job order by DEPTNO; DEPTNO AVG(SAL) JOB 10 1300 CLERK 10 2450 MANAGER 10 5000 PRESIDENT查询每个部门的每种岗位的平均工资和最低工资 |
| 3. (group By, having) |
SQL> selectdeptno,avg(sal) from emp group by deptno having avg(sal)<(select avg(sal) fromemp); DEPTNO AVG(SAL) 30 1566.66666 查询出部门平均工资低于平均工资的部门及其平均工资 |
3. 分组函数只能出现在选择列表、having、order by子语句中
4. 如果在select语句中同时包含group by、having和order by，那么他们的顺序是group by→having→order by
5. 在选择列中如果有列、表达式和分组函数，那么至少有一个要出现在group by字句中，否则则会出错。 ---(oracle11g测试分组条件可以不出现在查询字段中)
Oracle的多表查询
---基于2个或两个以上的表的查询
| Eg：表的联力查询 |
|
| 1. (a1.no= a2.no) |
显示雇员名，雇员工资和所在部门的名称 |
| SQL> select ename,sal,DNAME from emp,dept whereemp.DEPTNO = dept.DEPTNO; ENAME SAL DNAME CLARK 2450.00 ACCOUNTING SCOTT 3000.00 RESEARCH WARD 1250.00 SALES |
|
| 2. (and ) |
显示部门号为10的部门名和员工和工资 |
| SQL> select ename,sal,DNAME from emp,dept whereemp.DEPTNO = dept.DEPTNO and emp.deptno=10; ENAME SAL DNAME CLARK 2450.00 ACCOUNTING |
|
| 3. (between and) |
显示雇员的性命，工资及其工资的级别 – (添加表salgrade) |
| SQL> select ename,sal,grade from emp,salgradewhere emp.sal between salgrade.losal andsalgrade.hisal; ENAME SAL GRADE SMITH 800.00 1 MILLER 1300.00 2 ALLEN 1600.00 3 JONES 2975.00 4 KING 5000.00 5 注：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 SAL DNAME MILLER 1300.00ACCOUNTING KING 5000.00 ACCOUNTING FORD 3000.00 RESEARCH |
|
自连接查询
自连接是指在同一张表的连接查询
| Eg:显示员工的上级领导的姓名--- 同表联立当成2个表来考虑就可以了 |
| SQL> selecta1.empno,a1.ename,a1.mgr,a2.empno,a2.ename from emp a1,emp a2 wherea1.mgr=a2.empno; EMPNO ENAME MGR EMPNO ENAME 7902 FORD 7566 7566JONES 7788 SCOTT 7566 7566JONES |
子查询
---- 子查询是指嵌入在其他sql语句中的select语句
单行子查询
---- 只返回单行数据的子查询
| Eg：查询与SMITH同一部门的所有员工 |
| SQL> select ename,DEPTNO from emp whereDEPTNO=(select DEPTNO from emp whereename='SMITH'); ENAME DEPTNO SMITH 20 SCOTT 20 |
多行子查询
---- 返回多行数据的子查询
| Eg:查询和部门10的工作相同的雇员的姓名、工作和部门号 |
| SQL> select ename,job,deptno from empwhere job in (select distinct job from empwhere deptno=10); ENAME JOB DEPTNO CLARK MANAGER 10 BLAKE MANAGER 30 JONES MANAGER 20 |
---- 使用all操作符
| Eg：查询出工资比部门20的所有员工的工资高的员工 |
| SQL> select ename,sal,deptno from emp wheresal> all (select sal from empwhere deptno=30); ENAME SAL DEPTNO JONES 2975.00 20 SCOTT 3000.00 20 |
--- 使用any操作符
| Eg:显示工资比20部门任意一个员工工资的员 |