最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
Oracle数据库中通用的函数代码实例解析
时间:2022-06-29 09:22:24 编辑:袖梨 来源:一聚教程网
本篇文章小编给大家分享一下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, [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;
相关文章
- 炉石传说网易云音乐联动怎么玩 网易云音乐联动活动介绍 11-22
- 《潜行者2:切尔诺贝利之心》游戏车辆使用推荐 11-22
- 《潜行者2:切尔诺贝利之心》挡路的特异点处理方法介绍 11-22
- 《潜行者2:切尔诺贝利之心》开局获得满强AK方法介绍 11-22
- 《潜行者2:切尔诺贝利之心》军用防弹背心获得方法介绍 11-22
- 《潜行者2:切尔诺贝利之心》防毒面具获得方法介绍 11-22