一、模式对象DDL/DML

1、表

（1）DDL–创建表

示例：创建课程表，
create table EXAM.COURSES
(
COURSE_ID INT not null,
COURSE_NAME VARCHAR(50) not null,
CREDIT_NUMBER(3, 1) not null,
COURSE_TYPE BIT not null,
TEACHER VARCHAR(50) not null,
primary key(“COURSE_ID”)
)
storage(initial 1,next 1, minextents 1,fillfactor 0)

（2）DDL–修改表

–1.新增字段ADD
ALTER TABLE EXAM.COURSES ADD SEMESTER VARCHAR(20);
–2.修改字段类型MODIFY
ALTER TABLE EXAM.COURSES MODIFY TEACHER VARCHAR(60);
–3.修改字段名RENAME COLUMN
ALTER TABLE EXAM.COURSES RENAME COLUMN SEMESTER TO TERM;

–4.删除字段DROP
ALTER TABLE EXAM.COURSES DROP COLUMN TERM;
–5.增加普通约束ADD CONSTRAINT
ALTER TABLE EXAM.COURSES ADD CONSTRAINT uk_coursename UNIQUE(“COURSE_NAME”);
–6.删除约束DROP CONSTRAINT
ALTER TABLE EXAM.COURSES DROP CONSTRAINT uk_coursename;
–7.修改表名
ALTER TABLE EXAM.COURSES RENAME TO COURSES_BAK;
ALTER TABLE EXAM.COURSES_BAK RENAME TO COURSES;

（3）DDL–删除表

–普通删除表DROP
DROP TABLE EXAM.COURSES;
–如果存在才删除DROP IF EXISTS
DROP TABLE IF EXISTS EXAM.COURSES;
–删除表同时清除约束、索引DROP PURGE
DROP TABLE EXAM.COURSES PURGE;

（4）DDL–清空表

–清空表全部数据，保留表结构、索引、约束TRUNCATE
TRUNCATE TABLE EXAM.COURSES;
–释放存储空间TRUNCATE STORAGE
TRUNCATE TABLE EXAM.COURSES REUSE STORAGE;

（5）DDL–加注释

–给表加注释COMMENT ON TABLE
COMMENT ON TABLE EXAM.COURSES IS ‘课程信息表’;
–给字段加注释COMMENT ON COLUMN
COMMENT ON COLUMN EXAM.COURSES.COURSE_ID IS ‘课程编号主键’;
COMMENT ON COLUMN EXAM.COURSES.COURSE_NAME IS ‘课程名称’;
COMMENT ON COLUMN EXAM.COURSES.CREDIT_NUMBER IS ‘课程学分’;
COMMENT ON COLUMN EXAM.COURSES.COURSE_TYPE IS ‘课程类型 0选修课 1必修课’;
COMMENT ON COLUMN EXAM.COURSES.TEACHER IS ‘授课教师姓名’;

（6）DML–增

INSERT INTO EXAM.COURSES
(COURSE_ID, COURSE_NAME,CREDIT, COURSE_TYPE, TEACHER) VALUES
(1,yuwen’,2,1,‘yuwenlaoshi’),
(2,shuxue’,2,1,‘shuxuelaoshi),
(3,yingyu’,2,1,‘yingyulaoshi),
(4,wuli’,1,0,‘wulilaoshi’),
(5,‘lishi’,1,0,‘lishilaoshi’);
COMMIT；
SELECT FROM EXAM.COURSES；

（7）DML–删

–按条件删除DELETE WHERE
DELETE FROM EXAM.COURSES WHERE COURSE_ID = 3;

–删除全部数据(DML，会产生大量回滚日志，区别truncate)
DELETE FROM EXAM.COURSES;

（8）DML–改

–条件更新单条
UPDATE EXAM.COURSES
SET TEACHER=‘wangjianguo’,CREDIT_NUMBER=4.5
WHERE COURSE_ID = 1;

–批量更新，所有必修课学分+0.5
UPDATE EXAM.COURSES
SET CREDIT_NUMBER=CREDIT_NUMBER+0.5
WHERE COURSE_TYPE=1;

（9）DML–查

–全表查询
SELECT * FROM EXAM.COURSES;

–指定字段查询
SELECT COURSE_ID,COURSE_NAME,TEACHER FROM EXAM.COURSES;

–where条件查询
SELECT * FROM EXAM.COURSES WHERE CREDIT_NUMBER >=3.0 AND COURSE_TYPE=1;

–order by排序
SELECT * FROM EXAM.COURSES ORDER BY CREDIT_NUMBER DESC;

–聚合函数分组
SELECT COURSE_TYPE,COUNT(*) AS course_cnt,AVG(CREDIT_NUMBER) avg_credit
FROM EXAM.COURSES
GROUP BY COURSE_TYPE;

–分页查询LIMIT
SELECT * FROM EXAM.COURSES LIMIT 0,2;

–别名
SELECT c.COURSE_ID 课程号,c.COURSE_NAME 课程名 FROM EXAM.COURSES c;

2、索引

（1）创建

–普通B树单列索引
CREATE INDEX IDX_COURSES_TEACHER ON EXAM.COURSES(TEACHER);

–复合索引
CREATE INDEX IDX_COURSES_TYPE_CREDIT ON EXAM.COURSES(COURSE_TYPE,CREDIT_NUMBER);

–唯一索引
CREATE UNIQUE INDEX IDX_COURSES_NAME ON EXAM.COURSES(COURSE_NAME);

–局部/全局索引（分区表用法，普通表直接忽略GLOBAL）
CREATE INDEX IDX_GLOBAL_TEACHER ON EXAM.COURSES(TEACHER) GLOBAL;

（2）修改

–重建索引（整理碎片）
ALTER INDEX EXAM.IDX_COURSES_TEACHER REBUILD;

–重建索引并修改存储参数
ALTER INDEX EXAM.IDX_COURSES_TEACHER REBUILD STORAGE(INITIAL 2,NEXT 2);

–重命名索引
ALTER INDEX EXAM.IDX_COURSES_TEACHER RENAME TO IDX_COURSES_TEACHER_NEW;

（3）删除
DROP INDEX EXAM.IDX_COURSES_TEACHER_NEW;
DROP INDEX IF EXISTS EXAM.IDX_COURSES_NAME;

3、视图
（1）定义
–普通视图：查询必修课
CREATE VIEW EXAM.VW_COURSE_REQUIRED
AS
SELECT COURSE_ID,COURSE_NAME,CREDIT_NUMBER,TEACHER
FROM EXAM.COURSES
WHERE COURSE_TYPE = 1;

–带检查选项视图，防止插入不满足where条件的数据
CREATE VIEW EXAM.VW_COURSE_ELECTIVE
AS
SELECT COURSE_ID,COURSE_NAME,CREDIT_NUMBER,TEACHER
FROM EXAM.COURSES
WHERE COURSE_TYPE = 0
WITH CHECK OPTION;

（2）删除

DROP VIEW EXAM.VW_COURSE_REQUIRED;
DROP VIEW IF EXISTS EXAM.VW_COURSE_ELECTIVE;

（3）查询

–像查询表一样查询视图
SELECT * FROM EXAM.VW_COURSE_REQUIRED;

SELECT COURSE_NAME,TEACHER FROM EXAM.VW_COURSE_ELECTIVE WHERE CREDIT_NUMBER>2;

（4）编译

ALTER VIEW EXAM.VW_COURSE_REQUIRED COMPILE;

（5）更新

–通过视图修改底层表数据
UPDATE EXAM.VW_COURSE_REQUIRED
SET TEACHER=‘语文老师’
WHERE COURSE_ID=1;

–通过视图插入数据（vw_course_elective有check option，只能插入COURSE_TYPE=0的数据）
INSERT INTO EXAM.VW_COURSE_ELECTIVE(COURSE_ID,COURSE_NAME,CREDIT_NUMBER,TEACHER)
VALUES(6’dashuju’,2.0,‘liulaoshi’);

–通过视图删除
DELETE FROM EXAM.VW_COURSE_ELECTIVE WHERE COURSE_ID=6;

4、触发器

（1）创建

示例：课程表插入前触发器，学分不能大于 10
CREATE OR REPLACE TRIGGER EXAM.TRG_COURSES_BEFORE_INSERT
BEFORE INSERT ON EXAM.COURSES
FOR EACH ROW
BEGIN
IF :NEW.CREDIT_NUMBER > 10 THEN
RAISE_APPLICATION_ERROR(-20001,‘学分不能大于10’);
END IF;
END;

（2）替换

CREATE OR REPLACE 直接替换已有触发器
CREATE OR REPLACE TRIGGER EXAM.TRG_COURSES_BEFORE_INSERT
BEFORE INSERT ON EXAM.COURSES
FOR EACH ROW
BEGIN
IF :NEW.CREDIT_NUMBER > 10 OR :NEW.CREDIT_NUMBER <=0 THEN
RAISE_APPLICATION_ERROR(-20001,‘学分必须大于0且小于等于10’);
END IF;
END;

（3）删除

DROP TRIGGER EXAM.TRG_COURSES_BEFORE_INSERT;
DROP TRIGGER IF EXISTS EXAM.TRG_COURSES_BEFORE_INSERT;

（4）禁止与允许

–禁用触发器DISABLE
ALTER TRIGGER EXAM.TRG_COURSES_BEFORE_INSERT DISABLE;

–启用触发器ENABLE
ALTER TRIGGER EXAM.TRG_COURSES_BEFORE_INSERT ENABLE;

（5）重编

ALTER TRIGGER EXAM.TRG_COURSES_BEFORE_INSERT COMPILE;

5、序列

（1）创建

用于课程 ID 自增，供 COURSE_ID 使用
CREATE SEQUENCE EXAM.SEQ_COURSE_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 99999
MINVALUE 1
NOCYCLE
CACHE 20;

（2）获取值

–获取下一个值
SELECT EXAM.SEQ_COURSE_ID.NEXTVAL FROM DUAL;

–获取当前值
SELECT EXAM.SEQ_COURSE_ID.CURRVAL FROM DUAL;

（3）创建

–插入时直接使用序列
INSERT INTO EXAM.COURSES(COURSE_ID,COURSE_NAME,CREDIT_NUMBER,COURSE_TYPE,TEACHER)
VALUES(EXAM.SEQ_COURSE_ID.NEXTVAL,‘Python’,3.0,1,‘pythonlaoshi’);

（4）修改

–修改序列
ALTER SEQUENCE EXAM.SEQ_COURSE_ID INCREMENT BY 2;

（5）删除

–删除序列
DROP SEQUENCE EXAM.SEQ_COURSE_ID;

二、分区表

1、创建水平分区表

（1）创建范围分区表

创建通话记录表，按时间范围分区
按通话时间季度分区，使用 VALUES LESS THAN 定义区间，MAXVALUE 代表无穷大。
CREATE TABLE callinfo(
caller CHAR(15),
callee CHAR(15),
time DATETIME,
duration INT
)
PARTITION BY RANGE(time)(
PARTITION p1 VALUES LESS THAN (‘2010-04-01’),
PARTITION p2 VALUES LESS THAN (‘2010-07-01’),
PARTITION p3 VALUES LESS THAN (‘2010-10-01’),
PARTITION p4 VALUES EQU OR LESS THAN (MAXVALUE)
);

– 查询指定分区数据
SELECT * FROM callinfo PARTITION (p1);

（2）创建List分区表

– 销售记录表，按城市LIST分区
按城市离散值分区，适用于固定枚举字段。
CREATE TABLE sales(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY LIST(city)(
PARTITION p1 VALUES (‘北京’, ‘天津’),
PARTITION p2 VALUES (‘上海’, ‘南京’, ‘杭州’),
PARTITION p3 VALUES (‘武汉’, ‘长沙’),
PARTITION p4 VALUES (‘广州’, ‘深圳’)
);

（3）创建哈希分区表

数据均匀打散，两种写法：自定义分区名 / 指定分区数量。
– 写法1：自定义分区名称
CREATE TABLE sales01(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY HASH(city)(
PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);

– 写法2：指定分区数量+指定表空间，自动生成分区名dmhashpart0~3
CREATE TABLE sales02(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY HASH(city)
PARTITIONS 4 STORE IN (ts1, ts2, ts3, ts4);

– 查询哈希分区数据
SELECT * FROM sales02 PARTITION (dmhashpart0);

（4）创建多级分区表

一级按城市 LIST，二级按时间 RANGE，支持子分区模板，单分区可自定义子分区。
CREATE TABLE SALES(
SALES_ID INT,
SALEMAN CHAR(20),
SALEDATE DATETIME,
CITY CHAR(10)
)
PARTITION BY LIST(CITY)
SUBPARTITION BY RANGE(SALEDATE) SUBPARTITION TEMPLATE(
SUBPARTITION P11 VALUES LESS THAN (‘2012-04-01’),
SUBPARTITION P12 VALUES LESS THAN (‘2012-07-01’),
SUBPARTITION P13 VALUES LESS THAN (‘2012-10-01’),
SUBPARTITION P14 VALUES EQU OR LESS THAN (MAXVALUE)
)
(
PARTITION P1 VALUES (‘北京’, ‘天津’)
(
SUBPARTITION P11_1 VALUES LESS THAN (‘2012-10-01’),
SUBPARTITION P11_2 VALUES EQU OR LESS THAN (MAXVALUE)
),
PARTITION P2 VALUES (‘上海’, ‘南京’, ‘杭州’),
PARTITION P3 VALUES (DEFAULT)
);

2、建立索引

（1）隐式

规则：主键 / 唯一键包含全部分区键 → 局部索引；否则自动创建全局索引。
– 例1：HASH分区键c1，主键c2不包含分区键 → 自动生成全局索引
CREATE TABLE test(c1 INT, c2 INT PRIMARY KEY) PARTITION BY HASH(c1) PARTITIONS 2;

– 例2：主键c2是分区键(局部索引)，c3唯一不包含分区键(全局索引)
CREATE TABLE test2(c1 INT, c2 INT primary key,c3 int unique) PARTITION BY HASH(c2) PARTITIONS 2;

（2）显式

（CREATE INDEX 手动指定 GLOBAL / 局部）
– 1. 先创建RANGE分区表t1
create table t1(c1 int, c2 int, c3 int) partition by range(c1)
(
partition p1 values less than(100),
partition p2 values less than(200),
PARTITION p3 VALUES EQU OR LESS THAN (MAXVALUE)
);

– 2. 显式创建全局索引（加GLOBAL关键字）
create index idx1 on t1(c2) GLOBAL;

– 3. 显式创建局部索引（默认不加GLOBAL）
create index idx2 on t1(c3);

– 局部唯一索引：索引键必须包含全部分区键c1
CREATE UNIQUE INDEX ind_c1 ON t1(c1);
CREATE UNIQUE INDEX ind_c13 ON t1(c1,c3);

3、维护水平分区表

###（1）增加
仅支持 RANGE、LIST 分区；RANGE 只能在末尾新增，LIST 不能重复已有离散值。
– 1. RANGE分区新增季度分区，指定表空间ts5
ALTER TABLE callinfo
ADD PARTITION p5 VALUES LESS THAN (‘2011-4-1’) STORAGE (ON ts5);

– 2. LIST分区新增城市分区
ALTER TABLE sales
ADD PARTITION p5 VALUES (‘拉萨’, ‘呼和浩特’) STORAGE (ON ts5);

（2）删除

仅 RANGE、LIST 支持；仅剩 1 个分区时不可删除。
– 删除callinfo的p1分区，数据同步删除
ALTER TABLE callinfo DROP PARTITION p1;

（3）交换

分区与普通表互换数据，几乎无 IO，适合历史数据归档；两张表结构、索引顺序完全一致。
– 1. 创建归档普通表
CREATE TABLE callinfo_2011Q2(
caller CHAR(15),
callee CHAR(15),
time DATETIME,
duration INT
);

– 2. 分区p2与归档表交换数据
ALTER TABLE callinfo EXCHANGE PARTITION p2 WITH TABLE callinfo_2011Q2;

– 3. 交换后删除旧分区、新增新分区
ALTER TABLE callinfo DROP PARTITION p2;
ALTER TABLE callinfo ADD PARTITION p6 VALUES LESS THAN (‘2011-7-1’) STORAGE (ON ts2);

（4）合并

仅 RANGE、LIST；RANGE 必须相邻分区，合并后重建索引。
– 将p3、p4两个相邻RANGE分区合并为p3_4
ALTER TABLE callinfo MERGE PARTITIONS p3, p4 into partition p3_4;

（5）拆分

拆分超大分区，拆分值必须落在原分区区间内。
– 将合并后的p3_4拆分回p3、p4，分割点2010-09-30
ALTER TABLE callinfo SPLIT PARTITION p3_4 AT (‘2010-9-30’) INTO (PARTITION p3, PARTITION p4);