Oracle SQL 基础：查询、过滤、排序与分组全流程
1.1 SELECT 语句的基本结构
Oracle SQL 的查询操作从 SELECT 语句开始，这是最基础也是最重要的 SQL 语句。SELECT 语句的基本语法结构如下：
SELECT column1, column2, …
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column;
这个结构包含了 SQL 查询的五个核心部分：选择列、指定表、过滤条件、分组和排序。下面我们通过一个简单的示例来理解：
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 90
ORDER BY salary DESC;
这段代码会从 employees 表中选择员工 ID、姓名和薪资，只显示部门 ID 为 90 的员工，并按薪资降序排列。
1.2 FROM 子句：指定数据源
FROM 子句用于指定查询的数据源，即要查询的表或视图。在 Oracle 中，可以查询单个表或多个表（通过连接）。
#publish-mermaid-0-1791450259325{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-0-1791450259325 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-0-1791450259325 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-0-1791450259325 .error-icon{fill:#552222;}#publish-mermaid-0-1791450259325 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-0-1791450259325 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-0-1791450259325 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-0-1791450259325 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-0-1791450259325 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-0-1791450259325 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-0-1791450259325 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-0-1791450259325 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-0-1791450259325 .marker.cross{stroke:#333333;}#publish-mermaid-0-1791450259325 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-0-1791450259325 p{margin:0;}#publish-mermaid-0-1791450259325 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-0-1791450259325 .cluster-label text{fill:#333;}#publish-mermaid-0-1791450259325 .cluster-label span{color:#333;}#publish-mermaid-0-1791450259325 .cluster-label span p{background-color:transparent;}#publish-mermaid-0-1791450259325 .label text,#publish-mermaid-0-1791450259325 span{fill:#333;color:#333;}#publish-mermaid-0-1791450259325 .node rect,#publish-mermaid-0-1791450259325 .node circle,#publish-mermaid-0-1791450259325 .node ellipse,#publish-mermaid-0-1791450259325 .node polygon,#publish-mermaid-0-1791450259325 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-0-1791450259325 .rough-node .label text,#publish-mermaid-0-1791450259325 .node .label text,#publish-mermaid-0-1791450259325 .image-shape .label,#publish-mermaid-0-1791450259325 .icon-shape .label{text-anchor:middle;}#publish-mermaid-0-1791450259325 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-0-1791450259325 .rough-node .label,#publish-mermaid-0-1791450259325 .node .label,#publish-mermaid-0-1791450259325 .image-shape .label,#publish-mermaid-0-1791450259325 .icon-shape .label{text-align:center;}#publish-mermaid-0-1791450259325 .node.clickable{cursor:pointer;}#publish-mermaid-0-1791450259325 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-0-1791450259325 .arrowheadPath{fill:#333333;}#publish-mermaid-0-1791450259325 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-0-1791450259325 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-0-1791450259325 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-0-1791450259325 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-0-1791450259325 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-0-1791450259325 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-0-1791450259325 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-0-1791450259325 .cluster text{fill:#333;}#publish-mermaid-0-1791450259325 .cluster span{color:#333;}#publish-mermaid-0-1791450259325 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-0-1791450259325 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-0-1791450259325 rect.text{fill:none;stroke-width:0;}#publish-mermaid-0-1791450259325 .icon-shape,#publish-mermaid-0-1791450259325 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-0-1791450259325 .icon-shape p,#publish-mermaid-0-1791450259325 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-0-1791450259325 .icon-shape .label rect,#publish-mermaid-0-1791450259325 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-0-1791450259325 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-0-1791450259325 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-0-1791450259325 .node .neo-node{stroke:#9370DB;}#publish-mermaid-0-1791450259325 [data-look=\”neo\”].node rect,#publish-mermaid-0-1791450259325 [data-look=\”neo\”].cluster rect,#publish-mermaid-0-1791450259325 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791450259325 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-0-1791450259325 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-0-1791450259325 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791450259325 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-0-1791450259325 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791450259325 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-0-1791450259325 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791450259325 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-0-1791450259325 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}
SELECT 语句开始
解析 FROM 子句
确定数据源表
检查表是否存在
获取表结构信息
准备数据读取
这张图展示了 Oracle 处理 FROM 子句的流程。当 Oracle 执行 SELECT 语句时，首先解析 FROM 子句来确定要查询的表，然后检查表是否存在，获取表结构信息，最后准备数据读取操作。
1.3 WHERE 子句：数据过滤
WHERE 子句用于过滤数据，只返回满足特定条件的行。WHERE 子句可以包含各种条件表达式，如比较运算符、逻辑运算符等。
#publish-mermaid-1-1791450259400{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-1-1791450259400 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-1-1791450259400 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-1-1791450259400 .error-icon{fill:#552222;}#publish-mermaid-1-1791450259400 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-1-1791450259400 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-1-1791450259400 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-1-1791450259400 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-1-1791450259400 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-1-1791450259400 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-1-1791450259400 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-1-1791450259400 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-1-1791450259400 .marker.cross{stroke:#333333;}#publish-mermaid-1-1791450259400 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-1-1791450259400 p{margin:0;}#publish-mermaid-1-1791450259400 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-1-1791450259400 .cluster-label text{fill:#333;}#publish-mermaid-1-1791450259400 .cluster-label span{color:#333;}#publish-mermaid-1-1791450259400 .cluster-label span p{background-color:transparent;}#publish-mermaid-1-1791450259400 .label text,#publish-mermaid-1-1791450259400 span{fill:#333;color:#333;}#publish-mermaid-1-1791450259400 .node rect,#publish-mermaid-1-1791450259400 .node circle,#publish-mermaid-1-1791450259400 .node ellipse,#publish-mermaid-1-1791450259400 .node polygon,#publish-mermaid-1-1791450259400 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1-1791450259400 .rough-node .label text,#publish-mermaid-1-1791450259400 .node .label text,#publish-mermaid-1-1791450259400 .image-shape .label,#publish-mermaid-1-1791450259400 .icon-shape .label{text-anchor:middle;}#publish-mermaid-1-1791450259400 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-1-1791450259400 .rough-node .label,#publish-mermaid-1-1791450259400 .node .label,#publish-mermaid-1-1791450259400 .image-shape .label,#publish-mermaid-1-1791450259400 .icon-shape .label{text-align:center;}#publish-mermaid-1-1791450259400 .node.clickable{cursor:pointer;}#publish-mermaid-1-1791450259400 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-1-1791450259400 .arrowheadPath{fill:#333333;}#publish-mermaid-1-1791450259400 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-1-1791450259400 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-1-1791450259400 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1-1791450259400 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-1-1791450259400 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1-1791450259400 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-1-1791450259400 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-1-1791450259400 .cluster text{fill:#333;}#publish-mermaid-1-1791450259400 .cluster span{color:#333;}#publish-mermaid-1-1791450259400 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-1-1791450259400 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-1-1791450259400 rect.text{fill:none;stroke-width:0;}#publish-mermaid-1-1791450259400 .icon-shape,#publish-mermaid-1-1791450259400 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-1-1791450259400 .icon-shape p,#publish-mermaid-1-1791450259400 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-1-1791450259400 .icon-shape .label rect,#publish-mermaid-1-1791450259400 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-1-1791450259400 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-1-1791450259400 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-1-1791450259400 .node .neo-node{stroke:#9370DB;}#publish-mermaid-1-1791450259400 [data-look=\”neo\”].node rect,#publish-mermaid-1-1791450259400 [data-look=\”neo\”].cluster rect,#publish-mermaid-1-1791450259400 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791450259400 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-1-1791450259400 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-1-1791450259400 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791450259400 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-1-1791450259400 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791450259400 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-1-1791450259400 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791450259400 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-1-1791450259400 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}是否
读取数据行
条件是否满足?
保留该行
丢弃该行
继续处理下一行
这张图展示了 WHERE 子句的过滤逻辑。Oracle 逐行读取数据，检查每行是否满足 WHERE 条件，满足的行被保留，不满足的被丢弃。
1.4 ORDER BY 子句：结果排序
ORDER BY 子句用于对查询结果进行排序。可以按一个或多个列排序，支持升序（ASC）和降序（DESC）。
#publish-mermaid-2-1791450259439{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-2-1791450259439 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-2-1791450259439 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-2-1791450259439 .error-icon{fill:#552222;}#publish-mermaid-2-1791450259439 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-2-1791450259439 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-2-1791450259439 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-2-1791450259439 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-2-1791450259439 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-2-1791450259439 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-2-1791450259439 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-2-1791450259439 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-2-1791450259439 .marker.cross{stroke:#333333;}#publish-mermaid-2-1791450259439 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-2-1791450259439 p{margin:0;}#publish-mermaid-2-1791450259439 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-2-1791450259439 .cluster-label text{fill:#333;}#publish-mermaid-2-1791450259439 .cluster-label span{color:#333;}#publish-mermaid-2-1791450259439 .cluster-label span p{background-color:transparent;}#publish-mermaid-2-1791450259439 .label text,#publish-mermaid-2-1791450259439 span{fill:#333;color:#333;}#publish-mermaid-2-1791450259439 .node rect,#publish-mermaid-2-1791450259439 .node circle,#publish-mermaid-2-1791450259439 .node ellipse,#publish-mermaid-2-1791450259439 .node polygon,#publish-mermaid-2-1791450259439 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-2-1791450259439 .rough-node .label text,#publish-mermaid-2-1791450259439 .node .label text,#publish-mermaid-2-1791450259439 .image-shape .label,#publish-mermaid-2-1791450259439 .icon-shape .label{text-anchor:middle;}#publish-mermaid-2-1791450259439 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-2-1791450259439 .rough-node .label,#publish-mermaid-2-1791450259439 .node .label,#publish-mermaid-2-1791450259439 .image-shape .label,#publish-mermaid-2-1791450259439 .icon-shape .label{text-align:center;}#publish-mermaid-2-1791450259439 .node.clickable{cursor:pointer;}#publish-mermaid-2-1791450259439 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-2-1791450259439 .arrowheadPath{fill:#333333;}#publish-mermaid-2-1791450259439 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-2-1791450259439 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-2-1791450259439 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-2-1791450259439 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-2-1791450259439 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-2-1791450259439 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-2-1791450259439 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-2-1791450259439 .cluster text{fill:#333;}#publish-mermaid-2-1791450259439 .cluster span{color:#333;}#publish-mermaid-2-1791450259439 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-2-1791450259439 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-2-1791450259439 rect.text{fill:none;stroke-width:0;}#publish-mermaid-2-1791450259439 .icon-shape,#publish-mermaid-2-1791450259439 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-2-1791450259439 .icon-shape p,#publish-mermaid-2-1791450259439 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-2-1791450259439 .icon-shape .label rect,#publish-mermaid-2-1791450259439 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-2-1791450259439 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-2-1791450259439 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-2-1791450259439 .node .neo-node{stroke:#9370DB;}#publish-mermaid-2-1791450259439 [data-look=\”neo\”].node rect,#publish-mermaid-2-1791450259439 [data-look=\”neo\”].cluster rect,#publish-mermaid-2-1791450259439 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791450259439 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-2-1791450259439 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-2-1791450259439 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791450259439 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-2-1791450259439 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791450259439 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-2-1791450259439 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791450259439 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-2-1791450259439 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}
获取查询结果集
解析 ORDER BY 子句
确定排序列
应用排序算法
生成排序后的结果
这张图展示了 ORDER BY 子句的执行流程。Oracle 首先解析 ORDER BY 子句，确定要排序的列，然后应用排序算法，最后生成排序后的结果集。
1.5 GROUP BY 与 HAVING 子句：数据分组
GROUP BY 子句用于将数据分组，HAVING 子句用于过滤分组后的结果。这两个子句通常与聚合函数一起使用。
#publish-mermaid-3-1791450259473{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#publish-mermaid-3-1791450259473 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#publish-mermaid-3-1791450259473 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#publish-mermaid-3-1791450259473 .error-icon{fill:#552222;}#publish-mermaid-3-1791450259473 .error-text{fill:#552222;stroke:#552222;}#publish-mermaid-3-1791450259473 .edge-thickness-normal{stroke-width:1px;}#publish-mermaid-3-1791450259473 .edge-thickness-thick{stroke-width:3.5px;}#publish-mermaid-3-1791450259473 .edge-pattern-solid{stroke-dasharray:0;}#publish-mermaid-3-1791450259473 .edge-thickness-invisible{stroke-width:0;fill:none;}#publish-mermaid-3-1791450259473 .edge-pattern-dashed{stroke-dasharray:3;}#publish-mermaid-3-1791450259473 .edge-pattern-dotted{stroke-dasharray:2;}#publish-mermaid-3-1791450259473 .marker{fill:#333333;stroke:#333333;}#publish-mermaid-3-1791450259473 .marker.cross{stroke:#333333;}#publish-mermaid-3-1791450259473 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#publish-mermaid-3-1791450259473 p{margin:0;}#publish-mermaid-3-1791450259473 .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#publish-mermaid-3-1791450259473 .cluster-label text{fill:#333;}#publish-mermaid-3-1791450259473 .cluster-label span{color:#333;}#publish-mermaid-3-1791450259473 .cluster-label span p{background-color:transparent;}#publish-mermaid-3-1791450259473 .label text,#publish-mermaid-3-1791450259473 span{fill:#333;color:#333;}#publish-mermaid-3-1791450259473 .node rect,#publish-mermaid-3-1791450259473 .node circle,#publish-mermaid-3-1791450259473 .node ellipse,#publish-mermaid-3-1791450259473 .node polygon,#publish-mermaid-3-1791450259473 .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#publish-mermaid-3-1791450259473 .rough-node .label text,#publish-mermaid-3-1791450259473 .node .label text,#publish-mermaid-3-1791450259473 .image-shape .label,#publish-mermaid-3-1791450259473 .icon-shape .label{text-anchor:middle;}#publish-mermaid-3-1791450259473 .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#publish-mermaid-3-1791450259473 .rough-node .label,#publish-mermaid-3-1791450259473 .node .label,#publish-mermaid-3-1791450259473 .image-shape .label,#publish-mermaid-3-1791450259473 .icon-shape .label{text-align:center;}#publish-mermaid-3-1791450259473 .node.clickable{cursor:pointer;}#publish-mermaid-3-1791450259473 .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#publish-mermaid-3-1791450259473 .arrowheadPath{fill:#333333;}#publish-mermaid-3-1791450259473 .edgePath .path{stroke:#333333;stroke-width:1px;}#publish-mermaid-3-1791450259473 .flowchart-link{stroke:#333333;fill:none;}#publish-mermaid-3-1791450259473 .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-3-1791450259473 .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#publish-mermaid-3-1791450259473 .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-3-1791450259473 .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#publish-mermaid-3-1791450259473 .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#publish-mermaid-3-1791450259473 .cluster text{fill:#333;}#publish-mermaid-3-1791450259473 .cluster span{color:#333;}#publish-mermaid-3-1791450259473 div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#publish-mermaid-3-1791450259473 .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#publish-mermaid-3-1791450259473 rect.text{fill:none;stroke-width:0;}#publish-mermaid-3-1791450259473 .icon-shape,#publish-mermaid-3-1791450259473 .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#publish-mermaid-3-1791450259473 .icon-shape p,#publish-mermaid-3-1791450259473 .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#publish-mermaid-3-1791450259473 .icon-shape .label rect,#publish-mermaid-3-1791450259473 .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#publish-mermaid-3-1791450259473 .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#publish-mermaid-3-1791450259473 .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#publish-mermaid-3-1791450259473 .node .neo-node{stroke:#9370DB;}#publish-mermaid-3-1791450259473 [data-look=\”neo\”].node rect,#publish-mermaid-3-1791450259473 [data-look=\”neo\”].cluster rect,#publish-mermaid-3-1791450259473 [data-look=\”neo\”].node polygon{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791450259473 [data-look=\”neo\”].swimlane.cluster rect{filter:none;}#publish-mermaid-3-1791450259473 [data-look=\”neo\”].node path{stroke:#9370DB;stroke-width:1px;}#publish-mermaid-3-1791450259473 [data-look=\”neo\”].node .outer-path{filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791450259473 [data-look=\”neo\”].node .neo-line path{stroke:#9370DB;filter:none;}#publish-mermaid-3-1791450259473 [data-look=\”neo\”].node circle{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791450259473 [data-look=\”neo\”].node circle .state-start{fill:#000000;}#publish-mermaid-3-1791450259473 [data-look=\”neo\”].icon-shape .icon{fill:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791450259473 [data-look=\”neo\”].icon-shape .icon-neo path{stroke:#9370DB;filter:drop-shadow(1px 2px 2px rgba(185, 185, 185, 1));}#publish-mermaid-3-1791450259473 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}是否
原始数据
按 GROUP BY 列分组
计算聚合函数值
HAVING 条件是否满足?
保留该组
丢弃该组
生成最终结果
这张图展示了 GROUP BY 和 HAVING 子句的工作流程。数据首先按指定列分组，然后计算聚合函数值，最后通过 HAVING 条件过滤分组。
2.1 多表连接查询
在 Oracle 中，经常需要从多个表中查询数据，这时就需要使用 JOIN 操作。常见的连接类型有 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN。
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
这段代码使用 INNER JOIN 连接 employees 和 departments 表，获取员工信息和对应的部门名称。
2.2 子查询与嵌套查询
子查询是在一个查询中嵌套另一个查询，可以用于 WHERE 子句、FROM 子句或 SELECT 子句中。
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
这段代码查找薪资高于平均薪资的员工。
2.3 聚合函数应用
Oracle 提供了多种聚合函数，如 COUNT、SUM、AVG、MAX、MIN 等，用于对数据进行统计计算。
SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
这段代码按部门分组，计算每个部门的员工数量和平均薪资。
3.1 销售数据分析案例
假设我们有一个销售数据表 sales，包含销售日期、产品 ID、销售数量和销售金额。我们可以使用 SQL 查询来分析销售数据。
SELECT
product_id,
SUM(quantity) AS total_quantity,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product_id
HAVING SUM(amount) > 10000
ORDER BY total_amount DESC;
这段代码计算了 2023 年每个产品的总销量、总金额和平均金额，只显示总金额超过 10000 的产品，并按总金额降序排列。
3.2 员工绩效统计案例
假设我们有一个员工表 employees 和一个绩效表 performance，我们可以查询员工的绩效统计信息。
SELECT
e.employee_id,
e.first_name || ' ' || e.last_name AS full_name,
p.performance_score,
p.rating
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
WHERE p.performance_date = '2023-12-31'
ORDER BY p.performance_score DESC;
这段代码查询了 2023 年底员工的绩效评分和评级，并按绩效评分降序排列。
3.3 库存管理查询案例
假设我们有一个库存表 inventory，包含产品 ID、库存数量和最低库存阈值。我们可以查询需要补货的产品。
SELECT
product_id,
product_name,
current_stock,
min_stock,
(min_stock – current_stock) AS stock_needed
FROM inventory
WHERE current_stock < min_stock
ORDER BY stock_needed DESC;
这段代码查询了当前库存低于最低库存阈值的产品，并计算需要补充的库存数量，按需要补充的数量降序排列。
通过以上案例，我们可以看到 Oracle SQL 查询、过滤、排序和分组的强大功能。掌握这些基础操作是进行复杂数据分析的前提。
