涓?銆侀棶棰樼殑鎻愬嚭
銆?鍦ㄥ簲鐢ㄧ郴缁熷紑鍙戝垵鏈燂紝鐢变簬寮?鍙戞暟鎹?簱鏁版嵁姣旇緝灏戯紝瀵逛簬鏌ヨ?SQL璇?彞锛屽?鏉傝?鍥剧殑鐨勭紪鍐欑瓑浣撲細涓嶅嚭SQL璇?彞鍚勭?鍐欐硶鐨勬?ц兘浼樺姡锛屼絾鏄??鏋滃皢搴旂敤绯荤粺鎻愪氦瀹為檯搴旂敤鍚庯紝闅忕潃鏁版嵁搴撲腑鏁版嵁鐨勫?鍔狅紝绯荤粺鐨勫搷搴旈?熷害灏辨垚涓虹洰鍓嶇郴缁熼渶瑕佽В鍐崇殑鏈?涓昏?鐨勯棶棰樹箣涓?銆傜郴缁熶紭鍖栦腑涓?涓?緢閲嶈?鐨勬柟闈㈠氨鏄疭QL璇?彞鐨勪紭鍖栥?傚?浜庢捣閲忔暟鎹?紝鍔ｈ川SQL璇?彞鍜屼紭璐⊿QL璇?彞涔嬮棿鐨勯?熷害宸?埆鍙?互杈惧埌涓婄櫨鍊嶏紝鍙??瀵逛簬涓?涓?郴缁熶笉鏄?畝鍗曞湴鑳藉疄鐜板叾鍔熻兘灏卞彲锛岃?屾槸瑕佸啓鍑洪珮璐ㄩ噺鐨凷QL璇?彞锛屾彁楂樼郴缁熺殑鍙?敤鎬с??
銆?銆?鍦ㄥ?鏁版儏鍐典笅锛孫racle浣跨敤绱㈠紩鏉ユ洿蹇?湴閬嶅巻琛?紝浼樺寲鍣ㄤ富瑕佹牴鎹?畾涔夌殑绱㈠紩鏉ユ彁楂樻?ц兘銆備絾鏄?紝濡傛灉鍦⊿QL璇?彞鐨剋here瀛愬彞涓?啓鐨凷QL浠ｇ爜涓嶅悎鐞嗭紝灏变細閫犳垚浼樺寲鍣ㄥ垹鍘荤储寮曡?屼娇鐢ㄥ叏琛ㄦ壂鎻忥紝涓?鑸?氨杩欑?SQL璇?彞灏辨槸鎵?璋撶殑鍔ｈ川SQL璇?彞銆傚湪缂栧啓SQL璇?彞鏃舵垜浠?簲娓呮?浼樺寲鍣ㄦ牴鎹?綍绉嶅師鍒欐潵鍒犻櫎绱㈠紩锛岃繖鏈夊姪浜庡啓鍑洪珮鎬ц兘鐨凷QL璇?彞銆?
銆?銆?浜屻?丼QL璇?彞缂栧啓娉ㄦ剰闂??
銆?銆?涓嬮潰灏辨煇浜汼QL璇?彞鐨剋here瀛愬彞缂栧啓涓?渶瑕佹敞鎰忕殑闂??浣滆?缁嗕粙缁嶃?傚湪杩欎簺where瀛愬彞涓?紝鍗充娇鏌愪簺鍒楀瓨鍦ㄧ储寮曪紝浣嗘槸鐢变簬缂栧啓浜嗗姡璐ㄧ殑SQL锛岀郴缁熷湪杩愯?璇?QL璇?彞鏃朵篃涓嶈兘浣跨敤璇ョ储寮曪紝鑰屽悓鏍蜂娇鐢ㄥ叏琛ㄦ壂鎻忥紝杩欏氨閫犳垚浜嗗搷搴旈?熷害鐨勬瀬澶ч檷浣庛??
銆?銆?1. IS NULL 涓?IS NOT NULL
銆?銆?涓嶈兘鐢╪ull浣滅储寮曪紝浠讳綍鍖呭惈null鍊肩殑鍒楅兘灏嗕笉浼氳?鍖呭惈鍦ㄧ储寮曚腑銆傚嵆浣跨储寮曟湁澶氬垪杩欐牱鐨勬儏鍐典笅锛屽彧瑕佽繖浜涘垪涓?湁涓?鍒楀惈鏈塶ull锛岃?鍒楀氨浼氫粠绱㈠紩涓?帓闄ゃ?備篃灏辨槸璇村?鏋滄煇鍒楀瓨鍦ㄧ┖鍊硷紝鍗充娇瀵硅?鍒楀缓绱㈠紩涔熶笉浼氭彁楂樻?ц兘銆?
銆?銆?浠讳綍鍦╳here瀛愬彞涓?娇鐢╥s null鎴杋s not null鐨勮?鍙ヤ紭鍖栧櫒鏄?笉鍏佽?浣跨敤绱㈠紩鐨勩??
銆?銆?2. 鑱旀帴鍒?
銆?銆?瀵逛簬鏈夎仈鎺ョ殑鍒楋紝鍗充娇鏈?鍚庣殑鑱旀帴鍊间负涓?涓?潤鎬佸?硷紝浼樺寲鍣ㄦ槸涓嶄細浣跨敤绱㈠紩鐨勩?傛垜浠?竴璧锋潵鐪嬩竴涓?緥瀛愶紝鍋囧畾鏈変竴涓?亴宸ヨ〃(employee)锛屽?浜庝竴涓?亴宸ョ殑濮撳拰鍚嶅垎鎴愪袱鍒楀瓨鏀?FIRST_NAME鍜孡AST_NAME)锛岀幇鍦ㄨ?鏌ヨ?涓?涓?彨姣斿皵.鍏嬫灄椤?Bill Cliton)鐨勮亴宸ャ??
銆?銆?涓嬮潰鏄?竴涓?噰鐢ㄨ仈鎺ユ煡璇㈢殑SQL璇?彞锛?
select * from employss where first_name||''||last_name ='Beill Cliton';
涓婇潰杩欐潯璇?彞瀹屽叏鍙?互鏌ヨ?鍑烘槸鍚︽湁Bill Cliton杩欎釜鍛樺伐锛屼絾鏄?繖閲岄渶瑕佹敞鎰忥紝绯荤粺浼樺寲鍣ㄥ?鍩轰簬last_name鍒涘缓鐨勭储寮曟病鏈変娇鐢ㄣ??
銆?銆?褰撻噰鐢ㄤ笅闈㈣繖绉峉QL璇?彞鐨勭紪鍐欙紝Oracle绯荤粺灏卞彲浠ラ噰鐢ㄥ熀浜巐ast_name鍒涘缓鐨勭储寮曘??
*** where first_name ='Beill' and last_name ='Cliton';
. 甯﹂?氶厤绗?%)鐨刲ike璇?彞
銆?銆?鍚屾牱浠ヤ笂闈㈢殑渚嬪瓙鏉ョ湅杩欑?鎯呭喌銆傜洰鍓嶇殑闇?姹傛槸杩欐牱鐨勶紝瑕佹眰鍦ㄨ亴宸ヨ〃涓?煡璇㈠悕瀛椾腑鍖呭惈cliton鐨勪汉銆傚彲浠ラ噰鐢ㄥ?涓嬬殑鏌ヨ?SQL璇?彞:
select * from employee where last_name like '%cliton%';
杩欓噷鐢变簬閫氶厤绗?%)鍦ㄦ悳瀵昏瘝棣栧嚭鐜帮紝鎵?浠?racle绯荤粺涓嶄娇鐢╨ast_name鐨勭储寮曘?傚湪寰堝?鎯呭喌涓嬪彲鑳芥棤娉曢伩鍏嶈繖绉嶆儏鍐碉紝浣嗘槸涓?瀹氳?蹇冧腑鏈夊簳锛岄?氶厤绗﹀?姝や娇鐢ㄤ細闄嶄綆鏌ヨ?閫熷害銆傜劧鑰屽綋閫氶厤绗﹀嚭鐜板湪瀛楃?涓插叾浠栦綅缃?椂锛屼紭鍖栧櫒灏辫兘鍒╃敤绱㈠紩銆傚湪涓嬮潰鐨勬煡璇?腑绱㈠紩寰楀埌浜嗕娇鐢?
select * from employee where last_name like 'c%';
4. Order by璇?彞
銆?銆?ORDER BY璇?彞鍐冲畾浜哋racle濡備綍灏嗚繑鍥炵殑鏌ヨ?缁撴灉鎺掑簭銆侽rder by璇?彞瀵硅?鎺掑簭鐨勫垪娌℃湁浠?涔堢壒鍒?殑闄愬埗锛屼篃鍙?互灏嗗嚱鏁板姞鍏ュ垪涓?璞¤仈鎺ユ垨鑰呴檮鍔犵瓑)銆備换浣曞湪Order by璇?彞鐨勯潪绱㈠紩椤规垨鑰呮湁璁＄畻琛ㄨ揪寮忛兘灏嗛檷浣庢煡璇㈤?熷害銆?
銆?銆?浠旂粏妫?鏌?rder by璇?彞浠ユ壘鍑洪潪绱㈠紩椤规垨鑰呰〃杈惧紡锛屽畠浠?細闄嶄綆鎬ц兘銆傝В鍐宠繖涓?棶棰樼殑鍔炴硶灏辨槸閲嶅啓order by璇?彞浠ヤ娇鐢ㄧ储寮曪紝涔熷彲浠ヤ负鎵?浣跨敤鐨勫垪寤虹珛鍙﹀?涓?涓?储寮曪紝鍚屾椂搴旂粷瀵归伩鍏嶅湪order by瀛愬彞涓?娇鐢ㄨ〃杈惧紡銆?
5. NOT
銆?銆?鎴戜滑鍦ㄦ煡璇㈡椂缁忓父鍦╳here瀛愬彞浣跨敤涓?浜涢?昏緫琛ㄨ揪寮忥紝濡傚ぇ浜庛?佸皬浜庛?佺瓑浜庝互鍙婁笉绛変簬绛夌瓑锛屼篃鍙?互浣跨敤and(涓?銆乷r(鎴?浠ュ強not(闈?銆侼OT鍙?敤鏉ュ?浠讳綍閫昏緫杩愮畻绗﹀彿鍙栧弽銆備笅闈㈡槸涓?涓狽OT瀛愬彞鐨勪緥瀛?
... where not (status ='VALID')
濡傛灉瑕佷娇鐢∟OT锛屽垯搴斿湪鍙栧弽鐨勭煭璇?墠闈㈠姞涓婃嫭鍙凤紝骞跺湪鐭??鍓嶉潰鍔犱笂NOT杩愮畻绗︺?侼OT杩愮畻绗﹀寘鍚?湪鍙﹀?涓?涓??昏緫杩愮畻绗︿腑锛岃繖灏辨槸涓嶇瓑浜?<>)杩愮畻绗︺?傛崲鍙ヨ瘽璇达紝鍗充娇涓嶅湪鏌ヨ?where瀛愬彞涓?樉寮忓湴鍔犲叆NOT璇嶏紝NOT浠嶅湪杩愮畻绗︿腑锛岃?涓嬩緥:
... where status <>'INVALID';
瀵硅繖涓?煡璇?紝鍙?互鏀瑰啓涓轰笉浣跨敤NOT:
select * from employee where salary<3000 or salary>3000;
铏界劧杩欎袱绉嶆煡璇㈢殑缁撴灉涓?鏍凤紝浣嗘槸绗?簩绉嶆煡璇㈡柟妗堜細姣旂?涓?绉嶆煡璇㈡柟妗堟洿蹇?簺銆傜?浜岀?鏌ヨ?鍏佽?Oracle瀵箂alary鍒椾娇鐢ㄧ储寮曪紝鑰岀?涓?绉嶆煡璇㈠垯涓嶈兘浣跨敤绱㈠紩銆?
铏界劧杩欎袱绉嶆煡璇㈢殑缁撴灉涓?鏍凤紝浣嗘槸绗?簩绉嶆煡璇㈡柟妗堜細姣旂?涓?绉嶆煡璇㈡柟妗堟洿蹇?簺銆傜?浜岀?鏌ヨ?鍏佽?Oracle瀵箂alary鍒椾娇鐢ㄧ储寮曪紝鑰岀?涓?绉嶆煡璇㈠垯涓嶈兘浣跨敤绱㈠紩銆?
===============================================================================================
鎴戜滑瑕佸仛鍒颁笉浣嗕細鍐橲QL,杩樿?鍋氬埌鍐欏嚭鎬ц兘浼樿壇鐨凷QL,浠ヤ笅涓虹瑪鑰呭?涔犮?佹憳褰曘?佸苟姹囨?婚儴鍒嗚祫鏂欎笌澶у?鍒嗕韩锛?
锛?锛?nbsp; 閫夋嫨鏈?鏈夋晥鐜囩殑琛ㄥ悕椤哄簭(鍙?湪鍩轰簬瑙勫垯鐨勪紭鍖栧櫒涓?湁鏁?锛?
ORACLE 鐨勮В鏋愬櫒鎸夌収浠庡彸鍒板乏鐨勯『搴忓?鐞咶ROM瀛愬彞涓?殑琛ㄥ悕锛孎ROM瀛愬彞涓?啓鍦ㄦ渶鍚庣殑琛?鍩虹?琛?driving table)灏嗚?鏈?鍏堝?鐞嗭紝鍦‵ROM瀛愬彞涓?寘鍚??涓?〃鐨勬儏鍐典笅,浣犲繀椤婚?夋嫨璁板綍鏉℃暟鏈?灏戠殑琛ㄤ綔涓哄熀纭?琛ㄣ?傚?鏋滄湁3涓?互涓婄殑琛ㄨ繛鎺ユ煡璇? 閭ｅ氨闇?瑕侀?夋嫨浜ゅ弶琛?intersection table)浣滀负鍩虹?琛? 浜ゅ弶琛ㄦ槸鎸囬偅涓??鍏朵粬琛ㄦ墍寮曠敤鐨勮〃.
锛?锛?nbsp; WHERE瀛愬彞涓?殑杩炴帴椤哄簭锛庯細
ORACLE閲囩敤鑷?笅鑰屼笂鐨勯『搴忚В鏋怶HERE瀛愬彞,鏍规嵁杩欎釜鍘熺悊,琛ㄤ箣闂寸殑杩炴帴蹇呴』鍐欏湪鍏朵粬WHERE鏉′欢涔嬪墠, 閭ｄ簺鍙?互杩囨护鎺夋渶澶ф暟閲忚?褰曠殑鏉′欢蹇呴』鍐欏湪WHERE瀛愬彞鐨勬湯灏?
锛?锛?nbsp; SELECT瀛愬彞涓?伩鍏嶄娇鐢?鈥?* 鈥橈細
ORACLE鍦ㄨВ鏋愮殑杩囩▼涓? 浼氬皢'*' 渚濇?杞?崲鎴愭墍鏈夌殑鍒楀悕, 杩欎釜宸ヤ綔鏄??氳繃鏌ヨ?鏁版嵁瀛楀吀瀹屾垚鐨? 杩欐剰鍛崇潃灏嗚?楄垂鏇村?鐨勬椂闂?
锛?锛?nbsp; 鍑忓皯璁块棶鏁版嵁搴撶殑娆℃暟锛?
ORACLE鍦ㄥ唴閮ㄦ墽琛屼簡璁稿?宸ヤ綔: 瑙ｆ瀽SQL璇?彞, 浼扮畻绱㈠紩鐨勫埄鐢ㄧ巼, 缁戝畾鍙橀噺 , 璇绘暟鎹?潡绛夛紱
锛?锛?nbsp; 鍦⊿QL*Plus , SQL*Forms鍜孭ro*C涓?噸鏂拌?缃瓵RRAYSIZE鍙傛暟, 鍙?互澧炲姞姣忔?鏁版嵁搴撹?闂?殑妫?绱㈡暟鎹?噺 ,寤鸿?鍊间负200
锛?锛?nbsp; 浣跨敤DECODE鍑芥暟鏉ュ噺灏戝?鐞嗘椂闂达細
浣跨敤DECODE鍑芥暟鍙?互閬垮厤閲嶅?鎵?弿鐩稿悓璁板綍鎴栭噸澶嶈繛鎺ョ浉鍚岀殑琛?
锛?锛?nbsp; 鏁村悎绠?鍗?鏃犲叧鑱旂殑鏁版嵁搴撹?闂?細
濡傛灉浣犳湁鍑犱釜绠?鍗曠殑鏁版嵁搴撴煡璇㈣?鍙?浣犲彲浠ユ妸瀹冧滑鏁村悎鍒颁竴涓?煡璇?腑(鍗充娇瀹冧滑涔嬮棿娌℃湁鍏崇郴)
锛?锛?nbsp; 鍒犻櫎閲嶅?璁板綍锛?
鏈?楂樻晥鐨勫垹闄ら噸澶嶈?褰曟柟娉?( 鍥犱负浣跨敤浜哛OWID)渚嬪瓙锛?
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
锛?锛?nbsp; 鐢═RUNCATE鏇夸唬DELETE锛?
褰撳垹闄よ〃涓?殑璁板綍鏃?鍦ㄩ?氬父鎯呭喌涓? 鍥炴粴娈?rollback segments ) 鐢ㄦ潵瀛樻斁鍙?互琚?仮澶嶇殑淇℃伅. 濡傛灉浣犳病鏈塁OMMIT浜嬪姟,ORACLE浼氬皢鏁版嵁鎭㈠?鍒板垹闄や箣鍓嶇殑鐘舵??鍑嗙‘鍦拌?鏄?仮澶嶅埌鎵ц?鍒犻櫎鍛戒护涔嬪墠鐨勭姸鍐? 鑰屽綋杩愮敤TRUNCATE鏃? 鍥炴粴娈典笉鍐嶅瓨鏀句换浣曞彲琚?仮澶嶇殑淇℃伅.褰撳懡浠よ繍琛屽悗,鏁版嵁涓嶈兘琚?仮澶?鍥犳?寰堝皯鐨勮祫婧愯?璋冪敤,鎵ц?鏃堕棿涔熶細寰堢煭. (璇戣?呮寜: TRUNCATE鍙?湪鍒犻櫎鍏ㄨ〃閫傜敤,TRUNCATE鏄疍DL涓嶆槸DML)
锛?0锛?灏介噺澶氫娇鐢–OMMIT锛?
鍙??鏈夊彲鑳?鍦ㄧ▼搴忎腑灏介噺澶氫娇鐢–OMMIT, 杩欐牱绋嬪簭鐨勬?ц兘寰楀埌鎻愰珮,闇?姹備篃浼氬洜涓篊OMMIT鎵?閲婃斁鐨勮祫婧愯?屽噺灏?
COMMIT鎵?閲婃斁鐨勮祫婧?
a. 鍥炴粴娈典笂鐢ㄤ簬鎭㈠?鏁版嵁鐨勪俊鎭?
b. 琚?▼搴忚?鍙ヨ幏寰楃殑閿?
c. redo log buffer 涓?殑绌洪棿
d. ORACLE涓虹?鐞嗕笂杩?绉嶈祫婧愪腑鐨勫唴閮ㄨ姳璐?
锛?1锛?鐢╓here瀛愬彞鏇挎崲HAVING瀛愬彞锛?
閬垮厤浣跨敤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杩涜?杩囨护銆傜敱姝ゅ彲瑙侊紝瑕佹兂杩囨护鏉′欢璧峰埌姝ｇ‘鐨勪綔鐢?紝棣栧厛瑕佹槑鐧借繖涓?潯浠跺簲璇ュ湪浠?涔堟椂鍊欒捣浣滅敤锛岀劧鍚庡啀鍐冲畾鏀惧湪閭ｉ噷
锛?2锛?鍑忓皯瀵硅〃鐨勬煡璇?細
鍦ㄥ惈鏈夊瓙鏌ヨ?鐨凷QL璇?彞涓?瑕佺壒鍒?敞鎰忓噺灏戝?琛ㄧ殑鏌ヨ?.渚嬪瓙锛?
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
锛?3锛?閫氳繃鍐呴儴鍑芥暟鎻愰珮SQL鏁堢巼.锛?
澶嶆潅鐨凷QL寰?寰?鐗虹壊浜嗘墽琛屾晥鐜? 鑳藉?鎺屾彙涓婇潰鐨勮繍鐢ㄥ嚱鏁拌В鍐抽棶棰樼殑鏂规硶鍦ㄥ疄闄呭伐浣滀腑鏄?潪甯告湁鎰忎箟鐨?
锛?4锛?浣跨敤琛ㄧ殑鍒?悕(Alias)锛?
褰撳湪SQL璇?彞涓?繛鎺ュ?涓?〃鏃? 璇蜂娇鐢ㄨ〃鐨勫埆鍚嶅苟鎶婂埆鍚嶅墠缂?浜庢瘡涓狢olumn涓?杩欐牱涓?鏉?灏卞彲浠ュ噺灏戣В鏋愮殑鏃堕棿骞跺噺灏戦偅浜涚敱Column姝т箟寮曡捣鐨勮?娉曢敊璇?
锛?5锛?鐢‥XISTS鏇夸唬IN銆佺敤NOT EXISTS鏇夸唬NOT IN锛?
鍦ㄨ?澶氬熀浜庡熀纭?琛ㄧ殑鏌ヨ?涓?涓轰簡婊¤冻涓?涓?潯浠?寰?寰?闇?瑕佸?鍙︿竴涓?〃杩涜?鑱旀帴.鍦ㄨ繖绉嶆儏鍐典笅, 浣跨敤EXISTS(鎴朜OT EXISTS)閫氬父灏嗘彁楂樻煡璇㈢殑鏁堢巼. 鍦ㄥ瓙鏌ヨ?涓?NOT IN瀛愬彞灏嗘墽琛屼竴涓?唴閮ㄧ殑鎺掑簭鍜屽悎骞? 鏃犺?鍦ㄥ摢绉嶆儏鍐典笅,NOT IN閮芥槸鏈?浣庢晥鐨?(鍥犱负瀹冨?瀛愭煡璇?腑鐨勮〃鎵ц?浜嗕竴涓?叏琛ㄩ亶鍘?. 涓轰簡閬垮厤浣跨敤NOT IN ,鎴戜滑鍙?互鎶婂畠鏀瑰啓鎴愬?杩炴帴(Outer Joins)鎴朜OT EXISTS.
渚嬪瓙锛?
锛堥珮鏁堬級SELECT * FROM EMP (鍩虹?琛? WHERE EMPNO > 0 AND EXISTS (SELECT 鈥榅' 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璇?彞锛?
铏界劧鐩?墠鍚勭?鍏充簬SQL浼樺寲鐨勫浘褰㈠寲宸ュ叿灞傚嚭涓嶇┓,浣嗘槸鍐欏嚭鑷?繁鐨凷QL宸ュ叿鏉ヨВ鍐抽棶棰樺?缁堟槸涓?涓?渶濂界殑鏂规硶锛?
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 . 鍥犱负绱㈠紩闇?瑕侀?澶栫殑瀛樺偍绌洪棿鍜屽?鐞?閭ｄ簺涓嶅繀瑕佺殑绱㈠紩鍙嶈?屼細浣挎煡璇㈠弽搴旀椂闂村彉鎱?銆傚畾鏈熺殑閲嶆瀯绱㈠紩鏄?湁蹇呰?鐨?锛?
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
18锛?鐢‥XISTS鏇挎崲DISTINCT锛?
褰撴彁浜や竴涓?寘鍚?竴瀵瑰?琛ㄤ俊鎭?姣斿?閮ㄩ棬琛ㄥ拰闆囧憳琛?鐨勬煡璇㈡椂,閬垮厤鍦⊿ELECT瀛愬彞涓?娇鐢―ISTINCT. 涓?鑸?彲浠ヨ?冭檻鐢‥XIST鏇挎崲, EXISTS 浣挎煡璇㈡洿涓鸿繀閫?鍥犱负RDBMS鏍稿績妯″潡灏嗗湪瀛愭煡璇㈢殑鏉′欢涓?鏃︽弧瓒冲悗,绔嬪埢杩斿洖缁撴灉. 渚嬪瓙锛?
(浣庢晥):
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浠ｇ爜涓?敖閲忓皯鐢ㄨ繛鎺ョ?鈥滐紜鈥濊繛鎺ュ瓧绗︿覆锛?
锛?1锛?閬垮厤鍦ㄧ储寮曞垪涓婁娇鐢∟OT 閫氬父锛屻??
鎴戜滑瑕侀伩鍏嶅湪绱㈠紩鍒椾笂浣跨敤NOT, NOT浼氫骇鐢熷湪鍜屽湪绱㈠紩鍒椾笂浣跨敤鍑芥暟鐩稿悓鐨勫奖鍝? 褰揙RACLE鈥濋亣鍒扳?漀OT,浠栧氨浼氬仠姝?娇鐢ㄧ储寮曡浆鑰屾墽琛屽叏琛ㄦ壂鎻?
锛?2锛?閬垮厤鍦ㄧ储寮曞垪涓婁娇鐢ㄨ?绠楋紟
WHERE瀛愬彞涓?紝濡傛灉绱㈠紩鍒楁槸鍑芥暟鐨勪竴閮ㄥ垎锛庝紭鍖栧櫒灏嗕笉浣跨敤绱㈠紩鑰屼娇鐢ㄥ叏琛ㄦ壂鎻忥紟
涓句緥:
浣庢晥锛?
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鈥?
浣庢晥:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = 鈥淢ELBOURNE鈥?
濡傛灉浣犲潥鎸佽?鐢∣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锛?鎬绘槸浣跨敤绱㈠紩鐨勭?涓?涓?垪锛?
濡傛灉绱㈠紩鏄?缓绔嬪湪澶氫釜鍒椾笂, 鍙?湁鍦ㄥ畠鐨勭?涓?涓?垪(leading column)琚玾here瀛愬彞寮曠敤鏃?浼樺寲鍣ㄦ墠浼氶?夋嫨浣跨敤璇ョ储寮? 杩欎篃鏄?竴鏉＄畝鍗曡?岄噸瑕佺殑瑙勫垯锛屽綋浠呭紩鐢ㄧ储寮曠殑绗?簩涓?垪鏃?浼樺寲鍣ㄤ娇鐢ㄤ簡鍏ㄨ〃鎵?弿鑰屽拷鐣ヤ簡绱㈠紩
28锛?鐢║NION-ALL 鏇挎崲UNION ( 濡傛灉鏈夊彲鑳界殑璇?锛?
褰揝QL 璇?彞闇?瑕乁NION涓や釜鏌ヨ?缁撴灉闆嗗悎鏃?杩欎袱涓?粨鏋滈泦鍚堜細浠?NION-ALL鐨勬柟寮忚?鍚堝苟, 鐒跺悗鍦ㄨ緭鍑烘渶缁堢粨鏋滃墠杩涜?鎺掑簭. 濡傛灉鐢║NION ALL鏇夸唬UNION, 杩欐牱鎺掑簭灏变笉鏄?繀瑕佷簡. 鏁堢巼灏变細鍥犳?寰楀埌鎻愰珮. 闇?瑕佹敞鎰忕殑鏄?紝UNION ALL 灏嗛噸澶嶈緭鍑轰袱涓?粨鏋滈泦鍚堜腑鐩稿悓璁板綍. 鍥犳?鍚勪綅杩樻槸瑕佷粠涓氬姟闇?姹傚垎鏋愪娇鐢║NION ALL鐨勫彲琛屾?? UNION 灏嗗?缁撴灉闆嗗悎鎺掑簭,杩欎釜鎿嶄綔浼氫娇鐢ㄥ埌SORT_AREA_SIZE杩欏潡鍐呭瓨. 瀵逛簬杩欏潡鍐呭瓨鐨勪紭鍖栦篃鏄?浉褰撻噸瑕佺殑. 涓嬮潰鐨凷QL鍙?互鐢ㄦ潵鏌ヨ?鎺掑簭鐨勬秷鑰楅噺
浣庢晥锛?
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锛?
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 鈥?nbsp; FROM EMP WHERE EMPNO = 鈥?23'
瀹為檯涓?缁忚繃ORACLE绫诲瀷杞?崲, 璇?彞杞?寲涓?
SELECT 鈥?nbsp; FROM EMP WHERE EMPNO = TO_NUMBER(鈥?23')
骞歌繍鐨勬槸,绫诲瀷杞?崲娌℃湁鍙戠敓鍦ㄧ储寮曞垪涓?绱㈠紩鐨勭敤閫旀病鏈夎?鏀瑰彉.
鐜板湪,鍋囪?EMP_TYPE鏄?竴涓?瓧绗︾被鍨嬬殑绱㈠紩鍒?
SELECT 鈥?nbsp; FROM EMP WHERE EMP_TYPE = 123
杩欎釜璇?彞琚玂RACLE杞?崲涓?
SELECT 鈥?nbsp; FROM EMP WHERETO_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涔熸槸鍙?互鑰冭檻鐨? 姣曠珶瀹冧滑鐨勫彲璇绘?у緢寮?
锛?4锛?浼樺寲GROUP BY:
鎻愰珮GROUP BY 璇?彞鐨勬晥鐜? 鍙?互閫氳繃灏嗕笉闇?瑕佺殑璁板綍鍦℅ROUP BY 涔嬪墠杩囨护鎺?涓嬮潰涓や釜鏌ヨ?杩斿洖鐩稿悓缁撴灉浣嗙?浜屼釜鏄庢樉灏卞揩浜嗚?澶?
浣庢晥:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP by JOB
HAVING JOB = 鈥楶RESIDENT'
OR JOB = 鈥楳ANAGER'
楂樻晥:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = 鈥楶RESIDENT'
OR JOB = 鈥楳ANAGER'
GROUP by JOB
====================================
====================================
濡傛灉浣犳?鍦ㄨ礋璐ｄ竴涓?熀浜嶴QL Server鐨勯」鐩?紝鎴栬?呬綘鍒氬垰鎺ヨЕSQL Server锛屼綘閮芥湁鍙?兘瑕侀潰涓翠竴浜涙暟鎹?簱鎬ц兘鐨勯棶棰橈紝杩欑瘒鏂囩珷浼氫负浣犳彁渚涗竴浜涙湁鐢ㄧ殑鎸囧?锛堝叾涓?ぇ澶氭暟涔熷彲浠ョ敤浜庡叾瀹冪殑DBMS锛夈??
鍦ㄨ繖閲岋紝鎴戜笉鎵撶畻浠嬬粛浣跨敤SQL Server鐨勭獚闂?紝涔熶笉鑳芥彁渚涗竴涓?寘娌荤櫨鐥呯殑鏂规?锛屾垜鎵?鍋氱殑鏄??荤粨涓?浜涚粡楠?---鍏充簬濡備綍褰㈡垚涓?涓?ソ鐨勮?璁°?傝繖浜涚粡楠屾潵鑷?垜杩囧幓鍑犲勾涓?粡鍙楃殑鏁欒?锛屼竴鐩存潵锛屾垜鐪嬪埌璁稿?鍚屾牱鐨勮?璁￠敊璇??涓?娆″張涓?娆＄殑閲嶅?銆?
涓?銆佷簡瑙ｄ綘鐢ㄧ殑宸ュ叿
涓嶈?杞昏?杩欎竴鐐癸紝杩欐槸鎴戝湪杩欑瘒鏂囩珷涓??杩扮殑鏈?鍏抽敭鐨勪竴鏉°?備篃璁镐綘涔熺湅鍒版湁寰堝?鐨凷QL Server绋嬪簭鍛樻病鏈夋帉鎻″叏閮ㄧ殑T-SQL鍛戒护鍜孲QL Server鎻愪緵鐨勯偅浜涙湁鐢ㄧ殑宸ュ叿銆?
鈥滀粈涔堬紵鎴戣?娴?垂涓?涓?湀鐨勬椂闂存潵瀛︿範閭ｄ簺鎴戞案杩滀篃涓嶄細鐢ㄥ埌鐨凷QL鍛戒护锛燂紵锛熲?濓紝浣犱篃璁镐細杩欐牱璇淬?傚?鐨勶紝浣犱笉闇?瑕佽繖鏍峰仛銆備絾鏄?綘搴旇?鐢ㄤ竴涓?懆鏈?祻瑙堟墍鏈夌殑 T-SQL鍛戒护銆傚湪杩欓噷锛屼綘鐨勪换鍔℃槸浜嗚В锛屽皢鏉ワ紝褰撲綘璁捐?涓?涓?煡璇㈡椂锛屼綘浼氳?璧锋潵锛氣?滃?浜嗭紝杩欓噷鏈変竴涓?懡浠ゅ彲浠ュ畬鍏ㄥ疄鐜版垜闇?瑕佺殑鍔熻兘鈥濓紝浜庢槸锛屽埌MSDN 鏌ョ湅杩欎釜鍛戒护鐨勭‘鍒囪?娉曘??
浜屻?佷笉瑕佷娇鐢ㄦ父鏍?
璁╂垜鍐嶉噸澶嶄竴閬嶏細涓嶈?浣跨敤娓告爣銆傚?鏋滀綘鎯崇牬鍧忔暣涓?郴缁熺殑鎬ц兘鐨勮瘽锛屽畠浠??掓槸浣犳渶鏈夋晥鐨勯?閫夊姙娉曘?傚ぇ澶氭暟鐨勫垵瀛﹁?呴兘浣跨敤娓告爣锛岃?屾病鏈夋剰璇嗗埌瀹冧滑瀵规?ц兘閫犳垚鐨勫奖鍝嶃?傚畠浠?崰鐢ㄥ唴瀛橈紝杩樼敤瀹冧滑閭ｄ簺涓嶅彲鎬濊?鐨勬柟寮忛攣瀹氳〃锛屽彟澶栵紝瀹冧滑绠?鐩村氨鍍忚湕鐗涖?傝?屾渶绯熺硶鐨勬槸锛屽畠浠?彲浠ヤ娇浣犵殑DBA鎵?鑳藉仛鐨勪竴鍒囨?ц兘浼樺寲绛変簬娌″仛銆備笉 鐭ヤ綘鏄?惁鐭ラ亾姣忔墽琛屼竴娆?ETCH灏辩瓑浜庢墽琛屼竴娆?ELECT鍛戒护锛熻繖鎰忓懗鐫?濡傛灉浣犵殑娓告爣鏈?0000鏉¤?褰曪紝瀹冨皢鎵ц?10000娆?ELECT锛佸?鏋滀綘 浣跨敤涓?缁凷ELECT銆乁PDATE鎴栬?匘ELETE鏉ュ畬鎴愮浉搴旂殑宸ヤ綔锛岄偅灏嗘湁鏁堢巼鐨勫?銆?
鍒濆?鑰呬竴鑸??涓轰娇鐢ㄦ父鏍囨槸涓?绉嶆瘮杈冪啛鎮夊拰鑸掗?傜殑缂栫▼鏂瑰紡锛屽彲寰堜笉骞革紝杩欎細瀵艰嚧绯熺硶鐨勬?ц兘銆傛樉鐒讹紝SQL鐨勬?讳綋鐩?殑鏄?綘瑕佸疄鐜颁粈涔堬紝鑰屼笉鏄??庢牱瀹炵幇銆?
鎴戞浘缁忕敤T-SQL閲嶅啓浜嗕竴涓?熀浜庢父鏍囩殑瀛樺偍杩囩▼锛岄偅涓?〃鍙?湁100,000鏉¤?褰曪紝鍘熸潵鐨勫瓨鍌ㄨ繃绋嬬敤浜?0鍒嗛挓鎵嶆墽琛屽畬姣曪紝鑰屾柊鐨勫瓨鍌ㄨ繃绋嬪彧鐢ㄤ簡10绉掗挓銆傚湪杩欓噷锛屾垜鎯充綘搴旇?鍙?互鐪嬪埌涓?涓?笉绉拌亴鐨勭▼搴忓憳绌剁珶鍦ㄥ共浜嗕粈涔堬紒锛侊紒
鎴戜滑鍙?互鍐欎竴涓?皬绋嬪簭鏉ュ彇寰楀拰澶勭悊鏁版嵁骞朵笖鏇存柊鏁版嵁搴擄紝杩欐牱鍋氭湁鏃朵細鏇存湁鏁堛?傝?浣忥細瀵逛簬寰?幆锛孴-SQL鏃犺兘涓哄姏銆?
鎴戝啀閲嶆柊鎻愰啋涓?涓嬶細浣跨敤娓告爣娌℃湁濂藉?銆傞櫎浜咲BA鐨勫伐浣滃?锛屾垜浠庢潵娌℃湁鐪嬪埌杩囦娇鐢ㄦ父鏍囧彲浠ユ湁鏁堢殑瀹屾垚浠讳綍宸ヤ綔銆?
涓夈?佽?鑼冨寲浣犵殑鏁版嵁琛?
涓轰粈涔堜笉瑙勮寖鍖栨暟鎹?簱锛熷ぇ姒傛湁涓や釜鍊熷彛锛氬嚭浜庢?ц兘鐨勮?冭檻鍜岀函绮瑰洜涓烘噿鎯般?傝嚦浜庣?浜岀偣锛屼綘杩熸棭寰椾负姝や粯鍑轰唬浠枫?傝?屽叧浜庢?ц兘鐨勯棶棰橈紝浣犱笉闇?瑕佷紭鍖栨牴鏈?氨涓嶆參鐨勪笢瑗裤?傛垜缁忓父鐪嬪埌涓?浜涚▼搴忓憳鈥滃弽瑙勮寖鍖栤?濇暟鎹?簱锛屼粬浠?殑鐞嗙敱鏄??滃師鏉ョ殑璁捐?澶?參浜嗏?濓紝鍙?粨鏋滃嵈甯稿父鏄?粬浠??绯荤粺鏇存參浜嗐?侱BMS琚??璁＄敤鏉ュ?鐞嗚?鑼冩暟鎹?簱 鐨勶紝鍥犳?锛岃?浣忥細鎸夌収瑙勮寖鍖栫殑瑕佹眰璁捐?鏁版嵁搴撱??
鍥涖?佷笉瑕佷娇鐢⊿ELECT *
杩欑偣涓嶅お瀹规槗鍋氬埌锛屾垜澶?簡瑙ｄ簡锛屽洜涓烘垜鑷?繁灏辩粡甯歌繖鏍峰共銆傚彲鏄?紝濡傛灉鍦⊿ELECT涓?寚瀹氫綘鎵?闇?瑕佺殑鍒楋紝閭ｅ皢浼氬甫鏉ヤ互涓嬬殑濂藉?锛?
1 鍑忓皯鍐呭瓨鑰楄垂鍜岀綉缁滅殑甯﹀?
2 浣犲彲浠ュ緱鍒版洿瀹夊叏鐨勮?璁?
3 缁欐煡璇?紭鍖栧櫒鏈轰細浠庣储寮曡?鍙栨墍鏈夐渶瑕佺殑鍒?
浜斻?佷簡瑙ｄ綘灏嗚?瀵规暟鎹?繘琛岀殑鎿嶄綔
涓轰綘鐨勬暟鎹?簱鍒涘缓涓?涓?仴澹?殑绱㈠紩锛岄偅鍙?槸鍔熷痉涓?浠躲?傚彲瑕佸仛鍒拌繖涓?鐐圭畝鐩村氨鏄?竴闂ㄨ壓鏈??傛瘡褰撲綘涓轰竴涓?〃娣诲姞涓?涓?储寮曪紝SELECT浼氭洿蹇?簡锛屽彲INSERT 鍜孌ELETE鍗村ぇ澶х殑鍙樻參浜嗭紝鍥犱负鍒涘缓浜嗙淮鎶ょ储寮曢渶瑕佽?澶氶?澶栫殑宸ヤ綔銆傛樉鐒讹紝杩欓噷闂??鐨勫叧閿?槸锛氫綘瑕佸?杩欏紶琛ㄨ繘琛屼粈涔堟牱鐨勬搷浣溿?傝繖涓?棶棰樹笉澶?ソ鎶婃彙锛岀壒鍒?槸娑夊強DELETE鍜孶PDATE鏃讹紝鍥犱负杩欎簺璇?彞缁忓父鍦╓HERE閮ㄥ垎鍖呭惈SELECT鍛戒护銆?
鍏??佷笉瑕佺粰鈥滄?у埆鈥濆垪鍒涘缓绱㈠紩
棣栧厛锛屾垜浠?繀椤讳簡瑙ｇ储寮曟槸濡備綍鍔犻?熷?琛ㄧ殑璁块棶鐨勩?備綘鍙?互灏嗙储寮曠悊瑙ｄ负鍩轰簬涓?瀹氱殑鏍囧噯涓婂?琛ㄨ繘琛屽垝鍒嗙殑涓?绉嶆柟寮忋?傚?鏋滀綘缁欑被浼间簬鈥滄?у埆鈥濊繖鏍风殑鍒楀垱寤轰簡涓?涓?绱㈠紩锛屼綘浠呬粎鏄?皢琛ㄥ垝鍒嗕负涓ら儴鍒嗭細鐢峰拰濂炽?備綘鍦ㄥ?鐞嗕竴涓?湁1,000,000鏉¤?褰曠殑琛?紝杩欐牱鐨勫垝鍒嗘湁浠?涔堟剰涔夛紵璁颁綇锛氱淮鎶ょ储寮曟槸姣旇緝璐规椂鐨勩?傚綋浣犺?璁＄储 寮曟椂锛岃?閬靛惊杩欐牱鐨勮?鍒欙細鏍规嵁鍒楀彲鑳藉寘鍚?笉鍚屽唴瀹圭殑鏁扮洰浠庡?鍒板皯鎺掑垪锛屾瘮濡傦細濮撳悕+鐪佷唤+鎬у埆銆?
涓冦?佷娇鐢ㄤ簨鍔?
璇蜂娇鐢ㄤ簨鍔★紝鐗瑰埆鏄?綋鏌ヨ?姣旇緝鑰楁椂銆傚?鏋滅郴缁熷嚭鐜伴棶棰橈紝杩欐牱鍋氫細鏁戜綘涓?鍛界殑銆備竴鑸?湁浜涚粡楠岀殑绋嬪簭鍛橀兘鏈変綋浼?----浣犵粡甯镐細纰板埌涓?浜涗笉鍙??鏂欑殑鎯呭喌浼氬?鑷村瓨鍌ㄨ繃绋嬪穿婧冦??
鍏??佸皬蹇冩?閿?
鎸夌収涓?瀹氱殑娆″簭鏉ヨ?闂?綘鐨勮〃銆傚?鏋滀綘鍏堥攣浣忚〃A锛屽啀閿佷綇琛˙锛岄偅涔堝湪鎵?鏈夌殑瀛樺偍杩囩▼涓?兘瑕佹寜鐓ц繖涓?『搴忔潵閿佸畾瀹冧滑銆傚?鏋滀綘锛堜笉缁忔剰鐨勶級鏌愪釜瀛樺偍杩囩▼涓?厛閿佸畾琛˙锛屽啀閿佸畾琛ˋ锛岃繖鍙?兘灏变細瀵艰嚧涓?涓??閿併?傚?鏋滈攣瀹氶『搴忔病鏈夎?棰勫厛璇︾粏鐨勮?璁″ソ锛屾?閿佹槸涓嶅お瀹规槗琚?彂鐜扮殑銆?
涔濄?佷笉瑕佹墦寮?澶х殑鏁版嵁闆?
涓?涓?粡甯歌?鎻愬嚭鐨勯棶棰樻槸锛氭垜鎬庢牱鎵嶈兘杩呴?熺殑灏?00000鏉¤?褰曟坊鍔犲埌ComboBox涓?紵杩欐槸涓嶅?鐨勶紝浣犱笉鑳戒篃涓嶉渶瑕佽繖鏍峰仛銆傚緢绠?鍗曪紝浣犵殑鐢ㄦ埛瑕佹祻瑙?100000鏉¤?褰曟墠鑳芥壘鍒伴渶瑕佺殑璁板綍锛屼粬涓?瀹氫細璇呭拻浣犵殑銆傚湪杩欓噷锛屼綘闇?瑕佺殑鏄?竴涓?洿濂界殑UI锛屼綘闇?瑕佷负浣犵殑鐢ㄦ埛鏄剧ず涓嶈秴杩?00鎴?00鏉¤?褰曘??
鍗併?佷笉瑕佷娇鐢ㄦ湇鍔″櫒绔?父鏍?
涓庢湇鍔″櫒绔?父鏍囨瘮璧锋潵锛屽?鎴风?娓告爣鍙?互鍑忓皯鏈嶅姟鍣ㄥ拰缃戠粶鐨勭郴缁熷紑閿?锛屽苟涓旇繕鍑忓皯閿佸畾鏃堕棿銆?
鍗佷竴銆佷娇鐢ㄥ弬鏁版煡璇?
鏈夋椂锛屾垜鍦–SDN鎶?鏈??鍧涚湅鍒扮被浼艰繖鏍风殑闂??锛氣?淪ELECT * FROM a WHERE a.id='A'B锛屽洜涓哄崟寮曞彿鏌ヨ?鍙戠敓寮傚父锛屾垜璇ユ?庝箞鍔烇紵鈥濓紝鑰屾櫘閬嶇殑鍥炵瓟鏄?細鐢ㄤ袱涓?崟寮曞彿浠ｆ浛鍗曞紩鍙枫?傝繖鏄?敊璇?殑銆傝繖鏍锋不鏍囦笉娌绘湰锛屽洜涓轰綘杩樹細鍦ㄥ叾浠?涓?浜涘瓧绗︿笂閬囧埌杩欐牱鐨勯棶棰橈紝鏇翠綍鍐佃繖鏍蜂細瀵艰嚧涓ラ噸鐨刡ug锛岄櫎姝や互澶栵紝杩欐牱鍋氳繕浼氫娇SQL Server鐨勭紦鍐茬郴缁熸棤娉曞彂鎸ュ簲鏈夌殑浣滅敤銆備娇鐢ㄥ弬鏁版煡璇?紝閲滃簳鎶借柂锛岃繖浜涢棶棰樼粺缁熶笉瀛樺湪浜嗐??
鍗佷簩銆佸湪绋嬪簭缂栫爜鏃朵娇鐢ㄥぇ鏁版嵁閲忕殑鏁版嵁搴?
绋嬪簭鍛樺湪寮?鍙戜腑浣跨敤鐨勬祴璇曟暟鎹?簱涓?鑸?暟鎹?噺閮戒笉澶э紝鍙?粡甯哥殑鏄?渶缁堢敤鎴风殑鏁版嵁閲忛兘寰堝ぇ銆傛垜浠??氬父鐨勫仛娉曟槸涓嶅?鐨勶紝鍘熷洜寰堢畝鍗曪細鐜板湪纭?洏涓嶆槸寰堣吹锛屽彲涓轰粈涔堟?ц兘闂??鍗磋?绛夊埌宸茬粡鏃犲彲鎸藉洖鐨勬椂鍊欐墠琚?敞鎰忓憿锛?
鍗佷笁銆佷笉瑕佷娇鐢↖NSERT瀵煎叆澶ф壒鐨勬暟鎹?
璇蜂笉瑕佽繖鏍峰仛锛岄櫎闈為偅鏄?繀椤荤殑銆備娇鐢║TS鎴栬?匓CP锛岃繖鏍蜂綘鍙?互涓?涓捐?屽吋寰楃伒娲绘?у拰閫熷害銆?
鍗佸洓銆佹敞鎰忚秴鏃堕棶棰?
鏌ヨ?鏁版嵁搴撴椂锛屼竴鑸?暟鎹?簱鐨勭己鐪侀兘姣旇緝灏忥紝姣斿?15绉掓垨鑰?0绉掋?傝?屾湁浜涙煡璇㈣繍琛屾椂闂磋?姣旇繖闀匡紝鐗瑰埆鏄?綋鏁版嵁搴撶殑鏁版嵁閲忎笉鏂?彉澶ф椂銆?
鍗佷簲銆佷笉瑕佸拷鐣ュ悓鏃朵慨鏀瑰悓涓?璁板綍鐨勯棶棰?
鏈夋椂鍊欙紝涓や釜鐢ㄦ埛浼氬悓鏃朵慨鏀瑰悓涓?璁板綍锛岃繖鏍凤紝鍚庝竴涓?慨鏀硅?呬慨鏀逛簡鍓嶄竴涓?慨鏀硅?呯殑鎿嶄綔锛屾煇浜涙洿鏂板氨浼氫涪澶便?傚?鐞嗚繖绉嶆儏鍐典笉鏄?緢闅撅細鍒涘缓涓?涓猼imestamp瀛楁?锛屽湪鍐欏叆鍓嶆?鏌ュ畠锛屽?鏋滃厑璁革紝灏卞悎骞朵慨鏀癸紝濡傛灉瀛樺湪鍐茬獊锛屾彁绀虹敤鎴枫??
鍗佸叚銆佸湪缁嗚妭琛ㄤ腑鎻掑叆绾?綍鏃讹紝涓嶈?鍦ㄤ富琛ㄦ墽琛孲ELECT MAX(ID)
杩欐槸涓?涓?櫘閬嶇殑閿欒?锛屽綋涓や釜鐢ㄦ埛鍦ㄥ悓涓?鏃堕棿鎻掑叆鏁版嵁鏃讹紝杩欎細瀵艰嚧閿欒?銆備綘鍙?互浣跨敤SCOPE_IDENTITY锛孖DENT_CURRENT鍜孖DENTITY銆傚?鏋滃彲鑳斤紝涓嶈?浣跨敤IDENTITY锛屽洜涓哄湪鏈夎Е鍙戝櫒鐨勬儏鍐典笅锛屽畠浼氬紩璧蜂竴浜涢棶棰橈紙璇﹁?杩欓噷鐨勮?璁猴級銆?
鍗佷竷銆侀伩鍏嶅皢鍒楄?涓篘ULLable
濡傛灉鍙?兘鐨勮瘽锛屼綘搴旇?閬垮厤灏嗗垪璁句负NULLable銆傜郴缁熶細涓篘ULLable鍒楃殑姣忎竴琛屽垎閰嶄竴涓??澶栫殑瀛楄妭锛屾煡璇㈡椂浼氬甫鏉ユ洿澶氱殑绯荤粺寮?閿?銆傚彟澶栵紝灏嗗垪璁句负NULLable浣跨紪鐮佸彉寰楀?鏉傦紝鍥犱负姣忎竴娆¤?闂?繖浜涘垪鏃堕兘蹇呴』鍏堣繘琛屾?鏌ャ??
鎴戝苟涓嶆槸璇碞ULLS鏄?夯鐑︾殑鏍规簮锛屽敖绠℃湁浜涗汉杩欐牱璁や负銆傛垜璁や负濡傛灉浣犵殑涓氬姟瑙勫垯涓?厑璁糕?滅┖鏁版嵁鈥濓紝閭ｄ箞锛屽皢鍒楄?涓篘ULLable鏈夋椂浼氬彂鎸ュ緢濂界殑浣滅敤锛屼絾鏄?紝濡傛灉鍦ㄧ被浼间笅闈㈢殑鎯呭喌涓?娇鐢∟ULLable锛岄偅绠?鐩村氨鏄?嚜璁ㄨ嫤鍚冦??
CustomerName1
CustomerAddress1
CustomerEmail1
CustomerName2
CustomerAddress2
CustomerEmail3
CustomerName1
CustomerAddress2
CustomerEmail3
濡傛灉鍑虹幇杩欑?鎯呭喌锛屼綘闇?瑕佽?鑼冨寲浣犵殑琛ㄤ簡銆?
鍗佸叓銆佸敖閲忎笉瑕佷娇鐢═EXT鏁版嵁绫诲瀷
闄ら潪浣犱娇鐢═EXT澶勭悊涓?涓?緢澶х殑鏁版嵁锛屽惁鍒欎笉瑕佷娇鐢ㄥ畠銆傚洜涓哄畠涓嶆槗浜庢煡璇?紝閫熷害鎱?紝鐢ㄧ殑涓嶅ソ杩樹細娴?垂澶ч噺鐨勭┖闂淬?備竴鑸?殑锛孷ARCHAR鍙?互鏇村ソ鐨勫?鐞嗕綘鐨勬暟鎹???
鍗佷節銆佸敖閲忎笉瑕佷娇鐢ㄤ复鏃惰〃
灏介噺涓嶈?浣跨敤涓存椂琛?紝闄ら潪浣犲繀椤昏繖鏍峰仛銆備竴鑸?娇鐢ㄥ瓙鏌ヨ?鍙?互浠ｆ浛涓存椂琛ㄣ?備娇鐢ㄤ复鏃惰〃浼氬甫鏉ョ郴缁熷紑閿?锛屽?鏋滀綘鏄?敤COM+杩涜?缂栫▼锛屽畠杩樹細缁欎綘甯︽潵寰堝ぇ鐨勯夯 鐑︼紝鍥犱负COM+浣跨敤鏁版嵁搴撹繛鎺ユ睜鑰屼复鏃惰〃鍗磋嚜濮嬭嚦缁堥兘瀛樺湪銆係QL Server鎻愪緵浜嗕竴浜涙浛浠ｆ柟妗堬紝姣斿?Table鏁版嵁绫诲瀷銆?
浜屽崄銆佸?浼氬垎鏋愭煡璇?
SQL Server鏌ヨ?鍒嗘瀽鍣ㄦ槸浣犵殑濂戒紮浼达紝閫氳繃瀹冧綘鍙?互浜嗚В鏌ヨ?鍜岀储寮曟槸濡備綍褰卞搷鎬ц兘鐨勩??
浜屽崄涓?銆佷娇鐢ㄥ弬鐓у畬鏁存??
瀹氫箟涓诲仴銆佸敮涓?鎬х害鏉熷拰澶栭敭锛岃繖鏍峰仛鍙?互鑺傜害澶ч噺鐨勬椂闂淬??
================================================================================================
銆怚T168 鎶?鏈?枃妗ｃ?戜换浣曚簨鎯呴兘鏈夊畠鐨勬簮澶达紝瑕佽В鍐抽棶棰橈紝涔熷緱浠庢簮澶村紑濮嬶紝褰卞搷ORACLE鎬ц兘鐨勬簮澶撮潪甯稿?锛屼富瑕佸寘鎷??涓嬫柟闈?細鏁版嵁搴撶殑纭?欢閰嶇疆:CPU銆佸唴瀛樸?佺綉缁滄潯浠躲??
銆?銆?1. CPU:鍦ㄤ换浣曟満鍣ㄤ腑CPU鐨勬暟鎹??鐞嗚兘鍔涘線寰?鏄? 閲忚?绠楁満鎬ц兘鐨勪竴涓?爣蹇楋紝骞朵笖ORACLE鏄?竴涓?彁渚涘苟琛岃兘鍔涚殑鏁版嵁搴撶郴缁燂紝鍦–PU鏂归潰鐨勮?姹傚氨鏇撮珮浜嗭紝濡傛灉杩愯?闃熷垪鏁扮洰瓒呰繃浜咰PU澶勭悊鐨勬暟鐩?紝鎬ц兘灏变細涓嬮檷锛屾垜浠??瑙ｅ喅鐨勯棶棰樺氨鏄??閫傚綋澧炲姞CPU鐨勬暟閲忎簡锛屽綋鐒舵垜浠?繕鍙?互灏嗛渶瑕佽?澶氳祫婧愮殑杩涚▼KILL鎺?
銆?銆?2. 鍐呭瓨:琛￠噺鏈哄櫒鎬ц兘鐨勫彟澶栦竴涓?寚鏍囧氨鏄?唴瀛樼殑澶氬皯浜嗭紝鍦∣RACLE涓?唴瀛樺拰鎴戜滑鍦ㄥ缓鏁版嵁搴撲腑鐨勪氦鎹㈠尯杩涜?鏁版嵁鐨勪氦鎹?紝璇绘暟鎹?椂锛岀?鐩業/O蹇呴』绛夊緟鐗╃悊I/O鎿嶄綔瀹屾垚锛屽湪鍑虹幇ORACLE鐨勫唴瀛樼摱棰堟椂锛屾垜浠??涓?涓??鑰冭檻鐨勬槸澧炲姞鍐呭瓨锛岀敱浜嶪/O鐨勫搷搴旀椂闂存槸褰卞搷ORACLE鎬ц兘鐨勪富瑕佸弬鏁帮紝鎴戝皢鍦ㄨ繖鏂归潰杩涜?璇︾粏鐨勮?瑙?
銆?銆?3. 缃戠粶鏉′欢:NET*SQL璐熻矗鏁版嵁鍦ㄧ綉缁滀笂鐨勬潵寰?锛屽ぇ閲忕殑SQL浼氫护缃戠粶閫熷害鍙樻參銆傛瘮濡?0M鐨勭綉鍗″拰100鐨勭綉鍗″氨瀵筃ET*SQL鏈夐潪甯告槑鏄剧殑褰卞搷锛岃繕鏈変氦鎹㈡満銆侀泦绾垮櫒绛夌瓑缃戠粶璁惧?鐨勬?ц兘瀵圭綉缁滅殑褰卞搷寰堟槑鏄撅紝寤鸿?鍦ㄤ换浣曠綉缁滀腑涓嶈?璇曞浘鐢?涓?泦绾垮櫒鏉ュ皢缃戞?浜掕仈銆?
銆?銆?OS鍙傛暟鐨勮?缃?
銆?銆?涓嬭〃缁欏嚭浜哋S鐨勫弬鏁拌?缃?強璇存槑锛孌BA鍙?互鏍规嵁瀹為檯闇?瑕佸?杩欎簺鍙傛暟杩涜?璁剧疆
銆?銆?鍐呮牳鍙傛暟鍚?
銆?銆?璇存槑
銆?銆?bufpages
銆?銆?瀵筨uffer绌洪棿涓嶆寜闈欐?佸垎閰嶏紝閲囩敤鍔ㄦ?佸垎閰嶏紝浣縝ufpages鍊奸殢nbuf涓?璧峰?buffer绌洪棿杩涜?鍔ㄦ?佸垎閰嶃??
銆?銆?create_fastlinks
銆?銆?瀵笻FS鏂囦欢绯荤粺鍏佽?蹇??熺?鍙烽摼鎺?
銆?銆?dbc_max_pct
銆?銆?鍔犲ぇ鏈?澶у姩鎬乥uffer绌洪棿鎵?鍗犵墿鐞嗗唴瀛樼殑鐧惧垎姣旓紝浠ユ弧瓒冲簲鐢ㄧ郴缁熺殑璇诲啓鍛戒腑鐜囩殑闇?瑕併??
銆?銆?dbc_min_pct
銆?銆?璁剧疆鏈?灏忓姩鎬乥uffer绌洪棿鎵?鍗犵墿鐞嗗唴瀛樼殑鐧惧垎姣?
銆?銆?desfree
銆?銆?鎻愰珮寮?濮嬩氦鎹㈡搷浣滅殑鏈?浣庣┖闂插唴瀛樹笅闄愶紝淇濋殰绯荤粺鐨勭ǔ瀹氭?э紝闃叉?鍑虹幇涓嶅彲棰勮?鐨勭郴缁熷穿婧?Crash)銆?
銆?銆?fs_async
銆?銆?鍏佽?杩涜?纾佺洏寮傛?鎿嶄綔锛屾彁楂楥PU鍜岀?鐩樼殑鍒╃敤鐜?
銆?銆?lotsfree
銆?銆?鎻愰珮绯荤粺瑙ｉ櫎鎹㈤〉鎿嶄綔鐨勭┖闂插唴瀛樼殑涓婇檺鍊硷紝淇濊瘉搴旂敤绋嬪簭鏈夎冻澶熺殑鍙?敤鍐呭瓨绌洪棿銆?
銆?銆?maxdsiz
銆?銆?閽堝?绯荤粺鏁版嵁閲忓ぇ鐨勭壒鐐癸紝鍔犲ぇ鏈?澶ф暟鎹??鐨勫ぇ灏忥紝淇濊瘉搴旂敤鐨勯渶瑕併??32浣?
銆?銆?maxdsiz_64bit
銆?銆?maximum process data segment size for 64_bit
銆?銆?Maxssiz
銆?銆?鍔犲ぇ鏈?澶у爢鏍堟?鐨勫ぇ灏忋??32_bit)
銆?銆?maxssiz_64bit
銆?銆?鍔犲ぇ鏈?澶у爢鏍堟?鐨勫ぇ灏忋??64_bit)
銆?銆?Maxtsiz
銆?銆?鎻愰珮鏈?澶т唬鐮佹?澶у皬锛屾弧瓒冲簲鐢ㄨ?姹?
銆?銆?maxtsiz_64bit
銆?銆?鍘熷?艰繃澶э紝搴旇皟灏?
銆?銆?Minfree
銆?銆?鎻愰珮鍋滄?浜ゆ崲鎿嶄綔鐨勮嚜鐢卞唴瀛樼殑涓婇檺
銆?銆?Shmem
銆?銆?鍏佽?杩涜?鍐呭瓨鍏变韩锛屼互鎻愰珮鍐呭瓨鐨勫埄鐢ㄧ巼
銆?銆?Shmmax
銆?銆?璁剧疆鏈?澶у叡浜?唴瀛樻?鐨勫ぇ灏忥紝瀹屽叏婊¤冻鐩?墠鐨勯渶瑕?
銆?銆?Timeslice
銆?銆?鐢变簬绯荤粺鐨勭摱棰堜富瑕佸弽鏄犲湪纾佺洏I/O涓婏紝鍥犳?銆?闄嶄綆鏃堕棿鐗囩殑澶у皬锛屼竴鏂归潰鍙?伩鍏嶅洜纾佺洏I/O涓嶇晠閫犳垚CPU鐨勭瓑寰咃紝浠庤?屾彁楂樹簡CPU鐨勭患鍚堝埄鐢ㄧ巼銆傚彟涓?鏂归潰鍑忓皯浜嗚繘绋嬬殑闃诲?閲忋??
銆?銆?unlockable_mem
銆?銆?鎻愰珮浜嗕笉鍙?攣鍐呭瓨鐨勫ぇ灏忥紝浣垮彲鐢ㄤ簬鎹㈤〉鍜屼氦鎹㈢殑鍐呭瓨绌洪棿鎵╁ぇ,鐢ㄤ互婊¤冻绯荤粺瀵瑰唴瀛樼?鐞嗙殑瑕佹眰銆?
鐢ㄦ埛SQL璐ㄩ噺
銆?銆?浠ヤ笂璁茬殑閮芥槸纭?欢鏂归潰鐨勪笢瑗匡紝鍦ㄦ潯浠舵湁闄愮殑鏉′欢涓嬶紝鎴戜滑鍙?互璋冩暣搴旂敤绋嬪簭鐨凷QL璐ㄩ噺:
銆?銆?1. 涓嶈?杩涜?鍏ㄨ〃鎵?弿(Full Table Scan):鍏ㄨ〃鎵?弿瀵艰嚧澶ч噺鐨処/O
銆?銆?2. 灏介噺寤哄ソ鍜屼娇鐢ㄥソ绱㈠紩:寤虹储寮曚篃鏄?湁璁茬┒鐨勶紝鍦ㄥ缓绱㈠紩鏃讹紝涔熶笉鏄?储寮曡秺澶氳秺濂斤紝褰撲竴涓?〃鐨勭储寮曡揪鍒?涓?互涓婃椂锛孫RACLE鐨勬?ц兘鍙?兘杩樻槸鏀瑰杽涓嶄簡锛屽洜涓篛LTP绯荤粺姣忚〃瓒呰繃5涓?储寮曞嵆浼氶檷浣庢?ц兘锛岃?屼笖鍦ㄤ竴涓猻ql 涓?紝 Oracle 浠庝笉鑳戒娇鐢ㄨ秴杩?5涓?储寮?褰撴垜浠?敤鍒癎ROUP BY鍜孫RDER BY鏃?ORACLE灏变細鑷?姩瀵规暟鎹?繘琛屾帓搴?鑰孫RACLE鍦↖NIT.ORA涓?喅瀹氫簡sort_area_size鍖虹殑澶у皬,褰撴帓搴忎笉鑳藉湪鎴戜滑缁欏畾鐨勬帓搴忓尯瀹屾垚鏃?ORACLE灏变細鍦ㄧ?鐩樹腑杩涜?鎺掑簭,涔熷氨鏄?垜浠??鐨勪复鏃惰〃绌洪棿涓?帓搴? 杩囧?鐨勭?鐩樻帓搴忓皢浼氫护 free buffer waits 鐨勫?煎彉楂?鑰岃繖涓?尯闂村苟涓嶅彧鏄?敤浜庢帓搴忕殑,瀵逛簬寮?鍙戜汉鍛樻垜鎻愬嚭濡備笅蹇犲憡:
銆?銆?1)銆乻elect,update,delete 璇?彞涓?殑瀛愭煡璇㈠簲褰撴湁瑙勫緥鍦版煡鎵惧皯浜?0%鐨勮〃琛?濡傛灉涓?涓??鍙ユ煡鎵剧殑琛屾暟瓒呰繃鎬昏?鏁扮殑20%,瀹冨皢涓嶈兘閫氳繃浣跨敤绱㈠紩鑾峰緱鎬ц兘涓婄殑鎻愰珮.
銆?銆?2)銆佺储寮曞彲鑳戒骇鐢熺?鐗?鍥犱负璁板綍浠庤〃涓?垹闄ゆ椂,鐩稿簲涔熶粠琛ㄧ殑绱㈠紩涓?垹闄?琛ㄩ噴鏀剧殑绌洪棿鍙?互鍐嶇敤,鑰岀储寮曢噴鏀剧殑绌洪棿鍗翠笉鑳藉啀鐢?棰戠箒杩涜?鍒犻櫎鎿嶄綔鐨勮?绱㈠紩鐨勮〃,搴斿綋闃舵?鎬у湴閲嶅缓绱㈠紩,浠ラ伩鍏嶅湪绱㈠紩涓??犳垚绌洪棿纰庣墖,褰卞搷鎬ц兘.鍦ㄨ?鍙?殑鏉′欢涓?涔熷彲浠ラ樁娈垫?у湴truncate琛?truncate鍛戒护鍒犻櫎琛ㄤ腑鎵?鏈夎?褰?涔熷垹闄ょ储寮曠?鐗?
銆?銆?3)銆佸湪浣跨敤绱㈠紩鏃朵竴瀹氳?鎸夌储寮曞?搴斿瓧娈电殑椤哄簭杩涜?寮曠敤銆?
銆?銆?4)銆佺敤(+)姣旂敤NOT IN鏇存湁鏁堢巼銆?
銆?銆?闄嶄綆ORACLE鐨勭珵浜?
銆?銆?鍏堣?鍑犱釜ORACLE鐨勫嚑涓?弬鏁帮紝杩欏嚑涓?弬鏁板叧绯诲埌ORACLE鐨勭珵浜?
銆?銆?1)銆乫reelists 鍜?freelist 缁?浠栦滑璐熻矗ORACLE鐨勫?鐞嗚〃鍜岀储寮曠殑绌洪棿绠＄悊;
銆?銆?2)銆乸ctfree 鍙?pctused:璇ュ弬鏁板喅瀹氫簡freelists 鍜?freelist 缁勭殑琛屼负锛宲ctfree 鍜宲ctused 鍙傛暟鐨勫敮涓?鐩?殑灏辨槸涓轰簡鎺у埗鍧楀?浣曞湪 freelists 涓?繘鍑?
銆?銆?璁剧疆濂絧ctfree 鍙?pctused瀵瑰潡鍦╢reelists鐨勭Щ璧板拰璇诲彇寰堥噸瑕併??
銆?銆?鍏朵粬鍙傛暟鐨勮?缃?
銆?銆?1)銆佸寘鎷琒GA鍖?绯荤粺鍏ㄥ眬鍖?:绯荤粺鍏ㄥ眬鍖?SGA)鏄?竴涓?垎閰嶇粰Oracle 鐨勫寘鍚?竴涓?Oracle 瀹炰緥鐨勬暟鎹?簱鐨勬帶鍒朵俊鎭?唴瀛樻?銆?
銆?銆?涓昏?鍖呮嫭鏁版嵁搴撻珮閫熺紦瀛?the database buffer cache)锛?
銆?銆?閲嶆紨鏃ュ織缂撳瓨(the redo log buffer)锛?
銆?銆?鍏变韩姹?the shared pool)锛?
銆?銆?鏁版嵁瀛楀吀缂撳瓨(the data dictionary cache)浠ュ強鍏跺畠鍚勬柟闈㈢殑淇℃伅
銆?銆?2)銆乨b_block_buffers(鏁版嵁楂橀?熺紦鍐插尯)璁块棶杩囩殑鏁版嵁閮芥斁鍦ㄨ繖涓?鐗囧唴瀛樺尯鍩燂紝璇ュ弬鏁拌秺澶э紝Oracle鍦ㄥ唴瀛樹腑鎵惧埌鐩稿悓鏁版嵁鐨勫彲鑳芥?у氨瓒婂ぇ锛屼篃鍗冲姞蹇?簡鏌ヨ?閫熷害銆?
銆?銆?3)銆乻hare_pool_size (SQL鍏变韩缂撳啿姹?:璇ュ弬鏁版槸搴撻珮閫熺紦瀛樺拰鏁版嵁瀛楀吀鐨勯珮閫熺紦瀛樸??
銆?銆?4)銆丩og_buffer (閲嶆紨鏃ュ織缂撳啿鍖?
銆?銆?5)銆乻ort_area_size(鎺掑簭鍖?
銆?銆?6)銆乸rocesses (鍚屾椂杩炴帴鐨勮繘绋嬫暟)
銆?銆?7)銆乨b_block_size (鏁版嵁搴撳潡澶у皬):Oracle榛樿?鍧椾负2KB锛屽お灏忎簡锛屽洜涓哄?鏋滄垜浠?湁涓?涓?KB鐨勬暟鎹?紝鍒?KB鍧楃殑鏁版嵁搴撹?璇?娆＄洏锛屾墠鑳借?瀹岋紝鑰?KB鍧楃殑鏁版嵁搴撳彧瑕?娆″氨璇诲畬浜嗭紝澶уぇ鍑忓皯浜咺/O鎿嶄綔銆傛暟鎹?簱瀹夎?瀹屾垚鍚庯紝灏变笉鑳藉啀鏀瑰彉db_block_size鐨勫?间簡锛屽彧鑳介噸鏂板缓绔嬫暟鎹?簱骞朵笖寤哄簱鏃讹紝瑕侀?夋嫨鎵嬪伐瀹夎?鏁版嵁搴撱??
銆?銆?8)銆乷pen_links (鍚屾椂鎵撳紑鐨勯摼鎺ユ暟)
銆?銆?9)銆乨ml_locks
銆?銆?10)銆乷pen_cursors (鎵撳紑鍏夋爣鏁?
銆?銆?11)銆乨bwr_io_slaves (鍚庡彴鍐欒繘绋嬫暟)
銆?
銆?銆?6. IN鍜孍XISTS
銆?銆?鏈夋椂鍊欎細灏嗕竴鍒楀拰涓?绯诲垪鍊肩浉姣旇緝銆傛渶绠?鍗曠殑鍔炴硶灏辨槸鍦╳here瀛愬彞涓?娇鐢ㄥ瓙鏌ヨ?銆傚湪where瀛愬彞涓?彲浠ヤ娇鐢ㄤ袱绉嶆牸寮忕殑瀛愭煡璇???
銆?銆?绗?竴绉嶆牸寮忔槸浣跨敤IN鎿嶄綔绗?
... where column in(select * from ... where ...);
绗?簩绉嶆牸寮忔槸浣跨敤EXIST鎿嶄綔绗?
... where exists (select 'X' from ...where ...);