SQL 常用数据库的语法使用区别

SQL(结构化查询语言)是用于管理关系数据库的标准编程语言,用于数据库管理和数据操作。虽然基本的SQL语法大体相同,但不同的数据库系统(如MySQL、PostgreSQL、SQLite、Oracle和Microsoft SQL Server)在一些特定的语法和功能上存在细微的差别。

1. 数据定义语言(DDL)

SQL中,数据定义语言(DDL)的核心任务之一是创建数据库表,这个过程在不同的数据库管理系统(DBMS)中可能有细微的语法差异。

1)MySQL/MariaDB

CREATE TABLE table_name (
    column1 datatype CONSTRAINTS,
    column2 datatype CONSTRAINTS,
    ...
    PRIMARY KEY (one_or_more_columns),
    FOREIGN KEY (column) REFERENCES other_table(column),
    INDEX index_name (column)
);

MySQL支持多种存储引擎,如InnoDB、MyISAM等,在创建表时可以指定存储引擎。 提供丰富的数据类型,包括日期、时间、二进制等。

2) PostgreSQL

CREATE TABLE table_name (
    column1 datatype CONSTRAINTS,
    column2 datatype CONSTRAINTS,
    ...
    PRIMARY KEY (one_or_more_columns),
    FOREIGN KEY (column) REFERENCES other_table(column),
    UNIQUE (column)
);

PostgreSQL提供了更丰富的数据类型,如数组、JSON等。 支持创建表时指定表空间。 强大的约束定义和检查能力,包括表级和列级约束。

3)SQLite

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
    FOREIGN KEY (column) REFERENCES other_table(column)
);

SQLite的DDL相对简单,不支持一些复杂的数据类型和操作。 不支持在创建表时添加索引,必须在表创建完成后单独创建。 主键自动设为整数类型的ROWID。

4)Microsoft SQL Server

CREATE TABLE table_name (
    column1 datatype CONSTRAINTS,
    column2 datatype CONSTRAINTS,
    ...
    PRIMARY KEY (one_or_more_columns),
    FOREIGN KEY (column) REFERENCES other_table(column)
);

SQL Server支持定义计算列。 可以使用方案作为命名空间的一部分。 提供了丰富的数据类型和扩展功能。

5)Oracle

CREATE TABLE table_name (
    column1 datatype CONSTRAINTS,
    column2 datatype CONSTRAINTS,
    ...
    PRIMARY KEY (one_or_more_columns),
    FOREIGN KEY (column) REFERENCES other_table(column)
);

Oracle支持很多高级功能,如分区表、虚拟列等。 可以创建临时表,这些表中的数据对会话是私有的。 在表创建时支持更复杂的存储和索引选项。

2. 数据操作语言(DML)

在使用不同的数据库管理系统(DBMS)进行数据操作时,尤其是插入数据,不同系统之间存在一些语法和功能的差异。大多数数据库系统支持标准的 INSERT INTO 语法。但Oracle在插入数据时可能会更加严格,需要更加明确的数据类型转换。

1)MySQL/MariaDB

-- 基本插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
-- 批量插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;
-- 从另一表插入
INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM other_table;

2)PostgreSQL

-- 基本插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
-- 批量插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;
-- 从另一表插入
INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM other_table;
-- 插入后返回信息
INSERT INTO table_name (column1) VALUES (value1) RETURNING id;

3)SQLite

-- 基本插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
-- 批量插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;
-- 从另一表插入
INSERT INTO table_name SELECT * FROM other_table;

4)Microsoft SQL Server

-- 基本插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
-- 从另一表插入
INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM other_table;
-- 合并操作(插入与更新结合)
MERGE INTO target_table USING source_table ON target_table.id = source_table.id
WHEN MATCHED THEN 
  UPDATE SET target_table.column = source_table.column
WHEN NOT MATCHED THEN 
  INSERT (column1, column2) VALUES (value1, value2);

5)Oracle

-- 基本插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
-- 批量插入
INSERT ALL
  INTO table_name (column1, column2) VALUES (value1, value2)
  INTO table_name (column1, column2) VALUES (value3, value4)
SELECT * FROM dual;
-- 从另一表插入
INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM other_table;

6)日期和时间的处理

不同数据库系统获取当前日期和时间,如下,

数据库系统

日期和时间处理详细说明

MySQL

1)当前时间戳

NOW()

2)格式化

DATE_FORMAT(datetime, format)

3)提取日期

DATE(datetime)

4)日期加减

DATE_ADD(date, INTERVAL value unit)

DATE_SUB(date, INTERVAL value unit)

PostgreSQL

1)当前时间戳

NOW()

2)格式化

TO_CHAR(datetime, format)

3)提取日期

DATE(datetime)

4)日期加减

timestamp + INTERVAL '1 day'

SQLite

1)当前时间戳

datetime('now')

2)格式化

strftime(format, datetime)

3)提取日期

date(datetime)

4)日期加减

datetime(date, '+N days')

SQL Server

1)当前时间戳

GETDATE()

CURRENT_TIMESTAMP

2)格式化

FORMAT(datetime, format)

3)提取日期

CONVERT(date, datetime)

4)日期加减

DATEADD(unit, value, date)

DATEDIFF(unit, startdate, enddate)

Oracle

1)当前时间戳

SYSDATE

CURRENT_TIMESTAMP

2)格式化

TO_CHAR(datetime, format)

3)提取日期

EXTRACT(YEAR FROM date_column) 

EXTRACT(MONTH FROM date_column) 

EXTRACT(DAY FROM date_column)

4)日期加减

date + INTERVAL '1' DAY

3. 数据查询语言(DQL)

SQL(结构化查询语言)是用于管理关系数据库的标准语言,不同的数据库系统在SQL的支持和扩展上有所不同,尤其在数据查询语言(DQL)方面。

1)限制返回的记录数

不同的数据库管理系统(DBMS)中,数据查询语言(DQL)部分的语法尤其在限制返回记录数方面存在差异。如下表,

数据库系统

限制方法说明

MySQL / MariaDB

使用 LIMIT 子句来限制返回的记录数。

SELECT * FROM table_name LIMIT 10;

PostgreSQL

使用 LIMIT子句,与 MySQL

和 MariaDB 相同。

SELECT * FROM table_name LIMIT 10;

SQLite

使用 LIMIT 子句,与 PostgreSQL 和 MySQL 相同。

SELECT * FROM table_name LIMIT 10;

Microsoft SQL Server / Sybase

初始使用 TOP 关键字。

在 SQL Server 2012 及之后的版本中,

可使用 OFFSET-FETCH 子句。

SELECT TOP 10 * FROM table_name;

SELECT * FROM table_name 

ORDER BY column_name 

OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle

传统上使用 ROWNUM。从 Oracle 12c 开始,

可以使用 FETCH FIRST n ROWS ONLY

SELECT * FROM 

(SELECT * FROM table_name 

WHERE ROWNUM <= 10);

SELECT * FROM table_name 

FETCH FIRST 10 ROWS ONLY;

2)字符串函数和操作

不同的数据库管理系统(DBMS)中,数据查询语言(DQL)的字符串函数和操作可能会有所不同。这些差异可能涉及函数的命名、参数的接受方式以及一些额外功能的提供。

MySQL,使用CONCAT()函数或CONCAT_WS()进行字符串连接,并通过LENGTH()CHAR_LENGTH()分别获取字节长度和字符数。

-- 连接字符串
SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS('-', '2023', '04', '30');

-- 字符串长度
SELECT CHAR_LENGTH('text');

PostgreSQL主要使用||操作符来连接字符串,并使用LENGTH()函数测量字符串长度,同时还可以使用SUBSTRING()函数配合正则表达式提取特定模式的字符串。

-- 连接字符串
SELECT 'Hello' || ' ' || 'World';

-- 字符串长度
SELECT LENGTH('text');

-- 正则表达式提取
SELECT SUBSTRING('Sample text' FROM '%#"text#"%' FOR '#');
SQLite同样使用||操作符进行字符串连接,字符串长度的测定方法也是通过LENGTH()函数。
-- 连接字符串 SELECT 'Hello' || ' ' || 'World'; -- 字符串长度 SELECT LENGTH('text'); Microsoft SQL Server既提供+操作符也支持CONCAT()函数进行字符串连接,长度测定则通过LEN()函数。
-- 连接字符串 SELECT 'Hello' + ' ' + 'World'; SELECT CONCAT('Hello', ' ', 'World'); -- 字符串长度 SELECT LEN('text'); Oracle除了使用||操作符进行字符串连接和LENGTH()函数测定长度外,还支持通过SUBSTR()函数提取子字符串,并提供了多种字符串转换和操作函数,如INITCAP(), LOWER(), UPPER(), TRIM()等。这些功能强大的工具可以有效地处理和转换数据库中的文本数据。
-- 连接字符串 SELECT 'Hello' || ' ' || 'World'; -- 字符串长度 SELECT LENGTH('text'); -- 子字符串 SELECT SUBSTR('Hello World', 1, 5); -- 字符串转换和操作 SELECT INITCAP('hello world'); -- Converts first letter of each word to uppercase SELECT LOWER('HELLO WORLD'); -- Converts all characters to lowercase SELECT UPPER('hello world'); -- Converts all characters to uppercase SELECT TRIM(' Hello World '); -- Trims leading and trailing spaces

4. 事务处理

在数据库管理中,事务处理是一个非常重要的概念,确保数据的完整性和一致性。不同的数据库管理系统(DBMS)对事务处理的支持可能会有细微的差异,特别是在语法和功能上。大多数数据库系统都支持基本的事务语法 BEGIN, COMMITROLLBACK。但Oracle的默认行为是每条语句执行后自动提交,除非明确地开始了一个事务。

数据库

事务处理命令与设置

MySQL

1)开启事务:

START TRANSACTION

BEGIN

2)提交事务

COMMIT

3)回滚事务

ROLLBACK

4)设置事务隔离级别:

SET TRANSACTION ISOLATION LEVEL

PostgreSQL

1)开启事务

BEGIN

2)提交事务

COMMIT

3)回滚事务

ROLLBACK

4)设置事务隔离级别

SET TRANSACTION ISOLATION LEVEL

SQLite

1)开启事务

BEGIN TRANSACTION

BEGIN

2)提交事务

COMMIT

END

3)回滚事务

ROLLBACK

注意: 事务默认自动提交,不支持在语句中直接设置隔离级别

SQL Server

1)开启事务

BEGIN TRANSACTION

2)提交事务

COMMIT TRANSACTION

3)回滚事务

ROLLBACK TRANSACTION

4)设置事务隔离级别

SET TRANSACTION ISOLATION LEVEL

Oracle

1)开启事务

事务自动开始于第一个DML操作
提交事务:

COMMIT

2)回滚事务

ROLLBACK

3)设置事务隔离级别

通过会话或系统级别设置

4)特殊功能:

支持SAVEPOINT,可以回滚到指定点

推荐阅读
cjavapy编程之路首页