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 ;