Oracle 查询

2022-01-22 10:36:58

2 Oracle 查询

单表查询
连接查询
子查询
分页查询
单行函数
行列转换
分析函数
集合运算
总结

1 单表查询

简单条件查询

select * from T_OWNERS where watermeter='30408'

模糊查询

select * from t_owners where name like '%刘%'

and 和 or 运算符

select * from t_owners
where name like '%刘%' and housenumber like '%5%'

select * from t_owners
where name like '%刘%' or housenumber like '%5%'

- and 的优先级比or高

between … and

select * from T_ACCOUNT
where usenum between 10000 and 20000

空值查询

-- 查询为空的
select * from T_PRICETABLE t where maxnum is null
--  查询非空的
select * from T_PRICETABLE t where maxnum is not null

去重 DISTINCT

select distinct addressid from T_OWNERS

排序查询默认升序

select * from T_ACCOUNT order by usenum

--    降序查询
select * from T_ACCOUNT order by usenum desc

基于伪列的查询

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就

像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。

1 ROWID

表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的

物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的

标识表中的一行

select rowID,t.* from T_AREA t

2 ROWNUM

在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，

第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行

数

select rownum,t.* from T_OWNERTYPE t

2 连接查询

内连接
左外连接
右外连接

1 内连接

需求：查询显示业主编号，业主名称，业主类型名称

查询语句

select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id

2 左外连接查询

需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主

没有账务记录也要列出姓名

第一种 SQL1999 标准写法

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 右外连接查询

需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记

录没有对应的业主信息，也要列出记录

第一种 SQL1999 标准写法

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

如果是右外连接，就在左表所在的条件一端填上(+)

3 子查询

where 子句中的子查询
from 子句中的子查询
select 子句中子查询

where 子句中的子查询

1 单行子查询

2 单行操作符

select * from T_ACCOUNT
where year='2012' and month='01' and usenum>
( select avg(usenum) from T_ACCOUNT where year='2012' and  month='01' )

2 多行子查询

多行操作符

in 运算符

（1）需求：查询地址含有“花园”的业主的信息

select * from T_OWNERS
where addressid not in
( select id from t_address where name like '%花园%' )

any 运算符

any表示有任何一个满足就返回true

（2）需求查询出01班中，年龄大于 02班任意一个的同学

select * from student where 班级=’01’ and age > any (select age from student where 班级=’02’);

all 运算符

2）需求查询出01班中，年龄大于 02班所有的同学

select * from student where 班级=’01’ and age > all (select age from student where 班级=’02’);

2 from 子句中的子查询

需求：查询显示业主编号，业主名称，业主类型名称，条件为业主类型为”居民”，

使用子查询实现

select * from  
(select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id)
where 业主类型='居民'

即把from 后的子查询当做一个结果集

3 Select 子句中子查询

select 子句的子查询必须为单行子查询

需求：列出业主信息，包括 ID，名称，所属地址。

select id,name,
(select name from t_address where id=addressid) addressname
from t_owners

4 分页查询根据ROWNUM

简单分页

需求1 ：分页查询台账表 T_ACCOUNT，前 10 条记录

select rownum,t.* from T_ACCOUNT t where rownum<=10

需求2 ：分页查询台账表 T_ACCOUNT第10-20条记录

select * from
(select rownum r,t.* from T_ACCOUNT t where rownum<=20)
where r>10

不要使用rownum>10 and rownum<=20 因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于”符号，只能使用“小于”或“小于等于” ，只用“等于”也不行rownum 10后面的还没产生rownum 值，但是却使用它去判断了

基于排序的分页

ROWNUM 伪列的产生是在表记录扫描是先产生的，而排序是后进行的

因此应该先排序，在分页

需求：分页查询台账表 T_ACCOUNT，第10-20条记录，按使用字数降序排序

select * from
(select rownum r,t.* from
(select * from T_ACCOUNT order by usenum desc) t
where rownum<=20 )
where r>10

4 单行函数

字符函数
数值函数
日期函数
转换函数
其他函数

1 字符函数

常用的字符函数

（1）求字符串长度 LENGTH

select length('ABCD') from dual;

(2 ) 求字符串的子串 SUBSTR 注意索引从1开始

// 从2开始截取两个
select substr('ABCD',2,2) from dual;

结果BC

(3) 字符串拼接 CONCAT

select concat('ABC','D') from dual;

结果ABCD

也可以用|| 对字符串进行拼接

select 'ABC'||'D' from dual;

完整的字符函数

2 数值函数

常用的数值函数

（1）四舍五入函数 ROUND

select round(100.567) from dual
-- 结果101
select round(100.567,2) from dual
-- 结果100.57

（2）截取函数 TRUNC

select trunc(100.567) from dual
-- 结果100

select trunc(100.567,2) from dual
-- 结果100.56

(3) 取模

select mod(10,3) from dual

结果1

完整的数值函数

3 日期函数

常用的日期函数

sysdate 这个系统变量来获取当前日期和时间

1 加月函数 ADD_MONTHS ：在当前日期基础上加指定的月

select add_months(sysdate,2) from dual

2 求所在月最后一天 LAST_DAY

select last_day(sysdate) from dual

3 日期截取

select TRUNC(sysdate) from dual
-- 2021-05-27

-- 截取月份，获取月份的第一天
select TRUNC(sysdate,'mm') from dual
-- 2021-05-01

-- 截取月份，获取年的第一天
select TRUNC(sysdate,'yy') from dual
-- 2021-01-01

完整的日期函数

4 转换函数

常用的转换函数

（1）数字转字符串 TO_CHAR

select TO_CHAR(1024) from dual

（2）日期转字符串 TO_CHAR

select TO_CHAR(sysdate,'yyyy-mm-dd') from dual

select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

(3) 字符串转日期 TO_DATE

select TO_DATE('2017-01-01','yyyy-mm-dd') from dual

4）字符串转数字 TO_NUMBER

select to_number('100') from dual

完整的转换函数

5 其他转换函数

（1）空值处理函数 NVL

NVL（检测的值，如果为 null 的值）；

select NVL(NULL,0) from dual

(2 ) NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；

select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限') from T_PRICETABLE where OWNERTYPEID=1

（3）条件取值 decode

decode(条件,值 1,翻译值 1,值 2,翻译值 2,…值 n,翻译值 n,缺省值)

【功能】根据条件返回相应值

select name,decode( ownertypeid,1,' 居 民 ',2,' 行 政 事 业 单 位 ',3,'商业') as 类型 from T_OWNERS

(4 ) case when then 语句

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 )

5 行列转换

case when 在 select 子句中的使用

需求：按月份统计 2012 年上半年各个地区的水费

select (select name from t_area where id=areaid)  区域 , sum(case when month='01' then money else 0 end) 一月

, 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) 六月
from t_account where ownertype=1 group by areaid;

6 分析函数

用途一用于排名，排名的三种方式

第一种类排序

值相同，排名相同，序号跳跃

--可以先这么写select  t.* from t_account t order by usenum 再转为下面的形式
select rank() over(order by usenum) 排名, t.* from t_account t

在这里插入图片描述

值相同排名相同，序号连续

--可以先这么写select  t.* from t_account t order by usenum 再转为下面的形式
select dense_rank() over(order by usenum) 排名, t.* from t_account t

序号连续不管值是否相同`

--可以先这么写select  t.* from t_account t order by usenum 再转为下面的形式
select row_number() over(order by usenum) 排名, t.* from t_account t

基于row_number 实现分页查询


select * from (select row_number() over(order by usenum) num ,t.* from t_account t) where num>10 and num<20

6 集合运算

并集运算
交集运算
差集运算

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

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

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

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

·MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之

后剩余的记录

1 并集运算

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

2 交集运算

select * from t_owners where id<=7 intersect select * from t_owners where id>=5

3 差集运算

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

码农公寓