软件开发培训班 >> 编程开发 >> MySQL


mysql 子查询与连接表详情

1銆佷粈涔堟槸瀛愭煡璇紵

鍒楀嚭璁㈣喘鐗╁搧TNT2鐨勬墍鏈夊鎴凤細

select cust_id
from orders
where order_num IN (SELECT order_num

from orderitems
where prod_id = 'TNT2'
)

鏍煎紡鍖?code>SQL 鍖呭惈瀛愭煡璇㈢殑SELECT璇彞闅句互闃呰鍜岃皟璇曪紝鐗瑰埆鏄畠浠緝涓哄鏉傛椂鏇存槸濡傛銆傚涓婃墍绀烘妸瀛愭煡璇㈠垎瑙ｄ负澶氳骞朵笖閫傚綋鍦拌繘琛岀缉杩涳紝鑳芥瀬澶у湴绠�鍖栧瓙鏌ヨ鐨勪娇鐢ㄣ�?/p>

瀵逛簬鑳藉祵濂楃殑瀛愭煡璇㈢殑鏁扮洰娌℃湁闄愬埗锛屼笉杩囧湪瀹為檯浣跨敤鏃剁敱浜庢�ц兘鐨勯檺鍒讹紝涓嶈兘宓屽澶鐨勫瓙鏌ヨ銆?/p>

娉?

鍒楀繀椤诲尮閰?鍦?code>WHERE瀛愬彞涓娇鐢ㄥ瓙鏌ヨ锛堝杩欓噷鎵�绀猴級锛屽簲璇ヤ繚璇?code>SELECT璇彞鍏锋湁涓?code>WHERE瀛愬彞涓浉鍚屾暟鐩殑鍒椼�傞�氬父锛?br /> 瀛愭煡璇㈠皢杩斿洖鍗曚釜鍒楀苟涓斾笌鍗曚釜鍒楀尮閰嶏紝浣嗗鏋滈渶瑕佷篃鍙互浣跨敤澶氫釜鍒椼�?/p>

闄や簡瀛愭煡璇㈠彲浠ユ斁鍦?code>where 涓紝杩樺彲浠ユ斁鍒?code>select涓幓銆?/p>

鍋囧闇�瑕佹樉绀?code>customers琛ㄤ腑姣忎釜瀹㈡埛鐨勮鍗曟�绘暟銆?/p> 

select cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) as orders
from customers
ORDER BY cust_name

mysql 鐨勮繍琛岃繃绋嬫槸鍏堟墽琛屼簡customers 涓煡鍑烘潵浜?code>cust_name锛?code>cust_state锛?code>cust_id锛岀劧鍚庢墽琛?娆″瓙鏌ヨ锛屾煡鍑烘潵浜嗙粨鏋溿�?/p>

閫愭笎澧炲姞瀛愭煡璇㈡潵寤虹珛鏌ヨ 鐢ㄥ瓙鏌ヨ娴嬭瘯鍜岃皟璇曟煡璇㈠緢鏈夋妧宸ф�э紝鐗瑰埆鏄湪杩欎簺璇彞鐨勫鏉傛�т笉鏂鍔犵殑鎯呭喌涓嬫洿鏄姝ゃ�傜敤瀛愭煡璇㈠缓绔嬶紙鍜屾祴璇曪級鏌ヨ鐨勬渶鍙潬鐨勬柟娉曟槸閫愭笎杩涜锛岃繖涓嶮ySQL澶勭悊瀹冧滑鐨勬柟娉曢潪甯哥浉鍚屻�傞鍏堬紝寤虹珛鍜屾祴璇曟渶鍐呭眰鐨勬煡璇€�傜劧鍚庯紝鐢ㄧ‖缂栫爜鏁版嵁寤虹珛鍜屾祴璇曞灞傛煡璇紝骞朵笖浠呭湪纭瀹冩甯稿悗鎵嶅祵鍏ュ瓙鏌ヨ銆傝繖鏃讹紝鍐嶆娴嬭瘯瀹冦�傚浜庤澧炲姞鐨勬瘡涓煡璇紝閲嶅杩欎簺姝ラ銆傝繖鏍峰仛浠呯粰鏋勯�犳煡璇㈠鍔犱簡涓�鐐圭偣鏃堕棿锛屼絾鑺傜渷浜嗕互鍚庯紙鎵惧嚭鏌ヨ涓轰粈涔堜笉姝ｅ父锛夌殑澶ч噺鏃堕棿锛屽苟涓旀瀬澶у湴鎻愰珮浜嗘煡璇竴寮�濮嬪氨姝ｅ父宸ヤ綔鐨勫彲鑳芥�?br />

涓嬮潰浠嬬粛涓�涓嬭仈缁?

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE  vendors.vend_id=products.vend_id
ORDER BY vend_name, prod_name

娉?

瀹屽叏闄愬畾鍒楀悕 鍦ㄥ紩鐢ㄧ殑鍒楀彲鑳藉嚭鐜颁簩涔夋�ф椂锛屽繀椤讳娇鐢ㄥ畬鍏ㄩ檺瀹氬垪鍚嶏紙鐢ㄤ竴涓偣鍒嗛殧鐨勮〃鍚嶅拰鍒楀悕锛夈�傚鏋滃紩鐢ㄤ竴涓病鏈夌敤琛ㄥ悕闄愬埗鐨勫叿鏈変簩涔夋�х殑鍒楀悕锛孧ySQL灏嗚繑鍥為敊璇�?/p>

杩欓噷浣跨敤where 璇彞杩涜鑱旀帴鐨勪綔鐢?

鍒╃敤WHERE瀛愬彞寤虹珛鑱旂粨鍏崇郴浼间箮鏈夌偣濂囨�紝浣嗗疄闄呬笂锛屾湁涓�涓緢鍏呭垎鐨勭悊鐢便�傝璁颁綇锛屽湪涓�鏉ELECT璇彞涓仈缁撳嚑涓〃鏃讹紝鐩稿簲鐨勫叧绯绘槸鍦ㄨ繍琛屼腑鏋勯�犵殑銆傚湪鏁版嵁搴撹〃鐨勫畾涔変腑涓嶅瓨鍦ㄨ兘鎸囩ずMySQL濡備綍瀵硅〃杩涜鑱旂粨鐨勪笢瑗裤�備綘蹇呴』鑷繁鍋氳繖浠朵簨鎯呫�傚湪鑱旂粨涓や釜琛ㄦ椂锛屼綘瀹為檯涓婂仛鐨勬槸灏嗙涓�涓〃涓殑姣忎竴琛屼笌绗簩涓〃涓殑姣忎竴琛岄厤瀵广�俉HERE瀛愬彞浣滀负杩囨护鏉′欢锛屽畠鍙寘鍚偅浜涘尮閰嶇粰瀹氭潯浠讹紙杩欓噷鏄仈缁撴潯浠讹級鐨勮銆傛病鏈塛HERE瀛愬彞锛岀涓�涓〃涓殑姣忎釜琛屽皢涓庣浜屼釜琛ㄤ腑鐨勬瘡涓閰嶅锛岃�屼笉绠″畠浠�昏緫涓婃槸鍚﹀彲浠ラ厤鍦ㄤ竴璧枫�?br />

娉?

绗涘崱鍎跨Н锛?code>cartesian product锛?鐢辨病鏈夎仈缁撴潯浠剁殑琛ㄥ叧绯昏繑鍥炵殑缁撴灉涓虹瑳鍗″効绉�傛绱㈠嚭鐨勮鐨勬暟鐩皢鏄涓�涓〃涓殑琛屾暟涔樹互绗簩涓〃涓殑琛屾暟銆傜洰鍓嶄负姝㈡墍鐢ㄧ殑鑱旂粨绉颁负绛夊�艰仈缁擄紙equijoin锛夛紝瀹冨熀浜庝袱涓〃涔嬮棿鐨勭浉绛夋祴璇曘�傝繖绉嶈仈缁撲篃绉颁负鍐呴儴鑱旂粨銆傚叾瀹烇紝瀵逛簬杩欑鑱旂粨鍙互浣跨敤绋嶅井涓嶅悓鐨勮娉曟潵鏄庣‘鎸囧畾鑱旂粨鐨勭被鍨嬨�?/p>

涓嬮潰鐨凷ELECT璇彞杩斿洖涓庡墠闈緥瀛愬畬鍏ㄧ浉鍚岀殑鏁版嵁锛?/strong>

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products on vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name

浣跨敤鍝璇硶 ANSI SQL瑙勮寖棣栭�?code>INNER JOIN璇硶銆傛澶栵紝灏界浣跨敤WHERE瀛愬彞瀹氫箟鑱旂粨鐨勭‘姣旇緝绠�鍗曪紝浣嗘槸浣跨敤鏄庣‘鐨?br /> 鑱旂粨璇硶鑳藉纭繚涓嶄細蹇樿鑱旂粨鏉′欢锛屾湁鏃跺�欒繖鏍峰仛涔熻兘褰卞搷鎬ц兘銆?/p>

鎬ц兘鑰冭檻 MySQL鍦ㄨ繍琛屾椂鍏宠仈鎸囧畾鐨勬瘡涓〃浠ュ鐞嗚仈缁撱�傝繖绉嶅鐞嗗彲鑳芥槸闈炲父鑰楄垂璧勬簮鐨勶紝鍥犳搴旇浠旂粏锛屼笉瑕佽仈缁?br /> 涓嶅繀瑕佺殑琛ㄣ�傝仈缁撶殑琛ㄨ秺澶氾紝鎬ц兘涓嬮檷瓒婂帀瀹炽�?/p>

澶氬仛瀹為獙 姝ｅ鎵�瑙侊紝涓烘墽琛屼换涓�缁欏畾鐨凷QL鎿嶄綔锛屼竴鑸瓨鍦ㄤ笉姝竴绉嶆柟娉曘�傚緢灏戞湁缁濆姝ｇ‘鎴栫粷瀵归敊璇殑鏂规硶銆傛�ц兘鍙兘
浼氬彈鎿嶄綔绫诲瀷銆佽〃涓暟鎹噺銆佹槸鍚﹀瓨鍦ㄧ储寮曟垨閿互鍙婂叾浠栦竴浜涙潯浠剁殑褰卞搷銆傚洜姝わ紝鏈夊繀瑕佸涓嶅悓鐨勯�夋嫨鏈哄埗杩涜瀹為獙锛屼互鎵?br /> 鍑烘渶閫傚悎鍏蜂綋鎯呭喌鐨勬柟娉曘�傛垜浠悓鏍峰彲浠ヤ娇鐢ㄥ寮犺〃鐨勮仈鎺ワ紝浣嗘槸鏈変竴涓棶棰橈紝鍥犱负琛ㄥ悕澶氫釜鍦版柟浣跨敤锛屾晠鑰岃〃鍚嶅緢闀匡紝閭ｄ箞鍙互浣跨敤琛ㄧ殑鍒悕銆?/p>

濡傦細

涓嬮潰浠嬬粛涓�涓嬪嚑绉嶇壒娈婄殑杩炴帴銆?/p>

2銆佽嚜鑱旀帴

鍋囧浣犲彂鐜版煇鐗╁搧锛堝叾ID涓?code>DTNTR锛夊瓨鍦ㄩ棶棰橈紝鍥犳鎯崇煡閬撶敓浜ц鐗╁搧鐨勪緵搴斿晢鐢熶骇鐨勫叾浠栫墿鍝佹槸鍚︿篃瀛樺湪杩欎簺闂銆傛鏌ヨ瑕佹眰棣栧厛鎵惧埌鐢熶骇ID涓?code>DTNTR鐨勭墿鍝佺殑渚涘簲鍟嗭紝鐒跺悗鎵惧嚭杩欎釜渚涘簲鍟嗙敓浜х殑鍏朵粬鐗╁搧銆?br />

涓嬮潰鏄В鍐虫闂鐨勪竴绉嶆柟娉曪細

浣犲彲鑳戒娇鐢ㄥ瓙鏌ヨ锛岃繖鏍峰仛:

select prod_id,prod_name
from products
where vend_id = (SELECT vend_id from products WHERE prod_id ='DTNTR')

 鍚屾牱鍙互浣跨敤鑷仈鎺ャ�?/p> 

select t1.prod_id,t2.prod_name
from products t1, products t2
where t1.vend_id = t2.vend_id and t1.prod_id='DTNTR'

鐢ㄨ嚜鑱旂粨鑰屼笉鐢ㄥ瓙鏌ヨ 鑷仈缁撻�氬父浣滀负澶栭儴璇彞鐢ㄦ潵鏇夸唬浠庣浉鍚岃〃涓绱㈡暟鎹椂浣跨敤鐨勫瓙鏌ヨ璇彞銆傝櫧鐒舵渶缁堢殑缁撴灉鏄?br /> 鐩稿悓鐨勶紝浣嗘湁鏃跺�欏鐞嗚仈缁撹繙姣斿鐞嗗瓙鏌ヨ蹇緱澶氥�傚簲璇ヨ瘯涓�涓嬩袱绉嶆柟娉曪紝浠ョ‘瀹氬摢涓�绉嶇殑鎬ц兘鏇村ソ銆?/p>

3銆佽嚜鐒惰仈鎺?/h2>

鏃犺浣曟椂瀵硅〃杩涜鑱旂粨锛屽簲璇ヨ嚦灏戞湁涓�涓垪鍑虹幇鍦ㄤ笉姝竴涓〃涓紙琚仈缁撶殑鍒楋級銆傛爣鍑嗙殑鑱旂粨锛堝墠涓�绔犱腑浠嬬粛鐨勫唴閮ㄨ仈缁擄級杩斿洖鎵�鏈夋暟鎹紝鐢氳嚦鐩稿悓鐨勫垪澶氭鍑虹幇銆傝嚜鐒惰仈缁撴帓闄ゅ娆″嚭鐜帮紝浣挎瘡涓垪鍙繑鍥炰竴娆°�?/p>

鎬庢牱瀹屾垚杩欓」宸ヤ綔鍛紵绛旀鏄紝绯荤粺涓嶅畬鎴愯繖椤瑰伐浣滐紝鐢变綘鑷繁瀹屾垚瀹冦�傝嚜鐒惰仈缁撴槸杩欐牱涓�绉嶈仈缁擄紝鍏朵腑浣犲彧鑳介�夋嫨閭ｄ簺鍞竴鐨勫垪銆傝繖涓�鑸槸閫氳繃瀵硅〃浣跨敤閫氶厤绗︼紙SELECT * 锛夊鎵�鏈夊叾浠栬〃鐨勫垪浣跨敤鏄庣‘鐨勫瓙闆嗘潵瀹屾垚鐨勩�?/p>

4銆佸閮ㄨ仈缁?/h2>

璁稿鑱旂粨灏嗕竴涓〃涓殑琛屼笌鍙︿竴涓〃涓殑琛岀浉鍏宠仈銆備絾鏈夋椂鍊欎細闇�瑕佸寘鍚病鏈夊叧鑱旇鐨勯偅浜涜銆備緥濡傦紝鍙?strong>鑳介渶瑕佷娇鐢ㄨ仈缁撴潵瀹屾垚浠ヤ笅宸ヤ綔锛?/strong>

姣斿锛?/strong>瀵规瘡涓鎴蜂笅浜嗗灏戣鍗曡繘琛岃鏁帮紝鍖呮嫭閭ｄ簺鑷充粖灏氭湭涓嬭鍗曠殑瀹㈡埛锛?/p> 

SELECT customers.cust_id,order_num
from customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id

杩欐潯SELECT璇彞浣跨敤浜嗗叧閿瓧OUTER JOIN鏉ユ寚瀹氳仈缁撶殑绫诲瀷锛堣�屼笉鏄湪WHERE瀛愬彞涓寚瀹氾級銆備絾鏄紝涓庡唴閮ㄨ仈缁撳叧鑱斾袱涓〃涓殑琛屼笉鍚岀殑鏄紝澶栭儴鑱旂粨杩樺寘鎷病鏈夊叧鑱旇鐨勮銆傚湪浣跨敤OUTER JOIN璇硶鏃讹紝蹇呴』浣跨敤RIGHT鎴?code>LEFT鍏抽敭瀛?br /> 鎸囧畾鍖呮嫭鍏舵墍鏈夎鐨勮〃锛?code>RIGHT鎸囧嚭鐨勬槸OUTER JOIN鍙宠竟鐨勮〃锛岃�孡EFT鎸囧嚭鐨勬槸OUTER JOIN宸﹁竟鐨勮〃锛夈�?/p>

浣跨敤甯﹁仛闆嗗嚱鏁扮殑鑱旂粨:

瑕佹绱㈡墍鏈夊鎴峰強姣忎釜瀹㈡埛鎵�涓嬬殑璁㈠崟鏁?

SELECT customers.cust_id, COUNT(order_num) as num
from customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id
GROUP BY cust_id

娉ㄦ剰鐐?
1.娉ㄦ剰鎵�浣跨敤鐨勮仈缁撶被鍨嬨�備竴鑸垜浠娇鐢ㄥ唴閮ㄨ仈缁擄紝浣嗕娇鐢ㄥ閮ㄨ仈缁撲篃鏄湁鏁堢殑銆?br /> 2.淇濊瘉浣跨敤姝ｇ‘鐨勮仈缁撴潯浠讹紝鍚﹀垯灏嗚繑鍥炰笉姝ｇ‘鐨勬暟鎹�?br /> 3.搴旇鎬绘槸鎻愪緵鑱旂粨鏉′欢锛屽惁鍒欎細寰楀嚭绗涘崱鍎跨Н銆?br /> 4.鍦ㄤ竴涓仈缁撲腑鍙互鍖呭惈澶氫釜琛紝鐢氳嚦瀵逛簬姣忎釜鑱旂粨鍙互閲囩敤涓嶅悓鐨勮仈缁撶被鍨嬨�傝櫧鐒惰繖鏍峰仛鏄悎娉曠殑锛屼竴鑸篃寰堟湁鐢紝浣嗗簲璇ュ湪涓�璧锋祴璇曞畠浠墠锛屽垎鍒祴璇曟瘡涓仈缁撱�傝繖灏嗕娇鏁呴殰鎺掗櫎鏇翠负绠�鍗曘�?/p>

鍒版杩欑瘒鍏充簬mysql 瀛愭煡璇笌杩炴帴琛ㄨ鎯呯殑鏂囩珷灏变粙缁嶅埌杩欎簡,鏇村鐩稿叧mysql 瀛愭煡璇笌杩炴帴琛ㄥ唴瀹硅鎼滅储鑴氭湰涔嬪浠ュ墠鐨勬枃绔犳垨缁х画娴忚涓嬮潰鐨勭浉鍏虫枃绔犲笇鏈涘ぇ瀹朵互鍚庡澶氭敮鎸佽剼鏈箣瀹讹紒