Reprinted
I want to summarize the differences between the SQL statements of various databases for your reference!
- Number of returned records
1. SQL Server Syntax:
Select top number | percent column_name (s)
From table_name
2. MySQL Syntax:
Select column_name (s)
From table_name
Limit number
3. Oracle Syntax:
Select column_name (s)
From table_name
Where rownum <= Number
Note: Oracle rownum does not support "> ="
· SQL unique constraints
1. MySQL:
Create Table persons
(
Id_p int not null,
Lastname varchar (255) not null,
Firstname varchar (255 ),
Address varchar (255 ),
City varchar (255 ),
Unique (id_p)
2. SQL Server/Oracle/MS access:
Create Table persons
(
Id_p int not null unique,
Lastname varchar (255) not null,
Firstname varchar (255 ),
Address varchar (255 ),
City varchar (255)
)
3. MySQL/SQL Server/Oracle/MS access:
Create Table persons
(
Id_p int not null,
Lastname varchar (255) not null,
Firstname varchar (255 ),
Address varchar (255 ),
City varchar (255 ),
Constraint uc_personid unique (id_p, lastname)
)
· SQL unique constraint on ALTER TABLE
For MySQL/SQL Server/Oracle/MS access:
Alter table persons
Add unique (p_id)
Or
Alter table persons
Add constraint uc_personid unique (p_id, lastname)
1. MySQL:
Alter table persons
Drop index uc_personid
2. SQL Server/Oracle/MS access:
Alter table persons
Drop constraint uc_personid
Only the unique constraint is provided here. The primary key constraint, foreign key constraint, and check constraint are similar.
·SQL auto Increment Field
1. MySQL:
Create Table persons
(
P_id int not null auto_increment,
Lastname varchar (255) not null,
Firstname varchar (255 ),
Address varchar (255 ),
City varchar (255 ),
Primary Key (p_id)
)
2. SQL Server:
Create Table persons
(
P_id int primary key identity,
Lastname varchar (255) not null,
Firstname varchar (255 ),
Address varchar (255 ),
City varchar (255)
)
3. Access:
Create Table persons
(
P_id int primary key autoincrement,
Lastname varchar (255) not null,
Firstname varchar (255 ),
Address varchar (255 ),
City varchar (255)
)
4. Oracle:
Create sequence seq_person
Minvalue 1
Start with 1
Increment by 1
Cache 10
Insert into persons (p_id, firstname, lastname)
Values (seq_person.nextval, 'lars', 'monsen ')
1. Web SQL Server/MS access
Select productname, unitprice * (unitsinstock + isnull (unitsonorder, 0 ))
From Products
2. Oracle
Select productname, unitprice * (unitsinstock + nvl (unitsonorder, 0 ))
From Products
3. MySQL
Select productname, unitprice * (unitsinstock + ifnull (unitsonorder, 0 ))
From Products
Or
Select productname, unitprice * (unitsinstock + coalesce (unitsonorder, 0 ))
From Products
author: dxx23
statement: This article is an original Article published by the javaeye website. without the written consent of the author, it is strictly prohibited to repost this article on any website; otherwise, we will be held legally responsible!