【MySQL】一对多关系Jions相关运用

学之初学之时 2021-05-05

208

课程来源：

Udemy - The Ultimate MySQL Bootcamp:

Go from SQL Beginner to Expert

分享的大部分数据来源于课程，总结为本人原创

包含视频/约24分钟

撰文/Iris帆

排版/Iris帆

全文/780字（包含代码）

导读

Joins是数据库运用中十分常见也是十分重要的一块内容，join翻译为中文是“参与和结合”的意思。在SQL的实际操作中，通过一一对应的关系，join用来结合两个或两个以上的表格。

本期分享重点

Joins相关关键词大集合：JOIN、INNER JOIN、LEFT JOIN、RIGHT JOIN
DELETE CASCADE：创建表格时对FOREIGN KEY的一种自选规定

视频讲解

下面为视频中使用到的代码：

SELECT * FROM customers;
SELECT * FROM orders;
-- 一共5名顾客，有的顾客有好几单购买记录，有的顾客一单购买记录也没有
-- 1名顾客对应>=0次购买记录


-- -- 检索/查找名为George的顾客的订单 -- --


SELECT id FROM customers WHERE last_name='George';
SELECT * FROM orders WHERE customer_id = 1;


SELECT * FROM orders WHERE customer_id =
    (
        SELECT id FROM customers
        WHERE last_name='George'
    );


-- -- -- JIONS -- -- --
-- 加入，结合
-- 一一对应关系，对号入座
-- https://dataschool.com/how-to-teach-people-sql/left-right-join-animated/


-- -- 跨越式 JOIN -- --
-- 直接将两个表格一起SELECT
SELECT * FROM customers, orders; 


-- -- INNER JOIN -- --
-- SELECT两个表中具有匹配值的记录


-- 隐式 INNER JOIN


SELECT * FROM customers, orders 
WHERE customers.id = orders.customer_id;


-- 隐式 INNER JOIN


SELECT first_name, last_name, order_date, amount
FROM customers, orders 
    WHERE customers.id = orders.customer_id;
    
-- 显式 INNER JOINS


SELECT * FROM customers
INNER JOIN orders
    ON customers.id = orders.customer_id;
    
SELECT first_name, last_name, order_date, amount 
FROM customers
JOIN orders
    ON customers.id = orders.customer_id;
    
SELECT *
FROM orders
JOIN customers
    ON customers.id = orders.customer_id;
    
SELECT 
    first_name, 
    last_name, 
    SUM(amount) AS total_spent
FROM customers
JOIN orders
    ON customers.id = orders.customer_id
GROUP BY orders.customer_id
ORDER BY total_spent DESC;
-- 综合运用：以orders表格中的customer_id为分类依据，计算各不同customer_id所订购商品总额


-- LEFT JOIN和RIGHT JOIN
SELECT * FROM customers
LEFT JOIN orders
    ON customers.id = orders.customer_id;


SELECT 
    first_name, 
    last_name,
    IFNULL(SUM(amount), 0) AS total_spent
FROM customers
LEFT JOIN orders
    ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY total_spent;


SELECT * FROM customers
RIGHT JOIN orders
    ON customers.id = orders.customer_id;


-- -- DELETE CASCADE  -- --
-- 连锁性删除


CREATE TABLE orders(
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(8,2),
       
    customer_id INT,
    FOREIGN KEY(customer_id) 
        REFERENCES customers(id)
        ON DELETE CASCADE
);
DELETE FROM customers WHERE id = 2;

扫码关注我吧

学之初学之时

我努力前行，想在停下来的时候，把人生分享给您

mysql

文章转载自学之初学之时，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【MySQL】一对多关系Jions相关运用

评论