Oracle資料庫之五 限定查詢和排序顯示

Oracle資料庫之五 限定查詢和排序顯示

五、限定查詢和排序顯示

5.1、限定查詢

5.1.1 認識限定查詢

例如：如果一張表中有 100w 條資料，一旦執行了 “ SELECT * FROM 表 ” 語句之後，則将在螢幕上顯示表中全部資料行的記錄，這樣既不友善浏覽，也可能造成當機的問題，是以此時就必須對查詢的結果進行篩選，隻選出對自己有用的資料即可，那麼就可以通過 WHERE 指定查詢的篩選條件。

​ 這麼多條資料一起顯示肯定是無法浏覽的。另外，如果資料量顯示的過多，那麼有可能出現當機的問題。是以下面首先來觀察資料量大對于查詢顯示會存在什麼問題。

​ 在進行 Oracle 資料庫安裝的時候已經安裝了樣本方案資料庫，是以現在必須将容器由 CDB 切換到 PDB 之中。

範例：以 nolog 的方式打開 sqlplus (在指令行終端)

sqlplus /nolog

範例：使用 sys 管理者登入

CONN sys/chagne_on_install AS SYSDBA;

範例：切換到 PDB 之中

ALTER SESSION SET CONTAINER=pdbmldn;

範例：打開 PDB

ALTER DATABASE pdbmldn OPEN;

範例：檢視 sh 使用者的資料表内容

SELECT COUNT(*) FROM sh.sales;

​ 現在這張表中存在有 9w 多條記錄，如果直接發出如下指令：

SELECT * FROM sh.sales;

​ 現在顯示結果一直不停變換，無法檢視，按 Ctrl+C 停止。是以現在資料量一大，那麼是不可能這樣直接檢視全部資料的，是以全部資料行的顯示根本就不可能使用。很多時候往往需要針對所需要的資料進行篩選，而篩選就是限定查詢的功能。

​ 現在連接配接到 c##scott 使用者:

conn c##scott/tiger;

5.1.2 限定查詢文法：

SELECT [DISTINCT] * | 列名稱 [AS] [列别名],列名稱 [AS] [列别名],...

FROM 表名稱[表别名]

[WHERE 條件( s )];

​ 在這個文法之中，就是比之前的文法多了一個 WHERE 子句，在 WHERE 子句之中可以設定一系列的過濾條件。而這些條件可以設定多個，那麼這多個條件之間就可以利用邏輯運算進行連接配接。

邏輯運算符共有以下三種：

與（AND）：連接配接多個條件，多個條件同時滿足時才傳回 TRUE，有一個條件不滿足結果就是 FALSE ；

或（OR）：連接配接多個條件，多個條件之中隻要有一個傳回 TRUE ，結果就是 TRUE ，如果多個條件傳回的都是 FALSE ，結果才是 FALSE ；

非（NOT）：求反操作，可以将 TRUE 變 FALSE ，FALSE 變 TRUE 。

邏輯真值表：

NO. 條件 x 條件 y x AND y x OR y NOT x

1 TRUE TRUE TRUE TRUE FALSE

2 TRUE NULL NULL TRUE FALSE

3 TRUE FALSE FALSE TRUE FALSE

4 NULL TRUE NULL TRUE NULL

5 NULL NULL NULL NULL NULL

6 NULL FALSE FALSE NULL NULL

7 FALSE TRUE FALSE TRUE TRUE

8 FALSE NULL FALSE NULL TRUE

9 FALSE FALSE FALSE TRUE TRUE

範例**：統計出基本工資高出 1500 的全部雇員資訊

​ 現在的查詢已經出現了一個條件要求，是以在這種情況下就必須使用 WHERE 子句進行條件的設定。

SELECT * FROM emp WHERE sal>1500;

​ 現在可以發現并不是所有的資料都顯示了，隻是部分的資料顯示，而且這部分都是滿足條件的資料。

​ 現在對于 SQL 文法而言，就具備了三個子句：

​ 第一步：執行 FROM 子句，來控制資料的來源

​ 第二步：執行 WHERE 子句，使用限定符進行資料行的過濾

​ 第三步：執行 SELECT 子句，确定要顯示的資料列

​

5.1.3 對資料進行限定查詢

​ 在之前所使用的 “ > ” 是一個關系運算符，在标準 SQL 之中定義了許多的運算符。

​ 常用限定運算符：

NO 運算符 符号 描述

1 關系運算符 >、<、>=、<=、=、!=、<> 進行大小或相等的比較，其中不等于有兩種：!= 和 <>

2 判斷 null IS NULL 、IS NOT NULL 判斷某一列的内容是否是 null

3 邏輯運算符 AND 、OR、NOT AND 表示多個條件必須同時滿足，OR 表示隻需要有一個條件滿足即可，NOT 表示條件取反，即：真變假，假變真

4 範圍查詢 BETWEEN 最小值 AND 最大值 在一個指定範圍中進行查找，查找結果為：“ 最小值 <= 内容 <= 最大值 ”

5 範圍查詢 IN 通過 IN 可以指定一個查詢的範圍

6 模糊查詢 LIKE 可以對指定的字段進行模糊查詢

5.1.3.1 關系運算符

​ 關系運算就是确定大小、相等關系的比較。

範例：要求查詢出所有基本工資小于等于2000的全部雇員資訊

SELECT *

FORM emp

WHERE sal<=2000;

範例：根據之前的查詢結果發現 SMITH 的工資最低，現在希望可以取得 SMITH 的詳細資料。

WHERE ename='SMITH';

範例：查詢出所有辦事員（CLERK）的雇員資訊

WHERE job='CLERK';

​ 但是在使用關系運算符判斷字元資料的時候請一定要主要大小寫的編寫問題。因為 Oracle 是區分大小寫的。

範例：錯誤的代碼

WHERE job='clerk'; // 不會有結果傳回

範例：取得了所有辦事員的資料之後，為了和其他職位的雇員對比，現在決定再查詢出所有不是辦事員的雇員資訊。

​ 既然現在職位不是辦事員，那麼肯定使用不等于符号（<>, !=）

​ 實作一：

SELECT *

WHERE job<>'CLERK';

​ 實作二：

WHERE job!='CLERK';

範例：查詢出工資範圍在 1500 ~ 3000 （都包含）的全部雇員資訊

​ 現在這個判斷是兩個條件，而且這兩個條件肯定需要同時滿足，那麼就使用 AND 進行條件的連接配接

WHERE sal>=1500 AND sal<=3000;

範例：查詢職位是銷售，并且基本工資高于 1200 的所有雇員資訊

WHERE job='SALESMAN' AND sal>1200;

範例：查詢出 10 部門中的經理或者是 20 部門的業務員的資訊

WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK');

範例：查詢不是辦事員的且基本工資大于 2000 的全部雇員資訊

​ 實作一：基本實作

WHERE job!='CLERK' AND sal>2000;

WHERE job<>'CLERK' AND sal>2000;

​ 實作二：使用 NOT 對條件求反

WHERE NOT(job='CLERK' OR sal<=2000);

5.1.3.2 範圍查詢

範例：使用 BETWEEN...AND... 操作符查詢工資範圍在 1500（含）~ 3000（含）的全部雇員資訊

WHERE sal BETWEEN 1500 AND 3000;

範例：查詢出在 1981 年雇傭的全部雇員資訊

WHERE hiredate BETWEEN '01-1月-81' AND '31-12月-1981';

​ 實際上這裡就實作了日期和字元串資料之間的轉換操作的功能。

5.1.3.3 null 判斷：

​ 判斷内容是否為 null ： IS NULL , IS NOT NULL

文法：

判斷為 NULL：字段|值 IS NULL;

判斷不為 NULL：字段|值 IS NOT NULL; ( NOT 字段|值 IS NULL; )

​NULL 是一個未知的資料，是以對于 NULL 的處理，如果直接利用關系運算判斷，是不會有結果的

範例：利用 = 進行 NULL 比較

WHERE comm=null AND empno=7369;

​ 結果顯示沒有任何資料傳回，因為 NULL 不能使用 = 判斷。

範例：查詢出所有領取傭金的雇員的完整資訊

​ 傭金的字段是 comm，領取傭金的概念就屬于傭金不為null。

​ 實作一：直接使用IS NOT NULL 完成

WHERE comm IS NOT NULL;

​ 實作二：使用IS NULL 并使用NOT 求反完成

WHERE NOT comm IS NULL;

範例：查詢出所有不領取傭金的雇員的完整資訊

WHERE comm IS NULL;

範例：列出所有的不領取獎金的雇員，而且同時要求這些雇員的基本工資大于2000的全部雇員資訊

WHERE comm IS NULL AND sal > 2000;

範例：找出不領取傭金或領取的傭金低于100的員工

WHERE comm IS NULL OR comm < 100;

範例：找出收取傭金的員工的不同工作

​ 既然現在要找的是職位，那麼很有可能出現重複，重複發資料就必須使用 DISTINCT 消除。

SELECT DISTINCT job

5.1.3.4 清單範圍查找：IN ， NOT IN

​ 所謂的清單範圍指的是給了使用者固定的幾個參考值，隻要符合這個值就滿足條件。

在指定資料範圍内：字段|值 IN (值,值,...);

不在指定資料範圍内：字段|值 NOT IN (值,值,...);

範例：查詢出雇員編号是7369,7788,7566的雇員資訊

​ 那麼面對這樣的操作，如果此時不使用IN判斷符就可以利用多個條件并且使用OR 進行連接配接。

WHERE empno = 7369 OR empno = 7788 OR empno = 7566;

​ 下面使用優秀代碼 IN 實作

WHERE empno IN (7369,7788,7566);

範例：現在查詢除了7369,7788,7566之外的雇員資訊

WHERE empno NOT IN (7369,7788,7566);

但是在使用NOT IN 操作的時候有一點需要注意，關于NULL的問題：

如果使用的是IN 操作符判斷的範圍資料之中包含了NULL，那麼不會影響最終的查詢結果。

WHERE empno IN (7369,7788,null);

但是如果使用的是NOT IN，裡面有NULL，直接的後果就是沒有任何資料顯示。

WHERE empno NOT IN (7369,7788,null);

使用NOT IN 或 IN 其目的隻是顯示部分内容，如果說現在有一列資料不可能為NULL，并且 NOT IN 裡面判斷 null 的條件滿足了，那麼就表示的是查詢全部資料。這樣就有可能導緻取得的資料量過多導緻程式當機。

是以，NOT IN 中強加了一個限制，不能有 null，有 null 就沒有資料，這是一個死限制。

5.1.3.4 模糊查詢: LIKE ，NOT LIKE

滿足模糊查詢：字段|值 LIKE 比對标記

不滿足模糊查詢：字段|值 NOT LIKE 比對标記

如果現在想對查詢某一列進行模糊查詢，可以使用 LIKE 子句完成，通過 LIKE 可以進行關鍵字的模糊查詢，在 LIKE 子句中有兩個通配符：

百分号（%）：可以比對任意類型和長度的字元，如果是中文則使用兩個百分号（%%）

下劃線（_）：比對單個任意字元，它常用來限制表達式的字元長度

範例：查詢出雇員姓名是以 S 開頭的全部雇員資訊

​ 證明 S 之後的内容可以是任意的資料，可能是 0位、1位或者多位。

WHERE ename LIKE 'S%';

範例：查詢出雇員姓名的第二個字母是 M 的全部雇員資訊

​ 現在隻是第二個字母，那麼證明第一個字母可以任意了，是以使用 “_” 。

WHERE ename LIKE '_M%';

範例：查詢出雇員姓名中任意位置包含字母 F 的雇員資訊

​ 現在可能是開頭，也可能是結尾，或者是在中間，是以就必須考慮到前後都有的問題，那麼使用% 。

WHERE ename LIKE '%F%';

範例：查詢雇員姓名長度為6或者是超過6個的雇員資訊。

​ 姓名的長度為6，那麼肯定可以寫6個 “_”，如果可以超過6，就加一個% 。

WHERE ename LIKE '______%';

範例：查詢出雇員基本工資中包含1 或者是在81年雇傭的全部雇員

​ 在之前的所有查詢之中都是針對于字元資料進行的操作，而對于 LIKE 而言，也可以在數字或者；日期類型上使用。

WHERE sal LIKE '%1%' OR hiredate LIKE (%81%);

但是有一點需要提醒的是，如果在設定模糊查詢的時候不設定關鍵字，就表示查詢全部，如：

WHERE sal LIKE '%%'

OR hiredate LIKE (%%) 
OR ename LIKE '%%' 
OR job LIKE '%%';           

​ 結果顯示全部資料。

範例：找出部門10中所有經理，部門20中所有辦事員，既不是經理又不是辦事員但薪金大于或等于2000的所有員工的詳細資料，并且要求這些雇員姓名之中含有字母 S 或字母 K。

​ 現在存在于以下幾個條件：

​ 條件一：10部門的經理

​ 條件二：20部門的辦事員

​ 條件三：不是經理和辦事員，但是薪金大于或等于2000

​ 條件四：以上所有條件滿足之後再過濾，包含字母 S 或字母 K

WHERE ((deptno = 10 AND job = 'MANAGER')

OR (deptno = 20 AND job = 'CLERK')
    OR (job NOT IN (MANAGER,CLERK) AND sal >= 2000))
    AND (ename LIKE '%S% OR ename LIKE '%K%');           

​ 小結：

限定排序主要使用 WHERE 子句，用于對選取的資料進行控制。

限定查詢主要的運算符：關系運算、BETWEEN...AND、IN、IS NULL、LIKE

5.2、排序顯示

5.2.1 認識排序

​ 傳統資料查詢的時候隻會按照設定的主鍵排序。如果現在希望對指定的列進行排序的操作，那麼就必須通過 ORDER BY 子句完成控制。

5.2.2 排序文法

SELECT [DISTINCT] * |列名稱 [AS] 列别名,列名稱 [AS] 列别名

FORM 表名稱 表别名

[WHERE 條件(S)]

[ORDER BY 排序的字段|列索引序号 ASC | DESC,

排序的字段2 ASC | DESC ...]...;           

在 ORDER BY 子句之中可以指定要進行排序的字段，而後字段有兩種排序模式：

升序：ASC ，預設

降序：DESC ，需要編寫的。

在所有的子句之中，一定要記住，ORDER BY 子句是放在查詢語句的最後一行，是最後一個執行的，它的執行順序：FROM、WHERE、SELECT、ORDER BY ，既然 ORDER BY 在 SELECT 之後執行，那麼就表示 ORDER BY 子句可以使用 SELECT 子句之中設定的别名。

範例：查詢雇員的完整資訊并且按照基本工資由高到低進行排序

ORDER BY sal DESC;

範例：修改之前的查詢，要求按照基本工資由低到高進行排序

ORDER BY sal;

ORDER BY sal ASC;

範例：查詢出所有辦事員（CLERK）的詳細資料，并且按基本工資由低到高排序

​ 現在不再是針對所有的資料進行排序，需要對資料執行篩選，那麼就利用 WHERE 子句完成。

WHERE job = 'CLERK'

範例：查詢所有雇員資訊，要求按照基本工資由高到低排序，如果工資相等則按照雇傭日期進行排序，按照由早到晚的順序

​ 現在的排序需要設定兩個排序的字段：sal（DESC），hiredate（ASC）

ORDER BY sal DESC,hiredate ASC;

​ 顯示結果中有點小問題，本程式的文法沒有問題，有問題的是在于資料上。因為現在的資料都是後期處理的結果，如果要想正常的觀察資料，那麼可以将資料庫切換到 PDB 中，找到原始的 scott 資料。

​ 方法：在 DBA_Connection 中先執行下面兩行代碼後繼續查詢

ALTER SESSION SET CONTAINER = pdbmldn;

FROM scott.emp

ORDER BY sal DESC ,hiredate ASC;

但是對于排序，除了使用字段之外，也可以設定一個序号，但是此操作不建議使用。

SELECT empno, ename, sal, job

ORDER BY 3 DESC;

​ 上面兩個語句執行效果一樣，但個人認為，在編寫排序的時候還是建議寫上字段，不要用序号，不友善。

小結

SQL 文法：

SELECTL [DISTINCT] * |列 AS, 列 AS,...

FROM 表名稱 [别名]

[WHERE 限定條件(s)]

ORDER BY 排序字段 [ASC|DESC...]];

使用 ORDER BY 子句可以對查詢結果進行排序，ORDER BY 子句一定要寫在所有查詢語句的最後。

說明：本學習資料是根據李興華的Oracle開發實戰經典整理

原文位址

https://www.cnblogs.com/duncan1863/p/11433596.html