单表查询
create table testtable(
Tname nvarchar2(10),
Tage number(2)
);
commit;
insert into testtable values('哈哈',32);
insert into testtable values('嘿嘿',12);
insert into testtable values('嘻嘻',21);
insert into testtable values('佳佳',32);
insert into testtable values('哈哈',32);
insert into testtable values('嘿嘿',12);
insert into testtable values('哈哈',32);
insert into testtable values('嘿嘿',12);
insert into testtable values('嘻嘻',21);
insert into testtable values('qqqq',32);
insert into testtable values('adcxzdf',32);
insert into testtable values('safdfsfa',32);
insert into testtable values('adfafgsdf',32);
insert into testtable values('qerf',32);
commit;
------------------------------
--重复数据中查找唯一行
select distinct * from testtable;
--删除重复行
delete testtable where rowid not in (
select max(rowid) from testtable group by tname);
--分页
select * from (
select rownum r,t.* from (select * from testtable)t where rownum<=15
)where r>10;
--由结果集建表
create table temp_testtable as
select * from testtable where 条件;
--逻辑运算符or
select * from(
select rownum r,t.* from(select * from testtable)t
) where r=7 or r=10;
--逻辑运算符and
select * from(
select rownum r,t.* from(select * from testtable)t
) where r>=6 and r<=12;
--字符串连接||
select tname||'_'||tage from testtable;
select '姓名:'||tname||' '||'年龄:'||tage from testtable;
--like模糊查询 % _
select * from testtable where tname like '%';
select * from testtable where tname like '%a%';
select * from testtable where tname like '%a';
select * from testtable where tname like 'a%';
select * from testtable where tname like '___f';
select * from testtable where tname like '%f_';
--in子句
select * from testtable where tage in(32,21);
--null
select * from testtable where tage is not null;
--between...and
select * from testtable where tage between 12 and 20;
select * from testtable where tage>=12 and tage<20;
select * from testtable where tage>12 and tage<20;
select * from testtable where tage>12 and tage<40 and tname='qerf';
------------------------------
select * from testtable;
select rownum,tname from testtable;
select rowid,tname,tage from testtable group by rowid,tname,tage;
select * from testtable order by tage asc;
select min(rowid) from testtable;
delete testtable where rowid not in(
select min(rowid) from testtable
group by tname
);