DQL查询语句内容整理

select * from t_hq_ryxx;

select bianh,xingm from t_hq_ryxx;

--为字段名定义别名
select bianh as 编号,xingm as 姓名 from t_hq_ryxx;

select bianh  编号 from t_hq_ryxx;

select bianh || xingm as 编号和姓名 from t_hq_ryxx;

select bianh as bh, t.* from t_hq_ryxx t order by bianh desc;

--order by 排序,desc降序排序
select bianh as bh, t.* from t_hq_ryxx t order by xingb desc,bum desc;

--nulls last/first的用法,desc或者asc要放在nulls前面
select bianh ,xingm ,bum,xingb from t_hq_ryxx t order by bum desc nulls last,xingb;

--选择排序为8的字段
select * from t_hq_ryxx order by 8;

--由bum和bianh列来定义排序方式
select * from t_hq_ryxx order by bum || bianh;
--相加的字段若有空则结果为空
select * from t_hq_ryxx order by nianl + gongz;

select nianl,gongz, (nianl + gongz) as g from t_hq_ryxx order by (nianl + gongz) desc;
--为字段的和定义别名,并以此为排序定义方式
select nianl,gongz, (nianl + gongz) as g from t_hq_ryxx order by g;


select * from t_hq_ryxx;
--选择bum为102或者103,性别为女的行
select * from t_hq_ryxx where (bum = ‘102‘ or bum = ‘103‘) and xingb = 2;
--选择bum不为空的行
select * from t_hq_ryxx where bum is not null;


--去重复
select distinct bum,xingb from t_hq_ryxx;

--模糊查询 % 通配符 _  通配一位
select * from t_hq_ryxx where xingm like ‘%阿%‘;

select * from t_hq_ryxx where bianh in (‘101‘,‘103‘,‘105‘);

select * from t_hq_ryxx where bianh = ‘101‘ or bianh = ‘103‘ or bianh = ‘105‘;

select * from t_hq_ryxx where gongz between ‘1000‘ and ‘2000‘;

select * from t_hq_ryxx where gongz >= 1000 and gongz <= 2000;

--子查询
select * from t_hq_ryxx where bum in (select bumenbm from t_hq_bm where lianxidh = ‘911‘);

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

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

select bum, count(1) as 数量,avg(gongz) as 平均值,sum(gongz) as 合计 from t_hq_ryxx group by bianh, bum;
--分组基础上过滤
select bum, count(1) as 数量,avg(gongz) as 平均值,sum(gongz) as 合计 from t_hq_ryxx group by bum having avg(gongz) > 2000;

select bum, count(1) as 数量,avg(gongz) as 平均值,sum(gongz) as 合计 from t_hq_ryxx where bum is not null group by bum having avg(gongz) > 2000;