WHERE 、GROUP BY，HAVING、ORDER BY、LIME

条件表达式 WHERE

对 记录进行过滤，如果没有指定WHERE子句，则显示所有记录。
在WHERE表达式中，可以使用MySQL支持的函数或运算符。

查询分组 GROUP BY

[GROUP BY {col_name| position} [ASC| DESC],…]
asc 升序dese降序

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  27 |    0 |
|  3 | Json     | 123                              |  25 |    0 |
|  4 | Json     | 123                              |  21 |    0 |
|  5 | Json     | 123                              |  23 |    0 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  19 |    0 |
|  7 | Ben      | 456                              |   8 |    0 |
+----+----------+----------------------------------+-----+------+
6 rows in set (0.00 sec)

按照性别分组

mysql> SELECT sex FROM users GROUP BY sex;
+------+
| sex  |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

分组条件

[HAVING where_condition]

mysql> SELECT sex FROM users GROUP BY sex HAVING age>30;
ERROR 1054 (42S22): Unknown column 'age' in 'having clause'

这里age要么在SELECT中出现，要么是聚合函数

在SELECT语句中

mysql> SELECT sex,age FROM users GROUP BY sex HAVING age>20;
+------+-----+
| sex  | age |
+------+-----+
|    0 |  27 |
+------+-----+
1 row in set (0.00 sec)

聚合函数

mysql> SELECT sex FROM users GROUP BY sex HAVING count(id)>3;
+------+
| sex  |
+------+
|    0 |
+------+
1 row in set (0.02 sec)

对查询结果进行排序 ORDER BY

[ORDER BY {col_name | expr | position} [ASC| DESC],…]
可以多条件排序，当第一个条件不起作用时，遵循后面的条件
- SELECT *FROM users ORDER BY age DESC ，id ;
- 当age相同时，按照id升序排序。

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  27 |    0 |
|  3 | Json     | 123                              |  25 |    0 |
|  4 | Json     | 123                              |  21 |    0 |
|  5 | Json     | 123                              |  23 |    0 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  19 |    0 |
|  7 | Ben      | 456                              |   8 |    0 |
|  8 | Hek      | 11111                            |  33 | NULL |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT *FROM users ORDER BY id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  8 | Hek      | 11111                            |  33 | NULL |
|  7 | Ben      | 456                              |   8 |    0 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  19 |    0 |
|  5 | Json     | 123                              |  23 |    0 |
|  4 | Json     | 123                              |  21 |    0 |
|  3 | Json     | 123                              |  25 |    0 |
|  1 | Tom      | 123                              |  27 |    0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

限制查询结果返回的数量 LIME

[LIME {[offset,] row_count| row_count OFFSET offset}]

mysql> SELECT *FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  27 |    0 |
|  3 | Json     | 123                              |  25 |    0 |
|  4 | Json     | 123                              |  21 |    0 |
|  5 | Json     | 123                              |  23 |    0 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  19 |    0 |
|  7 | Ben      | 456                              |   8 |    0 |
|  8 | Hek      | 11111                            |  33 | NULL |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

返回两天记录

mysql> SELECT *FROM users LIMIT 2;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | Tom      | 123      |  27 |    0 |
|  3 | Json     | 123      |  25 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

从index2开始 返回3条

mysql> SELECT *FROM users LIMIT 2 ,3;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  4 | Json     | 123                              |  21 |    0 |
|  5 | Json     | 123                              |  23 |    0 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  19 |    0 |
+----+----------+----------------------------------+-----+------+
3 rows in set (0.00 sec)

having和where的区别：

作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组。WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反，HAVING 子句总是包含聚集函数。（严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）在前面的例子里，我们可以在 WHERE 里应用城市名称限制，因为它不需要聚集。 这样比在 HAVING 里增加限制更加高效，因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算综上所述：having一般跟在group by之后，执行记录组选择的一部分来工作的。where则是执行所有数据来工作的。再者having可以用聚合函数，如having sum(qty)>1000