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米段'));
