DM8数据定义语言(DDL)

 表管理

 创建表

-- 基本CREATE TABLE语法
/*
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
...
[table_constraints]
) [TABLESPACE tablespace_name];
*/

-- 创建表示例
CREATE TABLE employees (
employee_id NUMBER(6) PRIMARY KEY, -- 主键约束
name VARCHAR2(50) NOT NULL, -- 非空约束
email VARCHAR2(100) UNIQUE, -- 唯一约束
hire_date DATE DEFAULT SYSDATE, -- 默认值
salary NUMBER(8,2) CHECK (salary > 0), -- 检查约束
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) -- 外键约束
REFERENCES departments(department_id)
) TABLESPACE users;

-- 创建表时添加注释
COMMENT ON TABLE employees IS '公司员工信息表';
COMMENT ON COLUMN employees.employee_id IS '员工编号，主键';
COMMENT ON COLUMN employees.name IS '员工姓名';

-- 基于查询创建表
CREATE TABLE emp_dept_10
AS
SELECT
e.employee_id,
e.name,
e.salary,
d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
WHERE
e.department_id = 10;

修改表

-- 添加列
ALTER TABLE employees
ADD (middle_name VARCHAR2(30),
nickname VARCHAR2(20) DEFAULT '无名');

-- 修改列定义
ALTER TABLE employees
MODIFY (email VARCHAR2(150),
salary NUMBER(10,2));

-- 删除列
ALTER TABLE employees
DROP COLUMN nickname;

-- 重命名列
ALTER TABLE employees
RENAME COLUMN name TO full_name;

-- 添加约束
ALTER TABLE employees
ADD CONSTRAINT emp_salary_min CHECK (salary >= 3000);

-- 禁用/启用约束
ALTER TABLE employees
DISABLE CONSTRAINT emp_salary_min;

ALTER TABLE employees
ENABLE CONSTRAINT emp_salary_min;

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

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

删除表

-- 基本DROP TABLE语法
DROP TABLE employees;

-- 带约束的删除
DROP TABLE departments CASCADE CONSTRAINTS; -- 级联删除依赖的约束

-- 清空表数据但保留结构
TRUNCATE TABLE temp_employees;

索引管理

创建索引

-- 创建普通索引
CREATE INDEX idx_emp_name ON employees(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- 创建复合索引
CREATE INDEX idx_emp_dept_sal ON employees(department_id, salary DESC);

-- 创建函数索引
CREATE INDEX idx_emp_name_upper ON employees(UPPER(name));

-- 位图索引(适合低基数列)
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

-- 分区索引
CREATE INDEX idx_emp_hire_date ON employees(hire_date)
GLOBAL PARTITION BY RANGE (hire_date) (
PARTITION p_2020 VALUES LESS THAN (TO_DATE('2021-01-01', 'YYYY-MM-DD')),
PARTITION p_2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

管理索引

-- 重建索引
ALTER INDEX idx_emp_name REBUILD;

-- 重命名索引
ALTER INDEX idx_emp_name RENAME TO idx_employee_name;

-- 监控索引使用
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 查看索引使用情况
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMP_NAME';

-- 删除索引
DROP INDEX idx_emp_name;

视图管理

创建视图

-- 基本视图
CREATE VIEW v_emp_dept AS
SELECT
e.employee_id,
e.name,
e.salary,
d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id;

-- 带检查选项的视图(确保通过视图的修改满足视图定义)
CREATE VIEW v_emp_10 AS
SELECT * FROM employees WHERE department_id = 10
WITH CHECK OPTION;

-- 只读视图
CREATE VIEW v_emp_sal_readonly AS
SELECT
department_id,
AVG(salary) avg_sal,
MAX(salary) max_sal,
MIN(salary) min_sal
FROM
employees
GROUP BY
department_id
WITH READ ONLY;

-- 物化视图(预计算并存储结果)
CREATE MATERIALIZED VIEW mv_emp_sal_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT
department_id,
COUNT(*) emp_count,
SUM(salary) total_sal,
AVG(salary) avg_sal
FROM
employees
GROUP BY
department_id;

管理视图

-- 修改视图
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT
e.employee_id,
e.name,
e.salary,
d.department_name,
l.city
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
JOIN
locations l ON d.location_id = l.location_id;

-- 重编译视图
ALTER VIEW v_emp_dept COMPILE;

-- 删除视图
DROP VIEW v_emp_dept;

-- 刷新物化视图
BEGIN
DBMS_MVIEW.REFRESH('mv_emp_sal_summary', 'C');
END;
/