GROUP BY 与聚合函数
mysql> SELECT sex,COUNT(sex) FROM tb_students_info GROUP BY sex;
+--------+------------+
| sex | COUNT(sex) |
+--------+------------+
| Female | 5 |
| Male | 6 |
+--------+------------+
2 rows in set (0.01 sec)
mysql> SELECT sex,COUNT(sex) FROM tb_students_info ;
+------+------------+
| sex | COUNT(sex) |
+------+------------+
| Male | 11 |
+------+------------+
1 row in set (0.00 sec)
mysql> SELECT sex 性别,COUNT(sex) 个数 FROM tb_students_info GROUP BY sex;
+--------+--------+
| 性别 | 个数 |
+--------+--------+
| Female | 5 |
| Male | 6 |
+--------+--------+
2 rows in set (0.00 sec)
mysql> SELECT sex 性别,COUNT(1) 个数 FROM tb_students_info GROUP BY sex;
+--------+--------+
| 性别 | 个数 |
+--------+--------+
| Female | 5 |
| Male | 6 |
+--------+--------+
2 rows in set (0.00 sec)
GROUP BY 与 WITH ROLLUP
mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex WITH ROLLUP;
+--------+-------------------------------------------------------------+
| sex | GROUP_CONCAT(name) |
+--------+-------------------------------------------------------------+
| Female | Henry,Jim,John,Thomas,Tom |
| Male | Dany,Green,Jane,Lily,Susan,LiMing |
| NULL | Henry,Jim,John,Thomas,Tom,Dany,Green,Jane,Lily,Susan,LiMing |
+--------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex ;
+--------+-----------------------------------+
| sex | GROUP_CONCAT(name) |
+--------+-----------------------------------+
| Female | Henry,Jim,John,Thomas,Tom |
| Male | Dany,Green,Jane,Lily,Susan,LiMing |
+--------+-----------------------------------+
2 rows in set (0.00 sec)
mysql> select avg(age) from tb_students_info;
+----------+
| avg(age) |
+----------+
| 22.7273 |
+----------+
1 row in set (0.00 sec)
mysql> select avg(age) from tb_students_info group by sex;
+----------+
| avg(age) |
+----------+
| 22.6000 |
| 22.8333 |
+----------+
2 rows in set (0.00 sec)
子查询
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)