How to retrieve data from a MySQL database table

In the "Create Database and database table with MySQL" article, how do we create a database and database table and how to add records to the database table. How can we retrieve data from database tables? 1. retrieving information from the database table. In fact, we have used the SELECT statement to retrieve information from the database table. Sele

In the "Create Database and database table with MySQL" article, how do we create a database and database table and how to add records to the database table. How can we retrieve data from database tables? 1. retrieving information from the database table. In fact, we have used the SELECT statement to retrieve information from the database table. Sele

In the "Create Database and database table with MySQL" article, how do we create a database and database table and how to add records to the database table. How can we retrieve data from database tables?
1. Retrieve Information from a database table
In fact, we have used the SELECT statement to retrieve information from database tables.
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> 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> 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> 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> 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> 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> 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> 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
The database often needs 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> select count (*) FROM mytable; + ---------- + | COUNT (*) | + ---------- + | 3 | + ---------- + 1 row in set (0.06 sec) number of male and female employees: mysql> 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.