锘??xml version="1.0" encoding="utf-8" standalone="yes"?>
Daniel Robbins (drobbins@gentoo.org)
鎬昏錛忛甯墽琛屽畼錛孏entoo Technologies錛孖nc.
2001 騫?7 鏈?/p>
鍦ㄦ湰緋誨垪鏂囩珷涓紝鎮ㄥ皢瀛︿範 RSA 鍜?DSA 璁よ瘉鐨勫伐浣滃師鐞嗭紝浠ュ強浜嗚В濡備綍姝g‘璁劇疆鏃犲瘑鐮佽璇併傚湪鏈郴鍒楃殑絎竴綃囨枃绔犻噷錛孌aniel Robbins 涓昏浠嬬粛 RSA 鍜?DSA 璁よ瘉鍗忚騫跺悜鎮ㄥ睍紺哄浣曞湪緗戠粶涓婂簲鐢ㄨ繖浜涘崗璁?/blockquote>鎴戜滑涓湁璁稿浜烘妸浼樼鐨?OpenSSH錛堝弬瑙佹湰鏂囧悗闈㈢殑鍙傝冭祫鏂?/u>錛夌敤浣滃彜鑰佺殑
rsh
鍛戒護鐨勬浛浠e搧錛孫penSSH 涓嶄粎鏄畨鍏ㄧ殑鑰屼笖鏄姞瀵嗙殑銆侽penSSH 鏇村姞鍚稿紩浜虹殑鐗規т箣涓鏄畠鑳藉浣跨敤鍩轟簬涓瀵逛簰琛ョ殑鏁板瓧寮忓瘑閽ョ殑 RSA 鍜?DSA 璁よ瘉鍗忚鏉ヨ璇佺敤鎴楓俁SA 鍜?DSA 璁よ瘉鎵胯涓嶅繀鎻愪緵瀵嗙爜灝辮兘澶熷悓榪滅▼緋葷粺寤虹珛榪炴帴錛岃繖鏄畠鐨勪富瑕侀瓍鍔涗箣涓銆傝櫧鐒惰繖闈炲父鍚稿紩浜猴紝浣嗘槸 OpenSSH 鐨勬柊鐢ㄦ埛浠父甯鎬互涓縐嶅揩閫熷嵈涓嶅畬鍠勭殑鏂瑰紡閰嶇疆 RSA/DSA錛岀粨鏋滆櫧鐒跺疄鐜頒簡鏃犲瘑鐮佺櫥褰曪紝鍗翠篃鍦ㄦ榪囩▼涓紑浜嗕竴涓緢澶х殑瀹夊叏婕忔礊銆?/p>浠涔堟槸 RSA/DSA 璁よ瘉錛?/span>
SSH錛岀壒鍒槸 OpenSSH錛堝畬鍏ㄥ厤璐圭殑 SSH 鐨勫疄鐜幫級錛屾槸涓涓笉鍙濊鐨勫伐鍏楓傜被浼間簬rsh
錛?code>sshd錛屽嵆telnet
涓嶅悓鐨勬槸錛?code>ssh 鐨勭‘寰堟錛屼絾榪樻槸鏈変竴涓?ssh-agent
闅愯棌宸茬粡瑙e瘑鐨勪笓鐢ㄥ瘑閽ワ紝榪樺皢浠嬬粛ssh-agent
鐨勫墠绔紝鍙互鍦ㄤ笉鐗虹壊瀹夊叏鎬х殑鍓嶆彁涓嬫彁渚涜澶氫究鍒┿傚鏋滄偍涓鐩存兂瑕佹帉鎻?OpenSSH 鏇撮珮綰х殑璁よ瘉鍔熻兘鐨勮瘽錛岄偅涔堝氨璇鋒偍緇х畫寰涓嬭鍚с?/p>RSA/DSA 瀵嗛挜鐨勫伐浣滃師鐞?/span>
涓嬮潰浠庢暣浣撲笂綺楃暐鐨勪粙緇嶄簡 RSA/DSA 瀵嗛挜鐨勫伐浣滃師鐞嗐傝鎴戜滑浠庝竴縐嶅亣鎯崇殑鎯呭艦寮濮嬶紝鍋囧畾鎴戜滑鎯崇敤 RSA 璁よ瘉鍏佽涓鍙版湰鍦扮殑 Linux 宸ヤ綔绔欙紙縐頒綔 localbox錛夋墦寮 remotebox 涓婄殑涓涓繙紼?shell錛?em>remotebox 鏄垜浠殑 ISP 鐨勪竴鍙版満鍣ㄣ傛鍒伙紝褰撴垜浠瘯鍥劇敤浠ュ簲鐢?RSA 鍜?DSA 璁よ瘉鍗忚銆?/p>
% ssh drobbins@remotebox
drobbins@remotebox's password:姝ゅ鎴戜滑鐪嬪埌鐨勬槸
ssh
灝變細鐢ㄥ畨鍏ㄥ瘑鐮佽璇佸崗璁紝鎶婃垜浠殑瀵嗙爜浼犻佺粰 remotebox 榪涜楠岃瘉銆備絾鏄紝鍜?ssh
緙虹渷鐨勮璇佹柟娉曠浉褰撳畨鍏紝RSA 鍜?DSA 璁よ瘉鍗翠負鎴戜滑寮鍒涗簡涓浜涙柊鐨勬綔鍦ㄧ殑鏈轟細銆?/p>浣嗘槸錛屼笌
sshd
鑳藉瀹氫綅瀹冭屾妸瀹冩斁鍦ㄤ竴涓笓闂ㄧ殑鏂囦歡錛垀/.ssh/authorized_keys錛夐噷錛屾垜浠氨涓轟嬌鐢?RSA 璁よ瘉鐧誨綍鍒?remotebox 涓婂仛濂戒簡鍑嗗銆?/p>瑕佺敤 RSA 鐧誨綍鐨勬椂鍊欙紝鎴戜滑鍙鍦?localbox 鐨勬帶鍒跺彴閿叆
ssh
鍛婅瘔 remotebox 鐨?sshd
浼氱敓鎴愪竴涓殢鏈烘暟錛屽茍鐢ㄦ垜浠厛鍓嶆嫹璐濊繃鍘葷殑鍏敤瀵嗛挜瀵硅繖涓殢鏈烘暟榪涜鍔犲瘑銆傜劧鍚庯紝ssh
銆傛帴涓嬫潵錛岃疆鍒版垜浠殑sshd
寰楀嚭緇撹錛屾棦鐒舵垜浠寔鏈夊尮閰嶇殑涓撶敤瀵嗛挜錛屽氨搴斿綋鍏佽鎴戜滑鐧誨綍銆傚洜姝わ紝鎴戜滑鏈夊尮閰嶇殑涓撶敤瀵嗛挜榪欎竴浜嬪疄鎺堟潈鎴戜滑璁塊棶 remotebox銆?/p>涓ら」娉ㄦ剰浜嬮」
鍏充簬 RSA 鍜?DSA 璁よ瘉鏈変袱欏歸噸瑕佺殑娉ㄦ剰浜嬮」銆傜涓欏規槸鎴戜滑鐨勭‘鍙渶瑕佺敓鎴愪竴瀵瑰瘑閽ャ傜劧鍚庢垜浠彲浠ユ妸鎴戜滑鐨勫叕鐢ㄥ瘑閽ユ嫹璐濆埌鎯寵璁塊棶鐨勯偅浜涜繙紼嬫満鍣ㄤ笂錛屽畠浠兘浼氭牴鎹垜浠殑閭f妸涓撶敤瀵嗛挜榪涜鎭板綋鐨勮璇併傛崲鍙ヨ瘽璇達紝鎴戜滑騫朵笉闇瑕佷負鎯寵璁塊棶鐨?em> 姣忎釜緋葷粺閮藉噯澶囦竴瀵瑰瘑閽ャ傚彧瑕佷竴瀵瑰氨瓚沖浜嗐?/p>鍙︿竴欏規敞鎰忎簨欏規槸涓撶敤瀵嗛挜涓嶅簲钀藉叆鍏跺畠浜烘墜涓?/em>銆傛鏄笓鐢ㄥ瘑閽ユ巿鏉冩垜浠闂繙紼嬬郴緇燂紝浠諱綍鎷ユ湁鎴戜滑鐨勪笓鐢ㄥ瘑閽ョ殑浜洪兘浼氳鎺堜簣鍜屾垜浠畬鍏? 鐩稿悓鐨勭壒鏉冦傚鍚屾垜浠笉鎯寵闄岀敓浜烘湁鎴戜滑鐨勪綇澶勭殑閽ュ寵涓鏍鳳紝鎴戜滑搴旇淇濇姢鎴戜滑鐨勪笓鐢ㄥ瘑閽ヤ互闃叉湭鎺堟潈鐨勪嬌鐢ㄣ傚湪姣旂壒鍜屽瓧鑺傜殑涓栫晫閲岋紝榪欐剰鍛崇潃娌℃湁浜烘槸鏈潵灝? 搴旇鑳借鍙栨垨鏄嫹璐濇垜浠殑涓撶敤瀵嗛挜鐨勩?/p>
ssh
鍜?ssh
琚緗垚浜嗗鏋滄垜浠殑瀵嗛挜鐨勬枃浠舵潈闄愬厑璁擱櫎鎴戜滑涔嬪鐨勪換浣曚漢璇誨彇瀵嗛挜錛屽氨鎵撳嵃鍑轟竴鏉″ぇ澶х殑璀﹀憡娑堟伅銆傚叾嬈★紝鍦ㄦ垜浠敤ssh-keygen
浼氳姹傛垜浠緭鍏ヤ竴涓瘑鐮佺煭璇傚鏋滄垜浠緭鍏ヤ簡瀵嗙爜鐭錛?code>sshssh-keygen 緇嗘帰
璁劇疆 RSA 璁よ瘉鐨勭涓姝ヤ粠鐢熸垚涓瀵瑰叕鐢紡涓撶敤瀵嗛挜瀵瑰紑濮嬨俁SA 璁よ瘉鏄?
% ssh-keygen
Generating public/private rsa1 key pair.
Enter file in which to save the key (/home/drobbins/.ssh/identity): (hit enter)
Enter passphrase (empty for no passphrase): (enter a passphrase)
Enter same passphrase again: (enter it again)
Your identification has been saved in /home/drobbins/.ssh/identity.
Your public key has been saved in /home/drobbins/.ssh/identity.pub.
The key fingerprint is:
a4:e7:f2:39:a7:eb:fd:f8:39:f1:f1:7b:fe:48:a1:09 drobbins@localbox褰?
ssh-keygen
灝嗘妸涓撶敤瀵嗛挜淇濆瓨鍦ㄦ璺緞涓紝鍏敤瀵嗛挜灝卞瓨鍦ㄧ揣涓村畠鐨勪竴涓彨鍋?identity.pub 鐨勬枃浠墮噷銆?/p>榪樿璇鋒偍娉ㄦ剰涓涓?
ssh-keygen
鐢ㄨ繖涓瘑鐮佺煭璇姞瀵嗕簡鎴戜滑鐨勪笓鐢ㄥ瘑閽ワ紙~/.ssh/identity錛夛紝浠ヤ嬌鎴戜滑鐨勪笓鐢ㄥ瘑閽ュ浜庨偅浜涗笉鐭ラ亾榪欎釜瀵嗙爜鐭鐨勪漢灝嗗彉寰楁鏃犵敤澶勩?/p>榪芥眰蹇熺殑鎶樿》鏂規
褰撴垜浠寚瀹氬瘑鐮佺煭璇椂錛岃櫧鐒惰繖浣垮緱ssh
榪炴帴鍒?drobbins@remotebox 甯愭埛鏃訛紝ssh
瀹㈡埛紼嬪簭灝變細澶勭悊鍏朵綑鐨勪簨鎯呫傝櫧鐒朵嬌鐢ㄦ垜浠殑榪滅▼瀵嗙爜鍜屼嬌鐢?RSA 瀵嗙爜鐭鐨勬満鍒跺畬鍏ㄤ笉鍚岋紝浣嗗疄闄呬笂榪樻槸浼氭彁紺烘垜浠緭鍏ヤ竴涓?#8220;淇濆瘑鐨勭煭璇?#8221;緇?
# ssh drobbins@remotebox
Enter passphrase for key '/home/drobbins/.ssh/identity': (enter passphrase)
Last login: Thu Jun 28 20:28:47 2001 from localbox.gentoo.org
Welcome to remotebox!
%榪欓噷灝辨槸浜轟滑緇忓父浼氳璇鑰屽鑷磋拷姹傚揩閫熺殑鎶樿》鏂規鐨勫湴鏂廣傛湁寰堝鏃跺欙紝浠呬粎鏄負浜嗕笉蹇呰緭鍏ュ瘑鐮侊紝浜轟滑灝變細鍒涘緩涓嶅姞瀵嗙殑涓撶敤瀵嗛挜銆傞偅鏍風殑璇濓紝浠栦滑鍙杈撳叆
# ssh drobbins@remotebox
Last login: Thu Jun 28 20:28:47 2001 from localbox.gentoo.org
Welcome to remotebox!
%鐒惰岋紝灝界榪欐牱寰堟柟渚匡紝浣嗘槸鍦ㄨ繕娌℃湁瀹屽叏鐞嗚В榪欑鏂規硶瀵瑰畨鍏ㄦх殑褰卞搷鏃訛紝鎮ㄤ笉搴旇浣跨敤銆傚鏋滄湁浜哄湪鏌愪竴鏃跺埢闂叆浜?localbox錛屼竴鎶婁笉鍔犲瘑鐨勪笓鐢ㄥ瘑閽ヤ嬌寰椾粬浠篃鑷姩鏈夋潈璁塊棶 remotebox 浠ュ強鍏跺畠鎵鏈夌敤榪欐妸鍏敤瀵嗛挜閰嶇疆榪囩殑緋葷粺銆?/p>
鎴戠煡閬撴偍鍦ㄦ兂浜涗粈涔堛傛棤瀵嗙爜璁よ瘉錛岃櫧鐒舵湁鐐瑰啋闄╋紝鍙湅璧鋒潵鐨勭‘寰堣浜恒傛垜瀹屽叏鍚屾剰銆備絾鏄紝 榪樻湁鏇村ソ鐨勫姙娉曪紒璇風浉淇℃垜錛屾垜灝嗗悜鎮ㄥ睍紺哄浣曟棦鍙互浜彈鍒版棤瀵嗙爜璁よ瘉鐨勫ソ澶勶紝鍙堜笉蹇呯壓鐗蹭笓鐢ㄥ瘑閽ョ殑瀹夊叏鎬с傚湪鎴戠殑涓嬩竴綃囨枃绔犻噷錛屾垜榪樺皢鍚戞偍灞曠ず濡備綍鐔熺粌鐨勪嬌鐢?
ssh-agent
鍋氬ソ鍑嗗銆備笅闈㈡槸閫愭鐨勬寚瀵箋?/p>RSA 瀵嗛挜瀵圭殑鐢熸垚
瑕佽緗?RSA 璁よ瘉錛屾垜浠渶瑕佹墽琛岀敓鎴愬叕鐢紡涓撶敤瀵嗛挜瀵圭殑涓嬈℃ф楠ゃ傛垜浠殑杈撳叆濡備笅錛?/p>
% ssh-keygen
鍑虹幇鎻愮ず鏃訛紝璇鋒帴鍙楃己鐪佺殑瀵嗛挜浣嶇疆錛堝吀鍨嬬殑鎯呭喌涓嬫槸 ~/.ssh/identity 鍜屽瓨鍌ㄥ叕鐢ㄥ瘑閽ョ殑 ~/.ssh/identity.pub錛夛紝騫舵彁渚涚粰
ssh-keygen
瀹屾垚錛屾偍灝嗕細寰楀埌涓鎶婂叕鐢ㄥ瘑閽ュ拰涓鎶婄敤瀵嗙爜鐭鍔犲瘑鐨勪笓鐢ㄥ瘑閽ャ?/p>RSA 鍏敤瀵嗛挜鐨勫畨瑁?/span>
鎺ヤ笅鏉ワ紝鎴戜滑闇瑕佹妸姝e湪榪愯
% scp ~/.ssh/identity.pub drobbins@remotebox:
鐢變簬 RSA 璁よ瘉榪樻病鏈夊畬鍏ㄨ緗ソ錛屾墍浠ヤ細鎻愮ず鎴戜滑杈撳叆 remotebox 涓婄殑瀵嗙爜銆傝鎮ㄧ収鍋氥傜劧鍚庯紝鐧誨綍鍒?remotebox 騫舵妸鍏敤瀵嗛挜闄勫姞鍒版枃浠?~/.ssh/authorized_keys 涓婏紝濡備笅鎵紺猴細
% ssh drobbins@remotebox
drobbins@remotebox's password: (enter password)
Last login: Thu Jun 28 20:28:47 2001 from localbox.gentoo.org
Welcome to remotebox!
% cat identity.pub >> ~/.ssh/authorized_keys
% exit鐜板湪錛岄厤緗繃 RSA 璁よ瘉浠ュ悗錛屽綋鎴戜滑璇曞浘浣跨敤
% ssh drobbins@remotebox
Enter passphrase for key '/home/drobbins/.ssh/identity':
濂藉搰錛孯SA 璁よ瘉鐨勯厤緗畬鎴愪簡錛佸鏋滃垰鎵嶆病鏈夋彁紺烘偍杈撳叆瀵嗙爜鐭錛屾偍鍙互璇曢獙涓涓嬩互涓嬪嚑縐嶆儏鍐點傜涓錛屽皾璇曢氳繃杈撳叆
ssh
鍙簲鐢?ssh 鍗忚鐗堟湰 1錛屽鏋滃嚭浜庢煇縐嶅師鍥犺繙紼嬬郴緇熺己鐪佽緗殑鏄?DSA 璁よ瘉鐨勮瘽錛屽彲鑳戒細瑕佹眰榪欎箞鍋氥傚鏋滀笉濂忔晥鐨勮瘽錛岃紜鎮ㄧ殑 /etc/ssh/ssh_config 閲屾病鏈夊啓鐫榪欎箞涓琛?DSA 瀵嗛挜鐨勭敓鎴?/span>
ssh
鍗忚鐨勬渶鏂扮増鏈傜洰鍓嶆墍鏈夌殑 OpenSSH 鐗堟湰閮藉簲璇ユ棦鑳戒嬌鐢?RSA 瀵嗛挜鍙堣兘浣跨敤 DSA 瀵嗛挜銆侱SA 瀵嗛挜浠ュ涓嬬被浼間簬 RSA 瀵嗛挜鐨勬柟寮忎嬌鐢?OpenSSH 鐨?
% ssh-keygen -t dsa
鍙堜細鎻愮ず鎴戜滑杈撳叆瀵嗙爜鐭銆傝緭鍏ヤ竴涓畨鍏ㄧ殑瀵嗙爜鐭銆傝繕浼氭彁紺烘垜浠緭鍏ヤ繚瀛?DSA 瀵嗛挜鐨勪綅緗傛甯告儏鍐典笅錛岀己鐪佺殑 ~/.ssh/id_dsa 鍜?~/.ssh/id_dsa.pub 灝卞彲浠ヤ簡銆傚湪鎴戜滑涓嬈℃х敓鎴?DSA 瀵嗛挜瀹屾垚鍚庯紝灝辮鎶婃垜浠殑 DSA 鍏敤瀵嗛挜瀹夎鍒拌繙紼嬬郴緇熶笂鍘諱簡銆?/p>
DSA 鍏敤瀵嗛挜鐨勫畨瑁?/span>
DSA 鍏敤瀵嗛挜鐨勫畨瑁呭張鏄嚑涔庡拰 RSA 瀹夎瀹屽叏涓鏍楓傚浜?DSA錛屾垜浠皢瑕佹妸 ~/.ssh/id_dsa.pub 鏂囦歡鎷瘋礉鍒?remotebox錛岀劧鍚庢妸瀹冮檮鍔犲埌 remotebox 涓婄殑 ~/.ssh/authorized_keys2 鏂囦歡銆傝娉ㄦ剰榪欎釜鏂囦歡鐨勫悕瀛楀拰 RSA 鐨?authorized_keys 鏂囦歡鍚嶄笉鍚屻備竴鏃﹂厤緗畬姣曪紝杈撳叆鎴戜滑鐨?DSA 涓撶敤瀵嗛挜鐨勫瘑鐮佺煭璇氨搴旇鑳界櫥褰曞埌 remotebox錛岃屼笉闇瑕佹垜浠緭鍏ュ湪 remotebox 涓婄湡姝g殑瀵嗙爜銆?/p>涓嬩竴綃?/span>
姝ゅ埢錛屾偍搴旇宸茬粡鍙互浣跨敤 RSA 鎴栬?DSA 璁よ瘉浜嗭紝浣嗘槸鍦ㄦ瘡涓嬈℃柊榪炴帴鏃訛紝鎮ㄤ粛闇杈撳叆鎮ㄧ殑瀵嗙爜鐭銆傚湪鎴戠殑涓嬩竴綃囨枃绔犻噷錛屾垜浠皢浼氫簡瑙e埌濡備綍浣跨敤keychain
錛屽畠鏄竴涓潪甯告柟渚跨殑ssh-agent
姣斾互鍓嶆洿鍙潬銆佹洿鏂逛究鑰屼笖浣跨敤璧鋒潵鏇村叿瓚e懗鎬с傚湪姝や箣鍓嶏紝璇峰氨榪戞煡闃呬笅闈㈠垪鍑虹殑鍙傝冭祫鏂欎互浣挎偍鑳借窡涓婅繘搴︺?/p>
- 璇峰姟蹇呰闂?OpenSSH 鐨勫紑鍙戜富欏點?br>
- 璇風湅鐪嬫渶鏂扮殑 OpenSSH 婧愪唬鐮?tarball 鍜?RPM銆?br>
- 璇鋒煡闃?OpenSSH 甯歌闂瑙g瓟銆?br>
- PuTTY 鏄敤浜?Windows 鏈哄櫒鐨勬瀬濂界殑
鍏充簬浣滆?/span>
Daniel Robbins 灞呬綇鍦ㄧ編鍥芥柊澧ㄨタ鍝ュ窞鐨勯樋灝斿竷寮鍏嬨備粬涓誨垱浜?Gentoo Linux錛岃繖鏄竴縐嶇敤浜?PC 鐨勯珮綰?Linux錛屼互鍙?Portage 緋葷粺錛屾槸鐢ㄤ簬 Linux 鐨勪笅涓浠gЩ妞嶇郴緇熴備粬榪樻槸鍑犳湰 Macmillan 鍑虹増鐨勪功綾?Caldera OpenLinux Unleashed銆?em>SuSE Linux Unleashed 鍜?Samba Unleashed 鐨勬姇紼夸漢銆侱aniel 鑷簩騫寸駭璧峰氨涓庤綆楁満緇撲笅浜嗕笉瑙d箣緙橈紝閭f椂浠栨渶鍏堟帴瑙︾殑鏄?Logo 紼嬪簭璇█錛屽茍娌夋漢浜?Pac Man 娓告垙涓傝繖涔熻灝辨槸浠栬嚦浠婁粛鎷呬換 SONY Electronic Publishing/Psygnosis 棣栧腑鍥懼艦璁捐甯堢殑鍘熷洜鎵鍦ㄣ侱aniel 鍠滄涓庡瀛?Mary 鍜屾柊鍑虹敓鐨勫コ鍎?Hadassah 涓璧峰叡搴︽椂鍏夈傛偍鍙互閫氳繃 drobbins@gentoo.org 鍜?Daniel 鑱旂郴銆?br>
]]>
濂戒簡錛屽氨鏄繖涔堜笁涓〃錛屽鎴瘋姹傛牴鎹粺璁$敤鎴峰姣忎釜妯″潡鐨勪嬌鐢ㄦ鏁幫紝騫惰姹傛寜鐓ч儴闂ㄩ『搴忚繘琛屾帓搴?騫朵笖緇熻緇撴灉鎺掗櫎綆$悊甯愬彿admin錛?br>鎬庝箞鍔? 鐪嬪埌灝忕帇浠ュ墠鐨勮鍥炬槸錛?/p>
SELECT 鐢ㄦ埛琛?F_DEPTNAME, COUNT(*)
AS count, 閮ㄩ棬琛?F_ORDER
FROM 鏃ュ織琛?INNER JOIN
鐢ㄦ埛琛?ON
鏃ュ織琛?F_login = 鐢ㄦ埛琛?F_LOGIN INNER JOIN
閮ㄩ棬琛?ON
鐢ㄦ埛琛?F_DEPTNAME = 閮ㄩ棬琛?F_DEPATNAME
WHERE (鏃ュ織琛?F_login <> 'admin')
GROUP BY 鐢ㄦ埛琛?F_DEPTNAME,
閮ㄩ棬琛?F_NO
ORDER BY 閮ㄩ棬琛?F_NO
閮侀椃錛岃繖璇曞浘鐪嬭搗鏉ユ病浠涔堥棶棰樺晩錛屼絾鏄竴榪愯闂灝辨潵浜?
鑰冿紝濡傛灉閮ㄩ棬A鐨勭敤鎴烽兘娌℃湁浣跨敤錛屼篃灝辨槸鏃ュ織琛ㄩ噷娌℃湁璁板綍錛岄偅涔堣鍥鵑噷鏍規湰灝變笉浼氭樉紺鴻鍗曚綅錛屼絾鏄緢鏄庢樉榪欐牱涓嶅錛屾垜浠渶瑕佹病鏈変嬌鐢ㄧ殑鍗曚綅鏄劇ず嬈℃暟涓?鍢涳紝
鎴戞兂鍔炴硶涓嶆槸鏄庢憜鐫鐨勫槢錛屾妸"INNER JOIN 閮ㄩ棬琛?鏀逛負"RIGHT JOIN"閮ㄩ棬琛ㄤ笉灝眔k浜嗕箞錛屽ソ錛屾敼鍙?
SELECT 鐢ㄦ埛琛?F_DEPTNAME, COUNT(*)
AS count, 閮ㄩ棬琛?F_ORDER
FROM 鏃ュ織琛?INNER JOIN
鐢ㄦ埛琛?ON
鏃ュ織琛?F_login = 鐢ㄦ埛琛?F_LOGIN RIGHT JOIN
閮ㄩ棬琛?ON
鐢ㄦ埛琛?F_DEPTNAME = 閮ㄩ棬琛?F_DEPATNAME
WHERE (鏃ュ織琛?F_login <> 'admin')
GROUP BY 鐢ㄦ埛琛?F_DEPTNAME,
閮ㄩ棬琛?F_NO
ORDER BY 閮ㄩ棬琛?F_NO
榪愯錛屽張閮侀椃錛屾庝箞榪樻槸娌℃湁鍑虹幇錛屾姄鑰蟲尃鑵崐鏅屽紕涓嶆槑鐧斤紝蹇冩兂鍙嶆鑰佸瓙鏈涓嶆曠殑灝辨槸鍥伴毦(鏈鎬曠殑鏄編濂蟲斁鐢礮_^),鎴戜竴鍙ヤ竴鍙ユ潵錛岃皟璇曘佽皟璇?緇堜簬鍙戠幇闂鎵鍦細
"WHERE (鏃ュ織琛?F_login <> 'admin')"
褰揜ight join浠ュ悗錛屾病鏈夋搷浣滅殑閮ㄩ棬浼氬湪瑙嗗浘鐣欎笅涓鏉¤褰曪紝鑰岃繖鏉¤褰曞彧鍖呭惈閮ㄩ棬琛ㄧ殑淇℃伅錛岀敤鎴瘋〃鍜屾棩蹇楄〃鍧囦負NULL,NULL鏄病鏈夊姙娉曞拰'admin'姣旇緝鐨勶紝涔熷氨鏄NULL <> 'admin' 榪斿洖鐨勬槸false,鎬庝箞鍔?璋冩暣瑙嗗浘join鐨勬搴忥紝濡備笅:
SELECT 鐢ㄦ埛琛?F_DEPTNAME, COUNT(*)
AS count, 閮ㄩ棬琛?F_ORDER
FROM 鐢ㄦ埛琛?INNER JOIN
閮ㄩ棬琛?ON
鐢ㄦ埛琛?F_DEPTNAME = 閮ㄩ棬琛?F_DEPATNAME LEFT JOIN
鏃ュ織琛?ON
鏃ュ織琛?F_login = 鐢ㄦ埛琛?F_LOGIN
WHERE (鐢ㄦ埛琛?F_login <> 'admin')
GROUP BY 鐢ㄦ埛琛?F_DEPTNAME,
閮ㄩ棬琛?F_NO
ORDER BY 閮ㄩ棬琛?F_NO
榪欐牱涓嶇鎬庝箞鍙橈紝榪欐墍鏈夌敤鎴峰拰閮ㄩ棬閮芥槸鏈夌殑錛岃屼笖admin涔熻繃婊ょ殑錛屼絾鏄?...涓嶅鍟婏紝鎬庝箞娌℃湁鐢ㄦ埛鐨勫崟浣嶄嬌鐢ㄦ鏁伴兘寰堝ぇ鍟婏紝鍝︼紝鍘熸潵鏄垜鐢ㄧ殑count(*)
鏈夐棶棰橈紝鑲畾寰楃敤sum鍑芥暟鍟︺傛煡鏌ヨ仈鏈轟笡涔︼紝鏈鍚庡畾紼垮涓嬶細
SELECT 鐢ㄦ埛琛?F_DEPTNAME,
SUM(CASE WHEN 緇熻琛?F_login IS NULL THEN 0 ELSE 1 END) as count,
閮ㄩ棬琛?F_ORDER
FROM 鐢ㄦ埛琛?INNER JOIN
閮ㄩ棬琛?ON
鐢ㄦ埛琛?F_DEPTNAME = 閮ㄩ棬琛?F_DEPATNAME LEFT JOIN
鏃ュ織琛?ON
鏃ュ織琛?F_login = 鐢ㄦ埛琛?F_LOGIN
WHERE (鐢ㄦ埛琛?F_login <> 'admin')
GROUP BY 鐢ㄦ埛琛?F_DEPTNAME,
閮ㄩ棬琛?F_NO
ORDER BY 閮ㄩ棬琛?F_NO
緇堜簬鎼炲畾浜嗭紝涓囧瞾錛侊紒涓嶈繃CASE鐨勪嬌鐢ㄤ篃鍒嗕袱縐嶏紝涓縐嶆槸綆鍗旵ASE鍑芥暟錛屼竴縐嶆槸CASE鎼滅儲鍑芥暟錛岃仈鏈轟粠涔︿腑鍏充簬when_expression 鍜孊oolean_expression 鍐欑殑寰堢緇燂紝鎴戠殑鐞嗚Вwhen_expression灝辨槸涓涓鹼紝鑰孊oolean_expression鏄竴涓垽鏂?鍡紝鍐欒繖涓牬涓滆タ涔熷﹩濠嗗濡堢殑鍐欎簡鍗婁釜灝忔椂錛屽埌姝ゆ敹絎斻?br>
Not surprisingly, it is natural for me to think of arrays as fixed size containers, where elements can be accessed at random location through O(1) operation (ie; in constant time) and insertion/deletion in the middle are O(N) operations (ie; could take time proportional to the size of the array) and hence, are better avoided. In contrast, a linked list can grow in size, access of its head or tail and insertion/deletion in the middle are all O(1) operations (assuming that you have pointer to an adjacent element).
It is possible get around the fixed size limitation of arrays by writing a wrapper which will allocate a new array, copy the elements of the old array into the new one and then discard the old one (BTW, this is what ArrayList does). Still, the basic arrays remain a datastructure for collections of fixed size. In contrast, a linked list consists of nodes with 'pointers' to the next and previous node in the list. So, adding or removing a node is simply a matter of reassigning the pointers. Of course, this implies linear time for traversing upto an indexed node, starting from beginning or end. This simple model is very handy in deciding when to use an array and when to use a linked list.
In Java, the ArrayList and LinkedList classes provide a uniform interface to both these datastructures and hence, destroy this simple conceptual model, so necessary to make judicious implementation decisions, in impressionable young minds of many Java programmers. Let me further elaborate this with my recent own experience.
Today, while going over a graph traversal code, I was somewhat alarmed by the generous use of ArrayLists. This code was written by someone who perhaps had learnt programming with Java. As hinted earlier, both ArrayList and LinkedList implement List interface and support similar operations. An ArrayList can grow dynamically and allows insertion/deletion of elements. A LinkedList also allows access of elements through an index, exactly the same way as an ArrayList. This is all fine. However, the problem is that the apparent similarity in the API hides the widely different memory and time costs of these datastructures for different kinds of operations, luring the unwary to use them in dangerous ways:
While researching on this topic, I did find a couple of good articles on the Web:
I am not advocating either ArrayList or LinkedList, though it can be justifiably argued that the use of ArrayList is better suited in many more programming scenarios, and I have no contention with that. The point I am making is that the sameness of the API makes it easy for programmers to assume that these can be used interchangeably. Nothing can be farther from truth. They are distinct datastructures, each optimized for certain kinds of operations and domain of applicability. And a good programmer should be aware of the distinction. The API exposed by the above mentioned Java classes blur this distinction. In my opinion, this is one of those areas where implementation hiding behind a common, easy-to-use interface (think of List interface that both ArrayList and LinkedList implement) may not be in the best interest of the primary user of these classes.