mysql 多表连接查询

本章主要总结了：

mysql的多表连接查询，包括，inner join，left join，right join，full join（由于mysql不支持该连接，所以为了达到效果，只能用union组合左右连接来达到效果）

步骤：

1.建两张表

2.语句介绍

3.mysql 语句

1. table student

+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wanger   |
+----+----------+

2.table book

+----+------+
| id | name |
+----+------+
|  1 | java |
|  2 | css  |
|  4 | jsp  |
+----+------+

3. inner join 取到两张表中共有的部分

select student.*,book.* from student inner join book on student.id=book.id;
+----+----------+----+------+
| id | name     | id | name |
+----+----------+----+------+
|  1 | zhangsan |  1 | java |
|  2 | lisi     |  2 | css  |
+----+----------+----+------+

4. left join 显示左表的所有属性，右表中显示匹配的属性，缺省值为null

select student.*,book.* from student left join book on student.id=book.id;
+----+----------+------+------+
| id | name     | id   | name |
+----+----------+------+------+
|  1 | zhangsan |    1 | java |
|  2 | lisi     |    2 | css  |
|  3 | wanger   | NULL | NULL |
+----+----------+------+------+

5.right join 显示右表的所有属性，左表中显示匹配的属性，缺省值为null

select student.*,book.* from student right join book on student.id=book.id;
+------+----------+----+------+
| id   | name     | id | name |
+------+----------+----+------+
|    1 | zhangsan |  1 | java |
|    2 | lisi     |  2 | css  |
| NULL | NULL     |  4 | jsp  |
+------+----------+----+------+

6. full join采用组合左右连接的方式，显示两张表中的所有信息，缺省值为null

select student.*,book.* from student left join book on student.id=book.id union select student.*,book.* from student right join book on student.id=book.id;
+------+----------+------+------+
| id   | name     | id   | name |
+------+----------+------+------+
|    1 | zhangsan |    1 | java |
|    2 | lisi     |    2 | css  |
|    3 | wanger   | NULL | NULL |
| NULL | NULL     |    4 | jsp  |
+------+----------+------+------+

以上是个人总结文档，

以方便以后查阅，

如果路过者调试有问题可以给我留言，一起探讨，我这边测试通过的