How to query all columns (fields) in a table with mysql?

Mysql method of querying all columns in the table: 1. Query all columns of the table through "*", the syntax is "SELECT * FROM table name;"; 2. Query the table by listing all fields of the table All columns, syntax "SELECT field list FROM table name;".

MySQL data table is composed of rows and columns. Usually the "columns" of the table are called fields (Field), and the "rows" of the table are called For the record (Record).

Query all columns (fields) in the table

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 "*" wildcard to query all fields

List all fields of the table

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

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

SELECT * FROM 表名;

When using "*" to query, you can only sort according to the order of the fields in the data table. You cannot Change the order of 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 "*".

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.

SELECT 字段列表 FROM 表名;

If you forget the field name, you can use the DESC command to view the structure of the table.

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 character 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.

Recommended tutorial: mysql video tutorial

The above is the detailed content of How to query all columns (fields) in a table with mysql?. For more information, please follow other related articles on the PHP Chinese website!