oracle如何规范建表
实例如下:
drop table mmc.MBANK_DOWNLOAD_STATISTICS;
CREATE TABLE MMC.MBANK_DOWNLOAD_STATISTICS
(
ID VARCHAR2(50) NOT NULL ,--ID
DL_DATE VARCHAR29(32) ,--时间
BRAND VARCHAR2(100) ,--品牌
COUNTS NUMBER(10,0) ,--下载次数
CONSTRAINT PK_ID PRIMARY
KEY (ID) USING INDEX TABLESPACE MMCIDX
)
COMMENT ON TABLE MMC.MBANK_DOWNLOAD_STATISTICS IS '官网下载统计表';
COMMENT ON COLUMN MMC.MBANK_DOWNLOAD_STATISTICS.ID IS 'ID';
COMMENT ON COLUMN MMC.MBANK_DOWNLOAD_STATISTICS.DL_DATE IS '时间';
COMMENT ON COLUMN MMC.MBANK_DOWNLOAD_STATISTICS.BRAND IS '品牌';
COMMENT ON COLUMN MMC.MBANK_DOWNLOAD_STATISTICS.COUNTS IS '下载次数';
CREATE INDEX DL_DATE_INDEX ON MMC.MBANK_DOWNLOAD_STATISTICS(DL_DATE) TABLESPACE MMCIDX;
CREATE INDEX BRAND_INDEX ON MMC.MBANK_DOWNLOAD_STATISTICS(BRAND) TABLESPACE MMCIDX;
逐句解释:
为了建表顺利,第一步是先删除表,保证能创建成功!!
drop table mmc.MBANK_DOWNLOAD_STATISTICS;
这一句是删表语句
然后就是耳熟能详的建表:
CREATE TABLE MMC.MBANK_DOWNLOAD_STATISTICS
(
ID VARCHAR2(50) NOT NULL ,--ID
DL_DATE VARCHAR29(32) ,--时间
BRAND VARCHAR2(100) ,--品牌
COUNTS NUMBER(10,0) ,--下载次数
CONSTRAINT PK_ID PRIMARY
KEY (ID) USING INDEX TABLESPACE MMCIDX
)
这里基本上都是熟悉的,除了CONSTRAINT 的应用的,上面是为了给主键加索引!!
给表和字段加注释
COMMENT ON TABLE MMC.MBANK_DOWNLOAD_STATISTICS IS '官网下载统计表';
COMMENT ON COLUMN MMC.MBANK_DOWNLOAD_STATISTICS.ID IS 'ID';
COMMENT ON COLUMN MMC.MBANK_DOWNLOAD_STATISTICS.DL_DATE IS '时间';
COMMENT ON COLUMN MMC.MBANK_DOWNLOAD_STATISTICS.BRAND IS '品牌';
COMMENT ON COLUMN MMC.MBANK_DOWNLOAD_STATISTICS.COUNTS IS '下载次数';
增加索引
给DL_DAT和BRAND增加索引
CREATE INDEX DL_DATE_INDEX ON MMC.MBANK_DOWNLOAD_STATISTICS(DL_DATE) TABLESPACE MMCIDX;
CREATE INDEX BRAND_INDEX ON MMC.MBANK_DOWNLOAD_STATISTICS(BRAND) TABLESPACE MMCIDX;