MySql 连接查询

连接包括内连接和外连接。

通过连接运算符可以实现多个表查询。

1 内连接查询

mysql> select * from students;
+----+--------+-------+------+------+
| id | number | name  | sex  | age  |
+----+--------+-------+------+------+
|  1 |   1111 | LiLy  | W    |   12 |
|  2 |   1112 | Lucy  | W    |   11 |
|  3 |   1113 | LiLei | M    |   13 |
|  4 |   1114 | Bird  | NULL |   33 |
+----+--------+-------+------+------+
4 rows in set (0.00 sec)

mysql> select * from scores;
+----+--------+---------+---------+
| id | number | Chinese | English |
+----+--------+---------+---------+
|  1 |   1111 |      99 |      87 |
|  2 |   1112 |      66 |      87 |
|  3 |   1113 |      55 |     100 |
|  4 |   1116 |      55 |      77 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql> select name,age,scores.Chinese,scores.English from students inner join scores on students.number=scores.number;
+-------+------+---------+---------+
| name  | age  | Chinese | English |
+-------+------+---------+---------+
| LiLy  |   12 |      99 |      87 |
| Lucy  |   11 |      66 |      87 |
| LiLei |   13 |      55 |     100 |
+-------+------+---------+---------+
3 rows in set (0.00 sec)

2 外连接查询

外连接又分为左外连接和右外连接。

Left Join： 会返还左边表中所有数据和 与右表中与连接字段匹配的数据。  Right Join与之相似。

 2.1 左连接示例

mysql> select name,age,scores.Chinese,scores.English from students left outer join scores on students.number=scores.number;
+-------+------+---------+---------+
| name  | age  | Chinese | English |
+-------+------+---------+---------+
| LiLy  |   12 |      99 |      87 |
| Lucy  |   11 |      66 |      87 |
| LiLei |   13 |      55 |     100 |
| Bird  |   33 |    NULL |    NULL |
+-------+------+---------+---------+
4 rows in set (0.00 sec)

2.2 右连接示例

mysql> select name,age,scores.Chinese from students right outer join scores on students.number=scores.number;
+-------+------+---------+
| name  | age  | Chinese |
+-------+------+---------+
| LiLy  |   12 |      99 |
| Lucy  |   11 |      66 |
| LiLei |   13 |      55 |
| NULL  | NULL |      55 |
+-------+------+---------+
4 rows in set (0.00 sec)