MySQL查询之分页查询

语法格式SELECT 字段列表 FROM 表LIMIT 偏移量每页条数注意1. 偏移量从默认从0开始偏移量(查询页-1) * 每页条数2. 如果查询的是第一页那么偏移量可以省略直接写为 LIMIT 记录数3. 分页查询是数据库的方言不同数据库有不同的实现MySQL中是LIMIT。1. 数据源createtablestudent(numintnullcomment学号,namevarchar(10)defaultzhangnullcomment姓名,scorefloatnull,sexcharnull,addrvarchar(10)null);INSERTINTOstudent(num,name,score,sex,addr)VALUES(101,zhang,98,男,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(102,han,69,女,西安);INSERTINTOstudent(num,name,score,sex,addr)VALUES(103,hui,72,女,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(104,fang,100,男,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(105,li,88,女,北京);INSERTINTOstudent(num,name,score,sex,addr)VALUES(106,cheng,88,女,北京);INSERTINTOstudent(num,name,score,sex,addr)VALUES(107,zhao,88,中,北京);INSERTINTOstudent(num,name,score,sex,addr)VALUES(121,zhang,88,中,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(2142,zhang,88,中,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(212,zhang,88,中,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(425,zhang,88,中,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(111,zhang,88,中,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(11,zhang,88,中,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(22,zhang,88,中,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(33,zhang,88,中,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(44,zhang,88,中,苏州);INSERTINTOstudent(num,name,score,sex,addr)VALUES(555,zhang,88,中,苏州);mysqlselect*fromstudent;--------------------------------|num|name|score|sex|addr|--------------------------------|101|zhang|98|男|苏州||102|han|69|女|西安||103|hui|72|女|苏州||104|fang|100|男|苏州||105|li|88|女|北京||106|cheng|88|女|北京||107|zhao|88|中|北京||121|zhang|88|中|苏州||2142|zhang|88|中|苏州||212|zhang|88|中|苏州||425|zhang|88|中|苏州||111|zhang|88|中|苏州||11|zhang|88|中|苏州||22|zhang|88|中|苏州||33|zhang|88|中|苏州||44|zhang|88|中|苏州||555|zhang|88|中|苏州|--------------------------------17rowsinset(0.01sec)2. 测试// 查询第一页或者说查询前5条数据 偏移量(1-1)*5 0mysqlselect*fromstudentLIMIT0,5;--------------------------------|num|name|score|sex|addr|--------------------------------|101|zhang|98|男|苏州||102|han|69|女|西安||103|hui|72|女|苏州||104|fang|100|男|苏州||105|li|88|女|北京|--------------------------------5rowsinset(0.00sec)sql// 查询第2页 偏移量(2-1)*5 5mysqlselect*fromstudentLIMIT5,5;--------------------------------|num|name|score|sex|addr|--------------------------------|106|cheng|88|女|北京||107|zhao|88|中|北京||121|zhang|88|中|苏州||2142|zhang|88|中|苏州||212|zhang|88|中|苏州|--------------------------------5rowsinset(0.00sec)// 查询第3页 偏移量(3-1)*5 10mysqlselect*fromstudentLIMIT10,5;--------------------------------|num|name|score|sex|addr|--------------------------------|425|zhang|88|中|苏州||111|zhang|88|中|苏州||11|zhang|88|中|苏州||22|zhang|88|中|苏州||33|zhang|88|中|苏州|--------------------------------5rowsinset(0.00sec)// 查询第4页 偏移量(4-1)*5 15mysqlselect*fromstudentLIMIT15,5;--------------------------------|num|name|score|sex|addr|--------------------------------|44|zhang|88|中|苏州||555|zhang|88|中|苏州|--------------------------------2rowsinset(0.00sec)