select * from student order by id

drop table student

select * from oracletest

create table student(

idcard varchar2(18),

name varchar2(20),

sex int,

high int,

age int

)

insert into student values ('450326198912241844','罗江超',0,178,null);

insert into student values('411424198912068072','候桂申',0,158,null);

insert into student values('450881199006112350','潘海林',0,177,null);

insert into student values('450111197806062156','韦山',0,175,null);

insert into student values('450103197912150539','廖韵',0,173,null);

insert into student values('450324198809231637','唐文林',0,178,null);

insert into student values('450111197806061234','小明',0,180,null);

commit

select * from student

--年龄最大的人

select * from student where substr(idcard,7,8)=(select min(substr(idcard,7,8)) as birt from student)

select * from student where

截取字符串substr

substr(idcard,7,8) in ('19891224','19780606')

select * from student where idcard like '%19780606%'

以......排序

select rownum,substr(idcard,7,8) as birt,s.*

from student s order by birt asc

-------------------------

select rownum,t.* from (

select substr(idcard,7,8) as birt,s.*

from student s order by birt asc

) t

where rownum=1

union

select rownum,t.* from (

select substr(idcard,7,8) as birt,s.*

from student s order by birt desc

) t

where rownum=1

------------------------

select * from student s for update

commit

delete from student

truncate table student

select

sex 性别,

count(1) 总人数,

min(high) 最矮,

max(high) 最高,

avg(high) 平均

from student s group by sex

这个0是什么呢这时就需要decode函数

别名decode

select

decode(sex,0,'男',1,'女',2,'人妖') 性别,

count(1) 总人数,

min(high) 最矮,

max(high) 最高,

avg(high) 平均

from student s group by sex

select

high,

count(1) 总人数

from student s group by high

--1.5米段,1.6米段,1.7米段,1.8米段 人数

select * from (

select 身高段,count(1) 人数 from

(

select

case

when high>=150 and high<160 then '1.5米段'

when high>=160 and high<170 then '1.6米段'

when high>=170 and high<180 then '1.7米段'

when high>=180 then '1.8米段'

end 身高段

from student s

) tt

group by 身高段

)

pivot(count(人数) for 身高段 in ('1.5米段','1.6米段','1.7米段','1.8米段'));