Oracle 数据库/限制和排序数据

    SELECT *|{[DISTINCT] column|expr [[AS] alias],...}
      FROM table
    [WHERE condition(s)]
    [ORDER BY {column, alias, expr, numeric_position} [ASC|DESC] [NULLS FIRST|NULLS LAST] ];

限制查询检索的行

编写包含 WHERE 子句以限制检索输出的查询
- 字符字符串和日期值用单引号括起来
- 字符值区分大小写，日期值区分格式
- 默认日期显示格式为 DD-MON-YY
- WHERE 子句中不能使用别名

    SELECT last_name, department_id, hire_date
    FROM   hr.employees
    WHERE department_id = 90;

    SELECT last_name, department_id, hire_date
    FROM   hr.employees
    WHERE last_name = 'King';

    SELECT last_name, department_id, hire_date
    FROM   hr.employees
    WHERE hire_date = '30-JAN-96';

列出 WHERE 子句中使用的比较运算符和逻辑运算符

运算符	含义
=	等于
>	大于
>=	大于或等于
<	小于
<=	小于或等于
<>	不等于（也可以使用 != 或 ^=）
BETWEEN ... AND ...	介于两个值之间（包含两个值）
IN (set)	匹配列表中的任何值
LIKE	匹配字符模式 '%' - 零个或多个字符；'_' - 一个字符
IS NULL	是空值
AND	如果两个条件都为真，则返回 TRUE
OR	如果两个条件中有一个为真，则返回 TRUE
NOT	如果条件为假，则返回 TRUE

    -- must specify the lower limit first
    SELECT last_name, salary
    FROM   hr.employees
    WHERE salary BETWEEN 4000 AND 5000;

    -- can also use on character value
    SELECT last_name, salary
    FROM   hr.employees
    WHERE last_name BETWEEN 'Abel' AND 'Bull'
    ORDER BY last_name;

    SELECT last_name, salary
    FROM   hr.employees
    WHERE salary in (4000,6000,8000);

    -- last name start with 'A' and 2 characters at least
    SELECT last_name, salary
    FROM   hr.employees
    WHERE last_name like 'A_%';

    -- hire date at year 1999
    SELECT last_name, salary, hire_date
    FROM   hr.employees
    WHERE hire_date like '%99';    

    -- employee doesn't report to any manager 
    SELECT last_name, salary
    FROM   hr.employees
    WHERE manager_id is null;

    -- use AND, OR, NOT operators
    SELECT last_name, job_id, salary
    FROM   hr.employees
    WHERE (job_id like 'AD%' OR job_id like 'IT%')
    AND salary > 5000
    AND NOT last_name = 'King';    

    -- use ESCAPE identifier 
    SELECT last_name, job_id
    FROM   hr.employees
    WHERE job_id like 'A_\_P%' ESCAPE '\';

描述比较运算符和逻辑运算符的优先级规则

优先级	运算符	描述
1	圆括号	圆括号内的表达式始终先计算
2	/, *	除法和乘法
3	+, -	加法和减法
4	\|\|	连接
5	=, <, >, <=, >=	相等和不等比较
6	[NOT] LIKE, IS [NOT] NULL, [NOT] IN	模式、空值和集合比较
7	[NOT] BETWEEN	范围比较
8	<>, !=, ^=	不等于
9	NOT	NOT 逻辑条件
10	AND	AND 逻辑条件
11	OR	OR 逻辑条件

排序查询检索的行

编写包含 ORDER BY 子句以对 SELECT 语句的输出进行排序的查询

 * The default sort order is ascending  
 * Null values are displayed last for ascending sequences and first for descending sequence
 * You can also sort by a column that is not in the SELECT list

    SELECT employee_id, last_name, salary*12 annsal
    FROM   hr.employees
    ORDER BY annsal DESC ;

以降序和升序排序输出

    SELECT   last_name, job_id, salary, commission_pct, salary*commission_pct "Comm"
    FROM     hr.employees
    ORDER BY commission_pct NULLS FIRST, 2 DESC, salary, "Comm";

使用“&”替换在运行时限制和排序输出

使用替换变量

使用单“&”和双“&”替换临时存储值

使用替换变量来补充以下内容

WHERE 条件
ORDER BY 子句
列表达式
表名
完整的 SELECT 语句

    --any &column_name after the &&column_name will not prompt for value again
    SELECT   employee_id, last_name, job_id, &&column_name
    FROM     hr.employees
    ORDER BY &column_name ;