• 【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
  • 相关阅读:
    为什么要用全文搜索引擎:全文搜索引擎 VS 数据库管理系统
    大数据学习路线之hive存储格式
    web测试教程之JavaScript中的变量
    Java学习中面向过程与面向对象的优缺点
    Java教程之Java反射
    Python技术基础知识点:OS模块的应用
    软件测试教程——概念解析及常用方法概说
    UI设计师必备技能 网页中的色彩搭配(色彩篇)
    UI技术分享 如何提高自己的设计视野
    JavaScript学习指南分享
  • 原文地址:https://www.cnblogs.com/6970-9192/p/11426518.html
Copyright © 2020-2023  润新知