常见oracle ,mysql 建表语句及索引创建

oracle:

-- Create sequence sql.nextval
create sequence supplier_head_seq minvalue 1 maxvalue 99999999
increment by 1
start with 1;

-- Create table
create table supplier_modify_head
(
id NUMBER NOT NULL ,
elsaccount VARCHAR2(255),
toelsaccount VARCHAR2(255),
order_no VARCHAR2(64),
create_user VARCHAR2(255),
create_id VARCHAR2(64),
update_user VARCHAR2(255),
update_id VARCHAR2(64),
audit_user VARCHAR2(255),
audit_id VARCHAR2(64),
status NUMBER default 0,
auditstatus NUMBER,
stretegycode VARCHAR2(255),
flowcode VARCHAR2(255),
create_time DATE ,
update_time DATE ,
fbk1 VARCHAR2(255),
fbk2 VARCHAR2(255),
fbk3 VARCHAR2(255),
fbk4 VARCHAR2(255),
fbk5 VARCHAR2(255)
)
tablespace SRM_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on table supplier_modify_head is '供应商数据修改头表';
comment on column supplier_modify_head.toelsaccount
is '供应商SRM编码';
comment on column supplier_modify_head.order_no
is '单据号';
comment on column supplier_modify_head.create_user
is '创建人';
comment on column supplier_modify_head.update_user
is '更新人';
comment on column supplier_modify_head.audit_user
is '审核人';
comment on column supplier_modify_head.status
is '数据状态 默认0正常,1删除';
comment on column supplier_modify_head.auditstatus
is '审核状态';
comment on column supplier_modify_head.create_time
is '创建时间';
comment on column supplier_modify_head.update_time
is '更新时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table supplier_modify_head
add primary key (id)
using index
tablespace SRM_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

--create index
create index idx_smh_order_no on supplier_modify_head(order_no);
create index idx__smh_create_user on supplier_modify_head(create_user);
create index idx__smh_auditstatus on supplier_modify_head(auditstatus);

联合索引：create index idx_dept_dname_deptno on dept(dname, deptno);

---唯一索引
create unique index idx_emp_ename on scott.emp(ename);

 

mysql：

CREATE TABLE `hwxc_limit_goods_list` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`order_no` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT '单号',
`status` int(1) NOT NULL DEFAULT '0' COMMENT '数据状态(0 正常 1 删除)',
`remark` varchar(524) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '备注',
`remark1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '备用字段1',
`remark2` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '备用字段2',
`create_user_id` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '创建人ID',
`create_user` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_user` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_order_no` (`order_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='限制发货表清单表'