DQL---條件查詢、單行函數、多行函數、分組函數、數據類型

一、DQL 1、基本規則：（1）對於日期型數據，做 *，/ 運算不合法，可以進行 +， - 運算。比如給日期加一天或減一個月，結果仍為一個日期。兩個日期間只能為減法，返回兩個日期相差的天數，兩個日期間做加法沒任何意義。（2）包含空值（null）的數學表達式計算結果均為空值。（3）給欄位取別名時 ...

一、DQL

1、基本規則：

　　（1）對於日期型數據，做 *，/ 運算不合法，可以進行 +， - 運算。比如給日期加一天或減一個月，結果仍為一個日期。兩個日期間只能為減法，返回兩個日期相差的天數，兩個日期間做加法沒任何意義。

　　（2）包含空值（null）的數學表達式計算結果均為空值。

　　（3）給欄位取別名時，別名使用雙引號括起來（根據雙引號里的內容顯示），不加雙引號時會轉為大寫。欄位與別名間可以使用AS關鍵字，也可使用空格。

　　（4）oracle中連接字元是 || （也可以使用單行函數concat()），不是java中的 + 。

　　（5）字元串使用單引號括起來，字元串區分大小寫。

　　（6）distinct用於過濾重覆的數據，但不能亂用。

　　（7）dual為虛表，是一個不存在的表，只為滿足SELECT語法，通常用來測試表達式的結果。

2、DQL基本關鍵字

　　（1）SELECT後跟想查詢的列，即最後顯示的列。

　　（2）FROM後跟的是想查詢的表。

　　（3）WHERE後跟的是查詢限制條件。 WHERE後跟的是數字時，可以不用單引號引起，但若為字元串或者日期格式時，需要用單引號引起。

--舉例：
--列印系統時間
SELECT SYSDATE FROM dual;   --只會顯示一行’helloworld‘。

--輸出整張表的信息
SELECT * FROM lf_employee;  

--使用字元串，別名，連接符輸出表的信息
SELECT employee_name ||' status is ' || employee_status as "name" FROM lf_employee;

--正確使用distinct關鍵字
SELECT distinct employee_status status, department_id as "Id" FROM lf_employee;

二、條件查詢語句、以及排序

1、Between ... And ...

SELECT employee_name, employee_salary salary
FROM lf_employee
WHERE employee_salary BETWEEN 4000 AND 7000;
--等價於employee_salary >= 4000 AND employee_salary <= 7000

2、操作符（AND, OR, IN）

比較操作符：>, < , =, >=, <= , <>(此為不等於，不建議寫!=)。
關鍵字：AND（需同時滿足條件），OR（滿足一個條件即可 ），且AND優先順序大於OR。

比較操作符：IN(list)， NOT IN(list)。
list指集合，表示多個元素，IN(list)表示在list取出滿足（一個）條件的數據，NOT IN(list)表示取出不符合條件的list數據。
IN等價於OR的用法。

ANY與ALL不能單獨使用，其需要與>,<，=,<=,>=等連用。
 >ANY 大於最小值。
>ALL 大於最大值。
<ANY 小於最大值。
<ALL  小於最小值。
ALL與IN的區別在於IN是進行值比較，ALL進行的是範圍比較。

SELECT employee_name, employee_salary
FROM lf_employee
WHERE employee_salary in(4000, 5000, 6000);
--等價於 employee_salary = 4000 OR employee_salary = 5000 OR employee_salary = 6000

SELECT employee_id, employee_salary
FROM lf_employee
WHERE employee_salary > ANY(1000, 2000)
--等價於 employee_salary > 1000

3、like，模糊查詢

-- % 表示匹配多個字元（0~n個字元）
-- _ 表示匹配單個字元（1個字元）
SELECT employee_name, employee_salary
FROM lf_employee t
WHERE t.employee_name like '_';
--表示查詢名字為單個字元的人

SELECT employee_name, employee_salary
FROM lf_employee t
WHERE t.employee_name like '1%';
--表示查詢以1開頭的所有人名。

4、escape，轉義字元

SELECT employee_name, employee_salary
FROM lf_employee t
WHERE t.employee_name like '%\_%' escape '\';
--表示查詢含有_的名字。將通配符_轉義為下劃線_。

SELECT employee_name, employee_salary
FROM lf_employee t
WHERE t.employee_name like '%_%';
--查詢所有名字

5、order by ... desc/asc，排序

SELECT employee_id, employee_salary
FROM lf_employee t
ORDER BY t.employee_salary asc, t.employee_id desc 
--先按照薪資升序排序，當薪資相同時，按照id降序排序。預設按照升序排序，即asc可不寫。

6、DISTINCT

--去除列的重覆行。
--對單列去重，則無重覆行。
--對多列去重，則多列的組合不重覆行。

SELECT DISTINCT employee_salary, employee_id
FROM lf_employee --正確輸出結果

SELECT employee_id,DISTINCT employee_salary
FROM lf_employee --報錯

三、單行函數

單行函數可以嵌套，執行循序為從內到外。

1、字元函數：

lower(char)  --將字元串char轉為全大寫。
upper(char)  --將字元串char轉為全小寫。
initcap(char)  --將字元串char中每個單詞的首字母轉為大寫，其餘字母不變。
concat(char1, char2)  --將兩個字元串拼接。等價於 char1 || char2 。
substr(char, a, b)  --截取字元串char，從第a個位置開始，輸出b個字元。
length(char)  --輸出字元串char的長度。
instr(char1, char2)  --返回字元串char2首次出現在字元串char1中的位置，不存在則返回0。
lpad(char1, n, char2)  --左對齊，顯示n位char1,不足的用char2補左邊缺失的位。
rpad(char1, n, char2)  --右對齊，顯示n位char1,不足的用char2補右邊缺失的位。
trim('h' from 'hhellohhworldhh')  --去除首尾相同的字元
replace('abbbcd', 'bb', 'm') --替換匹配的字元

SELECT upper('kiNG'), lower('King'), initcap('kiNG'), concat('hello', ' world'), substr('hello', 1, 2), length('hello'),instr('hello', 'lo')
FROM dual 

 SELECT lpad(employee_salary, 10, '*'), rpad(employee_salary, 10, '*'), trim('h' from 'hhellohhworldhh'), replace('abbbcd', 'bb', 'm')
  FROM lf_employee

2、數字函數

ROUND(m[,n])  --四捨五入，round(45.926, 2) = 45.93
TRUNC()  --截斷,trunc(45.926, 2) = 45.92
--註意：若存在第二個參數，第二個參數為正數時，對小數點右邊進行操作，為負數時，對小數點左邊進行操作。

CEIL(n)  --取大於或等於n的最小整數
FLOOR(n)  --取小於或等於n的最大整數。
MOD(m, n)  --求餘,mod(1600, 300) = 1600%300 = 100

SELECT round(45.926, 2), round(45.926, -1), trunc(45.926, 2), trunc(45.926, -1), mod(1600, 300), mod(300, 1600)
FROM dual

3、日期函數

SYSDATE  --返回當前系統時間，精確到秒。
SYSTIMESTAMP  --返回當前系統時間，精確到納秒。

MONTHS_BETWEEN (date1, date2) --用於計算date1和date2之間有幾個月。若date1比date2早，則返回負數，若date1比date2晚，則返回正數，相同則返回0.
ADD_MONTHS(date, month)  --用於給date加上month個月，當month為小數時，會先被截取（trunc）成整數再參與運算。
NEXT_DAY(data, char)  --表示data之後下一個星期幾是哪天。若char表示為數字，即char為1~7時，表示星期日~星期六。
LAST_DAY(date)  --表示date所在月的最後一天
ROUND(date)  --將日期四捨五入
TRUNC(date)  --將日期截斷

EXTRACT(date from datetime)  --從datetime中返回date指定的數據。

GREATEST(expr1[,expr2[,expr3]])  --為比較函數，返回參數中最大的值。
LEAST(expr1[,expr2[,expr3]])  --為比較函數，返回參數中最小的數。
-- 註意： 比較時，需要參數類型一致。比較時第二個參數會隱式轉為第一個參數，若能夠轉換，則比較，否則會報錯。

SELECT sysdate, months_between(sysdate, sysdate + 61) month, add_months(sysdate, 2), next_day(sysdate, 7), last_day(sysdate), trunc(sysdate), round(sysdate)
FROM dual

SELECT EXTRACT(year FROM sysdate), ADD_MONTHS(sysdate, 2.8), GREATEST(SYSDATE + 1, SYSDATE -1, SYSDATE), LEAST(22, 11, 33)
FROM DUAL

4、轉換函數

--隱式轉換： 
    date <==> varchar2 <==> number
--顯示轉換：
     date 通過 to_char() 函數轉為 char。 
     char 通過 to_date() 函數轉為 date。
     char 通過 to_number() 函數轉為 number。
     number 通過 to_char() 函數轉為 char。

其中：
    to_char(數字, '格式')， $表示在數字前加$符號，L表示本地貨幣，.表示小數點， ,表示分隔符。
    
SELECT to_char(sysdate, 'yyyy"年"mm"月"dd"日"'), to_char(201906.13, 'L999,999.99'), to_char(201906.13, '$999,999.99'),
         to_char(201906.13, '999,999.99'), to_date('2019.06,13', 'yyyy-mm-dd')
FROM dual

5、通用函數

-- NULL與任何數字計算結果仍為NULL。
-- NULL與字元串連接，NULL會被當成空格。
-- 在WHERE條件中判斷不為NULL時，需寫成IS NOT NULL，為空寫成IS NULL。

NVL(e1, e2)  --當e1為null時，使用e2去替換它。
NVL2(e1, e2, e3)  --當e1不為null時，返回e2。e1為null時，返回e3。
NULLIF(e1, e2)  --當e1不等於e2時，返回e1。相等時返回null。
coalesce(e1, e2, e3, ..., en)  --當e1為null時，執行e2，e2若為null，執行e3，迴圈執行，直至不為null或執行完。

SELECT nvl(null, 1), nvl(2, 1), nvl2(null, 2, 3), nvl2(1, 2, 3), nullif(1, 1), nullif(1, 2),
         coalesce(null, '1', '2'), coalesce(null, null, '2')
FROM dual

6、條件表達式

【簡單Case函數：】
    CASE 參數 WHEN 值1 THEN 結果1
              WHEN 值2 THEN 結果2
              ELSE 結果3
    END;
    
【Case搜索函數：（可以進行更多的判斷）】
    CASE WHEN 表達式1 THEN 結果1
         WHEN 表達式2 THEN 結果2
        ELSE 結果3 
   END;
 註：Case函數只返回第一個符合條件的值。
    
【Decode表達式：】
    Decode(參數, 值1， 結果1，
                 值2， 結果2，
                      結果3)
                      
  SELECT employee_salary salary, 
         CASE employee_salary 
              WHEN 5000 THEN 3000
              WHEN 7000 THEN 4000
              ELSE 5000
         END AS "Case Salary",
         DECODE(employee_salary, 5000, 3000,
                                 7000, 4000,
                                 5000) "Decode Salary"
  FROM lf_employee

四、多行函數（聚合函數、組函數、集合函數）

1、聚合函數用於統計數據，聚合函數對一組值進行計算並返回單一的值。
2、聚合函數不能單獨寫在WHERE中，因為WHERE是對每行數據進行判斷，而聚合函數是對所有數據進行操作。聚合函數一般與GROUP BY連用。
3、聚合函數忽略空值（NULL）。

假如：現有10個人，但只有4人有獎金，如果採用聚合函數直接進行計算的話，得到的結果是
(4人獎金和/4人)，因為會忽略空值（即空值不參與聚合函數運算）。
若想實現（4人獎金和/10人），可按照如下寫法：
SELECT AVG(NVL(salary, 0))
FROM emp;
這個查詢語句表示，當salary為NULL時，將其賦值為0，這樣就可以讓其參與聚合函數的運算。

 --MAX(列名) 求某列的最大值 ,  MIN(列名) 求某列的最小值。
 --AVG(列名) 求某列的平均值， SUM(列名) 求某列的總和。
--COUNT(列名) 求某列非空的記錄數。
--COUNT(*) 統計表中的（非空）記錄數。

SELECT MAX(employee_salary), MIN(employee_salary), AVG(employee_salary), SUM(employee_salary),COUNT(employee_salary), COUNT(*)
FROM lf_employee

五、分組函數（GROUP BY , HAVING）

1、GROUP BY一般寫在FROM之後，用於分組。
2、若GROUP BY中出現多列，那麼將列組合看成分組的依據。
3、若SELECT中出現了非組函數列（非MAX,MIN等），那麼這些列必須存在GROUP BY中，否則或報錯，但GROUP BY中出現了非組函數列，SELECT中可以不存在。

比如：employee_status（非組函數列）存在SELECT中，而不在GROUP BY中，則會報錯。
SELECT employee_id,employee_status,MAX(employee_salary), MIN(employee_salary)
FROM lf_employee
GROUP BY employee_id  --錯誤寫法，會報錯

SELECT employee_id,employee_status,MAX(employee_salary), MIN(employee_salary)
FROM lf_employee
GROUP BY employee_id, employee_status   --正確寫法

4、HAVING子句不能單獨存在，必須跟在GROUP BY後面，其是對分組結果的進一步限制。HAVING是在第一次檢索完成後，進行第二次的檢索。

SELECT MAX(employee_salary), COUNT(employee_salary)
FROM lf_employee
GROUP BY employee_salary

SELECT MAX(employee_salary), COUNT(employee_salary)
FROM lf_employee
GROUP BY employee_salary HAVING(employee_salary) > 2000

六、數據類型

1、常用數據類型：

　　NUMBER、 CHAR、VARCHAR2、DATE、TIMESTAMP、 LONG、CLOB。

--舉例：
CREATE TABLE emp{  --創建一個表
    id NUMBER(4);   --表示四位整數
    name VARCHAR2(20);  --最長20個位元組的變長字元串
    gender CHAR(1); --定長1個位元組的字元串
    sal NUMBER(6, 2); --表示四位整數，兩位小數
    hiredate DATE;  --定義日期類型
}

2、NUMBER（oracle獨有類型）

NUMBER指數字類型，
格式：
    NUMBER(P, S)， P指數字的總位數（P取值為1~38），S指小數點後面的位數。

number類型和java數據類型對應關係： 
number類型長度             java數據類型 
      n>18                java.math.BigDecimal 
 10<=n<=18                java.lang.Long 
  1 <=n<=9                java.lang.Integer
    
舉例：
emp表中的sal列定義為： sal NUMBER(6, 2)，則表示為整數部分最大為4位，小數部分最大為2位，即表示的最大值為9999.99 。

3、CHAR、VARCHAR2

CHAR表示固定長度的字元串。易造成空間的浪費，但索引效率高。
格式：
    CHAR(N)，固定占用N個位元組（不是字元），最大為2000位元組。

VARCHAR2 相當於 其他資料庫的 VARCHAR，表示可變長的字元串。VARCHAR2比CHAR空間利用率高，但性能差。
格式：
    VARCHAR2(N)，最多占用N個位元組，最大值為4000位元組。

4、DATE、TIMESTAMP

DATE用於定義日期的數據。長度為7個位元組。
 對於日期數據，可以定義為Date類型，也可以定義為Varchar2(30)。
 sysdate,本質為Oracle的內部函數，用於返回當前的系統時間，精確到秒。
 
 TIMESTAMP用於保存日期時間，相比於DATE，TIMESTAMP可以保存更精確的值，精確到納秒。
 其長度為7位元組或者11位元組。長度為7位元組時，與DATE相同。長度為11位元組時，第8位元組至第11位元組內部採用整型運算，用於保存納秒值。

5、LONG與CLUB類型

LONG是VARCHAR2的加長版，也是變長字元串，最多能存2GB的字元串數據。每個表最多只允許存在一個LONG列，且不能為主鍵、不能建立索引、不能出現在查詢語句中。

CLUB：是Oracle推薦的， 建議用CLUB代替LONG，存儲定長或變長字元串，最多4GB。