文章目录
准备工作一、业主类型表（t_onwnertype）二、价格表（t_pricetable）三、区域表（t_area）四、收费员表（t_operator）五、地址表（t_address）六、业主表（t_owners）七、收费台账（t_account） 一、简单查询（单表）1.精确查询2.模糊查询3.and运算符4.or运算符5. and 与 or 运算符混合使用6. 范围查询7. 空值查询 二、去掉重复记录三、排序查询1.升序查询2.降序排序 四、基于伪列的查询1.rowid2.rownum 五、聚合统计1.聚合函数2.分组聚合 Group by3.分组后条件查询 having 二、连接查询（一）多表内连接查询（二）左外连接查询（三）右外连接查询 三、子查询（一）where 子句中的子查询1. 单行子查询2. 多行子查询in运算符 （二）from 子句中的子查询（三）select 子句中的子查询 四、分页查询（一）简单分页（二）基于排序的分页 五、单行函数（一）字符函数（二）数值函数（三）日期函数（四）转换函数（五）其它函数（六）行列转换（七）分析函数八、集合运算（一）什么是集合运算（二）并集运算（三）交集运算（四）差集运算准备工作
以黑马程序员Oracle教程为例，需要准备七张表，表结构及关系如下（Oracle数据库不区分大小写，最终都会转换为大写）：
一、业主类型表（t_onwnertype）
二、价格表（t_pricetable）
三、区域表（t_area）
四、收费员表（t_operator）
五、地址表（t_address）
六、业主表（t_owners）
七、收费台账（t_account）
七张表的关系如下：
大家自己可以使用DDL语言自己按照表结构创建表，或者使用下面代码创建，同时插入数据:
注意：大家在创建表之前，要先创建表空间、在表空间中创建用户、给用户赋予权限、然后登录用户，在用户中创建表。
--建立价格区间表create table t_pricetable(id number primary key,price number(10,2),ownertypeid number,minnum number,maxnum number);--业主类型create table t_ownertype(id number primary key,name varchar2(30));--业主表create table t_owners(id number primary key,name varchar2(30),addressid number,housenumber varchar2(30),watermeter varchar2(30),adddate date,ownertypeid number);--区域表create table t_area(id number,name varchar2(30));--收费员表create table t_operator(id number,name varchar2(30));--地址表create table t_address(id number primary key,name varchar2(100),areaid number,operatorid number);--账务表--create table t_account (id number primary key,owneruuid number,ownertype number,areaid number,year char(4),month char(2),num0 number,num1 number,usenum number,meteruser number,meterdate date,money number(10,2),isfee char(1),feedate date,feeuser number);create sequence seq_account;--业主类型insert into t_ownertype values(1,'居民');insert into t_ownertype values(2,'行政事业单位');insert into t_ownertype values(3,'商业');--地址信息--insert into t_address values( 1,'明兴花园',1,1);insert into t_address values( 2,'鑫源秋墅',1,1);insert into t_address values( 3,'华龙苑南里小区',2,2);insert into t_address values( 4,'河畔花园',2,2);insert into t_address values( 5,'霍营',2,2);insert into t_address values( 6,'回龙观东大街',3,2);insert into t_address values( 7,'西二旗',3,2);--业主信息insert into t_owners values(1,'范冰',1,'1-1','30406',to_date('-04-12','yyyy-MM-dd'),1 );insert into t_owners values(2,'王强',1,'1-2','30407',to_date('-02-14','yyyy-MM-dd'),1 );insert into t_owners values(3,'马腾',1,'1-3','30408',to_date('-03-18','yyyy-MM-dd'),1 );insert into t_owners values(4,'林小玲',2,'2-4','30409',to_date('-06-15','yyyy-MM-dd'),1 );insert into t_owners values(5,'刘华',2,'2-5','30410',to_date('-09-11','yyyy-MM-dd'),1 );insert into t_owners values(6,'刘东',2,'2-2','30411',to_date('-09-11','yyyy-MM-dd'),1 );insert into t_owners values(7,'周健',3,'2-5','30433',to_date('-09-11','yyyy-MM-dd'),1 );insert into t_owners values(8,'张哲',4,'2-2','30455',to_date('-09-11','yyyy-MM-dd'),1 );insert into t_owners values(9,'昌平区中西医结合医院',5,'2-2','30422',to_date('-10-11','yyyy-MM-dd'),2 );insert into t_owners values(10,'美廉美超市',5,'4-2','30423',to_date('-10-12','yyyy-MM-dd'),3 );--操作员insert into t_operator values(1,'马小云');insert into t_operator values(2,'李翠花');--地区--insert into t_area values(1,'海淀');insert into t_area values(2,'昌平');insert into t_area values(3,'西城');insert into t_area values(4,'东城');insert into t_area values(5,'朝阳');insert into t_area values(6,'玄武');--价格表--insert into t_pricetable values(1,2.45,1,0,5);insert into t_pricetable values(2,3.45,1,5,10);insert into t_pricetable values(3,4.45,1,10,null);insert into t_pricetable values(4,3.87,2,0,5);insert into t_pricetable values(5,4.87,2,5,10);insert into t_pricetable values(6,5.87,2,10,null);insert into t_pricetable values(7,4.36,3,0,5);insert into t_pricetable values(8,5.36,3,5,10);insert into t_pricetable values(9,6.36,3,10,null);--账务表--insert into t_account values( seq_account.nextval,1,1,1,'','01',30203,50123,0,1,sysdate,34.51,'1',to_date('-02-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','02',50123,60303,0,1,sysdate,23.43,'1',to_date('-03-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','03',60303,74111,0,1,sysdate,45.34,'1',to_date('-04-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','04',74111,77012,0,1,sysdate,52.54,'1',to_date('-05-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','05',77012,79031,0,1,sysdate,54.66,'1',to_date('-06-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','06',79031,80201,0,1,sysdate,76.45,'1',to_date('-07-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','07',80201,88331,0,1,sysdate,65.65,'1',to_date('-08-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','08',88331,89123,0,1,sysdate,55.67,'1',to_date('-09-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','09',89123,90122,0,1,sysdate,112.54,'1',to_date('-10-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','10',90122,93911,0,1,sysdate,76.21,'1',to_date('-11-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','11',93911,95012,0,1,sysdate,76.25,'1',to_date('-12-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,1,1,1,'','12',95012,99081,0,1,sysdate,44.51,'1',to_date('-01-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','01',30334,50433,0,1,sysdate,34.51,'1',to_date('-02-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','02',50433,60765,0,1,sysdate,23.43,'1',to_date('-03-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','03',60765,74155,0,1,sysdate,45.34,'1',to_date('-04-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','04',74155,77099,0,1,sysdate,52.54,'1',to_date('-05-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','05',77099,79076,0,1,sysdate,54.66,'1',to_date('-06-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','06',79076,80287,0,1,sysdate,76.45,'1',to_date('-07-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','07',80287,88432,0,1,sysdate,65.65,'1',to_date('-08-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','08',88432,89765,0,1,sysdate,55.67,'1',to_date('-09-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','09',89765,90567,0,1,sysdate,112.54,'1',to_date('-10-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','10',90567,93932,0,1,sysdate,76.21,'1',to_date('-11-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','11',93932,95076,0,1,sysdate,76.25,'1',to_date('-12-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'','12',95076,99324,0,1,sysdate,44.51,'1',to_date('-01-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,100,1,3,'','12',95076,99324,0,1,sysdate,44.51,'1',to_date('-01-01','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,101,1,3,'','12',95076,99324,0,1,sysdate,44.51,'1',to_date('-01-01','yyyy-MM-dd'),2 );update t_account set usenum=num1-num0;update t_account set money=usenum*2.45;commit;
到此准备工作就算完成了，下面开始讲解相关的查询。
一、简单查询（单表）
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 的记录。
注意： and优先级大于or，当and和or同时出现，建议加上括号
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;
使用 between…and…（从…开始到…结束）
select * from t_account where usenum between 10000 and 20000;
7. 空值查询
需求：查询 T_PRICETABLE 表中 MAXNUM 为空的记录
select * from t_pricetable where maxnum is null;
需求：查询 T_PRICETABLE 表中 MAXNUM 不为空的记录
select * from t_pricetable where maxnum is not null;
二、去掉重复记录
需求：查询业主表中的地址 ID,不重复显示
select distinct addressid from t_owners ;
三、排序查询
1.升序查询
需求：对 T_ACCOUNT 表按使用量进行升序排序
select * from t_account order by usenum asc;
2.降序排序
需求：对 T_ACCOUNT 表按使用量进行降序排序
select * from t_account order by usenum desc;
四、基于伪列的查询
Oracle中的表存在两个伪列rowid和rownum，它就像表中的列一样，但是没有存储在表中，这也是我们在表中看不见它的原因。伪列只能查询，不能进行DML(增删改)操作。
1.rowid
rowid可以理解为表中每一条数据的物理地址，rwoid可以唯一标识一行数据
select rowid,t.* from t_owners t
我们可以指定 rowid来进行查询
select * from t_owners where rowid='AAAM11AAGAAAADPAAA';
2.rownum
rownum可以理解为查询结果集的行号，从第一行开始，第一行标识为1，第二行为2，一直标识到查询结果集的末尾。
select rownum,t.* from t_owners t;
使用rownum可以进行分页查询。
五、聚合统计
1.聚合函数
（1）求和 sum
需求：统计 年所有用户的用水量总和
select sum(usenum) from t_account where year='';
（2）求平均 avg
需求：统计 年所有用水量（字数）的平均值
select avg(usenum) from t_account where year='';
（3）求最大值 max
需求：统计 年最高用水量（字数）
select max(usenum) from t_account where year='';
（4）求最小值 min
需求：统计 年最低用水量（字数）
select min(usenum) from t_account where year='';
（5）统计记录个数 count
需求：统计业主类型 ID 为 1 的业主数量
select count(*) from t_owners where ownertypeid=1;
2.分组聚合 Group by
需求：按区域分组统计水费合计数
select sum(money) from t_account group by areaid;
3.分组后条件查询 having
需求：查询水费合计大于 169000 的区域及水费合计
select areaid,sum(money) from t_account group by areaid having sum(money)>169000;
二、连接查询
（一）多表内连接查询
（1）需求：查询显示业主编号，业主名称，业主类型名称，
select o.id 业主编号,o.name 业主名称,ot.name 业主类型from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id
（2）需求：查询显示业主编号，业主名称、地址和业主类型。
此查询需要三表关联查询。分别是业主表，业主分类表和地址表
select o.id 业主编号,o.name 业主名称,ad.name 地址,ot.name 业主类型from T_OWNERS o,T_OWNERTYPE ot,T_ADDRESS adwhere o.ownertypeid=ot.id and o.addressid=ad.id
（二）左外连接查询
以左边的表为主，如果右边没有数据与左边对应，则为空。
需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名。
SQL1999标准写法：
SELECT ow.id,ow.name,ac.year ,ac.month,ac.moneyFROM T_OWNERS ow left join T_ACCOUNT acon ow.id=ac.owneruuid
Oracle数据库左外连接的写法（在右表中添加（+））
SELECT ow.id,ow.name,ac.year ,ac.month,ac.moneyFROM T_OWNERS ow , T_ACCOUNT acwhere ow.id=ac.owneruuid(+);
（三）右外连接查询
需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记录没有对应的业主信息，也要列出记录。
SQL1999标准写法：
select ow.id,ow.name,ac.year,ac.month,ac.money fromT_OWNERS ow right join T_ACCOUNT acon ow.id=ac.owneruuid
Oracle数据库右外连接的写法（在左表中添加（+））
select ow.id,ow.name,ac.year,ac.month,ac.money fromT_OWNERS ow ,T_ACCOUNT acwhere ow.id(+)=ac.owneruuid;
三、子查询
（一）where 子句中的子查询
1. 单行子查询
字查询只返回一条记录单行操作符需求：查询 年 1 月用水量大于平均值的台账记录
select * from T_ACCOUNTwhere year='' and month='01' and usenum>( select avg(usenum) from T_ACCOUNT where year='' and month='01' )
;
（2）需求：查询地址含有“花园”的业主的信息
select * from T_OWNERS where addressid in(select id from t_address where name like '%花园%')
（2）需求：查询地址不含有“花园”的业主的信息（not in）
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 otwhere o.ownertypeid=ot.id)where 业主类型='居民'
（三）select 子句中的子查询
字查询也可以放在select之后，但是必须是单条数据的子查询，只能返回一条数据。
（1）需求：列出业主信息，包括 ID，名称，所属地址
select id,name,(select name from t_address where id=addressid) as addressnamefrom t_owners
（2）需求：列出业主信息，包括 ID，名称，所属地址，所属区域。
select id,name,( select name from t_address where id=addressid ) as addressname,( select (select name from t_area where id=areaid ) from t_address where id=addressid ) as adrenamefrom t_owners;
四、分页查询
（一）简单分页
需求：分页查询台账表 T_ACCOUNT，每页 10 条记录
oracle 中的分页查询，需要用到伪列 rownum
我们先显示前10条记录，也就是第一页的数据
select rownum ,t.* from t_account t where rownum <= 10;
用同样的方法查询第二页的数据，也就是第10 到 20条数据
select rownum ,t.* from t_account t where rownum > 10 and rownum <=20;
居然没有结果了，这是因为rownum它不是一瞬间就给所有的行生成行号，而是从第一条数据开始，依次生成，所以rownum伪列不能使用> 、>=、=等运算符，只能使用<,<=运算符。
所以我们应该如何得到第二页的数据呢?我们可以使用子查询先查询出前20条数据，在 通过where截取10以上的
select * from (select rownum as r ,t.* from t_account t where rownum <=20) where r >10;
（二）基于排序的分页
需求：分页查询台账表 T_ACCOUNT，每页 10 条记录，按使用字数降序排序。
查询第一页的数据
select rownum, t.* from t_account t where rownum <= 10 order by usenum desc;
看这个结果是不是发现问题了，为什么rownum是乱的？
因为rownum在结果查询出来之后就生成了，而排序order by则是在生成rownum之后执行，所以一排序rownum就是乱的，那应该怎么解决这个问题？让order by在生成rownum之前执行？
通过子查询先查询出 order by 的数据，然后再生成rownum即可。
select rownum, t.* from (select * from t_account order by usenum desc) twhere rownum <= 10 ;
用同样 的方法查询第二页，不过第二页要稍微复杂一点，需要嵌套三层子查询，首先肯定是先查询出前20条order by的数据，然后通过这个order by的数据生成 rownum，然后在取数据即可（当存在子查询时，建议先编写子查询中的sql语句，由内到外），
先拿到前20条数据
select rownum ,t.* from (select * from t_account order by usenum desc) t where rownum <= 20
然后在将这个结果在作为子查询，截取10以上的数据（记得rownum加别名）
select * from(select rownum r ,t.* from (select * from t_account order by usenum desc) t where rownum <= 20)where r>10;
五、单行函数
（一）字符函数
常用字符函数讲解：
（1）求字符串长度 LENGTH
select length('abcd') from dual;
（2）求字符串的子串 SUBSTR
select substr('ABCD',2,2) from dual;
（3）字符串拼接 CONCAT
select concat('Hello','Oracle!') from dual;
或者使用 || 实现字符串拼接
select 'Hello ' || 'SQL' from dual;
（二）数值函数
（1）四舍五入函数 round
select round(12.5),round(12.4) from dual;
（2）截取函数 trunc
select trunc(100.234) ,trunc(100.234,1),trunc(100.276,1) from dual;
（3）取模 mod
select mod(10,3),mod(10,2),mod(12,5) from dual;
（4）向上取余ceil
select ceil(12.3),ceil(12.6),ceil(12.1) from dual;
（5）向下取余floor
select floor(12.9), floor(12.5) ,floor(12.1) from dual;
（三）日期函数
我们用 sysdate 这个系统变量来获取当前日期和时间
select sysdate from dual;
（1）加月函数 ADD_MONTHS ：在当前日期基础上加指定的月
select add_months(sysdate,2) from dual;
（2）求所在月最后一天 LAST_DAY
select last_day(sysdate) from dual;
（3）日期截取 TRUNC
select trunc(sysdate) from dual;
（四）转换函数
（1）数字转字符串 TO_CHAR
select to_char(1024) from dual;
（2）日期转字符串 TO_CHAR
select to_char(sysdate,'yyyy-mm-dd' ) ,to_char(sysdate,'yyyy-mm-dd hh:mm:ss') from dual;
（3）字符串转日期 TO_DATE
select to_date('-12-13','yyyy-mm-dd') from dual;
（4）字符串转数字 TO_NUMBER
select to_char(1024), to_number('1024') from dual;
（五）其它函数
（1）空值处理函数 NVL
用法：NVL（检测的值，如果为 null 的值）；
select nvl(null,0) from dual;
需求：显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,则显示 9999999
select PRICE,MINNUM,NVL(MAXNUM,9999999)from T_PRICETABLE where OWNERTYPEID=1
（2）空值处理函数 NVL2
用法：NVL2（检测的值，如果不为 null 的值，如果为 null 的值）
需求：显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不限”.
select t.* ,nvl2(maxnum,to_char(maxnum),'不限') from T_PRICETABLE t where OWNERTYPEID=1;
（3）条件取值 decode，类似于case when then
decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)【功能】根据条件返回相应值
需求：显示下列信息（不要关联查询业主类型表，直接判断 1 2 3 的值）
select name,decode(ownertypeid ,1,'居民',2,'行政事业单位',3,'商业') from T_OWNERS
也可以使用 case when then实现
select name ,(case ownertypeid when 1 then '居民'when 2 then '行政事业单位'when 3 then '居民'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) 七月,sum( case when month='08' then money else 0 end) 八月,sum( case when month='09' then money else 0 end) 九月,sum( case when month='10' then money else 0 end) 十月,sum( case when month='11' then money else 0 end) 十一月,sum( case when month='12' then money else 0 end) 十二月from T_ACCOUNT where year='' group by areaid
需求：按季度统计 年各个地区的水费，如下图
select (select name from T_AREA where id= areaid ) 区域,sum( case when month>='01' and month<='03' then money else0 end) 第一季度,sum( case when month>='04' and month<='06' then money else0 end) 第二季度,sum( case when month>='07' and month<='09' then money else0 end) 第三季度,sum( case when month>='10' and month<='12' then money else0 end) 第四季度from T_ACCOUNT where year='' group by areaid
（七）分析函数
以下三个分析函数可以用于排名使用。
下图为三种排名方式的举例
（1） RANK 相同的值排名相同，排名跳跃
需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳跃
select rank() over(order by usenum desc),usenum from t_account ;
（2） DENSE_RANK 相同的值排名相同，排名连续
需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名连续
select dense_rank() over(order by usenum desc),usenum from t_account;
（3） ROW_NUMBER 返回连续的排名，无论值是否相等
需求：对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是否相等
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<=7union allselect * from t_owners where id>=5
UNION 去掉重复记录
select * from t_owners where id<=7union select * from t_owners where id>=5
（三）交集运算
select * from t_owners where id<=7intersectselect * from t_owners where id>=5
（四）差集运算
select * from t_owners where id<=7minusselect * from t_owners where id>=5
如果我们用 minus 运算符来实现分页，语句如下：
select rownum,t.* from T_ACCOUNT t where rownum<=20minusselect rownum,t.* from T_ACCOUNT
