SQL函数
单行函数
针对每一行都会返回一个结果
1. 字符函数
字符串大小写转换函数
- initCap(待转换的字符串)
将字符串首字母转换成大写,其余都转换成小写
select initCap(ename) from emp
- lower(待转换的字符串)
将参数里面的字符串,转换成小写
select lower(ename) from emp;
select * from emp where lower(ename) = 'scott'
- upper(待转换的字符串)
将参数里面的字符串,转换成大写
select upper(ename) from emp;
字符串控制函数
- concat(字符串1,字符串2)
将字符串1和字符串2连接到一起获得一个新的字符串
select '奖金:'||comm||'工作:'||job||'工作编号:'||empno from emp;
尝试拼接一下别的字段
select concat('姓名:',ename) from emp;
select concat(concat('姓名:',ename),'薪资:') from emp;
select concat(concat(concat('姓名:',ename),'薪资:'),sal) abc from emp;
- substr(字符串,开始位置,保留数量)
拆分字符串
select substr(ename,2,4) from emp;
- lpad(补齐字符串,整体补齐的位数,不够位数的用指定的字符补齐)
左补齐
select lpad(ename,10,'a') from emp;
- rpad(补齐字符串,整体补齐的位数,不够位数的用指定的字符补齐)
右补齐
select rpad(job,8,'www')from emp;
- instr(字符串,查找字符,开始位置(默认为1,可以不写))
返回该字符在字符串中的第一个出现的位置
select instr(ename,'M',2) from emp;
select instr(ename,'T') from emp where empno = 7369 ;
- length
返回字符串的长度
select length(ename) from emp where empno = 7369;
2. 数字函数
select * from emp1 for update
insert into emp1 values(7935,'JOKER','MANAGER',NULL,
to_date('1990-10-10','yyyy-mm-dd'),1500.45,null,20);
- ceil(待向上取整的值)
比数大,且距离最近的整数
select ceil(sal) from emp1 where empno=7936
- floor(待向下取整的值)
比数小,且距离最近的整数
select floor(sal) from emp where empno=7844
- mod(值1,值2)
% 取余,值1/值2
select mod(sal,200) from emp1 where empno=7844
- round(传参,保留小数点的位数)
select round(sal,2) from emp1 where empno in(7935,7936)
- trunc(传参,保留小数位)
不会四舍五入
select trunc(sal,5) from emp where empno = 7935
3. 日期函数
- 系统当前时间:sysdate
dual 空表占位为了符合语法
select sysdate from dual;
- add_months(待增加的日期,要增加的月份数)
把增加月份数后的日期返回
select add_months(sysdate,2) from dual;
select add_months(hiredate,2) from emp where empno = 7935;
–next_day(指定的日期,星期几)
返回指定日期的下一个星期几
select next_day(sysdate,'星期三') from dual
- last_day(指定的日期)
返回指定日期所在月份的最后一天
select last_day(sysdate) from dual
- trunc用法:
- trunc(指定的日期):截断时分秒,返回年月日
select trunc(hiredate) from emp1 where empno=7935
select hiredate from emp where empno=7844
select trunc(sysdate) from dual;
select sysdate from dual;
- trunc(指定的日期,‘yyyy’):返回指定日期所在年份的第一天
select trunc(hiredate,'year') from emp1;
select trunc(sysdate,'yyyy') from dual;
select trunc(hiredate,'yyyy') from emp;
- trunc(指定的日期,‘mm’):返回指定日期所在月份的第一天
select trunc(hiredate,'month')from emp1 where empno=7844
select trunc(sysdate,'mm') from dual;
- trunc(指定的日期,‘day’):返回指定日期所在星期的第一天
select trunc(sysdate,'dd') from dual
select trunc(sysdate,'day') from dual;
-
数据库中一个星期的第一天是周天
-
round用法:
- round(指定的日期):当天时间已过12小时,四舍五入到下一天,舍弃时分秒
select round(sysdate) from dual;
select round(
to_date('2020-01-02 02:32:11','yyyy-mm-dd hh24:mi:ss')) from dual
- round(指定的日期,‘yyyy’):当年时间已过6个月,四舍五入到下一年初,舍弃时分秒
select round(sysdate,'yyyy') from dual;
select round(hiredate,'yyyy') from emp1 where empno=7844
- round(指定的日期,‘mm’):当月时间已过一半(例如15天),四舍五入到下一月初,舍弃时分秒
select round(sysdate,'mm') from dual;
select round(hiredate,'mm') from emp1 where empno=7935
- round(指定的日期,‘day’):当前星期已过一半,四舍五入到下一星期开始,舍弃时分秒
select round(sysdate,'day') from dual;
select round(hiredate,'dd') from emp1 where empno=7936
4. 转换函数
- to_date(日期的字符串,转换格式)
select * from emp where hiredate =
to_date('1981-02-22','yyyy-mm-dd');
select to_date(
'2018-10-11 11:51:52','yyyy-mm-dd hh24:mi:ss') from dual;
- to_char(日期的字符串,截取时间)
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
select sysdate from dual 与上方格式不同
select to_char(sysdate,'yyyy') from dual;
5. 通用函数
- nvl(查询值,补全值)
如果查询值没有数据,则显示补全值
select nvl(comm,10),t1.* from emp t1;
- decode(列名,判断1,输出1,判断2,输出2···)
select * from emp;
select comm,ename,decode(
comm,300,'你好',500,'你很好',null,'不好') from emp;
select job,ename,decode(
job,'SALESMAN','不戳','CLERK','真不戳','MANAGER','棒棒棒')from emp1;
多行函数
接收多个输入值,返回一个输出值
1. 聚合函数
- avg(值):平均值
select avg(sal) from emp;
- min(值):最小值
select min(sal),min(hiredate),min(ename) from emp;
- max(值):最大值
select max(sal),max(hiredate),max(ename) from emp;
- sum(值):求和
select sum(sal) from emp;
- count(值):统计有数值的数量(理解为统计行数,如果没有数据则不统计)
select count(*) from emp - nvl(字段,x) 补全函数,补x占位
查年薪
select t.*,(sal+nvl(comm,0))*12 from emp t
把0换成10
select nvl(comm,10) from emp
group by 分组
这两种要求都与SQL语句的执行优先级有关:
from > on > join > where > group by > having > select > distinct > order by
- 求每个部门的人数,平均薪资,最大薪资,最小薪资,薪资总和,最简单的将重复数据分组
- 部门的平均薪资
select deptno, avg(sal) from emp group by deptno
- 最大薪资
select deptno, max(sal) from emp group by deptno
- 最小薪资
min(sal) - 薪资总和
select deptno, sum(sal) from emp group by deptno
having 分组条件
类似于where,不过只能用在group by 里面
- 筛选出最小薪资大于1000元的数据
select deptno,min(sal) from emp1 group by deptno having min(sal)>1000
2. 分组函数/分析函数
-
语法:
–row_number() over() :默认从第一行到最后一行排名
–row_number() over(order by 排序):根据某一列排序,然后排名
–row_number() over(partition by 分组 order by 排序):根据某个值分组,然后某一列排序,然后排名 -
row_number:排名(值重复,会选择一个排名靠前,一个靠后,然后依次排名)
-
rank:排名(值重复,排名并列,然后跳过原有排名,总排名数不变)
-
dense_rank:排名(值重复,排名并列,不跳过原有排名,依次排名)
select
row_number() over(partition by deptno order by sal desc),
rank() over(partition by deptno order by sal desc),
dense_rank() over(partition by deptno order by sal desc),
t1.*
from emp t1 ;
- 将每个部门员工按照薪资降序排序,展示部门编号、员工姓名、薪资;
select deptno,ename,sal from emp order by deptno desc,sal desc
- 去重:distinct
- 例:查询emp表中不重复的部门;
select distinct deptno from emp
- 例:查询emp表中不重复的部门;