SQL 语句使用案例大全：从入门到精通

AI 智能体 4 小时前 0 1

SQL（Structured Query Language）是数据库操作的核心语言。无论你是开发人员、数据分析师还是运维工程师，掌握 SQL 的各种使用技巧都能让你事半功倍。

本文整理了 SQL 语句的各种实用案例，从基础查询到高级技巧，涵盖日常开发中的常见场景。建议收藏备用！

一、基础查询

1.1 基本 SELECT

-- 查询所有列
SELECT * FROM users;

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

-- 使用别名
SELECT 
    id AS user_id,
    username AS user_name,
    email AS user_email
FROM users;

-- 去重查询
SELECT DISTINCT department FROM employees;

1.2 WHERE 条件查询

-- 等于条件
SELECT * FROM users WHERE status = 'active';

-- 范围查询
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- IN 查询
SELECT * FROM users WHERE role IN ('admin', 'editor', 'viewer');

-- LIKE 模糊查询
SELECT * FROM articles WHERE title LIKE '%Python%';

-- 多条件组合
SELECT * FROM orders 
WHERE status = 'pending' 
  AND created_at >= '2025-01-01'
  AND total_amount > 1000;

-- NULL 判断
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

1.3 ORDER BY 和 LIMIT

-- 单列排序
SELECT * FROM products ORDER BY price DESC;

-- 多列排序
SELECT * FROM orders 
ORDER BY created_at DESC, total_amount ASC;

-- 限制结果数量
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 分页查询（每页 20 条，第 3 页）
SELECT * FROM articles 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 40;

二、连接查询（JOIN）

2.1 内连接（INNER JOIN）

内连接只返回两个表中匹配的记录。

-- 内连接：只返回匹配的记录
SELECT 
    o.id AS order_id,
    o.order_no,
    u.username,
    o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed';

2.2 左连接（LEFT JOIN）

左连接返回左表的所有记录，右表无匹配则为 NULL。常用于查找"不存在"的记录。

-- 左连接：返回左表所有记录，右表无匹配则为 NULL
SELECT 
    u.id AS user_id,
    u.username,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
HAVING COUNT(o.id) = 0;  -- 找出没有下单的用户

2.3 多表连接

-- 多表连接
SELECT 
    o.order_no,
    u.username,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS subtotal
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
WHERE o.created_at >= '2025-01-01';

三、聚合函数与分组

3.1 基础聚合函数

SQL 提供 5 个常用聚合函数：

COUNT()：统计行数
SUM()：求和
AVG()：平均值
MAX()：最大值
MIN()：最小值

-- 基础聚合函数
SELECT 
    COUNT(*) AS total_users,
    COUNT(DISTINCT department) AS dept_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees;

-- 按部门统计
SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- HAVING 过滤分组结果
SELECT 
    department,
    COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

3.2 GROUP BY 分组

配合聚合函数使用，按指定列分组统计。

3.3 HAVING 过滤分组

HAVING 用于过滤 GROUP BY 后的分组结果，WHERE 无法在分组后过滤。

四、子查询

4.1 WHERE 子查询

在 WHERE 条件中使用子查询结果。

4.2 SELECT 子查询

在 SELECT 列表中嵌入子查询，常用于统计关联数据。

4.3 FROM 子查询（派生表）

将子查询结果作为临时表使用。

4.4 EXISTS 子查询

判断子查询是否返回结果，效率通常优于 IN。

-- WHERE 子句中的子查询
SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE is_active = 1
);

-- SELECT 子句中的子查询
SELECT 
    u.username,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- FROM 子句中的子查询（派生表）
SELECT 
    dept,
    avg_salary
FROM (
    SELECT 
        department AS dept,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE avg_salary > 50000;

-- EXISTS 子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'pending'
);

五、数据操作（DML）

5.1 INSERT 插入数据

-- 插入单条记录
INSERT INTO users (username, email, status, created_at)
VALUES ('zhangsan', 'zhangsan@example.com', 'active', NOW());

-- 插入多条记录
INSERT INTO users (username, email, status, created_at)
VALUES 
    ('lisi', 'lisi@example.com', 'active', NOW()),
    ('wangwu', 'wangwu@example.com', 'active', NOW()),
    ('zhaoliu', 'zhaoliu@example.com', 'inactive', NOW());

-- 从另一张表插入数据
INSERT INTO users_backup (id, username, email, status)
SELECT id, username, email, status
FROM users
WHERE status = 'active';

5.2 UPDATE 更新数据

-- 更新单条记录
UPDATE users 
SET email = 'newemail@example.com', 
    updated_at = NOW()
WHERE id = 1;

-- 更新多条记录
UPDATE products 
SET status = 'offline', 
    updated_at = NOW()
WHERE stock = 0;

-- 条件更新
UPDATE employees 
SET salary = salary * 1.1
WHERE performance_score >= 90;

-- 多表更新
UPDATE orders o
INNER JOIN users u ON o.user_id = u.id
SET o.status = 'cancelled'
WHERE u.status = 'banned';

5.3 DELETE 删除数据

-- 删除指定记录
DELETE FROM users WHERE id = 1;

-- 条件删除
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- 软删除（推荐）
UPDATE users 
SET deleted_at = NOW(), 
    status = 'deleted'
WHERE id = 1;

-- 清空表（慎用）
TRUNCATE TABLE temp_data;  -- 比 DELETE 快，重置自增 ID

⚠️ 重要提示：生产环境执行 UPDATE/DELETE 前，务必先用 SELECT 验证条件！

六、索引使用

6.1 创建索引

-- 查看索引
SHOW INDEX FROM users;

-- 创建单列索引
CREATE INDEX idx_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 删除索引
DROP INDEX idx_username ON users;

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';

-- 查看详细执行计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

6.2 索引使用原则

场景	建议
WHERE 条件列	✅ 适合建索引
JION 连接列	✅ 适合建索引
ORDER BY 列	✅ 适合建索引
低基数列（如性别）	❌ 不建议建索引
频繁更新的列	❌ 谨慎建索引
大文本列	❌ 不适合建索引

七、窗口函数

窗口函数是 SQL 的高级特性，支持在分组的同时保留原始行数据。

7.1 排名函数

ROW_NUMBER()：连续行号（1,2,3,4...）
RANK()：跳跃排名（1,2,2,4...）
DENSE_RANK()：密集排名（1,2,2,3...）

-- ROW_NUMBER：行号
SELECT 
    id,
    username,
    ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM users;

-- RANK/DENSE_RANK：排名
SELECT 
    username,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM user_scores;

-- 分区窗口函数
SELECT 
    department,
    username,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 累计求和
SELECT 
    date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_revenue;

-- 前后行比较
SELECT 
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
    LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_revenue;

7.2 分析函数

LAG()：访问前一行数据
LEAD()：访问后一行数据
FIRST_VALUE()：窗口内第一个值
LAST_VALUE()：窗口内最后一个值
SUM() OVER：累计求和

八、CTE（公共表表达式）

CTE 让复杂查询更清晰，支持递归查询。

8.1 基础 CTE

-- 基础 CTE
WITH active_users AS (
    SELECT id, username FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE username LIKE '%a%';

-- 递归 CTE（层级查询）
WITH RECURSIVE org_tree AS (
    -- 锚点：根节点
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归：子节点
    SELECT d.id, d.name, d.parent_id, ot.level + 1
    FROM departments d
    INNER JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;

8.2 递归 CTE

适用于层级结构查询，如组织架构、分类树等。

九、性能优化

9.1 常见性能问题

-- 避免 SELECT *
SELECT id, username, email FROM users;  -- ✅ 好
SELECT * FROM users;                     -- ❌ 不好

-- 避免在索引列上使用函数
SELECT * FROM users WHERE DATE(created_at) = '2025-01-01';  -- ❌ 索引失效
SELECT * FROM users 
WHERE created_at >= '2025-01-01' 
  AND created_at < '2025-01-02';                             -- ✅ 索引有效

-- 避免 OR 连接不同列
SELECT * FROM users WHERE username = 'zhang' OR email = 'zhang@example.com';  -- ❌
SELECT * FROM users WHERE username = 'zhang'
UNION ALL
SELECT * FROM users WHERE email = 'zhang@example.com';  -- ✅

-- 使用 LIMIT 限制结果集
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;  -- ✅

-- 避免大表 JOIN 大表
-- 先过滤再 JOIN
SELECT * FROM (
    SELECT * FROM orders WHERE created_at >= '2025-01-01'
) o
JOIN users u ON o.user_id = u.id;

9.2 优化建议

使用 EXPLAIN 分析：查看查询执行计划，找出性能瓶颈
避免全表扫描：确保 WHERE 条件使用索引
合理分页：大表分页使用 LIMIT offset, size 或基于 ID 分页
批量操作：多条 INSERT/UPDATE 合并执行
定期维护：OPTIMIZE TABLE、ANALYZE TABLE
读写分离：复杂查询走从库

十、实战案例

10.1 用户订单统计

SELECT 
    u.id AS user_id,
    u.username,
    COUNT(o.id) AS total_orders,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS avg_order_value,
    MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'cancelled'
GROUP BY u.id, u.username
HAVING total_orders > 0
ORDER BY total_spent DESC
LIMIT 100;

10.2 连续登录用户

-- 找出连续登录 7 天以上的用户
SELECT user_id, COUNT(*) AS consecutive_days
FROM (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY login_date
        ) DAY) AS grp
    FROM user_logins
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 7;

10.3 销售排行榜（按月份）

SELECT 
    DATE_FORMAT(o.created_at, '%Y-%m') AS month,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    RANK() OVER (
        PARTITION BY DATE_FORMAT(o.created_at, '%Y-%m') 
        ORDER BY SUM(oi.quantity * oi.unit_price) DESC
    ) AS category_rank
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
GROUP BY month, p.category
ORDER BY month, category_rank;

总结

本文涵盖了 SQL 语句的核心使用场景：

✅ 基础查询与条件过滤
✅ 多表连接与聚合统计
✅ 子查询与 CTE
✅ 数据操作（INSERT/UPDATE/DELETE）
✅ 索引优化与性能调优
✅ 窗口函数高级应用

学习建议：

先在测试环境练习，熟悉语法
生产环境执行前先用 EXPLAIN 分析
复杂查询先写子查询，再优化为 JOIN
定期复习和总结，形成自己的 SQL 模板库

🔗 相关链接

欢迎收藏转发，有问题欢迎留言讨论！💪

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。