左右連接
全相乘方法（效率很低）
mysql>?select?*?from?test10; +------+-------+ |?id?|?sname?| +------+-------+ |?1?|?云彩?|? |?2?|?月亮?|? |?3?|?星星?|? +------+-------+
3 rows in set (0.00 sec)
mysql>?select?*?from?test11; +--------+-------+ |?cat_id?|?cname?| +--------+-------+ |?95?|?猴子?|? |?96?|?老虎?|? +--------+-------+
2 rows in set (0.00 sec)
數據庫中實現兩表*的效果
mysql>?select?*?from?test10,test11; +------+-------+--------+-------+ |?id?|?sname?|?cat_id?|?cname?| +------+-------+--------+-------+ |?1?|?云彩?|?95?|?猴子?|? |?1?|?云彩?|?96?|?老虎?|? |?2?|?月亮?|?95?|?猴子?|? |?2?|?月亮?|?96?|?老虎?|? |?3?|?星星?|?95?|?猴子?|? |?3?|?星星?|?96?|?老虎?|? +------+-------+--------+-------+
6 rows in set (0.03 sec)
分析：
test10看成集合有三個元素
test11看成集合有兩個元素
test10*test11得到新集合是有六個元素
行 兩個表行數相乘
列 兩個表列數相加（可以重復）
多表查詢時候列名重復的情況時候，需要明確指出取得是那個表
mysql>?select?goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name?from?minigoods,category?limit?3; +----------+--------+--------------------+--------+----------+ |?goods_id?|?cat_id?|?goods_name?|?cat_id?|?cat_name?| +----------+--------+--------------------+--------+----------+ |?1?|?4?|?KD876?|?1?|?手機類型?|? |?4?|?8?|?htcN85原裝充電器?|?1?|?手機類型?|? |?3?|?8?|?諾基亞原裝5800耳機?|?1?|?手機類型?|? +----------+--------+--------------------+--------+----------+
技巧 ：創建一個同樣結構的表 create table [新表名] like [舊表名]
create table minigoods like goods;
復制一部分表的內容
mysql>?insert?into?minigoods ->?select?*?from?goods?limit?3;
取得有意義的兩個表的對應 minigoods.cat_id=category.cat_id
mysql>?select?goods_id,minigoods.cat_id,goods_name,category.cat_id,cat_name?from?minigoods,category?where?minigoods.cat_id=category.cat_id?; +----------+--------+--------------------+--------+----------+ |?goods_id?|?cat_id?|?goods_name?|?cat_id?|?cat_name?| +----------+--------+--------------------+--------+----------+ |?1?|?4?|?KD876?|?4?|?3G手機?|? |?4?|?8?|?htcN85原裝充電器?|?8?|?耳機?|? |?3?|?8?|?諾基亞原裝5800耳機?|?8?|?耳機?|? +----------+--------+--------------------+--------+----------+
3 rows in set (0.00 sec)
左連接語法
a表在左，不動
b表在右，動
a表和b表通過一個關系（自己設定），來篩選a所需要的b的行
a left join b on [條件] —-條件為真，取出b的行
a left join b on [條件] 結果集也可以看成表（假設為c表），可以對其再查詢?
mysql>?select?goods_id,goods_name,cat_name? ->?from ->?(minigoods?left?join?category?on?minigoods.cat_id=category.cat_id);? +----------+--------------------+----------+ |?goods_id?|?goods_name?|?cat_name?| +----------+--------------------+----------+ |?1?|?KD876?|?3G手機?|? |?4?|?htcN85原裝充電器?|?耳機?|? |?3?|?諾基亞原裝5800耳機?|?耳機?|? +----------+--------------------+----------+
[minigoods left join category on minigoods.cat_id=category.cat_id 看做c表]
驗證：就是后面依然可以使用where 等等過濾條件
mysql>?select?goods_id,goods_name,cat_name?from? (minigoods?left?join?category?on?minigoods.cat_id=category.cat_id?) where?1?order?by?goods_id?desc?limit?2;? +----------+--------------------+----------+ |?goods_id?|?goods_name?|?cat_name?| +----------+--------------------+----------+ |?4?|?htcN85原裝充電器?|?耳機?|? |?3?|?諾基亞原裝5800耳機?|?耳機?|? +----------+--------------------+----------+
2 rows in set (0.00 sec)
可以多張表左連接，就是把結果看成表?
看做一張表
{{a?left?join?b?on?[條件]}?left?join?c?on?[條件]}? mysql>?select?goods.goods_id,goods.goods_name,goods.cat_id,cat_name ->?from ->?minigoods?left?join?category?on?minigoods.cat_id=category.cat_id ->?left?join?goods?on?minigoods.cat_id=4?limit?4; +----------+--------------------+--------+----------+ |?goods_id?|?goods_name?|?cat_id?|?cat_name?| +----------+--------------------+--------+----------+ |?1?|?KD876?|?4?|?3G手機?|? |?4?|?htcN85原裝充電器?|?8?|?3G手機?|? |?3?|?諾基亞原裝5800耳機?|?8?|?3G手機?|? |?5?|?索愛原裝M2卡讀卡器?|?11?|?3G手機?|? +----------+--------------------+--------+----------+
左右連接的區別===================================================================================================
a left join b on 意思是查詢的時候以a為基準查詢
a right join b on 意思是查詢的時候以b為基準查詢
a left join b on 等同于 b right join a （都是以a為基準查詢）
技巧：處于移植兼容性和理解上來說，最好一律使用左連接實現
create?table?boy( bname?varchar(20), other?char(1) )engine?myisam?charset?utf8; insert?into?boy values ('屌絲','A'), ('李四','B'), ('王五','C'), ('高富帥','D'), ('鄭七','E');
create?table?girl( gname?varchar(20), other?char(1) )engine?myisam?charset?utf8; insert?into?girl values ('空姐','B'), ('大S','C'), ('阿嬌','D'), ('張柏芝','D'), ('林黛玉','E'), ('寶釵','F');
取出所有男生的配偶（左連接）
select?boy.*,girl.*?from boy?left?join?girl?on boy.other=girl.other; +--------+-------+--------+-------+ |?bname?|?other?|?gname?|?other?| +--------+-------+--------+-------+ |?屌絲?|?A?|?NULL?|?NULL?|? |?李四?|?B?|?空姐?|?B?|? |?王五?|?C?|?大S?|?C?|? |?高富帥?|?D?|?阿嬌?|?D?|? |?高富帥?|?D?|?張柏芝?|?D?|? |?鄭七?|?E?|?林黛玉?|?E?|? +--------+-------+--------+-------+
取出所有女生的配偶（左連接）
mysql>?select?girl.*,boy.*?from ->?girl?left?join?boy?on? ->?boy.other=girl.other; +--------+-------+--------+-------+ |?gname?|?other?|?bname?|?other?| +--------+-------+--------+-------+ |?空姐?|?B?|?李四?|?B?|? |?大S?|?C?|?王五?|?C?|? |?阿嬌?|?D?|?高富帥?|?D?|? |?張柏芝?|?D?|?高富帥?|?D?|? |?林黛玉?|?E?|?鄭七?|?E?|? |?寶釵?|?F?|?NULL?|?NULL?|? +--------+-------+--------+-------+
取出所有女生的配偶（右連接，和上面左連接一致）
mysql>?select?girl.*?,boy.*?from ->?boy?right?join?girl?on ->?boy.other=girl.other; +--------+-------+--------+-------+ |?gname?|?other?|?bname?|?other?| +--------+-------+--------+-------+ |?空姐?|?B?|?李四?|?B?|? |?大S?|?C?|?王五?|?C?|? |?阿嬌?|?D?|?高富帥?|?D?|? |?張柏芝?|?D?|?高富帥?|?D?|? |?林黛玉?|?E?|?鄭七?|?E?|? |?寶釵?|?F?|?NULL?|?NULL?|? +--------+-------+--------+-------+
內連接===================================================================================================
取出有配偶的
select?girl.*,boy.*?from boy?inner?join?girl?on? boy.other=girl.other; +--------+-------+--------+-------+ |?gname?|?other?|?bname?|?other?| +--------+-------+--------+-------+ |?空姐?|?B?|?李四?|?B?|? |?大S?|?C?|?王五?|?C?|? |?阿嬌?|?D?|?高富帥?|?D?|? |?張柏芝?|?D?|?高富帥?|?D?|? |?林黛玉?|?E?|?鄭七?|?E?|? +--------+-------+--------+-------+
內連接是左右連接的交集
（外連接是左右連接的并集，mysql不支持）可以使用用union實現
左連接應用===================================================================================================
create?table?match_t( match_id?int?primary?key?auto_increment, host_team_id?int, guest_team_id?int, match_result?varchar(20), match_time?date )engine?myisam?charset?utf8; insert?into?match_t values (1,1,2,'2:0','2006-05-21'), (2,2,3,'1:2','2006-06-21'), (3,3,1,'2:5','2006-07-21'), (4,1,1,'3:2','2006-08-21'); create?table?team_t( team_id?int?primary?key?auto_increment, team_name?varchar(20) )engine?myisam?charset?utf8; insert?into?team_t values (1,'恒大'), (2,'國安'), (3,'申花'), (4,'大連'); mysql>?select?*?from?match_t; +----------+--------------+---------------+--------------+------------+ |?match_id?|?host_team_id?|?guest_team_id?|?match_result?|?match_time?| +----------+--------------+---------------+--------------+------------+ |?1?|?1?|?2?|?2:0?|?2006-05-21?|? |?2?|?2?|?3?|?1:2?|?2006-06-21?|? |?3?|?3?|?1?|?2:5?|?2006-07-21?|? |?4?|?4?|?1?|?3:2?|?2006-08-21?|? +----------+--------------+---------------+--------------+------------+ mysql>?select?*?from?team_t; +---------+-----------+ |?team_id?|?team_name?| +---------+-----------+ |?1?|?恒大?|? |?2?|?國安?|? |?3?|?申花?|? |?4?|?大連?|? +---------+-----------+
技巧 ：善與給表其別名
2006-06-21日期之后 把表中對戰換成隊名?
select?host_t.team_name,guest_t.team_name,match_result,match_time?from match_t?left?join?(team_t?as?host_t)?on?match_t.host_team_id=host_t.team_id left?join?(team_t?as?guest_t)?on?match_t.guest_team_id=guest_t.team_id where?match_time>='2006-06-21'; +--------------+-----------+---------------+-----------+--------------+------------+ |?host_team_id?|?team_name?|?guest_team_id?|?team_name?|?match_result?|?match_time?| +--------------+-----------+---------------+-----------+--------------+------------+ |?1?|?恒大?|?2?|?國安?|?2:0?|?2006-05-21?|? |?2?|?國安?|?3?|?申花?|?1:2?|?2006-06-21?|? |?3?|?申花?|?1?|?恒大?|?2:5?|?2006-07-21?|? |?4?|?大連?|?1?|?恒大?|?3:2?|?2006-08-21?|? +--------------+-----------+---------------+-----------+--------------+------------+
以上就是mysql 左,右,內連接的內容，更多相關內容請關注PHP中文網（www.php.cn）！
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
