【PostgreSQL】sql语句 psql 常用命令

• Select distinct rows by using DISTINCT operator.
• Filter rows by using WHERE clause.
• Sort rows by usingORDER BY clause.
• Select rows based on various operators such as BETWEEN, INand LIKE.
• Group rows into groups using GROUP BY clause
• Apply conditions for groups using HAVING clause.
• Join a table to other tables using INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN clauses.

SELECT
 film_id,
 title,
 rental_rate
FROM
 film
ORDER BY
 rental_rate DESC
LIMIT 10  offset 10;

psql教程：根据实际需求查功能介绍

 http://www.postgresqltutorial.com/ 

https://www.tutorialspoint.com/postgresql/

Connect to PostgreSQL database

The following command connects to a database under a specific user. After pressing Enter PostgreSQL will ask for the password of the user.

1	psql -d database -U  user -W

For example, to connect to dvdrental database under postgres user, you use the following command:

1 2 3

C:\Program Files\PostgreSQL\9.5\bin>psql -d dvdrental -U postgres -W Password for user postgres: dvdrental=#

If you want to connect to a database that resides on another host, you add the -h option as follows:

1	psql -h host -d database -U user -W

In case you want to use SSL mode for the connection, just specify it in the command as the following command:

1	psql -U user -h host "dbname=db sslmode=require"

 

Switch connection to a new database

Once you are connected to a database, you can switch the connection to a new database under a user specified by user. The previous connection will be closed. If you omit the user parameter, the current user is assumed.

1	\c dbname username

The following command connects to dvdrental database under postgres user:

1 2 3

postgres=# \c dvdrental You are now connected to database "dvdrental" as user "postgres". dvdrental=#

 

List available databases

To list all databases in the current PostgreSQL database server, you use \l command:

1

\l

 

List available tables

To list all tables in the current database, you use \dt command:

1

\dt

Note that this command shows only table in the current connected database.

Describe a table

To describe a table such as a column, type, modifiers of columns, etc., you use the following command:

1	\d table_name

 

List available schema

To list all schema of the currently connected database, you use the \dn command.

1

\dn

 

List available functions

To list available functions in the current database, you use the \df command.

1

\df

 

List available views

To list available views in the current database, you use the \dv command.

1

\dv

 

List users and their roles

To list all users and their assign roles, you use \du command:

1

\du

 

Execute the previous command

To retrieve the current version of PostgreSQL server, you use the version() function as follows:

1	SELECT version();

Now, you want to save time typing the previous command again, you can use \g command to execute the previous command:

1

\g

psql executes the previous command again, which is the SELECT statement,.

Command history

To display command history, you use the \s command.

1

\s

If you want to save the command history to a file, you need to specify the file name followed the \scommand as follows:

1	\s filename

 

Execute psql commands from a file

In case you want to execute psql commands from a file, you use \i command as follows:

1	\i filename

 

Get help on psql commands

To know all available psql commands, you use the \? command.

1

\?

To get help on specific PostgreSQL statement, you use the \h command.

For example, if you want to know detailed information on ALTER TABLE statement, you use the following command:

1	\h ALTER TABLE

 

Turn on query execution time

To turn on query execution time, you use the \timing command.

1 2 3 4 5 6 7 8 9 10

dvdrental=# \timing Timing is on. dvdrental=# select count(*) from film; count -------   1000 (1 row)   Time: 1.495 ms dvdrental=#

You use the same command \timing to turn it off.

1 2 3

dvdrental=# \timing Timing is off. dvdrental=#

 

Edit command in your own editor

It is very handy if you can type the command in your favorite editor. To do this in psql, you \ecommand. After issuing the command, psql will open the text editor defined by your EDITOR environment variable and place the most recent command that you entered in psql into the editor.

After you type the command in the editor, save it, and close the editor, psql will execute the command and return the result.

It is more useful when you edit a function in the editor.

1	\ef [function name]

Switch output options

psql supports some types of output format and allows you to customize how the output is formatted on fly.

•  \a command switches from aligned to non-aligned column output.
•  \H command formats the output to HTML format.

Quit psql

To quit psql, you use \q command and press enter to exit psql.

1

\q

In this tutorial, we have shown you how to use psql commands to perform various commonly used tasks.