领先的免费Web技术教程,涵盖HTML到ASP.NET

网站首页 > 知识剖析 正文

【SQL】SQL 语法差异大全(PgSQL/MySQL/Oracle/TiDB/OceanBase)

nixiaole 2025-05-09 20:52:58 知识剖析 2 ℃

以下是针对不同数据库系统的 SQL 语法差异总结,按功能分类展示:

一、基础查询

1. 分页查询

-- PostgreSQL/TiDB/OceanBase
SELECT * FROM TableName LIMIT 10 OFFSET 20;

-- MySQL
SELECT * FROM TableName LIMIT 20, 10;

-- Oracle
SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM (
    SELECT * FROM TableName ORDER BY column1
  ) t WHERE ROWNUM <= 30
) WHERE rn > 20;

2. 字符串连接

-- PostgreSQL/TiDB/OceanBase
SELECT column1 || ' ' || column2 FROM TableName;

-- MySQL
SELECT CONCAT(column1, ' ', column2) FROM TableName;

-- Oracle
SELECT column1 || ' ' || column2 FROM TableName;
-- 或
SELECT CONCAT(CONCAT(column1, ' '), column2) FROM TableName;

二、日期函数

1. 当前日期时间

-- PostgreSQL/TiDB
SELECT NOW();
SELECT CURRENT_TIMESTAMP;

-- MySQL
SELECT NOW();
SELECT CURRENT_TIMESTAMP();
SELECT SYSDATE();

-- Oracle
SELECT SYSDATE FROM dual;
SELECT CURRENT_TIMESTAMP FROM dual;

-- OceanBase
SELECT NOW();
SELECT CURRENT_TIMESTAMP;

2. 日期加减

-- PostgreSQL/TiDB/OceanBase
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '1 hour';

-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- Oracle
SELECT SYSDATE + 1 FROM dual;
SELECT SYSDATE - 1/24 FROM dual;

三、类型转换

1. 字符串转日期

-- PostgreSQL/TiDB/OceanBase
SELECT CAST('2023-01-01' AS DATE);
SELECT '2023-01-01'::DATE;

-- MySQL
SELECT CAST('2023-01-01' AS DATE);
SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d');

-- Oracle
SELECT TO_DATE('2023-01-01', 'YYYY-MM-DD') FROM dual;

2. 数字格式化

-- PostgreSQL/TiDB/OceanBase
SELECT TO_CHAR(1234.56, '9999.99');

-- MySQL
SELECT FORMAT(1234.56, 2);

-- Oracle
SELECT TO_CHAR(1234.56, '9999.99') FROM dual;

四、窗口函数

1. ROW_NUMBER()

-- 所有数据库通用语法
SELECT column1, column2, 
       ROW_NUMBER() OVER (ORDER BY column2) AS rn
FROM TableName;

2. 分页查询(使用窗口函数)

-- PostgreSQL/TiDB/OceanBase/MySQL 8.0+
SELECT * FROM (
  SELECT t.*, 
         ROW_NUMBER() OVER (ORDER BY column1) AS rn
  FROM TableName t
) WHERE rn BETWEEN 21 AND 30;

-- Oracle
SELECT * FROM (
  SELECT t.*, 
         ROW_NUMBER() OVER (ORDER BY column1) AS rn
  FROM TableName t
) WHERE rn BETWEEN 21 AND 30;

五、DDL 语句

1. 自增列

-- PostgreSQL
CREATE TABLE TableName (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50)
);

-- MySQL/TiDB/OceanBase
CREATE TABLE TableName (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

-- Oracle
CREATE TABLE TableName (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(50)
);

2. 修改列

-- PostgreSQL/TiDB/OceanBase
ALTER TABLE TableName ALTER COLUMN column1 TYPE VARCHAR(100);

-- MySQL
ALTER TABLE TableName MODIFY COLUMN column1 VARCHAR(100);

-- Oracle
ALTER TABLE TableName MODIFY column1 VARCHAR2(100);

六、索引操作

1. 创建索引

-- PostgreSQL/TiDB/OceanBase
CREATE INDEX idx_name ON TableName (column1);

-- MySQL
CREATE INDEX idx_name ON TableName (column1);

-- Oracle
CREATE INDEX idx_name ON TableName (column1);

2. 函数索引

-- PostgreSQL
CREATE INDEX idx_name ON TableName (LOWER(column1));

-- MySQL 8.0+/TiDB/OceanBase
CREATE INDEX idx_name ON TableName ((LOWER(column1)));

-- Oracle
CREATE INDEX idx_name ON TableName (LOWER(column1));

七、事务控制

1. 事务语法

-- PostgreSQL/MySQL/TiDB/OceanBase
START TRANSACTION;
-- SQL语句
COMMIT;
-- 或
ROLLBACK;

-- Oracle
SET TRANSACTION;
-- SQL语句
COMMIT;
-- 或
ROLLBACK;

八、特殊功能

1. JSON 处理

-- PostgreSQL
SELECT json_column->>'key' FROM TableName;

-- MySQL/TiDB/OceanBase
SELECT JSON_EXTRACT(json_column, '$.key') FROM TableName;

-- Oracle 12c+
SELECT json_column.key FROM TableName;

2. 正则表达式

-- PostgreSQL/TiDB/OceanBase
SELECT * FROM TableName WHERE column1 ~ 'pattern';

-- MySQL
SELECT * FROM TableName WHERE column1 REGEXP 'pattern';

-- Oracle
SELECT * FROM TableName WHERE REGEXP_LIKE(column1, 'pattern');

九、系统函数

1. 获取最后插入ID

-- PostgreSQL
INSERT INTO TableName (...) VALUES (...) RETURNING id;

-- MySQL/TiDB/OceanBase
INSERT INTO TableName (...) VALUES (...);
SELECT LAST_INSERT_ID();

-- Oracle
INSERT INTO TableName (...) VALUES (...) RETURNING id INTO :variable;

2. 分组合并字符串

-- PostgreSQL
SELECT string_agg(column1, ',') FROM TableName GROUP BY column2;

-- MySQL
SELECT GROUP_CONCAT(column1 SEPARATOR ',') FROM TableName GROUP BY column2;

-- Oracle
SELECT LISTAGG(column1, ',') WITHIN GROUP (ORDER BY column1) 
FROM TableName GROUP BY column2;

-- TiDB/OceanBase
SELECT GROUP_CONCAT(column1 SEPARATOR ',') FROM TableName GROUP BY column2;

十、存储过程

1. 基本语法

-- PostgreSQL
CREATE OR REPLACE FUNCTION func_name() RETURNS void AS $
BEGIN
  -- 代码
END;
$ LANGUAGE plpgsql;

-- MySQL/TiDB/OceanBase
DELIMITER //
CREATE PROCEDURE proc_name()
BEGIN
  -- 代码
END //
DELIMITER ;

-- Oracle
CREATE OR REPLACE PROCEDURE proc_name AS
BEGIN
  -- 代码
END;
/

以上是主要数据库系统在常见 SQL 功能上的语法差异,实际使用时还需考虑各数据库的版本差异。

最近发表
标签列表