Mysql 查询统计练习

2019独角兽企业重金招聘Python工程师标准>>>

1、建表

customers 顾客表

products 产品表

orders 订单表

-- 顾客表
CREATE TABLE customers (
    c_id INT NOT NULL AUTO_INCREMENT,
    lastname VARCHAR(255),
    firstname VARCHAR(255),
    address VARCHAR(255),
    birthday DATETIME,
    PRIMARY KEY (c_id)
);
-- 产品表
CREATE TABLE products (
    p_id INT NOT NULL AUTO_INCREMENT,
    productname VARCHAR(255),
    price FLOAT(12 , 3 ), -- 写 3 是为了等一下试一下 round 函数
    stock INT,
    PRIMARY KEY (p_id)
);
-- 订单表
CREATE TABLE orders (
    o_id INT NOT NULL AUTO_INCREMENT,
    c_id INT,
    p_id INT,
    quantity INT,
    create_time DATETIME,
    PRIMARY KEY (o_id),
    -- 曾经有前辈和我说，在 Mysql 中外键约束不适合在高并发情况下使用，容易死锁，这里只是练习
    FOREIGN KEY (c_id) 
        REFERENCES customers (c_id),
    FOREIGN KEY (p_id)
        REFERENCES products (p_id)
);

2、增加数据

-- Add some data to customers
insert into customers values (null, '三', '张', '上海市黄浦区', '1988-09-29 18:20:18');
insert into customers values (null, '四', '李', '上海市静安区', '1992-01-15 19:17:55');
insert into customers values (null, '无', '钱', '上海市浦东新区', '1982-07-25 05:21:37');
insert into customers values (null, '吴', '周', '上海市虹口区', '1995-02-18 12:30:09');
insert into customers values (null, '王', '郑', '上海市徐汇区', '1999-01-19 11:45:18');
-- Just select 
SELECT 
    *
FROM
    customers;
-- Add some data to products 
insert into products values (null, '无线路由器', 110.091, 100);
insert into products values (null, '雷蛇鼠标', 320.50, 200);
insert into products values (null, 'cherry 键盘', 600.1, 300);
insert into products values (null, 'AOG 显示器', 1100.0, 400);
insert into products values (null, '甜筒', 3, 500);
-- Just select
SELECT 
    *
FROM
    products;
-- Let buy some products for add data to orders
insert into orders values (null, 1, 1, 1, date_sub(now(),interval 1 day));
insert into orders values (null, 1, 2, 2, date_sub(now(),interval 1 day));
insert into orders values (null, 1, 3, 3, date_sub(now(),interval 2 day));
insert into orders values (null, 1, 4, 4, date_sub(now(),interval 2 day));
insert into orders values (null, 2, 5, 7, date_sub(now(),interval 5 hour));
insert into orders values (null, 3, 2, 1, date_sub(now(),interval 5 hour));
insert into orders values (null, 4, 4, 2, now());
insert into orders values (null, 4, 5, 2, now());
-- Just select
SELECT 
    *
FROM
    orders;

3、做统计

（1）当天 雷蛇鼠标的销售额

-- 当天 雷蛇鼠标的销售额
SELECT 
    SUM(o.quantity) AS router_sales_quantity
FROM
    orders o
        LEFT JOIN
    products p ON o.p_id = p.p_id
WHERE
    p.productname = '雷蛇鼠标'
        AND DATE(o.create_time) = DATE(NOW());

（2）雷蛇鼠标 在 80 后人群中的销售额

-- 雷蛇鼠标 在 80 后人群中的销售额
SELECT 
    round(sum(o.quantity * p.price), 2) as productname_sum_money
FROM
    orders o
        LEFT JOIN
    products p ON o.p_id = p.p_id
        LEFT JOIN
    customers c ON o.c_id = c.c_id
WHERE
    p.productname = '雷蛇鼠标'
        AND EXTRACT(YEAR FROM c.birthday) BETWEEN '1980' AND '1990';

（3）80 , 90 后人群的总销售额和平均销售额

-- 80 , 90 后人群的总销售额和平均销售额
SELECT 
    CASE
        WHEN
            EXTRACT(YEAR FROM c.birthday) >= '1980'
                AND EXTRACT(YEAR FROM c.birthday) < '1990'
        THEN
            '80'
        WHEN
            EXTRACT(YEAR FROM c.birthday) >= '1990'
                AND EXTRACT(YEAR FROM c.birthday) < '2000'
        THEN
            '90'
        ELSE 'null'
    END AS age,
    ROUND(SUM(o.quantity * p.price), 2) AS sum_money,
    ROUND(AVG(o.quantity * p.price), 2) AS avg_money
FROM
    orders o
        LEFT JOIN
    products p ON o.p_id = p.p_id
        LEFT JOIN
    customers c ON o.c_id = c.c_id
GROUP BY age;

...

有空继续增加统计查询的内容

转载于:https://my.oschina.net/yudongyang/blog/1539967