8：分页查询
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
SELECT 查询列表
FROM 表名
【JOIN type JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序的字段】
LIMIT要显示条目的起始索引（起始索引从0开始），长度（要显示的条目个数）
特点：
1.LIMIT 语句放在查询语句的最后
2.上述代码的执行顺序为：
1)FROM 表名
2)JOIN type JOIN 表2
3)ON 连接条件
4)WHERE 筛选条件
5)GROUP BY 分组字段
6)HAVING 分组后的筛选
7)SELECT 查询列表
8)ORDER BY 排序的字段
9)LIMIT要显示条目的起始索引（起始索引从0开始），长度（要显示的条目个数）
3.公式
要显示的页数page , 每页的条目数size
SELECT 查询列表
from 表名
LIMIT (page-1)*size,size;
page第n页的起始索引1 0210320
# 【案例1】 查询前五条员工信息SELECT * FROM employees LIMIT 0,5;# 默认起始索引从0开始，可省略不写SELECT * FROM employees LIMIT 5;# 【案例2】 查询第11条到第25条员工信息SELECT * FROM employees LIMIT 10,15;# 【案例3】 有奖金的员工信息，并且工资较高的前10名显示出来SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;# 【练习1】 查询所有学员的邮箱的用户名SELECT SUBSTR(email,1,INSTR(email,'@')-1) 用户名FROM stuinfo# 【练习2】 查询男生和女生的个数SELECT count(*) 个数 FROM stuinfoGROUP BY sex;# 【练习3】 查询年龄 > 18岁的所有学生的姓名和年级名称SELECT name, gradeNameFROM stuinfo sINNER JOIN grade gon s.gradeId = g.idWHERE age >18;# 【练习4】 查询哪个年级的最小年龄大于20岁# 1) 查询每个年级的最小年龄SELECT MIN(age),gradeidFROM stuinfoGROUP BY gradeId;# 2) 1)的查询结果中大于20岁的SELECT MIN(age),gradeidFROM stuinfoGROUP BY gradeIdHAVING MIN(age) > 20;
子查询复习：
#1.查询工资最低的员工信息：last_name，salary# 1) 查询最低工资SELECT MIN(salary)FROM employees;USE myemployees;# 2) 查询last_name，salary，要求last_name = 1)中的查询结果SELECT last_name , salaryFROM employeesWHERE salary = (SELECT MIN(salary)FROM employees);#2.查询平均工资最低的部门信息# 方法一：# 1)查询各部门的平均工资SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id;# 2) 查询 1)结果上的最低平均工资SELECT MIN(ag),department_idFROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id ) ag_dep# 3) 查询哪个部门的平均工资 = 2）查询出来的结果SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_idHAVING AVG(salary) = (SELECT MIN(ag)FROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id ) ag_dep);# 4) 查询部门编号 = 3)的查询结果中的部门编号的 部门信息SELECT d.*FROM departments dWHERE d.department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) = (SELECT MIN(ag)FROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id ) ag_dep));# 方法二：# 1)查询各部门的平均工资SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id;# 2) 在 1) 的基础上升序排列，取第一条记录，就能得到平均工资最低的部门的编号SELECT department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) ASCLIMIT 0,1;# 3)查询部门信息SELECT *FROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) ASCLIMIT 0,1);#3.查询平均工资最低的部门信息和该部门的平均工资# 1)查询各部门的平均工资SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id;# 2) 在 1) 的基础上升序排列，取第一条记录，就能得到平均工资最低的部门的编号SELECT AVG(salary) , department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) ASCLIMIT 0,1;# 3)查询部门信息(内连接)SELECT d.* , agFROM departments dINNER JOIN (SELECT AVG(salary) ag, department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) ASCLIMIT 0,1)ag_depON d.department_id = ag_dep.department_id;#4.查询平均工资最高的job信息# 1)查询各部门的平均工资SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id;# 2) 在 1) 的基础上降序排列，取第一条记录，就能得到平均工资最低的工种的编号SELECT AVG(salary) , job_idFROM employeesGROUP BY job_idORDER BY AVG(salary) DESCLIMIT 0,1;# 3) 在2)的基础上，筛选出 job_id = 2)查询结果中的job_idSELECT *FROM jobsWHERE job_id = (SELECT job_idFROM employeesGROUP BY job_idORDER BY AVG(salary) DESCLIMIT 0,1);#5.查询平均工资高于公司平均工资的部门有哪些？# 1) 查询各个部门的平均工资SELECT AVG(salary),department_idFROM employeesGROUP BY department_id;# 2) 查询整个公司的平均工资SELECT AVG(salary)FROM employees;# 3) 筛选 2)的结果集，满足平均工资 > 1)SELECT AVG(salary),department_idFROM employeesGROUP BY department_idHAVING AVG(salary) > (SELECT AVG(salary)FROM employees);#6.查询出公司中所有manager的详细信息.# 1) 查询出所有的manager_idSELECT DISTINCT manager_idFROM employees;# 2) 查询详细信息，满足employee_id = 1)中查询的结果# 方法一SELECT *FROM employeesWHERE employee_id = ANY(SELECT DISTINCT manager_idFROM employees);# 方法二SELECT *FROM employeesWHERE employee_id in(SELECT DISTINCT manager_idFROM employees);#7.查询各个部门的最高工资中最低的 那个部门的 最低工资是多少# 1) 查询各个部门的最高工资 中，最低的那个部门的最高工资SELECT MAX(salary),department_idFROM employeesGROUP BY department_idORDER BY MAX(salary) ASCLIMIT 0,1;# 2) 查询 1) 结果的那个部门的最低工资SELECT MIN(salary)FROM employeesWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idORDER BY MAX(salary) ASCLIMIT 0,1);#8.查询平均工资最高的部门的manager的详细信息：last_name，department_id，email，salary# 1) 查询各个部门的平均工资SELECT AVG(salary),department_idFROM employeesGROUP BY department_id;# 2) 在 1)的查询结果中，查找平均工资最高的部门的department_idSELECT AVG(salary),department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) DESCLIMIT 0,1;# 3) 将employees 和 departments连接查询，筛选条件是 1）SELECT last_name,d.department_id,email,salaryFROM employees eINNER JOIN departments dON e.employee_id = d.manager_idWHERE e.department_id = (SELECT department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) DESCLIMIT 0,1);
强化练习：
-- 一、查询每个专业的学生人数SELECT majorid , COUNT(*)FROM studentGROUP BY majorid;-- 二、查询参加考试的学生中，每个学生的平均分、最高分SELECT AVG(score) , MAX(score) , studentnoFROM resultGROUP BY studentno;-- 三、查询姓张的每个学生的最低分大于60的学号、姓名SELECT MIN(score),s.studentname,s.studentnoFROM student sINNER JOIN result rON s.studentno = r.studentnoWHERE studentname LIKE'张%'GROUP BY studentnoHAVING MIN(score) > 60;-- 四、查询专业生日在“1988-1-1”后的学生姓名、专业名称SELECT s.studentname , s.majorid, m.majorname,s.borndateFROM student sINNER JOIN major mON s.majorid = m.majoridWHERE DATEDIFF(borndate,'1988-1-1')>0;-- 五、查询每个专业的男生人数和女生人数分别是多少# 方法一SELECT COUNT(*) 个数, sex , majoridFROM studentGROUP BY sex , majorid# 方法二SELECT majorid, (SELECT COUNT(*) FROM student WHERE sex='男' AND majorid=s.majorid) 男 ,(SELECT COUNT(*) FROM student WHERE sex='女' AND majorid=s.majorid)女FROM student sGROUP BY majorid;-- 六、查询专业和张翠山一样的学生的最低分# 1) 查询张翠山的专业编号SELECT majoridFROM studentWHERE studentname = '张翠山';# 2) 查询专业编号 = 1)的所有学生的编号SELECT studentnoFROM studentWHERE majorid = (SELECT majoridFROM studentWHERE studentname = '张翠山');# 3) 查询最低分SELECT MIN(score)FROM resultWHERE studentno in (SELECT studentnoFROM studentWHERE majorid = (SELECT majoridFROM studentWHERE studentname = '张翠山'));-- 七、查询大于60分的学生的姓名、密码、专业名SELECT s.studentname , s.studentno , r.score , loginpwd , majorname FROM student sINNER JOIN result rON s.studentno = r.studentnoINNER JOIN major mON s.majorid = m.majoridWHERE score > 60;-- 八、按邮箱位数分组，查询每组的学生个数SELECT COUNT(*) , LENGTH(email)FROM studentGROUP BY LENGTH(email);-- 九、查询学生名、专业名、分数SELECT studentname , m.majorid , majorname , scoreFROM student sleft JOIN result rON s.studentno = r.studentnoINNER JOIN major mON s.majorid = m.majorid;-- 十、查询哪个专业没有学生，分别用左连接和右连接实现# 左连接SELECT m.majorid , m.majorname , s.studentnoFROM major mLEFT JOIN student sON m.majorid = s.majoridWHERE studentno IS NULL;# 右连接SELECT m.majorid , m.majorname , s.studentnoFROM student sRIGHT JOIN major mON m.majorid = s.majoridWHERE studentno IS NULL;-- 十一、查询没有成绩的学生人数SELECT COUNT(*) 无成绩学生个数FROM student sLEFT JOIN result rON s.studentno = r.studentnoWHERE r.id IS NULL;
