Oracle 数据表对象 —— 语法知识点详解与案例实践
一、环境准备：Oracle 安装与配置（简要指南）
说明：本章内容基于 Oracle Database。以下为在 Windows 或 Linux 上安装 Oracle Database 的基本步骤（以 Oracle 21c Express Edition 为例）。
1. 下载 Oracle Database
- 访问官网：https://www.oracle.com/database/technologies/xe-downloads.html
- 下载 Oracle Database 21c XE（免费版）
2. 安装步骤（Windows 示例）
- 以管理员身份运行安装程序
OracleXE213_Win64.exe - 设置数据库口令（如：
oracle123） - 选择安装路径（默认即可）
- 安装完成后，服务会自动启动：
OracleServiceXEOracleXETNSListener
3. 连接数据库
使用 SQL*Plus 或 SQL Developer 连接：
# 使用 sqlplus 命令行sqlplus sys/oracle123@localhost:1521/XE as sysdba或创建普通用户（推荐）：
-- 创建表空间（可选）CREATETABLESPACEusers_ts DATAFILE'users_ts.dbf'SIZE100M AUTOEXTENDON;-- 创建用户CREATEUSERhr IDENTIFIEDBYhr123DEFAULTTABLESPACEusers_ts;-- 授予权限GRANTCONNECT,RESOURCE,CREATEVIEWTOhr;然后用hr/hr123登录进行练习。
二、数据表操作语法详解与案例
1. 数据表概述
- 表是 Oracle 中存储数据的基本逻辑单元。
- 每个表由行（记录）和列（字段）组成。
- 表属于某个用户（Schema），如
hr.employees。
2. 创建数据表（CREATE TABLE）
语法：
CREATETABLE[schema.]table_name(column_name datatype[DEFAULTexpr][column_constraint],...[table_constraint])[TABLESPACEtablespace_name];案例：创建员工表
-- 创建 employees 表CREATETABLEemployees(emp_id NUMBER(6)PRIMARYKEY,-- 主键约束first_name VARCHAR2(20),-- 可为空last_name VARCHAR2(25)NOTNULL,-- 非空约束email VARCHAR2(30)UNIQUE,-- 唯一性约束phone VARCHAR2(20),hire_dateDATEDEFAULTSYSDATE,-- 默认当前日期job_id VARCHAR2(10),salary NUMBER(8,2)CHECK(salary>0),-- 检查约束（隐式）manager_id NUMBER(6),dept_id NUMBER(4));注释：
VARCHAR2是 Oracle 推荐的变长字符串类型。DEFAULT SYSDATE表示若未提供值，则自动填入系统当前日期。CHECK约束确保工资大于 0。
3. 数据表的逻辑结构
- 段（Segment）：表在物理上对应一个数据段。
- 区（Extent）：由多个连续数据块组成。
- 块（Block）：最小 I/O 单元，默认 8KB。
可通过USER_SEGMENTS查看：
SELECTsegment_name,segment_type,bytes/1024/1024ASsize_mbFROMuser_segmentsWHEREsegment_name='EMPLOYEES';4. 数据表的特性
- 支持分区（Partitioning）
- 支持压缩（Compression）
- 支持并行处理
- 可设置存储参数（如 PCTFREE, INITTRANS）
5. 维护数据表
(1) 增加字段（ADD COLUMN）
-- 添加奖金字段ALTERTABLEemployeesADD(bonus NUMBER(8,2));(2) 删除字段（DROP COLUMN）
-- 删除 phone 字段ALTERTABLEemployeesDROPCOLUMNphone;⚠️ 注意：删除字段不可逆，且可能影响性能（标记删除，后续清理）。
(3) 修改字段（MODIFY COLUMN）
-- 修改 salary 精度，并设默认值ALTERTABLEemployeesMODIFY(salary NUMBER(10,2)DEFAULT5000.00);-- 修改字段为非空ALTERTABLEemployeesMODIFY(first_name VARCHAR2(20)NOTNULL);(4) 重命名表（RENAME）
-- 重命名表RENAMEemployeesTOemp_info;-- 或ALTERTABLEemp_infoRENAMETOemployees;(5) 改变表空间和存储参数
-- 移动表到新表空间ALTERTABLEemployees MOVETABLESPACEusers_ts;-- 修改存储参数（需启用 row movement）ALTERTABLEemployees MOVE PCTFREE20INITTRANS4;(6) 删除表（DROP TABLE）
-- 普通删除（可闪回）DROPTABLEemployees;-- 彻底删除（不可恢复）DROPTABLEemployeesPURGE;-- 闪回恢复（如果未 purge）FLASHBACKTABLEemployeesTOBEFOREDROP;(7) 修改表状态
-- 设置为只读ALTERTABLEemployeesREADONLY;-- 恢复为读写ALTERTABLEemployeesREADWRITE;6. 数据完整性与约束
约束用于保证数据的准确性与一致性。
(1) 非空约束（NOT NULL）
-- 创建时定义CREATETABLEtest_notnull(id NUMBER,name VARCHAR2(20)NOTNULL);-- 或通过修改添加（需先确保无空值）ALTERTABLEemployeesMODIFY(last_name VARCHAR2(25)NOTNULL);(2) 主键约束（PRIMARY KEY）
-- 创建表时定义CREATETABLEdepartments(dept_id NUMBER(4)PRIMARYKEY,dept_name VARCHAR2(30)NOTNULL);-- 或单独添加ALTERTABLEemployeesADDCONSTRAINTemp_pkPRIMARYKEY(emp_id);(3) 唯一性约束（UNIQUE）
ALTERTABLEemployeesADDCONSTRAINTemp_email_ukUNIQUE(email);(4) 外键约束（FOREIGN KEY）
-- 先确保主表存在CREATETABLEdepartments(dept_id NUMBER(4)PRIMARYKEY,dept_name VARCHAR2(30));-- 在 employees 中添加外键ALTERTABLEemployeesADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdepartments(dept_id)ONDELETESETNULL;-- 或 CASCADE / NO ACTION（默认）外键选项说明：
ON DELETE CASCADE：主表删除，从表级联删除。ON DELETE SET NULL：主表删除，从表字段设为 NULL（要求字段可为空）。
(5) 禁用和激活约束
-- 禁用外键约束（常用于大批量导入）ALTERTABLEemployeesDISABLECONSTRAINTemp_dept_fk;-- 激活约束（会验证现有数据）ALTERTABLEemployeesENABLECONSTRAINTemp_dept_fk;-- 启用但不验证已有数据（需谨慎）ALTERTABLEemployeesENABLENOVALIDATECONSTRAINTemp_dept_fk;(6) 删除约束
-- 删除约束（保留列）ALTERTABLEemployeesDROPCONSTRAINTemp_email_uk;-- 删除主键（同时删除唯一索引）ALTERTABLEemployeesDROPPRIMARYKEY;三、综合性案例
案例目标：
- 创建部门表和员工表，建立主外键关系。
- 插入测试数据。
- 修改表结构：增加字段、修改约束。
- 演示约束禁用与启用。
- 删除表并恢复（闪回）。
步骤 1：创建表结构
-- 创建部门表CREATETABLEdepartments(dept_id NUMBER(4)PRIMARYKEY,dept_name VARCHAR2(30)NOTNULL,location VARCHAR2(50));-- 创建员工表（暂不加外键）CREATETABLEemployees(emp_id NUMBER(6),first_name VARCHAR2(20),last_name VARCHAR2(25)NOTNULL,email VARCHAR2(30),hire_dateDATEDEFAULTSYSDATE,salary NUMBER(8,2)CHECK(salary>0),dept_id NUMBER(4));-- 添加主键ALTERTABLEemployeesADDCONSTRAINTemp_pkPRIMARYKEY(emp_id);-- 添加唯一约束ALTERTABLEemployeesADDCONSTRAINTemp_email_ukUNIQUE(email);-- 添加外键（引用 departments）ALTERTABLEemployeesADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_id)REFERENCESdepartments(dept_id)ONDELETESETNULL;步骤 2：插入测试数据
-- 插入部门INSERTINTOdepartmentsVALUES(10,'HR','Beijing');INSERTINTOdepartmentsVALUES(20,'IT','Shanghai');-- 插入员工INSERTINTOemployees(emp_id,first_name,last_name,email,salary,dept_id)VALUES(101,'Alice','Smith','alice@example.com',8000,10);INSERTINTOemployees(emp_id,first_name,last_name,email,salary,dept_id)VALUES(102,'Bob','Lee','bob@example.com',9000,20);步骤 3：修改表结构
-- 增加字段ALTERTABLEemployeesADD(commission_pct NUMBER(2,2));-- 修改字段默认值ALTERTABLEemployeesMODIFY(commission_pctDEFAULT0.1);-- 尝试插入违反外键的数据（会失败）-- INSERT INTO employees VALUES (103, 'Tom', 'King', 'tom@example.com', SYSDATE, 7000, 99); -- 错误！dept_id=99 不存在步骤 4：禁用约束并批量导入
-- 禁用外键（假设要导入历史数据）ALTERTABLEemployeesDISABLECONSTRAINTemp_dept_fk;-- 插入无效 dept_id（临时允许）INSERTINTOemployees(emp_id,first_name,last_name,email,salary,dept_id)VALUES(103,'Charlie','Brown','charlie@example.com',6000,99);-- 修复数据或删除无效行DELETEFROMemployeesWHEREdept_id=99;-- 重新启用约束ALTERTABLEemployeesENABLECONSTRAINTemp_dept_fk;步骤 5：删除与闪回
-- 删除表（放入回收站）DROPTABLEemployees;-- 查看回收站SHOWRECYCLEBIN;-- 闪回恢复FLASHBACKTABLEemployeesTOBEFOREDROP;-- 重命名恢复后的表（如果名字冲突）FLASHBACKTABLE"BIN$...$"TOBEFOREDROPRENAMETOemployees_old;四、总结
| 操作类型 | 关键语法 |
|---|---|
| 创建表 | CREATE TABLE ... |
| 增加字段 | ALTER TABLE ... ADD (...) |
| 修改字段 | ALTER TABLE ... MODIFY (...) |
| 删除字段 | ALTER TABLE ... DROP COLUMN ... |
| 重命名表 | RENAME old TO new |
| 添加主键 | ADD CONSTRAINT pk PRIMARY KEY (...) |
| 添加外键 | ADD CONSTRAINT fk FOREIGN KEY (...) REFERENCES ... |
| 禁用约束 | ALTER TABLE ... DISABLE CONSTRAINT ... |
| 删除表 | DROP TABLE ... [PURGE] |
| 闪回表 | FLASHBACK TABLE ... TO BEFORE DROP |
最佳实践建议：
- 所有约束命名（如
emp_pk），便于管理。- 生产环境避免频繁
DROP COLUMN。- 大批量数据操作前，考虑禁用约束并事后验证。
- 使用
PURGE谨慎，避免无法恢复。
如有进一步需求（如分区表、物化视图、索引组织表等高级特性），可继续扩展本章内容。
)
