Statement comparison table for oracle, sqlserver, and MySQL Databases

-- Create table statement
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 'This is the comment of column1 ';
SQLSERVER:
Create table _ table_name (
Column1 varchar (10) primary key,
Column2 int not null,
Apsaradb for memo varchar (100)
);
MYSQL:
Create table '_ table_name'
(
'Column1 'varchar (32) primary key comment' COMMENT ',
'Column2 'varchar (30) not null comment' COMMENT ',
Primary key ('column1 ') -- The primary key definition can also be placed here
) ENGINE = InnoDB default charset = gbk;

-- Modify the field statement
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 ';

-- Create and delete an index statement. The index can only be deleted and rebuilt and cannot be modified.
ORACLE:
-- Primary Key Index
Alter table _ table_name add constraint index_name primary key (column_name) using index tablespace URMSPK;
-- Normal column Index
Create index index_name $ cl2 on _ table_name (column1_name, column2_name DESC) tablespace URMSIDX;
-- Delete an index
Drop index index_name;
SQLSERVER:
-- Primary Key Index
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

-- Normal column index. The keyword unique nonclustered must be removed for non-UNIQUE indexes.
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
-- Delete an index
Drop index _ table_name.idxname;
MYSQL:
-- Common Index
Alter table _ table_name add index index_name (APPID, createdate desc );
-- Unique index
Alter table _ table_name add unique index_name (column_list );
-- Primary Key Index
Alter table _ table_name add primary key index_name (column_list );
 

-- Delete an index
Alter table _ table_name drop index index_name;

 

-- Insert statement
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'); -- multiple records can be inserted.

-- Modify Table Name
ORACLE:
Alter table leave rename to Leave01;
SQLSERVER:
EXEC sp_rename leave, leave01;
MYSQL:
Alter table 'Leave 'rename to 'leave01 ';
-- Delete a table statement
ORACLE:
Drop table table_name;
SQLSERVER:
Drop table table_name;
MYSQL:
Drop table table_name
-- Delete statements for all tables
SQLSERVER:
Exec sp_msforeachtable 'drop table? ';
-- Modify column name
SQLSERVER:
EXEC sp_rename 'table name. column name', 'new column name', 'column ';
-- Delete record
ORACLE:
Delete (from) tablename where _ column_name = ?;