1、排序
order by
用于对结果集按照一个列或者多个列进行排序。默认按照升序对记录进行排序,如果需要按照降序对记录进行排序,可以使用 desc
关键字。
order by
对多列排序的时候,先排序的列放前面,后排序的列放后面。并且,不同的列可以有不同的排序规则。
SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;
2、分组
group by
:
group by
子句将记录分组到汇总行中。group by
为每个组返回一个记录。group by
通常还涉及聚合count
,max
,sum
,avg
等。group by
可以按一列或多列进行分组。group by
按分组字段进行排序后,order by
可以以汇总字段来进行排序。
分组
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;
分组后排序
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;
having
:
having
用于对汇总的group by
结果进行过滤。having
一般都是和group by
连用。where
和having
可以在相同的查询中。
使用 WHERE 和 HAVING 过滤数据
SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;
having
vs where
:
where
:过滤过滤指定的行,后面不能加聚合函数(分组函数)。where
在group by
前。having
:过滤分组,一般都是和group by
连用,不能单独使用。having
在group by
之后。