SQL中不同类型的函数
在select语句中使用字符，数字，日期和转换函数
使用条件表达式
单行函数：字符 通用 转换 日期 数值
--字符：
-- 控制大小写 lower upper initcap
SELECT lower( 'AFANGFANG'),upper('AFANGFANGmiaomiao') ,initcap('AFANGFANG MIAOMIAO') FROM dual;
LOWER('AFANGFANG') UPPER('AFANGFANGMIAOMIAO') INITCAP('AFANGFANGMIAOMIAO') ------------------ -------------------------- ---------------------------- afangfang AFANGFANGMIAOMIAO Afangfang Miaomiao
--应用
select last_name from employees where upper(last_name) = 'KING';
LAST_NAME ------------------------- King King
-- 字符控制函数
| 函数 | 结果 |
| concat('Hello','World') | HelloWorld 拼接 |
| substr('HelloWorld',1,6) | HelloW 第一位开始 截6个字符 |
| length('HelloWorld') | 10 |
| instr('HelloWorld','W') | 6 字符在字符串首次出现的位置 select instr('HelloWorld','l') from dual; 3 |
| lpad(salary,10,'*') | *****24000 给10个位不足的用*填 填左边 select employee_id,last_name,lpad(salary,10,' ') from employees; |
| rpad(salary,10,'*') | 24000***** |
| trim('H' from 'HelloWorLdH') | elloWorLd 去除首尾的 select trim('H' from 'HelloWorLdH') from dual; |
| replace('abcedd','d','m') | abcemm 全部替换 select replace('abcedd','d','m') from dual; |
-- 数字函数
--round 四舍五入
select round(435.45,1),round(435.45),round(435.45,-1) from dual;
ROUND(435.45,1) ROUND(435.45) ROUND(435.45,-1) --------------- ------------- ---------------- 435.5 435 440
-- trunc 截断
select trunc(435.45,1),round(435.45),round(435.45,-1)from dual;
TRUNC(435.45,1) ROUND(435.45) ROUND(435.45,-1) --------------- ------------- ---------------- 435.4 435 440
-- mod 求余
select mod(1200,100)from dual;
MOD(1200,100) ------------- 0
select mod(1200,500)from dual;
MOD(1200,500) ------------- 200
-- 日期的数学运算：在日期上加上或者减去一个数字结果仍为日期
-- 两个日期相减返回日期之间相差的天数（日期不允许做加法运算， 无意义）
-- 可以用数字除去24来向日期中加上或减去的天数
--查工作的天数
select employee_id, last_name, trunc(sysdate-hire_date) worked_days from employees;
EMPLOYEE_ID LAST_NAME WORKED_DAYS ----------- ------------------------- ----------- 100 King 11966 101 Kochhar 11139 102 De Haan 9929 103 Hunold 11035
--查工作月数
select employee_id, last_name, (sysdate-hire_date)/30 worked_days1, months_between(sysdate,hire_date) worked_days2 from employees;
-- 一般使用后面的（准确）select employee_id, last_name,months_between(sysdate,hire_date) worked_days2 from employees;
EMPLOYEE_ID LAST_NAME WORKED_DAYS1 WORKED_DAYS2 ----------- ------------------------- ------------ ------------ 100 King 398.8906821 393.152273 101 Kochhar 371.3240154 366 102 De Haan 330.9906821 326.2813053 103 Hunold 367.8573488 362.6038859
--来公司的员工，hire_date是每个月倒数第二天来公司的有哪些？
select last_name ,hire_date from employees where hire_date = last_day(hire_date)-1;
LAST_NAME HIRE_DATE ------------------------- --------- Atkinson 30-OCT-97 Tucker 30-JAN-97 Olsen 30-MAR-98 King 30-JAN-96
-- 转换函数：数据类型转换 ： 隐性 & 显性
-- 隐式转换：
select '12'+2 from dual;
'12'+2 ---------- 14
-- 显示转换：
date转换为char型的：where to_char(hire_date,'yyy"年"-mm"月"-dd"日"') = '1994年- 03月-09日'
char转换为date型的：where to_date('1994-03-23','yyyy-mm-dd') =hire_date;
numberhe char之间的转换：
--前面不用0填的用99 ，用0 填的写成0,前面可以加$或者L（为本地的）
--s数字转换为字符的 to_char
select to_char(12343253443.77,'999,999,999,999,999.99') from dual;
TO_CHAR(12343253443.77,'999,999,999,999,999.99') ------------------------------------------------ 12,343,253,443.77
select to_char(12343253443.77,'000,000,000,000,000.00') from dual;
TO_CHAR(12343253443.77,'000,000,000,000,000,00') ------------------------------------------------ 000,000,123,432,534,44
select to_char(12343253443.77,'L999,999,999,999,999.99') from dual;
TO_CHAR(12343253443.77,'L999,999,999,999,999.99') ------------------------------------------------- ￥12,343,253,443.77
--字符转换为数字 to_number
select to_number('￥12,343,253,443.77','L999,999,999,999,999.99')from dual;
TO_NUMBER('￥12,343,253,443.77','L999,999,999,999,999.99'); --------------------------------------------------------- 12343253440
-- 通用函数：适用于任何数据类型，同时也适用于空值
-- nvl(e1,e2) 当这e1不为空用它本身，为空用后面e2的值代替
-- 求公司员工的年薪含commission_pct
select employee_id ,last_name ,salary*12*(1+nvl(commission_pct,0)) "annual sal" from employees;
EMPLOYEE_ID LAST_NAME annual sal ----------- ------------------------- ---------- 100 King 288000 101 Kochhar 204000 102 De Haan 204000 103 Hunold 108000
-- 输出 last_name , department_id ,当 department_id 为null 时，显示 '没有部门'
select last_name,nvl(to_char(department_id),'没有部门') from employees;
LAST_NAME NVL(TO_CHAR(DEPARTMENT_ID),'没有部门') ------------------------- ---------------------------------------- King 90Taylor 80 Grant 没有部门 Urman 100
--nvl2(e1,e2,e3)
--nullif(e1,e2)
--coalesce(e1,e,2,..,en)
-- 条件表达式：sql语句中使用if-then-else逻辑
使用的两种方法： case表达式
decode函数
-- 查询部门号为10,20，30 的员工信息，若部门号为10，则打印其工资的1.1倍，
--20号部门，则打印其工资的1.2倍，30号部门打印其工资的1.3倍
select employee_id,last_name,department_id ,case department_id when 10 then salary*1.1 when 20 then salary*1.2 else salary*1.3 end new_sal from employees where department_id in(10,20,30);
select employee_id,last_name,department_id ,decode(department_id,10,salary*1.1, 20,salary*1.2, 30,salary*1.3) new_sal from employees where department_id in(10,20,30);
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID NEW_SAL ----------- ------------------------- ------------- ---------- 200 Wha_len 10 4840 201 Hartstein 20 15600 202 Fay 20 7200 114 Raphaely 30 14300 115 Khoo 30 4030
练习：
-- 1. 打印出 "2009年10月14日 9:25:40" 格式的当前系统的日期和时间
select to_char(sysdate,'yyyy"年"-mm"月"-dd"日" hh:mi;ss') from dual;
TO_CHAR(SYSDATE,'YYYY"年"-MM"月"-DD"日"HH:MI;SS') ---------------------------------------------- 2020年-03月-22日 10:34;27
-- 2.将员工的姓名按首字母排序，并写出姓名的长度（length）
select last_name,length(LAST_NAME) FROM employees order by last_name;
LAST_NAME LENGTH(LAST_NAME) ------------------------- ----------------- Abel 4 Ande 4 Atkinson 8 Austin 6
-- 3. 查询各员工的姓名，并显示出各员工在公司工作的月份数 round保留一位小数
select last_name,hire_date,round(months_between(sysdate,hire_date),1) workded_month from employees;
LAST_NAME HIRE_DATE WORKDED_MONTH ------------------------- --------- ------------- King 17-6月 -87 393.2 Kochhar 21-9月 -89 366 De Haan 13-1月 -93 326.3 Hunold 03-1月 -90 362.6
-- 4.使用case函数，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
|
Last_name |
Job_id |
Grade |
|
king |
AD_PRES |
A |
select last_name "Last_name",job_id "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' end "Grade" from employees;
Last_name Job_id Grade ------------------------- ---------- ----- King AD_PRES A Kochhar AD_VP De Haan AD_VP Hunold IT_PROG C Ernst IT_PROG C Lorentz IT_PROG C