SQL SELECT语句实战：从基础查询到高级技巧

1. SQL SELECT 语句实战指南：从基础到高级查询技巧

作为一名数据库开发人员，我经常需要处理各种复杂的数据查询需求。SQL的SELECT语句是数据检索的核心工具，掌握它的各种用法对提高工作效率至关重要。本文将分享我在实际工作中总结的SELECT语句使用技巧，涵盖基础查询、条件筛选、聚合函数、多表连接等核心知识点。

1.1 基础SELECT查询

最基本的SELECT语句用于从表中检索数据。语法结构如下：

SELECT 列名1, 列名2, ... FROM 表名

例如，查询world表中所有国家的名称和人口：

SELECT name, population FROM world

注意：在生产环境中，尽量避免使用SELECT *，明确列出需要的字段能减少网络传输和提高查询效率。

1.1.1 WHERE条件筛选

WHERE子句用于过滤记录，只返回满足条件的行。常见运算符包括：

• 比较运算符：=,>,<,>=,<=,<>(不等于)
• 范围判断：BETWEEN...AND...
• 集合判断：IN(...)
• 模糊匹配：LIKE

-- 查询德国的人口 SELECT population FROM world WHERE name = 'Germany' -- 查询面积在20万到25万之间的国家 SELECT name, area FROM world WHERE area BETWEEN 200000 AND 250000

1.1.2 LIKE模式匹配

LIKE操作符配合通配符使用可以实现灵活的模糊查询：

• %：匹配任意数量字符（包括零个）
• _：匹配单个字符

-- 查找以Y开头的国家 SELECT name FROM world WHERE name LIKE 'Y%' -- 查找名字中包含x的国家 SELECT name FROM world WHERE name LIKE '%x%' -- 查找名字是4个字母的国家 SELECT name FROM world WHERE name LIKE '____'

1.2 聚合函数与分组查询

1.2.1 常用聚合函数

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

-- 计算非洲国家的GDP总和 SELECT SUM(gdp) FROM world WHERE continent = 'Africa' -- 统计面积超过100万平方公里的国家数量 SELECT COUNT(name) FROM world WHERE area >= 1000000

1.2.2 GROUP BY分组

GROUP BY将结果集按一个或多个列分组，通常与聚合函数配合使用：

-- 按大洲分组，统计每个洲的国家数量 SELECT continent, COUNT(name) FROM world GROUP BY continent

经验分享：GROUP BY子句中的列必须是SELECT列表中的非聚合列，否则会导致语法错误。

1.2.3 HAVING筛选分组

HAVING用于对分组后的结果进行筛选，类似于WHERE但作用于分组：

-- 找出总人口超过5亿的大洲 SELECT continent, SUM(population) FROM world GROUP BY continent HAVING SUM(population) > 500000000

1.3 多表连接查询

1.3.1 INNER JOIN内连接

返回两个表中匹配的行：

-- 查询教师及其所属部门 SELECT teacher.name, dept.name FROM teacher INNER JOIN dept ON (teacher.dept=dept.id)

1.3.2 LEFT/RIGHT JOIN外连接

LEFT JOIN返回左表所有行，右表无匹配则为NULL；RIGHT JOIN相反：

-- 列出所有教师，包括没有部门的 SELECT teacher.name, dept.name FROM teacher LEFT JOIN dept ON (teacher.dept=dept.id) -- 列出所有部门，包括没有教师的 SELECT teacher.name, dept.name FROM teacher RIGHT JOIN dept ON (teacher.dept=dept.id)

1.3.3 自连接

同一表与自己连接，常用于层级或关系数据：

-- 查询巴士路线中相邻的车站 SELECT a.stop, b.stop FROM route a JOIN route b ON (a.num=b.num AND a.company=b.company) WHERE a.pos+1 = b.pos

1.4 子查询与嵌套SELECT

子查询是嵌套在另一个查询中的SELECT语句：

-- 找出人口多于俄罗斯的国家 SELECT name FROM world WHERE population > (SELECT population FROM world WHERE name='Russia') -- 找出GDP高于欧洲所有国家的国家 SELECT name FROM world WHERE gdp > ALL(SELECT gdp FROM world WHERE gdp > 0 AND continent = 'Europe')

性能提示：复杂的子查询可能影响性能，考虑使用JOIN或临时表优化。

1.5 窗口函数(Window Functions)

窗口函数对一组行执行计算，同时保留原始行：

-- 按得票数排名 SELECT party, votes, RANK() OVER (ORDER BY votes DESC) as posn FROM ge WHERE constituency = 'S14000024' AND yr = 2017 -- 按年份分区后排名 SELECT yr, party, votes, RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn FROM ge WHERE constituency = 'S14000021'

常用窗口函数：

• ROW_NUMBER()：行号
• RANK()/DENSE_RANK()：排名
• LAG()/LEAD()：访问前后行数据

1.6 实用函数与技巧

1.6.1 字符串处理

-- 连接字符串 SELECT CONCAT(name, ' City') FROM world -- 替换字符串 SELECT REPLACE(capital, name, '') AS ext FROM world

1.6.2 条件逻辑

-- CASE WHEN条件判断 SELECT name, CASE WHEN continent IN ('Europe', 'Asia') THEN 'Eurasia' WHEN continent IN ('NA', 'SA') THEN 'America' ELSE continent END FROM world -- COALESCE处理NULL值 SELECT name, COALESCE(mobile, '暂无号码') FROM teacher

1.6.3 日期函数

-- 提取日期部分 SELECT name, DAY(whn), MONTH(whn), YEAR(whn) FROM covid -- 日期格式化 SELECT DATE_FORMAT(whn,'%Y-%m-%d') FROM covid -- 日期计算 SELECT DATE_ADD(whn, INTERVAL 1 WEEK) FROM covid

1.7 实战案例解析

1.7.1 足球比赛数据分析

-- 计算每场比赛的主客队得分 SELECT mdate, team1, SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1, team2, SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2 FROM game LEFT JOIN goal ON id = matchid GROUP BY mdate, id, team1, team2

1.7.2 疫情数据分析

-- 计算每日新增病例 SELECT name, DAY(whn), confirmed - LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) AS new FROM covid WHERE name = 'Italy' AND MONTH(whn) = 3

1.7.3 电影数据库查询

-- 查询与特定演员合作过的其他演员 SELECT DISTINCT a.name FROM actor a JOIN casting c ON a.id = c.actorid WHERE c.movieid IN ( SELECT movieid FROM casting JOIN actor ON actorid = actor.id WHERE name = 'Julie Andrews') AND a.name != 'Julie Andrews'

1.8 性能优化与常见问题

1.8.1 索引使用建议

• 为WHERE、JOIN、ORDER BY中的列创建索引
• 避免在索引列上使用函数或计算
• 复合索引遵循最左前缀原则

1.8.2 查询优化技巧

• 限制返回的列和行数
• 避免使用SELECT *
• 复杂查询分解为多个简单查询
• 使用EXPLAIN分析执行计划

1.8.3 常见错误排查

• 分组查询中SELECT包含非聚合列
• 混淆WHERE和HAVING的使用场景
• 多表连接时列名歧义（使用表名前缀）
• NULL值比较使用IS NULL而非= NULL

1.9 高级技巧与最佳实践

1.9.1 动态SQL生成

-- 使用CASE动态生成列 SELECT SUM(CASE WHEN subject='CS' THEN response ELSE 0 END) AS cs_responses, SUM(CASE WHEN subject='Math' THEN response ELSE 0 END) AS math_responses FROM nss

1.9.2 递归查询

-- 使用WITH实现递归CTE WITH RECURSIVE bus_routes AS ( SELECT stop FROM route WHERE num='4' AND company='LRT' AND pos=1 UNION ALL SELECT r.stop FROM route r JOIN bus_routes br ON r.num='4' AND r.company='LRT' AND r.pos=br.pos+1 ) SELECT * FROM bus_routes

1.9.3 事务处理

BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

1.10 实际开发中的经验分享

1. 命名规范：保持表名、列名一致且有意义，使用下划线或驼峰命名法。

2. 注释：复杂查询添加注释说明业务逻辑和特殊处理。

3. 参数化查询：防止SQL注入，提高性能。

4. 分页处理：大数据量查询使用LIMIT和OFFSET分页。

5. 错误处理：捕获并记录SQL执行错误。

6. 版本控制：SQL脚本纳入版本管理。

7. 测试：针对不同数据场景测试查询性能。

8. 文档：记录数据模型和常用查询。

掌握这些SELECT语句的技巧后，你将能够高效地从数据库中提取所需信息，解决实际工作中的各种数据查询需求。记住，编写SQL不仅要考虑功能的实现，还要关注性能和可维护性。