oracle数据库---基本查询（单表查询、多表查询、子查询、分页查询、oracle内置函数、行列转换、集合运算）

思维导图

单表查询

数据准备

-- 练习的表如果存在 请先删除
-- 如果不存在直接创建
drop table t_owners;--业主表
create table t_owners
(id number primary key,name varchar2(30),addressid number,housenumber varchar2(30),watermeter varchar2(30),adddate date,ownertypeid number
);comment on table t_owners is '业主表';
comment on column t_owners.id is '主键';
comment on column t_owners.name is '业主名称';
comment on column t_owners.addressid is '地址ID';
comment on column t_owners.housenumber is '门牌号';
comment on column t_owners.watermeter is '水表编号';
comment on column t_owners.adddate is '登记日期';
comment on column t_owners.ownertypeid is '业主类型ID';--建立价格区间表
create  table t_pricetable
(
id number primary key,
price number(10,2),
ownertypeid number,
minnum number,
maxnum number
);
comment on table t_pricetable is '价格表';
comment on column t_pricetable.id is '主键';
comment on column t_pricetable.price is '价格';
comment on column t_pricetable.ownertypeid is '业主类型ID';
comment on column t_pricetable.minnum is '区间数开始值';
comment on column t_pricetable.maxnum is '区间数截止值';--业主类型
create table t_ownertype
(
id number primary key,
name varchar2(30)
);
comment on table t_ownertype is '业主类型表';
comment on column t_ownertype.id is '主键';
comment on column t_ownertype.name is '类型名称';--区域表
create table t_area
(
id number,
name varchar2(30)
);
comment on table t_area is '区域表';
comment on column t_area.id is '主键';
comment on column t_area.name is '区域名称';--收费员表
create table t_operator
(
id number,
name varchar2(30)
);
comment on table t_operator is '收费员表';
comment on column t_operator.id is '主键';
comment on column t_operator.name is '收费员名称';--地址表
create table t_address
(
id number primary key,
name varchar2(100),
areaid number,
operatorid number
);
comment on table t_address is '地址表';
comment on column t_address.id is '主键';
comment on column t_address.name is '地址名称';
comment on column t_address.areaid is '区域ID';
comment on column t_address.operatorid is '收费员ID';--收费台账表--
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
);
comment on table t_account is '收费台账表';
comment on column t_account.id is '主键';
comment on column t_account.owneruuid is '业主编号';
comment on column t_account.ownertype is '业主类型';
comment on column t_account.areaid is '所在区域';
comment on column t_account.year is '账务年份';
comment on column t_account.month is '账务月份';
comment on column t_account.num0 is '上月累计数';
comment on column t_account.num1 is '本月累计数';
comment on column t_account.usenum is '本月使用数';
comment on column t_account.meteruser is '抄表员';
comment on column t_account.meterdate is '抄表日期';
comment on column t_account.money is '应缴金额';
comment on column t_account.isfee is '是否缴费';
comment on column t_account.feedate is '缴费日期';
comment on column t_account.feeuser is '收费员';drop sequence seq_account;
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('2015-04-12','yyyy-MM-dd'),1 );
insert into t_owners values(2,'王强',1,'1-2','30407',to_date('2015-02-14','yyyy-MM-dd'),1 );
insert into t_owners values(3,'马腾',1,'1-3','30408',to_date('2015-03-18','yyyy-MM-dd'),1 );
insert into t_owners values(4,'林小玲',2,'2-4','30409',to_date('2015-06-15','yyyy-MM-dd'),1 );
insert into t_owners values(5,'刘华',2,'2-5','30410',to_date('2013-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(6,'刘东',2,'2-2','30411',to_date('2014-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(7,'周健',3,'2-5','30433',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(8,'张哲',4,'2-2','30455',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(9,'昌平区中西医结合医院',5,'2-2','30422',to_date('2016-10-11','yyyy-MM-dd'),2 );
insert into t_owners values(10,'美廉美超市',5,'4-2','30423',to_date('2016-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,'2012','01',30203,50123,0,1,sysdate,34.51,'1',to_date('2012-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','02',50123,60303,0,1,sysdate,23.43,'1',to_date('2012-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','03',60303,74111,0,1,sysdate,45.34,'1',to_date('2012-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','04',74111,77012,0,1,sysdate,52.54,'1',to_date('2012-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','05',77012,79031,0,1,sysdate,54.66,'1',to_date('2012-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','06',79031,80201,0,1,sysdate,76.45,'1',to_date('2012-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','07',80201,88331,0,1,sysdate,65.65,'1',to_date('2012-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','08',88331,89123,0,1,sysdate,55.67,'1',to_date('2012-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','09',89123,90122,0,1,sysdate,112.54,'1',to_date('2012-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','10',90122,93911,0,1,sysdate,76.21,'1',to_date('2012-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','11',93911,95012,0,1,sysdate,76.25,'1',to_date('2012-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','12',95012,99081,0,1,sysdate,44.51,'1',to_date('2013-01-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,2,1,3,'2012','01',30334,50433,0,1,sysdate,34.51,'1',to_date('2013-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','02',50433,60765,0,1,sysdate,23.43,'1',to_date('2013-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','03',60765,74155,0,1,sysdate,45.34,'1',to_date('2013-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','04',74155,77099,0,1,sysdate,52.54,'1',to_date('2013-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','05',77099,79076,0,1,sysdate,54.66,'1',to_date('2013-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','06',79076,80287,0,1,sysdate,76.45,'1',to_date('2013-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','07',80287,88432,0,1,sysdate,65.65,'1',to_date('2013-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','08',88432,89765,0,1,sysdate,55.67,'1',to_date('2013-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','09',89765,90567,0,1,sysdate,112.54,'1',to_date('2013-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','10',90567,93932,0,1,sysdate,76.21,'1',to_date('2013-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','11',93932,95076,0,1,sysdate,76.25,'1',to_date('2013-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-14','yyyy-MM-dd'),2 );insert into t_account values( seq_account.nextval,100,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-01','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,101,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2015-01-01','yyyy-MM-dd'),2 );update t_account set usenum=num1-num0;
update t_account set money=usenum*2.45;
commit;

ctrl+a 全选sql  运行

执行结束  可以点击运行commit按钮提交一下

可以发现wateruser下已经创建好了7张表  通过双击表可以查看各个表中的数据

简单条件查询

精确查询

- todo 需求 ：查询水表编号为 30408 的业主记录

查询语句 ：

select * from t_owners where watermeter= '30408';

查询结果 ：

模糊查询

-- todo 需求 ：查询业主名称包含“刘”的业主记录

查询语句 ：

select * from t_owners where name like '%刘%';
--下面两个什么区别？
select * from t_owners where name like '刘_';
select * from t_owners where name like '刘%';

％(百分号)匹配零个或多个字符的任何字符串。

_(下划线)匹配任何单个字符。

查询结果 ：

and 运算符

需求 ：查询业主名称包含“刘”的并且门牌号包含 5 的业主记录

查询语句 ：

select * from t_owners where name like '%刘%' and housenumber like '%5%';

查询结果 ：

​​​​​​​or 运算符

需求 ：查询业主名称包含“刘”的或者门牌号包含 5 的业主记录

查询语句 ：

select * from t_owners where name like '%刘%' or housenumber like '%5%';

查询结果 ：

​​​​​​​and 与 or 运算符混合使用

需求 ：查询业主名称包含“刘”的或者门牌号包含 5 的业主记录 ，并且地址编号 为 3 的记录。

语句 ：

select * from t_owners
where (name like '%刘%' or housenumber like '%5%')
and addressid=3;

查询结果 ：

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

​​​​​​​范围查询

需求 ：查询台账记录中用水字数大于等于 10000 ，并且小于等于 20000 的记录

我们可以用>=  和<=来实现

--todo 思考一下，下面这种数学上的写法是否可以？
select * from t_account
where 20000>= usenum >=10000; -- 语法不支持select * from t_account
where usenum>=10000 and usenum<=20000;

我们也可以用 between      ..    and      ..来实现

select * from t_account
where usenum between 10000  and 20000;

--in（不连续的范围）
--需求：查询业主ID是1或者3或者5或者7的业主记录
select * from t_owners where id =1 or id = 3 or id = 5 or id = 7;
select * from t_owners where id in(1,3,5,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;

查询结果 ：

--todo 思考一下 下面这样是否可以？ null究竟表示什么意思？ null ''  'null'有啥区别 ''？
select * from t_pricetable t where maxnum = null;insert into T_AREA values (8,null);
insert into T_AREA values (9,'');
insert into T_AREA values (10,'null');select * from T_AREA where NAME is null;
select * from T_AREA where NAME = 'null';

在 Oracle 数据库中，NULL 表示空值或缺失的值。它是数据库中的特殊标记，用来表示数据值未知、不存在或不可用。

与空字符串或零值不同，NULL 表示该字段完全没有任何数据。

​​​​​​​去掉重复记录

需求 ：查询业主表中的地址 ID,不重复显示

语句 ：

select distinct addressid from t_owners;

select addrid from 表 group by  addrid ；分组在聚合统计中有介绍

窗口函数：rownumber（）over（partitiion by 分组）过滤 rn = 1 下一章讲

/*去重、排序的操作*/
--需求 ：查询业主表中的地址 ID,不重复显示
select  addressid from t_owners;select distinct addressid from t_owners;-- todo SQL中实现数据去重的方式有几种？
-- 方案1：distinct
select distinct addressid from t_owners;
-- 方案2：group by
select ADDRESSID from T_OWNERS group by ADDRESSID;
-- 方案3：窗口函数row_number
select t1.*,row_number() over (partition by ADDRESSID order by ADDRESSID) rn
from T_OWNERS t1 ;with t2 as ( select t1.*,row_number() over (partition by ADDRESSID order by ADDRESSID) rn
from T_OWNERS t1  )
select * from t2 where rn=1;

​​​​​​​排序查询

1.升序排序

需求 ：对 T_ACCOUNT 表按使用量进行升序排序

语句 ：

select * from t_account order by usenum;

查询结果 ：

2.降序排序

需求 ：对 T_ACCOUNT 表按使用量进行降序排序

语句 ：

select * from t_account order by usenum desc;

查询结果 ：

​​​​​​​基于伪列的查询

在 Oracle 的表的使用过程中 ，实际表中还有一些附加的列，称为伪列。

伪列就 像表中的列一样 ，但是在表中并不存储。伪列只能查询，不能进行增删改操作。

接下来学习两个伪列 ：ROWID 和 ROWNUM。

​​​​​​​ROWID

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

使用 ROWID 可以快速的定位表中的某一行。

ROWID 值可以唯一的标识表中的一行。

由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可以显示行是如何存储的。

常见的用法：

唯一标识行：可以用来快速定位和访问数据库中的某一行。

高效查询和更新：利用 ROWID 进行查询和更新操作，通常比使用主键或其他索引列更快，因为 ROWID 直接指向数据文件中的物理位置。

select rowid, t.* from t_area t;

查询结果如下：

我们可以通过指定 ROWID 来查询海淀的记录（注意：每个人的伪列不同）

select rowID,t.*
from T_AREA t
where ROWID= 'AAASDQAAHAAAAC+AAA';

查询结果如下 ：

​​​​​​​ROWNUM

在查询的结果集中 ，ROWNUM 为结果集中每一行标识一个行号 ，第一行返回 1 ， 第二行返回 2 ，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数

常见的用法

限制结果集大小：可以用来限制查询返回的行数，如取前 N 行。

分页查询：在分页查询中，通过 ROWNUM 可以实现简单的分页逻辑。

select rownum,t.* from t_owners t;

-- 查询返回的前 5 行
select *
from t_owners
where rownum <= 5;

查询结果如下 ：

我们的分页查询需要用到此伪列，在本章第四小节详细讲解。

简单总结一下就是：

ROWID：唯一标识数据库中每一行的物理位置，常用于快速访问和更新特定行。

ROWNUM：表示查询结果集中每行的行号，常用于限制查询结果集大小和实现分页查询。

​​​​​​​聚合统计

ORACLE 的聚合统计是通过分组函数来实现的 ，与 MYSQL 一致。

/*分组、聚合操作、having过滤和MySQL一致。 常用的聚合函数：count  sum  avg  max mintodo1、分组和聚合是什么关系？要同时存在吗？1.分组可以去重2.聚合可以不分组（全表看成一组）3.边分组边聚合 （经典用法分组聚合）2、有了分组聚合之后，select查询返回有哪些限制？为什么要限制？1、分组的列 2、聚合函数列原因：（无关的列可能会不一致）3、如果相对分组聚合之后的结果进行过滤筛选，where行吗？不行怎么办？where 不可以， where执行时机太早，那个时候聚合还没算出来。使用having 条件 进行过滤。4、where和having能否同时存在？是可以的。先走where、后走group by 再走having*/
-- 需求按照地区分组。求每个地区的用水量，用户用水量对于1000的不参与统计。
select ac.AREAID,sum(ac.USENUM)
from T_ACCOUNT ac join T_OWNERS own on ac.OWNERUUID = own.ID
where ac.USENUM > 1000 group by ac.AREAID having sum(ac.USENUM)>68000;select ac.AREAID,sum(ac.USENUM)
from T_ACCOUNT ac join T_OWNERS own on ac.OWNERUUID = own.IDgroup by ac.AREAID;

查询结果如下 ：

1.  聚合函数

( 1 ) 求和   sum

需求 ：统计 2012 年所有用户的用水量总和

select sum(usenum) from t_account where year= '2012';

查询结果如下 ：

( 2 ) 求平均 avg

需求 ：统计 2012 年所有用水量 (字数) 的平均值

select avg(usenum) from t_account where year= '2012';

查询结果如下 ：

( 3 ) 求最大值 max

需求 ：统计 2012 年最高用水量 (字数)

select max(usenum) from t_account where year= '2012';

查询结果如下 ：

( 4 ) 求最小值 min

需求 ：统计 2012 年最低用水量 (字数)

select min(usenum) from t_account where year= '2012';

查询结果如下 ：

( 5 ) 统计记录个数 count

需求 ：统计业主类型 ID 为 1 的业主数量

select count(*) from t_owners t where ownertypeid=1;

查询结果如下 ：

2.  分组聚合 Group by

需求 ：按区域分组统计水费合计数

语句 ：

selectareaid,sum(money)
from t_account group by areaid;--下面可以运行吗
selectareaid,usenum,sum(money)
from t_account group by areaid;

查询结果 ：

3.  分组后条件查询 having

需求 ：查询水费合计大于 16900 的区域及水费合计

语句 ：

selectareaid,sum(money)
from t_account
group by areaid
having sum(money)>169000;

查询结果 ：

注意：

selectareaid,sum(money) as smoney
from t_account
group by areaid
having smoney > 169000;-- 不支持的操作。

多表查询

多表内连接查询

需求 ：查询显示业主编号 ，业主名称 ，业主类型名称

如下图 ：

查询语句 ：

selectt1.id,t1.name,t2.name
from t_owners t1
inner join t_ownertype t2
on t1.ownertypeid=t2.id;selectt1.id,t1.name,t2.name
from t_owners t1
join t_ownertype t2
on t1.ownertypeid=t2.id;--todo 隐式连接的写法
selectt1.id,t1.name,t2.name
from t_owners t1, t_ownertype t2
where t1.ownertypeid=t2.id;;--todo 如果内连接不加条件 返回的是笛卡尔积结果  称之为交叉连接  cross join
select*
from t_owners t1, t_ownertype t2;select*
from t_owners t1 cross join t_ownertype t2;

查询结果 ：

( 2 ) 需求 ：查询显示业主编号 ，业主名称、地址和业主类型 ，如下图

分析 ：此查询需要三表关联查询。分别是业主表 ，业主分类表和地址表

语句 ：

selecto.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;

selectt1.id 业主编号,t1.name 业主名称,t2.name 地址,t3.name 业主类型
from t_owners t1
join t_address t2 on t1.addressid=t2.id
join t_ownertype t3 on t1.ownertypeid=t3.id;

( 3 ) 需求 ：查询显示业主编号、业主名称、地址、所属区域、业主分类 ，如下图 ：

分析 ：这里需要四个表关联查询 ，比上边多了一个区域表 ( T_AREA )

查询语句 ：

selecto.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;

( 4 ) 需求 ：查询显示业主编号、业主名称、地址、所属区域、  收费员、业主分 类 ，如下图 ：

分析 ：此查询比上边又多了一个表 T_OPERATOR

语句 ：

selectow.id 业主编号,ow.name 业主名称,ad.name 地址,ar.name 所属区域,op.name 收费员,ot.name 业主类型
from t_owners ow,t_ownertype ot,t_address ad, t_area ar, t_operator op
where ow.ownertypeid=ot.id and ow.addressid=ad.id
and ad.areaid=ar.id and ad.operatorid=op.id;

​​​​​​​左外连接查询

需求 ：查询业主的账务记录 ，显示业主编号、名称、年、月、金额。如果此业主 没有账务记录也要列出姓名。

分析 ：我们要查询这个结果 ，需要用到 T_OWNERS (业主表)    ，T_ACCOUNT (台账表)    按照查询结果 ，业主表为左表、账务表为右表。

按照 SQL1999 标准的语法 ，查询语句如下 ：

selectow.id,ow.name,ac.year,ac.month,ac.money
from t_owners ow
left join t_account ac
on ow.id=ac.owneruuid;

按照 ORACLE 提供的语法 ，就很简单了 ：

selectow.id,ow.name,ac.year,ac.month,ac.money
from t_owners ow,t_account ac
where ow.id=ac.owneruuid(+);

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

​​​​​​​右外连接查询

需求 ：查询业主的账务记录 ，显示业主编号、名称、年、月、金额。

如果账务记录没有对应的业主信息 ，也要列出记录。如下图 ：

SQL1999 标准的语句

selectow.id,ow.name,ac.year,ac.month,ac.money
from t_owners ow right join t_account ac
on ow .id=ac .owneruuid;

ORACLE 的语法

selectow.id,ow.name,ac.year,ac.month,ac.money
from t_owners ow right join t_account ac
on ow .id=ac .owneruuid;--todo oracle特有的语法
selectow.id,ow.name,ac.year,ac.month,ac.money
from t_owners ow, t_account ac
where ow.id(+) = ac.owneruuid;

全外连接

-- 补充:  todo 全外连接 ------full join-----左特有+左右关联+右特有
select * from T_OWNERS t1 full join T_OWNERTYPE t2 on t1.OWNERTYPEID = t2.id;

查询结果 ：

补充：

-- mysql不使用full join 也能实现全外连接查询。
select * from T_OWNERS t1 left join T_OWNERTYPE t2 on t1.OWNERTYPEID = t2.id
union
select * from T_OWNERS t1 right join T_OWNERTYPE t2 on t1.OWNERTYPEID = t2.id;

​​​​​​​子查询

​​​​​​​where 子句中的子查询

1.  单行子查询

   只返回一条记录

   单行操作符

操作符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

 

需求 ：查询 2012 年 1 月用水量大于平均值的台账记录

语句 ：

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

查询结果 ：

平均值为 ：

2.  多行子查询

   返回了多条记录

   多行操作符

操作符	含义
in	等于列表中的任意一个
any	和子查询返回的任意一个值比较
all

in  运算符

( 1 ) 需求 ：查询地址编号为 1、3、4  的业主记录

分析 ：如果我们用 or 运算符编写 ，SQL 非常繁琐 ，所以我们用 in 来进行查询

语句如下 ：

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

查询结果如下 ：

( 2 ) 需求 ：查询地址含有“花园”的业主的信息

语句 ：

select * from t_owners
where addressid in
(select id from t_address where name like '%花园%');

查询结果 ：

( 3 ) 需求 ：查询地址不含有“花园”的业主的信息

语句 ：

select * from t_owners
where addressid not in
(select id from t_address where name like '%花园%');

查询结果 ：

​​​​​​​from 子句中的子查询

from 子句的子查询为多行子查询

需求 ：查询显示业主编号 ，业主名称 ，业主类型名称 ，条件为业主类型为”居民” ， 使用子查询实现。

语句 ：

-- todo 1 条件为业主类型为”居民”
select t1.id, t1.name from t_ownertype t1
where t1.name = '居民';-- todo 2 关联查询
select t1.id 业主编号, t1.name as 业主名称, t2.name 业主类型名称
from t_owners t1, (select t1.id, t1.name from t_ownertype t1where t1.name = '居民') t2
where t1.ownertypeid = t2.id;

查询结果如下:

​​​​​​​select 子句中的子查询

select  子句的子查询必须为单行子查询

需求 ：列出业主信息 ，包括 ID ，名称 ，所属地址。

语句 ：

select id, name,(select t2.name from t_address t2 where t2.id=t1.addressid) addressname
from t_owners t1;

查询结果如下 ：

分页查询

​​​​​​​简单分页

需求 ：分页查询台账表 T_ACCOUNT ，每页 10 条记录

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

我们首先显示前 10 条记录 ，语句如下 ：

select rownum, t.* from t_account t where rownum<=10;

显示结果如下 ：

那么我们显示第 11 条到第 20 条的记录呢？编写语句 ：

select rownum,t .* from t_account t where rownum>10 and rownum<=20;

查询结果 ：

嗯？怎么没有结果？

这是因为 rownum 是在查询语句扫描每条记录时产生的 ，所以不能使用“大于”符号 ，只能使用“小于”或“小于等于”，只用“等于”也不行。

那怎么办呢？我们可以使用子查询来实现

select *
from (select rownum as rnum, t.*from t_account twhere rownum <= 20
)
where rnum >= 10;

查询结果如下 ：

​​​​​​​基于排序的分页

需求 ：分页查询台账表 T_ACCOUNT ，每页 10 条记录 ，按使用字数降序排序。

我们查询第 2 页数据 ，如果基于上边的语句添加排序 ，语句如下 ：

select *
from (select rownum as rnum, t.*from t_account twhere rownum <= 20order by usenum desc
)
where rnum >= 10;

查询结果如下 ：

经过验证 ，我们看到第 2 页的结果应该是下列记录

所以推断刚才的语句是错误的 ！那为什么是错误的呢 ？

我们可以先单独执行嵌套查询里面的那句话

select rownum as rnum, t.*
from t_account t
where rownum <= 20
order by usenum desc

你会看到查询结果如下 ：

你会发现排序后的 R 是乱的。这是因为 ROWNUM 伪列的产生是在表记录扫描 是产生的 ，而排序是后进行的 ，排序时 R 已经产生了 ，所以排序后 R 是乱的。

那该如何写呢 ？

很简单 ，我们只要再嵌套一层循环( 一共三层)  ，让结果先排序 ，然后对排序后 的结果再产生 R ，这样就不会乱了。

语句如下 ：

select * from(select rownum as rnum, t.* from(select * from t_account order by usenum desc) twhere rownum<=20)
where rnum>10;

结果如下 ：

Oracle内置函数

​​​​​​​字符函数

函    数	说                明
ASCII	返回对应字符的十进制值
CHR	给出十进制返回字符
CONCAT	拼接两个字符串 ，与  || 相同
INITCAT	将字符串的第一个字母变为大写
INSTR	找出某个字符串的位置
INSTRB	找出某个字符串的位置和字节数
LENGTH	以字符给出字符串的长度
LENGTHB	以字节给出字符串的长度
LOWER	将字符串转换成小写
LPAD	使用指定的字符在字符的左边填充
LTRIM	在左边裁剪掉指定的字符
RPAD	使用指定的字符在字符的右边填充
RTRIM	在右边裁剪掉指定的字符
REPLACE	执行字符串搜索和替换
SUBSTR	取字符串的子串
SUBSTRB	取字符串的子串 (以字节)
SOUNDEX	返回一个同音字符串
TRANSLATE	执行字符串搜索和替换
TRIM	裁剪掉前面或后面的字符串
UPPER	将字符串变为大写

常用字符函数讲解 ：

( 1 ) 查询字符串中的字符个数 LENGTH

语句 ：

--	( 1 ) 查询字符串中的字符个数 LENGTH
select length('ABCD') from dual;
select length('我爱你') from dual;
--查询字符串中的字节个数
select lengthb('ABCD') from dual;
select lengthb('我爱你') from dual;

显示结果为 ：

( 2 ) 求字符串的子串 SUBSTR

语句 ：

select substr('hello,oracle!',2,2) from dual;
select substr('hello,oracle!',2) from dual;
select substr('hello,oracle!',-7) from dual;

显示结果为 ：

( 3 ) 字符串拼接 CONCAT

语句 ：

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

查询结果如下 ：

我们也可以用 ||  对字符串进行拼接

select 'ABC' || 'D' from dual;

查询结果同上。

注意：concat只能拼两段字符，|| 可以拼多段字符。

​​​​​​​数值函数

函数	说明
ABS(value)	绝对值
CEIL(value)	大于或等于 value 的最小整数
COS(value)	余弦
COSH(value)	反余弦
EXP(value)	e 的 value 次幂
FLOOR(value)	小于或等于 value 的最大整数
LN(value)	value 的自然对数
LOG(value)	value 的以 10 为底的对数
MOD(value,divisor)	求模
POWER(value,exponent)	value 的 exponent 次幂
ROUND(value,precision)	按 precision 精度 4 舍 5 入
SIGN(value)	value 为正返回 1;为负返回-1;为 0 返回 0.
SIN(value)	余弦
SINH(value)	反余弦
SQRT(value)	value  的平方根
TAN(value)	正切
TANH(value)	反正切
TRUNC(value,按 precision)	按照 precision 截取 value
VSIZE(value)	返回 value 在 ORACLE 的存储空间大小

常用数值函数讲解 ：

( 1 ) 四舍五入函数 ROUND

语句 ：

select round(100.567) from dual;
select round(100.567,2) from dual;

查询结果如下 ：

( 2 ) 截取函数 TRUNC

语句 ：

select trunc(100.567) from dual;
select trunc(100.567,2) from dual;

查询结果 ：

( 3 ) 取模   MOD

语句 ：

select mod(10, 3) from dual;

结果 ：

生成随机数

-- todo 如果想生成随机数
-- 首先，初始化随机数生成器
begindbms_random.initialize(sys.dbms_random.value);
end;-- 然后，使用 DBMS_RANDOM.VALUE 函数生成一个随机整数。
select round(dbms_random.value(1, 100)) as random_int from dual;

查询结果如下 ：

生成一个 0 到 1 之间的随机浮点数

--生成一个 0 到 1 之间的随机浮点数  [默认]
select dbms_random.value as random_float from dual;

查询结果如下 ：

生成一个长度为 10 的随机字母字符串

--生成一个长度为 10 的随机字母字符串
select dbms_random.string('U', 10) as random_string from dual;-- upper
select dbms_random.string('L', 10) as random_string from dual;-- lower
select dbms_random.string('A', 10) as random_string from dual;-- all
select dbms_random.string('X', 6) as random_string from dual;-- 'U'：生成大写字母字符串。
-- 'L'：生成小写字母字符串。
-- 'A'：生成大写字母和小写字母的字符串。
-- 'X'：生成大写字母和数字字符串

查询结果如下 ：

(三) 日期函数

函   数	描      述
ADD_MONTHS	在日期 date 上增加 count 个月
GREATEST(date1,date2,. . .)	从日期列表中选出最晚的日期
LAST_DAY( date )	返回日期 date 所在月的最后一天
LEAST( date1, date2, . . .)	从日期列表中选出最早的日期
MONTHS_BETWEEN(date2, date1)	给出  Date2 - date1  的月数(可以是小数)
NEXT_DAY( date,’day’)	给出日期 date 之后下一天的日期 ，这里的 day 为星期 ， 如 ：MONDAY,Tuesday 等。
NEW_TIME(date, ’this’,’other’)	给出在 this  时区=Other 时区的日期和时间
ROUND(date,’format’)	未指定 format 时 ，如果日期中的时间在中午之前 ，则 将日期中的时间截断为 12 A.M.(午夜 ，一天的开始),否 则进到第二天。 时间截断为 12 A.M.(午夜 ，一天的开始), 否则进到第二天。
TRUNC(date,’format’)	未指定 format 时 ，将日期截为 12 A.M.( 午夜 ，一天的开始).

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

语句如下 ：

select sysdate from dual;

查询结果如下 ：

常用日期函数讲解 ：

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

语句 ：

select add_months(sysdate, 2) from dual;

查询结果如下 ：

( 2 )求所在月最后一天 LAST_DAY

语句 ：

select last_day(sysdate) from dual;

查询结果如下 ：

只要年月日

select last_day(to_date('1999-09-09','YYYY-MM-dd')) from dual;

查询结果如下 ：

( 3 ) 日期截取 TRUNC

语句 ：

select trunc(sysdate) from dual -- 截掉时分秒。留下年月日

查询结果如下 ：

语句 ：

select trunc(sysdate, 'yyyy') from dual; -- 今年的第一天

查询结果如下 ：

语句 ：

select trunc(sysdate, 'mm') from dual; -- 这个月的第一天

查询结果如下 ：

（4）日期加减

--将天数从一个日期中加上select sysdate + 10 as future_date from dual;
select sysdate + interval '5' hour as future_time from dual;
select sysdate + interval '30' minute as future_time from dual;select sysdate - 10 as past_date from dual;
select sysdate - interval '5' hour as past_time from dual;
select sysdate - interval '30' minute as past_time from dual;

查询结果如下 ：

当前时间 ：

以下结果依次按代码顺序显示

计算两个日期之间的天数差

--计算两个日期之间的天数差
select to_date('2024-07-01', 'yyyy-mm-dd') - to_date('2024-06-01', 'yyyy-mm-dd') as days_difference
from dual;

​​​​​​​转换函数

函   数	描      述
CHARTOROWID	将 字符转换到  rowid 类型
CONVERT	转换一个字符节到另外一个字符节
HEXTORAW	转换十六进制到 raw 类型
RAWTOHEX	转换 raw 到十六进制
ROWIDTOCHAR	转换  ROWID 到字符
TO_CHAR	转换日期格式到字符串
TO_DATE	按照指定的格式将字符串转换到日期型
TO_MULTIBYTE	把单字节字符转换到多字节
TO_NUMBER	将数字字串转换到数字
TO_SINGLE_BYTE	转换多字节到单字节

常用转换函数讲解 ：

( 1 ) 数字转字符串 TO_CHAR

语句 ：

select to_char(1024) from dual

查询结果 ：

( 2 )  日期转字符串 TO_CHAR

语句 ：

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

查询结果 ：

语句 ：

select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual

查询结果 ：

语句 ：

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual

查询结果 ：

( 3 )字符串转日期 TO_DATE

语句 ：

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

查询结果如下 ：

( 4 ) 字符串转数字 TO_NUMBER

语句 ：

select to_number('100'), '100' from dual

查询结果如下 ：

​​​​​​​其它函数

( 1 ) 空值处理函数 NVL

用法 ：

NVL (检测的值 ，如果为 null 的值)  ；

语句 ：

select NVL(NULL, 0) from dual;

查询结果如下 ：

语句 ：

select NVL(100, 0) from dual;

查询结果如下 ：

需求 ：

显示价格表中业主类型 ID 为 1 的价格记录 ，如果上限值为 NULL,则显示 9999999

语句 ：

select price, minnum, nvl(maxnum,9999999)from t_pricetablewhere ownertypeid=1

查询结果 ：

( 2 ) 空值处理函数 NVL2

用法 ：

NVL2 (检测的值，如果不为 null 的值，如果为 null 的值)  ；

需求 ：显示价格表中业主类型 ID 为 1 的价格记录 ，如果上限值为 NULL,显示“不限“ .

语句 ：

select price, minnum, nvl2(maxnum, to_char(maxnum), '不 限')
from t_pricetable
where ownertypeid=1;

( 3 ) 条件取值 decode

语法 ：

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

需求 ：显示下列信息 (不要关联查询业主类型表 ，直接判断 1 2 3  的值)

语句 ：

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

上边的语句也可以用 case when then  语句来实现

select name,case ownertypeidwhen 1 then '居民'when 2 then '行政事业单位 'when 3 then '商业'end as a
from t_owners;

还有另外一种写法 ：

select name,(casewhen ownertypeid=1 then '居民'when ownertypeid=2 then '行政事业'when ownertypeid=3 then '商业'end )
from t_owners;

行列转换

需求 ：按月份统计 2012 年各个地区的水费 ，如下图

代码如下:

select(select a.NAME from T_AREA a where t.AREAID = a.ID ) as 区域,sum(case when MONTH = '01' then MONEY else 0 end ) as 一月,sum(case when MONTH = '02' then MONEY else 0 end ) as 二月,sum(case when MONTH = '03' then MONEY else 0 end ) as 三月,sum(case when MONTH = '04' then MONEY else 0 end ) as 四月,sum(case when MONTH = '05' then MONEY else 0 end ) as 五月,sum(case when MONTH = '06' then MONEY else 0 end ) as 六月,sum(case when MONTH = '07' then MONEY else 0 end ) as 七月,sum(case when MONTH = '08' then MONEY else 0 end ) as 八月,sum(case when MONTH = '09' then MONEY else 0 end ) as 九月,sum(case when MONTH = '10' then MONEY else 0 end ) as 十月,sum(case when MONTH = '11' then MONEY else 0 end ) as 十一月,sum(case when MONTH = '12' then MONEY else 0 end ) as 十二月
from T_ACCOUNT t where YEAR = '2012'
group by t.AREAID;

需求 ：按季度统计 2012 年各个地区的水费 ，如下图

语句如下 ：

select(select t2.name from t_area t2 where id=t1.areaid) 区域,sum(case when month in ('01', '02', '03') then money end) as 第一季度,sum(case when month in ('04', '05', '06') then money end) as 第二季度,sum(case when month in ('07', '08', '09') then money end) as 第三季度,sum(case when month in ('10', '11', '12') then money end) as 第四季度
from t_account t1
where year='2012'
group by areaid;

集合运算

什么是集合运算

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

• UNION ALL(并集) ，返回各个查询的所有记录 ，包括重复记录。
• UNION(并集) ，返回各个查询的所有记录 ，不包括重复记录。
• INTERSECT(交集) ，返回两个查询共有的记录。
• MINUS(差集) ，返回第一个查询检索出的记录减去第二个查询检索出的记录之 后剩余的记录。

​​​​​​​并集运算

UNION ALL  不去掉重复记录

-- union
select * from t_owners where id<=3
union
select * from t_owners where id<=3;
-- union all
select * from t_owners where id<=3
union all
select * from t_owners where id<=3;

​​​​​​​交集运算

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;

结果如下: