It is not difficult to retrieve data from MySQL database tables.

Today, we will mainly talk about how to correctly retrieve related data from MySQL database tables, if you are interested in the actual operations on the MySQL database table to retrieve the relevant data, you can click to view the following article.

1. Retrieve Information from the MySQL database table

In fact, we have used the SELECT statement to retrieve information from the MySQL database table.

The select statement format is generally:

SELECT search keyword FROM the retrieved table WHERE search condition (optional)

The previously used "*" indicates Selecting All columns.

Next we will continue to use the mytable table created in the previous article.

2. query all data:

 
 

  
  
MySQL (the best combination with PHP)> select * from mytable;
  
  
+ ---------- + ------ + ------------ + ---------- +
  
  
| Name | sex | birth | birthaddr |
  
  
+ ---------- + ------ + ------------ + -------- +
  
  
| Abccs | f | 1977-07-07 | china |
  
  
| Mary | f | 1978-12-12 | usa |
  
  
| Tom | m | 1970-09-02 | usa |
  
  
+ ---------- + ------ + ------------ + ---------- +
  
  
3 row in set (0.00 sec)
 
 

3. Corrected error records:

If the birth date of tom is incorrect, it should be, you can use the update statement to correct it: MySQL (the best combination with PHP)> update mytable set birth = "1973-09-02" where name = "tom ";

Use the statement in step 2 to check whether it has been corrected.

4. Select a Specific Row

We modified the birth date of tom above. We can select tom to see if there have been any changes:

 
 

  
  
MySQL (best combination with PHP)> select * from mytable where name = "tom ";
  
  
+ -------- + ------ + ------------ +
  
  
| Name | sex | birth | birthaddr |
  
  
+ -------- + ------ + ------------ +
  
  
| Tom | m | 1973-09-02 | usa |
  
  
+ -------- + ------ + ------------ +
  
  
1 row in set (0.06 sec)
 
 

The above WHERE parameter specifies the search condition. We can also use combination conditions for queries:

 
 

  
  
MySQL (best combination with PHP)> SELECT * FROM mytable WHERE sex = "f" AND birthaddr = "china ";
  
  
+ -------- + ------ + ------------ +
  
  
| Name | sex | birth | birthaddr |
  
  
+ -------- + ------ + ------------ +
  
  
| Abccs | f | 1977-07-07 | china |
  
  
+ -------- + ------ + ------------ +
  
  
1 row in set (0.06 sec)
 
 

5. Select a specific column

If you want to view the names of all users in the table, you can perform the following operations:

 
 

  
  
MySQL (best combination with PHP)> SELECT name FROM mytable;
  
  
+ ---------- +
  
  
| Name |
  
  
+ ---------- +
  
  
| Abccs |
  
  
| Mary |
  
  
| Tom |
  
  
+ ---------- +
  
  
3 row in set (0.00 sec)
 
 

If you want to list the names and Gender columns, you can use commas to separate the keyword name and birth: myaql> select name, birth from mytable;

6. Sort rows

We can sort the records in the table by birthday size:

 
 

  
  
MySQL (best combination with PHP)> SELECT name, birth FROM mytable order by birth;
  
  
+ ---------- + ------------ +
  
  
| Name | birth |
  
  
+ ---------- + ------------ +
  
  
| Tom | 1973-09-02 |
  
  
| Abccs | 1977-07-07 |
  
  
| Mary | 1978-12-12 |
  
  
+ ---------- + ------------ +
  
  
3 row in set (0.00 sec)
 
 

We can use DESC for reverse sorting:

 
 

  
  
MySQL (best combination with PHP)> SELECT name, birth FROM mytable order by birth DESC;
  
  
+ ---------- + ------------ +
  
  
| Name | birth |
  
  
+ ---------- + ------------ +
  
  
| Mary | 1978-12-12 |
  
  
| Abccs | 1977-07-07 |
  
  
| Tom | 1973-09-02 |
  
  
+ ---------- + ------------ +
  
  
3 row in set (0.00 sec)
 
 

7. Row count

MySQL databases often need to collect some data, such as the number of employees in the table, we need to use the row counting function COUNT (). The COUNT () function is used to COUNT records with non-NULL results:

 
 

  
  
MySQL (the best combination with PHP)> select count (*) FROM mytable;
  
  
+ ---------- +
  
  
| COUNT (*) |
  
  
+ ---------- +
  
  
| 3 |
  
  
+ ---------- +
  
  
1 row in set (0.06 sec)
 
 

Number of male and female employees:

 
 

  
  
MySQL (the best combination with PHP)> SELECT sex, COUNT (*) FROM mytable group by sex;
  
  
+ ------ + ---------- +
  
  
| Sex | COUNT (*) |
  
  
+ ------ + ---------- +
  
  
| F | 2 |
  
  
| M | 1 |
  
  
+ ------ + ---------- +
  
  
2 row in set (0.00 sec)
 
 

Note that we use group by to group sex. The above content is an introduction to how to retrieve data from a MySQL database table.