| 标题 | oracle,sqlserver,MySQL数据库语句对照表 |
| 内容 | -- 创建表语句 ORACLE: create table _table_name( column1 varchar2(10) primary key, column2 number(5) not null, memo varchar2(100) ); comment on column _table_name.column1 is '这是column1的注释'; SQLSERVER: create table _table_name( column1 varchar(10) primary key, column2 int not null, memo varchar(100) ); MYSQL: create table `_table_name` ( `column1` VARCHAR(32) primary key COMMENT '注释', `column2` VARCHAR(30) not null COMMENT '注释', PRIMARY KEY (`column1`) -- 主键定义也可放在此处 )ENGINE=InnoDB DEFAULT CHARSET=gbk; -- 修改字段语句 ORACLE: alter table _table_name add/modify column_name varchar2(505); alter table _table_name drop column column_name; SQLSERVER: alter table _table_name add column_name VARCHAR(20); alter table _table_name alter column column_name varchar(2000); alter table _table_name drop column column_name; MYSQL: alter table _table_name add/modify column column_name varchar(2000); 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$cl2 on _table_name (column1_name,column2_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_name.idxname; 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_list2');-- 可以插入多条记录 -- 修改表名 ORACLE: alter table leave rename to Leave01; SQLSERVER: EXEC sp_rename leave,leave01; MYSQL: alter table `leave` RENAME to `leave01`; -- 删除表语句 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=?; |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。