字符函数
- lower 转换小写
- upper 转换大写
- initcap 转换首字母大写
SELECT 'helloworld Java',
LOWER('helloworld Java') ,
UPPER('helloWorld Java') ,
INITCAP('helloWorld Java')
FROM dual
- concat 字符连接
SELECT CONCAT('Hello', ' World!') FROM dual
- substr 查询子串
SELECT SUBSTR('abcdefgh',2,4) FROM dual
- length 查询字符长度
- instr 获取某个字符在字符串中的位置
SELECT LENGTH('HelloWorld!'),INSTR('HelloWorld!','W') FROM dual
- lpad 向左补齐字符
- rpad 向右补齐字符
SELECT salary,LPAD(salary,10,'*'),RPAD(salary,10,'*') FROM emp
- trim去除前后字符函数
SELECT TRIM('H' FROM 'HelloHWorldHH') FROM dual
- trim去除空格
SELECT TRIM(' HelloHWorldHH ') FROM dual
- replace字符串替换函数
SELECT REPLACE('abcdabcdabcd','a','m') FROM dual
数值函数
- round 四舍五入到指定位
SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM dual
- trunc 对指定位截断
SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM dual
-mod 求余（模）
SELECT sal, MOD(sal,1000) AS Mod_Sal FROM emp
日期函数
image.png
转换函数
- to_date 字符转换日期
- to_number 字符转换数字
- to_char 对日期进行转换
select TO_CAHR(SYSDATE,'yyyy-MM-dd hh:mm:dd day') from dual
- 金额格式，如果是用9，不足的位数不会填充；但是使用0，就会在不足的位上补零
SELECT TO_CHAR(sal, '$999,999.00') SALARY
FROM emp
WHERE ename = 'King';
SELECT TO_CHAR(sal, '$000,000.00') SALARY
FROM emp
WHERE ename = 'King'
通用函数
- nvl 将空值转换成一个已知的值
- nvl2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
SELECT ename,sal,NVL2(comm, comm, 0) comm
FROM emp
WHERE deptno IN (20, 30)
- nullif (expr1, expr2) :相等返回NULL，不等返回expr1
select nullif(50,50) from dual
- coalesce :与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值
select coalesce(null,comm,sal) from emp
条件表达式
- case
select ename, deptno,
(case deptno
when 10 then '销售部'
when 20 then '技术部'
when 30 then '管理部'
else '无' end) deptname
from emp
- decode
select ename, deptno,
decode(deptno,
10, '销售部',
20, '技术部',
30, '管理部',
'无' ) deptname
from emp
练习1.1 写一个查询，用首字母大写，其他字母小写显示雇员的ename，显示名字的长度，并给每一列适当的标签，条件是满足所有雇员名字的开始字母是J、A或M的雇员，并对查询结果按雇员的ename升序排序。（用initcap、length、substr）
select initcap(ename) ename, length(ename) length from emp
where substr(ename,1,1) in ('J','A','M')
order by ename
2.1 查询员工姓名中中包含大写或小写字母A的员工姓名。
select ename from emp where instr(upper(ename),'A')>0
2.2 查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中包含大写字母A的员工姓名，员工姓名长度（提示，要求使用INSTR函数，不能使用like进行判断)。
select ename, length(ename) from emp
where deptno in (10,20) and hiredate>'1981-05-01' and instr(ename,'A')>0
2.3 查询每个职工的编号,姓名，工资要求将查询到的数据按照 要求将查询到的数据按照定的格式合并成一个字符串.
–前10位：编号,不足部分用填充,左对齐
–中间10位：姓名，不足部分用填充,左对齐
–后10位：工资，不足部分用*填充,右对齐
select rpad(empno,10,'*')||rpad(ename,10,'*')||lpad(sal,10,'*') emp from emp
3.1 写一个查询，分别计算100.456四舍五入到小数点后2位，1位，整数位的值。
select round(100.456,2), round(100.456, 1), round(100.456,0) from dual
3.2 写一个查询，分别计算100.456从小数点后2位，1位，整数位截断的值。
select trunc(100.456,2), trunc(100.456,1), trunc(100.456,0) from dual
4.1 查询每个员工截止到现在一共入职多少天？
select round(sysdate-hiredate,0) days from emp
5.1 查询服务器当前时间
select sysdate from dual
5.2 查询部门10 20 的员工截止到2000年1月1日，工作了多少个月，入职的月份。(提示：使用months_between,extract)
select round(months_between('2000-01-01', hiredate),0) work_months, extract(month from hiredate) hire_month
from emp
where deptno in (10,20)
5.3 如果员工试用期6个月，查询职位不是MANAGER的员工姓名，入职日期,转正日期，入职日期后的第一个星期一,入职当月的最后一天日期。（提示：使用add_months,next_day,last_day)
select ename, hiredate, add_months(hiredate, 6), next_day(hiredate, 'Monday'), last_day(hiredate)
from emp
where job<>'MANAGER'
6.4 把字符串2015-3月-18 13:13:13 转换成日期格式转换成日期格式，并计算
和系统当前时间间隔多少天。 (提示：使用to_date函数)
select round(sysdate-to_date('2015-March-18 13:13:13','YYYY-MM-DD HH24:MI:SS')) from dual
课后作业1. 计算2000年1月1日到现在有多少月，多少周（四舍五入）。
select round(months_between(sysdate,'2000-01-01')) months,
round((sysdate-date'2000-01-01')/7) weeks
from dual
- 查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。
select * from emp where substr(ename,3,1)='A'
select * from emp where instr(ename,'A',3,1)=3
- 使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘hello’分别处理得到下列字符串ello、Hello、ll、hello。
select trim('h'from'hello') from dual;
select initcap('hello') from dual;
select trim('b'from'bllb') from dual;
select lower('hello') from dual
- 将员工工资按如下格式显示：123,234.00 RMB 。
select to_char('12323400', '999,999,99')||'RMB' sal from dual
- 查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
select ename, nvl(to_char(mgr),'No Manager') from emp
- 将员工的参加工作日期按如下格式显示：月份/年份。
select to_char(hiredate, 'MON/YY') from emp
- 在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，如果工资大于等于1000并小于2000，
税率为10％，如果工资大于等于2000并小于3000，税率为15％，如果工资大于等于3000，税率为20％。
select ename, sal, (case
when sal<1000 then 0
when sal>=1000 and sal<2000 then sal*0.1
when sal>=2000 and sal<3000 then sal*0.15
when sal>=3000 then sal*0.2
end) tax
from emp
- 创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。
select ename, lpad(sal,15,'$') salary from emp
网友评论