SRE文档PostgreSQL的使用向导
0.快捷键
- 显示所有数据库
sql
\l 显示所有数据库
postgres=# \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
(3 rows)\l 显示所有数据库
postgres=# \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
(3 rows)- 切换当前数据库
sql
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=#postgres=# \c test
You are now connected to database "test" as user "postgres".
test=#- 显示数据库中有哪些表
sql
test=# \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | test1 | table | postgres
(1 row)test=# \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | test1 | table | postgres
(1 row)- 显示表 tb_name的定义
sql
test=# \d test1
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+--------------+-----------+----------+---------
name | character(1) | | |test=# \d test1
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+--------------+-----------+----------+---------
name | character(1) | | |- 显示更表详细信息
sql
test=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------+-------+----------+---------+-------------
public | test1 | table | postgres | 0 bytes |test=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------+-------+----------+---------+-------------
public | test1 | table | postgres | 0 bytes |- 只显示表
sql
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | test1 | table | postgres
(1 row)test=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | test1 | table | postgres
(1 row)- 只显示索引
\di 只显示索引
\ds 只显示序列
\dv 只显示视图
\du 或者 \dg 显示所有角色或用户\di 只显示索引
\ds 只显示序列
\dv 只显示视图
\du 或者 \dg 显示所有角色或用户- 显示表的权限分配情况
sql
\dp tb_name
test=# \dp test1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------+-------+-------------------+-------------------+----------
public | test1 | table | | |
(1 row)\dp tb_name
test=# \dp test1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------+-------+-------------------+-------------------+----------
public | test1 | table | | |
(1 row)\timing on 显示sql执行的时间
\timing off
\encoding utf8 指定客户端字符编码
\pset border 0 输出内容无边框
\pset border 1 边框只在内部
\pset border 2 内部、外部都有边框
\x 一行数据的每一列，单行显示，相当于 mysql的 \G
\s 显示执行的命令历史记录
\? 显示帮助信息\timing on 显示sql执行的时间
\timing off
\encoding utf8 指定客户端字符编码
\pset border 0 输出内容无边框
\pset border 1 边框只在内部
\pset border 2 内部、外部都有边框
\x 一行数据的每一列，单行显示，相当于 mysql的 \G
\s 显示执行的命令历史记录
\? 显示帮助信息https://www.modb.pro/doc/446#pf46
DDL（表结构操控）
CREATE、ALTER、DROP、RENAME、TRUNCATECREATE、ALTER、DROP、RENAME、TRUNCATEDML（数据操作）
INSERT、UPDATE、DELETE CALL（ 调用过程）、MERGE（合并（插入或修改））、
COMMIT（事务提交）、ROLLBACK（事务回滚）INSERT、UPDATE、DELETE CALL（ 调用过程）、MERGE（合并（插入或修改））、
COMMIT（事务提交）、ROLLBACK（事务回滚）DCL（数据控制语言）
sql
CRANT（权限授予）、REVOKE（权限回收）CRANT（权限授予）、REVOKE（权限回收）DQL（数据查询语言）
SELECT、FROM 、WHERESELECT、FROM 、WHERE常用操作：
\help命令——用于查看各个子命令的语法帮助
\help create
\help create databse
\help create user
\l命令——也就是list，用于列出所有已创建的数据库，支持通配符过滤
\d 命令——也就是display，用于查看数据库信息，支持通配符过滤
\d #列出当前库下的所有表
\d table_name #跟上表名显示表结构，支持通配符
\db #显示所有表空间
\dn #列出所有schema
\du #显示所有用户或角色，也可以写为\dg
\dp table_name #显示表的权限分配情况，也可以跟+号显示更详细
\i命令——也就是import，执行指定SQL文件
\i /data/test.sql\help命令——用于查看各个子命令的语法帮助
\help create
\help create databse
\help create user
\l命令——也就是list，用于列出所有已创建的数据库，支持通配符过滤
\d 命令——也就是display，用于查看数据库信息，支持通配符过滤
\d #列出当前库下的所有表
\d table_name #跟上表名显示表结构，支持通配符
\db #显示所有表空间
\dn #列出所有schema
\du #显示所有用户或角色，也可以写为\dg
\dp table_name #显示表的权限分配情况，也可以跟+号显示更详细
\i命令——也就是import，执行指定SQL文件
\i /data/test.sql\l 查看所有数据库
\d 查看当前数据库中所有表名字
\dn 列出所有schema
\x 把表中的每一行数据拆分为单元，与mysql中\G 类似
\echo 输出信息
1.数据库
创建数据库
bash
??
[postgres@db ~]$ createdb zsddb??
[postgres@db ~]$ createdb zsddb查看版本
bash
zsddb=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)
-- 或者
postgres@postgres=> show server_version;
server_version
----------------
12.2
(1 row)
-- 或者
postgres@postgres=>select current_setting('server_version_num');
current_setting
-----------------
120002
(1 row)
postgres@postgres=>show server_version_num;
server_version_num
--------------------
120002
(1 row)zsddb=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)
-- 或者
postgres@postgres=> show server_version;
server_version
----------------
12.2
(1 row)
-- 或者
postgres@postgres=>select current_setting('server_version_num');
current_setting
-----------------
120002
(1 row)
postgres@postgres=>show server_version_num;
server_version_num
--------------------
120002
(1 row)查看当前时间日期
bash
zsddb=# SELECT current_date;
current_date
--------------
2019-12-02
(1 row)zsddb=# SELECT current_date;
current_date
--------------
2019-12-02
(1 row)获得帮助命令
bash
zsddb=# \hzsddb=# \h退出psql客户端
bash
两种方式，如下:
zsddb=# quit
zsddb=# \q两种方式，如下:
zsddb=# quit
zsddb=# \q查看所有数据库
sql
查看所有数据库：
zsddb=#\l
切换数据库：
zsddb=#\c databasename
查看所有数据库（包括详细参数）：
zsddb=#select * from pg_database;查看所有数据库：
zsddb=#\l
切换数据库：
zsddb=#\c databasename
查看所有数据库（包括详细参数）：
zsddb=#select * from pg_database;查看某数据库下表
sql
用户自定义的表，如果未经特殊处理，默认都是放在名为public的schema下
konga=>select * from pg_tables where schemaname = 'public';
或者
select tablename from pg_tables where schemaname='public'
#获取db中所有的表信息
konga=> select * from pg_tables;
#查看表结构
konga=> \d routes;
Table "public.routes"
Column | Type | Collation | Nullable | Default
----------------------------+--------------------------+-----------+----------+---------
id | uuid | | not null |
created_at | timestamp with time zone | | |
updated_at | timestamp with time zone | | |用户自定义的表，如果未经特殊处理，默认都是放在名为public的schema下
konga=>select * from pg_tables where schemaname = 'public';
或者
select tablename from pg_tables where schemaname='public'
#获取db中所有的表信息
konga=> select * from pg_tables;
#查看表结构
konga=> \d routes;
Table "public.routes"
Column | Type | Collation | Nullable | Default
----------------------------+--------------------------+-----------+----------+---------
id | uuid | | not null |
created_at | timestamp with time zone | | |
updated_at | timestamp with time zone | | |查看表大小
sql
konga=> \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------------------------------+----------+-------+------------+-------------
public | acls | table | konga | 8192 bytes |
public | apis | table | konga | 8192 bytes |
public | basicauth_credentials | table | konga | 8192 bytes |
public | ca_certificates | table | konga | 8192 bytes |
public | certificates | table | konga | 8192 bytes |
public | cluster_ca | table | konga | 32 kB |
#查看单个表大小
konga=> select pg_size_pretty(pg_relation_size('表名字'));
pg_size_pretty
----------------
8192 bytes
(1 row)
#查看单个数据库大小
konga=> select pg_size_pretty(pg_database_size('数据库名字'));
pg_size_pretty
----------------
11 MB
(1 row)
#查看所有表大小
test01=#select schemaname,tablename,pg_table_size(schemaname||'.'||tablename) as tabsize from pg_tables order by 3 desc;konga=> \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------------------------------+----------+-------+------------+-------------
public | acls | table | konga | 8192 bytes |
public | apis | table | konga | 8192 bytes |
public | basicauth_credentials | table | konga | 8192 bytes |
public | ca_certificates | table | konga | 8192 bytes |
public | certificates | table | konga | 8192 bytes |
public | cluster_ca | table | konga | 32 kB |
#查看单个表大小
konga=> select pg_size_pretty(pg_relation_size('表名字'));
pg_size_pretty
----------------
8192 bytes
(1 row)
#查看单个数据库大小
konga=> select pg_size_pretty(pg_database_size('数据库名字'));
pg_size_pretty
----------------
11 MB
(1 row)
#查看所有表大小
test01=#select schemaname,tablename,pg_table_size(schemaname||'.'||tablename) as tabsize from pg_tables order by 3 desc;pg查看系统表
\dS显示有哪些系统表
\d pg_class显示某一个系统表
sql
konga=> \dS
List of relations
Schema | Name | Type | Owner
------------+--------------------------------------+----------+----------
pg_catalog | pg_aggregate | table | postgres
pg_catalog | pg_am | table | postgres
konga=> \d pg_class
Table "pg_catalog.pg_class"
Column | Type | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
relname | name | | not null |
relnamespace | oid | | not null |
reltype | oid | | not null |
reloftype | oid | | not null |
relowner | oid | | not null |
relam | oid | | not null |konga=> \dS
List of relations
Schema | Name | Type | Owner
------------+--------------------------------------+----------+----------
pg_catalog | pg_aggregate | table | postgres
pg_catalog | pg_am | table | postgres
konga=> \d pg_class
Table "pg_catalog.pg_class"
Column | Type | Collation | Nullable | Default
---------------------+--------------+-----------+----------+---------
relname | name | | not null |
relnamespace | oid | | not null |
reltype | oid | | not null |
reloftype | oid | | not null |
relowner | oid | | not null |
relam | oid | | not null |查看配置文件位置
sql
postgres=# select name,setting from pg_settings where category='File Locations';
name | setting
-------------------+-----------------------------------
config_file | /data/pgdata/data/postgresql.conf
data_directory | /data/pgdata/data
external_pid_file |
hba_file | /data/pgdata/data/pg_hba.conf
ident_file | /data/pgdata/data/pg_ident.conf
(5 rows)postgres=# select name,setting from pg_settings where category='File Locations';
name | setting
-------------------+-----------------------------------
config_file | /data/pgdata/data/postgresql.conf
data_directory | /data/pgdata/data
external_pid_file |
hba_file | /data/pgdata/data/pg_hba.conf
ident_file | /data/pgdata/data/pg_ident.conf
(5 rows)字符串用单引号扩起来，结尾使用分号结束
查看port
sql
postgres@postgres=>select * from current_setting('port');
current_setting
-----------------
5532
(1 row)postgres@postgres=>select * from current_setting('port');
current_setting
-----------------
5532
(1 row)查看数据库对应的目录
sql
-- 根据oid 查找
han_db=# select oid,datname from pg_database;
oid | datname
-------+-----------
13447 | postgres
16385 | han_db
1 | template1
13446 | template0
(4 rows)-- 根据oid 查找
han_db=# select oid,datname from pg_database;
oid | datname
-------+-----------
13447 | postgres
16385 | han_db
1 | template1
13446 | template0
(4 rows)关闭数据库连接
#查看所有
postgres=#SELECT datname,pid FROM pg_stat_activity;
postgres=# select pid from pg_stat_activity where datname = 'agile' ;
pid
---------
2544156
(1 row)
postgres=# select pg_terminate_backend(2544156) ;
pg_terminate_backend
----------------------
t
(1 row)
#或者
-- 关闭数据库连接，Database_Name为数据库名称
select pg_terminate_backend(pid) from (select pid from pg_stat_activity where datname = 'Database_Name' ) a;
-- 删除数据库
DROP DATABASE Database_Name;#查看所有
postgres=#SELECT datname,pid FROM pg_stat_activity;
postgres=# select pid from pg_stat_activity where datname = 'agile' ;
pid
---------
2544156
(1 row)
postgres=# select pg_terminate_backend(2544156) ;
pg_terminate_backend
----------------------
t
(1 row)
#或者
-- 关闭数据库连接，Database_Name为数据库名称
select pg_terminate_backend(pid) from (select pid from pg_stat_activity where datname = 'Database_Name' ) a;
-- 删除数据库
DROP DATABASE Database_Name;2.表
sql
#创建表
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);#创建表
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);删除表
sql
DROP TABLE tablename;
#清空表里内容
truncate locktest ;
-- 注意，adg 表名字
han_db=# select pg_relation_filepath('adg');
pg_relation_filepath
----------------------
base/16385/16386
(1 row)
han_db=#drop table adg;
-- postgres drop table后会删除物理文件DROP TABLE tablename;
#清空表里内容
truncate locktest ;
-- 注意，adg 表名字
han_db=# select pg_relation_filepath('adg');
pg_relation_filepath
----------------------
base/16385/16386
(1 row)
han_db=#drop table adg;
-- postgres drop table后会删除物理文件插入数据
sql
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
The point type requires a coordinate pair as input, as shown here:
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
The point type requires a coordinate pair as input, as shown here:
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);数据库导出成csv文本文件
sql
zsddb=# copy weather to '/home/postgres/weather.csv' delimiter ',' csv header;
COPY 3
zsddb=# exit
[postgres@db ~]$ cat /home/postgres/weather.csv
city,temp_lo,temp_hi,prcp,date
San Francisco,46,50,0.25,1994-11-27
San Francisco,43,57,0,1994-11-29
Hayward,37,54,,1994-11-29zsddb=# copy weather to '/home/postgres/weather.csv' delimiter ',' csv header;
COPY 3
zsddb=# exit
[postgres@db ~]$ cat /home/postgres/weather.csv
city,temp_lo,temp_hi,prcp,date
San Francisco,46,50,0.25,1994-11-27
San Francisco,43,57,0,1994-11-29
Hayward,37,54,,1994-11-29复制一张表
sql
test=# select * into tbl_insert1 from tbl_insert ;
SELECT 14
test=# select * into tbl_insert1 from tbl_insert ;
ERROR: relation "tbl_insert1" already existstest=# select * into tbl_insert1 from tbl_insert ;
SELECT 14
test=# select * into tbl_insert1 from tbl_insert ;
ERROR: relation "tbl_insert1" already exists3.查询表的语句
查询所有的数据
sql
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)查看指定列
sql
zsddb=# SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)zsddb=# SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)查看平均温度
sql
zsddb=# SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
city | temp_avg | date
---------------+----------+------------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)zsddb=# SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
city | temp_avg | date
---------------+----------+------------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)查看城市是'San Francisco',降水率大于0的数据
sql
zsddb=# SELECT * FROM weather
zsddb-# WHERE city = 'San Francisco' AND prcp > 0.0;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)zsddb=# SELECT * FROM weather
zsddb-# WHERE city = 'San Francisco' AND prcp > 0.0;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)对城市排序
sql
zsddb=# SELECT * FROM weather
zsddb-# ORDER BY city;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
(3 rows)
#城市排序后，对温度再排序
zsddb=# SELECT * FROM weather
zsddb-# ORDER BY city, temp_lo;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27zsddb=# SELECT * FROM weather
zsddb-# ORDER BY city;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
(3 rows)
#城市排序后，对温度再排序
zsddb=# SELECT * FROM weather
zsddb-# ORDER BY city, temp_lo;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27城市去重DISTINCT
sql
zsddb=# SELECT DISTINCT city
zsddb-# FROM weather;
city
---------------
Hayward
San Francisco
(2 rows)
#去重+排序
SELECT DISTINCT city
FROM weather
ORDER BY city;zsddb=# SELECT DISTINCT city
zsddb-# FROM weather;
city
---------------
Hayward
San Francisco
(2 rows)
#去重+排序
SELECT DISTINCT city
FROM weather
ORDER BY city;4.表连接查询
查看zsddb数据库下的表名称
sql
zsddb=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | cities | table | postgres
public | weather | table | postgres
(2 rows)zsddb=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | cities | table | postgres
public | weather | table | postgres
(2 rows)查看表结构
sql
zsddb=# \d weather
Table "public.weather"
Column | Type | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
city | character varying(80) | | |
temp_lo | integer | | |
temp_hi | integer | | |
prcp | real | | |
date | date | | |
zsddb=# \d cities
Table "public.cities"
Column | Type | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
name | character varying(80) | | |
location | point | | |zsddb=# \d weather
Table "public.weather"
Column | Type | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
city | character varying(80) | | |
temp_lo | integer | | |
temp_hi | integer | | |
prcp | real | | |
date | date | | |
zsddb=# \d cities
Table "public.cities"
Column | Type | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
name | character varying(80) | | |
location | point | | |表连接查询
sql
zsddb=# SELECT *
zsddb-# FROM weather, cities
zsddb-# WHERE city = name;
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)
#一般来说，你可能会输出你想要的列而不是"*"
zsddb=# SELECT city, temp_lo, temp_hi, prcp, date, location
zsddb-# FROM weather, cities
zsddb-# WHERE city = name;
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53)zsddb=# SELECT *
zsddb-# FROM weather, cities
zsddb-# WHERE city = name;
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)
#一般来说，你可能会输出你想要的列而不是"*"
zsddb=# SELECT city, temp_lo, temp_hi, prcp, date, location
zsddb-# FROM weather, cities
zsddb-# WHERE city = name;
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53)虽然pg的parser会自动分析city和name是属于哪个表的列。但是如果有同名的列话，还需手动指定，如下:
sql
zsddb=# SELECT weather.city, weather.temp_lo, weather.temp_hi,
zsddb-# weather.prcp, weather.date, cities.location
zsddb-# FROM weather, cities
zsddb-# WHERE cities.name = weather.city;
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53)
(2 rows)
#一种替代写法如下
zsddb=# SELECT *
zsddb-# FROM weather INNER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)zsddb=# SELECT weather.city, weather.temp_lo, weather.temp_hi,
zsddb-# weather.prcp, weather.date, cities.location
zsddb-# FROM weather, cities
zsddb-# WHERE cities.name = weather.city;
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53)
(2 rows)
#一种替代写法如下
zsddb=# SELECT *
zsddb-# FROM weather INNER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)inner join的表连接
sql
zsddb=# SELECT *
zsddb-# FROM weather INNER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)zsddb=# SELECT *
zsddb-# FROM weather INNER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)outer join
sql
现在我们要把 Hayward那条记录找回来，我们需要对weather 表中每条记录与cities的表的记录相匹配，如果
匹配不到，就用"empty value"来显示，这个就需要用到outer join
zsddb=# SELECT *
zsddb-# FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
Hayward | 37 | 54 | | 1994-11-29 | |
(3 rows)现在我们要把 Hayward那条记录找回来，我们需要对weather 表中每条记录与cities的表的记录相匹配，如果
匹配不到，就用"empty value"来显示，这个就需要用到outer join
zsddb=# SELECT *
zsddb-# FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)
Hayward | 37 | 54 | | 1994-11-29 | |
(3 rows)right outer joins
full outer joins
self join
sql
#可以对表自身做一个表连接
zsddb=# SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
zsddb-# W2.city, W2.temp_lo AS low, W2.temp_hi AS high
zsddb-# FROM weather W1, weather W2
zsddb-# WHERE W1.temp_lo < W2.temp_lo
zsddb-# AND W1.temp_hi > W2.temp_hi;
city | low | high | city | low | high
---------------+-----+------+---------------+-----+------
San Francisco | 43 | 57 | San Francisco | 46 | 50
Hayward | 37 | 54 | San Francisco | 46 | 50
(2 rows)#可以对表自身做一个表连接
zsddb=# SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
zsddb-# W2.city, W2.temp_lo AS low, W2.temp_hi AS high
zsddb-# FROM weather W1, weather W2
zsddb-# WHERE W1.temp_lo < W2.temp_lo
zsddb-# AND W1.temp_hi > W2.temp_hi;
city | low | high | city | low | high
---------------+-----+------+---------------+-----+------
San Francisco | 43 | 57 | San Francisco | 46 | 50
Hayward | 37 | 54 | San Francisco | 46 | 50
(2 rows)alias
sql
#减少select打query的数量
zsddb=# SELECT *
zsddb-# FROM weather w, cities c
zsddb-# WHERE w.city = c.name;
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)#减少select打query的数量
zsddb=# SELECT *
zsddb-# FROM weather w, cities c
zsddb-# WHERE w.city = c.name;
city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53)5.Aggregate Functions 聚合函数
max
sql
#天气最大值
zsddb=# SELECT max(temp_lo) FROM weather;
max
-----
46
(1 row)
#想知道temp_lo最大值对应的城市
zsddb=# SELECT city FROM weather
zsddb-# WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city
---------------
San Francisco
(1 row)#天气最大值
zsddb=# SELECT max(temp_lo) FROM weather;
max
-----
46
(1 row)
#想知道temp_lo最大值对应的城市
zsddb=# SELECT city FROM weather
zsddb-# WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city
---------------
San Francisco
(1 row)聚合函数，需要group by来进行分组
sql
zsddb=# SELECT city, max(temp_lo)
zsddb-# FROM weather
zsddb-# GROUP BY city;
city | max
---------------+-----
Hayward | 37
San Francisco | 46
(2 rows)
#聚合函数，需要group by，再加where条件，挑选出小于40的
zsddb=# SELECT city, max(temp_lo)
zsddb-# FROM weather
zsddb-# GROUP BY city
zsddb-# HAVING max(temp_lo) < 40;
city | max
---------+-----
Hayward | 37
(1 row)zsddb=# SELECT city, max(temp_lo)
zsddb-# FROM weather
zsddb-# GROUP BY city;
city | max
---------------+-----
Hayward | 37
San Francisco | 46
(2 rows)
#聚合函数，需要group by，再加where条件，挑选出小于40的
zsddb=# SELECT city, max(temp_lo)
zsddb-# FROM weather
zsddb-# GROUP BY city
zsddb-# HAVING max(temp_lo) < 40;
city | max
---------+-----
Hayward | 37
(1 row)6.update语句的应用
sql
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
zsddb=# UPDATE weather
zsddb-# SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
zsddb-# WHERE date > '1994-11-28';
UPDATE 2
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
zsddb=# UPDATE weather
zsddb-# SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
zsddb-# WHERE date > '1994-11-28';
UPDATE 2
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29修改数据库名字
sql
-- 将数据库的名称由database2改成database1
UPDATE pg_database SET datname = 'database1' WHERE datname = 'database2';-- 将数据库的名称由database2改成database1
UPDATE pg_database SET datname = 'database1' WHERE datname = 'database2';7.delete语句的应用
sql
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29
(3 rows)
#删除指定
zsddb=# DELETE FROM weather WHERE city = 'Hayward';
DELETE 1
#删除所有
zsddb=# DELETE FROM weather;
DELETE 1
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
(2 rows)zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29
(3 rows)
#删除指定
zsddb=# DELETE FROM weather WHERE city = 'Hayward';
DELETE 1
#删除所有
zsddb=# DELETE FROM weather;
DELETE 1
zsddb=# SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
(2 rows)8.视图
sql
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
zsddb=# SELECT * FROM myview;
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 41 | 55 | 0 | 1994-11-29 | (-194,53)
(2 rows)CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
zsddb=# SELECT * FROM myview;
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 41 | 55 | 0 | 1994-11-29 | (-194,53)
(2 rows)9.事务
PostgreSQL通过BEGIN和COMMIT这一对代表事务的开启
sql
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;10.替换字段
语法：
sql
UPDATE table_name SET column_name = REPLACE(column_name, 'old', 'new') WHERE ....UPDATE table_name SET column_name = REPLACE(column_name, 'old', 'new') WHERE ....sql
update "VideoInfo" set "CoverImage"=REPLACE ("CoverImage", 'old', 'new')update "VideoInfo" set "CoverImage"=REPLACE ("CoverImage", 'old', 'new')11.alter
- 修改字段类型
go
ALTER TABLE '表名' ALTER COLUMN '列名' type 新类型;ALTER TABLE '表名' ALTER COLUMN '列名' type 新类型;10.连接管理
使用以下三个SQL语句来取消正在运行的查询并终止连接
（1）查出活动连接列表及其进程ID
sql
select * from pg_stat_activity;select * from pg_stat_activity;(2)取消连接上的活动查询
sql
select pg_cancel_backend(procid);select pg_cancel_backend(procid);该操作不会终止连接本身
（3）终止该连接
sql
select pg_terminate_backend(procid);select pg_terminate_backend(procid);如果你未停止某个连接上正在执行的语句就直接终止该连接，那么这些语句此时也会被停止掉。在上述步骤2执行完毕后，客户端应用的挂起状态被解除，即客户端可以重新执行语句，有些着急的用户会在此时再次执行刚刚被终止掉的语句，这又会导致系统陷入之前的状态。为了避免此种情况的发生，可以采用直接
终止连接的方式。
如果你希望一次性终止某个用户的所有连接，那么在9.2版及之后的版本上可以执行以下语句：
sql
select pg_terminate_backend(pid) from pg_stat_activity where username='some_role'; select pg_terminate_backend(pid) from pg_stat_activity where username='some_role';在9.2版之前的版本上可以执行以下语句：
sql
select pg_terminate_backend(procpid) from pg_stat_activity where username='some_role'; select pg_terminate_backend(procpid) from pg_stat_activity where username='some_role';11.外部调用
sql
su - postgres -c 'psql -U postgres -h 127.0.0.1 -p 5532 -x -c "select * from pg_stat_replication" -d postgres'su - postgres -c 'psql -U postgres -h 127.0.0.1 -p 5532 -x -c "select * from pg_stat_replication" -d postgres'12.timezone
sql
postgres=# show timezone;
TimeZone
----------
PRC
(1 row)
or
#格式化输出
postgres=# \x
Expanded display is on.
postgres=# select * from pg_settings where name = 'TimeZone';
-[ RECORD 1 ]---+----------------------------------------------------------------
name | TimeZone
setting | PRC
unit |
category | Client Connection Defaults / Locale and Formatting
short_desc | Sets the time zone for displaying and interpreting time stamps.
extra_desc |
context | user
vartype | string
source | configuration file
min_val |
max_val |
enumvals |
boot_val | GMT
reset_val | PRC
sourcefile | /data/pgdata/data/postgresql.conf
sourceline | 15
pending_restart | f
#查找一个本地timezone名字
postgres=# select * from pg_timezone_names;
name | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
Africa/Malabo | WAT | 01:00:00 | f
Africa/Niamey | WAT | 01:00:00 | f
Africa/Porto-Novo | WAT | 01:00:00 | f
Africa/Maseru | SAST | 02:00:00 | f
Africa/Mbabane | SAST | 02:00:00 | f
Africa/Asmera | EAT | 03:00:00 | f
Africa/Timbuktu | GMT | 00:00:00 | f
# vi /opt/pgsql/data/postgresql.conf
timezone = 'America/New_York'
修改成
timezone = 'Asia/Shanghai'
postgres=# select pg_reload_conf();
postgres=# select * from pg_settings where name = 'TimeZone';
另外timezone 还可以有sql 级，user级和db 级. e.g. ALTER DATABASE postgres SET timezone TO 'Europe/Berlin';
postgres=# select now();
now
-------------------------------
2020-07-10 11:22:05.246962+08
(1 row)
#修改系统时间
[root@localhost Asia]# date
Mon Jun 17 10:27:16 EDT 2019
[root@localhost Asia]# ls /etc/localtime
/etc/localtime
[root@localhost ~]# cd /usr/share/zoneinfo/
[root@localhost zoneinfo]# ls
Africa Chile GB Indian MST PRC UTC
America CST6CDT GB-Eire Iran MST7MDT PST8PDT WET
Antarctica Cuba GMT iso3166.tab Navajo right W-SU
Arctic EET GMT0 Israel NZ ROC zone.tab
Asia Egypt GMT-0 Jamaica NZ-CHAT ROK Zulu
Atlantic Eire GMT+0 Japan Pacific Singapore
Australia EST Greenwich Kwajalein Poland Turkey
Brazil EST5EDT Hongkong Libya Portugal UCT
Canada Etc HST MET posix Universal
CET Europe Iceland Mexico posixrules US
[root@localhost zoneinfo]# cd Asia/
[root@localhost Asia]# ls
Aden Calcutta Hong_Kong Kuala_Lumpur Pyongyang Tel_Aviv
Almaty Chita Hovd Kuching Qatar Thimbu
Amman Choibalsan Irkutsk Kuwait Qyzylorda Thimphu
Anadyr Chongqing Istanbul Macao Rangoon Tokyo
Aqtau Chungking Jakarta Macau Riyadh Tomsk
Aqtobe Colombo Jayapura Magadan Saigon Ujung_Pandang
Ashgabat Dacca Jerusalem Makassar Sakhalin Ulaanbaatar
Ashkhabad Damascus Kabul Manila Samarkand Ulan_Bator
Baghdad Dhaka Kamchatka Muscat Seoul Urumqi
[root@localhost Asia]# cp /etc/localtime /etc/localtime_bak
[root@localhost Asia]# cp Shanghai /etc/localtime
cp: overwrite ‘/etc/localtime’? yes
[root@localhost Asia]# date
Mon Jun 17 22:29:23 CST 2019postgres=# show timezone;
TimeZone
----------
PRC
(1 row)
or
#格式化输出
postgres=# \x
Expanded display is on.
postgres=# select * from pg_settings where name = 'TimeZone';
-[ RECORD 1 ]---+----------------------------------------------------------------
name | TimeZone
setting | PRC
unit |
category | Client Connection Defaults / Locale and Formatting
short_desc | Sets the time zone for displaying and interpreting time stamps.
extra_desc |
context | user
vartype | string
source | configuration file
min_val |
max_val |
enumvals |
boot_val | GMT
reset_val | PRC
sourcefile | /data/pgdata/data/postgresql.conf
sourceline | 15
pending_restart | f
#查找一个本地timezone名字
postgres=# select * from pg_timezone_names;
name | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
Africa/Malabo | WAT | 01:00:00 | f
Africa/Niamey | WAT | 01:00:00 | f
Africa/Porto-Novo | WAT | 01:00:00 | f
Africa/Maseru | SAST | 02:00:00 | f
Africa/Mbabane | SAST | 02:00:00 | f
Africa/Asmera | EAT | 03:00:00 | f
Africa/Timbuktu | GMT | 00:00:00 | f
# vi /opt/pgsql/data/postgresql.conf
timezone = 'America/New_York'
修改成
timezone = 'Asia/Shanghai'
postgres=# select pg_reload_conf();
postgres=# select * from pg_settings where name = 'TimeZone';
另外timezone 还可以有sql 级，user级和db 级. e.g. ALTER DATABASE postgres SET timezone TO 'Europe/Berlin';
postgres=# select now();
now
-------------------------------
2020-07-10 11:22:05.246962+08
(1 row)
#修改系统时间
[root@localhost Asia]# date
Mon Jun 17 10:27:16 EDT 2019
[root@localhost Asia]# ls /etc/localtime
/etc/localtime
[root@localhost ~]# cd /usr/share/zoneinfo/
[root@localhost zoneinfo]# ls
Africa Chile GB Indian MST PRC UTC
America CST6CDT GB-Eire Iran MST7MDT PST8PDT WET
Antarctica Cuba GMT iso3166.tab Navajo right W-SU
Arctic EET GMT0 Israel NZ ROC zone.tab
Asia Egypt GMT-0 Jamaica NZ-CHAT ROK Zulu
Atlantic Eire GMT+0 Japan Pacific Singapore
Australia EST Greenwich Kwajalein Poland Turkey
Brazil EST5EDT Hongkong Libya Portugal UCT
Canada Etc HST MET posix Universal
CET Europe Iceland Mexico posixrules US
[root@localhost zoneinfo]# cd Asia/
[root@localhost Asia]# ls
Aden Calcutta Hong_Kong Kuala_Lumpur Pyongyang Tel_Aviv
Almaty Chita Hovd Kuching Qatar Thimbu
Amman Choibalsan Irkutsk Kuwait Qyzylorda Thimphu
Anadyr Chongqing Istanbul Macao Rangoon Tokyo
Aqtau Chungking Jakarta Macau Riyadh Tomsk
Aqtobe Colombo Jayapura Magadan Saigon Ujung_Pandang
Ashgabat Dacca Jerusalem Makassar Sakhalin Ulaanbaatar
Ashkhabad Damascus Kabul Manila Samarkand Ulan_Bator
Baghdad Dhaka Kamchatka Muscat Seoul Urumqi
[root@localhost Asia]# cp /etc/localtime /etc/localtime_bak
[root@localhost Asia]# cp Shanghai /etc/localtime
cp: overwrite ‘/etc/localtime’? yes
[root@localhost Asia]# date
Mon Jun 17 22:29:23 CST 201913.pset
sql
highgo=# \pset border 0 --无边框
Border style is 0.
highgo=# select * from test_a;
id name
-- ----
1 a
2 b
3 c
4 d
5 e
(5 rows)
highgo=# \pset border 1 --边框只在内部
Border style is 1.
highgo=# select * from test_a;
id | name
----+------
1 | a
2 | b
3 | c
4 | d
5 | e
(5 rows)
highgo=# \pset border 2 --内外都有边框
Border style is 2.
highgo=# select * from test_a;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
(5 rows)highgo=# \pset border 0 --无边框
Border style is 0.
highgo=# select * from test_a;
id name
-- ----
1 a
2 b
3 c
4 d
5 e
(5 rows)
highgo=# \pset border 1 --边框只在内部
Border style is 1.
highgo=# select * from test_a;
id | name
----+------
1 | a
2 | b
3 | c
4 | d
5 | e
(5 rows)
highgo=# \pset border 2 --内外都有边框
Border style is 2.
highgo=# select * from test_a;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
(5 rows)14.shell中调用sql
[postgres@pg02 ~]$ psql -U postgres -h 127.0.0.1 -p 5532 -c "select version();"
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
(1 row)[postgres@pg02 ~]$ psql -U postgres -h 127.0.0.1 -p 5532 -c "select version();"
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
(1 row)15.watch
sql
-- 反复查看语句执行结果
test=> select * from a.test; \watch 1 -----单位为秒
id
----
1
(1 row)-- 反复查看语句执行结果
test=> select * from a.test; \watch 1 -----单位为秒
id
----
1
(1 row)16.update
根据 PostgreSQLL 的 MVCC 机制，在执行 update 命令更新数据时， PG 会在原有基础上复制一份新的复本 tuples 出来，然后在新的 tuples 上进行更新
skytf=> create table test_59(id integer,name varchar(32));
CREATE TABLE
skytf=> insert into test_59 values (1,'a'),(2,'b'),(3,'c');
INSERT 0 3skytf=> create table test_59(id integer,name varchar(32));
CREATE TABLE
skytf=> insert into test_59 values (1,'a'),(2,'b'),(3,'c');
INSERT 0 3查询表的 ctid
skytf=> select ctid, * from test_59;
ctid | id | name
-------+----+------
(0,1) | 1 | a
(0,2) | 2 | b
(0,3) | 3 | c
(3 rows)skytf=> select ctid, * from test_59;
ctid | id | name
-------+----+------
(0,1) | 1 | a
(0,2) | 2 | b
(0,3) | 3 | c
(3 rows)备注： ctid 为记录上逻辑标识，ctid 有两个字段组成，第一个字段表示 table 的逻辑 PAGE 编号，第二个字段表示每个逻辑 PAGE 上的第 N 条记录; 例如 ctid =(0,3) 表示这条记录位于这个表的第 0 PAGE 页的第三条记录
skytf=> update test_59 set name='aaa' where id=1;
UPDATE 1
#查看
skytf=> select ctid, * from test_59;
ctid | id | name
-------+----+------
(0,2) | 2 | b
(0,3) | 3 | c
(0,4) | 1 | aaa
(3 rows)
skytf=> select ctid, * from test_59 where id=1;
ctid | id | name
-------+----+------
(0,4) | 1 | aaaskytf=> update test_59 set name='aaa' where id=1;
UPDATE 1
#查看
skytf=> select ctid, * from test_59;
ctid | id | name
-------+----+------
(0,2) | 2 | b
(0,3) | 3 | c
(0,4) | 1 | aaa
(3 rows)
skytf=> select ctid, * from test_59 where id=1;
ctid | id | name
-------+----+------
(0,4) | 1 | aaa备注：id=1 这条记录的 ctid 由原来的 (0,1) 变成现在的 (0,4) 了，说明在 update 时，是在这条记录的复本上进行 update, 同时老记录标识不可见，的记录依然在PAGE里，VACUUM之后才会将老记录清除
https://postgres.fun/20111021102003.html
17.导入外部sql文件
1.进入终端
登录到sql里面
psql -d db_name -U username
>\i /path/xxx.sql登录到sql里面
psql -d db_name -U username
>\i /path/xxx.sql2.终端外部
#psql -U username -h 127.0.0.1 -p 5432 -d db_name -U user_name -f /pathA/xxx.sql#psql -U username -h 127.0.0.1 -p 5432 -d db_name -U user_name -f /pathA/xxx.sql