SELECT expr,...
FROM table
[WHERE where_definition]
[GROUP BY~[ASC|DESC],...]
[HAVING where_definition]
[ORDER BY~[ASC|DESC],...]
[LIMIT~]
where指定范围；
having限定条件；
order by指定排序方式。
+----+--------+-----+-----+-------+------+-------+------------+
| id | name | age | sex | grade | math | level | date |
+----+--------+-----+-----+-------+------+-------+------------+
| 1 | 红红 | 12 | 女 | 66 | 87 | 3 | 2016-01-04 |
| 3 | 小王王 | 22 | 男 | 67 | 59 | 1 | 2016-01-13 |
| 4 | 小_ | 20 | 男 | 77 | 72 | 3 | 2016-01-07 |
| 5 | 张三 | 19 | 男 | 88 | 65 | 3 | 2016-01-21 |
| 6 | 溜溜 | 25 | 女 | 66 | 68 | 2 | 2016-01-08 |
| 7 | 可可 | 22 | 女 | 65 | 93 | 1 | 2016-01-28 |
| 8 | 糊糊 | 12 | 女 | 90 | 88 | 2 | 2016-01-19 |
| 9 | Mary | 21 | 男 | 88 | 79 | 1 | 2016-01-06 |
+----+--------+-----+-----+-------+------+-------+------------+
1、每个等级level的math最高分:
SELECT `level`,MAX(math)FROM student GROUP BY `level`;
+-------+-----------+
| level | MAX(math) |
+-------+-----------+
| 1 | 93 |
| 2 | 88 |
| 3 | 87 |
+-------+-----------+
3 rows in set
# 每个等级的总数学成绩
mysql> SELECT `level`,
sum
(math) FROM student GROUP BY `level`;
+-------+-----------+
| level | sum(math) |
+-------+-----------+
| 1 | 231 |
| 2 | 156 |
| 3 | 224 |
+-------+-----------+
3 rows in set
SELECT `level`,sex,MAX(math)FROM student GROUP BY `level`,sex;
先level后sex。
+-------+-----+-----------+
| level | sex | MAX(math) |
+-------+-----+-----------+
| 1 | 女 | 93 |
| 1 | 男 | 79 |
| 2 | 女 | 88 |
| 3 | 女 | 87 |
| 3 | 男 | 72 |
+-------+-----+-----------+
5 rows in set
为何 女nv 在 男nan 的前面？？？order by亦如此。
3、having限定条件
SELECT `level`,MAX(math)FROM student GROUP BY `level` HAVING COUNT(*)>2;
和2对比。按level分组，且要求每个level中的人数大于2.
+-------+-----------+
| level | MAX(math) |
+-------+-----------+
| 1 | 93 |
| 3 | 87 |
+-------+-----------+
2 rows in set
4、having、order by
SELECT id,`level`,MAX(math),AVG(grade)FROM student GROUPBY `level` HAVINGAVG(grade)>76ORDERBY id;
grade平均成绩大于76，按id排序。
+----+-------+-----------+------------+
| id | level | MAX(math) | AVG(grade) |
+----+-------+-----------+------------+
| 1 | 3 | 87 | 77 |
| 6 | 2 | 88 | 78 |
+----+-------+-----------+------------+
2 rows in set
