创建表:
CREATE TABLE {表名}({字段名1} {字段类型} ,{字段名2} {字段类型}… );
CREATE TABLE L_CONFIG_LIST
(
L_ID NUMBER NOT NULL,
F_ID VARCHAR2(30),
F_NAME VARCHAR2(100),
F_GRADE VARCHAR2(30),
L_UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL,
L_OPERATE_FLAG VARCHAR2(2),
L_BATCH_NO NUMBER
);
增加表的说明:
COMMENT ON TABLE {表名} IS ‘{表的说明}’;
COMMENT ON TABLE L_CONFIG_LIST
IS '配置名称表';
修改表名:
ALTER TABLE {原表名} RENAME TO {新表名} ;
ALTER TABLE L_CONFIG_LIST RENAME TO T_CONFIG_LIST;
增加字段说明:
COMMENT ON COLUMN {表名}.{字段名} IS ‘{字段的说明}’;
COMMENT ON COLUMN L_CONFIG_LIST.F_ID
IS '配置ID';
创建sequence:
CREATE SEQUENCE SEQ_L_CONFIG_LIST MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 NOCACHE;
创建主键:
ALTER TABLE {表名} ADD PRIMARY KEY ({字段名});
ALTER TABLE L_CONFIG_LIST ADD PRIMARY KEY (L_ID);
创建索引:
CREATE INDEX {索引名称} ON {表名} ({字段名1},{字段名2}…);
CREATE INDEX IDX_L_CONFIG_LIST_1 ON L_CONFIG_LIST (F_ID, F_GRADE);
添加字段:
ALTER TABLE {表名} ADD {字段名} {字段类型}({长度(如果需要)});
ALTER TABLE L_CONFIG_LIST ADD REMARK VARCHAR2(200);
删除字段:
ALTER TABLE {表名} DROP COLUMN {字段名};
ALTER TABLE L_CONFIG_LIST DROP COLUMN REMARK;
重命名表:
ALTER TABLE {原表名} RENAME TO {新表名};
ALTER TABLE L_CONFIG_LIST RENAME TO L_CONFIG_LIST_NEW;
字段重命名:
ALTER TABLE {表名} RENAME COLUMN {原字段名} TO {新字段名};
ALTER TABLE L_CONFIG_LIST RENAME COLUMN REMARK TO REMARK_NEW;
修改字段类型:
ALTER TABLE {表名} MODIFY {字段名} {字段类型}({长度(如果需要)});
ALTER TABLE L_CONFIG_LIST MODIFY REMARK NUMBER(5,2);