4、Oracle 中的单行函数
最近项目要用到Oracle，奈何之前没有使用过，所以在B站上面找了一个学习视频，用于记录学习过程以及自己的思考。
视频链接：
【尚硅谷】Oracle数据库全套教程，oracle从安装到实战应用
如果有侵权，请联系删除，谢谢。
本文主要讲解以下几点：
-
SQL中不同类型的函数
-
在 SELECT 语句中使用字符，数字，日期和转换函数
-
使用条件表达式
1、SQL函数
DUAL 是一个‘伪表’，可以用来测试函数和表达式
1.1、两种 SQL 函数
- 单行函数
- 多行函数
1.1.1、单行函数
单行函数:
- 操作数据对象
- 接受参数返回一个结果
只对一行进行变换每行返回一个结果- 可以转换数据类型
- 可以嵌套
- 参数可以是一列或一个值
function_name [(arg1, arg2,...)]
1.1.2、单行函数有哪些
- 字符函数
- 数值函数
- 日期函数
- 转换函数
- 通用函数
2、字符函数
1、大小写控制函数
- LOWER：所有字符转为小写
- UPPER：所有字符换为大写
- INITCAP：字符首字母大写，空格分割、下划线等分割符分开的字符都算
SELECT LOWER('JUST TEST'), UPPER('just test TT'), INITCAP('this is Test, wwww_y') FROM dual;
执行结果：
LOWER('JUSTTEST')|UPPER('JUSTTESTTT')|INITCAP('THISISTEST,WWWW_Y')|
-----------------+-------------------+----------------------------+
just test |JUST TEST TT |This Is Test, Wwww_Y |
2、字符控制函数
- CONCAT：连接不同的字符串。注意不能指定连接符。
- SUBSTR：截取字符。SUBSTR('string', start_index, length)，从start_index位置开始，截取长度为length的字符。注意字符其实索引为1。
- LENGTH：计算给定字符串长度
- INSTR：计算给定字符首次出现在字符串中的索引位置。INSTR('string', '给定字符')
- LPAD | RPAD：在长度未达到指定长度时，使用给定字符填充。LPAD(salary,10,'*')：*****24000
- TRIM：截取给定字符串两边指定字符串
- REPLACE：将字符串中指定字符串替换为给定字符串。REPLACE(‘abcd’,’b’,’m’)： amcd
SELECT CONCAT('JUST', 'TEST'), SUBSTR('this is 测试案例', 9, 10), LENGTH('this is 测试案例'), INSTR('你好，未来', '未来') FROM dual;
执行结果：
CONCAT('JUST','TEST')|SUBSTR('THISIS测试案例',9,10)|LENGTH('THISIS测试案例')|INSTR('你好，未来','未来')|
---------------------+-------------------------+--------------------+-------------------+
JUSTTEST |测试案例 | 12| 4|
SELECT LPAD('你好，未来', 10, '='), LPAD('你好，未来', 20, '='), TRIM('H' FROM 'HelloWorldhHHhH'),REPLACE('abcd','bc','你好') FROM dual;
执行结果：
LPAD('你好，未来',10,'=')|LPAD('你好，未来',20,'=')|TRIM('H'FROM'HELLOWORLDHHHHH')|REPLACE('ABCD','BC','你好')|
--------------------+--------------------+------------------------------+-------------------------+
你好，未来 |==========你好，未来 |elloWorldhHHh |a你好d |
3、数字函数
- ROUND: 四舍五入。默认不保留小数点。
- TRUNC: 截断。默认不保留小数点。
- MOD: 求余
1、ROUND 函数
SELECT ROUND(435.45, 2), ROUND(435.45), ROUND(435.45, -2), ROUND(435.45, -1) FROM dual;
执行结果：
ROUND(435.45,2)|ROUND(435.45,2)|ROUND(435.45,-2)|ROUND(435.45,-1)|
---------------+---------------+----------------+----------------+
435.45| 435| 400| 440|
2、TRUNC 函数
SELECT TRUNC(435.45, 2), TRUNC(435.45), TRUNC(435.45, -2), TRUNC(435.45, -1) FROM dual;
执行结果：
TRUNC(435.45,2)|TRUNC(435.45)|TRUNC(435.45,-2)|TRUNC(435.45,-1)|
---------------+-------------+----------------+----------------+
435.45| 435| 400| 430|
3、MOD 函数
SELECT MOD(10, 9) FROM dual;
执行结果：
MOD(10,9)|
---------+
1|
4、日 期
Oracle 中的日期型数据实际含有两个值: 日期和时间。
SELECT last_name, hire_date
FROM employees
WHERE last_name like 'G%';
LAST_NAME|HIRE_DATE |
---------+-----------------------+
Greenberg|1994-08-17 00:00:00.000|
Gee |1999-12-12 00:00:00.000|
Greene |1999-03-19 00:00:00.000|
Grant |1999-05-24 00:00:00.000|
Geoni |2000-02-03 00:00:00.000|
Gates |1998-07-11 00:00:00.000|
Grant |2000-01-13 00:00:00.000|
Gietz |1994-06-07 00:00:00.000|
4.1、函数SYSDATE
返回:
- 日期
- 时间
SELECT SYSDATE FROM dual;
SYSDATE |
-----------------------+
2024-05-18 10:22:09.000|
4.2、日期的数学运算
-
在日期上加上或减去一个数字结果仍为日期。
-
两个日期相减返回日期之间相差的天数。 -
日期不允许做加法运算，无意义
-
可以用数字除24来向日期中加上或减去天数。
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
LAST_NAME|WEEKS |
---------+-----------------------------------------+
King |1313.490419973544973544973544973544973545|
Kochhar | 1799.6332771164021164021164021164021164|
De Haan |1635.490419973544973544973544973544973543|
4.3、日期函数
5、转换函数
数据类型转换分为隐性和显性
5.1、隐式数据类型转换
Oracle 自动完成下列转换：
SELECT SYSDATE + 1, 12 + '12', '12' + '12' FROM dual;
执行结果：
SYSDATE+1 |12+'12'|'12'+'12'|
-----------------------+-------+---------+
2024-05-19 11:12:49.000| 24| 24|
5.2、显式数据类型转换
1、TO_CHAR函数对日期的转换
TO_CHAR(date, 'format_model')
格式:
- 必须包含在
单引号中而且大小写敏感。 - 可以包含任意的
有效的日期格式。 日期之间用逗号隔开。
SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') FROM dual;
执行结果：
TO_CHAR(SYSDATE,'YYYY-MM-DDHH:MI:SS')|
-------------------------------------+
2024-05-18 11:14:52 |
2、日期格式的元素
| 格式 | 示例 |
|---|---|
| YYYY | 2004 |
| YEAR | TWO THOUSAND AND FOUR |
| MM | 02 |
| MONTH | JULY |
| MON | JUL |
| DY | MON |
| DAY | MONDAY |
| DD | 02 |
- 时间格式
- 使用双引号向日期中添加字符
3、TO_CHAR 函数对日期的转换
SELECT last_name,
TO_CHAR(hire_date, 'DD Month YYYY')
AS HIREDATE
FROM employees;
select employee_id,last_name,hire_date
from employees
where to_char(hire_date,'yyyy-mm-dd') = '1987-09-17'
4、TO_DATE 函数对字符的转换
- 使用 TO_DATE :
- 使用 TO_DATE 函数将字符转换为数字:
SELECT TO_DATE('2012年10月29日 08:10:21','yyyy"年"mm"月"dd"日"hh:mi:ss')
From dual;
执行结果：
TO_DATE('2012年10月29日08:10:21','YYYY"年"MM"月"DD"日"HH:MI:SS')|
----------------------------------------------------------+
2012-10-29 08:10:21.000|
5、TO_CHAR函数对数字的转换
TO_CHAR(number, 'format_model')
下面是在TO_CHAR 函数中经常使用的几种格式:
6、TO_CHAR函数对数字的转换
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';
执行结果：
SALARY |
-----------+
$6,000.00|
7、TO_NUMBER 函数对字符的转换
- 使用 TO_NUMBER 函数将字符转换成日期:
TO_NUMBER(char[, 'format_model'])
- 使用 TO_NUMBER :
SELECT TO_NUMBER('￥1,234,567,890.00','L999,999,999,999.99')
from dual
执行结果：
TO_NUMBER('￥1,234,567,890.00','L999,999,999,999.99')|
----------------------------------------------------+
1234567890|
6、通用函数
这些函数适用于任何数据类型，同时也适用于空值：
- NVL (expr1, expr2)
- NVL2 (expr1, expr2, expr3)
- NULLIF (expr1, expr2)
- COALESCE (expr1, expr2, ..., exprn)
6.1、NVL 函数
将空值转换成一个已知的值：
可以使用的数据类型有日期、字符、数字。
函数的一般形式:
- NVL(commission_pct,0)
- NVL(hire_date,'01-JAN-97')
- NVL(job_id,'No Job Yet')
SELECT commission_pct, last_name, nvl(commission_pct, '0') FROM EMPLOYEES e ;
执行结果：
COMMISSION_PCT|LAST_NAME |NVL(COMMISSION_PCT,'0')|
--------------+-----------+-----------------------+
|King | 0|
|Kochhar | 0|
|De Haan | 0|
SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;
6.2、NVL2 函数
NVL2 (expr1, expr2, expr3)
NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
类似于三元表达式：exp1 != null ? exp2 : exp3
SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM employees WHERE department_id IN (50, 80);
6.3、NULLIF 函数
NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1
SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;
6.4、COALESCE 函数
- COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
- 如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
SELECT last_name,
COALESCE(commission_pct, salary, 10) comm
FROM employees
ORDER BY commission_pct;
7、条件表达式
在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用两种方法:
- CASE 表达式
- DECODE 函数
7.1、CASE 表达式
在需要使用 IF-THEN-ELSE 逻辑时:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
-- 查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
7.2、DECODE 函数
在需要使用 IF-THEN-ELSE 逻辑时:
DECODE(col|expression, search1, result1 ,
[, search2, result2,...,]
[, default])
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
使用decode函数的一个例子：
SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM employees
WHERE department_id = 80;
8、嵌套函数
- 单行函数可以嵌套。
嵌套函数的执行顺序是由内到外。
SELECT last_name,
NVL(TO_CHAR(manager_id), 'No Manager')
FROM employees
WHERE manager_id IS NULL;
