PostgreSQL在Linux情况下的常用命令总结

标题

• 登录PgSQL
  
• 库表基本操作命令
  
• 新建库表
  
• 修改库表
  
• 
  
  
  
  • 修改数据库名称：
    
  • 修改表名称
    
  • 修改表字段信息
    
  
  
• 删除库表
  
• 
  
  
  
  • pgsql删除正在利用的数据库
    
  
  

须知：
以下全部命令我都在Linux情况中执行验证过，大家放心食用，此中的实际名称换成自己的实际名称即可。而且这块假如你不是运维人员的话，你只需要简单的会在Linux下查一下数据就行了。其他的操作都在客户端工具操作比较简单。了解一下即可
关于客户端的操作命令可以点我查察

登录PgSQL

基本毗连方式
psql -U <用户名> -d <数据库名>：这是毗连到指定命据库的常用命令。
例如，psql -U postgres -d mydb 表示以 postgres 用户身份毗连到 mydb 数据库。
假如省略 -d 选项和数据库名，将毗连到用户的默认数据库(postgres) 。

库表基本操作命令

• 查察数据库版本：select version();
  
• #查察有哪些用户：dg
  
• 列出全部数据库：l
  
• 切换数据库：c mydb
  
• 列出当前数据库的全部表：dt
  
• 列出表的结构：d 表名
  
• 列出全部角色：du
  
• 修改用户名暗码：postgres=# alter user postgres with password 'postgres';
  – 创建新数据库
  CREATE DATABASE 数据库名;
  – 删除数据库
  DROP DATABASE 数据库名;
  – 创建新表
  CREATE TABLE 表名 (
  列1 数据类型,
  列2 数据类型,
  …
  );
  – 删除表
  DROP TABLE 表名;
  – 插入数据
  INSERT INTO 表名 (列1, 列2, …) VALUES (值1, 值2, …);
  – 更新数据
  UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;
  – 删除数据
  DELETE FROM 表名 WHERE 条件;
  – 查询数据
  SELECT * FROM 表名;
  – 创建新用户
  CREATE USER 用户名 WITH PASSWORD ‘暗码’;
  – 修改用户暗码
  ALTER USER 用户名 WITH PASSWORD ‘新暗码’;
  – 为用户授权
  GRANT 权限 TO 用户名;
  – 撤销权限
  REVOKE 权限 FROM 用户名;
  

新建库表

以下是在 PostgreSQL 中创建数据库 mydb 以及在此中创建 student 表和 teacher 表，并为每个表添加一些测试用字段的示例代码：

1. -- 创建数据库mydb
2. CREATE DATABASE mydb;
4. -- 连接到新创建的数据库mydb
5. c mydb
7. -- 创建student表
8. CREATE TABLE student (
9.     student_id SERIAL PRIMARY KEY,
10.     student_name VARCHAR(50),
11.     student_age INTEGER,
12.     student_gender CHAR(1),
13.     major VARCHAR(100)
14. );
16. -- 创建teacher表
17. CREATE TABLE teacher (
18.     teacher_id SERIAL PRIMARY KEY,
19.     teacher_name VARCHAR(50),
20.     teacher_age INTEGER,
21.     subject VARCHAR(100),
22.     department VARCHAR(100)
23. );
27. -- 向student表插入第一条数据
28. INSERT INTO student (student_name, student_age, student_gender, major)
29. VALUES ('张三', 20, 'M', '计算机科学与技术');
31. -- 向student表插入第二条数据
32. INSERT INTO student (student_name, student_age, student_gender, major)
33. VALUES ('李四', 22, 'F', '软件工程');
35. -- 向teacher表插入第一条数据
36. INSERT INTO teacher (teacher_name, teacher_age, subject, department)
37. VALUES ('王老师', 35, '数据库原理', '计算机系');
39. -- 向teacher表插入第二条数据
40. INSERT INTO teacher (teacher_name, teacher_age, subject, department)
41. VALUES ('李老师', 40, '操作系统', '计算机系');

复制代码

修改库表

修改数据库名称：

一般来说没这种需求，不过测试玩玩的话照旧整理一下吧
方法一

1. 备份sql（见下面说明）
2. 切换到别的数据库下删除：DROP DATABASE mydb;
3. CREATE DATABASE mybdtest;
4. 执行备份SQL进行回复（见下面说明）

复制代码

备份数据库（利用pg_dump）
在 Linux 下，备份mydb数据库可以利用pg_dump命令。假设你的 PostgreSQL 数据库用户名是postgres，暗码是your_password（请替换为实际暗码），主机地址是localhost，数据库名是mydb，你可以利用以下命令将数据库备份为一个.sql文件：
pg_dump -U postgres -h localhost -Fc mydb > mydb_backup.dump
命令解释：
-U postgres：指定以postgres用户身份举行备份操作。
-h localhost：指定命据库主机地址为本地（localhost），假如数据库在远程服务器，需要替换为实际的远程主机地址。
-Fc：指定输出格式为自定义（custom）二进制格式，这种格式在恢复数据时比较高效。假如想要纯文本格式的备份文件，可以利用-Fp（plain text），不过恢复时可能会稍微复杂一点。
mydb：要备份的数据库名称。
> mydb_backup.dump：将备份内容输出到名为mydb_backup.dump的文件中，你可以根据自己的喜好更改文件名。

恢复数据库（利用pg_restore）
在删除mydb并创建mybdtest后，利用pg_restore命令来恢复备份的数据到mybdtest数据库。假设你的用户名、主机地址等信息稳固，执行以下命令：
pg_restore -U postgres -h localhost -d mybdtest mydb_backup.dump
命令解释：
-U postgres：指定以postgres用户身份举行恢复操作。
-h localhost：指定命据库主机地址为本地。
-d mybdtest：指定恢复数据的目标数据库为mybdtest。
mydb_backup.dump：要恢复的备份文件的名称，需要与备份时的文件名一致。
需要注意的是，在执行这些命令时，可能需要确保postgres用户有足够的权限举行备份和恢复操作。假如数据库设置了暗码，可能还需要在命令中正确提供暗码，或者设置PGPASSWORD情况变量来存储暗码，这样就不用每次在命令中输入暗码了。例如，可以在执行命令前设置情况变量：export PGPASSWORD=your_password

方法二
利用 ALTER DATABASE（在某些特定版本和场景下可能有限制）
在一些较新的 PostgreSQL 版本中，理论上可以实行利用 ALTER DATABASE 语句来更改数据库名称，但这种方法可能会受到一些限制，比如数据库的利用状态、权限等因素可能会影响其乐成与否。
假设你在 psql 命令行情况下而且有足够的权限，你可以实行以下操作：
ALTER DATABASE mydb RENAME TO mybdtest;
假如此操作乐成，那么数据库名称就直接被更改为 mybdtest 了。但假如遇到错误，可能就需要按照方法一的步调来完成更名操作啦。
需要注意的是，无论利用哪种方法，在举行这些操作之前，都发起你先对告急数据举行备份，以防出现意外情况导致数据丢失。

修改表名称

要将表名 student 修改为 student_info，可以利用 ALTER TABLE 语句来实现。以下是具体的操作步调：

1. -- 将表student修改为student_info
2. ALTER TABLE student RENAME TO student_info;

复制代码

修改表字段信息

修改表字段名称、字段类型、字段注释
这块我有一篇博客根据各种情况都有对应的命令分析可以点我去看

1. 一、修改列名
2. 将 student_id 修改为 stu_id：
3. ALTER TABLE student_info RENAME COLUMN student_id TO stu_id;
5. 二、修改数据类型
6. 将 student_name 的数据类型从 character varying(50) 修改为 character(20)：
7. ALTER TABLE student_info ALTER COLUMN student_name TYPE character(20);
9. 三、修改数据类型及注释
10. 先将 student_gender 的数据类型从 character(1) 修改为 integer：
11. # ALTER TABLE student_info ALTER COLUMN student_gender TYPE integer;
12. 如果执行上面sql一定会报错的，因为上面这种情况只适合修改前后数据类型一致的情况，需要执行下面sql,
13. 但是不保证一定成功，除非你存储的数据能成功转换为目标类型，像我实践中就报错，所以要保证数据的合理性
15. ALTER TABLE student_info
16. ALTER COLUMN student_gender TYPE integer
17. USING CAST(student_gender AS integer);
19. 然后为 student_age 添加注释 “学生年龄”。在 PostgreSQL 中，添加列注释可以使用 COMMENT ON COLUMN 语句：
20. COMMENT ON COLUMN student_info.student_age IS '学生年龄';
22. 完成上述操作后，你可以再次使用 d student_info 命令来查看表结构的变化情况，此时表结构应该符合你修改后的要求。
23. d student_info

复制代码

关于看不见添加的注释问题缘故原由分析：
d student_info 命令来查察表结构。这个命令在某些数据库客户端中可能不会表现列注释。
你可以实行利用以下查询语句来查察带有注释的表结构：

1. SELECT column_name, data_type, column_default, is_nullable, col_description((table_schema || '.' || table_name)::regclass,ordinal_position) AS comment
2. FROM information_schema.columns
3. WHERE table_schema = 'public' AND table_name = 'student_info';

复制代码

这个查询会从 information_schema.columns 中获取列的详细信息，包罗注释。
数据库设置或客户端问题
有些数据库客户端可能不支持表现注释，或者需要特定的设置才能表现注释。你可以查抄一下你利用的数据库客户端的设置，看是否有相关选项可以启用注释表现。
注释未正确添加
虽然从截图中看到了添加注释的操作，但有可能由于某种缘故原由（例如权限问题或数据库错误）导致注释没有乐成添加。你可以实行重新添加注释，然后利用上述查询语句来验证注释是否添加乐成。
删除库表

一般来说直接 drop database mydb;就可以删除掉数据库了，下面我们说下相关注意事项

执行这条命令之前要确保你当前没有毗连到 mydb 数据库哦，不然也会报错无法删除的。
一般可以先通过 c 命令切换到其他数据库（比如 postgres），然后再执行删除 mydb 的操作。
例如：
c postgres
drop database mydb;
当前还有其他用户（或者其他会话）正在毗连并利用 mydb 数据库时，是无法直接删除它的。
你可以通过以下几种方法来办理这个问题并乐成删除 mydb 数据库：
方法一：停止其他正在利用的会话
首先，你需要查察当前正在利用 mydb 数据库的会话信息。可以利用以下查询语句（在 postgres 数据库下执行）：
SELECT pid, usename, datname, application_name, client_addr, client_hostname, client_port FROM pg_stat_activity WHERE datname ='mydb';
这条查询语句会列出正在利用 mydb 数据库的会话的历程 ID（pid）、用户名（usename）、数据库名（datname）、应用程序名（application_name）、客户端地址（client_addr）、客户端主机名（client_hostname）以及客户端端口（client_port）等信息。
找到对应的会话历程 ID 后，你可以利用以下命令来停止该会话（需要有足够的权限，比如超级用户权限）：
SELECT pg_terminate_backend(pid);
这里的 pid 就是你在上一步查询中找到的谁人正在利用 mydb 数据库的会话的历程 ID。
停止全部正在利用 mydb 数据库的会话后，再实行执行删除数据库的命令：
drop database mydb;
方法二：等候其他会话竣事自行利用
假如你确定其他正在利用 mydb 数据库的会话会在不久后自行竣事，你可以稍作等候，然后再重新实行执行删除数据库的命令：drop database mydb;
不过这种方法可能不太确定需要等候多长时间，而且假如其他会话一直不竣事，就无法顺遂删除数据库啦。
pgsql删除正在利用的数据库

这个了解一下就行，也就测试阶段利用

1. -- 设置数据库禁止连接
2. UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'db_name';
3. -- 中断当前所有连接会话
4. SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'db_name';
5. -- 删除数据库
6. drop database db_name;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。