51Testing软件测试论坛

标题: mysql的左、右外连接（多个相同字段） [打印本页]

作者: fusm1008 时间: 2013-3-26 13:37
标题: mysql的左、右外连接（多个相同字段）
本帖最后由 fusm1008 于 2013-3-26 13:38 编辑

1、原始的两张表
mysql> select * from t1;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1  | zsb  | 32  |
| 2  | fusm | 31  |
| 3  | zjx  | 3 |
+----+------+-----+
3 rows in set

mysql> select * from t2;
+----+-------+-------+
| id | name  | grade |
+----+-------+-------+
| 1  | zsb2  | 11 |
| 2  | fusm2 | 22 |
| 4  | fusq2 | 44 |
| 5  | fusq2 | 55 |
+----+-------+-------+
4 rows in set
2、左外连接
mysql> select t1.id,t1.name,age,t2.id,t2.name,grade from t1 left join t2 on t1.id=t2.id;
+----+------+-----+------+-------+-------+
| id | name | age | id | name  | grade |
+----+------+-----+------+-------+-------+
| 1  | zsb  | 32  | 1 | zsb2  | 11 |
| 2  | fusm | 31  | 2 | fusm2 | 22 |
| 3  | zjx  | 3 | NULL | NULL  | NULL  |
+----+------+-----+------+-------+-------+
3 rows in set
3、右外连接
mysql> select t1.id,t1.name,age,t2.id,t2.name,grade from t1 right join t2 on t1.id=t2.id;
+------+------+------+----+-------+-------+
| id | name | age  | id | name  | grade |
+------+------+------+----+-------+-------+
| 1 | zsb  | 32 | 1  | zsb2  | 11 |
| 2 | fusm | 31 | 2  | fusm2 | 22 |
| NULL | NULL | NULL | 4  | fusq2 | 44 |
| NULL | NULL | NULL | 5  | fusq2 | 55 |
+------+------+------+----+-------+-------+
4 rows in set
4、内连接
mysql> select t1.id,t1.name,age,t2.id,t2.name,grade from t1 inner join t2 on t1.id=t2.id;
+----+------+-----+----+-------+-------+
| id | name | age | id | name  | grade |
+----+------+-----+----+-------+-------+
| 1  | zsb  | 32  | 1  | zsb2  | 11 |
| 2  | fusm | 31  | 2  | fusm2 | 22 |
+----+------+-----+----+-------+-------+
2 rows in set

作者: 丝路 时间: 2013-4-7 16:32
谢谢分享！

作者: 水晶叶子 时间: 2013-4-11 11:26
多谢

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)