PostgreSQL命令大全

码农世界 2024-06-13 后端 99 次浏览 0个评论

文章目录

连接与退出
数据库操作
表操作
外键约束
视图操作
存储过程与函数
权限管理
事务管理
查询优化与分析
数据类型转换
分区表操作

复制与备份恢复

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 $$
DECLARE
  result type;
BEGIN
  SELECT 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)
        ————————————————
        
        最后我们放松一下眼睛