Oracle数据库中通用的函数

Oracle数据库中的函数

??程序员小王的博客：程序员小王的博客

?? 欢迎点赞 ?? 收藏 留言 ??

?? 如有编辑错误联系作者，如果有比较好的文章欢迎分享给我，我会取其精华去其糟粕

??java自学的学习路线：java自学的学习路线

一、 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.878)?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;