收集的各种数据库的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
LIMIT number

 

3. Oracle的语法：

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number

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

补充：

求薪水最高的第6到第10名雇员：
SELECT ename,sal  FROM
(SELECT ename,sal,rownum r FROM
     (SELECT ename,sal FROM emp ORDER BY sal DESC)
 )WHERE r>=6 AND r<=10;

 

 

• 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

 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