SQL系统函数知识点梳理（Oracle）

函数

函数是已经封装好的程序，会返回一个值，直接拿来使用就可以了
特点：返回一个值

系统函数

系统函数是已经存在的函数，比如： 聚合函数，转换函数（to_date()函数）。
下面介绍一些常用函数

转换函数

to_date()

to_date(‘目标字符’,‘日期格式’);
功能：将目标字符转换成想要的日期格式
返回：日期

SYSDATE --系统日期

SELECT SYSDATE FROM dual;

to_char()

转换字符函数
to_char(目标数值或日期,[‘格式’])
功能：将数值或者日期按照格式转换成字符
返回：字符

将数值转换成字符

SELECT to_char(123.45) FROM dual  //没给格式仅仅将数值转换成了字符。

格式

0 ：代表格式，
----当整数位无数字则以0补全，
----当小数位无数字，以0补全，
----当小数位精度不足，则四舍五入，
----当整数位精度不足，则显示成#####
9 ：代表格式
----当整数位无数字，则格式不生效（只有此处与0不同）
----当小数位无数字，以0补全，
----当小数位精度不足，则四舍五入，
----当整数位精度不足，则显示成######

以上是格式9的用法，0与9的区别只有第一条的区别不一样。
如果换成0整数位无数字会被0代替。

添加货币符号

$:美元符号
L:当地货币

将日期转换成字符

d-本周的第几天 ==注意：==每周的第几天，美国人是按照星期日开始计算的 所以说星期三是本周的第四天
dd-本月的第几天
ddd-本年的第几天
w-本月的第几周
ww-本年的第几周
mm-本年的第几月
q-本年的第几季（1.1-4.1，4.1-7.1，7.1-10.1，10.1-1.1）
yyyy-年
day-星期几
year-多少年

其他不常用的转换函数

函数名	作用
chartorowid()	字符串转换为rowid值
ROWIDTOCHAR()	rowid值转换成字符串
CONVERT()	字符串语言字符集转换
HEXTORAW()	16进制转换为二进制
RAWTOHEX()	二进制转换为16进制
TO_NUMBER()	字符串转换为数字型
TO_MULTI_BYTE()	半角转换为全角
to_single_byte()	全角转化为半角
nls_charset_id()	字符集名称转为ID
nls_charset_name()	字符集ID转换为名称

---

字符型函数

针对字符做处理

连接函数

CONCAT(‘字符1’,‘字符2’)
作用：将字符1和字符2进行拼接，拼接后的结果作字符返回。
返回：字符。

SELECT CONCAT('ABC','def') from dual;
SELECT concat(ename,job) FROM emp;
SELECT concat(ename,sal) FROM emp; //sal是数值类型，注意下面的解释

与 || 的区别
1.CONCAT()里面的参数只有两个。如果连接的字符有三个及以上 ||可以直接拼，而CONCAT()需要用到函数嵌套。
函数嵌套：里层的函数返回的结果成为了外层函数的一部分
2.concat()是函数存在于多个数据库中，但是 || 只能再Oracle数据库中使用。

Oracle数据库会将数值隐性转换成字符。通俗来讲，在Oracle中写出来的数字既是数字也是字符。但是在代码中尽量少或者不要出现隐性转换，因为会效率低。

---

大小写转换函数

大写转换

UPPER(字符)
作用：将字符变成大写
返回：字符

SELECT UPPER('asAADSsaers一二三ad') From dual;

汉字没有大小之分，如果放入大写字母或者汉字都不会变

小写转换

LOWER(字符)
作用：将字符变成小写
返回：字符

首字母大写，其余的小写

INITCAP(字符)
作用：将字符首字母大写，其余变成小写。
返回：字符

SELECT INITCAP('mkmkADWQdsaMKAdaS') FROM dual;

注意：如果转变的字符中存在空格或者其他符号，被分开的第二段字母首字母同样会大写。

---

替换函数

REPLACE(目标字符,目标字符中要被替换的部分，替换成的值)

SELECT REPLACE('asdwsc','sdw','哈哈哈哈');
SELECT REPLACE(ename,'a','K'),ename from emp;

REPLACE()可以去空格

SELECT REPLACE('errr errw   wad',' ','哈'),REPLACE('errr errw   wad',' ','') from dual;

去除空格函数

Trim()
作用：去除字符两侧的空格
返回：字符

SELECT TRIM('   abc  def    ')  FROM dual;

Ltrim():去除左侧空格
Rtrim():去除右侧空格

截取函数

Substr(目标字符，截取位置参数n，[长度参数l])
作用：从第n个位置，截取l个长度。

SELECT ename,SUBSTR(ename,0,2) FROM EMP;

SELECT ename,SUBSTR(ename,-2,2) FROM EMP;//位置可以是负数，-2是指从倒数第二位开始截，截2长度。长度不能为负数

如果不写l参数，默认截取到最后。
1.在这里面 0，1没区别 都是从头开始截取。
2.位置可以是负数，长度必须的大于0；
3.不管位置是不是负数，截取都是从前往后截

练习题：

1）截取字符串’ABCDEF’中的前三个字符
2）截取名字中的前两位字符
3）从名字中的第二位字符开始，截取三位长度

SELECT SUBSTR('ABCDEF',0,3) FROM dual;
SELECT ename,SUBSTR(ename,0,2) FROM emp;
SELECT ename,SUBSTR(ename,2,3) FROM emp;

---

填充函数

lpad(目标字符,长度l,值)
左右：向目标左侧填充值，使其达到长度L
返回：字符

SELECT LPAD('abc',10,'*');

rpad(目标字符,长度l,值)
左右：向目标右侧填充值，使其达到长度L
返回：字符

SELECT LPAD('abc',10,'*');

---

获取字符长度函数

length(字符);
作用：返回字符的长度
返回：数值

SELECT LENGTH('sadasfasd') FROM dual;

练习：
1.找出姓名长度是五位 的员工信息
2.查询姓名长度是五位并且首字母是A
3.按照员工姓名长度分组，计算每组的人数，并且按照姓名长度排序。

SELECT * 
	FROM emp 
		WHERE LENGTH(ename)=5;
SELECT * 
	FROM emp 
		WHERE LENGTH(ename)=5 AND ename LIKE'A%';
SELECT * 
	FROM emp 
		WHERE LENGTH(ename)=5 AND SUBSTR(ename,1,1) ='A';.//第二题第二种写法。
SELECT COUNT(1),LENGTH(ename) 
	FROM emp 
		GROUP BY LENGTH(ename) 
			ORDER BY LENGTH(ename);2

//WHERE后面不能用聚合函数，其他函数可以用

---

获取字符位置函数

INSTR(目标字符,目标字符中要找的字节s,位置参数n,次数参数n1)
作用：在目标字符中，从第n个位置开始找，找到第n1次出现的s，返回s所在目标字符中的位置。
返回：数值

SELECT INSTR('sjakdjkalsjhfasjdjwa','j','3','2') FROM dual;//从第三位开始找，找到j第2次出现的位置

找不到值的时候会返回0;
练习
查找姓名中至少有两个A的员工信息；
查找姓名中正好有两个A的员工信息；

SELECT * FROM emp WHERE INSTR(ename,'A')!=0;
SELECT * FROM emp WHERE INSTR(ename,'A',1,2)!=0 AND INSTR(ename,'A',1,3)=0;

---

函数名	作用
ASCLL()	返回字符的ASCII码
CHR()	返回ASCII码为ｘ的字符
NLS_LOWER()	把整个字符串转换成小写
NLS_UPPER()	把整个字符串转换成大写
LENGTH()	返回字符串的长度
LENGTHB()	返回字符串的长度（全角算２字符）
LTRIM()	删除左边字符串
RTRIM()	删除右边字符串
SOUNDEX()	字符串语音表示形式
TRANSLATE()	替换子字符

---

数值型函数

绝对值函数

abs(数值)
作用：返回数值的绝对值
返回：数值

SELECT ABS(-516.5),ABS(546),ABS(0) FROM dual;

---

向上取整，向下取整

向上取整
ceil(整数)
作用：返回离数值较近的较大的整数。
返回：数值

SELECT ceil(1.1),ceil(1.9999),ceil(-1.1) FROM dual;

向下取整
floor(数值)
作用：返回离数值较近的较小的整数。
返回：数值

SELECT floor(1.1),floor(1.9999),floor(-1.1) FROM dual;

---

取余函数

mod(x,y)
作用：返回x除以y得到的余数
返回：数值
练习：
查询姓名长度是奇数的员工信息

SELECT * from emp where MOD( LENGTH(ename),2)=1;

---

四舍五入函数

ROUND(数值,[精度p])
作用：将数值按照精度p进行四舍五入
返回：数值

正数时保留到小数点后几位，负数时保留到小数点前几位，0是保留整数。位数不足时不会以0补全。
ROUND(123.45,-1)的结果是120
ROUND(123.45,0)的结果是123
不写精度默认保留整数

---

截取函数

trunc(数值,[截取精度])
作用：直接截取，不四舍五入

---

判断正负函数

sign(数值)
作用：判断数值的正负
返回：数值

SELEC SIGN(123.45),sign(-123.45),sign(0) FROM dual;

–

返回x的y次幂函数

power(x,y)
作用：返回x的y次幂
返回：数值

---

日期型函数

添加月函数

ADD_MONTHS(d1,n1)
作用：返回在日期d1的基础上，再加上n1个月后的新日期
返回：日期

SELECT SYSDATE,add_months(sysdate,3) hz FROM dual;

---

该月最后一天函数

last_day(d1)
作用：返回日期d1所在月份最后一天的日期。
返回：日期

---

间隔月函数

months_between(d1,d2)
作用：返回两个日期之间的月数
返回：数值

SELECT SYSDATE,MONTHS_BETWEEN(SYSDATE,TO_DATE('2008/2/3','yyyy/mm/dd')) FROM dual;

如果d1>d2,则返回正数，
如果d2>d1,则返回负数

---

NEW_TIME()

NEW_TIME(dt1,c1,c2)
【功能】：给出时间dt1在c1时区对应c2时区的日期和时间
【参数】：dt1，d2 日期型

【返回】：日期时间

【参数】：c1,c2对应的 时区及其简写
大西洋标准时间：AST或ADT
阿拉斯加_夏威夷时间：HST或HDT
英国夏令时：BST或BDT
美国山区时间：MST或MDT
美国中央时区：CST或CDT
新大陆标准时间：NST
美国东部时间：EST或EDT
太平洋标准时间：PST或PDT
格林威治标准时间：GMT
Yukou标准时间：YST或YDT

---

NEXT_DAY()

NEXT_DAY(d1[,c1])
【功能】：返回日期d1在下周，星期几(参数c1)的日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前 日期)
【参数表】：c1对应:星期一，星期二，星期三……星期日
【返回】：日期

trunc()

trunc(d1[,c1])
【功能】：返回日期d1所在期间(参数c1)的第一天日期
【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即当前日期)
【参数表】：c1对应的参数表:
最近0点日期: 取消参数c1或j
最近的星期日：day或dy或d (每周顺序：日，一，二，三，四，五，六）
最近月初日期：month或mon或mm或rm
最近季日期：q
最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)
最近世纪初日期：cc或scc

日期减法

日期减日期等于天数

练习
1.查询出入职日期是当月倒数第三天入职的员工信息

SELECT * FROM emp WHERE last_date(hiredate)-hiredate = 2;

---

聚合函数（分组函数）

AVG()
SUM()
MAX()
MIN()
COUNT()
等，相信大家用的很熟练。

开窗函数（分析函数）（窗口函数）–重点

未更新

其他函数

空值替换函数

NVL(列名,值)
作用：将该列空值替换成想要的值

SELECT ename,sal,NVL(comm,1) FROM emp;

注意：保证值和前面列的属性一致。

练习，计算每个人的年薪

SELECT ename,sal,comm,(sal+NVL(comm,0) )*12 年薪 FROM emp;

NVL2(列名，值1，值2)
作用：当该列不为空的时候返回值1，为空的时候返回值2.

SELECT ename,comm,NVL2(comm,2,4) FROM emp;

去重

DISTINCT
作用：将查询结果去重
DISTINCT 效率低
/*执行顺序
FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT ***
ORDER BY
*/
(不是函数)

---

条件判断取值函数（重点！）

DECODE(判断目标,
				目标返回的值1,重新定义值1,
				目标返回值2,重新定义值2
				，.......,
				其他情况定义为
				)

判断奖金，当奖金时空值的时候，则翻译成没有奖金。当奖金有值的时候翻译成有奖金。

SELECT ename,comm,DECODE(comm,NULL,'没有奖金','有奖金') from emp;

练习：当工资为1000-1999的时候，翻译成工资小于2000，当工资是2000-2999 翻译成小于3000；

SELECT sal,DECODE(floor(sal/1000),1,'工资小于2000',2,'工资小于3000',3,'工资小于4000','工资大于4000');

2. 显示员工姓名 姓名长度，当姓名长度是奇数的时候返回’奇数‘否则返回’偶数‘

SELECT ename,Length(ename),DECODE(MOD(LENGTH(ename),2),1,'奇数',2,'偶数','啊？') FROM emp;

CASE WHEN

写法

CASE WHEN 条件1......    --当满足条件1时
		  THEN 结果1       ----执行结果1
  	 WHEN 条件2......
		  THEN 结果2
     WHEN 条件3......
		  THEN 结果3.....
			      [ELSE 其他结果]     ---当不满足以上所有条件，则要执行的结果
END      ----结束

练习：当工资为1000-1999的时候，翻译成工资小于2000，当工资是2000-2999 翻译成小于3000；

  SELECT 
      ename,sal,
      CASE WHEN sal>=1000 AND sal<2000
      THEN '工资小于2000'
      WHEN sal>=2000 AND sal<3000
      THEN  '工资小于3000'
      WHEN sal>=3000 AND sal<4000
      THEN  '工资小于4000'
      ELSE  '土豪'
      END AS "工资情况"
      
      FROM emp;
			

练习：给13季度员工涨薪10%，给24季度员工降薪10%；

SELECT ename,sal,CASE 
       WHEN to_char(hiredate,'q') IN (1,3)
         THEN  sal*1.1
       WHEN to_char(hiredate,'q') IN (2,4)
         THEN sal*0.9
       END AS "薪资变化"
FROM emp;

函数练习题：

--查询当前日期
SELECT SYSDATE FROM DUAL;
--显示员工工资，加上$
SELECT SAL, CONCAT(SAL, 's') FROM EMP;
--将字符串"1980-12-17"转成日期
SELECT TO_DATE('1980-12-17', 'yyyy-mm-dd') FROM DUAL;
--使用case表达式，职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400
SELECT SAL,
       ENAME,
       JOB,
       CASE
         WHEN JOB = 'ANALYSE' THEN
          SAL + 1000
         WHEN JOB = 'MANAGER' THEN
          SAL + 800
         ELSE
          SAL + 400
       END AS 薪资变化
  FROM EMP;
--使用decode函数，职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400【oracle专用】
SELECT SAL,
       JOB,
       DECODE(JOB, 'ANALYSE', SAL + 1000, 'MANAGER', SAL + 800, SAL + 400) "薪资变化"
  FROM EMP;
---从今天开始算，下下一个星期三是多少号？(next_day)
SELECT NEXT_DAY(NEXT_DAY(SYSDATE, '星期三'), '星期三') FROM DUAL;
--员工总工资，平均工资，四舍五入，保留小数点后2位
SELECT ROUND(SUM(SAL), 2), ROUND(AVG(SAL), 2) FROM EMP;
--求入职最早员工和入职最晚员工的入职日期
SELECT MIN(TO_CHAR(HIREDATE, 'yyyy')) "最早",
       MAX(TO_CHAR(HIREDATE, 'yyyy')) "最晚"
  FROM EMP;
--统计有佣金的员工人数
SELECT COUNT(1), DECODE(COMM, NULL, '无奖金', '有奖金') "有无佣金"
  FROM EMP
 GROUP BY DECODE(COMM, NULL, '无奖金', '有奖金');
--按部门求出部门平均工资，且平均工资取整数
SELECT ROUND(AVG(SAL), 0), AVG(SAL), DEPTNO FROM EMP GROUP BY DEPTNO;
--除30号部门外，查询部门平均工资大于1500元的部门
SELECT DEPTNO
  FROM EMP
 WHERE DEPTNO <> 30
 GROUP BY DEPTNO
HAVING AVG(SAL) > 1500;
--显示部门平均工资的最大值
SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;