PostgreSQL命令大全

文章目录

PostgreSQL是一个功能强大的开源关系型数据库管理系统，以下是一些基本且常用的命令按功能分类：

连接与退出

• 连接数据库：psql -U[username]-d[database]示例：以用户postgres身份连接到名为mydb的数据库psql -U postgres -d mydb
• 退出 PostgreSQL Shell：\q

数据库操作

• 查看所有数据库：\l
• 创建数据库：CREATEDATABASE[database_name];示例：CREATEDATABASE my_new_db;
• 切换数据库：\c [database_name]示例：\c my_new_db
• 删除数据库：DROPDATABASE[database_name];示例：DROPDATABASE unused_db;

表操作

• 列出当前数据库中的表：\dt
• 创建表：CREATETABLE[table_name]( column1 data_type, column2 data_type,...);示例：CREATETABLE users ( id SERIALPRIMARYKEY, name VARCHAR(50), age INTEGER);
• 查询表数据：SELECT*FROM[table_name];示例：SELECT*FROM users;
• 插入数据：INSERTINTO[table_name](column1, column2,...)VALUES(value1, value2,...);示例：INSERTINTO users (name, age)VALUES('John Doe',30);
• 更新数据：UPDATE[table_name]SET column1 = value1, column2 = value2 WHERE condition;示例：UPDATE users SET age =31WHERE name ='John Doe';
• 删除数据：DELETEFROM[table_name]WHERE condition;示例：DELETEFROM users WHERE name ='John Doe';
• 创建索引：CREATEINDEX[index_name]ON[table_name](column1, column2);示例：CREATEINDEX idx_users_name ON users (name);
• 删除索引：DROPINDEX[index_name];示例：DROPINDEX idx_users_name;

当然，以下是更多PostgreSQL高级功能的命令示例：

外键约束

• 创建具有外键约束的表：CREATETABLE orders ( order_id SERIALPRIMARYKEY, user_id INTEGERREFERENCES users(id)ONDELETECASCADE);

视图操作

• 创建视图：CREATEVIEW[view_name]ASSELECT*FROM[table_name]WHERE condition;示例：CREATEVIEW active_users ASSELECT*FROM users WHERE is_active =true;
• 查询视图：SELECT*FROM[view_name];示例：SELECT*FROM active_users;
• 更新或删除视图中的数据（通过视图修改原表数据）：UPDATE[view_name]SET column1 = value1 WHERE condition;DELETEFROM[view_name]WHERE condition;
• 删除视图：DROPVIEW[view_name];示例：DROPVIEW active_users;

存储过程与函数

• 创建存储过程：CREATEORREPLACEPROCEDURE my_procedure (IN param1 type,OUT param2 type)AS $$BEGIN-- 过程体SELECT some_column INTO param2 FROM some_table WHERE condition = param1;END;$$ LANGUAGE plpgsql;
• 调用存储过程：CALL my_procedure('value',OUT result);
• 创建函数：CREATEORREPLACEFUNCTION my_function(param1 type)RETURNStypeAS $$DECLARE result type;BEGINSELECT some_column INTO result FROM some_table WHERE condition = param1;RETURN result;END;$$ LANGUAGE plpgsql;
• 使用函数：SELECT my_function('value')FROM dual;

权限管理

• 授予用户权限：GRANT privilege_type ON[table_name]TO[username];示例：GRANTSELECT,INSERTON users TO new_user;
• 撤销用户权限：REVOKE privilege_type ON[table_name]FROM[username];示例：REVOKEALLPRIVILEGESON users FROM new_user;

事务管理

• 开始事务：BEGINTRANSACTION;
• 提交事务（确认所有更改）：COMMIT;
• 回滚事务（撤销所有更改）：ROLLBACK;
• 保存点设置（在事务中创建一个可以回滚到的点）：SAVEPOINT[savepoint_name];示例：SAVEPOINT my_savepoint;
• 回滚到保存点：ROLLBACKTO[savepoint_name];示例：ROLLBACKTO my_savepoint;

查询优化与分析

• 查看查询执行计划：EXPLAIN[ANALYZE][VERBOSE][BUFFERS][FORMAT type]SELECT...; 示例：EXPLAINANALYZESELECT*FROM users WHERE age >30;

数据类型转换

• 强制数据类型转换：SELECT CAST(columnAS new_type)FROMtable; 示例：SELECT CAST(age ASTEXT)FROM users;

分区表操作

• 创建分区表（例如，按年份分区）：CREATETABLE sales ( id SERIALPRIMARYKEY, product VARCHAR(50), sale_date DATE)PARTITIONBY RANGE (sale_date);CREATETABLE sales_2021 PARTITIONOF salesFORVALUESFROM('2021-01-01')TO('2022-01-01');

复制与备份恢复

• 数据库备份：pg_dump -U[username]-d[database]> backup.sql示例：pg_dump -U postgres -d mydb > mydb_backup.sql
• 从备份恢复数据库：psql -U[username]-d[database]< backup.sql示例：psql -U postgres -d mydb < mydb_backup.sql

以上是PostgreSQL数据库常用命令及使用案例的一部分，更多详细内容请参阅官方文档。对于大型应用和企业级环境，可能还需要涉及更复杂的集群配置、高可用性解决方案、性能调优等方面的知识。请参考官方文档以获取完整信息：https://www.postgresql.org/docs/current/index.html

python推荐学习汇总连接：
50个开发必备的Python经典脚本(1-10)

50个开发必备的Python经典脚本(11-20)

50个开发必备的Python经典脚本(21-30)

50个开发必备的Python经典脚本(31-40)

50个开发必备的Python经典脚本(41-50)
————————————————

​最后我们放松一下眼睛