PostgreSQL05-常用命令

命令行命令

psql --version                      查看数据库版本
psql -l                             列出所有数据库   (数据库用户可操作)
createdb dbname                     创建数据库   (数据库用户可操作)
dropdb dbname                       删除数据库   (数据库用户可操作)

元命令

\l	列出数据库	\c db_name	切换数据库
\db	查看表空间列表	\copyright	查看PG的发行条款
\d  table_name	查看表定义	\x	纵向显示查询结果
\dt+ table_name	查看表大小	\dt	查看当前库中的表
\di+ index_name	查看索引大小	\di	查看当前库中的索引
\du	查看数据库用户	\h SQL	查看SQL的使用方法
\sf func_name	查看函数定义	?	列出所有元命令及用途

\dn  \dnS                         查看模式
\echo :value                      显示变量value的值
\echo :PROMPT[1,2,3]              显示提示符格式
\set name value                   设置变量
\encoding [字元编码名称]           显示或设定用户端字元编码
\password [USERNAME]              更改用户名密码
\q                                退出 psql
\timing                           显示或关闭sql执行时间
\watch second                     以second间隔秒数，反复执行上一条sql

获取元命令对应的SQL代码

连接数据库时，添加-E选项，再输入元命令，即可查看

psql -U ap -E \l  # 也可直接元命令

---

对比

切换数据库	\c	use dbname
列举数据库	\l	show databases
列举表	\dt	show tables
查看表结构	\d	desc tbname

查看auto库的oid
select oid,datname from pg_database where datname = 'auto';

查看product表的oid
select oid,relfilenode from pg_class where relname = 'product';

psql命令

psql -U ap -d auto -c "select * from product where id = 6"

id | name | auto_type |       product_type        |      software_versions       | lic_name
---+------+-----------+---------------------------+------------------------------+----------
 6 | BVS  | 1         | {NX3-X,NX3-S,NX3-E,NX3-P} | {V6.0.1.0,V6.0.1.4,V6.0.2.0} | bvs

-c 必选项，后接sql语句
-A 命令紧凑，输出没有空格
-t 只显示数据，不显示选项名
-----------------------------

执行文件中的sql语句
psql -U ap -f test.sql -q

-f 指定文件名，*.sql中有sql语句就能执行
-q 取消命令的输出

连接数据库

psql dbname username

psql传递变量

通过\set设置
\set v_id 2     设置变量
select * from product where id =:v_id
\set v_id                取消变量

通过-v设置
编写脚本，引用变量
vim test.sql 
select * from product where id =:v_id;
psql -v v_id=2 mydb pguser -f test.sql

查询活动会话
set active_session 'select pid,usename,datname,query,client_addr from pg_stat_activity \
where pid <> pg_backend_pid () and state＝\'active\' order by query;'
使用
:active_session

查看会话等待事件
\set wait_event 'select pid,usename,datname,query,client_addr,wait_event_type,wait_event \
from pg_stat_activity \
where pid <> pg_backend_pid() and wait_event is not null order by wait_event_type ;
使用
:wait_event

---

数据导入、导出表数据

copy与\copy
copy需要数据库超级用户权限，\copy不需要
COPY读取或写入数据库服务端主机上的文件，而\copy是从psql客户
copy比\copy的性能好（大文件场景）

copy test_table from '/tmp/test.txt'; (tab为分隔符)
\copy test_table from '/tmp/test.txt';

copy (select * from product) to '/tmp/test.csv' with csv header;
\copy product to '/tmp/test.txt';

---

创建用户与删除用户

create role username with encrypted password 'user_passwd' login;      省略login则没有login权限
create user username with encrypted password 'user_passwd';            create user创建的用户没有login权限
create ( role | user ) username superuser;    创建超级用户(需要现有超级用户)
drop ( role | user ) username;

命令行下使用
createuser username;
dropuser username;

查看所有角色

select rolname from pg_roles;
\du

赋予用户登陆权限

alter role user_name login;

---

创建表空间

创建表空间目录

mkdir -p /database/pg10/pg_tbs/tbs_mydb

创建表空间

create tablespace tbs_mydb owner pguser location '/database/pg10/pg_tbs/tbs_mydb'

创建数据库

CREATE DATABASE mydb
WITH OWNER = pguser
TEMPLATE = template0
ENCODING = 'UTF-8'
TABLESPACE = tbs_mydb;

赋权

GRANT ALL ON DATABASE mydb TO pguser WITH GRANT OPTION;
GRANT ALL ON TABLESPACE tbs_mydb TO pguser;

---

其他

select now()            查看时间
select version()        查看版本
select txid_current()        查看当前事务

create database db_name owner user_name        创建数据库

alter database dbname owner to new_owner;        修改数据库所有者
alter table table_name rename to new_name;       重命名一个表
alter table table_name add column column_name type_name;        在已有的表里添加字段
alter table table_name drop column column_name;                 删除表中的字段
alter table table_name alter column_name type type_name(350)         修改数据库列属性
alter table table_name rename column column_name to new_name;        重命名一个字段  
alter table table_name alter column column_name set default new_default_value;        给一个字段设置缺省值
alter table table_name alter column column_name drop default;        去除缺省值