查詢 (DML 一)

以這篇的SQL當練習資料
V$VERSION 這張表可以得知Oracle的版本

※基本查詢、排序

SELECT DEPTNO, DNAME, LOC FROM dept;-- 1
SELECT * FROM dept;-- 2
SELECT d.DEPTNO,d.LOC FROM dept as d;-- 3
SELECT DISTINCT FROM dept;-- 4
SELECT DISTINCT job, deptno FROM emp;-- 5
SELECT sal + 100, sal * 1.2 FROM emp;-- 6
SELECT (sal + 100) bb, sal as salary FROM emp;-- 7
SELECT 'a'||ename, ('xxx'||'ooo') xo  FROM emp;-- 8
SELECT COUNT(*) FROM emp;-- 9
SELECT ename FROM emp ORDER BY ename ASC;-- 10

※1：搜尋欄位
※2：搜尋全部欄位時，可以用「*」代替，可以少打點字
※3：表可以取別名，「as」可省略
※4：DISTINCT可以去掉重覆的欄位
※5：DISTINCT可以用多個欄位，如job,deptno兩個加起來如果重覆才會去掉
※6：還可以加減乘除
※7：欄位一樣可取別名，「as」一樣可省略
※8：可以用「||」將欄位或文字、數字…等加起來
※9：COUNT(*)表示整個資料有幾筆；
COUNT('a')、COUNT(5454)，裡面是數字、文字執行的結果都和COUNT(*)一樣

COUNT(欄位)不會加NULL的筆數
假設只查詢一個欄位，有查到9筆。
如果有2筆不是null，那結果就是7；再如果全部是null，那結果就是0

※10：排序，預設是小到大，所以「ASC」可省略；倒序用「DESC」
「ORDER BY sal, deptno DESC」這句是以sal正序，如果值一樣，就以deptno倒序

不一定要使用欄位名稱，也可以用數字，例如 ORDER BY 1 ASC, 3 DESC
ORACLE第一個不管在哪裡都是1(不是0)，所以表示以第一個欄位正序，如果一樣再以第3個欄位倒序

※自定排序

SELECT * FROM table

order by decode(column, 'column value2', 1, 'column value1', 2, 'column value3', 3)

※使用 decode 或 case when 方法，可以做到自定排序

※此例的結果為column value2-->column value1-->column value3-->其他欄位

※條件

-- 11
SELECT empno, e.sal FROM emp e
WHERE sal > 500;
    
-- 12
SELECT empno, e.sal FROM emp e
WHERE sal >= 500
AND sal <= 1000;
    
-- 13
SELECT empno, e.sal FROM emp e
WHERE sal BETWEEN 500 AND 1000;

※11：可以用「>」「<」等符號判斷，可看官方文件，注意不等於有三種寫法，11g我下載的文件還有第四種，但那個字根本打不出來，我用複製的也沒用

※12：有很多條件時，用「AND」隔開

※13：12的寫法也可以用這種

※AND／OR／NOT

-- 14
SELECT empno, e.sal FROM emp e
where sal >= 1300
OR sal <= 1000;
    
-- 15
SELECT empno, e.sal FROM emp e
WHERE (deptno=10 AND job='MANAGER')
OR (deptno=20 AND job='CLERK');
    
-- 16
SELECT empno, e.sal FROM emp e
WHERE NOT(deptno=10 OR job='MANAGER');

※14、15：OR的用法
※16：NOT就是相反囉！

※NULL

-- 17
SELECT empno, e.sal FROM emp e
WHERE comm = null;
    
-- 18
SELECT empno, e.sal FROM emp e
WHERE comm IS NULL;

※17：這是錯誤的判斷，但也不會錯，只是搜尋不到而已(包括使用字串null的方式，如果出現錯誤，是因為型態問題)

※18：要判斷NULL，就只能用IS NULL或IS NOT NULL
假設查詢的結果有Y、N和NULL，用WHERE column != 'Y'，只能查到N而已，所以還要加上AND column IS NULL或IS NOT NULL

※IN

-- 19
SELECT empno, e.sal FROM emp e
WHERE sal = 1600
OR sal = 1250
OR sal = 800;
    
-- 20
SELECT empno, e.sal FROM emp e
WHERE e.sal IN(1600,1250,800);

※20：19的寫法也可以，但太長了，想少打點字，可用IN或NOT IN

※LIKE

-- 21
SELECT ename FROM emp
WHERE ename LIKE 'A%';

※要注意有分大小寫

※「%」表示0~多；「_」表示1，類似windows的「*」和「?」

※「%A%」表示有A就搜出來； 「_A_」表示什麼A什麼的，如bAq、1A)…等，反正前後都剛好有一個字就可以了
中文我試的結果也算一個，但不知編碼有沒有差

※如果想搜尋「%」和「_」字串要用跳脫字元的寫法，如下：

LIKE '%\%' ESCAPE '\';
LIKE '%&%' ESCAPE '&';
LIKE 'A\_' ESCAPE '\';

※ESCAPE後面接的就是跳脫字元，可以隨自己的想法用，只要和前面的一樣就可以了
第一條SQL表示要找「XXX%」，XXX是0~多個字元
第二條SQL和第一條一樣，只差在跳脫字元不同而已
第三條SQL表示要找「A_」，所以也等同= 'A_'
跳脫字元一般習慣用「\」

ANY／SOME／ALL

-- 22
SELECT ename,sal
FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 10);
    
-- 23
SELECT ename,sal
FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 20);

※22：ANY等同SOME(但SOME大部分喜歡用在「=」的時候)，上面的官網連結有寫，這語法如果只執行ANY後面的SQL，會發現有多筆，因為any(some)是指任何一筆，所以會以sal最小的為主，所以只要薪水比得出來的條件最小的還大，就搜尋出來

※23：這語法如果只執行ALL後面的SQL，會發現有多筆，因為all是全部，所以會以sal最大的為主

※注意事項(綠色的沒意義)：

> ANY：顯示最小的結果還大的(最小的結果要顯示，要寫 >=)
< ANY：顯示最大的結果還小的(最大的結果要顯示，要寫 <=)
= ANY：和IN一樣
^= ANY、<> ANY、!= ANY：全部顯示，並不是NOT IN

> ALL：顯示最大的結果還大的(最小的結果要顯示，要寫 >=)
< ALL：顯示最小的結果還小的(最大的結果要顯示，要寫 <=)
= ALL：全部不顯示
^= ALL、<> ALL、!= ALL：和NOT IN一樣