Oracle数据库多表连接查询操作以及查询操作的补充

一、查询语句概述

1、查询语句基本语法格式

SELECT [ ALL | DISTINCT ] [ * | [table.* | expr[alias] | view.*] [, [ table.* | expr[alias]]]...]
	FROM table [ alias ][ ,table[ alias ] ]...
	[ WHERE condition]
	[ GROUP BY expr [, expr] ...] 
	[ HAVING condition]
	[ ORDER BY expression  [ ASC | DESC ] ]

2、伪表和伪劣

1）伪表dual

SQL的标准语法规定，查询语句必须至少有2个子句，即SELECT子句和FROM子句， Oracle数据库中就提供了一个表以便从语法上支持查询语句的完成,即dual表。

2）伪列

Oracle数据库为了增强其功能，提供了一组数据列，这些列是由Oracle数据库自动创建的，从形式上看这些列与表的普通列没有什么区别，但实际上它们并不存储在表中，可以使用查询语句从这些列中查询到数据，但是不能对其进行插入、更新或删除操作，因为这些列不是真实的存在于表中，因此称之为伪列。

二、单表查询

1、select子句

1）查询全部列的数据

在SELECT后使用星号（*）表示将查询FROM子句中指定表或视图的所有列。

如果想保证效率，不要使用星号

SELECT * FROM diploma; 

 --等同于SELECT diplpma_id, diplpma_name FROM diploma

2）查询指定列的数据

在SELECT子句后加上表或视图的列名可以从表或视图的指定列中查询数据。

SELECT t_id, t_name, t_entertime, t_duty, t_research FROM teacher;

3）使用内置函数

在SELECT子句中使用Oracle数据库的内置函数，可以对结果列进行处理。

SELECT s_id, s_name, s_classname, NVL(s_duty, '没有职务') FROM student;

4）使用算数表达式

在SELECT子句中也可以使用算数表达式，以实现对一列或多列的计算。

SELECT s_id, s_name, s_classname, s_chinese+ s_math + s_foreign FROM student;

5）使用常量和连接运算符

在SELECT子句中使用常量可以使结果具有一定的含义，常量与表的列之间通常使用双竖线（||）来做为连接运算符，以更清楚地表达返回结果的实际意思（此条是Oracle数据库的语法，其它数据库可能有其它规定）。

SELECT '学生学号是：' || s_id 学号,'学生姓名是：' ||s_name 姓名 FROM student;

在SELECT子句中使用常量也有一些特殊的用处，比如下面的代码：

 SELECT 1 FROM student;

1其实就是一个常量，查询到的所有行的值都是这个常量，这里可以将常量看做是一个临时列。从效率上来说，SELECT临时列>SELECT索引列>SELECT普通列>SELECT *，这是因为不用查询Oracle数据库的数据字典表，因此速度比较快。

6）使用列别名

为列起别名的方法有2种：

（1）列名列别名

SELECT t_id 教师编号, t_name 教师姓名, t_research 研究方向 FROM teacher;

（2）列名 AS 列别名

SELECT t_id AS 教师编号, t_name AS 教师姓名, t_research AS 研究方向FROM teacher;

通常情况下，不需要用双引号（""）将列别名括起来，但是以下三种情况，列别名需要使用双引号引起来：

① 列别名中含有空格时。

② 想让别名原样显示时（不用双引号则英文字符全部大写）。

③ 列别名中含有特殊字符时。

2、FROM子句

FROM子句的功能比较简单，作用就是指定查询语句的源表，可以指定多个源表，表名之间用逗号隔开。

可以在FROM子句中为表定义别名，但是在Oracle数据库中不支持使用AS关键字命名表别名的方法。

为表列起别名的方法是：表名表别名

3、WHERE子句

WHERE子句的作用是限定返回的记录，即返回的记录必须满足WHERE子句所指定的条件。

WHERE子句需要使用运算符来完成的查询条件的指定，下表列出了WHERE子句中可以使用的运算符及其含义。

类别	运算符	含义
比较运算符	=	等于
	>	大于
	>=	大于等于
	<	小于
	<=	小于等于
	<>、!=	不等于
范围运算符	BEWTEEN AND	在某个范围内
	NOT BEWTEEN AND	不在某个范围内
包含运算符	IN	在某个集合中
	NOT IN	不在某个集合中
字符匹配运算符	LIKE	与某个文本匹配
	NOT LIKE	与某个文本不匹配
空值判断运算符	IS NULL	是空值
	IS NOT NULL	不是空值
逻辑运算符	AND	逻辑与
	OR	逻辑或
	NOT	逻辑非

1）比较运算符

比较运算符可以对两侧的表达式进行比较。使用比较运算符需要注意以下几点：

字符型及日期时间型的数据需要使用单引号引起来。

日期时间型的数据要求符合格式要求，可以通过查看NLS参数来获取默认的格式信息。

在Oracle数据库中，WHERE子句中的条件值是大小写敏感的。

2）2.BEWTEEN AND和NOT BEWTEEN AND

BEWTEEN AND用于判断指定的条件项是否位于某个范围之内，BEWTEEN之后的表达式指定范围的最小值，AND之后的表达式指定范围的最大值。必须把最小值放在前面，否则查询不到正确的结果。比较的数据类型可以是字符型、数值型和日期时间型。

BEWTEEN AND前后都是闭区间，即BEWTEEN A AND B结构包括A和B在内。

3）IN和NOT IN

用来指定条件的集合，当WHERE子句中的列值等于条件集合中的某个值时，才返回相应的记录。NOT IN表示当WHERE子句中的列值不等于条件集合中的任何一个值时，才返回相应的记录。可以将条件集合中的数据看做是枚举类型的数据，因为这些数据都是离散的，它们可以是字符型、数值型和日期时间型数据，但是集合中所有数据的数据类型必须相同。

4）LIKE和NOT LIKE

LIKE运算符用于字符类型的查询条件的模糊匹配，以实现模糊查询的功能，LIKE运算符中可以使用的通配符如下表所示。

通配符	含义
%（百分号）	用于代替任意数目（可以为0）的任意字符
_（下划线）	用于代替一个任意字符

SQL中提供了对字符进行转义的功能，方法是使用ESCAPE关键字，后接代表转义的字符，语法格式：

[NOT] LIKE ‘模糊查询条件’ ESCAPE '转义字符‘

除了LIKE运算符中需要使用ESCAPE关键字定义转义字符之外，在某些情况下也需要使用转义字符来对特殊字符进行处理。

5）空值判断运算符IS NULL和IS NOT NULL

因为空值不等于任何值，所以不能使用“= NULL”来判断一个列上的值是否等于空值，而只能使用IS NULL或IS NOT NULL来判断一个列上的值是否为空值。

6）逻辑运算符

逻辑运算符包括AND、OR和NOT，其中AND运算符和OR运算符用于连接多个布尔表达式，NOT运算符用于连接单个布尔表达式。

AND运算符的运算规则是：参与运算的多个布尔表达式如果全为真则结果为真，有一个为假则结果为假；空值和TRUE进行与运算，返回空值，空值和FALSE进行与运算，返回FALSE。

OR运算符的运算规则是：参与运算的多个布尔表达式如果全为假则结果为假，有一个为真则结果为真；空值和TRUE进行或运算，返回TRUE，空值和FALSE进行或运算，返回空值。

NOT运算符的运算规则是：如果布尔表达式为真则结果为假，如果布尔表达式为假则结果为真。

7）运算符优先级

优先级排序	运算符类型	运算符举例
1	一元运算符	+（正号），-（负号）
2	算数运算符	*，\，+，-
3	比较运算符	=，<>，<，>，<=，>=
4	其他比较运算符	IS [NOT] NULL，LIKE，[NOT] BETWEEN，[NOT] IN
5	逻辑非	NOT
6	逻辑与	AND
7	逻辑或	OR
8	赋值运算符	=

4、DISTINCT关键字

DISTINCT关键字的作用是在列上筛选出不同的值，即取消重复的行记录。

对于空值，DISTINCT关键字后面指定的列中所有的空值都会被视为是一个值，也就是会将所有的空值压缩成一个值。

distinct后面可以跟多个字段，表示联合唯一去重

5、GROUP BY子句与聚合函数

GROUP BY子句的功能是通过在子句的后面指定的表达式将表中的记录划分成若干个组，然后使用聚合函数对每个组的记录进行数据处理。

聚合函数能够对一组记录中的某个列上的值执行计算，并返回单个值。常用的聚合函数有5个，它们分别是：

1）COUNT函数：用于统计某个列中的记录数。语法格式如下：

COUNT（*）：用于计算数据表的所有记录数。

COUNT（[ALL|DISTINCT] 列名或表达式）：用于计算某个列或表达式的记录数，但是不包括含有空值的行。

2）AVG函数：用于计算某个列或表达式的数值的平均数。语法格式如下：

AVG（[ALL | DISTINCT] 列名或表达式）：其参数表达式应该为数字类型或者能够转化为数字类型的字符类型的数据。

3）SUM函数：用于计算某个列或表达式的数值的总和。语法格式如下：

SUM（[ALL | DISTINCT] 列名或表达式）：其参数表达式应该为数字类型或者能够转化为数字类型的字符类型的数据。

4）MAX函数：用于查找某个列或表达式的数值的最大值。语法格式如下：

MAX（列名或表达式）：其参数表达式可以是数字类型、字符类型或日期类型的数据。

5）MIN函数：用于查找某个列或表达式的数值的最小值。语法格式如下：

MIN（列名或表达式）：其参数表达式可以是数字类型、字符类型或日期类型的数据。

6、HAVING子句

在使用GROUP BY子句分组之后，如果想对已分的组进行有条件的选择，可以使用HAVING子句，其功能就是选择和排除分组，因此HAVING子句又被称为分组筛选语句。当使用HAVING子句时，Oracle数据库按照以下步骤执行：

（1）对记录分组。

（2）在分组的基础上使用聚合函数。

（3）输出与HAVING子句匹配的结果。

HAVING子句与WHERE子句的相同点和区别？（面试题）

HAVING子句与WHERE子句都可以起到过滤数据的作用，但是它们之间有一些区别：

（1）功能不同。这两个子句都具有筛选数据的功能，但是WHERE子句是对所有的数据进行筛选，HAVING子句则是对分组后的数据进行筛选，因此HAVING子句必须与GROUP BY子句才有意义。

（2）使用场合不同。HAVING子句只能用于查询语句中，而WHERE子句则可以用于SELECT、UPDATE、DELETE和INSERT…INTO等语句中。

（3）执行顺序不同。WHERE子句的执行顺序先于HAVING子句，即在对查询结果进行分组前就将不符合WHERE子句条件的记录筛选掉了，然后才会执行GROUP BY子句，再根据HAVING子句的条件筛选分组数据。

（4）条件表达式的要求不同。HAVING子句中的条件表达式要么是GROUP BY子句中指定的分组条件，要么是聚合函数或常量，而WHERE子句中的条件则不受此限制，但是WHERE子句中不能使用聚合函数（实际上还是与执行顺序有关）。

（5）有些时候，HAVING子句可以与WHERE子句互换使用，但是效率有所不同。

7、ORDER BY子句

ORDER BY子句的作用就是对查询的结果集进行排序，以便可以按照用户的需求显示数据。

ORDER BY子句的后面可以是表的列名、列的别名、列的序号和表达式。ASC关键字表示结果按照升序排列，DESC关键字表示结果按照降序排列，默认按照升序对记录进行排序。如果使用了ORDER BY子句，则ORDER BY子句必须是查询语句的最后一个子句。

DESC还可以查询对象表结构：

desc course;

不同数据类型的排序规则（以升序为例）

（1）数值类型的数据按照数字大小的顺序由小到大排列。

（2）日期类型的数据规则是较早的日期在前，较晚的日期在后。例如：“01-1月-11”排在“01-1月-12”之前。

（3）英文字符排列按照字母由小到大的顺序排列，且大写字母排在小写字母的前面，即按照A-Za-z的顺序排列

（4）中文的排序规则。

数据库服务器或客户端使用的字符集决定了使用何种语言，而在Oracle数据库中不同的语言的排序方式各不相同。如果采用的字符集是ZHS16GBK，那么ORDER BY子句默认的是按照汉字的拼音顺序进行排序的；如果采用的是其他字符集（如UTF8），那么汉字的排序是按照BINARY（二进制编码）排序的，即按照编码的大小来排序。

8、查询语句的执行顺序

第一步：执行FROM子句。将FROM子句中的表的记录全部取出来。

第二步：执行WHERE子句。从第一步产生的记录中筛选出满足WHERE子句条件的所有记录。

第三步：执行GROUP BY子句。按照GROUP BY 子句中的分组原则对第二步产生的记录进行分组。

第四步：执行HAVING子句。按照HAVING子句中的分组筛选条件从第三步产生的记录中筛选出满足条件的所有分组的排序规则排序。

第五步：执行SELECT子句。从第四步产生的记录中将SELECT子句中指定的列或表达式全部取出来。

第六步：执行DISTINCT操作。将重复的记录从第五步产生的记录中删除。

第七步：执行ORDER BY子句。将第六步产生的记录按照ORDER BY子句指定的排序规则排序。

三、多表连接查询

1、内连接

内连接（INNER JOIN）又称为相等连接或简单连接，就是当两个或多个表之间存在意义相同列的时候，把这些意义相同的列用“=”运算符连接起来进行比较，只有连接列上值相等的记录才会被作为查询结果返回。其语法格式如下：

SELECT select_list

FROM table1 [alias], table2 [alias]...

WHERE table1.column = table2.column

[ GROUP BY expr [, expr] ...] 

[ HAVING condition]

[ ORDER BY expression [ ASC | DESC ] ]

ANSI为内连接定义了标准的SQL语法，语法格式如下：

SELECT select_list

FROM table1 

[INNER] JOIN table2

ON table1. column = table2.column

WHERE conditions

内连接连接三个数据表的语法格式如下：

SELECT select_list

FROM (table1

[INNER] JOIN table2

ON table1. column = table2.column)

[INNER] JOIN table3

ON table2. column = table3.column)

WHERE conditions

内连接的另外一种形式是使用USING子句，语法格式如下：

SELECT select_list

FROM table1

JOIN table2 USING（column1, column2…）;

table1 是驱动表/主表/左表，table2是匹配表/从表/右表。

面试题：内连接的结果集是怎样的？

驱动表与匹配表之间能互相匹配上的结果组成的集合叫内连接的结果集。

2、外连接

外连接扩充了内连接的功能，把原来被内连接删除的记录根据外连接的类型保留了下来。根据保留数据的来源，外连接分为左外连接、右外连接和全外连接3种。

除了可以使用标准的SQL语法表示外连接外，Oracle数据库中还可以使用“(+)”运算符来表示一个连接是外连接。注意：无论左外连接还是右外连接，“(+)”都要放在没有匹配记录列值就被设置为空值的表的一端。

1）左外连接

左外连接（left outer join）以左表为基准，**即使右表中没有与之相匹配的记录，也将显示左表的所有行，但对于右表来说，只能保留与左表匹配的行，未能找到与左表匹配的记录的列值将被设置为空值。**语法格式如下：

SELECT select_list

FROM table1,table2

WHERE table1.column = table2.column(+)

面试题：左外连接的结果集是怎样的？

左表中的所有数据与右表中可匹配上的数据组成的集合叫做左外连接的结果集。右表上其它未匹配上的数据为空。

ANSI SQL标准的左外连接语法格式如下：

SELECT select_list

FROM table1

LEFT [OUTER] JOIN table2 

ON table1.column = table2.column;

2）右外连接

右外连接（right outer join）以右表为基准，即使左表中没有与之相匹配的记录，也将显示右表的所有行，但对于左表来说，只能保留与右表匹配的行，未能找到与右表匹配的记录的列值将被设置为空值，语法格式如下：

SELECT select_list

FROM table1, table2

WHERE table1.column(+) = table2.column

面试题：右外连接的结果集是怎样的？

右表中的所有数据与左表中可匹配上的数据组成的集合叫做右外连接的结果集。左表上其它未匹配上的数据为空。

ANSI SQL标准的右外连接语法格式如下：

SELECT select_list

FROM table1

RIGHT [OUTER] JOIN table2 

ON table1.column = table2.column;

3）全外连接

全外连接（full outer join）主要功能是返回两个表连接中满足等值连接的记录，以及两个表中所有等值连接失败的记录，也就是说全外连接会把两个表所有的行都显示在结果表中，相当于同时做左外连接和右外连接。

如果使用非标准SQL语句做全外连接查询的话，需要使用UNION运算符将2个连接做集合运算。

ANSI SQL标准的全外连接语法格式如下：

SELECT select_list

FROM table1

FULL [OUTER] JOIN table2 

ON table1.column = table2.column;

面试题：怎样用全外连接、左外连接、右外连接、内连接的结果集相互介绍它们？

全外连接包括左外连接和右外连接
左外连接包括内连接和右外连接没有的那一部分
右外连接包括内连接和左外连接没有的那一部分

3、自然连接

自然连接自动使用两个表中数据类型和值都相同的同名列进行连接，不必为自然连接手动的添加连接条件，此时的效果和内连接的效果相同。

不同之处在于自然连接只能是同名属性的等值连接，而内连接可以使用ON或USING子句来指定连接条件。

面试题：内连接和自然连接的相同点和区别？

相同点：自动使用两个表中数据类型和值都相同的同名列进行连接

不同点：自然连接只能是同名属性的等值连接，而内连接可以使用ON或USING子句来指定连接条件。

自然连接的语法形式如下：

SELECT select_list

FROM table1

NATURAL JOIN table2;

4、笛卡尔积和交叉连接

如果在连接查询中没有指定任何连接条件，那么查询结果将是多个表中所有的记录进行乘积操作得到的结果。

没有使用WHERE子句的连接查询又称为交叉连接（CROSS JOIN），得到的查询的结果也是笛卡尔积。

ANSI SQL标准的交叉连接语法格式如下：

SELECT select_list

FROM table1

CROSS JOIN table2;

5、自连接

自连接（self join）就是一个表自己连接自己以实现获取特定数据的目的。从查询的角度来看，自连接的FROM子句中的表都是同一个表，只是在做连接的时候把它们视为不同的数据源来匹配对应的连接条件。

四、子查询

1、可以使用子查询的语句

子查询通常用于查询语句的WHERE子句中，但是也可以用于查询语句的其他子句中，如SELECT子句、FROM子句（该类子查询又称为内联视图）、GROUP BY子句、HAVING子句、ORDER BY子句或其他子查询中，还可以用于数据操纵语言和数据定义语言中，如INSERT语句、UPDATE语句、DELETE 语句和CREATE TABLE语句中。任何允许使用表达式的地方都可以使用子查询。

2、子查询的分类

1）单行单列子查询

子查询只返回一行数据中某一列上的值，即只返回单个值。如果主查询使用单行单列子查询返回的结果作为查询条件，则应该使用单行运算符进行比较，如>、>=、<、<=、=、<>、!=等。

2）单行多列子查询

单行多列子查询返回一行数据中多列上的值。

3）多行单列子查询

多行单列子查询返回多行数据，但是列数只有一列，如果主查询使用多行单列子查询返回的结果作为查询条件，则应该使用能够处理多个条件的运算符，常用的多条件运算符包括：IN、ANY和ALL。

IN：和前面介绍的功能相同，含有IN运算符的WHERE子句判断比较列或表达式是否与子查询结果中的任意一个值相同，如果相同则返回true，否则返回false。

ANY或SOME：表示任意的含义，即含有ANY或SOME运算符的WHERE子句中的比较列或表达式与子查询结果中的每一个值进行比较，只要有一个值满足则返回true，如果都不满足则返回false。

ALL：表示所有的的含义，即含有ALL运算符的WHERE子句中的比较列或表达式与子查询结果中的每一个值进行比较，只有全部满足才能返回true，否则返回false

4）多行多列子查询

多行多列子查询返回多个行上的多个列值，与单行多列子查询很相似，只是单行子查询只能使用单行比较运算符，多行子查询需要使用多行运算符。

3、相关子查询

在一些子查询中，如果想完成子查询中的查询操作，必须使用外部主查询语句的某些列，这类依赖于外部主查询的子查询被称为相关子查询（Correlated Subquery）。

相关子查询的执行方式与普通的子查询有所不同，相关子查询是重复执行的，为主查询可能选择的每一行均执行一次子查询。

在相关子查询中，使用频率很高的关键字就是EXISTS和NOT EXISTS，它们的功能是用来判断查询的结果集中是否存在元素，EXISTS A表示当结果集A不为空时返回true，为空时返回false；NOT EXISTS A表示当结果集A为空时返回为true，不为空时返回false。NOT EXISTS因为运算方法与NOT IN不同，只会返回true或false，不会返回空值，所以不需要考虑子查询去除空值的问题。

在很多情况下可以将EXISTS和IN、NOT EXISTS和NOT IN互换使用。

面试题：EXISTS和IN、NOT EXISTS和NOT IN是否可以互换？

EXISTS和IN可以互换，但是NOT EXISTS和NOT IN互换是有条件的，条件是表中数据不能有空值。

五、集合运算

1、并集运算

对两个或两个以上的结果集进行并集运算需要使用UNION或UNION ALL运算符。

UNION与UNION ALL的差别是前者会自动去掉并集的重复记录，而后者不会。

进行集合运算时只要2个类的数据类型相近，Oracle数据库可以自动进行类型和长度的转换。

如果使用ORDER BY子句则排序列是第一个查询中定义的列。

华为面试题：区分UNION和UNION ALL。