排序查询

题目

单字段

例：查询goods表中书籍id、name字段的数据，并按照id字段值进行排序

SELECT goods.ID ,goods.`name` FROM goods WHERE type = '书籍'  ORDER BY ID ASC;

多字段

例：查询goods表中书籍的id、name字段的数据，并按照price降序，id升序进行排序

SELECT goods.ID ,goods.`name` FROM goods WHERE type = '书籍'  ORDER BY price DESC ,ID ASC;

限制查询LIMIT

例：显示goods表中第四条到第6条

SELECT * FROM goods LIMIT n-1,m-(n-1);

SELECT * FROM goods LIMIT 3,3;

聚合函数count

例：查询goods表中最高价格和最低价格

SELECT MAX(goods.price)maxprice ,MIN(goods.price)miniprice FROM goods;

例：统计goods表的商品平均价格

SELECT AVG(goods.price)average FROM goods;

分组查询GROUP BY

例查询平均价格大于5元的商品类别

SELECT type, AVG(goods.price)average FROM goods GROUP BY type HAVING average > 5;