mysql 查询第几行到第几行记录查询最后一行和第一行记录查询前几行和后几行记录（查询正序或者倒序前几行）

古城微笑少年丶 2022-01-30 06:21 254阅读 0赞

如果查询正序或者倒序前几行查询就这样写

select * from shangpinxinxi  ORDER BY id asc limit 0,5

如果在limit（0，5）加入where条件

select * from shangpinxinxi where  tupian<>'' limit 0,5

如果在加上查询

select * from shangpinxinxi  where  tupian<>''  ORDER BY id asc limit 0,5

重要部分

if (curpage==1)
    				{
    				sql="select  * from liuyanban limit 0,"+page_record;
    				}
    				else
    				{
    				sql="select   * from liuyanban limit "+page_record+","+(curpage-1)*page_record;
    				}
    				
    				  sql=sql+" "

**myql分页查询**

<%@ page language="java"  pageEncoding="gb2312"%>
    <%
    String path = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
    %>
    <%@ page language="java" import="java.sql.*" %>
    <jsp:useBean id="connDbBean" scope="page" class="db.db"/>
    <html>
      <head>
        <base href="<%=basePath%>">
        
        <title>XXXX企业门户网</title><LINK 
    href="qtimages/style.css" type=text/css rel=stylesheet>
    <style type="text/css">
    
    </style>
    
      </head>
      <body >
      <table width="992" border="0" align="center" cellpadding="0" cellspacing="0">
      <tr>
        <td colspan="2"><%@ include file="qttop.jsp"%>
    	</td>
      </tr>
      <tr>
        <td width="163" valign="top" ><%@ include file="qtleft.jsp"%></td>
        <td width="813" valign="top"><table width="98%" border="0" align="center" cellpadding="0" cellspacing="0">
          <tbody>
            <tr>
              <td height="44" background="qtimages/index_r4_c8.jpg"><table width="100%" border="0" cellpadding="0" cellspacing="0">
                <tr>
                  <td width="9%"> </td>
                  <td width="76%" class="red STYLE2">在线留言</td>
                  <td width="15%" ><a href="lyb.jsp"><font class="red STYLE2">我要留言</font></a></td>
                </tr>
              </table></td>
            </tr>
            <tr>
              <td class="boder" height="182"><style type="text/css">
    
                          </style>
                <%
        int curpage=1;//当前页
    				int page_record=3;//每页显示的记录数
    				int zgs=0;
    				int zys=0;
    				//用下面的方法（sql查询完成，速度快）
    				String hsgnpage=request.getParameter("page");
    				String fysql="select count(id) as ss from liuyanban";
    				ResultSet RS_resultfy=connDbBean.executeQuery(fysql);
      while(RS_resultfy.next()){
      zgs=Integer.parseInt(RS_resultfy.getString("ss"));
      zys=zgs/page_record+1;
      }
    				if (hsgnpage!=null)
    				{
    				curpage=Integer.parseInt(request.getParameter("page"));//获取传递的值，需要显示的页
    				}
    				else
    				{
    				curpage=1;
    				}
    				if (curpage==0)
    				{
    					curpage=1;
    				}
    				if(curpage>zys)
    				{
    					curpage=zys;
    				}
    				//out.print(curpage);
    				String sql;
    				if (curpage==1)
    				{
    				sql="select  * from liuyanban limit 0,"+page_record;
    				}
    				else
    				{
    				sql="select   * from liuyanban limit "+page_record+","+(curpage-1)*page_record;
    				}
    				
    				  sql=sql+" ";
    				  
    
    
      ResultSet RS_result=connDbBean.executeQuery(sql);
      String id="";
     String nicheng="";String xingbie="";String QQ="";String youxiang="";String dianhua="";String neirong="";String huifuneirong="";
     String addtime="";
     int i=0;
     
     
     
     
     
     while(RS_result.next()){
     i=i+1;
     id=RS_result.getString("id");
    nicheng=RS_result.getString("cheng");xingbie=RS_result.getString("xingbie");QQ=RS_result.getString("QQ");youxiang=RS_result.getString("youxiang");dianhua=RS_result.getString("dianhua");neirong=RS_result.getString("neirong");huifuneirong=RS_result.getString("huifuneirong");
     addtime=RS_result.getString("addtime");
     
     
    %>
                <table width="95%" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#009900" class="tb">
                  
                  <tr>
                    <td width="85" rowspan="4" align="center" valign="middle" bgcolor="#FFFFFF"><img width='70'height='70' src=img/<%=xingbie %>.gif border=0> </td>
                    <td height="20" align="left" valign="middle" bgcolor="#FFFFFF">    留言于:<%=addtime%>  </td>
                  </tr>
                  <tr>
                    <td height="78" align="left" valign="top" bgcolor="#FFFFFF"> <%=neirong%></td>
                  </tr>
                  <tr>
                    <td align="left" valign="middle" bgcolor="#FFFFFF" style="height: 25px">   昵称：<%=nicheng%>    电话：<%=dianhua %>  邮 箱:<%=youxiang%>   QQ:<%=QQ%>    </td>
                  </tr>
                  <tr>
                    <td align="left" valign="middle" bgcolor="#FFFFFF" style="height: 25px">   管理员回复：<%=huifuneirong%></td>
                  </tr>
                </table>
                <%}%>
                <p align="center">  共<%=zgs%>条记录  <%=page_record %>条/页　<a href="lyblist.jsp?page=1">首页</a>　<a href="lyblist.jsp?page=<%= curpage-1%>">上一页</a>　<A 
                            
                            href="lyblist.jsp?page=<%= curpage+1%>">下一页</A>　<a href="lyblist.jsp?page=<%=zys %>">尾页</A>　当前第<FONT 
                            color=red><%=curpage %></FONT>页/共<FONT 
                            color=red><%=zys %></FONT>页</p></td>
            </tr>
            <tr>
              <td valign="top" height="9"><img 
                      src="qtimages/index_r19_c8.jpg" width="320" /> </td>
            </tr>
            
          </tbody>
        </table></td>
      </tr>
    </table>
    <%@ include file="qtdown.jsp"%>
      </body>
    </html>

1、查询第一行记录：

select \* from table limit 1

2、查询第n行到第m行记录  
select \* from table1 limit n-1,m-n;

SELECT \* FROM table LIMIT 5,10；返回第6行到第15行的记录

select \* from employee limit 3,1; // 返回第4行

3、查询前n行记录

select \* from table1 limit 0,n;

或

select \* from table1 limit n;

4、查询后n行记录

select \* from table1 order by id desc dlimit n；//倒序排序，取前n行 id为自增形式

5、查询一条记录($id)的下一条记录