--------------------查询---------------------------
--精确查询--
select*from t_owners where watermeter =‘30408‘
--模糊查询--
select*from t_owners where name like ‘%刘%‘
--条件并列查询--
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--
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_account where usenum between 10000 and 20000
--空值查询--
select*from t_pricetable where maxnum is null
select*from t_pricetable 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
--伪列 rowid(物理地址)--
select rowid,t.* from t_owners t where rowid=‘AAASNXAAGAAAACnAAD‘
--伪列 rownum--
select rownum,t.* from t_owners t where rowid=‘AAASNXAAGAAAACnAAD‘
--聚合统计--
--求和--
select sum(usenum) from t_account where year=‘2012‘
--求平均--
select avg(usenum) from t_account where year=‘2012‘
--求最大值--
select max(usenum) from t_account where year=‘2012‘
--求最小值--
select min(usenum) from t_account where year=‘2012‘
--统计个数--
select count(*) from t_owners where ownertypeid=1
--分组聚合统计（select后面只能跟分组聚合条件或者聚合函数）--
select areaid,sum(money) from t_account group by areaid
--分组后条件查询--
select areaid,sum(money) from t_account group by areaid having sum(money)>169000
--多表连接查询--
--内连接--
--两表查询--
select ow.id 业主编号,ow.name 业主名称,ot.name 业主类型
from t_owners ow,t_ownertype ot
where ow.ownertypeid=ot.id
--三表查询--
select ow.id 业主编号,ow.name 业主名称,ot.name 业主类型,ad.name 地址,ar.name 区域
from t_owners ow,t_ownertype ot,t_address ad，t_area ar
where ow.ownertypeid=ot.id and ow.addressid=ad.id and ad.areaid=ar.id
--外连接--
--左外连接（账务没有记录的人也要列出来，以左边为主）--
--SQL1999标准写法--
select ow.id,ow.name,year,month,money from t_owners ow right join t_account ac
where ow.id=ac.owneruuid
--ORACLE的语法--
select ow.id,ow.name,year,month,money from t_owners ow,t_account ac
where ow.id=ac.owneruuid(+)
--右外连接（账务记录的在owner表里没有也要列出来，以右边为主）--
select ow.id,ow.name,year,month,money from t_owners ow,t_account ac
where ow.id(+)=ac.owneruuid
--子查询（嵌套查询）--
--where子句--
--单行子查询（只返回一条记录）--
select avg(usenum) from t_account where year=‘2012‘ and month=‘01‘
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 (select id from t_address where name like ‘%花园%‘)
select*from t_owners where addressid not in (select id from t_address where name like ‘%花园%‘)
--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
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) areaname
from t_owners
--分页查询--
--简单分页--
select rownum,t.* from t_account t where rownum<=10
select*from(select rownum r,t.* from t_account t)
where r<=20 and r>10
--基于排序的分页--
select*from(select rownum r,t.* from (select*from t_account order by usenum desc) t)
where r<=20 and r>10
--------------------函数---------------------------
--单行函数--
--字符函数--
--LENGTH（字符串长度） dual伪表--
select length(‘ABCD‘) from dual
--求字符串子串（原字符串，从第几位截取，截取字符数）--
select substr(‘ABCD‘,2,2) from dual
--字符串拼接--
select concat(concat(‘ABC‘,‘D‘),‘EF‘) from dual
select ‘ABC‘||‘D‘||‘EF‘ from dual
--数值函数--
--四舍五入--
select round(100.556) from dual
select round(100.556,2) from dual
--数字截取--
select trunc(100.567) from dual
select trunc(100.567,2) from dual
--取余数--
select mod(10,3) from dual
--日期函数--
--加月函数--
select add_months(sysdate,-2) from dual
--求所在月的最后一天--
select last_day(sysdate) from dual
--日期截取--
select sysdate from dual
select trunc(sysdate) from dual
select trunc(sysdate,‘mm‘) from dual
select trunc(sysdate,‘hh‘) from dual
--转换函数--
--数字转字符串--
select to_char(100) from dual
select 100||‘‘ from dual
--日期转字符串--
select to_char(sysdate,‘yyyy-mm-dd‘) from dual
--字符串转日期--
select to_date(‘2016-03-10‘,‘yyyy-mm-dd‘) from dual
--字符串转数字--
select to_number(‘100‘) from dual
--其他函数--
--空值处理函数(当值为null时自动转为其他值)--
select nvl(maxnum,9999999) from t_pricetable where ownertypeid=1
select nvl2(maxnum,to_char(maxnum),‘不限‘) from t_pricetable where ownertypeid=1
--条件取值--
select decode(100,1,2,3,4,100,200) from dual
select name,decode(ownertypeid,1,‘居民‘,2,‘行政事业单位‘,3,‘商业‘,‘其他‘) from t_owners
--sql1999--
select name,(case ownertypeid
when 1 then ‘居民‘
when 2 then ‘行政事业‘
when 3 then ‘商业‘
else ‘其他‘
end) from t_owners
--行列转换--
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) 七月
from t_account where year=‘2012‘ group by areaid
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
--分析函数--
--值相同，排名相同，序号跳跃--
select rank() over(order by usenum desc) 排名,t.* from t_account t
--值相同，排名相同，序号连续--
select dense_rank() over(order by usenum desc) 排名,t.* from t_account t
--序号连续,不管值是否相同--
select row_number() over(order by usenum desc) 排名,t.* from t_account t
--row_number实现分页查询--
select*from
(select row_number() over(order by usenum desc) rownumber,t.* from t_account t)
where rownumber<=20 and rownumber>10
--------------------集合---------------------------
--集合运算--
--并集 union all(包括重复)--
select*from t_owners where id>5
union
select*from t_owners where id<8
--交集--
select*from t_owners where id>5
intersect
select*from t_owners where id<8
--差集--
select*from t_owners where id>5
minus
select*from t_owners where id<8
--用差集左分页--
select rownum,t.* from t_account t where rownum<=20
minus
select rownum,t.* from t_account t where rownum<=10
原文：https://www.cnblogs.com/fddd/p/14702604.html