Oracle数据库中通用的函数实例详解
作者:程序员小王java 发布时间:2023-07-08 04:49:00
一、 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;
来源:https://blog.csdn.net/weixin_44385486/article/details/123402447
猜你喜欢
- 自己写的用js读取配置文件的程序 D:\Useful Stuff\Javascript\mytest.txt 文件内容如下 [plugin_
- 在上一篇文章中实现了树莓派下对摄像头的调用,有兴趣的可以看一下:python+opencv实现摄像头调用的方法接下来,我们将使用python
- 本文实例讲述了php中$_GET与$_POST过滤sql注入的方法,分享给大家供大家参考。具体分析如下:此函数只能过滤一些敏感的sql命令了
- 基于MySQL分布式锁实现原理及代码工欲善其事必先利其器,在基于MySQL实现分布式锁之前,我们要先了解一点MySQL锁自身的相关内容MyS
- 1、Pycharm -> References(进入设置界面): 3、点击 Add Remote 来添加远程解释器: 4、完善信息:
- 一、前期准备CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT,
- 我的 jupyter-bootbook 是在 ubuntu 下安装 anaconda 获得的,下面的命令在 Windows 下大部分可以运行
- 方法一:也是最简单的 直接使用pd.to_datetime函数实现data['交易时间'] = pd.to_datetime
- 通常的聊天室所采用的程序,也就是Chat程序了,其基本结构原理是不会采用到数据库的。那究竟采用什么技术呢?我们知道ASP变量当中Sessio
- 如下所示:' '.join(line.split())例如:'line dd',运行line.split()
- PDO::rollBackPDO::rollBack — 回滚一个事务(PHP 5 >= 5.1.0, PECL pdo >=
- 今天成功把易语言调用验证码通杀的DLL在Python中成功调用了特此共享出来,下面是识别截图:识别方法1:"""
- 序 言哈喽兄弟们,好久不见!最近实在太忙了,所以又双叒叕断更了~表弟大学快毕业了,学了一个学期Python居然还不会写学生管理系统,真的给我
- 随着 CSS3 渐入人心,Web 字体逐渐成为话题,这种即将让未来的 Web 更加丰富多彩的技术(或者说标准)拥有多种可能,虽然 .webf
- 前言本文给大家深入的解答了关于Python的11道基本面试题,通过这些面试题大家能对python进一步的了解和学习,下面话不多说,来看看详细
- 前言这几天比较空闲,就接触了下百度地图的API(开发者中心链接地址:http://developer.baidu.com),发现调用还是挺方
- 很多次遇到在pycharm中无法安装第三方库的情况,今天我就遇到了,找了很多办法都没用但是在pycharm中配置anaconda环境之后再从
- 通过第三方库exifread读取照片信息。exifread官网:https://pypi.org/project/ExifRead/一、安装
- 用过vuex的肯定会有这样一个痛点,就是刷新以后vuex里面存储的state就会被浏览器释放掉,因为我们的state都是存储在内存中的。所以
- 如下所示:from mpl_toolkits.mplot3d import axes3dimport matplotlib.pyplot a