數據庫多表查詢 多表查詢也叫連接查詢，此處為基於兩個表的連接查詢 如果一個查詢需要對多個表進行操作就稱為連接查詢，連接查詢的結果集或結果稱為表之間的連接。 連接查詢實際上是通過 各個 表之間共同列 的關聯性 來查詢數據的，它是關系數據庫查詢最主要的特征。 select 表1.字段名，表2.字段名，... from 表1，表2 where 連接條件 連接查詢分類： 1.自連接查詢，對同一個表進行連接操作 2.內連接查詢，<又分為：自然連接，等值連接，不等值連接三種> 3.外連接查詢，<又分為：左外連接，右外連接，全外連接三種>（左連接和左外連接一樣，右連接和右外連接一樣，在mysql中不支持全連接，在oracle中左連接才有“（+）” 的形式 eg：select * a.name(+) = b.name ） 4.交叉連接查詢，也稱為 無條件查詢。 5.聯合查詢 一 自連接查詢 一個表 自己 與 自己建立 連接 稱為自連接或自身連接。 進行自連接就如同兩個分開的表 一樣，可以把一個表的某一行與同一表中的另一行連接起來。 例： 查詢選學“101”課程的成績高於“9505201”號學生成績的所有學生記錄 並按成績高低 排列 select x.* from sclass x,sclass y where x.cno='101' and x.degree>y.degree and y.sno='9505201' and y.cno='101' order by x.degree desc 二 內連接（Inner join） 內連接是最常用的一種連接方式，它只返回兩個數據集合之間 匹配關系的 那些行。 將位於兩個相互交叉的數據集合中重疊部分以內的那些數據行連接起來. 內連接(INNER JOIN)使用比較運算符進行表間某(些)列數據的比較操作， 並列出這些表中與連接條件相匹配的數據行。 根據所使用的比較方式不同，內連接查詢操作列出與連接條件匹配的數據行， 它使用比較運算符比較被連接列的列值。 語法： select 字段名列表 from 表名 [inner] join 表名 on 連接條件 [where 條件表達式] 例： select 學生表.學號,學生表.姓名,學生表.班級代號,成績表.課程代號,成績表.課程成績 from 學生表 INNER JOIN 成績表 on 學生表.學號=成績表.學號 where 學生表.班級代號=''200201'' select x.sno,x.sname,y.cno,y.degree from student x INNER join sclass y on x.sno=y.sno where x.sclass=''95文秘1'' 內連接分三種： 1、等值連接： 所謂等值連接，是指表之間通過“等於”關系連接起來，產生一個臨時表， 然後對該臨時表進行處理後生成最終結果。其查詢結果中列出被連接表中的所有列， 包括其中的重復列。 (1)查詢所有學生的sno,cname,degree列: SELECT x.sno,y.cname,x.degree FROM score x,course y WHERE x.cno=y.cno (2)查詢“95033”班所選課程的平均分: SELECT y.cno,avg(y.degree) as "平均分" FROM student x,score y WHERE x.class=''95033'' and x.sno=y.sno group by y.cno (3)查詢所有學生的student,score表信息： select x.*,y.* from student x,score y where x.sno=y.sno 用等值連接列出authors和publishers表中位於同一城市的作者和出版社： SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city 2、不等值連接： 是指表之間的連接關系不是“等於”，而是其它關系。 這些運算符包括>、>=、<=、<、!>、!<和<>。 (1)查詢所有學生的sno,cno,rank列: select sno,cno,rank from score,grade where degree between low and upp order by rank (2) select x.sno,x.sname,y.cno,y.degree from student x,score y where x.sno!=y.sno 3、自然連接： 在等值連接中消除重復列就是自然連接。 (1) select x.sno,x.sname,y.cno,y.degree from student x,score where x.sno=y.sno 用自然連接，在選擇列表中刪除authors 和publishers 表中重復列(city和state)： SELECT a.*?p.pub_id?p.pub_name?p.country FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city ---------------------------- 三。外連接(OUTER JOIN): 與內連接不同的是，外連接不只列出與連接條件相匹配的行， 而是列出左表(左外連接時)、右表(右外連接時)或兩個表(全外連接時) 中所有符合搜索條件的數據行。 外連接是對內連接的擴充,除了將兩個數據集合中重疊部分以內的那些數據行連接起來之外, 還可以根據要求返回左側或右側數據集合中非匹配的數據或全部的數據. 即 左外連接(LEFT OUTER JOIN); 右外連接(RIGHT OUTER JOIN); 全部連接(FULL OUTER JOIN). 外連接返回的結果集中的一些數據看起來和內連接返回的數據完全一樣, 但有一些數據也與內連接返回的數據不同,這些數據行的某些部分是來自於表中的數據， 另一部分是NULL值,產生這些NULL值的原因是因為另一表中不包含與指定表相匹配的數據項 內連接時，返回查詢結果集合中的僅是符合查詢條件( WHERE 搜索條件或 HAVING 條件)和 連接條件的行。而采用外連接時，它返回到查詢結果集合中的不僅包含符合連接條件的行， 而且還包括左表(左外連接時)、右表(右外連接時)或兩個邊接表(全外連接)中的所有數據行。 注： 外連接強以使不滿足條件的記錄也出現在結果集中。 外連接語法： Select 字段名列表 from 表名 Left|Right|Full [Outer] join 表名 ON 連接條件 外連接分為: 1。左外連接(LEFT OUTER JOIN或LEFT JOIN)： 在結果表中包含第一個表中滿足條件的所有記錄。 如果是在連接條件上匹配的記錄，則第二個表返回相應值，否則第二個表返回空值。 select 學生表.學號,學生表.姓名,成績表.課程代號,成績表.課程成績 from 學生表 left outer join 成績表 on 學生表.學號=成績表.學號 2。右外連接(RIGHT OUTER JOIN或RIGHT JOIN)： 在結果表中包含第二個表中滿足條件的所有記錄。 如果是在連接條件上匹配的記錄，則第一個表返回相應值，否則第一個表返回空值。 select 學生表.學號,學生表.姓名,成績表.課程代號,成績表.課程成績 from 學生表 right outer join 成績表 on 學生表.學號=成績表.學號 3。全外連接(FULL OUTER JOIN或FULL JOIN): 在結果表中包含兩個表中滿足條件的所有記錄。 如果是在連接條件上匹配的元組，則另一個表返回相應值，否則另一個表返回空值。 select 學生表.學號,學生表.姓名,成績表.課程代號,成績表.課程成績 from 學生表 full outer join 成績表 on 學生表.學號=成績表.學號 例子： SELECT a.*,b.* FROM student as a left JOIN sclass as b ON a.sno=b.sno and a.sno=''9502101'' go SELECT a.*,b.* FROM student as a right JOIN sclass as b ON a.sno=b.sno and a.sno=''9502101'' go SELECT a.*,b.* FROM student as a full JOIN sclass as b ON a.sno=b.sno go ---------------------------- 四。交叉連接: 交叉連接不使用任何連接條件來限制結果集合,將各表的記錄以“笛卡爾”積的方式組合起來, 是分別使用兩個數據源中的行以所有可能的方式進行組合, 即數據集中的每一行都要與另一表每一行組成一個新的行. 例如:一表中有三條記錄,另一表有4條記錄,交叉連接後,結果集合將由12條記錄組成. 交叉連接(CROSS JOIN)沒有WHERE 子句，它返回被連接的兩個表所有數據行的笛卡爾積， 返回到結果集合中的數據行數等於第一個表中符合查詢條件的數據行數乘以第二個表中 符合查詢條件的數據行數。 例， titles表中有6類圖書，而publishers表中有8家出版社， 則下列交叉連接檢索到的記錄數將等於6*8=48行。 select * from student,sclass SELECT * FROM student a CROSS JOIN sclass ORDER BY a.sno ---------------------------- 五。聯合查詢 UNION運算符可以將兩個或兩個以上上SELECT語句的查詢結果集合合並成一個結果集合顯示， 即執行聯合查詢。 UNION的語法格式為： select_statement UNION [ALL] selectstatement [UNION [ALL] selectstatement][…n] 其中selectstatement為待聯合的SELECT查詢語句。 ALL選項表示將所有行合並到結果集合中。不指定該項時， 被聯合查詢結果集合中的重復行將只保留一行。 聯合查詢時，查詢結果的列標題為第一個查詢語句的列標題。 因此，要定義列標題必須在第一個查詢語句中定義。要對聯合查詢結果排序時， 也必須使用第一查詢語句中的列名、列標題或者列序號。 在使用UNION 運算符時，應保證每個聯合查詢語句的選擇列表中有相同數量的表達式， 並且每個查詢選擇表達式應具有相同的數據類型，或是可以自動將它們轉換為相同的數據類型 。 在自動轉換時，對於數值類型，系統將低精度的數據類型轉換為高精度的數據類型。 在包括多個查詢的UNION語句中，其執行順序是自左至右，使用括號可以改變這一執行順序。 例如： 查詢1 UNION (查詢2 UNION 查詢3) select topicbody,posttime from bbs_topic union all select replybody,posttime from bbs_reply ---------------------------- 六。復雜查詢 ---復合連接 select a.學號,a.姓名,b.課程代號,b.課程成績,c.課程名稱,d.教師代號 from 學生表 a,成績表 b,課程表 c,教學表 d where (a.學號=b.學號) and (b.課程代號=c.課程代號) and(c.課程代號=d.課程代號) 三個表以上的連接 SELECT dbo.kb.xq, dbo.kbk.kcmc, dbo.kbk.lbdh, dbo.kbk.jsmc, dbo.kb.jse, dbo.bj.bj, dbo.kb.jc, 2 AS num, dbo.kb.zc, CASE dbo.kb.ds WHEN ''單'' THEN ''1'' WHEN ''雙'' THEN ''2'' WHEN '' '' THEN ''0'' END AS ds, dbo.kb.zc1, dbo.kb.zc2 FROM dbo.kb INNER JOIN dbo.bj ON dbo.kb.bh = dbo.bj.bh INNER JOIN dbo.kbk ON dbo.kb.xq = dbo.kbk.xq AND dbo.kb.bh = dbo.kbk.bh AND dbo.kb.kcdm = dbo.kbk.kcdm WHERE (dbo.kb.jc = 1) OR (dbo.kb.jc = 3) OR (dbo.kb.jc = 5) OR (dbo.kb.jc = 7) OR (dbo.kb.jc = 9) OR (dbo.kb.jc = 11) ---------------------------- 總結： 無論哪種連接都不能對text、ntext和image數據類型列進行直接連接， 但可以對這三種列進行間接連接。 例如： SELECT p1.pub_id?p2.pub_id?p1.pr_info FROM pub_info AS p1 INNER JOIN pub_info AS p2 ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
