Oracle DQL查询语言整理

select * from t_hq_ryxx;

select nianl, xingm from t_hq_ryxx;

select nianl as 年龄, xingm as 姓名 from t_hq_ryxx t;

select nianl 年龄 from t_hq_ryxx;

select nianl || xingm as 年龄和姓名 from t_hq_ryxx;

select nianl as hhh,t.* from t_hq_ryxx t order by nianl desc ;--排序

select nianl as hhh,t.* from t_hq_ryxx t order by xingb desc ,bum desc;

select nianl,xingm,bum,xingb from t_hq_ryxx t order by bum desc nulls last,xingb;

select * from t_hq_ryxx order by 3;

select nianl,gongz, (nianl+gongz)as g from t_hq_ryxx order by (gongz||nianl);

--去重复
select distinct bum from t_hq_ryxx;

select distinct bum,xingb from t_hq_ryxx;

select * from t_hq_ryxx;


select * from t_hq_ryxx where xingb = '1 'and bum = '102';

select * from t_hq_ryxx where (bum = '101' or bum = '102') and xingb = '2'and nianl >'40' ;

select * from t_hq_ryxx where bum is not null;

--模糊查询 %表示通配符

select * from t_hq_ryxx where xingm like '三%'

select * from t_hq_ryxx where xingm like '%儿'

select * from t_hq_ryxx where xingm like '%三%'

select * from t_hq_ryxx where gongz <any (select pingjgz from t_hq_bm);

--值得范围
select * from t_hq_ryxx where nianl in ('33','58');

select * from t_hq_ryxx where nianl ='33' or nianl = '58';

--区间范围

select * from t_hq_ryxx where gongz between 40 and 100;

select * from t_hq_ryxx where gongz >=40 and gongz <= 100;

--子查询
select * from t_hq_ryxx where bum in (select bumbm from t_hq_bm where lianxdh = '10086');

--大于最小值，小于最大值

select * from t_hq_ryxx where gongz >any (select pingjgz from t_hq_bm);

--大于最大值，小于最小值


select * from t_hq_ryxx where gongz >all (select pingjgz from t_hq_bm);

--分组 多种形式
select bum from t_hq_ryxx group by bum;

select bum,count(1) as 数量 from t_hq_ryxx group by bum;

select bum,count(1) as 数量,sum (gongz) as 合计 from t_hq_ryxx group by bum;

select bum,count(1) as 数量,avg(gongz) as 平均值 from t_hq_ryxx group by bum;

select nianl, bum,count(1) as 数量,avg(gongz) as 平均值 from t_hq_ryxx group by nianl, bum;

select bum,count(1) as 数量,avg(gongz) as 平均值 from t_hq_ryxx group by bum having avg(gongz)>60;