MySQL學習筆記(二)—查詢

標籤：

一、多表串連查詢

建立兩張表t_user、t_order。

          

 

1.內串連

     返回滿足條件的所有記錄。

(1)顯式內串連

     使用inner join關鍵字，在on子句中設定串連條件。

SELECTu.id,u.name,u.address,o.ordernoFROMt_user uINNER JOIN t_order o ON u.id = o.userid;

結果:

      

 

(2)隱式內串連

     不包含inner join和on關鍵字，在where子句中設定串連條件。

SELECTu.id,u.name,u.address,o.ordernoFROMt_user u,t_order oWHEREu.id = o.userid;

結果:

 

2.外串連

(1)左外串連

     A:返回滿足串連條件的所有記錄，同時也返回左表中剩餘的其他記錄。

     B:使用left outer join關鍵字，在on子句中設定串連條件，可加where子句。

SELECTu.id,u.name,u.address,o.ordernoFROMt_user uLEFT OUTER JOIN t_order o ON u.id = o.userid;

結果:

    

 

(2)左外串連

     A:返回滿足串連條件的所有記錄，同時也返回右表中剩餘的其他記錄。

     B:使用left outer join關鍵字，在on子句中設定串連條件，可加where子句。

SELECTu.id,u.name,u.address,o.ordernoFROMt_user uRIGHT OUTER JOIN t_order o ON u.id = o.userid;

結果:

 

3.SQL查詢的基本原理

(1)單表查詢

     根據where條件過濾表中的記錄，形成中間表(這個中間表對使用者是不可見的)。然後根據select的選擇列選擇相應的列進行返回最終結果。

 

(2)兩表串連查詢

     對兩表求積(笛卡爾積)並用on條件和連線類型進行過濾形成中間表；然後根據where條件過濾中間表的記錄，並根據select指定的列返回查詢結果。

 

(3)多表串連查詢

     先對第一個和第二個表按照兩表串連做查詢，然後用查詢結果和第三個表做串連查詢，以此類推，直到所有的表都串連上為止，最終形成一個中間的結果表，然後根據where條件過濾中間表的記錄，並根據select指定的列返回查詢結果。
理解sql查詢的過程是進行sql最佳化的理論依據。

 

(4)on條件和where條件的區別

on條件: 是過濾兩個連結資料表笛卡爾積形成中間表的約束條件。
where條件: 在有on條件的select語句中是過濾中間表的約束條件。在沒有on的單表查詢中，是限制物理表或者中間查詢結果返回記錄的約束。在兩表或多表串連中是限制串連形成最終中間表的返回結果的約束。
從這裡可以看出，將where條件移入on後面是不恰當的。推薦的做法是: on只進行串連操作，where只過濾中間表的記錄。

 

二、子查詢

     子查詢也叫巢狀查詢，是指在select子句或者where子句中又嵌入select查詢語句。

 

1.where型子查詢

把內層查詢結果當作外層查詢的比較條件。

#不用order by 來查詢最新的商品select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);                #取出每個欄目下最新的產品(goods_id唯一)select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

 

2.from子查詢

把內層的查詢結果供外層重新查詢。

###用子查詢查出掛科兩門及以上的同學的平均成績####先查出哪些同學掛科兩門以上select name,count(*) as gk from stu where score < 60 having gk >=2;#以上查詢結果，我們只要名字就可以了，所以再取一次名字select name from (select name,count(*) as gk from stu having gk >=2) as t;#找出這些同學了，那麼再計算他們的平均分select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

MySQL學習筆記(二)—查詢