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

• SELECT statement basis
• Arithmetic and comparison operators
• Logical operator

(end)

1. 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. ↩︎

2. 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. ↩︎

3. 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. ↩︎

4. When using strings or date constants in SQL statements, they must be enclosed in single quotes ('). ↩︎

5. This is the same as the function of filtering rows according to filter conditions in Excel. ↩︎

6. In MySQL, you need to add a half width space after "-" (if it is not added, it will not be considered as a comment). ↩︎