2.4 Mysql 基础 --关联查询 1对多，多对多练习

1. 一对多练习

left jon （外连接） 和inner join 区别（内连接）

数据准备:

CREATE TABLE customers(
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100) 
);

INSERT INTO customers(first_name, last_name, email) VALUES
    ('Robin', 'Jackman', 'roj@gmail.com'),
    ('Taylor', 'Edward', 'taed@gmail.com'),
    ('Vivian', 'Dickens', 'vidi@gmail.com'),
    ('Harley', 'Gilbert', 'hgi@gmail.com');
     ('Lihua', 'Cao', 'xx@qq.com');

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
);

INSERT INTO orders(order_date, amount, customer_id) VALUES
    ('2001-10-12', 99.12, 1),
    ('2001-09-21', 110.99, 2),
    ('2001-10-13', 12.19, 1),
    ('2001-11-29', 88.09, 3),
    ('2001-11-11', 205.01, 4);

 

订单表：

用户表：

内连接 ： 取两边的交集

select  * from customers t1  , orders t2   where  t1.id=t2.customer_id

外连接 ，左连接:左边是最全的，没关联到的是null。Robin 有多个订单

select  * from    customers t1     LEFT JOIN   orders t2  on  t1.id=t2.customer_id 

 

进一步筛选计算： 求每个人的订单总额

select first_name,last_name,SUM(amount)    from    customers t1     LEFT JOIN   orders t2  on  t1.id=t2.customer_id  GROUP BY t2.customer_id 

但是 SUM有个字段是NULL

select first_name,last_name,
 case when SUM(amount) is null then 0
 else SUM(amount) end as total_amount
from    customers t1     LEFT JOIN   orders t2  on  t1.id=t2.customer_id  GROUP BY t2.customer_id 

 

2.多对多练习