SQL 语法

Oracle

1. 檢查多條重複數據
2. 兩個日期間的天數
3. 求某天是星期幾
4. 時間為null的用法
5. 日期格式衝突問題
6. 查詢月份
7. 獲取小時數， EXTRACT()找出日期或間隔值的欄位值
8. 處理月份天數不定的辦法
9. 找出今年的天數
10. 閏年的處理方法
11. 不同時區的處理
12. 5秒鐘一個間隔
13. 一年的第幾天
14. 計算小時，分，秒，毫秒
15. 兩個日期間相差的秒數。日期返回的是天，然後轉換為秒
16. ROUND 舍入到最接近的日期 (day:舍入到最接近的星期日)
17. TRUNC[截斷到最接近的日期,單位為天] ,返回的是日期類型
18. 返回日期列表中最晚日期
19. 計算時間差。注:oracle時間差是以天數為單位,所以換算成年月,日
20. 更新時間。注:oracle時間加減是以天數為單位,設改變量為N,所以換算成年月,日
21. 查詢月的第一天，最後一天
22. 字串截取
23. 查找字符串位置
24. 字元串連接
25. 去掉字符串
26. 返回字串首字母的Ascii值
27. 返回Ascii值對應的字母
28. 計算字串長度
29. initcap 首字母變大寫 | lower 變小寫 | upper 變大寫
30. REPLACE替換
31. TRANSLATE
32. lpad(左添充)rpad(右填充) 用於控制輸出格式
33. DECODE(實現IF ..THEN 邏輯)
34. CASE(實現SWITCH ..CASE 邏輯)
35. 取整函數（ceil 向上取整,floor 向下取整）
36. 取冪(power) 和求平方根(sqrt)
37. 求餘
38. 返回固定小數位數(round:四捨五入，trunc:直接截斷)
39. 返回值的符號(正數返回為1,負數為-1)
40. TO_CHAR()[將日期和數位類型轉換成字元類型]
41. TO_DATE()[將字元類型轉換為日期類型]
42. TO_NUMBER() 轉換為數位類型
43. 返回登錄的用戶名稱
44. 返回運算式所需的位元組數
45. NVL(EX1,EX2): EX1值為空則返回EX2,否則返回該值本身EX1（常用）例：如果雇員沒有傭金，將顯示0，否則顯示傭金
46. NVL2(EX1,EX2,EX3) : 如果EX1不為空，顯示EX2,否則顯示EX3
47. NULLIF(EX1,EX2): 值相等返空，否則返回第一個值例：如果工資和傭金相等，則顯示空，否則顯示工資
48. COALESCE：返回列表中第一個非空運算式
49. 运算函数
50. 帶GROUP BY 和 HAVING 的分組
51. STDDEV 返回一組值的標準差
52. VARIANCE 返回一組值的方差差
53. 帶有ROLLUP和CUBE操作符的GROUP BY
54. 臨時表
55. LISTAGG和XMLAGG函数
56. 立刻执行sql语句，并赋值给某个变量
57. ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
58. count()over()分析函数的使用
59. REGEXP_REPLACE(1,2,3,4,5,6)
60. OVER(PARTITION BY …. ORDER BY …)
61. PIVOT(MAX(RATE/*行转列后列的值*/) FOR WEEK/*需要行转列的列*/ IN(’46’ “Week_46”,’47’ “Week_47″/*列的值*/))

1. 檢查多條重複數據

SELECT SN,COUNT(1) 重复次数 FROM Mytable GROUP BY SN HAVING COUNT(1)>1

2. 兩個日期間的天數

SELECT FLOOR(SYSDATE - TO_DATE('20191205','yyyymmdd')) FROM DUAL

3. 求某天是星期幾

SELECT TO_CHAR(TO_DATE('2019-12-05','yyyy-mm-dd'),'day') FROM DUAL

4. 時間為null的用法

SELECT 1, TO_DATE(NULL) FROM DUAL

5. 日期格式衝突問題

    輸入的格式要看你安裝的ORACLE字元集的類型, 比如: US7ASCII, DATE格式的類型就是: '01-Jan-01'      
    ALTER SYSTEM SET NLS_DATE_LANGUAGE = AMERICAN      
    ALTER SESSION SET NLS_DATE_LANGUAGE = AMERICAN      
    或者在TO_DATE中寫      
    SELECT TO_CHAR(TO_DATE('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') FROM DUAL;      
    注意我這只是舉了NLS_DATE_LANGUAGE，當然還有很多，      
    可查看      
    SELECT * FROM NLS_SESSION_PARAMETERS      
    SELECT * FROM V$NLS_PARAMETERS
-------------------------------------------------------------------------------------
	SELECT COUNT(*)  FROM ( 
       SELECT ROWNUM-1 RNUM  FROM ALL_OBJECTS WHERE ROWNUM <= TO_DATE('2002-02-28','yyyy-mm-dd') - TO_DATE('2002-02-01','yyyy-mm-dd')+1      
      )   
WHERE TO_CHAR( TO_DATE('2002-02-01','yyyy-mm-dd')+RNUM-1, 'D' ) NOT IN ( '1', '7' )      
   
   查找2002-02-28至2002-02-01間除星期一和七的天數      
   在前後分別調用DBMS_UTILITY.GET_TIME, 讓後將結果相減(得到的是1/100秒, 而不是毫秒).

6. 查詢月份

SELECT MONTHS_BETWEEN(TO_DATE('01-31-1999','MM-DD-YYYY'),TO_DATE('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;   
SELECT MONTHS_BETWEEN(TO_DATE('02-01-1999','MM-DD-YYYY'),TO_DATE('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;

7. 獲取小時數， EXTRACT()找出日期或間隔值的欄位值

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') FROM DUAL

8. 處理月份天數不定的辦法

SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) +1, -2), 'yyyymmdd'),LAST_DAY(SYSDATE) FROM DUAL

9. 找出今年的天數

SELECT ADD_MONTHS(TRUNC(SYSDATE,'year'), 12) - TRUNC(SYSDATE,'year') FROM DUAL

10. 閏年的處理方法

TO_CHAR(LAST_DAY( TO_DATE('02'| | :YEAR,'mmyyyy') ), 'dd' )如果是28就不是閏年

11. 不同時區的處理

SELECT TO_CHAR( NEW_TIME( SYSDATE, 'GMT','EST'), 'yyyy-mm-dd hh24:mi:ss') ,SYSDATE FROM DUAL

12. 5秒鐘一個間隔

SELECT TO_DATE (FLOOR (TO_CHAR (SYSDATE, 'SSSSS') / 300) * 300, 'SSSSS'),TO_CHAR (SYSDATE, 'SSSSS') FROM DUAL --SSSSS表示5位秒數

13. 一年的第幾天

SELECT TO_CHAR (SYSDATE, 'DDD') AS DAY, SYSDATE FROM DUAL

14. 計算小時，分，秒，毫秒

SELECT Days,
       A,
       TRUNC (A * 24) Hours,
       TRUNC (A * 24 * 60 - 60 * TRUNC (A * 24)) Minutes,
       TRUNC (A * 24 * 60 * 60 - 60 * TRUNC (A * 24 * 60)) Seconds,
       TRUNC (A * 24 * 60 * 60 * 100 - 100 * TRUNC (A * 24 * 60 * 60))
          mSeconds
  FROM (SELECT TRUNC (SYSDATE) Days, SYSDATE - TRUNC (SYSDATE) A FROM DUAL)

15. 兩個日期間相差的秒數。日期返回的是天，然後轉換為秒

SELECT (SYSDATE - TO_DATE ('2019-12-16 12:55:45', 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 FROM DUAL

16. ROUND 舍入到最接近的日期 (day:舍入到最接近的星期日)

SELECT SYSDATE S1, ROUND (SYSDATE) S2, ROUND (SYSDATE, 'year') YEAR,ROUND (SYSDATE, 'month') MONTH, ROUND (SYSDATE, 'day') DAY  FROM DUAL

17. TRUNC[截斷到最接近的日期,單位為天] ,返回的是日期類型

  SELECT SYSDATE S1,
       TRUNC (SYSDATE) S2,
       TRUNC (SYSDATE, 'year') YEAR,    --返回當前年的1月1日,無時分秒
       TRUNC (SYSDATE, 'month') MONTH,  --返回當前月的1日,無時分秒
       TRUNC (SYSDATE, 'day') DAY       --返回當前星期的星期天,無時分秒
  FROM DUAL

18. 返回日期列表中最晚日期

SELECT GREATEST('01-1月-04','04-1月-04','10-2月-04') FROM DUAL

19. 計算時間差。注:oracle時間差是以天數為單位,所以換算成年月,日

 SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2019-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) AS SPANYEARS FROM DUAL        --時間差-年   
 SELECT CEIL(MONTHS_BETWEEN(SYSDATE,TO_DATE('2019-11-02','yyyy-mm-dd'))) AS SPANMONTHS FROM DUAL       --時間差-月      
 SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2019-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) AS SPANDAYS FROM DUAL             --時間差-天      
 SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2019-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) AS SPANHOURS FROM DUAL         --時間差-時      
 SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2019-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) AS SPANMINUTES FROM DUAL      --時間差-分      
 SELECT FLOOR(TO_NUMBER(SYSDATE-TO_DATE('2019-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) AS SPANSECONDS FROM DUAL --時間差-秒

20. 更新時間。注:oracle時間加減是以天數為單位,設改變量為N,所以換算成年月,日

     SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(SYSDATE+N*365,'yyyy-mm-dd hh24:mi:ss') AS NEWTIME FROM DUAL        --改變時間-年
     SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'),ADD_MONTHS(SYSDATE,N) AS NEWTIME FROM DUAL                                 --改變時間-月
     SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(SYSDATE+N,'yyyy-mm-dd hh24:mi:ss') AS NEWTIME FROM DUAL            --改變時間-日
     SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(SYSDATE+N/24,'yyyy-mm-dd hh24:mi:ss') AS NEWTIME FROM DUAL         --改變時間-時
     SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(SYSDATE+N/24/60,'yyyy-mm-dd hh24:mi:ss') AS NEWTIME FROM DUAL      --改變時間-分
     SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(SYSDATE+N/24/60/60,'yyyy-mm-dd hh24:mi:ss') AS NEWTIME FROM DUAL   --改變時間-秒

21. 查詢月的第一天，最後一天

   SELECT TRUNC(TRUNC(SYSDATE, 'MONTH') - 1, 'MONTH') FIRST_DAY_LAST_MONTH,
       TRUNC(SYSDATE, 'MONTH') - 1 / 86400 LAST_DAY_LAST_MONTH,
       TRUNC(SYSDATE, 'MONTH') FIRST_DAY_CUR_MONTH,
       LAST_DAY(TRUNC(SYSDATE, 'MONTH')) + 1 - 1 / 86400 LAST_DAY_CUR_MONTH
   FROM DUAL

22. 字串截取

   SELECT SUBSTR('abcdef',1,3) FROM DUAL

23. 查找字符串位置

   SELECT INSTR('abcfdgfdhd','fd') FROM DUAL

24. 字元串連接

   SELECT 'HELLO'||'hello world' FROM DUAL

25. 去掉字符串

   1)去掉字符串中的空格：SELECT LTRIM(' abc') S1,RTRIM('zhang ') S2,TRIM(' zhang ') S3 FROM DUAL
   2)去掉前導和尾碼：SELECT TRIM(LEADING 9 FROM 9998767999) S1,TRIM(TRAILING 9 FROM 9998767999) S2,TRIM(9 FROM 9998767999) S3 FROM DUAL

26. 返回字串首字母的Ascii值

   SELECT ASCII('a') FROM DUAL

27. 返回Ascii值對應的字母

   SELECT CHR(97) FROM DUAL

28. 計算字串長度

   SELECT LENGTH('abcdef') FROM DUAL

29. initcap 首字母變大寫 | lower 變小寫 | upper 變大寫

   SELECT LOWER('ABC') S1,UPPER('def') S2,INITCAP('efg') S3 FROM DUAL

30. REPLACE替換

   SELECT REPLACE('abc','b','xy') FROM DUAL

31. TRANSLATE

   SELECT TRANSLATE('abc','b','xx') FROM DUAL -- x是1位

32. lpad(左添充)rpad(右填充) 用於控制輸出格式

   SELECT LPAD('func',15,'=') S1, RPAD('func',15,'-') S2 FROM DUAL

33. DECODE(實現IF …THEN 邏輯)

   SELECT DECODE('50',10,'1',20,'2',30,'3','其他') FROM DUAL
   注:第一個是運算式,最後一個是不滿足任何一個條件的值

34. CASE(實現SWITCH …CASE 邏輯)

  SELECT CASE X-FIELD 
         WHEN X-FIELD < 40 THEN 'X-FIELD 小於 40'
         WHEN X-FIELD < 50 THEN 'X-FIELD 小於 50'
         WHEN X-FIELD < 60 THEN 'X-FIELD 小於 60'
         ELSE 'UNBEKNOWN'
        END
   FROM DUAL 
   注:CASE語句在處理類似問題就顯得非常靈活。當只是需要匹配少量數值時，用Decode更為簡潔。

35. 取整函數（ceil 向上取整,floor 向下取整）

  SELECT CEIL(66.6) N1,FLOOR(66.6) N2 FROM DUAL

36. 取冪(power) 和求平方根(sqrt)

  SELECT POWER(3,2) N1,SQRT(9) N2 FROM DUAL

37. 求餘

 SELECT MOD(9,5) FROM DUAL

38. 返回固定小數位數(round:四捨五入，trunc:直接截斷)

 SELECT ROUND(66.667,2) N1,TRUNC(66.667,2) N2 FROM DUAL

39. 返回值的符號(正數返回為1,負數為-1)

 SELECT SIGN(-32),SIGN(293) FROM DUAL

40. TO_CHAR()[將日期和數位類型轉換成字元類型]

    1) SELECT TO_CHAR(SYSDATE) S1,
        TO_CHAR(SYSDATE,'yyyy-mm-dd') S2,
        TO_CHAR(SYSDATE,'yyyy') S3,
        TO_CHAR(SYSDATE,'yyyy-mm-dd hh12:mi:ss') S4,
        TO_CHAR(SYSDATE, 'hh24:mi:ss') S5,
        TO_CHAR(SYSDATE,'DAY') S6 
    FROM DUAL;
   2) SELECT SAL,TO_CHAR(SAL,'＄99999') N1,TO_CHAR(SAL,'＄99,999') N2 FROM EMP

41. TO_DATE()[將字元類型轉換為日期類型]

42. TO_NUMBER() 轉換為數位類型

SELECT TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) FROM DUAL; --以數字顯示的小時數

43. 返回登錄的用戶名稱

SELECT USER FROM DUAL

44. 返回運算式所需的位元組數

SELECT VSIZE('HELLO') FROM DUAL

45. NVL(EX1,EX2): EX1值為空則返回EX2,否則返回該值本身EX1（常用）例：如果雇員沒有傭金，將顯示0，否則顯示傭金

46. NVL2(EX1,EX2,EX3) : 如果EX1不為空，顯示EX2,否則顯示EX3

47. NULLIF(EX1,EX2): 值相等返空，否則返回第一個值例：如果工資和傭金相等，則顯示空，否則顯示工資

48. COALESCE：返回列表中第一個非空運算式

SELECT NULL,13,COALESCE(NULL,13,13*10) FROM DUAL

49. 运算函数

    //求部門30 的最高工資，最低工資,平均工資，總人數，有工作的人數，工種數量及工資總和
     SELECT MAX(ENAME),MAX(SAL), 
     MIN(ENAME),MIN(SAL),
     AVG(SAL),
     COUNT(*) ,COUNT(JOB),COUNT(DISTINCT(JOB)) ,
     SUM(SAL) FROM EMP WHERE DEPTNO=30

50. 帶GROUP BY 和 HAVING 的分組

    1)按部門分組求最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和
    SELECT DEPTNO, MAX(ENAME),MAX(SAL),
    MIN(ENAME),MIN(SAL),
    AVG(SAL),
    COUNT(*) ,COUNT(JOB),COUNT(DISTINCT(JOB)) ,
    SUM(SAL) FROM EMP GROUP BY DEPTNO;   
   2)部門30的最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和 
    SELECT DEPTNO, MAX(ENAME),MAX(SAL),
    MIN(ENAME),MIN(SAL),
    AVG(SAL),
    COUNT(*) ,COUNT(JOB),COUNT(DISTINCT(JOB)) ,
    SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=30

51. STDDEV 返回一組值的標準差

    1)按部門分組求最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和
    SELECT DEPTNO, MAX(ENAME),MAX(SAL),
    MIN(ENAME),MIN(SAL),
    AVG(SAL),
    COUNT(*) ,COUNT(JOB),COUNT(DISTINCT(JOB)) ,
    SUM(SAL) FROM EMP GROUP BY DEPTNO;   
   2)部門30的最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和 
    SELECT DEPTNO, MAX(ENAME),MAX(SAL),
    MIN(ENAME),MIN(SAL),
    AVG(SAL),
    COUNT(*) ,COUNT(JOB),COUNT(DISTINCT(JOB)) ,
    SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=30

52. VARIANCE 返回一組值的方差差

    SELECT DEPTNO,VARIANCE(SAL) FROM EMP GROUP BY DEPTNO

53. 帶有ROLLUP和CUBE操作符的GROUP BY

    ROLLUP 按分組的第一個列進行統計和最後的小計
    CUBE 按分組的所有列的進行統計和最後的小計
    SELECT DEPTNO,JOB ,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB;
    SELECT DEPTNO,JOB ,SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO,JOB); 
    CUBE 產生組內所有列的統計和最後的小計
    SELECT DEPTNO,JOB ,SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO,JOB);

54. 臨時表

   //只在會話期間或在交易處理期間存在的表.
   //臨時表在插入資料時，動態分配空間 
   CREATE GLOBAL TEMPORARY TABLE TEMP_DEPT
   (DNO NUMBER,
   DNAME VARCHAR2(10))
   ON COMMIT DELETE ROWS;
   INSERT INTO TEMP_DEPT VALUES(10,'ABC');
   COMMIT;
   SELECT * FROM TEMP_DEPT; --無資料顯示,資料自動清除
   ON COMMIT PRESERVE ROWS:--在會話期間表一直可以存在（保留資料）
   ON COMMIT DELETE ROWS:--事務結束清除資料（在事務結束時自動刪除表的資料）

55. LISTAGG和XMLAGG函数

   1)LISTAGG(BSO.ID, ',') WITHIN GROUP (ORDER BY BSO.ID) 
  规范写法 : LISTAGG(字段, 连接符) WITHIN GROUP (ORDER BY 字段) 
  LISTAGG是满足需要的，LISTAGG 返回的是一个varchar2类型的数据，最大字节长度为4000。
  2)XMLAGG(XMLPARSE(CONTENT BSO.ID || ',' WELLFORMED) ORDER BY BSO.ID).GETCLOBVAL()
  规划写法： XMLAGG(XMLPARSE(CONTENT 字段 || 字符串 WELLFORMED) ORDER BY 字段).GETCLOBVAL()
  XMLAGG返回的类型为CLOB，最大字节长度为32767。

56. 立刻执行sql语句，并赋值给某个变量

   EXECUTE IMMEDIATE L_STR2 INTO LS_WEEK;

57. ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

简单的说ROW_NUMBER ()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号.

58. count()over()分析函数的使用

1.)count(*) over()求总计数。
2.)count(*) over(order by id)递加求计数。
3.)count(*) over(partition by id)分组求计数。
4.)count(*) over(partition by id order by name)分组递加求计数。

59. REGEXP_REPLACE(1,2,3,4,5,6)

语法说明：1：字段；2：替换的字段；3.替换成什么；4.起始位置（默认从1开始）；5：替换的次数（0是无限次）；6：不区分大小写。

60. OVER(PARTITION BY …. ORDER BY …)

    1.COUNT ( )  OVER(PARTITION BY …. ORDER BY …) 求分组后的总数。
	2.MAX ( )  OVER(PARTITION BY …. ORDER BY …) 求分组后的最大值。
	3.MIN ( )  OVER(PARTITION BY …. ORDER BY …) 求分组后的最小值。
	4.AVG ( )  OVER(PARTITION BY …. ORDER BY …) 求分组后的平均值。
	5.LAG ( )  OVER(PARTITION BY …. ORDER BY …) 取出前N行数据。
	6.LEAD ( )  OVER(PARTITION BY …. ORDER BY …) 取出后N行数据。

61. PIVOT(MAX(RATE/行转列后列的值/) FOR WEEK/需要行转列的列/ IN(‘46’ “Week_46”,‘47’ “Week_47”/列的值/))

本文地址：https://blog.csdn.net/weixin_43658143/article/details/107632915