MySQL 数据库

-- 创建销售数据表 CREATE TABLE sales ( sale_id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100), category VARCHAR(50), sale_date DATE, quantity INT, unit_price DECIMAL(10,2), total_amount DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) ); -- 插入示例数据 INSERT INTO sales (product_name, category, sale_date, quantity, unit_price) VALUES ('iPhone 15', '手机', '2024-01-10', 5, 6999.00), ('MacBook Pro', '电脑', '2024-01-10', 3, 12999.00), ('iPad Air', '平板', '2024-01-11', 8, 4799.00), ('iPhone 15', '手机', '2024-01-12', 4, 6999.00), ('MacBook Pro', '电脑', '2024-01-12', 2, 12999.00); -- COUNT 计数 SELECT COUNT(*) AS total_sales FROM sales; SELECT COUNT(DISTINCT category) AS category_count FROM sales; -- SUM 求和 SELECT SUM(quantity) AS total_quantity FROM sales; SELECT SUM(total_amount) AS total_revenue FROM sales; -- AVG 平均值 SELECT AVG(unit_price) AS avg_price FROM sales; -- MAX 最大值 SELECT MAX(total_amount) AS max_sale FROM sales; -- MIN 最小值 SELECT MIN(sale_date) AS first_sale_date FROM sales; -- 多个聚合函数 SELECT COUNT(*) AS sales_count, SUM(total_amount) AS total_revenue, AVG(total_amount) AS avg_sale_amount, MAX(total_amount) AS max_sale, MIN(total_amount) AS min_sale FROM sales;

-- 按产品分组统计 SELECT product_name, COUNT(*) AS sales_count, SUM(quantity) AS total_quantity, SUM(total_amount) AS total_revenue, AVG(unit_price) AS avg_price FROM sales GROUP BY product_name; -- 按类别分组 SELECT category, COUNT(*) AS sales_count, SUM(total_amount) AS category_revenue, AVG(total_amount) AS avg_category_sale FROM sales GROUP BY category ORDER BY category_revenue DESC; -- 按日期分组 SELECT sale_date, COUNT(*) AS daily_sales, SUM(total_amount) AS daily_revenue FROM sales GROUP BY sale_date ORDER BY sale_date; -- 多字段分组 SELECT category, product_name, COUNT(*) AS sales_count, SUM(quantity) AS total_quantity FROM sales GROUP BY category, product_name ORDER BY category, total_quantity DESC;

-- HAVING vs WHERE -- WHERE 在分组前过滤行 -- HAVING 在分组后过滤组 -- 筛选总销售额大于10000的类别 SELECT category, SUM(total_amount) AS category_revenue FROM sales GROUP BY category HAVING category_revenue > 10000; -- 筛选销售次数大于2的产品 SELECT product_name, COUNT(*) AS sales_count, AVG(total_amount) AS avg_sale_amount FROM sales GROUP BY product_name HAVING sales_count > 1 ORDER BY sales_count DESC; -- 复杂HAVING条件 SELECT category, COUNT(*) AS sales_count, SUM(total_amount) AS total_revenue, AVG(total_amount) AS avg_revenue FROM sales WHERE sale_date >= '2024-01-10' GROUP BY category HAVING total_revenue > 5000 AND sales_count >= 2 ORDER BY total_revenue DESC; -- 查找平均单价高于5000的产品类别 SELECT category, AVG(unit_price) AS avg_category_price FROM sales GROUP BY category HAVING avg_category_price > 5000;