f(sixleaves) = sixleaves

重剑无锋 大巧不工

  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  95 随笔 :: 0 文章 :: 7 评论 :: 0 Trackbacks
  1 /*
  2 保存客户信息,FId为主键,FName为客户姓名,FAge为客户年龄
  3 */
  4 CREATE TABLE T_Customer(
  5  FId INT NOT NULL,
  6  FName VARCHAR(20NOT 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(20NOT 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(20NOT 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时的处理区别,而这其中还存在一种特殊连接就是自连接,自连接并不是独立于这几种连接的,而是这几种连接中的一种特例而存在。
posted on 2014-04-24 20:52 swp 阅读(1162) 评论(0)  编辑 收藏 引用 所属分类: Web

只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理