删除重复记录
delete from acc a1
where rowid != (select max(rowid)
from acc a2
where a1.name = a2.name
and a1.age = a2.age)
分页
select a2.*
from (select a1.*, rownum rn from acc a1) a2
where a2.rn > 1
and rownum <= 2
Rownum 与 rowid 的区别
select * from acc where rownum > 0 // 查出全部结果
select * from acc where rownum >2 //查出0条记录
向数据库中插数据
1) 创建表:
CREATE TABLE acc
(
Id NUMBER(8) PRIMARY KEY ,
Name VARCHAR2(30),
Age NUMBER(3)
)
2) 编写.cvs 文件 c:\oraclexe\acc.cvs
8,赵二,89
9,不要脸,29
3) 编写.ctl文件 c:\oraclexe\acc.ctl
load data
infile 'C:\oraclexe\acc.cvs'
insert into table acc
fields terminated by ','
(id,name,age)
4) 运行
cmd>sqlldr userid=X5/123456 control=c:\oraclexe\acc.ctl
delete from acc a1
where rowid != (select max(rowid)
from acc a2
where a1.name = a2.name
and a1.age = a2.age)
分页
select a2.*
from (select a1.*, rownum rn from acc a1) a2
where a2.rn > 1
and rownum <= 2
Rownum 与 rowid 的区别
select * from acc where rownum > 0 // 查出全部结果
select * from acc where rownum >2 //查出0条记录
向数据库中插数据
1) 创建表:
CREATE TABLE acc
(
Id NUMBER(8) PRIMARY KEY ,
Name VARCHAR2(30),
Age NUMBER(3)
)
2) 编写.cvs 文件 c:\oraclexe\acc.cvs
8,赵二,89
9,不要脸,29
3) 编写.ctl文件 c:\oraclexe\acc.ctl
load data
infile 'C:\oraclexe\acc.cvs'
insert into table acc
fields terminated by ','
(id,name,age)
4) 运行
cmd>sqlldr userid=X5/123456 control=c:\oraclexe\acc.ctl