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

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）日期和时间的处理

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

3. 数据查询语言（DQL）

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

1）限制返回的记录数

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

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, COMMIT 和 ROLLBACK。但Oracle的默认行为是每条语句执行后自动提交，除非明确地开始了一个事务。