Oracle SQL语句大全详解教程
引言
Oracle SQL是管理和操作关系型数据库的核心工具，覆盖数据查询、操作、定义和控制四大领域。本教程从基础到高级，结合真实业务场景，通过示例数据表详细解析每条语句的执行逻辑。亮点：每节附实战陷阱提醒和性能优化技巧！
Oracle官方中文网：https://www.oracle.com/cn/
第一章 基础语句篇
数据表结构：
-- 员工表(employees)
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY, -- 员工ID
name VARCHAR2(50), -- 姓名
dept_id NUMBER, -- 部门ID
salary NUMBER(8,2), -- 薪资
hire_date DATE -- 入职日期
);
-- 部门表(departments)
CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY, -- 部门ID
dept_name VARCHAR2(50) -- 部门名称
);
1.1 数据查询(SELECT)
-- 查询所有员工信息
SELECT * FROM employees;
-- 结果示例：
| EMP_ID | NAME | DEPT_ID | SALARY | HIRE_DATE |
|--------|--------|---------|--------|------------|
| 101 | 张三 | 10 | 8000 | 2020-01-01 |
| 102 | 李四 | 20 | 9500 | 2019-05-12 |
-- 带条件查询（薪资>9000的员工）
SELECT name, salary
FROM employees
WHERE salary > 9000;
解析：
SELECT指定返回列，*表示所有列WHERE过滤条件，支持>, <, =, LIKE等运算符 陷阱：字符串比较需用单引号'IT'，数字直接写9000
1.2 数据插入(INSERT)
-- 插入单条数据
INSERT INTO departments (dept_id, dept_name)
VALUES (10, '技术部');
-- 插入多条数据（Oracle特有语法）
INSERT ALL
INTO departments VALUES (20, '销售部')
INTO departments VALUES (30, '市场部')
SELECT * FROM DUAL;
解析：
列名可省略（需按表结构顺序）DUAL是Oracle虚拟表，用于无表操作 性能贴士：批量插入用INSERT ALL比单条快5倍以上！
第二章 中级操作篇
2.1 表连接(JOIN)
-- 查询员工所属部门名称（内连接）
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- 结果：
| NAME | DEPT_NAME |
|------|-----------|
| 张三 | 技术部 |
| 李四 | 销售部 |
解析：
INNER JOIN只返回匹配记录表别名e/d简化代码 高级技巧：
-- 左连接（返回所有员工+匹配部门）
SELECT e.name, NVL(d.dept_name, '未分配')
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
2.2 聚合函数与分组(GROUP BY)
-- 统计各部门平均薪资
SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING AVG(e.salary) > 8000;
-- 结果：
| DEPT_NAME | AVG_SALARY |
|-----------|------------|
| 销售部 | 9500 |
解析：
AVG()计算平均值，其他函数：SUM(), COUNT(), MAX()GROUP BY按部门分组HAVING过滤分组结果（与WHERE区别：WHERE在分组前过滤行，HAVING在分组后过滤组）
第三章 高级应用篇
3.1 窗口函数
-- 计算部门内薪资排名
SELECT
name,
salary,
dept_id,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM employees;
-- 结果：
| NAME | SALARY | DEPT_ID | RANK |
|------|--------|---------|------|
| 李四 | 9500 | 20 | 1 |
| 张三 | 8000 | 10 | 1 |
解析：
PARTITION BY定义窗口分区（类似分组但不聚合）ORDER BY指定排序规则RANK()生成排名（相同值并列） 业务场景：薪资梯队分析、销售业绩排名
3.2 递归查询(CONNECT BY)
-- 创建层级部门表
ALTER TABLE departments ADD parent_id NUMBER;
-- 查询技术部所有子部门
SELECT dept_name
FROM departments
START WITH dept_name = '技术部'
CONNECT BY PRIOR dept_id = parent_id;
解析：
START WITH定义根节点CONNECT BY指定父子关系PRIOR指向父级字段 应用场景：组织架构树、分类层级导航
第四章 特殊语句实战
4.1 MERGE语句（更新插入一体化）
-- 员工奖金表(bonus)存在则更新，否则插入
MERGE INTO bonus b
USING employees e
ON (b.emp_id = e.emp_id)
WHEN MATCHED THEN
UPDATE SET b.amount = e.salary * 0.1
WHEN NOT MATCHED THEN
INSERT (b.emp_id, b.amount)
VALUES (e.emp_id, e.salary * 0.05);
优势：原子操作避免先查询后判断，事务效率提升40%！
4.2 闪回查询(Flashback Query)
-- 查询10分钟前的数据（需启用UNDO管理）
SELECT * FROM employees
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE emp_id = 101;
场景：误删数据恢复，审计数据变更历史 限制：依赖UNDO表空间保留时间
第五章 性能优化黄金法则
索引策略
高频查询字段建索引：CREATE INDEX idx_emp_dept ON employees(dept_id)避免对索引列使用函数：WHERE TRUNC(hire_date) = '2023-01-01' ❌ → WHERE hire_date >= '2023-01-01' ✅ 执行计划分析
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary > 8000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
关键指标：
COST：执行成本（越低越好）INDEX RANGE SCAN：索引范围扫描（高效） 批量提交
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO log_table VALUES (...);
IF MOD(i,1000)=0 THEN COMMIT; -- 每1000条提交一次
END LOOP;
END;
结语
掌握Oracle SQL需理论与实践结合：
基础语句是根基，务必精确书写高级特性解决复杂业务问题性能优化永无止境，需持续分析调优
建议：在生产环境执行写操作前，务必在测试库验证结果！