锘??xml version="1.0" encoding="utf-8" standalone="yes"?>鲁大师精品99久久久,国产成人精品一区二区免费看京,欧美成人综合在线http://www.aygfsteel.com/geeksun/zh-cnFri, 20 Jun 2025 18:22:30 GMTFri, 20 Jun 2025 18:22:30 GMT60Oracle 鍑芥暟http://www.aygfsteel.com/geeksun/archive/2008/10/31/237846.htmlgeeksungeeksunFri, 31 Oct 2008 08:59:00 GMThttp://www.aygfsteel.com/geeksun/archive/2008/10/31/237846.htmlhttp://www.aygfsteel.com/geeksun/comments/237846.htmlhttp://www.aygfsteel.com/geeksun/archive/2008/10/31/237846.html#Feedback0http://www.aygfsteel.com/geeksun/comments/commentRss/237846.htmlhttp://www.aygfsteel.com/geeksun/services/trackbacks/237846.htmllower鍑芥暟錛?br />  姝ゅ嚱鏁板皢鍏ㄩ儴鐨勫ぇ鍐欏瓧姣嶉兘鍙互鍙樹負(fù)灝忓啓瀛楁瘝
  SELECT lower('ABCDE') FROM dual ;
 • dual琛ㄤ負(fù)涓涓櫄鎷熻〃錛屼互鍚庝粙緇?/div>
upper鍑芥暟錛氬皢杈撳叆鐨勫瓧絎︿覆鍙樹負(fù)澶у啓瀛楁瘝
  SELECT upper('abcd') FROM dual ;
渚嬪錛氫箣鍓嶆煡璇㈢殑鏃跺欏尯鍒嗗ぇ灝忓啓錛屾墍浠ユ澶勫鏋滆緭鍏ョ殑鏄皬鍐欏瓧姣嶏紝灝卞彲浠ラ氳繃upper鍑芥暟榪涜杞崲銆?br />  鏌ヨ闆囧憳濮撳悕鍐呭涓簊mith鐨勫叏閮ㄥ憳宸ャ?br />  SELECT * FROM emp WHERE ename=UPPER('smith') ;
initcap鍑芥暟錛氬皢姣忎釜瀛楃涓茬殑棣栧瓧姣嶅ぇ鍐?br />  渚嬪錛氳姹傚皢闆囧憳琛ㄤ腑鐨勫叏閮ㄩ泧鍛樹俊鎭殑棣栧瓧姣嶅ぇ鍐欍?br />   SELECT initcap(lower(ename)) FROM emp ;
 鍑芥暟鍙互榪涜宓屽銆?/div>
concat鍑芥暟錛氬瓧絎︿覆榪炴帴錛屽彲浠ヨ繛鎺ヤ袱涓瓧絎︿覆
 SELECT concat('hello',' world!!!') FROM dual ;
 瀹為檯涓婁嬌鐢?#8220;||”涔熷彲浠ュ畬鎴愪袱涓瓧絎︿覆鐨勮繛鎺ユ搷浣溿?/div>
substr鍑芥暟錛氬瓧絎︿覆鎴彇
 鎴彇鐨勬椂鍊欓渶瑕佹敞鎰忥細(xì)瑕佷粠閭ｉ噷寮濮嬫埅鍙栵紝涔嬪悗鍙栧闀跨殑闀垮害銆?br />  SELECT substr('hello',1,2) FROM dual ;
 娉ㄦ剰錛歴ubstr鍑芥暟鐨勫紑濮嬬偣鏄粠1寮濮嬬殑銆?/div>
length錛氬彇鍑哄瓧絎︿覆鐨勯暱搴︼紝渚嬪錛屽彇鍑烘瘡涓涓泧鍛樼殑濮撳悕鐨勯暱搴︼細(xì)
 SELECT ename||' 濮撳悕鐨勯暱搴︿負(fù)錛?||length(ename) FROM emp ;
instr鍑芥暟錛氭煡鎵懼湪涓涓瓧絎︿覆涓槸鍚︽湁鎸囧畾鐨勫瓧絎︿覆錛屽鏋滄湁錛屽垯榪斿洖鍏朵綅緗?br />  SELECT instr('hello','x') FROM dual ;
 • 濡傛灉鏈夋瀛楃涓詫紝鍒欒繑鍥炰綅緗紝濡傛灉娌℃湁錛屽垯榪斿洖0銆?/div>
replace鍑芥暟錛氭浛鎹紝鍙互灝嗕竴涓瓧絎︿覆涓殑鎸囧畾瀛楃涓叉浛鎹負(fù)鍏朵粬鍐呭錛?br />  SELECT replace('hello','l','x') FROM dual ;
trim鍑芥暟錛氬幓鎺夊乏鍙崇┖鏍肩殑鍑芥暟
 SELECT trim('        hello          ') FROM dual ;
鏌ヨ錛?br />  1銆?nbsp;媯(gè)绱㈠鍚嶆渶鍚庝竴涓瓧姣嶄負(fù)N鐨勯泧鍛?br />   • SELECT * FROM emp WHERE substr(ename,-1,1)='N';
 2銆?nbsp;媯(gè)绱㈣亴鍔′負(fù)“SALE”鐨勫叏閮ㄥ憳宸ヤ俊鎭?br />   • SELECT * FROM emp WHERE substr(job,1,4)='SALE';
 
ROUND琛ㄧず鍥涜垗浜斿叆
 •SELECT ROUND(34.56,-1) FROM dual ;
TRUNC鍑芥暟錛氳〃紺烘埅鍙栧嚱鏁?br />  • SELECT TRUNC(34.56,-2) FROM dual ;
MOD鍑芥暟錛氬彇浣欐暟
 •SELECT MOD(10,3) FROM dual ;
鍙栧緱褰撳墠鏃ユ湡錛?br />  鍦∣racle涓彲浠ラ氳繃鏌ヨsysdate鍙栧緱褰撳墠鐨勬棩鏈燂細(xì)
  • SELECT sysdate FROM dual ;
 
渚嬪錛氳綆?0閮ㄩ棬涓殑鍛樺伐榪涘叆鍏徃鐨勬棩鏈熸暟錛?br />  鑲畾浣跨敤褰撳墠鏃ユ湡-闆囦劍鏃ユ湡錛坔iredate錛?= 澶╂暟錛屽ぉ鏁?7涓烘棩鏈熸暟銆?br />  SELECT ename,round((sysdate-hiredate)/7) from emp ;
 
 
渚嬪錛?br />  姹傚嚭鎵鏈夊憳宸ョ殑鍙楅泧鏈堟暟錛歮onths_between錛屼嬌鐢ㄥ綋鍓嶆棩鏈熶笌闆囦劍鏃ユ湡姣旇緝銆?br />  • SELECT ename,round(MONTHS_BETWEEN(sysdate,hiredate)) from emp ;
ADD_MONTHS錛氳〃紺哄湪鏃ユ湡鐨勫熀紜涓婂鍔犲嚑涓湀涔嬪悗鐨勬棩鏈燂細(xì)
 姹傚嚭錛屼笁涓湀涔嬪悗鐨勬棩鏈熸槸閭ｄ竴澶╋細(xì)
  SELECT ADD_MONTHS(sysdate,3) FROM dual ;
NEXT_DAY錛氳〃紺轟笅涓涓繖澶╋紙鏄熸湡鍑狅級(jí)
 SELECT NEXT_DAY(sysdate,'鏄熸湡涓') FROM dual ;
LAST_DAY錛氭眰鍑哄綋鍓嶆棩鏈熸墍鍦ㄦ湀鐨勬渶鍚庝竴澶╋細(xì)
 SELECT LAST_DAY(sysdate) FROM dual ;
渚嬪錛?br />  鏄劇ず鍙楅泧鏃墮棿涓嶆弧320-涓湀鐨勯泧鍛樼殑緙栧彿銆佸彈闆囨棩鏈熴佸彈闆囩殑鏈堟暟銆佹弧鍏釜鏈堢殑澶嶅鏃ユ湡銆佸彈闆囧悗鐨勭涓涓槦鏈熶簲浠ュ強(qiáng)鍙楅泧褰撴湀鐨勬渶鍚庝竴澶?br /> 姹傚嚭闆囦劍鏈堟暟澶т簬320鐨勯泧鍛樹俊鎭細(xì)
 SELECT * FROM emp WHERE MONTHS_BETWEEN(sysdate,hiredate)>320 ;
緇煎悎錛?br />  SELECT empno 闆囧憳緙栧彿,hiredate 闆囦劍鏃ユ湡,
 round(MONTHS_BETWEEN(sysdate,hiredate)) 闆囦劍鏈堟暟 ,
 add_months(hiredate,6) 澶嶅鏃ユ湡,next_day(hiredate,'鏄熸湡浜?) 鍙楅泧鍚庡埌絎竴涓槦鏈熶簲,
 last_day(hiredate) 鏈鍚庝竴澶?br /> FROM emp WHERE MONTHS_BETWEEN(sysdate,hiredate)>320 ;
 
渚嬪錛氱幇鍦ㄨ姹傚彇鍑哄叏閮ㄥ憳宸ュ彈闆囩殑騫翠喚
 瀹為檯涓婅瀵筯iredate涓殑鍐呭榪涜鍒嗙錛屽彇鍑哄勾浠姐?br />   SELECT empno,ename,TO_CHAR(hiredate,'YYYY') FROM emp ;
 鍙栧嚭闆囦劍鐨勬湀浠藉拰鏃ワ細(xì)
  SELECT empno,ename,TO_CHAR(hiredate,'YYYY') 騫?
TO_CHAR(hiredate,'MM') 鏈?TO_CHAR(hiredate,'DD') 鏈?FROM emp ;
涔熷彲浠ラ氳繃姝ゅ嚱鏁版妸鏄劇ず鐨勬棩鏈熺殑鏍煎紡榪涜淇錛?br />  騫?鏈?鏃ワ細(xì)2008-4-7
 SELECT empno,ename,TO_CHAR(hiredate,'YYYY-MM-DD')  FROM emp ;
 
 姝ｅ父鎯呭喌涓嬬殑鏃ユ湡錛屽鏋滄槸鏈堝簲璇ユ槸涓や綅錛屽鏋滅幇鍦ㄦ槸2鏈堬紝鍒欎細(xì)鏄劇ず02鏈堬紝濡傛灉鐜板湪涓嶅笇鏈涙樉紺哄嚭鍓嶅0錛屽垯鍙互鍔犲叆涓涓猣m
 SELECT empno,ename,TO_CHAR(hiredate,'fmYYYY-MM-DD')  FROM emp ;
 
鍙互灝嗕竴涓暟鍊兼寜鐓ф寚瀹氱殑鏍煎紡杈撳嚭銆?br />  SELECT empno,sal FROM emp ; 飪?姝ゆ椂涓嶆槸寰堟槑紜殑鐭ラ亾鏌ヨ鍑虹殑宸ヨ祫錛岃屼笖宸ヨ祫鐨勪綅鏁頒篃涓嶆爣鍑嗐?br />  浣跨敤to_char瀹屾垚錛歋ELECT empno,to_char(sal,'L99,999') FROM emp ;
to_date鍑芥暟錛?br />  鍙互灝嗕竴涓瓧絎︿覆鍙樹負(fù)涓涓棩鏈熺被鍨嬨?br />   SELECT to_date('2007-4-7','YYYY-MM-DD') FROM dual ;
瑕佹眰錛?br />  鏌ヨ鍑哄叏閮ㄩ泧鍛樼殑騫磋柂錛堝熀鏈伐璧勫拰浣ｉ噾錛夈?br />   SELECT empno,(sal+comm)*12 FROM emp ;
 濡傛灉娌℃湁濂栭噾鐨勯泧鍛橈紝鍒欏勾钖篃娌′簡(jiǎn)錛屽洜涓烘槸null鍊鹼紝鎵浠ユ渶鍚庣殑璁＄畻緇撴灉浠嶇劧鏄痭ull銆?br /> 甯屾湜濡傛灉鏈鍚庣殑鍊間負(fù)null錛屽垯搴旇鎸?榪涜璁＄畻錛屾鏃跺氨闇瑕丯VL鍑芥暟鐨勬敮鎸併?/div>
Nvl鍑芥暟鍙互灝嗙┖鍊煎彉涓轟竴涓壒瀹氱殑鏁板?br />  SELECT empno,sal,NVL(comm,0) FROM emp ;
浣跨敤姝ゅ嚱鏁頒慨鏀逛笂闈㈢殑鏌ヨ錛?br />  SELECT empno,(sal+NVL(comm,0))*12 FROM emp ;
  select empno,ename,job from emp ;
 甯屾湜錛?br />  • 濡傛灉宸ヤ綔鏄疌LERK錛氬垯鏄劇ず鍔炰簨鍛?br />  • 濡傛灉宸ヤ綔鏄疭ALESMAN錛氬垯鏄劇ず涓洪攢鍞漢鍛?br />  • 濡傛灉宸ヤ綔鏄疢ANAGER錛屽垯鏄劇ず涓虹粡鐞?br />  SELECT empno,ename,DECODE(job,'CLERK','鍔炰簨鍛?,'SALESMAN','閿鍞漢鍛?,'MANAGER','緇忕悊') FROM emp ;

杞嚜錛歨ttp://lixinghua.blog.51cto.com/421838/91209


geeksun 2008-10-31 16:59 鍙戣〃璇勮
]]> 主站蜘蛛池模板： 辛集市| 开平市| 乐平市| 新巴尔虎左旗| 宜都市| 莱阳市| 镇巴县| 罗田县| 宽城| 济阳县| 泸西县| 永宁县| 徐汇区| 塔河县| 华宁县| 凤山县| 嘉禾县| 青川县| 仙桃市| 宁乡县| 许昌县| 泰安市| 丽水市| 太保市| 福贡县| 兰考县| 文水县| 施秉县| 密山市| 南雄市| 扎兰屯市| 合作市| 苍南县| 新疆| 岳阳县| 迭部县| 乌拉特后旗| 信宜市| 屏东县| 邢台市| 余江县|