您好,登錄后才能下訂單哦!
1.內(nèi)連接inner join
1.1交叉連接cross join
SELECT * FROM person_hobby,person,hobby;
表A(m列),表B(n列),表C(l列)的笛卡爾積(m*n*l列)
1.2等值連接
SELECT * FROM person as p,hobby as h,person_hobby as ph where p.person_id=ph.person_id and h.hobby_id=ph.hobby_id;
在交叉連接的基礎(chǔ)上根據(jù)條件進(jìn)行選擇(過(guò)濾掉不合法條件的行)
1.3自然連接natural join
SELECT * FROM person natural join person_hobby natural join hobby;
在等值連接的基礎(chǔ)上去除重復(fù)列(若A,B有相同名稱(chēng)的列,否則就等價(jià)于交叉連接)
2.外連接outter join
2.1左外連接left join
SELECT * FROM person_hobby right join person on person_hobby.person_id=person.person_id left join hobby on person_hobby.hobby_id=hobby.hobby_id;
左表全部行和右表對(duì)應(yīng)左表的行
2.2右外連接right join
SELECT * FROM person_hobby right join person on person_hobby.person_id=person.person_id right join hobby on person_hobby.hobby_id=hobby.hobby_id;
右表的全部行和左表對(duì)應(yīng)右表的行
2.3全外連接full join(部分?jǐn)?shù)據(jù)庫(kù)管理系統(tǒng)支持,mysql不支持)
select * from A full join B on A.a=B.a;
3.聯(lián)合union:將多個(gè)查詢(xún)結(jié)果合并,去除重復(fù)行(union all包含重復(fù)行)
select ... from A where ... union select ...from b where ... union select ... from c where ... group by ... order by ...
使用規(guī)則:
每個(gè)select語(yǔ)句需要選取相同數(shù)量的,數(shù)據(jù)類(lèi)型兼容的列。
group by或order by只能在最后使用一次,不能每個(gè)select都使用,因?yàn)閡nion是合并之后再進(jìn)行分組或排序。
4.intersect:求交集
select ... from A where ... intersect select ...from b where ... intersect select ... from c where ...
5.except:求差集
select ... from A where ... except select ...from b where ... except select ... from c where ...
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。