【MySQL 常用 SQL 语句】

MySQL 常用 SQL 语句

查询数据 SELECT * FROM table_name WHERE condition; SELECT column1, column2 FROM table_name ORDER BY column1 DESC; SELECT COUNT(*) FROM table_name GROUP BY column1;

插入数据 INSERT INTO table_name (column1, column2) VALUES (value1, value2);

更新数据 UPDATE table_name SET column1 = value1 WHERE condition;

删除数据 DELETE FROM table_name WHERE condition;

表操作 CREATE TABLE table_name (column1 datatype, column2 datatype); ALTER TABLE table_name ADD column_name datatype; DROP TABLE table_name;

数据分析案例

案例：电商销售数据分析

数据表结构

• orders (order_id, customer_id, order_date, total_amount)
• order_items (item_id, order_id, product_id, quantity, price)
• products (product_id, product_name, category)

分析目标

1. 计算每月销售额
2. 分析最畅销的产品类别
3. 计算客户平均订单价值

代码实现

每月销售额

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(total_amount) AS monthly_sales
FROM orders
GROUP BY month
ORDER BY month;

最畅销的产品类别

SELECT 
    p.category,
    SUM(oi.quantity * oi.price) AS total_sales
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY total_sales DESC
LIMIT 5;

客户平均订单价值

SELECT 
    customer_id,
    AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY customer_id
ORDER BY avg_order_value DESC;

高级分析技巧

窗口函数计算累计销售额

SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS cumulative_sales
FROM orders;

使用 CTE 分析客户购买频率

WITH customer_stats AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT 
    customer_id,
    order_count,
    total_spent / order_count AS avg_order_value
FROM customer_stats
WHERE order_count > 1;

优化技巧

• 为常用查询字段添加索引 CREATE INDEX idx_order_date ON orders(order_date);
• 使用 EXPLAIN 分析查询性能 EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
• 避免 SELECT *，只查询必要字段

以上 SQL 语句和分析案例涵盖了 MySQL 在数据分析中的常见应用场景，可根据实际需求调整查询条件和分析维度。