Oracle——SELECT语句

本问介绍了以下内容：

• 执行单表SELECT语句和多表的SELECT语句
• 在Oracle数据库内部使用行标识符rowid来访问一行
• 在SQL语句中执行算术运算
• 对日期进行加减法操作
• 使用别名来引用表和列，列需要使用AS关键字
• 使用连接操作符(||)合并列输出
• 使用空值来表示未知值
• 使用DISTINCT操作禁止显示重复行
• 使用WHERE子句限制返回结果
• 使用ORDER BY子句对结果进行排序
• 使用SQL/86和SQL/92的标准语法执行内连接、外连接和自连接。Oracle 9i及以上采用SQL/92标准的语法

一、使用SELECT语句从一个或多个表中检索信息

1.1 对单表

SELECT customer_id,first_name
FROM customers;

SELECT *
FROM customers;

二、使用算术表达式进行计算

2.1 伪列rowid

Oracle数据库中的每一行都有一个唯一的行标识符rowid
rowid是以64为基数的。并且这个列只在数据库内部使用，它是一个伪列。

SELECT rowid
FROM customers;

2.2 算术表达式

Oracle在SQL语句中使用算术表达式来进行算数运算，包括±*/。
算术表达式由两个操作数(数字或日期)和一个操作符组成。

SELECT 2*6
FROM dual;

其中2*6就是一个表达式，表达式可以包括列、值和操作符的组合。
dual表：这个表中只包含如下结构（在command窗口中使用DESCRIBE 命令，可以显示表的结构）
可以由下图看到，dual表只包含了一行。

2.3 日期运算

对日期进行加法和减法运算。
TO_DATE()是一个函数，其功能是将一个字符串转换为日期。
下面一个是对日期进行加法，一个是计算两个日期之间相差的天数

SELECT TO_DATE('2003-07-31','YYYY-MM-DD') + 2
FROM dual;

SELECT TO_DATE('2003-08-02','YYYY-MM-DD') - TO_DATE('2003-07-31','YYYY-MM-DD')
FROM dual;

2.4 列运算

操作数不一定必须是数字或日期，也可以是一个表中的列。

SELECT name, price + 2
FROM products;

2.5 使用列别名

在使用表达式时，Oracle会去掉表达式中的空格，并将其作为标题，我们只能使用由Oracle生成的标题
也可以使用别名指定标题，其中as可以省略
如果希望在别名中使用空格，并保留空格。就必须使用双引号("")将别名正文引起来。

SELECT customer_id as id
FROM customers;

SELECT 2*6 as result
FROM dual;

SELECT 2*6 as "compute result"
FROM dual;

2.6 使用串联操作合并列的输出结果

可以使用连接操作符(||)实现。

SELECT first_name || ' ' || last_name AS "Customer Name"
FROM customers;

2.7 空值

空值并不是一个空字符串，而是一个特殊的值，表示数据库中未知的值。
空值表示该列的值未知

SELECT *
FROM customers
WHERE dob IS NULL;

空值不会显示任何内容，那么在检索所有的行时如何区分空值和空字符串？
答：使用Oracle内置的一个函数：NVL()
NVL()可以将空值转换成另外一个可以真正理解的值。
它接收两个参数：列(返回一个值的任意表达式)和一个值。
如果第一个参数是空值，就将其替换成第二个参数的值。
要记得使用AS来替换列名

SELECT customer_id, first_name, last_name,
NVL(phone, 'Unknown phone number') AS PHONE_NUMBER
FROM customers;

NVL()函数除了用于转换包含空值的字符串列之外，还可以用于转换数字和日期列。

SELECT customer_id, first_name, last_name,
NVL(dob, to_date('2000-01-01','YYYY-MM-DD')) AS DOB
FROM customers;

2.8 禁止显示重复行

查询结果中出现多个重复行，该怎么避免呢？
使用DISTINCT关键字：用于禁止显示上一个查询中重复的行

SELECT DISTINCT purchased_by
FROM purchases;

2.9 使用WHERE子句过滤行

select …
from
where …;

操作符	说明
=	等于
<>或 !=	不等于
<	小于
>	大于
<=	小于等于
=>	大于等于
ANY	与一个列表中的任何值进行比较
ALL	与一个列表中的所有值进行比较

SELECT语句使用ANY操作符从customers表中检索customer_id列大于2、3或4中任何一个值的行。

SELECT *
FROM customers
WHERE customer_id > ANY(2, 3, 4);

SELECT语句使用ALL操作符从customers表中检索customer_id列比2、3和4都大的行。

SELECT *
FROM customers
WHERE customer_id > ALL(2, 3, 4);

使用SQL操作符

操作符	说明
LIKE （NOT LIKE）	匹配字符串中的模式
IN （NOT IN）	匹配值列表
BETWEEN （NOT BETWEEN ）	匹配值范围
IS NULL（IS NOT NULL）	匹配空值
IS NAN （IS NOT NAN）	匹配NaN这个特殊值，意思是“非数字”
IS INFINITE （IS NOT INFINITE ）	匹配无穷的BINARY_FLOAT和BINARY_DOUBLE值

• 使用LIKE操作符
  LIKE用于查看一个文本列中的字符是否匹配指定的模式。
  模式可以使用普通字符和以下两个通配符的组合指定。
  — 下划线(_)匹配指定位置上的一个字符
  — 百分号字符(%)匹配从指定位置开始任意个字符

SELECT *
FROM customers
WHERE first_name LIKE '_o%';

如果需要对一个字符串中的下划线或百分号字符进行文本匹配，可以使用ESCAPE选项。
下面是检索名称中包含字符串“a_product”的所有产品

SELECT first_name
FROM customers
WHERE first_name LIKE '%a\_product%' ESCAPE '\';

• 使用IN操作符
  IN用于检索其列值在指定的值列表中的行。

下面是从customers表中检索customer_id列的值为2，3或5的记录

SELECT *
FROM customers
WHERE customer_id IN (2, 3, 5);

• BETWEEN操作符

BETWEEN用于检索列值包含在指定区间内的行。

SELECT *
FROM customers
WHERE customer_id BETWEEN 1 AND 3;

---------------------------------------------------------------------------------------

使用逻辑操作符

操作符	说明
x AND y	当x和y都为true时，返回true
x OR y	当x和y中有一个为true时，就返回true
NOT x	取反

----------------------------------------------------------------------

操作符的优先级
比较操作符 > AND > OR，可以使用（）来改变优先级顺序
下面从customers表中检索符合以下两个条件之一的行：
dob列的值大于1970年1月1日
customer_id列的值小于2，而且电话号码是以1211结尾

SELECT *
FROM customers
WHERE dob > to_date('1970-1-1'，'YYYY-MM-DD')
OR customer_id < 2
AND phone LIKE '%1211';

2.10 使用ORDER BY子句对行进行排序

ORDER BY子句用于对数据库中检索处的行进行排序，默认情况升序排序（可以使用DESC关键字进行降序，也可以使用ASC关键字显示地说明采用升序）
它可以指定一列或多列，而且必须位于FROM或WHERE子句之后。
下面对customers表中获得的数据根据first_name列的值进行升序排序，根据last_name列的值进行降序排序

SELECT *
FROM customers
ORDER BY first_name ASC, last_name DESC;

ORDER BY子句中，也可以根据列的次序指定对那一列进行排序：1表示按第1列进行排序，2表示按第2列。。。

SELECT *
FROM customers
ORDER BY 2;

2.11 执行使用两个表的SELECT语句

SELECT products.name, product_types.name
FROM products, product_types
WHERE products.product_type_id = product_types.product_type_id;

2.12 使用表别名

下面使用p作为products表的别名，pt作为product_types表的别名
表别名可以让查询语句具备更好的可读性，避免了重复输入表名。

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id;

2.13 笛卡尔积

笛卡尔积：如果在多表查询中不指定连接条件，就会导致一个表中的所有行都连接到另外一个表中的所有行上。

2.14 执行使用多于两个表的SELECT语句

连接可以用于连接任意多个表，WHERE子句中需要的连接个数 = 查询中使用的表的总个数减去1
例子：查询以下信息：已经购买过产品的顾客、顾客的姓名、顾客购买的产品名、产品类型名

SELECT c.first_name, c.last_name, p.name AS product_name, pt.name AS product_type
FROM customers c, purchases pu, products p, product_types pt
WHERE c.customer_id = pu.customer_id 
AND pu.product_id = p.product_id
AND p.product_type_id = pt.product_type_id;

2.15 理解连接条件和连接类型

2.15.1 可以看作笛卡尔积中符合WHERE子句中的条件的连接

连接条件可以分为两类：

• 等连接：在连接中使用操作符=。
• 不等连接：在连接中使用除等号之外的操作符，例如>、<、BETWEEN等

除了连接条件的区分外，连接本身也有3种不同的类型：

• 内连接：只有当连接中的列包含满足连接条件的值时才会返回一行。（如果某一行的连接条件中的一列是空值，那么这行就不会返回）
• 外连接：即使连接条件中的一列包含空值也会返回一行
• 自连接：返回连接到同一个表中的行

2.15.2 不等连接

SELECT e.first_name, e.last_name, e.title, e.salary,sg.salary_grade_id, sg.low_salary,sg.high_salary
FROM employees e, salary_grades sg
WHERE e.salary BETWEEN sg.low_salary AND sg.high_salary;

2.15.3 外连接
即使连接中的列包含一个空值，外连接也会返回一行。
在连接条件中可以使用外连接操作符来执行一个外连接，外连接操作符：(+)
下面举例说明，加上外连接操作符后，列中包含空值的也显示了

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id (+);

虽然外连接符可以放在连接操作符的任意一边
但是通常都应该将其放在想检索的行中包含空值的列相反的一边

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id (+) = pt.product_type_id;

外连接分为

• 右外连接：WHERE table1.column1 (+) = table2.column2; table1中包含column1为空值的一行
• 左外连接：WHERE table1.column1 = table2.column2 (+) ; table2中包含column2为空值的一行

外连接的限制

1. 只能在连接的一段使用外连接操作符，而不能在两端同时使用外连接操作符。
2. 不能同时使用外连接条件和IN操作符
3. 不能同时使用一个外连接条件和另外一个OR操作符的连接条件
4. 。。。。。。

2.15.3 自连接
自连接是对同一个表进行的连接。要执行一个自连接，必须使用不同的表别名来标识在查询中每次对表的引用。
下面举例：employees表中包含了员工的信息。其中manager_id列包含了员工的管理者的employee_id
查询显示每个雇员及其管理者的名字

SELECT e.first_name || ' ' || e.last_name || ' works for '||
m.first_name || ' ' || m.last_name AS work
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

2.16 使用SQL/92语法执行连接

前面使用的都是Oracle语法，它的基础是ANSI SQL/86标准。在开发Oracle 9i时，数据库还实现了ANSI SQL/92标准的连接语法。www.ansi.org

2.16.1 使用SQL/92标准语法执行两个表的内连接

SQL/92引入了INNER JOIN和ON子句来执行内连接
将下面第一个SELECT语句进行重写，得到第二个SELECT

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id;

SELECT p.name, pt.name
FROM products p INNER JOIN product_types pt
ON p.product_type_id = pt.product_type_id;

不等连接操作符和ON子句可以同时使用。
将下面第一个SELECT语句进行重写，得到第二个SELECT

SELECT e.first_name, e.last_name, e.title, e.salary,sg.salary_grade_id
FROM employees e, salary_grades sg
WHERE e.salary BETWEEN sg.low_salary AND sg.high_salary;

SELECT e.first_name, e.last_name, e.title, e.salary,sg.salary_grade_id
FROM employees e INNER JOIN salary_grades sg
ON e.salary BETWEEN sg.low_salary AND sg.high_salary;

2.16.2 使用USING关键字简化连接

SQL/92标准可以使用USING子句对连接条件进一步进行简化，但是要满足以下限制条件：

1. 查询必须是等连接的
2. 等连接中的列必须同名

将下面第一个SELECT语句进行重写，得到第二个SELECT

SELECT p.name, pt.name
FROM products p INNER JOIN product_types pt
ON p.product_type_id = pt.product_type_id;

SELECT p.name, pt.name
FROM products p INNER JOIN product_types pt
USING（product_type_id）;

此时，如果希望查看product_type_id，在查询语句中只能指定该列名，不能使用表名或别名，可以看到第一个查询语句正确，第二个出错。
USING子句中也只能单独使用列名，不能使用表名或别名。
正确：USING（product_type_id） 错误：USING（p.product_type_id）

SELECT p.name, pt.name，product_type_id
FROM products p INNER JOIN product_types pt
USING（product_type_id）;

SELECT p.name, pt.name，p.product_type_id
FROM products p INNER JOIN product_types pt
USING（product_type_id）;

2.16.3 使用SQL/92执行多于两个以上表的内连接

将第一个查询语句进行重写，得到第二个查询语句

SELECT c.first_name, c.last_name, p.name AS product_name, pt.name AS product_type
FROM customers c, purchases pu, products p, product_types pt
WHERE c.customer_id = pu.customer_id 
AND pu.product_id = p.product_id
AND p.product_type_id = pt.product_type_id;

SELECT c.first_name, c.last_name, p.name AS product_name, pt.name AS product_type
FROM customers c INNER JOIN purchases pu
USING(customer_id)
INNER JOIN products p
USING(product_id)
INNER JOIN product_types pt
USING(product_type_id);

2.16.3 使用SQL/92执行多列的内连接

如果连接使用两个表中的多个列，那么就可以在ON子句中使用AND操作符逐一列出这些列。

SELECT ...
FROM table1 INNER JOIN table2
ON table1.column1 = table2.column1
AND table1.column2 = table2.column2;

下面使用USING进行简化：条件是执行等连接，而且列名相同。

SELECT ...
FROM table1 INNER JOIN table2
USING (column1 , column2);

2.16.5 使用SQL/92执行外连接

之前使用外连接符(+)执行外连接，SQL/92使用一个不同的语法来执行外连接。如下：
FROM table1 { LEFT | RIGHT | FULL } OUTER JOIN table2
table1和table2指定了希望连接的表
LEFT：执行左连接 RIGHT：执行右连接
FULL：执行全连接；全连接使用两个表中所有的行，包括连接列为空值的行

1. 使用SQL/92 执行左外连接

使用LEFT OUTER JOIN对第一个查询语句进行重写，得到第二个查询语句

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id (+);

SELECT p.name, pt.name
FROM products p LEFT OUTER JOIN product_types pt
USING(product_type_id);

2. 使用SQL/92 执行右外连接

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id (+) = pt.product_type_id;

SELECT p.name, pt.name
FROM products p RIGHT OUTER JOIN product_types pt
USING(product_type_id);

3. 使用SQL/92 执行全外连接

全外连接使用连接的表中所有的行，包括连接中使用的列为空值的那些行。FULL OUTER JOIN

SELECT p.name, pt.name
FROM products p FULL OUTER JOIN product_types pt
USING(product_type_id);

2.16.6 使用SQL/92执行自连接

使用INNER JOIN和ON对第一个查询语句进行重写，得到第二个查询语句

SELECT e.last_name || ' works for '|| m.last_name AS work
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

SELECT e.last_name || ' works for '|| m.last_name AS work
FROM employees e INNER JOIN employees m
ON e.manager_id = m.employee_id;

2.16.7 使用SQL/92执行交叉连接

如果省略两个表之间的连接条件，会导致笛卡尔积。
使用SQL/92的连接语法，可以避免笛卡尔积，因为在对表进行连接时，通常都必须提供一个ON或USING子句。因为通常都不希望得到笛卡尔积
如果想要使用笛卡尔积，SQL/92要求必须在查询中使用CROSS JOIN关键字显式地进行声明

SELECT *
FROM product_types CROSS JOIN products;