目录
前言
模式的概念
模式（Schema）表示数据库中的逻辑容器，用于组织和管理数据库对象，如表、视图、索引等。一个模式可以看作是一组相关对象的命名空间。
模式不同于表，它更多地是对数据库对象进行逻辑分组和隔离的一种方式。一个模式可以包含多个表，每个表又可以属于不同的模式。通过使用模式，可以将相关的数据库对象组织在一起，提供更好的可管理性和安全性。
模式的主要作用
- 命名空间隔离：不同模式下的对象可以使用相同的名称，而不会发生冲突。例如，可以在模式A下创建一个名为"users"的表，在模式B下也可以创建一个名为"users"的表，它们互不冲突。
- 访问控制：可以通过授权不同用户或角色对不同模式中的对象进行访问，从而实现更细粒度的权限控制。
- 代码组织：将相关的数据库对象组织在同一个模式下，便于维护、管理和查询。
一、Mysql和PostgreSQL语法比较
1.数据类型差异
1.1整型
- MySQL中的TEXT数据类型最大存储容量为64KB，而PostgreSQL中的TEXT类型没有此限制。
- MySQL中使用TINYINT、MEDIUMINT和INT表示不同大小的整数，而PostgreSQL使用SMALLINT、INT和BIGINT
1.2浮点数类型
- MySQL 提供了 FLOAT 和 DOUBLE 类型来表示浮点数，可以指定精度。
- PostgreSQL 提供了 REAL 和 DOUBLE PRECISION 类型来表示浮点数。
1.3字符串差异
- MySQL中可以使用''或""来表示字符串，而PostgreSQL只接受''来表示字符串。
- PostgreSQL使用E'…' 来表示带有转义序列的字符串。
- 双引号在 PostgreSQL 中用于引用标识符（如表名、列名等），而不是字符串值。
- MySQL 使用 VARCHAR 来表示可变长度的字符串，例如 VARCHAR(255)，VARCHAR(n)最大只能是64kb。而postgresql中的VARCHAR(n) 最大可以存 1GB。
- PostgreSQL 使用 VARCHAR 或 TEXT 来表示可变长度的字符串，postgresql中的TEXT相当于MySQL中的longtext。
1.4数组类型
- MySQL 不直接支持数组类型，但可以使用逗号分隔的字符串来模拟数组。
- PostgreSQL 支持原生的数组类型，如，integer[]、varchar[]等。
2.字符串连接
- 在MySQL中，可以使用
"+"或CONCAT函数来连接字符串，而在PostgreSQL中，可以使用"||"来连接字符串。
-- 使用双竖线进行字符串连接
SELECT 'Hello' || ' ' || 'World'; -- 输出结果为 'Hello World'-- 使用 CONCAT 函数进行字符串连接
SELECT CONCAT('Hello', ' ', 'World'); -- 输出结果为 'Hello World'
-- 直接使用空格符号进行字符串连接
SELECT 'Hello' ' ' 'World'; -- 输出结果为 'Hello World'3.日期和时间函数差异
- MySQL使用
NOW()来获取当前日期时间，而PostgreSQL使用CURRENT_TIMESTAMP或CURRENT_DATE来获取。 - MySQL使用
DATE_ADD()、DATE_SUB()等函数来添加或减少日期时间，而PostgreSQL使用INTERVAL进行类似操作。
-- 在当前日期上添加3天
SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY);
-- 在当前日期上减去1周
SELECT DATE_SUB(CURDATE(), INTERVAL 1 WEEK);-- 在当前日期上添加3天
SELECT CURRENT_DATE + INTERVAL '3 day';
-- 在当前日期上减去1周
SELECT CURRENT_DATE - INTERVAL '1 week';4.Limit子句的差异
在MySQL中，LIMIT子句用于限制结果集中返回的记录数，语法为"LIMIT offset, count"，offset为起始位置，count为返回记录数。
在PostgreSQL中，LIMIT子句用于限制结果集中返回的记录数，语法为"LIMIT count OFFSET offset"，count为返回记录数，offset为起始位置。
count表示要返回的记录数，offset 表示返回记录集开始的位置。如果省略 offset 参数，则默认从第一条记录开始。
SELECT name, score FROM class ORDER BY score DESC LIMIT 5;SELECT name, score FROM class ORDER BY score DESC LIMIT 5 OFFSET 3;
-- 跳过前3个记录，然后获取接下来的5个记录
注意：LIMIT 子句需要与 ORDER BY 子句一起使用来保证结果集的正确性。否则，返回的结果可能会是没有排序的任意行。
5.NULL值的处理
- 在MySQL中，使用"
= NULL"或"IS NULL"来检查NULL值，使用"IS NOT NULL"来检查非NULL值。 - 在PostgreSQL中，使用"
IS NULL"来检查NULL值，使用"IS NOT NULL"来检查非NULL值。
6.自增主键列
- 在MySQL中，可以使用
AUTO_INCREMENT关键字将主键列设置为自增列。 - 在PostgreSQL中，可以使用
SERIAL或BIGSERIAL类型来创建自增主键列。
-- 使用 SERIAL 类型创建自增主键列
CREATE TABLE users (id SERIAL PRIMARY KEY,username VARCHAR(50),email VARCHAR(100));
-- 使用 BIGSERIAL 类型创建自增主键列
CREATE TABLE products (id BIGSERIAL PRIMARY KEY,name VARCHAR(100),price DECIMAL(10,2));注意：
使用SERIAL类型时，自增的主键列将存储 **4 字节大小的整数值，范围从 1 到 2,147,483,647。
而使用BIGSERIAL类型时，自增的主键列将存储 8 **字节大小的整数值，范围从 1 到 9,223,372,036,854,775,807。
无论使用
SERIAL还是BIGSERIAL类型，都要将其指定为主键（PRIMARY KEY）以确保唯一性和索引优化。通过使用这些自增类型，可以方便地为表创建带有自动生成的唯一标识符的主键列。
7.变量和参数差异
- MySQL使用
@符号来声明和使用用户变量，使用?来作为占位符来传递参数。 - PostgreSQL使用冒号（
:）来声明和使用变量，使用$1、$2等占位符来传递参数。
-- 声明一个名为my_var的用户变量，并将其赋值为'Hello World'
SET @my_var := 'Hello World';
-- 打印变量的值
SELECT @my_var;
-- 查询指定用户的信息，使用占位符传递参数
SELECT username, email FROM users WHERE id = ?;-- 声明一个名为my_var的变量，并将其赋值为'Hello World'
DO $$
DECLARE
my_var TEXT := 'Hello World';
BEGIN
-- 打印变量的值
RAISE NOTICE 'The value of my_var is: %', my_var;
END $$;
-----------------------------------------
-- 查询指定用户的信息，使用占位符传递参数
SELECT username, email FROM users WHERE id = $1;8.连接查询
- 在MySQL中，可以使用
JOIN、LEFT JOIN、RIGHT JOIN和INNER JOIN来进行不同类型的连接。 - 在PostgreSQL中，使用
JOIN来进行内连接，使用LEFT JOIN和RIGHT JOIN来进行左连接和右连接。
9.having语法差异
- 字符串比较：在 PostgreSQL 中，字符串比较是区分大小写的，默认情况下使用区分大小写的排序规则。而在 MySQL 中，字符串比较默认是不区分大小写的，可以使用 COLLATE 子句来指定不同的排序规则。
- 聚合函数的引用：在 PostgreSQL 中，HAVING 子句可以直接引用 SELECT 语句中定义的聚合函数，如 HAVING SUM(column) > 100。而在 MySQL 中，默认情况下不允许在 HAVING 子句中直接使用 SELECT 语句中定义的聚合函数结果，需要使用重复的聚合函数表达式，如 HAVING SUM(column) > 100 和 HAVING SUM(column) > 200。
- NULL 值处理：在 PostgreSQL 中，HAVING 子句可以直接使用 NULL 进行比较，如 HAVING column IS NULL。而在 MySQL 中，需要使用特殊函数 IS NULL 或 IS NOT NULL 进行 NULL 值的比较，如 HAVING IS NULL(column)。
- 在 PostgreSQL 中，不能直接在 HAVING 子句中使用 SELECT 语句中定义的列别名进行引用。而在 MySQL 中，默认情况下也不支持在 HAVING 子句中直接使用 SELECT 语句中的列别名。然而，从 MySQL 5.7+ 版本开始，已经开始支持使用列别名。
10.Group by差异
10.1在选择列表中的非聚合列
- MySQL 允许在 GROUP BY 子句中不列出选择列表中的非聚合列，而且结果集将使用该组中的任意一个值。这可能会导致不可预测的结果。
- PostgreSQL 要求选择列表中的非聚合列要么包含在 GROUP BY 子句中，要么在聚合函数中使用。否则，会引发错误。
10.2聚合函数的处理
- MySQL 允许在 SELECT 语句中使用聚合函数，即使没有使用 GROUP BY 子句。这将导致对整个结果集进行聚合计算并返回单个结果。
- PostgreSQL 要求在使用聚合函数时必须使用 GROUP BY 子句，并按照指定的分组进行计算。
#Mysql
SELECT name, AVG(score) AS avg_score FROM student GROUP BY name;#PostgreSQL
SELECT name, AVG(score) AS avg_score
FROM student
GROUP BY name;
--- 报错！！！
可以看到，MySQL 允许在 SELECT 子句中使用聚合函数，而 PostgreSQL 要求使用
GROUP BY子句，并确保所有非聚合列都在GROUP BY子句中。
11.编码和排序方式
11.1编码
- PostgreSQL 提供了许多字符集和编码，其中包括 UTF-8、Latin1、WIN1251 等。可以通过创建数据库时指定编码或者修改已有数据库的编码来设置。
- MySQL 也提供了多种字符集和编码，其中包括 UTF-8、GBK、GB2312 等。可以通过创建数据库时指定字符集或者修改已有数据库的字符集来设置。
11.2排序方式
- PostgreSQL 的默认排序规则是由其设置的区域设置（locale）确定的，可以使用 LC_COLLATE 环境变量或 SET 命令来更改。例如，如果使用以英文为基础的区域设置，则会在排序中将大写字母与小写字母一起处理。此外，PostgreSQL还支持自定义排序规则。
- MySQL 的默认排序规则是根据字符集而不是区域设置确定的。例如，在 utf8 字符集下，MySQL 按照Unicode标准进行排序，因此对于不同语言的字符排序可能会有所不同。MySQL 支持使用 COLLATE 关键字来指定排序规则。
二、PostgreSQL特有的
1.几何类型
包括点（point）、直线（line）、线段（lseg）、路径（path）、多边形（polygon）、圆（cycle）等类型。
CREATE TABLE places (id SERIAL PRIMARY KEY,name VARCHAR(100),location GEOMETRY(Point, 4326));
INSERT INTO places (name, location) VALUES ('Central Park',ST_GeomFromText('POINT(-73.968500 40.785800)', 4326));2.网络地址类型
有cidr、inet、macaddr 3种类型
CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(100),ip_address INET);
INSERT INTO users (name, ip_address) VALUES ('Alice', '192.168.0.1');3.数组类型
可以在表中存储和操作多个数值或对象
CREATE TABLE products (id SERIAL PRIMARY KEY,name VARCHAR(100),prices INTEGER[]);
INSERT INTO products (name, prices) VALUES ('Apple', ARRAY[10, 15, 20]);4.范围类型
range类型，可以存储范围数据，其他数据库中无此类型
用于表示一定范围内的数值或时间戳等数据。
CREATE TABLE events (id SERIAL PRIMARY KEY,name VARCHAR(100),event_time TSRANGE);
INSERT INTO events (name, event_time) VALUES ('Meeting', '[2024-03-30 16:00, 2024-03-31 16:00]');5.对象标识符类型
PostgreSQL 具有特殊的对象标识符类型，用于存储和引用数据库对象的标识，如表、列、模式等。
CREATE TABLE orders (id SERIAL PRIMARY KEY,order_number OID,product_name VARCHAR(100));
INSERT INTO orders (order_number, product_name) VALUES ('163125', 'Computer');三、数据类型别名
为了提高SQL的兼容性，部分数据类型还有很多别名，如integer类型，可以用int、int4表示，smallint也可以用int2表示；char varying(n)可以用varchar(n)表示，numeric(m,n)也可以用decimal(m,n)表示，等等。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
