#SQL详解之DML - 数据操作语言完整指南

-- 1. 指定列查询（推荐：避免取冗余字段，提升速度）
SELECT order_id, user_id, total_amount, create_time
FROM orders;

-- 2. 取所有列（开发测试偶尔用，生产严格禁止！）
SELECT *
FROM employees;

-- 3. 字段别名（方便阅读、处理字段名冲突）
SELECT 
    u.user_name AS buyer,
    o.order_id,
    o.total_amount * 0.95 AS final_pay  -- 别名也支持计算结果
FROM orders o
JOIN users u ON o.user_id = u.user_id;

-- 4. 去重查询（只查有过订单的用户）
SELECT DISTINCT user_id
FROM orders;

-- 5. 条件查询（查2024年6月、支付状态完成、金额≥100的订单）
SELECT *
FROM orders
WHERE create_time BETWEEN '2024-06-01 00:00:00' AND '2024-06-30 23:59:59'
  AND pay_status = 'completed'
  AND total_amount >= 100;

-- 1. 原生LIMIT分页（PostgreSQL也支持，但写法没区别？不，主要MySQL5.7以下的窗口函数是短板，分页依赖这个多）
SELECT order_id, user_id, total_amount
FROM orders
ORDER BY create_time DESC
LIMIT 10 OFFSET 20;  -- 跳过前2页（20条），取第3页10条

-- 2. 全文搜索（MATCH...AGAINST，不用建复杂索引，小场景够用）
SELECT article_id, title, publish_time
FROM articles
WHERE MATCH(title, content) AGAINST('SQL 性能优化' IN NATURAL LANGUAGE MODE);

-- 1. 窗口函数（不需要分组就能取排名、行号、滚动统计，PostgreSQL原生全支持）
SELECT 
    order_id,
    user_id,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS user_latest_order_rank  -- 每个用户的订单倒序排名
FROM orders;

-- 2. 数组操作（支持原生数组字段，存标签、技能这类很方便）
SELECT employee_id, first_name, skills[1] AS primary_skill  -- 取第一个技能
FROM employee_profiles
WHERE 'Python' = ANY(skills);  -- 查询会Python的员工

-- 3. RETURNING子句（增删改后直接返回修改/新增/删除的行，不用再查一次！）
WITH deleted_inactive_users AS (
    DELETE FROM users
    WHERE last_login < '2023-01-01'
    RETURNING user_id  -- 返回被删的用户ID，用于同步清理其他表
)
DELETE FROM user_profiles
WHERE user_id IN (SELECT user_id FROM deleted_inactive_users);

-- 1. 单行插入
INSERT INTO products (product_name, category, price, stock)
VALUES ('无线蓝牙耳机', '电子数码', 199.00, 200);

-- 2. 批量插入（生产批量必用！比循环单行插入快几十倍）
INSERT INTO products (product_name, category, price, stock)
VALUES 
    ('机械键盘青轴', '电子数码', 399.00, 150),
    ('棉麻短袖T恤', '服饰', 79.00, 500),
    ('不锈钢保温杯', '家居', 49.00, 300);

-- 插入商品，若商品ID已存在则更新库存和价格
INSERT INTO products (product_id, product_name, price, stock)
VALUES (101, '无线蓝牙耳机升级款', 229.00, 250)
ON DUPLICATE KEY UPDATE
    product_name = VALUES(product_name),  -- 更新为新值
    price = VALUES(price),
    stock = stock + VALUES(stock);  -- 也可以结合原有值操作

-- 方式1：冲突则跳过
INSERT INTO products (product_id, product_name, price, stock)
VALUES (101, '无线蓝牙耳机升级款', 229.00, 250)
ON CONFLICT (product_id) 
DO NOTHING;

-- 方式2：冲突则更新
INSERT INTO products (product_id, product_name, price, stock)
VALUES (101, '无线蓝牙耳机升级款', 229.00, 250)
ON CONFLICT (product_id) 
DO UPDATE SET
    product_name = EXCLUDED.product_name,  -- 用EXCLUDED代替VALUES，PostgreSQL专属
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

-- 1. 简单修改（给服饰类所有商品打8折）
UPDATE products
SET price = price * 0.8
WHERE category = '服饰';

-- 2. 条件分支修改（员工年终奖按绩效发：S级10万，A级5万，B级2万，其余1万）
UPDATE employees
SET year_end_bonus = CASE
    WHEN performance_level = 'S' THEN 100000
    WHEN performance_level = 'A' THEN 50000
    WHEN performance_level = 'B' THEN 20000
    ELSE 10000
END
WHERE hire_date >= '2023-01-01';  -- 只给2023年入职的员工发

-- 给电子数码部门的所有员工涨薪5%
UPDATE employees e
INNER JOIN departments d ON e.department_id = d.department_id
SET e.salary = e.salary * 1.05
WHERE d.department_name = '电子数码';

-- 给电子数码部门的所有员工涨薪5%
UPDATE employees
SET salary = employees.salary * 1.05
FROM departments
WHERE employees.department_id = departments.department_id
  AND departments.department_name = '电子数码'
RETURNING employee_id, first_name, salary;  -- 加RETURNING直接看涨薪后的结果

-- 1. 简单删除（删除所有已取消的订单）
DELETE FROM orders
WHERE order_status = 'cancelled';

-- 2. 清空表（但保留结构、索引、约束，性能比DELETE FROM快N倍！）
TRUNCATE TABLE temp_cart_data;

-- 删除所有属于“已关闭部门”的员工
DELETE e FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_status = 'closed';

-- 删除所有属于“已关闭部门”的员工
DELETE FROM employees
USING departments
WHERE employees.department_id = departments.department_id
  AND departments.department_status = 'closed'
RETURNING employee_id, first_name;  -- 返回被删的员工记录

-- 循环删除2022年之前的日志，每次删1万条
DELETE FROM logs
WHERE log_time < '2022-01-01 00:00:00'
LIMIT 10000;
-- 开发/运维可以写个脚本循环执行直到返回受影响行数为0

-- 1. 用EXPLAIN/EXPLAIN ANALYZE分析慢查询
-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND pay_status = 'completed';
-- PostgreSQL（ANALYZE会实际执行查询，给更真实的耗时）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND pay_status = 'completed';

-- 2. 避免SELECT *，只取需要的字段
-- 3. 给WHERE、JOIN、ORDER BY的高频字段加索引
CREATE INDEX idx_orders_user_pay ON orders(user_id, pay_status);

-- 1. 批量插入代替循环单行插入
-- 2. 大批量插入时，MySQL可以临时关闭自动提交
SET autocommit = 0;
START TRANSACTION;
-- ... 批量插入语句
COMMIT;
SET autocommit = 1;
-- 3. PostgreSQL大批量插入可以用COPY命令（从CSV文件导入最快）
-- COPY products FROM '/path/to/products.csv' WITH (FORMAT csv, HEADER true);