Oracle表操作 #

Oracle表类型
├── 堆组织表（Heap Table）
│   └── 默认表类型，数据无序存储
├── 索引组织表（IOT）
│   └── 按主键顺序存储数据
├── 外部表（External Table）
│   └── 访问外部文件数据
├── 临时表（Temporary Table）
│   └── 会话或事务级临时数据
├── 分区表（Partitioned Table）
│   └── 大表分区存储
└── 聚簇表（Clustered Table）
    └── 多表物理存储在一起

表存储结构
├── 段（Segment）
│   └── 表占用的存储空间
├── 区（Extent）
│   └── 连续的数据块集合
├── 块（Block）
│   └── 最小I/O单位（默认8KB）
└── 行（Row）
    └── 实际数据记录

-- 创建表基本语法
CREATE TABLE table_name (
    column1 datatype [DEFAULT default_value] [NULL|NOT NULL],
    column2 datatype [DEFAULT default_value] [NULL|NOT NULL],
    ...
) [TABLESPACE tablespace_name];

-- 创建员工表
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    phone_number VARCHAR2(20),
    hire_date DATE DEFAULT SYSDATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER(6),
    department_id NUMBER(4)
);

-- 指定表空间
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
) TABLESPACE users_data;

-- 添加表注释
COMMENT ON TABLE employees IS '员工信息表';
COMMENT ON COLUMN employees.employee_id IS '员工ID';
COMMENT ON COLUMN employees.first_name IS '名';
COMMENT ON COLUMN employees.last_name IS '姓';

-- 创建带完整约束的表
CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30) NOT NULL,
    manager_id NUMBER(6),
    location_id NUMBER(4),
    
    CONSTRAINT uk_dept_name UNIQUE (department_name),
    CONSTRAINT fk_dept_manager FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
);

-- 创建带命名约束的表
CREATE TABLE orders (
    order_id NUMBER(10),
    customer_id NUMBER(6) NOT NULL,
    order_date DATE DEFAULT SYSDATE,
    total_amount NUMBER(12,2),
    status VARCHAR2(20),
    
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT chk_status CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED')),
    CONSTRAINT chk_amount CHECK (total_amount >= 0)
);

-- 创建表并复制数据
CREATE TABLE employees_backup AS
SELECT * FROM employees;

-- 创建表结构（不复制数据）
CREATE TABLE employees_structure AS
SELECT * FROM employees WHERE 1=0;

-- 创建表并复制部分数据
CREATE TABLE high_salary_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 10000;

-- 创建表并添加新列
CREATE TABLE employees_with_level AS
SELECT 
    employee_id,
    first_name,
    last_name,
    salary,
    CASE 
        WHEN salary >= 20000 THEN 'HIGH'
        WHEN salary >= 10000 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS salary_level
FROM employees;

-- 创建会话级临时表
CREATE GLOBAL TEMPORARY TABLE temp_session_data (
    id NUMBER,
    name VARCHAR2(100),
    create_time DATE DEFAULT SYSDATE
) ON COMMIT PRESERVE ROWS;

-- 创建事务级临时表
CREATE GLOBAL TEMPORARY TABLE temp_transaction_data (
    id NUMBER,
    value NUMBER
) ON COMMIT DELETE ROWS;

-- 临时表特点
-- 1. 数据只在会话或事务期间存在
-- 2. 结构永久保存
-- 3. 数据自动清理
-- 4. 不产生redo日志

-- 创建索引组织表（IOT）
CREATE TABLE iot_employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100)
) ORGANIZATION INDEX;

-- 带溢出区的IOT
CREATE TABLE iot_orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    order_date DATE,
    total_amount NUMBER(12,2),
    notes VARCHAR2(4000)
) ORGANIZATION INDEX
OVERFLOW TABLESPACE users_data;

-- IOT特点
-- 1. 按主键顺序存储
-- 2. 适合主键查询
-- 3. 节省存储空间

-- 添加单列
ALTER TABLE employees ADD (
    middle_name VARCHAR2(50)
);

-- 添加多列
ALTER TABLE employees ADD (
    address VARCHAR2(200),
    city VARCHAR2(50),
    country VARCHAR2(50) DEFAULT 'China'
);

-- 添加带约束的列
ALTER TABLE employees ADD (
    status VARCHAR2(20) DEFAULT 'ACTIVE' NOT NULL,
    CONSTRAINT chk_emp_status CHECK (status IN ('ACTIVE', 'INACTIVE'))
);

-- 修改列数据类型
ALTER TABLE employees MODIFY (
    first_name VARCHAR2(100)
);

-- 修改列约束
ALTER TABLE employees MODIFY (
    phone_number VARCHAR2(20) NOT NULL
);

-- 修改列默认值
ALTER TABLE employees MODIFY (
    hire_date DATE DEFAULT SYSDATE
);

-- 修改多列
ALTER TABLE employees MODIFY (
    first_name VARCHAR2(100),
    last_name VARCHAR2(100) NOT NULL
);

-- 修改列名
ALTER TABLE employees RENAME COLUMN middle_name TO second_name;

-- 删除单列
ALTER TABLE employees DROP COLUMN middle_name;

-- 删除多列
ALTER TABLE employees DROP (address, city, country);

-- 设置列为UNUSED（不立即删除，减少影响）
ALTER TABLE employees SET UNUSED (old_column);

-- 删除UNUSED列
ALTER TABLE employees DROP UNUSED COLUMNS;

-- 查看UNUSED列
SELECT * FROM USER_UNUSED_COL_TABS WHERE TABLE_NAME = 'EMPLOYEES';

-- 添加主键约束
ALTER TABLE employees ADD CONSTRAINT pk_employees 
PRIMARY KEY (employee_id);

-- 添加唯一约束
ALTER TABLE employees ADD CONSTRAINT uk_email 
UNIQUE (email);

-- 添加外键约束
ALTER TABLE employees ADD CONSTRAINT fk_dept 
FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- 添加检查约束
ALTER TABLE employees ADD CONSTRAINT chk_salary 
CHECK (salary > 0);

-- 添加非空约束
ALTER TABLE employees MODIFY last_name NOT NULL;

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT uk_email;

-- 删除主键（级联删除外键）
ALTER TABLE employees DROP PRIMARY KEY CASCADE;

-- 删除唯一约束
ALTER TABLE employees DROP UNIQUE (email);

-- 禁用约束
ALTER TABLE employees DISABLE CONSTRAINT chk_salary;

-- 启用约束
ALTER TABLE employees ENABLE CONSTRAINT chk_salary;

-- 禁用约束（不验证现有数据）
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT chk_salary;

-- 重命名表
ALTER TABLE employees RENAME TO staff;

-- 或使用RENAME命令
RENAME employees TO staff;

-- 删除表
DROP TABLE employees;

-- 删除表（级联删除约束）
DROP TABLE employees CASCADE CONSTRAINTS;

-- 删除表（清除回收站）
DROP TABLE employees PURGE;

-- 删除表（检查依赖）
-- 如果有外键引用，需要先删除外键或使用CASCADE CONSTRAINTS

-- 查看回收站
SELECT * FROM USER_RECYCLEBIN;
SELECT * FROM RECYCLEBIN;

-- 闪回删除的表
FLASHBACK TABLE employees TO BEFORE DROP;

-- 闪回并重命名
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_restored;

-- 清空回收站
PURGE RECYCLEBIN;

-- 清空指定表
PURGE TABLE employees;

-- 彻底删除（不进入回收站）
DROP TABLE employees PURGE;

-- 截断表（删除所有数据，保留结构）
TRUNCATE TABLE employees;

-- 截断表（释放存储空间）
TRUNCATE TABLE employees DROP ALL STORAGE;

-- TRUNCATE vs DELETE
-- TRUNCATE：DDL，不可回滚，速度快，重置高水位线
-- DELETE：DML，可回滚，速度慢，不重置高水位线

约束类型
├── PRIMARY KEY - 主键约束
│   └── 唯一标识每一行
├── UNIQUE - 唯一约束
│   └── 列值唯一，允许NULL
├── NOT NULL - 非空约束
│   └── 列值不能为空
├── FOREIGN KEY - 外键约束
│   └── 引用其他表的主键
├── CHECK - 检查约束
│   └── 自定义条件验证
└── REF - 引用约束
    └── 引用对象类型

-- 创建表时定义主键
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    name VARCHAR2(50)
);

-- 命名主键约束
CREATE TABLE employees (
    employee_id NUMBER(6),
    name VARCHAR2(50),
    CONSTRAINT pk_employees PRIMARY KEY (employee_id)
);

-- 复合主键
CREATE TABLE order_items (
    order_id NUMBER(10),
    item_id NUMBER(5),
    product_id NUMBER(6),
    quantity NUMBER(5),
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_id)
);

-- 添加主键
ALTER TABLE employees ADD PRIMARY KEY (employee_id);
ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (employee_id);

-- 创建表时定义外键
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    order_date DATE,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
);

-- 外键选项
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE        -- 删除父记录时删除子记录
        -- ON DELETE SET NULL    -- 删除父记录时设置子记录为NULL
        -- ON DELETE NO ACTION   -- 默认行为，不允许删除
);

-- 自引用外键
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    manager_id NUMBER(6),
    CONSTRAINT fk_manager FOREIGN KEY (manager_id) 
        REFERENCES employees(employee_id)
);

-- 创建表时定义检查约束
CREATE TABLE products (
    product_id NUMBER(6) PRIMARY KEY,
    product_name VARCHAR2(100),
    price NUMBER(10,2),
    stock_quantity NUMBER(5),
    status VARCHAR2(20),
    
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_stock CHECK (stock_quantity >= 0),
    CONSTRAINT chk_status CHECK (status IN ('ACTIVE', 'INACTIVE', 'DISCONTINUED'))
);

-- 复杂检查约束
ALTER TABLE employees ADD CONSTRAINT chk_salary_range 
CHECK (salary BETWEEN 1000 AND 100000);

-- 条件检查约束
ALTER TABLE employees ADD CONSTRAINT chk_commission 
CHECK (commission_pct IS NULL OR (commission_pct >= 0 AND commission_pct <= 1));

-- 约束状态
-- ENABLE VALIDATE：启用并验证所有数据（默认）
-- ENABLE NOVALIDATE：启用但不验证现有数据
-- DISABLE VALIDATE：禁用但验证新数据
-- DISABLE NOVALIDATE：禁用且不验证

-- 启用约束（验证所有数据）
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT chk_salary;

-- 启用约束（不验证现有数据）
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT chk_salary;

-- 禁用约束
ALTER TABLE employees DISABLE CONSTRAINT chk_salary;

-- 查看约束状态
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    STATUS,
    VALIDATED
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES';

-- 创建延迟约束
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        DEFERRABLE INITIALLY DEFERRED
);

-- DEFERRABLE：允许延迟检查
-- INITIALLY IMMEDIATE：默认立即检查
-- INITIALLY DEFERRED：默认延迟到事务提交时检查

-- 在事务中设置
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINT fk_customer DEFERRED;

-- 查看表结构
DESC employees;
DESCRIBE employees;

-- 查看表的列信息
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    NULLABLE,
    DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'
ORDER BY COLUMN_ID;

-- 查看表注释
SELECT 
    TABLE_NAME,
    COMMENTS
FROM USER_TAB_COMMENTS
WHERE TABLE_NAME = 'EMPLOYEES';

-- 查看列注释
SELECT 
    COLUMN_NAME,
    COMMENTS
FROM USER_COL_COMMENTS
WHERE TABLE_NAME = 'EMPLOYEES';

-- 查看表的约束
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    STATUS,
    VALIDATED
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES';

-- 查看约束列
SELECT 
    c.CONSTRAINT_NAME,
    c.CONSTRAINT_TYPE,
    cc.COLUMN_NAME
FROM USER_CONSTRAINTS c
JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE c.TABLE_NAME = 'EMPLOYEES';

-- 约束类型说明
-- P：主键
-- R：外键
-- U：唯一
-- C：检查
-- O：视图只读
-- V：视图检查选项

-- 查看表统计信息
SELECT 
    TABLE_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_ROW_LEN,
    LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = 'EMPLOYEES';

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES');

-- 查看表大小
SELECT 
    SEGMENT_NAME,
    SEGMENT_TYPE,
    BYTES/1024/1024 AS SIZE_MB
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = 'EMPLOYEES';

-- 表命名规范
-- 1. 使用小写字母和下划线
-- 2. 使用复数形式
-- 3. 添加业务前缀

-- 好的命名
CREATE TABLE hr_employees (...);
CREATE TABLE fin_accounts (...);
CREATE TABLE inv_products (...);

-- 列命名规范
-- 1. 使用小写字母和下划线
-- 2. 主键：表名_id 或 id
-- 3. 外键：关联表名_id
-- 4. 布尔：is_xxx 或 has_xxx
-- 5. 日期：xxx_date 或 xxx_time

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,         -- 主键
    customer_id NUMBER,                   -- 外键
    order_date DATE,                      -- 日期
    is_paid NUMBER(1) DEFAULT 0,          -- 布尔
    create_time TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 数值类型
-- 主键：NUMBER(10) 或 NUMBER
-- 金额：NUMBER(18,4)
-- 百分比：NUMBER(5,4)

-- 字符类型
-- 定长编码：CHAR
-- 变长字符串：VARCHAR2
-- 多语言：NVARCHAR2

-- 日期类型
-- 一般日期：DATE
-- 精确时间：TIMESTAMP

-- 大对象
-- 长文本：CLOB
-- 二进制：BLOB

-- 1. 总是定义主键
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    ...
);

-- 2. 合理使用外键
-- 考虑性能影响，必要时使用应用层约束

-- 3. 使用检查约束保证数据完整性
CREATE TABLE products (
    price NUMBER(10,2),
    CONSTRAINT chk_price CHECK (price > 0)
);

-- 4. 为约束命名
-- 便于后续管理和识别
ALTER TABLE employees ADD CONSTRAINT uk_email UNIQUE (email);

-- 1. 选择合适的表类型
-- 默认堆表适合大多数场景
-- 主键查询频繁考虑IOT

-- 2. 合理设置存储参数
CREATE TABLE large_table (
    id NUMBER PRIMARY KEY,
    data VARCHAR2(4000)
) PCTFREE 10 PCTUSED 80;

-- 3. 使用分区表处理大表
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

-- 4. 定期收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES');