Oracle資料庫中通用的函數範例詳解

2022-03-25 16:01:26

一、 Scott使用者下的表結構

SCOTT。是在Oracle資料庫中，一個範例使用者的名稱。其作用是為初學者提供一些簡單的應用範例，不過其預設是鎖定狀態，在安裝時，根據使用者需要，在“資料庫設定助手”介面完成後，彈出的對話方塊中--口令管理，裡面解鎖。

SCOTT是ORACLE內部的一個範例使用者，預設口令為tiger，下面有表emp, dept等，這些表和表間的關係演示了關係型資料庫的一些基本原理

1、如果自己沒有Scoot表就可以自己建立一個

（1）建立DEPT表

CREATE  TABLE  DEPT  (
DEPTNO  NUMBER(2)  CONSTRAINT  PK_DEPT   PRIMARY KEY,
DNAME  VARCHAR2(14) ,  
LOC  VARCHAR2(13) 
) ;

（2）表DEPT新增資料

INSERT  INTO  DEPT  VALUES  (10  ,  'ACCOUNTING'  ,  'NEW YORK'  );  
COMMIT;
INSERT  INTO  DEPT  VALUES  (20  ,  'RESEARCH'  ,  'DALLAS'  );  
COMMIT;
INSERT  INTO  DEPT  VALUES  (30  ,  'SALES'  ,  'CHICAGO'  );  
COMMIT;
INSERT  INTO  DEPT  VALUES  (40  ,  'OPERATIONS'  ,  'BOSTON'  );  
COMMIT;

（3）建立EMP表

CREATE  TABLE  EMP  (
EMPNO  NUMBER(4)    CONSTRAINT PK_EMP PRIMARY KEY,  
ENAME  VARCHAR2(10),  
JOB  VARCHAR2(9),  
MGR  NUMBER(4),  
HIREDATE  DATE,  
SAL  NUMBER(7,2),  
COMM  NUMBER(7,2),  
DEPTNO  NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

（4）表EMP新增資料

INSERT   INTO   EMP   VALUES  (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
COMMIT;
INSERT   INTO   EMP   VALUES  (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
COMMIT;
INSERT   INTO   EMP   VALUES  (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
COMMIT;
INSERT   INTO   EMP   VALUES  (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
COMMIT;
INSERT   INTO   EMP   VALUES  (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
COMMIT;
INSERT   INTO   EMP   VALUES  (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
COMMIT;
INSERT   INTO   EMP   VALUES  (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
COMMIT;
INSERT   INTO   EMP   VALUES  (7788,'SCOTT','ANALYST',7566,to_date('19-04-1987','dd-mm-yyyy')-85,3000,NULL,20);
COMMIT;
INSERT   INTO   EMP   VALUES  (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
COMMIT;
INSERT   INTO   EMP   VALUES  (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
COMMIT;
INSERT   INTO   EMP   VALUES  (7876,'ADAMS','CLERK',7788,to_date('23-05-1987','dd-mm-yyyy')-51,1100,NULL,20);
COMMIT;
INSERT   INTO   EMP   VALUES  (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
COMMIT;
INSERT   INTO   EMP   VALUES  (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
COMMIT;
INSERT   INTO   EMP   VALUES  (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

（5）建立SALGRADE表

CREATE   TABLE   SALGRADE    (
GRADE   NUMBER,  
LOSAL   NUMBER,  
HISAL   NUMBER 
);

（6）表SALGRADE新增資料

INSERT   INTO   SALGRADE   VALUES   (1,700,1200);  
COMMIT;
INSERT   INTO   SALGRADE   VALUES   (2,1201,1400);  
COMMIT;
INSERT   INTO   SALGRADE   VALUES   (3,1401,2000);  
COMMIT;
INSERT   INTO   SALGRADE   VALUES   (4,2001,3000);  
COMMIT;
INSERT   INTO   SALGRADE   VALUES   (5,3001,9999);  
COMMIT;

（7）建立BONUS表

CREATE   TABLE   BONUS    (  
ENAME   VARCHAR2(10)  , 
JOB   VARCHAR2(9)  ,  
SAL   NUMBER,  
COMM   NUMBER  
) ;

二、單行函數

1、字元函數

接收字元輸入返回字元或者數值，dual 是偽表

（1）把小寫的字元轉換成大寫的字元

--（1）把小寫的字元轉換成大寫的字元
select upper('smith') from dual;

（2）把大寫字元變成小寫字元

--（2）把大寫字元變成小寫字元
select lower('WHJ') from dual;

2、數值函數

（1）四捨五入函數：round()

預設情況下 ROUND 四捨五入取整，可以自己指定保留的位數
四捨五入函數小數第一位小於5

--四捨五入函數 小數第一位小於5
select round(5.342345) from dual;

四捨五入函數小數第一位大於5

--四捨五入函數 小數第一位小於5
select round(5.342345) from dual;

四捨五入函數小數點保留兩位

--四捨五入函數 小數點保留兩位
select round(5.12764,2) from dual;

（2）日期函數

Oracle 中提供了很多和日期相關的函數，包括日期的加減，在日期加減時有一些規律

日期 – 數位 = 日期

日期 + 數位 = 日期

日期 – 日期 = 數位

範例：查詢僱員的進入公司的週數。（分析：查詢僱員進入公司的天數(sysdate – 入職日期)/7就是週數）

--查詢僱員的進入公司的週數。（分析：查詢僱員進入公司的天數(sysdate – 入職日期)/7就是週數）
--1.員工表
select * from emp;
--2.查詢ward 進入公司的週數
select Ename,round((sysdate-hiredate)/7) from emp where Ename='WARD';

獲得兩個時間段中的月數：MONTHS_BETWEEN()

範例：查詢所有僱員進入公司的月數

--查詢所有僱員進入公司的月數
select ename,round(months_between(sysdate,hiredate)) as 進入公司月數 from emp;

（3）轉換函數

TO_CHAR:字串轉換函數

範例：查詢所有的僱員將將年月日分開，此時可以使用 TO_CHAR 函數來拆分

拆分時需要使用萬用字元
年：y, 年是四位使用 yyyy
月：m, 月是兩位使用 mm
日：d, 日是兩位使用 dd

查詢所有的僱員將將年月日分開

--查詢所有的僱員將將年月日分開
select empno,ename,
       to_char(hiredate,'yyyy') as 年,
       to_char(hiredate,'mm') as 月,
       to_char(hiredate,'dd') as 日
       from emp;

日期將日期格式改為yyyy-mm-dd字串格式

-- 初始格式
select * from emp;
--日期將日期格式改為yyyy-mm-dd字串格式
select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;

在結果中10以下的月前面補了0，可以使用fm去掉前置0

--在結果中10以下的月前面補了0，可以使用fm去掉前置0
select empno,ename,to_char(hiredate,'fmyyyy-mm-dd') from emp;

TO_DATE:日期轉換函數

TO_DATE 可以把字串的資料轉換成日期型別

--TO_DATE 可以把字串的資料轉換成日期型別
select to_date('2022-03-10','yyyy/mm/dd:ss')as 當前日期 from dual;

（4）通用函數

空值處理 nvl

範例：查詢所有的僱員的年薪

--查詢所有的僱員的年薪 comm年終獎
select ename,sal*12+comm from emp;

我們發現很多員工的年薪是空的，原因是很多員工的獎金是 null，null 和任何數值計算都是

null，這時我們可以使用 nvl 來處理

--查詢所有的僱員的年薪 comm年終獎
select ename,nvl(comm,0) 年終獎 ,sal*12+nvl(comm,0)年薪 from emp;

Decode 函數

--該函數類似 if....else if...esle
 
--語法：
 DECODE(col/expression, [search1,result1],[search2, result2]....[default])
Col/expression:列名或表示式
 
1. Search1，search2...:用於比較的條件
2. Result1, result2...:返回值
3. 如果 col/expression 和 Searchi 匹配就返回 resulti,否則返回 default 的預設值

--decode函數
--1. 我是1
select decode(1,1,'我是1',2,'我是2','我是無名') from dual;
--2. 我是2
select decode(2,1,'我是1',2,'我是2','我是無名') from dual;
--3. 我是無名
select decode(3,1,'我是1',2,'我是2','我是無名') from dual;

範例：查詢出所有僱員的職位的中文名

select ename,decode(job,
                   'clerk','業務員',
                   'SALESMAN','銷售',
                   'PRESIDENT','總裁',
                   'MANAGER','經理',
                   'NALYST','分析員',
                   '員工' 
                   ) from emp ;

case when

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

範例：查詢出所有僱員的職位的中文名

--範例：Case when 查詢出所有僱員的職位的中文名
select t.empno,t.ename,
       case
          when t.job='clerk' then '業務員'
          when t.job='SALESMAN' then '銷售'
          when t.job='PRESIDENT' then '總裁'
          when t.job='MANAGER' then '經理'
          when t.job='NALYST' then '分析員'
          else '員工'
          end
          from emp t;

三、多行函數（聚合函數）

1、統計記錄數

範例：查詢出所有員工的記錄數

-- 範例：查詢出所有員工的記錄數
select count(*) from emp;

不建議使用 count(*)，可以使用一個具體的列以免影響效能。

--不建議使用 count(*)，可以使用一個具體的列以免影響效能。
select count(ename) from emp;

2、最小值查詢 min()

範例：查詢出來員工最低工資

--範例：查詢出來員工最低工資
select min(sal) from emp;

3、最大值查詢 max()

範例：查詢出員工的最高工資

--範例：查詢出員工的最高工資
select max(sal) from emp;

4、平均值查詢 avg()

範例：查詢出員工的平均工資

--範例：查詢出員工的平均工資
select avg(sal) from emp;

5、求和函數

範例：查詢出 20 號部門的員工的工資總和

--範例：查詢出 20 號部門的員工的工資總和
select sum(sal) from emp where deptno=20;

四、分組統計

分組統計需要使用 GROUP BY 來分組

--語法：
SELECT * |列名 FROM 表名 
{WEHRE 查詢條件} 
{GROUP BY 分組欄位}
 ORDER BY 
 列 名 1 ASC|DESC，列名 2...ASC|DESC

範例：查詢每個部門的人數

-- 範例：查詢每個部門的人數
select deptno,count(ename) from emp group by deptno;

範例：查詢出每個部門的平均工資

-- 範例：查詢出每個部門的平均工資
select deptno,avg(sal) from emp group by deptno;

如果我們想查詢出來部門編號，和部門下的人數

-- 如果我們想查詢出來部門編號，和部門下的人數
select deptno,count(ename) from emp;

我們發現報了一個 ORA-00937 的錯誤

注意：

1.如果使用分組函數，SQL 只可以把 GOURP BY 分組條件欄位和分組函數查詢出來，不能有其

他欄位。

2. 如果使用分組函數，不使用 GROUP BY 只可以查詢出來分組函數的值

範例：按部門分組，查詢出部門名稱和部門的員工數量

-- 範例：按部門分組，查詢出部門名稱和部門的員工數量
select d.deptno,d.dname,count(e.ename) 
from emp e,dept d
where e.deptno=d.deptno 
group by d.deptno,d.dname

範例：查詢出部門人數大於 5 人的部門

-- 範例：查詢出部門人數大於 5 人的部門
--(分析：需要給 count(ename)加條件，此時在本查詢中不能使用 where，可以使用 HAVING)
select d.deptno,d.dname,count(e.ename) 
 from emp e,dept d
 where e.deptno=d.deptno 
 group by d.deptno,d.dname 
 having  count(e.ename)>5 ;

分析：需要給 count(ename)加條件，此時在本查詢中不能使用 where，可以使用 HAVING

範例：查詢出部門平均工資大於 2000 的部門

-- 範例：查詢出部門平均工資大於 2000 的部門
 
select d.deptno,d.dname,avg(e.sal) 
from dept d ,emp e
 where d.deptno=e.deptno 
 group by d.deptno,d.dname
  having avg(e.sal)>2000;