-- 查询所有字段
SELECT * FROM users;

-- 查询指定字段
SELECT id, username, email FROM users;

-- 带别名
SELECT 
    id AS user_id,
    username AS name,
    email AS "Email Address"
FROM users;

-- 字段计算
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total
FROM orders;

-- 去除重复记录
SELECT DISTINCT department FROM employees;

-- 多字段组合去重
SELECT DISTINCT department, position FROM employees;

-- 限制返回行数
SELECT * FROM users LIMIT 10;

-- 分页查询（第11-20条）
SELECT * FROM users LIMIT 10 OFFSET 10;
-- 或
SELECT * FROM users LIMIT 10, 10;

-- 查询前10条
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 基本比较
SELECT * FROM users WHERE age >= 18;
SELECT * FROM products WHERE price < 100;
SELECT * FROM orders WHERE status != 'cancelled';

-- 字符串比较
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name != '张三';

-- BETWEEN AND（包含边界）
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- IN（多值匹配）
SELECT * FROM users WHERE city IN ('北京', '上海', '广州', '深圳');

-- NOT IN
SELECT * FROM users WHERE city NOT IN ('北京', '上海');

-- 查询为空
SELECT * FROM users WHERE phone IS NULL;

-- 查询不为空
SELECT * FROM users WHERE phone IS NOT NULL;

-- LIKE 模式匹配
-- % 匹配任意字符
SELECT * FROM users WHERE name LIKE '张%';      -- 张三, 张三丰
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE name LIKE '%三%';     -- 包含"三"

-- _ 匹配单个字符
SELECT * FROM users WHERE name LIKE '张_';      -- 张三, 张四
SELECT * FROM users WHERE name LIKE '张__';     -- 张三丰

-- NOT LIKE
SELECT * FROM users WHERE name NOT LIKE '%admin%';

-- REGEXP/RLIKE（正则表达式）
SELECT * FROM users WHERE name REGEXP '^张';
SELECT * FROM users WHERE email REGEXP '^[a-z]+@gmail\\.com$';

-- AND 逻辑与
SELECT * FROM users WHERE age >= 18 AND status = 'active';

-- OR 逻辑或
SELECT * FROM users WHERE city = '北京' OR city = '上海';

-- NOT 逻辑非
SELECT * FROM users WHERE NOT status = 'banned';

-- 优先级：NOT > AND > OR
SELECT * FROM users 
WHERE (age >= 18 AND status = 'active') 
   OR (age < 18 AND parent_consent = 'Y');

-- 升序（ASC，默认）
SELECT * FROM products ORDER BY price ASC;

-- 降序（DESC）
SELECT * FROM products ORDER BY price DESC;

-- 按别名排序
SELECT 
    name,
    price,
    price * 1.1 AS sale_price
FROM products ORDER BY sale_price;

-- 先按第一个字段，相同值时按第二个字段
SELECT * FROM employees ORDER BY department ASC, salary DESC;

-- 不同字段不同方向
SELECT * FROM users ORDER BY status ASC, created_at DESC;

-- 按计算结果排序
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total
FROM orders 
ORDER BY total DESC;

-- 随机排序
SELECT * FROM users ORDER BY RAND();

-- 按部门统计人数
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department;

-- 按状态统计订单
SELECT status, COUNT(*) AS order_count, SUM(total) AS total_amount
FROM orders
GROUP BY status;

-- 多字段分组
SELECT department, position, COUNT(*) AS headcount
FROM employees
GROUP BY department, position;

-- HAVING 过滤分组后的结果
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 10000;

-- WHERE vs HAVING
-- WHERE: 过滤行（在分组前过滤）
-- HAVING: 过滤分组（在分组后过滤）

-- 示例：统计每个部门的平均工资，筛选人数大于5的部门
SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active'        -- 先过滤行
GROUP BY department
HAVING COUNT(*) > 5            -- 再过滤分组
ORDER BY avg_sal DESC;         -- 最后排序

-- 合并分组内的值
SELECT department, GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ', ') AS members
FROM employees
GROUP BY department;

-- 去重合并
SELECT department, GROUP_CONCAT(DISTINCT position ORDER BY position) AS positions
FROM employees
GROUP BY department;

-- 笛卡尔积（慎用）
SELECT * FROM table1 CROSS JOIN table2;
SELECT * FROM table1, table2;  -- 隐式写法

-- 基本语法
SELECT o.id, o.order_no, u.username, u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- 隐式内连接
SELECT o.id, o.order_no, u.username
FROM orders o, users u
WHERE o.user_id = u.id;

-- 多表连接
SELECT o.id, o.order_no, u.username, p.product_name, oi.quantity
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- 返回左表所有记录，右表无匹配则显示 NULL
SELECT u.id, u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 查询未下单用户
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 返回右表所有记录，左表无匹配则显示 NULL
SELECT u.username, o.order_no
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- 查询每个员工的经理
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 查询同部门的其他员工
SELECT e1.name, e2.name AS colleague
FROM employees e1
JOIN employees e2 ON e1.department = e2.department AND e1.id != e2.id;

-- 当连接字段名称相同时
SELECT * FROM orders
INNER JOIN order_items USING (order_id);

-- 等价于
SELECT * FROM orders
INNER JOIN order_items ON orders.order_id = order_items.order_id;

-- 返回单个值
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 查询最高工资员工
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 返回一列值
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');

-- NOT IN 排除
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE status = 'completed');

-- 返回一行
SELECT * FROM employees
WHERE (department, salary) = (
    SELECT department, MAX(salary)
    FROM employees
    GROUP BY department
);

-- 返回临时表
SELECT *
FROM (SELECT department, AVG(salary) AS avg_sal
      FROM employees
      GROUP BY department) AS dept_stats
WHERE avg_sal > 8000;

-- EXISTS：检查子查询是否返回行
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'completed'
);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 子查询引用外层表
SELECT p1.product_name, p1.price
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price) 
    FROM products p2 
    WHERE p2.category = p1.category
);

-- 合并多个 SELECT 结果（自动去重）
SELECT name FROM students
UNION
SELECT name FROM teachers;

-- UNION ALL（不去重，效率更高）
SELECT name FROM students
UNION ALL
SELECT name FROM teachers;

-- 带条件的 UNION
(SELECT id, name, '学生' AS type FROM students WHERE age >= 18)
UNION
(SELECT id, name, '教师' AS type FROM teachers WHERE status = 'active')
ORDER BY name;

SELECT 
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade,
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS description
FROM students;

-- 统计各状态数量
SELECT 
    SUM(status = 'pending') AS pending_count,
    SUM(status = 'processing') AS processing_count,
    SUM(status = 'completed') AS completed_count,
    COUNT(*) AS total
FROM orders;

-- 或使用 COUNT + CASE
SELECT 
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count
FROM orders;

-- ROW_NUMBER()：连续排名（无并列）
SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS ranking
FROM students;

-- RANK()：跳跃排名（有并列）
SELECT 
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS ranking
FROM students;

-- DENSE_RANK()：密集排名（有并列）
SELECT 
    name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS ranking
FROM students;

-- 分组排名
SELECT 
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 累计求和
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_sum
FROM orders;

-- 分组累计
SELECT 
    department,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY department ORDER BY order_date) AS dept_cumulative
FROM orders;

-- 分析 SELECT 语句
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 详细分析
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%@example.com';

-- 查看所有字段
EXPLAIN ANALYZE FORMAT=JSON SELECT ...

system > const > eq_ref > ref > range > index > ALL

✅ 掌握 SELECT 基本查询和别名使用
✅ 熟练使用 WHERE 条件、LIKE 模糊匹配
✅ 理解 GROUP BY 分组和 HAVING 过滤
✅ 精通 INNER JOIN、LEFT JOIN 多表连接
✅ 学会使用子查询和 UNION 联合查询
✅ 掌握窗口函数进行排名和累计计算
✅ 善用 EXPLAIN 分析查询性能