Learning focus
-
Use the SELECT statement to SELECT data from a table.
-
Set an alias for the column to display.
-
Constants or expressions can be used in SELECT statements.
-
Duplicate rows can be deleted by specifying DISTINCT.
-
Annotations can be used in SQL statements.
-
You can select the data that meets the query criteria from the table through the WHERE statement.
1, Column query
When selecting data from a table, you need to use a SELECT statement, that is, only SELECT the necessary data from the table. The process of querying and selecting the necessary data through the SELECT statement is called matching query or query.
KEYWORD
-
SELECT statement
-
Matching query
-
query
SELECT statement is the most basic SQL statement used in SQL statements. Mastering the SELECT statement is not far from mastering the SQL statement.
The basic syntax of the SELECT statement is as follows.
Syntax 1 Basic SELECT statement
SELECT <Listing>,...... FROM <Table name>;
The SELECT statement contains two clauses (SELECT and FROM). clause is an element of an SQL statement and a phrase starting with SELECT or FROM.
KEYWORD
- clause
The SELECT clause lists the names of the columns you want to query FROM the table, while the FROM clause specifies the name of the table FROM which the data is selected.
Next, we try from Table creation In the Product table created in, query the Product shown in Figure 1_ ID column, product_name column and purchase_ The price column.

Figure 1 query the columns in the Product table
See code listing 1 for the corresponding SELECT statement. The normal execution result of the statement is shown in the execution result [1].
Listing 1 outputs three columns from the Product table
SELECT product_id, product_name, purchase_price FROM Product;
results of enforcement
product_id | product_name | purchase_price -----------+--------------+--------------- 0001 | T shirt | 500 0002 | Punch | 320 0003 | motion T Shirt | 2800 0004 | kitchen knife | 2800 0005 | pressure cooker | 5000 0006 | Fork | 0007 | Dish cleaning board | 790 0008 | ball pen |
SELECT product in the first line of the SELECT statement_ id, product_ name, purchase_ Price is the SELECT clause. The order of the queried columns can be specified arbitrarily. When querying multiple columns, you need to separate them with commas. The order of columns in the query result is the same as that in the SELECT clause [2].
2, Query all columns in the table
When you want to query all columns, you can use an asterisk (*) representing all columns.
KEYWORD
- Asterisk (*)
Syntax 2 query all columns
SELECT * FROM <Table name>;
For example, the statement to query all columns in the Product table is shown in Listing 2.
Listing 2 outputs all the columns in the Product table
SELECT * FROM Product;
The result is the same as that of the SELECT statement in Listing 3.
Code listing 3 is a SELECT statement with the same meaning as code listing 2
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date FROM Product;
The execution results are as follows.
results of enforcement
product_id | product_name | product_type | sale_price | purchase_price | regist_date ------------+--------------+--------------+------------+----------------+------------ 0001 | T shirt | clothes | 1000 | 500 | 2009-09-20 0002 | Punch | Office Supplies | 500 | 320 | 2009-09-11 0003 | motion T Shirt | clothes | 4000 | 2800 | 0004 | kitchen knife | kitchenware | 3000 | 2800 | 2009-09-20 0005 | pressure cooker | kitchenware | 6800 | 5000 | 2009-01-15 0006 | Fork | kitchenware | 500 | | 2009-09-20 0007 | Dish cleaning board | kitchenware | 880 | 790 | 2008-04-28 0008 | ball pen | Office Supplies | 100 | | 2009-11-11
Rule 1
An asterisk (*) means all columns.
However, if you use asterisks, you cannot set the display order of columns. The columns are sorted as defined in the CREATE TABLE statement.
special column
Feel free to use line breaks
SQL statements use newline characters or half width spaces to separate words. They can be separated at any position. Even if the following is a newline character, it will not affect the execution of the SELECT statement. However, this may make an error because you can't see clearly. In principle, I hope you can wrap lines in terms of clauses (if the clauses are too long, you can wrap lines for convenience).
SELECT * FROM Product ;
In addition, inserting blank lines (lines without any characters) like the following will cause execution errors. Please pay special attention.
SELECT * FROM Product;
3, Set alias for column
SQL statements can alias columns using the AS keyword. See code listing 4.
KEYWORD
-
AS keyword
-
alias
Listing 4 sets the alias for the column
SELECT product_id AS id, product_name AS name, purchase_price AS price FROM Product;
results of enforcement
id | name | price ------+---------+------- 0001 | T shirt | 500 0002 | Punch | 320 0003 | motion T Shirt | 2800 0004 | kitchen knife | 2800 0005 | pressure cooker | 5000 0006 | Fork | 0007 | Dish cleaning board | 790 0008 | ball pen |
The alias can be in Chinese. When using Chinese, it needs to be enclosed in double quotation marks (") [3] . please note that it is not a single quotation mark ('). See code listing 5 for the SELECT statement to set the Chinese alias.
KEYWORD
- Double quotation mark (")
Listing 5 sets the Chinese alias
SELECT product_id AS "Item number", product_name AS "Trade name", purchase_price AS "Purchase unit price" FROM Product;
results of enforcement
Item number | Trade name | Purchase unit price ----------+----------+--------- 0001 | T shirt | 500 0002 | Punch | 320 0003 | motion T Shirt | 2800 0004 | kitchen knife | 2800 0005 | pressure cooker | 5000 0006 | Fork | 0007 | Dish cleaning board | 790 0008 | ball pen |
It is easier to understand through the implementation results. Using aliases like this makes the execution results of the SELECT statement easier to understand and manipulate.
Rule 2
When setting Chinese aliases, you need to use double quotation marks (").
4, Constant query
In the SELECT clause, you can write not only column names, but also constants. In the SELECT clause in code listing 6, the first column 'commodity' is a string constant, the second column 38 is a numeric constant, and the third column '2009-02-24' is a date constant. They will be queried together with the product_id column and the product_name column. [4]
KEYWORD
-
String constant
-
Digital constant
-
Date constant
Code listing 6 query constants
SELECT 'commodity' AS string, 38 AS number, '2009-02-24' AS date, product_id, product_name FROM Product;
results of enforcement
string | number | date | product_id | product_name ---------+----------+-------------+------------+-------------- commodity | 38 | 2009-02-24 | 0001 | T shirt commodity | 38 | 2009-02-24 | 0002 | Punch commodity | 38 | 2009-02-24 | 0003 | motion T Shirt commodity | 38 | 2009-02-24 | 0004 | kitchen knife commodity | 38 | 2009-02-24 | 0005 | pressure cooker commodity | 38 | 2009-02-24 | 0006 | Fork commodity | 38 | 2009-02-24 | 0007 | Dish cleaning board commodity | 38 | 2009-02-24 | 0008 | ball pen
As shown in the above execution results, the constants in the SELECT clause are displayed in all rows.
In addition, expressions can be written in addition to constants in the SELECT clause. We will learn how to write expressions in [arithmetic and comparison operators] ({{< ref "602-02-02-arithmetic and comparison operators. MD" = ">}}).
5, Remove duplicate rows from results
If you want to know which Product_types are saved in the Product table, it would be nice if you could delete the duplicate data as shown in Figure 2.

Figure 2 commodity types after removing duplicate data
As shown above, when you want to delete duplicate rows, you can do so by using DISTINCT in the SELECT clause (code listing 7).
KEYWORD
- DISTINCT keyword
Listing 7 uses DISTINCT to delete duplicate data in the product_type column
SELECT DISTINCT product_type FROM Product;
results of enforcement
product_type --------------- kitchenware clothes Office Supplies
Rule 3
Use DISTINCT in a SELECT statement to remove duplicate rows.
When using DISTINCT, NULL is also regarded as a kind of data. When NULL exists in multiple rows, it will also be merged into one NULL data. Use the SELECT statement of DISTINCT for the purchase_price column containing NULL data, see code listing 8. In addition to two 2800 data, two NULL data are also merged into one.
Listing 8 uses the DISTINCT keyword for columns containing NULL data
SELECT DISTINCT purchase_price FROM Product;
results of enforcement
DISTINCT can also be used before multiple columns, as in listing 9. At this time, the data of multiple columns will be combined to merge the duplicate data into one. The SELECT statement in listing 9_ Type column and register_ Combine the data in the date column to merge the duplicate data into one piece.
Listing 9 uses DISTINCT before multiple columns
SELECT DISTINCT product_type, regist_date FROM Product;
results of enforcement
product_type | regist_date --------------+------------ clothes | 2009-09-20 Office Supplies | 2009-09-11 Office Supplies | 2009-11-11 clothes | kitchenware | 2009-09-20 kitchenware | 2009-01-15 kitchenware | 2008-04-28
As shown in the above execution results, product_type is listed as' kitchen utensils', and register_ Two pieces of data whose date is listed as' 2009-09-20 'are merged into one.
The distinct keyword can only be used before the first column name. Therefore, please note that it cannot be written as register_ date, DISTINCT product_ type.
6, Select records according to the WHERE statement
In the previous examples, all the data stored in the table are selected, but in fact, it is not necessary to select all the data every time. In most cases, it is necessary to select the data that meets some conditions such as "the commodity type is clothes" and "the sales unit price is more than 1000 yen".
The SELECT statement specifies the conditions for querying data through the WHERE clause. In the WHERE clause, you can specify conditions such as "the value of a column is equal to this string" or "the value of a column is greater than this number". Execute the SELECT statement containing these conditions to query the records that only meet the conditions. [5]
KEYWORD
- WHERE clause
The syntax for using the WHERE clause in a SELECT statement is as follows.
Syntax 3 WHERE clause in SELECT statement
SELECT <Listing>, ...... FROM <Table name> WHERE <Conditional expression>;
Figure 3 shows the record of selecting the product_type as' clothes' from the Product table.

Fig. 3 record of selecting commodity category as' clothes'
You can also query the desired columns from the selected records. To make it easier to understand, we are querying product_ While the type column, the product_ The name column is also read out. See code listing 10 for the SELECT statement.
Listing 10 is used to SELECT product_ SELECT statement for a record whose type is listed as' clothes'
SELECT product_name, product_type FROM Product WHERE product_type = 'clothes';
results of enforcement
product_name | product_type --------------+-------------- T shirt | clothes motion T Shirt | clothes
"product_type = 'clothes' in the WHERE clause is an expression used to represent query conditions (condition expression). The equal sign is a symbol to compare whether the contents on both sides are equal. The above condition is to set product_ The value of the type column is compared with 'clothes' to determine whether it is equal. All records in the product table are compared.
KEYWORD
- Conditional expression
Next, the product specified in the SELECT statement will be selected from the queried records_ Name column and product_type column, as shown in the execution result, that is, first query the records that meet the specified conditions through the WHERE clause, and then SELECT the column specified in the SELECT statement (Figure 4).

Figure 4 select rows and then output columns
In order to confirm whether the selected data is correct, the statement in code listing 10 uses the SELECT clause to SELECT the product as the query condition_ The type column is also selected. In fact, this is not necessary. If you only want to know the product name, you can SELECT only product as shown in code listing 11_ Name column.
Code listing 11 can also uncheck the columns as query criteria
SELECT product_name FROM Product WHERE product_type = 'clothes';
results of enforcement
product_name --------------- T shirt motion T Shirt
The writing order of SQL sub sentences is fixed and cannot be changed at will. The WHERE clause must be immediately followed by the FROM clause. If the writing order is changed, it will cause an execution error (code listing 12).
In listing 12, arbitrarily changing the writing order of clauses can cause errors
SELECT product_name, product_type WHERE product_type = 'clothes' FROM Product;
Execution results (PostgreSQL)
ERROR: "FROM"Or there are grammatical errors before and after it Line 3: FROM Product; ^
Rule 4
The WHERE clause must be immediately followed by the FROM clause.
7, Writing method of notes
Finally, let's introduce the writing method of notes. Comments are the parts of SQL statements that are used to identify descriptions or precautions.
KEYWORD
- notes
Comments have no effect on the execution of SQL. Therefore, both English letters and Chinese characters can be used at will.
Notes can be written in the following two ways.
-
Single-Line Comments
After "--", it can only be written on the same line. [6]
KEYWORD
-
Single-Line Comments
-
--
-
-
multiline comment
Write between "/ *" and "* /", which can span multiple lines.
KEYWORD
-
multiline comment
-
/*
-
*/
-
See code listings 13 and 14 for practical examples.
Listing 13 shows an example of using a single line comment
-- book SELECT Statement removes duplicate rows from the result. SELECT DISTINCT product_id, purchase_price FROM Product;
Listing 14 shows an example of using multi line comments
/* This SELECT statement, Duplicate rows are removed from the results.*/ SELECT DISTINCT product_id, purchase_price FROM Product;
Any comment can be inserted in the SQL statement (listing 15, listing 16).
Listing 15 inserts a single line comment into the SQL statement
SELECT DISTINCT product_id, purchase_price -- book SELECT Statement removes duplicate rows from the result. FROM Product;
Listing 16 inserts a multiline comment into the SQL statement
SELECT DISTINCT product_id, purchase_price /* This SELECT statement, Duplicate rows are removed from the results.*/ FROM Product;
The execution results of these SELECT statements are exactly the same as when annotations are not used. Notes can help readers better understand SQL statements, especially when writing complex SQL statements. I hope you can add as many concise and understandable notes as possible. Comments can be written not only in the SELECT statement, but also in any SQL statement, as much as possible.
Rule 5
Comments are the parts of SQL statements that are used to identify descriptions or precautions.
It is divided into single line annotation and multi line annotation.
see also
(end)
The display mode of the results is slightly different according to the different clients of the RDBMS (the content of the data is the same). Unless otherwise specified, the execution results of PostgreSQL 9.5 are displayed in this tutorial. ↩︎
The order of rows may also be different from the above execution results. This may happen if the user does not set the order of rows in the execution result of the SELECT statement. The row sorting method will be Sort query results Learn in. ↩︎
Use double quotation marks to set aliases that contain spaces (whitespace). However, if you forget to use double quotation marks, you may make mistakes, so it is not recommended. You can like product_list uses underscores () instead of whitespace. ↩︎
When using strings or date constants in SQL statements, they must be enclosed in single quotes ('). ↩︎
This is the same as the function of filtering rows according to filter conditions in Excel. ↩︎
In MySQL, you need to add a half width space after "-" (if it is not added, it will not be considered as a comment). ↩︎