选择表数据

注：要完成本节中的教程和示例，必须以 SQL Developer 的 HR 用户身份连接到 Oracle Database。有关说明，请参阅从 SQL Developer 以用户 HR 身份连接到 Oracle Database 。

关于查询

查询（或称 SQL SELECT 语句）用于从一个或多个表或视图中选择数据。

最简单形式的查询的语法如下：

SELECT select_list FROM source_list

select_list 值指定要从其中选择数据的列，source_list 值指定包含这些列的表或视图。

嵌套在另一个 SQL 语句内的查询称为子查询。

在 SQL*Plus 环境中，您可以在 SQL> 提示符之后输入查询（或任何其他 SQL 语句）。

在 SQL Developer 环境中，可以在工作表中输入查询（或任何其他 SQL 语句）。

注：在显示查询结果时，除非您使用 ORDER BY 子句指定记录顺序，否则记录是无序排行的。有关详细信息，请参阅“对所选数据进行排序”。

另请参见：

Oracle Database SQL Language Reference（了解有关查询和子查询的详细信息）
Oracle Database SQL Language Reference（了解有关 SELECT 语句的详细信息）
SQL*Plus 用户指南和参考，了解有关 SQL*Plus 命令行界面的更多信息
Oracle SQL Developer User’s Guide（了解有关在 SQL Developer 中使用工作表的信息）

在 SQL Developer 中运行查询

本部分介绍如何使用工作表在 SQL Developer 中运行查询。

注：工作表不仅限于查询；您可以使用它来运行任何 SQL 语句。

在 SQL Developer 中运行查询的步骤：

如果 SQL Developer 的右侧框架显示 hr_conn 窗格：
1. 如果未显示“工作表”子窗格，请选择工作表选项卡。
2. 请转至步骤 4 。
选择 SQL Worksheet 图标。
如果打开“选择连接”窗口：
1. 如果“连接”字段的值不是 hr_conn，请从菜单中选择该值。
2. 选择确定。
此时将显示一个标签为 hr_conn 的窗格，并显示两个子窗格：Worksheet 和 Query Builder。在“Worksheet（工作表）”中，可以输入 SQL 语句。
在工作表内，键入一个查询（SELECT 语句）。
单击图标 Run Statement（运行语句）。

该查询即会运行。在“工作表”下，将显示“查询结果”窗格，其中显示查询结果。
在 hr_conn 选项卡下，单击 Clear（清除）图标。

查询将消失，您可以在工作表中输入另一个 SQL 语句。运行另一个 SQL 语句时，其结果将显示在“查询结果”窗格中，以替换以前运行的 SQL 语句的结果。

另请参见： Oracle SQL Developer User’s Guide（了解有关在 SQL Developer 中使用工作表的信息）

教程：选择表中的所有列

本教程介绍如何选择 EMPLOYEES 表中的所有列。

选择 EMPLOYEES 表所有列的步骤：

如果显示了包含选项卡 hr_conn 的窗格，请选择它。否则，单击 SQL Worksheet 图标，如“在 SQL Developer 中运行查询”中所示。
在“Worksheet（工作表）”中，输入以下查询：

SELECT * FROM EMPLOYEES;
单击 Run Statement（运行语句）图标。

该查询即会运行。此时将显示“Worksheet（工作表）”下的“Query Result（查询结果）”窗格，其中显示了 EMPLOYEES 表的所有列。

注意：对于包含存储敏感数据的列的表（如密码或信用卡信息），请务必小心使用 SELECT *。

另请参阅：“教程：使用 SQL Developer 查看 EMPLOYEES 表属性和数据”了解有关使用 SQL Developer 查看表数据的另一种方式的信息

教程：选择表中的特定列

本教程介绍如何仅选择 EMPLOYEES 表的 FIRST_NAME、LAST_NAME 和 DEPARTMENT_ID 列。

要仅选择 FIRST_NAME、LAST_NAME 和 DEPARTMENT_ID 的步骤：

如果显示了包含选项卡 hr_conn 的窗格，请选择它。否则，请单击“SQL Worksheet”图标，如“在 SQL Developer 中运行查询”中所示。
如果“工作表”窗格包含查询，请通过选择清除图标清除查询。
在“Worksheet（工作表）”中，输入以下查询：

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES;
单击 Run Statement（运行语句）图标。

该查询即会运行。在“工作表”下，将显示“查询结果”窗格，其中显示查询的结果，这些结果与以下文本类似。

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID

-------------------- ------------------------- -------------
Donald               OConnell                             50
Douglas              Grant                                50
Jennifer             Whalen                               10
Michael              Hartstein                            20
Pat                  Fay                                  20
Susan                Mavris                               40
Hermann              Baer                                 70
Shelley              Higgins                             110
William              Gietz                               110
Steven               King                                 90
Neena                Kochhar                              90

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID

-------------------- ------------------------- -------------
Lex                  De Haan                              90
...
Kevin                Feeney                               50

107 rows selected.

在新标题下显示所选列

在显示的查询结果中，默认列标题是列名。要在新标题下显示列，请在列名称之后立即指定新标题 ( alias )。别名将会在查询期间重命名列，但不会在数据库中更改其名称。

示例 2-5 中的查询选择与“教程：选择表的特定列”中的查询相同的列，但它还为这些行指定了别名。由于别名未使用双引号括起来，因此它们显示为大写字母的形式。

如果您用双引号将列别名括起，则保留大小写，并且别名中可以包含空格，如下例所示 Example 2-6 。

另请参见： Oracle Database SQL Language Reference（了解有关 SELECT 语句的详细信息，包括列别名 (c_alias)

示例 2-5 在新标题下显示所选列

SELECT FIRST_NAME First, LAST_NAME last, DEPARTMENT_ID DepT
FROM EMPLOYEES;

结果与以下文本类似：

FIRST                LAST                            DEPT
-------------------- ------------------------- ----------
Donald               OConnell                          50
Douglas              Grant                             50
Jennifer             Whalen                            10
Michael              Hartstein                         20
Pat                  Fay                               20
Susan                Mavris                            40
Hermann              Baer                              70
Shelley              Higgins                          110
William              Gietz                            110
Steven               King                              90
Neena                Kochhar                           90

FIRST                LAST                            DEPT
-------------------- ------------------------- ----------
Lex                  De Haan                           90
...
Kevin                Feeney                            50

107 rows selected.

示例 2-6 在列别名中保留大小写并包含空格

SELECT FIRST_NAME "Given Name", LAST_NAME "Family Name"
FROM EMPLOYEES;

结果与以下文本类似：

Given Name           Family Name
-------------------- -------------------------
Donald               OConnell
Douglas              Grant
Jennifer             Whalen
Michael              Hartstein
Pat                  Fay
Susan                Mavris
Hermann              Baer
Shelley              Higgins
William              Gietz
Steven               King
Neena                Kochhar

Given Name           Family Name
-------------------- -------------------------
Lex                  De Haan
...
Kevin                Feeney

107 rows selected.

选择满足指定条件的数据

要仅选择与指定条件匹配的数据，请在 SELECT 语句中包含 WHERE 子句。

WHERE 子句中的条件可以是任何 SQL 条件（有关 SQL 条件的信息，请参阅 Oracle Database SQL Language Reference）。

示例 2-7 中的查询仅为部门 90 中的员工选择数据。

要仅为部门 100、110 和 120 的雇员选择数据，请使用以下 WHERE 子句：

WHERE DEPARTMENT_ID IN (100, 110, 120);

示例 2-8 中的查询仅为姓氏以“Ma”开头的雇员选择数据。

要仅为姓氏包含 "ma" 的雇员选择数据，请使用以下 WHERE 子句：

WHERE LAST_NAME LIKE '%ma%';

示例 2-9 中的查询测试两个条件：薪水是否至少为 11000，以及佣金比例是否不为空值。

另请参见：

Oracle Database SQL Language Reference（了解有关 SELECT 语句以及 WHERE 子句的详细信息）
Oracle Database SQL Language Reference（了解有关 SQL 条件的详细信息）

示例 2-7：从一个部门选择数据

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;

结果与以下文本类似：

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
-------------------- ------------------------- -------------
Steven               King                                 90
Neena                Kochhar                              90
Lex                  De Haan                              90

3 rows selected.

示例 2-8 选择名字以相同子字符串开头的姓氏的数据

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'Ma%';

结果与以下文本类似：

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jason                Mallin
Steven               Markle
James                Marlow
Mattea               Marvins
Randall              Matos
Susan                Mavris

6 rows selected.

示例 2-9 选择满足两个条件的数据

SELECT FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT "%"
FROM EMPLOYEES
WHERE (SALARY >= 11000) AND (COMMISSION_PCT IS NOT NULL);

结果与以下文本类似：

FIRST_NAME           LAST_NAME                     SALARY          %
-------------------- ------------------------- ---------- ----------
John                 Russell                        14000         .4
Karen                Partners                       13500         .3
Alberto              Errazuriz                      12000         .3
Gerald               Cambrault                      11000         .3
Lisa                 Ozer                           11500        .25
Ellen                Abel                           11000         .3

6 rows selected.

对所选数据进行排序

如果显示查询结果，则记录是无序排列的，除非您使用 ORDER BY 子句指定记录顺序。

示例 2-10 中的查询结果按 LAST_NAME 升序（默认值）排序。

此外，在 SQL Developer 中，您可以省略 ORDER BY 子句，而是双击要排序的列名。

排序标准无需包括在选择列表中，如示例 2-11 所示。

另请参见： Oracle Database SQL Language Reference（了解有关 SELECT 语句以及 ORDER BY 子句的详细信息）

示例 2-10 按 LAST_NAME 对所选数据排序

SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY LAST_NAME;

结果：

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Ellen                Abel                      11-MAY-04
Sundar               Ande                      24-MAR-08
Mozhe                Atkinson                  30-OCT-05
David                Austin                    25-JUN-05
Hermann              Baer                      07-JUN-02
Shelli               Baida                     24-DEC-05
Amit                 Banda                     21-APR-08
Elizabeth            Bates                     24-MAR-07
...
FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Jose Manuel          Urman                     07-MAR-06
Peter                Vargas                    09-JUL-06
Clara                Vishney                   11-NOV-05
Shanta               Vollman                   10-OCT-05
Alana                Walsh                     24-APR-06
Matthew              Weiss                     18-JUL-04
Jennifer             Whalen                    17-SEP-03
Eleni                Zlotkey                   29-JAN-08

107 rows selected

示例 2-11 按未选择的列对选定数据排序

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY LAST_NAME;

结果：

FIRST_NAME           HIRE_DATE
-------------------- ---------
Ellen                11-MAY-04
Sundar               24-MAR-08
Mozhe                30-OCT-05
David                25-JUN-05
Hermann              07-JUN-02
Shelli               24-DEC-05
Amit                 21-APR-08
Elizabeth            24-MAR-07
...
FIRST_NAME           HIRE_DATE
-------------------- ---------
Jose Manuel          07-MAR-06
Peter                09-JUL-06
Clara                11-NOV-05
Shanta               10-OCT-05
Alana                24-APR-06
Matthew              18-JUL-04
Jennifer             17-SEP-03
Eleni                29-JAN-08

107 rows selected.

从多个表中选择数据

要从多个表中选择数据，可以使用称为联接的查询。联接中的表必须至少具有一个列名。

假设您要选择每位员工的 FIRST_NAME、LAST_NAME 和 DEPARTMENT_NAME。“名字”和“姓氏”在“雇员”表中，而“部门名称”在“部门”表中。这两个表都含有 DEPARTMENT_ID。可以使用 Example 2-12 中的查询。

对于仅出现在联接的一个表中的列名来说，表名限定符是可选的，但对于同时出现在两个表中的列名来说，则是必选的。下列查询等效于 Example 2-12 中的查询：

SELECT FIRST_NAME "First",
LAST_NAME "Last",
DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
ORDER BY DEPARTMENT_NAME, LAST_NAME;

要让使用限定列名的查询更加可读，请使用表别名，如以下示例中所示：

SELECT FIRST_NAME "First",
LAST_NAME "Last",
DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY d.DEPARTMENT_NAME, e.LAST_NAME;

尽管别名在 FROM 子句中创建，但您可以在查询中提前使用它们，如以下示例所示：

SELECT e.FIRST_NAME "First",
e.LAST_NAME "Last",
d.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY d.DEPARTMENT_NAME, e.LAST_NAME;

另请参阅： Oracle Database SQL Language Reference（了解有关联接的详细信息）

示例 2-12：从两个表选择数据（联接两个表）

SELECT EMPLOYEES.FIRST_NAME "First",
EMPLOYEES.LAST_NAME "Last",
DEPARTMENTS.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
ORDER BY DEPARTMENTS.DEPARTMENT_NAME, EMPLOYEES.LAST_NAME;

结果：

First                Last                      Dept. Name
-------------------- ------------------------- ------------------------------
William              Gietz                     Accounting
Shelley              Higgins                   Accounting
Jennifer             Whalen                    Administration
Lex                  De Haan                   Executive
Steven               King                      Executive
Neena                Kochhar                   Executive
John                 Chen                      Finance
...
Jose Manuel          Urman                     Finance
Susan                Mavris                    Human Resources
David                Austin                    IT
...
Valli                Pataballa                 IT
Pat                  Fay                       Marketing
Michael              Hartstein                 Marketing
Hermann              Baer                      Public Relations
Shelli               Baida                     Purchasing
...
Sigal                Tobias                    Purchasing
Ellen                Abel                      Sales
...
Eleni                Zlotkey                   Sales
Mozhe                Atkinson                  Shipping
...
Matthew              Weiss                     Shipping

106 rows selected.

在查询中使用运算符和函数

查询的 select_list 中可以包含 SQL 表达，而 SQL 表达可以包含 SQL 运算符和 SQL 函数。这些运算符和函数可将表数据用作操作数和参数。对 SQL 表达式进行求值后，它们的值将显示在查询结果中。

另请参见：

Oracle Database SQL Language Reference（了解有关 SQL 运算符的详细信息）
Oracle Database SQL Language Reference（了解有关 SQL 函数的详细信息）

在查询中使用算术运算符

基本算术运算符（+（加）、-（减）、\*（乘）和/（除））对列值执行运算。

示例 2-13 中的查询按 SALARY 的降序显示部门 90 中每位员工的 LAST_NAME、SALARY（月薪）和年薪。

示例 2-13 在查询中使用算术表达式

SELECT LAST_NAME,
SALARY "Monthly Pay",
SALARY * 12 "Annual Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
ORDER BY SALARY DESC;

结果：

LAST_NAME                 Monthly Pay Annual Pay
------------------------- ----------- ----------
King                            24000     288000
De Haan                         17000     204000
Kochhar                         17000     204000

在查询中使用数值函数

数值函数可接受数值输入，并返回数值。对于待求值的每个行，每个数值函数都返回一个数值。

Oracle Database SQL Language Reference 中列出了 SQL 支持的数值函数并进行了说明。

示例 2-14 中的查询使用数值函数 ROUND 来显示部门 100 中每名员工的日薪（精确到美分），将其四舍五入到最接近的值。

示例 2-15 中的查询使用数值函数 TRUNC 来显示部门 100 中每名员工的日薪（精确至美元），并截断到最接近的值。

另请参见： Oracle Database SQL Language Reference（了解有关 SQL 数字函数的详细信息）

示例 2-14 舍入数字数据

SELECT LAST_NAME,
ROUND (((SALARY * 12)/365), 2) "Daily Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

结果：

LAST_NAME                  Daily Pay
------------------------- ----------
Chen                          269.59
Faviet                        295.89
Greenberg                     394.52
Popp                          226.85
Sciarra                       253.15
Urman                         256.44

6 rows selected.

示例 2-15 截断数字数据

SELECT LAST_NAME,
TRUNC ((SALARY * 12)/365) "Daily Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

结果：

LAST_NAME                  Daily Pay
------------------------- ----------
Chen                             269
Faviet                           295
Greenberg                        394
Popp                             226
Sciarra                          253
Urman                            256

6 rows selected.

在查询中使用连接运算符

连接运算符 (||) 通过向第一个字符串附加第二个字符串，将两个字符串合并到一个字符串中。例如，'a'||'b'='ab'。您可以使用此运算符符合并来自查询结果中的同一列或同一个列中的两列或表达式的信息。

示例 2-16 中的查询将连接每个选定员工的名字、空格和姓氏。

另请参见： Oracle Database SQL Language Reference（了解有关连接运算符的详细信息）

示例 2-16 连接字符数据

SELECT FIRST_NAME || ' ' || LAST_NAME "Name"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

结果：

Name
----------------------------------------------
John Chen
Daniel Faviet
Nancy Greenberg
Luis Popp
Ismael Sciarra
Jose Manuel Urman

6 rows selected.

在查询中使用字符函数

字符函数可接受字符输入。大多数字符函数返回字符值，但也有一些字符函数返回数值。对于待求值的每个行，每个字符函数都返回一个值。

Oracle Database SQL Language Reference 中列出了 SQL 支持的字符函数并进行了说明。

函数 UPPER、INITCAP 和 LOWER 分别以大写、首字母大写和小写形式显示其字符参数。

Example 2-17 中的查询使用大写显示 LAST_NAME；使用大写显示第一个字符的 FIRST_NAME，以及所有其他字符（小写）和 EMAIL（小写）。

另请参阅： Oracle Database SQL Language Reference（了解有关 SQL 字符函数的详细信息）

示例 2-17 更改字符数据的大小写

SELECT UPPER(LAST_NAME) "Last",
INITCAP(FIRST_NAME) "First",
LOWER(EMAIL) "E-Mail"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY EMAIL;

结果：

Last                      First                E-Mail
------------------------- -------------------- -------------------------
FAVIET                    Daniel               dfaviet
SCIARRA                   Ismael               isciarra
CHEN                      John                 jchen
URMAN                     Jose Manuel          jmurman
POPP                      Luis                 lpopp
GREENBERG                 Nancy                ngreenbe

6 rows selected.

在查询中使用日期时间函数

日期时间函数对 DATE、时间戳和间隔值进行运算。对于待求值的每个行，每个日期时间函数都会返回一个值。

Oracle Database SQL Language Reference 中列出了 SQL 支持的日期时间函数并进行了说明。

对于每个 DATE 和时间戳值，Oracle Database 将存储以下信息：

Year
Month
Date
Hour
分钟
秒

对于每个时间戳值，Oracle Database 还会存储秒的小数部分，您可以指定其精度。要同时存储时区，请使用数据类型 TIMESTAMP WITH TIME ZONE 或 TIMESTAMP WITH LOCAL TIME ZONE。

有关 DATE 数据类型的详细信息，请参阅 Oracle Database SQL Language Reference。

有关 TIMESTAMP 数据类型的详细信息，请参阅 Oracle Database SQL Language Reference。

有关其他时间戳数据类型和间隔数据类型的信息，请参阅 Oracle Database SQL Language Reference。

示例 2-18 中的查询使用 EXTRACT 和 SYSDATE 函数来显示部门 100 中每名员工的工作年限。SYSDATE 函数返回系统时钟的当前日期作为 DATE 值。有关 SYSDATE 函数的详细信息，请参阅 Oracle Database SQL Language Reference。有关 EXTRACT 函数的信息，请参阅 Oracle Database SQL Language Reference。

Example 2-19 中的查询使用 SYSTIMESTAMP 函数显示当前系统日期和时间。SYSTIMESTAMP 函数返回 TIMESTAMP 值。有关 SYSTIMESTAMP 函数的信息，请参阅 Oracle Database SQL Language Reference。

查询的 FROM 子句中的表 DUAL 是 Oracle Database 自动创建的一行表，它与数据字典一起创建。当您要使用 SELECT 语句计算常数表达式时，请从 DUAL 中选择。由于 DUAL 中只有一行，因此仅返回一次常数。有关从 DUAL 中选择的详细信息，请参阅 Oracle Database SQL Language Reference。

另请参见： Oracle Database SQL Language Reference（了解有关 SQL 日期时间函数的详细信息）

示例 2-18 显示日期之间的年数

SELECT LAST_NAME,
(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)) "Years Employed"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY "Years Employed";

结果：

LAST_NAME                 Years Employed
------------------------- --------------
Popp                                   5
Urman                                  6
Chen                                   7
Sciarra                                7
Greenberg                             10
Faviet                                10

6 rows selected.

示例 2-19 显示系统日期和时间

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' ||
ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP) || '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date"
FROM DUAL;

结果取决于当前 SYSTIMESTAMP 值，但其格式如下：

System Time and Date
-------------------------------------------------------------------
18:17:53, 12/27/2012

在查询中使用转换函数

转换函数可将一种数据类型转换为另一种数据类型。

Oracle Database SQL Language Reference 中列出了 SQL 支持的转换函数并进行了说明。

示例 2-20 中的查询使用 TO_CHAR 函数将 HIRE_DATE 值（类型为 DATE）转换成格式为 FMMonth DD YYYY 的字符值。FM 从月份名称中删除前导空白和尾随空格。FMMonth DD YYYY 是日期时间格式模型的一个示例。有关日期时间格式样式的信息，请参阅 Oracle Database SQL Language Reference。

示例 2-21 中的查询使用 TO_NUMBER 函数将 POSTAL_CODE 值（类型为 VARCHAR2）转换为要在计算中使用的 NUMBER 类型的值。

另请参见：

Oracle Database SQL Language Reference（了解有关 SQL 转换函数的详细信息）
“关于 NLS_DATE_FORMAT 参数”

示例 2-20 使用格式模板将日期转换为字符

SELECT LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'FMMonth DD YYYY') "Date Started"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

结果：

LAST_NAME                 HIRE_DATE Date Started
------------------------- --------- -----------------
Chen                      28-SEP-05 September 28 2005
Faviet                    16-AUG-02 August 16 2002
Greenberg                 17-AUG-02 August 17 2002
Popp                      07-DEC-07 December 7 2007
Sciarra                   30-SEP-05 September 30 2005
Urman                     07-MAR-06 March 7 2006

6 rows selected.

示例 2-21 将字符转换为数字

SELECT CITY,
POSTAL_CODE "Old Code",
TO_NUMBER(POSTAL_CODE) + 1 "New Code"
FROM LOCATIONS
WHERE COUNTRY_ID = 'US'
ORDER BY POSTAL_CODE;

结果：

CITY                           Old Code       New Code
------------------------------ ------------ ----------
Southlake                      26192             26193
South Brunswick                50090             50091
Seattle                        98199             98200
South San Francisco            99236             99237

4 rows selected.

在查询中使用聚集函数

聚集函数接受一组行并返回单个结果行。这组行可以是整个表或视图。

Oracle Database SQL Language Reference 中列出了 SQL 支持的聚集函数并进行了说明。

与 GROUP BY 子句一起使用时，聚集函数将变得强大，该子句将查询结果按一行或多列分组，每组一个结果。

Example 2-22 中的查询使用 COUNT 函数和 GROUP BY 子句来显示向每位经理报告的人员数量。通配符 * 表示整个记录。示例 2-22 显示有一名员工不是某经理的下属。以下查询选择这名员工的姓氏、名字和职位：

COLUMN FIRST_NAME FORMAT A10;
COLUMN LAST_NAME FORMAT A10;
COLUMN JOB_TITLE FORMAT A10;

SELECT e.FIRST_NAME,
e.LAST_NAME,
j.JOB_TITLE
FROM EMPLOYEES e, JOBS j
WHERE e.JOB_ID = j.JOB_ID
AND MANAGER_ID IS NULL;

结果：

FIRST_NAME LAST_NAME  JOB_TITLE
---------- ---------- ----------
Steven     King       President

要让查询仅返回聚集值满足指定条件的行，请在查询的 HAVING 子句中使用聚集函数。

示例 2-23 中的查询显示每个部门有多少薪水每年开支，但仅显示金额超过 1,000,000 美元的部门。

Example 2-24 中的查询使用一些聚集函数来显示每个 JOB_ID 的薪金统计信息。

另请参见： Oracle Database SQL Language Reference（了解有关 SQL 聚合函数的详细信息）

示例 2-22 计算每个组中的行数

SELECT MANAGER_ID "Manager",
COUNT(*) "Number of Reports"
FROM EMPLOYEES
GROUP BY MANAGER_ID
ORDER BY MANAGER_ID;

结果：

Manager Number of Reports
---------- -----------------
       100                14
       101                 5
       102                 1
       103                 4
       108                 5
       114                 5
       120                 8
       121                 8
       122                 8
       123                 8
       124                 8
       145                 6
       146                 6
       147                 6
       148                 6
       149                 6
       201                 1
       205                 1
                           1

19 rows selected.

示例 2-23 限制聚集函数仅返回满足某个条件的行

SELECT DEPARTMENT_ID "Department",
SUM(SALARY*12) "All Salaries"
FROM EMPLOYEES
HAVING SUM(SALARY * 12) >= 1000000
GROUP BY DEPARTMENT_ID;

结果：

Department All Salaries
---------- ------------
        50      1876800
        80      3654000

示例 2-24 使用聚集函数获取统计信息

SELECT JOB_ID,
COUNT(*) "#",
MIN(SALARY) "Minimum",
ROUND(AVG(SALARY), 0) "Average",
MEDIAN(SALARY) "Median",
MAX(SALARY) "Maximum",
ROUND(STDDEV(SALARY)) "Std Dev"
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;

结果：

JOB_ID              #    Minimum    Average     Median    Maximum    Std Dev
---------- ---------- ---------- ---------- ---------- ---------- ----------
AC_ACCOUNT          1       8300       8300       8300       8300          0
AC_MGR              1      12008      12008      12008      12008          0
AD_ASST             1       4400       4400       4400       4400          0
AD_PRES             1      24000      24000      24000      24000          0
AD_VP               2      17000      17000      17000      17000          0
FI_ACCOUNT          5       6900       7920       7800       9000        766
FI_MGR              1      12008      12008      12008      12008          0
HR_REP              1       6500       6500       6500       6500          0
IT_PROG             5       4200       5760       4800       9000       1926
MK_MAN              1      13000      13000      13000      13000          0
MK_REP              1       6000       6000       6000       6000          0
PR_REP              1      10000      10000      10000      10000          0
PU_CLERK            5       2500       2780       2800       3100        239
PU_MAN              1      11000      11000      11000      11000          0
SA_MAN              5      10500      12200      12000      14000       1525
SA_REP             30       6100       8350       8200      11500       1524
SH_CLERK           20       2500       3215       3100       4200        548
ST_CLERK           20       2100       2785       2700       3600        453
ST_MAN              5       5800       7280       7900       8200       1066

19 rows selected.

在查询中使用 NULL 相关函数

与 NULL 相关的函数为处理 NULL 值提供方便。

Oracle Database SQL Language Reference 中列出了 SQL 支持的空相关函数，并进行了说明。

Example 2-25 中的查询返回姓氏以 "B" 开头的雇员的姓氏和佣金。如果某位员工未收到任何佣金（即 COMMISSION_PCT 为 NULL），则 NVL 函数会将 NULL 替代为 "Not Applicable"。

示例 2-26 中的查询使用 NVL2 函数返回姓氏以 'B' 开头的雇员的姓氏、薪金和收入：如果 COMMISSION_PCT 不为 NULL，则收入为薪金加上佣金；如果 COMMISSION_PCT 为 NULL，则收入仅为薪金。

另请参见：

Oracle Database SQL Language Reference（了解有关 NVL 函数的详细信息）
Oracle Database SQL Language Reference（了解有关 NVL2 函数的详细信息）

示例 2-25 使用字符串替代 NULL 值

SELECT LAST_NAME,
NVL(TO_CHAR(COMMISSION_PCT), 'Not Applicable') "COMMISSION"
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'B%'
ORDER BY LAST_NAME;

结果：

LAST_NAME                 COMMISSION
------------------------- ----------------------------------------
Baer                      Not Applicable
Baida                     Not Applicable
Banda                     .1
Bates                     .15
Bell                      Not Applicable
Bernstein                 .25
Bissot                    Not Applicable
Bloom                     .2
Bull                      Not Applicable

9 rows selected.

示例 2-26 为 NULL 值和非 NULL 值指定不同表达式

SELECT LAST_NAME, SALARY,
NVL2(COMMISSION_PCT, SALARY + (SALARY * COMMISSION_PCT), SALARY) INCOME
FROM EMPLOYEES WHERE LAST_NAME LIKE 'B%'
ORDER BY LAST_NAME;

结果：

LAST_NAME                     SALARY     INCOME
------------------------- ---------- ----------
Baer                           10000      10000
Baida                           2900       2900
Banda                           6200       6820
Bates                           7300       8395
Bell                            4000       4000
Bernstein                       9500      11875
Bissot                          3300       3300
Bloom                          10000      12000
Bull                            4100       4100

9 rows selected.

在查询中使用 CASE 表达式

通过 CASE 表达式，您可以在 SQL 语句中使用 IF … THEN … ELSE 逻辑，而无需调用子程序。CASE 表达式有两种，即简单表达式和搜索表达式。

Example 2-27 中的查询使用简单的 CASE 表达式来显示每个国家/地区代码的国家/地区名称。

示例 2-28 中的查询使用搜索的 CASE 表达式，根据与服务年限关联的日期范围显示建议的加薪（15%、10%、5% 或 0%）。

另请参见：

Oracle Database SQL Language Reference（了解有关 CASE 表达式的详细信息）
Oracle Database PL/SQL Language Reference（了解有关 CASE 表达式的详细信息）
“在查询中使用 DECODE 函数”
“使用 CASE 语句”

示例 2-27 在查询中使用简单 CASE 表达式

SELECT UNIQUE COUNTRY_ID ID,
       CASE COUNTRY_ID
         WHEN 'AU' THEN 'Australia'
         WHEN 'BR' THEN 'Brazil'
         WHEN 'CA' THEN 'Canada'
         WHEN 'CH' THEN 'Switzerland'
         WHEN 'CN' THEN 'China'
         WHEN 'DE' THEN 'Germany'
         WHEN 'IN' THEN 'India'
         WHEN 'IT' THEN 'Italy'
         WHEN 'JP' THEN 'Japan'
         WHEN 'MX' THEN 'Mexico'
         WHEN 'NL' THEN 'Netherlands'
         WHEN 'SG' THEN 'Singapore'
         WHEN 'UK' THEN 'United Kingdom'
         WHEN 'US' THEN 'United States'
       ELSE 'Unknown'
       END COUNTRY
FROM LOCATIONS
ORDER BY COUNTRY_ID;

结果：

ID COUNTRY
-- --------------
AU Australia
BR Brazil
CA Canada
CH Switzerland
CN China
DE Germany
IN India
IT Italy
JP Japan
MX Mexico
NL Netherlands
SG Singapore
UK United Kingdom
US United States

14 rows selected.

示例 2-28 在查询中使用搜索型 CASE 表达式

SELECT LAST_NAME "Name",
HIRE_DATE "Started",
SALARY "Salary",
CASE
  WHEN HIRE_DATE < TO_DATE('01-Jan-03', 'dd-mon-yy')
    THEN TRUNC(SALARY*1.15, 0)
  WHEN HIRE_DATE >= TO_DATE('01-Jan-03', 'dd-mon-yy') AND
       HIRE_DATE < TO_DATE('01-Jan-06', 'dd-mon-yy')
    THEN TRUNC(SALARY*1.10, 0)
  WHEN HIRE_DATE >= TO_DATE('01-Jan-06', 'dd-mon-yy') AND
       HIRE_DATE < TO_DATE('01-Jan-07', 'dd-mon-yy')
    THEN TRUNC(SALARY*1.05, 0)
  ELSE SALARY
END "Proposed Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;

结果：

Name                      Started       Salary Proposed Salary
------------------------- --------- ---------- ---------------
Faviet                    16-AUG-02       9000           10350
Greenberg                 17-AUG-02      12008           13809
Chen                      28-SEP-05       8200            9020
Sciarra                   30-SEP-05       7700            8470
Urman                     07-MAR-06       7800            8190
Popp                      07-DEC-07       6900            6900

6 rows selected.

在查询中使用 DECODE 函数

DECODE 函数将表达式与多个搜索值进行比较。每当表达式的值与搜索值匹配时，DECODE 将返回与该搜索值关联的结果。如果未找到匹配项，则 DECODE 将返回默认值（如果已指定）或 NULL（如果未指定的默认值）。

示例 2-29 中的查询使用 DECODE 函数来显示三种不同工作的建议加薪数额。表达式为 JOB_ID；搜索值为‘ PU_CLERK ’、‘ SH_CLERK ’和‘ ST_CLERK ’；默认值为 SALARY。

注： DECODE 函数的参数可以是任何 SQL 数字或字符类型。比较之前，Oracle 会自动将表达式和每个搜索值转换为第一个搜索值的数据类型。Oracle 会自动将返回值转换为与第一个结果相同的数据类型。如果第一个结果的数据类型为 CHAR，或者第一个结果为 NULL，则 Oracle 会将返回值转换为数据类型 VARCHAR2。

另请参见：

Oracle Database SQL Language Reference（了解有关 DECODE 函数的信息）
“在查询中使用 CASE 表达式”

示例 2-29 在查询中使用 DECODE 函数

SELECT LAST_NAME, JOB_ID, SALARY,
DECODE(JOB_ID,
'PU_CLERK', SALARY * 1.10,
'SH_CLERK', SALARY * 1.15,
'ST_CLERK', SALARY * 1.20,
SALARY) "Proposed Salary"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%_CLERK'
AND LAST_NAME < 'E'
ORDER BY LAST_NAME;

结果：

LAST_NAME                 JOB_ID         SALARY Proposed Salary
------------------------- ---------- ---------- ---------------
Atkinson                  ST_CLERK         2800            3360
Baida                     PU_CLERK         2900            3190
Bell                      SH_CLERK         4000            4600
Bissot                    ST_CLERK         3300            3960
Bull                      SH_CLERK         4100            4715
Cabrio                    SH_CLERK         3000            3450
Chung                     SH_CLERK         3800            4370
Colmenares                PU_CLERK         2500            2750
Davies                    ST_CLERK         3100            3720
Dellinger                 SH_CLERK         3400            3910
Dilly                     SH_CLERK         3600            4140

11 rows selected.