【第29天】Oracle的运算和逻辑操作符、自带函数及辅助查询的关键字

1 运算和逻辑操作符
2 自带函数
3 辅助查询的关键字
4 查询语句常见关键字的优先级（执行顺序）

1 运算和逻辑操作符

测试运算符、函数的方法这里使用Oracle中给出的dual伪表。

1.1 运算符

+（加）、-（减）、*（乘）、/（除）、mod(x,y)（取余）

1.2 关系比较

>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、=（等于）、<>（尽量使用这个作为不等号，不使用“!=”）

1.3 条件

and（且）、or（或）、between a and b（一个闭区间，>=a且<=b）

1.4 与空判断

is null（空）、is not nul（不空）

2 自带函数

函数支持嵌套使用，但在嵌套之前先搞清楚函数需要传什么类型的值。select对数据库做的操作都不会修改表结构和表数据。

2.1 多行（组）函数

也叫作聚组函数、聚簇函数。可一次操作多行（一组）数据。

在有要求全体数据都参与运算使用下列函数时，为防止空数据被忽略而不参与运算，要注意对空数据进行判断并补0，使用nvl()函数，使用方法“nvl(数值型的字段名, 0)”。

求最大值
select max(数据类型为数值型的字段名) from student;
求最小值
select min(数据类型为数值型的字段名) from student;
求平均值
select avg(数据类型为数值型的字段名) from student;
求和
select sum(数据类型为数值型的字段名) from student;
查看当前表有多少条记录
select count(*) from student;
select count(列名) from student;

2.2 单行函数

返回大于等于x的最小整数
ceil(数值型) [意为天花板]
select ceil(12.5) from dual; —>13
返回小于等于x的最大整数
floor(数值型) [意为地板]
select floor(12.5) from dual;—>12
四舍五入
round(数值型)
select round(3.) from dual; —>3
指定四舍五入保留几位小数
round(数值型,保留小数位数)
select round(3.,3) from dual; —>3.142
截断小数位
trunc(数值型)
select trunc(3.) from dual;—>3
指定截断保留几位小数
trunc(数值型,保留小数位数)
select trunc(3.,3) from dual;—>3.141
求符号位
sign(数值型) 正数：1 负数：-1 零：0
select sign(-8) from dual; —>-1
求绝对值
abs(数值型)
select abs(-8) from dual;—>8
求a的b次方
power(数值型,指数):
select power(2,3) from dual;—>8
求正平方根
sqrt(数值型)
select sqrt(9) from dual; —>3

2.3 字符函数

注意这里指的是字符，而不是字节，不论中英文、标点、数字等，均算一个字符。与Java不同的是，查找的下标从1开始。

将传入字符全转换成小写
lower(字符型)
将传入字符全转换成大写
upper(字符型)
将传入的字符首字母大写
initcap(字符型)
计算传入字符的长度
length(字符型)
截取字符串
substr(a1,a2,a3)
a1:被截取的字符串
a2:从哪个位置开始截取
a3:截取长度默认截取到最后
select substr(‘示例数据’, 3, 7) from dual; —>数据12345
select substr(‘示例数据’, 4) from dual;—>据
索引字符串
instr(a1,a2,a3,a4):
a1:被索引的字符串
a2:希望找到的字符
a3:从哪个位置开始找默认是1
a4:第几次出现默认是1
查找存在，返回目标位置第一个字的下标，若查找不存在，返回0。
select instr(‘示例数据示例数据示例数据示例数据’,‘例数据’, 6, 2) from dual; —>10
select instr(‘示例数据示例数据示例数据示例数据’,‘例数据’, 6) from dual;—>6
select instr(‘示例数据示例数据示例数据示例数据’,‘例数据’) from dual;—>2
完全替换
replace(a1,a2,a3):
a1:原字符串
a2:被替换的字符串
a3:替换的字符串
注意a2在这里只是作为参照物，用a2在原字符串逐个字符对比，遇到一致的就换掉了，所以在替换手机号时，不能“select replace(phone, substr(phone, 4, 7), ‘’) from student;”，若遇到“select replace(‘’, substr(‘’, 4, 7), ‘’) from student;”，这样的号码，会在一开始就被替换掉。
拼接字符串
concat(a1,a2) 或 a1 || a2
select concat(‘123’,‘456’) from dual; —>
select ‘123’ || 456 from dual;—>

例：查询手机号中间四位以代替

select concat(concat(substr(phone,1,3),’’),substr(phone,8))from student where length(phone) = 11;

select substr(phone,1,3) || ‘’ || substr(phone,8) from student where length(phone) = 11;

左侧补全/右侧补全
lpad(a1,a2,a3)
rpad(a1,a2,a3)
a1:希望补全的字符串
a2:补全到多少位
a3:以哪个字符来补
select lpad(‘’,13,‘138’) from dual;—>88
去除两侧空格
- 默认把两侧空格删除
  trim()
  trim(a1 from a2):默认把a2两侧去除a1
  select trim(‘a’ from ‘aaaabsadaa’) from dual;—>bsad
- 把左侧空格删除
  ltrim()
  ltrim(a1,a2)
- 把右侧空格删除
  rtrim()
  rtrim(a1,a2)
  a1:被去除的字符串
  a2:去除的字符串
  select rtrim(’ abc ') from dual; —> abc

2.4 日期函数

日期可以加减运算（整数），单位是天，日期与日期不能相加，但可以相减，日期与数字可以相加。

日期与日期（date类型）相减
select sysdate - hiredate from emp;
日期与数字加减
select sysdate + 1.5 from dual;—>2018-12-15 03:24:04
select sysdate - 1.5 from dual;—>2018-12-12 03:25:05
在某个日期上增加几个月
add_months(d1,d2)
d1:date类型的值
d2:整数值
select add_months(sysdate,2) from dual; —>2019-02-13 15:41:07 加了两个月
计算两个日期之间的月份
months_between(d1,d2)
计算方式：d1-d2，返回几个月
select months_between(sysdate,‘2018-10-11’) from dual; —>2.0 两个月，小数点后可不计
计算给定日期所在月份的最后一天
last_day()
计算给定日期所在月份的倒数第二天
select last_day(sysdate)-1 from dual;—>2018-12-30 15:44:38
从当前日期开始得到到未来第一个d2所指的星期几所对应的date类型日期
next_day(d1,d2)
d1:日期
d2:周中的某天
select next_day(sysdate,‘星期日’) from dual;—>2018-12-16 15:46:50

2.5 转换函数

将一个字符类型的数字变成数值类型
to_number()：
select to_number(‘’) from dual;—>
将数值类型或date类型转成字符类型的数字
to_char():
select to_char() from dual; —>
常用在货币单位，格式化字符串，第二个参数只起一个参照作用
to_char(数值型, 要转为的货币格式的字符串):
select to_char(123.12,‘999,999,999,999,999.99’) from dual;—>21,123,123,123,123.12
日期转换
to_char(date类型日期,要转为的格式)
select to_char(sysdate,‘yyyy-mm-dd’) from dual;—>2018-12-13
select to_char(systimestamp,‘yyyy-mm-dd hh24:mi:ss:ff3’) from dual;—>2018-12-13 16:00:19:573
将字符类型转为date类型，对比日期时，先使用to_date()转换，再进行比较。
to_date(d1,d2)
d1:字符类型的日期
d2:日期格式
select to_date(‘’,‘yyyy-mm-dd’) from dual;—>2018-12-11 00:00:00

如需要判断是不是在这个字符型时间段内
between to_date(‘起始时间（字符串）’,‘yyyy-mm-dd’) and to_date(‘截止时间（字符串）’,‘yyyy-mm-dd’)
或
字段名 >= to_date(‘起始时间（字符串）’,‘yyyy-mm-dd’) and 字段名<=to_date(‘截止时间（字符串）’,‘yyyy-mm-dd’)

取出年/月/日（运用to_date将字符型日期转换为date类型的数据，再通过to_char的日期转换特性将年月日分别取出）
select to_char(to_date(‘’,‘yyyy-mm-dd’),‘yyyy’) from dual;
select to_char(to_date(‘’,‘yyyy-mm-dd’),‘mm’) from dual;
select to_char(to_date(‘’,‘yyyy-mm-dd’),‘dd’) from dual;

2.6 通用函数

空值处理
nvl(字段，替换显示的内容)
select nvl(comm, 0) from emp;
空值处理升级版，参数必须是三个，否则参数无效
nvl2(字段，不是空显示什么，是空显示什么)
select name,nvl2(comm, comm, ‘0’) from emp;
Oracle中的三目运算符
decode(c1,c2,c3,c4…cx,cx+1)
c1:被拿来判断的值（一般是一个固定的字段名）
从c2开始，每两个参数看作一组，拿每一组的第一个参数（c2）和c1作对比，
如果相同则返回该组的第二个值（c3）；不相等则继续下一组的对比，第一个参数（c4）和c1作对比，如果相同则返回该组的第二个值（c5），不相等则继续下一组的对比…
如果相同则返回该组的第二个值（c3）
第一次判断：c2==c1?c3:
第二次判断：c4==c1?c5:
如果参数个数是偶数个，且最终判断没有相同的值，则返回最后一个参数的值；
如果参数个数是奇数个，且最终判断没有相同的值（最后一个对比时已没有第二个值了），则返回空。
如
select ename, deptno, sal, decode(deptno, 10, sal+100, 20, sal+200) a from emp;
若deptno=10，返回sal+100；若dept=20，返回sal+200，否则为返回空。

3 辅助查询的语句

3.1 条件取值

(case --开始条件取值
when --若条件成立
then --则
else --默认值（否则）
end) --结束

 select ename,deptno,sal, --选取结果集显示的列 (case deptno --deptno作为条件 when 10 then sal+100 --若deptno为10，则返回sal+100 when 20 then sal+200 --若deptno为20，则返回sal+200 else sal-100 --否则返回sal-100 end) a --结束 from emp; --在emp表查询

3.2 排序

order by 需排序字段 desc（降序） asc （升序[默认升序]）

3.3 分组

group by 被分组的字段
根据在某一个列上或多个列上的值，将列上相同的值划分为一组，该表就可以分为多个组。group by后面加having而不是where，where要在group by之前执行，写在前面。

注意：
Oracle中如果以字段 A分组，那么只能在select输出显示结果集时，只能显示A或使用多行（组）函数来统计的其他字段，不能显示其他字段！
select deptno,avg(sal) from emp group by deptno;

3.4 分组后筛选满足条件的组

having 条件

跟在group by后面筛选满足条件的组，使用having效率会降低，所以尽量要在group by之前使用where过滤掉不符合的数据来提高效率。

3.5 去重

distinct 字段名（后面可以加多字段，支持单列、多列去重。多列去重的意思是对比这几个列，字段都重复，就会被执行去重）

3.6 in/not in

in
将子查询作为结果缓存下来，子查询对主查询的结果集逐一进行hash连接（引自_雨@cnBlog），以此判断是否存在，此时內表作为索引。查询结果逐个进行hash连接，两个查询耦合度高。
条件 in (值1, 值2, …值n)
条件 in select语句（若再接其他select语句需要使用and/or连接后再重复“条件 in select语句”）
相当于：条件 = 值1 or 条件 = 值2 or … or 条件 = 值n
not in
表示条件不能是查询结果中的任何一个值
条件 not in (值1, 值2, …值n)
条件 not in select语句（若再接其他select语句需要使用and/or连接后再重复“条件 not in select语句”）
相当于：条件 <> 值1 and 条件 <> 值2 and … and 条件 <> 值n

3.7 some/any

some表示满足一个条件即可，some一般适用于“=”的情况；any和some一样表示满足一个条件，只是any常用于大于、小于不等于的情况（类比英文）。

这两个用法与in一致，区别为in用在无符号的情况，some/any用在有符号的情况。

3.8 all

表示比所有值都大或都小

等价关系：
>any 与 >min
<any 与 <max
>all 与 >max
<all 与 <min

3.9 exists

exists：存在
not exists：不存在
对外表作loop循环，每次loop再执行子查询看其结果是否存在，存在返回true，这条主查询就会最终在结果集显示，此时外表作为索引。主查询结束后，只是查看一下子查询，两个查询耦合度低。

3.10 in和exists的比较及使用场景

关键字名称	原理	使用场景
in	将子查询作为结果缓存下来，子查询对主查询的结果集逐一进行hash连接，以此判断是否存在，此时內表作为索引。查询结果逐个进行hash连接，两个查询耦合度高。	子查询数据量小于主查询
exists	对外表作loop循环，每次loop再执行子查询看其结果是否存在，存在返回true，这条主查询就会最终在结果集显示，此时外表作为索引。主查询结束后，只是查看一下子查询，两个查询耦合度低。	子查询数据量大于主查询

总之使用索引时，想提高效率，主要是减少索引的节点个数。

4 查询语句常见关键字的优先级（执行顺序）

一条查询语句的优先级（执行顺序）究竟是怎样的？

select 列名 from 表名 where 条件 group by 字段名 having 条件 order by 字段名;

从高到低的执行顺序（若有子查询则从左到右依次执行）：
1.from 先有要查询的表（数据来源）（必选）
2.where 在分组前先使用where对数据进行过滤（可选）
3.group by 分组（可选）
4.having 有分组才会有having，筛选满足条件的组（可选）
5.select 要从结果集中查找什么字段信息并显示（必选）
6.order by 按字段对结果集排序（可选）

今天的文章【第29天】Oracle的运算和逻辑操作符、自带函数及辅助查询的关键字分享到此就结束了，感谢您的阅读。