【Mysql】表链接

一、连接的类型

• 内连接(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