oracle数据查询

最新推荐文章于 2024-05-06 21:10:29 发布
最新推荐文章于 2024-05-06 21:10:29 发布
阅读量715 收藏 2
点赞数
分类专栏： oracle 文章标签： oracle 数据库 dba
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Java_Fly1/article/details/124956885
版权

oracle数据查询

数据准备


--建立价格区间表
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('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;

单表查询

基于伪列的查询

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。接下来学习两个伪列：ROWID 和 ROWNUM。

ROWID

表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的

-- 查询语句
select rowid,t.* from t_owners t ;

查询结果如下：
在这里插入图片描述
可以通过指定 ROWID 来查询记录

select rowID,t.*
from t_owners t
where ROWID='AAADY/AAFAAAADYAAA';

查询结果如下：
在这里插入图片描述

ROWNUM

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

select rownum,t.*
from t_owners t;

查询结果如下：
在这里插入图片描述

聚合统计

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

求和 sum

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

select sum(usenum) 用水量总和
from t_account where year='2012'

查询结果如下
在这里插入图片描述

求平均 avg

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

select avg(usenum) 用水量平均值
from t_account where year='2012'

查询结果如下
在这里插入图片描述

求最大值 max

统计 2012 年最高用水量（字数）

select max(usenum) 最高用水量
from t_account where year='2012'

查询结果如下
在这里插入图片描述

求最小值 min

统计 2012 年最低用水量（字数）

select min(usenum) 最低用水量
from t_account where year='2012'

查询结果如下
在这里插入图片描述

分组聚合 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

查询结果：
在这里插入图片描述

连接查询

多表内连接查询

（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 ad
where o.ownertypeid=ot.id and o.addressid=ad.id

（3）需求：查询显示业主编号、业主名称、地址、所属区域、业主分类，如下图：
在这里插入图片描述
分析：这里需要四个表关联查询，比上边多了一个区域表（T_AREA）
查询语句：

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

（4）需求：查询显示业主编号、业主名称、地址、所属区域、收费员、业主分类，如下图：
在这里插入图片描述
分析：此查询比上边又多了一个表 T_OPERATOR
语句：

select ow.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 标准的语法，查询语句如下：

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 子句中的子查询

单行子查询

  • 只返回一条记录
  • 单行操作符
    在这里插入图片描述
    需求：查询 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')

查询结果：
在这里插入图片描述

多行子查询

  • 返回了多条记录
  • 多行操作符
    在这里插入图片描述
    in 运算符
    （1）需求：查询地址编号为 1 、3、4 的业主记录
select * from t_owners t where t.addressid in (1,3,4);

查询结果如下：
在这里插入图片描述
（2）需求：查询地址含有“花园”的业主的信息

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

查询结果：
在这里插入图片描述
3）需求：查询地址不含有“花园”的业主的信息

select * from t_owners t 
where t.addressid not in 
(select d.id from t_address d where d.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)v 
where v.业主类型='居民';

在这里插入图片描述

select 子句中的子查询

select 子句的子查询必须为单行子查询
（1）需求：列出业主信息，包括 ID，名称，所属地址。

select id,name,
(select name from t_address where id=addressid) addressname from t_owners

在这里插入图片描述
（2）需求：列出业主信息，包括 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;

在这里插入图片描述

分页查询

简单分页

需求：分页查询台账表 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 r,t.* from T_ACCOUNT t where rownum<=20)where r>10

在这里插入图片描述

基于排序的分页

需求：分页查询台账表 T_ACCOUNT，每页 10 条记录，按使用字数降序排序。
我们查询第 2 页数据，如果基于上边的语句添加排序，语句如下：

select * from
(select rownum r,t.* from T_ACCOUNT t where rownum<=20 order by usenum desc)
where r>10

查询结果如下：
在这里插入图片描述
经过验证，我们看到第 2 页的结果应该是下列记录
在这里插入图片描述
所以推断刚才的语句是错误的！那为什么是错误的呢？
我们可以先单独执行嵌套查询里面的那句话

select rownum r,t.* from T_ACCOUNT t 
where rownum<=20 order by usenum desc;

你会看到查询结果如下：
在这里插入图片描述
你会发现排序后的 R 是乱的。这是因为 ROWNUM 伪列的产生是在表记录扫描
是产生的，而排序是后进行的，排序时 R 已经产生了，所以排序后R是乱的。
那该如何写呢？
很简单，我们只要再嵌套一层循环（一共三层），让结果先排序，然后对排序后
的结果再产生 R，这样就不会乱了。
语句如下：

select * from
(select rownum r,t.* from
(select * from T_ACCOUNT order by usenum desc) t
where rownum<=20 )
where r>10;

字符函数

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

在这里插入图片描述
（2）求字符串的子串 SUBSTR

select substr('abcd',2,2) from dual;

在这里插入图片描述
（3）字符串拼接 CONCAT

select concat('abc','d') from dual;

在这里插入图片描述
我们也可以用|| 对字符串进行拼接

select 'abc'||'d' from dual;

在这里插入图片描述
查询结果同上。

数值函数

函数说明
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;

在这里插入图片描述

日期函数

函数描述
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;

在这里插入图片描述
（3）日期截取 TRUNC

select TRUNC(sysdate) from dual;

在这里插入图片描述

-- 按年度截取
select TRUNC(sysdate,'yyyy') from dual;

在这里插入图片描述

select TRUNC(sysdate,'mm') 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;

在这里插入图片描述
字符串转日期 TO_DATE

select TO_DATE('2017-01-01','yyyy-mm-dd') 日期 from dual;

在这里插入图片描述
（4）字符串转数字 TO_NUMBER

select to_number('100') 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 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 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;

在这里插入图片描述

行列转换

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

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 = '2012'
 group by areaid;

在这里插入图片描述
需求：按季度统计 2012 年各个地区的水费

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;

在这里插入图片描述

分析函数

以下三个分析函数可以用于排名使用。
下图为三种排名方式的举例
在这里插入图片描述
（1） RANK 相同的值排名相同，排名跳跃
需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳跃
rank() over()排序

-- 分析函数
-- 值相同，排名相同，序号跳跃
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) 排名,t.usenum from t_account t;

在这里插入图片描述
（3） ROW_NUMBER 返回连续的排名，无论值是否相等需求：对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是否相等

-- 序号连续，不管值是否相同
select row_number() over(order by usenum desc) 排名,t.usenum from t_account t;

在这里插入图片描述
用 row_number()分析函数实现的分页查询相对三层嵌套子查询要简单的多:

select * from
(select row_number() over(order by usenum desc )
rownumber,usenum from T_ACCOUNT)
where rownumber>10

在这里插入图片描述

集合运算

什么是集合运算

集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：
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;

在这里插入图片描述

差集分页

如果我们用 minus 运算符来实现分页，语句如下：

select rownum,t.* from T_ACCOUNT t where rownum<=20
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10;

在这里插入图片描述

迷失的小鹿
关注
专栏目录
Oracle查询数据
jiangjiangyy的博客
08-09 780
一、查询数据SELECT SELECT关键字表示数据的检索，它由一系列子句组成，最终检索出来的数据是由子句决定的，即检索出来的数据必须满足所有子句的限制。 SELECT语句按照复杂程度可分为简单查询、WHERE条件查询、多表查询、子查询等。 1、SELECT （1）语句语法 SELECT [DISTINCT|ALL] select_list FROM table_list [where_...
Oracle 数据库 计算一张表每个时间段（月、季等）表内全部数据的总数以及当段时间内的总数
H_King_H的博客
07-13 3545
注：本文中的SQL语句，皆在Oracle数据库中验证可用，几大主流数据库的SQL语句相近，其余数据库，各位可自行修改验证~ 需求： 一张表，第一个月录入数据100条，第二个月录入数据150条，第三个月录入数据200条； 需要得出的结果：1  100  2  250   3  450 以下是每段时间内，各自的数据总数的SQL   SELECT DISTINCT HIRE_YEAR_MO...
【Oracle】数据查询
cxxdcyy
12-09 610
1. select语句
Oracle数据查询
qq_69555789的博客
05-10 1206
comment on column 表名.列名 is '自定义'; -------添加备注 去重复数据项 select distinct d.sex性别 from students d; distinct 查询关键字 作用将去除被限制的查询字段列重复的数据 查询结果排序 oracle 查询语句中使用order by 语句对查询结果进行排序 asc 设置自然排序（正序|升序）此次可省略关键字asc desc 设置倒序排序（倒序|降序） select * from students order.
oracle查数据,oracle数据查询
weixin_39608613的博客
04-12 163
ios oc 和 swfit 用dispatch&lowbar;once 创建单例网上已经有方法了,我这里就是抄了下,原文链接 http://bj007.blog.51cto.com/1701577/649413 http://blog.csdn.net/u010124617/ar ...Problem list不定时更新,发现好题目但是没时间写的就添加,写完就删除. hdu5732求树...
Oracle 基本数据查询
EERINESS
07-09 368
/* DISTINCT 关键字消除取值重复的记录行 */SELECT DISTINCT ANIMAL_NAME FROM ANIMAL/* 查看前五条数据 */SELECT * FROM ANIMAL WHERE ROWNUM &lt;= 5;/* 在确定范围内查询 */SELECT ANIMAL_NAME,ANIMAL_REGDATE FROM ANIMAL WHERE ANIMAL_REGDA...
ORACLE查询表最近更改数据的方法
12-16
在Oracle数据库管理中，了解如何查询表的最近更改数据对于跟踪和审计数据库的变化至关重要。当进行项目修改，如增加字段、删除字段或修改表的注释等操作时，记录这些变动变得尤为重要，尤其是在多版本环境或者团队...
查询Oracle数据库表空间和数据文件方法
03-03
下面以oracle9i为例，详细介绍查询Oracle数据库表空间信息和数据文件信息的方法。一、查看Oracle数据库中表空间信息的方法1、查看Oracle数据库中表空间信息的工具方法：使用oracleenterprisemanagerconsole工具，这...
oracle数据仓库国宝级资料(全套)
09-19
oracle数据仓库国宝级资料(全套) 1、Oracle+10g数据仓库实践--数据仓库基础.pdf 2、Oracle+10g数据仓库实践--总体方案.pdf 3、Oracle+10g数据仓库实践--方案的总体优势.pdf 4、Oracle+10g据仓库实践--数据仓库工具的...
Oracle数据表与查询
03-27
Oracle数据表与查询 菜鸟可以载下看看。老师给的文档。
实验1——Oracle数据查询
qq_41007733的博客
10-25 1447
一、实验题目 Oracle数据查询设计 二、实验目的 熟悉统计查询、子查询、外连接查询、递归查询等高级查询设计。 三、实验内容 1、编写SQL创建视图view_getTeacherbyDeptID，获取信息技术学院及其下属部门的所有教师信息。 create or replace view view_getTeacherbyDeptID as select * from teacher where...
【ORACLE】数据库的查询
最新发布
2301_76210107的博客
05-06 1645
【例1】SELECT 姓名 AS NAME,地址 AS ADDRESS,电话号码 AS PHONENUMBER FROM EMPLOYEES EHERE SEX=1;完全外连接（FULL OUTER JOIN）：结果表中除了满足连接条件的行外，还包括两个表所有行。左外链接（LEFT OUTER JOIN）：结果表中除了满足连接条件的行外，还包括左表的所有行。外连接的结果不但包含满足连接条件的行，还包括相应表中的所有行。结果表中包含编号相同的EMPLOYEES表中的姓名列和SALARY表中的所有列。
ORACLE——数据查询
nikerxu的博客
12-22 672
数据查询功能大概是SQL语句中使用最频繁的语句。Select [all|distinct] top n[percent] with ties select_list [info[new table name]] [from{table_name|view_name}[optimizer_hints]] [,{table_name2|view_name2}[optimizer_hints]]] [wh
Oracle（一）数据查询
Nancy1709的博客
03-27 699
https://blog.csdn.net/wzj_110/article/details/80443385 一、简单查询 1.清屏：HOST CLS 2.显示当前用户：SHOW USER 3.显示当前用户下的所有的表名称:SELECT * FROM tab 4.设置表的显示格式： 查询列宽：SHOW LINESIZE; 设置列宽：SET LINESIZE 300; 设置每页显示的数据量：SET ...
【oracle数据库】最全最详细的数据库查询
热门推荐
打不倒的程序猿
07-05 1万+
必读在前言这我们先了解下查询的基本情况，这是一篇关于查询的文章，你想要知道的查询知识都在这！如果嫌文章字多，可以根据目录跳转快速阅读！！！ 查询是数据的一个重要操作。用户发送查询请求，经编译软件变异成二进制文件供服务器查询，后返回查询结果集给用户，查询会产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正的存储，每次执行查询只是从数据表中提取数据，并按照表的形式显示出来。 **执行过程**：from--where -- group by– having– select-- order by
oracle查询sql记录,Oracle SQL查询记录
weixin_32123259的博客
04-04 360
如果您使用的是现代版本的数据库(9i或更高版本),并且您具有企业版许可证,则可以使用细粒度审核.它允许我们通过定义的策略以非常低的粒度级别来审核用户查询.要捕获SQL文本和绑定变量,您需要在添加FGA策略时适当地设置AUDIT_TRAIL参数. Find out more.“i’m using an 11g standard, so auditing functions are not suppo...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值