MYSQL复习笔记10-连接

Date: 20140219
Auth: Jin

一、介绍
连接是二元运算，可以对两个表或多个表进行查询。
T-SQL中分两大类，一是符合SQL标准的连接谓词表示形式，二是T-SQL扩展的使用关键字JOIN的表示形式。

二、连接谓词
where子句使用比较运算符给出连接条件对表进行连接，将这种表示形式称为连接谓词表示形式。
连接谓词中的比较字符可以是〈，〈=，〉，〉=，！=，〈〉，!<, !>,当比较符为=时就是等值连接。若在目标列中除去相同的字段名（重复的只保留一个 ），则为自然连接。

1、select
mysql> select Product.*，ProductPrice.* from Product，ProductPrice where Product.productId=ProductPrice.productId;
mysql> select a.*,b.* from Product as a,ProductPrice as b where a.productId=b.productId;
mysql> select a.*,b.* from Product as a,ProductPrice as b where a.productId>b.productId;
注意：a的id会依次和b每一个id对比。

2.delete
mysql> delete a.*,b.* from Product as a,ProductPrice as b where a.productId=b.productId and a.created < '2004-01-01';
Query OK, 8 rows affected (0.00 sec)

3.update
mysql> update Product as a,ProductPrice as b set b.price=a.price*0.8 where a.productId=b.productId;

三、以JOIN关键字指定的连接
INNER JOIN 内连接
OUTER JOIN 外连接
CROSS JOIN 交叉连接
1、内连接 INNER
tableA as a INNER JOIN tableB as B ON A.productId = B.productId
tableA P INNER JOIN tableB PP ON P.productId = PP.productId
内连接是系统默认，可以省略关键字,使用内连接后仍可以使用WHERE子句指定条件。

mysql> select a.*,b.* from Product as a INNER JOIN ProductPrice as b ON a.productID = b.productID WHERE a.created < '2004-
01-01';
mysql> delete a.*,b.* from Product as a INNER JOIN ProductPrice as b ON a.productID = b.productID WHERE a.created < '2004-
01-01';
mysql> update Product as a INNER JOIN ProductPrice as b ON a.productId=b.productId set b.price=a.price*0.6 where a.created <
'2004-01-01';

2.外连接 OUTER
左外连接 LEFT OUTER JOIN: 结果表中除了包含满足连接条件的行外，还包括左表的行
右外连接 RIGHT OUTER JOIN: 结果表中除了包含满足连接条件的行外，还包括右表的行
完全外连接 FULL OUTER JOIN: 结果表中除了包含满足连接条件的行外，还包括两个表的所有行

四，例题
1.SQL SERVER中
USE PXSCJ
GO
select * from A;
1 a
2 b
3 c
4 d
5 e

select * from B;
1 A
2 B
2 C
5 D

内连接 4个记录
select * from A inner join B on A.id=B.id;
1 a 1 A
2 b 2 B
2 b 2 C
5 e 5 D

自然连接 4个记录
select A.*,B.uname from A inner join B on A.id=B.id;
1 a A
2 b B
2 b C
5 e D

左外连接 加左边表的记录 6个
select * from A left outer join B on A.id=B.id;
1 a 1 A
2 b 2 B
2 b 2 C
3 c NULL NULL
4 d NULL NULL
5 e 5 D

右外连接 加左边表的记录 4个
select * from A right outer join B on A.id=B.id;
1 a 1 A
2 b 2 B
2 b 2 C
5 e 5 D

完全外连接 包括两个表的所有行 6
select * from A full outer join B on A.id=B.id;
1 a 1 A
2 b 2 B
2 b 2 C
3 c NULL NULL
4 d NULL NULL
5 e 5 D

2、MYSQL
1）准备数据
mysql> create table A( id int(6) not null, name char(6) );
mysql> create table B( id int(6) not null, uname char(6) );
mysql> insert into A(id,name) value (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
mysql> select * from A;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)

mysql> insert into B(id,Uname) value (1,'A'),(2,'B'),(2,'C'),(5,'D');
mysql> select * from B;
+----+-------+
| id | uname |
+----+-------+
| 1 | A |
| 2 | B |
| 2 | C |
| 5 | D |
+----+-------+

2 连接
内连接 4个记录
mysql> select * from A inner join B on A.id=B.id;
+----+------+----+-------+
| id | name | id | uname |
+----+------+----+-------+
| 1 | a | 1 | A |
| 2 | b | 2 | B |
| 2 | b | 2 | C |
| 5 | e | 5 | D |
+----+------+----+-------+
4 rows in set (0.01 sec)

自然连接 4个记录
mysql> select A.*,B.uname from A inner join B on A.id=B.id;
+----+------+-------+
| id | name | uname |
+----+------+-------+
| 1 | a | A |
| 2 | b | B |
| 2 | b | C |
| 5 | e | D |
+----+------+-------+
4 rows in set (0.00 sec)

左外连接 加左边表的记录 6个
mysql> select * from A left outer join B on A.id=B.id;
+----+------+------+-------+
| id | name | id | uname |
+----+------+------+-------+
| 1 | a | 1 | A |
| 2 | b | 2 | B |
| 2 | b | 2 | C |
| 3 | c | NULL | NULL |
| 4 | d | NULL | NULL |
| 5 | e | 5 | D |
+----+------+------+-------+
6 rows in set (0.03 sec)

右外连接 加左边表的记录 4个
mysql> select * from A right outer join B on A.id=B.id;
+------+------+----+-------+
| id | name | id | uname |
+------+------+----+-------+
| 1 | a | 1 | A |
| 2 | b | 2 | B |
| 2 | b | 2 | C |
| 5 | e | 5 | D |
+------+------+----+-------+
4 rows in set (0.00 sec)

完全外连接 包括两个表的所有行 6

mysql> select * from A full outer join B on A.id=B.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join B
on A.id=B.id' at line 1

由于mysql 不支持 full join 经过查资料可以这样写
mysql> select * from A left outer join B on A.id=B.id union select * from A right outer join B on A.id=B.id;
+------+------+------+-------+
| id | name | id | uname |
+------+------+------+-------+
| 1 | a | 1 | A |
| 2 | b | 2 | B |
| 2 | b | 2 | C |
| 3 | c | NULL | NULL |
| 4 | d | NULL | NULL |
| 5 | e | 5 | D |
+------+------+------+-------+
6 rows in set (0.00 sec)