SQL differences of various databases

I want to summarize the differences between the SQL statements of various databases for your reference! Number of returned records 1. SQLServer Syntax: SELECTTOPnumber | percentcolumn_name (s) FROMtable_name2.MySql Syntax: SELECTcolumn_name (s) FROMtable_nameLI

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 LI

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)

• Undo UNIQUE Constraints

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')

• NULL Function

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