连接语法

select 字段
from 表名1，表名2
where 表名1.key =  表名2.key
[group by 字段]
[having 条件]
[order by 字段 [desc|asc]]

select 字段...
from 表名1
【连接类型】join 表名2 on 表名1.key = 表名2.key
[where 条件]
[group by 条件]
[having 条件]
[order by 字段 [desc|asc]

等值连接

查询学生id,名字，班级名称 方法一：

SELECT
    student.id,
    student.name student_name,
    class.name class_name 
FROM
    student,
    class 
WHERE
    student.class_id = class.id;

+----+--------------+------------+
| id | student_name | class_name |
+----+--------------+------------+
|  1 | 张三         | 班级1      |
|  2 | 王五         | 班级1      |
|  3 | 李四         | 班级1      |
|  4 | 小红         | 班级2      |
|  5 | 小花         | 班级2      |
|  6 | huyongjian   | 班级3      |
|  7 | Join         | 班级3      |
+----+--------------+------------+

方法二：

SELECT
    student.id,
    student.name student_name,
    class.name class_name 
FROM
    student
    INNER JOIN class ON student.class_id = class.id;

+----+--------------+------------+
| id | student_name | class_name |
+----+--------------+------------+
|  1 | 张三         | 班级1      |
|  2 | 王五         | 班级1      |
|  3 | 李四         | 班级1      |
|  4 | 小红         | 班级2      |
|  5 | 小花         | 班级2      |
|  6 | huyongjian   | 班级3      |
|  7 | Join         | 班级3      |
+----+--------------+------------+

左连接

SELECT
    student.`id`,
    student.`name` student_name,
    class.`name` class_name 
FROM
    student
    LEFT JOIN class ON student.`class_id` = class.`id`;

+----+--------------+------------+
| id | student_name | class_name |
+----+--------------+------------+
|  1 | 张三         | 班级1      |
|  2 | 王五         | 班级1      |
|  3 | 李四         | 班级1      |
|  4 | 小红         | 班级2      |
|  5 | 小花         | 班级2      |
|  6 | huyongjian   | 班级3      |
|  7 | Join         | NULL       |
+----+--------------+------------+
7 rows in set (0.00 sec)

右连接

SELECT
    student.`id`,
    student.`name` student_name,
    class.`name` class_name 
FROM
    student
    RIGHT JOIN class ON student.`class_id` = class.`id`;

+------+--------------+------------+
| id   | student_name | class_name |
+------+--------------+------------+
|    3 | 李四         | 班级1      |
|    2 | 王五         | 班级1      |
|    1 | 张三         | 班级1      |
|    7 | Join         | 班级2      |
|    6 | huyongjian   | 班级2      |
|    5 | 小花         | 班级2      |
|    4 | 小红         | 班级2      |
| NULL | NULL         | 班级3      |
+------+--------------+------------+
8 rows in set (0.00 sec)