sql查詢語句有多種，下面總結下。首先先建三張表用于后面的實驗
--?學生表，記錄學生信息 ????CREATE?TABLE?student( ????sno?VARCHAR(10), ????sname?VARCHAR(10), ????ssex?ENUM('男','女'), ????sage?INT, ????sdept?VARCHAR(10), ????PRIMARY?KEY(sno) ); +-----------+-------+------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?| +-----------+-------+------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????| |?201215122?|?劉晨??|?女???|???19?|?CS????| |?201215123?|?王敏??|?女???|???18?|?MA????| |?201215125?|?張立??|?男???|???19?|?IS????| +-----------+-------+------+------+-------+ --?課程表，記錄課程信息，cpno是指當前記錄的先行課程的cno CREATE?TABLE?course( ????cno?INT?AUTO_INCREMENT, ????cname?VARCHAR(10), ????cpno?INT, ????ccredit?INT?NOT?NULL, ????PRIMARY?KEY(cno), ????FOREIGN?KEY(cpno)?REFERENCES?course(cno) ); +-----------+-------+------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?| +-----------+-------+------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????| |?201215122?|?劉晨??|?女???|???19?|?CS????| |?201215123?|?王敏??|?女???|???18?|?MA????| |?201215125?|?張立??|?男???|???19?|?IS????| +-----------+-------+------+------+-------+ --?選課記錄表，記錄選課信息 CREATE?TABLE?sc?( ????sno?VARCHAR(10), ????cno?INT, ????grade?INT ); +-----------+------+-------+ |?sno???????|?cno??|?grade?| +-----------+------+-------+ |?201215121?|????1?|????92?| |?201215121?|????2?|????85?| |?201215121?|????3?|????88?| |?201215122?|????1?|????90?| |?201215122?|????2?|????80?| +-----------+------+-------+
1.單表查詢
僅涉及一張表的查詢語句稱為單表查詢語句，舉個栗子。
SELECT?*?FROM?student； SELECT??FROM?student?WHERE?sage>=20;
這些語句僅涉及了一張表，所以是單表查詢語句。
2.多表查詢
與單標查詢對應，涉及多個表的查詢為多表查詢，其中又分為連接查詢、嵌套查詢、 派生表查詢、集合查詢。
2.1連接查詢
連接查詢是數據庫查詢中最常用的一種查詢語句，是指通過連接字段和連接條件連接多個表從而進行查詢，連接查詢又分為小類：等值連接、非等值連接 、自然連接、外連接、內連接、自身連接。
等值連接與非等值連接
當連接條件是等于號（=）時的連接稱之為等值連接，相反，當連接條件不是等于號就是非等值連接。
--?查詢每個學生的選修課情況,連接條件是等于，連接字段是sno SELECT?*?FROM?student,sc?WHERE?student.sno?=?sc.sno; +-----------+-------+------+------+-------+-----------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?|?sno???????|?cno??|?grade?| +-----------+-------+------+------+-------+-----------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????1?|????92?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????2?|????85?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????3?|????88?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????1?|????90?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????2?|????80?| +-----------+-------+------+------+-------+-----------+------+-------+
該連接操作的過程是，首先拿出student表中的第一條記錄，然后根據連接條件和連接字段，與 sc表中的所有記錄進行匹配，合適接連接起來形成結果表中的一個元組。然后再拿student表的 第二條記錄與sc表進行匹配，第三條記錄…，如此反復直到取完。這一匹配算法稱為嵌套循環連接算法
內連接
內連接就是等值連接或者非等值連接的另一種寫法，寫法有INNER JOIN ON或者CORSS JOIN USING兩種
--?使用內連接查詢每個學生的選修課情況,查詢結果和使用上面的等值連接一樣。 --?在mysql中，INNER可省略,CROSS?JOIN=?INNER?JOIN?=?INNER SELECT?*?FROM?student?INNER?JOIN?sc?ON?student.sno=sc.sno; SELECT?*?FROM?student?JOIN?sc?ON?student.sno=sc.sno; SELECT?*?FROM?student?CROSS?JOIN?sc?USING(sno); +-----------+-------+------+------+-------+-----------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?|?sno???????|?cno??|?grade?| +-----------+-------+------+------+-------+-----------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????1?|????92?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????2?|????85?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????3?|????88?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????1?|????90?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????2?|????80?| +-----------+-------+------+------+-------+-----------+------+-------+
外連接（左外連接、右外連接、全外連接）
外連接的存在可以彌補內連接僅匹配符合條件的元組的缺陷,也就是說，內連接僅能查詢出兩個表中符合連接條件的元組 ，而外連接可以在某種程度上彌補這種缺陷。外連接分為左外連接（以JOIN關鍵字左邊的表為基準,沒有匹配的記錄則置NULL），右外連接（以JOIN關鍵字右邊的表為基準） ，全外連接（以JOIN關鍵字左右兩邊的表為基準）。其中MySQL不支持全外連接，但是可以用集合查詢做到，即將左外連接的查詢結果和右外連接的查詢結果做UNION ALL操作。
--?左外連接，以左邊的表student為基準。 在MySQL中，OUTER關鍵字在MySQL中可省略?LEFT?JOIN=LEFT?OUTER?JOIN，RIGHT?JOIN=RIGHT?OUTER?JOIN SELECT?*?FROM?student?LEFT?OUTER?JOIN?sc?ON?student.sno=sc.sno; SELECT?*?FROM?student?LEFT?JOIN?sc?ON?student.sno=sc.sno; +-----------+-------+------+------+-------+-----------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?|?sno???????|?cno??|?grade?| +-----------+-------+------+------+-------+-----------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????1?|????92?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????2?|????85?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????3?|????88?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????1?|????90?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????2?|????80?| |?201215123?|?王敏??|?女???|???18?|?MA????|?NULL??????|?NULL?|??NULL?| |?201215125?|?張立??|?男???|???19?|?IS????|?NULL??????|?NULL?|??NULL?| +-----------+-------+------+------+-------+-----------+------+-------+ --?右外連接，注意sc和student換了位置 SELECT?*?FROM?sc?RIGHT?OUTER?JOIN?student?ON?student.sno=sc.sno; +-----------+------+-------+-----------+-------+------+------+-------+ |?sno???????|?cno??|?grade?|?sno???????|?sname?|?ssex?|?sage?|?sdept?| +-----------+------+-------+-----------+-------+------+------+-------+ |?201215121?|????1?|????92?|?201215121?|?李勇??|?男???|???20?|?CS????| |?201215121?|????2?|????85?|?201215121?|?李勇??|?男???|???20?|?CS????| |?201215121?|????3?|????88?|?201215121?|?李勇??|?男???|???20?|?CS????| |?201215122?|????1?|????90?|?201215122?|?劉晨??|?女???|???19?|?CS????| |?201215122?|????2?|????80?|?201215122?|?劉晨??|?女???|???19?|?CS????| |?NULL??????|?NULL?|??NULL?|?201215123?|?王敏??|?女???|???18?|?MA????| |?NULL??????|?NULL?|??NULL?|?201215125?|?張立??|?男???|???19?|?IS????| +-----------+------+-------+-----------+-------+------+------+-------+ --?全外連接 SELECT?*?FROM?sc?FULL?JOIN?student?ON?student.sno=sc.sno; ERROR?1054?(42S22):?Unknown?column?'sc.sno'?in?'on?clause' --?注意是UNION?ALL，而非UNION，UNION有個去重效果 SELECT?*?FROM?student?LEFT?OUTER?JOIN?sc?ON?student.sno=sc.sno UNION?ALL SELECT?*?FROM?student?RIGHT?OUTER?JOIN?sc?ON?student.sno=sc.sno; +-----------+-------+------+------+-------+-----------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?|?sno???????|?cno??|?grade?| +-----------+-------+------+------+-------+-----------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????1?|????92?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????2?|????85?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????3?|????88?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????1?|????90?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????2?|????80?| |?201215123?|?王敏??|?女???|???18?|?MA????|?NULL??????|?NULL?|??NULL?| |?201215125?|?張立??|?男???|???19?|?IS????|?NULL??????|?NULL?|??NULL?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????1?|????92?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????2?|????85?| |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?|????3?|????88?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????1?|????90?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?|????2?|????80?| +-----------+-------+------+------+-------+-----------+------+-------+
自然連接（全自然連接、左自然連接、右自然連接）
在等值連接中去除相同的屬性即為自然連接或稱全自然連接，左自然連接以左表為基準匹配， 右自然連接以右表為基準匹配
--?查詢每個學生的選修課情況，自然連接，去除相同的屬性sno SELECT?student.sno,student.sname,student.ssex,student.sage,student.sdept,sc.cno,sc.grade FROM?student,sc?WHERE?student.sno?=?sc.sno; SELECT?*?FROM?student?NATURAL?JOIN?sc; +-----------+-------+------+------+-------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?|?cno??|?grade?| +-----------+-------+------+------+-------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????|????1?|????92?| |?201215121?|?李勇??|?男???|???20?|?CS????|????2?|????85?| |?201215121?|?李勇??|?男???|???20?|?CS????|????3?|????88?| |?201215122?|?劉晨??|?女???|???19?|?CS????|????1?|????90?| |?201215122?|?劉晨??|?女???|???19?|?CS????|????2?|????80?| +-----------+-------+------+------+-------+------+-------+ SELECT?*?FROM?student?NATURAL?LEFT?JOIN?sc; +-----------+-------+------+------+-------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?|?cno??|?grade?| +-----------+-------+------+------+-------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????|????1?|????92?| |?201215121?|?李勇??|?男???|???20?|?CS????|????2?|????85?| |?201215121?|?李勇??|?男???|???20?|?CS????|????3?|????88?| |?201215122?|?劉晨??|?女???|???19?|?CS????|????1?|????90?| |?201215122?|?劉晨??|?女???|???19?|?CS????|????2?|????80?| |?201215123?|?王敏??|?女???|???18?|?MA????|?NULL?|??NULL?| |?201215125?|?張立??|?男???|???19?|?IS????|?NULL?|??NULL?| +-----------+-------+------+------+-------+------+-------+ --?sc和student位置交換了，仍已student為基準，以為王敏、張立沒有選課，所以有NULL字段 SELECT?*?FROM?sc?NATURAL?RIGHT?JOIN?student; +-----------+-------+------+------+-------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?|?cno??|?grade?| +-----------+-------+------+------+-------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????|????1?|????92?| |?201215121?|?李勇??|?男???|???20?|?CS????|????2?|????85?| |?201215121?|?李勇??|?男???|???20?|?CS????|????3?|????88?| |?201215122?|?劉晨??|?女???|???19?|?CS????|????1?|????90?| |?201215122?|?劉晨??|?女???|???19?|?CS????|????2?|????80?| |?201215123?|?王敏??|?女???|???18?|?MA????|?NULL?|??NULL?| |?201215125?|?張立??|?男???|???19?|?IS????|?NULL?|??NULL?| +-----------+-------+------+------+-------+------+-------+
自身連接
顧名思義，自身連接就是一張表，自己和自己連接。
--?'數據庫'的先修課信息，連接條件是course1.cno?=?course2.cpno SELECT?*?FROM?course?AS?course1,course?AS?course2 WHERE?course1.cno?=?course2.cpno AND?course1.cno?=?4 +-----+--------+------+---------+-----+--------+------+---------+ |?cno?|?cname??|?cpno?|?ccredit?|?cno?|?cname??|?cpno?|?ccredit?| +-----+--------+------+---------+-----+--------+------+---------+ |???4?|?數據庫?|????2?|???????4?|???7?|?PASCAL?|????4?|???????4?| +-----+--------+------+---------+-----+--------+------+---------+
2.2嵌套查詢
首先引入一個查詢塊的概念，一個 SELECT…FROM…WHERE… 形式的SQL語句稱為查詢塊。當一個查詢塊的SELECT子句或者WHERE子句中嵌套了另一個查詢塊的查詢語句就稱為嵌套查詢。最外層的查詢稱為外層查詢或父查詢，最內層的查詢稱為內層查詢或子查詢。子查詢用到了父查詢的數據（表、字段）的情況稱為相關子查詢，相反，如果沒用到就稱為不相關子查詢。 通常嵌套查詢與IN、ALL、ANY、EXISTS配合使用。
--?查詢與劉晨在同一個系中的學生（先查出劉晨所在系，再查該系中的學生） --?內層查詢可以獨立運行沒有依賴于外層，所以是不相關子查詢 SELECT?*?FROM?student?WHERE?sdept?IN?( ????SELECT?sdept?FROM?student?WHERE?sname='劉晨' ) +-----------+-------+------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?| +-----------+-------+------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????| |?201215122?|?劉晨??|?女???|???19?|?CS????| +-----------+-------+------+------+-------+ --?查詢選修了‘信息系統’的學生信息（先查出信息系統的課程號cno，再查處所有選課信息，再查出學生信息） --?同樣，也是不相關子查詢 SELECT?*?FROM?student?WHERE?sno?IN?( ????SELECT?sno?FROM?sc?WHERE?cno?IN?( ????????SELECT?cno?FROM?course?WHERE?cname='信息系統' ????)? ) +-----------+-------+------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?| +-----------+-------+------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????| +-----------+-------+------+------+-------+ --?找出每個學生超過自己選修課平均成績的選課信息(先查出平均成績，再查出選課信息) --?內層查詢無法獨立運行，所以是相關子查詢 SELECT?*?FROM?sc?AS?x?WHERE?grade?>=?( ????SELECT?AVG(grade)?FROM?sc?AS?y?WHERE?x.sno?AND?y.sno ) +-----------+------+-------+ |?sno???????|?cno??|?grade?| +-----------+------+-------+ |?201215121?|????1?|????92?| |?201215121?|????3?|????88?| |?201215122?|????1?|????90?| +-----------+------+-------+
2.3派生表查詢
個人認為也是嵌套查詢的一種，但用得比較廣泛，就提出來了。當查詢塊出現在FROM子句后面時，就稱為派生表查詢。
--?查詢所有選修了cno=1的課程的學生信息 SELECT?*?FROM?student,( ????SELECT?sno?FROM?SC?WHERE?cno=1 )?AS?tempSC WHERE?student.sno?=?tempSC.sno +-----------+-------+------+------+-------+-----------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?|?sno???????| +-----------+-------+------+------+-------+-----------+ |?201215121?|?李勇??|?男???|???20?|?CS????|?201215121?| |?201215122?|?劉晨??|?女???|???19?|?CS????|?201215122?| +-----------+-------+------+------+-------+-----------+
2.4集合查詢
涉及UNION、UNION ALL、INTERSECT、EXCEPT的查詢操作就稱為集合查詢。其中，UNION和UNION ALL都會做 并集，但UNION會去除重復的記錄。最后，MySQL不支持INTERSECT和EXCEPT。
--查詢CS系及年齡不大于19歲的學生（CS系的學生與年齡不大于19歲的學生做并集） SELECT?*?FROM?student?WHERE?sdept='CS' UNION?ALL SELECT?*?FROM?student?WHERE?sage19 AND?b.sno?IS?NOT?NULL SELECT?*?FROM?student?WHERE?sdept='CS'?AND?sage>19; +-----------+-------+------+------+-------+ |?sno???????|?sname?|?ssex?|?sage?|?sdept?| +-----------+-------+------+------+-------+ |?201215121?|?李勇??|?男???|???20?|?CS????| +-----------+-------+------+------+-------+
總結
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
