8：分頁查詢

應(yīng)用場景：當(dāng)要顯示的數(shù)據(jù)，一頁顯示不全，需要分頁提交sql請求
語法：
SELECT 查詢列表
FROM 表名
【JOIN type JOIN 表2
ON 連接條件
WHERE 篩選條件
GROUP BY 分組字段
HAVING 分組后的篩選
ORDER BY 排序的字段】
LIMIT 要顯示條目的起始索引（起始索引從0開始），長度（要顯示的條目個數(shù)）

特點：
1.LIMIT 語句放在查詢語句的最后
2.上述代碼的執(zhí)行順序為：
1)FROM 表名
2)JOIN type JOIN 表2
3)ON 連接條件
4)WHERE 篩選條件
5)GROUP BY 分組字段
6)HAVING 分組后的篩選
7)SELECT 查詢列表
8)ORDER BY 排序的字段
9)LIMIT 要顯示條目的起始索引（起始索引從0開始），長度（要顯示的條目個數(shù)）

3.公式
要顯示的頁數(shù)page , 每頁的條目數(shù)size
SELECT 查詢列表
from 表名
LIMIT (page-1)*size,size;

page 第n頁的起始索引1 02 103 20 # 【案例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;# 【練習(xí)1】 查詢所有學(xué)員的郵箱的用戶名 SELECT SUBSTR(email,1,INSTR(email,'@')-1) 用戶名 FROM stuinfo# 【練習(xí)2】 查詢男生和女生的個數(shù) SELECT count(*) 個數(shù) FROM stuinfo GROUP BY sex;# 【練習(xí)3】 查詢年齡 > 18歲的所有學(xué)生的姓名和年級名稱 SELECT name, gradeName FROM stuinfo s INNER JOIN grade g on s.gradeId = g.id WHERE age >18;# 【練習(xí)4】 查詢哪個年級的最小年齡大于20歲 # 1) 查詢每個年級的最小年齡 SELECT MIN(age),gradeid FROM stuinfo GROUP BY gradeId;# 2) 1)的查詢結(jié)果中大于20歲的 SELECT MIN(age),gradeid FROM stuinfo GROUP BY gradeId HAVING MIN(age) > 20;

子查詢復(fù)習(xí)：

#1.查詢工資最低的員工信息：last_name，salary# 1) 查詢最低工資 SELECT MIN(salary) FROM employees;USE myemployees; # 2) 查詢last_name，salary，要求last_name = 1)中的查詢結(jié)果 SELECT last_name , salary FROM employees WHERE salary = (SELECT MIN(salary)FROM employees );#2.查詢平均工資最低的部門信息# 方法一： # 1)查詢各部門的平均工資 SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id;# 2) 查詢 1)結(jié)果上的最低平均工資 SELECT MIN(ag),department_id FROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id ) ag_dep# 3) 查詢哪個部門的平均工資 = 2）查詢出來的結(jié)果 SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id HAVING AVG(salary) = (SELECT MIN(ag)FROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id ) ag_dep );# 4) 查詢部門編號 = 3)的查詢結(jié)果中的部門編號的 部門信息 SELECT d.* FROM departments d WHERE 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_id FROM employees GROUP BY department_id;# 2) 在 1) 的基礎(chǔ)上升序排列，取第一條記錄，就能得到平均工資最低的部門的編號 SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) ASC LIMIT 0,1;# 3)查詢部門信息 SELECT * FROM departments WHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) ASCLIMIT 0,1 );#3.查詢平均工資最低的部門信息和該部門的平均工資 # 1)查詢各部門的平均工資 SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id;# 2) 在 1) 的基礎(chǔ)上升序排列，取第一條記錄，就能得到平均工資最低的部門的編號 SELECT AVG(salary) , department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) ASC LIMIT 0,1;# 3)查詢部門信息(內(nèi)連接) SELECT d.* , ag FROM departments d INNER JOIN (SELECT AVG(salary) ag, department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) ASCLIMIT 0,1 )ag_dep ON d.department_id = ag_dep.department_id;#4.查詢平均工資最高的job信息 # 1)查詢各部門的平均工資 SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id;# 2) 在 1) 的基礎(chǔ)上降序排列，取第一條記錄，就能得到平均工資最低的工種的編號 SELECT AVG(salary) , job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 0,1;# 3) 在2)的基礎(chǔ)上，篩選出 job_id = 2)查詢結(jié)果中的job_id SELECT * FROM jobs WHERE job_id = (SELECT job_idFROM employeesGROUP BY job_idORDER BY AVG(salary) DESCLIMIT 0,1 );#5.查詢平均工資高于公司平均工資的部門有哪些？# 1) 查詢各個部門的平均工資 SELECT AVG(salary),department_id FROM employees GROUP BY department_id;# 2) 查詢整個公司的平均工資 SELECT AVG(salary) FROM employees;# 3) 篩選 2)的結(jié)果集，滿足平均工資 > 1) SELECT AVG(salary),department_id FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT AVG(salary)FROM employees );#6.查詢出公司中所有manager的詳細信息.# 1) 查詢出所有的manager_id SELECT DISTINCT manager_id FROM employees;# 2) 查詢詳細信息，滿足employee_id = 1)中查詢的結(jié)果 # 方法一 SELECT * FROM employees WHERE employee_id = ANY(SELECT DISTINCT manager_idFROM employees );# 方法二 SELECT * FROM employees WHERE employee_id in(SELECT DISTINCT manager_idFROM employees );#7.查詢各個部門的最高工資中最低的 那個部門的 最低工資是多少 # 1) 查詢各個部門的最高工資 中，最低的那個部門的最高工資 SELECT MAX(salary),department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) ASC LIMIT 0,1;# 2) 查詢 1) 結(jié)果的那個部門的最低工資 SELECT MIN(salary) FROM employees WHERE 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_id FROM employees GROUP BY department_id;# 2) 在 1)的查詢結(jié)果中，查找平均工資最高的部門的department_id SELECT AVG(salary),department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 0,1;# 3) 將employees 和 departments連接查詢，篩選條件是 1） SELECT last_name,d.department_id,email,salary FROM employees e INNER JOIN departments d ON e.employee_id = d.manager_id WHERE e.department_id = (SELECT department_idFROM employeesGROUP BY department_idORDER BY AVG(salary) DESCLIMIT 0,1 );

強化練習(xí)：

-- 一、查詢每個專業(yè)的學(xué)生人數(shù) SELECT majorid , COUNT(*) FROM student GROUP BY majorid;-- 二、查詢參加考試的學(xué)生中，每個學(xué)生的平均分、最高分 SELECT AVG(score) , MAX(score) , studentno FROM result GROUP BY studentno;-- 三、查詢姓張的每個學(xué)生的最低分大于60的學(xué)號、姓名 SELECT MIN(score),s.studentname,s.studentno FROM student s INNER JOIN result r ON s.studentno = r.studentno WHERE studentname LIKE'張%' GROUP BY studentno HAVING MIN(score) > 60;-- 四、查詢專業(yè)生日在“1988-1-1”后的學(xué)生姓名、專業(yè)名稱 SELECT s.studentname , s.majorid, m.majorname,s.borndate FROM student s INNER JOIN major m ON s.majorid = m.majorid WHERE DATEDIFF(borndate,'1988-1-1')>0;-- 五、查詢每個專業(yè)的男生人數(shù)和女生人數(shù)分別是多少 # 方法一 SELECT COUNT(*) 個數(shù), sex , majorid FROM student GROUP 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 s GROUP BY majorid;-- 六、查詢專業(yè)和張翠山一樣的學(xué)生的最低分# 1) 查詢張翠山的專業(yè)編號 SELECT majorid FROM student WHERE studentname = '張翠山';# 2) 查詢專業(yè)編號 = 1)的所有學(xué)生的編號 SELECT studentno FROM student WHERE majorid = (SELECT majoridFROM studentWHERE studentname = '張翠山' ); # 3) 查詢最低分 SELECT MIN(score) FROM result WHERE studentno in (SELECT studentnoFROM studentWHERE majorid = (SELECT majoridFROM studentWHERE studentname = '張翠山') );-- 七、查詢大于60分的學(xué)生的姓名、密碼、專業(yè)名 SELECT s.studentname , s.studentno , r.score , loginpwd , majorname FROM student s INNER JOIN result r ON s.studentno = r.studentno INNER JOIN major m ON s.majorid = m.majorid WHERE score > 60;-- 八、按郵箱位數(shù)分組，查詢每組的學(xué)生個數(shù) SELECT COUNT(*) , LENGTH(email) FROM student GROUP BY LENGTH(email);-- 九、查詢學(xué)生名、專業(yè)名、分數(shù) SELECT studentname , m.majorid , majorname , score FROM student s left JOIN result r ON s.studentno = r.studentno INNER JOIN major m ON s.majorid = m.majorid;-- 十、查詢哪個專業(yè)沒有學(xué)生，分別用左連接和右連接實現(xiàn) # 左連接 SELECT m.majorid , m.majorname , s.studentno FROM major m LEFT JOIN student s ON m.majorid = s.majorid WHERE studentno IS NULL;# 右連接 SELECT m.majorid , m.majorname , s.studentno FROM student s RIGHT JOIN major m ON m.majorid = s.majorid WHERE studentno IS NULL;-- 十一、查詢沒有成績的學(xué)生人數(shù) SELECT COUNT(*) 無成績學(xué)生個數(shù) FROM student s LEFT JOIN result r ON s.studentno = r.studentno WHERE r.id IS NULL;

總結(jié)

以上是生活随笔為你收集整理的四、MySQL分页查询 + 子查询复习 学习笔记 （复习连接查询相关内容 详解）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。