In the previous article, we taught you how to correctly create a MySQL database and database table, and how to correctly add relevant records to the database table. Today, let's talk about how to retrieve data from database tables?
Getting started with MySQL 1. retrieving information from database tables
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 table mytable we 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 (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.
MySQL getting started 4. Select a specific line
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 the MySQL database. I hope you will get some benefits.