Oracle基础教程（三）数据库的查询

三 数据库的查询

3.1 单表查询

• 精确查询

select * from 表名 where 条件

• 模糊查询

select * from 表名 where name like '%条件%'

• and运算符

select * from 表名 where 字段 like '%条件%' and 字段
like '%条件%

• or 运算

select * from 表名 where 字段 like '%条件%' or 字段
like '%条件%

注意：因为 and 的优先级比 or 大，所以我们需要用 ( ) 来改变优先级。

• 范围查询

// <= >=
select * from T_ACCOUNT
where usenum>=10000 and usenum<=2000
//betwween ..and
select * from T_ACCOUNT
where usenum between 10000 and 20000

• 空值查询

select * from T_PRICETABLE t where maxnum is null

• 去掉重复

select distinct addressid from T_OWNERS

• 升序查询

select * from T_ACCOUNT order by usenum asc

• 降序查询

select * from T_ACCOUNT order by usenum desc

• 伪列

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就 像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。 接下来学习两个伪列：ROWID和 ROWNUM

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

select rowID,t.* from T_AREA 

ROWNUM:在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号。

select rownum,t.* from T_OWNERTYP

• 聚合统计

  • sum:求和
  • avg:平均值
  • max:最大值
  • min:最小值
  • count:统计

• 分组聚合 Group by

select areaid,sum(money) from t_account group by areaid

• ​ 分组后条件查询 having

select areaid,sum(money) from t_account group by areaid
having sum(money)>16900

3.3 连接查询

• 内连接

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

• 左外连接

SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM
T_OWNERS ow,T_ACCOUNT ac
WHERE ow.id=ac.owneruuid(+)

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

• 右外连接

select ow.id,ow.name,ac.year,ac.month,ac.money from
T_OWNERS ow , T_ACCOUNT ac
where ow.id(+) =ac.owneruui

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

3.3 子查询

• 单个条件嵌套查询

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

• 多行子查询

select * from T_OWNERS
where addressid in ( 1,3,4 )

• from 子句中的子查询

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

• select 子句中的子查询

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

3.4 分页查询

• 简单分页

我们在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询

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

• 接下来10-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 rownum<=20 order
by usenum desc)
where r>10

3.5 单行函数

• 字符函数

  • 求字符串长度 LENGT

  select length('ABCD') from dual
  

  • 截取字符串的 SUBST

  select substr('ABCD',2,2) from dual
  

  • 字符串拼接 CONCAT

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

• 数值函数

  • 四舍五入函数ROUND

  select round(100.567) from dual
  

  • 截取函数 TRUNC

  select round(100.567,2) from dual
  

  • 取模 MOD

  select mod(10,3) from dual
  

• 日期函数

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

  select sysdate from dual
  

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

  select add_months(sysdate,2) from dual
  

  • 求所在月最后一天LAST_DAY

  select last_day(sysdate) from dual
  

  • 日期截取TRUNC

  select TRUNC(sysdate) from dual
  

• 转换函数

  • 数字转字符串 TO_CHAR

  select TO_CHAR(1024) from dual
  

  • 日期转字符串TO_CHAR

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

  • 字符串转日期 TO_DATE

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

  • 字符串转数字 TO_NUMBE

  select to_number('100') from dual
  

• 其他函数

  • 空值处理函数 NVL

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

  select NVL(NULL,0) from dual
  

  • 空值处理函数NVL2

    NVL2（检测的值，如果不为 null 的值，如果为 null 的值）

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

  • 条件取值 decode

  decode(条件,值 1,翻译值 1,值 2,翻译值 2,…值 n,翻译值 n,缺省值) 【功能】根据条件返回相应值

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

3.6 行列转换

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 areai

3.7 分析函数

• RANK 相同的值排名相同，排名跳跃

select rank() over(order by usenum desc ),usenum from T_ACCOUNT

• DENSE_RANK 相同的值排名相同，排名连续

select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT

• ROW_NUMBER 返回连续的排名，无论值是否相等

select row_number() over(order by usenum desc ),usenum from T_ACCOUNT

3.8 集合运算

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

• 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