oracle,sqlserver,MySQL数据库语句对照表

创建表语句
ORACLE:
create table _table_name(
column varchar() primary key
column number() not null
memo varchar()
);
comment on column _table_namecolumn
is 这是column的注释;
SQLSERVER:
create table _table_name(
column varchar() primary key
column int not null
memo varchar()
);
MYSQL:
create table `_table_name`
(
`column` VARCHAR() primary key COMMENT 注释
`column` VARCHAR() not null COMMENT 注释
PRIMARY KEY (`column`) 主键定义也可放在此处
)ENGINE=InnoDB DEFAULT CHARSET=gbk;

修改字段语句
ORACLE:
alter table _table_name add/modify column_name varchar();
alter table _table_name drop column column_name;
SQLSERVER:
alter table _table_name add column_name VARCHAR();
alter table _table_name alter column column_name varchar();
alter table _table_name drop column column_name;
MYSQL:
alter table _table_name add/modify column column_name varchar();
alter table _table_name drop `column_name`;

创建删除索引语句索引只能删除重建不能修改
ORACLE:
主键索引
alter table _table_name add constraint index_name primary key (column_name) using index tablespace URMSPK;
普通列索引
create index index_name$cl on _table_name (column_namecolumn_name DESC) tablespace URMSIDX;
删除索引
drop index index_name;
SQLSERVER:
主键索引
Alter table _table_name add primary key(column_name);
alter table _table_name add constraint index_name primary key CLUSTERED (column_name)
WITH (
PAD_INDEX = OFF
IGNORE_DUP_KEY = OFF
STATISTICS_NORECOMPUTE = OFF
ALLOW_ROW_LOCKS = ON
ALLOW_PAGE_LOCKS = ON)
ON URMSPK
go

普通列索引 非唯一索引需要去掉 UNIQUE NONCLUSTERED 关键字
CREATE UNIQUE NONCLUSTERED INDEX [index_name] ON [_table_name]
([ORGRANGE] [SHOWORDER] DESC)
WITH (
PAD_INDEX = OFF
IGNORE_DUP_KEY = OFF
DROP_EXISTING = OFF
STATISTICS_NORECOMPUTE = OFF
SORT_IN_TEMPDB = OFF
ONLINE = OFF
ALLOW_ROW_LOCKS = ON
ALLOW_PAGE_LOCKS = ON)
ON [URMSIDX]
GO
删除索引
drop index _table_nameidxname;
MYSQL:
普通索引
ALTER TABLE _table_name ADD INDEX index_name (APPID CREATEDATE DESC);
唯一索引
ALTER TABLE _table_name ADD UNIQUE index_name (column_list);
主键索引
ALTER TABLE _table_name ADD PRIMARY KEY index_name (column_list);

删除索引
alter table _table_name drop index index_name;

插入语句
ORACLE:
insert into _table_name (column_list) values (value_list);
SQLSERVER:
insert into _table_name (column_list) values (value_list);
MYSQL:
insert into UMFRAMESET (`column_list`) values (value_list)(value_list); 可以插入多条记录

修改表名
ORACLE:
alter table leave rename to Leave;
SQLSERVER:
EXEC sp_rename leaveleave;
MYSQL:
alter table `leave` RENAME to `leave`;
删除表语句
ORACLE:
drop table table_name;
SQLSERVER:
drop table table_name;
MYSQL:
drop table table_name
删除所有表的语句
SQLSERVER:
exec sp_msforeachtable drop table ?;
修改列名
SQLSERVER:
EXEC sp_rename 表名列名新列名column;
删除记录
ORACLE:
delete (from) tablename where _column_name=?;