【Oracle】DQL语言

个人主页：Guiat
归属专栏：Oracle

正文
DQL（Data Query Language）是SQL语言的核心组成部分，专门用于从数据库中查询和检索数据。在Oracle数据库中，DQL主要就是SELECT语句，但别小看这一个语句，它的功能强大到可以让你眼花缭乱！

1. DQL概述

1.1 什么是DQL？

DQL就像是数据库的"搜索引擎"，你告诉它你想要什么数据，它就帮你从茫茫数据海洋中找出来。不管你的数据库有多大，DQL都能精准定位你需要的信息。

1.2 DQL的核心功能

Oracle DQL的功能可以分为以下几个层次：

2. SELECT语句基础

2.1 基本语法结构

SELECT语句就像是一个万能工具箱，每个关键字都有自己的作用：

-- Oracle SELECT语句的完整语法结构
SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
[OFFSET offset_value ROWS]
[FETCH NEXT number_of_rows ROWS ONLY];

2.2 最简单的查询

让我们从最基础的开始，就像学走路一样：

-- 查询所有员工信息
SELECT * FROM employees;-- 查询特定字段
SELECT employee_id, first_name, last_name, salary 
FROM employees;-- 使用别名让结果更友好
SELECT employee_id AS "员工编号",first_name AS "名字",last_name AS "姓氏",salary AS "薪资"
FROM employees;-- 计算字段
SELECT first_name || ' ' || last_name AS "全名",salary * 12 AS "年薪",ROUND(salary * 1.1, 2) AS "涨薪后月薪"
FROM employees;

2.3 DISTINCT去重

有时候数据会有重复，DISTINCT就像是一个"去重神器"：

-- 查询所有不重复的部门ID
SELECT DISTINCT department_id 
FROM employees;-- 多字段组合去重
SELECT DISTINCT department_id, job_id 
FROM employees;-- 统计不重复记录数
SELECT COUNT(DISTINCT department_id) AS "部门总数"
FROM employees;

3. WHERE条件筛选

3.1 基本条件运算符

WHERE子句就像是数据的"筛子"，帮你过滤出想要的数据：

-- 数值比较
SELECT * FROM employees WHERE salary > 5000;
SELECT * FROM employees WHERE salary BETWEEN 3000 AND 8000;
SELECT * FROM employees WHERE department_id IN (10, 20, 30);-- 字符串匹配
SELECT * FROM employees WHERE first_name = 'John';
SELECT * FROM employees WHERE first_name LIKE 'J%';  -- 以J开头
SELECT * FROM employees WHERE first_name LIKE '%son'; -- 以son结尾
SELECT * FROM employees WHERE first_name LIKE '_ohn'; -- 第一个字符任意，后面是ohn-- 空值处理
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

3.2 逻辑运算符组合

多个条件可以像搭积木一样组合起来：

-- AND：所有条件都必须满足
SELECT * FROM employees 
WHERE salary > 5000 AND department_id = 20;-- OR：满足任一条件即可
SELECT * FROM employees 
WHERE department_id = 10 OR department_id = 20;-- NOT：取反
SELECT * FROM employees 
WHERE NOT (salary < 3000);-- 复杂条件组合
SELECT * FROM employees 
WHERE (salary > 5000 OR commission_pct IS NOT NULL)AND department_id IN (10, 20, 30)AND hire_date > DATE '2005-01-01';

3.3 高级条件筛选

Oracle提供了一些特殊的条件运算符：

-- EXISTS：检查子查询是否返回结果
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);-- ANY/SOME：与子查询结果中的任一值比较
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 20
);-- ALL：与子查询结果中的所有值比较
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 20
);-- REGEXP_LIKE：正则表达式匹配
SELECT * FROM employees
WHERE REGEXP_LIKE(first_name, '^[A-D].*');  -- 名字以A-D开头

4. 排序和分页

4.1 ORDER BY排序

排序就像给数据排队，让它们按你想要的顺序站好：

-- 单字段排序
SELECT * FROM employees ORDER BY salary DESC;  -- 按薪资降序
SELECT * FROM employees ORDER BY hire_date;    -- 按入职日期升序（默认）-- 多字段排序
SELECT * FROM employees 
ORDER BY department_id, salary DESC;-- 使用表达式排序
SELECT first_name, last_name, salary
FROM employees
ORDER BY LENGTH(first_name), salary DESC;-- 空值排序控制
SELECT * FROM employees 
ORDER BY commission_pct NULLS LAST;  -- 空值排在最后-- 使用CASE进行自定义排序
SELECT * FROM employees
ORDER BY CASE department_id WHEN 10 THEN 1WHEN 20 THEN 2WHEN 30 THEN 3ELSE 4END,salary DESC;

4.2 分页查询

Oracle 12c之后引入了标准的分页语法，比以前的ROWNUM方式更直观：

-- Oracle 12c+ 标准分页语法
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;  -- 跳过前10行，取接下来的5行-- 传统的ROWNUM分页（适用于所有Oracle版本）
SELECT * FROM (SELECT ROWNUM rn, e.* FROM (SELECT * FROM employees ORDER BY salary DESC) e WHERE ROWNUM <= 15
) WHERE rn > 10;-- 使用ROW_NUMBER()窗口函数分页
SELECT * FROM (SELECT employee_id, first_name, last_name, salary,ROW_NUMBER() OVER (ORDER BY salary DESC) as rnFROM employees
) WHERE rn BETWEEN 11 AND 15;

5. 聚合函数和分组

5.1 常用聚合函数

聚合函数就像是数据的"计算器"，帮你做各种统计：

-- 基本聚合函数
SELECT COUNT(*) AS "总员工数",COUNT(commission_pct) AS "有提成的员工数",AVG(salary) AS "平均薪资",SUM(salary) AS "薪资总和",MAX(salary) AS "最高薪资",MIN(salary) AS "最低薪资",STDDEV(salary) AS "薪资标准差",VARIANCE(salary) AS "薪资方差"
FROM employees;-- 字符串聚合函数
SELECT LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name) AS "所有员工名字"
FROM employees;-- 日期聚合
SELECT MIN(hire_date) AS "最早入职日期",MAX(hire_date) AS "最晚入职日期"
FROM employees;

5.2 GROUP BY分组

分组就像是把数据按类别整理到不同的盒子里：

-- 按部门分组统计
SELECT department_id,COUNT(*) AS "员工数量",AVG(salary) AS "平均薪资",SUM(salary) AS "部门薪资总和"
FROM employees
GROUP BY department_id
ORDER BY department_id;-- 多字段分组
SELECT department_id,job_id,COUNT(*) AS "员工数量",AVG(salary) AS "平均薪资"
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;-- 使用表达式分组
SELECT EXTRACT(YEAR FROM hire_date) AS "入职年份",COUNT(*) AS "该年入职人数"
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY "入职年份";

5.3 HAVING子句

HAVING就像是对分组结果的"二次筛选"：

-- 筛选员工数量大于5的部门
SELECT department_id,COUNT(*) AS "员工数量",AVG(salary) AS "平均薪资"
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5
ORDER BY "员工数量" DESC;-- 复杂的HAVING条件
SELECT department_id,COUNT(*) AS "员工数量",AVG(salary) AS "平均薪资"
FROM employees
WHERE hire_date > DATE '2005-01-01'  -- WHERE先筛选原始数据
GROUP BY department_id
HAVING AVG(salary) > 5000            -- HAVING再筛选分组结果AND COUNT(*) >= 3
ORDER BY "平均薪资" DESC;

6. 多表连接查询

6.1 连接类型概览

多表连接就像是把不同的拼图片拼在一起，形成完整的画面：

6.2 内连接（INNER JOIN）

内连接是最常用的连接方式，只返回两表都有匹配的记录：

-- 标准内连接语法
SELECT e.employee_id,e.first_name,e.last_name,d.department_name,j.job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id;-- Oracle传统连接语法（等价于上面）
SELECT e.employee_id,e.first_name,e.last_name,d.department_name,j.job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_idAND e.job_id = j.job_id;-- 多条件连接
SELECT e.first_name,e.last_name,m.first_name AS "经理名字"
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > 5000;

6.3 外连接（OUTER JOIN）

外连接能保留一侧表的所有记录，即使另一侧没有匹配：

-- 左外连接：显示所有部门，包括没有员工的部门
SELECT d.department_name,COUNT(e.employee_id) AS "员工数量"
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_name;-- 右外连接：显示所有员工，包括没有部门的员工
SELECT e.first_name,e.last_name,d.department_name
FROM departments d
RIGHT JOIN employees e ON d.department_id = e.department_id;-- 全外连接：显示所有部门和所有员工
SELECT e.first_name,e.last_name,d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;-- Oracle传统外连接语法（使用(+)）
SELECT d.department_name,e.first_name
FROM departments d, employees e
WHERE d.department_id = e.department_id(+);  -- 左外连接

6.4 自连接

自连接就像是表在照镜子，与自己进行连接：

-- 查询员工及其经理信息
SELECT emp.first_name || ' ' || emp.last_name AS "员工",mgr.first_name || ' ' || mgr.last_name AS "经理",emp.salary AS "员工薪资",mgr.salary AS "经理薪资"
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.employee_id
ORDER BY emp.employee_id;-- 查找薪资比同部门平均薪资高的员工
SELECT e1.first_name,e1.last_name,e1.salary,e1.department_id
FROM employees e1
WHERE e1.salary > (SELECT AVG(e2.salary)FROM employees e2WHERE e2.department_id = e1.department_id
);

7. 子查询

7.1 子查询类型

子查询就像是"查询中的查询"，可以嵌套使用：

7.2 标量子查询

标量子查询返回单个值，可以用在任何需要单个值的地方：

-- 查询薪资高于平均薪资的员工
SELECT first_name,last_name,salary,(SELECT AVG(salary) FROM employees) AS "平均薪资"
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);-- 在SELECT子句中使用子查询
SELECT first_name,last_name,salary,(SELECT department_name FROM departments d WHERE d.department_id = e.department_id) AS "部门名称"
FROM employees e;-- 在ORDER BY中使用子查询
SELECT first_name, last_name, department_id
FROM employees
ORDER BY (SELECT department_name FROM departments d WHERE d.department_id = employees.department_id
);

7.3 表子查询

表子查询返回多行多列，可以当作临时表使用：

-- 查询每个部门薪资最高的员工
SELECT e.first_name,e.last_name,e.salary,e.department_id
FROM employees e
WHERE (e.department_id, e.salary) IN (SELECT department_id, MAX(salary)FROM employeesGROUP BY department_id
);-- 使用子查询作为临时表
SELECT dept_stats.department_id,dept_stats.avg_salary,d.department_name
FROM (SELECT department_id,AVG(salary) as avg_salary,COUNT(*) as emp_countFROM employeesGROUP BY department_idHAVING COUNT(*) > 5
) dept_stats
JOIN departments d ON dept_stats.department_id = d.department_id;

7.4 相关子查询

相关子查询的执行依赖于外层查询的每一行：

-- 查询薪资高于本部门平均薪资的员工
SELECT e1.first_name,e1.last_name,e1.salary,e1.department_id
FROM employees e1
WHERE e1.salary > (SELECT AVG(e2.salary)FROM employees e2WHERE e2.department_id = e1.department_id
);-- 使用EXISTS的相关子查询
SELECT d.department_name
FROM departments d
WHERE EXISTS (SELECT 1FROM employees eWHERE e.department_id = d.department_idAND e.salary > 10000
);-- 查询每个部门中入职最早的员工
SELECT e1.first_name,e1.last_name,e1.hire_date,e1.department_id
FROM employees e1
WHERE e1.hire_date = (SELECT MIN(e2.hire_date)FROM employees e2WHERE e2.department_id = e1.department_id
);

8. 高级查询技巧

8.1 窗口函数

窗口函数是Oracle的强大功能，可以在不改变结果集行数的情况下进行分析：

-- ROW_NUMBER()：为每行分配唯一序号
SELECT first_name,last_name,salary,department_id,ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank
FROM employees;-- RANK()和DENSE_RANK()：处理并列排名
SELECT first_name,last_name,salary,RANK() OVER (ORDER BY salary DESC) as salary_rank,DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;-- LAG()和LEAD()：访问前后行数据
SELECT first_name,last_name,salary,LAG(salary, 1) OVER (ORDER BY hire_date) as prev_salary,LEAD(salary, 1) OVER (ORDER BY hire_date) as next_salary
FROM employees;-- 累计统计
SELECT first_name,last_name,salary,SUM(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) as running_total,AVG(salary) OVER (ORDER BY hire_date ROWS 2 PRECEDING) as moving_avg
FROM employees;

8.2 分析函数

分析函数提供了更多的统计分析能力：

-- NTILE()：将数据分成N个桶
SELECT first_name,last_name,salary,NTILE(4) OVER (ORDER BY salary) as salary_quartile
FROM employees;-- PERCENT_RANK()：百分位排名
SELECT first_name,last_name,salary,PERCENT_RANK() OVER (ORDER BY salary) as pct_rank,CUME_DIST() OVER (ORDER BY salary) as cumulative_dist
FROM employees;-- FIRST_VALUE()和LAST_VALUE()
SELECT first_name,last_name,salary,department_id,FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS UNBOUNDED PRECEDING) as highest_salary_in_dept,LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_salary_in_dept
FROM employees;

8.3 集合运算

集合运算可以组合多个查询的结果：

-- UNION：合并结果集（去重）
SELECT first_name, last_name FROM employees WHERE department_id = 10
UNION
SELECT first_name, last_name FROM employees WHERE salary > 10000;-- UNION ALL：合并结果集（不去重）
SELECT 'Employee' as type, first_name, last_name FROM employees
UNION ALL
SELECT 'Manager' as type, first_name, last_name FROM employees WHERE manager_id IS NULL;-- INTERSECT：交集
SELECT employee_id FROM employees WHERE department_id = 20
INTERSECT
SELECT employee_id FROM employees WHERE salary > 5000;-- MINUS：差集
SELECT employee_id FROM employees WHERE department_id = 20
MINUS
SELECT employee_id FROM employees WHERE salary > 8000;

9. 实际应用案例

9.1 销售数据分析系统

让我们看一个完整的销售数据分析案例：

-- 创建示例表结构
CREATE TABLE sales_data (sale_id NUMBER PRIMARY KEY,product_id NUMBER,customer_id NUMBER,sale_date DATE,quantity NUMBER,unit_price NUMBER(10,2),total_amount NUMBER(12,2),sales_rep_id NUMBER,region VARCHAR2(50)
);-- 复杂的销售分析查询
WITH monthly_sales AS (-- 月度销售统计SELECT EXTRACT(YEAR FROM sale_date) as sale_year,EXTRACT(MONTH FROM sale_date) as sale_month,region,SUM(total_amount) as monthly_total,COUNT(*) as transaction_count,AVG(total_amount) as avg_transactionFROM sales_dataWHERE sale_date >= ADD_MONTHS(SYSDATE, -12)GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date), region
),
regional_rankings AS (-- 区域排名SELECT *,RANK() OVER (PARTITION BY sale_year, sale_month ORDER BY monthly_total DESC) as region_rank,LAG(monthly_total) OVER (PARTITION BY region ORDER BY sale_year, sale_month) as prev_month_totalFROM monthly_sales
)
SELECT sale_year,sale_month,region,monthly_total,region_rank,CASE WHEN prev_month_total IS NULL THEN 'N/A'ELSE ROUND(((monthly_total - prev_month_total) / prev_month_total) * 100, 2) || '%'END as growth_rate,SUM(monthly_total) OVER (PARTITION BY region ORDER BY sale_year, sale_month) as running_total
FROM regional_rankings
ORDER BY sale_year, sale_month, region_rank;

9.2 员工绩效评估系统

-- 员工绩效综合评估
WITH employee_metrics AS (SELECT e.employee_id,e.first_name,e.last_name,e.department_id,e.salary,e.hire_date,-- 计算工作年限ROUND(MONTHS_BETWEEN(SYSDATE, e.hire_date) / 12, 1) as years_of_service,-- 部门内薪资排名RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as dept_salary_rank,-- 薪资百分位PERCENT_RANK() OVER (ORDER BY e.salary) as salary_percentileFROM employees e
),
performance_scores AS (SELECT em.*,d.department_name,-- 综合评分计算CASE WHEN salary_percentile >= 0.8 THEN 5WHEN salary_percentile >= 0.6 THEN 4WHEN salary_percentile >= 0.4 THEN 3WHEN salary_percentile >= 0.2 THEN 2ELSE 1END as salary_score,CASE WHEN years_of_service >= 10 THEN 5WHEN years_of_service >= 7 THEN 4WHEN years_of_service >= 5 THEN 3WHEN years_of_service >= 2 THEN 2ELSE 1END as experience_scoreFROM employee_metrics emJOIN departments d ON em.department_id = d.department_id
)
SELECT first_name,last_name,department_name,salary,years_of_service,dept_salary_rank,ROUND(salary_percentile * 100, 1) as salary_percentile_pct,(salary_score + experience_score) as total_score,CASE WHEN (salary_score + experience_score) >= 8 THEN 'Excellent'WHEN (salary_score + experience_score) >= 6 THEN 'Good'WHEN (salary_score + experience_score) >= 4 THEN 'Average'ELSE 'Below Average'END as performance_rating
FROM performance_scores
ORDER BY total_score DESC, salary DESC;

9.3 库存管理分析

-- 库存周转率分析
WITH inventory_analysis AS (SELECT p.product_id,p.product_name,p.category,i.current_stock,i.reorder_level,-- 计算过去30天的销售量NVL(s.sales_30days, 0) as sales_30days,-- 计算库存天数CASE WHEN NVL(s.sales_30days, 0) = 0 THEN 999ELSE ROUND(i.current_stock / (s.sales_30days / 30), 1)END as days_of_inventory,-- 库存状态CASE WHEN i.current_stock <= i.reorder_level THEN 'Low Stock'WHEN i.current_stock <= i.reorder_level * 1.5 THEN 'Medium Stock'ELSE 'High Stock'END as stock_statusFROM products pJOIN inventory i ON p.product_id = i.product_idLEFT JOIN (SELECT product_id,SUM(quantity) as sales_30daysFROM sales_dataWHERE sale_date >= SYSDATE - 30GROUP BY product_id) s ON p.product_id = s.product_id
)
SELECT product_name,category,current_stock,sales_30days,days_of_inventory,stock_status,-- 库存周转率分类CASE WHEN days_of_inventory <= 7 THEN 'Fast Moving'WHEN days_of_inventory <= 30 THEN 'Normal Moving'WHEN days_of_inventory <= 90 THEN 'Slow Moving'ELSE 'Dead Stock'END as turnover_category,-- 建议行动CASE WHEN stock_status = 'Low Stock' THEN 'Reorder Immediately'WHEN days_of_inventory > 90 THEN 'Consider Promotion'WHEN days_of_inventory <= 7 THEN 'Monitor Closely'ELSE 'Normal Operation'END as recommended_action
FROM inventory_analysis
ORDER BY CASE stock_status WHEN 'Low Stock' THEN 1WHEN 'Medium Stock' THEN 2ELSE 3END,days_of_inventory;

10. 性能优化技巧

10.1 查询优化策略

10.2 实用优化技巧

-- 1. 使用绑定变量避免硬解析
-- 不好的写法
SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = 101;-- 好的写法（使用绑定变量）
SELECT * FROM employees WHERE employee_id = :emp_id;-- 2. 合理使用索引提示
SELECT /*+ INDEX(e, emp_department_ix) */ first_name, last_name 
FROM employees e 
WHERE department_id = 20;-- 3. 避免在WHERE子句中使用函数
-- 不好的写法
SELECT * FROM employees WHERE UPPER(first_name) = 'JOHN';-- 好的写法
SELECT * FROM employees WHERE first_name = 'John';-- 4. 使用EXISTS代替IN（当子查询返回大量数据时）
-- 可能较慢
SELECT * FROM departments 
WHERE department_id IN (SELECT department_id FROM employees);-- 通常更快
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);-- 5. 分页查询优化
-- 对于大数据量的分页，使用基于游标的分页
SELECT * FROM (SELECT employee_id, first_name, last_name, salary,ROW_NUMBER() OVER (ORDER BY employee_id) as rnFROM employeesWHERE salary > 5000
) WHERE rn BETWEEN 1001 AND 1020;

Oracle的DQL功能真的是博大精深，从简单的SELECT到复杂的分析函数，每一个功能都有其独特的应用场景。掌握这些技巧，你就能像数据库的"魔法师"一样，从海量数据中快速提取出有价值的信息。记住，实践是最好的老师，多写多练，你会发现DQL的魅力所在！

结语
感谢您的阅读！期待您的一键三连！欢迎指正！