各种数据库的SQL的不同点-Mysql Tutorial-php.cn

今天复习数据库的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) Copy after login FROM table_name Copy after login Copy after login Copy after login 2. MySql的语法：

SELECT column_name(s) Copy after login Copy after login FROM table_name Copy after login Copy after login Copy after login LIMIT number Copy after login 3. Oracle的语法：

SELECT column_name(s) Copy after login Copy after login FROM table_name Copy after login Copy after login Copy after login WHERE ROWNUM 注意:oracle ROWNUM不支持">="

· SQL UNIQUE 约束 1. MySQL:

CREATE TABLE Persons Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login ( Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Id_P int NOT NULL, Copy after login Copy after login LastName varchar(255) NOT NULL, Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login FirstName varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Address varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login City varchar(255), Copy after login Copy after login Copy after login UNIQUE(Id_P) Copy after login 2. SQL Server / Oracle / MS Access:

CREATE TABLE Persons Copy after login Copy after login Copy after login Copy after login Copy after login ( Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Id_P int NOT NULL UNIQUE, Copy after login LastName varchar(255) NOT NULL, Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login FirstName varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Address varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login City varchar(255) Copy after login Copy after login Copy after login ) Copy after login Copy after login Copy after login Copy after login Copy after login 3. MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons Copy after login Copy after login Copy after login Copy after login Copy after login ( Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Id_P int NOT NULL, Copy after login Copy after login LastName varchar(255) NOT NULL, Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login FirstName varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Address varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login City varchar(255), Copy after login Copy after login Copy after login CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) Copy after login ) Copy after login Copy after login Copy after login Copy after login Copy after login · SQL UNIQUE Constraint on ALTER TABLE MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons Copy after login Copy after login Copy after login Copy after login ADD UNIQUE (P_Id) Copy after login 或 Copy after login Copy after login ALTER TABLE Persons Copy after login Copy after login Copy after login Copy after login ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) Copy after login 撤销 UNIQUE 约束 1. MySql:

ALTER TABLE Persons Copy after login Copy after login Copy after login Copy after login DROP INDEX uc_PersonID Copy after login 2. SQL Server / Oracle / MS Access:

ALTER TABLE Persons Copy after login Copy after login Copy after login Copy after login DROP CONSTRAINT uc_PersonID Copy after login 这里只提供UNIQUE约束，PRIMARY KEY约束、FOREIGN KEY约束、CHECK约束类似。

· SQL AUTO INCREMENT 字段

1. MySql:

CREATE TABLE Persons Copy after login Copy after login Copy after login Copy after login Copy after login ( Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login P_Id int NOT NULL AUTO_INCREMENT, Copy after login LastName varchar(255) NOT NULL, Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login FirstName varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Address varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login City varchar(255), Copy after login Copy after login Copy after login PRIMARY KEY (P_Id) Copy after login ) Copy after login Copy after login Copy after login Copy after login Copy after login 2. SQL Server:

CREATE TABLE Persons Copy after login Copy after login Copy after login Copy after login Copy after login ( Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login P_Id int PRIMARY KEY IDENTITY, Copy after login LastName varchar(255) NOT NULL, Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login FirstName varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Address varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login City varchar(255) Copy after login Copy after login Copy after login ) Copy after login Copy after login Copy after login Copy after login Copy after login 3.Access:

CREATE TABLE Persons Copy after login Copy after login Copy after login Copy after login Copy after login ( Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login P_Id int PRIMARY KEY AUTOINCREMENT, Copy after login LastName varchar(255) NOT NULL, Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login FirstName varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Address varchar(255), Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login City varchar(255) Copy after login Copy after login Copy after login ) Copy after login Copy after login Copy after login Copy after login Copy after login 4. Oracle:

CREATE SEQUENCE seq_person Copy after login MINVALUE 1 Copy after login START WITH 1 Copy after login INCREMENT BY 1 Copy after login CACHE 10 Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login INSERT INTO Persons (P_Id,FirstName,LastName) Copy after login VALUES (seq_person.nextval,'Lars','Monsen') Copy after login NULL 函数 1. SQL server/ MS Access

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) Copy after login FROM Products Copy after login Copy after login Copy after login Copy after login 2. Oracle

SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) Copy after login FROM Products Copy after login Copy after login Copy after login Copy after login 3.MySql

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) Copy after login FROM Products Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login 或 Copy after login Copy after login Copy after login Copy after login Copy after login Copy after login SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0)) Copy after login FROM Products Copy after login Copy after login Copy after login Copy after login 作者: Dxx23