欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Oracle數(shù)據(jù)庫中通用的函數(shù)實例詳解

 更新時間：2022年03月25日 10:44:55   作者：程序員小王java  
OracleSQL提供了用于執(zhí)行特定操作的專用函數(shù),這些函數(shù)大大增強了SQL語言的功能,下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫中通用函數(shù)的相關(guān)資料,需要的朋友可以參考下

一、 Scott用戶下的表結(jié)構(gòu)

SCOTT。是在Oracle數(shù)據(jù)庫中，一個示例用戶的名稱。其作用是為初學者提供一些簡單的應(yīng)用示例，不過其默認是鎖定狀態(tài)，在安裝時，根據(jù)用戶需要，在“數(shù)據(jù)庫配置助手”界面完成后，彈出的對話框中--口令管理，里面解鎖。

SCOTT是ORACLE內(nèi)部的一個示例用戶，缺省口令為tiger，下面有表emp, dept等，這些表和表間的關(guān)系演示了關(guān)系型數(shù)據(jù)庫的一些基本原理

1、如果自己沒有Scoot表就可以自己創(chuàng)建一個

（1）創(chuàng)建DEPT表

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

（2）表DEPT添加數(shù)據(jù)

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）創(chuàng)建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添加數(shù)據(jù)

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）創(chuàng)建SALGRADE表

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

（6）表SALGRADE添加數(shù)據(jù)

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）創(chuàng)建BONUS表

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

二、單行函數(shù)

1、字符函數(shù)

接收字符輸入返回字符或者數(shù)值，dual 是偽表

（1）把小寫的字符轉(zhuǎn)換成大寫的字符

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

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

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

2、數(shù)值函數(shù)

（1）四舍五入函數(shù)：round()

  • 默認情況下 ROUND 四舍五入取整，可以自己指定保留的位數(shù)
  • 四舍五入函數(shù) 小數(shù)第一位小于5
--四舍五入函數(shù) 小數(shù)第一位小于5
select round(5.342345) from dual;

四舍五入函數(shù) 小數(shù)第一位大于5

--四舍五入函數(shù) 小數(shù)第一位小于5
select round(5.342345) from dual;

四舍五入函數(shù) 小數(shù)點保留兩位

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

（2）日期函數(shù)

Oracle 中提供了很多和日期相關(guān)的函數(shù)，包括日期的加減，在日期加減時有一些規(guī)律

日期 – 數(shù)字 = 日期

日期 + 數(shù)字 = 日期

日期 – 日期 = 數(shù)字

范例：查詢雇員的進入公司的周數(shù)。（分析：查詢雇員進入公司的天數(shù)(sysdate – 入職日期)/7就是周數(shù)）

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

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

范例：查詢所有雇員進入公司的月數(shù)

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

（3）轉(zhuǎn)換函數(shù)

TO_CHAR:字符串轉(zhuǎn)換函數(shù)

范例：查詢所有的雇員將將年月日分開，此時可以使用 TO_CHAR 函數(shù)來拆分

拆分時需要使用通配符
年：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;

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

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

TO_DATE:日期轉(zhuǎn)換函數(shù)

TO_DATE 可以把字符串的數(shù)據(jù)轉(zhuǎn)換成日期類型

--TO_DATE 可以把字符串的數(shù)據(jù)轉(zhuǎn)換成日期類型
select to_date('2022-03-10','yyyy/mm/dd:ss')as 當前日期 from dual;

（4）通用函數(shù)

空值處理 nvl

范例：查詢所有的雇員的年薪

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

我們發(fā)現(xiàn)很多員工的年薪是空的，原因是很多員工的獎金是 null，null 和任何數(shù)值計算都是

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

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

Decode 函數(shù)

--該函數(shù)類似 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函數(shù)
--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','業(yè)務(wù)員',
                   'SALESMAN','銷售',
                   'PRESIDENT','總裁',
                   'MANAGER','經(jīng)理',
                   '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 '業(yè)務(wù)員'
          when t.job='SALESMAN' then '銷售'
          when t.job='PRESIDENT' then '總裁'
          when t.job='MANAGER' then '經(jīng)理'
          when t.job='NALYST' then '分析員'
          else '員工'
          end
          from emp t;

三、多行函數(shù)（聚合函數(shù)）

1、統(tǒng)計記錄數(shù)

范例：查詢出所有員工的記錄數(shù)

-- 范例：查詢出所有員工的記錄數(shù)
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、求和函數(shù)

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

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

四、分組統(tǒng)計

分組統(tǒng)計需要使用 GROUP BY 來分組

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

范例：查詢每個部門的人數(shù)

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

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

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

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

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

我們發(fā)現(xiàn)報了一個 ORA-00937 的錯誤

注意：

1.如果使用分組函數(shù)，SQL 只可以把 GOURP BY 分組條件字段和分組函數(shù)查詢出來，不能有其
 
他字段。
 
2. 如果使用分組函數(shù)，不使用 GROUP BY 只可以查詢出來分組函數(shù)的值

范例：按部門分組，查詢出部門名稱和部門的員工數(shù)量

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

范例：查詢出部門人數(shù)大于 5 人的部門

-- 范例：查詢出部門人數(shù)大于 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;

總結(jié)

到此這篇關(guān)于Oracle數(shù)據(jù)庫中通用的文章就介紹到這了,更多相關(guān)Oracle通用函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

最新評論