Oracle DQL查询语言整理

             1
            select * from t_hq_ryxx;
 2 3select nianl, xingm from t_hq_ryxx;
 4 5select nianl as 年龄, xingm as 姓名 from t_hq_ryxx t;
 6 7select nianl 年龄 from t_hq_ryxx;
 8 9select nianl || xingm as 年龄和姓名 from t_hq_ryxx;
1011select nianl as hhh,t.* from t_hq_ryxx t order by nianl desc ;--排序
1213select nianl as hhh,t.* from t_hq_ryxx t order by xingb desc ,bum desc;
1415select nianl,xingm,bum,xingb from t_hq_ryxx t order by bum desc nulls last,xingb;
1617select * from t_hq_ryxx order by 3;
1819select nianl,gongz, (nianl+gongz)as g from t_hq_ryxx order by (gongz||nianl);
2021 --去重复
22select distinct bum from t_hq_ryxx;
2324select distinct bum,xingb from t_hq_ryxx;
2526select * from t_hq_ryxx;
272829select * from t_hq_ryxx where xingb = ‘1 ‘and bum = ‘102‘;
3031select * from t_hq_ryxx where (bum = ‘101‘ or bum = ‘102‘) and xingb = ‘2‘and nianl >‘40‘ ;
3233select * from t_hq_ryxx where bum is not null;
3435 --模糊查询 %表示通配符
3637select * from t_hq_ryxx where xingm like ‘三%‘3839select * from t_hq_ryxx where xingm like ‘%儿‘4041select * from t_hq_ryxx where xingm like ‘%三%‘4243select * from t_hq_ryxx where gongz <any (select pingjgz from t_hq_bm);
4445 --值得范围
46select * from t_hq_ryxx where nianl in (‘33‘,‘58‘);
4748select * from t_hq_ryxx where nianl =‘33‘ or nianl = ‘58‘;
4950 --区间范围
5152select * from t_hq_ryxx where gongz between 40 and 100;
5354select * from t_hq_ryxx where gongz >=40 and gongz <= 100;
5556 --子查询
57select * from t_hq_ryxx where bum in (select bumbm from t_hq_bm where lianxdh = ‘10086‘);
5859 --大于最小值，小于最大值
6061select * from t_hq_ryxx where gongz >any (select pingjgz from t_hq_bm);
6263 --大于最大值，小于最小值
646566select * from t_hq_ryxx where gongz >all (select pingjgz from t_hq_bm);
6768 --分组 多种形式
69select bum from t_hq_ryxx group by bum;
7071select bum,count(1) as 数量 from t_hq_ryxx group by bum;
7273select bum,count(1) as 数量,sum (gongz) as 合计 from t_hq_ryxx group by bum;
7475select bum,count(1) as 数量,avg(gongz) as 平均值 from t_hq_ryxx group by bum;
7677select nianl, bum,count(1) as 数量,avg(gongz) as 平均值 from t_hq_ryxx group by nianl, bum;
7879select bum,count(1) as 数量,avg(gongz) as 平均值 from t_hq_ryxx group by bum having avg(gongz)>60;
808182