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,'feifei');

 

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('feifei');

 

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('feifei');

 

select * from test;

(2)分页:top N

select top 3 * from test

where id not in

(select top 3 id from test);