文章目录
- 连接与退出
- 数据库操作
- 表操作
- 外键约束
- 视图操作
- 存储过程与函数
- 权限管理
- 事务管理
- 查询优化与分析
- 数据类型转换
- 分区表操作
- 复制与备份恢复
PostgreSQL是一个功能强大的开源关系型数据库管理系统，以下是一些基本且常用的命令按功能分类：
连接与退出
-
连接数据库：
psql -U [username] -d [database]示例：以用户postgres身份连接到名为mydb的数据库
psql -U postgres -d mydb -
退出 PostgreSQL Shell：
\q
数据库操作
-
查看所有数据库：
\l -
创建数据库：
CREATE DATABASE [database_name];示例：
CREATE DATABASE my_new_db; -
切换数据库：
\c [database_name]示例：
\c my_new_db -
删除数据库：
DROP DATABASE [database_name];示例：
DROP DATABASE unused_db;
表操作
-
列出当前数据库中的表：
\dt -
创建表：
CREATE TABLE [table_name] (column1 data_type,column2 data_type,... );示例：
CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(50),age INTEGER ); -
查询表数据：
SELECT * FROM [table_name];示例：
SELECT * FROM users; -
插入数据：
INSERT INTO [table_name] (column1, column2, ...) VALUES (value1, value2, ...);示例：
INSERT INTO users (name, age) VALUES ('John Doe', 30); -
更新数据：
UPDATE [table_name] SET column1 = value1, column2 = value2 WHERE condition;示例：
UPDATE users SET age = 31 WHERE name = 'John Doe'; -
删除数据：
DELETE FROM [table_name] WHERE condition;示例：
DELETE FROM users WHERE name = 'John Doe'; -
创建索引：
CREATE INDEX [index_name] ON [table_name] (column1, column2);示例：
CREATE INDEX idx_users_name ON users (name); -
删除索引：
DROP INDEX [index_name];示例：
DROP INDEX idx_users_name;
当然，以下是更多PostgreSQL高级功能的命令示例：
外键约束
- 创建具有外键约束的表：
CREATE TABLE orders (order_id SERIAL PRIMARY KEY,user_id INTEGER REFERENCES users(id) ON DELETE CASCADE );
视图操作
-
创建视图：
CREATE VIEW [view_name] AS SELECT * FROM [table_name] WHERE condition;示例：
CREATE VIEW active_users AS SELECT * FROM users WHERE is_active = true; -
查询视图：
SELECT * FROM [view_name];示例：
SELECT * FROM active_users; -
更新或删除视图中的数据（通过视图修改原表数据）：
UPDATE [view_name] SET column1 = value1 WHERE condition; DELETE FROM [view_name] WHERE condition; -
删除视图：
DROP VIEW [view_name];示例：
DROP VIEW active_users;
存储过程与函数
-
创建存储过程：
CREATE OR REPLACE PROCEDURE 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); -
创建函数：
CREATE OR REPLACE FUNCTION my_function(param1 type) RETURNS type AS $$ DECLAREresult 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];示例：
GRANT SELECT, INSERT ON users TO new_user; -
撤销用户权限：
REVOKE privilege_type ON [table_name] FROM [username];示例：
REVOKE ALL PRIVILEGES ON users FROM new_user;
事务管理
-
开始事务：
BEGIN TRANSACTION; -
提交事务（确认所有更改）：
COMMIT; -
回滚事务（撤销所有更改）：
ROLLBACK; -
保存点设置（在事务中创建一个可以回滚到的点）：
SAVEPOINT [savepoint_name];示例：
SAVEPOINT my_savepoint; -
回滚到保存点：
ROLLBACK TO [savepoint_name];示例：
ROLLBACK TO my_savepoint;
查询优化与分析
- 查看查询执行计划：
示例：EXPLAIN [ANALYZE] [VERBOSE] [BUFFERS] [FORMAT type] SELECT ... ;EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;
数据类型转换
- 强制数据类型转换：
示例：SELECT CAST(column AS new_type) FROM table;SELECT CAST(age AS TEXT) FROM users;
分区表操作
- 创建分区表（例如，按年份分区）：
CREATE TABLE sales (id SERIAL PRIMARY KEY,product VARCHAR(50),sale_date DATE ) PARTITION BY RANGE (sale_date);CREATE TABLE sales_2021 PARTITION OF sales FOR VALUES FROM ('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)
————————————————
最后我们放松一下眼睛
