Oracle语句练习
1. 查询
(1.1).单表查询，去掉重复记录，排序查询，基于伪列的查询，聚合统计，多表内连接查询，左外连接查询,⼦查询
--(-)单表查询
--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 t where maxnum is null;
--需求：查询 T_PRICETABLE 表中 MAXNUM 不为空的记录
select * from T_PRICETABLE t where maxnum is not null;
--（⼆）去掉重复记录
--需求：查询业主表中的地址 ID，不重复显⽰
select distinct addressid from t_owners;
-
-（三）排序查询
--1.升序排序需求：对 T_ACCOUNT 表按使⽤量进⾏升序排序
select * from T_ACCOUNT order by usenum;
--2.降序排序需求：对 T_ACCOUNT
select * from T_ACCOUNT order by usenum desc;
--（四）基于伪列的查询--伪列只能查询，不能进⾏增删改操作
--1.ROWID
select rowID,t.* from T_AREA t;
--指定ROWID来查询记录
select rowID,t.* from T_AREA t where ROWID='AAGBTWAAKAAKqx9AAA';
--2.ROWNUM和rowid
select rownum,rowid,id,name from T_OWNERTYPE;
--（五）聚合统计
--1. 聚合函数
--求和 sum 需求：统计 2012 年所有⽤户的⽤⽔量总和
select sum(usenum) from t_account where year='2012';
--求平均 avg 统计 2012 年所有⽤⽔量（字数）的平均值
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';
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 查询⽔费合计⼤于 16900 的区域及⽔费合计
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;
--需求：查询显⽰业主编号，业主名称、地址和业主类型
select o.id 业主编号,o.name 业主名称,ad.name 地址, ot.name 业主类型
from T_OWNERS o,T_OWNERTYPE ot,T_ADDRESS ad
where o.ownertypeid=ot.id and o.addressid=ad.id;
--需求：查询显⽰业主编号、业主名称、地址、所属区域、业主分类
select o.id 业主编号,o.name 业主名称,ar.name 区域, ad.name 地址, ot.name 业主类型
from T_OWNERS o ,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA ar
where o.ownertypeid=ot.id and o.addressid=ad.id and ad.areaid=ar.id;
-
-（⼆）左外连接查询 --得截图
--需求：查询业主的账务记录，显⽰业主编号、名称、年、⽉、⾦额。如果此业主没有账务记录也要列出姓名SELECT T_OWNERS.id,T_OWNERS.name,ar,h, FROM T_OWNERS left join T_ACCOUNT
on T_OWNERS.id=T_ACCOUNT.owneruuid;
--连表查询
SELECT T_OWNERS.id,T_OWNERS.name,ar,h, FROM T_OWNERS ,T_ACCOUNT
where T_OWNERS.id=T_ACCOUNT.owneruuid;
--三、⼦查询
--（⼀）where ⼦句中的⼦查询
-- 单⾏⼦查询需求：查询 2012 年 1 ⽉⽤⽔量⼤于平均值的台账记录
oracle四舍五入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
--需求：查询地址编号为 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 '%花园%');
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 ⼦句中的⼦查询
--需求：列出业主信息，包括 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;
--四、分页查询
-
-（⼀）简单分页
select rownum r,t.* from T_ACCOUNT t where rownum<=10;
--查询结果为null因为 rownum 是在查询语句扫描每条记录时产⽣的，所以不能使⽤“⼤于” 符号，
--只能使⽤“⼩于”或“⼩于等于” ，只⽤“等于”也不⾏。
select rownum,t.* from T_ACCOUNT t where rownum>10 and rownum<=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
select rownum r,t.* from T_ACCOUNT t
where rownum<=20 order by usenum desc)
where r>10;
select * from (
select rownum r,t.* from (
select * from T_ACCOUNT order by usenum desc) t
where rownum<=20 ) where r>10;
--五、单⾏函数
--求字符串长度 LENGTH
select length('ABCD') from dual;
--求字符串的⼦串 SUBSTR
select substr('ABCD',2,2) from dual;
-
-字符串拼接 CONCAT
select concat('ABC','D') from dual;
--使⽤||拼接
select 'ABC'||'D' from dual;
--（⼆）数值函数
select round(100.567) from dual;
--四舍五⼊保留⽽⼆位
select round(100.567,2) from dual;
--截取函数 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 ：在当前⽇期基础上加指定的⽉
select add_months(sysdate,2) from dual;
--求所在⽉最后⼀天 LAST_DAY
select last_day(sysdate) from dual;
select TO_CHAR(1024) from dual;
select TO_CHAR(sysdate,'yyyy-mm-dd') from dual;
select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
-
-TO_NUMBER
select to_number('100') from dual;
--（五）其它函数
--空值处理函数 NVL
select NVL(NULL,0) from dual;
--NVL2（检测的值，如果不为 null 的值，如果为 null 的值）
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')
from T_PRICETABLE where OWNERTYPEID=1;
--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;
--六、集合运算
--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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。