MySQL课程笔记---第二章：各种连接

主讲老师：Mosh；教程链接：【【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！-哔哩哔哩】 https://b23.tv/MqVRzdk 

-- 第二章 各种连接
-- 内连接
SELECT * 
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id -- 使order和customers两个表的customer_id相同来达成链接
-- 也可以只用orders的几列
SELECT order_id,first_name,order.customer_id -- 这里加上前缀，特指order表中的customer_id，避免与customer中的customer_id产生混淆
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id
-- 对于多次重复的前缀order和customer,我们可以创建缩写
SELECT order_id,first_name,o.customer_id 
FROM orders o -- 此处将order缩写为o
JOIN customers c -- 此处将customers缩写为c
	ON o.customer_id = c.customer_id
    
SELECT  -- 练习题
	order_id,
    oi.product_id,
    quantity,
    oi.unit_price
FROM order_items o
JOIN products p
	ON oi.product_id = p.product_id
 
-- 跨数据库连接
SELECT * 
FROM order_items oi -- 只用给不在当前数据库的表格添加前缀，比如此处的order_items在当前数据库sql_store中，因为不用加前缀
JOIN sql_inventory.products p -- 与另一个数据库sql_inventory中的products表链接，实现跨数据库链接
	ON oi.product_id = p.product_id

-- 自连接
USE sql_hr;
SELECT
	e.employee_id,
    e.first_name,
    m.first_name AS manager -- 对于有相同名称的列，重命名
FROM employees e -- 对于同一张表起了e和m两个别名，用于表和表自链接
JOIN employees m
	ON e.reports_to = m.employee_id -- 将报告对象与员工相连，就可以找到每个员工的报告对象是谁

-- 多表连接    
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 -- 将orders与customers链接
JOIN order_statuses os
	ON o.status = os.order_status_id -- 将orders与order_statuses链接，实现了三张表链接

USE sql_invoicing;
SELECT * -- 练习题
FROM payments p
JOIN clients c
	ON p.client_id = c.client_id
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id

-- 复合连接
USE sql_store;
SELECT *
FROM order_items oi -- 对于有复合主键的表格order_items，与其他表格的连接，要关于两个主键进行链接
JOIN order_item_notes oin
	ON oi.order_id = oin.order_id -- 主键一
	AND oi.product_id = oin.product_id -- 主键二

-- 隐式链接
SELECT *
FROM orders o,customers c
WHERE o.customer_id = c.customer_id -- 与下面的语句作用相同
SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id 
    
-- 外连接
SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
JOIN orders o
	ON o.customer_id = c.customer_id -- 此时用的是内连接，因此只有有重合的customer_id的记录才会被链接，
    -- 如果想让其他没有购买物品的顾客id也出现在链接表里就需要用到外连接
ORDER BY c.customer_id

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o -- 返回所有左表（customers表）的，不管是否在orders里有对应项,即不管on后面的条件是否成立
	ON o.customer_id = c.customer_id
    
SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
RIGHT JOIN orders o -- 返回所有右表（orders表）的，不管是否在customers里有对应项,即不管on后面的条件是否成立
	ON o.customer_id = c.customer_id

SELECT -- 练习题
	p.product_id,
    p.name,
    oi.quantity
From products p
LEFT JOIN order_items oi
	ON p.product_id = oi.product_id

-- 多表外连接
SELECT 
	c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
FROM customers c
LEFT JOIN orders o -- 左表是customers
	ON o.customer_id = c.customer_id -- 表orders和表customers外连接
LEFT join shippers sh
	ON o.shipper_id = sh.shipper_id -- 表orders和表shippers外连接

SELECT -- 练习题
	o.order_date,
    o.order_id,
    c.first_name,
    sh.name AS shipper,
    os.name AS status
FROM orders o
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id
LEFT JOIN order_statuses os
	ON o.status = os.order_status_id
JOIN customers c
	ON o.customer_id = c.customer_id

-- 自外连接
USE sql_hr;
SELECT 
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m -- 此时仅使用自连接将不会显示CEO的信息，因为没有人管理他
	ON e.reports_to = m.employee_id
LEFT JOIN employees m -- 在自连接的基础上加入左连接，即不管有没有人管理他，都会有他的信息
	ON e.reports_to = m.employee_id
    
-- using子句
USE sql_store;
SELECT *
FROM orders o
JOIN customers c
	-- ON o.customer_id = c.customer_id 可以替换为下面的using子句
    USING (customer_id) -- 只能用于列名完全相同的情况

-- 对于有复合主键的情况
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)
    
USE sql_invoicing; -- 练习题
SELECT 
	p.date,
    p.amount,
    c.name AS client,
    pm.name AS name
FROM payments p
JOIN clients c
	using(client_id)
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id

-- 自然连接
SELECT *
FROM orders o 
NATURAL JOIN customers c -- 自然连接就是让电脑自己链接

-- 交叉连接
SELECT *
FROM orders o 
CROSS JOIN customers c -- 所有的列都连（可以理解为做叉乘）
SELECT *
FROM orders o，customers c -- 与交叉链接相同

USE sql_store; -- 练习题
SELECT *
FROM shippers s
CROSS JOIN products p
-- FROM shippers s,products p

-- 联合
-- UNION  用来联合行,可以是不同表格的行，也可以是相同表格的行，反正是上下拼接，但注意列数要相同
SELECT first_name -- 列名基于前面行的列名，此处列名为first_name
FROM customers
UNION 
SELECT name
FROM shippers

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 > 2000
ORDER BY first_name