SQL学习之函数，基于Oracle下的HR用户（二）

3.6 日期处理
3.6.1 日期的使用
3.6.1.1 SYSDATE 函数
SYSDATE 是一个日期函数，它返回当前数据库服务器的日期和时间。
3.6.1.2 用日期计算
• 从日期加或者减一个数，结果是一个日期值
• 两个日期相减，得到两个日期之间的天数
• 用小时数除以 24，可以加小时到日期上

运算	结果	说明
date + number	日期	加一个天数到日期上
date - number	日期	从一个日期上减一个天数
date - date	天数	一个日期减另外一个日期
date + number/24	日期	加一个小时数到一个日期上

3.6.1.3 用日期做算术运算
3.6.1.3.1 示例

显示所有在部门 90 中的雇员的名字和从业的周数。雇员的总工作时间以周计算。
select last_name, (sysdate-hire_date)/7 from employees where department_id = 90;

3.6.2 日期函数

函数	说明
months_between	两个日期之间的月数
add_months	加日历月到日期
next_day	下个星期几是几号
last_day	指定月的最后一天
round	四舍五入日期
trunc	截断日期

MONTHS_BETWEEN(date1,date2)：计算 date1 和 date2 之间的月数。其结果可以是正的也可以是负的。如果 date1 大于 date2，结果是正的。反之，结果是负的。
date1：日期类型。
date2：日期类型。

ADD_MONTHS(date, n)：添加 n 个日历月到 date。n 的值必须是整数，但可以是负的。date：日期类型。n：整数NEXT_DAY(date, ‘char’)：计算在 date 之后的下一个周(‘char’)的指定天的日期。char 的值可能是一个表示一天的数或者是一个字符串。如果使用数字表示星期，1 是从星期日开始。数字范为：1-7。
date：日期类型。
char：数字或字符串。

LAST_DAY(date)：计算包含 date 的月的最后一天的日期。
date：日期类型。

ROUND(date,‘fmt’)：返回用格式化模式 fmt 四舍五入到指定单位的 date ，如果格式模式 fmt 被忽略，date 被四舍五入到最近的天。
date：日期类型。
fmt：字符串类型。

TRUNC(date, ‘fmt’)：返回用格式化模式 fmt 截断到指定单位的带天的。如果格式模式fmt 被忽略，date 被截断到最近的天。
date：日期类型。
fmt：字符串类型。
3.6.2.1.1 示例一

--查询所有受雇在 15 年 (180 个月) 以内的雇员的 employee_id，hire_date，显示他们
--已被雇用的月，从受雇日期开始加 6 个月的试用期后的日期，受雇日期后的第一个星期五是几号，以及受雇月的最后一天是几号。
select employee_id,hire_date,months_between(sysdate,hire_date),add_months(hire_date,6),next_day(hire_date,' 星期五 '),last_day(hire_date)from employees where months_between(sysdate,hire_date) < 180;

3.6.2.1.2 示例二

查询受雇日期，找出 2002 年开始工作的哪些人。用 ROUND 和 TRUNC
函数显示开始的月份。
select round(hire_date,'month'),trunc(hire_date,'month') from employees where hire_date like '%02';

3.7 数据类型装换
3.7.1 隐式数据类型转换
隐式转换：当源数据的类型和目标数据的类型不同的时候，如果没有转换函数，就会发生隐式转换，也称自动转换。
3.7.1.1 对于直接赋值转换
3.7.1.2 对于表达式赋值
3.7.1.3 隐式转换的问题
3.7.1.3.1 性能影响：
隐式转换的最大问题就是转换时会导致索引的无效，进而可能导致全表扫描。
当表的数据量很大的时候，产生会很大的性能问题。比如说，VARCHAR2 和NVARCHAR2 隐式数据类型转换导致的性能问题。
3.7.1.3.2 不便于阅读：
由于隐式转换使得数据库编程人员和 DBA 难以了解到究竟发生了怎样的类型转换，而且如果代码很多很长的话要查出错误就需要费很大的劲。
3.7.2 显示数据类型转换
通过数据库中的转换函数完成数据类型的转换。
3.7.3 转换函数
TO_CHAR(arg1,’fmt’)：将一个日期或者数字转换为字符类型。带格式化样式 fmt。
arg1：数字或者日期类型。需要转换的数据。fmt：转换格式。
3.7.3.1 to_char 日期转换
日期格式模板的元素

格式	说明
YYYY	数字全写年
YEAR	年的拼写
MM	月的两个数字值
MONTH	月的全名
MON	月的缩写
DY	周缩写
DAY	周全名
DD	月的数字天

时间格式模板元素

元素	说明
AM或PM	正午指示
A.M.或P.M.	带点的正午指示
HH 或HH2 或HH24	天的小时
MI	分
ss	秒
sssss	午夜之后的秒

其它格式

元素	说明
/.,	在结果中使用标点符号
“of the”	在结果中使用引入文串

指定后缀来影响数字显示

月数	说明
TH	序数
SP	拼写出数字
SPTH or THSP	拼写出序数

3.7.3.1.1 示例

--显示所有雇员的名字和受雇日期，受雇日期以 2007 年 8 月 10 日 12:00:00 AM 显示。
select last_name,to_char(hire_date, 'yyyy" 年 "MM" 月"DD"日" HH:MI:SS AM') from employees;

3.7.3.2 to_char 数字转换
数字格式模板

元素	说明
9	表示一个数
0	强制显示为零
$	放置一个浮动的美元符号
L	使用浮动本地货币符号
.	打印一个小数点
,	打印一个千位指示

FM：代表去掉返回结果中的前后空格。
3.7.3.2.1 示例

--查询雇员 Whalen，显示他的薪水，在薪水前添加美元符号与千位符。
select last_name,salary ,to_char(salary,'fm$999,999,999.00') from employees where last_name ='Whalen';

3.7.3.3 to_number 字符串到数字转换
TO_NUMBER(‘arg1’,fmt’)：将字符串转换为数值型的格式。带格式化样式 fmt。
arg1：字符串类型。需要转换的数据。fmt：转换格式。
3.7.3.3.1 示例

将￥34,346.56 转换为数字类型。
select to_number('￥34,346.56','L999,999.99') from dual;

3.7.4to_date 字符到日期转换
TO_DATE(‘arg1’,’fmt’)：将字符串转换为日期格式。带格式化样式 fmt。
arg1：字符串类型。需要转换的数据。fmt：转换格式。
3.7.4.1.1 示例

--将 2019 年 3 月 9 日 11 点 30 分转换为 Date 类型。
select to_date('2019 年 3 月 9 日 11 点 30 分 ','yyyy" 年 "MM" 月 "DD" 日 "HH" 点 "MI" 分 "') from dual;

3.7.5 函数嵌套
• 单行函数能够被嵌套任意层次
• 嵌套函数的计算是从最里层到最外层
3.7.5.1 示例

显示受雇日期 6 个月后的下一个星期五的日期。结果日期将应该是：星期，月，日，年。使用 Next 6 Month Review 作为列别名。结果按受雇日期排序。
select to_char(next_day(add_months(hire_date,6),' 星期五 '),'day,MM" 月 ",DD" 日 ",yyyy" 年 "') as "Next 6 Month Review" from employees order by hire_date;

3.8 通用函数
通用函数：可用于任意数据类型，并且适用于空值。
• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, …, exprn)
NVL(expr1, expr2) 函数：转换一个空值到一个实际的值。expr1,expr2：可用的数据类型
可以是日期、字符和数字。两个参数的数据类型必须匹配。expr1：是包含空值的源值或者表达式。expr2：是用于转换空值的目的值。
3.8.1 示例一

--计算所有员工的年薪，如果有佣金包含佣金。
select last_name,salary,commission_pct, 12*salary*nvl(commission_pct,1) from employees;

3.8.2 示例二

--计算雇员的年报酬，你需要用 12 乘以月薪，再加上年薪乘以佣金百分比。显示雇员的名字、薪水、佣金和计算完后的薪水，新的薪水列名为 AN_SAL。
select last_name,salary,commission_pct,12*salary+1
2*salary*nvl(commission_pct,0) an_sal from employees;

NVL2(expr1, expr2, expr3) 函数：NVL2 函数检查第一个表达式，如果第一个表达式不
为空，那么 NVL2 函数返回第二个表达式；如果第一个表达式为空，那么第三个表达式被
返回。
expr1：是可能包含空的源值或表达式。
expr2：expr1 非空时的返回值。expr3：
expr1 为空时的返回值。
3.8.3 示例三

--查询雇员信息，如果有佣金的显示 SAL+COMM 如果没有佣金则显示 SAL。
select last_name,salary,commission_pct,nvl2(to_cha
r(commission_pct),'sal_comm','sal') from employees;

NULLIF(expr1, expr2)函数：比较两个表达式，如果相等，函数返回空，如果不相等，
函数返回第一个表达式。第一个表达式不能为 NULL。
expr1 是对于 expr2 的被比较原值
expr2 是对于 expr1 的被比较原值。(如果它不等于 expr1，expr1 被返回)。
3.8.4 示例四

--查询雇员，显示他们的 first_name 与长度，长度列命名为 expr1。last_name 与长度，长度命名为 expr2。判断他们的 first_name 与 last_name 的长度，如果长度相同返回空，否则返回 first_name 的长度。判断结果列命名为 result。
select first_name,length(first_name)"expr1",last_name,length(last_name)"expr2",nullif(length(first_name),length(last_name))"result" from employees;

COALESCE (expr1, expr2, … exprn) 函数：返回列表中的第一个非空表达式。
expr1 如果它非空，返回该表达式。 expr2 如果第一个表达式为空并且该表达式非空，返回该表达式。
exprn 如果前面的表达式都为空，返回该表达式。
3.8.5 示例五

--查 询 雇 员 表 ， 如 果 COMMISSION_PCT 值 是 非 空 ， 显 示 它 。 如 果COMMISSION_PCT 值 是 空 ， 则 显 示 SALARY 。 如 果 COMMISSION_PCT 和SALARY 值都是空，那么显示 10。
select first_name,coalesce(commission_pct,salary,
1 0) from employees order by first_name;

3.9 条件表达式
• 在 SQL 语句中提供 IF-THEN-ELSE 逻辑的使用
• 两种用法：
– CASE 表达式
– DECODE 函数
3.9.1CASE 表达式
CASE 表达式：CASE 表达式可以让你在 SQL 语句中使用 IF-THEN-ELSE 逻辑。如果没有 WHEN … THEN 满足条件，并且ELSE 子句存在，Oracle 返回 else_expr。否则，Oracle 返回 null。所有的表达式 ( expr、comparison_expr 和 return_expr) 必须是相同的数据类型，
3.9.1.1 示例

--查询雇员，显示 last_name,job_id,salary 如果 JOB_ID 是 IT_PROG，薪水增加 10%；如果JOB_ID 是 ST_CLERK，薪水增加 15%；如果 JOB_ID 是 SA_REP，薪水增加 20%。 对于所有其他的工作角色，不增加薪水。
select last_name,job_id,salary,CASE job_id 
WHEN 'IT_PROG' THEN salary * 1.1 
WHEN 'ST_CLERK' THEN salary * 1.15 
WHEN 'SA_REP' THEN salary * 1.2 END 
from employees;

3.9.2DECODE 函数

DECODE 函数：DECODE 函数以一种类似于在多种语言中使用的 IF-THEN-ELSE 逻辑的方法判断一个表达式。 DECODE 函数在比较表达式 (expression) 和每个查找 (search)值后，如果表达式与查找相同，返回结果。如果省略默认值，当没有查找值与表达式相匹
配时返回一个空值。
3.9.2.1 示例

--使用 DECODE 函数完成（显示 last_name,job_id,salary 如果 JOB_ID 是 IT_PROG，薪水增加 10%；如果 JOB_ID 是 ST_CLERK，薪水增加 15%；如果 JOB_ID 是 SA_REP， 薪水增加 20%。对于所有其他的工作角色，不增加薪水。)
select last_name,job_id,salary,decode(job_id,'IT_PROG',salary*1.1,'ST_CLERK',salary*1.15,'SA_REP',salary * 1.2) from employees;

3.10 单行函数小节练习
3.10.1 写一个查询显示当前日期，列标签显示为 Date。

select sysdate "Date" from dual;

3.10.2 对每一个雇员，显示 employee number、last_name、salary 和 salary 增加 15%，并且表示成整数，列标签显示为 New Salary。

select employee_id,last_name,salary,round(salary*1.15) "New Salary" from employees;

3.10.3 查询所有名字开始字母是 J、A 或 M 的雇员，用首字母大写，其它字母小写显示雇员的 last names，显示名字的长度，用雇员的 last_names 排序结果。

select initcap(last_name),length(last_name) from
employees where last_name like 'J%' or last_name like 'M%' or last_name like 'A%' order by last_name;

3.10.4 对每一个雇员，显示其的 last name，并且计算从雇员受雇日期到今天的月数，列标签 MONTHS_WORKED。按受雇月数排序结果，四舍五入月数到最靠近的整数月。

select last_name,round(months_between(sysdate,hire
_date)) month_worked from employees order by months_between(sysdate,hire_date);

3.10.5 写一个查询对每个雇员做计算：<雇员的 last name>earns monthly but wants < 3 倍 salary>。要求薪水中包含$与千位符。列标签 Dream Salaries。

select last_name || 'earns' || to_char(salary,
'fm$999,999.00') || 'monthly but wants' || to_char(3*salary,'fm$999,999.00') as "Dream Salaries" from employees;

3.10.6 创建一个查询显示所有雇员的 last name 和 salary。对 salary 格式化为 15 个字符长度，用 $ 左填充，列标签 SALARY。

select last_name, lpad(salary ,15,'$') salary from employees;

3.10.7 显示每一个雇员的 last name、hire date 和 salary 和入职日期，该日期是服务六个月后的第一个星期一，列标签 REVIEW。格式化日期显示看起来象 “ 2019 年 3 月 9日 星期六” 的样子。

select last_name,hire_date,salary,to_char(next_day(add_months(hire_date,6),'星期一'),'yyyy"年"MM"月"DD" 日"DAY') REVIEW from employees;

3.10.8 显示 last name、hire date 和 雇员开始工作的星期，列标签 DAY，用星期一作为周的起始日排序结果。

select last_name,hire_date,to_char(hire_date,'DAY')day from employees order by to_char(hire_date -1,'d');

3.10.9 创建一个查询显示雇员的 last names 和 commission( 佣 金 ) 比 率 。 如 果 雇 员 没 有 佣 金 ， 显 示“No Commission”，列标签 COMM。

select last_name,nvl(to_char(commission_pct),
'No Commission') from employees;

3.10.10 用 DECODE 函数，写一个查询，按照下面的数据显示所有雇员的基于 JOB_ID 列值的级别。

select
job_id,decode(job_id,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP','D','ST_CLERK','E','0') from employees;

3.10.11 用 CASE 语法，实现前面的查询。

select job_id,CASE job_id WHEN 'AD_PRES'
THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG'
THEN 'C' WHEN 'SA_REP' THEN 'D' WHEN 'ST_CLERK'
THEN 'E' ELSE '0' END from employees;