主讲老师: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