Oracle常用语句及分页

发表于 | 更新于:
字数统计: 155 | 阅读时长 ≈ 1

创建表：

– Create table
create table MUSIC
(
ID NUMBER not null,
NAME VARCHAR2(32),
SINGER VARCHAR2(16),
AUTHOR VARCHAR2(32)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
– Create/Recreate primary, unique and foreign key constraints
alter table MUSIC
add constraint MUSIC_ID_PK primary key (ID)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

begin
for id in 1..99 loop
insert into music(name,author,singer)values(‘童话世界’||id,’阿田’||id,’阿牛’||id);
end loop;
end;

分页：

–这个速度稍微慢些
select from
(select m.,rownum rn
from (select * from music order by id) m)
where rn between 11 and 20

–最好选择以下方式
select from
(select m.,rownum rn
from (select * from music order by id) m
where rownum<=20)
where rn>=11

0%