MySQL多表查询操作的一写关键字

菠萝蜜blm

于 2024-04-22 22:26:53 发布

阅读量2.1k

点赞数 56

分类专栏：一起成为数据库高手文章标签：数据库

本文链接：https://blog.csdn.net/weixin_46984703/article/details/138094620

版权

一起成为数据库高手专栏收录该内容

28 篇文章 0 订阅

订阅专栏

1.1.2.1. 内连接（INNER JOIN）

内连接就是将第一个表的每一行与第二个表的每一行进行比较，满足给定的连接条件，会将两个表的行组合在一起作为结果集中的一行。

在进行数据库查询操作时，经常会根据各表之间的关系来查询不同表中的数据，因为设计数据库表时为了减少重复的信息和方便修改，会把表分开存放。

内连接的语法： FROM JOIN ON

这个语句就是告诉sql，需要将那几个表以什么基础连接或合并起来，（不是真的合并，就是逻辑上的合并，查询时能够同时显示我们想要的数据）

通过案例看一下：

select 
    order_id, 
    o.customer_id, 
    first_name, 
    last_name
from 
-- orders表 连接customers表，
orders o join customers c 
-- 连接的条件
    on o.customer_id = c.customer_id

-- 这两种形式效果是一样的

select 
    order_id, 
    o.customer_id, 
    first_name, 
    last_name
from orders o ，customers c 
where o.customer_id = c.customer_id

说明：两张表中存在相同的字段，在查询时相同的字段必须加上前缀表名指出是那张表的字段，多表查询涉及到的表比较多，起别名可以在书写的时候简洁一点，不会出现很长的表名.字段名。

因为FROM … JOIN … 语句是最先执行的，在这里会给表取别名，所以在别的地方都要用别名，否则会报错。

1.1.2.2. 跨数据库连接

有时需要选取不同库的表的字段，其他和内连接都一样，就只是WHERE JOIN里对于不是目前正在用的库的表要加上库名前缀而已。也可用别名来简化。

select * from order_items oi
join sql_inventory.products p
    on oi.product_id = p.product_id

1.1.2.3. 自连接

就是一个表和它自己合并。如下面的例子，员工的上级也是员工，所以也在员工表里，想得到员工和他的上级信息的合并表，就要员工表自己和自己合并，如果上级是老板没有上级了也需要显示出来，用两个不同的表别名即可实现。这个例子中只有两级，但也可用类似的方法构建多层级的组织结构。

select 
    e.employee_id,
    e.first_name,
    m.first_name as manager
    from employees e
join employees m
-- reports_to存放管理员的id
    on e.reports_to = m.employee_id

自合并每列都要加表前缀，因为查询结果每列都同时在两张表中出现。另外，两个 first_name 字段不容易分辨，注意将最后一列改名为 manager 使得结果表更易于理解

1.1.2.4. 多表连接

FROM 一个核心表A，用多个 JOIN …… ON …… 分别通过不同的链接关系链接不同的表B、C、D……，通常是让表B、C、D……为表A提供更详细的信息从而合并为一张详情合并版A表，即：

FROM  A 
    JOIN B ON AB的关系 
    JOIN C ON AC的关系 
    JOIN D ON AD的关系

真实工作场景中有时甚至要合并十多张表

多表连接案例：

订单表同时链接顾客表和订单状态表，合并为有顾客和状态信息的详细订单表

SELECT 
    o.order_id, 
    o.order_date,
    c.first_name,
    c.last_name,
    os.name AS status
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN order_statuses os
    ON o.status = os.order_status_id

1.1.2.5. 复合连接

像订单项目（order_items）这种表，订单id和产品id合在一起才能唯一表示一条记录，这叫复合主键，设计模式下看两个字段都有PrimaryKey标识，订单项目备注表（order_item_notes）也是这两个复合主键，因此它们两合并时要用复合条件：FROM 表1 JOIN 表2 ON 条件1 【AND】条件2

符合连接条件案例：

SELECT * 
FROM order_items oi
JOIN order_item_notes oin
    ON oi.order_Id = oin.order_Id
    AND oi.product_id = oin.product_id

1.1.2.6. 隐式链接

隐式连接就是用FROM WHERE取代FROM JOIN ON，尽量不要使用，如果忘记WHERE条件筛选语句，会得到交叉合并（cross join）结果：就是10条order会分别与10个customer结合，得到100条记录。最好使用显性合并语法，因为会强制要求写合并条件ON语句，不至于漏掉。

在内连接案例中，就是显性合并和隐性合并

-- 显性合并
SELECT * 
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
-- 隐式合并
SELECT * 
FROM orders o, customers c  
WHERE o.customer_id = c.customer_id

1.1.2.7. 外连接

(INNER) JOIN 结果只包含两表的交集，另外注意“广播（broadcast）”效应

广播效应：是当某个节点上的操作对整个系统产生影响时，这些影响会自动传播到其他相关节点的现象。

LEFT/RIGHT (OUTER) JOIN 结果里除了交集，还包含只出现在左/右表中的记录

外连接案例：

SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
JOIN orders o
    ON o.customer_id = c.customer_id
ORDER BY customer_id

说明：这样是INNER JOIN，只展示有订单的顾客（及其订单），也就是两张表的交集（没订单的顾客不会显示），但注意这里因为一个顾客可能有多个订单，所以INNER JOIN以后顾客信息其实是是广播了的，即一条顾客信息被多条订单记录共用，当然这叫广播（broadcast）效应，是另一个问题，这里关注的重点是 INNER JOIN 的结果确实是两表的交集，是那些同时有顾客信息和订单信息的记录。

若要展示全部顾客（及其订单，如果有的话），要改用左外连接LEFT (OUTER) JOIN，结果相较于 INNER JOIN 多了没有订单的那些顾客，即只有顾客信息没有订单信息的记录

FROM customers c
    LEFT [OUTER] JOIN orders o 
    -- 中括号 [] 表示是可选项、可省略 
    ON c.customer_id = o.customer_id

若要展示全部订单（及其顾客），就应该是 orders RIGHT JOIN customers，结果相较于 INNER JOIN 多了没有顾客的那些订单，即只有订单信息没有顾客信息的记录。（注：因为这里所有订单都有顾客，所以这里 LEFT JOIN 结果和 INNER JOIN 一样）

左连接和右连接切换将FROM和JOIN后的表调换一下即可

注意：左连接和右连接尽量使用一种，否则代码可读性会非常差，容易捋不清逻辑。

1.1.2.8. 多表外连接

与内连接类似，可以对多个表（3个及以上）进行外连接，最好只用 JOIN 和 LEFT JOIN

多表外连接案例：

查询顾客、订单和发货商记录，要包括所有顾客（包括无订单的顾客），也要包括所有订单（包括未发出的）

SELECT 
    c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
ORDER BY customer_id

1.1.2.9. 自外连接

就用前面那个员工表的例子来说，就是用LEFT JOIN让得到的员工-上级合并表也包括老板本人（老板没有上级，即 reports_to 字段为空，如果用 JOIN 会被筛掉，用 LEFT JOIN 才能保留）

SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m  -- 包含所有雇员（包括没有report_to的老板本人）
    ON e.reports_to = m.employee_id

1.1.2.10. USING子句

作为合并条件（join condition）的字段在两个表中有相同的字段名时，可用 USING (……, ……) 取代 ON …… AND …… 进行简化，内/外链接都可以使用此操作进行简化。注意：USING 后接的是括号

USING子句实例：

SELECT
    o.order_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
    USING (customer_id) -- customers和orders表中都有customer_id字段
LEFT JOIN shippers sh
    USING (shipper_id) -- shippers和customers表中都有shipper_id字段
ORDER BY order_id

复合主键表间复合连接条件的合并也可用 USING，中间逗号隔开就行：

SELECT *
FROM order_items oi
JOIN order_item_notes oin
  ON oi.order_id = oin.order_Id 
  AND	oi.product_id = oin.product_id
-- 可替换为 USING (order_id, product_id)

sql_invoicing库里，将payments、clients、payment_methods三张表合并起来，以知道什么日期哪个顾客用什么方式付了多少钱

SELECT 
    p.date,
    c.name AS client,
    pm.name AS payment_method,
    p.amount
FROM payments p
JOIN clients c USING (client_id)
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id

注意

字段名不同就必须用 ON …… ，实际开发中同一个字段在不同表列名不同的情况也很常见（如上面的 payment_method 和payment_method_id）

1.1.2.11. 自然连接（Natural Joins）

NATURAL JOIN 就是让MySQL自动检索同名的字段作为合并条件，但是最好不使用，因为不确定合并条件是否找对了，有时会得到预期之外的结果。混个脸熟，能看懂就可以。

自然连接实例：

SELECT 
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

1.1.2.12. 交叉连接（Cross Joins ）

得到名字和产品的所有组合，因此不需要合并条件。实际运用如：要得到尺寸和颜色的全部组合

交叉连接实例：

得到顾客和产品的全部组合（毫无意义，只是为了展示交叉连接）

SELECT 
    c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name

上面是显性语法，还有隐式语法，其实就是隐式内合并忽略WHERE子句（即合并条件）的情况，就是把 CROSS JOIN 改为逗号，即 FROM A CROSS JOIN B 等效于 FROM A, B，最好使用显式语法，会更清晰。

SELECT 
    c.first_name,
    p.name
FROM customers c, products p
ORDER BY c.first_name

1.1.2.13. 联合（UNIONS）

就是可以将多个查询的结果集合并在一起，如果两个表都只查询了一个字段，会将这两列查询到的数据合并成一列，正常的在两列显示。

FROM …… JOIN …… 可以对多张表进行横向列合并（就是将结果合并到右侧单独一列显示），而 …… UNION …… 可用来按行纵向合并多个查询结果（将结果合并成一列显示），这些查询结果可能来自相同或不同的表

- 同一张表可通过UNION添加新的分类字段，先通过分类查询并添加新的分类字段再UNION合并为带分类字段的新表。

-- 查询高销售额记录并添加分类字段
SELECT product_name, 
       sales_amount, 
       sales_date, 
       '高销售额' AS sales_category
FROM sales_data
WHERE sales_amount > 1000

UNION

-- 查询低销售额记录并添加分类字段
SELECT product_name, 
       sales_amount, 
       sales_date, 
       '低销售额' AS sales_category
FROM sales_data
WHERE sales_amount <= 1000;

- 不同表通过UNION合并的情况如：将一张18年的订单表和19年的订单表纵向合并起来在一张表里展示

-- 从2018年订单表中选择数据并添加年份字段
SELECT order_id, customer_name, order_date, '2018' AS order_year
FROM orders_2018

UNION

-- 从2019年订单表中选择数据并添加年份字段
SELECT order_id, customer_name, order_date, '2019' AS order_year
FROM orders_2019;

注意：合并的查询结果必须列数相等，否则会报错，合并表里的列名由排在 UNION 前面的决定

联合（UNIONS）案例：

给顾客按积分大小分类，添加新字段type，并按顾客id排序，分类标准如下

points	type
<2000	Bronze
2000~3000	Silver
>3000	Gold

SELECT 
        customer_id,
        first_name,
        points,
        'Bronze' AS type
    FROM customers 
    WHERE points < 2000

UNION

    SELECT 
        customer_id,
        first_name,
        points,
        'Silver' AS type
    FROM customers 
    WHERE points BETWEEN 2000 and 3000

UNION

    SELECT 
        customer_id,
        first_name,
        points,
        'Gold' AS type
    FROM customers 
    WHERE points > 3000

ORDER BY customer_id

菠萝蜜blm

关注

56
点赞
踩
22

收藏

觉得还不错? 一键收藏
0
评论
MySQL多表查询操作的一写关键字

说明：这样是INNER JOIN，只展示有订单的顾客（及其订单），也就是两张表的交集（没订单的顾客不会显示），但注意这里因为一个顾客可能有多个订单，所以INNER JOIN以后顾客信息其实是是广播了的，即一条顾客信息被多条订单记录共用，当然这叫。如下面的例子，员工的上级也是员工，所以也在员工表里，想得到员工和他的上级信息的合并表，就要员工表自己和自己合并，如果上级是老板没有上级了也需要显示出来，作为合并条件（join condition）的字段在两个表中有相同的字段名时，可用 USING (……
复制链接

扫一扫