PostgreSQL Quick start: Use of psql tool _ MySQL

I. INTRODUCTION to psql is a PostgreSQL command line interactive client tool, similar to the command line tool sqlplus: 1 in Oracle allows you to interactively type SQL or commands, then they are sent to the PostgreSQL server, and then the SQL or command results are displayed; 2 input

I. INTRODUCTION to psql

Psql is a PostgreSQL command line interactive client tool, similar to the command line tool sqlplus in Oracle:

1. allow you to interactively type SQL or commands, then send them to the PostgreSQL server, and then display the SQL or command results;

2. the input content can also come from a file;

3. it also provides some meta commands and a variety of shell-like features to write scripts, as well as to automate the work of bulk tasks;

II. simple and practical psql

Follow the previous steps to switch the su-postgres user and use the psql tool to connect to the database.

1. view the databases

postgres=# \l                                   List of databases    Name    |  Owner   | Encoding |  Collation  |   Ctype   |  Access privileges  -----------+----------+----------+-------------+-------------+-----------------------  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 (3 rows)

A. After installation, a database named postgres will be created by default. There are also two template databases template0 and template1;

B. When you recreate a database, the template database template1 is cloned by default;

C. template0 is the most simplified template library. when creating a database, if you specify to integrate the database, a simplified database will be created;

2. create an osdba database

postgres=# CREATE DATABASE osdba; CREATE DATABASE postgres-# \l                                   List of databases    Name    |  Owner   | Encoding |  Collation  |    Ctype    |  Access privileges-----------+----------+----------+-------------+-------------+-----------------------  osdba     | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 |   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)

3. access the osdba database

-bash-4.1$ psql osdba psql (8.4.20) Type "help" for help.

4. create table t in database osdba

osdba=# create table t(id int primary key,name varchar(40)); NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE

5. View tables in the osdb database

osdba=# \d         List of relations  Schema | Name | Type  |  Owner    --------+------+-------+----------  public | t    | table | postgres (1 row)

6. create a database named testdb and connect to the database named testdb.

osdba=# CREATE DATABASE testdb; CREATE DATABASE osdba=# \c testdb psql (8.4.20) You are now connected to database "testdb". testdb=#

III. common psql commands

1. \ d command-View all tables in the current database

osdba-# \d         List of relations  Schema | Name | Type  |  Owner    --------+------+-------+----------  public | t    | table | postgres (1 row)

2. \ d command-view the structure definition of the table with a table life

osdba-# \d t               Table "public.t"  Column |         Type          | Modifiers  --------+-----------------------+-----------  id     | integer               | not null  name   | character varying(40) |  Indexes:     "t_pkey" PRIMARY KEY, btree (id)

3. \ d command-you can view the index information of table t.

osdba-# \d t_pkey Index "public.t_pkey"  Column |  Type    --------+---------  id     | integer primary key, btree, for table "public.t"

4. \ d command-and wildcards such as * OR?

osdba-# \d t*               Table "public.t"  Column |         Type          | Modifiers  --------+-----------------------+-----------  id     | integer               | not null  name   | character varying(40) |  Indexes:     "t_pkey" PRIMARY KEY, btree (id)  Index "public.t_pkey"  Column |  Type    --------+---------  id     | integer primary key, btree, for table "public.t"

5. the \ d + command displays more detailed information than the \ d command, and displays comments associated with the table column

osdba-# \d+                     List of relations  Schema | Name | Type  |  Owner   |  Size   | Description  --------+------+-------+----------+---------+-------------  public | t    | table | postgres | 0 bytes |  (1 row)

6. \ d commands that match different object types, such as \ dt-only displaying the matching table; \ di-only displaying the index; \ ds-only displaying the sequence; \ dv-only displaying the view; \ df-only display functions...

osdba-# \dt t*         List of relations  Schema | Name | Type  |  Owner    --------+------+-------+----------  public | t    | table | postgres

7. \ dn command-list all schemas

osdba-# \dn         List of schemas         Name        |  Owner    --------------------+----------  information_schema | postgres  pg_catalog         | postgres  pg_toast_temp_1    | postgres  public             | postgres (5 rows)

8. \ db command-display all tablespaces

osdba-# \db        List of tablespaces     Name    |  Owner   | Location  ------------+----------+----------  pg_default | postgres |   pg_global  | postgres |  (2 rows)

9. \ dg-list all roles and users in the database

osdba-# \dg             List of roles  Role name | Attributes  | Member of  -----------+-------------+-----------  postgres  | Superuser   | {}            : Create role               : Create DB

10. \ dp-displays the table permission allocation

osdba-# \dp                           Access privileges  Schema | Name | Type  | Access privileges | Column access privileges  --------+------+-------+-------------------+--------------------------  public | t    | table |                   |  (1 row)

4. commands for compiling the specified character set

1. \ encoding gbk-set the client's character encoding to gbk

V. \ pset command

1. \ pset command-used to set the output format, \ pset border 0/1/2: The output content has no border, only the internal border, and both the internal and external border have border

osdba-# \pset border 0 Border style is 0. osdba-# \dp                      Access privileges Schema Name Type  Access privileges Column access privileges ------ ---- ----- ----------------- ------------------------ public t    table                    (1 row)  osdba-# \pset border 1 Border style is 1. osdba-# \dp                           Access privileges  Schema | Name | Type  | Access privileges | Column access privileges  --------+------+-------+-------------------+--------------------------  public | t    | table |                   |  (1 row)  osdba-# \pset border 2 Border style is 2. osdba-# \dp                            Access privileges +--------+------+-------+-------------------+--------------------------+ | Schema | Name | Type  | Access privileges | Column access privileges | +--------+------+-------+-------------------+--------------------------+ | public | t    | table |                   |             | +--------+------+-------+-------------------+--------------------------+ (1 row)

2. \ x command-you can split each column of data in each row of the table into a single row for display. if there is too many lines of data to be split, you can use this command

osdba-# \x Expanded display is on. osdba-# \dp Access privileges +-[ RECORD 1 ]-------------+--------+ | Schema                   | public | | Name                     | t      | | Type                     | table  | | Access privileges        |        | | Column access privileges |        | +--------------------------+--------+  osdba-# \x Expanded display is off. osdba-# \dp                            Access privileges +--------+------+-------+-------------------+--------------------------+ | Schema | Name | Type  | Access privileges | Column access privileges | +--------+------+-------+-------------------+--------------------------+ | public | t    | table |                   |         | +--------+------+-------+-------------------+--------------------------+ (1 row)

5. psql usage skills

1. use the up/down key to call out previously used commands or SQL statements. two consecutive tab keys indicate completion or prompt input.

osdba-# \d \d    \dc   \dD   \dew  \dFd  \dg   \dn   \ds   \dT    \da   \dC   \des  \df   \dFp  \di   \do   \dS   \du    \db   \dd   \deu  \dF   \dFt  \dl   \dp   \dt   \dv

2. with the-E parameter added to psql, you can print out the actual SQL statements executed by various commands starting with "\" in psql.

-bash-4.1$ psql -E postgres psql (8.4.20) Type "help" for help.  postgres=# \d ********* QUERY ********** SELECT n.nspname as "Schema",   c.relname as "Name",   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S'   THEN 'sequence'WHEN 's' THEN 'special' END as "Type",   pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','v','S','')       AND n.nspname <> 'pg_catalog'       AND n.nspname <> 'information_schema'       AND n.nspname !~ '^pg_toast'   AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; **************************  No relations found

If you want to disable it immediately after use

postgres=# \set ECHO_HIDDEN off postgres=# \d No relations found.

The above is the PostgreSQL Quick start: the use of psql tool _ MySQL content, for more information, please follow the PHP Chinese network (www.php1.cn )!