MySQL 学习笔记第三章-内连接

第三章内连接

13. INNER joins 内连接

目的：从多张列表中选择数据

SELECT * 
FROM orders
-- JOIN 关键字，分为INNER JOIN 和外连接，内连接可以不打INNER字样
JOIN customers 
    ON orders.customer_id = customers.customer_id;
-- 关于结果，前几列是orders里面的，因为先选的是orders表

SELECT order_id, customer_id, first_name, last_name
FROM orders
-- JOIN 关键字，分为INNER JOIN 和外连接，内连接可以不打INNER字样
JOIN customers 
    ON orders.customer_id = customers.customer_id
-- 会出现错误，因为两张表里都有customer_id, 软件不知道从哪张表里选，所以要加个前缀，orders.customer_id or customer.customer_id
-- 避免重复使用别名

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o -- 空格后为别名
-- JOIN 关键字，分为INNER JOIN 和外连接，内连接可以不打INNER字样
JOIN customers c
    ON o.customer_id = c.customer_id

-- exercise 给了一张表，写出相应的代码
SELECT *
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
-- 一旦一处用了缩写，那么处处都要有缩写

-- exercise 给了一张表，写出相应的代码
SELECT order_id, oi.product_id, quantity, oi.unit_price – 给的答案中判断出要用oi的单位价格
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
-- 一旦一处用了缩写，那么处处都要有缩写

14. Joining across database 数据库之间的连接

SELECT *
FROM order_items oi
JOIN sql_inventory.products p 
    ON oi.product_id = p.product_id
# 这个是在 USE sql_store的前提下使用的

15. self join 自连接

USE sql_hr;
 
 select *
 FROM employees e
 JOIN employees m
    ON e.reports_to = m.employee_id
-- 查询各个员工的管理人员

USE 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

16. Joining multiple tables 多表连接

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

-- 练习
USE sql_invoicing;

SELECT 
    p.date,
    p.invoice_id,
    p.amount,
    c.name,
    pm.name AS method
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

截图2.jpg

17. Compound join conditions 复合连接条件

之前所有的连接都是用单一列来识别表里的情况

但列里有重复的就无法一一对应的识别

即复合主键

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

18. Implicit Join Syntax 隐式连接语法

-- 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就会出现交叉连接，原来每个表10条记录，会出现100条

19. Outer joins 外连接

Inner joins 的 inner可以省略

SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
JOIN orders o 
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id
-- 结果集里没有顾客为 1 3等人的信息，要是想看，就用外连接

外连接有两种

LEFT JOIN 和RIGHT JOIN

左连接返回左边表里的所有记录

FROM customers LEFT JOIN orders ，这里的左右指的是join的左右

完整的表达是LEFT OUTER JOIN 但是OUTER 可有可无，像INNER一样

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

20. Outer joins between multiple tables 多表外连接

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

尽量用左连接

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

-- Mosh答案
SELECT 
    o.order_date,
    o.order_id,
    c.first_name AS customer,
    sh.name AS shipper,
    os.name AS status
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
JOIN order_statuses os
    ON o.status = os.order_status_id
-- 只用了一个左连接 但答案一样

21. self outer joins 自外连接

USE sql_hr;

SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id
-- 把管理者也列出来了，虽然他没有管理者，因为管理者就是他自己

22. USING clause USING子句

SELECT
    o.order_id,
    c.first_name
FROM orders o
JOIN customers c 
    -- ON o.customer_id = c.customer_id
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)

-----
-- 练习 (invocing数据库)
SELECT 
    p.date,
    c.name AS client,
    p.amount,
    pm.name AS payment_method
FROM payments p 
JOIN clients c
    USING (client_id)
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id

23. Natural joins 自然连接

让引擎自己猜测按照什么来连接，不建议使用

24. Cross Joins 交叉连接

SELECT *
 FROM customers c 
 CROSS JOIN products p

SELECT 
    c.first_name AS customer,
    p.name AS product
 FROM customers c（, products p） 隐性语法
 -- CROSS JOIN products p 显性语法
 ORDER BY c.first_name

--------------------------
-- exercise
-- Do a cross join between shippers and products
--  using the implict syntax 
SELECT * 
FROM shippers s 
CROSS JOIN products p 
--  using the explicit syntax
SELECT * 
FROM shippers s, products p

25. unions 联合

Joins 将多张表的列连接到一起，那么如何将行连在一起呢？

SELECT 
    order_id,
    order_date,
    'Active' AS status
FROM orders 
WHERE order_date >= '2019-01-01'
-- 选出当年（2019）的订单，并将其状态标为active
UNION -- 将不同的行连接起来了，可以连接同一个表，也可以连接不同的表
SELECT 
    order_id,
    order_date,
    'Acchived' AS status
FROM orders 
WHERE order_date <  '2019-01-01'

SELECT first_name 
FROM customers
UNION
SELECT name 
FROM shippers
# 返回的结果的列名称是first_name，如果改变顺序，用shippers那么就会是name
# 两个表的列的数量要一样，否则就会得到错误提示

SELECT first_name, last_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 > 3000
ORDER BY first_name

最后编辑于：2021.12.17 15:47:43

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 196,099评论 5赞 462
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 82,473评论 2赞 373
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 143,229评论 0赞 325
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,570评论 1赞 267
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,427评论 5赞 358
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,335评论 1赞 273
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,737评论 3赞 386
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,392评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,693评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,730评论 2赞 312
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,512评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,349评论 3赞 314
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,750评论 3赞 299
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,017评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,290评论 1赞 251
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,706评论 2赞 342
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,904评论 2赞 335