目录
- 单⾏函数是指对一⾏数据进⾏操作返回一条数据的函数,其结果集也只有一个数据
- 一、单行函数—字符函数
- 二,单行函数—数值函数
- 三、单行函数—日期函数1
- 四、单行函数—转换函数
- 五,单行函数—通用函数
- 1、NVL(m, n),如果 m为空则返回 n——【NVL的参数类型必须要一致】
- 3、DECODE(条件, 值1, 返回值1, 值2, 返回值2, ...值n, 返回值n, 缺省值)
- 4、NVL2(expr1, expr2, expr3),expr1不为null,返回expr2,否则返回 expr3,如果 expr2与 expr3 类型不同的话,expr3会转化为expr2的类型。
- 5、DUMP() 函数可以查看任意值在oracle数据库中的类型,具体类型用Type=t,代码表示。类型与代码值对应关系官方文档。
- 6、CASE WHEN,分支判断函数,与DECODE函数类似,CASE WHEN 更适用于需要做特殊判断处理的条件中
- 六、单行函数练习题
- 七、分组函数
- 八、关于ALL,IN,ANY的函数区别
- 九、日期函数2--last_day()函数和Next_day()
- 十、日期函数3
单⾏函数是指对一⾏数据进⾏操作返回一条数据的函数,其结果集也只有一个数据
一、单行函数—字符函数
1、UPPER,字符串转大写函数
SELECT UPPER ('abcde') FROM dual ; --ABCDE
2、LOWER,字符串转小写函数
SELECT LOWER('ABCDE') FROM dual ; --abcde
3、INITCAP,字符串每个单词的首字母大写
SELECT INITCAP('abc, def') FROM dual; --Abc, Def
4、CONCAT,字符串连接函数
SELECT CONCAT('a', 'b') FROM dual; --ab SELECT 'a' || 'b' FROM dual; --ab
5、SUBSTR (string str, int a, int b),字符串截取函数
只传入两个参数时,str 为需要截取的字符串,a为从第a个下标开始截取后面所有的字符串。传入三个参数时,str为需要截取的字符串,a表示字符串截取的位置,b表示截取字符串的长度。
注:当a等于0或1时,都是从第一位开始截取)
SELECT SUBSTR('abcde', 3) FROM dual; --cde SELECT SUBSTR('abcde', 3, 2) FROM dual; --cd
6、LENGTH,计算字符串长度
SELECT LENGTH('abcde') FROM dual; --5
7、REPLACE,字符串替换函数
SELECT REPLACE('abcde', 'a', 'A') FROM dual; --Abcde
8、INSTR,寻找元素下标
格式一:
instr( string1, string2 ),instr(源字符串, 目标字符串),表示第一次出现string2,同时第一次出现string2首字母的位置。
格式二:
instr( string1, string2, start_position, nth_appearance) ,instr(源字符串, 目标字符串, 起始位置, 匹配序号),表示从string1的第start_position开始,寻找第nth_appearance次出现string2的位置序号。
SELECT INSTR('Hello World', 'or') FROM dual; --寻找在'Hello World'中首次出现'or', 且此时'o'的位置序号, 8
SELECT INSTR('helloworld', 'l', 2, 2) FROM dual; --返回结果:4, 在"helloworld"的第2号位置开始查找,查找第二次出现的“l”的位置
SELECT INSTR('helloworld', 'l', 4, 2) FROM dual; --返回结果:9, 在"helloworld"的第4号位置开始,查找第二次出现的“l”的位置
9、lpad( string, padded_length, [ pad_string ] ) ,向左填充字符至指定长度、rpad( string, padded_length, [ pad_string ] ) ,向右填充字符至指定长度
String:准备被填充的字符串; padded_length:填充完之后的字符串长度,如果该值小于原字符串长度值,则会截取; pad_string:要用来填充的字符,如果未指定,默认填充空格。
SELECT LPAD('Smith',10,'*') FROM dual; --*****Smith SELECT RPAD('Smith',10,'*') FROM dual; --Smith*****
10、TRIM ,去掉字符串首尾空格
SELECT TRIM(' Mr Smith ') FROM dual;
二,单行函数—数值函数
1、ROUND (NUMBER, decimals)
NUMBER:待做处理的数值;
decimals:表明保留小数点后面的小数位,四舍五入,可选项,若忽略,则直接去掉所有的小数点位并四舍五入。若该值为负数,则表示从小数点开始左边的位数,相应整数数字用 0 填充,小数被去掉。
注:与TRUNC函数不同的是,ROUND会对处理的数字进行四舍五入。
SELECT ROUND(412.513) FROM dual; --413 SELECT ROUND(412.513, -2) FROM dual; --400 SELECT ROUND(412.513, 2) FROM dual; --412.51
2、MOD (m, n)
MOD 返回m除以n的余数,如果n为0,返回m。
select MOD(23,8), MOD(24,8), MOD(25, 0) from dual; --7, 0, 25
3、TRUNC
格式一:TRUNC(date, [fmt]),日期处理函数,date为必要参数,fmt为可选参数,若为空则表示指定日期的0点。
SELECT TRUNC(sysdate) FROM dual; --2022-01-22, 得到当前时间, 当前日期0点 SELECT TRUNC(sysdate, 'mm') FROM dual; --2022-01-01, 得到当前月份的第一天 SELECT TRUNC(sysdate, 'hh') FROM dual; --2022-01-22 19:00:00, 得到当前时间, 精确到小时
格式二:TRUNC(number, [decimals]),数值处理函数,number为必要参数,decimals为可选参数,若为空则默认截掉小数点后面的值,为正数时表示保存小数点后面几位,为负数时表示小数点左侧几位替换为0,小数点右侧全舍弃。和ROUND函数很像,二者的区别在于TRUNC不会对处理结果进行四舍五入。
SELECT TRUNC(123.567, 2) FROM dual; --123.56 SELECT TRUNC(123.567, -2) FROM dual; --100 SELECT TRUNC(123.567) FROM dual; --123
三、单行函数—日期函数1
1、MONTHS_BETWEEN(m, n)),m与n都为date类型,计算m与n之间相差的月份数,有小数。
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2000-01-15', 'YYYY.MM.DD')) FROM dual; --264.252886798088410991636798088410991637
2、ADD_MONTHS(date, d),date与d都是必要参数,对date加上d个月,得到新的时间,d可以为负数。
SELECT SYSDATE FROM dual; --2022-01-22 20:14:47 SELECT ADD_MONTHS(SYSDATE, 1) FROM dual; --2022-02-22 20:14:47 SELECT ADD_MONTHS(SYSDATE, -1) FROM dual; --2021-12-22 20:14:47
3、LAST_DAY(date),返回指定日期该月份的最后一天。
SELECT SYSDATE FROM dual; --2022-01-22 20:22:29 SELECT LAST_DAY(SYSDATE) FROM dual; --2022-01-31 20:22:29
4、NEXT_DAY(date, d),返回指定日期的下个星期几,d为必须参数,d用1~7替代,1表示星期日,2代表星期一。。。
SELECT NEXT_DAY(SYSDATE, 2) FROM dual; --2022-01-24 20:26:25, 返回指定日期的下个周一
四、单行函数—转换函数
1、TO_CHAR (str, fmt),对字符串str按照fmt的格式进行转换。
SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual; --2022 SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual; --2022-01-22 SELECT TO_CHAR('8000.00','L999,999,999') FROM dual; --¥8,000 SELECT TO_CHAR(SYSDATE,'D') FROM dual; --7, 返回指定日期是星期几
2、TO_DATE (str, fmt),将str转换为fmt的格式
SELECT TO_DATE('20090210', 'yyyyMMdd') FROM dual; --2009-02-10
3、TO_NUMBER,可以将字符串转为number类型,进行加减乘除运算
SELECT TO_NUMBER('13') + TO_NUMBER('14') FROM dual; --27
五,单行函数—通用函数
1、NVL(m, n),如果 m为空则返回 n——【NVL的参数类型必须要一致】
SELECT NVL('1', 0) FROM dual; --1 SELECT NVL('', 0) FROM dual; --0
2、NULLIF(expr1, expr2),如果 expr1=expr2,则返回null,若不相等则返回 expr1。第一个参数不可指定为空。对于非数字类型参数,数据类型必须一致。
SELECT NULLIF('1', '1') FROM dual; --null SELECT NULLIF('1', '0') FROM dual; --1 SELECT NULLIF(1 , '1') FROM dual; --[42000][932] ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR
3、DECODE(条件, 值1, 返回值1, 值2, 返回值2, ...值n, 返回值n, 缺省值)
该函数的含义如:IF(条件 = 值1)THEN RETURN 返回值1 ELSE IF(条件 = 值2)THEN RETURN 返回值2…ELSE RETURN 缺省值 END IF;
SELECT A00, A0101, A0104 FROM A01; SELECT A00, A0101, DECODE(A0104, 1, '男', 2, '女', '未知') FROM A01;
4、NVL2(expr1, expr2, expr3),expr1不为null,返回expr2,否则返回 expr3,如果 expr2与 expr3 类型不同的话,expr3会转化为expr2的类型。
SELECT NVL2('1', '2', 3) FROM dual; --2
SELECT NVL2('', '2', 3) FROM dual; --3
SELECT DUMP(NVL2('', '2', 3)) FROM dual; --Typ=1 Len=1: 51
5、DUMP() 函数可以查看任意值在oracle数据库中的类型,具体类型用Type=t,代码表示。类型与代码值对应关系官方文档。
COALESCE (expr1, expr2, expr3...),返回第一个不会空的参数,所有参数列表的数据类型必须一致 SELECT COALESCE('', '2', '3') FROM dual; --2 SELECT COALESCE('', '2', 3) FROM dual; --[42000][932] ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 NUMBER
6、CASE WHEN,分支判断函数,与DECODE函数类似,CASE WHEN 更适用于需要做特殊判断处理的条件中
SELECT A00, A0101, CASE A0104 WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '未知' END FROM A01; --62CB8048-97B9-466E-946E-23E2B86180EA, 吴毓萍, 女
注:所有的函数都可嵌套使用。
六、单行函数练习题
1、找出每个月倒数第三天受雇的员工(如:2021-5-29),hiredate为受雇日期字段
SELECT * FROM EMP WHERE LAST_DAY(HIREDATE)-2 = HIREDATE;
2、找出 25 年前雇的员工
SELECT * FROM EMP WHERE HIREDATE<=ADD_MONTHS(SYSDATE, -25*12); --找出受雇日期早于等于当前时间减25年
3、所有员工名字前加上 Dear ,并且名字首字母大写
SELECT 'DEAR ' || INITCAP(ENAME) FROM EMP;
4、找出姓名为 5 个字母的员工
SELECT * FROM EMP WHERE LENGTH(ENAME) = 5;
5、找出姓名中不带 R 这个字母的员工
SELECT * FROM EMP WHERE ENAME NOT LIKE '%R%';
6、显示所有员工的姓名的第一个字
SELECT SUBSTR(ENAME,0,1) FROM EMP;
7、显示所有员工,按名字降序排列,若相同,则按工资升序排序
SELECT * FROM EMP ORDER BY ENAME desc, SALARY;
8、假设一个月为 30 天,找出所有员工的日薪,不计小数
SELECT ENAME, TRUNC(SALARY/30) FROM EMP;
9、找到 2 月份受雇的员工
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'FMMM') = '2';
10、列出员工加入公司的月数(四舍五入)
SELECT ENAME, ROUND(MONTHS_BETWEEN(SYSDATE, HIREDATE)) FROM EMP;
七、分组函数
1、COUNT,求数据量函数,如果数据库表的没有数据,count(*)返回的不是 null,而是 0
SELECT COUNT(*) FROM AZ09; --0
2、Avg, max, min, sum,求平均值、最大最小、总和。注:分组函数会省略列中的空值,可使用NVL()函数强制分组函数处理空值。
SELECT AVG(NVL(COMM, 0)) FROM EMP;
3、GROUP BY 子句,出现在SELECT列表中的字段或者出现在ORDER BY后面的字段,如果不是包含在分组函数中,那么该字段必须同时在GROUP BY子句中出现。
包含在 GROUP BY子句中的字段则不必须出现在 SELECT列表中,可使用 WHERE子句限定查询条件,可使用ORDER BY子句指定排序方式。
如果没有GROUP BY子句,SELECT列表中不允许出现字段(单行函数)与分组函数混用的情况。
SELECT A0101, ZDYXA01013 FROM A01; --合法 SELECT AVG(ZDYXA01013) FROM A01; --合法 SELECT A0101, INITCAP(A00), AVG(ZDYXA01013) FROM A01; --[42000][937] ORA-00937: 不是单组分组函数
同时也不允许在WHERE子句中使用分组函数。
SELECT A0101, ZDYXA01013 FROM A01 WHERE AVG(ZDYXA01013)>1; --[42000][934] ORA-00934: 此处不允许使用分组函数
4、Having 子句,分组函数中对结果集进行进一步筛选
SELECT A0117 FROM A01 WHERE A0107 < TO_DATE('2000-01-01', 'yyyy-mm-dd') GROUP BY A0117 HAVING A0117='01' ORDER BY A0117; --[42000][979] ORA-00979: 不是 GROUP BY 表达式 SELECT A0117 FROM A01 WHERE A0107 < TO_DATE('2000-01-01', 'yyyy-mm-dd') GROUP BY A0104 HAVING A0104='01' ORDER BY A0101;
5、分组函数可嵌套使用
SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;
八、关于ALL,IN,ANY的函数区别
1、in:用于匹配多个定值中的一个
select * from emp where deptno in (10,20);
2、any:表示子查询中有一个值知足条件便可。any和
>any(单列多行子查询)大于最小值的均可以。
例子:查询比10号部门某个员工工资高的员工信息
select * from emp where sal >any( select sal from emp where deptno = 10);
3、ALL:表示子查询中的全部值都知足条件才能够。
>all(单列多行子查询)大于最大值的均可以(类比any比较学习)
例子:查询比20号部门全部员工工资高的员工信息
select * from emp where sal > all ( select sal from emp where deptno = 20);
4、some:用法和any同样:都是表中其中的一个知足条件便可。
九、日期函数2--last_day()函数和Next_day()
1、last_day 函数
用法:返回指定日期所属月份的最后一天。
语法:last_day(sysdate) 它只有一个日期参数;
例1:判断该日期是否是所属月份的最后一天,即月末。
if(last_day('2020-02-03')='2020-02-03','Y','N')
注:判断该日期是否是所属月份的最后一天,即月末。
例2:找出每个月倒数第三天入职的员工
SELECT * FROM EMP WHERE last_day(hiredate)-2=hiredate;
注:last_day(hiredate) 得到的是每个员工入职当有的最后一天,-2表示倒数第三天,如果与员工入职时间相等,说明这个员工是当月倒数第三天入职的。
例3:获取当前日期、当前日期所在月的最后一天、距离月末还剩多少天
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL;
2、next_day 函数
定义
NEXT_DAY(date,char) date参数为日期型, char:为1~7或Monday/Mon~Sunday/
格式:NEXT_DAY(DATE,WEEKDAY) 即 NEXT_DAY(日期,星期几)
用法:查询出指定时间的下一个星期几(由char指定)所在的日期, char也可用1~7替代,1表示星期日,2代表星期一。 char 还可以是星期一、星期二......星期日。即 monday,thusday.....(看是什么字符集的)。
例1:next(sysdate,'mon'); 查询出下一个星期一是哪一日
例2:next_day(to_date('2020.06.09','yyyy.mm.dd'),'friday') 返回 2020年06月12日
例3:next_day(to_date('2020.06.09','yyyy.mm.dd'),'Mon') 返回 2020年06月15日
注:2020年06月09日是星期二,第二个参数是星期五,是三天后。
第二个例子日期是星期二,第二个参数是星期一,只能返回下一个星期的星期一对应的日期。
例4:if(date_add(next_day('2020-02-03','mo'),-1)='2020-02-03', 'Y', 'N'),
注:这个可以判断出该日期是否是星期日;
3、ceil(n)
用法:ceil(n);
功能:返回大于或者等于指定表达式的最小整数
SQL> select ceil(12.349) from dual;
CEIL(12.349)
------------
13
4、add_months(x,y)或者add_months(times,months)函数:
用途:这个函数用于计算在时间x之上机上Y个月后的时间值,要是Y的值为负数的话就是在
这个时间点之间的时间值(这个时间-Y个月)。
查询出半年前的时间:
select add_months(sysdate,-6) from dual;
查询出半年后的时间
select add_months(sysdate,6) from dual;
十、日期函数3
https://blog.csdn.net/qq_33459369/article/details/80305175
current_date、current_timestamp、localtimestamp、sessiontimezone、dbtimezone,systimestamp函数
(1)current_timestamp
以 timestamp with time zone 数据类型返回当前会话时区所对应的日期时间。
SQL> select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 22-JUL-22 11.19.47.282671 AM +08:00
(2)systimestamp
SQL> select current_timestamp from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 22-JUL-22 11.20.40.282671 AM +08:00
(3)localtimestamp
返回当前会话时区的日期时间
SQL> select localtimestamp from dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 22-JUL-22 11.20.42.721053 AM
(4)current_date
current_date:9i新增函数,返回当前会话时区所对应的日期时间(date型)
SQL> select current_date from dual; CURRENT_DATE -------------------- 22-JUL-2022 11:16:19
(5)sessiontimezone
返回会话时区(字符型)
SQL> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------------------------------------- +08:00
(6)dbtimezone
返回数据库所在时区
SQL> select dbtimezone from dual; DBTIME ------ +08:00