标题: 【石家庄校区】总结Oracle第2天的笔记-Oracled的查询 [打印本页] 作者: 隔壁小白 时间: 2017-12-28 21:12 标题: 【石家庄校区】总结Oracle第2天的笔记-Oracled的查询 *单表查询 *简单条件查询 *精确查询
select * from T_OWNERS where watermeter='30408' *模糊查询
select * from t_owners where name like '%刘%' *and 运算符
select * from t_owners where name like '%刘%' and housenumber like '%5%' *or 运算符
select * from t_owners where name like '%刘%' or housenumber like '%5%' *and 和 or 运算符混合使用(and优先级大于or)
select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=3 *范围查询
select * from T_ACCOUNT where usenum>=10000 and usenum<=20000 *空值查询
select * from T_PRICETABLE t where maxnum is null
select * from T_PRICETABLE t where maxnum is not null *去掉重复记录
select distinct addressid from T_OWNERS *排序查询 *升序排序
select * from T_ACCOUNT order by usenum *降序排序
select * from T_ACCOUNT order by usenum desc *基于伪列的查询
在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。 *ROWID
表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可以显示行是如何存储的.
查询语句：
select rowID,t.* from T_AREA t
select rowID,t.* from T_AREA t where ROWID='AAAM1uAAGAAAAD8AAC'; *ROWNUM
在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数.
查询语句：
select rownum,t.* from T_OWNERTYPE t *聚合统计 *聚合函数 *求和 sum
select sum(usenum) from t_account where year='2012' *求平均值 avg
select avg(usenum) from T_ACCOUNT where year='2012' *求最大值 max
select max(usenum) from T_ACCOUNT where year='2012' *求最小值 min
select min(usenum) from T_ACCOUNT where year='2012' *统计记录个数 count
select count(*) from T_OWNERS t where ownertypeid=1 *分组聚合 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)>169000 *连接查询 *多表内连接查询
select o.id 业主编号,o.name 业主名称,ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot
where o.ownertypeid=ot.id *左外连接查询
*按照 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(+) *右外连接查询
*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 *子查询 *where子句中的子查询 *单行子查询
只返回一条记录
单行操作符 =、<、>、>=、<=、<>(不等于)
语法:
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 运算符
select * from T_OWNERS where addressid in ( 1,3,4 )
select * from T_OWNERS
where addressid (not) in
( select id from t_address where name like '%花园%' ) *from 子句中的子查询
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子句的子查询必须为单行子查询
select id,name,
(select name from t_address where id=addressid) addressname
from t_owners;
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; *分页查询 *简单分页
select rownum,t.* from T_ACCOUNT t where rownum<=10
rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于”符号，只能使用“小于”或“小于等于” ，只用“等于”也不行. *基于排序的分页
1、获取一个排序的结果 select * from t_account order by usenum
2、获取rownum行号 select rownum r,t.* from (select * from t_account order by usenum) t
3、获取分页的结果 select * from (select rownum r,t.* from (select * from t_account order by usenum) t) where r>10 and r<20 *分页的三种方式: 1、简单分页（基于排序的分页）:
select * from ( select rownum r,t.* from (select * from t_account order by usenum) t) where r>10 and r<=20 2、基于分析函数的分页
select * from (select row_number() over(order by usenum) r,t.* from t_account t) where r>10 and r<=20 3、基于集合运算的分页（差集）
select rownum,t.* from t_account t where rownum < 20
minus
select rownum,t.* from t_account t where rownum < 10 *单行函数 *字符函数 *求字符串长度 lenth
select length('ABCD') from dual; *求字符串的子串 substr(从1开始)
select substr('ABCD',2,2) from dual; *字符串拼接 concat
select concat('ABC','D') from dual;
也可以用|| 对字符串进行拼接
select 'ABC'||'D' from dual; *数值函数 *四舍五入函数 ROUND
select round(100.567) from dual *截取函数 TRUNC
select trunc(100.567) from dual *取模 MOD(取余)
select mod(10,3) 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
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 * 字符串转日期 TO_DATE
语句：
select TO_DATE('2017-01-01','yyyy-mm-dd') from dual *字符串转数字 TO_NUMBER
语句：
select to_number('100') from dual *其他函数 *空值处理函数 NVL
用法：
NVL（检测的值，如果为 null 的值）；
语句：
select NVL(NULL,0) from dual *空值处理函数 NVL2
用法：
NVL2（检测的值，如果不为 null 的值，如果为 null 的值）； *条件取值 decode
语法：
*decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
【功能】根据条件返回相应值
select name,decode( ownertypeid,1,'居民',2,'行政事业单位
',3,'商业') as 类型 from T_OWNERS
*上边的语句也可以用 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 )
from T_OWNERS *行列转换
D:/%E6%9C%89%E9%81%93%E4%BA%91%E7%AC%94%E8%AE%B0/qqBB0481296A56989BF7E01398425AC5B3/7851fb9fb62849d5afaebefbe6fa6013/clipboard.png
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 *分析函数 * 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 *集合运算
集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。
集合运算包括：
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
UNION(并集)，返回各个查询的所有记录，不包括重复记录。
INTERSECT(交集)，返回两个查询共有的记录。
MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。 *并集运算 *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 *交集运算
select * from t_owners where id<=7
intersect
select * from t_owners where id>=5 *差集运算
select * from t_owners where id<=7
minus
select * from t_owners where id>=5