1.知識點:可以對照下面的錄屏進行閱讀
SQL> --字符函數 SQL> --字符串的轉換 SQL> select lower('hellO WORld') 轉小寫,upper('hellO WORld') 轉大寫,initcap('hello world') 首字母大寫 2 from dual; SQL> --substr(a,b) 從a中,第b位開始取,取右邊所有的字符 SQL> select substr('Hello World',4) from dual; SQL> --substr(a,b,c) 從a中,第b位開始取,取c位 SQL> select substr('Hello World',4,4) from dual; SQL> --length 字符數 lengthb 字節數 SQL> select length('Hello World') 字符數, lengthb('Hello World') 字節數 2 from dual; SQL> --instr 在母串中查找子串,找到返回下標(從1開始),否則返回0 SQL> select instr('Hello World','ll') from dual; SQL> --lpad 左填充 rpad 右填充 SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 --向’abcd’字符串的左/右填充’*’,填充到整個字符串的長度為10 2 from dual; SQL> --trim 去掉前後指定的字符 SQL> select trim('H' from 'Hello WorldH') from dual; --去掉 'Hello WorldH'中所有的’H’ SQL> --replace 替換指定位置的字符 SQL> select replace('Hello World','l','*') from dual; --將'Hello World'中的’l’替換為’*’ SQL> --數字函數 SQL> --四捨五入,第一個參數為要進行四捨五入的數,第二個參數為保留的位數,大於0的為小數點後,小於0的為小數點前的 SQL> select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二, ROUND(45.926, 0) 三, 2 ROUND(45.926, -1) 四, ROUND(45.926, -2) 五 3 from dual; SQL> --截斷,第一個參數為要進行截斷的數,第二個參數為保留的位數,大於0的為小數點後,小於0的為小數點前的 SQL> select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二, TRUNC(45.926, 0) 三, 2 TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五 3 from dual SQL> --日期函數 SQL> --查詢當前日期 SQL> select sysdate from dual; SQL> --查詢當前時間,具體到時分秒 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual SQL> --systimestamp時間戳,更精確 SQL> select systimestamp from dual; SQL> --昨天 今天 明天 SQL> select (sysdate-1) 昨天, sysdate 今天, (sysdate+1) 明天 from dual; SQL> --員工的工齡, SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期, 2 (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 3 from emp; SQL> --日期不能進行相加 SQL> select hiredate+sysdate from emp; select hiredate+sysdate from emp * 第 1 行出現錯誤: ORA-00975: 不允許日期 + 日期 SQL> --last_day:本月的最後一天 SQL> select last_day(sysdate) from dual; SQL> --MONTHS_BETWEEN :計算兩個時間之間相差的月份 SQL> select (sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二 --計算工齡(粗略計算和精確計算) 2 from emp; SQL> --add_months:指定時間加上指定月份 SQL> select add_months(sysdate,114) from dual; --查詢當前時間加上114個月份後的時間 SQL> --next_day:指定日期的下一個日期 SQL> select next_day(sysdate,'星期二') from dual; --從今天開始算,下一個星期二 SQL> --對日期進行四捨五入 SQL> select round(sysdate,'month') 一,round(sysdate,'year') 二 from dual; --按月,年進行四捨五入 SQL> --隱式轉換的前提: 被轉換對象是可以轉換的 SQL> --varchar2 or char ->number ; varchar2 or char ->date ; number -> varchar2 ; date ->varchar2 ; SQL> --顯式轉換 SQL> --number to_char character ; character to_number number ; date to_char character ; char to_date date ; SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual; --將date轉為char SQL> --to_char函數中經常使用的格式:9 數字;0 零;$ 美元符;L 本地貨幣;. 小數點;, 千位符; SQL> --查詢員工的薪水: 貨幣符號,兩位小數,千位符 SQL> select sal, to_char(sal,'L9,999.99') from emp; SQL> --通用函數 SQL> --nvl2(a,b,c) 當a=null時,返回c,否則返回b SQL> select sal*12+nvl2(comm,comm,0) from emp; SQL> --nullif(a,b) 當a=b時,返回null,否則返回a SQL> select nullif('abc','abc') from dual; SQL> --COALESCE 從左至右找到第一個不為null的值,如果全為空,則返回空 SQL> select comm,sal,COALESCE(comm,sal) from emp; SQL> --case語句: SQL> --根據職位漲工資 總裁1000 經理800 其他400 SQL> select ename,job,sal 漲前薪水, case job when 'PRESIDENT' then sal+1000 2 when 'MANAGER' then sal+800 3 else sal+400 4 end 漲後薪水 5 from emp; SQL> --decode:功能和case一樣,第一個參數為表達式,之後每兩個為一對:條件,值;最後一個為缺省值,相當於else的值 SQL> select ename,job,sal 漲前薪水,decode(job,'PRESIDENT',sal+1000, 2 'MANAGER', sal+800, 3 sal+400)漲後薪水 4 from emp;2.在Sqlplus下實際執行的結果錄屏:
SQL> --字符函數 SQL> select lower('hellO WORld') 轉小寫,upper('hellO WORld') 轉大寫,initcap('hello world') 首字母大寫 2 from dual; 轉小寫 轉大寫 首字母大寫 ----------- ----------- ----------- hello world HELLO WORLD Hello World SQL> host cls SQL> --substr(a,b) 從a中,第b位開始取,取右邊所有的字符 SQL> select substr('Hello World',4) from dual; SUBSTR(' -------- lo World SQL> --substr(a,b,c) 從a中,第b位開始取,取c位 SQL> select substr('Hello World',4,4) from dual; SUBS ---- lo W SQL> host cls SQL> --length 字符數 lengthb 字節數 SQL> select length('Hello World') 字符數, lengthb('Hello World') 字節數 2 from dual; 字符數 字節數 ---------- ---------- 11 11 SQL> ed 已寫入 file afiedt.buf 1 select length('北京') 字符數, lengthb('北京') 字節數 2* from dual SQL> / 字符數 字節數 ---------- ---------- 2 4 SQL> host cls SQL> --instr 在母串中查找子串,找到返回下標(從1開始),否則返回0 SQL> select instr('Hello World','ll') from dual; INSTR('HELLOWORLD','LL') ------------------------ 3 SQL> host cls SQL> --lpad 左填充 rpad 右填充 SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 2 from dual; 左 右 ---------- ---------- ******abcd abcd****** SQL> host cls SQL> --trim 去掉前後指定的字符 SQL> select trim('H' from 'Hello WorldH') from dual; TRIM('H'FR ---------- ello World SQL> select replace('Hello World','l','*') from dual; REPLACE('HE ----------- He**o Wor*d SQL> host cls SQL> --數字函數 SQL> select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二, ROUND(45.926, 0) 三 2 ROUND(45.926, -1) 四, ROUND(45.926, -2) 五 3 from dual; ROUND(45.926, -1) 四, ROUND(45.926, -2) 五 * 第 2 行出現錯誤: ORA-00923: 未找到要求的 FROM 關鍵字 SQL> ed 已寫入 file afiedt.buf 1 select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二, ROUND(45.926, 0) 三, 2 ROUND(45.926, -1) 四, ROUND(45.926, -2) 五 3* from dual SQL> / 一 二 三 四 五 ---------- ---------- ---------- ---------- ---------- 45.93 45.9 46 50 0 SQL> ed 已寫入 file afiedt.buf 1 select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二, TRUNC(45.926, 0) 三, 2 TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五 3* from dual SQL> / 一 二 三 四 五 ---------- ---------- ---------- ---------- ---------- 45.92 45.9 45 40 0 SQL> host cls SQL> --日期函數 SQL> select sysdate from dual; SYSDATE -------------- 22-10月-12 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual 2 ; TO_CHAR(SYSDATE,'YY ------------------- 2012-10-22 15:39:21 SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 22-10月-12 03.40.05.406000 下午 +08:00 SQL> host cls SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2012-10-22 15:40:33 SQL> select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss*ff') from dual; TO_CHAR(SYSTIMESTAMP,'YYYY-MM ----------------------------- 2012-10-22 15:40:47*218000 SQL> --systimestamp時間戳 SQL> --昨天 今天 明天 SQL> select (sysdate-1) 昨天, sysdate 今天, (sysdate+1) 明天 from dual; 昨天 今天 明天 -------------- -------------- -------------- 21-10月-12 22-10月-12 23-10月-12 SQL> --員工的工齡 SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期, 2 (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 3 from emp; ENAME HIREDATE 天 星期 月 年 ---------- -------------- ---------- ---------- ---------- ---------- SMITH 17-12月-80 11632.6571 1661.80815 387.755235 31.8702933 ALLEN 20-2月 -81 11567.6571 1652.52244 385.588569 31.6922111 WARD 22-2月 -81 11565.6571 1652.23672 385.521902 31.6867317 JONES 02-4月 -81 11526.6571 1646.66529 384.221902 31.5798824 MARTIN 28-9月 -81 11347.6571 1621.09387 378.255235 31.0894714 BLAKE 01-5月 -81 11497.6571 1642.52244 383.255235 31.5004303 CLARK 09-6月 -81 11458.6571 1636.95101 381.955235 31.393581 SCOTT 13-7月 -87 9233.65706 1319.09387 307.788569 25.2976906 KING 17-11月-81 11297.6571 1613.95101 376.588569 30.9524851 TURNER 08-9月 -81 11367.6571 1623.95101 378.921902 31.1442659 ADAMS 13-7月 -87 9233.65706 1319.09387 307.788569 25.2976906 ENAME HIREDATE 天 星期 月 年 ---------- -------------- ---------- ---------- ---------- ---------- JAMES 03-12月-81 11281.6571 1611.66529 376.055235 30.9086495 FORD 03-12月-81 11281.6571 1611.66529 376.055235 30.9086495 MILLER 23-1月 -82 11230.6571 1604.37958 374.355235 30.7689235 已選擇14行。 SQL> select hiredate+sysdate from emp; select hiredate+sysdate from emp * 第 1 行出現錯誤: ORA-00975: 不允許日期 + 日期 SQL> host cls SQL> select last_day(sysdate) from dual; LAST_DAY(SYSDA -------------- 31-10月-12 SQL> --MONTHS_BETWEEN 計算工齡 SQL> select (sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二 2 from emp; 一 二 ---------- ---------- 387.755329 382.182577 385.588662 380.085802 385.521996 380 384.221996 378.666448 378.255329 372.827738 383.255329 377.698706 381.955329 376.440641 307.788662 303.311609 376.588662 371.182577 378.921996 373.472899 307.788662 303.311609 一 二 ---------- ---------- 376.055329 370.634189 376.055329 370.634189 374.355329 368.989028 已選擇14行。 SQL> host cls SQL> --114月後 SQL> select add_months(sysdate,114) from dual; ADD_MONTHS(SYS -------------- 22-4月 -22 SQL> --從今天開始算(2012.10.22星期一),下一個星期一 SQL> select next_day(sysdate,'星期一') from dual; NEXT_DAY(SYSDA -------------- 29-10月-12 SQL> --從今天開始算(2012.10.22星期一),下一個星期二 SQL> select next_day(sysdate,'星期二') from dual; NEXT_DAY(SYSDA -------------- 23-10月-12 SQL> host cls SQL> --對日期進行四捨五入 SQL> select round(sysdate,'month') 一,round(sysdate,'year') 二 from dual; 一 二 -------------- -------------- 01-11月-12 01-1月 -13 SQL> --隱式轉換的前提: 被轉換對象是可以轉換的 SQL> host cls SQL> --顯式轉換 SQL> --2012-10-22 16:24:13 今天是星期一 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI ---------------------------------- 2012-10-22 16:25:52今天是星期一 SQL> --查詢員工的薪水: 貨幣符號,兩位小數,千位符 SQL> select sal, to_char(sal,'L9,999.99') from emp; SAL TO_CHAR(SAL,'L9,999 ---------- ------------------- 800 ¥800.00 1600 ¥1,600.00 1250 ¥1,250.00 2975 ¥2,975.00 1250 ¥1,250.00 2850 ¥2,850.00 2450 ¥2,450.00 3000 ¥3,000.00 5000 ¥5,000.00 1500 ¥1,500.00 1100 ¥1,100.00 SAL TO_CHAR(SAL,'L9,999 ---------- ------------------- 950 ¥950.00 3000 ¥3,000.00 1300 ¥1,300.00 已選擇14行。 SQL> host cls SQL> --通用函數 SQL> --nvl2(a,b,c) 當a=null時,返回c,否則返回b SQL> 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行。 SQL> host cls SQL> --nullif(a,b) 當a=b時,返回null,否則返回a SQL> select nullif('abc','abc') from dual; NUL --- SQL> select nullif('abc','abdc') from dual; NUL --- abc SQL> host cls SQL> -- ¥1,600.00 SQL> ¥1,250.00 SP2-0042: 未知命令 "¥1,250.00" - 其余行忽略。 SQL> ¥2,975.00 SP2-0042: 未知命令 "¥2,975.00" - 其余行忽略。 SQL> ¥1,250.00 SP2-0042: 未知命令 "¥1,250.00" - 其余行忽略。 SQL> ¥2,850.00 SP2-0042: 未知命令 "¥2,850.00" - 其余行忽略。 SP2-0044: 要獲取已知命令的列表, 請輸入 HELP; 輸入 EXIT 可退出。 SQL> ¥2,450.00 SP2-0042: 未知命令 "¥2,450.00" - 其余行忽略。 SQL> ¥3,000.00 SP2-0042: 未知命令 "¥3,000.00" - 其余行忽略。 SQL> ¥5,000.00 SP2-0042: 未知命令 "¥5,000.00" - 其余行忽略。 SQL> ¥1,500.00 SP2-0042: 未知命令 "¥1,500.00" - 其余行忽略。 SP2-0044: 要獲取已知命令的列表, 請輸入 HELP; 輸入 EXIT 可退出。 SQL> host cls SQL> --COALESCE 從左至右找到第一個不為null的值 SQL> 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> host cls SQL> --根據職位漲工資 總裁1000 經理800 其他400 SQL> select ename,job,sal 2 from emp; ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 ENAME JOB SAL ---------- --------- ---------- JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 已選擇14行。 SQL> select ename,job,sal 漲前薪水, case job when 'PRESIDENT' then sal+1000 2 when 'MANAGER' then sal+800 3 else sal+400 4 end 漲後薪水 5 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行。 SQL> select ename,job,sal 漲前薪水,decode(job,'PRESIDENT',sal+1000, 2 'MANAGER', sal+800, 3 sal+400)漲後薪水 4 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行。 SQL> spool off