Oracle進階（三）常用函式

目錄

1、聚合函式

2、轉換函式

3、字符函式

4、數值函式

5、日期函式

6、判斷函式

7、分析函式

附表

1、聚合函式

常用的SQL 聚合函式有5個：最大(MAX) 最小(MIN) 平均(AVG) 總和(SUM) 求數(COUNT)

--一、聚合函式 基本語法

--MAX|MIN|AVG|SUM|COUNT (DISTINCT|ALL)

--其它聚合函式 類似,但中位數 DISTINCT會提示錯誤：DISTINCT 選項在此函式中禁用，
SELECT MAX(DISTINCT SAL) FROM EMP;
SELECT MAX(SAL) FROM EMP

--二、聚合函式 常見需求：

--1、查詢EMP表里所有部門的名稱和最高工資

SELECT E.DEPTNO,D.DNAME,MAX(E.SAL) 
FROM EMP E 
LEFT JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO,D.DNAME;

--2、查詢EMP表里所有部門的名稱和最低工資

SELECT E.DEPTNO,D.DNAME,MAX(E.SAL),MIN(E.SAL) 
FROM EMP E 
LEFT JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO,D.DNAME;

--3、計算EMP表里超過1人的每個部門

SELECT E.DEPTNO,COUNT(*) 
FROM EMP E
GROUP BY DEPTNO
HAVING COUNT(*) > 1;

--4、 查詢EMP表里每個部門的匯總工資

SELECT E.DEPTNO,SUM(E.SAL) AS 工資總和 
FROM EMP E
GROUP BY DEPTNO
HAVING COUNT(*) > 1;

--5、查詢EMP表里每個部門的平均薪酬

SELECT E.DEPTNO,AVG(E.SAL) AS 平均工資
FROM EMP E
GROUP BY DEPTNO
HAVING COUNT(*) > 1;

--其它：標準差(STDDEV) 協方差(VARIANCE) 中位數(MEDIAN) 

2、轉換函式

常用的SQL 轉換函式有3個：

TO_CHAR()：將DATE或者NUMBER轉換為字串
TO_DATE()：將NUMBER、CHAR轉換為DATE
TO_NUMBER()：將CHAR轉換為NUMBER

--1、TO_CHAR()：將DATE或者NUMBER轉換為字串

 SELECT TRUNC(TO_DATE(SYSDATE),'Y') FROM DUAL;
 SELECT TO_CHAR(DATE'2021-04-25','YYYY') FROM DUAL;  -- 年
 SELECT TO_CHAR(DATE'2021-04-25','YYYYMM') FROM DUAL; -- 年月
 SELECT TO_CHAR(DATE'2021-04-25','WW') FROM DUAL;     -- 第17周
 SELECT TO_CHAR(DATE'2021-04-25','IW') FROM DUAL;     -- 第16周（自然周）
 SELECT TO_CHAR(DATE'2021-04-25','Q') FROM DUAL;     -- 第2季度
 SELECT TO_CHAR(DATE'2021-04-25','MM') FROM DUAL;    -- 月
 SELECT TO_CHAR(DATE'2021-04-25','DD') FROM DUAL;    -- 日
 SELECT TO_CHAR(DATE'2021-04-25','D') FROM DUAL;       --本周第幾天（星期天為第一天）

--2、 TO_DATE()：將NUMBER、CHAR轉換為DATE

--注：Date 強制轉換,要有'-'才能轉換，數字只會出現錯誤
--注：字符或數字中的年月日必須是日期范圍內的值
--注：字符日期中有-或/轉化為日期型別必須加-或/   

  SELECT DATE'2021-01-01' FROM DUAL;  -- 強制轉換為日期格式
  SELECT TO_DATE('2021-01-01','YYYY/MM/DD') FROM DUAL;
  SELECT TO_DATE('2021-01-01','YYYY-MM-DD') FROM DUAL;
  SELECT TO_DATE('2021-01-01','YYYYMMDD') FROM DUAL;  -- 錯誤:無效的月份
  SELECT TO_DATE('2021-13-08','YYYY-MM-DD'),TO_DATE(20210732,'YYYY-MM-DD') FROM DUAL;-- 錯誤:無效的月份
  SELECT TO_DATE('2021-07-08','YYYYMMDD') FROM DUAL;-- 錯誤:無效的月份
  SELECT TO_DATE(20210708,'YYYY-MM-DD')  FROM DUAL;

--3、 TO_NUMBER()：將CHAR轉換為NUMBER

  SELECT TO_NUMBER('1 2') FROM DUAL;--錯誤：無效數字
  SELECT TO_NUMBER(' 12') FROM DUAL;
  SELECT TO_NUMBER('12 ') FROM DUAL;
  SELECT TO_NUMBER(' 12 ') FROM DUAL;
  SELECT TO_NUMBER('12D') FROM DUAL;--錯誤：無效數字

  SELECT  TO_NUMBER(TO_DATE(20210425,'YYYYMMDD')) FROM DUAL;  --錯誤：無效數字
  SELECT  TO_NUMBER(TO_CHAR(TO_DATE(20210425,'YYYYMMDD'),'YYYYMMDD')) FROM DUAL; 

3、字符函式

--字符函式

--1 ASCII 回傳引數的十進制數字表示
SELECT ASCII('A') FROM DUAL; --65

--2 CHR 回傳引數的代碼所指定的字符
SELECT CHR(65) FROM DUAL;  --A

--3 CONCAT 回傳arg1與arg2的拼接結果
SELECT CONCAT('ABC','DEF') FROM DUAL; --ABCDEF

--4 INITCAP 將引數arg1每個單詞的首字母大寫
SELECT INITCAP('yi er shan ren') FROM DUAL; --Yi Er Shan Ren

--5 INSTR(arg1,子字串,開始檢索的位置,搜索第幾次出現子字串)
--從左面第1個字符開始搜索,第一次出現ABC的位置
SELECT INSTR('HABCHABC','ABC',1,1) FROM DUAL; --2

--6 LENGTH 回傳引數arg1的長度
SELECT LENGTH('abcdefg') FROM DUAL; --7

--7 lower 回傳引數arg1的小寫形式
SELECT LOWER('AbCdEfG') FROM DUAL; --abcdefg

--8 LPAD(arg1,n,arg3) 回傳在arg1左邊填充若干個arg3組成長度為n新字串
SELECT LPAD('ABC',10,'Ha') FROM DUAL; --HaHaHaHABC

--9 LTRIM(arg1,arg2)在arg1左面洗掉連續出現的arg2.不指定arg2則默認洗掉空格
SELECT LTRIM('AAAASA','A') FROM DUAL; --SA

--10 RPAD(arg1,n,arg3) 在字串arg1右邊連續重復填充arg3直到填充后的字串總長度到達n為止
SELECT RPAD('thr',5,'ee') FROM DUAL; --three

--11 RTRIM(arg1,arg2) 從arg1中洗掉右側連續出現的arg2.不指定arg2則默認洗掉空格.
SELECT RTRIM('abcdabcabc','abc') FROM DUAL; --abcd

--12 REGEXP_INSTR(源字串,正則運算式,[開始搜索的位置],[描述執行多少次替換],[描述匹配字串的位置的整數],[允許修改正則運算式引擎的默認行為的字串])
SELECT REGEXP_INSTR('ABC123ABC','[0-9]A') FROM DUAL; --6

--13 REGEXP_REPLACE(源字串,正則運算式,當正則運算式匹配時所使用的替換字串,[開始搜索的位置的整數],[描述執行多少次替換的資料],[允許修改正則運算式引擎的默認行為的字串])
SELECT REGEXP_REPLACE('abc123abc123','[0-9]','#') FROM DUAL; --abc###abc###

--14 EGEXP_SUBSTR(源字串,正則運算式,[開始搜索的位置],[描述執行多少次替換的資料],[允許修改正則運算式引擎的默認行為的字串])
SELECT REGEXP_substr('abc133abc456','([0-9]{3})abc([0-9]{3})') FROM DUAL; --133abc456

--15 REPLACE(arg1,search,replace) 該函式用replace引數替換出現的所有search引數
SELECT REPLACE('abcdabce','abc','x') FROM DUAL; --xdxe

--16 SUBSTR(arg1,pos,len) 
SELECT SUBSTR('1234567890',3,5) FROM DUAL; --34567

--17 TRANSLATE(arg1,match,replace)
SELECT TRANSLATE('PI*R*2','*R','#@') FROM DUAL;  --PI#@#2

--18 TRIM([LEADING][TRAILING][BOTH] char FROM source)
--截斷串首連續出現的字符1
SELECT TRIM(LEADING '1' FROM '1122332211') FROM DUAL;--22332211
SELECT TRIM(BOTH ' ' from ' ! ') as BOTH,TRIM(' ! ') as source FROM DUAL; --!  --!

4、數值函式

--數值函式

--1 ABS 絕對值 
SELECT  ABS(-2) FROM DUAL;  -- 2

--2 ROUND 四舍五入
SELECT ROUND(2.281,1) FROM DUAL; -- 2.3

--3 MOD 求余
SELECT MOD(10,3) FROM DUAL; --- 1

--4 POWER 平方 求導
SELECT POWER(2,3) AS 平方,POWER(2,-1) AS 求導 FROM DUAL;--8 --.5

--5 TRUNC 直接截取，不四舍五入
SELECT TRUNC(4.1231,2) FROM DUAL; --4.12

5、日期函式

--日期函式

--日期格式：
/*
YYYY：四位表示的年份 
YYY，YY，Y：年份的最后三位、兩位或一位，預設為當前世紀 
IYYY:ISO標準的四位年份
MM：01~12的月份編號 
MON：縮寫字符集表示 
MONTH：全拼字符集表示的月份，右邊用空格填補
Q：季度
W：當月第幾周
WW：當年第幾周 
IW:ISO標準的年中的第幾周
D：當周第幾天 
DD：當月第幾天 
DDD：當年第幾天 
DY：縮寫字符集表示
DAY：全拼字符集表示的天 如（星期六）
HH，HH12：一天中的第幾個小時，12進制表示法 
HH24：一天中的第幾個小時，取值為00~23 
MI：一小時中的分鐘 
SS：一分鐘中的秒 
SSSS：從午夜開始過去的秒數 
*/
SELECT TO_CHAR(SYSDATE, 'YYYY') 年,
       TO_CHAR(SYSDATE, 'MM') 月,
       TO_CHAR(SYSDATE, 'DD') 日,
       TO_CHAR(SYSDATE, 'HH24') 時,
       TO_CHAR(SYSDATE, 'MI') 分,
       TO_CHAR(SYSDATE, 'SS') 秒,
       TO_CHAR(SYSDATE, 'DAY') 天,
       TO_CHAR(SYSDATE, 'Q') 第幾季度,
       TO_CHAR(SYSDATE, 'W') 當月第幾周,
       TO_CHAR(SYSDATE, 'WW') 當年第幾周,
       TO_CHAR(SYSDATE, 'D') 當周第幾天,
       TO_CHAR(SYSDATE, 'DDD') 當年第幾天    
  FROM DUAL;
	
--注：日期可以加減數字,表示加減多少天;日期減去日期表示相隔多少天;日期不能相加
--注：MONTHS_BETWEEN的第一個日期要大于第二個日期，否則回傳負數，

  SELECT DATE'2021-04-25'+2,DATE'2021-04-25'-2,DATE'2021-02-25'-DATE'2021-04-25' FROM DUAL;--2021-4-27 --2021-4-23  --59
			
--1 ADD_MONTHS(日期,月數)（n可以為負值） 增減月份
  SELECT ADD_MONTHS(SYSDATE, 1),ADD_MONTHS(DATE'2021-04-25',-2) FROM DUAL;--2021-5-25 16:17:38 --2021-2-25
    
--2 LAST_DAY(日期),回傳指定日期當月的最后一天
  SELECT LAST_DAY(SYSDATE),LAST_DAY(DATE'2021-04-25') FROM DUAL;--2021-4-30 16:19:46 --2021-4-30

--3 MONTHS_BETWEEN (date1, date2),用于計算date1和date2之間有幾個月
  SELECT MONTHS_BETWEEN(TO_DATE('2021-4-25', 'yyyy-mm-dd'), TO_DATE('2021-1-1', 'yyyy-mm-dd')) MON_DIFF FROM DUAL;--3.77419354838709677419354838709677419355
  
--4 FLOOR：計算兩個日期間的天數
  SELECT FLOOR(TO_DATE('2021-05-03 20:00:00','yyyy-mm-dd hh24:mi:ss')-TO_DATE('2021-05-01 12:00:00','yyyy-mm-dd hh24:mi:ss')) A FROM DUAL;--2

--5 NEXT_DAY(d,string)：（準確來說一個禮拜是從星期日開始到星期六結束的）
  SELECT NEXT_DAY(SYSDATE,'星期五') NEXT_DAY FROM DUAL; --2021-4-30 16:34:32

--6 EXTRACT(fmt FROM d)：提取日期中的特定部分
SELECT SYSDATE ,
       EXTRACT(YEAR FROM SYSDATE) "YEAR",
       EXTRACT(MONTH FROM SYSDATE) "MONTH",
       EXTRACT(DAY FROM SYSDATE) "DAY",
       EXTRACT(HOUR FROM SYSTIMESTAMP) "HOUR",
       EXTRACT(MINUTE FROM SYSTIMESTAMP) "MINUTE",
       EXTRACT(SECOND FROM SYSTIMESTAMP) "SECOND"
  FROM DUAL;

--7 TO_TIMESTAMP(char[fmt[,'nls_param’]])：應注意char、fmt、nls_param之間的對應關系，
SELECT TO_TIMESTAMP('25-4月-21 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') FROM DUAL;--25-4月 -21 07.46.41.000000000 上午
SELECT CAST(SYSDATE AS TIMESTAMP) DATE_TO_TIMESTAMP from dual;--25-4月 -21 04.36.04.000000 下午

6、判斷函式

6.1 NVL(expr1,expr2)：若expr1引數為空則顯示expr2引數的值，反之則顯示expr1引數本來的值

--1 NVL(expr1,expr2)：若expr1引數為空則顯示expr2引數的值，反之則顯示expr1引數本來的值
 SELECT NVL(NULL,0) FROM DUAL;     -- 0
 SELECT NVL(NULL,100) FROM DUAL;   -- 100
 SELECT NVL('', 99) FROM DUAL;   -- 99
 SELECT NVL(10, 100) FROM DUAL;   --10
 SELECT NVL('AB',25) FROM DUAL;     -- AB

6.2 NVL2(expr1,expr2, expr3)：若expr1引數為空則顯示expr2的值，反之則顯示expr3引數的值

--2 NVL2(expr1,expr2, expr3)：若expr1引數為空則顯示expr2的值，反之則顯示expr3引數的值
 SELECT NVL2(NULL, 0, 1) FROM DUAL;      -- 1
 SELECT NVL2('', 99, 2) FROM DUAL;       -- 2
 SELECT NVL2(10, 3, 100) FROM DUAL;      -- 3
 SELECT NVL2('AD', 'AB', 25) FROM DUAL;  -- AB

6.3 DECODE(value,if1,then1,if2,then2,if3,then3,...,else):若value與第一個值相等，則與第一個值的匹配值，以此類推，最后都沒有的，就回傳默認值

--3 DECODE(value,if1,then1,if2,then2,if3,then3,...,else):若value與第一個值相等，則與第一個值的匹配值，以此類推，最后都沒有的，就回傳默認值，
 SELECT DECODE(12, -- value
           1, 2, --if1,then1
          12, 10 --if2,then2
					) FROM DUAL;  -- 10				
 SELECT DECODE(12, 1, 2, 13, 10) FROM DUAL;-- 空			
 SELECT DECODE(12, 1, 2, 13, 10, 
           7 --默認值
					 ) FROM DUAL; --7				 
 SELECT DECODE(12, 1, 2, 12, 10, 12, 88,7) FROM DUAL; --10

6.4 CASE WHEN 多條件判斷

 --4 CASE WHEN 多條件判斷

7、分析函式

附表

--附表1：SCOTT表中的EMP表：員工表

SELECT * FROM EMP
--DROP TABLE EMP

-- CREATE TABLE
CREATE TABLE EMP
(
  EMPNO NUMBER(4),--員工編號，EMP表主鍵
	ENAME VARCHAR2(10), --員工姓名
	JOB VARCHAR2(9), --員工作業
	MGR NUMBER(4),  --員工的領導編號，參考EMPNO
	HIREDATE DATE , --入職日期 
	SAL NUMBER(7,2),  --員工工資 
	COMM NUMBER(7,2),  --員工獎金
	DEPTNO NUMBER(2)  --員工部門編號，是表DEPT的外鍵，
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE EMP
  IS '員工表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN EMP.EMPNO
  IS '員工編號，EMP表主鍵';
COMMENT ON COLUMN EMP.ENAME
  IS '員工姓名';
COMMENT ON COLUMN EMP.JOB
  IS '員工作業';
COMMENT ON COLUMN EMP.MGR
  IS '員工的領導編號，參考EMPNO ';
COMMENT ON COLUMN EMP.HIREDATE
  IS '入職日期 ';
COMMENT ON COLUMN EMP.SAL
  IS '員工工資';
COMMENT ON COLUMN EMP.COMM
  IS '員工獎金';
COMMENT ON COLUMN EMP.DEPTNO
  IS '員工部門編號，是表DEPT的外鍵，';
	
--附表2：SCOTT表中的DEPT表：部門表

SELECT * FROM DEPT
--DROP TABLE DEPT

-- CREATE TABLE
CREATE TABLE DEPT
(
  DEPTNO NUMBER(2), --部門編號，主鍵
	DNAME VARCHAR2(14), --部門名稱
	LOC VARCHAR2(13) --部門位置
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE DEPT
  IS '部門表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN DEPT.DEPTNO
  IS '部門編號，主鍵';
COMMENT ON COLUMN DEPT.DNAME
  IS '部門名稱';
COMMENT ON COLUMN DEPT.LOC
  IS '部門位置';
	
--附表3：SCOTT表中的SALGRADE表：工資等級表

SELECT * FROM SALGRADE

-- CREATE TABLE
CREATE TABLE SALGRADE
(
  GRADE NUMBER, --等級
	LOSAL NUMBER, --此等級下最低工資
	HISAL NUMBER --此等級下最高工資
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE SALGRADE
  IS '工資等級表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN SALGRADE.GRADE
  IS '等級';
COMMENT ON COLUMN SALGRADE.LOSAL
  IS '此等級下最低工資';
COMMENT ON COLUMN SALGRADE.HISAL
  IS '此等級下最高工資';