当前位置:Gxlcms > mysql > 各种数据库的SQL的不同点

各种数据库的SQL的不同点

时间:2021-07-01 10:21:17 帮助过:9人阅读

今天复习数据库的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

人气教程排行