书接上文，上篇文章刚介绍了 PG 单机安装，菜鸟都可以看懂的 PostgreSQL 单机安装与基础知识学习手册（上），这一篇我们来学习数据库的简单操作。
三、数据库简单操作示例
以上配置好数据库，可以进行简单的操作学习了，使用 psql 直接进入数据库，如同oracle 里的 sqlplus 命令。
首先第一个就是 help 命令，详细信息如下：
[postgres@jiekexu-test ~]$ psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "postgres")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
--csv CSV (Comma-Separated Values) table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "/home/postgres/pgdata")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "postgres")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
[postgres@jiekexu-test ~]$
根据以上信息，使用 psql 进入数据库，相当于 Oracle 数据库的 SQL plus 命令。这样也可以查看数据库的版本亦或者使用 psql -V 也可以查看数据库版本。
\help --帮助信息
\l --列出当前系统所有数据库信息
[postgres@jiekexu-test ~]$ psql -V
psql (PostgreSQL) 13.4
--通过默认端口 5432 登录
[postgres@jiekexu-test ~]$ psql
psql (13.4)
Type "help" for help.
postgres=#
postgres=# \help
Available help:
ABORT ALTER TEXT SEARCH TEMPLATE CREATE PUBLICATION DROP FUNCTION IMPORT FOREIGN SCHEMA
ALTER AGGREGATE ALTER TRIGGER CREATE ROLE DROP GROUP INSERT
ALTER COLLATION ALTER TYPE CREATE RULE DROP INDEX LISTEN
ALTER CONVERSION ALTER USER CREATE SCHEMA DROP LANGUAGE LOAD
ALTER DATABASE ALTER USER MAPPING CREATE SEQUENCE DROP MATERIALIZED VIEW LOCK
ALTER DEFAULT PRIVILEGES ALTER VIEW CREATE SERVER DROP OPERATOR MOVE
ALTER DOMAIN ANALYZE CREATE STATISTICS DROP OPERATOR CLASS NOTIFY
ALTER EVENT TRIGGER BEGIN CREATE SUBSCRIPTION DROP OPERATOR FAMILY PREPARE
ALTER EXTENSION CALL CREATE TABLE DROP OWNED PREPARE TRANSACTION
ALTER FOREIGN DATA WRAPPER CHECKPOINT CREATE TABLE AS DROP POLICY REASSIGN OWNED
ALTER FOREIGN TABLE CLOSE CREATE TABLESPACE DROP PROCEDURE REFRESH MATERIALIZED VIEW
ALTER FUNCTION CLUSTER CREATE TEXT SEARCH CONFIGURATION DROP PUBLICATION REINDEX
ALTER GROUP COMMENT CREATE TEXT SEARCH DICTIONARY DROP ROLE RELEASE SAVEPOINT
ALTER INDEX COMMIT CREATE TEXT SEARCH PARSER DROP ROUTINE RESET
ALTER LANGUAGE COMMIT PREPARED CREATE TEXT SEARCH TEMPLATE DROP RULE REVOKE
ALTER LARGE OBJECT COPY CREATE TRANSFORM DROP SCHEMA ROLLBACK
ALTER MATERIALIZED VIEW CREATE ACCESS METHOD CREATE TRIGGER DROP SEQUENCE ROLLBACK PREPARED
ALTER OPERATOR CREATE AGGREGATE CREATE TYPE DROP SERVER ROLLBACK TO SAVEPOINT
ALTER OPERATOR CLASS CREATE CAST CREATE USER DROP STATISTICS SAVEPOINT
ALTER OPERATOR FAMILY CREATE COLLATION CREATE USER MAPPING DROP SUBSCRIPTION SECURITY LABEL
ALTER POLICY CREATE CONVERSION CREATE VIEW DROP TABLE SELECT
ALTER PROCEDURE CREATE DATABASE DEALLOCATE DROP TABLESPACE SELECT INTO
ALTER PUBLICATION CREATE DOMAIN DECLARE DROP TEXT SEARCH CONFIGURATION SET
ALTER ROLE CREATE EVENT TRIGGER DELETE DROP TEXT SEARCH DICTIONARY SET CONSTRAINTS
ALTER ROUTINE CREATE EXTENSION DISCARD DROP TEXT SEARCH PARSER SET ROLE
ALTER RULE CREATE FOREIGN DATA WRAPPER DO DROP TEXT SEARCH TEMPLATE SET SESSION AUTHORIZATION
ALTER SCHEMA CREATE FOREIGN TABLE DROP ACCESS METHOD DROP TRANSFORM SET TRANSACTION
ALTER SEQUENCE CREATE FUNCTION DROP AGGREGATE DROP TRIGGER SHOW
ALTER SERVER CREATE GROUP DROP CAST DROP TYPE START TRANSACTION
ALTER STATISTICS CREATE INDEX DROP COLLATION DROP USER TABLE
ALTER SUBSCRIPTION CREATE LANGUAGE DROP CONVERSION DROP USER MAPPING TRUNCATE
ALTER SYSTEM CREATE MATERIALIZED VIEW DROP DATABASE DROP VIEW UNLISTEN
ALTER TABLE CREATE OPERATOR DROP DOMAIN END UPDATE
ALTER TABLESPACE CREATE OPERATOR CLASS DROP EVENT TRIGGER EXECUTE VACUUM
ALTER TEXT SEARCH CONFIGURATION CREATE OPERATOR FAMILY DROP EXTENSION EXPLAIN VALUES
ALTER TEXT SEARCH DICTIONARY CREATE POLICY DROP FOREIGN DATA WRAPPER FETCH WITH
ALTER TEXT SEARCH PARSER CREATE PROCEDURE DROP FOREIGN TABLE GRANT
postgres=#
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
0、创建数据库，查看数据库，删除数据库
postgres=# select * from pg_database;
postgres=# create database testdb;
postgres=# create database JiekeXu;
CREATE DATABASE
postgres=# \l
List ofdatabases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
jiekexu | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | C | C |
(5 rows)
postgres=# drop database TestDB;
DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
jiekexu | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
1、创建表，查看表，删除表，截断表
1)、查看数据库大小
postgres=# select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;
datname | size
-----------+---------
postgres | 7885 kB
jiekexu | 7901 kB
template1 | 7737 kB
template0 | 7737 kB
testdb | 7737 kB
(5 rows)
2)、查看某一个数据库大小
postgres=# select pg_database_size('jiekexu');
pg_database_size
------------------
8090159
(1 row)
3)、按顺序查看索引
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
4)、查看所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
切换数据库
postgres=# \c jiekexu
You are now connected to database"jiekexu" as user "postgres".
创建表
jiekexu=# create table test(id int,name char(20), age int);
CREATE TABLE
jiekexu=#
jiekexu=# insert into test values(1,'JiekeXu',18);
INSERT 0 1
jiekexu=# insert into test values(2,'Adventure',25);
INSERT 0 1
jiekexu=#
查看表信息
jiekexu=# \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | test | table | postgres
public | test1 | table | postgres
public | test2 | table | postgres
(3 rows)
jiekexu=# \d test
Table"public.test"
Column | Type | Collation | Nullable |Default
--------+---------------+-----------+----------+---------
id |integer | | |
name |character(20) | | |
age |integer
查看表内容
jiekexu=# select * from test;
id| name | age
----+----------------------+-----
1 |JiekeXu | 18
2 |Adventure | 25
(2 rows)
修改表内容
jiekexu=# update test set age=26 where name='JiekeXu';
UPDATE 1
jiekexu=# commit;
WARNING: there is no transaction in progress
COMMIT
jiekexu=# select * from test;
id| name | age
----+----------------------+-----
2 |Adventure | 25
1 |JiekeXu | 26
(2 rows)
删除表数据
drop table test2;
delete from test;
jiekexu=# select * from test;
id| name | age
----+----------------------+-----
2 |Adventure | 25
1 |JiekeXu | 26
2 |BreatHeat | 22
(3 rows)
jiekexu=# delete from test where age=22;
DELETE 1
jiekexu=# select * from test;
id| name | age
----+----------------------+-----
2 |Adventure | 25
1 |JiekeXu | 26
(2 rows)
jiekexu=# drop table test2;
DROP TABLE
jiekexu=#
jiekexu=# \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | test | table | postgres
public | test1 | table | postgres
(2 rows)
截断表
Truncate 截断表如果不是自动提交则是可以回滚，则 truncate 属于 DML 语句。
jiekexu=# create table t (id int);
CREATE TABLE
jiekexu=# insert into t values (1),(2),(3),(4);
INSERT 0 4
jiekexu=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t | table | postgres
(1 row)
jiekexu=# select * from t;
id
----
1
2
3
4
(4 rows)
jiekexu=# commit;
WARNING: there is no transaction in progress
COMMIT
jiekexu=#
jiekexu=# begin;
BEGIN
jiekexu=*# truncate table t;
TRUNCATE TABLE
jiekexu=*# select * from t;
id
----
(0 rows)
jiekexu=*# rollback;
ROLLBACK
jiekexu=# select * from t;
id
----
1
2
3
4
(4 rows)
查看用户
postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
(1 row)
查看数据库 jiekexu 用户的拥有者是 postgres
postgres=# \l jiekexu
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------+----------+----------+---------+-------+-------------------
jiekexu | postgres | UTF8 | C | C |
(1 row)
最后，使用 \q 或者 Ctrl + d 退出命令行
jiekexu=# \q
[postgres@jiekexu-test ~]$
2、图形化工具
pgAdmin4 是开源数据库 PostgreSQL 的图形管理工具，是桌面版图形管理工具pgAdmin3 的重写，遵循 PostgreSQL 协议 是开源、免费、可商用的。pgAdmin4 是python 开发的 web 应用程序，既可以部署为 web 模式通过浏览器访问，也可以部署为桌面模式独立运行。
下载：https://www.pgadmin.org/download/
下载：https://www.postgresql.org/ftp/pgadmin/pgadmin4/v8.4/windows/
2024-03-07 已经更新到 8.4 版本，Windows下的安装就是点击下一步、下一步，这里不再多说了。如果我们安装了 Windows 版本的 PostgreSQL 数据库，那么默认在安装目录下也自带了 pgAdmin4 工具。
3、数据类型简介
PostgreSQL 有着丰富的本地数据类型可用。用户可以使用 CREATE TYPE 命令为 PostgreSQL 增加新的数据类型。
每种数据类型都有一个由其输入和输出函数决定的外部表现形式，许多内建的类型有明显的
格式。不过，许多类型要么是 PostgreSQL 所特有的（例如几何路径），要么可能是有几种不同的格式（例如日期和时间类型），有些输入和输出函数是不可逆的，即输出函数的结果
和原始输入比较时可能丢失精度。
下面对 PG 的数据类型做一个简单的罗列，具体不做介绍了，因为不经常开发，也不怎么建表，这块的详细内容先忽略掉吧。只简单看看下图即可。
图片来自 PostgreSQL 修炼之道：从小工到专家（第2版）
PostgreSQL有两种 JSON 数据类型：json 和 jsonb。
● 对于 jsonb 类型数据来说，GIN 索引比 B-tree 索引更有效率，因为 B-tree 索引无法搜索 jsonb 内部的数据结构。
● 对于 json 类型数据来说，PostgreSQL 不支持创建索引。
全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
