• 【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
  • 相关阅读:
    Luogu P3919【模板】可持久化数组(可持久化线段树/平衡树)
    线段树||BZOJ5194: [Usaco2018 Feb]Snow Boots||Luogu P4269 [USACO18FEB]Snow Boots G
    线段树||BZOJ1593: [Usaco2008 Feb]Hotel 旅馆||Luogu P2894 [USACO08FEB]酒店Hotel
    CF 610E. Alphabet Permutations
    BZOJ 1227: [SDOI2009]虔诚的墓主人
    BZOJ1009: [HNOI2008]GT考试
    BZOJ3674: 可持久化并查集加强版
    BZOJ3261: 最大异或和
    BZOJ2741: 【FOTILE模拟赛】L
    BZOJ3166: [Heoi2013]Alo
  • 原文地址:https://www.cnblogs.com/6970-9192/p/11426518.html
Copyright © 2020-2023  润新知