ORACLE 数据库

 

第 2 章ORACLE 查询

 

 

 

传智播客.黑马程序员

一、单表查询

（一）简单条件查询

 

1. 精确查询

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

 

查询结果：

 

2. 模糊查询

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

 

 

查询结果：

 

3.         and 运算符

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

 

 

 

查询结果：

 

4.         or 运算符

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

 

 

 

查询结果：

 

5.         and 与 or 运算符混合使用

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

为 3 的记录。语句：

 

 

 

查询结果：

 

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

 

6.         范围查询

需求：查询台账记录中用水字数大于等于 10000，并且小于等于 20000 的记录我们可以用>= 和<=来实现，语句

 

 

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

 

7.         空值查询

需求：查询 T_PRICETABLE 表中 MAXNUM 为空的记录语句：

 

 

查询结果：

 

需求：查询 T_PRICETABLE 表中 MAXNUM 不为空的记录语句：

 

 

查询结果：

 

 

（二）去掉重复记录

 

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

 

 

 

 

 

 

 

 

（三）排序查询

 

1. 升序排序

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

 

 

查询结果：

2. 降序排序

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

 

查询结果：

 

（四）基于伪列的查询

 

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

1          ROWID

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

查询语句：

查询结果如下：

 

Ps： 其实我们用Oracle的话可以不用建立主键的，这个伪列就相当于一个唯一id；而且用ROWID作为主键id去查询的话效率更高，因为它是物理地址，可以直接定位

我们可以通过指定 ROWID 来查询记录

查询结果如下：

 

 

2            ROWNUM（在讲分页查询的时候会详细讲）

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

 

数。

查询语句：

查询结果如下：

 

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

 

（五）聚合统计

 

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

1.    聚合函数

（1） 求和     sum

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

 

查询结果如下：

（2） 求平均 avg

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

 

查询结果如下：

 

（3） 求最大值 max

 

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

 

查询结果如下：

 

（4） 求最小值 min

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

 

查询结果如下：

 

 

（5） 统计记录个数 count

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

 

查询结果如下：

 

 

2.    分组聚合 Group by

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

 

 

查询结果：

 

 

 

 

3.    分组后条件查询 having

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

 

 

查询结果：

 

 

二、连接查询

（一）多表内连接查询

 

（1）  

（3） 需求：查询显示业主编号，业主名称，业主类型名称，如下图：

查询语句：

 

 

（4） 需求：查询显示业主编号，业主名称、地址和业主类型，如下图

 

 

 

 

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

 

 

 

 

 

 

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

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

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

 

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

语句：

 

 

（二）左外连接查询

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

分析：我们要查询这个结果，需要用到 T_OWNERS（业主表） ，T_ACCOUNT

（台账表）   按照查询结果，业主表为左表、账务表为右表。按照 SQL1999 标准的语法，查询语句如下：

 

 

 

 

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

 

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

 

（三）右外连接查询

 

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

 

 

 

 

 

SQL1999 标准的语句

 

ORACLE 的语法

 

 

三、子查询

（一）where 子句中的子查询

 

1. 单行子查询

l  只返回一条记录

l   

l  单行操作符

 

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

 

 

 

 

 

 

查询结果：

 

平均值为：

 

 

1. 多行子查询

l  返回了多条记录

l  多行操作符

 

in 运算符

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

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

 

 

查询结果如下：

 

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

 

 

 

 

 

语句：

查询结果：

 

（3）   需求：查询地址不含有“花园”的业主的信息语句：

 

 

 

查询结果：

 

 

（二）from 子句中的子查询

 

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

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

语句：

 

 

 

 

 

 

 

 

 

 

 

查询结果如下：

 

 

（三）select 子句中的子查询

 

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

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

 

 

 

查询结果如下：

 

 

（2）    需求：列出业主信息，包括 ID，名称，所属地址，所属区域。语句:

 

 

 

 

 

 

 

 

 

 

查询结果如下：

 

 

四、分页查询

（一）简单分页

 

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

分析：我们在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询我们首先显示前 10 条记录，语句如下：

 

显示结果如下：

 

 

 

 

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

查询结果：

 

 

嗯？怎么没有结果？

这是因为 rownum 是在查询语句扫描每条记录时产生的（依次赋值到此表字段的），所以不能使用“大于” 符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。

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

 

查询结果如下：

 

（二）基于排序的分页

 

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

 

 

 

查询结果如下：

 

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

 

 

所以推断刚才的语句是错误的！那为什么是错误的呢？ 我们可以先单独执行嵌套查询里面的那句话

 

 

你会看到查询结果如下：

 

 

 

 

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

 

那该如何写呢？

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

语句如下：

 

 

结果如下：

 

五、单行函数

（一）字符函数

 

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

 

TRANSLATE	执行字符串搜索和替换
TRIM	裁剪掉前面或后面的字符串
UPPER	将字符串变为大写

 

 

常用字符函数讲解：

（1）          求字符串长度 LENGTH

语句：

select length('ABCD')这么写在Oracle中会报错（但是在MySQL中不会报错）因为Oracle严格遵守了sql语法，即select后边必须跟一个from关键字，但是现在后边跟哪个表呢？跟一个只有一行一列的伪表dual，这个表存在的意义就是凑数

显示结果为：

 

 

（2）          求字符串的子串 SUBSTR

语句：

 

显示结果为：

 

 

（3）          字符串拼接 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

语句：

查询结果如下：默认是整数

 

语句：

 

查询结果如下：

 

 

（2）   截取函数 TRUNC

语句：

 

查询结果：

 

 

语句：

 

 

 

 

 

 

 

这是保留小数截取

 

（3）   取模     MOD

语句：

 

 

结果：

 

 

（三）日期函数

 

函 数	描    述
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’,’	给出在 this 时区=Other 时区的日期和时间

 

other’)
ROUND(date,’format’)	未指定 format 时，如果日期中的时间在中午之前，则将日期中的时间截断为 12 A.M.(午夜，一天的开始),否 则进到第二天。时间截断为 12 A.M.(午夜，一天的开始),   否则进到第二天。
TRUNC(date,’format’)	未指定 format 时，将日期截为 12 A.M.( 午夜，一天的   开始).

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

 

查询结果如下：

 

常用日期函数讲解：

（1）  加月函数 ADD_MONTHS ：在当前日期基础上加指定的月语句：

 

查询结果如下：

 

（2）  求所在月最后一天 LAST_DAY

语句：

 

查询结果如下：

 

 

（3）  日期截取 TRUNC

语句：

查询结果如下：

 

语句：

查询结果如下：

 

语句：

 

查询结果如下：

 

 

 

（四）转换函数

 

函 数	描    述
CHARTOROWID	将 字符转换到 rowid 类型
CONVERT	转换一个字符节到另外一个字符节
HEXTORAW	转换十六进制到 raw 类型

 

RAWTOHEX	转换 raw 到十六进制
ROWIDTOCHAR	转换 ROWID 到字符
TO_CHAR	转换日期格式到字符串
TO_DATE	按照指定的格式将字符串转换到日期型
TO_MULTIBYTE	把单字节字符转换到多字节
TO_NUMBER	将数字字串转换到数字
TO_SINGLE_BYTE	转换多字节到单字节

 

常用转换函数讲解：

（1）       数字转字符串 TO_CHAR

语句：

查询结果：

 

 

（2）        日期转字符串 TO_CHAR

语句：

查询结果：

 

语句：

查询结果：

 

 

 

（3）        字符串转日期 TO_DATE

语句：

 

查询结果如下：

 

（4）       字符串转数字 TO_NUMBER

语句：

 

 

（五）其它函数

 

（1）          空值处理函数 NVL

用法：

NVL（检测的值，如果为 null 的值）； 语句：

 

查询结果如下：

 

需求：

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

 

语句：

 

查询结果：

 

 

（2）          空值处理函数 NVL2

用法：

NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；

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

 

语句：

 

（3）          条件取值 decode

语法：

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

语句：

 

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

 

 

还有另外一种写法：

 

 

 

 

 

 

 

 

六、行列转换

 

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

 

 

 

 

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)	十二月

 

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

 

 

 

 

语句如下：

 

七、分析函数

 

以下三个分析函数可以用于排名使用。下图为三种排名方式的举例

 

 

（1）              RANK 相同的值排名相同，排名跳跃

需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳

 

跃

语句：

结果：

 

 

（2）              DENSE_RANK      相同的值排名相同，排名连续

需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名连续

语句：

结果：

 

 

 

 

（3）              ROW_NUMBER    返回连续的排名，无论值是否相等

需求：对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是否相等

 

语句：

 

 

 

用 row_number()分析函数实现的分页查询相对三层嵌套子查询要简单的多:

查询结果如下：

 

 

八、集合运算

（一）什么是集合运算

 

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

··UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

··UNION(并集)，返回各个查询的所有记录，不包括重复记录。

··INTERSECT(交集)，返回两个查询共有的记录。

··MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

 

 

 

 

（二）并集运算

 

 

UNION ALL 不去掉重复记录

 

结果如下：

 

 

UNION 去掉重复记录

 

 

 

 

 

（三）交集运算

 

 

 

结果：

 

（四）差集运算

 

 

 

 

 

 

 

 

 

 

结果:

 

 

 

 

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

 

 

 

 

九、总结

（一）知识点总结

 

（二）上机任务布置

 

为《自来水收费系统》开发统计模块相关的功能

1. 收费日报单（总）

统计某日的收费，按区域分组汇总，效果如下：

 

 

 

 

 

 

 

 

2. 收费日报单（收费员）

统计某收费员某日的收费，按区域分组汇总，效果如下：

 

 

3. 收费月报表（总）

统计某年某月的收费记录，按区域分组汇总

 

4. 收费月报表（收费员）

统计某收费员某年某月的收费记录，按区域分组汇总

 

5. 收费年报表（分区域统计）

统计某年收费情况，按区域分组汇总，效果如下：

 

6. 收费年报表（分月份统计）

统计某年收费情况，按月份分组汇总，效果如下

 

 

 

 

 

 

7. 收费年报表（分月份统计）

统计某年收费情况，按月份分组汇总，效果如下

 

8. 统计用水量，收费金额（分类型统计）

根据业主类型分别统计每种居民的用水量（整数，四舍五入）及收费金额 ，如果该类型在台账表中无数据也需要列出值为 0 的记录 ， 效果如下：

 

 

分析：这里所用到的知识点包括左外连接、sum()、分组 group by 、round()   和

nvl()

 

9. 统计每个区域的业主户数，并列出合计

 

10. 统计每个区域的业主户数，如果该区域没有业主户数也要列出 0

如图: