【Oracle数据库】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