数据库Ⅴ——查询数据操作（2）

一、ORDER BY对查询结果排序

1.升序

• 按照id降序排列DESC ,默认的是升序ASC

SELECT * FROM cms_user ORDER BY id ;
SELECT * FROM cms_user ORDER BY id ASC;
SELECT * FROM cms_user ORDER BY id DESC;

• 按照年龄升序排列

SELECT * FROM cms_user ORDER BY age ASC;

2.降序

• 按字段位置降序排列

SELECT * FROM cms_user ORDER BY 1 DESC;

更新数据

UPDATE cms_user SET age=12 WHERE id=5;

3.升序、降序同时使用

• 按照年龄升序，id降序排列

SELECT * FROM cms_user ORDER BY age ASC,id DESC;
#查询id,age,sex，显示分组详情，统计各组人数和各组年龄总和，并且限制条件id>=2 ，按照sex分组，每组人数大于等于2，并且按照年龄降序，id升序排列
SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age 
FROM cms_user 
WHERE id>=2 
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC;

– 实现记录随机

SELECT * FROM cms_user ORDER BY RAND();

二、LIMIT限制显示条数

1.查询前n条记录

• 查询表中前3条记录

SELECT * FROM cms_user LIMIT 3;

• 查询按id降序排列的前五条记录

SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

2.根据（起始位置，步长）查询记录

• 查询表中前一条记录（起始位置，步长）

SELECT * FROM cms_user LIMIT 1;
SELECT * FROM cms_user LIMIT 0,1;
SELECT * FROM cms_user LIMIT 1,1;
SELECT * FROM cms_user LIMIT 0,5;

• 查询id,age,sex，显示分组详情，统计各组人数和各组年龄最大值、最小值、平均值、总和，并且限制条件id>=1 ，按照sex分组，每组人数大于等于2，并且按照年龄降序排列，取前两条记录

SELECT id,sex,age,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;