Directory
SELECT
WITH
LIMIT
like
WHERE
GROUP BY
HAVING
ORDER BY
SELECT
Results are stored in a result table, called a result set.
Syntax
The syntax of the SELECT statement is as follows:
SELECT column1, column2,...columnN FROM table_name;
-
column1, column2,…columnN are the field names in the table.
-
table_name is the table name.
If we want to read all the data in the table, we can use the following SQL statement:
SELECT * FROM table_name;
WITH
In PostgreSQL, the WITH clause provides a way to write auxiliary statements for use in larger queries.
The WITH clause helps break down large, complex queries into simpler forms that are easier to read. These statements are usually called common table expressions (Common Table Express, CTE), and can also be used as a temporary table for query.
The WITH clause is especially useful when executing a subquery multiple times, allowing us to refer to it by its name (possibly multiple times) in the query.
The WITH clause must be defined before it can be used.
Syntax
The basic syntax of a WITH query is as follows:
WITH
name_for_summary_data AS (
SELECT Statement)
SELECT columns
FROM name_for_summary_data
WHERE conditions <=> (
SELECT column
FROM name_for_summary_data)
[ORDER BY columns]
name_for_summary_data is the name of the WITH clause, name_for_summary_data can be the same as an existing table name and has priority.
Data INSERT , UPDATE , or DELETE statements can be used within WITH , allowing you to perform multiple different operations in the same query.
WITH recursion
The data output by itself can be used in the WITH clause.
An important advantage of a common table expression (CTE) is the ability to reference itself, thus creating a recursive CTE. A recursive CTE is a common table expression that repeatedly executes an initial CTE to return a subset of data until a complete result set is obtained.
LIMIT
The limit clause in PostgreSQL is used to limit the amount of data queried in the SELECT statement.
Syntax
The basic syntax of a SELECT statement with a LIMIT clause is as follows:
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]
Following is the syntax of the LIMIT clause when used with the OFFSET clause:
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]
LIKE
In the PostgreSQL database, if we want to get data containing certain characters, we can use the LIKE clause.
In the LIKE clause, it is usually used in combination with wildcards, which represent any characters. In PostgreSQL, there are mainly two kinds of wildcards:
- percent sign %
- underscore _
If neither of the above wildcards is used, the LIKE clause and the equal sign = give the same result.
Syntax
Following is the general syntax for fetching data from the database using the LIKE clause with percent sign % and underscore _ :
SELECT FROM table_name WHERE column LIKE 'XXXX%'; --or SELECT FROM table_name WHERE column LIKE '%XXXX%'; --or SELECT FROM table_name WHERE column LIKE 'XXXX_'; --or SELECT FROM table_name WHERE column LIKE '_XXXX'; --or SELECT FROM table_name WHERE column LIKE '_XXXX_';
You can specify any condition in the WHERE clause.
You can specify one or more conditions using AND or OR.
XXXX can be any number or character.
WHERE
In PostgreSQL, when we need to query data from a single table or multiple tables according to specified conditions, we can add a WHERE clause to the SELECT statement to filter out the data we don’t need.
The WHERE clause can be used not only in SELECT statements, but also in UPDATE, DELETE, etc. statements.
Syntax
Following is the general syntax for reading data from database using WHERE clause in SELECT statement:
SELECT column1, column2, columnN FROM table_name WHERE [condition1]
We can use comparison operators or logical operators like >, <, =, LIKE, NOT etc. in WHERE clause.
GROUP BY
In PostgreSQL, the GROUP BY statement is used together with the SELECT statement to group the same data.
GROUP BY is placed after the WHRER clause and before the ORDER BY clause in a SELECT statement.
Syntax
The basic syntax of the GROUP BY clause is given below:
SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN
The GROUP BY clause must be placed after the conditions in the WHERE clause and before the ORDER BY clause.
In the GROUP BY clause, you can group by one or more columns, but the grouped columns must exist in the column list.
HAVING
The HAVING clause allows us to filter the groups of data after grouping.
The WHERE clause sets conditions on the selected columns, while the HAVING clause sets conditions on the groupings created by the GROUP BY clause.
Syntax
Here is where the HAVING clause goes in a SELECT query:
SELECT FROM WHERE GROUP BY HAVING ORDER BY
The HAVING clause must be placed after the GROUP BY clause and before the ORDER BY clause. The following is the basic syntax of the HAVING clause in the SELECT statement:
SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [conditions] ORDER BY column1, column2
Example:
The records of the COMPANY table are as follows:
id | name | age | address | salary ---- + ------- + ----- + -------------- + -------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows)
The following example will find the data grouped according to the value of the name field, and the count of the name is greater than 1:
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
The result is as follows:
name ------- Paul James (2 rows)
ORDER BY
In PostgreSQL, ORDER BY is used to sort one or more columns of data in ascending order (ASC) or descending order (DESC).
Syntax
The basic syntax of the ORDER BY clause is as follows:
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
You can use one or more columns in ORDER BY, but the columns to be sorted must exist.
ASC means ascending order and DESC means descending order.
Example:
Create the COMPANY table?, the data content is as follows:
id | name | age | address | salary ---- + ------- + ----- + ----------- + -------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 (7 rows)
The following example will sort the results in ascending order based on the NAME field value and the SALARY field value:
SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
and get the following result:
id | name | age | address | salary ---- + ------- + ----- + ------------------------------- --------------------- + -------- 2 | Allen | 25 | Texas | 15000 5 | David | 27 | Texas | 85000 7 | James | 24 | Houston | 10000 6 | Kim | 22 | South-Hall | 45000 4 | Mark | 25 | Rich-Mond | 65000 1 | Paul | 32 | California | 20000 3 | Teddy | 23 | Norway | 20000 (7 rows)
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. PostgreSQL skill tree HomepageOverview 5736 people are studying systematically