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=?;

From:http://tw.wingwit.com/Article/program/Oracle/201311/19025.html