026 SQL函数深入

涉及知识点：外连接、交叉连接、自然连接、自连接、组函数、GROUP BY子句、USING的用法、HAVING子句、组函数的嵌套使用

1. 什么是外连接

(1) 什么是外连接？

外连接是指查询出符合连接条件的数据同时还包含孤儿数据。左外连接包含左表的孤儿数据，右外连接包含右表的孤儿数据，全外连接包含两个表的孤儿数据。

外连接与内连接的最大的区别就是包含孤儿数据。

(2) 什么是孤儿数据？

孤儿数据是指被连接的列的值为空的数据。

(3) 外连接类型有哪些？分别表示什么含义？

左外（LEFT OUTER JOIN）：包含左表的孤儿数据；

右外（RIGHT OUTER JOIN）:包含右表的孤儿数据；

全外（FULL OUTER JOIN）：包含左右两个表中的孤儿数据。

以上均为SQL99标准。

2. SQL99外连接(OUTER JOIN)

(1) 用左外链接查询雇员名字以及他们所在的部门名称，包含那些没有部门的雇员。

SQL99外连接语法格式，用LEFT OUTER JOIN|RIGHT OUTER JOIN|FULL OUTER JOIN定义连接类型，有ON子句创建连接条件。

左外连接：谁是左表，谁的数据都显示，包含孤儿数据。

SQL> select last_name,department_name from employees em left outer join departments de on em.department_id=de.department_id;

(2) 用右外链接查询雇员名字以及他们所在的部门名称，包含那些没有雇员的部门。

SQL> select last_name,department_name from employees em right outer join departments de on de.department_id=em.department_id;

(3) 查询所有雇员和部门，包含那些没有雇员的部门以及没有部门的雇员。

SQL> select last_name,department_name from employees em full outer join departments de on em.department_id=de.department_id;

3. Oracle中的外连接

(1) Oracle扩展的外连接语法结构是什么？

在Oracle数据库中对外连接中的左外和右外连接做了扩展，可以简化外连接的语法。通过在连接条件的后侧使用（+）来表示是否显示孤儿数据，有（+）表示不显示孤儿数据而另一侧显示孤儿数据。但是该种写法仅能在Oracle数据库中使用。

实例：查询雇员名字以及他们所在的部门名称，包含那些没有部门的雇员。

SQL> select last_name,department_name from employees em,departments de where em.department_id=de.department_id(+);

实例：查询雇员名字以及他们所在的部门名称，包含那些没有雇员的部门。

SQL> select em.last_name,de.department_name from employees em,departments de where em.department_id(+)=de.department_id;

4. SQL99交叉连接(CROSS JOIN)

(1) 什么是SQL99中的交叉连接？

CROSS JOIN子句导致两个表的交叉乘积；

该连接和两个表之间的笛卡尔乘积是一样的。

SELECT last_name,department_name from employees cross join departments;

5. SQL99自然连接(NATURAL JOIN)

(1) 什么是SQL99中的自然连接？

NATURAL JOIN子句基于两个表之间有相同名字的所有列。

它从两个表中选择在所有的匹配列中有相等值的行。

如果有相同名字的列的数据类型不同，返回一个错误。

(2) 使用自然连接需要注意什么？

1-如果做自然连接的两个表有多个字段都满足有相同名称个类型，那么他们会被作为自然连接的条件。

2-如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。

3-由于Oracle中可以进行这种非常简单的natural join，我们在设计表时对具有相同含义的字段需要考虑到使用相同的名字和数据类型。

实例：查询部门ID，部门名称以及他们所在的城市。

SQL> select de.department_id,de.department_name,lo.city from departments de natural join locations lo;

6. SQL99USING子句的使用

(1) USING子句的作用是什么？

当有多个列匹配时，用USING子句匹配唯一的列。

如果某列在USING中使用，那么在引用该列时不要使用表名或者别名。

NATURAL JOIN和USING子句是互相排斥的。

实例：查询location_id为1800的部门名称以及他们所在的城市名称，指定location_id为连接列。

SQL> select d.department_name,l.city from departments d join locations l using(location_id) where location_id=1800;

7. SQL99内连接(INNER JOIN)

(1) 什么是SQL99内连接？

内连接（INNER JOIN）：内连接通过INNER JOIN来建立两个表的连接。在内连接中使用INNER JOIN作为表的连接，用ON子句给定连接条件。INNER JOIN语句在性能上其他语句没有性能优势。

实例：查询雇员ID为202的雇员名字，部门名称，以及工作的城市。

SQL> select e.last_name,d.department_name,l.city from employees e inner join departments d on e.department_id=d.department_id inner join locations l on d.location_id=l.location_id where e.employee_id=202;

在内连接中使用USING子句定义等值连接条件

SQL> select e.last_name,d.department_name,l.city from employees e inner join departments d using(department_id) inner join locations l using(location_id) where e.employee_id=202;

小节练习：

1-写一个查询显示所有雇员的last_name、department_number和department_name。

SQL> select e.last_name,department_id,d.department_name from employees e join departments d using(department_id);

2-查询部门编号80中的所有工作岗位的唯一列表，在输出中包括部门编号、地点编号。

SQL> select distinct e.job_id,department_id,d.location_id from employees e inner join departments d using(department_id) where department_id=80;

3-写一个查询显示所有有佣金的雇员的last_name、department_name、location_id和城市。

SQL> select e.last_name,d.department_name,location_id from employees e inner join departments d using(department_id) inner join locations l using(location_id) where e.commission_pct is not null;

4-显示所有在其last_name中有一个小写a的雇员的last_name和department_name。

SQL> select e.last_name,d.department_name from employees e inner join departments d using(department_id) where e.last_name like '%a%';

5-使用内连接写一个查询显示那些工作在Toronto的所有雇员的last_name,job_id,department_id和department_name。

SQL> select e.last_name,e.job_id,department_id,d.department_name from employees e inner join departments d using(department_id) inner join locations l using(location_id) where l.city='Toronto';

6-显示雇员的last_name和employee_id连同他们的经理的last_name和employee_id，列标签分别为Employee、Emp#、Manager和Mgr#。

SQL> select emp.last_name "Employee",emp.employee_id "Emp#",mgr.last_name "Manager",mgr.employee_id "Mgr#" from employees emp,employees mgr where emp.manager_id=mgr.employee_id;

7-查询所有雇员的经理包括King,他没有经理。显示雇员的名字、雇员ID、经理名、经理ID、用雇员号拍序结果。

SQL> select emp.last_name,emp.employee_id,mgr.last_name,mgr.employee_id from employees emp left outer join employees mgr on(emp.manager_id = mgr.employee_id) order by emp.employee_id;

8-创建一个查询显示所有与被指定雇员工作在同一部门的雇员（同事）的last_name，department_id。给每列一个适当的标签。

SQL> select e.last_name Name,e.department_id dep from employees e,employees c where e.department_id=c.department_id and e.employee_id<>c.employee_id;

9-显示JOB_GRADES表的结构。创建一个查询显示所有雇员的name,job,department name,salary和grade。

SQL> select e.last_name,e.job_id,d.department_name,e.salary,j.gra from employees e,departments d,job_grades j where e.department_id=d.department_id and e.salary between j.lowest_sal and j.highest_sal;

10-创建一个查询显示那些在雇员Davies之后入本公司工作的雇员的name和hire_date。

SQL> select e.last_name,e.hire_date from employees e,employees d where d.last_name='Davies' and e.hire_date>d.hire_date order by e.hire_date;

11-显示所有雇员的name和hire_date，他们在他们的经理之前进入本公司，连同他们的经理的名字和受雇日期一起显示。列标签分别为Employee、Emp Hired、Manager和Mgr Hired。

SQL> select e.last_name "Employee",e.hire_date "Emp Hired",m.last_name "Manager",m.hire_date "Mgr Hired" from employees e,employees m where e.manager_id=m.employee_id and e.hire_date<m.hire_date;

8. 组函数介绍

(1) 什么是组函数？

也被称为聚合函数。组函数操作行集，给出每组的结果。组函数不像单行函数，组函数对行的集合进行操作，对每组给出一个结果。这些集合可能是整个表或者是表分成的组。

(2) 组函数与单行函数的区别是什么？

单行函数对查询到的每个结果集做处理，而组函数只对分组数据做处理。单行函数对每个结果集返回一个结果，而组函数对每个分组返回一个结果。

(3) Oracle中有哪些组函数？

AVG 平均值

COUNT 计数

MAX 最大值

MIN 最小值

SUM 合计

组函数的语法：

SELECT [column,] group function(column),...

FROM table

[WHERE condition]

[GROUP BY column]

[GROUP BY column];

(4) 每个组函数的作用是什么？

AVG(arg)函数：对分组数据做平均值运算。

Arg:参数类型只能是数字类型。

SUM(arg)函数：对分组数据求和。

Arg:参数类型只能是数字类型。

MIN(arg)函数：求分组中的最小数据。

Arg:参数类型可以是字符、数字、日期。

MAX(arg)函数：求分组中最大数据。

Arg:参数类型可以是字符、数字、日期。

COUNT函数：返回一个表中的行数。

COUNT函数有三种格式：

COUNT(*):返回表中满足SELECT语句标准的行数，包括重复行，包括有空值列的行。如果WHERE子句包括在SELECT语句中，COUNT(*)返回满足WHERE子句条件的行数。

实例：返回查询结果的总条数。

SQL> select count(*) from employees where salary>10000;

COUNT(expr)：返回在列中的有expr指定的非空值的数。

COUNT(DISTINCT expr)：使用DISTINCT关键字禁止计算在一列中重复值。

(5) 使用组函数的原则是什么？

用于函数的参数的数据类型可以是CHAR,VARCHAR2,NUMBER或DATE；所有组函数忽略空值。为了用一个值代替空值，用NVL、NVL2或COALESCE函数。

9. 分组函数的使用

(1) 求雇员表中的的平均薪水与薪水总额。

SQL> select round(avg(salary),2),sum(salary) from employees;

(2) 求雇员表中的最高薪水与最低薪水。

SQL> select min(salary),max(salary) from employees;

(3) 显示部门 80 中有佣金的雇员人数。

SQL> select count(e.commission_pct) from employees e where e.department_id=80;

(4) 显示 EMPLOYEES 表中不重复的部门数。

SQL> select count(distinct department_id) from employees;

10. 组函数和 Null 值

(1) 组函数中如何处理空值？

所有组函数忽略列中的空值。

在组函数中使用NVL函数来处理掉空值。

(2) 计算有佣金的员工的佣金平均值。

SQL> select avg(commission_pct) from employees;

(3) 计算所有员工的佣金的平均值。

SQL> select avg(nvl(commission_pct,0)) from employees;

11. 什么是创建分组

(1) 什么是创建数据组？

可以根据需要将查询到的结果集信息划分为较小的组，用GROUP BY子句实现。

GROUP BY子句语法

SELECT column,group_function(column)

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[ORDER BY column]

(2) 使用分组的原则是什么？

如果在SELECT子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在GROUP BY子句中。如果未能在GROUP BY子句中包含一个字段列表，你会收到一个错误信息；

使用WHERE子句，你可以在划分行成组以前过滤行；

在GROUP BY子句中必须包含列；

在GROUP BY子句中你不能使用列别名；

默认情况下，行以包含在GROUP BY列表中的字段的升序排序。可以用ORDER BY子句覆盖这个默认值。

12. GROUP BY 子句的使用

(1) GROUP BY子句执行顺序是什么？

我们可以根据自己的需要对数据进行分组，在分组时，只要将需要做分组的列的列名添加到GROUP BY子句后侧就可以。GROUP BY列不必再SELECT列表中。

SELECT AVG(salary) FROM employees GROUP BY department_id;

执行顺序：先进行数据查询，再对数据进行分组，然后执行组函数。

非法使用GROUP函数的查询：在SELECT列表中的任何列必须在GROUP BY子句中；在GROUP BY子句中的列或表达式不必在SELECT列表中。

错误示例：

Select department_id,count(last_name) from employees;

Select department_id,count(last_name)

ERROR at line 1:

ORA-00937: not a single-group group function

(2) 求每个部门的平均薪水

SQL> select department_id,avg(salary) from employees group by department_id;

(3) 显示在每个部门中付给每个工作岗位的合计薪水的报告。

SQL> select department_id,job_id,sum(salary) from employees group by department_id,job_id;

13. 约束分组结果(HAVING子句)

(1) 什么是HAVING子句？

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。having也可放在group之前，where之后，但不建议。建议放在group by语句之后方便阅读。

HAVING子句语法：

显示那些最高薪水大于10000的部门的部门号和最高薪水。

Select department_id,max(salary) from employees group by department_id having max(salary)>10000;

查询那些最高薪水大于10000的部门的部门号和平均薪水。

SQL> select department_id,avg(salary),max(salary) from employees group by department_id;

14. 嵌套组函数

(1) 显示部门中的最大平均薪水。

嵌套组函数：在使用组函数时我们也可以根据需要来做组函数的嵌套使用。

SQL> select max(avg(salary)) from employees group by department_id;

组函数小节练习：

1-组函数在多行上计算，对每个组产生一个结果。 T

2-组函数在计算中包含空值。 F

组函数会忽略空值，如果需要空值进行参与计算，需要使用NVL函数处理空值。

3-在分组计算中，WHERE子句对行的限制在计算的前面。 T

4-显示所有雇员的最高，最低，合计和平均薪水，列标签分别为Maximum,Minimum,Sum和Average。四舍五入结果为最近的整数。

SQL> select max(salary) "Maximum",min(salary) "Minimum",sum(salary) "Sum",round(avg(salary)) "Average" from employees;

5-修改上题显示每种工作类型的最低、最高、合计和平均薪水。

SQL> select job_id,max(salary) "Maximum",min(salary) "Minimum",sum(salary) "Sum",round(avg(salary)) "Average" from employees group by job_id;

6-写一个查询显示每一工作岗位的人数。

SQL> select job_id,count(employee_id) from employees group by job_id;

7-确定经理人数，不需要列出他们，列标签是Number of Managers。

SQL> select count(distinct manager_id) "Number of Managers" from employees;

8-写一个查询显示最高薪水和最低薪水之间的差，列标签是DIFFERENCE。

SQL> select max(salary)-min(salary) difference from employees;

9-显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于6000的组。按薪水降序排序输出。

SQL> select manager_id,min(salary) from employees where manager_id is not null group by manager_id having min(salary)>6000 order by min(salary) desc;

10-写一个查询显示每个部门的名字，地点，人数和部门中所有雇员的平均薪资。四舍五入薪水到两位小数。

SQL> select d.department_name,l.city,count(e.employee_id),round(avg(e.salary),2) from employees e inner join departments d on(e.department_id=d.department_id) inner join locations l on(d.location_id=l.location_id) group by d.department_name,l.city;

注：要想在投影中显示出来，必须在group子句中做为列分组。

11-创建一个查询显示雇员总数，和在2001,2002,2003,2004受雇的雇员人数。创建适当的列标签。

SQL> select count(*) total,sum(decode(to_char(hire_date,'yyyy'),'2001',1,0)) "2001",sum(decode(to_char(hire_date,'yyyy'),'2002',1,0)) "2002",sum(decode(to_char(hire_date,'yyyy'),'2003',1,0)) "2003",sum(decode(to_char(hire_date,'yyyy'),'2004',1,0)) "2004" from employees;

或者：

SQL> select to_char(hire_date,'yyyy') year,count(employee_id) num from employees group by to_char(hire_date,'yyyy') having to_char(hire_date,'yyyy') between '2000' and '2009' order by to_char(hire_date,'yyyy');

12-创建一个混合查询显示工作岗位和工作岗位的薪水合计，并且合计部门20，50,80,90的工作岗位的薪水。给每个列一个恰当的列标题。

SQL> select job_id,sum(salary),sum(decode(department_id,20,salary)) "Dep20",sum(decode(department_id,50,salary)) "Dep50",sum(decode(department_id,80,salary)) "Dep80",sum(decode(department_id,90,salary)) "Dep90" from employees group by job_id;