How to query data of all fields in mysql

In mysql, you can use the SELECT statement to query the data of all fields, the syntax is "SELECT * FROM table name;" or "SELECT all field name list FROM table name;".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In MySQL, you can use the SELECT statement to query data. Querying data refers to using different query methods to obtain different data from the database according to needs. It is the most frequently used and important operation.

How does mysql query the data of all fields

Querying all fields refers to querying the data of all fields in the table. MySQL provides the following 2 ways to query all fields in the table.

• Use the "*" wildcard to query all fields

• List all fields of the table

1) Use "*" to query all fields in the table

SELECT You can use "*" to query data for all fields in the table, syntax The 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, 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 character "*". 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 "*".

2) List all fields in the table

The field name after the SELECT keyword is the field that needs to be found, so the names of all fields in the table can be followed by after the SELECT keyword. If you forget the field names, you can use the DESC command to see the structure of the table.

The syntax format is as follows:

SELECT 所有字段名列表 FROM 表名;

Sometimes, because the table has many fields, it is not possible to remember the names of all fields, so this method is very inconvenient and is not recommended.

Example 2

Query all data in the tb_students_info table. The SQL statement can also be written as follows:

SELECT id,name,dept_id,age,sex,height,login_date FROM tb_students_info;

The running result is the same as Example 1.

This query method is more flexible. If you need to change the order of field display, you only need to adjust the order of the field list after the SELECT keyword.

Although the method of listing all fields of a table is more flexible, the "*" wildcard is usually used when querying all fields. Using "*" is relatively simple, especially when there are many fields in the table, the advantages of this method are more obvious. Of course, if you need to change the order in which fields are displayed, you can choose to list all fields in the table.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to query data of all fields in mysql. For more information, please follow other related articles on the PHP Chinese website!