数据库DDL语句书写规范

数据库DDL语句书写规范

1.SQL语句编写说明
编写SQL语句应遵循统一的规范，包括大小写、空格、换行、缩进等等，只有完全一样的SQL才能在数据库中共享，从而减少硬解析。


字段类型、长度：根据数据情况定义合适的字段数据类型及长度。
对于无法确认字段长度的情况，采用变长的数据类型，比如使用varchar2代替char。char类型为固定长度，对于长度不足的数据会用空格补齐，这种情况下会造成空间浪费。对于日期时间数据，使用date数据类型，避免使用number、varchar2等数据类型存储转换后的日期，因为数据库优化器在分析该字段时无法知道这是日期、时间，按数字、字符类型分析出的结果可能与实际情况有较大偏差，从而造成执行计划选择不正确，影响性能。避免不必要的数据类型变换，比如字符类型的字段也可以用来存储数字类型数据，但数据库要做数据类型转换，因而增加了数据库负担。

分区表：对于较大的表应考虑使用分区。

2.新增数据库对象2.1创建数据表

-- Create table

create table MUB2C.PUBSALE_INFO
(
  ID      NUMBER(18) not null,
  PUB_ID NUMBER(18),
  FFP_NO VARCHAR2(20),
  PRICE  NUMBER(10,2),
  PUB_DT DATE
) tablespace MUB2C_DAT;

 

注意点：

①创建表时，应在表名前加上schema，表名和字段长度都不能超过30个字符。②number类型默认情况下,精度为38位,取值范围1-38之间。

③应为新建表指定数据表空间，生产库上数据表空间一般为：SCHEMA_DAT。schema：模式是一个数据库对象（如表、约束、索引、视图、序列等）的集合。

-- Add comments

COMMENT ON TABLE  MUB2C.PUBSALE_INFO
  IS '拍卖出价信息表';
COMMENT ON COLUMN MUB2C.PUBSALE_INFO.PUB_ID
  IS '拍卖产品ID';
COMMENT ON COLUMN MUB2C.PUBSALE_INFO.FFP_NO
  IS '竞拍用户编号';
COMMENT ON COLUMN MUB2C.PUBSALE_INFO.PRICE
  IS '出价';
COMMENT ON COLUMN MUB2C.PUBSALE_INFO.PUB_DT
  IS '竞拍日期';

 

注意点：
注释：创建表时，应同时对表和字段添加comment注释，表名前一定要加上schema。

-- Grant/Revoke object privileges 
GRANT SELECT ON MUB2C.PUBSALE_INFO TO R_SELMUB2C;
GRANT INSERT,UPDATE,DELETE ON MUB2C.PUBSALE_INFO TO R_UPDMUB2C;

注意点：
①表名前一定要加上schema。
②数据表创建后，需添加授权：
SELECT——将查看的权限授权给角色R_SELSCHEMA
INSERT,UPDATE,DELETE——将增删改的权限授权给角色R_UPDSCHEMA

2.2创建约束

-- Create/Recreate primary, unique and foreign key constraints

alter table MUB2C.PUBSALE_INFO
  add constraint PK_PUBSALE_INFO primary key (ID)
  using index 
  tablespace MUB2C_IDX;

alter table MUB2C.PUBSALE_INFO
  add constraint UK_PUBSALE_INFO unique (PUB_ID, FFP_NO)
  using index 
  tablespace MUB2C_IDX;

注意点：

①建表语句和建主键约束/唯一性约束语句要分开写，主键约束/唯一性约束一定不要写在建表中语句中。

②在创建主键约束/唯一性约束时，Oracle会自动为所使用的字段创建一个索引，因此无需再为该字段创建索引。

③应为新建约束指定索引表空间，生产库上索引表空间一般为：SCHEMA_IDX。

④为便于查找和区分，主键约束名称命名：PK_+主键约束名称，唯一性约束名称命名：UK_+唯一性约束名称，约束名称长度不能超过30个字符。

⑤表名前一定要加上schema。

alter table MUB2C.PUBSALE_INFO
  add constraint FK_PUBSALE_INFO_REF_PRODUCT foreign key (PUB_ID)
  references MUB2C.PUBSALE_PRODUCT (ID);

注意点：

①一般的外键约束会使用两个表进行关联（也存在同一个表自连接的情况）。外键是指“当前表”（即外键表）引用“另外一个表”（即被引用表）的某个列或某几个列，而“另外一个表”中被引用的列必须具有主键约束或者唯一性约束。

②外键约束名称命名：FK_…_REF_…，约束名称长度不能超过30个字符。

③外键表和被引用表的表名前一定要加上schema。

2.3创建索引
-- Create/Recreate indexes
create index MUB2C.IDX_PNR_INFO on MUB2C.PNR_INFO (PNR_NAME, PNR_CODE)tablespace MUB2C_IDX;

注意点：
①根据查询条件，选择选择性好的字段建立索引，对于多个字段的联合索引，将选择性好的字段放在前面。避免同一字段出现在多个索引中。
②索引名称命名：IDX_+索引名称，索引名长度不能超过30个字符。索引名称前一定要加上schema。
③应为新建索引指定索引表空间，生产库上索引表空间一般为：SCHEMA_IDX。
④表名前一定要加上schema。


2.4创建序列
--Create sequence
create sequence MUB2C.SEQ_CITYCUSS_ALL
minvalue 1
maxvalue 999999999999999999
start with 1
increment by 1
cache 20;

注意点：
①序列名称命名：SEQ_+序列名称，序列名长度不能超过30个字符。序列名前一定要加上schema。
②序列最大值为18个9。
③不建议在一个schema下创建多个序列，一般为2-3个。


-- Grant/Revoke object privileges 
GRANT SELECT ON MUB2C.SEQ_CITYCUSS_ALL TO R_SELMUB2C;

注意点：
数据表创建后，需添加授权：
SELECT——将查看的权限授权给角色R_SELSCHEMA



3.修改数据库对象
--重命名字段
ALTER TABLE EBSVRF.POINT_TASK_TRACK RENAME COLUMN PNRD_VERIFIED_NUM TO ET_VERIFIED_NUM;
comment on column EBSVRF.POINT_TASK_TRACK.ET_VERIFIED_NUM is '通过ET验证成行的旅客人数';
--增加字段
ALTER TABLE MUB2C.DS_INFO ADD DS_RANK NUMBER(18);
comment on column MUB2C.DS_INFO.DS_RANK is '记录点杀的名次';

--修改字段添加默认值

ALTER TABLE MUB2C.DS_INFO MODIFY CRT_DT DEFAULT SYSDATE;

--修改字段类型

ALTER TABLE MUB2C.DS_INFO MODIFY DS_RANK NUMBER(19);

--删除某个字段

ALTER TABLE MUB2C.DS_INFO DROP COLUMN TEST_DT;

注意点：
①修改数据库对象，在表名前一定要加上schema。
②如有重命名字段或增加字段，需为该字段添加注释。