Oracle筆記（四） 簡單查詢、限定查詢、資料的排序

標籤：字元   管理系統   數學   返回   情況下   字串表   scott   匹配   strong   

Oracle筆記（四） 簡單查詢、限定查詢、資料的排序

一、簡單查詢

SQL（Structured Query Language) 結構化查詢語言 (SQL)，是一種資料庫查詢和程式設計語言，用於存取資料以及查詢、更新和管理關聯性資料庫系統。ANSI（美國國家標準局）聲稱，SQL是關聯式資料庫管理系統的標準語言。

Oracle資料庫之所以發展的很好，主要也是因為Oracle是全世界最早採用SQL語句的資料庫產品。

SQL功能強大，概括起來，它可以分成以下幾組：

• DML（Data Manipulation Language） 資料操作語言，用於檢索或者修改資料
• DDL（Data Definition Language） 資料定義語言 (Data Definition Language)，用於定義資料的結構，建立、修改或者刪除資料庫物件
• DCL（Data Control Language） 資料控制語言，用於定義資料庫的許可權

簡單查詢指的是查詢出一張表中的所有的資料，簡單查詢的文法如下：

SELECT [DISTINCT] * | 欄位 [別名] [,欄位 [別名]]

FROM 表名稱 [別名];

範例：查詢dept表的全部記錄

SELECT * FROM dept;

範例：查詢出每個僱員的編號、姓名、基本工資

SELECT empno, ename, sal FROM emp;

範例：查詢出每個僱員的職位

SELECT job FROM emp;

這個時候發現查詢出來的job內容中出現了重複的資料，而之所以資料會有重複，主要的原因是現在沒有消除掉重複記錄，可以使用DISTINCT消除掉所有的重複內容：

SELECT DISTINCT job FROM emp;

但是對於重複資料，指的是一行中的每個列的記錄都重複，才叫重複資料。

範例：查詢出每個僱員的姓名、職位

SELECT DISTINCT ename, job FROM emp;

在進行簡單查詢的操作之中，也可以使用各個數學的四則運算子。

範例：要求顯示每一個僱員的姓名、職位、基本年薪

SELECT ename, job, sal*12 FROM emp;

但是這個時候顯示列上出現了一個“SAL*12”，這個肯定是顯示的查詢列，但是這個列名稱不方便瀏覽，所以此時可以起一個別名。

SELECT ename, job, sal*12 income FROM emp;

但是對於別名，建議不要使用中文，只要是程式的開發，都要迴避中文。

範例：由於公司的福利很高，每個月都有200元的飯食補助以及100元車費補貼，這個時候的年薪

SELECT ename, job, (sal+300)*12 income FROM emp;

範例：公司每年的年底都會多發一個月的基本工資

SELECT ename, job, (sal+300)*12+sal income FROM emp;

在簡單查詢中也可以使用“||”串連查詢的欄位。

範例：觀察“||”的使用

SELECT empno || ‘,‘ || ename FROM emp;

 

由於“,”屬於原樣輸出的字串，所以必須使用“ ‘ ”括起來，即：在SQL語句之中，“ ‘ ”表示的是字串。

範例：要求現在的資料庫按照如下的格式顯示：

“僱員編號是：7369的僱員姓名是：SMITH，基本工資是：800，職位是：CLERK!”

現在相當於尋找：empno、ename、sal、job欄位，並且使用“||”進行串連。

SELECT ‘僱員編號是：‘ || empno || ‘的僱員姓名是：‘ || ename  || ‘，基本工資是：‘ || sal || ‘，職位是：‘ || job || ‘! ‘ 僱員資訊 FROM emp;

 

一定要記住，別名上的內容不要使用“ ‘ ”括起來，而只有在SELECT子句之中出現的內容使用“ ‘ ”。

二、限定查詢

在之前的簡單查詢中，是將所有的記錄進行顯示，但是現在可以對顯示的記錄進行過濾的操作，而這就屬於限定查詢的工作了，限定查詢就是在之前文法的基礎上增加了一個WHERE子句，用於指定限定條件，此時文法如下：

SELECT [DISTINCT] * | 欄位 [別名] [,欄位 [別名]]

FROM 表名稱 [別名]

[WHERE 條件(S)];

在WHERE子句之後可以增加多個條件，最常見的條件就是基本的關係運算：>、>=、<、<=、!=(<>)、BETWEEN、AND、LIKE、IN、IS NULL、AND、OR、NOT；

1、關係運算

範例：要求查詢出基本工資高於1500的所有僱員資訊

SELECT * FROM emp WHERE sal>1500;

範例：查詢出所有職位是辦事員的僱員資訊

SELECT * FROM emp WHERE job=‘clerk‘;

這個時候沒有返回相應的查詢結果，主要原因是在Oracle資料庫中，所有的資料都是區分大小寫，所以代碼修改如下：

SELECT * FROM emp WHERE job=‘CLERK‘;

以上只是操作了一個條件，現在也可以操作多個條件，而這多個條件之間可以使用AND或OR進行串連操作。

範例：查詢工資在1500~3000之間的全部僱員資訊

SELECT * FROM emp

WHERE sal>=1500 AND sal<=3000;

範例：查詢出職位是辦事員，或者是銷售人員的全部資訊

SELECT * FROM emp

WHERE job=‘CLERK‘ OR job=‘SALESMAN‘;

範例：查詢出職位是辦事員，或者是銷售人員的全部資訊，並且要求這些僱員的工資大於1200

SELECT * FROM emp

WHERE (job=‘CLERK‘ OR job=‘SALESMAN‘) AND sal>1200;

範例：查詢所有不是辦事員的僱員資訊

SELECT * FROM emp WHERE job<>‘CLERK‘;

SELECT * FROM emp WHERE job!=‘CLERK‘;

SELECT * FROM emp WHERE NOT job=‘CLERK‘;

2、範圍判斷：BETWEEN…AND…

“BETWEEN 最小值 AND 最大值”，表示的是一個範圍的判斷過程。

範例：要求查詢出基本工資在1500~3000的僱員資訊

SELECT * FROM emp

WHERE sal BETWEEN 1500 AND 3000;

範例：現在也可以對BETWEEN…AND…操作求反

SELECT * FROM emp

WHERE NOT sal BETWEEN 1500 AND 3000;

“BETWEEN…AND…”操作符不光只是針對於數字有用，對於日期也同樣有用。

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

時間範圍：1981-01-01~1981-12-31，使用hiredate欄位表示僱傭日期；

hiredate欄位上的內容可以使用字串表示：’01-1月-1981’~’31-12月-81’

SELECT * FROM emp

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

3、判斷是否為空白：IS (NOT) NULL

使用此文法可以判斷某一個欄位上的內容是否是“null”，但是null和數字0以及Null 字元串是兩個概念。

範例：查詢出所有領取獎金的僱員資訊

SELECT * FROM emp

WHERE comm IS NOT NULL;

SELECT * FROM emp

WHERE NOT comm IS NULL;

範例：查詢出所有不領取獎金的僱員

SELECT * FROM emp

WHERE comm IS NULL;

4、指定範圍的判斷：IN操作符

IN操作符表示的是指定一個查詢的範圍，例如，現在有如下一個查詢要求：

範例：查詢出僱員編號是7369、7566、7799的僱員資訊

如果按照最早的做法，使用OR操作完成；

SELECT * FROM emp

WHERE empno=7369 OR empno=7566 OR empno=7799;

如果現在使用了新的操作符IN的話，則代碼簡單了；

SELECT * FROM emp

WHERE empno IN (7369,7566,7799);

而如果現在使用的是NOT IN呢？則表示不在指定的範圍之中。

SELECT * FROM emp

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

注意點：關於NOT IN的問題

如果現在使用了IN操作符，查詢的範圍之中存在了null，不影響查詢；

SELECT * FROM emp WHERE empno IN(7369,7566,null);

如果現在使用的是NOT IN操作符，如果查詢範圍之中有了null則表示的就是查詢全部資料。

SELECT * FROM emp WHERE empno NOT IN(7369,7566,null);

對於這個限制，現在先作為特點記住，以後會講解為什麼NOT IN之中不能出現null。

5、模糊查詢：LIKE子句

LIKE子句的功能是提供了模糊尋找的操作，例如：某些程式上出現的搜尋操作，都屬於LIKE子句的實現，但是必須提醒的，搜尋引擎上的查詢可不是LIKE。

但是要想使用LIKE子句則必須認識兩個匹配符號：

匹配單個字元：_;             ->  1個

匹配任意多個字元：%;      ->  0個、1個、多個

範例：要求查詢僱員姓名中以字母A開頭的全部僱員資訊

SELECT * FROM emp WHERE ename LIKE ‘A%‘;

範例：要求查詢出僱員姓名中第二個字母是A的全部僱員資訊

SELECT * FROM emp WHERE ename LIKE ‘_A%‘;

範例：要求查詢出僱員姓名中帶有字母A的僱員

SELECT * FROM emp WHERE ename LIKE ‘%A%‘;

現在也可以使用NOT操作，對操作進行求反的功能：

SELECT * FROM emp WHERE ename NOT LIKE ‘%A%‘;

但是對於LIKE子句，不一定只能在字串資料上表示，可以在任意的資料上表示：

SELECT * FROM emp WHERE ename LIKE ‘%1%‘ OR hiredate LIKE ‘%1%‘ OR sal LIKE ‘%1%‘;

說明：關於LIKE子句的使用

在開發之中，資料庫的模糊查詢肯定使用LIKE子句，但是在使用LIKE子句的時候有一個最大的注意點：如果在模糊查詢上不設定任何的查詢關鍵字的話（‘%%‘）則表示查詢全部記錄：

SELECT * FROM emp WHERE ename LIKE ‘%%‘ OR hiredate LIKE ‘%%‘ OR sal LIKE ‘%%‘;

這一個特點可以協助使用者節約很多的代碼，所以一定要記住。

三、資料的排序

當資料返回查詢結果之後，所有的資料預設情況下是按照僱員編號排序的，當然，現在也可以使用”ORDER BY”子句指定所需要的排序的操作列，而這個時候的SQL文法如下：

 

SELECT [DISTINCT] * | 欄位 [別名] [,欄位 [別名]]

FROM 表名稱 [別名]

[WHERE 條件(S)]

[ORDER BY 欄位 [ASC|DESC] [,欄位 [ASC|DESC],…]];

 

“ORDER BY”子句是寫在所有的SQL語句最後的內容，而且對於排序有以下幾點說明：

排序的時候可以指定多個排序的欄位；

排序的方式有兩種：

• 升序（ASC）：預設，不寫也是升序；
• 降序（DESC）：使用者需要指定，由大到小排序；

範例：查詢所有的僱員的資訊，要求按照工資排序

SELECT * FROM emp ORDER BY sal;

SELECT * FROM emp ORDER BY sal ASC;

範例：進行降序排列

SELECT * FROM emp ORDER BY sal DESC;

範例：要求查詢出所有的僱員資訊，按照工資由高到低排序，如果工資相同，則按照僱傭日期由早到晚排序

此時肯定需要兩個欄位排序：工資（DESC），僱傭日期（ASC）；

SELECT * FROM emp ORDER BY sal DESC, hiredate ASC;

 

SQL> SELECT * FROM emp ORDER BY sal DESC, hiredate ASC;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

      7839 KING       PRESIDENT            17-11月-81           5000                    10

      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30

      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20

      7900 JAMES      CLERK           7698 03-12月-81            950                    30

      7369 SMITH      CLERK           7902 17-12月-80            800                    20

      7788 SCOTT      CLERK           7566 19-4月 -87            800                    20

已選擇14行。

 

對於排序操作，一般只在需要的地方上使用，而且一定要記住的是，ORDER BY 子句是寫在所有的SQL語句的最後的部分。

Oracle筆記（四） 簡單查詢、限定查詢、資料的排序