Oracle高级查询

1.分组查询

1.1分组函数

分组函数作用于一组数据，并对一组数据返回一个值

1.2常用的分组函数

AVG、SUM、MIN、MAX、COUNT、WM_CONCAT

1.2.1 AVG（平均值）和 SUM（总和）

例：求员工的平均工资和工资总额

select avg(sal),sum(sal) from emp;

1.2.2 MIN（最小值）和 MAX（最大值）

例：求员工工资的最小值和最大值

select max(sal),min(sal) from emp;

1.2.3 COUNT（计数）

例：求出员工的总人数

select count(emp_no) from emp;

distinct 用于去掉重复的记录

例：求部门数（不重复）

select count(distinct dept_no) from emp;

1.2.4 WM_CONCAT（行转列）

例：

select dept_no 部门号 ,wm_concat(emp_name) from emp group by dept_no

1.3 分组函数与空值

例：求员工的平均奖金

写法一：

select sum(comm)/count(*) from emp;

写法二：

select sum(comm)/count(comm) from emp;

写法三：

select avg(comm) from emp;

以上三种写法中，二和三的结果一样，但是和一的结果不一样。

为什么？因为写法一中有空值，而分组函数会自动过滤空值，导致结果不一样。

如何让他们的结果一致呢？使用分组函数中的NVL函数，可以使分组函数无法忽略空值。

例如，将写法二改为：

select sum(comm)/count(NVL(comm,0)) from emp;

1.4 Group by 字句的使用

例：求每个部门的平均工资，要求显示：部门号，部门工资

select dept_no,avg(sal) from emp group by dept_no;

注意：在select列表中所有未包含在组函数中的列都应该包含在group by字句中

例：按部门、不同职位，统计员工的工资总额

select dept_no,job,sum(sal) from emp group by dept_no,job 

1.5 having 和 where的区别

例：求平均工资大于2000的部门

<span style="font-family: monospace; white-space: pre; background-color: rgb(240, 240, 240);">select dept_no,avg(sal) from emp group by dept_no </span> having avg(sal)>2000;

区别：

1.不能在where子句中使用组函数

     可以在having子句中使用组函数

2.where是先过滤后分组

    having是先分组会过滤

1.6 在分组函数中使用order by子句

例：求每个部门的平均工资，要求显示部门号，部门平均工资，并按工资升序排列

select dept_no,avg(sal) from emp group by dept_no order by 2;

1.7 分组函数的嵌套

例：求部门平均工资的最大值

select max(avg(sal)) from emp group by dept_no

1.8 group by 语句的增强

语法：group by rollup（a,b）

等价于：

group by a,b

+

group by a

+

group by null

2.多表查询

2.1 笛卡尔集

笛卡尔集的列数等于两个表的列数的相加；

笛卡尔集的行数等于两个表的行数的相乘；

多表查询就是从笛卡尔集中选出正确的记录；这个选出正确记录的条件就是连接条件；

连接条件至少有n-1个；N代表表的个数

2.2 等值连接

连接条件中使用的是“=”

例：查询员工信息，要求显示：员工号，姓名，月薪，部门名称

select e.emp_no,e.emp_name,e.sal,d.dept_name from emp e ,dept d where e.dept_no = d.dept_no

2.3 不等值连接

连接条件中不是“=”

例：查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别

select e.emp_no ,e.emp_name,e.sal,s.gradefrom emp e, salgrade swhere e.sal between s.losal and s.hisal

2.4 外连接

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

左外连接：当连接条件不成立的时候，左边的表仍然被包含

右外连接：当连接条件不成立的时候，右边的表仍然被包含

例：按部门统计员工的人数，要求显示：部门号，部门名称，人数

select d.dept_no 部门号,d.dept_name 部门名称 ,count（e.emp_no） 人数from emp e ,dept d where e.dept_no（+） = d.dept_no（右外连接）group by d.dept_no ,d.dept_name

2.5 自连接

核心：通过表别名，将同一张表视为多张表

例：查询员工姓名和员工老板的姓名

select e.emp_name 员工姓名  , b.emp_name 老板姓名from emp e,emp b where e.boss_no = b.emp_no

问题：

不适合操作大表

3.子查询

解决问题不能一步解决的情况

语法：select的嵌套

例：查询比socct工资高的员工信息

select * from emp where sal>(select sal from emp where emp_name='socct');