3.使用单行函数——Oracle数据库学习日记

使用单行函数

函数是SQL的一个非常强大的特性，它可以用来做以下事情：

• 对数据进行计算
• 修改单个数据项
• 操作行组的输出
• 格式化显示日期和数字
• 转换列数据类型

函数分单行函数的多行函数，单行函数用于操作数据项。他们接受一个或多个参数，并为查询返回的每一行返回一个值。可以是下列之一：

• 用户提供的值
• 变量值
• 列名
• 表达式

单行函数的特点包括：

• 对查询中返回的每一行执行操作
• 每行返回一个结果
• 可能返回与引用的数据类型不同的数据值
• 可能需要一个或多个参数
• 可用于SELECT,WHERE,and ORDER BY子句；可以嵌套

单行函数类型：

• 字符
• 数值
• 日期
• 转换
• 通用

1、字符函数

1.1大小写转换函数

这些函数转换字符串的大小写：

函数	结果	作用
LOWER(‘SQL COURSE’)	sql course	转换成小写
UPPER(‘sql course’)	SQL COURSE	转换成大写
INITCAP(‘SQL COURSE’)	Sql Course	首字母大写

实例

select initcap(ename),lower(job),upper(mgt)
	from emp
where ename='KING';

1.2字符控制函数

这些函数处理字符串：

函数	结果	含义
CONCAT(‘Hello’,‘World’)	HelloWorld	将值连接在一起（使用CONCAT只能使用两个参数）
SUBSTR(‘HelloWorld’,1,5)	hello	取一个确定长度的字符串
LENGTH(‘Hello World’)	10	取字符串的长度显示为数值
INSTR(‘HelloWorld’,‘W’)	6	查找指定字符的数字位置
LAPD(salary,10,’*’)	*****24000	使用字符表达式返回左填充到n个字符长度的表达式
RAPD(salary,10,’*’)	24000*****	使用字符表达式返回右填充到n个字符长度的表达式
REPLACE(‘JACK and JUE’,‘J’,‘BL’)	BLACK and BLUE	查找字符，并进行替换字符
TRIM(’ ’ FROM ‘xxxHello Worldxxx’)	HelloWorld	从字符串中修剪开头或结尾的字段

实例
CONCAT：

select concat('good','dba') 
    from dual;
    
select concat(concat(ename,'is'),job)
    from emp;

SUBSTR:

select substr('oracle',1,3)
    from dual;

LENGTH:

select length('oracle')
    from dual;

INSTR:

select instr('oracle','a')
    from dual;  # instr经常被用来判断，判断一个字符串是否在另一个字符串中

select instr('1*2*3*4*5*','*',3,4)
    from dual;  # 查询从第3个字符开始，*第4次出现的位置
    
select instr('1*2*3*4*5*','*',-3,2)
    from dual;  # 查询从倒数第3个字符开始向前，*第2次出现的位置

LPAD,RPAD:

select sal,lpad(sal,4,0),rpad(sal,4,0)
    from emp;

TRIM:

select trim(' hello  ' )
   from dual;
   
select length('  hello  '),length(trim(' hello   '))
   from dual;

2、数值函数

以下函数为处理数值的函数：

函数	结果	含义
ROUND(45.926,2)	45.93	四舍五入到指定的的十进制值
TRUNC(45.926,2)	45.92	截断到指定十进制值
MOD(1600,300)	100	返回余数

ROUND:

SQL> select round(45.923,2),round(45.923,0),round(45.923,-1)
  2     from dual;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
          45.92              46               50

TRUNC:

SQL> select trunc(45.923,2),trunc(45.923),trunc(45.923,-1)
  2     from dual;

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
--------------- ------------- ----------------
          45.92            45               40

MOD:

SQL> select mod(1600,300)
  2     from dual;

MOD(1600,300)
-------------
          100

3.日期函数

3.1使用日期

• Oracle内部使用的数字存储日期：世纪、年、月、日、小时
• 默认的日期显示格式为DD-MON-RR

修改当前session日期格式

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss day';
Session altered.
SQL> select sysdate
  2     from dual;
SYSDATE
-----------------------------
2019-07-20 09:45:01 saturday

日期的算数运算：

命令	结果	含义
date ± number	Date	在日期上加上或减去一个数字，结果仍为日期
date - date	Number of days	两个日期相减返回日期之间的相差天数
date + number/24	Date	可以用小时数除以24，可以加小时到日期上

因为数据库将日期存储为数字，所以可以使用加法和减法等算数运算符执行计算，你可以加减数字常数和日期
实例

SQL> select ename,(sysdate-hiredate)/365 as year
  2     from emp;

ENAME            YEAR
---------- ----------
SMITH      38.6148401
ALLEN      38.4367579
WARD       38.4312784

3.2日期操作函数

函数	含义
SYSDATE	查看当前系统日期时间
MONTHS_BETWEEN	两个日期相差的月数
ADD_MONTHS	向指定日期中添加若干月数
NEXT_DAY	指向日期的下一个日期
LAST_DAY	本月的最后一天
ROUND	日期四舍五入
TRUNC	日期截断

SYSDATE:

SQL> SELECT SYSDATE
  2     FROM DUAL;

SYSDATE
-----------------------------
2019-07-20 10:09:06 saturday

MONTHS_BETWEEN:

SQL> select ename,months_between(sysdate,hiredate)/12 as year
  2     from emp;

ENAME            YEAR
---------- ----------
SMITH      38.5925309
ALLEN      38.4166667
WARD       38.4124233

ADD_MONTHS:

select sysdate,add_months(sysdate,1) from dual;     # 一个月后的现在
select sysdate,add_months(sysdate,12) from dual:        # 一年后
select sysdate,add_months(sysdate,-12) from dual;       # 一年前

NEXT_DAY:

select next_day(sysdate,'MONDAY') from dual;        # 下周一（当前时间）
select next_day(sysdate,2) from dual;       # 下周第二天——下周一（周日为第一天）

LAST_DAY:

select sysdate,last_day(sysdate) from dual;

ROUND:

select sysdate,round(sysdate) from dual;
select sysdate,round(sysdate,'mi') from dual;
select sysdate,round(sysdate,'DD') from dual;

TRUNC:

select sysdate,trunc(sysdate) from dual;        今天
select sysdate,trunc(sysdate,'dd') from dual;       零点
select sysdate,trunc(sysdate,'mm') from dual;       本月的第一天
select sysdate,trunc(sysdate,'yyyy') from dual;     今年的第一天