声明一下，最近在公司学习，看的传智播客的视频，感觉能练练，就吧这个笔记开始写到这里了。要视频的可以给我留言哈。
一、字符函数：
大小写控制函数： LOWER、UPPER、INITCAP
字符控制函数：CONCAT、SUBSTR、LENGTH/LENGTHB、INSTR、LPAD | RPAD、TRIM、REPLACE
1.大小写控制函数
<span style="font-size:18px;">select lower('Hello WORLd') 转小写,upper('Hello WORLd') 转大写,initcap('hello world') 首字母大写 from dual;</span>
2.substr(a,b) 从a中，第b位开始取，取右边所有的字符
<span style="font-size:18px;">select substr('hello world',3) from dual;</span>
3.substr(a,b,c) 从a中，第b位开始取，取c位(空格也算哦)
<span style="font-size:18px;">select substr('hello world',3,4) from dual;</span>
4.length 字符数 lengthb 字节数
<span style="font-size:18px;">select length('hello world') 字符数,lengthb('hello world') 字节数 from dual;</span><span style="font-size:18px;"> 字符数 字节数
---------- ----------
11 11 </span><span style="font-size:18px;">select length('中国') 字符数,lengthb('中国') 字节数 from dual;</span><span style="font-size:18px;"> 字符数 字节数
---------- ----------
2 4
</span>5.instr(a,b) 从a中查找b，找到返回下标，否则返回0
<span style="font-size:18px;">select instr('hello world','ll') from dual;</span><span style="font-size:18px;">INSTR('HELLOWORLD','LL')
------------------------
3 </span>6.lpad 左填充 rpad右填充
<span style="font-size:18px;">select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;</span><span style="font-size:18px;">左 右
---------- ----------
******abcd abcd******
</span>7.trim: 去掉前后指定的字符
<span style="font-size:18px;">select trim('H' from 'Hello WorldH') from dual;</span><span style="font-size:18px;">TRIM('H'FR
----------
ello World </span>
8.replace 替换
<span style="font-size:18px;">select replace('hello world','l','*') from dual;</span><span style="font-size:18px;">REPLACE('HE
-----------
he**o wor*d </span>二、数字函数
ROUND:四舍五入 ROUND(3.14159,3) 3.142
TRUNC:截断 TRUNC(3.14159,3) 3.141
MOD:求余 MOD(1600,300) 100
1.ROUND:四舍五入
</pre></p><pre name="code" class="sql"><span style="font-size:18px;">select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二,ROUND(45.926, 0) 三,ROUND(45.926, -1) 四, ROUND(45.926, -2) 五 from dual;</span>
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.93 45.9 46 50 0 2. TRUNC :截断
<span style="font-size:18px;">select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二,TRUNC(45.926, 0) 三,TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五 from dual;<span style="font-family: Arial, Helvetica, sans-serif;"> </span></span> 一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.92 45.9 45 40 0 三、日期函数 sysdate 精确到秒 systimestamp 精确到秒以下的单位
1.日期
select sysdate from dual;
SYSDATE
--------------
19-6月 -13 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YY
-------------------
2013-06-19 14:22:23
select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff') from dual;TO_CHAR(SYSTIMESTAMP,'YYYY-MM
-----------------------------
2013-06-19 14:23:22:000000 2.昨天 今天 明天
select (sysdate-1) 昨天, sysdate 今天,(sysdate+1) 明天 from dual;昨天 今天 明天
-------------- -------------- --------------
18-6月 -13 19-6月 -13 20-6月 -13 3.计算员工的工龄
select ename,hiredate,(sysdate-hiredate) 天, (sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp;
ENAME HIREDATE 天 星期 月 年
---------- -------------- ---------- ---------- ---------- ----------
SMITH 17-12月-80 11872.6018 1696.08597 395.753392 32.5276761
ALLEN 20-2月 -81 11807.6018 1686.80025 393.586725 32.3495939
WARD 22-2月 -81 11805.6018 1686.51454 393.520059 32.3441144
JONES 02-4月 -81 11766.6018 1680.94311 392.220059 32.2372651
MARTIN 28-9月 -81 11587.6018 1655.37168 386.253392 31.7468541
BLAKE 01-5月 -81 11737.6018 1676.80025 391.253392 32.157813
CLARK 09-6月 -81 11698.6018 1671.22882 389.953392 32.0509637
SCOTT 13-7月 -87 703435.602 100490.8 23447.8534 1927.22083
KING 17-11月-81 11537.6018 1648.22882 384.586725 31.6098678
TURNER 08-9月 -81 11607.6018 1658.22882 386.920059 31.8016487
ADAMS 13-7月 -87 703435.602 100490.8 23447.8534 1927.22083
ENAME HIREDATE 天 星期 月 年
---------- -------------- ---------- ---------- ---------- ----------
JAMES 03-12月-81 11521.6018 1645.94311 384.053392 31.5660322
FORD 03-12月-81 11521.6018 1645.94311 384.053392 31.5660322
MILLER 23-1月 -82 11470.6018 1638.65739 382.353392 31.4263062
已选择14行。注意：不可以日期加日期：
select sysdate+hiredate from emp;select sysdate+hiredate from emp
*
第 1 行出现错误:
ORA-00975: 不允许日期 + 日期 4.last_day 日期所在月份的最后一天
select last_day(sysdate) from dual;LAST_DAY(SYSDA
--------------
30-6月 -13 5.MONTHS_BETWEEN 返回两个日期相差的月数
select ename,hiredate,(sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二 from emp;ENAME HIREDATE 一 二
---------- -------------- ---------- ----------
SMITH 17-12月-80 395.753475 390.084008
ALLEN 20-2月 -81 393.586809 387.987234
WARD 22-2月 -81 393.520142 387.922718
JONES 02-4月 -81 392.220142 386.567879
MARTIN 28-9月 -81 386.253475 380.72917
BLAKE 01-5月 -81 391.253475 385.600137
CLARK 09-6月 -81 389.953475 384.342073
SCOTT 13-7月 -87 23447.8535 23111.213
KING 17-11月-81 384.586809 379.084008
TURNER 08-9月 -81 386.920142 381.374331
ADAMS 13-7月 -87 23447.8535 23111.213
ENAME HIREDATE 一 二
---------- -------------- ---------- ----------
JAMES 03-12月-81 384.053475 378.535621
FORD 03-12月-81 384.053475 378.535621
MILLER 23-1月 -82 382.353475 376.89046
已选择14行。6.ADD_MONTHS: 加上若干个月
select ADD_MONTHS(sysdate,12) from dual;ADD_MONTHS(SYS
--------------
22-10月-15 7.next_day下一个星期几
select next_day(sysdate,'星期三'） from dual;NEXT_DAY(SYSDA
--------------
29-10月 -14
注意：其使用官方叫法，别用俗称：礼拜三等等
8.日期的四舍五入
select round(sysdate,'month'), round(sysdate,'year') from dual;ROUND(SYSDATE, ROUND(SYSDATE,
-------------- --------------
01-11月 -14 01-1月 -14 四、转换函数
数据类型转换：隐性转换，显性转换
|
源数据类型 |
目标数据类型
VARCHAR2 or CHAR NUMBER VARCHAR2 or CHAR DATE NUMBER VARCHAR2 DATE VARCHAR2隐式转换的前提： 被转换对象是可以转换的
1.TO_CHAR转日期
| 格式 | 说明 | 举例 |
| YYYY | Full year in numbers | 2011 |
| YEAR | Year spelled out(年的英文全称) | twenty eleven |
| MM | Two-digit value of month 月份（两位数字） | 04 |
| MONTH | Full name of the month（月的全称） | 4月 |
| DY | Three-letter abbreviation of the day of the week(星期几) | 星期一 |
| DAY | Full name of the day of the week | 星期一 |
| DD | Numeric day of the month | 02 |
eg：
select to_char(sysdate,'YYYY-mm-dd hh24:mi:ss"今天是"day') from dual;TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:
-----------------------------------
2014-10-22 22:11:45今天是 星期三 2.TO_CHAR转数字
| 9 | 数字 |
| 0 | 零 |
| $ | 美元符 |
| L | 本地货币符号 |
| . | 小数点 |
| , | 千位符 |
select to_char(sal,'L9,999.99') from emp;TO_CHAR(SAL,'L9,999
-------------------
￥800.00
￥1,600.00
￥1,250.00
￥2,975.00
￥1,250.00
￥2,850.00
￥2,450.00
￥3,000.00
￥5,000.00
￥1,500.00
￥1,100.00
TO_CHAR(SAL,'L9,999
-------------------
￥950.00
￥3,000.00
￥1,300.00
已选择14行。
五、通用函数，这些函数适用于热河数据类型，同时也适用于空值
NVL(expr1, expr2)
NVL2(expr1, expr2, expr 3)
NULLIF(expr1, expr2)
COALESCE(expr1,expr2,...,exprn)
前两个常用，后两个了解。
1.nvl2(a,b,c) 当a=null时，返回c，否则返回b
select sal*12+nvl2(comm,comm,0) from emp;SAL*12+NVL2(COMM,COMM,0)
------------------------
9600
19500
15500
35700
16400
34200
29400
36000
60000
18000
13200
SAL*12+NVL2(COMM,COMM,0)
------------------------
11400
36000
15600
已选择14行。2.NULLIF(a,b) 当a=b时, 返回null，否则返回a
select nullif('abc','abc') from dual;NUL
--- select nullif('abc','abcd') from dual;NUL
---
abc 3.COALESCE :从左往右找到第一个不为null的值
select comm,sal,COALESCE(comm,sal) from emp; COMM SAL COALESCE(COMM,SAL)
---------- ---------- ------------------
800 800
300 1600 300
500 1250 500
2975 2975
1400 1250 1400
2850 2850
2450 2450
3000 3000
5000 5000
0 1500 0
1100 1100
COMM SAL COALESCE(COMM,SAL)
---------- ---------- ------------------
950 950
3000 3000
1300 1300
已选择14行。六、条件表达式
在SQL语句中使用IF-THEN-ELSE逻辑
a.CASE表达式：SQL99的语法，类似Basic，比较繁琐
b.DECODE函数：Oracle自己的语法，类似Java，比较简单
eg:涨工资，总裁1000 经理800 其他400
a.case语法
<span style="color:#3333ff;">CASE </span>expr <span style="color:#3333ff;">WHEN </span>comparison_expr1 <span style="color:#3333ff;">THEN </span>return_expr1<span style="color:#3333ff;">
[WHEN </span>comparison_expr2 <span style="color:#3333ff;">THEN </span>return_expr2<span style="color:#3333ff;">
WHEN </span>comparison_exprn <span style="color:#3333ff;">THEN </span>return_exprn<span style="color:#3333ff;">
ELSE </span>else_expr<span style="color:#3333ff;">]
END
</span>select ename,job,sal 涨前薪水,
case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后薪水
from emp;ENAME JOB 涨前薪水 涨后薪水
---------- --------- ---------- ----------
SMITH CLERK 800 1200
ALLEN SALESMAN 1600 2000
WARD SALESMAN 1250 1650
JONES MANAGER 2975 3775
MARTIN SALESMAN 1250 1650
BLAKE MANAGER 2850 3650
CLARK MANAGER 2450 3250
SCOTT ANALYST 3000 3400
KING PRESIDENT 5000 6000
TURNER SALESMAN 1500 1900
ADAMS CLERK 1100 1500
ENAME JOB 涨前薪水 涨后薪水
---------- --------- ---------- ----------
JAMES CLERK 950 1350
FORD ANALYST 3000 3400
MILLER CLERK 1300 1700
已选择14行。b.DECODE语法
<span style="color:#3333ff;">DECODE</span>(col|expression, search1, result1
[, search2, result2,...,]
[, default])
select ename,job,sal 涨前薪水,
decode(job,'PRESIDENT',sal+1000,
'MANAGER',sal+800,
sal+400) 涨后薪水
from emp;
ENAME JOB 涨前薪水 涨后薪水
---------- --------- ---------- ----------
SMITH CLERK 800 1200
ALLEN SALESMAN 1600 2000
WARD SALESMAN 1250 1650
JONES MANAGER 2975 3775
MARTIN SALESMAN 1250 1650
BLAKE MANAGER 2850 3650
CLARK MANAGER 2450 3250
SCOTT ANALYST 3000 3400
KING PRESIDENT 5000 6000
TURNER SALESMAN 1500 1900
ADAMS CLERK 1100 1500
ENAME JOB 涨前薪水 涨后薪水
---------- --------- ---------- ----------
JAMES CLERK 950 1350
FORD ANALYST 3000 3400
MILLER CLERK 1300 1700
已选择14行。ＨＯＭＥＷＯＲＫ：
根据80好部门员工的工资，显示税率
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;
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
