MySQL中的分组查询与子查询操作详解-优快云博客

分组查询

在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。

使用 GROUP BY 关键字的语法格式如下：

GROUPBY  <字段名>

其中，"字段名"表示需要分组的字段名称，多个字段时用逗号隔开。

GROUP BY单独使用

<code class="language-plaintext hljs">mysql> SELECT name,sex FROM tb_students_info GROUP BY sex;
+-------+--------+
| name  | sex    |
+-------+--------+
| Henry | Female |
| Dany  | Male   |
+-------+--------+
3 rows in set (0.01 sec)

mysql></code>

GROUP BY 与 GROUP_CONCAT

<code class="language-plaintext hljs">mysql> select sex, group_concat(name) from tb_students_info group by sex;
+--------+----------------------------------------+
| sex    | group_concat(name)                     |
+--------+----------------------------------------+
| Female | Henry,Jim,Thomas,Tom                   |
| Male   | Dany,Green,Jane,John,Lily,Susan,Liming |
+--------+----------------------------------------+
2 rows in set (0.00 sec)</code>

mysql> </code>

<code class="language-plaintext hljs">mysql> select age,sex,group_concat(name) names from tb_students_info group by age,sex;
+------+--------+------------------+
| age  | sex    | names            |
+------+--------+------------------+
|   21 | Male   | John             |
|   22 | Female | Thomas           |
|   22 | Male   | Jane,Lily,Liming |
|   23 | Female | Henry,Tom        |
|   23 | Male   | Green,Susan      |
|   24 | Female | Jim              |
|   25 | Male   | Dany             |
+------+--------+------------------+
7 rows in set (0.00 sec)</code>

mysql> </code>

GROUP BY 与聚合函数

<code class="language-plaintext hljs">mysql> select sex,count(sex) from tb_students_info group by sex;
+--------+------------+
| sex    | count(sex) |
+--------+------------+
| Female |          4 |
| Male   |          7 |
+--------+------------+
2 rows in set (0.00 sec)

mysql> select sex 性别,count(sex)  个数 from tb_students_info group by sex;
+--------+--------+
| 性别   | 个数   |
+--------+--------+
| Female |      4 |
| Male   |      7 |
+--------+--------+
2 rows in set (0.00 sec)

mysql></code>

GROUP BY 与 WITH ROLLUP

WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

mysql> select sex,group_concat(name) name from tb_students_info group by sex;
+--------+----------------------------------------+
| sex    | name                                   |
+--------+----------------------------------------+
| Female | Henry,Jim,Thomas,Tom                   |
| Male   | Dany,Green,Jane,John,Lily,Susan,Liming |
+--------+----------------------------------------+
2 rows in set (0.00 sec)
mysql> </code>

<code class="language-plaintext hljs">mysql> select sex,group_concat(name) name from tb_students_info group by sex with rollup;
+--------+-------------------------------------------------------------+
| sex    | name                                                        |
+--------+-------------------------------------------------------------+
| Female | Henry,Jim,Thomas,Tom                                        |
| Male   | Dany,Green,Jane,John,Lily,Susan,Liming                      |
| NULL   | Henry,Jim,Thomas,Tom,Dany,Green,Jane,John,Lily,Susan,Liming |
+--------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

with rollup 统计名单

<code class="language-plaintext hljs">mysql> select avg(age) from tb_students_info;
+----------+
| avg(age) |
+----------+
|  22.7273 |
+----------+
1 row in set (0.00 sec)

mysql></code>

子查询

子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

<code class="language-plaintext hljs">mysql> SELECT name FROM tb_students_info  WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.00 sec)

mysql> SELECT id FROM tb_course WHERE course_name = 'Java';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> SELECT name FROM tb_students_info  WHERE course_id IN (1);
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.01 sec)

mysql> SELECT name FROM tb_students_info  WHERE course_id NOT IN (SELECT id FROM tb_course WHERE course_name = 'Java');
+--------+
| name   |
+--------+
| Green  |
| Jane   |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| LiMing |
+--------+
9 rows in set (0.01 sec)

mysql>  SELECT name FROM tb_students_info WHERE course_id = (SELECT id FROM tb_course WHERE course_name = 'Python');
+------+
| name |
+------+
| Jane |
+------+
1 row in set (0.00 sec)

mysql> SELECT name FROM tb_students_info WHERE course_id <> (SELECT id FROM tb_course WHERE course_name = 'Python');
+--------+
| name   |
+--------+
| Dany   |
| Green  |
| Henry  |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| LiMing |
+--------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM tb_students_info WHERE EXISTS(SELECT course_name FROM tb_course WHERE id=1);
+----+--------+------+--------+--------+-----------+
| id | name   | age  | sex    | height | course_id |
+----+--------+------+--------+--------+-----------+
|  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Green  |   23 | Male   |    158 |         2 |
|  3 | Henry  |   23 | Female |    185 |         1 |
|  4 | Jane   |   22 | Male   |    162 |         3 |
|  5 | Jim    |   24 | Female |    175 |         2 |
|  6 | John   |   21 | Female |    172 |         4 |
|  7 | Lily   |   22 | Male   |    165 |         4 |
|  8 | Susan  |   23 | Male   |    170 |         5 |
|  9 | Thomas |   22 | Female |    178 |         5 |
| 10 | Tom    |   23 | Female |    165 |         5 |
| 11 | LiMing |   22 | Male   |    180 |         7 |
+----+--------+------+--------+--------+-----------+
11 rows in set (0.00 sec)

mysql>  SELECT * FROM tb_students_info WHERE age>24 AND EXISTS(SELECT course_name FROM tb_course WHERE id=1);
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | Dany |   25 | Male |    160 |         1 |
+----+------+------+------+--------+-----------+
1 row in set (0.00 sec)

</code>