四、使用GROUP BY子句进行分组
根据性别分组,并查询每组的人数
mysql> SELECT sex AS 性别,COUNT(*) AS 人数
-> FROM students
-> GROUP BY sex;
+------+------+
| 性别 | 人数 |
+------+------+
| 女 | 3 |
| 男 | 5 |
+------+------+
2 rows in set
五、使用HAVING子句进行对分组的过滤
根据性别分组,查询每组的人数,显示人数大于4的分组
mysql> SELECT sex,COUNT(*)
-> FROM students
-> GROUP BY sex
-> HAVING COUNT(*)>4;
+-----+----------+
| sex | COUNT(*) |
+-----+----------+
| 男 | 5 |
+-----+----------+
1 row in set
六、使用ORDER BY子句进行排序
根据sid进行升序(ASC),降序(DESC)排序
mysql> SELECT * FROM students
-> ORDER BY sid ASC;
+-----+-------+-----+
| sid | sname | sex |
+-----+-------+-----+
| 1 | tom | 男 |
| 2 | toy | 女 |
| 3 | lili | 女 |
| 4 | lidan | 男 |
| 5 | sam | 男 |
| 6 | ll | 男 |
| 7 | qw | 女 |
| 8 | wt | 男 |
+-----+-------+-----+
8 rows in set
mysql> SELECT * FROM students
-> ORDER BY sid DESC;
+-----+-------+-----+
| sid | sname | sex |
+-----+-------+-----+
| 8 | wt | 男 |
| 7 | qw | 女 |
| 6 | ll | 男 |
| 5 | sam | 男 |
| 4 | lidan | 男 |
| 3 | lili | 女 |
| 2 | toy | 女 |
| 1 | tom | 男 |
+-----+-------+-----+
8 rows in set
七、使用LIMIT子句进行分页查询
显示查询结果第三个元素之后的3个元素
mysql> SELECT * FROM students
-> LIMIT 3,3;
+-----+-------+-----+
| sid | sname | sex |
+-----+-------+-----+
| 4 | lidan | 男 |
| 5 | sam | 男 |
| 6 | ll | 男 |
+-----+-------+-----+
3 rows in set