MySQL basic simple operation 03

MySQL basic simple operation

Now I create a data table with the following contents:

mysql> select * from gubeiqing_table;
+----------+-----+
| name     | age |
+----------+-----+
| gbq      | 21  |
| zhangsan | 20  |
| lisi     | 22  |
+----------+-----+
3 rows in set (0.00 sec)

I now need to sort the data in ascending order by age.

mysql> select * from gubeiqing_table order by age ASC;
+----------+-----+
| name     | age |
+----------+-----+
| zhangsan | 20  |
| gbq      | 21  |
| lisi     | 22  |
+----------+-----+
3 rows in set (0.00 sec)

You can see that the databases are sorted in ascending order.
order by is used for sorting, ASC is ascending, DESC is descending.

Now I have a data table like this.

mysql> select * from gubeiqing_table;
+----------+-----+
| name     | age |
+----------+-----+
| gbq      | 21  |
| zhangsan | 20  |
| lisi     | 22  |
| zhangsi  | 21  |
| lisan    | 22  |
+----------+-----+
5 rows in set (0.00 sec)

I want to know what age groups are in this data sheet.

mysql> select age from gubeiqing_table group by age;
+-----+
| age |
+-----+
| 21  |
| 20  |
| 22  |
+-----+
3 rows in set (0.00 sec)

First look up the age field from the data table, and then group the ages by group by.
You can also count how many people are of these ages.

mysql> select age,count(*) from gubeiqing_table group by age;
+-----+----------+
| age | count(*) |
+-----+----------+
| 21  |        2 |
| 20  |        1 |
| 22  |        2 |
+-----+----------+
3 rows in set (0.00 sec)

Next, learn to use UNION to join the results of more than two SELECT statements into a result set. Multiple SELECT statements will delete duplicate data..

mysql> show tables;
+---------------------+
| Tables_in_gubeiqing |
+---------------------+
| gbq                 |
| gubeiqing_table     |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from gbq;
+------+-----+
| name | age |
+------+-----+
| qaz  | 20  |
| wsx  | 23  |
| edc  | 21  |
+------+-----+
3 rows in set (0.00 sec)

mysql> select * from gubeiqing_table;
+----------+-----+
| name     | age |
+----------+-----+
| gbq      | 21  |
| zhangsan | 20  |
| lisi     | 22  |
| zhangsi  | 21  |
| lisan    | 22  |
+----------+-----+
5 rows in set (0.00 sec)

I want to see two tables now

mysql> select age from gbq
    -> union
    -> select age from gubeiqing_table;
+-----+
| age |
+-----+
| 20  |
| 23  |
| 21  |
| 22  |
+-----+
4 rows in set (0.00 sec)

Now you see all the values, but not all the values of this field. You can see all the values after de duplication with UNION. If you don't want to de duplication, use UNION ALL.

mysql> select age from gbq
    -> union all
    -> select age from gubeiqing_table;
+-----+
| age |
+-----+
| 20  |
| 23  |
| 21  |
| 21  |
| 20  |
| 22  |
| 21  |
| 22  |
+-----+
8 rows in set (0.00 sec)

So you see all the values.
If you guys don't mind, I just want some traffic. Personal blog

Tags: MySQL

Posted on Thu, 13 Feb 2020 12:02:14 -0800 by vandalite