Oracle_SQL函數(shù)-單行函數(shù)

SQL函數(shù)

SQL函數(shù)分類

  • SQL函數(shù)主要有兩種，分為單行函數(shù)、多行函數(shù)
    • 單行函數(shù)：只對一行進行變換，每行返回一個結(jié)果?？梢赞D(zhuǎn)換數(shù)據(jù)類型，可以嵌套參數(shù)可以是一列或一個值
    • 多行函數(shù)：多行函數(shù)，每次對一組記錄進行處理。然后對于這一組記錄只返回一個結(jié)果。
兩種SQL函數(shù)

單行函數(shù)

  • 單行函數(shù)的分類：主要有字符、通用、轉(zhuǎn)換、日期、數(shù)值
單行函數(shù)

字符函數(shù)

  • 字符函數(shù)分為：大小寫控制函數(shù)、字符控制函數(shù)
字符函數(shù)
大小寫控制函數(shù) ：這類函數(shù)改變字符的大小寫
 SELECT 'helloworld Java',
        LOWER('helloworld Java') AS "Lower Last_Name",
        UPPER('helloWorld Java') AS "UPper Last_name",
        INITCAP('helloWorld Java') AS "InitCap Last_Name"
FROM dual
大小寫控制函數(shù)
  • 在全稱匹配中，可以通過大小寫的轉(zhuǎn)換函數(shù)來進行字符的大小寫轉(zhuǎn)換。
字符控制函數(shù)
字符控制函數(shù)的種類
  • CONCAT(字符連接函數(shù))：CONCAT('Hello', ' World!') 
SELECT CONCAT('Hello', ' World!') FROM  dual
  • SUBSTR(字符字串函數(shù)) ：SUBSTR('HelloWorld', 1, 5)
    • SUBSTR函數(shù)字符串是從 1開始，這點和 Java 有點不同。
    • 函數(shù)的最后一個參數(shù)表示，從開始往后截取多少個字符串。
SELECT SUBSTR('abcdefgh',2,4) FROM dual;
結(jié)果
  • LENGTH：取得字符串的長度LENGTH('HelloWorld!')
  • INSTR：獲取某個字符在字符串中的位置INSTR('HelloWorld!','W') 
SELECT LENGTH('HelloWorld!'),INSTR('HelloWorld!','W') FROM dual;
結(jié)果
  • LPAD：左補齊函數(shù)LPAD(salary,10,'*')
  • RPAD：右補齊函數(shù)RPAD(salary,10,'*') 
SELECT salary,LPAD(salary,10,'*'),RPAD(salary,10,'*') FROM employees;
LPAD,RPAD
  • TRIM去除前后字符函數(shù)，函數(shù)能去掉字符的前后空格，或者去掉字符串中首尾特定字符（使用該方式需要使用到FROM關(guān)鍵字）
SELECT TRIM('H' FROM 'HelloHWorldHH') FROM dual;
SELECT TRIM('  HelloHWorldHH   ') FROM dual;
去除特定字符
去除空格
  • REPLACE字符串替換函數(shù)。能夠替換字符串中所有的特定函數(shù)
SELECT REPLACE('abcdabcdabcd','a','m') FROM dual
字符串替換函數(shù)

數(shù)值函數(shù)

  • 數(shù)值函數(shù)主要有以下：ROUND: 四舍五入 、TRUNC: 截斷、MOD: 求余
    數(shù)值函數(shù)
  • ROUND 函數(shù)：通過第二個參數(shù)，表示在第幾位進行四舍五入，并且舍棄后面的數(shù)值
SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM dual
四舍五入
  • TRUNC函數(shù)，能夠直接對于數(shù)值進行截斷！不做任何進位操作
SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM dual
截斷函數(shù)
  • MOD 函數(shù)，對數(shù)值進行求余運算。
SELECT salary, MOD(salary,1000) AS Mod_Salary FROM employees;
Mod求余函數(shù)

日期函數(shù)

  • Oracle 中的日期型數(shù)據(jù)實際含有兩個值: 日期和時間。

  • 函數(shù)SYSDATE既包含日期也包含時間SELECT sysdate FROM dual

  • 日期的數(shù)學(xué)運算

    • 在日期上加上或減去一個數(shù)字結(jié)果仍為日期。
    • 兩個日期相減返回日期之間相差的天數(shù)。
      • 日期不允許做加法運算，無意義
    • 可以用數(shù)字除24來向日期中加上或減去天數(shù)。

  • 可以對兩個日期之間的天數(shù)進行操作

SELECT last_name,(SYSDATE-hire_date)/7 AS Weeks
FROM employees
WHERE department_id = 90
  • 日期函數(shù)主要有以下幾種：
函數(shù) 描述
MONTHS_BETWEEN 兩個日期相差的月數(shù)
ADD_MONTHS 向指定日期中加上若干個月數(shù)
NEXT_DAY 指定日期的下一個星期所對應(yīng)的日期
LAST_DAY 本月的最后一天
ROUND 日期四舍五入
TRUNC 日期截斷
  • 時間和日期的表示方法：
yyyy  年、 mm   月、dd   日
hh   小時、 mi  分鐘、ss 秒
day  星期      

SELECT MONTHS_BETWEEN(SYSDATE+30,SYSDATE),
       ADD_MONTHS(SYSDATE,2),
       NEXT_DAY(SYSDATE,'星期四'),
       LAST_DAY(SYSDATE),
       ROUND(SYSDATE,'mm'),ROUND(SYSDATE,'yyyy'), 
       TRUNC(SYSDATE,'mm'),TRUNC(SYSDATE,'yyyy')
FROM dual;

轉(zhuǎn)換函數(shù)

隱式數(shù)據(jù)類型轉(zhuǎn)換
  • Oracle 自動完成下列轉(zhuǎn)換：
源數(shù)據(jù)類型 目標數(shù)據(jù)類型
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2 
DATE <---> VARCHAR2 <---> NUMBER
顯式的數(shù)據(jù)類型轉(zhuǎn)換
  • 顯式的數(shù)據(jù)類型轉(zhuǎn)換主要使用到幾個轉(zhuǎn)換函數(shù)：
    • TO_DATE
    • TO_CAHR
    • TO_NUMBER
  • TO_CAHR函數(shù)對日期的轉(zhuǎn)換的轉(zhuǎn)換
    • TO_CHAR(date,'fomat_model')
  • 格式:
    • 必須包含在單引號中而且大小寫敏感。
    • 可以包含任意的有效的日期格式。
    • 日期之間用逗號隔開。
select TO_CAHR(SYSDATE,'yyyy-MM-dd hh:mm:dd day') from dual
  • 日期格式的元素
Model View
YYYY 2016
YEAR twenty sixteen
MM 06
MONTH 6月(根據(jù)本地顯示) 或者June
MON 6月(根據(jù)本地顯示) 或者Jun
DY 星期六 或者 SATURDAY
DAY 星期六 或者 SAT
DD 18
  • 時間格式
HH:MI:SS AM         02:06:18 下午
HH24:MI:SS          14:06:18
  • 使用雙引號向日期中添加字符
-- DD "of" MONTH    18 of 6月 
select to_char(sysdate,'DD "of" MONTH') from dual
  • TO_DATE 函數(shù)對字符的轉(zhuǎn)換
    • TO_DATE(CHAR,'fomat_model')
    • 使用TO_DATE函數(shù)將字符轉(zhuǎn)換為數(shù)字
  • 注意：文字與格式的字符串要相匹配！
SELECT TO_DATE('2016年06月18日 14:31:35','yyyy"年"mm"月"dd"日"hh24:mi:ss')
From dual

  • TO_DATE 函數(shù)對數(shù)字的轉(zhuǎn)換

    • 下面是在TO_CHAR 函數(shù)中經(jīng)常使用的幾種格式：
      格式

  • 金額格式，如果是用9，不足的位數(shù)不會填充；但是使用0，就會在不足的位上補零

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

SELECT TO_CHAR(salary, '$000,000.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';
  • TO_NUMBER 函數(shù)對字符的轉(zhuǎn)換
  • 使用 TO_NUMBER 函數(shù)將字符轉(zhuǎn)換成數(shù)值:
    • TO_DATE(CHAR,'fomat_model')
SELECT TO_NUMBER('￥1,234,567,890.00','L999,999,999,999.99')
FROM dual

通用函數(shù)

  • 這些函數(shù)適用于任何數(shù)據(jù)類型，同時也適用于空值：

    • NVL (expr1, expr2)
    • NVL2 (expr1, expr2, expr3)
    • NULLIF (expr1, expr2)
    • COALESCE (expr1, expr2, ..., exprn)

  • NVL 函數(shù)：將空值轉(zhuǎn)換成一個已知的值

    • 可以使用的數(shù)據(jù)類型有日期、字符、數(shù)字。
    • 函數(shù)的一般形式:
    • NVL(commission_pct,0)
    • NVL(hire_date,'01-JAN-97')
    • NVL(job_id,'No Job Yet')

  • NVL2 函數(shù)：NVL2 (expr1, expr2, expr3) : expr1不為NULL，返回expr2；為NULL，返回expr3。

SELECT last_name,  salary, commission_pct,
       NVL2(commission_pct, 
            'SAL+COMM', 'SAL') income
FROM  employees 
WHERE department_id IN (50, 80);
  • NULLIF 函數(shù)：NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1 
SELECT first_name, LENGTH(first_name) "expr1", 
       last_name,  LENGTH(last_name)  "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;
  • COALESCE 函數(shù)
    • COALESCE 與 NVL 相比的優(yōu)點在于 COALESCE 可以同時處理交替的多個值。
    • 如果第一個表達式為空,則返回下一個表達式，對其他的參數(shù)進行COALESCE 。
-- 如果commision_pct 為空，返回salary，如果salary還為空就返回10.  

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

  • 條件表達式

    • 在 SQL 語句中使用IF-THEN-ELSE 邏輯
    • 使用兩種方法:
      • CASE 表達式
      • DECODE 函數(shù)

  • CASE 表達式示例

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

-- 練習：查詢部門號為 10, 20, 30 的員工信息, 若部門號為 10, 則打印其工資的 1.1 倍, 20 號部門, 則打印其工資的 1.2 倍, 30 號部門打印其工資的 1.3 倍數(shù)
SELECT employee_id,last_name,department_id,salary,
       CASE department_id WHEN 10 THEN salary*1.1
                          WHEN 20 THEN salary*1.2
                          WHEN 30 THEN salary*1.3
                          ELSE salary
       END AS "new_salary"
FROM employees
WHERE department_id in (10,20,30)
  • DECODE 函數(shù)示例
DECODE(col|expression, search1, result1 ,
               [, search2, result2,...,]
               [, default])

-- DECODE版本
SELECT employee_id,last_name,department_id,salary,
       DECODE (department_id,10, salary*1.1,
                             20, salary*1.2,
                             30, salary*1.3,
                             salary) AS "new_salary"
FROM employees
WHERE department_id in (10,20,30)

嵌套函數(shù)

  • 單行函數(shù)可以嵌套。

  • 嵌套函數(shù)的執(zhí)行順序是由內(nèi)到外。


    嵌套函數(shù)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • SQL SELECT 語句 一、查詢SQL SELECT 語法 （1）SELECT 列名稱 FROM 表名稱 （2...
    有錢且幸福閱讀 5,999評論 0贊 33
  • 1.簡介 數(shù)據(jù)存儲有哪些方式？電子表格，紙質(zhì)文件，數(shù)據(jù)庫。 那么究竟什么是關(guān)系型數(shù)據(jù)庫？ 目前對數(shù)據(jù)庫的分類主要是...
    喬震閱讀 2,013評論 0贊 2
  • Oracle-SQL 這是對Oracle-SQL知識點詳細介紹的文章系列，其他文章如下： Oracle-SQL系列...
    GuaKin_Huang閱讀 2,731評論 0贊 14
  • 作者/蓋是那么美 那一夜，無雪的寒冬，開心的冰激凌； 那一夜，孤單的街燈，漫長的身影。 那一夜，看著你如嫣的笑臉，...
    蓋是那么美閱讀 638評論 23贊 17
  • 2017年4月3日 星期一 晴 【早起】 5：10起床。（在老家，這次因為要跑三處，沒提電腦回...
    滋心潤霖閱讀 75評論 0贊 0

友情鏈接更多精彩內(nèi)容