Sql Server:分页
示例:
SQL:
select astart,avg(aid) a from air group by astart
select dateadd(yy,1, '2008-9-10')//2009-09-10 00:00:00.000
select len('我们快放假了.') a//7
select lower('Beautiful')//upper
select round(13.4321,2)//13.4300
select round(13.4567,-1)//10.0000
Select dateAdd(dd,-1,getdate())//一天前
sql函数:
select distinct aend from air--distinct 去掉重复值
select getdate()--获取系统的当前时间
select YEAR('2009-09-01') --获取年份
select MONTH('2009-08-15')--获取月份
select DAY('2009-09-12')--获取日期
select right('abcd',3)--从右开始往左截取几个
select left('abcd',3)--从左开始往右截取几个
select datalength('好好学习')--返回字节的个数//8
分页:
--pageSize 每页显示多少
--pageIndex 页码
--i=(@pageIndex-1)*@pageSize
--(1-1)*2=0
--(2-1)*2=2
--(3-1)*2=4
--(4-1)*2=6
-- (页码-1)*每页显示多少=里面top
select top 2--pageSize
* from air where aid not in(
select top 6 --i
aid from air order by aid asc)
order by aid asc
模糊分页:
--pageSize 每页显示多少
--pageIndex 页码
--i=(@pageIndex-1)*@pageSize
--(1-1)*2=0
--(2-1)*2=2
--(3-1)*2=4
--(4-1)*2=6
-- (页码-1)*每页显示多少=里面top
select top 2--pageSize
* from myuser where hid not in(
select top 0 --i
hid from myuser where hname like '%地%'
union
select hid from myuser where hname not like '%地%'
)
order by hid asc
select * from myuser where hname like '%地%'
存储过程分页:
create procedure fy @pageSize int,@pageIndex int
--pageSize 每页显示多少
--pageIndex 页码
as
declare @i int
set @i=(@pageIndex-1)*@pageSize
declare @sql varchar(999)
set @sql='select top '+cast(@pageSize as varchar)+' * from
stuInfo where stuNo not in
(
select top '+cast(@i as varchar)+' stuno from
stuInfo order by stuSeat asc
) order by stuseat asc'
exec(@sql)
exec fy 3,3
-- 显示第几页 每页显示多少条
--第一页 里面的top 0 外面的top 2
--第二页 里面的top 2 外面的top 2
--第三页 里面的top 4 外面的top 2
--第四页 里面的top 6 外面的top 2
--假设传递一个参数(每页显示多少条)
(1-1)*4=0
(2-1)*4=4
(3-1)*3=6
(4-1)*2=6
(页码-1)*每页的条目数=里面的top
java:
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.entity.User;
import com.util.DBhelper;
public class Dao {
public int getMax(String str){
int nn=0;
Connection con=null;
PreparedStatement pre=null;
ResultSet res=null;
try {
con=DBhelper.getCon();
String sql="select count(*) from myuser where hname like '%"+str+"%' " ;
pre=con.prepareStatement(sql);
res=pre.executeQuery();
if (res.next()) {
nn=res.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
DBhelper.myClose(con, pre,res);
}
return nn;
}
public List<User> GetAll(int index,String str){
List<User> ml=new ArrayList<User>();
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
int size=2;
int start=(index-1)*size;
try {
con=DBhelper.getCon();
String sql="select top 2 * from myuser where hid not in(" +
"select hid from myuser where hname not like '%"+str+"%' " +
" union " +
" select top "+start+" hid from myuser where hname like '%"+str+"%')";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
User n=new User();
n.setHid(rs.getInt(1));
n.setHname(rs.getString(2));
ml.add(n);
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
finally{
DBhelper.myClose(con, ps, rs);
}
return ml;
}
}