PostgreSQL学习
PostgreSQL的使用:运行环境,ubuntu,LTS,14.4 一个psql命令,会得到一个提示如下 psql (9.3.5) postgres=# 这里就是输入SQL语句的地方,我们输入help来看一下有哪些提示 You are using psql,the command-line interface to PostgreSQL. 基本的就介绍完了!下面开始打外围! Usage: psql [OPTION]... [DBNAME [USERNAME]] #命令格式,比如 psql -U postgres General options: #通用选项 -c,--command=COMMAND run only single command (SQL or internal) and exit #输入一天语句就结束 -d,--dbname=DBNAME database name to connect to (default: "rjg") #连接的数据库的名字 -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 #将psql变量名赋予一个值 -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 show this help,then exit Input and output options: 输入输出选项 -a,--echo-all echo all input from script #从脚本中输出所有的输入选项 -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 #没有别名的表的输出模式 -F,--field-separator=STRING set field separator (default: "|") #区域分割 -H,--html HTML table output mode #HTML的表输出模式 -P,--pset=VAR[=ARG] set printing option VAR to ARG (see pset command) -R,--record-separator=STRING set record separator (default: newline) #设置记录分隔符,默认为每一行 -t,--tuples-only print rows only #只输出每一行的信息,没有第一行的那个列名提示 -T,--table-attr=TEXT set HTML table tag attributes (e.g.,width,border) #设置HTML标签 -x,--expanded turn on expanded table output #打开拓展表的输出 -z,--field-separator-zero set field separator to zero byte #将区域分割设置为0 -0,--record-separator-zero set record separator to zero byte #将记录分割设置为0 Connection options: #连接选项 -h,--host=HOSTNAME database server host or socket directory (default: "/var/run/postgresql") #主机名 -p,--port=PORT database server port (default: "5432") #主机端口 -U,--username=USERNAME database user name (default: "rjg") #用户名 -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@postgresql.org. 好吧,外围打完了,我们需要补充装备(熟悉PostgreSQL操作数据库的特性) Query Buffer #查询缓冲区 e [FILE] [LINE] edit the query buffer (or file) with external editor #使用外部编辑器,编辑查询或则文件 ef [FUNCNAME [LINE]] edit function 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 #输入输出 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) #将string写到输出流 Informational #信息 (options: S = show system objects,+ = additional detail) #S表示显示系统对象,+表示附加的详尽信息 d[S+] list tables,views,and sequences #列出,tables,sequences d[S+] NAME describe table,view,sequence,or index #描述.table,index da[S] [PATTERN] list aggregates #列出聚集体 db[+] [PATTERN] list tablespaces #列出表空间 dc[S+] [PATTERN] list conversions #列出转化关系 dC[+] [PATTERN] list casts #列出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 #显示[only agg/normal/trigger/window]方法 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[+] [PATTERN] list roles #列出 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,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[+] [PATTERN] list roles #列出roles dv[S+] [PATTERN] list views #列出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 #显示方法的描述 z [PATTERN] same as dp #和dp一样 Formatting #格式化 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 := {format|border|expanded|fieldsep|fieldsep_zero|footer|null| numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager}) t [on|off] show only rows (currently off) #只展示行 T [STRING] set HTML <table> tag attributes,or unset if none #设置html标签 x [on|off|auto] toggle expanded output (currently off) #切换拓展输出 Connection #连接 c[onnect] [DBNAME|- USER|- HOST|- PORT|-] connect to new database (currently "room") #连接新的数据库 encoding [ENCODING] show or set client encoding #显示或则设置客户端编码 password [USERNAME] securely change the password for a user #改变密码 conninfo display information about current connection 显示连接信息 Operating System cd [DIR] change the current working directory #切换工作目录 setenv NAME [VALUE] set or unset environment variable #设置或则删除环境变量 timing [on|off] toggle timing of commands (currently off) #切换时间命令 !! [COMMAND] execute command in shell or start interactive shell #在shell中执行命令 Variables 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 lo_export LOBOID FILE lo_import FILE [COMMENT] lo_list lo_unlink LOBOID large object operations 好吧,装备补充完了,我们开始打怪!下面是PostgreSQL所有的关键字,这可是一场攻坚战啊! ABORT CLUSTER DEALLOCATE END ALTER AGGREGATE COMMENT DECLARE EXECUTE ALTER COLLATION COMMIT DELETE EXPLAIN ALTER CONVERSION COMMIT PREPARED DISCARD FETCH ALTER DATABASE COPY DO GRANT ALTER DEFAULT PRIVILEGES CREATE AGGREGATE DROP AGGREGATE INSERT ALTER DOMAIN CREATE CAST DROP CAST LISTEN ALTER EVENT TRIGGER CREATE COLLATION DROP COLLATION LOAD ALTER EXTENSION CREATE CONVERSION DROP CONVERSION LOCK ALTER FOREIGN DATA WRAPPER CREATE DATABASE DROP DATABASE MOVE ALTER FOREIGN TABLE CREATE DOMAIN DROP DOMAIN NOTIFY ALTER FUNCTION CREATE EVENT TRIGGER DROP EVENT TRIGGER PREPARE ALTER GROUP CREATE EXTENSION DROP EXTENSION PREPARE TRANSACTION ALTER INDEX CREATE FOREIGN DATA WRAPPER DROP FOREIGN DATA WRAPPER REASSIGN OWNED ALTER LANGUAGE CREATE FOREIGN TABLE DROP FOREIGN TABLE REFRESH MATERIALIZED VIEW ALTER LARGE OBJECT CREATE FUNCTION DROP FUNCTION REINDEX ALTER MATERIALIZED VIEW CREATE GROUP DROP GROUP RELEASE SAVEPOINT ALTER OPERATOR CREATE INDEX DROP INDEX RESET ALTER OPERATOR CLASS CREATE LANGUAGE DROP LANGUAGE REVOKE ALTER OPERATOR FAMILY CREATE MATERIALIZED VIEW DROP MATERIALIZED VIEW ROLLBACK ALTER ROLE CREATE OPERATOR DROP OPERATOR ROLLBACK PREPARED ALTER RULE CREATE OPERATOR CLASS DROP OPERATOR CLASS ROLLBACK TO SAVEPOINT ALTER SCHEMA CREATE OPERATOR FAMILY DROP OPERATOR FAMILY SAVEPOINT ALTER SEQUENCE CREATE ROLE DROP OWNED SECURITY LABEL ALTER SERVER CREATE RULE DROP ROLE SELECT ALTER TABLE CREATE SCHEMA DROP RULE SELECT INTO ALTER TABLESPACE CREATE SEQUENCE DROP SCHEMA SET ALTER TEXT SEARCH CONFIGURATION CREATE SERVER DROP SEQUENCE SET CONSTRAINTS ALTER TEXT SEARCH DICTIONARY CREATE TABLE DROP SERVER SET ROLE ALTER TEXT SEARCH PARSER CREATE TABLE AS DROP TABLE SET SESSION AUTHORIZATION ALTER TEXT SEARCH TEMPLATE CREATE TABLESPACE DROP TABLESPACE SET TRANSACTION ALTER TRIGGER CREATE TEXT SEARCH CONFIGURATION DROP TEXT SEARCH CONFIGURATION SHOW ALTER TYPE CREATE TEXT SEARCH DICTIONARY DROP TEXT SEARCH DICTIONARY START TRANSACTION ALTER USER CREATE TEXT SEARCH PARSER DROP TEXT SEARCH PARSER TABLE ALTER USER MAPPING CREATE TEXT SEARCH TEMPLATE DROP TEXT SEARCH TEMPLATE TRUNCATE ALTER VIEW CREATE TRIGGER DROP TRIGGER UNLISTEN ANALYZE CREATE TYPE DROP TYPE UPDATE BEGIN CREATE USER DROP USER VACUUM CHECKPOINT CREATE USER MAPPING DROP USER MAPPING VALUES CLOSE CREATE VIEW DROP VIEW WITH (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |