GROUP by and have and date functions commonly used in MySQL

A. GROUP by and having in MySQL
GROUP by is commonly used in conjunction with aggregation functions (Sum,min,max,count).

Like what:
SELECT Category,sum (Money) as "total" from the User_money GROUP by category;
Group statistics by Category User_money table how much money each category totals

Now if you add a condition, you need to count the amount of money spent in each category, such as rule=1 for expenses, then rewrite the statement to
SELECT Category,sum (Money) as "total" from the User_money WHERE rule=1 GROUP by category;

If you need to count the amount of money spent on each category, and you need to filter out the group of spending money greater than 500
SELECT Category,sum (Money) as "total" from User_money WHERE rule=1 GROUP by category have ' total ' >500;

1.WHERE is used to filter the data before grouping, having to filter the data after grouping.
2. Use GROUP by two points of attention
(1) The field that appears in select is either an aggregate function or appears in group by, otherwise the SQL statement can be executed, but the field simply takes the field value of the first record in the grouped record (the value can be changed by using order by), and it doesn't make any sense
(2) Use where to filter the data before grouping, then use Group by to group, and then use having to filter the data after grouping, both the conditional fields cannot appear after the aggregate function computed by the name field
(3) Order BY and GROUP by sequence issues
SELECT Category,sum (Money) as "total" from User_money ORDER by ID DESC GROUP by category have ' total ' >500;
The order by is placed in front of group by, which means that the unsorted results are sorted (the sort field cannot be an aggregate function after the computed name field) generally useless, anyway, your final goal is to aggregate the calculations.
SELECT Category,sum (Money) as "total" from User_money DESC GROUP by category have ' Total ' >500 ORDER by T desc;
GROUP BY is placed in front of the order by, not sorting the result after grouping, this time the sort field is the aggregate function after the calculation of the Name field or group by .
(4) Multi-field grouping in group by
Group the first field first, and then group the results of the previous grouping again based on the second field

Two. Useful date functions for MySQL
SELECT now ()- 2017-05-20 20:36:49
SELECT curdate (), 2017-05-20
SELECT curdate (), 2017-05-20
SELECT current_time () 20:38:37
SELECT Unix_timestamp (now ()) ->1495283945 convert date to timestamp

GROUP by and have and date functions commonly used in MySQL