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 谢谢分享! 多谢:handshake
页:
[1]