oracle学习第二天--函数

合集 - oracle数据库学习(4)

1.oracle-select第一天2024-07-30

2.oracle学习第二天--函数2024-07-31

3.oracle学习第三天--多表关联2024-08-054.oracle学习第四天-DDL/DML2024-08-05

收起

一、group by子句

（1）group by

（2）group by 多字段

1、语法：group by 字段,字段...
2、原理：字段1和字段2组合值相同归为一组
3、案例

-- 1 统计部门工种的最高薪资
-- 解析：按部门和job分组
select * from emp group by deptno,job;

4、结论：多个字段的组合值相同归为一组，对于字段值为null的分组，所有null值归为一组

（3）having 子句

1、语法：having 条件
2、原理：对组进行过滤
3、案例：

-- 按部门求平均薪水，找出平均薪水>2000的部门编号
-- 1、解析：按部门分组，统计出部门平均薪水avg_sal
select deptno,avg(sal) from emp group by deptno
-- 2、解析：使用having 根据平均薪资>2000对组进行过滤
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

-- 求部门中薪水大于1200的员工的平均工资大于2500的部门
-- 1、找出薪水大于1200的所有雇员
select * from emp where sal > 1200;
-- 2、按部门对以上结果集分组，统计平均工资avg_sal
select deptno ,avg(sal) from emp where sal > 1200 group by deptno;
-- 3、对分组按avg_sal>2500进行过滤
select deptno ,avg(sal) from emp where sal > 1200 group by deptno having avg(sal) > 2500;

4：面试题：where和having的区别？
回答：where是对行也就是记录进行过滤，而having是对组进行过滤

二、函数

概念：具有特定功能的单元，可以被重复使用
作用：对结果集进行加工
分类：
单行函数：对单个值进行加工，返回一个新值
多行函数：对多行数据进行加工，返回一个新值。也称为聚合函数（统计函数）

（1）单行函数

1、字符函数：专门用于处理字符串
a、函数：
length(str): 返回str的长度（字符个数）

select length('this is database world') from dual;

concat(str1,str2) 把str1和str2拼接为新的字符串并返回。简写str1||str2

select concat('this is database',' world') from dual;

lower(str) str转化成小写

select lower('this is database world') from dual;

upper(str) str转化成大写

select upper('this is database world') from dual;

ltrim(str,x) 删除str左边的x，x可省略，默认是空格

select ltrim(' this is database world ') from dual;

rtrim(str,x) 删除str右边的x，x可省略，默认是空格

select rtrim(' this is database world ') from dual;

trim(x from str) 删除str两边的x，x可省略，默认是空格

select trim(' this is database world ') from dual;

replace(str,oldstr,newstr) 替换字符串

select replace('hello','o','s') from dual;

substr(str,start[,length]) 从str中截取部分字符串
-str： 源字符串
-start： 开始截取的位置，位置从1计数
-lenght： 截取的长度，可省略，默认截取到结尾

select substr('hello',-1,1) from dual;

instr(str,x,start,n) 从左向右在str中查找x，返回第一次找到的位置，没找到返回0
-str： 源字符串
-x： 要查找的字符串
-start： 开始查找的位置，可省略，默认是1，注意传入负值，表示从右向左
-n： n表示第几次出现

select instr('hello','l',-1,1) from dual;

b、案例：
查询员工姓名以'S'结尾的员工信息，不允许出现like，写出其他方法

-- 方式1：ename截取1为等于S

select * from emp where substr(ename,-1,1) = 'S'

-- 方式2：查找S在ename中，且在最后的位置

select * from emp where instr(ename,'S',-1,1) = length(ename)

-- 方式3：通过删除S来比较删除前后名字的长度

select * from emp where length(rtrim(ename,'S')) != length(ename);

2、数值函数

3、日期时间函数：对日期、时间进行获取和运算
a、函数：
sysdate 返回当前日期时间（日期时间类型）

select sysdate from dual;

add_months(date,n) 在date上增加n个月

select sysdate,add_months(sysdate,2) from dual;

last_day(date) 返回date中当月最后一天

select sysdate,last_day(sysdate) from dual;

months_bwtween(d1,d2) 返回d1和d2之间的月数

select sysdate,months_between(sysdate,add_months(sysdate,2)) from dual;

round(date[,format]) 返回一个以fmt格式的四舍五入日期值
DDD：对月中的天进行四舍五入

select round(sysdate,'DDD') as test from dual;

DAY：舍入到最近的周日

select round(sysdate,'DAY') as test from dual;

MONTH：舍入到某月1日

select round(sysdate,'MONTH') as test from dual;

YEAR：舍入到某年的1月1日

select round(sysdate,'YEAR') as test from dual;

trunc(date[,format]) 返回一个以fmt格式的截断日期，不要四舍五入
DDD：当天

select trunc(sysdate,'DDD') as test from dual;

DAY：舍入本周的周日

select trunc(sysdate,'DAY') as test from dual;

MONTH：舍入到当月1日

select trunc(sysdate,'MONTH') as test from dual;

YEAR：舍入到当年的1月1日

select trunc(sysdate,'YEAR') as test from dual;