Oracle第二天大纲.docVIP

Oracle第二天大纲 ================================================= 1锛屽嚱鏁? 瀛楃鍑芥暟锛? lower 鍏ㄩ儴杞负灏忓啓 upper 鍏ㄩ儴杞负澶у啓 initcap 棣栧瓧姣嶈浆涓哄ぇ鍐? ------- concat 杩炴帴涓や釜瀛楃涓? substr 鍦ㄥ瓧绗︿覆str涓粠绗琺涓綅缃紑濮嬪彇n涓瓧绗︼紙浣嶇疆浠?寮€濮嬶級 length 姹傞暱搴? instr 浠庡瓧绗︿覆strA涓壘鍑簊tr鎵€鍦ㄧ殑浣嶇疆锛堣繑鍥炵鎵惧埌鐨?涓紝浣嶇疆浠?寮€濮嬶級 lpad 鎶婂瓧绗︿覆str琛ラ綈鍒皀涓暱搴︼紝涓嶈冻灏卞湪宸﹁竟鍔犳寚瀹氬瓧绗锛? 濡傛灉str.lengthn灏卞彇str鐨勫墠n涓瓧绗? rpad 鎶婂瓧绗︿覆str琛ラ綈鍒皀涓暱搴︼紝涓嶈冻灏卞湪鍙宠竟鍔犳寚瀹氬瓧绗锛? 濡傛灉str.lengthn灏卞彇str鐨勫墠n涓瓧绗︼紙涔熸槸浠庡墠闈㈠彇锛? trim 浠巗tr鐨勪袱绔幓鎺夊瓧绗︼紝瑕佹槸鎸囧畾鐨勫瓧绗︽椂鎵嶅幓鎺夛紝浣跨敤鏂瑰紡鐗规畩锛? 渚嬶細trim(a from aaITCASTaa) 缁撴灉涓?ITCAST trim(C from aaITCASTaa) 缁撴灉涓?aaITCASTaa 娉ㄦ剰锛氬墠闈㈠彧鑳芥寚瀹氫竴涓瓧绗︺€? replace 渚嬶細replace(aaITCASTaa, a, =) 缁撴灉涓?==ITCAST== 鏁板瓧鍑芥暟锛? round 鍥涜垗浜斿叆锛屼緥 round(45.923, 2) 琛ㄧず淇濆瓨涓や綅灏忔暟锛屼繚鐣欑殑浣嶆暟鍙互鎸囧畾姝ｃ€侀浂銆佽礋鏁般€? trunc 鎴柇锛岃垗鎺夊悗闈㈢殑鏁帮紝淇濈暀鐨勪綅鏁板彲浠ユ寚瀹氭銆侀浂銆佽礋鏁般€? mod 姹備綑锛屽缁撴灉涓? 鏃ユ湡鍑芥暟锛? 鏃ユ湡鍙互鐩稿噺锛屼絾涓嶈兘鐩稿姞锛屽洜涓烘病鏈夋剰涔夈€? select sysdate-1 鏄ㄥぉ, sysdate 浠婂ぉ, sysdate+1 鏄庡ぉ from dual; select ename,(sysdate-hiredate) 澶?(sysdate-hiredate)/7 鏄熸湡, (sysdate-hiredate)/30 鏈?(sysdate-hiredate)/365 骞? add_months next_day 浠庢煇涓棩鏈熺畻璧凤紝涓嬩竴涓嚭鐜拌鏄熸湡鍑犵殑鏃ユ湡鏄摢澶╋細 select next_day(sysdate,鏄熸湡涓?) from dual; last_day 褰撴湀鐨勬渶鍚庝竴澶? round trunc 杞崲鍑芥暟锛? 闅愬紡鏁版嵁绫诲瀷杞崲 涓?鏄惧紡鏁版嵁绫诲瀷杞崲 to_char(date, format) to_date(string [,format]) to_char(number, format) to_number(string [,format]) 濡傦細 to_number(22) 鎴?to_number($22, $99) 绌哄€煎鐞嗗嚱鏁帮細 飦?NVL (expr1, expr2) 鍙互浣跨敤鐨勬暟鎹被鍨嬫湁鏃ユ湡銆佸瓧绗︺€佹暟瀛? 飦?NVL2 (expr1, expr2, expr3) expr1涓嶄负NULL锛岃繑鍥瀍xpr2锛涗负NULL锛屽氨杩斿洖expr3锛?expr1 != null ? expr2 : expr3 锛? 鏉′欢琛ㄨ揪寮忥細 鐢ㄤ簬瀹炵幇 IF-THEN-ELSE 閫昏緫銆? CASE 琛ㄨ揪寮忥細SQL99鐨勮娉曪紝姣旇緝绻佺悙銆? DECODE 鍑芥暟锛歄racle鑷繁鐨勮娉曪紝绫讳技Java锛屾瘮杈冪畝娲併€? 渚嬶細 鏍规嵁鍛樺伐鐨勮亴浣嶆定宸ヨ祫锛氭€昏缁忕悊800 鍏朵粬400 PRESIDENT MANAGER 鍑芥暟宓屽锛? 宓屽鍑芥暟鐨勬墽琛岄『搴忔槸鐢卞唴鍒板銆? ================================================= 2锛岄泦鍚堣繍绠? 鏌ヨ灞炰簬閮ㄩ棬10涓庨儴闂?0鐨勬墍鏈夊憳宸ヤ俊鎭紝杩樺彲浠ヨ繖鏍锋煡 select * from emp where deptno=10 鍔犱笂锛堣繖閲屽啓闆嗗悎杩愮畻绗︼級 select * from emp where deptno=20; 杩欏氨鏄泦鍚堣繍绠椼€? 骞堕泦锛? UNION 闆嗗悎a + b鐨勭粨鏋滐紝娌℃湁閲嶅璁板綍銆? UNIO