oracle数据查询(二)

HAVING 子句通常与GROUP BY 子句一起使用，在完成对分组结果统计后，可以使用

HAVING 子句对分组的结果做进一步的筛选。如果没有GROUP BY子句，HAVING

子句的功能与WHERE子句一样，HAVING和WHERE的相似之处是都定义搜索条件。

只是HAVING和组有关，WHERE子句与单个的行有关。

1、select子句中使用了GROUP BY 子句：HAVING用于GROUP BY子句创建的组。

2、如果有WHERE子句而没有GROUP BY子句，HAVING子句将用于WHERE子句

输出，并且整个输出被看作一个组。

3、如果既没有WHERE子句也没有GROUP BY子句，那么HAVING子句将用于FROM

子句的输出，并且将其看作是一个组。

提示：记住select子句的处理顺序：在select子句中先由FROM找到数据表，WHERE

子句接收FROM子句的输出，而HAVING子句则接收来自GROUP BY、WHERE 或FROM子句的输入。 例如：

列出平均薪资大于10000的统计信息：

.select job_id,avg(ralary),sum(ralary),max(ralary),count(*) from employees group by job_id having avg(salary)>10000;

多表连接查询

1、简单连接：

简单连接使用逗号将两个或多个表进行连接，这是最简单的也是最常用的多表

查询形式。 （1）基本形式：

简单连接仅是select子句和where子句来连接多个表。其结果是一个通过

笛卡尔积所产生的表，就是由基表中的每一行与另一个基表中的每一行连在

一起所生成的表，查询结果的行数是两个基表行数的积。 （2）、条件限定：

在实际需求中，由于笛卡尔积中包含了大量的冗余信息，这在一般情况下

毫无意义。为了避免这种情况的出现，通常在select语句中提供了一个连接

条件过滤其中无意义的数据，从而满足需求。在使用where子句限定时，只有

第一个表中的列与第二个表中相应列相互匹配后才能在结果集中显示。这是连

接查询最常用的形式。 如：

.select employee_id,last_name,department_name,from employees,

departments where employees.department_id=departments.department_id; （3）、表别名：

表别名是在FROM子句中用于各个表的“简短名称”，它们可以唯一的标识数据

源。 如：

.select em.employee_id,em.last_name,dep.department_name form employees em,department dep where em.department_name=

Dep.department_id and dep.department_name='Shipping'; 注意：如果为表指定了别名，那么语句中所有的子句都必须使用别名，而不允

许在使用实际的表名。

这里介绍一下select语句中各子句执行的顺序：FROM子句被最先执行。然后

是where子句，最后才是select子句。当在FROM子句中指定表别名之后，表

的真实名称将被替换。同时其他的子句只能使用表的别名来限定列。 2、JOIN连接：

除了使用逗号连接外，oracle还支持使用关键字JOIN连接。使用JOIN连接的语

法格式如下：

FROM join_table1 join_type join_table2 [ON(join_condition)] 其中，JOIN_TABLE指出参与连接操作的表名；JOIN_TYPE指出连接的类型，常用

的连接包括：内连接、自然连接、外连接、自连接。连接查询中的 ON(JOIN_CONDITION)指出连接条件，他由被连接表中的列和比较运算符、逻辑运算

符等构成。 （1）、内连接：

关键字：INNER JOIN 。其中，可以省略INNER关键字，而只使用JOIN关键字

表示内连接。内连接件使用比较运算符时，在连接表的某些列之间进行比较操

作并列出表中与连接条件相匹配的数据行。

使用内连接查询多个表时，在FROM子句中除了JOIN 关键字外，还必须

定义一个ON子句，ON子句指定内连接操作列出与连接条件匹配的数据行，它

使用比较运算符比较被连接列值。简单的说就是使用JOIN指定用于连接的两

个表，使用ON指定连接表的连接条件。若进一步指定连接条件，则可以在后

面添加where子句。 如：

.select em.employee_id,em.last_name,dep.department_name form

Employees em inner join department dep on em.department_id =

dep.department_id where em.job_id='AD_ASST'; 提示：使用内连接可以实现两个以上表的查询。

.select em.employee_id,em.last_name,dep.department_name, j.job_title from employees em inner join jobs on em.job_id

Jobs.job_id inner join department dep on em.department_id=

dep.department_id where em.job='IT_PROG'; (2)、自然连接：

自然连接与内连接功能相似，在使用自然连接查询多个表时，oracle会将第

一个表中的那些列与第二个表中具有相同名称的列进行连接。在自然连接中

用户不需要明确指定进行连接的列，系统会自动完成这一任务。 .select em.employe_id,em.first_name,em.last_name ,dep.departmentname

from employees em natural join departments dep where Dep.departmentnames='Sales';

自然连接在实际应用中使用很少，因为他有一个限制条件，即连接的各个表之

间必须具有相同名称的列，而这在实际应用中可能和应用的实际含义发生矛

盾。假设employees表和departments表都有一个address列，则在进行自然

连接时oracle会尝试使用employees和departments的这两个列连接表，这

要求对应的address列相同，但是在应用语义上毫无疑问这两个address列

代表了两个完全不同的含义（一个是雇员的居住地址，一个是部门的所在地

址），这样的连接毫无价值。 （3）、外连接：

使用内连接进行多表查询时，返回的查询结果集中仅包括符合查询条件

（where搜索条件或HAVING条件）和连接条件的行。内连接消除了与另一个

表中的任何行不匹配的行，而外连接扩展了内连接的结果集，除了返回所有

匹配的行外，还会返回一部分或全部不匹配的行，这主要取决与外连接的种类。

外连接分为左外连接（LEFT OUTER JOIN 或 LEFT JOIN）,右外连接

（RIGHT OUTER JOIN 或 RIGHT JOIN）和全外连接（FULL OUTER JOIN 或

FULL JOIN）三种。与内连接不同的是，外连接不只列出与连接条件相匹配的

行，还列出左表（左外连接时）、右表（右外连接时）、或两个表（全外连接时）

中的所有符合搜索条件的数据行。

注意：在外连接的左外连接和右外连接中，要特别注意两个表的位置。

（4）、自连接：

有时候用户可能会有自引用式外键。自引用式外键意味着表中的一个列可以是

该表主键的一个外键。例如：employees表中的manager_id列可以是可以是

另一行的employees_id，因为部门经理也是雇员。通过下面的语句可以看出

Manager_id 和 employees_id 列的关联。

.select employee_id,last_name,job_id,manager_id from employees

order by employee_id;

用户通过自连接，在同一行中看到雇员和部门经理的信息。 .select em1.last_name \"manager\" ,em2.last_name \"employee\" from

Employees em1 left join employees em2 on em1.employee_id =

em2.manager_id order by em1.employee_id;

集合操作

就是将两个或多个sql查询结果合并构成复合查询，以完成一些特殊的任务需求。

集合操作主要由集合操作符实现，常用的集合操作符包括UNION(并运算符)、UNION All

、INTERSECT(交运算)和MINUS(差运算)。 1、UNION:

UNION运算符可以将多个查询结果集相加，形成一个结果集，其结果等同于集合运

算中的并运算，并消除其中重复的行形成一个合集。

例：下面的示例中，第一个查询将选择所有LAST_NAME列以C或者S开头的雇员信

息，第二个查询将会选择所有LAST_NAME列以S或者T开头的雇员信息。其结果是

所有LAST_NAME列以C或者S或者T开头的雇员信息均会被列出。 .select employee_id,last_name from employees where last_name like 'C%'

or last_name like 'S%' union select employee_id,last_name from employees

where last like 'S%' or last_name like 'T%';

注意：UNION运算会将合集中的重复行记录滤除，这是UNION运算和UNION ALL

运算唯一不同的地方。 2、UNION ALL:

UNION 和 UNION ALL语句的工作方式基本相同，不同之处是UNION ALL操作符形

成的结果集中包含有两个子结果集中的重复行。 3、INTERSECT：

INTERSECT操作符也用于对两个sql语句所产生的结果集进行处理。不同之处是

UNION 基本上是一个OR运算，而INTERSECT则比较像AND。 4、MINUS：

MINUS集合运算符可以找到两个给定的集合之间的差集，也就是说该集合操作符会

返回所有从第一个查询中返回的，但是没有在第二个查询中返回的记录。

例如：以下面的查询语句为例，使用运算符MINUS求两个查询的差集。第一个

查询会返回所有LAST_NAME以C或S开头的雇员，而第二个查询会返回所有

LAST_NAME 以 S 和 T 开头的雇员。因此，两个查询结果集的MINUS操作将返回

LAST_NAME以C开头的那些雇员。

.select employee_id,last_name from employees where last_name like

'C%' or last_name like 'S%' minus

select employee_id,last_name from employees where last_name like 'S%'

or last_name like 'T%';

说明：在使用集合操作编写符合查询时，其规则包括：

1、在构成复合查询的各个查询中，各select语句指定的列必须在数量和数据

类型上相匹配。

2、不允许在构成复合查询的各个查询中规定ORDER BY子句。 3、不允许在BLOB、LONG这样的大数据类型对象上使用集合操作符。

子查询

子查询是一个select语句，它可以在select、insert、update、或delete 语

句中使

用。

1、IN关键字：

使用IN关键字可以将原表中特定列的值与子查询返回的结果集中的值进行比较，

如果某行的特定列的值存在，则在select语句的查询结果中就包含这一行。

例如：使用子查询查看所有部门在某一地区（1700）的雇员信息。 .select employee_id,last_name,department_id form employees where

.department_id in(select department_id from departments where

location_id = 1700); 2、EXISTS关键字：

在一些情况下，只需要考虑是否满足条件，而数据本身并不重要，这时就可以使用

EXISTS关键字来定义子查询。EXISTS关键字只注重子查询是否返回行，如果子查

询返回一个或多个行，那么EXISTS便返回TRUE，否则为FALSE。 例如：

.select employee_id,last_name from employees em where exists(

Select * from departments dep where em.department_id = dep.department_id

And location_id = 1700);

在该句中，外层的select语句返回的每一行数据都要由子查询来评估。如果EXISTS

关键字中指定的条件为真，查询结果就会包含这一行；否则该行被丢弃。因此，

整个查询的结果取决于内层的子查询。

提示：由于EXISTS关键字的返回值取决于查询是否会返回行，而不是取决于这些

行的内容，因此对子查询来说，输出列表无关紧要，可使用“*”代替。 3、比较运算符：

如果可以确定子查询返回的结果只包含一个单值，那么可以直接使用比较运算符

连接子查询。经常使用的比较运算符包括等于（=）、不等于（<>），小于（<）、

大于（>）、小于等于（<=）、大于等于（>=）。 如：

.select employee_id.last_name,job_id,salary from employees where job_id=

'PU_MAN' and salary >= (select avg(salary) from employees where job_id

='PU_MAN');