PostgreSQL与MySQL在语法上的区别

1. 数据库管理

1.1 查看数据库

PostgreSQL: 使用 \l 命令列出所有数据库，或者查询系统表 pg_database。
```
-- PostgreSQL
\l
-- 或者
SELECT datname FROM pg_database;
```
MySQL: 使用 SHOW DATABASES; 来列出所有数据库。
```
-- MySQL
SHOW DATABASES;
```

2. 表的管理

2.1 数据类型

PostgreSQL: 支持多种高级数据类型，如 ARRAY, JSONB, ENUM, HSTORE。这些类型可以处理复杂数据结构，特别是 JSONB 支持高效的查询和索引。
```
-- PostgreSQL 创建一个带有 JSONB 和 ARRAY 列的表
CREATE TABLE test_table (id SERIAL PRIMARY KEY,data JSONB,tags TEXT[]
);
```

MySQL: 支持 ENUM 和 JSON 数据类型，但不支持数组类型。

-- MySQL 创建一个带有 JSON 列的表
CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY,data JSON
);

2.2 查看表结构

PostgreSQL: 使用 \d tablename; 在 psql 中查看表的结构，或查询系统表。
```
-- PostgreSQL 查看表结构
\d test_table;
```
MySQL: 使用 DESCRIBE tablename; 或 SHOW COLUMNS FROM tablename; 查看表的结构。
```
-- MySQL 查看表结构
DESCRIBE test_table;
```

2.3 修改列

PostgreSQL: 使用 ALTER TABLE ALTER COLUMN 修改列的类型，支持直接修改而不需删除列。
```
-- PostgreSQL 修改列的类型
ALTER TABLE test_table ALTER COLUMN data TYPE TEXT;
```

MySQL: 使用 MODIFY 语法修改列的类型。

-- MySQL 修改列的类型
ALTER TABLE test_table MODIFY COLUMN data TEXT;

2.4 查看所有表

PostgreSQL: 使用 \dt 在 psql 终端中列出所有表。
```
-- PostgreSQL 查看所有表
\dt;
```
MySQL: 使用 SHOW TABLES; 列出当前数据库中的所有表。
```
-- MySQL 查看所有表
SHOW TABLES;
```

3. 数据查询

3.1 条件查询（WHERE）

PostgreSQL: 使用 ILIKE 进行大小写不敏感的字符串匹配查询。适合模糊匹配场景。

-- PostgreSQL 使用 ILIKE 进行大小写不敏感的查询
SELECT * FROM test_table WHERE data ILIKE '%somevalue%';

MySQL: 使用 LIKE 进行大小写敏感的查询。如果需要不敏感匹配，可以使用 LOWER() 函数。

-- MySQL 使用 LIKE 进行查询
SELECT * FROM test_table WHERE data LIKE '%somevalue%';-- 或者使用 LOWER() 进行大小写不敏感匹配
SELECT * FROM test_table WHERE LOWER(data) LIKE '%somevalue%';

4. 数据更新与删除

4.1 UPDATE 语句

PostgreSQL: 支持 RETURNING 子句，允许在更新数据时返回受影响的行。

-- PostgreSQL 更新并返回更新后的行
UPDATE test_table SET data = '{"name": "new value"}' WHERE id = 1 RETURNING *;

MySQL: 不支持 RETURNING，需要使用单独的查询获取更新后的数据。

-- MySQL 更新数据
UPDATE test_table SET data = '{"name": "new value"}' WHERE id = 1;-- 如果需要查看更新后的数据，需要再次查询
SELECT * FROM test_table WHERE id = 1;

4.2 DELETE 语句

PostgreSQL: 同样支持 RETURNING 子句，允许在删除时返回被删除的行。

-- PostgreSQL 删除并返回删除的行
DELETE FROM test_table WHERE id = 1 RETURNING *;

MySQL: 不支持 RETURNING，删除前需要先查询要删除的行。

-- MySQL 删除数据
DELETE FROM test_table WHERE id = 1;-- 删除前需要查询
SELECT * FROM test_table WHERE id = 1;

5. 条件判断语句

5.1 IF 语句

PostgreSQL: 使用标准的 CASE 表达式来实现条件判断。

-- PostgreSQL 使用 CASE 进行条件判断
SELECT CASE WHEN data->>'name' = 'John' THEN 'Match' ELSE 'No Match' END FROM test_table;

MySQL: 使用 IF() 函数进行简单的条件判断，相当于三元表达式。

-- MySQL 使用 IF 函数进行条件判断
SELECT IF(data->>'name' = 'John', 'Match', 'No Match') FROM test_table;

6. 联接操作

6.1 全外联接（FULL OUTER JOIN）

PostgreSQL: 支持 FULL OUTER JOIN，能够返回两个表中匹配和不匹配的记录。

-- PostgreSQL 使用 FULL OUTER JOIN
SELECT * FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id;

MySQL: 不支持 FULL OUTER JOIN，需要通过 UNION 组合 LEFT JOIN 和 RIGHT JOIN 实现。

-- MySQL 使用 UNION 模拟 FULL OUTER JOIN
SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.id
UNION
SELECT * FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;

7. 索引管理

7.1 创建索引

PostgreSQL: 支持多种类型的索引，如 B-tree, Hash, GIN, GiST，用于不同场景的优化。
```
-- PostgreSQL 创建 GIN 索引
CREATE INDEX idx_data_gin ON test_table USING GIN(data);
```

MySQL: 主要支持 B-tree 和 Hash 索引，适用于大多数场景。

-- MySQL 创建 B-tree 索引
CREATE INDEX idx_data_btree ON test_table(data);

8. 查询性能分析

8.1 EXPLAIN 语句

PostgreSQL: 使用 EXPLAIN ANALYZE 可以显示查询计划和实际执行时间，帮助优化查询性能。
```
-- PostgreSQL 使用 EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM test_table;
```
MySQL: EXPLAIN 仅显示查询计划，不显示执行时间。
```
-- MySQL 使用 EXPLAIN
EXPLAIN SELECT * FROM test_table;
```

9. 用户权限管理

9.1 刷新权限

PostgreSQL: 权限修改后自动生效，不需要手动刷新。
MySQL: 权限修改后需要使用 FLUSH PRIVILEGES; 手动刷新权限，以确保更改生效。
```
-- MySQL 刷新权限
FLUSH PRIVILEGES;
```

10. 视图管理

10.1 修改视图

PostgreSQL: 使用 CREATE OR REPLACE VIEW 修改视图，无需删除原有视图。

-- PostgreSQL 修改视图
CREATE OR REPLACE VIEW emp_view AS SELECT * FROM employees WHERE salary > 5000;

MySQL: 必须先删除视图，再重新创建新的视图。

-- MySQL 修改视图需要先删除再创建
DROP VIEW IF EXISTS emp_view;
CREATE VIEW emp_view AS SELECT * FROM employees WHERE salary > 5000;

11. 存储过程管理

11.1 语言支持

PostgreSQL: 支持多种语言编写存储过程，如 PL/pgSQL、PL/Python 等，适合复杂业务逻辑处理。

-- PostgreSQL 使用 PL/pgSQL 编写存储过程
CREATE FUNCTION add_employee() RETURNS void AS $$
BEGININSERT INTO employees (name) VALUES ('John');
END;
$$ LANGUAGE plpgsql;

MySQL: 只支持使用 SQL 语言编写存储过程，功能较为简单。

-- MySQL 编写存储过程
CREATE PROCEDURE add_employee()
BEGININSERT INTO employees (name) VALUES ('John');
END;

12. 触发器管理

12.1 触发器类型

PostgreSQL: 支持 BEFORE, AFTER, INSTEAD OF 触发器，适用于表和视图上的多种操作。

-- PostgreSQL 创建 BEFORE 插入触发器
CREATE TRIGGER emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION check_employee();

MySQL: 支持 BEFORE 和 AFTER 触发器，但不支持 INSTEAD OF 触发器。

-- MySQL 创建 BEFORE 插入触发器
CREATE TRIGGER emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGINCALL check_employee();
END;