近期项目中会用到oracle,mysql,两者的建表sql 类型,函数都会混淆。现在特意整理一下:
mysql :now(), CONCAT("","",""),int , varchar ,AUTO_INCREMENT
oracle :sysdate, || ,number, varchar2 ,sequence
一 mysql
CREATE TABLE `cat_egory_group` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`cid` int(11) DEFAULT NULL COMMENT '类目ID',
`gid` int(11) DEFAULT NULL COMMENT '分组ID',
`status` int(11) DEFAULT NULL COMMENT '状态1:正常 0删除',
`createor` varchar(50) DEFAULT NULL,
`gmtDate` date DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET=gbk COMMENT='类目分组表'
查看建表sql show create table tablename
二 oracle
1. -- Create table
create table CAT_CATEGORY
(
cat_id NUMBER not null,
cat_name VARCHAR2(200),
bu_id NUMBER,
bu_name VARCHAR2(200),
level_id NUMBER,
parent_cat_id NUMBER,
status NUMBER,
add_flag NUMBER,
is_cat NUMBER,
memo VARCHAR2(200)
)
-- Add comments to the table
comment on table CAT_CATEGORY
is '类目表';
-- Add comments to the columns
comment on column CAT_CATEGORY.cat_id
is '类目ID';
comment on column CAT_CATEGORY.cat_name
is '类目名称';
comment on column CAT_CATEGORY.bu_id
is 'BU的ID';
comment on column CAT_CATEGORY.bu_name
is 'BU名称';
comment on column CAT_CATEGORY.level_id
is '级别';
comment on column CAT_CATEGORY.parent_cat_id
is '父节点(0 为最上层节点)';
comment on column CAT_CATEGORY.status
is '状态值1 启用; 0 未启用; 2 中间过程';
comment on column CAT_CATEGORY.add_flag
is '是否参与汇总1 是;0 否(默认1)';
comment on column CAT_CATEGORY.is_cat
is '是否类目1 是;0 否';
comment on column CAT_CATEGORY.memo
is '备注';
concat :select * from test where CODE like concat ('%',#code#,'%')
添加列
alter table cat_category add test_column nvarchar2(200);
修改列名
alter table cat_category rename column test_column to test_column_new;
修改列类型 (有数据和无数据)
a .无数据 alter table cat_category modify test_column_new number;
b 有数据
- alter table tb_test add permile_temp number(5,2)
- update tb_test set permilepermile_temp=permile;
- alter table drop column permile;
- alter table test rename column permile_temp to permile
删除列
alter table cat_category drop column test_column_new;
2.序列
create sequence sequence_id
start with 1
increment by 1
minvalue 1
maxvalue 1000000000000
nocache;
select sequence_id.nextval from dual;
select sequence_id.currval from dual;
drop sequence sequence_id;