【MySQL练习】排序与分页练习
表数据:https://www.cnblogs.com/zhishu/p/16452950.html
1. 查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序显示
SELECT last_name,department_id,salary*12 AS AnnualSalary
FROM employees
ORDER BY AnnualSalary DESC,last_name ASC;
#错误的,将别名用于排序时,不能使用引号,考虑使用下划线
SELECT last_name,department_id,salary*12 AS "Annual Salary"
FROM employees
ORDER BY "Annual Salary" DESC,last_name ASC;
2. 选择工资不在 8000 到 17000 的员工的姓名和工资,按工资降序,显示第21到40位置的数据
分页从0开始
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;
3. 查询邮箱中包含 e 的员工信息,并先按邮箱的字节数降序,再按部门号升序
mysql中length()和char_length()的区别:https://www.cnblogs.com/biehongli/p/12389418.html
SELECT last_name,email,LENGTH(email),department_id
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
SELECT last_name,email,LENGTH(email),department_id
FROM employees
WHERE email REGEXP '[e]'
ORDER BY LENGTH(email) DESC,department_id ASC;