1.启动数据库
[postgres@cjc-db-01 ~]$ pg_ctl -D /pg/data -l /pg/log/pg.log start
2.登录
[postgres@cjc-db-01 ~]$ psql [postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb
3.退出登录
cjcdb=# \q
4.获取帮助信息
cjcdb=# \h cjcdb=# \h create table
5.查看\相关命令
cjcdb=# \?
6.数据库版本
cjcdb=# select version(); cjcdb=# show server_version;
7.查看有哪些数据库
cjcdb=# \l cjcdb=# \l+ cjcdb=# select oid,datname from pg_database;
8.查看当前登录的数据库
cjcdb=# select current_database();
9.切换数据库
postgres=# \c cjcdb
10.切换用户
cjcdb=# \c - chen
11.查看当前用户
cjcdb=# select user; cjcdb=# select current_user;
12.查看当前数据库下所有表
cjcdb=> \d cjcdb=> select tableowner,schemaname,tablename,tablespace from pg_tables where tableowner='cjc';
13.查看当前数据库下所有索引
cjcdb=# \di
14.查看索引定义语句
cjcdb=# select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 't1'; cjcdb=# select pg_get_indexdef(上面语句查到的b.indexrelid);
15.查看登录信息
cjcdb=# \conninfo
16.查看当前连接信息
cjcdb=# select * from pg_stat_activity;
17.查询表结构
cjcdb=> \d t1; cjcdb=> \d+ t1; cjcdb=> select table_schema,table_name,column_name,data_type,character_maximum_length from information_schema.columns where table_name='t1';
18.查看视图
cjcdb=# \dv cjcdb=# select * from pg_views where schemaname = 'public'; cjcdb=# select * from information_schema.views where table_schema = 'public';
19.查看触发器
cjcdb=# select * from information_schema.triggers;
20.查看序列
cjcdb=# select * from information_schema.sequences where sequence_schema = 'public';
21.查看约束
cjcdb=# select * from pg_constraint where contype = 'p' cjcdb=# select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 't1';
22.查看表所对应的数据文件路径与大小
cjcdb=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';
23.查看表大小
cjcdb=# select pg_relation_size('t1');
24.查看索引大小
cjcdb=# select pg_size_pretty(pg_relation_size('i_t1_id'));
25.查看表和索引总大小
cjcdb=# select pg_size_pretty(pg_total_relation_size('t1'));
26.查看表空间大小
cjcdb=# select pg_size_pretty(pg_tablespace_size('cjctbs'));
27.查看角色信息
cjcdb=# select rolname,rolsuper,rolcreatedb from pg_roles;
28.查询用户角色
cjcdb=# \dg cjcdb=# \du
29.查看用户表权限
cjcdb=# select * from information_schema.table_privileges where grantee='cjc'; cjcdb=# \dp
30.查看表空间
cjcdb=# \db
31.查询数据文件位置
cjcdb=# show data_directory;
32.查询配置文件位置
cjcdb=# show config_file;
33.查看数据库大小
cjcdb=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;
cjcdb=# select pg_size_pretty(pg_database_size('cjcdb'));
格式化输出 34.按列显示 类似MySQL的\G
cjcdb=# \x Expanded display is on. cjcdb=# select * from t1; ... cjcdb=# \x Expanded display is off.
35.显示执行时间
cjcdb=# \timing on cjcdb=# \timing of
36.执行shell命令 类似oracle的ho，MySQL的system命令
cjcdb=# \! pwd
37.格式化输出\pset 加输出边框，类似MySQL
cjcdb=# select * from t1; id | name ----+------------ 1 | a 2 | aaa (2 rows) cjcdb=# \pset border 2 Border style is 2. cjcdb=# select * from t1; +----+------------+ | id | name | +----+------------+ | 1 | a | | 2 | aaa | +----+------------+ (2 rows)
取消边框
cjcdb=# \pset border 0 Border style is 0. cjcdb=# select * from t1; id name -- ---------- 1 a 2 aaa (2 rows)
38.调整分隔符为"|"
cjcdb=# \pset format unaligned Output format is unaligned. cjcdb=# select * from t1; id|name 1|a 2|aaa (2 rows)
39.调整分隔符为"Tab"
cjcdb=# \pset fieldsep '\t' Field separator is "". cjcdb=# select * from t1; idname 1a 2a
40.输出结果到文本 类似oracle spool命令、MySQL tee命令。
cjcdb=# \o t1.txt cjcdb=# select * from t1; [postgres@cjc-db-01 ~]$ cat t1.txt idname 1a 2aaa (2 rows)
41.显示信息
cjcdb=# \echo hahaha hahaha
42.执行SQL脚本
[postgres@cjc-db-01 ~]$ cat t1.sql select * from t1; [postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb -f t1.sql Password for user cjc: id | name ----+------------ 1 | a 2 | aaa (2 rows)
43.获取快捷键实际执行的命令(参数-E) 例如，查询有哪些数据库 快捷键 查看有哪些数据库
cjcdb=# \l
实际执行的SQL
cjcdb=# \l ********* QUERY ********** SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1; ************************** List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- cjcdb | cjc | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/cjc + | | | | | cjc=CTc/cjc + | | | | | chen=CTc/cjc postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | cjc=c/postgres template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)
###chenjuchao 20230119 16:12###
