各种数据库的SQL的不同点

今天复习数据库的SQL，想着需要总结下各种数据库的SQL的不同点，供总结查阅，供别人参考！ 返回的记录的数目 1. SQL Server 的语法： SELECT TOP number|percent column_name(s) FROM table_name 2. MySql的语法： SELECT column_name(s) FROM table_name LI

今天复习数据库的SQL，想着需要总结下各种数据库的SQL的不同点，供总结查阅，供别人参考！

• 返回的记录的数目

1. SQL Server 的语法：

SELECT TOP number|percent column_name(s)

FROM table_name

2. MySql的语法：

SELECT column_name(s)

FROM table_name

LIMIT number

3. Oracle的语法：

SELECT column_name(s)

FROM table_name

WHERE ROWNUM <= number

注意:oracle ROWNUM不支持">="

· SQL UNIQUE 约束

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

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons

ADD UNIQUE (P_Id)

或

ALTER TABLE Persons

ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

• 撤销 UNIQUE 约束

1. MySql:

ALTER TABLE Persons

DROP INDEX uc_PersonID

2. SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT uc_PersonID

这里只提供UNIQUE约束，PRIMARY KEY约束、FOREIGN KEY约束、CHECK约束类似。

· SQL AUTO INCREMENT 字段

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 函数

1. 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

或

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))

FROM Products

作者: Dxx23