Oracle,MySql及SqlServer等数据库的自增和分页

Oracle：

(1)自增：oracle不能够在创建表的时候指定自增关键字，它需要重新创建sequence，然后以“创建键.nextval”来引用。

eg:

create table test

(

id int,

name varchar(20)

);

create sequence t_id increment by 1 start with 1;

insert into test values(t_id.nextval,'liuyi');

insert into test values(t_id.nextval,'linqingxia');

select * from test;

(2)分页：oracle的分页借助于rownum。

eg:

select * from

(select rownum r,test.* from test where rownum<7) t

where t.r>3;

MySql:

(1)自增:auto_increment,设为自增的id字段一定要设为主键。

eg:

create table test

(

id int auto_increment primary key,

name varchar(20)

);

insert into test(name) values('liuyi');

insert into test(name) values('linqingxia');

select * from test;

(2)分页:limit关键字

select * from test

limit 3,3;

SqlServer:

(1)自增:identity关键字

create table test

(

id int identity(1,1),

name varchar(20)

)

insert into test(name) values('liuyi');

insert into test(name) values('linqingxia');

select * from test;

(2)分页:top N

select top 3 * from test

where id not in

(select top 3 id from test);