1. Postgre SQL

PSQL序

切换到postgres用户进入数据库控制台

hightlight:true
1
sudo -u postgres psql

创建新数据库

hightlight:true
1
create database newdbname [owner username]

赋予用户数据库所有权限

hightlight:true
1
grant all privileges on database dbname to username

更改数据库owner

hightlight:true
1
alter database dbname owner to username

查看所有数据库列表

hightlight:true
1
\l

连接数据库

hightlight:true
1
\c dbname

显示所有的schema

hightlight:true
1
\dn

显示所有的用户

hightlight:true
1
\du

显示表的权限分配情况

hightlight:true
1
\dp

显示当前的模式

hightlight:true
1
show search_path

更改模式

hightlight:true
1
set search_path to myschema

已列的形式展示或取消

hightlight:true
1
\x

查看所有表

hightlight:true
1
\dt

显示执行时间

hightlight:true
1
\timing on

关闭显示执行时间

hightlight:true
1
\timing off

禁用全表扫描

hightlight:true
1
2
alter role rolename set enable_seqscan = off;
set enable_seqscan = off;

PSQL COMMANDS

General

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
\copyright             
# show PostgreSQL usage and distribution terms

\errverbose            
# show most recent error message at maximum verbosity

\g [FILE] or ;         
# execute query (and send results to file or |pipe)

\gexec                 
# execute query, then execute each value in its result

\gset [PREFIX]         
# execute query and store results in psql variables

\q                     
# quit psql

\crosstabview [COLUMNS] 
# execute query and display results in crosstab

\watch [SEC]           
# execute query every SEC seconds

Help

1
2
3
4
5
6
7
8
9
10
11
\? [commands]          
# show help on backslash commands

\? options             
# show help on psql command-line options

\? variables           
# show help on special variables

\h [NAME]              
# help on syntax of SQL commands, * for all commands

Query Buffer

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
\e [FILE] [LINE]       
# edit the query buffer (or file) with external editor

\ef [FUNCNAME [LINE]]  
# edit function definition with external editor

\ev [VIEWNAME [LINE]]  
# edit view definition with external editor

\p                     
# show the contents of the query buffer

\r                     
# reset (clear) the query buffer

\s [FILE]              
# display history or save it to file

\w FILE                
# write query buffer to file

Input/Output

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
\copy ...              
# perform SQL COPY with data stream to the client host

\echo [STRING]         
# write string to standard output

\i FILE                
# execute commands from file

\ir FILE               
# as \i, but relative to location of current script

\o [FILE]              
# send all query results to file or |pipe

\qecho [STRING]        
# write string to query output stream (see \o)

Informational

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
(options: S = show system objects, + = additional detail)

\d[S+]                 
# list tables, views, and sequences

\d[S+]  NAME           
# describe table, view, sequence, or index

\da[S]  [PATTERN]      
# list aggregates

\dA[+]  [PATTERN]      
# list access methods

\db[+]  [PATTERN]      
# list tablespaces

\dc[S+] [PATTERN]      
# list conversions

\dC[+]  [PATTERN]      
# list casts

\dd[S]  [PATTERN]      
# show object descriptions not displayed elsewhere

\ddp    [PATTERN]      
# list default privileges

\dD[S+] [PATTERN]      
# list domains

\det[+] [PATTERN]      
# list foreign tables

\des[+] [PATTERN]      
# list foreign servers

\deu[+] [PATTERN]      
# list user mappings

\dew[+] [PATTERN]      
# list foreign-data wrappers

\df[antw][S+] [PATRN]  
# list [only agg/normal/trigger/window] functions

\dF[+]  [PATTERN]      
# list text search configurations

\dFd[+] [PATTERN]      
# list text search dictionaries

\dFp[+] [PATTERN]      
# list text search parsers

\dFt[+] [PATTERN]      
# list text search templates

\dg[S+] [PATTERN]      
# list roles

\di[S+] [PATTERN]      
# list indexes

\dl                    
# list large objects, same as \lo_list

\dL[S+] [PATTERN]      
# list procedural languages

\dm[S+] [PATTERN]      
# list materialized views

\dn[S+] [PATTERN]      
# list schemas

\do[S]  [PATTERN]      
# list operators

\dO[S+] [PATTERN]      
# list collations

\dp     [PATTERN]      
# list table, view, and sequence access privileges

\drds [PATRN1 [PATRN2]] 
# list per-database role settings

\ds[S+] [PATTERN]      
# list sequences

\dt[S+] [PATTERN]      
# list tables

\dT[S+] [PATTERN]      
# list data types

\du[S+] [PATTERN]      
# list roles

\dv[S+] [PATTERN]      
# list views

\dE[S+] [PATTERN]      
# list foreign tables

\dx[+]  [PATTERN]      
# list extensions

\dy     [PATTERN]      
# list event triggers

\l[+]   [PATTERN]      
# list databases

\sf[+]  FUNCNAME       
# show a function's definition

\sv[+]  VIEWNAME       
# show a view's definition

\z      [PATTERN]      
# same as \dp

Formatting

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
\a                     
# toggle between unaligned and aligned output mode

\C [STRING]            
# set table title, or unset if none

\f [STRING]            
# show or set field separator for unaligned query output

\H                     
# toggle HTML output mode (currently off)

\pset [NAME [VALUE]]   
# set table output option
# (NAME := {border|columns|expanded|fieldsep|fieldsep_zero|
# footer|format|linestyle|null|numericlocale|pager|
# pager_min_lines|recordsep|recordsep_zero|tableattr|title|
# tuples_only|unicode_border_linestyle|
# unicode_column_linestyle|unicode_header_linestyle})

\t [on|off]            
# show only rows (currently off)

\T [STRING]            
# set HTML <table> tag attributes, or unset if none

\x [on|off|auto]       
# toggle expanded output (currently off)

Connection

1
2
3
4
5
6
7
8
9
10
11
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
# connect to new database (currently "crm_online_test")

\encoding [ENCODING]   
# show or set client encoding

\password [USERNAME]   
# securely change the password for a user

\conninfo              
# display information about current connection

Operating System

1
2
3
4
5
6
7
8
9
10
11
\cd [DIR]              
# change the current working directory

\setenv NAME [VALUE]   
# set or unset environment variable

\timing [on|off]       
# toggle timing of commands (currently on)

\! [COMMAND]           
# execute command in shell or start interactive shell

Variables

1
2
3
4
5
6
7
8
\prompt [TEXT] NAME    
# prompt user to set internal variable

\set [NAME [VALUE]]    
# set internal variable, or list all if no parameters

\unset NAME            
# unset (delete) internal variable

Large Objects

1
2
3
4
5
6
7
8
\lo_export LOBOID FILE

\lo_import FILE [COMMENT]

\lo_list

\lo_unlink LOBOID      
# large object operations
Copyright © 2020 Fzg5_Shelling | Powered by Hexo | Theme by Snippet | Deploys by Netlify