SQL語言基礎（函式）

函式在SQL 中的作用：

Ø   執行資料計算

Ø   修改單獨資料項

Ø   操縱一組行的輸出

Ø   替換日期的顯示格式

Ø   轉換列的資料型別

SQL 函式的兩種型別：

Ø   單行函式

Ø   多行函式

單行函式：

       Ø   運算元句物件

Ø   接受函式返回一個結果

Ø   只對一行進行變換

Ø   每行返回一個結果

Ø   可以轉換資料型別

Ø   可以巢狀

Ø   引數可以是一列或一個值

function_name [(arg1, arg2,...)]

單行函式分類：

1-1 字元函式

1-1-1 大小寫控制函式

LOWER('SQL Course')
UPPER('SQL Course')
INITCAP('SQL Course')

例：

SELECT employee_id, last_name, department_id
FROM   employees
WHERE  LOWER(last_name) = 'higgins';

1-1-2 字元控制函式

CONCAT 與’||’ 的區別：一個CONCAT 函式只能連線兩個字串 ，|| 可以一次連線多個。

1-2 數字函式

1-2-1 ROUND函式

SELECT ROUND(45.923,2), ROUND(45.923,0),
       ROUND(45.923,-1)
FROM   DUAL;

DUAL 是一個‘偽表’，可以用來測試函式和表示式

1-2-2 TRUNC函式

SELECT  TRUNC(45.923,2), TRUNC(45.923),
        TRUNC(45.923,-2)
FROM   DUAL;

1-2-3 MOD函式

SELECT last_name, salary, MOD(salary, 5000)
FROM   employees
WHERE  job_id = 'SA_REP';

1-3 日期函式

1-3-1 日期的數學計算

Ø   在日期上加上或減去一個數字結果仍為日期。

Ø   兩個日期相減返回日期之間相差的天數。

Ø   可以用數字除24 來向日期中加上或減去小時。

1-4 轉換函式

隱式

顯式

1-4-1 隱式資料型別轉換

Oracle 自動完成下列轉換：

1-4-2 顯式資料型別轉換

TO_CHAR 函式對日期的轉換

TO_CHAR(date, 'format_model')

TO_CHAR 函式對日期的轉換

SELECT last_name,
       TO_CHAR(hire_date, 'fmDD Month YYYY')
       AS HIREDATE
FROM   employees;

TO_CHAR 函式對數字的轉換

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';

TO_NUMBER 和 TO_DATE 函式

使用 TO_NUMBER 函式將字元轉換成數字:
TO_NUMBER(char[, 'format_model'])
使用 TO_DATE 函式將字元轉換成日期:
TO_DATE(char[, 'format_model'])

1-4-3  通用函式

NVL
          假如oracle第一個引數為空那麼顯示第二個引數的值，假如第一個引數的值不為空，則顯示第一個引數本來的值。   
NVL2
       假如該函式的第一個引數為空那麼顯示expr3引數的值，假如第一個引數的值不為空，則顯示expr2引數的值。    
NULLIF
       假如exp1和exp2相等則返回空(NULL)，否則返回第一個值。
COALESCE
             假如第一個引數為空，則看第二個引數是否是空，否則則顯示第一個引數，假如第二個引數是空再看第三個引數是否為空，否則顯示第二個引數，依次類推。 相當於NVL的迴圈使用。

NVL 函式

SELECT last_name, salary, NVL(commission_pct, 0),
   (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

NVL2 函式

SELECT last_name,  salary, commission_pct,
       NVL2(commission_pct,
            'SAL+COMM', 'SAL') income
FROM   employees WHERE department_id IN (50, 80);

NULLIF 函式

SELECT first_name, LENGTH(first_name) "expr1",
       last_name,  LENGTH(last_name)  "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;

COALESCE 函式

SELECT   last_name,
         COALESCE(commission_pct, salary, 10) comm
FROM     employees
ORDER BY commission_pct;

1-4-4 巢狀函式

Ø   單行函式可以巢狀。

Ø   巢狀函式的執行順序是由內到外。

SELECT last_name,
       NVL(TO_CHAR(manager_id), 'No Manager')
FROM   employees
WHERE  manager_id IS NULL;

1-5  條件表示式

Ø   CASE 表示式

Ø   DECODE 函式

1-5-1 case表示式

CASE 
WHEN condition1 THEN return_result1
WHEN condition2 THEN return_result2
……
WHEN conditionn THEN return_resultn
          ELSE default_result
END

1-5-2 DECODE表示式

DECODE(col|expression, search1, result1
           [, search2, result2,...,]
           [, default])

SELECT last_name, job_id, salary,
       DECODE(job_id, 'IT_PROG',  1.10*salary,
                      'ST_CLERK', 1.15*salary,
                      'SA_REP',   1.20*salary,
              salary)
       REVISED_SALARY
FROM   employees;

1-5-3 行列轉換

從格式一到格式二
SELECT a.card_code,
       SUM(decode(a.q, 1, a.val, 0)) q1,
       SUM(decode(a.q, 2, a.val, 0)) q2,
       SUM(decode(a.q, 3, a.val, 0)) q3,
       SUM(decode(a.q, 4, a.val, 0)) q4
  FROM t_change_lc a
 GROUP BY a.card_code
 ORDER BY 1;

從格式二到格式一
SELECT t.card_code,
       t.rn q,
       decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) val
  FROM (SELECT a.*, b.rn
          FROM t_change_cl a,
       (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
 ORDER BY 1, 2;

分組函式：

  分組函式是對一批（一組）資料進行操作（綜合）之後返回一個值。這批資料可能是整個表，也可能是按某種條件把該表分成的組。對於管理者或決策者來說綜合資料才是最有價值的資訊。

SELECT  [column,] group_function(column), ...
FROM  table
[WHERE  condition]
[GROUP BY  column]
[ORDER BY  column];

1-6 組函式型別

Ø   AVG （平均值）

Ø   COUNT （計數）

Ø   MAX （最大值）

Ø   MIN （最小值）

Ø   SUM （合計）

1-6-1  AVG 和 SUM 函式

SELECT AVG(salary), MAX(salary),
       MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

1-6-2  MIN 和 MAX 函式

可以對任意資料型別的資料使用 MIN 和 MAX 函式

SELECT MIN(hire_date), MAX(hire_date)
FROM    employees;

1-6-3 count函式

COUNT(*) 返回表中記錄總數。

SELECT COUNT(*)
FROM    employees
WHERE  department_id = 50;

COUNT( expr) 返回 expr 不為空的記錄總數

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 80

COUNT(distinct  expr)  返回   expr非空切不重複 記錄總數

SELECT COUNT(DISTINCT department_id)
FROM   employees;

組函式忽略空值

SELECT AVG(commission_pct)
FROM   employees;

1-7 分組資料

使用group by子句分組

SELECT  column, group_function(column)
FROM  table
[WHERE  condition]
[GROUP BY  group_by_expression]
[ORDER BY  column];

•     將表中的行分成更小的組

•     使用ORDER BY 子句改變預設的排序方式

•     SELECT 列表中的所有非組函式中的列必須在GROUP BY 子句中

•     GROUP BY 的列不必出現在SELECT 子句中

•     使用HAVING 子句限制組

•     可使用多個GROUP BY 列

•     由GROUP BY 子句中的列順序，決定結果的預設排序順序

SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;

 

不能在 WHERE 子句中使用組函式。

可以在HAVING 子句中使用組函式。

SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000
GROUP BY department_id;
WHERE  AVG(salary) > 8000
       *
ERROR at line 3:
ORA-00934: group function is not allowed here

1-8 過濾分組

使用 HAVING 過濾分組:

1.      行已經被分組。

2.      使用了組函式。

3.      滿足HAVING 子句中條件的分組將被顯示。

SELECT  column, group_function
FROM  table
[WHERE  condition]
[GROUP BY  group_by_expression]
[HAVING  group_condition]
[ORDER BY  column];

SELECT   job_id, SUM(salary) PAYROLL
FROM     employees
WHERE    job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING   SUM(salary) > 13000
ORDER BY SUM(salary);