7.3 执行基本的SELECT语句

SELECT语句的实际功能体现在它的执行中。执行所有查询语言语句的关键是理解其语法和决定其用法的规则。首先讨论这个主题，然后是基本查询的执行，最后介绍表达式和运算符——它们能够极大地增强存储在关系表中数据的效用。再接着介绍空值(null value)的概念以及它的缺点。这些主题可分为下面四个部分进行讨论：

● 基本SELECT语句的语法

● 必须遵守的规则

● SQL表达式和运算符

● NULL表示空

7.3.1 基本SELECT语句的语法

SELECT语句的最基本形式支持列投影和创建算术、字符和日期表达式。它也能够从结果集中删除重复值。基本SELECT语句的语法如下所示：

        SELECT *|{[DISTINCT] column|expression [alias], ...}
        FROM table;

SELECT语句语法的特殊关键字或者保留字使用大写字母。然而，当使用命令时，查询语句中保留字的大小写并不重要。保留字不能用作列名或者其他数据库对象的名称。SELECT、DISTINCT和FROM是三个关键字。一条SELECT语句总是包含两个或者更多子句。两个强制子句是SELECT子句和FROM子句。管道符号(|)表示OR。因此可以将上面SELECT语句的第一种形式读作：

        SELECT *
        FROM table;

在这种格式中，星号(*)表示所有列。SELECT *是要求Oracle服务器返回所有可能列的简洁方式。我们将它作为一种速记和省时的符号，这样就不需要输入SELECT column1, column2, column3, column4, …, columnX来选择所有列。FROM子句指定查询哪个表来获得SELECT子句所请求的列。

可以发出下面的SQL命令从HR模式的REGIONS表中检索所有列和所有行：

        select * from regions;

当执行这条命令时，它返回所有数据行和属于该表的所有列。在SELECT语句中使用星号有时表示“盲目(blind)”查询，因为没有确切指定要提取的列。

基本SELECT语句的第二种形式的FROM子句与第一种形式的相同，但SELECT子句不同：

        SELECT {[DISTINCT] column|expression [alias], ...}FROM table;

该SELECT子句可以简化为两种形式：

        SELECT column1 (possibly other columns or expressions) [alias optional]

或者

        SELECT DISTINCT column1 (possibly other columns or expressions)
        [alias optional]

别名是表示列或者表达式的可选名称。别名通常用于以用户友好的方式显示输出。当表示列或者表达式时，它们也可以作为速记方式以减少输入量。本章稍后将讨论别名。通过显式列出SELECT子句中的相关列，实际上可以投影要检索结果的确切子集。下面的语句只返回REGIONS表的REGION_NAME列：

        select region_name from regions;

假定要求获得组织中员工曾经从事过的所有工作角色。为此可以发出命令：SELECT *FROM JOB_HISTORY。然而，SELECT *结构会另外返回EMPLOYEE_ID、START_DATE和END_DATE列。使用下列语句，可以获得只包含JOB_ID和DEPARTMENT_ID列的整齐结果集。

        select job_id, department_id from job_history;

使用DISTINCT关键字允许从结果集中删除重复行。在很多情况下，只需要唯一的行集合。重要的是要注意，Oracle 服务器在确定行是唯一还是重复时所采用的标准完全取决于SELECT子句中DISTINCT关键字之后指定的内容。使用下列语句从JOB_ HISTORY表中选择不同的JOB_ID值会返回8种不同的工作类型。

        select distinct job_id from job_history;

DISTINCT关键字的一个重要特性是从列的组合中删除重复值。

7.3.2 必须遵守的规则

SQL是一种在语法规则上非常严格的语言，但它也非常简单和灵活，足以支持大量编程样式。本节将讨论SQL语句的一些基本规则。

1．大写字母或者小写字母

使用大写字母还是小写字母将SQL语句提交给数据库，这只是个人爱好。包括本书作者在内的许多开发人员都喜欢用小写字母来写SQL语句。需要用大写字母指定SQL的保留字，这也是一种常见误解。再次重申，这取决于你的爱好。建议使用一致的标准化格式。

关于字母大小写有一条告诫。当与字面值交互时，区分大小写就有意义了。请看JOB_HISTORY表的JOB_ID列。这一列包含的数据行刚好以大写字母形式存储在数据库中；例如，SA_REP和ST_ CLERK。当要求使用字面列限制结果集时，大小写就非常重要。Oracle服务器认为在JOB_HISTORY表中，对于JOB_ID列中包含St_Clerk值的所有行的请求不同于对JOB_ID列中包含ST_CLERK值的所有行的请求。

关于不同数据库对象的元数据默认使用大写字母存储在数据字典中。如果查询数据库字典表来返回HR模式所拥有的表列表，那么很可能返回的表名是存储为大写字母。这并不意味着不能使用小写名称创建表；其实可以这样做。只不过在数据库字典中使用大写字母创建和存储表、列以及其他数据库对象元数据的情况更常见，而且是Oracle服务器的默认行为。

2．语句终止符

通常用分号作为SQL语句的终止符。SQL*Plus总是需要语句终止符，通常使用分号。单个SQL语句甚至是几组相关语句通常存储为脚本文件以便将来使用。SQL脚本中的单个语句通常用换行(或者回车)终止，并且在下一行使用正斜杠(/)，而不是使用分号终止。可以创建SELECT语句，用换行终止它，包含一条正斜杠来执行语句，并在脚本文件中存储它。然后可以从SQL*Plus内部调用脚本文件。注意，如果只是单个语句，SQL Developer就不需要语句终止符，但如果使用了终止符也没有错。好的做法是使用分号终止SQL语句。SQL*Plus语句的一些示例如下所示：

        select country_name, country_id, location_id from countries;

       select city, location_id,
              state_province, country_id
        from locations
        /

第一个代码示例说明两条重要规则：第一，用分号终止语句；第二，整条语句写成一行。对于SQL语句而言，只要语句中的单词没有跨越数行，那么写成一行或者跨越多行，这完全可以接受。第二个代码样本显示的语句跨越三行，由新行终止，并使用正斜杠执行。

3．缩排、可读性和好的实践

考察下面的查询：

        select city, location_id,
              state_province, country_id
        from locations
        /

该示例突出强调了缩排SQL语句，以增强代码可读性的好处。Oracle服务器并不反对将整条语句写成一行、没有缩排。良好的实践是将SELECT语句的不同子句分离到不同的行。当子句中的表达式特别复杂时，通常最好将这种语句分离到新的行，以提高可读性。当开发SQL来满足报告需要时，通常需要重复这个过程。如果将复杂的表达式分成单独的行，那么在开发过程中SQL解释器就会更有用，因为抛出错误的格式通常是：“ERROR at line X:”，这使调试过程变得更容易。

练习7-1 使用SQL回答几个问题

在这个练习中，使用SQL*Plus作为HR用户进行连接，使用SELECT语句回答两个问题。

问题1：当前有多少唯一部门有员工在里面工作？

(1) 启动SQL*Plus，连接到HR模式。

(2) 最初可能试图在DEPARTMENTS表中查找答案。但通过仔细研究会发现，该问题要求的是员工信息。此信息包含在EMPLOYEES表中。

(3) 单词“唯一(unique)”说明应该使用DISTINCT关键字。

(4) 结合第(2)步和第(3)步，就可以构造下面的SQL语句：

        select distinct department_id
        from employees;

(5) 该查询返回12行。注意，第3行是空的。这是DEPARTMENT_ID列中的空值。

(6) 所以第一个问题的答案是：11个唯一部门中有员工工作，但至少有一名员工没有分配部门。

问题2：欧洲地区有多少个国家？

(1) 这个问题包含两个部分。考察REGIONS表(它包含四个地区，每个地区都由REGION_ID值唯一标识)和COUNTRIES表(它包含REGION_ID列，该列显示这个国家属于哪个地区)。

(2) 第一个查询需要确定欧洲地区的REGION_ID。这由SQL语句完成，它显示欧洲地区的REGION_ID值为1：

        select * from regions;

(3) 要确定哪些国家将1作为其REGION_ID，就需要执行下面的SQL查询：

        select region_id, country_name from countries;

(4) 手动计算REGION_ID值为1的行就知道：对于HR数据模型而言，欧洲地区有8个国家。

7.3.3 SQL表达式和运算符

SELECT语句的一般形式引入了可对列和表达式进行选择的概念。表达式通常由在一个或多个列值或者表达式上执行的运算组成。能够作用在列值上以构成表达式的运算符取决于底层数据类型。数字列主要有四种算术运算符(加、减、乘和除)；字符或者字符串列有串联运算符；日期和时间戳列有加减运算符。在一般算术运算中，当表达式中出现多个运算符时，有预定义的计算顺序(运算符优先级)。圆括号的优先级最高，乘法和除法次之，之后是加法和减法(它们的优先级最低)。

具有相同优先级的运算从左到右计算。圆括号用来强制非默认运算符优先级。当构造复杂的表达式时，大量使用括号是好的实践，通常鼓励这样做。因为这样代码就更具有可读性，也不容易出错。表达式使许多有用的数据操作变得可行。

1．算术运算符

考察JOB_HISTORY表的示例，该表存储员工的前一个工作角色的起止日期。例如，出于税收或养老金方面的考虑，通常需要计算该员工在此角色中工作了多长时间。可以使用算术表达式获得这些信息。SQL语句和图7-8返回的结果都有一些有意思的元素，值得进一步讨论。

在SELECT子句中指定了5个元素。前4个元素是JOB_HISTORY表的标准列。后面提供了计算员工在特定岗位上工作的天数所需的源信息。考察输出中第9行编号为176的员工。该员工从1999年1月1日起担任销售主管(Sales Manager)，于1999年12月31日结束雇佣。因此，这名员工刚好工作了一年(即1999年)，共计365天。

使用SELECT子句中的第5个元素，可以计算雇佣员工的天数，第5个元素是一个表达式。该表达式说明，在包含日期信息的列上执行的算术运算返回数字值，这些数值表示某个数量的天数。

为了强制减法运算的运算符优先级，可以用圆括号包含子表达式end_date-start_date。加1，以补偿减法运算中减少的一天。

2．表达式和列别名

图7-8引入了一个新概念——列别名。注意，表达式列有一个名为Days Employed的有意义的题头。这个题头就是别名。别名是列或者表达式的另一种可选名称。如果该表达式没有使用别名，列题头就是(END_ DATE-START_DATE)+1，这不太用户友好。别名对于表达式和计算特别有用，并且可以用几种方式实现它们。在SELECT语句中，有一些规则控制列别名的使用。图7-8中的别名“Days Employed”通过保留空格并且在双引号中输入别名来指定。这些引号必不可少，原因有两点：第一，该别名由多个单词组成。第二，只有用双引号包含别名，才能保留别名的大小写。如果在指定空格分隔的多单词的别名时，没有使用双引号，就会返回“ORA-00923:FROM keyword not found where expected”错误。SQL提供了一种更正式的方法来插入别名——在列或表达式和别名之间插入AS关键字，如下列查询的第一行中所示：

        SELECT EMPLOYEE_ID AS "Employee ID",
              JOB_ID AS "Occupation",
              START_DATE, END_DATE,
              (END_DATE-START_DATE)+1 "Days Employed"
        FROM JOB_HISTORY;

3．字符和字符串串联运算符

双管道符号||表示字符串联运算符。这个运算符用来将字符表达式或者列联接在一起，从而创建一个更大的字符表达式。表的列可以相互链接或者链接字面字符的字符串，以创建一个合成字符表达式。

串联运算符非常灵活，可以在字符表达式中的几乎任何位置多次使用。考虑下面这个查询：

        SELECT 'THE '||REGION_NAME||' region is on Planet Earth' "Planetary Location",
        FROM REGIONS;

这里，字符字面值“The”串联到REGION_NAME列的内容。新的字符串进一步串联到字符字面值“region is on Planet Earth”，再使用友好的列题头“Planetary Location”作为整个表达式的别名。

4．字面值和DUAL表

字面值在表达式中很常见。这些值表示SELECT子句中不来自任何数据库对象的数字、字符或者日期和时间值。将字符字面值串联到现有列数据通常很有用，那么应该如何处理与现有列数据无关的字面值？为了确保关系一致，对于使用数据库计算与任何表或者列都无关的表达式这个问题，Oracle提供了一种很好的解决方案。为了使用数据库来计算表达式，必须提交在语法上合法的SELECT语句。如果要知道两个数字型字面值的和，又该怎么办？需要用关系方式与数据库交互。因此Oracle提供了一种特殊的单行单列表DUAL，来解决与作用于字面表达式的数据库的交互问题。

回忆一下前面描述的DUAL表。它包含名为字符数据类型的DUMMY列。可以执行查询：SELECT * FROM DUAL，返回数据值“X”作为DUMMY列的内容。在开发过程中，通过查询DUAL表，来测试复杂表达式是评估这些表达式是否正确运行的有效方法。可以从任何表中查询字面表达式，但记住，表中的每一行都要处理表达式，而查询DUAL表只返回一行。

        select 'literal '||'processing using the REGIONS table'
        from regions;

       select 'literal '||'processing using the DUAL table'
        from dual;

第一条语句在结果集中返回四行，因为在REGIONS表中有四行数据，而第二行语句只返回一行。

5．两个单引号或者可选择的引用运算符

到目前为止串联的字面字符串，都是将单数形式的单词附加到列表达式的前面或后面。这些字符字面值使用单引号指定。例如：

        select 'I am a character literal string' from dual;

那么，该怎样处理包含单引号的字符字面值呢？复数造成了字符字面处理的特殊问题。考察下面的语句：

        select 'Plural's have one quote too many' from dual;

执行这条语句将导致产生一个Oracle错误。那么，如何处理包含单引号的句子呢？主要可以使用两种方法。其中最常见的是在字符串中应该出现的单引号前面再添加一个单引号。下面的语句说明了用字面值’Plural''s取代字符字面值’Plural's，就可以避免这个错误。

        select 'Plural''s have one quote too many' from dual;

使用两个单引号来处理字符字面值中应该出现的单引号的方法，会随着受影响字面值的增加而变得混乱和容易出错。Oracle用可选择的引用运算符(q)的形式提供了处理这类字符的简洁方法。注意，产生这个问题是因为Oracle选择单引号字符作为封装或者包含其他字符字面值的特殊符号。这些封装字符的符号可以是除了单引号之外的任何符号。

考虑可选择的引用运算符(q)。q运算符允许从一组可以用于封装字符字面值的符号对中选择一种，作为单引号的替代符号。它们可以是任何单字节、多字节字符或者四种括号——圆括号()、花括号{}、方括号[]或者尖括号< >。使用q运算符，实际上可以将字符分隔符从单引号改变为其他任何字符，如下所示：

        SELECT q'<Plural's can also be specified with alternate quote operators>'
        FROM DUAL;

       SELECT  q'[Even square brackets [] can be used for Plural's]' FROM DUAL;

       SELECT  q'XWhat about UPPER CASE X for Plural's ? X'
        FROM DUAL;

可选择的引用运算符的语法如下所示：

        q'delimiter character literal which may include single quotes delimiter'

其中delimiter可以是任何字符或括号。第一个和第二个示例显示将尖括号和方括号作为字符分隔符，而第三个示例说明如何通过可选择的引用运算符将大写字母“X”用作特殊字符分隔符。注意，字符“X”本身可包括在字符串中——只要其后面不跟引号。

7.3.4 NULL

空值表示没有数据。包含空值的行没有该列的数据。空值通常定义为不可用、未分配、未知或者不适用的值。没有注意空值的特殊要求，通常会导致错误，或者更糟糕的是，导致答案不正确。本节重点讨论使用SELECT语句与空值列数据的交互以及它对表达式的影响。

1．Not Null和Nullable列

表存储数据行，这些行被划分为一列或者多列。列有相应的名称和数据类型。其中有些列被数据库规则限制为强制列。在每行的NOT NULL列中，会强制存储某些数据。然而，当数据库约束没有强制表的列存储行的数据时，这些列就有可能为空。

Oracle提供了一种机制，可以使用第8章讨论的一般函数在算术上与NULL值交互。除以空值也会得到空值，和除以零不一样(它会产生错误)。当使用字符串联运算符遇到空值时，会忽略空值，而涉及空值的算术运算总是产生空值。

2．外键和Nullable列

当表通过主键和外键关系相互关联时，数据模型设计有时会产生问题，但基于外键的列可以为空。

DEPARTMENTS表以DEPARTMENT_ID列作为其主键。EMPLOYEES表包含DEPARTMENT_ID列，它由外键关系限制到DEPARTMENTS表中的DEPARTMENT_ID列。这意味着EMPLOYEES表中的DEPARTMENT_ID列不能记录在DEPARTMENTS表中没有的值。这种引用完整性构成了第三范式的基础，对于整个数据库的完整性也至关重要。

但如果是NULL值又如何？DEPARTMENTS表中的DEPARTMENT_ID列可以包含空值吗？答案是不能。Oracle坚持主键的所有列都被隐式限制为强制列。但对外键列的隐式约束又怎样呢？这是Oracle的难题，因为为了保持灵活性和迎合广大用户，它无法坚持说由引用完整性约束的列必须是强制的。而且，不是所有情况都需要这种功能。

EMPLOYEES表中的DEPARTMENT_ID列实际上可以为空值。因此，在表中可能包含空的DEPARTMENT_ID值的记录。实际上，在EMPLOYEES表中也有这样的记录。HR数据模型允许员工(不管正确与否)不属于任何部门。当实现两个表之间的关系联接时，完全可以遗漏或者排除某些在联接列中包含空值的记录。第10章将讨论应对这种挑战的方法。

练习7-2 构造表达式

在这个练习中，将使用表达式和别名构造一个查询，以用户友好的方式显示HR模式的结果。

(1) 查询HR.JOBS表并返回一个格式为The Job Id for the <job_title's> job is: <job_id>的表达式。注意，job_title 应有一个单引号，并且给它添加“s”，以便看起来更自然。工作头衔是主席的输出样本是“The Job Id for the President's job is: AD_PRES”。使用AS关键字给该列表达式起一个别名为Job Description。对此问题有多种解决方案。这里选择的方法是用另一个单引号处理自然出现的单引号。也可以使用可选择的引用运算符，用另一字符来限定自然出现的引号。

(2) 需要别名为Job Description的单个表达式；通过将字面值“The Job Id for the”串联到JOB_TITLE列，然后再串联到字面值“'s job is:”，最后再串联JOB_ID列就可构造它。添加另一个单引号就生成如下所示的SELECT语句：

        select 'The Job Id for the '||job_title||'''s job is: '||job_id
        AS "Job Description" from jobs;

7.4 限制查询检索的行

关系理论的基本原则之一是选择。使用SELECT语句的WHERE子句(有时被称为谓词)实现选择。限制返回数据集的条件有许多形式，它们对列和表达式都会产生影响。只有表中符合这些条件的行才会返回。条件使用比较运算符以及列和字面值来限制行。布尔运算符能够指定多种条件，进而限制返回的行。当在SELECT语句中遇到布尔、条件、串联和算术运算符时，需要确定它们的优先顺序。

7.4.1 WHERE子句

WHERE子句通过依据一种或者多种条件限制返回的行，来扩充SELECT语句。只使用SELECT和FROM子句查询表会返回表中所保存的数据的所有行。使用DISTINCT关键字，排除重复值，在某种程度上限制了结果行。如果需要从表中检索非常明确的信息——例如，只需要某些列包含特定值的数据时，情况又如何？如何从COUNTRIES表中检索位于欧洲地区的国家？如何只检索作为销售代表的员工？使用WHERE子句来指定必须返回哪些行，就可以回答这些问题。包含WHERE子句的SQL SELECT语句的格式为：

        SELECT *|{[DISTINCT] column|expression [alias], ...}
        FROM table
        [WHERE condition(s)];

WHERE子句总是紧跟在FROM子句之后。方括号表示WHERE子句是可选的。可以同时应用一种或者多种条件来限制结果集。使用条件运算符来比较两个项，这样可以指定条件。这些项可能是列值、字面值或者表达式。等于运算符(equality operator)最常用于限制结果集。使用WHERE子句的一个示例如下所示：

        select country_name
        from countries
        where region_id=3;

这个示例从COUNTRIES表中投影COUNTRY_NAME列。WHERE子句将返回的行限制为REGION_ID列中包含3的那些行，而不是选择所有行。

1．基于数字列的条件

条件必须适应不同的列数据类型。可以用几种不同的方法指定依据数字列来限制行的条件。考虑EMPLOYEES表中的SALARY列。这一列的数据类型为NUMBER(8,2)。可像如下所示限制SALARY列。

        select last_name, salary from employees where salary = 10000;

上面的语句可检索薪水为$10 000的员工的LAST_NAME和SALARY值，因为运算符两边的数据类型匹配、可兼容。

可以将数字列与相同行中的另一个数字列进行比较，从而构造一个WHERE子句条件，如下面的查询所示：

        select last_name, salary from employees
        where salary = department_id;

这个WHERE子句的限制过于严格，因而没有选中行。这是因为SALARY值的范围是2100～24000，而DEPARTMENT_ID值的范围是10～110。因为DEPARTMENT_ID值和SALARY值的范围没有重叠，所以没有满足这种条件的行，因此什么也没有返回。

还可以使用WHERE子句条件来比较数字列与表达式，或是将表达式与其他表达式相比较。

        select last_name, salary from employees
        where salary = department_id*100;

       select last_name, salary from employees
        where salary/10 = department_id*10;

第一个示例对于每一行而言，都要将SALARY列中的值与DEPARTMENT_ID值和100的乘积进行比较。第二个示例比较两个表达式。注意，两个示例中的条件在代数学中是相等的，执行时返回相同的结果集。

2．基于字符的条件

在条件子句内用单引号包含字符字面值，就可以指定依据字符数据确定选择哪些行的条件。EMPLOYEES表中的JOB_ID列的数据类型为VARCHAR2(10)。假设想获取当前为销售代表的员工的LAST_NAME值的列表。销售代表的JOB_ID值是SA_REP。下面的语句可以生成这样的列表：

        select last_name from employees where job_id='SA_REP';

如果没有用引号指定字符字面值，就会出现Oracle错误。记住，字符字面值数据区分大小写，因此下面的WHERE子句是不对等的：

        子句1: where job_id=SA_REP
        子句2: where job_id='Sa_Rep'
        子句3: where job_id='sa_rep'

子句1产生“ORA-00904:‘SA_REP': invalid identifier”错误，因为字面值SA_REP没有包含在单引号内。子句2和子句3在语法上是正确的，但并不对等。而且，这些子句都不会生成任何数据，因为EMPLOYEES表中没有行的JOB_ID列值是Sa_Rep或者sa_rep。

基于字符的条件并不只限于比较列值和字面值。还可以使用其他字符列和表达式来指定它们。基于字符的表达式可以构成由条件运算符划分的条件的一个或者两个部分。通过将字面值连接到一个或者多个字符列，就可以形成字符表达式。下面4个子句说明了基于字符的表达式的几个条件：

        子句1: where 'A '||last_name||first_name = 'A King'
        子句2: where first_name||' '||last_name = last_name||' '||first_name
        子句3: where 'SA_REP'||'King' = job_id||last_name
        子句4: where job_id||last_name ='SA_REP'||'King'

子句1将字符串字面值’A’连接到LAST_NAME和FIRST_NAME列。将这个表达式与字面值’A King’进行比较，返回所有满足条件的行。子句2说明，字符表达式可以放在条件运算符的两边。子句3说明字面值表达式也可以放在条件运算符的左边。从逻辑上说它与子句4对等，子句4交换了子句3中的操作数。子句3和子句4会返回相同的数据行。

3．基于日期的条件

当保存日期和时间信息时，DATE列就非常有用。日期字面值和字符数据一样都必须包含在单引号内。在WHERE条件子句中使用时，DATE列会与其他DATE列、字面值或表达式进行比较。这些字面值会依据默认的日期格式(即DD-MON-RR)自动转换为DATE值。如果字面值出现在涉及DATE列的表达式中，就会使用默认格式掩码将它自动转换为日期值。DD表示日，MON表示月的前三个字母，RR表示顺应2000年的年(也就是说，如果RR介于50～99之间，Oracle服务器就会返回20世纪的日期值，否则返回21世纪的日期值)。也可以指定完整的4位数年份YYYY。例如下面的4个SQL语句：

        子句1: where start_date = end_date
        子句2: where start_date = '01-JAN-2001'
        子句3: where start_date = '01-JAN-01'
        子句4: where start_date = '01-JAN-99'

第一个子句测试两个DATE列之间的相等性。会返回START_DATE和END_DATE列中包含相同值的行。然而请注意，只有所有部分(包括日、月、年、时、分和秒)都完全匹配， DATE值才相等。第8章将详细讨论存储DATE值。到目前为止，不必考虑时、分和秒部分。在第二个WHERE子句中，START_DATE列比较字符字面值：“01-JAN-2001”。已经指定完整的4位数年份(YYYY)。这是Oracle服务器可以接受的。第三个条件与第二个条件相等，因为字面值“01-JAN-01”被转换为日期值01-JAN-2001。这是因为RR部分小于50，因此将21世纪，即20，放在年RR部分的前面作为世纪值。在第4个条件中，字面值“01-JAN-99”的世纪部分变成20世纪，即19，产生的日期值为01-JAN-1999，这是因为RR部分是99，大于50。

在包含DATE值的表达式中也支持使用加减运算符的算术运算。表达式END_DATE -START_ DATE返回数字值，它表示START_ DATE和END_DATE之间的天数。表达式START_DATE+30返回START_DATE之后30天的DATE值。

7.4.2 比较运算符

等于运算符被广泛用于说明使用WHERE子句限制行的概念，也可以使用其他一些可选运算符。不等于运算符(如“小于”或者“大于等于”)可以用来返回符合不等于条件的行。BETWEEN运算符可以实现基于范围的比较，以测试列值是否介于两个值之间。IN运算符测试集合关系，如果条件中测试的列值是字面值集合的成员，那么会返回行。模式匹配比较运算符LIKE非常强大，它允许字符列数据部分匹配符合特定模式的字面值。本节讨论的最后一个比较运算符是IS NULL运算符，它返回列值包含空值的行。这些运算符可以在WHERE子句中组合使用。

1．等于和不等于运算符

限制查询返回的行需要指定合适的WHERE子句。如果子句的限制过于严格，那么几乎不会返回行。如果宽泛地指定条件子句，那么实际返回的行就多于要求返回的行。探讨不同的可用运算符可以用语言请求自己感兴趣的行。测试条件中的相等性很自然也很直观。使用“等于(=)”运算符构成这种条件。如果一行的等于条件为真，那么返回该行。考察下面的查询：

        select last_name, salary from employees where job_id='SA_REP';

该查询测试EMPLOYEES表中各行的JOB_ID列与字符字面值SA_REP的相等性。如果字符信息相等，那么必须匹配正确的大小写。当满足这种匹配时，会返回投影的LAST_NAME和SALARY列的值。注意，尽管条件子句建立在JOB_ID列的基础之上，但查询不需要投影该列。

基于不等于的条件强化WHERE子句规范。使用不等于和等于运算符可以实现范围和模式匹配比较，但通常更倾向于使用BETWEEN运算符和LIKE运算符来实现这种比较。不等于运算符如表7-6所示。

不等于运算符实现基于范围的查询。应用程序可能要求提供一个结果集，其中某个列值大于另一个值。例如，下面的查询可以获得薪水大于$5000的员工的LAST_NAME和SALARY值的列表：

        select last_name, salary from employees where salary > 5000;

下面的子句使用复合不等于运算符(由多个符号组成)：

        子句1: where salary <= 3000
        子句2: where salary <> department_id

子句1返回的行包含的SALARY值小于等于3000。子句2是“不等于”运算符的一种形式，返回SALARY列值不等于DEPARTMENT_ID值的行。

数字不相等当然很直观。然而，字符和日期的比较则比较复杂。测试字符不相等很有意思，因为不等于运算符两边被比较的字符串被转换为其字符的数字表示。依据数据库字符集和国家语言支持(National Language Support, NLS)设置，每个字符串都分配了一个数值。这些数值就是计算不等于比较的基础。例如下面的语句：

        select last_name from employees where last_name < 'King';

字符字面值’King’被转换为数字表示。假设US7ASCII数据库字符集使用AMERICAN NLS设置，那么字面值’King’被转换为其各个字符的值：K (75)、i (105)、n (110)和g (103)。对于EMPLOYEES表中的各行而言，同样将LAST_ NAME列数据转换为每个字符的数值，再与字面值’King’中各个字符的值比较。例如，与LAST_NAME='Kaufling’的行进行比较，两个字符串的第一个字符是’K'，其对应的值是75。所以，第二个字符(i=105)与(a=97)比较。因为(97 < 105)或(a < i), 'Kaufling' < 'King'，于是选择该行。使用不等于运算符比较数值数据的过程同样适用于字符数据。唯一的区别是Oracle服务器会根据某些数据库设置，把字符数据隐式转换为数值。

对日期值的不等于比较与字符数据的比较过程类似。Oracle服务器将日期保存为内部数字格式，在条件内比较这些值。

2．使用BETWEEN运算符进行范围比较

BETWEEN运算符测试列或者表达式值是否介于两个边界值的范围之内。被测试值至少与低边界值相等，或者最大与高边界值相等，或者在这个范围之内，这样条件才为true。

假设要查询薪水介于$3400～$4000之间的员工的姓。一种使用BETWEEN运算符的可能解决方案如下所示：

        select last_name from employees where salary between 3400 and 4000;

用BETWEEN运算符指定条件相当于使用两个基于不等于的条件：

        select last_name from employees where salary >=3400 and salary <=4000;

使用BETWEEN运算符指定范围条件更简洁、更方便。

3．用IN运算符进行集合比较

IN运算符测试某项是不是一组字面值的成员。用逗号将字面值分开，并将它们包含在圆括号内，通过这样的方式来指定集合。如果字面值是字符或者日期值，那么必须使用单引号界定它们。在集合中可以包含任意多个字面值。考察下面的示例：

        select last_name from employees where salary in (1000,4000,6000);

每行中的SALARY值都会与集合中指定的字面值比较相等性。如果SALARY值等于1000、4000或者6000，那么会返回该行的LAST_NAME值。下面两条语句说明IN运算符在字符和日期数据中的用法：

        select last_name from employees
        where last_name in ('King', 'Kochhar');

       select last_name from employees
        where hire_date in ('30-JAN-2004', '21-SEP-2005');

4．使用LIKE运算符进行模式比较

LIKE运算符专用于字符数据，它提供了搜索字母或者单词的强大机制。LIKE还有两个通配符：百分比符号(%)和下划线字符(_)。百分比符号用来指定零个或多个通配符字符，而下划线字符指定一个通配符字符。通配符字符可以表示任何字符。

可以使用下面的查询提供名字以字母“A”开头的员工列表：

        select first_name from employees where first_name like 'A%';

与FIRST_NAME列比较的字符字面值包含在单引号内，就像正常的字符字面值一样。另外，它有百分比符号，在LIKE运算符的上下文中，它有特殊含义。百分比符号替代附加给字母A的零个或者更多字符。通配符字符可以出现在字符字面值的开头、中间或者结尾。它们甚至能够单独出现，例如：

        where first_name like '%';

这里，将返回包含FIRST_NAME值不是空值的所有行。当使用LIKE运算符时，通配符不是强制的。在这种情况下，LIKE运算符就像是测试准确字符匹配的等于运算符；因此，下面两个WHERE子句是相等的：

        where last_name like 'King';
        where last_name = 'King';

下划线通配符替代字面值中的一个字符。假设要搜索员工，这些员工姓氏的长度为4个字母，以字母“K”开头，第二个字母未知，结尾为ng。可以执行下面的语句：

        where last_name like 'K_ng';

如图7-9所示，在一个WHERE条件中可以单独、一起甚至是多次使用这两个通配符。第一个查询检索这样的记录：其中COUNTRY_NAME以字母“I”开头，后面是一个或者多个字符，其中一个字符必须是小写字母“a”。

第二个查询检索的国家其名称以字母“i”作为第5个字母。COUNTRY_NAME值的长度和它们的开头字母并不重要。WHERE子句中小写字母“i”之前的4个下划线通配符符号表示4个字符(可能是任何字符)。第5个字母必须是“i”，百分比符号指定从第6个字符开始， COUNTRY_NAME可以有零个或者多个字符。

当搜索包含百分比或者下划线字符的字面值时，会出现什么情况？与ESCAPE字符结合使用ESCAPE标识符，可以转义(或者作为正常的非特殊符号对待)自然出现的下划线字符。在下面的例子中，返回以三个字符“SA_”开头的所有JOB_ID值：

        select job_id from jobs
        where job_id like 'SA\_%' escape '\';

一般来说，ESCAPE字符是反斜杠符号，但它不一定要这样。下面的语句与上面的语句对等，但使用美元符号($)作为ESCAPE字符。

        select job_id from jobs
        where job_id like 'SA$_%' escape '$';

当百分比符号作为字符数据自然出现时，同样可以转义它。

练习7-3 使用LIKE运算符

构造一个查询从DEPARTMENTS表中检索以“ing”这三个字符结尾的部门名称列表。

(1) 启动SQL*Plus，连接到HR模式。

(2) WHERE子句必须执行DEPARTMENT_ NAME列值与以零个或者多个字符开头但以三个特定字符“ing”结尾的模式的比较。用于字符模式匹配的运算符是LIKE运算符。DEPARTMENT_ NAME列必须符合的模式是’%ing'。

(3) 因此，正确的查询是：

        select department_name from departments where department_name like '%ing';

5．使用IS NULL运算符进行NULL比较

数据库表中不可避免会出现NULL值。有时要求只搜索特定列中是NULL值的那些记录。IS NULL运算符只选择特定列值为NULL的行。使用IS NULL运算符而不是“等于”运算符(=)来测试列值是不是等于NULL。

例如下面的查询，它从EMPLOYEES表中提取某些行的LAST_NAME列，这些行的COMMISSION_ PCT列中保存有NULL值：

        select last_name from employees
        where commission_pct is null;

这个WHERE子句只检索COMMISSION_PCT值为NULL的记录。

7.4.3 布尔运算符

布尔(Boolean)或者逻辑(logical)运算符允许在SELECT语句的WHERE子句中指定多个条件。这提供了更精练的数据提取能力。假设要提取FIRST_NAME值以字母“J”开头并且COMMISSION_PCT值大于10%的员工记录。首先，必须将EMPLOYEES表中的数据限制为这样的FIRST_NAME值“J%”；第二，必须测试记录的COMMISSION_PCT值以确定它们是不是大于10%。使用布尔AND运算符可以将这两个独立条件联系起来，从而能够在WHERE子句中同时使用它们。使用OR、AND和NOT布尔运算符可以检索符合任意或者全部条件，或者符合一个或多个条件的否定条件的结果集。

1．AND运算符

AND运算符将一些条件合并成一个更大的条件，结果集中包含的行必须符合这个大条件。如果用AND运算符连接WHERE子句中指定的两个条件，那么在检索之前，就会连续测试行，看它是否满足这两个条件。如果它不满足或者只满足其中一个，就不会返回行。使用下面的查询可以检索FIRST_NAME值以字母“J”开头并且COMMISSION_PCT值大于10%的员工记录：

        select first_name, last_name, commission_pct, hire_date from employees
        where first_name like 'J%' and commission_pct > 0.1;

注意，现在WHERE子句有两个条件，但只有一个WHERE关键字。AND运算符分隔这两个条件。要进一步指定更多强制条件，可以添加它们，并确保用AND运算符分隔它们。可以指定任意多个条件。但是请记住，指定的AND条件越多，查询条件就越苛刻。

2．OR运算符

OR运算符分隔多个条件，结果集中包含的行必须至少满足其中一个条件。如果用OR运算符连接WHERE子句中指定的两个条件，那么在检索之前，就会连续测试行，看它满足一个条件还是两个条件。对于返回的记录而言，只要符合OR条件中的一个条件就行了。如果这些条件都不满足，就不会返回行。检索员工记录，要求他们的FIRST_NAME值以字母“B”开头或者COMMISSION_ PCT值大于35%，该查询如下所示：

        select first_name, last_name, commission_pct, hire_date from employees
        where first_name like 'B%' or commission_pct > 0.35;

注意，用OR关键字分隔这两个条件，会返回所有FIRST_NAME值以大写字母“B”开头的员工记录，不管COMMISSION_PCT值是什么(即使它们是NULL)。同时还会返回COMMISSION_PCT值大于35%的所有记录，不管FIRST_NAME值以什么字母开头。

通过用OR运算符分隔条件，还可以指定更多OR条件。指定的OR条件越多，查询的限制就越宽松。

3．NOT运算符

NOT运算符会否定条件运算符。结果集中包含的行必须符合条件的逻辑非。NOT运算符可以否定条件运算符，如表7-7中的WHERE子句所示。

NOT运算符否定条件中的比较运算符，不管它是等于、不等于、基于范围、模式匹配、集合成员还是NULL测试运算符。

7.4.4 优先规则

在WHERE子句的上下文中可以使用算术、字符、比较和布尔表达式。但这些运算符如何相互交互呢？前面提及的运算符的优先层次结构如表7-8所示。

表达式中相同优先级的运算符按从左到右的顺序计算。当NOT运算符修改LIKE、IS NULL和IN比较运算符时，优先级与这些运算符的肯定形式相同。

例如下面的SELECT语句，它说明了各种不同运算符之间的交互：

        select last_name, salary, department_id, job_id, commission_pct
        from employees
        where last_name like '%a%' and salary > department_id * 200
        or
        job_id in ('MK_REP', 'MK_MAN') and commission_pct is not null

依据两个离散条件从EMPLOYEES表中投影LAST_NAME、SALARY、DEPARTMENT_ID、JOB_ID和COMMISSION_PCT列。第一个条件检索其LAST_NAME字段中包含字符“a”，且SALARY值大于DEPARTMENT_ID值的200倍的记录。查询会在不等于运算符之前计算DEPARTMENT_ ID和200的乘积，因为乘法的优先级比不等于运算符的优先级高。

第二个条件返回JOB_ID值为MK_MAN或者MK_REP的那些行，其中COMMISSION_PCT值为非空。该查询返回的行要满足由OR分隔的两个条件中的任一个。改变WHERE子句中条件的顺序就是依据运算符的不同优先级改变其含义。考虑下面的查询：

        select last_name, salary, department_id, job_id, commission_pct
        from employees
        where last_name like '%a%' and salary > department_id * 100 and commission_pct
    is not null
        or
        job_id = 'MK_MAN'

这个查询有两个组成条件。第一个条件检索其LAST_NAME字段中包含字符“a”，且SALARY值大于DEPARTMENT_ID值的100倍，且其中COMMISSION_PCT值为非空的记录。第二个条件返回JOB_ID值为MK_MAN的行。这个查询返回这样的行——它符合条件1或者(OR)条件2，但不需要同时满足两个条件。