mysql排序与分页

**

mysql排序与分页

**
#第5章排序与分页

#如果没有使用排序操作，查询将返回数据按照添加数据的顺序显示
#练习，按照salary从高到低的顺序显示员工信息
#使用order by对查询到的数据进行排序操作 升序为ASC，降序为DESC

SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id BETWEEN 100 AND 130
#order by salary asc;
ORDER BY salary DESC;

SELECT employee_id,salary
FROM employees
WHERE department_id IN(50,60,70)
ORDER BY department_id DESC;

#4.二级排序
显示员工信息，按照department_id的降序排列，如果前面相同 二级排序，按照salary的升序排列
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

#分页，mysql使用limit实现分页数据的显示
#每页显示20条记录，显示第一页
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;

#每页显示20条记录，显示第二页
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;

SELECT employee_id,salary
FROM employees
WHERE salary>6000
ORDER BY salary DESC
LIMIT 0,10;

#查询工资表中员工前六名工资最高的员工信息
SELECT employee_id ,last_name,salary
FROM employees
ORDER BY salary DESC
LIMIT 0,6;

第五章排序与分页课后练习

#1.查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序显示
SELECT salary*12 AS’年薪’,last_name,department_id
FROM employees
ORDER BY '年薪’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%’
WHERE email REGEXP’[e]’
ORDER BY LENGTH(email) DESC,department_id ASC;

DESC employees;
SELECT * FROM employees;

DESC departments;
SELECT * FROM departments;

DESC locations;
SELECT * FROM locations;

#查询名字为’Abel’的员工在哪个城市工作
SELECT *
FROM employees
WHERE last_name=‘Abel’;

SELECT *
FROM departments
WHERE department_id=80;

SELECT *
FROM locations
WHERE location_id=2500;