第三章 在多张表格中检索数据

第三章 在多张表格中检索数据

之前只学习从单一表格中选取列，接下来学习从多个表格中选取列。

p18 内连接√

这里先引出两个问题，为什么要分表？为什么在orders表中用customer_id代替用户信息？

因为假如在orders表中保存完整的用户信息，那么如果某个用户信息发生变化，且这个顾客下了多笔订单，那么就要改动多条记录，效率很低。

因此采用分表的方式，在orders表中用customer_id代替用户信息，当有用户的信息需要更改，只需要在customers表中更改一次就好了。在查询的时候连接表就好了！

因此，各表分开存放是为了减少重复信息和方便修改，需要时可以根据相互之间的关系连接成相应的合并详情表以满足相应的查询。FROM JOIN ON 语句就是告诉sql： 将哪几张表以什么基础连接/合并起来。

-- 记得加前缀，避免歧义
SELECT orders.order_id, customers.customer_id, customers.first_name, customers.last_name
FROM orders
-- 内连接，告诉mysql，我们想连接orders表和customers表，要确保orders.customer_id = customers.customer_id;
JOIN customers 
	on orders.customer_id = customers.customer_id;

-- 简化代码
SELECT o.order_id, c.customer_id, c.first_name, c.last_name
FROM orders o
JOIN customers c
	on o.customer_id = c.customer_id;

练习

SELECT oi.order_id, oi.product_id, quantity , oi.unit_price
From order_items oi
JOIN products p 
	ON oi.product_id = p.product_id;

p19 跨数据库连接√

有时需要选取不同库的表的列，其他都一样，就只是WHERE JOIN里对于非现在正在用的库的表要加上库名前缀而已。依然可用别名来简化。

实例

USE sql_store;
SELECT * 
FROM order_items oi
JOIN sql_inventory.products p
    on oi.product_id = p.product_id;

可见只有非当前使用的库才要加库前缀。

p20 自连接√

一个表和它自己合并。

实例

假如我们想在员工表中找到每个员工的上级，并且员工的上级也是员工，所以当然可以想到使员工表和manager表合并，在当前肯定是员工表自己和自己合并，用两个不同的表别名即可实现。

SELECT e.employee_id, e.first_name, m.first_name manager 
FROM employees e
JOIN employees m
	on e.reports_to = m.employee_id;

p21 多表连接√

实例，在orders表中，使之和customer表还有order_statuses多表连接。返回订单信息，用户last_name,还有订单状态。

-- 一对多连接表
select o.order_id, o.order_date, c.first_name, c.last_name, os.name 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;

p22 复合连接条件√

在之前的学习中，都是单一列唯一识别了表里的每一行（比如在customers表中，每个customer_id就唯一对应一个客户信息行，说白了就是单一主键）。但是存在我们无法用单一列来准确识别某张表里单一行的情况。

比如在order_items中order_id和product_id都有重复的情况，所以我们要使用order_id和product_id唯一识别某个订单中的产品的数量以及单价。

主键超过一列即为复合主键。那么复合主键如何与其他表连接呢？

USE sql_store;
-- 连接order_item_notes 和 order_items  （连接订单信息和订单备注）
SELECT oin.note_id, oi.order_id, oi.product_id, oin.note 
From order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id;

p23 隐式连接语法√

就是用FROM WHERE取代FROM JOIN 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;

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

p24 外连接√

• 内连接（INNER JOIN）结果只包含两表的交集.
• LEFT/RIGHT (OUTER) JOIN结果里除了交集，还包含只出现在左/右表中的记录。

实例

如果我们想看到所有的顾客以及他们的订单信息，不管他们有没有订单，应该怎么做？——外连接

-- 内连接
USE sql_store;
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 c.customer_id;
-- 我们只输出了下了订单的顾客的信息可订单信息 
-- 1 3 4号顾客没下订单所以没显示

外连接分为两种：左连接和右连接。

USE sql_store;
SELECT c.customer_id, c.first_name, o.order_id 
FROM customers c
LEFT JOIN orders o -- 左连接，所有左表（customers）的记录会被返回
    ON o.customer_id = c.customer_id
order by c.customer_id;

USE sql_store;
SELECT c.customer_id, c.first_name, o.order_id 
FROM customers c
RIGHT JOIN orders o -- 右连接，所有右表（orders）的记录会被返回
    ON o.customer_id = c.customer_id
order by c.customer_id;

• 如果写JOIN关键词 默认是内连接;
• 如果写 LEFT JOIN / RIGHT JOIN 默认是外连接。

补充说明：

左连接中，所有左表（customers）的记录会被返回（强调一个不少，但是允许有重复），但返回的不是去重的，如果发票表中有某个用户有5条发票记录，那么在连接的表中就会有5条该用户的记录。

USE sql_invoicing;
-- 不加DISTINCT的话返回的就不是去重的
SELECT DISTINCT client_id, name
FROM clients
LEFT JOIN invoices USING (client_id);

p25 多表外连接√

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的关系 
    ……

将得到一个合并了BCD……等表详细信息的详情合并版A表，真实工作场景中有时甚至要合并十多张表。

实例

假如我们想要连接订单表和发货人表，让发货人的名字出现在结果中。

有些订单还没有发货（shipper_date和shipper_id都为NULL），如果用内连接会不显示没有发货的订单，为了显示全部订单，要用左连接。

最好避免使用右连接，因为在多表连接时，同时使用左右连接会让人很迷惑。

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;

p26 自外连接√

实例

用p20 自（内）连接的实例来对比，由于用的是内连接，因此reports_to必须有对应的employee_id，这样的员工才会被显示出来，但是CEO是没有上级的，它的reports_to为空，因此不会显示CEO。

为了在员工表中显示CEO，就可以使用自外连接，见语句：

USE sql_hr;

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;

p26 USING子句√

当作为合并条件（join condition）的列在两个表中有相同的列名时，可用 USING (……, ……)取代 ON …… AND ……予以简化，内/外链接均可如此简化。

USE sql_store;

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 c.customer_id;

-- 如果要连接的两个表中有列名称是完全一样的
-- 那么就可以用一个更简洁的USING语句替换ON子句
USE sql_store;
SELECT
    o.order_id,
    customer_id, -- 用了using的列名也不用加前缀了!
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
    USING (customer_id)
LEFT JOIN shippers sh
    USING (shipper_id)
ORDER BY order_id;

-- 有复合主键的表连接时用USING更为简便
USE sql_store;
SELECT oin.note_id, oi.order_id, oi.product_id, oin.note 
From order_items oi
JOIN order_item_notes oin
	USING(order_id, product_id);

P28 自然连接√

NATURAL JOIN 就是让MySQL自动检索同名列作为合并条件。

USE sql_store;

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

注意

最好别用，NATURAL JOIN，因为我们让数据库引擎自己去猜应该怎么连接，会产生无法预料的问题。

p29 交叉连接√

得到名字和产品的所有组合，因此不需要合并条件。

实例

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

USE sql_store;

SELECT c.first_name AS customer, p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name;
-- or 隐式语法
SELECT c.first_name AS customer, p.name AS product
FROM customers c, products p;

p30 联合

FROM …… JOIN ……可对多张表进行横向列合并，而 …… UNION ……可用来按行纵向合并多个查询结果，这些查询结果可能来自相同或不同的表。

• 同一张表可通过UNION添加新的分类字段，即先通过分类查询并添加新的分类字段再UNION合并为带分类字段的新表。
• 不同表通过UNION合并的情况如：将一张18年的订单表和19年的订单表纵向合并起来在一张表里展示。

实例

在orders表中，在每笔订单边边加一个标签。如果是今年的订单，那么就加一个“active”；如果是前些年的，那么就标签为archive（存档）。

USE sql_store;

SELECT order_id, order_date, 'Active' as Status
FROM orders
where order_date >= '2019-01-01'
-- 这里使用UNION 就可以将两个结果合并为一个结果集
UNION
SELECT order_id, order_date, 'Archive' 
FROM orders
where order_date < '2019-01-01';

• UNION可以连接同一张表的结果，也可以连接不同表的结果。
• 但是要记住，UNION连接的结果返回的列的数量一定要一样，否则就会得到错误提示。
• 列名是基于第一段查询的。

给顾客按积分大小分类，添加新字段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;