SQL 语法
程序员文章站
2022-10-18 11:59:23
表
一个数据库通常包含一个或多个表。每个表由一个名字标识(例如“客户”或者“订单”)。表包含带有数据的记录(行)。
下面的例...
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