0%

MySQL笔记(2)--连接

发表于 更新于 分类于 Valine：

002_sql-join

1 Inner Joins (内连接)

1
2
3
4
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
INNER JOIN customers c
    ON o.customer_id = c.customer_id

返回表orders在表customers中有对应record的项

o和c分别是orders和customers的别名，以简化代码

关键字INNER可省略

1.1 跨数据库连接

1
2
3
4
5
6
USE sql_store;  -- 选择了数据库sql_store为当前数据库

SELECT *
FROM order_items oi
JOIN sql_inventory.products p   -- 另一个数据库sql_inventory
    ON oi.product_id = p.product_id

1.2 自连接

将表和自己连接，但是给不同的别名以区分。如下，manager也是一名员工，其信息也在表employees中，用自连接可以得到employee和TA的manager的具体员工信息。

1
2
3
4
5
6
7
8
9
USE sql_hr;  -- 选择了数据库sql_hr为当前数据库

SELECT
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
JOIN employees m  
    ON e.reports_to = m.employee_id

1.3 多表连接

1
2
3
4
5
6
7
8
9
10
11
12
13
USE sql_store;

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

表orders先和表customers连接，然后与表order_statuses连接。

1.4 复合连接条件

对于有两个主键的表，要用两个主键作为条件与别的表连接。例如表order_items的主键是order_id和product_id

1
2
3
4
5
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.5 隐式连接语法

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id


-- Implicit Join Syntax
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

不建议使用隐式连接语法，要是忘写WHERE就完了。

2 外连接

1
2
3
4
5
6
7
8
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
RIGHT JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

LEFT (OUTER) JOIN 和 RIGHT (OUTER) JOIN, OUTER 可省略

2.1 多表外连接

1
2
3
4
5
6
7
8
9
10
11
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 c.customer_id

2.2 自外连接

1
2
3
4
5
6
7
8
9
USE sql_hr;

SELECT 
    e.employee_id,
    e.first_name AS employee,
    m.employee_id AS manager
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id

3 USING子句

使用于两张表的键名完全一致的情况

1
2
3
4
5
6
7
8
9
10
SELECT 
    o.order_id, 
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
    -- ON o.customer_id = c.customer_id 
    USING (customer_id)
LEFT JOIN shippers sh
    USING (shipper_id)
1
2
3
4
5
6
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)

4 自然连接

自动找到同名的column来连接。（容易出错，不建议使用）

1
2
3
4
5
SELECT
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

5 交叉连接

连接两张表的所有records

1
2
3
4
5
6
SELECT
    c.first_name AS customers,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name

应用场景：表一是大中小型号，表二是红黄蓝等各种颜色，将所有型号和所有颜色组合起来。

隐式语法

1
2
3
4
5
SELECT
    c.first_name AS customers,
    p.name AS product
FROM customers c, products p
ORDER BY c.first_name

6 联合（Unions）

筛选某些records，用union将多次筛选的结果联合起来

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'	-- 在此日期后的order, 状态设为active
UNION
SELECT
    order_id,
    order_date,
    'Archived' AS status			-- 在此日期前的order, 状态设为archived
FROM orders
WHERE order_date < '2019-01-01'

也可以联合多张表

1
2
3
4
5
SELECT first_name  -- 决定列名
FROM customers
UNION
SELECT name
FROM shippers

练习

根据积分将顾客划分为金银铜三档：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT
    customer_id,
    first_name,
    points,
    'Bronze' AS type
FROM customers c
WHERE points < 2000
UNION
SELECT
    customer_id,
    first_name,
    points,
    'silver' AS type
FROM customers c
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT
    customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers c
WHERE points > 3000
ORDER BY first_name