Oracle 数据库学习笔记
整理日期：2026-05-21 | Oracle 21c Express Edition
目录
- 第一章：Oracle 基础概念
- 第二章：Oracle 体系架构
- 第三章：用户与权限管理
- 第四章：SQL 基础与查询
- 第五章：表与数据类型
- 第六章：约束与索引
- 第七章：函数与表达式
- 第八章：事务与并发控制
- 第九章：视图与存储过程
- 第十章：Navicat 和 JDBC 连接
- 附录：常见问题与解决方案
第一章：Oracle 基础概念
1.1 什么是 Oracle 数据库
Oracle 是甲骨文公司（Oracle Corporation）开发的关系型数据库管理系统（RDBMS - Relational Database Management System）。它是世界上第一个基于 SQL 标准的商业级数据库产品，以其高可靠性、高性能和完善的安全机制著称，广泛应用于金融、电信、政府机构、大型企业等对数据安全性和稳定性要求极高的领域。
Oracle 不仅是一个存储数据的容器，还提供了完整的数据管理解决方案，包括数据备份与恢复、数据复制、集群支持、分区表、自动化管理等功能。相比 MySQL 等轻量级数据库，Oracle 更适合处理海量数据和高并发访问场景。
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
mindmap
root((Oracle数据库))
定位
企业级数据库管理系统
关系型数据库
支持SQL标准
核心能力
高可用性
水平扩展
数据安全
自动化管理
版本类型
Enterprise Edition
Standard Edition
Express Edition (XE免费版)
Personal Edition
应用领域
金融系统
电信运营商
政府部门
电子商务
物流系统
1.2 Oracle 与 MySQL 的核心区别
很多开发者从 MySQL 转向 Oracle 时会遇到一些概念上的差异。理解这些差异有助于更好地掌握 Oracle 的特性。
架构层面的差异是两者最根本的区别。MySQL 相对简单，每个数据库实例通常只包含一个数据库；而 Oracle 采用多租户架构（从 12c 版本开始），可以在一个容器数据库（CDB）中托管多个可插拔数据库（PDB），这大大提高了资源利用率和管理效率。
数据类型方面，Oracle 提供了更丰富的数据类型选择。例如 CLOB 可以存储高达 128TB 的文本数据，NUMBER 类型可以精确控制小数位数，DATE 类型在 Oracle 中包含时分秒而 MySQL 早期版本是分开的。
密码和命名规则也有所不同。Oracle 的用户名和密码默认是区分大小写的，而 MySQL 在 Windows 上不区分大小写（在 Linux 上区分）。Oracle 的表名、列名等标识符最长支持 30 个字符，而 MySQL 支持 64 个字符。
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
flowchart LR
subgraph 对比维度
A1[架构] --> B1[Oracle: 多租户 CDB/PDB]
A1 --> C1[MySQL: 单实例单数据库]
A2[授权] --> B2[商业授权/免费XE]
A2 --> C2[开源免费]
A3[存储] --> B3[表空间管理]
A2 --> C3[数据库直接存储]
A4[语法] --> B4[ANSI SQL扩展]
A4 --> C4[简化语法]
end
style B1 fill:#1976d2,color:#fff
style C1 fill:#388e3c,color:#fff
style B2 fill:#7b1fa2,color:#fff
style C2 fill:#388e3c,color:#fff
style B3 fill:#1976d2,color:#fff
style C3 fill:#388e3c,color:#fff
style B4 fill:#1976d2,color:#fff
style C4 fill:#388e3c,color:#fff
1.3 Oracle 版本选择
Oracle 有多个版本可供选择，不同版本适用于不同规模的部署：
| 版本 | 说明 | 适用场景 | 限制 |
|---|---|---|---|
| Enterprise Edition (EE) | 功能最完整 | 大型企业核心系统 | 商业授权，按核心数收费 |
| Standard Edition (SE) | 基础功能 | 中小企业 | 功能少于 EE，价格较低 |
| Express Edition (XE) | 免费版 | 开发测试学习 | CPU/内存/存储有限制 |
| Personal Edition | 单用户版 | 个人开发者 | 仅支持单用户 |
Oracle 21c XE 是当前最新的免费版本，支持最多 2 个 CPU 核心、2GB 内存和 12GB 用户数据，对于学习和小型项目来说已经足够使用。
第二章：Oracle 体系架构
2.1 Oracle 服务器架构概述
理解 Oracle 的体系架构对于进行性能调优、故障排查和日常维护至关重要。Oracle 数据库的架构可以分为三个主要层次：客户端层、实例层和存储层。
客户端层包括所有连接到 Oracle 服务器的应用和工具，如 JDBC 应用、ODBC 应用、SQL Plus 命令行工具、Navicat 等图形化管理工具。客户端通过 Oracle 特有的网络协议（通常是通过 TNS 监听器）与服务器通信。
实例层是 Oracle 服务器的运行时环境，由内存结构（SGA、PGA）和后台进程组成。当 Oracle 服务器启动时，会创建一个实例，实例加载到内存中并启动一系列后台进程来管理数据库操作。
存储层负责持久化存储所有数据，包括数据文件、控制文件、重做日志文件等。存储层是数据的最终载体，即使服务器重启，数据也会安全地保存在磁盘上。
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
flowchart TB
subgraph ClientLayer["客户端层"]
A1[JDBC 应用]
A2[Navicat 工具]
A3[SQL Plus]
A4[ODBC 应用]
end
subgraph InstanceLayer["实例层（内存+进程）"]
subgraph Memory["SGA 共享池"]
B1[数据缓冲区]
B2[共享池]
B3[重做日志缓冲区]
B4[大型池]
end
subgraph Processes["后台进程"]
C1[PMON 进程监视器]
C2[SMON 系统监视器]
C3[DBWn 数据库写入]
C4[LGWR 日志写入]
C5[CKPT 检查点]
C6[TNS Listener 监听器]
end
end
subgraph StorageLayer["存储层"]
D1[数据文件 .dbf]
D2[控制文件 .ctl]
D3[重做日志 .log]
D4[表空间]
end
A1 --> F[TNS Listener :1521]
A2 --> F
A3 --> F
A4 --> F
F --> C6
C6 --> B2
B1 <--> D1
C4 --> D3
D3 --> D1
style ClientLayer fill:#bbdefb,color:#0d47a1
style InstanceLayer fill:#ffe0b2,color:#e65100
style StorageLayer fill:#c8e6c9,color:#1b5e20
style Memory fill:#ffecb3,color:#795548
style Processes fill:#ffcdd2,color:#b71c1c
2.2 内存结构详解
Oracle 的内存结构主要分为 SGA（System Global Area）和 PGA（Program Global Area）两部分。
SGA 是共享内存区域，被所有服务器进程和后台进程共享访问。SGA 包含以下主要组件：
-
数据缓冲区（Database Buffer Cache）：缓存从数据文件读取的数据块。当用户查询数据时，Oracle 首先在数据缓冲区中查找，如果不存在才从磁盘读取。这大大减少了磁盘 I/O 操作，提升了查询性能。
-
共享池（Shared Pool）：缓存已解析的 SQL 语句和 PL/SQL 代码。当相同的 SQL 语句被多次执行时，Oracle 可以直接使用已缓存的执行计划，避免了重复解析的开销。
-
重做日志缓冲区（Redo Log Buffer）：记录所有对数据库的修改操作。这些记录会写入重做日志文件，用于数据库恢复。
-
大型池（Large Pool）：为大型内存操作提供空间，如 RMAN 备份、并行查询等。
PGA 是进程私有内存区域，每个服务器进程和后台进程都有自己独立的 PGA，用于存储会话信息、排序区域、哈希区域等进程相关的数据。
2.3 后台进程功能
Oracle 的后台进程负责协调各种数据库操作，理解它们的功能有助于故障排查：
| 进程 | 全称 | 功能说明 |
|---|---|---|
| PMON | Process Monitor | 进程监视器，负责清理崩溃的进程，释放资源 |
| SMON | System Monitor | 系统监视器，执行实例恢复，清理临时段 |
| DBWn | Database Writer | 数据库写入，将脏数据写入数据文件 |
| LGWR | Log Writer | 日志写入，将重做日志缓冲区写入磁盘 |
| CKPT | Checkpoint | 检查点进程，触发 DBWn 写入数据 |
| TNS Listener | Oracle Net Listener | 监听客户端连接请求，建立网络连接 |
2.4 表空间与存储层次
Oracle 的存储结构采用分层管理，从宏观到微观依次为：数据库、表空间、段、区、块。
块（Block） 是 Oracle 数据库最小的 I/O 单位，默认大小为 8KB。数据块直接与操作系统的块对应，确保每次读写都是完整的块操作。
区（Extent） 是由多个连续数据块组成的逻辑单位。当表或索引创建时，Oracle 至少分配一个区。区随着数据增长而扩展。
段（Segment） 是由多个区组成的逻辑对象，代表一个数据库对象（如表、索引）的全部空间分配。表有表段，索引有索引段。
表空间（TABLESPACE） 是数据库的逻辑存储容器，由一个或多个数据文件组成。Oracle 数据库至少包含 SYSTEM 和 SYSAUX 表空间。
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
flowchart TB
A[数据库 Database] --> B[表空间 TABLESPACE]
B --> C1[SYSTEM 表空间<br/>数据字典]
B --> C2[SYSAUX 表空间<br/>系统辅助]
B --> C3[UNDO 表空间<br/>事务回滚]
B --> C4[TEMP 表空间<br/>临时数据]
B --> C5[USERS 表空间<br/>用户数据]
C5 --> D1[表段 Table Segment]
D1 --> E1[区 Extent 1]
D1 --> E2[区 Extent 2]
E1 --> F1[数据块 Block 1]
E1 --> F2[数据块 Block 2]
E1 --> F3[数据块 Block 3]
style A fill:#1565c0,color:#fff
style B fill:#1976d2,color:#fff
style C1 fill:#78909c,color:#fff
style C2 fill:#78909c,color:#fff
style C3 fill:#78909c,color:#fff
style C4 fill:#78909c,color:#fff
style C5 fill:#388e3c,color:#fff
style D1 fill:#4caf50,color:#fff
style E1 fill:#66bb6a,color:#fff
style E2 fill:#66bb6a,color:#fff
style F1 fill:#81c784,color:#fff
style F2 fill:#81c784,color:#fff
style F3 fill:#81c784,color:#fff
2.5 可插拔数据库（PDB）架构
Oracle 12c 及以后版本引入了多租户架构，允许在一个 CDB（Container Database）中托管多个 PDB（Pluggable Database）。这种架构特别适合云计算环境，可以更高效地利用资源。
CDB 是容器数据库，相当于一个"超级数据库"，包含所有 PDB 共享的系统数据和元数据。CDB 本身包含一个根容器（ROOT）和一个种子 PDB（PDB$SEED）。
PDB 是可插拔数据库，每个 PDB 相当于一个独立的传统数据库。PDB 有自己的用户、表、应用逻辑，但从系统层面共享 CDB 的资源和管理框架。
为什么要使用 PDB？多租户架构的主要优势包括：硬件资源共享、简化管理、降低授权成本、快速部署新数据库、灵活的迁移能力。
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
flowchart LR
subgraph CDB["容器数据库 CDB"]
subgraph ROOT["CDB$ROOT 根容器"]
R1[SYSTEM 用户]
R2[SYS 用户]
R3[公共用户]
end
subgraph SEED["PDB$SEED 种子"]
S1[模板定义]
S2[只读]
end
end
subgraph PDB1["可插拔数据库 XEPDB1"]
P1[SYSTEM 用户]
P2[MYAPP 用户]
P3[业务数据表]
end
subgraph PDB2["可插拔数据库 PDB2"]
P4[SYSTEM 用户]
P5[APP2 用户]
P6[业务数据表]
end
ROOT --- PDB1
ROOT --- PDB2
SEED -.-> PDB1
SEED -.-> PDB2
style CDB fill:#fff9c4,color:#f57f17
style ROOT fill:#ffe082,color:#e65100
style SEED fill:#b3e5fc,color:#01579b
style PDB1 fill:#c8e6c9,color:#1b5e20
style PDB2 fill:#c8e6c9,color:#1b5e20
第三章：用户与权限管理
3.1 Oracle 用户基础
Oracle 中的用户（User）与模式（Schema）是一一对应的关系，这是与 MySQL 显著不同的概念。当你创建一个用户时，Oracle 会自动创建同名模式，用户创建的所有数据库对象（表、视图、索引等）都存储在这个模式中。
用户用于身份认证，确认谁可以连接到数据库；模式用于对象组织，存放用户拥有的所有对象。这种设计使得权限管理更加清晰和安全。
在 Oracle 中，连接数据库需要两步：首先通过用户名和密码进行身份验证，然后根据该用户拥有的权限访问相应的模式对象。
3.2 常用系统用户
Oracle 数据库安装后会创建一些默认的系统用户，了解它们的用途对于数据库管理非常重要。
SYS 用户是 Oracle 数据库中权限最高的超级管理员账户，拥有 DBA 角色以及所有系统权限。SYS 用户主要用于执行数据库维护操作、创建数据字典、安装选项等系统级任务。在生产环境中，通常不建议使用 SYS 用户进行日常业务操作。
SYSTEM 用户是数据库管理员账户，权限仅次于 SYS，但更适合执行管理任务。SYSTEM 可以创建和修改表、视图，为其他用户授权等。由于权限较高，日常开发仍建议使用自己创建的业务用户。
PUBLIC 用户是一个特殊的"组"用户，代表所有数据库用户。将权限授予 PUBLIC 意味着所有用户都可以使用该权限。
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
flowchart TB
A[Oracle 用户分类] --> B[管理员用户]
A --> C[业务用户]
A --> D[系统用户]
B --> B1[SYS<br/>超级管理员]
B1 --> B1a[所有权限]
B1a --> B1b[数据字典所有者]
B1a --> B1c[启动关闭数据库]
B --> B2[SYSTEM<br/>系统管理员]
B2 --> B2a[大部分管理权限]
B2a --> B2b[创建表视图]
B2a --> B2c[用户管理]
C --> C1[MYAPP<br/>业务用户]
C1 --> C1a[连接权限]
C1a --> C1b[业务表空间配额]
C1a --> C1c[业务数据操作]
D --> D1[ DBSNMP<br/>监控用户]
D --> D2[ XDB<br/>XML数据库]
D1 --> D1a[性能监控]
D2 --> D2a[XML处理]
style B fill:#ef5350,color:#fff
style C fill:#66bb6a,color:#fff
style D fill:#9e9e9e,color:#fff
style B1 fill:#ef9a9a,color:#b71c1c
style B2 fill:#ef9a9a,color:#b71c1c
style C1 fill:#a5d6a7,color:#1b5e20
style D1 fill:#bdbdbd,color:#424242
style D2 fill:#bdbdbd,color:#424242
style B1a fill:#ffcdd2,color:#c62828
style B1b fill:#ffcdd2,color:#c62828
style B1c fill:#ffcdd2,color:#c62828
style B2a fill:#ffcdd2,color:#c62828
style B2b fill:#ffcdd2,color:#c62828
style B2c fill:#ffcdd2,color:#c62828
style C1a fill:#c8e6c9,color:#2e7d32
style C1b fill:#c8e6c9,color:#2e7d32
style C1c fill:#c8e6c9,color:#2e7d32
style D1a fill:#e0e0e0,color:#424242
style D2a fill:#e0e0e0,color:#424242
3.3 权限类型详解
Oracle 的权限系统分为两大类：系统权限和对象权限。
系统权限允许用户执行特定的数据库操作，如创建会话、创建表、创建视图等。系统权限通常以 CREATE、DROP、ALTER 等动词开头，授予用户执行某类操作的权力。
常见的系统权限包括：CREATE SESSION（连接数据库）、CREATE TABLE（创建表）、CREATE VIEW（创建视图）、CREATE PROCEDURE（创建存储过程）、UNLIMITED TABLESPACE（无限使用表空间）等。
对象权限允许用户对特定对象（表、视图、序列等）执行特定操作。对象权限通常以 SELECT、INSERT、UPDATE、DELETE 等动词开头，授予用户操作某个具体对象的能力。
常见的对象权限包括：SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）、REFERENCES（创建外键）、EXECUTE（执行存储过程）等。
3.4 角色及其作用
角色（Role）是权限的逻辑集合，方便批量授权和管理。Oracle 预定义了一些常用角色：
| 角色 | 包含的权限 | 使用场景 |
|---|---|---|
CONNECT |
CREATE SESSION | 仅需要连接的用户 |
RESOURCE |
CREATE TABLE, PROCEDURE, SEQUENCE 等 | 开发者用户 |
DBA |
大部分系统权限 | 数据库管理员 |
使用角色的好处是：当用户职责变更时，只需调整角色分配，无需逐个修改权限。
3.5 创建用户与授权实战
下面是在 Oracle 中创建业务用户的完整流程：
-- 第一步：使用 SYSTEM 或 SYS 用户登录后，创建新用户
-- 密码规则：必须以字母开头，可以包含字母、数字、#、$、_
CREATE USER myapp IDENTIFIED BY "Pass123";
-- 第二步：授予连接权限，允许用户登录数据库
GRANT CONNECT TO myapp;
-- 第三步：授予资源权限，允许用户创建表、序列、存储过程等
GRANT RESOURCE TO myapp;
-- 第四步：授予表空间无限使用权限（重要！）
-- 如果不授权，会出现 ORA-01950 错误
GRANT UNLIMITED TABLESPACE TO myapp;
-- 第五步：如果需要管理员权限，可以授予 DBA 角色
GRANT DBA TO myapp;
-- 第六步：验证用户创建结果
SELECT username,
account_status, -- OPEN 表示可用，LOCKED 表示被锁定
created, -- 创建时间
default_tablespace -- 默认表空间
FROM dba_users
WHERE username = 'MYAPP';
-- 如果用户被锁定，需要解锁
ALTER USER myapp ACCOUNT UNLOCK;
-- 修改用户密码
ALTER USER myapp IDENTIFIED BY "NewPass456";
3.6 权限传递与继承
当用户 A 将权限授予用户 B 时，这个授权被称为" admin 选项"；如果用户 A 将对象权限授予用户 B，用户 B 可以进一步将这个权限授予其他用户，这被称为"权限传递"。
-- 带 admin 选项授权，用户可以将被授予的权限再授予其他人
GRANT CONNECT TO myapp WITH ADMIN OPTION;
-- 带 grant option 授权，用户可以将对象权限授予他人
GRANT SELECT ON myapp.employees TO other_user WITH GRANT OPTION;
撤销权限时需要特别注意：如果用户 A 授予了用户 B 权限，然后用户 B 又授予用户 C 权限，当用户 A 撤销用户 B 的权限时，用户 C 的权限也会被级联撤销。
第四章：SQL 基础与查询
4.1 SQL 语句分类
SQL（Structured Query Language）是访问和操作数据库的标准语言。在 Oracle 中，SQL 语句可以分为以下几类：
DDL（Data Definition Language） 数据定义语言用于定义和管理数据库对象，包括 CREATE、ALTER、DROP、TRUNCATE 等语句。这些语句会自动提交事务，创建、修改或删除数据库对象。
DML（Data Manipulation Language） 数据操作语言用于操作数据库中的数据，包括 SELECT、INSERT、UPDATE、DELETE 等语句。DML 语句需要显式提交（COMMIT）才能永久保存更改。
TCL（Transaction Control Language） 事务控制语言用于管理事务，包括 COMMIT、ROLLBACK、SAVEPOINT 等语句，确保数据一致性和完整性。
DCL（Data Control Language） 数据控制语言用于控制权限，包括 GRANT（授权）和 REVOKE（撤销）语句。
4.2 SELECT 查询详解
SELECT 是最常用的 SQL 语句，用于从数据库中查询数据。其执行顺序如下：
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
flowchart LR
A[SELECT] --> B[FROM]
B --> C[WHERE]
C --> D[GROUP BY]
D --> E[HAVING]
E --> F[ORDER BY]
F --> G[结果输出]
style A fill:#1565c0,color:#fff
style B fill:#1976d2,color:#fff
style C fill:#388e3c,color:#fff
style D fill:#f57c00,color:#fff
style E fill:#7b1fa2,color:#fff
style F fill:#c62828,color:#fff
style G fill:#2e7d32,color:#fff
Oracle 中 SELECT 的完整语法顺序是：
SELECT column1, column2, ... -- 1. 选择列
FROM table_name -- 2. 指定表
WHERE condition -- 3. 过滤条件
GROUP BY column -- 4. 分组
HAVING group_condition -- 5. 分组后过滤
ORDER BY column ASC/DESC -- 6. 排序
4.3 条件查询 WHERE
WHERE 子句用于过滤满足条件的记录，是 SELECT 语句中最常用的过滤机制。
-- 基本比较运算
SELECT *
FROM employees
WHERE salary > 5000; -- 大于
SELECT *
FROM employees
WHERE department_id = 10; -- 等于
SELECT *
FROM employees
WHERE hire_date < '2020-01-01'; -- 日期比较
-- 范围查询 BETWEEN...AND...
SELECT *
FROM employees
WHERE salary BETWEEN 3000 AND 8000; -- 闭区间 [3000, 8000]
-- 列表查询 IN
SELECT *
FROM employees
WHERE department_id IN (10, 20, 30); -- department_id 在列表中
-- 模式匹配 LIKE
SELECT *
FROM employees
WHERE last_name LIKE 'S%'; -- S 开头的
SELECT *
FROM employees
WHERE email LIKE '%@example.com'; -- 以 @example.com 结尾
SELECT *
FROM employees
WHERE name LIKE 'J_O%' ESCAPE 'O'; -- 查找 J_O（O 是转义字符）
-- 组合条件
SELECT *
FROM employees
WHERE salary > 5000
AND department_id = 10
AND hire_date >= '2019-01-01';
SELECT *
FROM employees
WHERE department_id = 10
OR department_id = 20;
-- NULL 处理（注意：不能用 = NULL，要用 IS NULL）
SELECT *
FROM employees
WHERE manager_id IS NULL; -- 没有上级
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL; -- 有提成
4.4 分组与聚合
GROUP BY 子句将结果按一个或多个列分组，常与聚合函数一起使用来统计每组数据。
-- 基本聚合统计
SELECT COUNT(*) AS total_employees, -- 员工总数
SUM(salary) AS total_salary, -- 工资总和
AVG(salary) AS avg_salary, -- 平均工资
MAX(salary) AS max_salary, -- 最高工资
MIN(salary) AS min_salary -- 最低工资
FROM employees;
-- 按部门分组统计
SELECT department_id,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary,
SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;
-- HAVING 过滤分组（WHERE 过滤行，HAVING 过滤分组）
SELECT department_id,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 6000
ORDER BY avg_salary DESC;
-- 统计包含 NULL 的处理
SELECT COUNT(commission_pct) -- 只计算非 NULL 值
FROM employees;
SELECT COUNT(*) - COUNT(manager_id) -- 计算 NULL 的数量
FROM employees;
4.5 多表连接查询
当数据分散在多个表中时，需要使用 JOIN 操作将它们组合起来。
-- 内连接 INNER JOIN：只返回两个表都有的记录
SELECT e.employee_id,
e.first_name,
e.last_name,
d.department_name,
l.city
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
WHERE e.salary > 5000;
-- 左外连接 LEFT JOIN：返回左表所有记录，右表没有匹配的显示 NULL
SELECT e.employee_id,
e.first_name,
d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- 右外连接 RIGHT JOIN：返回右表所有记录，左表没有匹配的显示 NULL
SELECT e.employee_id,
e.first_name,
d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
-- 完全外连接 FULL JOIN：两个表的所有记录都返回
SELECT e.employee_id,
d.department_id,
d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id;
-- 多表连接
SELECT e.first_name,
e.last_name,
d.department_name,
l.city,
c.country_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id = c.country_id;
4.6 子查询
子查询是嵌套在另一个查询中的查询，用于在单个语句中完成复杂的数据检索。
-- 单行子查询：返回单行单列
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 多行子查询：返回多行
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE salary > 7000);
-- 关联子查询：子查询引用外层查询的列
SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(s.max_salary)
FROM employees m
WHERE e.department_id = m.department_id);
-- EXISTS 检查是否存在
SELECT d.department_name
FROM departments d
WHERE EXISTS (SELECT 1
FROM employees e
WHERE e.department_id = d.department_id
AND e.salary > 10000);
-- 创建表时使用子查询
CREATE TABLE high_salary_employees AS
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 8000;
第五章：表与数据类型
5.1 Oracle 数据类型概述
Oracle 提供了丰富的数据类型来存储不同种类的数据，选择合适的数据类型可以提高存储效率和查询性能。
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
flowchart LR
A[Oracle 数据类型] --> B[字符类型]
A --> C[数值类型]
A --> D[日期时间类型]
A --> E[大对象类型]
A --> F[其他类型]
B --> B1[VARCHAR2<br/>变长字符串]
B --> B2[CHAR<br/>定长字符串]
B --> B3[NCHAR<br/>Unicode定长]
B --> B4[NVARCHAR2<br/>Unicode变长]
C --> C1[NUMBER<br/>数值类型]
C --> C2[BINARY_FLOAT<br/>32位浮点]
C --> C3[BINARY_DOUBLE<br/>64位浮点]
D --> D1[DATE<br/>日期时间]
D --> D2[TIMESTAMP<br/>时间戳]
D --> D3[INTERVAL<br/>时间段]
E --> E1[CLOB<br/>大文本]
E --> E2[BLOB<br/>二进制]
E --> E3[BFILE<br/>外部文件]
style A fill:#1565c0,color:#fff
style B fill:#1976d2,color:#fff
style C fill:#388e3c,color:#fff
style D fill:#f57c00,color:#fff
style E fill:#7b1fa2,color:#fff
style F fill:#78909c,color:#fff
style B1 fill:#64b5f6,color:#0d47a1
style B2 fill:#64b5f6,color:#0d47a1
style B3 fill:#64b5f6,color:#0d47a1
style B4 fill:#64b5f6,color:#0d47a1
style C1 fill:#81c784,color:#1b5e20
style C2 fill:#81c784,color:#1b5e20
style C3 fill:#81c784,color:#1b5e20
style D1 fill:#ffb74d,color:#e65100
style D2 fill:#ffb74d,color:#e65100
style D3 fill:#ffb74d,color:#e65100
style E1 fill:#ce93d8,color:#4a148c
style E2 fill:#ce93d8,color:#4a148c
style E3 fill:#ce93d8,color:#4a148c
5.2 字符类型详解
VARCHAR2 是 Oracle 中最常用的可变长度字符串类型。它只存储实际输入的字符，不会浪费空间。需要指定最大长度（字节或字符），最大支持 32767 字节。
CHAR 是定长字符串类型。当存储的字符串长度不足时，会在尾部填充空格至指定长度。适合存储长度固定的数据如身份证号、邮政编码。
VARCHAR2 vs CHAR 的选择：对于长度不确定的文本，使用 VARCHAR2；对于长度固定的字符串（如产品代码、状态码），使用 CHAR。
-- VARCHAR2 示例
VARCHAR2(50) -- 最大50字节
VARCHAR2(50 CHAR) -- 最大50字符（推荐用于多语言）
-- CHAR 示例
CHAR(10) -- 定长10字节，不足补空格
-- 存储中文注意事项
-- Oracle 的中文字符默认占3个字节
-- 如果用 VARCHAR2(10)，只能存3-4个中文
-- 建议用 CHAR 或 VARCHAR2(10 CHAR)
5.3 数值类型详解
NUMBER 是 Oracle 中最常用的数值类型，可以存储整数和小数。语法为 NUMBER(p, s)，其中 p 是精度（总位数），s 是刻度（小数位数）。
NUMBER -- 精度38位，刻度0
NUMBER(10) -- 整数，最大10位
NUMBER(10, 2) -- 小数，总长10位，2位小数
NUMBER(10, -2) -- 整数部分8位，小数部分四舍五入到百位
-- 示例
123.4567 NUMBER(10, 2) --> 存储为 123.46
1234567890 NUMBER(10) --> 存储为 1234567890
0.1234567 NUMBER(5, 7) --> 存储为 0.1234567
5.4 日期时间类型
DATE 类型存储日期和时间，范围从公元前 4712 年到公元后 9999 年。DATE 类型固定占用 7 个字节，分别存储世纪、年、月、日、时、分、秒。
TIMESTAMP 是 DATE 的扩展，精确到小数秒（最高9位）。适用于需要精确计时的场景。
INTERVAL 用于表示时间段，如"3年2个月"。
-- DATE 显示格式默认是 DD-MON-RR
-- 需要使用 TO_CHAR 和 TO_DATE 进行格式转换
SELECT SYSDATE, -- 当前日期时间
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date,
TO_CHAR(SYSDATE, 'DAY') AS weekday_name,
TO_CHAR(SYSDATE, 'Q') AS quarter, -- 季度
TO_CHAR(SYSDATE, 'WW') AS week_of_year -- 一年中的第几周
FROM dual;
-- TIMESTAMP 示例
SELECT SYSTIMESTAMP, -- 带时区的时间戳
CURRENT_TIMESTAMP, -- 会话时区的当前时间
TO_CHAR(TIMESTAMP '2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual;
-- 日期计算
SELECT SYSDATE + 1 AS tomorrow, -- 加1天
SYSDATE - 1 AS yesterday, -- 减1天
SYSDATE + 7 AS next_week, -- 加7天
SYSDATE + 1/24 AS one_hour_later, -- 加1小时
SYSDATE - 30/1440 AS thirty_min_ago -- 减30分钟
FROM dual;
-- 计算日期间隔
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) AS months_worked,
ADD_MONTHS(SYSDATE, 6) AS six_months_later,
NEXT_DAY(SYSDATE, 'FRIDAY') AS next_friday,
TRUNC(SYSDATE, 'MM') AS first_day_of_month,
TRUNC(SYSDATE, 'YYYY') AS first_day_of_year
FROM employees;
5.5 创建表的完整示例
-- 创建用户表
CREATE TABLE myapp.users (
user_id NUMBER(10) PRIMARY KEY, -- 主键
username VARCHAR2(50) NOT NULL UNIQUE, -- 用户名，非空唯一
email VARCHAR2(100), -- 邮箱
password VARCHAR2(100) NOT NULL, -- 密码，非空
phone VARCHAR2(20), -- 电话
created_at DATE DEFAULT SYSDATE, -- 创建时间，默认当前时间
updated_at DATE, -- 更新时间
status NUMBER(1) DEFAULT 1, -- 状态，1启用0禁用
CONSTRAINT users_status_chk CHECK (status IN (0, 1))
);
-- 创建部门表
CREATE TABLE myapp.departments (
dept_id NUMBER(10) PRIMARY KEY,
dept_name VARCHAR2(100) NOT NULL,
dept_code VARCHAR2(20) UNIQUE,
manager_id NUMBER(10),
created_at DATE DEFAULT SYSDATE,
CONSTRAINT dept_name_len_chk CHECK (LENGTH(dept_name) >= 2)
);
-- 创建员工表（含外键）
CREATE TABLE myapp.employees (
emp_id NUMBER(10) PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL,
emp_no VARCHAR2(20) UNIQUE,
email VARCHAR2(100),
phone VARCHAR2(20),
hire_date DATE DEFAULT SYSDATE,
salary NUMBER(10, 2) CHECK (salary >= 0),
commission NUMBER(10, 2),
dept_id NUMBER(10),
created_at DATE DEFAULT SYSDATE,
updated_at DATE,
CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
CONSTRAINT emp_salary_chk CHECK (salary >= 0 AND salary <= 100000)
);
-- 创建序列（用于生成自增ID）
CREATE SEQUENCE myapp.users_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE myapp.departments_seq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE myapp.employees_seq START WITH 1 INCREMENT BY 1;
-- 查看表结构
DESC myapp.users;
DESC myapp.departments;
DESC myapp.employees;
-- 查看当前用户的所有表
SELECT table_name FROM user_tables ORDER BY table_name;
5.6 增删改查（CRUD）操作
-- ========== INSERT 插入 ==========
-- 单行插入（指定列）
INSERT INTO users (user_id, username, email, password, status)
VALUES (1, 'tom', 'tom@example.com', 'pass123', 1);
-- 单行插入（不指定列，使用序列）
INSERT INTO users (username, email, password)
VALUES ('jerry', 'jerry@example.com', 'pass456');
-- 使用序列插入
INSERT INTO users (user_id, username, email, password)
VALUES (users_seq.NEXTVAL, 'alice', 'alice@example.com', 'pass789');
-- 多行插入（INSERT ALL）
INSERT ALL
INTO users (user_id, username, email, password) VALUES (users_seq.NEXTVAL, 'user1', 'user1@example.com', 'pass1')
INTO users (user_id, username, email, password) VALUES (users_seq.NEXTVAL, 'user2', 'user2@example.com', 'pass2')
INTO users (user_id, username, email, password) VALUES (users_seq.NEXTVAL, 'user3', 'user3@example.com', 'pass3')
SELECT 1 FROM dual;
-- 从其他表复制数据
INSERT INTO users (username, email, password)
SELECT username, email, 'default123'
FROM old_users
WHERE status = 1;
-- ========== UPDATE 更新 ==========
-- 更新单列
UPDATE users
SET email = 'new_tom@example.com'
WHERE user_id = 1;
-- 更新多列
UPDATE users
SET email = 'updated@example.com',
phone = '13800138000',
updated_at = SYSDATE
WHERE user_id = 1;
-- 使用表达式更新
UPDATE employees
SET salary = salary * 1.1
WHERE dept_id = 10;
-- 使用子查询更新
UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees)
WHERE salary < (SELECT AVG(salary) FROM employees);
-- ========== DELETE 删除 ==========
-- 删除单行
DELETE FROM users
WHERE user_id = 5;
-- 删除多行
DELETE FROM users
WHERE status = 0 AND created_at < SYSDATE - 365;
-- 删除所有数据（高危操作）
DELETE FROM users; -- 可以回滚
-- ========== 事务控制 ==========
COMMIT; -- 提交事务，永久保存
ROLLBACK; -- 回滚事务，撤销更改
SAVEPOINT sp1; -- 创建保存点
ROLLBACK TO sp1; -- 回滚到保存点
第六章：约束与索引
6.1 约束类型详解
约束（Constraint）用于确保数据的完整性和一致性。Oracle 支持五种类型的约束：
| 约束类型 | 说明 | 语法关键字 |
|---|---|---|
| NOT NULL | 列值不能为空 | NOT NULL |
| UNIQUE | 列值唯一，允许NULL | UNIQUE |
| PRIMARY KEY | 主键，唯一且非空 | PRIMARY KEY |
| FOREIGN KEY | 外键，引用其他表 | REFERENCES |
| CHECK | 自定义条件检查 | CHECK |
NOT NULL 约束是最简单的约束，确保列必须包含值。在创建表时可以单独指定，也可以在列定义后追加。
UNIQUE 约束确保列或列组合的值在整个表中唯一。允许有多个 NULL 值（因为 NULL != NULL）。
PRIMARY KEY 主键是 UNIQUE 和 NOT NULL 的组合，每个表只能有一个主键。主键可以由多个列组成（复合主键）。
FOREIGN KEY 外键建立两个表之间的引用关系，确保引用完整性。父表（被引用的表）的被引用列必须是主键或唯一键。
CHECK 约束允许定义列值必须满足的条件，可以包含表达式、函数等。
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
flowchart TB
A[约束类型] --> B[NOT NULL<br/>非空约束]
A --> C[UNIQUE<br/>唯一约束]
A --> D[PRIMARY KEY<br/>主键]
A --> E[FOREIGN KEY<br/>外键]
A --> F[CHECK<br/>检查约束]
B --> B1[强制列必须有值]
B1 --> B2[允许创建索引]
C --> C1[列值不能重复]
C1 --> C2[允许多个NULL]
D --> D1[唯一+非空组合]
D1 --> D2[每表仅一个]
E --> E1[表间引用关系]
E1 --> E2[级联更新/删除]
F --> F1[自定义条件]
F1 --> F2[表达式/函数]
style A fill:#1565c0,color:#fff
style B fill:#388e3c,color:#fff
style C fill:#66bb6a,color:#1b5e20
style D fill:#f57c00,color:#fff
style E fill:#e64a19,color:#fff
style F fill:#7b1fa2,color:#fff
style B1 fill:#a5d6a7,color:#1b5e20
style B2 fill:#a5d6a7,color:#1b5e20
style C1 fill:#c8e6c9,color:#2e7d32
style C2 fill:#c8e6c9,color:#2e7d32
style D1 fill:#ffcc80,color:#e65100
style D2 fill:#ffcc80,color:#e65100
style E1 fill:#ffab91,color:#bf360c
style E2 fill:#ffab91,color:#bf360c
style F1 fill:#ce93d8,color:#4a148c
style F2 fill:#ce93d8,color:#4a148c
6.2 约束实战示例
-- 创建带约束的表
CREATE TABLE myapp.products (
product_id NUMBER(10) PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
category_id NUMBER(10),
price NUMBER(10, 2) NOT NULL,
stock NUMBER(10) DEFAULT 0,
created_at DATE DEFAULT SYSDATE,
-- 主键约束（列级）
CONSTRAINT pk_products PRIMARY KEY (product_id),
-- 唯一约束
CONSTRAINT uk_products_name UNIQUE (product_name),
-- 外键约束
CONSTRAINT fk_products_category
FOREIGN KEY (category_id) REFERENCES categories(category_id)
ON DELETE SET NULL,
-- CHECK 约束
CONSTRAINT chk_products_price CHECK (price > 0),
CONSTRAINT chk_products_stock CHECK (stock >= 0)
);
-- 给已有表添加约束
ALTER TABLE myapp.employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);
-- 修改列添加 NOT NULL
ALTER TABLE myapp.employees
MODIFY (emp_name VARCHAR2(50) NOT NULL);
-- 删除约束
ALTER TABLE myapp.products
DROP CONSTRAINT uk_products_name;
-- 查询约束信息
SELECT constraint_name,
constraint_type,
table_name,
status
FROM user_constraints
WHERE table_name = 'PRODUCTS';
-- 查询约束列
SELECT constraint_name,
column_name
FROM user_cons_columns
WHERE table_name = 'PRODUCTS';
6.3 索引基础
索引是数据库性能优化的重要工具，类似于书籍的目录，可以加速数据检索。Oracle 会自动为 PRIMARY KEY 和 UNIQUE 列创建唯一索引。
什么时候应该创建索引：WHERE 子句中经常使用的列；JOIN 操作的连接列；ORDER BY、DISTINCT、GROUP BY 涉及的列。
什么时候不应该创建索引：数据量很小的表；列值重复度很高（如性别只有男/女）；频繁进行大批量插入/更新的表。
6.4 索引类型
| 索引类型 | 说明 | 使用场景 |
|---|---|---|
| B-tree 索引 | 默认索引类型，适合高基数列 | 大多数场景 |
| 位图索引 | 适合低基数列，多用于数据仓库 | 性别、状态等 |
| 函数索引 | 基于函数/表达式创建 | 需要对计算结果搜索 |
| 复合索引 | 多列组合 | 多条件查询 |
-- 创建 B-tree 索引（默认）
CREATE INDEX idx_employees_dept ON employees(department_id);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_employees_email ON employees(email);
-- 创建复合索引（列顺序很重要！）
CREATE INDEX idx_employees_name_dept ON employees(last_name, first_name, department_id);
-- 创建位图索引（适合低基数列）
CREATE BITMAP INDEX idx_employees_gender ON employees(gender);
CREATE BITMAP INDEX idx_employees_status ON employees(status);
-- 创建函数索引
CREATE INDEX idx_employees_upper_name ON employees(UPPER(last_name));
CREATE INDEX idx_employees_salary_year ON employees(salary * 12);
-- 查询索引信息
SELECT index_name,
table_name,
column_name,
index_type
FROM user_indexes i
JOIN user_ind_columns c ON i.index_name = c.index_name
WHERE i.table_name = 'EMPLOYEES';
-- 删除索引
DROP INDEX idx_employees_dept;
-- 重建索引（优化性能）
ALTER INDEX idx_employees_dept REBUILD;
第七章：函数与表达式
7.1 字符函数
字符函数用于处理字符串数据，是 SQL 编程中最常用的函数类型。
| 函数 | 说明 | 示例结果 |
|---|---|---|
CONCAT(a, b) |
连接两个字符串 | 'HelloWorld' |
LENGTH(str) |
返回字符串长度（字符） | 5 |
LENGTHB(str) |
返回字符串长度（字节） | 5（英文）/15（中文） |
UPPER(str) |
转换为大写 | 'HELLO' |
LOWER(str) |
转换为小写 | 'hello' |
INITCAP(str) |
首字母大写 | 'Hello' |
SUBSTR(str, m, n) |
截取字符串 | 从m开始取n个 |
INSTR(str, substr) |
查找子串位置 | 0 表示未找到 |
TRIM(str) |
去除首尾空格 | 去掉空格 |
LTRIM(str, chars) |
去除左侧字符 | 去掉指定字符 |
RTRIM(str, chars) |
去除右侧字符 | 去掉指定字符 |
LPAD(str, n, pad) |
左填充 | 右对齐 |
RPAD(str, n, pad) |
右填充 | 左对齐 |
REPLACE(str, a, b) |
替换字符 | 替换指定字符 |
TRANSLATE(str, a, b) |
字符映射转换 | 按字符替换 |
-- 字符函数示例
SELECT CONCAT('Hello', 'World') AS result FROM dual; -- HelloWorld
SELECT LENGTH('Hello World') AS len FROM dual; -- 11
SELECT UPPER('hello') AS upper FROM dual; -- HELLO
SELECT LOWER('HELLO') AS lower FROM dual; -- hello
SELECT INITCAP('hello world') AS init FROM dual; -- Hello World
-- SUBSTR: 从第3个字符开始取5个字符（1-based）
SELECT SUBSTR('HelloWorld', 3, 5) AS sub FROM dual; -- lloWo
-- INSTR: 查找子串位置
SELECT INSTR('HelloWorld', 'o') AS pos FROM dual; -- 5
-- TRIM 系列
SELECT TRIM(' hello ') AS trim_result FROM dual; -- hello
SELECT LTRIM('aaaahelloaaa', 'a') AS ltrim FROM dual; -- helloaaa
SELECT RTRIM('aaaahelloaaa', 'a') AS rtrim FROM dual; -- aaaahello
-- LPAD/RPAD
SELECT LPAD('123', 10, '0') AS lpad FROM dual; -- 0000000123
SELECT RPAD('123', 10, '*') AS rpad FROM dual; -- 123*******
-- REPLACE 和 TRANSLATE
SELECT REPLACE('HelloWorld', 'o', 'O') AS rep FROM dual; -- HellOWOrld
SELECT TRANSLATE('HelloWorld', 'oW', 'Ow') AS trans FROM dual; -- HellOworld
7.2 数值函数
数值函数用于处理数值数据。
| 函数 | 说明 | 示例 |
|---|---|---|
ROUND(n, m) |
四舍五入 | ROUND(3.14159, 2) = 3.14 |
TRUNC(n, m) |
截断 | TRUNC(3.14159, 2) = 3.14 |
MOD(m, n) |
取余 | MOD(10, 3) = 1 |
ABS(n) |
绝对值 | ABS(-5) = 5 |
SQRT(n) |
平方根 | SQRT(16) = 4 |
POWER(m, n) |
幂运算 | POWER(2, 3) = 8 |
CEIL(n) |
向上取整 | CEIL(3.1) = 4 |
FLOOR(n) |
向下取整 | FLOOR(3.9) = 3 |
SIGN(n) |
符号函数 | SIGN(-5) = -1 |
-- 数值函数示例
SELECT ROUND(3.14159, 3) AS round_result FROM dual; -- 3.142
SELECT TRUNC(3.14159, 3) AS trunc_result FROM dual; -- 3.141
SELECT MOD(10, 3) AS mod_result FROM dual; -- 1
SELECT ABS(-100) AS abs_result FROM dual; -- 100
SELECT SQRT(144) AS sqrt_result FROM dual; -- 12
SELECT POWER(2, 10) AS power_result FROM dual; -- 1024
SELECT CEIL(3.1) AS ceil_result FROM dual; -- 4
SELECT FLOOR(3.9) AS floor_result FROM dual; -- 3
-- 结合实际应用
SELECT product_name,
price,
ROUND(price * 0.9, 2) AS discount_price,
TRUNC(price / 3, 2) AS split_price
FROM products;
7.3 日期函数
日期函数用于处理日期和时间数据，是 Oracle 编程中非常重要的部分。
-- 常用日期函数
SELECT SYSDATE AS current_date FROM dual; -- 当前日期
SELECT CURRENT_DATE FROM dual; -- 当前日期（时区感知）
SELECT CURRENT_TIMESTAMP FROM dual; -- 当前时间戳
SELECT SYSTIMESTAMP FROM dual; -- 系统时间戳
-- 日期计算
SELECT SYSDATE + 7 AS next_week FROM dual; -- 7天后
SELECT SYSDATE - 30 AS thirty_days_ago FROM dual; -- 30天前
SELECT SYSDATE + 1/24 FROM dual; -- 1小时后
SELECT SYSDATE + 1/1440 FROM dual; -- 1分钟后
-- MONTHS_BETWEEN: 计算两个日期间的月数
SELECT MONTHS_BETWEEN('2024-12-31', '2024-01-01') AS months FROM dual; -- 11.97
-- ADD_MONTHS: 日期加减月
SELECT ADD_MONTHS(SYSDATE, 6) AS six_months_later FROM dual;
SELECT ADD_MONTHS(SYSDATE, -3) AS three_months_ago FROM dual;
-- TRUNC: 日期截断
SELECT TRUNC(SYSDATE, 'YYYY') AS first_day_year FROM dual; -- 年初
SELECT TRUNC(SYSDATE, 'MM') AS first_day_month FROM dual; -- 月初
SELECT TRUNC(SYSDATE, 'DD') AS today_start FROM dual; -- 今天零点
SELECT TRUNC(SYSDATE, 'DAY') AS week_start FROM dual; -- 周初（周日）
-- EXTRACT: 提取日期部分
SELECT EXTRACT(YEAR FROM SYSDATE) AS year FROM dual;
SELECT EXTRACT(MONTH FROM SYSDATE) AS month FROM dual;
SELECT EXTRACT(DAY FROM SYSDATE) AS day FROM dual;
-- NEXT_DAY: 下周几
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') AS next_friday FROM dual;
SELECT NEXT_DAY(SYSDATE, 6) AS next_friday2 FROM dual; -- 1=周日,7=周六
7.4 转换函数
转换函数用于在不同数据类型之间进行转换。
| 函数 | 说明 |
|---|---|
TO_CHAR(d/n, fmt) |
转换为字符串 |
TO_DATE(str, fmt) |
字符串转日期 |
TO_NUMBER(str) |
字符串转数字 |
TO_TIMESTAMP(str, fmt) |
字符串转时间戳 |
CAST(expr AS type) |
通用类型转换 |
NVL(expr, value) |
NULL 值转换 |
NVL2(expr, v1, v2) |
NULL 处理 |
COALESCE(expr1, expr2, ...) |
返回第一个非 NULL 值 |
-- TO_CHAR: 格式化和转换
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS fmt_date FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"') AS chinese_date FROM dual;
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') AS time_only FROM dual;
SELECT TO_CHAR(12345.67, '999,999.99') AS fmt_num FROM dual; -- 12,345.67
SELECT TO_CHAR(12345.67, 'L999,999.99') AS fmt_currency FROM dual; -- ¥12,345.67
-- TO_DATE: 字符串转日期
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') AS to_date FROM dual;
SELECT TO_DATE('20240115', 'YYYYMMDD') AS to_date2 FROM dual;
-- TO_NUMBER: 字符串转数字
SELECT TO_NUMBER('12345.67') + 100 FROM dual; -- 12445.67
-- NVL 系列
SELECT NVL(NULL, '默认值') FROM dual; -- 默认值
SELECT NVL2(NULL, '非空值', '空值') FROM dual; -- 空值
SELECT COALESCE(NULL, NULL, '第三个', '第四个') FROM dual; -- 第三个
第八章：事务与并发控制
8.1 事务基础
事务（Transaction）是数据库工作的基本单元，由一条或多条 SQL 语句组成，作为一个整体执行。事务具有四个关键特性，简称 ACID：
原子性（Atomicity）：事务是最小执行单位，要么全部成功，要么全部失败回滚。不存在部分成功的情况。
一致性（Consistency）：事务执行前后，数据库必须保持一致状态。所有约束、触发器、级联操作等都应正确执行。
隔离性（Isolation）：并发执行的事务相互隔离，一个事务的中间状态对其他事务不可见。
持久性（Durability）：事务一旦提交，其结果永久保存，即使系统崩溃也不会丢失。
8.2 事务控制语句
-- 开启事务（Oracle 默认自动开启事务）
-- 每条 DML 语句在执行时自动开始一个事务
-- 提交事务
COMMIT;
-- 回滚事务（撤销所有未提交的更改）
ROLLBACK;
-- 回滚到保存点
SAVEPOINT sp1; -- 创建保存点
UPDATE ...; -- 执行操作
SAVEPOINT sp2; -- 再创建保存点
DELETE ...; -- 执行操作
ROLLBACK TO sp2; -- 回滚到 sp2，保留 sp1 之前的操作
ROLLBACK TO sp1; -- 回滚到 sp1
-- 示例
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
SAVEPOINT after_withdraw;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
-- 如果第二笔转账失败，回滚到保存点
ROLLBACK TO after_withdraw;
-- 此时第一个账户的扣款也被撤销了
8.3 并发问题
多个事务同时操作同一数据时，可能产生以下问题：
| 问题 | 说明 | 示例 |
|---|---|---|
| 脏读 | 读取未提交数据 | 事务 A 修改了 X，但未提交；事务 B 读取了 X；事务 A 回滚 |
| 不可重复读 | 同一查询结果不同 | 事务 A 读取 X=100；事务 B 修改 X=200 并提交；事务 A 再次读取 X=200 |
| 幻读 | 读取到新增数据 | 事务 A 读取满足条件的 10 条记录；事务 B 新增了 1 条满足条件的记录；事务 A 再次读取得到 11 条 |
8.4 隔离级别
Oracle 支持四种隔离级别，用于控制并发事务之间的相互影响程度：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 | 最低隔离，性能最好 |
| READ COMMITTED | 不可能 | 可能 | 可能 | Oracle 默认级别 |
| REPEATABLE READ | 不可能 | 不可能 | 可能 | MySQL 默认级别 |
| SERIALIZABLE | 不可能 | 不可能 | 不可能 | 最高隔离，性能最差 |
-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY; -- 只读事务，不能执行 DML
-- 在查询中指定隔离级别（使用提示）
SELECT /*+ FIRST_ROWS(10) */ * FROM employees WHERE ...
第九章：视图与存储过程
9.1 视图详解
视图（View）是一个虚拟表，其内容由查询定义。视图不存储实际数据，而是存储 SQL 查询语句。每次访问视图时，Oracle 会执行这个查询。
为什么要使用视图：
- 简化复杂查询，提高代码复用性
- 限制数据访问，保护敏感信息
- 提供向后兼容的接口
- 逻辑数据独立
-- 创建简单视图
CREATE VIEW v_employee_details AS
SELECT e.employee_id,
e.first_name || ' ' || e.last_name AS full_name,
d.department_name,
e.salary,
e.hire_date
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- 创建只读视图
CREATE VIEW v_high_salary AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 8000
WITH READ ONLY;
-- 创建带 CHECK OPTION 的视图（强制所有 DML 操作必须符合视图定义）
CREATE VIEW v_it_dept AS
SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE department_id = 50
WITH CHECK OPTION CONSTRAINT v_it_dept_chk;
-- 复杂视图
CREATE VIEW v_dept_salary_summary AS
SELECT d.department_id,
d.department_name,
COUNT(e.employee_id) AS emp_count,
SUM(e.salary) AS total_salary,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS max_salary,
MIN(e.salary) AS min_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
-- 使用视图
SELECT * FROM v_employee_details WHERE salary > 6000;
SELECT * FROM v_dept_salary_summary ORDER BY avg_salary DESC;
-- 视图 DML 操作（受限于视图定义）
INSERT INTO v_it_dept VALUES (999, 'Test', 5000, 50); -- 成功，department_id=50
INSERT INTO v_it_dept VALUES (998, 'Test2', 5000, 60); -- 失败，department_id 不符合
-- 删除视图
DROP VIEW v_employee_details;
9.2 序列详解
序列（Sequence）是 Oracle 用于生成自增数字的对象，常用于为主键字段生成唯一值。
-- 创建序列
CREATE SEQUENCE myapp.users_seq
START WITH 1 -- 起始值
INCREMENT BY 1 -- 增量
MINVALUE 1 -- 最小值
MAXVALUE 999999999999 -- 最大值
NOCYCLE -- 不循环
CACHE 20 -- 预分配 20 个值缓存
ORDER; -- 保证顺序（适用于 RAC）
-- 使用序列
SELECT myapp.users_seq.NEXTVAL FROM dual; -- 获取下一个值（会自增）
SELECT myapp.users_seq.CURRVAL FROM dual; -- 获取当前值（不自增）
-- 在 INSERT 中使用
INSERT INTO users (user_id, username, password)
VALUES (myapp.users_seq.NEXTVAL, 'testuser', 'pass');
-- 修改序列
ALTER SEQUENCE myapp.users_seq INCREMENT BY 10;
ALTER SEQUENCE myapp.users_seq CACHE 50;
-- 删除序列
DROP SEQUENCE myapp.users_seq;
-- 查询用户序列
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;
第十章：Navicat 和 JDBC 连接
10.1 Navicat 连接配置图解
%%{init: {'theme': 'base', 'themeVariables': {'background': '#ffffff', 'primaryColor': '#e3f2fd', 'primaryTextColor': '#0d47a1', 'primaryBorderColor': '#1565c0', 'lineColor': '#455a64', 'fontSize': '14px'}}}%%
flowchart LR
A[Navicat 连接 Oracle] --> B[新建连接]
B --> C[选择 Oracle]
C --> D[填写连接信息]
D --> E[测试连接]
E --> F{成功?}
F -->|是| G[保存连接]
F -->|否| H[检查配置]
H --> D
style A fill:#1565c0,color:#fff
style B fill:#90a4ae,color:#fff
style C fill:#90a4ae,color:#fff
style D fill:#90a4ae,color:#fff
style E fill:#90a4ae,color:#fff
style F fill:#ffb74d,color:#e65100
style G fill:#388e3c,color:#fff
style H fill:#f57c00,color:#fff
10.2 Navicat 连接配置步骤
第一步：新建连接
在 Navicat 主界面点击"连接"按钮，选择"Oracle"。
第二步：填写连接信息
| 配置项 | 填写内容 | 说明 |
|---|---|---|
| 连接名 | Oracle-Dev | 自定义名称，方便识别 |
| 连接类型 | Standard | 标准连接 |
| 主机 | 192.168.56.101 | Oracle 服务器地址 |
| 端口 | 1521 | Oracle 默认监听端口 |
| 服务名 | XE | 服务名或 SID |
| 用户名 | myapp | 数据库用户名 |
| 密码 | Pass123 | 用户密码 |
| 角色 | Normal | 普通用户角色 |
第三步：测试并保存
点击"测试连接"按钮，如果显示成功，点击确定保存连接。
10.3 JDBC 连接详解
Oracle JDBC 驱动（称为"Oracle JDBC Thin Driver"）允许 Java 应用直接连接到 Oracle 数据库，无需 Oracle Client 软件。
// Oracle JDBC 连接示例
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OracleConnection {
public static void main(String[] args) {
// 方式一：使用服务名（SERVICE_NAME）
String url = "jdbc:oracle:thin:@192.168.56.101:1521:XE";
// 方式二：使用完整服务名格式
String url2 = "jdbc:oracle:thin:@//192.168.56.101:1521/XE";
// 方式三：使用 SID
String url3 = "jdbc:oracle:thin:@192.168.56.101:1521:ORCL";
String username = "myapp";
String password = "Pass123";
try {
// 加载 JDBC 驱动（Oracle 12c 及以后版本可以省略）
Class.forName("oracle.jdbc.driver.OracleDriver");
// 建立连接
Connection conn = DriverManager.getConnection(url, username, password);
// 创建语句对象
Statement stmt = conn.createStatement();
// 执行查询
String sql = "SELECT user_id, username, email FROM users WHERE status = 1";
ResultSet rs = stmt.executeQuery(sql);
// 处理结果
while (rs.next()) {
int id = rs.getInt("user_id");
String name = rs.getString("username");
String email = rs.getString("email");
System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
}
// 关闭资源
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
10.4 Spring Boot 配置 Oracle
# application.yml 配置
spring:
datasource:
# Oracle 连接配置
url: jdbc:oracle:thin:@192.168.56.101:1521:XE
username: myapp
password: Pass123
driver-class-name: oracle.jdbc.driver.OracleDriver
# 连接池配置
hikari:
maximum-pool-size: 10
minimum-idle: 5
connection-timeout: 30000
idle-timeout: 600000
max-lifetime: 1800000
# JPA 配置
jpa:
database-platform: org.hibernate.dialect.Oracle12cDialect
show-sql: true
hibernate:
ddl-auto: validate
10.5 连接字符串格式说明
Oracle JDBC URL 的格式为：
jdbc:oracle:thin:@host:port:sid
jdbc:oracle:thin:@//host:port/serviceName
| 格式 | 示例 | 适用场景 |
|---|---|---|
| SID 格式 | @192.168.56.101:1521:XE |
Oracle 11g 及以前 |
| 服务名格式 | @//192.168.56.101:1521/XE |
Oracle 12c 及以后，推荐 |
附录：常见问题与解决方案
A.1 ORA-01950: 对表空间无权限
问题描述：插入数据时报错 ORA-01950: 对表空间 'USERS' 无权限
原因分析：用户没有在表空间上分配配额（Quota）
解决方案：
-- 方案一：授予表空间无限使用权限（推荐）
GRANT UNLIMITED TABLESPACE TO myapp;
-- 方案二：授予指定表空间配额
ALTER USER myapp QUOTA 100M ON USERS;
-- 方案三：授予 DBA 角色（包含所有权限）
GRANT DBA TO myapp;
-- 验证权限
SELECT * FROM dba_sys_privs WHERE grantee = 'MYAPP';
A.2 ORA-28009: 连接应为 SYSDBA 或 SYSOPER
问题描述：使用 SYS 用户连接时报错 ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
解决方案：不要使用 SYS 用户进行日常操作，改用 SYSTEM 或业务用户。如果必须使用 SYS，在连接角色中选择 SYSDBA。
A.3 ORA-01017: 无效的用户名/密码
问题描述：登录时报错 ORA-01017: invalid username/password; logon denied
排查步骤：
- 检查用户名和密码是否正确（注意大小写）
- 检查用户是否被锁定：
SELECT account_status FROM dba_users WHERE username = 'MYAPP'; - 检查用户是否被禁用
-- 解锁用户
ALTER USER myapp ACCOUNT UNLOCK;
-- 修改密码
ALTER USER myapp IDENTIFIED BY "NewPass123";
A.4 密码包含特殊字符的处理
在命令行中使用包含 ! 等特殊字符的密码时，需要转义：
# 在 bash 中，感叹号是历史命令的触发符，需要转义
sqlplus 'myapp/Pass123\!@XEPDB1'
# 或者使用双引号包裹
sqlplus "myapp/Pass123!@XEPDB1"
A.5 查看数据库版本和状态
-- 查看数据库版本
SELECT * FROM v$version;
-- 查看实例状态
SELECT instance_name, status, startup_time FROM v$instance;
-- 查看 PDB 状态
SELECT name, open_mode FROM v$pdbs;
-- 查看用户会话
SELECT username, machine, status, logon_time FROM v$session WHERE username = 'MYAPP';
📝 学习建议: Oracle 是一个功能非常强大的数据库系统，本文档涵盖了入门和日常使用的主要内容。建议在学习时多动手实践，结合官方文档深入了解每个主题。Oracle 的高级特性包括分区表、索引优化、RMAN 备份、Data Guard 等，可以在后续深入学习。
浙公网安备 33010602011771号