Oracle(二)

发表于 | 更新于 | 分类于 | 阅读次数：
| 本文字数： 12k

我有整个宇宙想讲给你听，张嘴却吐不出半粒星辰。

方拾贰-<山下>

以下结合基础篇建表语句所建的表查询。

一.单表查询

1.简单条件查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
1.精确查询:
--查询水表编号为 30408 的业主记录
select * from T_OWNERS where WATERMETER='30408';
2.模糊查询:
--查询业主名称包含“刘”的业主记录
select * from t_owners where name like '%刘%';
3. and 运算符:
--查询业主名称包含“刘”的并且门牌号包含 5 的业主记录
select * from t_owners where name like '%刘%' and housenumber like '%5%';
4. or 运算符:
--查询业主名称包含“刘”的或者门牌号包含 5 的业主记录
select * from t_owners where name like '%刘%' or housenumber like '%5%';
5.and 与 or 运算符混合使用:
--查询业主名称包含“刘”的或者门牌号包含 5 的业主记录，并且地址编号为 3 的记录。
select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=3;
6. 范围查询:
--查询台账记录中用水字数大于等于 10000，并且小于等于 20000 的记录
select * from t_account where usenum >= 10000 and usenum <= 20000;
select * from t_account where usenum between 10000 and 20000;
7. 空值查询:
--查询 T_PRICETABLE 表中 MAXNUM 为空的记录
select * from t_pricetable where maxnum is null;
--查询 T_PRICETABLE 表中 MAXNUM 不为空的记录
select * from t_pricetable where maxnum is not null;
2.去掉重复记录及排序
1
2
3
4
5
6
--查询业主表中的地址 ID,不重复显示
select distinct addressid from t_owners;
--对 T_ACCOUNT 表按使用量进行升序排序
select * from t_account order by usenum;
--对 T_ACCOUNT 表按使用量进行降序排序
select * from t_account order by usenum desc;
3.基于伪列的查询
1
2
3
4
--ROWID返回的就是该行的物理地址,可以快速的定位表中的某一行.
select rowID,t.* from t_area t;
--ROWNUM可以限制查询结果集中返回的行数,用于分页.
select rownum,t.* from t_ownertype t;
4.聚合函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--1.求和 sum:
--统计 2012 年所有用户的用水量总和
select sum(usenum) from t_account where year='2012';
--2.求平均 avg:
--统计 2012 年所有用水量（字数）的平均值
select avg(usenum) from t_account where year='2012';
--3.求最大值 max:
--统计 2012 年最高用水量（字数）
select max(usenum) from t_account where year='2012';
--4.求最小值 min:
--统计 2012 年最低用水量（字数）
select min(usenum) from t_account where year='2012';
--5.统计记录个数 count:
--统计业主类型 ID 为 1 的业主数量
select count(*) from t_owners where ownertypeid=1;
--6.分组聚合 Group by
--按区域分组统计水费合计数
select areaid,sum(money) from t_account group by areaid;
--7.分组后条件查询 having
--查询水费合计大于 169000 的区域及水费合计
select areaid,sum(money) from t_account group by areaid having sum(money)>169000;

二.多表查询

内连接: inner join (inner可以省略)
​ 显示内连接:在sql中显示的调用 inner join关键字.
​ select from 表1 inner join 表2 on 关联条件;
​ 例: SELECT FROM classes c INNER JOIN student s ON c.cid=s.cid;
​ 隐式内连接: 在sql中没有调用 inner join关键字.
​ select * from 表1,表2 where 关联条件;(以下均采用此种)

外连接: outer join (outer可以省略)
​ 左外连接:
​ select from 表1 left outer join 表2 on 关联条件;
​ 右外连接:
​ select from 表1 right outer join 表2 on 关联条件;

1.多表内连接查询
1
2
3
4
5
6
7
8
9
10
11
12
13
--查询显示业主编号，业主名称，业主类型名称(两表连接)
select ow.id 业主编号,ow.name 业主名称,ot.name 业主类型名称
from t_owners ow,t_ownertype ot where ow.ownertypeid=ot.id; 
--查询显示业主编号，业主名称、地址和业主类型(三表连接)
select ow.id 业主编号,ow.name 业主名称,ad.name 地址,ot.name 业主类型名称 from t_owners ow,t_ownertype ot,t_address ad
where ow.ownertypeid=ot.id and ow.addressid=ad.id;
--查询显示业主编号、业主名称、地址、所属区域、业主分类(四表连接)
select ow.id 业主编号,ow.name 业主名称,ad.name 地址,ae.name 所属区域,ot.name 业主类型名称
from t_owners ow,t_ownertype ot,t_address ad,t_area ae
where ow.ownertypeid=ot.id and ow.addressid=ad.id and ad.areaid=ae.id; 
--查询显示业主编号、业主名称、地址、所属区域、收费员、业主分类(五表连接)
select ow.id 业主编号,ow.name 业主名称,ad.name 地址,ae.name 所属区域,op.name 收费员,ot.name 业主类型名称 from t_owners ow,t_ownertype ot,t_address ad,t_area ae,t_operator op
where ow.ownertypeid=ot.id and ow.addressid=ad.id and ad.areaid=ae.id and ad.operatorid=op.id;
2.左外连接查询
1
2
3
4
5
6
7
--查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名。
--sql标准:
select ow.id,ow.name,ac.year ,ac.month,ac.money
from t_owners ow left join t_account ac on ow.id=ac.owneruuid;
--oracle标准:
select ow.id,ow.name,ac.year ,ac.month,ac.money
from t_owners ow ,t_account ac where ow.id=ac.owneruuid(+);
3.右外连接查询
1
2
3
4
5
6
7
--查询业主的账务记录，显示业主编号、名称、年、月、金额,如果账务记录没有对应的业主信息，也要列出记录.
--sql标准:
select ow.id,ow.name,ac.year ,ac.month,ac.money
from t_owners ow right join t_account ac on ow.id=ac.owneruuid;
--oracle标准:
select ow.id,ow.name,ac.year ,ac.month,ac.money
from t_owners ow ,t_account ac where ow.id(+)=ac.owneruuid;
4.子查询
–where 子句中的子查询
1
2
3
4
5
6
7
8
9
10
11
12
13
单行子查询:只返回一条记录,使用单行操作符(>,<,=...)
--查询 2012 年 1 月用水量大于平均值的台账记录
select * from t_account where year='2012' and month='01'and usenum>
(select avg(usenum) from t_account where year='2012' and month='01');
多行子查询:返回了多条记录,使用多行操作符(in,any,all)
--in 等于列表中的任何一个
--any 和子查询返回的任意一个值做比较
--all 和子查询返回的所有值做比较
--查询地址编号为 1 、3、4 的业主记录
select * from t_owners where addressid in(1,3,4);
--查询地址含有“花园”的业主的信息
select * from t_owners where addressid in
(select id from t_address where name like '%花园%');
–from 子句中的子查询
1
2
3
4
5
--(为多行子查询)
--查询显示业主编号，业主名称，业主类型名称，条件为业主类型为”居民”
select * from
(select o.id 业主编号,o.name 业主名称,ot.name 业主类型名称 from t_owners o,t_ownertype ot where o.ownertypeid=ot.id)
where 业主类型名称='居民';
–select子句的子查询
1
2
3
4
5
6
7
8
9
--(为单行子查询)
--列出业主信息，包括 ID，名称，所属地址
select id,name,(select name from t_address where id=addressid) addressname from t_owners;
--列出业主信息，包括 ID，名称，所属地址，所属区域
select id,name,
( select name from t_address where id=addressid )addressname,
( select (select name from t_area where id=areaid ) from
t_address where id=addressid )adrename
from t_owners;

三.分页查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
简单分页
--分页查询台账表 T_ACCOUNT，每页 10 条记录
select rownum,t.* from t_account t where rownum<=10;
--显示第 11 条到第 20 条的记录
select * from(
select rownum r,t.* from t_account t where rownum<=20)
where r>10;
=====================================
select * from
(select rownum r, t.* from t_account t)
where r>10 and r<=20;

--分页查询台账表 T_ACCOUNT，每页 10 条记录，按使用字数降序排序
select * from 
(select rownum r,t.* from 
    (select * from t_account order by usenum desc) t
        where rownum<=20)
 where r>10;    
=====================================
select * from
(select rownum r, t.* from (select * from t_account order by usenum desc) t) 
where r>10 and r<=20;

四.单行函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
--求字符串长度 LENGTH
select length('ABCD') from dual;
--求字符串的子串 SUBSTR （源字符串,从第几位截取,截取字符串）
select substr('ABCD',2,2) from dual;
--字符串拼接 CONCAT
select concat('ABC','D') from dual;
--字符串拼接2
select 'ABC'||'D' from dual;

--数值函数
--四舍五入函数 ROUND
select round(100.567) from dual; --101
select round(100.567,2) from dual;--保留两位小数 100.57
--截取函数 TRUNC
select trunc(100.567) from dual; --100
select trunc(100.567,2) from dual; --保留两位小数 100.56
--取模 MOD
select mod(10,3) from dual;

--日期函数
--获取当前日期和时间
select sysdate from dual;
--加月函数 ADD_MONTHS ：在当前日期基础上加指定的月,减月-2;
select add_months(sysdate,2) from dual;
--求所在月最后一天 LAST_DAY
select last_day(sysdate) from dual;
--日期截取 TRUNC
select TRUNC(sysdate) from dual; --把时间截掉
--截取年
select TRUNC(sysdate,'yyyy') from dual;
--截取月
select TRUNC(sysdate,'mm') from dual;

--转换函数
--数字转字符串 TO_CHAR
select TO_CHAR(1024) from dual;
--日期转字符串 TO_CHAR
select TO_CHAR(sysdate,'yyyy-mm-dd') from dual;
select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
--字符串转日期 TO_DATE
select TO_DATE('2017-01-01','yyyy-mm-dd') from dual;
--字符串转数字 TO_NUMBER
select to_number('100') from dual;
–其他函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
--其他函数
--空值处理函数 NVL
select NVL(NULL,0) from dual;
--显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,则显示 9999999
select PRICE,MINNUM,NVL(MAXNUM,9999999)
from T_PRICETABLE where OWNERTYPEID=1;
--空值处理函数 NVL2(null,0,100) 如果为null,显示100,不为null,显示0
--显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不限”
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限 ')
from T_PRICETABLE where OWNERTYPEID=1;
--条件取值 decode
--语法
decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
【功能】 根据条件返回相应值.
--显示下列信息（不要关联查询业主类型表，直接判断 1 2 3 的值）
select name,decode( ownertypeid,1,' 居 民 ',2,' 行 政 事 业 单 位
',3,'商业') as 类型 from T_OWNERS;
--或者
select name ,(case ownertypeid
    when 1 then '居民'
    when 2 then '行政事业单位'
    when 3 then '商业'
    else '其它'
end
) from T_OWNERS;
--或者
select name,(case
    when ownertypeid= 1 then '居民'
    when ownertypeid= 2 then '行政事业'
    when ownertypeid= 3 then '商业'
end );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

select last_day(sysdate) from dual;
--日期截取 TRUNC
select TRUNC(sysdate) from dual; --把时间截掉
--截取年
select TRUNC(sysdate,'yyyy') from dual;
--截取月
select TRUNC(sysdate,'mm') from dual;

--转换函数
--数字转字符串 TO_CHAR
select TO_CHAR(1024) from dual;
--日期转字符串 TO_CHAR
select TO_CHAR(sysdate,'yyyy-mm-dd') from dual;
select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
--字符串转日期 TO_DATE
select TO_DATE('2017-01-01','yyyy-mm-dd') from dual;
--字符串转数字 TO_NUMBER
select to_number('100') from dual;


--其他函数
--空值处理函数 NVL
select NVL(NULL,0) from dual;
--显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,则显示 9999999
select PRICE,MINNUM,NVL(MAXNUM,9999999)
from T_PRICETABLE where OWNERTYPEID=1;
--空值处理函数 NVL2(null,0,100) 如果为null,显示100,不为null,显示0
--显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不限”
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限 ')
from T_PRICETABLE where OWNERTYPEID=1;
--条件取值 decode
--语法
decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
【功能】 根据条件返回相应值.
--显示下列信息（不要关联查询业主类型表，直接判断 1 2 3 的值）
select name,decode( ownertypeid,1,' 居 民 ',2,' 行 政 事 业 单 位
',3,'商业') as 类型 from T_OWNERS;
--或者
select name ,(case ownertypeid
    when 1 then '居民'
    when 2 then '行政事业单位'
    when 3 then '商业'
    else '其它'
end
) from T_OWNERS;
--或者
select name,(case
    when ownertypeid= 1 then '居民'
    when ownertypeid= 2 then '行政事业'
    when ownertypeid= 3 then '商业'
end );

五.行列转换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select (select name from T_AREA where id= areaid ) 区域,
    sum( case when month='01' then money else 0 end) 一月,
    sum( case when month='02' then money else 0 end) 二月,
    sum( case when month='03' then money else 0 end) 三月,
    sum( case when month='04' then money else 0 end) 四月,
    sum( case when month='05' then money else 0 end) 五月,
    sum( case when month='06' then money else 0 end) 六月,
    sum( case when month='07' then money else 0 end) 七月,
    sum( case when month='08' then money else 0 end) 八月,
    sum( case when month='09' then money else 0 end) 九月,
    sum( case when month='10' then money else 0 end) 十月,
    sum( case when month='11' then money else 0 end) 十一月,
    sum( case when month='12' then money else 0 end) 十二月
from T_ACCOUNT where year='2012' group by areaid;

1
2
3
4
5
6
7
8
9
10
select (select name from T_AREA where id= areaid ) 区域,
sum( case when month>='01' and month<='03' then money else
0 end) 第一季度,
sum( case when month>='04' and month<='06' then money else
0 end) 第二季度,
sum( case when month>='07' and month<='09' then money else
0 end) 第三季度,
sum( case when month>='10' and month<='12' then money else
0 end) 第四季度
from T_ACCOUNT where year='2012' group by areaid;

六.分析函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--1. RANK 相同的值排名相同，排名跳跃
--对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳跃
select rank() over(order by usenum desc ),usenum from T_ACCOUNT;

--2. DENSE_RANK 相同的值排名相同，排名连续
--对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名连续
select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT;

--3. ROW_NUMBER 返回连续的排名，无论值是否相等
--对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是否相等
select row_number() over(order by usenum desc ),usenum from T_ACCOUNT;

--row_number()分析函数实现的分页
select * from
(select row_number() over(order by usenum desc )
rownumber,usenum from T_ACCOUNT)
where rownumber>10 and rownumber<=20;

七.集合运算

集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。

集合运算包括：

​ UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

​ UNION(并集)，返回各个查询的所有记录，不包括重复记录。

​ INTERSECT(交集)，返回两个查询共有的记录。

​ MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之
后剩余的记录.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
--并集运算
--UNION ALL 不去掉重复记录
select * from t_owners where id<=7
union all
select * from t_owners where id>=5;
--UNION 去掉重复记录
select * from t_owners where id<=7
union
select * from t_owners where id>=5;


--交集运算
--intersect 运算符返回同时属于两个集合的记录
select * from t_owners where id<=7
intersect
select * from t_owners where id>=5;


--差集运算
--minus 返回属于第一个集合,但不属于第二个集合的记录
select * from t_owners where id<=7
minus
select * from t_owners where id>=5;

--minus 运算符来实现分页
select rownum,t.* from T_ACCOUNT t where rownum<=20
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10;
-------------本文结束感谢您的阅读-------------