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

• 1 运算和逻辑操作符

  • 1.1 运算符

  • 1.2 关系比较

  • 1.3 条件

  • 1.4 与空判断

• 2 自带函数

  • 2.1 多行（组）函数

  • 2.2 单行函数

  • 2.3 字符函数

  • 2.4 日期函数

  • 2.5 转换函数

  • 2.6 通用函数

• 3 辅助查询的关键字

  • 3.1 条件取值

  • 3.2 排序

  • 3.3 分组

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

  • 3.5 去重

  • 3.6 in/not in

  • 3.7 some/any

  • 3.8 all

  • 3.9 exists

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

• 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.141592653) from dual; —>3

• 指定四舍五入保留几位小数
  round(数值型,保留小数位数)
  select round(3.141592653,3) from dual; —>3.142

• 截断小数位
  trunc(数值型)
  select trunc(3.141592653) from dual;—>3

• 指定截断保留几位小数
  trunc(数值型,保留小数位数)
  select trunc(3.141592653,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(‘示例数据123456789’, 3, 7) from dual; —>数据12345
  select substr(‘示例数据123456789’, 4) from dual;—>据123456789

• 索引字符串
  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(‘18888888888’, substr(‘18888888888’, 4, 7), ‘****’) from student;”，这样的号码，会在一开始就被替换掉。

• 拼接字符串
  concat(a1,a2) 或 a1 || a2
  select concat(‘123’,‘456’) from dual; —>123456
  select ‘123’ || 456 from dual;—>123456

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

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(‘88888888’,13,‘138’) from dual;—>1381388888888

• 去除两侧空格

  • 默认把两侧空格删除
    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 日期函数

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

Oracle：yyyy-mm-dd hh24:mi:ss
相当于Java：yyyy-MM-dd HH:mm:ss

• 日期与日期（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.08565226 两个月，小数点后可不计

• 计算给定日期所在月份的最后一天
  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(‘66666666’) from dual;—>66666666

• 将数值类型或date类型转成字符类型的数字
  to_char():
  select to_char(123456) from dual; —> 123456
  常用在货币单位，格式化字符串，第二个参数只起一个参照作用
  to_char(数值型, 要转为的货币格式的字符串):
  select to_char(21123123123123.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(‘20181211’,‘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(‘20181211’,‘yyyy-mm-dd’),‘yyyy’) from dual;
select to_char(to_date(‘20181211’,‘yyyy-mm-dd’),‘mm’) from dual;
select to_char(to_date(‘20181211’,‘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 （升序[默认升序]）

select ename,deptno,sal from emp order by deptno desc,sal asc;
先按deptno降序排列，若deptno相等，按照sal升序排列。

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过滤掉不符合的数据来提高效率。

select deptno,avg(sal),sum(sal) from emp group by deptno having avg(sal) > 2000;
从emp表中group by按部门编号聚合统计之后，筛选出平均sal大于2000的组，结果集显示部门编号、平均sal、sal总数。

3.5 去重

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

select distinct deptno,sal from emp;
（若有两个员工，deptno都为30，且sal都为1000，即被删除）

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用在有符号的情况。

查询部门表中的员工的部门信息
select * from dept where deptno = some(select deptno from emp);

查询工资不是倒数第一的员工
select * from emp where sal > any(select sal from emp);

3.8 all

表示比所有值都大或都小

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

3.9 exists

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

select ename, sal from emp a where exists (select sal from emp b where b.deptno = 30);
先在主查询“select ename, sal from emp a”中查询出，然后在子查询中查找是否存在一条这样的数据，如果有，主查询查到的结果就可以显示出来。在这条语句中，主查询和子查询没什么关系，所以主查询的每一条查子查询时，子查询都会有数据返回，故返回所有员工的数据。

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 按字段对结果集排序（可选）