天天看點
傳回

mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

先附上資料 

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
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

該語句的執行過程執行個體可以表示這樣：

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
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

從結果可以看到，每個教師的資訊均與所有課程資訊進行了比對連接配接。它實際傳回連接配接表中所有資料行的笛卡爾積，其結果集合中的資料行數等于第一個表中符合查詢條件的資料行乘以第二個表中符合查詢條件的資料行數，即10X11=110條記錄。

采用join關鍵字建立連接配接

        也可以在from子句中，通過連接配接關鍵字實作表的連接配接，這樣有助于将連接配接操作與where的搜尋條件區分開來。

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
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

無法得到想要結果

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)
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

得到想要結果

SELECT DISTINCT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s2.mark<60
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

from子句中的兩個表實際上都是表student。為了獨立地使用它們，采用表别名方法。

SELECT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s2.mark<60
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

系統首先執行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
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

自然連接配接

它将表中具有相同名稱的列自動進行記錄比對，自然連接配接不必指定任何同等連接配接條件。

mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

自然連接配接自動判斷相同名稱的列，而後形成比對。缺點是，雖然可以指定查詢結果包括哪些列，但是不能人為地指定哪些列被比對。另外，自然連接配接的一個特點是連接配接後的結果表中比對的列隻有一個。如上，在自然連接配接後的表中隻有一列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
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

事實上，使用基于where子句的等值連接配接要比使用natural join運算符進行自然連接配接要靈活的多。

正如前面介紹的，使用natural join運算符自動判斷出具有相同名稱的列，而後形成比對，不能人為地指定哪些列被比對。當自然連接配接student和teacher表時，CNO和dname列同時被比對，而不能隻比對一列。

外連接配接

不管是内連接配接還是帶where子句的多表查詢，都組合自多個表，并生成結果表。換句話說，如果任何一個源表中的行在另一個源表中沒有比對，DBMS将把該行放在最後的結果表中。

而外連接配接告訴ODBC生成的結果表，不僅包含符合條件的行，而且還包含左表（左外連接配接時），右表（右外連接配接時）或兩個邊接表（全外連接配接）中所有的資料行。

SQL的外連接配接共有三種類型：左外連接配接，右外連接配接，全外連接配接。

1，左外連接配接

左外連接配接，left outer join ,告訴DBMS生成的結果表中，除了包括比對行外，還包括join關鍵字（from子句中）左邊表的不比對行。

左外連接配接實際可以表示為：

左外連接配接=内連接配接+左邊表中失配的元組。

其中，缺少的右邊表中的屬性值用null表示。如下：

mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料
SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s LEFT JOIN course c
on s.cno=c.cno
ORDER BY sname
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

右外連接配接

右外連接配接，right outer join ,告訴DBMS生成的結果表中，除了包括比對行外，還包括join關鍵字（from子句中）右邊表的不比對行。

右外連接配接實際可以表示為：

右外連接配接=内連接配接+右邊表中失配的元組。

其中，缺少的左邊表中的屬性值用null表示。如下：

mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料
SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s RIGHT JOIN course c
on s.cno=c.cno
ORDER BY sname
mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料

全外連接配接

全外連接配接，full outer join，告訴DBMS生成的結果表中，除了包括比對行外，還包括join關鍵字（from子句中）左邊表和右邊表的不比對行。

可以這樣表示：

全外連接配接=内連接配接+左邊表中失配的元組+右邊表中失配的元組

mysql,SQL标準,多表查詢中内連接配接，外連接配接，自然連接配接等詳解之查詢結果集的笛卡爾積的演化先附上資料
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的功能.

 一些語句流程順序，等我有空回顧在寫把。等我。勿急躁。

作者：

intsmaze(劉洋)

出處：

http://www.cnblogs.com/intsmaze/

老鐵，你的--->推薦，--->關注,--->評論--->是我繼續寫作的動力。

微信公衆号号：Apache技術研究院

由于部落客能力有限，文中可能存在描述不正确，歡迎指正、補充！

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利。

sql 關系型資料庫 mysql 資料庫管理 mysql外連接配接查詢 mysql檢視版本号 mysql查詢内連接配接 mysql sql查詢結果 查詢結果mysql sql
上一篇: 由HashMap雜湊演算法引出的求餘%和與運算&amp;轉換問題
下一篇: mybatis源碼解讀(四)——事務的配置

繼續閱讀