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 2500001.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 10000001.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) 5000000001.3 多表连接查询1.3.1 INNER JOIN内连接返回两个表中匹配的行-- 查询教师及其所属部门 SELECT teacher.name, dept.name FROM teacher INNER JOIN dept ON (teacher.deptdept.id)1.3.2 LEFT/RIGHT JOIN外连接LEFT JOIN返回左表所有行右表无匹配则为NULLRIGHT JOIN相反-- 列出所有教师包括没有部门的 SELECT teacher.name, dept.name FROM teacher LEFT JOIN dept ON (teacher.deptdept.id) -- 列出所有部门包括没有教师的 SELECT teacher.name, dept.name FROM teacher RIGHT JOIN dept ON (teacher.deptdept.id)1.3.3 自连接同一表与自己连接常用于层级或关系数据-- 查询巴士路线中相邻的车站 SELECT a.stop, b.stop FROM route a JOIN route b ON (a.numb.num AND a.companyb.company) WHERE a.pos1 b.pos1.4 子查询与嵌套SELECT子查询是嵌套在另一个查询中的SELECT语句-- 找出人口多于俄罗斯的国家 SELECT name FROM world WHERE population (SELECT population FROM world WHERE nameRussia) -- 找出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 world1.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 teacher1.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 covid1.7 实战案例解析1.7.1 足球比赛数据分析-- 计算每场比赛的主客队得分 SELECT mdate, team1, SUM(CASE WHEN teamidteam1 THEN 1 ELSE 0 END) score1, team2, SUM(CASE WHEN teamidteam2 THEN 1 ELSE 0 END) score2 FROM game LEFT JOIN goal ON id matchid GROUP BY mdate, id, team1, team21.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) 31.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 Andrews1.8 性能优化与常见问题1.8.1 索引使用建议为WHERE、JOIN、ORDER BY中的列创建索引避免在索引列上使用函数或计算复合索引遵循最左前缀原则1.8.2 查询优化技巧限制返回的列和行数避免使用SELECT *复杂查询分解为多个简单查询使用EXPLAIN分析执行计划1.8.3 常见错误排查分组查询中SELECT包含非聚合列混淆WHERE和HAVING的使用场景多表连接时列名歧义使用表名前缀NULL值比较使用IS NULL而非 NULL1.9 高级技巧与最佳实践1.9.1 动态SQL生成-- 使用CASE动态生成列 SELECT SUM(CASE WHEN subjectCS THEN response ELSE 0 END) AS cs_responses, SUM(CASE WHEN subjectMath THEN response ELSE 0 END) AS math_responses FROM nss1.9.2 递归查询-- 使用WITH实现递归CTE WITH RECURSIVE bus_routes AS ( SELECT stop FROM route WHERE num4 AND companyLRT AND pos1 UNION ALL SELECT r.stop FROM route r JOIN bus_routes br ON r.num4 AND r.companyLRT AND r.posbr.pos1 ) SELECT * FROM bus_routes1.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 实际开发中的经验分享命名规范保持表名、列名一致且有意义使用下划线或驼峰命名法。注释复杂查询添加注释说明业务逻辑和特殊处理。参数化查询防止SQL注入提高性能。分页处理大数据量查询使用LIMIT和OFFSET分页。错误处理捕获并记录SQL执行错误。版本控制SQL脚本纳入版本管理。测试针对不同数据场景测试查询性能。文档记录数据模型和常用查询。掌握这些SELECT语句的技巧后你将能够高效地从数据库中提取所需信息解决实际工作中的各种数据查询需求。记住编写SQL不仅要考虑功能的实现还要关注性能和可维护性。