一、连接的类型
- 内连接(INNER JOIN):组合两个表中的记录,只要在公共字段之中有相符的值
- 左连接(LEFT JOIN):获取左表所有记录,即使右表没有对应匹配的记录
- 右连接(RIGHT JOIN):获取右表所有记录,即使左表没有对应匹配的记录
二、INNER JOIN
1.语法
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions
2.e.g
//查询账号对应的学生信息
SELECT
forge.users.id,
forge.users.first_name,
forge.students.id AS stu_id
FROM
forge.users
INNER JOIN forge.students ON forge.users.id = forge.students.user_id
WHERE
forge.users.mobile = 10000003000
LIMIT 1
//查询账号对应的学生与家长信息
SELECT
forge.users.id,
forge.users.first_name,
forge.students.id AS stu_id,
parents.parents_children.parent_id
FROM
forge.users
INNER JOIN forge.students ON forge.users.id = forge.students.user_id
INNER JOIN parents.parents_children ON forge.students.id = parents.parents_children.student_id
WHERE
forge.users.mobile = 10000003000
LIMIT 1
三、LEFT JOIN/RIGHT JOIN
1.语法
SELECT column_list
FROM t1
LEFT/RIGHT JOIN t2 ON join_condition1
LEFT/RIGHT JOIN t3 ON join_condition2
...
WHERE where_conditions
2.e.g
//查询账号对应的学生与家长信息
SELECT
forge.users.id,
forge.users.first_name,
forge.students.id AS stu_id,
parents.parents_children.parent_id
FROM
forge.users
LEFT JOIN forge.students ON forge.users.id = forge.students.user_id
LEFT JOIN parents.parents_children ON forge.students.id = parents.parents_children.student_id
WHERE
forge.users.mobile = 10000003000
LIMIT 1
//查询账号对应的学生与家长信息
SELECT
forge.users.id,
forge.users.first_name,
forge.students.id AS stu_id,
parents.parents_children.parent_id
FROM
forge.users
RIGHT JOIN forge.students ON forge.users.id = forge.students.user_id
RIGHT JOIN parents.parents_children ON forge.students.id = parents.parents_children.student_id
WHERE
forge.users.mobile = 10000003000
LIMIT 1