前言

在实际应用中经常需要执行复杂的数据统计,经常需要显示多张表的数据,故必须要高度重视: 复杂的select语句!

1-数据分组的重要函数: max、min、avg、sum、count

(1)查询出所有员工中最高薪水和最低薪水者及其姓名的信息.

(2)查询出所有员工的平均工资和工资总和

(3)计算共有多少员工

(4)查询出工资最高和最低的员工的名字、工作岗位

(5)查询出工资高于平均工资的员工信息

(6)查询出工资低于平均工资和入职日期在1982年12月1日之前的员工且他们的工资都上涨10%后的信息

2-group by 和having子句

group by用于对查询的结果分组统计;

having子句用于限制分组显示结果;

(1)查询出每个部门的平均工资和最高工资;

(2)查询出每个部门的每种岗位的平均工资和最低工资;

(3)查询出平均工资低于2000的部门号和它的平均工资.

重点总结:

对数据分组的总结:

1.分组函数只能出现在选择列表、having、order by子句中；

2.若在select语句中同时包含有group by、having、order by则它们的顺序是group by、having、order by；(先分组à再筛选à最后排序)

3.在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则会出错！

范例:

3-多表查询(重中之重!)

多表查询是基于两个和两个以上的表或视图的查询。

在实际应用中,查询单个表可能不满足要求,(例如: 查询出sales部门位置和其员工的姓名),这种情况下需要使用到(deptno表和emp表).

(1)查询出雇员名、雇员工资和所在部门的名字（笛卡尔积）；

原则：多表查询的条件是：至少不能少于 表的个数-1

范例：查询出部门名(dname),员工名(ename)和薪水(sal)的信息

(2)查询出部门号为10的部门名、员工名和工资；

分析：

a)套路:

*因为有3个列(部门号为10部门名、员工名和工资)所以先用3个？(问号)代替之；

*2张表使用别名；

*找出2张表“纽带”；

*用实际列名代替3个？号。

范例：

上面把所有部门的都查询出来，但是我们只要 “部门号为10”:

(3)查询出每个员工的姓名、工资及其工资的级别。

扩展：

(4)查询出雇员、雇员工资及所在部门的名字，并按部门排序。

自连接：

是指在同一张表的连接查询。

(5)查询出某个员工的上级领导的姓名

分析:

No.1: 先看FORD –> MGR 7566(上级领导编号) –> EMPNO 7566(雇员编号) –> 雇员名字JONES

No.2: 我们可以把同一张表emp表看成2张表（a1、a2）！

子句查询

*子查询：是指嵌入在其它sql语句中的select语句，也叫嵌套查询。

*单行子查询：是指只返回一行数据的子查询语句；

*多行子查询：是指返回多行数据的子查询。（4、5张已经是很复杂了，不建议超过它！）

(6) 单行子查询：是指只返回一行数据的子查询语句；

范例：

查询出与SMITH同一部门的所有员工。

(7)多行子查询：是指返回多行数据的子查询。（4、5张已经是很复杂了，不建议超过它！）

范例：

查询出与部门编号为10的工作岗位相同的雇员的名字、岗位、工资、部门号

(10)在多行子查询中使用all操作符

范例：all:含义是所有XXX!

查询出工资比部门编号30的所有员工的工资高的员工的姓名、工资和部门编号。

注释：查询出来的每条记录中的sal都应大于部门编号为30号中的员工薪水即可！

（其实只要比最高的工资高即可）！

方法1：

方法2：效率高得多 比 方法1

(11)在多行子查询中使用any操作符

范例：any:含义是任意一个XXX!

查询出工资比部门编号是30的任意一个员工的工资高的员工的姓名、工资和部门编号。

注释：查询出来的工资只要比部门编号为30中任一个员工大（其实只要比最低的工资高即可），就可以了！

方法1：

方法2：效率高!

(12)多列子查询

*单行子查询是指子查询只返回单列、单行数据；

*多行子查询是指返回单列多行数据，都是针对单列而言的，

*多列子查询则是指查询返回多个列数据的子查询语句。

范例：

查询出与SMITH的部门和岗位完全相同的所有雇员。

范例：难度 ★★

查询出高于自己部门平均工资的员工的信息。

PS:

*给列起别名时，可以加 as;

*给表起别名时，不能加 as.

归纳：在from子句中使用子查询

当在from子句中使用子查询时，该子查询会被作为一个视图来对待，故叫内嵌视图；

当在from子句中使用子查询时，必须给子句查询指定别名。

(13)分页查询

Oracle分页一共有3种方式：

方法：rownum分页：

范例：

按雇员的id号升序取出 8~12这5行!

No.1步: 将(select * from emp )当作是内嵌视图且起个别名a1,如下图所示:

No.2步: 显示rownum[Oracle分配的] (注:当作公式记住!)

注释:

* (select * from emp) a1表示: 给(select * from emp)起个别名为a1;

* rownum rn 表示:显示rownum(行号)且别名是rn;

* a1.* 表示: 将子查询(select * from emp)取出来,再查询1次.

No.3步: 取出/筛选/截取rn列小于等于12;

//将(select a1.*,rownum rn from (select * from emp) a1 where rownum<=12)当作内嵌视图(from子查询),再次查询之,如下图所示:

归纳总结:

a. 如果指定查询列,则仅需修改最内层的子查询; (select * from emp)

范例: 查询出rn列，行号为8~12的雇员姓名、薪水的信息。

b.如何排序,仅需修改最内层的子查询;

范例：查询出rn列，行号为8~12的雇员姓名、薪水且按薪水的升序排列的信息。

范例：查询出rn列，行号为8~12的雇员姓名、薪水且按薪水的降序排列的信息。

c.查询出4~9

PS：

统计一张表（emp）总共有多少行？