数据查询语言

select * from t_hq_ryxx;

select bianh,xingm from t_hq_ryxx;

 

--as  指定别名

select bianh as 编号, xingm as 姓名 from t_hq_ryxx  t;

select bianh  编号 from t_hq_ryxx

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

 

-- order by 按**排序

--desc  降序   

--asc  升序   省略的时候默认升序

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

select bianh as 编号, t.* from t_hq_ryxx t order by xingb,bumbm;

select bianh as 编号, t.* from t_hq_ryxx t order by xingb desc,bumbm desc;

select bianh,xingb from t_hq_ryxx order by bianh,xingb;

 

--数据为空的放最上  last 为放在最下

select bianh as 编号, t.* from t_hq_ryxx t order by xingb,bumbm nulls first ;

-- 1 指第一列

select * from t_hq_ryxx t order by 1;

--按部门编码+编号的和排序

select * from t_hq_ryxx t order by bumbm || bianh;

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

--去重复

select distinct bumbm from t_hq_ryxx t ;

select distinct bumbm,xingb from t_hq_ryxx t ;

 

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

select * from t_hq_ryxx where bumbm = '103'or bumbm = '102' ;

select * from t_hq_ryxx where (bumbm = '103'or bumbm = '102') and xingb = '2' ;

select * from t_hq_ryxx where bumbm = '103'or bumbm = '102' and xingb = '2' ;

--不可用 gongz = null

select * from t_hq_ryxx where gongz is not null;

select * from t_hq_ryxx where gongz != '0.00';

 

--模糊查询   %通配符   _ 通配一位

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 xingm like'大_王';

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 = '103'or bianh = '101' or bianh = '105' ;

 

-- between and 为内包含

select * from t_hq_ryxx where gongz between 5000 and 10000;

select * from t_hq_ryxx where gongz >= 5000 and gongz <= 10000;

 

--子查询

select * from t_hq_ryxx where bumbm in (select bumbm From t_hq_bm where lianxdh ='10086');

--any  大于最小的 小于最大的(大于或小于子查询中的一个)

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

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

--all 大于最大的  小于最小的（大于或小于子查询的所有值）

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

select * from t_hq_ryxx where gongz < all (select pinjgz from  t_hq_bm);

 

--分组

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

select bumbm,count(1)as 数量 ,avg(gongz) as 平均值,sum(gongz) as 合计 from t_hq_ryxx  group by bumbm;

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

--having  过滤

select bumbm,count(1)as 数量 ,avg(gongz) as 平均值,sum(gongz) as 合计 from t_hq_ryxx group by  bumbm having avg(gongz) > 5000;

 

-- 内连接 --inner 可以省略

select * from t_hq_ryxx t inner join t_hq_bm b on t.bumbm = b.bumbm ;

select * from t_hq_ryxx a,t_hq_ryxx b where a.bumbm=b.bumbm and a.xingb = '1';

select t.*,b.bummc from t_hq_ryxx t, t_hq_bm b where t.bumbm = b.bumbm ;

-- 内连接  inner 可以省略

select * from t_hq_ryxx t inner join t_hq_bm b on t.bumbm = b.bumbm ;

select * from t_hq_ryxx a,t_hq_ryxx b where a.bumbm=b.bumbm and a.xingb = '1';

--左外连接 

select * from t_hq_ryxx t left join t_hq_bm b on t.bumbm = b.bumbm ;

select * from t_hq_ryxx t inner join t_hq_bm b on t.bumbm = b.bumbm(+) ;

--右外连接

select * from t_hq_ryxx t right join t_hq_bm b on t.bumbm = b.bumbm ;

select * from t_hq_ryxx t inner join t_hq_bm b on t.bumbm(+) = b.bumbm ;

--全外连接

select * from t_hq_ryxx t full join t_hq_bm b on t.bumbm = b.bumbm ;