What is the use of database query statement select?

The database query statement select is used to select data from the table, and the results are stored in a result table (called a result set); the syntax format is "SELECT [column name] FROM table name [WHERE clause and other restrictions];".

(Recommended tutorial: mysql video tutorial)

SELECT statement

The SELECT statement is used to select data from a table.

The results are stored in a results table (called a result set).

The syntax format of SELECT is as follows:

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

Among them, the meaning of each clause is as follows:

• ##{*|< ;Field column name>} A field list containing the asterisk wildcard character, indicating the name of the field to be queried.

##05cee8b6ad5efd33489a0be0260a7bbc, a31a6b5c4a9ac86f75409c3950db0e23…, Table 1 and Table 2 represent the source of query data, which can be single or multiple.
WHERE ffbeece48539e6983ff3249db04f27c9 is optional. If selected, the query data must meet the query conditions.
GROUP BY3b26370eed070b4e2af74808aa8f2dee, this clause tells MySQL how to display the queried data and group it according to the specified field.
[ORDER BY0f5333100010744a1571ca8552350494], this clause tells MySQL in what order to display the queried data. The possible sorting is ascending order (ASC) and descending order (DESC ), which is ascending by default.

• ##[LIMIT[ab76cfca1a1dc7ff1291dcdf873f72ec,]bc984d207842008469e14f06321b6461], this clause tells MySQL to display the number of queried data items each time.
Use "*" to query all fields in the table

SELECT You can use "*" to query data for all fields in the table. The syntax format is as follows :

SELECT * FROM 表名;

When using "*" to query, you can only sort the fields in the order in the data table, and you cannot change the order of the fields.

Example 1

Query the data of all fields from the tb_students_info table. The SQL statement and running results are as follows.

mysql> use test_db;
Database changed
mysql> SELECT * FROM tb_students_info;
+----+--------+---------+------+------+--------+------------+
| id | name   | dept_id | age  | sex  | height | login_date |
+----+--------+---------+------+------+--------+------------+
|  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 |
|  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 |
|  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 |
|  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 |
|  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 |
|  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 |
|  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 |
| 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.26 sec)

The results show that when using the "*" wildcard character, all columns will be returned, and the data columns will be displayed in the order when the table was created.

Note: Generally, unless you need to use all field data in the table, it is best not to use the wildcard "*". Although using wildcards can save time typing query statements, obtaining unnecessary column data often reduces the efficiency of the query and the application used. The advantage of using "*" is that when the names of the required columns are not known, they can be obtained via "*".

Specified fields in the query table

The syntax format of a certain field in the query table is:

SELECT < 列名 > FROM < 表名 >;

Example 2

Query the names of all students in the name column in the tb_students_info table. The SQL statement and running results are as follows.

mysql> SELECT name FROM tb_students_info;
+--------+
| name   |
+--------+
| Dany   |
| Green  |
| Henry  |
| Jane   |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
+--------+
10 rows in set (0.00 sec)

The output shows all data under the name field in the tb_students_info table.

Use the SELECT statement to obtain data under multiple fields. You only need to specify the field name to be searched after the keyword SELECT. Different field names are separated by commas "," after the last field. There is no need to add commas. The syntax format is as follows:

SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;

Example 3

Get the three columns of id, name and height from the tb_students_info table. The SQL statement and running results are as follows.

mysql> SELECT id,name,height
    -> FROM tb_students_info;
+----+--------+--------+
| id | name   | height |
+----+--------+--------+
|  1 | Dany   |    160 |
|  2 | Green  |    158 |
|  3 | Henry  |    185 |
|  4 | Jane   |    162 |
|  5 | Jim    |    175 |
|  6 | John   |    172 |
|  7 | Lily   |    165 |
|  8 | Susan  |    170 |
|  9 | Thomas |    178 |
| 10 | Tom    |    165 |
+----+--------+--------+
10 rows in set (0.00 sec)

The output shows all data under the three fields of id, name and height in the tb_students_info table.

The above is the detailed content of What is the use of database query statement select?. For more information, please follow other related articles on the PHP Chinese website!