MySQL数据库分页查询，Oracle数据库分页查询，SqlServer数据库分页

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `pass` varchar(255) NOT NULL,
  `sex` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (\'1\', \'张三1\', \'111\', \'男\', \'21\', \'湖北省十堰市\');
INSERT INTO `student` VALUES (\'2\', \'李四2\', \'123\', \'男\', \'21\', \'上海市静安区\');
INSERT INTO `student` VALUES (\'3\', \'张三3\', \'111\', \'男\', \'21\', \'湖北省十堰市\');
INSERT INTO `student` VALUES (\'4\', \'李四4\', \'123\', \'男\', \'21\', \'上海市静安区\');
INSERT INTO `student` VALUES (\'5\', \'张三5\', \'111\', \'男\', \'21\', \'湖北省十堰市\');
INSERT INTO `student` VALUES (\'6\', \'李四6\', \'123\', \'男\', \'21\', \'上海市静安区\');
INSERT INTO `student` VALUES (\'7\', \'张三7\', \'111\', \'男\', \'21\', \'湖北省十堰市\');
INSERT INTO `student` VALUES (\'8\', \'李四8\', \'123\', \'男\', \'21\', \'上海市静安区\');
INSERT INTO `student` VALUES (\'9\', \'张三9\', \'111\', \'男\', \'21\', \'湖北省十堰市\');
INSERT INTO `student` VALUES (\'10\', \'李四0\', \'123\', \'男\', \'21\', \'上海市静安区\');

一.查询5~10条数据

mysql分页查询：

select * from student limit 5,10;

oracle分页查询：

select * from （select *，rownum rn from student ）where rn between 6 and 10；

sqlserver分页查询：

select top 10 * from student where id not in(select top 5 id from student order by id ) order by id ;

DB2分页查询：

select * from （select *，rownumber() over() as rownum from student ）where rn between 6 and 10；

二.jdbc链接数据库代码

1.jdbc链接mysql

package com.zjl.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbUtils {
    
    private  static  final String URL="jdbc:mysql://localhost:3306/DataBaseName?useUnicode=true&characterEncoding=utf-8";              
    private  static  final String USER="root";
    private  static  final String PASSWORD="password";
    
    static{
          try {  
              Class.forName("com.mysql.jdbc.Driver");                
           } catch (ClassNotFoundException e) {
               e.printStackTrace();
           }
    }
        
    public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    
    //关闭方法
    public  static void close(ResultSet rs, Statement stat, Connection conn) throws SQLException{
        if(rs!=null){
            rs.close();
        }if(stat!=null){
            stat.close();
        }if(conn!=null){
            conn.close();
        }
    }    
    
}    

2.jdbc链接oracle数据库

package com.zjl.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbUtils {
    
    private  static  final String URL="jdbc:oracle:thin:@192.168.0.1:1521:DataBaseName?useUnicode=true&characterEncoding=utf-8";  
        
    private  static  final String USER="root";
    private  static  final String PASSWORD="password";
    
    static{
          try {  
              Class.forName("oracle.jdbc.driver.OracleDriver");                
           } catch (ClassNotFoundException e) {
               e.printStackTrace();
           }
    }
        
    public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
    
    //关闭方法
    public  static void close(ResultSet rs, Statement stat, Connection conn) throws SQLException{
        if(rs!=null){
            rs.close();
        }if(stat!=null){
            stat.close();
        }if(conn!=null){
            conn.close();
        }
    }    
    
}