oracle涓煡璇㈡椂鎸囧畾绱㈠紩鐨勬柟娉

浜斻€佺储寮旾ndex鐨勪紭鍖栬璁?/p>

1銆佺鐞嗙粍缁囩储寮?/p>

绱㈠紩鍙互澶уぇ鍔犲揩鏁版嵁搴撶殑鏌ヨ閫熷害锛岀储寮曟妸琛ㄤ腑鐨勯€昏緫鍊兼槧灏勫埌瀹夊叏鐨凴owID锛屽洜姝ょ储寮曡兘杩涜蹇€熷畾浣嶆暟鎹殑鐗╃悊鍦板潃銆備絾鏄湁浜汥BA鍙戠幇锛屽涓€涓ぇ 鍨嬭〃寤虹珛鐨勭储寮曪紝骞朵笉鑳芥敼鍠勬暟鎹煡璇㈤€熷害锛屽弽鑰屼細褰卞搷鏁翠釜鏁版嵁搴撶殑鎬ц兘銆傝繖涓昏鏄拰SGA鐨勬暟鎹鐞嗘柟寮忔湁鍏炽€侽RACLE鍦ㄨ繘琛屾暟鎹潡楂橀€熺紦瀛樼鐞嗘椂锛?绱㈠紩鏁版嵁姣旀櫘閫氭暟鎹叿鏈夋洿楂樼殑椹荤暀鏉冮檺锛屽湪杩涜绌洪棿绔炰簤鏃讹紝ORACLE浼氬厛绉诲嚭鏅€氭暟鎹€傚涓€涓缓鏈夌储寮曠殑澶у瀷琛ㄧ殑鏌ヨ鏃讹紝绱㈠紩鏁版嵁鍙兘浼氱敤瀹屾墍鏈夌殑鏁?鎹潡缂撳瓨绌洪棿锛孫RACLE涓嶅緱涓嶉绻佸湴杩涜纾佺洏璇诲啓鏉ヨ幏鍙栨暟鎹紝鍥犳鍦ㄥ涓€涓ぇ鍨嬭〃杩涜鍒嗗尯涔嬪悗锛屽彲浠ユ牴鎹浉搴旂殑鍒嗗尯寤虹珛鍒嗗尯绱㈠紩銆傚鏋滃杩欐牱澶у瀷琛ㄧ殑 鏁版嵁鏌ヨ姣旇緝棰戠箒锛屾垨鑰呭共鑴嗕笉寤虹储寮曘€傚彟澶栵紝DBA鍒涘缓绱㈠紩鏃讹紝搴斿敖閲忎繚璇佽绱㈠紩鏈€鍙兘鍦拌鐢ㄤ簬where瀛愬彞涓紝濡傛灉瀵规煡璇㈠彧绠€鍗曞湴鍒跺畾涓€涓储寮曪紝骞朵笉 涓€瀹氫細鍔犲揩閫熷害锛屽洜涓虹储寮曞繀椤绘寚瀹氫竴涓€傚悎鎵€闇€鐨勮闂矾寰勩€?銆佽仛绨囩殑浣跨敤

Oracle鎻愪緵浜嗗彟涓€绉嶆柟娉曟潵鎻愰珮鏌ヨ閫熷害锛屽氨鏄仛绨囷紙Cluster锛夈€傛墍璋撹仛绨囷紝绠€鍗曞湴璇村氨鏄妸鍑犱釜琛ㄦ斁鍦ㄤ竴璧凤紝鎸変竴瀹氬叕鍏卞睘鎬ф贩鍚堝瓨鏀俱€傝仛绨囨牴 鎹叡鍚岀爜鍊煎皢澶氫釜琛ㄧ殑鏁版嵁瀛樺偍鍦ㄥ悓涓€涓狾racle鍧椾腑锛岃繖鏃舵绱竴缁凮racle鍧楀氨鍚屾椂寰楀埌涓や釜琛ㄧ殑鏁版嵁锛岃繖鏍峰氨鍙互鍑忓皯闇€瑕佸瓨鍌ㄧ殑Oracle鍧楋紝 浠庤€屾彁楂樺簲鐢ㄧ▼搴忕殑鎬ц兘銆?/p>

3銆佷紭鍖栬缃殑绱㈠紩锛屽氨蹇呴』鍏呭垎鍒╃敤鎵嶈兘鍔犲揩鏁版嵁搴撹闂€熷害銆侽RACLE瑕佷娇鐢ㄤ竴涓储寮曪紝 鏈変竴浜涙渶鍩烘湰鐨勬潯浠讹細1锛夈€亀here瀛愬悕涓殑杩欎釜瀛楁锛屽繀椤绘槸澶嶅悎绱㈠紩鐨勭涓€涓瓧娈碉紱2锛夈€亀here瀛愬悕涓殑杩欎釜瀛楁锛屼笉搴旇鍙備笌浠讳綍褰㈠紡鐨勮绠椼€?Sal*(2*90/100)

鍏€佸CPU鍜屽苟琛屾煡璇QO(Parallel Query Option)鏂瑰紡鐨勫埄鐢?/p>

1銆佸敖閲忓埄鐢ㄥ涓狢PU澶勭悊鍣ㄦ潵鎵ц浜嬪姟澶勭悊鍜屾煡璇?/p>

CPU鐨勫揩閫熷彂灞曚娇寰桹RACLE瓒婃潵瓒婇噸瑙嗗澶欳PU鐨勫苟琛屾妧鏈殑搴旂敤锛屼竴涓暟鎹簱鐨勮闂伐浣滃彲浠ョ敤澶氫釜CPU鐩镐簰閰嶅悎鏉ュ畬鎴愶紝鍔犱笂鍒嗗竷寮忚绠楀凡缁忕浉 褰撴櫘閬嶏紝鍙鍙兘锛屽簲璇ュ皢鏁版嵁搴撴湇鍔″櫒鍜屽簲鐢ㄧ▼搴忕殑CPU璇锋眰鍒嗗紑锛屾垨灏咰PU璇锋眰浠庝竴涓湇鍔″櫒绉诲埌鍙︿竴涓湇鍔″櫒銆傚浜庡CPU绯荤粺灏介噺閲囩敤 Parallel Query Option(PQO,骞惰鏌ヨ閫夐」)鏂瑰紡杩涜鏁版嵁搴撴搷浣溿€?/p>

2銆佷娇鐢≒arallel Query Option(PQO,骞惰鏌ヨ閫夋嫨)鏂瑰紡杩涜鏁版嵁鏌ヨ

浣跨敤PQO鏂瑰紡涓嶄粎鍙互鍦ㄥ涓狢PU闂村垎閰峉QL璇彞鐨勮姹傚鐞嗭紝褰撴墍鏌ヨ鐨勬暟鎹浜庝笉鍚岀殑纾佺洏鏃讹紝涓€涓釜鐙珛鐨勮繘绋嬪彲浠ュ悓鏃惰繘琛屾暟鎹鍙栥€?/p>

3銆佷娇鐢⊿QL*Loader Direct Path閫夐」杩涜澶ч噺鏁版嵁瑁呰浇

浣跨敤璇ユ柟娉曡繘琛屾暟鎹杞芥椂锛岀▼搴忓垱寤烘牸寮忓寲鏁版嵁鍧楃洿鎺ュ啓鍏ユ暟鎹枃浠朵腑锛屼笉瑕佹眰鏁版嵁搴撳唴鏍哥殑鍏朵粬I/O銆?/p>

涓冦€佸疄鏂界郴缁熻祫婧愮鐞嗗垎閰嶈鍒?/p>

ORACLE鎻愪緵浜咲atabase Resource Manager锛圖RM,鏁版嵁搴撹祫婧愮鐞嗗櫒锛夋潵鎺у埗鐢ㄦ埛鐨勮祫婧愬垎閰嶏紝DBA鍙互鐢ㄥ畠鍒嗛厤鐢ㄦ埛绫诲拰浣滀笟绫荤殑绯荤粺璧勬簮鐧惧垎姣斻€傚湪涓€涓狾LDP绯荤粺涓紝鍙粰鑱旀満 鐢ㄦ埛鍒嗛厤75%鐨凜PU璧勬簮锛屽墿涓嬬殑25%鐣欑粰鎵圭敤鎴枫€傚彟澶栵紝杩樺彲浠ヨ繘琛孋PU鐨勫绾у垎閰嶃€傞櫎浜嗚繘琛孋PU璧勬簮鍒嗛厤澶栵紝DRM杩樺彲浠ュ璧勬簮鐢ㄦ埛缁勬墽琛屽苟琛?鎿嶄綔鐨勯檺鍒躲€?/p>

鍏€佷娇鐢ㄦ渶鍜孲QL浼樺寲鏂逛紭鐨勬暟鎹簱杩炴帴妗?/p>

1銆佷娇鐢ㄧ洿鎺ョ殑OLE DB鏁版嵁搴撹繛鎺ユ柟寮忋€?/p>

閫氳繃ADO鍙互浣跨敤涓ょ鏂瑰紡杩炴帴鏁版嵁搴擄紝涓€绉嶆槸浼犵粺鐨凮DBC鏂瑰紡锛屼竴绉嶆槸OLE DB鏂瑰紡銆侫DO鏄缓绔嬪湪OLE DB鎶€鏈笂鐨勶紝涓轰簡鏀寔ODBC锛屽繀椤诲缓绔嬬浉搴旂殑OLE DB鍒癘DBC鐨勮皟鐢ㄨ浆鎹紝鑰屼娇鐢ㄧ洿鎺ョ殑OLE DB鏂瑰紡鍒欎笉闇€杞崲锛屼粠鑰屾彁楂樺鐞嗛€熷害銆?/p>

2銆佷娇鐢–onnection Pool鏈哄埗

鍦ㄦ暟鎹簱澶勭悊涓紝璧勬簮鑺遍攢鏈€澶х殑鏄缓绔嬫暟鎹簱杩炴帴锛岃€屼笖鐢ㄦ埛杩樹細鏈変竴涓緝闀跨殑杩炴帴绛夊緟鏃堕棿銆傝В鍐崇殑鍔炴硶灏辨槸澶嶇敤鐜版湁鐨凜onnection锛屼篃灏辨槸浣跨敤Connection Pool瀵硅薄鏈哄埗銆?/p>

Connection Pool鐨勫師鐞嗘槸锛欼IS+ASP浣撶郴涓淮鎸佷簡涓€涓繛鎺ョ紦鍐叉睜锛岃繖鏍凤紝褰撲笅涓€涓敤鎴疯闂椂锛岀洿鎺ュ湪杩炴帴缂撳啿姹犱腑鍙栧緱涓€涓暟鎹簱杩炴帴锛岃€屼笉闇€閲嶆柊杩炴帴鏁版嵁搴擄紝鍥犳鍙互澶уぇ鍦版彁楂樼郴缁熺殑鍝嶅簲閫熷害銆?/p>

3銆侀珮鏁堝湴杩涜SQL璇彞璁捐

閫氬父鎯呭喌涓嬶紝鍙互閲囩敤涓嬮潰鐨勬柟娉曚紭鍖朣QL瀵规暟鎹搷浣滅殑琛ㄧ幇锛?/p>

锛?锛夊噺灏戝鏁版嵁搴撶殑鏌ヨ娆℃暟锛屽嵆鍑忓皯瀵圭郴缁熻祫婧愮殑璇锋眰锛屼娇鐢ㄥ揩鐓у拰鏄惧舰鍥剧瓑鍒嗗竷寮忔暟鎹簱瀵硅薄鍙互鍑忓皯瀵规暟鎹簱鐨勬煡璇㈡鏁般€?/p>

锛?锛夊敖閲忎娇鐢ㄧ浉鍚岀殑鎴栭潪甯哥被浼肩殑SQL璇彞杩涜鏌ヨ锛岃繖鏍蜂笉浠呭厖鍒嗗埄鐢⊿QL鍏变韩姹犱腑鐨勫凡缁忓垎鏋愮殑璇硶鏍戯紝瑕佹煡璇㈢殑鏁版嵁鍦⊿GA涓懡涓殑鍙兘鎬т篃浼氬ぇ澶у鍔犮€?/p>

锛?锛夐檺鍒跺姩鎬丼QL鐨勪娇鐢紝铏界劧鍔ㄦ€丼QL寰堝ソ鐢紝浣嗘槸鍗充娇鍦⊿QL鍏变韩姹犱腑鏈変竴涓畬鍏ㄧ浉鍚岀殑鏌ヨ鍊硷紝鍔ㄦ€丼QL涔熶細閲嶆柊杩涜璇硶鍒嗘瀽銆?/p>

锛?锛夐伩鍏嶄笉甯︿换浣曟潯浠剁殑SQL璇彞鐨勬墽琛屻€傛病鏈変换浣曟潯浠剁殑SQL璇彞鍦ㄦ墽琛屾椂锛岄€氬父瑕佽繘琛孎TS锛屾暟鎹簱鍏堝畾浣嶄竴涓暟鎹潡锛岀劧鍚庢寜椤哄簭渚濇鏌ユ壘鍏跺畠鏁版嵁锛屽浜庡ぇ鍨嬭〃杩欏皢鏄竴涓极闀跨殑杩囩▼銆?/p>

锛?锛夊鏋滃鏈変簺琛ㄤ腑鐨勬暟鎹湁绾︽潫锛屾渶濂藉湪寤鸿〃鐨凷QL璇彞鐢ㄦ弿杩板畬鏁存€ф潵瀹炵幇锛岃€屼笉鏄敤SQL绋嬪簭涓疄鐜般€?/p>

锛?锛夊彲浠ラ€氳繃鍙栨秷鑷姩鎻愪氦妯″紡锛屽皢SQL璇彞姹囬泦涓€缁勬墽琛屽悗闆嗕腑鎻愪氦锛岀▼搴忚繕鍙互閫氳繃鏄惧紡鍦扮敤COMMIT鍜孯OLLBACL杩涜鎻愪氦鍜屽洖婊氳浜嬪姟銆?/p>

锛?锛夋绱㈠ぇ閲忔暟鎹椂璐规椂寰堥暱锛岃缃棰勫彇鏁板垯鑳芥敼鍠勭郴缁熺殑宸ヤ綔琛ㄧ幇锛岃缃竴涓渶澶у€硷紝褰揝QL璇彞杩斿洖琛岃秴杩囪鍊硷紝鏁板€煎簱鏆傛椂鍋滄鎵ц锛岄櫎闈炵敤鎴峰彂鍑烘柊鐨勬寚浠わ紝寮€濮嬬粍缁囧苟鏄剧ず鏁版嵁锛岃€屼笉鏄鐢ㄦ埛缁х画绛夊緟銆?/p>

涔濄€佸厖鍒嗗埄鐢ㄦ暟鎹殑鍚庡彴澶勭悊鏂规鍑忓皯缃戠粶娴侀噺

1銆佸悎鐞嗗垱寤轰复鏃惰〃鎴栬鍥?/p>

鎵€璋撳垱寤轰复鏃惰〃鎴栬鍥撅紝灏辨槸鏍规嵁闇€瑕佸湪鏁版嵁搴撳熀纭€涓婂垱寤烘柊琛ㄦ垨瑙嗗浘锛屽浜庡琛ㄥ叧鑱斿悗鍐嶆煡璇俊鎭殑鍙缓鏂拌〃锛屽浜庡崟琛ㄦ煡璇㈢殑鍙垱寤鸿鍥撅紝杩欐牱鍙厖鍒嗗埄鐢ㄦ暟 鎹簱鐨勫閲忓ぇ銆佸彲鎵╁厖鎬у己绛夌壒鐐癸紝鎵€鏈夋潯浠剁殑鍒ゆ柇銆佹暟鍊艰绠楃粺璁″潎鍙湪鏁版嵁搴撴湇鍔″櫒鍚庡彴缁熶竴澶勭悊鍚庤拷鍔犲埌涓存椂琛ㄤ腑锛屽舰鎴愭暟鎹粨鏋滅殑杩囩▼鍙敤鏁版嵁搴撶殑杩囩▼鎴?鍑芥暟鏉ュ疄鐜般€?/p>

2銆佹暟鎹簱鎵撳寘鎶€鏈殑鍏呭垎鍒╃敤

鍒╃敤鏁版嵁搴撴弿杩拌瑷€缂栧啓鏁版嵁搴撶殑杩囩▼鎴栧嚱鏁帮紝鐒跺悗鎶婅繃绋嬫垨鍑芥暟鎵撴垚鍖呭湪鏁版嵁搴撳悗鍙扮粺涓€杩愯鍖呭嵆鍙€?/p>

3銆佹暟鎹鍒躲€佸揩鐓с€佽鍥撅紝杩滅▼杩囩▼璋冪敤鎶€鏈殑杩愮敤

鏁版嵁澶嶅埗锛屽嵆灏嗘暟鎹竴娆″鍒跺埌鏈湴锛岃繖鏍蜂互鍚庣殑鏌ヨ灏变娇鐢ㄦ湰鍦版暟鎹紝浣嗘槸鍙€傚悎閭ｄ簺鍙樺寲涓嶅ぇ鐨勬暟鎹€備娇鐢ㄥ揩鐓т篃鍙互鍦ㄥ垎甯冨紡鏁版嵁搴撲箣闂村姩鎬佸鍒舵暟鎹紝瀹氫箟 蹇収鐨勮嚜鍔ㄥ埛鏂版椂闂存垨鎵嬪伐鍒锋柊锛屼互淇濊瘉鏁版嵁鐨勫紩鐢ㄥ弬鐓у畬鏁存€с€傝皟鐢ㄨ繙绋嬭繃绋嬩篃浼氬ぇ澶у噺灏戝洜棰戠箒鐨凷QL璇彞璋冪敤鑰屽甫鏉ョ殑缃戠粶鎷ユ尋銆?/p>

鎬讳箣锛屽鎵€鏈夌殑鎬ц兘闂锛屾病鏈変竴涓粺涓€鐨勮В鍐虫柟娉曪紝浣哋RACLE鎻愪緵浜嗕赴瀵岀殑閫夋嫨鐜锛屽彲浠ヤ粠ORACLE鏁版嵁搴撶殑浣撶郴缁撴瀯銆佽蒋浠剁粨鏋勩€佹ā寮忓璞?浠ュ強鍏蜂綋鐨勪笟鍔″拰鎶€鏈疄鐜板嚭鍙戯紝杩涜缁熺鑰冭檻銆傛彁楂樼郴缁熸€ц兘闇€瑕佷竴绉嶇郴缁熺殑鏁翠綋鐨勬柟娉曪紝鍦ㄥ鏁版嵁搴撹繘琛屼紭鍖栨椂锛屽簲瀵瑰簲鐢ㄧ▼搴忋€両/O瀛愮郴缁熷拰鎿嶄綔绯荤粺 锛圤S锛夎繘琛岀浉搴旂殑浼樺寲銆備紭鍖栨槸鏈夌洰鐨勫湴鏇存敼绯荤粺鐨勪竴涓垨澶氫釜缁勪欢锛屼娇鍏舵弧瓒充竴涓垨澶氫釜鐩爣鐨勮繃绋嬨€傚Oracle鏉ヨ锛屼紭鍖栨槸杩涜鏈夌洰鐨勭殑璋冩暣缁勪欢绾т互 鏀瑰杽鎬ц兘锛屽嵆澧炲姞鍚炲悙閲忥紝鍑忓皯鍝嶅簲鏃堕棿銆傚鏋淒BA鑳戒粠涓婅堪涔濅釜鏂归潰缁煎悎鑰冭檻浼樺寲鏂规锛岀浉淇″鏁癘RACLE搴旂敤鍙互鍋氬埌鎸夋渶浼樼殑鏂瑰紡鏉ュ瓨鍙栨暟鎹€?/p>

聽

聽

鎴戜滑瑕佸仛鍒颁笉浣嗕細鍐橲QL,杩樿鍋氬埌鍐欏嚭鎬ц兘浼樿壇鐨凷QL,浠ヤ笅涓虹瑪鑰呭涔犮€佹憳褰曘€佸苟姹囨€婚儴鍒嗚祫鏂欎笌澶у鍒嗕韩锛?/p>

锛?锛?閫夋嫨鏈€鏈夋晥鐜囩殑琛ㄥ悕椤哄簭(鍙湪鍩轰簬瑙勫垯鐨勪紭鍖栧櫒涓湁鏁?锛?/p>

ORACLE鐨勮В鏋愬櫒鎸夌収浠庡彸鍒板乏鐨勯『搴忓鐞咶ROM瀛愬彞涓殑琛ㄥ悕锛孎ROM瀛愬彞涓啓鍦ㄦ渶鍚庣殑琛?鍩虹琛?driving table)灏嗚鏈€鍏堝鐞嗭紝鍦‵ROM瀛愬彞涓寘鍚涓〃鐨勬儏鍐典笅,浣犲繀椤婚€夋嫨璁板綍鏉℃暟鏈€灏戠殑琛ㄤ綔涓哄熀纭€琛ㄣ€?/p>

濡傛灉鏈?涓互涓婄殑琛ㄨ繛鎺ユ煡璇? 閭ｅ氨闇€瑕侀€夋嫨浜ゅ弶琛?intersection table)浣滀负鍩虹琛? 浜ゅ弶琛ㄦ槸鎸囬偅涓鍏朵粬琛ㄦ墍寮曠敤鐨勮〃.鎶婃暟鎹皯鐨勮〃鏀惧湪FROM鍚庨潰鐨勬渶鍚?/p>

锛?锛?WHERE瀛愬彞涓殑杩炴帴椤哄簭锛庯細

ORACLE閲囩敤鑷笅鑰屼笂鐨勯『搴忚В鏋怶HERE瀛愬彞,鏍规嵁杩欎釜鍘熺悊,琛ㄤ箣闂寸殑杩炴帴蹇呴』鍐欏湪鍏朵粬WHERE鏉′欢涔嬪墠, 閭ｄ簺鍙互杩囨护鎺夋渶澶ф暟閲忚褰曠殑鏉′欢蹇呴』鍐欏湪WHERE瀛愬彞鐨勬湯灏?

锛?锛?SELECT瀛愬彞涓伩鍏嶄娇鐢?鈥?* 鈥橈細

ORACLE鍦ㄨВ鏋愮殑杩囩▼涓? 浼氬皢'*' 渚濇杞崲鎴愭墍鏈夌殑鍒楀悕, 杩欎釜宸ヤ綔鏄€氳繃鏌ヨ鏁版嵁瀛楀吀瀹屾垚鐨? 杩欐剰鍛崇潃灏嗚€楄垂鏇村鐨勬椂闂?/p>

锛?锛?鍑忓皯璁块棶鏁版嵁搴撶殑娆℃暟锛?/p>

ORACLE鍦ㄥ唴閮ㄦ墽琛屼簡璁稿宸ヤ綔: 瑙ｆ瀽SQL璇彞, 浼扮畻绱㈠紩鐨勫埄鐢ㄧ巼, 缁戝畾鍙橀噺 , 璇绘暟鎹潡绛夛紱

锛?锛?鍦⊿QL*Plus , SQL*Forms鍜孭ro*C涓噸鏂拌缃瓵RRAYSIZE鍙傛暟, 鍙互澧炲姞姣忔鏁版嵁搴撹闂殑妫€绱㈡暟鎹噺 ,寤鸿鍊间负200

锛?锛?浣跨敤DECODE鍑芥暟鏉ュ噺灏戝鐞嗘椂闂达細******************************

浣跨敤DECODE鍑芥暟鍙互閬垮厤閲嶅鎵弿鐩稿悓璁板綍鎴栭噸澶嶈繛鎺ョ浉鍚岀殑琛?

聽

decode (expression, search_1, result_1)

decode (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

聽

decode (expression, search_1, result_1, default)

decode (expression, search_1, result_1, search_2, result_2, default)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

decode鍑芥暟姣旇緝琛ㄨ揪寮忓拰鎼滅储瀛楋紝濡傛灉鍖归厤锛岃繑鍥炵粨鏋滐紱濡傛灉涓嶅尮閰嶏紝杩斿洖default鍊硷紱濡傛灉鏈畾涔塪efault鍊硷紝鍒欒繑鍥炵┖鍊笺€?/p>

浠ヤ笅鏄竴涓畝鍗曟祴璇曪紝鐢ㄤ簬璇存槑Decode鍑芥暟鐨勭敤娉?

聽

SQL> create table t as select username,default_tablespace,lock_date from dba_users;

聽

Table created.

聽

SQL> select * from t;

USERNAME DEFAULT_TABLESPACE LOCK_DATE

------------------------------ ------------------------------ ---------

SYS SYSTEM

SYSTEM SYSTEM

OUTLN SYSTEM

CSMIG SYSTEM

SCOTT SYSTEM

EYGLE USERS

DBSNMP SYSTEM

WMSYS SYSTEM 20-OCT-04

聽

8 rows selected.

聽

聽

SQL> select username,decode(lock_date,null,'unlocked','locked') status from t;

聽

USERNAME STATUS

------------------------------ --------

SYS unlocked

SYSTEM unlocked

OUTLN unlocked

CSMIG unlocked

SCOTT unlocked

EYGLE unlocked

DBSNMP unlocked

WMSYS locked

聽

8 rows selected.

聽

SQL> select username,decode(lock_date,null,'unlocked') status from t;

聽

USERNAME STATUS

------------------------------ --------

SYS unlocked

SYSTEM unlocked

OUTLN unlocked

CSMIG unlocked

SCOTT unlocked

EYGLE unlocked

DBSNMP unlocked

WMSYS

8 rows selected.

锛?锛?鏁村悎绠€鍗?鏃犲叧鑱旂殑鏁版嵁搴撹闂細

濡傛灉浣犳湁鍑犱釜绠€鍗曠殑鏁版嵁搴撴煡璇㈣鍙?浣犲彲浠ユ妸瀹冧滑鏁村悎鍒颁竴涓煡璇腑(鍗充娇瀹冧滑涔嬮棿娌℃湁鍏崇郴)

锛?锛?鍒犻櫎閲嶅璁板綍锛?/p>

鏈€楂樻晥鐨勫垹闄ら噸澶嶈褰曟柟娉?( 鍥犱负浣跨敤浜哛OWID)渚嬪瓙锛?/p>

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)

FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

锛?锛?鐢═RUNCATE鏇夸唬DELETE锛?/p>

褰撳垹闄よ〃涓殑璁板綍鏃?鍦ㄩ€氬父鎯呭喌涓? 鍥炴粴娈?rollback segments ) 鐢ㄦ潵瀛樻斁鍙互琚仮澶嶇殑淇℃伅. 濡傛灉浣犳病鏈塁OMMIT浜嬪姟,ORACLE浼氬皢鏁版嵁鎭㈠鍒板垹闄や箣鍓嶇殑鐘舵€?鍑嗙‘鍦拌鏄仮澶嶅埌鎵ц鍒犻櫎鍛戒护涔嬪墠鐨勭姸鍐? 鑰屽綋杩愮敤TRUNCATE鏃? 鍥炴粴娈典笉鍐嶅瓨鏀句换浣曞彲琚仮澶嶇殑淇℃伅.褰撳懡浠よ繍琛屽悗,鏁版嵁涓嶈兘琚仮澶?鍥犳寰堝皯鐨勮祫婧愯璋冪敤,鎵ц鏃堕棿涔熶細寰堢煭. (璇戣€呮寜: TRUNCATE鍙湪鍒犻櫎鍏ㄨ〃閫傜敤,TRUNCATE鏄疍DL涓嶆槸DML)

锛?0锛?灏介噺澶氫娇鐢–OMMIT锛?/p>

鍙鏈夊彲鑳?鍦ㄧ▼搴忎腑灏介噺澶氫娇鐢–OMMIT, 杩欐牱绋嬪簭鐨勬€ц兘寰楀埌鎻愰珮,闇€姹備篃浼氬洜涓篊OMMIT鎵€閲婃斁鐨勮祫婧愯€屽噺灏?

COMMIT鎵€閲婃斁鐨勮祫婧?

a. 鍥炴粴娈典笂鐢ㄤ簬鎭㈠鏁版嵁鐨勪俊鎭?

b. 琚▼搴忚鍙ヨ幏寰楃殑閿?/p>

c. redo log buffer 涓殑绌洪棿

d. ORACLE涓虹鐞嗕笂杩?绉嶈祫婧愪腑鐨勫唴閮ㄨ姳璐?/p>

锛?1锛?鐢╓here瀛愬彞鏇挎崲HAVING瀛愬彞锛?/p>

閬垮厤浣跨敤HAVING瀛愬彞, HAVING 鍙細鍦ㄦ绱㈠嚭鎵€鏈夎褰曚箣鍚庢墠瀵圭粨鏋滈泦杩涜杩囨护. 杩欎釜澶勭悊闇€瑕佹帓搴?鎬昏绛夋搷浣? 濡傛灉鑳介€氳繃WHERE瀛愬彞闄愬埗璁板綍鐨勬暟鐩?閭ｅ氨鑳藉噺灏戣繖鏂归潰鐨勫紑閿€. (闈瀘racle涓?on銆亀here銆乭aving杩欎笁涓兘鍙互鍔犳潯浠剁殑瀛愬彞涓紝on鏄渶鍏堟墽琛岋紝where娆′箣锛宧aving鏈€鍚庯紝鍥犱负on鏄厛鎶婁笉 绗﹀悎鏉′欢鐨勮褰曡繃婊ゅ悗鎵嶈繘琛岀粺璁★紝瀹冨氨鍙互鍑忓皯涓棿杩愮畻瑕佸鐞嗙殑鏁版嵁锛屾寜鐞嗚搴旇閫熷害鏄渶蹇殑锛寃here涔熷簲璇ユ瘮having蹇偣鐨勶紝鍥犱负瀹冭繃婊ゆ暟鎹悗 鎵嶈繘琛宻um锛屽湪涓や釜琛ㄨ仈鎺ユ椂鎵嶇敤on鐨勶紝鎵€浠ュ湪涓€涓〃鐨勬椂鍊欙紝灏卞墿涓媤here璺焗aving姣旇緝浜嗐€傚湪杩欏崟琛ㄦ煡璇㈢粺璁＄殑鎯呭喌涓嬶紝濡傛灉瑕佽繃婊ょ殑鏉′欢娌℃湁 娑夊強鍒拌璁＄畻瀛楁锛岄偅瀹冧滑鐨勭粨鏋滄槸涓€鏍风殑锛屽彧鏄痺here鍙互浣跨敤rushmore鎶€鏈紝鑰宧aving灏变笉鑳斤紝鍦ㄩ€熷害涓婂悗鑰呰鎱㈠鏋滆娑夊強鍒拌绠楃殑瀛?娈碉紝灏辫〃绀哄湪娌¤绠椾箣鍓嶏紝杩欎釜瀛楁鐨勫€兼槸涓嶇‘瀹氱殑锛屾牴鎹笂绡囧啓鐨勫伐浣滄祦绋嬶紝where鐨勪綔鐢ㄦ椂闂存槸鍦ㄨ绠椾箣鍓嶅氨瀹屾垚鐨勶紝鑰宧aving灏辨槸鍦ㄨ绠楀悗鎵嶈捣浣?鐢ㄧ殑锛屾墍浠ュ湪杩欑鎯呭喌涓嬶紝涓よ€呯殑缁撴灉浼氫笉鍚屻€傚湪澶氳〃鑱旀帴鏌ヨ鏃讹紝on姣攚here鏇存棭璧蜂綔鐢ㄣ€傜郴缁熼鍏堟牴鎹悇涓〃涔嬮棿鐨勮仈鎺ユ潯浠讹紝鎶婂涓〃鍚堟垚涓€涓复鏃惰〃 鍚庯紝鍐嶇敱where杩涜杩囨护锛岀劧鍚庡啀璁＄畻锛岃绠楀畬鍚庡啀鐢県aving杩涜杩囨护銆傜敱姝ゅ彲瑙侊紝瑕佹兂杩囨护鏉′欢璧峰埌姝ｇ‘鐨勪綔鐢紝棣栧厛瑕佹槑鐧借繖涓潯浠跺簲璇ュ湪浠€涔堟椂鍊?璧蜂綔鐢紝鐒跺悗鍐嶅喅瀹氭斁鍦ㄩ偅閲?/p>

锛?2锛?鍑忓皯瀵硅〃鐨勬煡璇細

鍦ㄥ惈鏈夊瓙鏌ヨ鐨凷QL璇彞涓?瑕佺壒鍒敞鎰忓噺灏戝琛ㄧ殑鏌ヨ.渚嬪瓙锛?/p>

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT

TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

锛?3锛?閫氳繃鍐呴儴鍑芥暟鎻愰珮SQL鏁堢巼.锛?/p>

澶嶆潅鐨凷QL寰€寰€鐗虹壊浜嗘墽琛屾晥鐜? 鑳藉鎺屾彙涓婇潰鐨勮繍鐢ㄥ嚱鏁拌В鍐抽棶棰樼殑鏂规硶鍦ㄥ疄闄呭伐浣滀腑鏄潪甯告湁鎰忎箟鐨?/p>

锛?4锛?浣跨敤琛ㄧ殑鍒悕(Alias)锛?/p>

褰撳湪SQL璇彞涓繛鎺ュ涓〃鏃? 璇蜂娇鐢ㄨ〃鐨勫埆鍚嶅苟鎶婂埆鍚嶅墠缂€浜庢瘡涓狢olumn涓?杩欐牱涓€鏉?灏卞彲浠ュ噺灏戣В鏋愮殑鏃堕棿骞跺噺灏戦偅浜涚敱Column姝т箟寮曡捣鐨勮娉曢敊璇?

锛?5锛?鐢‥XISTS鏇夸唬IN銆佺敤NOT EXISTS鏇夸唬NOT IN锛?/p>

鍦ㄨ澶氬熀浜庡熀纭€琛ㄧ殑鏌ヨ涓?涓轰簡婊¤冻涓€涓潯浠?寰€寰€闇€瑕佸鍙︿竴涓〃杩涜鑱旀帴.鍦ㄨ繖绉嶆儏鍐典笅, 浣跨敤EXISTS(鎴朜OT EXISTS)閫氬父灏嗘彁楂樻煡璇㈢殑鏁堢巼. 鍦ㄥ瓙鏌ヨ涓?NOT IN瀛愬彞灏嗘墽琛屼竴涓唴閮ㄧ殑鎺掑簭鍜屽悎骞? 鏃犺鍦ㄥ摢绉嶆儏鍐典笅,NOT IN閮芥槸鏈€浣庢晥鐨?(鍥犱负瀹冨瀛愭煡璇腑鐨勮〃鎵ц浜嗕竴涓叏琛ㄩ亶鍘?. 涓轰簡閬垮厤浣跨敤NOT IN ,鎴戜滑鍙互鎶婂畠鏀瑰啓鎴愬杩炴帴(Outer Joins)鎴朜OT EXISTS.

渚嬪瓙锛?/p>

锛堥珮鏁堬級SELECT * FROM EMP (鍩虹琛? WHERE EMPNO > 0 AND EXISTS (SELECT 1 FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 鈥楳ELB')

(浣庢晥)SELECT * FROM EMP (鍩虹琛? WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 鈥楳ELB')

锛?6锛?璇嗗埆'浣庢晥鎵ц'鐨凷QL璇彞锛?/p>

铏界劧鐩墠鍚勭鍏充簬SQL浼樺寲鐨勫浘褰㈠寲宸ュ叿灞傚嚭涓嶇┓,浣嗘槸鍐欏嚭鑷繁鐨凷QL宸ュ叿鏉ヨВ鍐抽棶棰樺缁堟槸涓€涓渶濂界殑鏂规硶锛?/p>

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,

ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,

ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,

SQL_TEXT

FROM V$SQLAREA

WHERE EXECUTIONS>0

AND BUFFER_GETS > 0

AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

ORDER BY 4 DESC;

锛?7锛?鐢ㄧ储寮曟彁楂樻晥鐜囷細

绱㈠紩鏄〃鐨勪竴涓蹇甸儴鍒?鐢ㄦ潵鎻愰珮妫€绱㈡暟鎹殑鏁堢巼锛孫RACLE浣跨敤浜嗕竴涓鏉傜殑鑷钩琛- tree缁撴瀯. 閫氬父,閫氳繃绱㈠紩鏌ヨ鏁版嵁姣斿叏琛ㄦ壂鎻忚蹇? 褰揙RACLE鎵惧嚭鎵ц鏌ヨ鍜孶pdate璇彞鐨勬渶浣宠矾寰勬椂, ORACLE浼樺寲鍣ㄥ皢浣跨敤绱㈠紩. 鍚屾牱鍦ㄨ仈缁撳涓〃鏃朵娇鐢ㄧ储寮曚篃鍙互鎻愰珮鏁堢巼. 鍙︿竴涓娇鐢ㄧ储寮曠殑濂藉鏄?瀹冩彁渚涗簡涓婚敭(primary key)鐨勫敮涓€鎬ч獙璇?銆傞偅浜汱ONG鎴朙ONG RAW鏁版嵁绫诲瀷, 浣犲彲浠ョ储寮曞嚑涔庢墍鏈夌殑鍒? 閫氬父, 鍦ㄥぇ鍨嬭〃涓娇鐢ㄧ储寮曠壒鍒湁鏁? 褰撶劧,浣犱篃浼氬彂鐜? 鍦ㄦ壂鎻忓皬琛ㄦ椂,浣跨敤绱㈠紩鍚屾牱鑳芥彁楂樻晥鐜? 铏界劧浣跨敤绱㈠紩鑳藉緱鍒版煡璇㈡晥鐜囩殑鎻愰珮,浣嗘槸鎴戜滑涔熷繀椤绘敞鎰忓埌瀹冪殑浠ｄ环. 绱㈠紩闇€瑕佺┖闂存潵瀛樺偍,涔熼渶瑕佸畾鏈熺淮鎶? 姣忓綋鏈夎褰曞湪琛ㄤ腑澧炲噺鎴栫储寮曞垪琚慨鏀规椂, 绱㈠紩鏈韩涔熶細琚慨鏀? 杩欐剰鍛崇潃姣忔潯璁板綍鐨処NSERT , DELETE , UPDATE灏嗕负姝ゅ浠樺嚭4 , 5 娆＄殑纾佺洏I/O . 鍥犱负绱㈠紩闇€瑕侀澶栫殑瀛樺偍绌洪棿鍜屽鐞?閭ｄ簺涓嶅繀瑕佺殑绱㈠紩鍙嶈€屼細浣挎煡璇㈠弽搴旀椂闂村彉鎱?銆傚畾鏈熺殑閲嶆瀯绱㈠紩鏄湁蹇呰鐨?锛氬湪鈥滅郴缁熺淮鎶ゆ竻鐞嗏€濋噷鏈変釜鈥滃瀮鍦炬枃浠舵竻 鐞嗏€?/p>

ALTER INDEX

锛?8锛?鐢‥XISTS鏇挎崲DISTINCT锛?/p>

褰撴彁浜や竴涓寘鍚竴瀵瑰琛ㄤ俊鎭?姣斿閮ㄩ棬琛ㄥ拰闆囧憳琛?鐨勬煡璇㈡椂,閬垮厤鍦⊿ELECT瀛愬彞涓娇鐢―ISTINCT. 涓€鑸彲浠ヨ€冭檻鐢‥XIST鏇挎崲, EXISTS 浣挎煡璇㈡洿涓鸿繀閫?鍥犱负RDBMS鏍稿績妯″潡灏嗗湪瀛愭煡璇㈢殑鏉′欢涓€鏃︽弧瓒冲悗,绔嬪埢杩斿洖缁撴灉. 渚嬪瓙锛?/p>

(浣庢晥):

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E

WHERE D.DEPT_NO = E.DEPT_NO

(楂樻晥):

SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 鈥榅'

FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

锛?9锛?sql璇彞鐢ㄥぇ鍐欑殑锛涘洜涓簅racle鎬绘槸鍏堣В鏋恠ql璇彞锛屾妸灏忓啓鐨勫瓧姣嶈浆鎹㈡垚澶у啓鐨勫啀鎵ц

锛?0锛?鍦╦ava浠ｇ爜涓敖閲忓皯鐢ㄨ繛鎺ョ鈥滐紜鈥濊繛鎺ュ瓧绗︿覆锛?/p>

锛?1锛?閬垮厤鍦ㄧ储寮曞垪涓婁娇鐢∟OT 閫氬父锛屻€€

鎴戜滑瑕侀伩鍏嶅湪绱㈠紩鍒椾笂浣跨敤NOT, NOT浼氫骇鐢熷湪鍜屽湪绱㈠紩鍒椾笂浣跨敤鍑芥暟鐩稿悓鐨勫奖鍝? 褰揙RACLE鈥濋亣鍒扳€漀OT,浠栧氨浼氬仠姝娇鐢ㄧ储寮曡浆鑰屾墽琛屽叏琛ㄦ壂鎻?

锛?2锛?閬垮厤鍦ㄧ储寮曞垪涓婁娇鐢ㄨ绠楋紟

WHERE瀛愬彞涓紝濡傛灉绱㈠紩鍒楁槸鍑芥暟鐨勪竴閮ㄥ垎锛庝紭鍖栧櫒灏嗕笉浣跨敤绱㈠紩鑰屼娇鐢ㄥ叏琛ㄦ壂鎻忥紟

涓句緥:

浣庢晥锛?/p>

SELECT 鈥?FROM DEPT WHERE SAL * 12 > 25000;

楂樻晥:

SELECT 鈥?FROM DEPT WHERE SAL > 25000/12;

锛?3锛?鐢?gt;=鏇夸唬>

楂樻晥:

SELECT * FROM EMP WHERE DEPTNO >=4

浣庢晥:

SELECT * FROM EMP WHERE DEPTNO >3

涓よ€呯殑鍖哄埆鍦ㄤ簬, 鍓嶈€匘BMS灏嗙洿鎺ヨ烦鍒扮涓€涓狣EPT绛変簬4鐨勮褰曡€屽悗鑰呭皢棣栧厛瀹氫綅鍒癉EPTNO=3鐨勮褰曞苟涓斿悜鍓嶆壂鎻忓埌绗竴涓狣EPT澶т簬3鐨勮褰?

锛?4锛?鐢║NION鏇挎崲OR (閫傜敤浜庣储寮曞垪)

閫氬父鎯呭喌涓? 鐢║NION鏇挎崲WHERE瀛愬彞涓殑OR灏嗕細璧峰埌杈冨ソ鐨勬晥鏋? 瀵圭储寮曞垪浣跨敤OR灏嗛€犳垚鍏ㄨ〃鎵弿. 娉ㄦ剰, 浠ヤ笂瑙勫垯鍙拡瀵瑰涓储寮曞垪鏈夋晥. 濡傛灉鏈塩olumn娌℃湁琚储寮? 鏌ヨ鏁堢巼鍙兘浼氬洜涓轰綘娌℃湁閫夋嫨OR鑰岄檷浣? 鍦ㄤ笅闈㈢殑渚嬪瓙涓? LOC_ID 鍜孯EGION涓婇兘寤烘湁绱㈠紩.

楂樻晥:

SELECT LOC_ID , LOC_DESC , REGION

FROM LOCATION

WHERE LOC_ID = 10

UNION

SELECT LOC_ID , LOC_DESC , REGION

FROM LOCATION

WHERE REGION = 鈥淢ELBOURNE鈥?/p>

浣庢晥:

SELECT LOC_ID , LOC_DESC , REGION

FROM LOCATION

WHERE LOC_ID = 10 OR REGION = 鈥淢ELBOURNE鈥?/p>

濡傛灉浣犲潥鎸佽鐢∣R, 閭ｅ氨闇€瑕佽繑鍥炶褰曟渶灏戠殑绱㈠紩鍒楀啓鍦ㄦ渶鍓嶉潰.

锛?5锛?鐢↖N鏉ユ浛鎹R

杩欐槸涓€鏉＄畝鍗曟槗璁扮殑瑙勫垯锛屼絾鏄疄闄呯殑鎵ц鏁堟灉杩橀』妫€楠岋紝鍦∣RACLE8i涓嬶紝涓よ€呯殑鎵ц璺緞浼间箮鏄浉鍚岀殑锛庛€€

浣庢晥:

SELECT鈥? FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30

楂樻晥

SELECT鈥?FROM LOCATION WHERE LOC_IN IN (10,20,30);

锛?6锛?閬垮厤鍦ㄧ储寮曞垪涓婁娇鐢↖S NULL鍜孖S NOT NULL

閬垮厤鍦ㄧ储寮曚腑浣跨敤浠讳綍鍙互涓虹┖鐨勫垪锛孫RACLE灏嗘棤娉曚娇鐢ㄨ绱㈠紩锛庡浜庡崟鍒楃储寮曪紝濡傛灉鍒楀寘 鍚┖鍊硷紝绱㈠紩涓皢涓嶅瓨鍦ㄦ璁板綍. 瀵逛簬澶嶅悎绱㈠紩锛屽鏋滄瘡涓垪閮戒负绌猴紝绱㈠紩涓悓鏍蜂笉瀛樺湪姝よ褰?銆€濡傛灉鑷冲皯鏈変竴涓垪涓嶄负绌猴紝鍒欒褰曞瓨鍦ㄤ簬绱㈠紩涓紟涓句緥: 濡傛灉鍞竴鎬х储寮曞缓绔嬪湪琛ㄧ殑A鍒楀拰B鍒椾笂, 骞朵笖琛ㄤ腑瀛樺湪涓€鏉¤褰曠殑A,B鍊间负(123,null) , ORACLE灏嗕笉鎺ュ彈涓嬩竴鏉″叿鏈夌浉鍚孉,B鍊硷紙123,null锛夌殑璁板綍(鎻掑叆). 鐒惰€屽鏋滄墍鏈夌殑绱㈠紩鍒楅兘涓虹┖锛孫RACLE灏嗚涓烘暣涓敭鍊间负绌鸿€岀┖涓嶇瓑浜庣┖. 鍥犳浣犲彲浠ユ彃鍏?000 鏉″叿鏈夌浉鍚岄敭鍊肩殑璁板綍,褰撶劧瀹冧滑閮芥槸绌? 鍥犱负绌哄€间笉瀛樺湪浜庣储寮曞垪涓?鎵€浠HERE瀛愬彞涓绱㈠紩鍒楄繘琛岀┖鍊兼瘮杈冨皢浣縊RACLE鍋滅敤璇ョ储寮?

浣庢晥: (绱㈠紩澶辨晥)

SELECT 鈥?FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

楂樻晥: (绱㈠紩鏈夋晥)

SELECT 鈥?FROM DEPARTMENT WHERE DEPT_CODE >=0;

锛?7锛?鎬绘槸浣跨敤绱㈠紩鐨勭涓€涓垪锛?/p>

濡傛灉绱㈠紩鏄缓绔嬪湪澶氫釜鍒椾笂, 鍙湁鍦ㄥ畠鐨勭涓€涓垪(leading column)琚玾here瀛愬彞寮曠敤鏃?浼樺寲鍣ㄦ墠浼氶€夋嫨浣跨敤璇ョ储寮? 杩欎篃鏄竴鏉＄畝鍗曡€岄噸瑕佺殑瑙勫垯锛屽綋浠呭紩鐢ㄧ储寮曠殑绗簩涓垪鏃?浼樺寲鍣ㄤ娇鐢ㄤ簡鍏ㄨ〃鎵弿鑰屽拷鐣ヤ簡绱㈠紩

锛?8锛?鐢║NION-ALL 鏇挎崲UNION ( 濡傛灉鏈夊彲鑳界殑璇?锛?/p>

褰揝QL璇彞闇€瑕乁NION涓や釜鏌ヨ缁撴灉闆嗗悎鏃?杩欎袱涓粨鏋滈泦鍚堜細浠NION-ALL鐨勬柟 寮忚鍚堝苟, 鐒跺悗鍦ㄨ緭鍑烘渶缁堢粨鏋滃墠杩涜鎺掑簭. 濡傛灉鐢║NION ALL鏇夸唬UNION, 杩欐牱鎺掑簭灏变笉鏄繀瑕佷簡. 鏁堢巼灏变細鍥犳寰楀埌鎻愰珮. 闇€瑕佹敞鎰忕殑鏄紝UNION ALL 灏嗛噸澶嶈緭鍑轰袱涓粨鏋滈泦鍚堜腑鐩稿悓璁板綍. 鍥犳鍚勪綅杩樻槸瑕佷粠涓氬姟闇€姹傚垎鏋愪娇鐢║NION ALL鐨勫彲琛屾€? UNION 灏嗗缁撴灉闆嗗悎鎺掑簭,杩欎釜鎿嶄綔浼氫娇鐢ㄥ埌SORT_AREA_SIZE杩欏潡鍐呭瓨. 瀵逛簬杩欏潡鍐呭瓨鐨勪紭鍖栦篃鏄浉褰撻噸瑕佺殑. 涓嬮潰鐨凷QL鍙互鐢ㄦ潵鏌ヨ鎺掑簭鐨勬秷鑰楅噺

浣庢晥锛?/p>

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = '31-DEC-95'

UNION

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = '31-DEC-95'

楂樻晥:

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = '31-DEC-95'

UNION ALL

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = '31-DEC-95'

锛?9锛?鐢╓HERE鏇夸唬ORDER BY锛?/p>

ORDER BY 瀛愬彞鍙湪涓ょ涓ユ牸鐨勬潯浠朵笅浣跨敤绱㈠紩.

ORDER BY涓墍鏈夌殑鍒楀繀椤诲寘鍚湪鐩稿悓鐨勭储寮曚腑骞朵繚鎸佸湪绱㈠紩涓殑鎺掑垪椤哄簭.

ORDER BY涓墍鏈夌殑鍒楀繀椤诲畾涔変负闈炵┖.

WHERE瀛愬彞浣跨敤鐨勭储寮曞拰ORDER BY瀛愬彞涓墍浣跨敤鐨勭储寮曚笉鑳藉苟鍒?

渚嬪:

琛―EPT鍖呭惈浠ヤ笅鍒?

DEPT_CODE PK NOT NULL

DEPT_DESC NOT NULL

DEPT_TYPE NULL

浣庢晥: (绱㈠紩涓嶈浣跨敤)

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE

楂樻晥: (浣跨敤绱㈠紩)

SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

锛?0锛?閬垮厤鏀瑰彉绱㈠紩鍒楃殑绫诲瀷:

褰撴瘮杈冧笉鍚屾暟鎹被鍨嬬殑鏁版嵁鏃? ORACLE鑷姩瀵瑰垪杩涜绠€鍗曠殑绫诲瀷杞崲.

鍋囪 EMPNO鏄竴涓暟鍊肩被鍨嬬殑绱㈠紩鍒?

SELECT 鈥?FROM EMP WHERE EMPNO = 鈥?23'

瀹為檯涓?缁忚繃ORACLE绫诲瀷杞崲, 璇彞杞寲涓?

SELECT 鈥?FROM EMP WHERE EMPNO = TO_NUMBER(鈥?23')

骞歌繍鐨勬槸,绫诲瀷杞崲娌℃湁鍙戠敓鍦ㄧ储寮曞垪涓?绱㈠紩鐨勭敤閫旀病鏈夎鏀瑰彉.

鐜板湪,鍋囪EMP_TYPE鏄竴涓瓧绗︾被鍨嬬殑绱㈠紩鍒?

SELECT 鈥?FROM EMP WHERE EMP_TYPE = 123

杩欎釜璇彞琚玂RACLE杞崲涓?

SELECT 鈥?FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123

鍥犱负鍐呴儴鍙戠敓鐨勭被鍨嬭浆鎹? 杩欎釜绱㈠紩灏嗕笉浼氳鐢ㄥ埌! 涓轰簡閬垮厤ORACLE瀵逛綘鐨凷QL杩涜闅愬紡鐨勭被鍨嬭浆鎹? 鏈€濂芥妸绫诲瀷杞崲鐢ㄦ樉寮忚〃鐜板嚭鏉? 娉ㄦ剰褰撳瓧绗﹀拰鏁板€兼瘮杈冩椂, ORACLE浼氫紭鍏堣浆鎹㈡暟鍊肩被鍨嬪埌瀛楃绫诲瀷

锛?1锛?闇€瑕佸綋蹇冪殑WHERE瀛愬彞:

鏌愪簺SELECT 璇彞涓殑WHERE瀛愬彞涓嶄娇鐢ㄧ储寮? 杩欓噷鏈変竴浜涗緥瀛?

鍦ㄤ笅闈㈢殑渚嬪瓙閲? (1)鈥?=' 灏嗕笉浣跨敤绱㈠紩. 璁颁綇, 绱㈠紩鍙兘鍛婅瘔浣犱粈涔堝瓨鍦ㄤ簬琛ㄤ腑, 鑰屼笉鑳藉憡璇変綘浠€涔堜笉瀛樺湪浜庤〃涓? (2) 鈥榺|'鏄瓧绗﹁繛鎺ュ嚱鏁? 灏辫薄鍏朵粬鍑芥暟閭ｆ牱, 鍋滅敤浜嗙储寮? (3) 鈥?'鏄暟瀛﹀嚱鏁? 灏辫薄鍏朵粬鏁板鍑芥暟閭ｆ牱, 鍋滅敤浜嗙储寮? (4)鐩稿悓鐨勭储寮曞垪涓嶈兘浜掔浉姣旇緝,杩欏皢浼氬惎鐢ㄥ叏琛ㄦ壂鎻?

锛?2锛?a. 濡傛灉妫€绱㈡暟鎹噺瓒呰繃30%鐨勮〃涓褰曟暟.浣跨敤绱㈠紩灏嗘病鏈夋樉钁楃殑鏁堢巼鎻愰珮.

b. 鍦ㄧ壒瀹氭儏鍐典笅, 浣跨敤绱㈠紩涔熻浼氭瘮鍏ㄨ〃鎵弿鎱? 浣嗚繖鏄悓涓€涓暟閲忕骇涓婄殑鍖哄埆. 鑰岄€氬父鎯呭喌涓?浣跨敤绱㈠紩姣斿叏琛ㄦ壂鎻忚鍧楀嚑鍊嶄箖鑷冲嚑鍗冨€?

锛?3锛?閬垮厤浣跨敤鑰楄垂璧勬簮鐨勬搷浣?

甯︽湁DISTINCT,UNION,MINUS,INTERSECT,ORDER BY鐨凷QL璇彞浼氬惎鍔⊿QL寮曟搸

鎵ц鑰楄垂璧勬簮鐨勬帓搴?SORT)鍔熻兘. DISTINCT闇€瑕佷竴娆℃帓搴忔搷浣? 鑰屽叾浠栫殑鑷冲皯闇€瑕佹墽琛屼袱娆℃帓搴? 閫氬父, 甯︽湁UNION, MINUS , INTERSECT鐨凷QL璇彞閮藉彲浠ョ敤鍏朵粬鏂瑰紡閲嶅啓. 濡傛灉浣犵殑鏁版嵁搴撶殑SORT_AREA_SIZE璋冮厤寰楀ソ, 浣跨敤UNION , MINUS, INTERSECT涔熸槸鍙互鑰冭檻鐨? 姣曠珶瀹冧滑鐨勫彲璇绘€у緢寮?/p>

锛?4锛?浼樺寲GROUP BY:

鎻愰珮GROUP BY 璇彞鐨勬晥鐜? 鍙互閫氳繃灏嗕笉闇€瑕佺殑璁板綍鍦℅ROUP BY 涔嬪墠杩囨护鎺?涓嬮潰涓や釜鏌ヨ杩斿洖鐩稿悓缁撴灉浣嗙浜屼釜鏄庢樉灏卞揩浜嗚澶?

浣庢晥:

SELECT JOB , AVG(SAL)

FROM EMP

GROUP JOB

HAVING JOB = 鈥楶RESIDENT'

OR JOB = 鈥楳ANAGER'

楂樻晥:

SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB = 鈥楶RESIDENT'

OR JOB = 鈥楳ANAGER'

GROUP JOB

聽

ORACLE鏌ヨ鎴栧垹闄ゆ椂鎸囧畾浣跨敤绱㈠紩鐨勫啓娉?br>
鏌ヨ鏃跺彲浠ユ寚瀹氫娇鐢ㄧ储寮曠殑鍐欐硶銆?br>
SELECT /*+ index(TB_ALIAS IX_G_COST3) */
TB_ALIAS.*
FROM g_Cost TB_ALIAS
WHERE Item_Two = 0
AND Flight_Date >= To_Date('20061201', 'YYYYMMDD')
AND Flight_Date <= To_Date('20061231', 'YYYYMMDD');

鍒犻櫎鏃朵篃鍙互鎸囧畾浣跨敤绱㈠紩鐨勫啓娉曘€?br>
DELETE /*+ index(TB_ALIAS IX_G_COST1) */
FROM g_Cost TB_ALIAS
WHERE ITEM_NAME = '灏忔椂璐?;

http://www.lfpsoft.com