你翻开这套 Oracle 数据库练习题的时候可能跟我当年一样第一反应不是题目难而是答案让人看不懂。题解看得懂又会做才是刷题的正确姿势。说实话Oracle 的 SQL 题刷起来特别容易卡在几个地方日期比较为什么总是差一天外连接为什么少一行排名的时候为什么三条 SQL 给三种结果。这篇文章我就用一套经典的练习表把常见题型的解题逻辑和坑位挨个过一遍。适合正在准备考试、面试或者刚转行做数据开发的同学尤其是那些“能跑出结果但不确定对不对”的人。文章里的表结构和 SQL 都是 Oracle 环境下的标准写法你拿 SQL Developer 或 SQL*Plus 都能直接跑。1. 练习题到底在考什么先看清考核维度1.1 表面考语法实际考执行逻辑我见过很多同学做 Oracle 练习题看到查询题就直接SELECT * FROM 表名然后条件一写跑出来结果跟答案不一样就开始怀疑是不是数据库版本有问题。实际上题目考的不只是语法能不能跑通而是你对执行顺序、数据类型、空值语义的理解。举一个最简单的例子WHERE hiredate 1981-01-01在某些配置下能跑通但很可能发生了隐式转换一旦字段里带时间或者会话的日期格式变了结果就是错的。刷题不是为了背 SQL是为了建立“先想逻辑再写代码”的习惯。还有一个常见误区认为所有数据库的 SQL 都一样。其实 Oracle 有自己的方言和函数体系比如字符串连接用||而不用空值要用IS NULL判断而不是 NULL日期运算也不像 MySQL 那样把日期当数字直接减。练习题的价值就在这里让你在安全环境里把这些差异点试明白。基本功踏实了后面遇到什么变体题都不慌。1.2 常见题型与对应能力要求我习惯把 Oracle 练习题的考核维度分成下面几类你在复习的时候可以直接对着这个清单过题型核心考核点常见错误单表查询条件过滤、排序、列运算空值比较、隐式类型转换多表连接连接条件、行匹配关系笛卡尔积、漏行分组统计GROUP BY、HAVING、聚合函数聚合条件写在 WHERE子查询嵌套逻辑、相关子查询单行子查询返回多行分析函数排名、分区、窗口分区键选错DML 与事务提交回滚、MERGE 使用忘记提交导致锁表这张表是我整理练习题的框架不管题目表面怎么变本质跑不出这几类。复习的时候按这个清单逐项找题练每个题型挑三五道题刷透比盲刷五十道有用得多。后面几节我就按这个顺序挑典型题目展开讲SQL 尽量用 Oracle 标准写法方便你直接在自己的环境里复现。2. 经典入门题单表查询与条件过滤的坑2.1 典型题目与参考解法先看一道出现频率极高的题查询员工表中所有在 1981 年入职的员工显示员工编号、姓名、入职日期按入职日期从早到晚排序。这道题考的是日期类型的基本功。很多新手会写出这样的答案SELECT empno, ename, hiredate FROM emp WHERE TO_CHAR(hiredate, YYYY) 1981 ORDER BY hiredate;能跑出来但不推荐。原因有两个第一hiredate列上套了TO_CHAR只要这个列上有普通索引索引就废了第二这种写法依赖字符转换遇到带时间的日期值要小心边界。更稳妥的思路是把字符串边界转成DATE再和列做范围比较SELECT empno, ename, hiredate FROM emp WHERE hiredate DATE 1981-01-01 AND hiredate DATE 1982-01-01 ORDER BY hiredate;这个写法的核心是“右开区间”。如果用BETWEEN 1981-01-01 AND 1981-12-31万一某条记录的hiredate是1981-12-31 10:30:00就会被漏掉。Oracle 的DATE类型本身就包含时间部分只是显示时经常被省略。练习题里往往不会告诉你数据里有没有时间所以按区间开闭来处理最安全。这也是为什么很多老手一看到BETWEEN和日期同时出现就会下意识警觉。2.2 空值与字符串比较的经典陷阱再来看一道几乎每套题都有的题目查询没有奖金的员工。注意员工表的comm字段没有奖金时存的是NULL不是数字 0。于是典型的错误写法就出现了SELECT ename, comm FROM emp WHERE comm NULL;结果是空。因为NULL不等于任何值它自己也不等于自己。正确写法是SELECT ename, comm FROM emp WHERE comm IS NULL;如果题目改成“查询奖金大于 100 的员工”可不能只写WHERE comm 100因为NULL 100结果是未知该行不会被返回。你必须在业务上先想清楚是只要“有奖金且大于 100”还是“有奖金就显示哪怕奖金为 0”。如果是前者写WHERE comm IS NOT NULL AND comm 100会更明确。NULL对聚合函数的影响也经常一起考。COUNT(*)统计所有行COUNT(comm)只统计comm不为空的行AVG(comm)在计算时自动忽略空值但如果所有comm都是NULL结果也是NULL。刷题时看到聚合第一反应就应该问自己这一列里有没有空值空值对结果的影响是什么2.3 日期函数与隐式转换日期函数是 Oracle 练习题的常客这里多讲一个细节EXTRACT和TO_CHAR都能截取年份。比如SELECT ename, EXTRACT(YEAR FROM hiredate) AS hire_year FROM emp WHERE EXTRACT(YEAR FROM hiredate) 1981;EXTRACT语义更规范但和TO_CHAR(hiredate, YYYY)一样属于在列上做函数运算会影响普通索引的使用。练习阶段数据量小看不出差别生产环境可能就慢得明显。所以我的建议是练习题里如果考官只关心“能不能查对”你用哪种都行如果是让你写生产可用的 SQL优先用 2.1 节的范围比较。另一个容易出问题的是TO_DATE的格式串。有人写TO_DATE(2024-07-01, YYYY-MM-DD)没问题但因为会话的 NLS 参数不同直接写字符串和日期比较时可能报ORA-01843: not a valid month。这种报错在练习题里很常见解决办法很简单不要依赖会话默认格式明确写TO_DATE和格式串或者用DATE 2024-07-01这种 ANSI 日期字面量。记住显示转换永远比隐式转换更可靠。3. 进阶必练多表连接与聚合分组3.1 两表内连接的基础写法多表连接是 Oracle 练习题的分水岭。先看最简单的查询员工姓名和所属部门名称。员工表和部门表通过deptno关联。推荐写法SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno;JOIN ON的好处是连接条件和过滤条件分得清楚。老版本 Oracle 写法是SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno d.deptno;我见过不少同学写第二种时忘记写WHERE结果出来一个笛卡尔积。练习题里如果多表查询结果比预期多很多行第一反应就是检查连接条件有没有漏。五条以内的数据看不出问题一旦表里有几百上千行笛卡尔积会直接把数据库跑挂。所以我不建议新手练连接题时用逗号写法至少心里要清楚它和JOIN ON的区别。3.2 外连接为什么老漏数据比内连接更爱出题的是外连接。经典题目查询所有部门及每个部门的员工数没有员工的部门也要显示。很多人写成SELECT d.dname, COUNT(e.ename) AS emp_cnt FROM dept d LEFT JOIN emp e ON d.deptno e.deptno GROUP BY d.dname;这个是对的。但如果有人在ON之后又加了一个WHERE e.deptno d.deptno左连接就被悄悄变回内连接了。因为ON负责匹配行而WHERE会过滤掉那些右边为NULL的行。要知道LEFT JOIN中右表不匹配时右表所有列都是NULL任何WHERE条件只要涉及右表列都会把这一行干掉。还有一个老式写法上的坑Oracle 的()外连接。比如WHERE d.deptno e.deptno()表示以部门表为主表。如果放错位置结果就反了。练习题里看到()知道它是什么意思即可实际书写我更推荐标准LEFT JOIN / RIGHT JOIN代码可读性高也方便迁移。3.3 分组统计与 HAVING 的筛选时机分组统计是面试笔试的高频区。典型题目查询每个部门员工人数只显示人数大于 3 的部门。正确写法SELECT deptno, COUNT(*) AS emp_cnt FROM emp GROUP BY deptno HAVING COUNT(*) 3 ORDER BY emp_cnt DESC;这里的核心知识点是WHERE不能使用聚合函数。因为WHERE在分组之前执行此时聚合值还没算出来。HAVING在分组之后执行专门用来过滤分组结果。你可以把GROUP BY想成“按部门把数据分篮子”HAVING是“检查每个篮子的总重量达不达标”。分组题最常见报错是ORA-00979: not a GROUP BY expression。比如有人写SELECT ename, deptno, COUNT(*) FROM emp GROUP BY deptno;ename没有出现在GROUP BY里Oracle 直接报错。这个报错很多 MySQL 用户会困惑因为 MySQL 某些宽松模式允许这种写法返回的ename是分组内任意一行。但 Oracle 的规则更严格SELECT中的非聚合列必须出现在GROUP BY中。练习题认 Oracle 的规矩不能拿 MySQL 的习惯来套。4. 子查询与集合运算从答案反推条件4.1 标量子查询与表子查询的区别子查询是 Oracle 练习题里的重头戏因为它能实现很多“先算一轮再关联主查询”的逻辑。先看一道查询员工姓名和工资同时显示其所在部门的平均工资。一种写法是标量子查询SELECT e.ename, e.sal, (SELECT AVG(sal) FROM emp x WHERE x.deptno e.deptno) AS avg_sal FROM emp e;这种写法直观每读一条员工记录就去算一次该部门平均工资。只要表数据量不大完全没问题。但你要知道它的执行逻辑是逐行执行的部门多、员工多的时候扫描次数会放大。更推荐的内联视图写法是SELECT e.ename, e.sal, t.avg_sal FROM emp e JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) t ON e.deptno t.deptno;这段 SQL 先把部门平均工资做成一张临时结果集再和员工表做连接。逻辑上是“先聚合、再关联”比逐行标量子查询更容易看懂数据量大时执行计划也更可控。练习题中如果让你“既显示明细又显示汇总”优先考虑这种写法。必要时打开执行计划你会发现两个方案的执行路径差异很大。4.2 IN、EXISTS 和 NOT IN 的取舍子查询里的经典难题是IN和EXISTS。题目查询有员工的部门信息。两种写法都能过SELECT * FROM dept WHERE deptno IN (SELECT deptno FROM emp);SELECT d.* FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno d.deptno);IN适合子查询结果集不大且不关注相关性的场景EXISTS是相关子查询对每一行部门去员工表里找匹配通常可以提前短路。练习题里两者都能跑但你要理解EXISTS里写SELECT 1还是SELECT *对结果没有影响因为它只关心有没有行不关心选什么列。真正容易翻车的是NOT IN。如果子查询结果中包含NULLNOT IN会直接返回空结果。比如想查没有员工的部门写成WHERE deptno NOT IN (SELECT deptno FROM emp WHERE deptno IS NOT NULL)才安全或者直接用NOT EXISTSSELECT d.* FROM dept d WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.deptno d.deptno);原因还是NULL的逻辑NOT IN本质上是一连串的“不等于”只要有一个NULL参与比较整个条件就变成未知行就被丢弃。这个坑我在处理真实数据时踩过所以提醒你看到NOT IN第一反应就是子查询会不会有NULL。4.3 集合运算 UNION 与 UNION ALL 的选择集合运算也是常考题尤其是UNION和UNION ALL的区别。UNION会合并两个查询结果并去重同时默认排序UNION ALL只是把两个结果直接拼在一起不去重不排序。题目经常这样出查询销售部门和管理部门的所有员工姓名请问结果是否需要去重如果需要去重用SELECT ename FROM emp WHERE deptno 10 UNION SELECT ename FROM emp WHERE deptno 20;如果两个部门的人员名单理论上不会重复或者业务上允许重复就用UNION ALLSELECT ename FROM emp WHERE deptno 10 UNION ALL SELECT ename FROM emp WHERE deptno 20;UNION多一步排序和去重数据量大时消耗明显。练习题里考这个点本质上是在考你有没有数据意识。我看到很多答案习惯性写UNION但如果你能判断“两个结果集不可能重复”写UNION ALL会显得更专业。反过来如果不能确定是否重复为了结果正确宁可牺牲一点性能也要用UNION这道题才算真正理解了。5. 分析函数与分页面试和工作中最爱考5.1 ROW_NUMBER、RANK、DENSE_RANK分析函数是 Oracle 练习题的加分项也是面试题里最爱考的点。先看一道排名题按工资从高到低给员工排名显示姓名、工资和排名。SELECT ename, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS row_num, RANK() OVER (ORDER BY sal DESC) AS rk, DENSE_RANK() OVER (ORDER BY sal DESC) AS dr FROM emp;三个函数的区别一定要记牢。ROW_NUMBER不管工资是否相同都按顺序编一个不重复的号RANK遇到并列名次时会跳号比如两个并列第一下一个就是第三DENSE_RANK不跳号并列第一之后还是第二。如果题目要求“取工资最高的前两名并列也要显示”直接用ROW_NUMBER会漏人必须用RANK或DENSE_RANK配合WHERE条件。刷题时可以用一个简单口诀不需要并列就ROW_NUMBER要并列且允许跳号就RANK要并列且不允许跳号就DENSE_RANK。这个知识点光看答案记不住最好自己在练习表里造几条相同工资的数据分别跑一遍看输出差异。5.2 ROWNUM 实现分页的经典写法分页查询在练习题里有两种考法一种考老式ROWNUM写法一种考新式ROW_NUMBER写法。先说传统写法按员工编号排序后取第 6 到第 10 条记录。SELECT * FROM ( SELECT e.*, ROWNUM AS rn FROM ( SELECT empno, ename FROM emp ORDER BY empno ) e WHERE ROWNUM 10 ) WHERE rn 6;这里的关键是为什么内层先要WHERE ROWNUM 10而不是直接WHERE ROWNUM BETWEEN 6 AND 10。因为ROWNUM是在结果集生成过程中逐行分配的第一条符合条件的记录如果编号是 1因不满足“大于等于 6”被丢弃下一条重新从 1 开始编号于是永远等不到 6结果就是空集。这个坑几乎是 Oracle 分页题的必考点。新版推荐用ROW_NUMBER实现SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY empno) AS rn FROM emp e ) WHERE rn BETWEEN 6 AND 10;逻辑更清晰也避免ROWNUM的边界问题。Oracle 12c 之后还支持OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY但很多老项目还在用传统分页练习题也会默认你掌握至少一种写法。我的建议是两种都会写代码时优先ROW_NUMBER面试时如果能顺手解释ROWNUM的坑反而加分。5.3 窗口函数常见应用分析函数不只是排名窗口聚合也很常用。比如要在每一行上显示“该部门员工数”。SELECT ename, deptno, COUNT(*) OVER (PARTITION BY deptno) AS dept_cnt FROM emp;这里PARTITION BY deptno相当于把员工按部门分区COUNT(*) OVER统计每个分区里的行数。它和第 4.1 节的内联视图作用类似但分析函数不会压缩行数明细行仍然保留非常适合“既要明细又要汇总”的场景。还有一点值得注意分析函数中的ORDER BY可以和PARTITION BY组合实现累计值。例如SELECT deptno, ename, sal, SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sal FROM emp;这段 SQL 会按部门内工资从小到大累计求和。练习题里如果出现“累计”“排名”“Top N”这类字眼基本就是分析函数的主场。不要试图用普通聚合函数临时拼容易写错分区逻辑。6. DML 与事务控制练习题里容易忽略的雷区6.1 UPDATE、DELETE 的提交与回滚很多练习题只考 SELECT但真正的项目开发离不开 DML。有一道很典型的题把部门 10 的员工工资上调 10%并提交事务。参考写法UPDATE emp SET sal sal * 1.1 WHERE deptno 10; COMMIT;看起来简单但这里面藏着两个考点。第一UPDATE如果不带WHERE就是把全表都改了练习题数据量小也许看不出后果生产环境就是事故。第二执行完UPDATE后必须显式COMMIT或ROLLBACK否则当前会话改了数据其他会话是看不到的甚至可能因为行锁被阻塞。我建议你刷题时装两个数据库会话一边UPDATE不提交另一条会话去查同一条记录观察它卡住。这个体验比背十遍“锁”的概念更有用。练完之后按ROLLBACK还是COMMIT要看题目要求如果题目说“确认改错了请回滚”你要在 UPDATE 后写ROLLBACK如果只是要你演示更新那就执行COMMIT固化结果。6.2 MERGE 的运用场景MERGE是 Oracle DML 里很有特色的一条语句。经典题目根据临时表new_sal中每个员工的编号和工资如果员工表里已有同编号员工则更新工资没有则插入一条新员工记录。MERGE INTO emp t USING new_sal s ON (t.empno s.empno) WHEN MATCHED THEN UPDATE SET t.sal s.sal WHEN NOT MATCHED THEN INSERT (empno, ename, sal) VALUES (s.empno, s.ename, s.sal);用生活化类比MERGE就像你拿着名单去签到签过名的就更新状态没签过的就补登记。用起来很爽但有一个很实际的坑ON条件的关联字段在源表里不能有重复值。如果new_sal表里同一个empno出现两行MERGE会直接报ORA-38104提示ON列无法唯一匹配。这个报错很多人第一次遇到会懵实际上就是源数据不干净。如果题目要求单独插入一条测试数据可以用dual表构造MERGE INTO emp t USING (SELECT 9999 AS empno, TEST AS ename, 3000 AS sal FROM dual) s ON (t.empno s.empno) WHEN NOT MATCHED THEN INSERT (empno, ename, sal) VALUES (s.empno, s.ename, s.sal);dual是 Oracle 的虚拟表专门用来计算常量或单行值。练习题里出现SELECT ... FROM dual不用多想它就是在造一行临时数据。6.3 锁与并发基础DML 题偶尔会延伸到锁和并发。比如题目问为什么第二个会话执行UPDATE emp SET sal sal * 1.1 WHERE empno 100;一直卡着不返回原因通常是第一条会话对同一行做了修改但没有提交行锁还没释放。练习题阶段你可以用两个会话模拟一下。先在一个会话里执行UPDATE emp SET sal sal 100 WHERE empno 7900;不提交切到另一个会话执行UPDATE emp SET sal sal 200 WHERE empno 7900;第二个会话会一直等待。这时候回第一个会话执行COMMIT或ROLLBACK第二个会话才会继续。理解这个过程比死记“悲观锁”“乐观锁”更有用。如果要排查“谁锁了哪一行”可以查视图SELECT sid, serial#, username, blocking_session, wait_class FROM v$session WHERE username IS NOT NULL AND blocking_session IS NOT NULL;不过练习题环境权限未必够而且这种语句在生产环境要谨慎使用。刷 DML 题时多开两个会话做实验你会发现很多“卡住”的现象背后都是事务没有结束而不是数据库坏了。7. 常见报错与排查技巧实录7.1 高频报错速查表练习到一定程度你会发现自己报的错来来回回就那么几个。这里整理一个速查表方便你对照排查报错代码常见场景解决方法ORA-00979GROUP BY 表达式错误SELECT 中的非聚合列必须出现在 GROUP BY 中ORA-00904无效标识符列名拼错或 WHERE 中使用 SELECT 里定义的别名ORA-00933SQL 命令未正确结束常见于把 MySQL 的 LIMIT 写进 OracleORA-01722数字转换失败字符串列里混入非数字隐式转换失败ORA-01427单行子查询返回多行使用但子查询返回了多行改用 IN 或 EXISTSORA-00918未明确定义列连接查询中同名列没加表别名或列别名ORA-00904是最容易忽略的一个。很多人在SELECT里定义了别名AS avg_sal然后在WHERE条件里直接写WHERE avg_sal 3000结果报无效标识符。这是因为WHERE在SELECT别名生效之前执行。如果你确实要按别名筛选需要把整段 SQL 包成内联视图再过滤。ORA-00933也算高频错尤其是有 MySQL 经验的人。Oracle 不支持LIMIT分页顺手写成WHERE ROWNUM 10或者用FETCH语法才对。看到这个报错先检查语句里是不是混进了非 Oracle 方言。7.2 一条 SQL 跑得慢练习题阶段怎么排查练习题的数据量通常不大一条 SQL 跑得快不代表写得好。我建议每个查詢写完都用执行计划看一眼。最朴素的命令是EXPLAIN PLAN FOR SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno d.deptno; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);或者在 SQL Developer 里直接按 F10 查看。重点看三样东西有没有全表扫描、有没有排序、有没有隐式转换。比如 2.1 节里TO_CHAR(hiredate, YYYY) 1981的写法执行计划很可能出现FULL TABLE SCAN而范围比较可能会走索引。练习题阶段看执行计划不是为了调优而是培养“先看计划再写 SQL”的思维。你自己可以造一张一万行的测试表把IN和EXISTS、UNION和UNION ALL各写一遍对比执行计划。实际生产环境里数据量上来后同样的逻辑性能差距会成倍放大。刷题时能养成这个习惯比多会几条语法有价值得多。7.3 题解之外的 3 个习惯最后分享几个我自己练题时的习惯不一定写进答案里但对提升效果很重要。第一个习惯不管多简单的查询都明确写列名不要用SELECT *。一方面是可读性另一方面是避免结果集里塞进不相关的列。练习题里不要求但面试官看到满屏SELECT *会怀疑你的生产意识。第二个习惯写每条 SQL 之前先问自己两个问题——“这张表有多少行”“这条 SQL 会影响多少行”数据量影响执行计划影响选择IN还是EXISTS影响是否该用分析函数。没有这个意识你写出来的 SQL 只能在练习环境里跑。第三个习惯练习时一定要用独立账号和自己建的测试表不要在公司或同学共用的环境里随便试 DML。很多人刷题喜欢在原有表上直接改碰到UPDATE忘了WHERE或者执行了DELETE没提交很容易把别人数据带走。自己建一张临时表随便怎么练都行。这本身也是数据库开发的基本职业素养。