MySQL、Oracle、PostgreSql的语法差异

菜菜今天转行了吗

已于 2024-07-02 15:09:30 修改

阅读量1k

点赞数 23

分类专栏： java面经文章标签： oracle postgresql 数据库 mysql 面试

于 2024-07-01 11:18:54 首次发布

本文链接：https://blog.csdn.net/qq_45184921/article/details/140095254

版权

java面经专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、SQL结尾的分号问题

PostgreSQL、MySQL、和 Oracle 都支持使用分号结束 SQL 语句

二、SQL中的引号问题

MySQL:
- 单引号 (') 用于字符串值，例如 'example'。
- 反引号 (`) 用于标识符（如表名或列名）的引用，用以区分保留字和特殊字符，例如 `table_name`。
- 双引号 (") 作用同反引号，在 ANSI_QUOTES SQL 模式下可以用来引用标识符。
Oracle:
- 单引号 (') 用于字符串值。
- 双引号 (") 用于标识符（如表名或列名）的引用，用以区分保留字和特殊字符，例如 “table_name”。
PostgreSQL:
- 单引号 (') 用于字符串值。
- 双引号 (") 用于标识符（如表名或列名）的引用，与 Oracle 的使用方式相同。

三、标识符和关键字的大小写

MySQL：在MySQL中，标识符（如表名和列名）默认是不区分大小写的，但可以在配置中更改这一行为。关键字是区分大小写的，但通常在SQL语句中都会使用大写来表示。
Oracle：Oracle的标识符默认是区分大小写的，但可以通过双引号来强制不区分大小写。Oracle的关键字也是不区分大小写的。
PostgreSQL：PostgreSQL的标识符默认是区分大小写的，但可以使用双引号来引用标识符，以便在必要时覆盖默认行为。关键字在PostgreSQL中也是区分大小写的。

四、字符串连接

MySQL 使用 CONCAT() 函数。
Oracle 使用 CONCAT() 或 || 操作符。
PostgreSQL 使用 CONCAT() 或 || 操作符。

注意： Oracle的 CONCAT() 函数只能连接两个字符串，如果需要连接多个字符串，可能需要嵌套使用 CONCAT() 或者直接使用 || 操作符。

--oracle：
SELECT CONCAT(CONCAT('Hello', ' '), 'World') FROM DUAL; 
SELECT 'Hello' || ' ' || 'World' FROM DUAL;
--mysql跟postgresql可以直接这样子用：
SELECT CONCAT('Hello', ' ', 'World') AS concat_string;

五、分页查询问题

MySQL:

使用LIMIT 关键词分页:

--问题：查询第 30 到第 40 条数据

--方法一：OFFSET 后面跟的数值表示跳过的记录数
--LIMIT 后面的数值表示返回的记录数
SELECT * FROM your_table_name
LIMIT 11 OFFSET 29;--（limit、offset顺序不可换）

--方法二： 29 表示跳过的记录数，11 是返回的记录数
SELECT * FROM your_table_name
LIMIT 29, 11;

Oracle：

(1) 在Oracle11g及其以下版本：

--问题：查询第 30 到第 40 条数据

--方法一：用ROWNUM伪列。
--是Oracle为查询结果的每一行动态分配的序号从1开始
SELECT *
FROM (
  SELECT a.*, ROWNUM rnum
  FROM (
    SELECT * FROM table_name
    ORDER BY some_column  -- 根据需要排序的列
  ) a
  WHERE ROWNUM <= 40
)
WHERE rnum >= 30;

--方法二：用ROW_NUMBER() 函数为结果集中的每一行分配一个序号，
--这个序号是根据 some_column 列排序后的结果

SELECT *
FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
  FROM table_name t
)
WHERE row_num BETWEEN 30 AND 40;

--方法三：若数据中可能存在相同的排序值并且需要包含所有相同排序值的记录
--可以使用 RANK() 或 DENSE_RANK()
--类似于使用 ROW_NUMBER()，但 DENSE_RANK() 会在排序值相同的情况下
--给予相同的排名，而不会像 ROW_NUMBER() 那样继续计数

SELECT *
FROM (
  SELECT t.*, DENSE_RANK() OVER (ORDER BY some_column) AS row_num
  FROM table_name t
)
WHERE row_num BETWEEN 30 AND 40;

(2) 在Oracle12c及其以上版本，还可以：

--使用 OFFSET 和 FETCH NEXT 语句来简化分页查询

SELECT * FROM table_name
ORDER BY some_column
OFFSET 29 ROWS FETCH NEXT 11 ROWS ONLY;

总结

选择哪种方法取决于具体需求：

ROWNUM 在处理简单的行数限制时很有用，但在需要精确控制排序和分页的复杂查询中下面的方法比较合适。
如果你需要精确的行控制，且数据没有重复的排序值，ROW_NUMBER() 是最合适的。
如果你想要简单的语法并且只是简单的分页，OFFSET 和 FETCH NEXT 是非常方便的。
如果你的数据中存在重复值，并且你想要在这些重复值之间不区分排名，那么使用 RANK() 或 DENSE_RANK() 会更合适。

PostgreSQL ：

--方法一：使用 LIMIT 和 OFFSET
sql
SELECT * FROM your_table
ORDER BY some_column
LIMIT 11 OFFSET 29;

--方法二：使用 FETCH NEXT 和 OFFSET
--FETCH NEXT指定返回记录的数量，OFFSET指定跳过的记录数。
sql
SELECT * FROM your_table
ORDER BY some_column
OFFSET 29 ROWS FETCH NEXT 11 ROWS ONLY;

六、批量插入insert问题

MySQL ：

-- 删除已存在的表（如果存在）
DROP TABLE IF EXISTS `students`;

-- 创建表
CREATE TABLE `students` (
  `id` varchar(128) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 批量插入数据
INSERT INTO `students`(`id`, `name`, `gender`) VALUES 
('id1', 'Alice', 'Female'),
('id2', 'Bob', 'Male'),
('id3', 'Charlie', 'Male');

Oracle ：

-- 删除已存在的表（Oracle不支持IF EXISTS）
DROP TABLE "students";

-- 创建表
CREATE TABLE "students" (
  "id" varchar2(128) NOT NULL,
  "name" varchar2(255) NOT NULL,
  "gender" varchar2(10) DEFAULT NULL,
  PRIMARY KEY ("id")
);

-- 使用INSERT ALL进行批量插入
INSERT ALL
 INTO "students"("id", "name", "gender") VALUES ('id1', 'Alice', 'Female')
 INTO "students"("id", "name", "gender") VALUES ('id2', 'Bob', 'Male')
 INTO "students"("id", "name", "gender") VALUES ('id3', 'Charlie', 'Male')
SELECT * FROM dual;

PostgreSQL ：

-- 删除已存在的表
DROP TABLE IF EXISTS "students";

-- 创建表
CREATE TABLE "students" (
  "id" varchar(128) NOT NULL,
  "name" varchar(255) NOT NULL,
  "gender" varchar(10) DEFAULT NULL,
  PRIMARY KEY ("id")
);

-- 批量插入数据，类似于MySQL
INSERT INTO "students"("id", "name", "gender") VALUES 
('id1', 'Alice', 'Female'),
('id2', 'Bob', 'Male'),
('id3', 'Charlie', 'Male');

总结

这些示例显示了在不同数据库系统中进行批量数据插入的语法。MySQL 和 PostgreSQL 的语法非常相似，而 Oracle 则使用不同的语法结构来实现类似的功能。

七、自增字段问题

MySQL：

在建表时，对于整型字段，可以使用 AUTO_INCREMENT 关键词配置为自增字段，通常为主键。
MySQL数据库中的一个表最多有一个自增字段。

   -- 创建表并定义自增主键
   CREATE TABLE my_table (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(50)
   );

Oracle：

Oracle与PostgreSQL类似，也支持序列的概念，但在Oracle 12c及更高版本中，引入了类似于MySQL的自增列特性（IDENTITY列）。
在Oracle数据库中，表可以定义多个自增字段，但需要通过序列来管理。

   -- 使用序列
   CREATE SEQUENCE my_sequence
       START WITH 1
       INCREMENT BY 1
       CACHE 1;
   
   CREATE TABLE my_table (
       id NUMBER DEFAULT my_sequence.NEXTVAL, -- 使用序列
       name VARCHAR2(50)
   );
   
   -- 或使用IDENTITY列创建自增字段
   CREATE TABLE my_table (
       id NUMBER GENERATED ALWAYS AS IDENTITY, -- 自增属性
       name VARCHAR2(50)
   );

PostgreSQL：

创建表时，可以使用 serial 或 bigserial 类型来定义自增字段，PostgreSQL会自动生成相应的序列。
PostgreSQL的一张表中可以存在多个自增字段，如果需要获取最后插入的自增值，需要指定对应的序列名。
也可以单独创建序列 SEQUENCE 来生成自增的值。示例如下：

--1、创建一个序列，这个序列会按照设定的步长自增：
sql
CREATE SEQUENCE my_sequence
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
--上面这个序列从1开始，每次增加1，没有设定最小值和最大值，缓存设置为1。

--接下来，可以在创建表的时候，使用这个序列来为自增字段提供值：
CREATE TABLE my_table (
    id INTEGER DEFAULT nextval('my_sequence'),
    name VARCHAR(50)
);
--上面例子中，my_table 表有一个名为 id 的列
--该列的默认值将由 my_sequence 序列自动提供，实现自增功能。
--每当向表中插入新行而没有指定 id 值时，
--id 列的值会自动从 my_sequence 获取下一个值。

八、数据类型

1. VARCHAR

MySQL：VARCHAR(n) 用于定义可变长度的字符序列，最大长度为n个字符。
Oracle： VARCHAR2(n) 用于定义可变长度的字符序列，最大长度为n个字符。
PostgreSQL：VARCHAR(n)或CHARACTER VARYING(n) 用于定义可变长度的字符序列，最大长度为n个字符。
对于固定长度的字符串，MySQL使用CHAR(n)，Oracle使用CHAR(n)，而PostgreSQL也使用CHAR(n)或CHARACTER(n)。

2. DATE and TIMESTAMP

MySQL：DATE类型仅存储日期（年、月、日），而TIMESTAMP类型存储日期和时间，并且如果设置为 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP，它会自动更新记录的创建和修改时间。
Oracle： DATE 类型在 Oracle 中存储日期和时间，但精度到秒。TIMESTAMP 类型提供了更高的精度，可以存储小数秒。
PostgreSQL：与Oracle类似，DATE仅存储日期，而TIMESTAMP可以选择是否存储时区信息，即 TIMESTAMP 和 TIMESTAMP WITH TIME ZONE。

3. BOOLEAN

MySQL：MySQL没有原生的BOOLEAN类型，而是使用TINYINT(1)来模拟布尔值，其中0表示假，非0表示真。
Oracle：Oracle也没有原生的BOOLEAN类型。通常需要使用NUMBER(1)或其他方法来表示布尔值。
PostgreSQL：支持原生的BOOLEAN类型，可以使用 TRUE, FALSE, 't', 'f', 'true', 'false', 1, 0 等表示。

4. TEXT

MySQL和PostgreSQL：都支持TEXT类型，用于存储可变长度的字符串，通常用于长文本。
Oracle：没有TEXT类型，相对应的是CLOB（Character Large Object）来存储大量字符数据。Oracle 还提供了 NCLOB 用于存储大量国家字符集数据，以及 BLOB 用于存储二进制数据。

九、索引的创建

创建 B-Tree 索引（使用基本创建语法即可）

--基本创建语法：
CREATE INDEX 索引名 ON 表名 (列名);

--题：创建名为idx_username的B-Tree索引，用于users表的username列
--MySQL、Oracle、postgresql：
CREATE INDEX idx_username ON users(username);

创建复合索引

--题：创建名为idx_username的复合索引，用于users表的username列和email列
--MySQL、Oracle、postgresql：
CREATE INDEX idx_username ON users(username, email);

总结：

mysql：

--B-Tree 索引：
CREATE INDEX 索引名 ON 表名 (列名);
--复合索引
CREATE INDEX 索引名 ON 表名(列名1, 列名2,....);
--唯一索引：
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);
--全文索引：
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);
--空间索引：
CREATE SPATIAL INDEX 索引名 ON 表名 (列名);

oracle:

--B-Tree 索引:
CREATE INDEX 索引名 ON 表名 (列名);
--复合索引:
CREATE INDEX 索引名 ON 表名(列名1, 列名2,....);
--唯一索引:
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);
--指定类型索引: bitmap
CREATE INDEX 索引名 ON 表名 (列名) INDEXTYPE IS 索引类型;
--在Oracle中，如果不指定表空间，Oracle将使用表的默认表空间来存储索引，如果要在创建索引时指定表空间:
CREATE INDEX 索引名 ON 表名(列名1, 列名2,....) TABLESPACE your_tablespace_name;

postgresql：

--B-Tree 索引:
CREATE INDEX 索引名 ON 表名 (列名);
--复合索引:
CREATE INDEX 索引名 ON 表名(列名1, 列名2,....);
--唯一索引:
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);
--指定类型索引:gin、gist、brin 
CREATE INDEX 索引名 ON 表名 USING 索引类型 (列名);