1.排序(ORDER BY)
使用:ORDER BY 对查询到的数据进行排序操作
升序:ASC(ascend) 默认不写升序
降序:DESC(descend)
SELECT employee_id,salary FROM employees ORDER BY salary DESC;
列的别名只能在ORDER BY 中使用,不能在WHERE中使用
二级排序(多级)
如显示员工信息,按照department_id的降序排列,salary的升序排列
SELECT employee_id,salary,department_id AS DD FROM employees ORDER BY DD DESC,salary ASC;
2.分页(LIMIT)
LIMIT格式:LIMIT 位置偏移量,条目数
#每页显示pageSize条记录,此时显示第pageNo页 #公式:LIMIT (pageNo-1)*pageSize,pageSize; #每页显示20条记录,此时显示第1页 SELECT employee_id,last_name FROM employees LIMIT 0,20 #每页显示20条记录,此时显示第2页 SELECT employee_id,last_name FROM employees LIMIT 20,20 #每页显示20条记录,此时显示第3页 SELECT employee_id,last_name FROM employees LIMIT 40,20
#WHERE...ORDER BY ...LIMIT声明顺序如下 SELECT employee_id,last_name,salary FROM employees WHERE salary > 6000 ORDER BY salary DESC LIMIT 10;
练习
#1.查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序显示 SELECT last_name,department_id,salary*12 annual_sal FROM employees ORDER BY annual_sal DESC,last_name ASC; #2.选择工资不在8000到17000的员工的姓名和工资,按工资降序,显示第21到40位置的数据 SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC LIMIT 20,20; #3.查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序 SELECT employee_id,last_name,department_id FROM employees WHERE email LIKE "%E%" ORDER BY LENGTH(email) DESC,department_id ASC;标签:salary,20,分页,ORDER,LIMIT,SQL,排序,id,SELECT From: https://www.cnblogs.com/hhs1998/p/17180348.html