SQL语言----基本函数

1、数值函数

• 求值函数
   - abs(n) 绝对值
   - log(x,y) x为底y的对数
   - mod(x,y) 取余
   - power(x,y) x的y次方
   - sqrt(x) x的平方根

select abs(-234) from dual;  --求 -234 的绝对值
--返回值：234
select log(2,4) from dual;   --以2为底4的对数
--返回值：2
select mod(2,4) from dual;   --求2/4的余数
--返回值：2
select power(2,4) from dual; --求2的4次方
--返回值：16
select sqrt(4) from dual;    --求4的平方根
--返回值：2

• 处理小数
   - round(n[,保留的小数位数])  --四舍五入
   - trunc(n[,保留的小数位数])  --舍去
   - CEIL(n)  --向上取整
   - FLOOR(n)  --向下取整

select round(12345.12345,2) from dual;
--返回：12346
select trunc(12345.99999,2) from dual;
--返回：12345.99
select ceil(12345.99999) from dual;
--返回：12346
select floor(12345.99999) from dual;
--返回：12345

• 聚合函数
   - sum(字段) --求和
   - avg(字段) --平均
   - max(字段) --最大值
   - min(字段) --最小值
   - count(字段) --个数
   - wm_concat(字段) --将字段所有的值拼接成一行

select sum(sal),avg(sal),max(sal),min(sal) from emp;

--返回：
   	SUM(SAL)	AVG(SAL)	MAX(SAL)	MIN(SAL)
	29025		2073.21428	5000	    800

select count(*) from emp;
--返回：14

select wm_concat(ename) from emp;
--返回：SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

2、字符函数

• 总值函数：length(字符 | 数字)

select length('abcdefg') from dual; --返回字符串的长度
--返回值：7
select length(12345) from dual; --返回数字的位数
--返回值：5
select ename,length(ename) from emp; --返回emp表中ename字段的长度
--返回值：SMITH  5

• 拼接函数：concat(参数1,参数2)  也可以直接使用 “||” 符号进行拼接，输出效果一致，注意使用函数只能拼接两个字段，超过两个需要进行函数嵌套

select concat(ename,job) from emp --拼接emp表中ename、job字段
select ename || job from emp      --拼接emp表中ename、job字段
--拼接前：SMITH  CLERK
--拼接后：SMITHCLERK

• 查询函数：instr(str,目标字符[,起始下标 默认1[,出现次数 默认1]]) 返回字符串所在位置的下标，启始下标为负数，代表逆向，但依然返回正向下标

• 截取函数：substr(str,起始下标,截取长度)，如启始位置为负数，代表逆向

select instr('abcdefg','e') from dual;    --返回字符串中e所在的位置
select instr('abcdefg','e',1) from dual;  --返回字符串中e所在的位置
--返回值：5
select instr('abcdefgezxvb','e',1,2) from dual;   --返回字符串中e第二次出现的位置
--返回值：8
select instr('abcdefgezxvb','e',3) from dual;     --从第三个位置开始查找返回字符串中e的位置
--返回值：5

select substr('abcd',3) from dual;       --截取后2个字符 
--返回值：cd
select substr('abcdefg',1,4) from dual;  --从第一个位置开始，截取4个字符
--返回值：abcd

• 去空函数：trim(str)去除字符串左右两边空格；ltrim(str)去除左边空格；rtrim(str)去除右边空格

select '  abc   ',
       trim('  abc   ')  as 去两边空格,
       ltrim('  abc   ') as 去左边空格,
       rtrim('  abc   ') as 去右边空格
  from dual ;
--返回值：'abc'  'abc   '  '  abc'
select '$$$$sadfasdfa$$$$',
        trim('$' from '$$$$sadfasdfa$$$$') as 去除两边符号,
        ltrim('$$$$sadfasdfa$$$$','$')     as 去除左边符号,
        rtrim('$$$$sadfasdfa$$$$','$')     as 去除右边符号
   from dual ;
--返回值：'sadfasdfa'  'sadfasdfa$$$$'  '$$$$sadfasdfa'

• 大小写转换：upper(str)小写转换为大写；lower(str)大写转小写；initcap(str)仅首字母大写

• 替换函数：replace(str,替换目标,替换值)，重复出现的字符都将被替

select initcap('abc') from dual;   --全小写，首字母大写，其余小写
select initcap('ABC') from dual;   --全大写，首字母大写，其余小写
--返回值：'Abc'
select upper('abc'), lower('ABC') from dual;  --小写转大写，大写转小写
--返回值：'ABC'  'abc'

select replace('abcdec','c','C') from dual;  --将字符串中小c替换为大C
返回值：abCdeC

• 空值填充：nvl(字段名,填充值)，目的使其可以参与运算；    nvl2(字段名,非空显示,空值显示)
• 非空填充：左 | 右 填充--lpad | rpad (字段，长度，'填充字符')

select nvl(comm,0) from emp;     --如果comm值为空 填充0
select nvl(ename,job) from emp;  --如果ename值为空 填充job字段的值

select nvl2(comm,'Y','N') from emp;  --如果comm值为空 填充'Y'否则填充'N'

select lpad('abc',10,'#') from dual; --在'abc'左侧填充'#'补全10位
--返回值：'#######abc'
select rpad('abc',10,'*') from dual; --在'abc'右侧填充'*'补全10位
--返回值：'abc*******'

3、日期函数

• 系统日期：sysdate，yyyy年，mm月，hh整点，mi分钟，ss秒
• 日期截取：trunc (日期,'格式化参数')

select sysdate from dual;      --返回当前系统时间
select sysdate + 1 from dual;  --返回当前系统时间加1天
--返回值：20XX-XX-XX XX:XX:XX  默认显示格式

select trunc(sysdate,'yyyy') from dual; --返回当前年的第一天
--返回值：20XX-01-01
select trunc(sysdate,'mm') from dual;   --返回当前月的第一天
--返回值：20XX-XX-01
select trunc(sysdate,'dd') from dual;   --返回当前日期
--返回值：20XX-XX-XX
select trunc(sysdate,'yyyy-mm-dd') from dual;
--返回值：ORA-01898: 精度说明符过多

• 获取最后一天：last_day(原日期)
• 获取下周时间：next_day()

select last_day(sysdate) from dual;    --返回当前月的最后一天
--返回值：20XX-XX-XX XX:XX:XX
select last_day(sysdate)-2 from dual;  --当前月的倒数第3天
--返回值：20XX-XX-XX XX:XX:XX

select next_day(sysdate,4) from dual;              --返回下一个周四的日期
select next_day(sysdate,'星期四') from dual;        --返回下一个周四的日期
select next_day(next_day(sysdate,4),4) from dual;  --返回下下个周四的日期
--返回值：20XX-XX-XX XX:XX:XX
select next_day(sysdate,8) from dual;
--返回错误： ORA-01846: 周中的日无效

• 返回增加月数后的日期：add_months(指定日期,增加月数) 
• 返回两个日期之间的月数：months_between(启始日期,终止日期)

select add_months(sysdate,5) from dual;      --当前日期加 5个月 
select add_months(sysdate,-12*3) from dual;  --当前日期减36个月
--返回值：20XX-XX-XX XX:XX:XX

--计算现在距离'2020-03-06'有多少个月
select months_between(sysdate,to_date('2020-03-06','yyyy-mm-dd')) from dual;
--返回值：XX.XXXX

4、转换函数

• 字符转数字：to_number(str [,数字格式]) 
• 数字转字符：to_char(num[,字符串格式]) 
• 字符转ASCII--ASCII(str)    ASCII转字符--chr(num)

select to_number('123') from dual;  --将文本型'123'转为数值型 123
--返回： 数值型：123

select to_number('$99.99999','$99.99999') from dual;  --通过用$进行格式化输出显示 
--返回： 数值型 99.99999
select to_number('$197.9890','$99.99999') from dual;
--返回： ORA-01722: 无效数字 ,要满足格式化精度

select to_char('123') from dual;  --将数值型 123 转为文本型'123'
--返回： 文本型：123
select to_char(57.12389,'L99.999') from dual;  --以本地货币符输出（L表示本地货币符）
-- 返回：￥197.98989

• 时间格式化：to_date(str,'yyyy-mm-dd hh24:mi:ss')，yyyy 4位年、mm 2位月、dd 2位天、hh 12时制、hh24 24时制、mi  分钟、ss 秒钟

• 日期转字符：to_char(num[,字符串格式])

select to_date('2020','yyyy') from dual;         --字符串格式化为日期类型 
select to_date('2020-01','yyyy-mm') from dual;
select to_date('2020-01-01','yyyy-mm-dd') from dual;
select to_date('2020-01-01 13:23:35','yyyy-mm-dd hh24:mi:ss') from dual;


select to_char(sysdate) from dual;      --日期格式化为字符串类型
--返回：日 - 月 - 年 的形式
select to_char(sysdate,'yyyy-mm-dd') from dual;
--返回：YYYY-MM-DD的日期格式
select to_char(sysdate,'yyyy') from dual;
select to_char(sysdate,'year') from dual;
--返回：4位的年 
select to_char(sysdate,'mm') from dual;
select to_char(sysdate,'month') from dual;
--返回：2位月份 
select to_char(sysdate,'dd') from dual;
--返回：2位天 
select to_char(sysdate,'cc') from dual;
--反回：当前世纪 
select to_char(sysdate,'day') from dual;
--返回：当前是星期几