• 030.PGSQL-psql元命令


    元命令

    http://www.postgres.cn/docs/13/app-psql.html

    postgres=# ?
    General
      copyright             show PostgreSQL usage and distribution terms
      g [FILE] or ;         execute query (and send results to file or |pipe)
      h [NAME]              help on syntax of SQL commands, * for all commands
      q                     quit psql
    
    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
      
                         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)
    
    Informational
      (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
      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[+]  [PATTERN]      list roles
      di[S+] [PATTERN]      list indexes
      dl                    list large objects, same as lo_list
      dL[S+] [PATTERN]      list procedural languages
      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
      dv[S+] [PATTERN]      list views
      dE[S+] [PATTERN]      list foreign tables
      dx[+]  [PATTERN]      list extensions
      l[+]                  list all databases
      sf[+] FUNCNAME        show a function's definition
      z      [PATTERN]      same as 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})
      	 [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
      c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                             connect to new database (currently "postgres")
      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
      	iming [on|off]       toggle timing of commands (currently off)
      ! [COMMAND]           execute command in shell or start interactive 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

    元命令的本质也是向数据库发出相应的sql查询

    psql连接数据库时,-E 可以获取元命令的sql代码

    [postgres@s101 /]$psql -E  -h localhost -p 5432 mydb pguser
    Password for user pguser:
    psql (9.2.24, server 13.3)
    WARNING: psql version 9.2, server version 13.0.
             Some psql features might not work.
    Type "help" for help.
    
    mydb-> db
    ********* QUERY **********
    SELECT spcname AS "Name",
      pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
      pg_catalog.pg_tablespace_location(oid) AS "Location"
    FROM pg_catalog.pg_tablespace
    ORDER BY 1;
    **************************
    
                        List of tablespaces
        Name    |  Owner   |             Location
    ------------+----------+-----------------------------------
     pg_default | postgres |
     pg_global  | postgres |
     tbs_mydb   | pguser   | /var/lib/pgsql/13/pg_tbs/tbs_mydb
    (3 rows)
    
    mydb-> 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'
    ') AS "Access privileges"
    FROM pg_catalog.pg_database d
    ORDER BY 1;
    **************************
    
                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    -----------+----------+----------+-------------+-------------+-----------------------
     mydb      | pguser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/pguser           +
               |          |          |             |             | pguser=C*T*c*/pguser
     postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
     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)

    h命令可以将sql命令的语法列出

    mydb-> h
    Available help:
      ABORT                            CREATE FUNCTION                  DROP TABLE
      ALTER AGGREGATE                  CREATE GROUP                     DROP TABLESPACE
      ALTER COLLATION                  CREATE INDEX                     DROP TEXT SEARCH CONFIGURATION
      ALTER CONVERSION                 CREATE LANGUAGE                  DROP TEXT SEARCH DICTIONARY
      ALTER DATABASE                   CREATE OPERATOR                  DROP TEXT SEARCH PARSER
      ALTER DEFAULT PRIVILEGES         CREATE OPERATOR CLASS            DROP TEXT SEARCH TEMPLATE
      ALTER DOMAIN                     CREATE OPERATOR FAMILY           DROP TRIGGER
      ALTER EXTENSION                  CREATE ROLE                      DROP TYPE
      ALTER FOREIGN DATA WRAPPER       CREATE RULE                      DROP USER
      ALTER FOREIGN TABLE              CREATE SCHEMA                    DROP USER MAPPING
      ALTER FUNCTION                   CREATE SEQUENCE                  DROP VIEW
      ALTER GROUP                      CREATE SERVER                    END
      ALTER INDEX                      CREATE TABLE                     EXECUTE
      ALTER LANGUAGE                   CREATE TABLE AS                  EXPLAIN
      ALTER LARGE OBJECT               CREATE TABLESPACE                FETCH
      ALTER OPERATOR                   CREATE TEXT SEARCH CONFIGURATION GRANT
      ALTER OPERATOR CLASS             CREATE TEXT SEARCH DICTIONARY    INSERT
      ALTER OPERATOR FAMILY            CREATE TEXT SEARCH PARSER        LISTEN
      ALTER ROLE                       CREATE TEXT SEARCH TEMPLATE      LOAD
      ALTER SCHEMA                     CREATE TRIGGER                   LOCK
      ALTER SEQUENCE                   CREATE TYPE                      MOVE
      ALTER SERVER                     CREATE USER                      NOTIFY
      ALTER TABLE                      CREATE USER MAPPING              PREPARE
      ALTER TABLESPACE                 CREATE VIEW                      PREPARE TRANSACTION
      ALTER TEXT SEARCH CONFIGURATION  DEALLOCATE                       REASSIGN OWNED
      ALTER TEXT SEARCH DICTIONARY     DECLARE                          REINDEX
      ALTER TEXT SEARCH PARSER         DELETE                           RELEASE SAVEPOINT
      ALTER TEXT SEARCH TEMPLATE       DISCARD                          RESET
      ALTER TRIGGER                    DO                               REVOKE
      ALTER TYPE                       DROP AGGREGATE                   ROLLBACK
      ALTER USER                       DROP CAST                        ROLLBACK PREPARED
      ALTER USER MAPPING               DROP COLLATION                   ROLLBACK TO SAVEPOINT
      ALTER VIEW                       DROP CONVERSION                  SAVEPOINT
      ANALYZE                          DROP DATABASE                    SECURITY LABEL
      BEGIN                            DROP DOMAIN                      SELECT
      CHECKPOINT                       DROP EXTENSION                   SELECT INTO
      CLOSE                            DROP FOREIGN DATA WRAPPER        SET
      CLUSTER                          DROP FOREIGN TABLE               SET CONSTRAINTS
      COMMENT                          DROP FUNCTION                    SET ROLE
      COMMIT                           DROP GROUP                       SET SESSION AUTHORIZATION
      COMMIT PREPARED                  DROP INDEX                       SET TRANSACTION
      COPY                             DROP LANGUAGE                    SHOW
      CREATE AGGREGATE                 DROP OPERATOR                    START TRANSACTION
      CREATE CAST                      DROP OPERATOR CLASS              TABLE
      CREATE COLLATION                 DROP OPERATOR FAMILY             TRUNCATE
      CREATE CONVERSION                DROP OWNED                       UNLISTEN
      CREATE DATABASE                  DROP ROLE                        UPDATE
      CREATE DOMAIN                    DROP RULE                        VACUUM
      CREATE EXTENSION                 DROP SCHEMA                      VALUES
      CREATE FOREIGN DATA WRAPPER      DROP SEQUENCE                    WITH
      CREATE FOREIGN TABLE             DROP SERVER
  • 相关阅读:
    java学习笔记——基于Robot类的屏幕分享
    Java实例——基于jsoup的简单爬虫实现(从智联获取工作信息)
    Java实例练习——基于UDP协议的多客户端通信
    java实例练习——基于TCP/IP协议的多客户端通信
    我个人的Java学习经验(一家之言)
    PHP mac localhost 环境下发送邮件
    php ob_start()、ob_end_flush和ob_end_clean()多级缓冲
    php ob_start()、ob_end_flush和ob_end_clean()多级缓冲
    程序员应该知道的13个设计技巧
    程序员应该知道的13个设计技巧
  • 原文地址:https://www.cnblogs.com/star521/p/15055506.html
Copyright © 2020-2023  润新知