数据库基本功之SQL的基本函数

1. 单行函数与多行函数

1.1 单行函数

• 指单行数据输入,返回一个值的函数. 所以查询一个表时,对选择的每一行数据都返回一个结果.

[oracle@oracle-db-19c ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 7 07:59:44 2023
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL> show con_name;CON_NAME
------------------------------
CDB$ROOT
SQL> show user;
USER is "SYS"
SQL> alter session set container=PDB1;Session altered.SQL> conn scott/tiger@PDB1;
Connected.
SQL> show user;
USER is "SCOTT"
SQL> set pagesize 200 linesize 200
SQL> select empno,lower(ename) from emp;EMPNO LOWER(ENAME)
---------- ------------------------------7369 smith7499 allen7521 ward7566 jones7654 martin7698 blake7782 clark7788 scott7844 turner7876 adams7900 james7902 ford7934 miller13 rows selected.SQL> 

 

• 单行函数是用于处理数据项的,它接受一个或多个参数,并为查询返回的每个行返回一个值,参数可以是下列对象:
  • 用户提供的常数
  • 变量值
  • 列名
  • 表达式
• 单上函数具有以下特性:
  • 作用于查询返回的每个行
  • 为每行返回一个结果
  • 可能会返回与引用数据类型不同的数据值
  • 可能需要一个或多个参数
  • 可能用在select、where和order by字句中

1.2 多行函数

指多行数据输入,返回一个值的函数.所以对表的多组进行操作,并且每组返回一个结果.(典型的是聚合函数)

SQL> 
SQL> show user;
USER is "SCOTT"
SQL> select sum(sal) from emp;SUM(SAL)
----------24025SQL> 

2. 单行函数的几种类型

2.1 字符函数

2.1.1 lower & upper & initcap

-- upper('sql course')			-- sql course 返回大写
-- upper('sql course')			-- sql course 返回大写
-- initcap('sql course')		-- sql course 返回只有首字母大写SQL> select ename,lower(ename),upper(ename),initcap(ename) from emp where ename='SMITH';ENAME                          LOWER(ENAME)                   UPPER(ENAME)                   INITCAP(ENAME)
------------------------------ ------------------------------ ------------------------------ ------------------------------
SMITH                          smith                          SMITH                          SmithSQL> 

2.1.2 concat

-- concat('good','string')		-- good string 拼接,只能拼接2个字符串SQL> show user
USER is "SCOTT"
SQL> show con_name;CON_NAME
------------------------------
PDB1
SQL> select concat(ename,job) from emp where ename='FORD';CONCAT(ENAME,JOB)
---------------------------------------------------------
FORDANALYSTSQL> 

2.1.3substr

-- substr('String',1,3)			-- Str 从第1位开始截取3位数
SQL> select substr(ename,1,3) from emp where ename='SCOTT';SUBSTR(ENAME,1,3)
------------------------------------
SCOSQL> 

2.1.4 instr

-- instr('t#i#m#r#a#n#','#',5)	-- 从第5位起始找#字符在那个绝对位置
SQL> select instr('K#L#A#U#S#','#',5) from dual;INSTR('K#L#A#U#S#','#',5)
-------------------------6SQL>

2.1.5 length

-- length('String')				-- 长度
SQL> 
SQL> show user;
USER is "SCOTT"
SQL> show con_name;CON_NAME
------------------------------
PDB1
SQL> select length(ename) from emp where ename='SCOTT';LENGTH(ENAME)
-------------5SQL> 

2.1.6 lpad & rpad

-- lpad('first',10,'$')		-- 左填充
-- rpad (676768,10,'*')		-- 右填充SQL> select rpad(ename,10,'$') from emp where rownum < 6;RPAD(ENAME,10,'$')
------------------------------------------------------------------------------------------------------------------------
SMITH$$$$$
ALLEN$$$$$
WARD$$$$$$
JONES$$$$$
MARTIN$$$$SQL> select lpad(ename,10,'*') from emp where rownum < 6;LPAD(ENAME,10,'*')
------------------------------------------------------------------------------------------------------------------------
*****SMITH
*****ALLEN
******WARD
*****JONES
****MARTINSQL> 

2.1.7 replace

-- replace ('JACK and JUE','J','BL')	-- 替换字符
SQL> select replace('Gkkd Jkb','k','o') from dual;REPLACE('GKKDJKB','K','O
------------------------
Good JobSQL> 

2.1.8 trim

-- trim ('a' from 'aaklausaa')			-- 去除前后指定字符
SQL> 
SQL> select trim ('a' from 'aaklausaa') from dual;TRIM('A'FROM'AA
---------------
klausSQL> 

2.2 数值函数

2.2.1 round

对指定的值做四舍五入
round (p, s)
s为正数时, 表示小数点后要保留的位数,
s为0或不填写,表示取整四舍五入.
s为负数时,表示小数点前要保留的位数.

SQL> 
SQL> select round(14535.9856) from dual;ROUND(14535.9856)
-----------------14536SQL> select round(14535.9856,2) from dual;ROUND(14535.9856,2)
-------------------14535.99SQL> select round(14535.9856,-2) from dual;ROUND(14535.9856,-2)
--------------------14500SQL> select round(14555.9856,-2) from dual;ROUND(14555.9856,-2)
--------------------14600SQL> 

2.2.2 trunc

对指定的值取整
trunc (p, s)
s为正数时, 表示小数点后要保留的位数,
s为0或不填写,表示取整.
s为负数时,表示小数点前要保留的位数.SQL> select trunc(14535.9856) from dual;TRUNC(14535.9856)
-----------------14535SQL> select trunc(14535.9856,2) from dual;TRUNC(14535.9856,2)
-------------------14535.98SQL> select trunc(14535.9856,-2) from dual;TRUNC(14535.9856,-2)
--------------------14500SQL> 

2.2.3 mod

返回除法后的余数

SQL> 
SQL> select mod(100,12) from dual;MOD(100,12)
-----------4SQL> 

 

2.3 日期函数

2.3.1 格式和语言

select * from v$nls_parameters;	-- 查询日期语言	
select SYSTIMESTAMP from dual;	-- 查看日期格式
-- 设置日期语言
alter session set NLS_DATE_LANGUAGE='American';				-- 只对当前会话起作用
alter system set NLS_DATE_LANGUAGE='American';				-- 对系统设置生效
alter session set NLS_DATE_LANGUAGE='SIMPLIFIED CHINESE';
-- 或者这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
-- 缺省格式:DD-MON-RR.
-- 可以表示日期范围:(公元前)4712 至(公元)9999

2.3.2 日期转字符

# (to_date,to_char)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;	-- 日期转化为字符串	
select to_char(sysdate,'yyyy') as nowYear from dual;					-- 获取时间的年	
select to_char(sysdate,'mm') as nowMonth from dual;						-- 获取时间的月
select to_char(sysdate,'dd') as nowDay from dual;						-- 获取时间的日  
select to_char(sysdate,'hh24') as nowHour from dual;					-- 获取时间的时  
select to_char(sysdate,'mi') as nowMinute from dual;					-- 获取时间的分
select to_char(sysdate,'ss') as nowSecond from dual;					-- 获取时间的秒

2.3.3 字符和时间互换

-- 显示Two Hundred Twenty-Two
SQL> 
SQL> select to_date('2023-03-07 09:32:46','yyyy-mm-dd hh24:mi:ss') from dual;TO_DATE('2023-0')
---------------
07-MAR-23SQL> select to_char(to_date(222,'J'),'Jsp') from dual;TO_CHAR(TO_DATE(222,'J'),'JSP')
------------------------------------------------------------------
Two Hundred Twenty-TwoSQL> 

2.3.4 天数差floor

SQL> select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;FLOOR(SYSDATE-TO_DATE('20020405','YYYYMMDD'))
---------------------------------------------7641SQL> 

2.3.5 Month_between

因为日期在oracle里是以数字形式存储的,所以可对它进行加减运算,计算是以天为单位

-- 1、数据类型转换
SQL> select months_between ('1994-04-01','1992-04-01') mm from dual;
select months_between ('1994-04-01','1992-04-01') mm from dual
ERROR at line 1:
ORA-01861: literal does not match format string
-- 报错：字符串格式不匹配
-- 原因:
-- 如果直接按照字符串方式,或者直接使用'2013-2-26 11:07:25',没有指定日期格式,就会报错
to_date('2013-2-26 11:07:25' , 'yyyy-mm-dd hh24:mi:ss')
-- 正确如下：
select months_between (to_date('1994-04-01','yyyy-mm-dd'),to_date('1992-04-01','yyyy-mm-dd')) mm from dual;
-- 参考第三章：[数据类型转换]部分-- 2、查找emp表中参加工作时间>30年的员工
SQL> select * from emp where months_between (sysdate, hiredate)/12>30;

知识点:
很容易认为单行函数返回的数据类型与函数类型一致,对于数值函数类型而言的确如此,但字符和日期函数可以返回任何数据类型的值.
比如 instr 函数是字符型的, months_between函数是日期型的,但它们返回的都是数值. 

2.3.6 Next_day

-- NEXT_DAY的第2个参数可以是数字1-7, 分别表示周日--周六, 比如要取下一个星期六, 则应该是:SQL> select next_day(sysdate,7) from dual;NEXT_DAY(SYSDAT
---------------
11-MAR-23SQL> select last_day (to_date('1999-06','yyyy-mm')) ld from dual;LD
---------------
30-JUN-99SQL> 

2.3.7 extract

-- extract()找出日期或间隔值的字段值
-- 获得小时数
SQL> SELECT EXTRACT(HOUR FROM TIMESTAMP '2020-09-16 2:39:40') from dual;EXTRACT(HOURFROMTIMESTAMP'2020-09-162:39:40')
---------------------------------------------2
--获取分钟数SQL> SELECT EXTRACT(minute FROM TIMESTAMP '2020-09-16 2:39:40') from dual;EXTRACT(MINUTEFROMTIMESTAMP'2020-09-162:39:40')
-----------------------------------------------39
---获取天数
SQL> SELECT EXTRACT(day FROM TIMESTAMP '2020-09-16 2:39:40') from dual;EXTRACT(DAYFROMTIMESTAMP'2020-09-162:39:40')
--------------------------------------------16
--- 获取月份数
SQL> SELECT EXTRACT(month FROM TIMESTAMP '2020-09-16 2:39:40') from dual;EXTRACT(MONTHFROMTIMESTAMP'2020-09-162:39:40')
----------------------------------------------9
--- 获取年份数
SQL> SELECT EXTRACT(year FROM TIMESTAMP '2020-09-16 2:39:40') from dual;EXTRACT(YEARFROMTIMESTAMP'2020-09-162:39:40')
---------------------------------------------2020SQL> select sysdate,to_char(sysdate,'hh24') from dual;SYSDATE         TO_CHA
--------------- ------
07-MAR-23       09SQL>

2.3.8 add_month

SQL> 
SQL> select add_months(to_date('2023-03-01','yyyy-mm-dd'),4) am from dual;AM
---------------
01-JUL-23SQL> select add_months(sysdate,-6) from dual;ADD_MONTHS(SYSD
---------------
07-SEP-22SQL> 

2.3.9 日期函数的运用

2.3.9.1 查看月份天数不定的方法

​
SQL> select to_char(add_months(last_day(sysdate) +1, -1), 'yyyymmdd') s,to_char(last_day(sysdate),'yyyymmdd') e from dual;S                        E
------------------------ ------------------------
20230301                 20230331SQL> ​

2.3.9.2 今年的天数

SQL> select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') days from dual;DAYS
----------365SQL> --- 今年2月份的最后一天
SQL> select to_char(last_day(to_date('02'||to_char(sysdate,'yyyy'),'mmyyyy')),'dd') days from dual;DAYS
------
28SQL> 

2.3.9.3 某天是星期几

SQL> select to_char(to_date('2023-03-31','yyyy-mm-dd'),'day') from dual;TO_CHAR(TO_DATE('2023-03-31
---------------------------
fridaySQL> select to_char(to_date('2023-03-31','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE =''simplified chinese''') from dual;TO_CHAR(TO_DATE('2023-03-31
---------------------------
星期五SQL>

2.3.9.4 去除指定周天的天数

-- 查找2002-02-28至2002-02-01间除星期一和七的天数SQL> select count(*)2  from(3  select rownum-1 rnum 4  from all_objects5  where rownum <= to_date('2023-03-31','yyyy-mm-dd')-to_date('2023-03-01','yyyy-mm-dd')+1)     6  where to_char( to_date('2023-03-01','yyyy-mm-dd')+rnum-1,'D')not in ('1','7');COUNT(*)
----------23SQL> 

2.3.9.5 ROUND(p,s),TRUNC(p,s)在日期中的应用

-- 如何舍入要看具体情况,s是MONTH按30天计,应该是15舍16入,s是YEAR则按6舍7入计算.SQL> SELECT empno, hiredate,2  round(hiredate,'MONTH') AS round,3  trunc(hiredate,'MONTH') AS trunc4  FROM emp WHERE empno=7788;EMPNO HIREDATE        ROUND           TRUNC
---------- --------------- --------------- ---------------7788 24-JAN-87       01-FEB-87       01-JAN-87
SQL>
SQL> SELECT empno, hiredate,2  round(hiredate,'YEAR') AS round,3  trunc(hiredate,'YEAR') AS trunc4  FROM emp WHERE empno=7369;EMPNO HIREDATE        ROUND           TRUNC
---------- --------------- --------------- ---------------7369 17-DEC-80       01-JAN-81       01-JAN-80SQL> 

2.4 几个有用的函数

实现sql语句中的条件判断语句,具有类似高级语言中的if语句的功能. decode函数源自oracle, case表达式源自sql标准, 实现功能类似

2.4.1 decode函数

-- 第一种形式
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
该函数的含义如下:
IF 条件=值1 THENRETURN(翻译值1)
ELSIF 条件=值2 THENRETURN(翻译值2)......
ELSIF 条件=值n THENRETURN(翻译值n)
ELSERETURN(缺省值)
END IF
-- 【eg.】----
SELECT empno, job, sal,DECODE (job,'ANALYST', SAL*1.1,'CLERK', SAL*1.15,'MANAGER', SAL*1.20, SAL) newsal
FROM emp;
-------------------------------------
-- 第二种形式
decode(字段或字段的运算,值1,值2,值3)
这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3
当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多

2.4.2 case表达式

------- -- 格式1---------
CASE WHEN 条件表达式1 THEN语句段1WHEN 条件表达式2 THEN语句段2......WHEN 条件表达式n THEN 语句段n[ELSE 语句段]
END;
-----------------------------
-- 【eg.】
select ename, job, sal
, case jobwhen 'ANALYST' then SAL*1.1when 'CLERK'   then SAL*1.15when 'MANAGER' then SAL*1.20else sal end newsal
from emp;
-------- -- 格式2---------
CASE 条件表达式WHEN 条件表达式结果1 THEN 语句段1WHEN 条件表达式结果2 THEN语句段2......WHEN 条件表达式结果n THEN语句段n[ELSE 条件表达式结果]
END;
-----------------------------
-- 【eg.】
select ename, job, sal,case when job='ANALYST' then SAL*1.1when job='CLERK'   then SAL*1.15when job='MANAGER' then SAL*1.20else sal end newsal
from emp;
-- case第二种语法比第一种语法增加了搜索功能.形式上第一种when后跟定值,而第二种还可以使用比较符.
-- eg.
select ename,sal,case when sal>=3000 then 'H'when sal>=2000 then 'M'else 'L' end Job_Level
from emp;
----------
select ename,sal,case when sal>=3000 then 'H'when sal>=2000 then 'M'else 'L' end
from emp;

2.4.3 DISTINCT

-- distinct貌似多行函数,严格来说它不是函数.
-- DISTINCT一定要放在开头,否则报错
-- 语法
SELECT DISTINCT column_name,column_name FROM table_name;
---------------------------------------------------------------------------------------
SQL> 
SQL> -- 消除表行重复值
SQL> select distinct job from emp;JOB
---------------------------
CLERK
SALESMAN
ANALYST
MANAGERSQL> -- 重复值是后面的字段组合起来考虑的
SQL> select distinct job,deptno from emp;JOB                             DEPTNO
--------------------------- ----------
CLERK                               20
MANAGER                             20
MANAGER                             30
MANAGER                             10
SALESMAN                            30
ANALYST                             20
CLERK                               30
CLERK                               108 rows selected.SQL> 

2.4.4 CHAR()和ASCII()函数

-- chr()函数将ASCII码转换为字符		:ASCII码 –》 字符
-- ascii()函数将字符转换为ASCII码	:字符 –》 ASCII码
-- 在oracle中chr()函数和ascii()是一对反函数.
SQL> select ASCII('K') FROM dual;	-- 大写K
ASCII('K')
------------
75
SQL> select ASCII('k') FROM dual;	-- 小写k
ASCII('K')
------------
107
SQL> select chr(75) from dual;
C
-
K

2.4.5 sys_context获取环境上下文的函数（很有用）

-- scott远程登录(查询IP时)
select sys_context('USERENV','AUTHENTICATION_TYPE') from dual;	-- DATABASE 用户的认证类型
select sys_context('USERENV','AUTHENTICATION_DATA') from dual;	-- null 未知
select sys_context('USERENV','BG_JOB_ID') from dual;			-- null 当前指定id的会话是否为oracle后台程序建立,不是则返回null
select sys_context('USERENV','CLIENT_INFO') from dual;			-- null 通过dbms_application_info包可以存储高达64字节的用户会话信息
select sys_context('USERENV','CURRENT_SCHEMA') from dual;		-- DICPTEST 默认的schema将被当做当前的schema.-- 当在当前会话中使用ALTER SESSION SET CURRENT_SCHEMA语句的时候,它的查询返回值将被改变
select sys_context('USERENV','CURRENT_SCHEMAID') from dual;		-- 当前schema的id
select sys_context('USERENV','CURRENT_USER') from dual;			-- DICPTEST 当前的登陆用户
select REPLACE(SUBSTR(sys_context('USERENV','HOST'),1,30),'/',':') from dual;	-- 当前会话主机操作系统名
select sys_context('USERENV','CURRENT_USERID') from dual;		-- 当前登陆的用户的id
select sys_context('USERENV','DB_DOMAIN') from dual;			-- null 为数据库的域指定初始化参数
select sys_context('USERENV','DB_NAME') from dual;				-- iomtest 数据库实例名
select sys_context('USERENV','ENTRYID') from dual;				-- null 可用的审计标示符.不能再分布式sql语句中使用此选项-- 使用USERENV关键字必须置AUDIT_TRAIL的初始化参数为真.
select sys_context('USERENV','EXTERNAL_NAME') from dual;		-- null 数据库用户的扩展名
select sys_context('USERENV','FG_JOB_ID') from dual;			-- 0 返回作业id当此会话是客户端进程创建.否则,返回null
select sys_context('USERENV','INSTANCE') from dual;				-- 1 当前数据库实例的标示id
select sys_context('USERENV','ISDBA') from dual;				-- FALSE 当前用户是否是以dba身份登录
select sys_context('USERENV','LANG') from dual;					-- ZHS iso对LANGUAGE’的简称,查询的参数比”LANGUAGE”短
select sys_context('USERENV','LANGUAGE') from dual;				-- SIMPLIFIED CHINESE_CHINA.ZHS16GBK 结果为当前数据库使用的存储语言,跟上面查询意义一样
select sys_context('USERENV','NETWORK_PROTOCOL') from dual;		-- tcp 用于通信的网络协议
select sys_context('USERENV','NLS_CALENDAR') from dual;			-- GREGORIAN 当前会话使用的,格林尼治时间
select sys_context('USERENV','NLS_CURRENCY') from dual;			-- ￥本地化的货币符,如人民币为￥,美元符为$
select sys_context('USERENV','NLS_DATE_FORMAT') from dual;		-- DD-MON-RR 当前使用的日期格式,一般中国为dd-mon-rr
select sys_context('USERENV','NLS_DATE_LANGUAGE') from dual;	-- SIMPLIFIED CHINESE 表示日期的语言,如中文简体SIMPLIFIED CHINESE
select sys_context('USERENV','NLS_TERRITORY') from dual;		-- CHINA 数据库服务器所在区域,如中国CHINA
select sys_context('USERENV','OS_USER') from dual;				-- 操作系统的用户名
select sys_context('USERENV','PROXY_USER') from dual;			-- null 是否使用代理用户.否返回null
select sys_context('USERENV','PROXY_USERID') from dual;			-- null 代理用户id
select sys_context('USERENV','SESSION_USER') from dual;			-- DICPTEST 当前认证的数据库用户名
select sys_context('USERENV','SESSION_USERID') from dual;		-- 当前认证的数据库用户名id
select sys_context('USERENV','SESSIONID') from dual;			-- 当前会话id
select sys_context('USERENV','TERMINAL') from dual;				-- 操作系统用户组
select sys_context('USERENV','IP_ADDRESS') from dual;			-- 当前会话主机ip
select sys_context('USERENV','HOST') from dual;					-- 当前会话主机操作系统名

-- 在sqldeveloper中 一键查询
select 
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_formAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual;