1. 排序
ORDER BY
没有使用排序操作,默认情况下查询返回的数据按照添加数据的顺序显示的。
SELECT * FROM employees;
练习:按照 salary 从低到高的顺序显示员工信息。
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary;
ASC(升序)DESC(降序)
练习:按照 salary 从高到低的顺序显示员工信息
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC;
ORDER BY后面没有显式指明排序方式,默认按照升序排列。
练习:使用列的别名进行排序
SELECT employee_id, last_name, salary*12 AS "annual_salary"
FROM employees
ORDER BY annual_salary;
列的别名只能在 ORDER BY 中使用,不能在 WHERE 中使用
WHERE 与ORDER BY 同时出现,WHERE 与 FROM 相连。
SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id IN (50, 60, 70)
ORDER BY department_id DESC;
二级排序
练习:显示员工信息,按照department_id的降序排序,salary的升序排列
SELECT employee_id, last_name, salary, department_id
FROM employees
ORDER BY department_id DESC, salary ASC;
2. LIMIT 分页
格式:LIMIT 位置偏移量, 条目数
结构 “LIMIT 0, 条目数” 等价于 “LIMIT 条目数”
需求1:每页显示5条记录,此时显示第一页
SELECT employee_id, last_name
FROM employees
LIMIT 0, 5 # 偏移量
需求2:每页显示5条记录,此时显示第二页
SELECT employee_id, last_name
FROM employees
LIMIT 5, 5 # 偏移量
需求:每页显示 pageSize 条数据,显示第pageNo页
公式:LIMIT (pageNo - 1) * pageSize, pageSize
SELECT employee_id, last_name
FROM employees
LIMIT (pageNo - 1) * pageSize, pageSize;
OFFSET
练习:表中有107条数据,显示第32,33条数据
SELECT employee_id, last_name, salary
FROM employees
LIMIT 2 OFFSET 31;
3. WHERE、OEDER BY、LIMIT 声明顺序
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
LIMIT 0, 10;
练习:表中有107条数据,显示第32,33条数据
SELECT employee_id, last_name, salary
FROM employees
LIMIT 31, 2;
练习: 查询员工表中工资最高的员工信息
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 0, 1;
拓展
Oracle 不支持 LIMIT
查询前10条记录
SELECT rownum, employee_id, salary
FROM employees
WHERE rownum<=10;
练习
#1. 查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序显示
SELECT last_name, department_id, salary*(1+IFNULL(commission_pct, 0))*12 AS "annual_salary"
FROM employees
ORDER BY annual_salary 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, email, department_id
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id ASC;