Getting Started with MySQL (Iv.)

MySQL MySQL primer learning (iv)
--Study Chapter

We learned how to create a database and database table, and know how to add records to a database table.
So how do we retrieve data from a database table?

1, from the database table to retrieve information
In fact, we've used a SELECT statement to retrieve information from a database table.
The SELECT statement format is generally:

Select retrieves the keyword from the retrieved table WHERE the search criteria (optional)

The previously used "*" indicates that all columns are selected.
Let's continue with the table mytable we created in the previous article:

2, query all the data:
Mysql> select * FROM MyTable;
+----------+------+------------+----------+
| name | sex | Birth | birthaddr |
+----------+------+------------+--------+
| Abccs |f | 1977-07-07 | Our |
| Mary |f | 1978-12-12 | USA |
| Tom |m | 1970-09-02 | USA |
+----------+------+------------+----------+
3 row in Set (0.00 sec)

3. Correct Error Record:
If Tom's date of birth is wrong, should be 1973-09-02, you can use the UPDATE statement to fix:
mysql> Update mytable Set birth = "1973-09-02" WHERE name = "Tom";
Then use the statements in 2 to see if they have been corrected.

4, select a specific line
Tom's date of birth has been modified, so we can choose Tom's line to see if there has been a change:
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 parameter of the where above specifies the retrieval condition. We can also use the combination of conditions to query:
Mysql> SELECT * from mytable WHERE sex = "F" and birthaddr = "E";
+--------+------+------------+------------+
| Name |sex | Birth | birthaddr |
+--------+------+------------+------------+
| Abccs |f | 1977-07-07 | Our |
+--------+------+------------+------------+
1 row in Set (0.06 sec)

5. Select specific columns
If you want to view the names of everyone in the table, you can do this:
mysql> SELECT name from MyTable;
+----------+
| name |
+----------+
| Abccs |
| Mary |
| Tom |
+----------+
3 row in Set (0.00 sec)
If you want to list names and genders, you can separate the keyword name from the birth with a comma:
Myaql> select Name,birth from MyTable;

6, the row to sort
We can sort the records in a table by their 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 to sort the reverse order:
mysql> SELECT name, birth from MyTable to 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 has to count some data, such as the number of employees in the table, we need to use the row Count function count ().
The count () function is used to count records that are NOT NULL results:
Mysql> SELECT COUNT (*) from mytable;
+----------+
| COUNT (*) |
+----------+
| 3 |
+----------+
1 row in Set (0.06 sec)

Number of men and women in the workforce:
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 have grouped sex by using GROUP by.