先附上數據。
CREATE TABLE `course` ( `cno` int(11) NOT NULL, `cname` char(30) CHARACTER SET utf8 NOT NULL, `ctime` int(11) NOT NULL, `scount` int(11) NOT NULL, `ctest` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `course` VALUES ('4', '應用數學基礎', '48', '120', '2016-03-10 10:08:29'); INSERT INTO `course` VALUES ('5', '生物工程概論', '32', '80', '2016-03-10 10:09:24'); INSERT INTO `course` VALUES ('1', '計算機軟件基礎', '32', '70', '2016-03-10 10:09:47'); INSERT INTO `course` VALUES ('2', '計算機硬件基礎', '24', '80', '2016-03-10 10:10:28'); INSERT INTO `course` VALUES ('8', '模擬電路設計', '28', '90', '2016-04-06 10:11:02'); INSERT INTO `course` VALUES ('7', '機械設計實踐', '48', '68', '2016-03-10 10:11:29'); INSERT INTO `course` VALUES ('3', '生物化學', '32', '40', '2016-03-29 10:11:54'); INSERT INTO `course` VALUES ('9', '數據庫設計', '16', '80', '2016-03-10 10:12:14'); INSERT INTO `course` VALUES ('6', '設計理論', '28', '45', '2016-03-10 10:12:33'); INSERT INTO `course` VALUES ('10', '計算機入門', '24', '150', '2016-03-10 10:12:53'); INSERT INTO `course` VALUES ('11', '數字電路設計基礎', '30', '125', '2016-03-10 10:13:10'); CREATE TABLE `student` ( `sno` char(4) CHARACTER SET utf8 DEFAULT NULL, `sname` char(10) CHARACTER SET utf8 DEFAULT NULL, `dname` char(10) CHARACTER SET utf8 DEFAULT NULL, `ssex` char(2) CHARACTER SET utf8 NOT NULL, `cno` int(11) NOT NULL, `mark` decimal(3,1) NOT NULL, `type` char(4) CHARACTER SET utf8 NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `student` VALUES ('9701', '劉建國', '管理工程', '男', '4', '82.5', '必修'); INSERT INTO `student` VALUES ('9701', '劉建國', '管理工程', '男', '10', '70.0', '必修'); INSERT INTO `student` VALUES ('9701', '劉建國', '管理工程', '男', '1', '78.5', '選修'); INSERT INTO `student` VALUES ('9702', '李春', '環境工程', '女', '5', '63.0', '必修'); INSERT INTO `student` VALUES ('9702', '李春', '環境工程', '女', '10', '58.0', '選修'); INSERT INTO `student` VALUES ('9703', '王天', '生物', '男', '5', '48.5', '必修'); INSERT INTO `student` VALUES ('9703', '王天', '生物', '男', '2', '86.0', '選修'); INSERT INTO `student` VALUES ('9704', '李華', '計算機', '女', '4', '76.0', '必修'); INSERT INTO `student` VALUES ('9704', '李華', '計算機', '女', '1', '92.0', '必修'); INSERT INTO `student` VALUES ('9704', '李華', '計算機', '女', '2', '89.0', '必修'); INSERT INTO `student` VALUES ('9704', '李華', '計算機', '女', '9', '80.0', '必修'); INSERT INTO `student` VALUES ('9704', '李華', '計算機', '女', '8', '70.0', '選修'); INSERT INTO `student` VALUES ('9705', '孫慶', '電子工程', '男', '8', '79.0', '必修'); INSERT INTO `student` VALUES ('9705', '孫慶', '電子工程', '男', '1', '59.0', '必修'); INSERT INTO `student` VALUES ('9705', '孫慶', '電子工程', '男', '11', '52.0', '必修'); INSERT INTO `student` VALUES ('9705', '孫慶', '電子工程', '男', '6', '68.0', '必修'); INSERT INTO `student` VALUES ('9706', '高偉', '機械工程', '男', '13', '93.0', '必修'); INSERT INTO `student` VALUES ('9706', '高偉', '機械工程', '男', '12', '88.5', '必修'); INSERT INTO `student` VALUES ('9706', '高偉', '機械工程', '男', '1', '78.0', '選修'); INSERT INTO `student` VALUES ('9706', '高偉', '機械工程', '男', '10', '76.0', '選修'); CREATE TABLE `teacher` ( `tno` int(11) NOT NULL, `tname` varchar(10) CHARACTER SET utf8 NOT NULL, `cno` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, `dname` char(10) CHARACTER SET utf8 NOT NULL, `tsex` char(2) CHARACTER SET utf8 NOT NULL, `age` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `teacher` VALUES ('1', '王軍', '4', '800', '數學', '男', '32'); INSERT INTO `teacher` VALUES ('2', '李丹', '5', '1200', '生物', '女', '54'); INSERT INTO `teacher` VALUES ('3', '王永軍', '1', '900', '計算機', '男', '40'); INSERT INTO `teacher` VALUES ('4', '劉小靜', '2', '1200', '計算機', '女', '46'); INSERT INTO `teacher` VALUES ('5', '高偉', '8', '2100', '電子工程', '男', '39'); INSERT INTO `teacher` VALUES ('6', '李偉', '7', '1200', '機械工程', '男', '29'); INSERT INTO `teacher` VALUES ('7', '劉輝', '3', '900', '生物', '女', '46'); INSERT INTO `teacher` VALUES ('8', '劉靜', '12', '1300', '經濟管理', '女', '28'); INSERT INTO `teacher` VALUES ('9', '李偉', '9', null, '計算機', '女', '43'); INSERT INTO `teacher` VALUES ('10', '劉一凱', '13', null, '計算機', '女', '33');
簡單的二表連接
SELECT tname,dname,cname,ctest from teacher,course WHERE teacher.cno=course.cno該語句的執行過程實例可以表示這樣: a,系統首先執行from子句,這裡from子句列出有兩個表teacher表和course表,DBMS講計算這兩個表的笛卡爾積,列出這兩個表中行的所以可能組合,形成一個中間表。中間表中的每條記錄包含了兩個表中的所有行。 b,然後系統執行where子句,根據teacher.cno=course.cno關系對中間表進行搜索,去除那些不滿足該關系的記錄。 c,最後系統執行select語句,從執行where子句後得到的中間表的每條記錄中,提取tname,dname,cname,ctest4個字段的信息作為結果表。 需要強調,表的連接所依據的關系是在where子句中定義的。在實際應用中,用戶要實現表的連接必然要依據一定的關系。 如果不指明連接關系,即不使用where子句。
SELECT tname,dname,cname,ctest from teacher,course
SELECT COLUMN from join_table join_type join_table on (join_condition)join_type為連接類型,可分為4種:自然連接,內連接,外連接和交叉連接。 自連接 自連接是指表與其自身進行連接,這需要使用表別名。 查詢成績中存在不及格課程的學生的姓名,所在系,所有的課程及成績信息。
SELECT s.sname,s.dname,s.cno,s.mark from student s where s.mark<60無法得到想要結果
SELECT s.sname,s.dname,s.cno,s.mark from student s where s.sno in(SELECT DISTINCT s.sno from student s where s.mark<60)得到想要結果
SELECT DISTINCT s.sname,s.dname,s.cno,s.mark from student s,student s2 where s.sno=s2.sno and s2.mark<60from子句中的兩個表實際上都是表student。為了獨立地使用它們,采用表別名方法。
SELECT s.sname,s.dname,s.cno,s.mark from student s,student s2 where s.sno=s2.sno and s2.mark<60系統首先執行from子句,將student表S1與它自身S2的笛卡爾積,作為中間表。 實際上,該中間表的每一條記錄包含兩部分信息,一部分是S1的記錄,一部分是S2的記錄。而後執行where子句,在中間表中,搜索S2中成績低於60的學生的記錄,同時要求記錄中S1與S2是同一個學生的記錄即學號相同。最後執行select語句,從中間表獲取S1中相應的信息作為結果表。 當執行where子句,從中間表中逐條搜索S2中成績低於60的學生的記錄時,由於孫慶有兩門課程不及格,所以對每門不及格的記錄都滿足搜索條件,因此導致從S1得到的信息中出現了重復的記錄。 簡單來說,中間表是沒有重復記錄的,但是S1部分字段是有重復的,而結果集提取的只是S1部分的字段,因此就有可能有重復記錄。 一般情況,自連接也可以使用子查詢的方式實現。
SELECT DISTINCT s.sname,s.dname,s.cno,s.mark from student s,student s2 where s.sno=s2.sno and s.mark<60自然連接 它將表中具有相同名稱的列自動進行記錄匹配,自然連接不必指定任何同等連接條件。 自然連接自動判斷相同名稱的列,而後形成匹配。缺點是,雖然可以指定查詢結果包括哪些列,但是不能人為地指定哪些列被匹配。另外,自然連接的一個特點是連接後的結果表中匹配的列只有一個。如上,在自然連接後的表中只有一列C。 從student表和teacher表中查詢學生姓名,所在系,所修的本系教師開設的課程的課程號以及開課教師姓名。這時候就采用natural join對兩個表進行自然連接。
SELECT sname,dname,cno,tname from student NATURAL join teacher等價
SELECT sname,s.dname,s.cno,tname from student s, teacher t where s.dname=t.dname and s.cno=t.cno
事實上,使用基於where子句的等值連接要比使用natural join運算符進行自然連接要靈活的多。 正如前面介紹的,使用natural join運算符自動判斷出具有相同名稱的列,而後形成匹配,不能人為地指定哪些列被匹配。當自然連接student和teacher表時,CNO和dname列同時被匹配,而不能只匹配一列。 外連接 不管是內連接還是帶where子句的多表查詢,都組合自多個表,並生成結果表。換句話說,如果任何一個源表中的行在另一個源表中沒有匹配,DBMS將把該行放在最後的結果表中。 而外連接告訴ODBC生成的結果表,不僅包含符合條件的行,而且還包含左表(左外連接時),右表(右外連接時)或兩個邊接表(全外連接)中所有的數據行。 SQL的外連接共有三種類型:左外連接,右外連接,全外連接。 1,左外連接 左外連接,left outer join ,告訴DBMS生成的結果表中,除了包括匹配行外,還包括join關鍵字(from子句中)左邊表的不匹配行。 左外連接實際可以表示為: 左外連接=內連接+左邊表中失配的元組。 其中,缺少的右邊表中的屬性值用null表示。如下:
SELECT s.sno,sname,s.cno,cname,ctest,mark from student s LEFT JOIN course c on s.cno=c.cno ORDER BY sname右外連接 右外連接,right outer join ,告訴DBMS生成的結果表中,除了包括匹配行外,還包括join關鍵字(from子句中)右邊表的不匹配行。 右外連接實際可以表示為: 右外連接=內連接+右邊表中失配的元組。 其中,缺少的左邊表中的屬性值用null表示。如下:
SELECT s.sno,sname,s.cno,cname,ctest,mark from student s RIGHT JOIN course c on s.cno=c.cno ORDER BY sname
全外連接 全外連接,full outer join,告訴DBMS生成的結果表中,除了包括匹配行外,還包括join關鍵字(from子句中)左邊表和右邊表的不匹配行。 可以這樣表示: 全外連接=內連接+左邊表中失配的元組+右邊表中失配的元組
SELECT s.sno,sname,s.cno,cname,ctest,mark from student s full OUTER JOIN course c on s.cno=c.cno ORDER BY sname本人使用mysql數據庫,因為mysql暫時還不支持全外連接full的功能.