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 $$ BEGIN INSERT INTO employees (name) VALUES ('John'); END; $$ LANGUAGE plpgsql;
- MySQL: 只支持使用 SQL 语言编写存储过程,功能较为简单。
-- MySQL 编写存储过程 CREATE PROCEDURE add_employee() BEGIN INSERT 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 BEGIN CALL check_employee(); END;
本站资源均来自互联网,仅供研究学习,禁止违法使用和商用,产生法律纠纷本站概不负责!如果侵犯了您的权益请与我们联系!
转载请注明出处: 免费源码网-免费的源码资源网站 » PostgreSQL与MySQL在语法上的区别
发表评论 取消回复