Sql Certificate 简明教程

SQL - Restricting and Sorting Data

SELECT 语句的基本功能是选择、投影和联接。从表中显示特定列称为投影操作。现在，我们将重点放在显示特定输出行上。这称为选择操作。可以通过向 SELECT 查询添加 WHERE 子句来选择特定行。实际上, WHERE 子句刚好在 SELECT 查询层次结构中的 FROM 子句之后出现。在所有情况下都必须保持此顺序。如果违反，Oracle 将引发异常。

Syntax:

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

在语法中，

  1. WHERE 子句是关键字

  2. [condition] 包含列名、表达式、常量、常量和比较运算符。

假设你的经理正在为你的组织制定季度预算。作为此活动的一部分，必须制作一份每个员工基本信息清单，但仅针对年薪至少 25,000 美元的员工。以下 SQL 查询将完成此任务。请注意 WHERE 子句以粗体显示。

SELECT Employee_ID, Last_Name, First_Name, Salary
FROM employees
WHERE Salary >= 25000;

EMPLOYEE_ID  LAST_NAME        FIRST_NAME       SALARY
----------   ---------------  ---------------  -----------
88303        Jones            Quincey          $30,550.00
88404        Barlow           William          $27,500.00
88505        Smith            Susan            $32,500.00

3 rows selected

Points to be noted -

  1. SELECT 子句只能包含一个 WHERE 子句。但是，可以使用 AND 或 OR 运算符向 WHERE 子句追加多个筛选条件。

  2. 谓词子句中的列、文字或表达式必须是类似的或可相互转换的数据类型。

  3. 列别名不能用于 WHERE 子句。

  4. 字符文字必须用单引号括起来，并且区分大小写。

  5. 日期文字必须用单引号括起来，并且格式敏感。默认格式为 DD-MON-RR 。

Comparison Operators

比较运算符用于谓词中比较一个术语或操作数与另一个术语。SQL 提供了全面的等式、不等式和杂项运算符。它们可以根据 SELECT 查询中的数据和筛选条件逻辑使用。在 WHERE 子句中使用比较运算符时，运算符两侧的参数（你正在比较的对象或值）既可以是列名，也可以是特定值。如果使用特定值，则该值必须是数值或文字字符串。如果值是字符字符串或日期，你必须将值输入单引号 ('') 内。

Oracle 有九个比较运算符用于等式或不等式条件。

Operator  Meaning
=         equal to
<         less than
>         greater than
>=        greater than or equal to
<=        less than or equal to
!=        not equal to
<>        not equal to

其他 Oracle 运算符是 BETWEEN..AND、IN、LIKE 和 IS NULL。

The BETWEEN Operator

BETWEEN 运算符可用于比较一定范围内的列值。指定的范围必须具有下限和上限，并且在比较期间两者都包含在内。它的用法类似于复合不等式运算符 (⇐ 和 >=)。它可用于数值、字符和日期类型的值。

例如，SELECT 查询中的 WHERE 条件 SALARY BETWEEN 1500 AND 2500 将列出薪水在 1500 和 2500 之间的那些员工。

The IN Operator

IN 运算符用于在给定的值集中测试列值。如果列可以等同于给定集合中的任何值，则条件有效。使用 IN 运算符定义的条件也称为成员资格条件。

例如，SELECT 查询中的 WHERE 条件* SALARY IN (1500, 3000, 2500) * 将限制薪水为 1500、3000 或 2500 的行。

The LIKE Operator

LIKE 运算符用于在 SELECT 查询中进行模式匹配和通配符搜索。如果列值的一部分未知，则可以使用通配符替换未知部分。它使用通配符运算符来构建搜索字符串，因此搜索称为通配符搜索。这两个运算符是百分位（'%'）和下划线（' '). Underscore (' '），前者替代单个字符，而后一个替代多个字符。它们还可以组合使用。

例如，下面的 SELECT 查询列出姓氏以“SA”开头的那些员工的姓氏。

SELECT first_name
FROM employees
WHERE last_name LIKE 'SA%';

IS (NOT) NULL Conditions

需要注意的是，不能使用等式运算符测试 NULL 值。这是因为 NULL 值是未知且未分配的，而等式运算符测试的是一个确定的值。IS NULL 运算符充当等式运算符来检查列的 NULL 值。

例如，SELECT 查询中的 WHERE 条件* COMMISSION_PCT IS NULL * 将列出佣金百分比为 NULL 的员工。

Logical Operators

可以向 WHERE 子句谓词添加多个筛选条件。可以使用逻辑运算符 AND、OR 和 NOT 将多个条件组合在一起。

  1. AND：联接两个或更多个条件，并且仅在所有条件为真时返回结果。

  2. OR：连接两个或更多的条件，并且当任何条件为真时返回结果。

  3. NOT：否定其后的表达式。

AND 运算符将 WHERE 子句中的两个或更多条件连接起来，并且仅当所有条件都为真时才返回 TRUE。假设一位经理需要一份女性员工列表。此外，该列表应该只包括姓氏以字母“E”开头或出现在字母表中更靠后的员工。此外，结果表应按员工姓氏排序。有两条简单的条件需要满足。WHERE 子句可以写成：WHERE Gender = 'F' AND last_name > 'E'。

SELECT last_name "Last Name", first_name "First Name", Gender "Gender"
FROM employees
WHERE Gender = 'F' AND last_name > 'E'
ORDER BY last_name;

OR 运算符在 WHERE 子句中连接多个条件，并且如果任一条件返回真，则返回 TRUE。假设您组织经理的要求发生了轻微的变化。需要另一个员工列表，但在该列表中，员工应：(1) 是女性，或 (2) 姓氏以字母“T”或字母表中更靠后的字母开头。结果表应按员工姓氏排序。在这种情况下，为了满足查询，可以满足这两个条件中的任何一个。应将女性员工与满足第二个条件的姓名员工一起列出。

NOT 运算符用于否定表达式或条件。

The ORDER BY Clause

当您只显示几行数据时，可能不需要对输出进行排序；但是，当您显示多行时，对信息进行排序可能有助于经理做出决策。可以使用可选的 ORDER BY 子句对 SELECT 语句的输出进行排序。使用 ORDER BY 子句时，您对其进行排序的列名称还必须是 SELECT 子句中指定的列名称。

下面的 SQL 查询使用 ORDER BY 子句按 last_name 列以升序对结果表进行排序。升序是默认排序顺序。

SELECT last_name, first_name
FROM employees
WHERE last_name >= 'J'
ORDER BY last_name;

last_name        first_name
---------------  ---------------
Jones            Quincey
Klepper          Robert
Quattromani      Toni
Schultheis       Robert

排序也可以基于数字和日期值。还可以基于多列进行排序。

默认情况下，ORDER BY 子句将按结果表中输出行的升序对其进行排序。我们可以使用关键字 DESC（降序的简称）启用降序排序。另一种则是按升序排列的 ASC 关键字，但由于 ASC 关键字是默认值，因此它很少使用。当使用 ASC 或 DESC 可选关键字时，它必须位于您在 WHERE 子句中对其进行排序的列名称之后。

位置排序 - 可以在 ORDER BY 子句中给出所选列列表中列的数字位置，而不是列名称。它主要用于 UNION 查询（后面讨论）。该查询按 salary 排序结果集，因为它在列列表中出现第 2 位。

SELECT  first_name, salary
FROM employees
ORDER BY 2;

Substitution Variables

当必须针对不同的输入集多次执行 SQL 查询时，可以使用替换变量。在查询执行之前，可以使用替换变量来提示用户输入。它们广泛用于基于查询的报表生成中，这些报表从用户那里获取数据范围作为条件筛选和数据显示的输入。替换变量前面加一个单和号 (&) 符号，以临时存储值。例如，

SELECT EMPLOYEE_ID, LAST_NAME, SALARY
FROM employees
WHERE LAST_NAME = &last_name
OR EMPLOYEE_ID = &EMPNO;

当执行上述 SELECT 查询时，oracle 会将“&”标识为替换变量。它会提示用户输入“last_name”和“EMPNO”的值，如下所示。

Enter value for last_name:
Enter value for empno:

一旦用户向这两个变量提供输入，就会替换值，验证并执行查询。

Points to be noted -

  1. 如果变量旨在替换字符或日期值，则字面量需要用单引号引起来。一种有用的技术是在处理字符和日期值时用单引号将和号替换变量括起来。

  2. SQL Developer 和 SQL* Plus 都支持替换变量以及 DEFINE/UNDEFINE 命令。但是，除了数据类型之外，SQL Developer 或 SQL* Plus 不支持用户输入的验证检查。

  3. 您不仅可以在 SQL 语句的 WHERE 子句中使用替换变量，还可以将替换变量用作列名称、表达式或文本的替换。

Using the Double-Ampersand Substitution Variable

当在多个地方使用相同的替换变量时，为了避免再次输入相同的数据，可以使用双和号替换。在这种情况下，一旦输入替换变量的值，它就会在所有使用实例中替换。

SELECT first_name, HIRE_DATE, SEPARATION_DATE
FROM employees
WHERE HIRE_DATE LIKE '%&DT%' AND SEPARATION_DATE '%&&DT%'

请注意，在上面的查询中 &DT 的值被替换了两次。因此，用户一旦给出的值将在两个地方替换。

The DEFINE and VERIFY Commands

在会话中设置变量的定义是由 SQL* Plus 的 DEFINE 特性设置的。可以在会话中定义变量，以避免在查询执行期间停止。Oracle 在 SQL 查询中每遇到一次就会读取相同的变量。它默认处于 ON 状态。借助 DEFINE 子句，可以在查询执行之前在命令行中声明一个变量，如 DEFINE variable=value; 。

Verify 命令验证上述替换，显示为 OLD 和 NEW 语句。它在默认情况下为 OFF，可以使用 SET 命令将其设置为 ON。

SQL> SET DEFINE ON
SQL> SET VERIFY ON
SQL> DEFINE NAME = MARTIN'
SQL> SELECT first_name, SALARY
FROM employees
WHERE first_name = '&NAME';
OLD   1: select first_name, sal from employee where first_name = '&first_name'
new   1: select first_name, sal from employee where first_name = 'MARTIN'

first_name     SALARY
-------        -------
MARTIN         5000