pg基础命令（1）

韶博雅

已于 2023-09-18 16:26:01 修改

阅读量110

点赞数

文章标签： oracle 数据库

于 2023-09-14 15:13:57 首次发布

本文链接：https://blog.csdn.net/weixin_39735909/article/details/132873526

版权

1.基本操作

1.1 基础命令

1.可使用“\d”显示数据库中有哪些表
test=# create table a(id int);
CREATE TABLE
test=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | a    | table | postgres


2.\d tab_name 查看表结构
test=# \d a
                 Table "public.a"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 


3.“学号（no）”为主键，则在列定义后面加上“primary key”。
test=# CREATE TABLE student(no int primary key, student_name varchar(40), age int);
CREATE TABLE


4.删除表的语法
test=# DROP TABLE student;
DROP TABLE


5. psql -l  非交互模式查看数据库
[pgsql@19c ~]$ psql -l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 testdb    | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(5 rows)
PS:当用户创建数据库时，默认是从模板数据
库“template1”克隆来的，所以通常我们可以定制template1数据库中的内
容，如向template1中添加一些表后函数，这样后续创建的数据库就会继
承template1中的内容，也会拥有这些表和函数。而template0是一个最简
化的模板库，如果创建数据库时明确指定从此数据库克隆，将创建出一
个最简化的数据库。

1.2 psql连接数据库的常用的方法

psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]
postgres=# create user user_test with password 'user_test' nocreatedb;
CREATE ROLE

2.连接
postgres=# alter database testdb owner to user_test;
ALTER DATABASE

[pgsql@19c ~]$ psql -h 192.168.56.131 -p 5432 testdb user_test
Password for user user_test: 
psql (13.2)
Type "help" for help.

testdb=> \d
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | student | table | postgres
(1 row)

1.3 psql常用命令

1  \h
testdb=> \h create user
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN


2. \d
1）如果“\d”命令后什么都不带，将列出当前数据库中的所有表
2）“\d”命令后面跟一个表名，表示显示这个表的结构定义
3）“\d”命令也可以用于显示索引信息
4）“\d”命令后面的表名或索引名中也可以使用通配符，如“*”或“?”等
testdb=> \d s*
               Table "public.stdad"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 

                     Table "public.student"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null | 
 name   | character varying(20) |           |          | 
ps:显示s开头的所有表结构

5）使用“\d+”命令可以显示比“\d”命令的执行结果更详细的信息
6）匹配不同对象类型的“\d”命令如下：
·如果只想显示匹配的表，可以使用“\dt”命令。
·如果只想显示索引，可以使用“\di”命令。
·如果只想显示序列，可以使用“\ds”命令。
·如果只想显示视图，可以使用“\dv”命令。
·如果想显示函数，可以使用“\df”命令。
7）如果想显示执行SQL语句的时间，可以用“\timing”命令
8）要想列出所有的schema，可以使用“\dn”命令
9）要想显示所有的表空间，可以用“\db”命令
PS:	实际上，PostgreSQL中的表空间对应一个目录，放在这个表空间中的表，就是把表的数据文件放到该表空间下

10)实际上，PostgreSQL中的表空间对应一个目录，放在这个表空间中的表，就是把表的数据文件放到该表空间下
testdb=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user_test |                                                            | {}
PS:“\du”和“\dg”命令等价。原因是，在PostgreSQL数据库中，用户和角色是不分的。

11）“\dp”或“\z”命令用于显示表的权限分配情况
testdb=> \dp
                              Access privileges
 Schema |  Name   | Type  | Access privileges | Column privileges | Policies 
--------+---------+-------+-------------------+-------------------+----------
 public | stdad   | table |                   |                   | 
 public | student | table |                   |                   |

1.4 格式化输出

testdb=> \pset
border                   1
columns                  0

testdb=# select * from student;
 id |   name   
----+----------
  1 | zhangsan

1.testdb=# \pset border 2
Border style is 2.
testdb=#  select * from student;

+----+----------+
| id |   name   |
+----+----------+
|  1 | zhangsan |
+----+----------+

PS:
·\pset border 0：表示输出内容无边框。
·\pset border 1：表示输出内容只有内边框。
·\pset border 2：表示输出内容内外都有边框。
psql中默认的输出格式是“\pset border 1”


2.如以逗号分隔或以Tab分隔的文本文件，这时就需要用到“\pset format unaligned”命令了
3.默认分隔符是“|”，我们可以用命令“\pset fieldsep”来设置分隔符
testdb=#  \pset fieldsep '\t'
Field separator is "	".
testdb=#  select * from student;

id	name
1	zhangsan


4.把SQL命令输出到一个文件中
testdb=# testdb=#  \o 111.txt
testdb=# select * from student;
testdb=# \q
[pgsql@19c ~]$ cat 111.txt 
id	name
1	zhangsan


5.“\x”命令
testdb=# \x
Expanded display is on.
testdb=# select * from student;
-[ RECORD 1 ]--
id   | 1
name | zhangsan


6.命令“\i <文件名>”用于执行存储在外部文件中的SQL语句或命令
testdb=# \i a.sql
-[ RECORD 1 ]--
id   | 1
name | zhangsan


“\e”后面也可以指定一个文件名，但要求这个文件必须存在

1.5 自动提交技巧

在psql中事务是自动提交的，比如，执行完一条DELETE或UPDATE语句后，事务就会自动提交，如果不想让事务自动提交，方法有两种。
1)osdba=# begin;
BEGIN
osdba=# update x1 set name='xxxxx' where id=1;
UPDATE 1
osdba=# select * from x1;
id | name
----+--------
2 | bbbbbb
3 | cccccc
1 | xxxxx
(3 rows)
osdba=# rollback;
ROLLBACK
osdba=# select * from x1;
id | name
----+--------
1 | aaaaaa
2 | bbbbbb
3 | cccccc


2) 直接使用psql中的命令关闭自动提交功能
\set AUTOCOMMIT off
PS:个命令中的“AUTOCOMMIT”是大写的，不能使用小写，如果使用小写，虽不会报错，但会导致关闭自动提交的操作无效


--在启动psql的命令行中加上“-E”参数，就可以把psql中各种以“\”开头的命令执行的实际SQL语句打印出来
[pgsql@19c ~]$  psql -E postgres
psql (13.2)
Type "help" for help.

postgres=# \d

--如果在已运行的psql中显示了某个命令实际执行的SQL语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN on|off”命令
postgres=#  \set ECHO_HIDDEN on
postgres=# \dn
********* QUERY **********
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
**************************

  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

postgres=# \set ECHO_HIDDEN off
postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

2. 数据库基本操作

2.1 数据库的基本操作包括创建、删除和修改数据库等

--创建数据库
postgres-# \h create database 
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/13/sql-createdatabase.html

·OWNER [=] user_name：用于指定新建的数据库属于哪个用户，如果不指定，新建的数据库就属于当前执行命令的用户。
·TEMPLATE [=] template：模板名（从哪个模板创建新数据库），如果不指定，将使用默认模板数据库（template1）。
·[ENCODING [=] encoding]：创建新数据库使用的字符编码。比如使用ISO-8859-1（LATIN1）编码创建一个数据库时
·TABLESPACE [=] tablespace：用于指定和新数据库关联的表空间名称。
·CONNECTION LIMIT [=] connlimit]：用于指定数据库可以接受多少并发的连接。默认值为“-1”，表示没有限制。通常使用时很少会用到指定数据库的字符集，因为PostgreSQL数据库服务端并不支持汉字字符集GBK GB180 30，所以一般都是使用UTF8字符集来支持中文的

2.2 修改数据库

postgres-# \h alter database
Command:     ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

URL: https://www.postgresql.org/docs/13/sql-alterdatabase.html


这里的“option”可以以下几种语法结构：
·CONNECTION LIMIT connlimit。
·ALTER DATABASE name RENAME TO new_name。
·ALTER DATABASE name OWNER TO new_owner。
·ALTER DATABASE name SET TABLESPACE new_tablespace。
·ALTER DATABASE name SET configuration_parameter {TO |=}
{value|DEFAULT}。
·ALTER DATABASE name SET configuration_parameter FROM CURRENT。
·ALTER DATABASE name RESET configuration_parameter。
·ALTER DATABASE name RESET ALL。
示例1，将数据库“testdb01”的最大连接数修改为“10”，命令如下
postgres=# create database testdb01;
CREATE DATABASE
postgres=# alter database testdb01 CONNECTION LIMIT 10;
ALTER DATABASE