Oracle數據庫中通用的函數是什么

這篇文章主要為大家展示了“Oracle數據庫中通用的函數是什么”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“Oracle數據庫中通用的函數是什么”這篇文章吧。

一、 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, [search2,result1],[search3, result2]....[default])
Col/expression:列名或表達式
 
1. Search2，search3...:用于比較的條件
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;

以上是“Oracle數據庫中通用的函數是什么”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！