1 /*
2 保存客戶信息，FId為主鍵，FName為客戶姓名，FAge為客戶年齡
3 */
4 CREATE TABLE T_Customer(
5 FId INT NOT NULL,
6 FName VARCHAR(20) NOT NULL,
7 FAge INT,
8 PRIMARY KEY(FId)
9 );
10
11 /*
12 保存訂單類型，FId為主鍵，FName為類型名
13 */
14 CREATE TABLE T_OrderType(
15 FId INT NOT NULL,
16 FName VARCHAR(20) NOT NULL,
17 PRIMARY KEY(FId)
18 );
19
20 /*
21 T_Order保存訂單信息，FId為主鍵，FNumber為訂單號，FPrice為價格，FCustomerId為客戶的主鍵
22 */
23 CREATE TABLE T_Order(
24 FId INT NOT NULL,
25 FNumber VARCHAR(20) NOT NULL,
26 FPrice DECIMAL(10,2),
27 FCustomerId INT,
28 FTypeId INT,
29 PRIMARY KEY(FId)
30 );
31
32
33 INSERT INTO T_Customer
34 (FId,FName,FAge)
35 VALUES
36 (1,'TOM',21),
37 (2,'MIKE',24),
38 (3,'JACK',30),
39 (4,'TOM',25),
40 (5,'LINDA',NULL);
41
42 INSERT INTO T_OrderType(FId,FName)
43 VALUES
44 (1,'MarketOrder'),
45 (2,'LimitOrder'),
46 (3,'Stop Order'),
47 (4,'StopLimit Order');
48
49 INSERT INTO T_Order
50 (FId,FNumber,FPrice,FCustomerId,FTypeId)
51 VALUES
52 (1,'K001',100,1,1),
53 (2,'K002',200,1,1),
54 (3,'T003',300,1,2),
55 (4,'N002',100,2,2),
56 (5,'N003',500,3,4),
57 (6,'T001',300,4,3),
58 (7,'T002',100,NULL,1);
59
60 #查看T_Customer表數據
61 SELECT * FROM T_Customer;
62 #查看T_OrderType數據
63 SELECT * FROM T_OrderType;
64 #查看T_Order表中的數據
65 SELECT * FROM T_Order;
66
67 #檢索所有的客戶姓名為MIKE的客戶的訂單號以及訂單價格
68
69 SELECT T_Order.FId,FNumber,FPrice
70 FROM
71 T_Order INNER JOIN T_Customer
72 ON FCustomerId = T_Customer.FId
73 WHERE T_Customer.FName = 'TOM';
74 /*
75 大多數數據庫中INNER JOIN 中的INNER是可選的，而就是說INNER JOIN是默認的連接方式
76 以上在ON后加等號的也都稱為等值連接。這是按照ON后邏輯運算符來分的類，可以分為兩類
77 。
78 */
79
80 #不等值連接
81 #檢索價格小于每個客戶的年齡的五倍值的訂單列表
82 SELECT o.FNumber,o.FPrice,c.FName,c.FAge
83 FROM
84 T_Order o INNER JOIN T_Customer c
85 ON o.FPrice < c.FAge * 5;
86
87 #交叉連接
88 與內連接比起來，交叉連接非常簡單，因為它不存在ON字句。交叉連接會將涉及到的所有表中的所有記錄
89 都包含在結果集中。可以采用兩種方式來定義交叉連接，分別是隱式的和顯示的。
90
91 隱式的交叉連接，其是就是數學上兩張表做全相乘，在FROM字句后跟上表名，用逗號分隔就行。這種方式幾
92 乎可以被任意的數據庫支持，如下面的sql語句將T_Customer表和T_Order表做交叉連接
93 SELECT T_Customer.FId, T_Customer.FName, T_Customer.FAge,
94 T_Order.FId, T_Order.FNumber, T_Order.FPrice
95 FROM T_Customer, T_Order;
96
97
98 #自連接（特例）
99 其實參與連接的表完全可以是同一張表,也就是表與其自身相互連接，這樣的連接被稱為自連接。自連接
100 并不是獨立于交叉連接、內連接、外連接等這些鏈接方式之外的另外一種連接方式，而知識這些連接方式
101 的一種特例，也就是交叉連接、內連接、外連接等連接方式中只要參與連接同一張表可以被稱為自連接。
102 SELECT o1.FNumber,o1.FPrice,o1.FTypeId,
103 o2.FNumber,o2.FPrice,o2.FTypeId
104 FROM T_Order o1
105 INNER JOIN T_Order o2
106 ON o1.FTypeId=o2.FTypeId and o1.FId<o2.FId
107
108
109
110 #外部連接
111 SELECT o.FNumber,o.FPrice,o.FCustomerId,c.FName,c.FAge
112 FROM
113 T_Order o INNER JOIN T_Customer c
114 ON o.FCustomerId = c.FId;
115 +---------+--------+-------------+-------+------+
116 | FNumber | FPrice | FCustomerId | FName | FAge |
117 +---------+--------+-------------+-------+------+
118 | K001 | 100.00 | 1 | TOM | 21 |
119 | K002 | 200.00 | 1 | TOM | 21 |
120 | T003 | 300.00 | 1 | TOM | 21 |
121 | N002 | 100.00 | 2 | MIKE | 24 |
122 | N003 | 500.00 | 3 | JACK | 30 |
123 | T001 | 300.00 | 4 | TOM | 25 |
124 +---------+--------+-------------+-------+------+
125
126 而其中T002訂單沒有顯示，因為其FCustomeredId = NULL，對于內聯接來說，沒匹配是不顯示的。
127 那么當我們在沒匹配時也需要顯示，但在另外那欄目填上NULL，就需要用外聯接來解決。
128
129
130 外連接與內連接的區別：
131 區別在對于空值的處理。外部連接不需要兩個表具有匹配記錄，這樣可以指定某個表中的記錄是放到結果集中。
132 根據那個表中的記錄總是放到結果集中，外部連接又分為三種類型，右外連接，簡稱右連接（RIGHT [OUTER] JOIN）、左外連接，簡稱
133 左連接（LEFT [OUTER] JOIN）和全外部連接（FULL OUTER JOIN）。
134
135 全外部連接其實就是 左外連接和右外連接的并集。同時要注意的是這里的左表和右表是相對于JOIN關鍵字來說的，位于JOIN關鍵字左側
136 的表即稱為左表，而位于JOIN關鍵字右側的表即稱為右表。
137 比如：
138 SELECT o.FNumber,o.FPrice,o.FCustomerId,
139 c.FName,c.FAge
140 FROM
141 T_Order o INNER JOIN T_Customer c
142 ON o.FCustomerId = c.FId;
143 這里T_Order就是左表，T_Customer則是右表
144
145 #左外部連接
146 在左外部連接中，左表中所有的記錄都會被放到結果集中，無論是否在右表中存在匹配記錄。比如下面的SQL語句用來實現
147 “查詢每張訂單號、價格、對應的客戶姓名以及客戶年齡，如果沒有相應的客戶，則在客戶信息處顯示空格”
148 SELECT o.FNumber,o.FPrice,o.FCustomerId,
149 c.FName,c.FAge
150 FROM
151 T_Order o LEFT OUTER JOIN T_Customer c
152 ON o.FCustomerId = c.FId;
153 +---------+--------+-------------+-------+------+
154 | FNumber | FPrice | FCustomerId | FName | FAge |
155 +---------+--------+-------------+-------+------+
156 | K001 | 100.00 | 1 | TOM | 21 |
157 | K002 | 200.00 | 1 | TOM | 21 |
158 | T003 | 300.00 | 1 | TOM | 21 |
159 | N002 | 100.00 | 2 | MIKE | 24 |
160 | N003 | 500.00 | 3 | JACK | 30 |
161 | T001 | 300.00 | 4 | TOM | 25 |
162 | T002 | 100.00 | NULL | NULL | NULL |
163 +---------+--------+-------------+-------+------+
164
165 雖然左外部連接包含左表中的所有記錄，但是它只提供出示的結果集，WHERE語句仍然會
166 改變最終的結果集。比如為上面的SQL語句添加一個WHERE子句，使得結果中不包含價格小于
167 150元的訂單：
168 SELECT o.FNumber,o.FPrice,o.FCustomerId,
169 c.FName,c.FAge
170 FROM T_Order o
171 LEFT OUTER JOIN T_Customer c
172 ON o.FCustomerId=c.FId
173 WHERE o.FPrice>=150;
174
175 +---------+--------+-------------+-------+------+
176 | FNumber | FPrice | FCustomerId | FName | FAge |
177 +---------+--------+-------------+-------+------+
178 | K002 | 200.00 | 1 | TOM | 21 |
179 | T003 | 300.00 | 1 | TOM | 21 |
180 | N003 | 500.00 | 3 | JACK | 30 |
181 | T001 | 300.00 | 4 | TOM | 25 |
182 +---------+--------+-------------+-------+------+
183
184 執行以后我們在輸出結果中看到下面的執行結果：
185 盡管左外部連接返回了T_Order表中的所有記錄，但是由于WHERE語句的過濾，包括
186 訂單號為T002在內的所有價格小于150元的訂單全部被排除在了結果集之外。
187
188 #右外部連接
189 和左外部連接就是個相對的概念而已
190 #全外部連接
191 幾乎所有的數據庫都支持左外部連接和右外部連接，但是全外部連接并不是所有數據庫都支持，特別是mysql
192 。而我們的練習是給予mysql的，但在mysql中我們可以通過使用UNION運算符來取兩個查詢結果集的并集。
193 SELECT o.FNumber,o.FPrice,o.FCustomerId,
194 c.FName,c.FAge
195 FROM T_Order o
196 LEFT OUTER JOIN T_Customer c
197 ON o.FCustomerId=c.FId
198 UNION
199 SELECT o.FNumber,o.FPrice,o.FCustomerId,
200 c.FName,c.FAge
201 FROM T_Order o
202 RIGHT OUTER JOIN T_Customer c
203 ON o.FCustomerId=c.FId;
204 +---------+--------+-------------+-------+------+
205 | FNumber | FPrice | FCustomerId | FName | FAge |
206 +---------+--------+-------------+-------+------+
207 | K001 | 100.00 | 1 | TOM | 21 |
208 | K002 | 200.00 | 1 | TOM | 21 |
209 | T003 | 300.00 | 1 | TOM | 21 |
210 | N002 | 100.00 | 2 | MIKE | 24 |
211 | N003 | 500.00 | 3 | JACK | 30 |
212 | T001 | 300.00 | 4 | TOM | 25 |
213 | T002 | 100.00 | NULL | NULL | NULL |
214 | NULL | NULL | NULL | LINDA | NULL |
215 +---------+--------+-------------+-------+------+
216 8 rows in set (0.00 sec)
217
218
在總結下其實表的連接也就主要有內連接，外連接，交叉連接。而交叉連接就是所謂的全相乘，而外連接又分為左外連接，右外連接，全部外連接三種。至于外連接和內連接的區別主要是在于匹配到NULL時的處理區別，而這其中還存在一種特殊連接就是自連接，自連接并不是獨立于這幾種連接的，而是這幾種連接中的一種特例而存在。