PostgreSQL学习

PostgreSQL的使用:运行环境,ubuntu,LTS,14.4
在数据库安装完以后呢,首先呢?是怎么登录问题:
su postgres
然后输入密码,比如123456;
这个时候进入了postgres用户权限下,提示如下:
postgres@rjg-K45VD:/home/rjg$

一个psql命令,会得到一个提示如下

psql (9.3.5)
Type “help” for help.

postgres=# 这里就是输入SQL语句的地方,我们输入help来看一下有哪些提示

You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms /这里是版权信息/
\h for help with SQL commands /\h是SQL的命令语句/
\? for help with psql commands /\?里面总结了在PostgreSQL中怎么操作数据库的方法/
\g or terminate with semicolon to execute query /\g和;的意思是相同的,都是终止当前语句的作用,比如你输入一条语句后,后面要加一个;号或者\g/
\q to quit /退出/

基本的就介绍完了!下面开始打外围!
首先呢?得连接数据库,PostgreSQL是以psql开头,看看psql有哪些参数
在终端下,输入,psql –help可以查看PostgreSQL的一些基本信息
rjg@rjg-K45VD:~$ psql –help
psql is the PostgreSQL interactive terminal. #psql是PostgreSQL的一个终端

Usage:  psql [OPTION]... [DBNAME [USERNAME]]  #命令格式,比如 psql -U postgresGeneral 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 exitInput 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    #将记录分割设置为0Connection 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 SQLcommands) from within psql, or consult the psql section in the PostgreSQL documentation.

Report bugs to [e-mail].

好吧,外围打完了,我们需要补充装备(熟悉PostgreSQL操作数据库的特性)
输入,\?
General #通用
\copyright show PostgreSQL usage and distribution terms #俗称版权信息
\g [FILE] or ; execute query (and send results to file or |pipe) #执行查询,将结果输入文件,或则,直接执行查询
\gset [PREFIX] execute query and store results in psql variables #执行查询,并将结果存在psql变量里面
\h [NAME] help on syntax of SQL commands, * for all commands #帮助文档
\q quit psql #退出
\watch [SEC] execute query every SEC seconds #每多少秒执行一次查询,(这个很有用啊,不断的显示更新有没有O(∩_∩)O)

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) #Ｓ表示显示系统对象，+表示附加的详尽信息  \d[S+]                 list tables, views, and sequences  ＃列出，tables,views,sequences  \d[S+]  NAME           describe table, view, sequence, or index   #描述．table,view,sequence,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, 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[+]  [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