程序媛计划——mysql连接表-优快云博客

本文详细介绍了SQL中的三种连接类型：inner join（等值连接/内连接）、left join和right join，并通过具体的MySQL示例展示了如何使用这些连接来从不同的表中获取所需的数据。

#inner join等值连接／内连接

mysql> select * from info;
+------+-------------+----------+
| name | phone       | address  |
+------+-------------+----------+
| zhao | 13199999999 | Beijing  |
| qian | 1328888888  | Harbin   |
| sun  | 13366666666 | Shanghai |
+------+-------------+----------+
3 rows in set (0.00 sec)

mysql> select * from score;
+----+------+-------+------------+
| id | name | score | evaluation |
+----+------+-------+------------+
|  1 | zhao | 98.00 | 10         |
|  2 | qian | 90.30 | 10         |
|  3 | sun  | 70.00 | 10         |
|  4 | li   | 80.50 | 10         |
+----+------+-------+------------+
4 rows in set (0.00 sec)

#inner join——按照相同的name值（筛选行）连接两个表中的某些字段（筛选列）构成表

mysql> select score.id,score.name,info.phone from score inner join info on score.name=info.name;
+----+------+-------------+
| id | name | phone       |
+----+------+-------------+
|  1 | zhao | 13199999999 |
|  2 | qian | 1328888888  |
|  3 | sun  | 13366666666 |
+----+------+-------------+
3 rows in set (0.00 sec)

#left join——读取左边表score的字段的所有值，按照相同的name值连接两个表中的字段构成表（右边的表没有对应记录的填null）

mysql> select score.id,score.name,info.phone from score left join info on score.name=info.name;
+----+------+-------------+
| id | name | phone       |
+----+------+-------------+
|  1 | zhao | 13199999999 |
|  2 | qian | 1328888888  |
|  3 | sun  | 13366666666 |
|  4 | li   | NULL        |
+----+------+-------------+
4 rows in set (0.00 sec)

#类似的有right join

mysql> select score.id,score.name,info.phone from score right join info on score.name=info.name;
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
|    1 | zhao | 13199999999 |
|    2 | qian | 1328888888  |
|    3 | sun  | 13366666666 |
+------+------+-------------+
3 rows in set (0.00 sec)