一些Oracle相关函数和关键字的思考总结

一、函数

1、trunc截取函数

trunc：类似截取函数，按指定的格式截取输入的数据，一般用在日期和数值的截取上：

1. 日期 ：trunc( date [, fmt] ) --date：日期值；fmt：日期格式；

/*这里只列出比较常用的*/
--默认是返回当天日期
select trunc(sysdate) from dual;
--返回当年的第一天
select trunc(sysdate,'yy') from dual;  --2022/1/1
--返回当月第一天
select trunc(sysdate,'mm') from dual;  --2022/9/1
--返回当天日期
select trunc(sysdate,'dd') from dual;  --2022/9/9
--返回当前星期的第一天，即星期天
select trunc(sysdate,'d') from dual;  --2022/9/4

2. 数值 ：trunc( number [ , decimals ])；

注意：数值的截取不采用四舍五入的方式

--截取小数点后两位
select trunc(123.567,2) from dual;   --123.56
--从右往左数，小数点左边第二位开始，补0
select trunc(123.567,-2) from dual;   --100

2、日期函数

1. 日期直接 + 或 - N
   注意：date’2022-09-02’ 的含义：‘2022-09-02 00:00:00’
   （这里补充一下：注意 a <= date’2022-08-31’，这样写是取不到08-31这一天的，可以理解吧）

/*这里只列出比较常用的*/
--日期加一天
select (date'2022-09-02' + 1) from dual;  --2022/9/3
--日期减一天
select (date'2022-09-02' - 1) from dual;  --2022/9/1
--加一个小时。若加N个小时，就是N/24
select (date'2022-09-02' + 1/24) from dual;  --2022/9/2 1:00:00
--减一个小时
select (date'2022-09-02' - 1/24) from dual;  --2022/9/1 23:00:00
--加一分钟。若加N分钟，就是N/(24 * 60)
select (date'2022-09-02' + 1/(24*60)) from dual;  --2022/9/2 0:01:00
--减一分钟
select (date'2022-09-02' - 1/(24*60)) from dual;  --2022/9/1 23:59:00
--加一秒。若加N秒，就是N/(24 * 60 *60)
select (date'2022-09-02' + 1/(24 * 60 *60)) from dual;  --2022/9/2 0:01:00
--减一秒
select (date'2022-09-02' - 1/(24 * 60 *60)) from dual;  --2022/9/1 23:59:00

2. 两个日期的差值

• 两个日期相差的天数：date1 - date2

--相差的天数
select (date'2022-09-02' - date'2022-09-01') from dual;  --1
--相差的小时数
select (date'2022-09-02' - date'2022-09-01') * 24 from dual;  --24
--相差的分钟数
select (date'2022-09-02' - date'2022-09-01') * 24 * 60 from dual;  --1140
--相差的秒数
select (date'2022-09-02' - date'2022-09-01') * 24 * 60 * 60 from dual;  --86400

• 两个日期相差的月份数：MONTHS_BETWEEN( date1, date2) : date1 - date2
  不建议使用：这个有bug，得出的结果是按照每个月31天计算的

--相差的月份数
--不同年的同一个月份得出正确值
select months_between(date'2022-09-09',date'2020-09-09') from dual; --24
--看看2022-02的，这个月份只有28天，但是天数不等于结果直接乘28天
select months_between(date'2022-02-09',date'2022-02-01') from dual;   --0.258064516129032
select months_between(date'2022-02-09',date'2022-02-01') * 28 from dual; --7.2258064516129
select months_between(date'2022-02-09',date'2022-02-01') * 31 from dual; --8

3、字符串相关函数

loacate( substr, str) /position(substr in str) ：返回子串substr在str中第一次出现的位置，若无返回0
left( str, length) ：从左边开始截取str，截取length长度
right( str, length) ：从右边开始截取str，截取length长度
substring_index( str, substr, n)：返回substr在str中第n次出现之前的字符串
substr( str, n, m)：返回字符串str从第n个字符开始，共截取m个
substr( str, pos) ：返回从pos位置开始到最后的所有str字符串
replace( str, n, m)：将str字符串中‘n’字符用‘m’字符替换
length( str ) ：统计字符串长度

4、条件函数

name	id
王五	001
李四	002

我有这么个需求，当name为李四的，我要将他的状态展示为’社牛‘。

select name, id ,decode(name,'李四','是','否') as '是否社牛' from table2

结果如下：

name	id	是否社牛
王五	001	否
李四	002	是

decode(expression, search, result [, default]) ：if ( expression = search) then result [else defualt]
eg：decode(name, ‘李四’, ’是‘, ’否‘)
if( name= ‘李四’, ’是‘, ’否‘)
case when name = ‘李四’ then ’是‘ else ’否‘ end case
以上三个达成的效果是一样的，都是当name为’李四‘的时候赋值’是‘，否则赋值’否‘，其中decode是oracle独有的

5、合并函数 wmsys.wm_concat()

当字段a相同，将其字段b的值合并为一行，并用,隔开，结合group by使用
eg：select a,to_char(wmsys.wm_concat(b))
from table1
group by table1.a
此函数要用group by 对其中相同的字段进行分组，比如下面例子中的name。
需求：将表table3中name相同的，购买的所有产品合并在一个单元格，并用‘，’隔开，例如：“张三 花,巧克力” 的形式

name	product
张三	花
王五	水
王五	面包
张三	巧克力

select name, tochar(wmsys.wm_concat(trim(product))) newpro
from table3 group by table3.name;

name	newpro
张三	花，巧克力
王五	水，面包

6、取整函数

1. ceil(x)函数
   天花板函数，顾名思义返回大于或等于x的最小整数

select ceil(3.46) from dual;   --    4
select ceil(-3.46) from dual;   --  -3

2. floor函数
   地板函数，顾名思义，返回小于或等于x的最大整数

select floor(3.46) from dual;   --    3
select floor(-3.46) from dual;   --  -4

3. roud()函数
   四舍五入取大约值

select round(3.46) from dual;   --    3
select round(-3.56) from dual;   --  -4

7、NVL()函数

NVL(string1, replace_with) ：若string1为空，返回replace_with；若string1不为空，返回string1本身的值

8、最值函数 Greatest、Least

greatest()：返回N个参数中最大值
least()：返回N个参数中的最小值
规则：
1.若参数中含有NULL，则直接返回NULL，不作比较
2. 若参数由数字和字符串组成，则函数将他们作为数字比较

select greatest(10,20,30) from dual;  --30
select least(10,20,30) from dual;   --10
--参数中含NULL
select greatest(10,null,30) from dual;   --null
select least(10,null,30) from dual;     --null

那就有人问了，如果我硬要比较，null的情况我就当是0，要怎么做？那么你可以事先判断一下
nvl()函数 、 if 、case when等判断函数都可以实现
比如，我现在有Q1 Q2 Q3我要获取最大值，但是我并不知道哪个是空的

select greatest(nvl(Q1,0), nvl(Q2,0), nvl(Q3,0)) from dual;

9、开窗函数

聚合函数+over()分析函数：
over()括号里面就是定义窗口的内容，partition by 根据某个字段分组
注意：在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行
eg：sum(score) over(partition by name)：先根据name分组，后将每个组（每个窗口）的里的字段score进行求和
看到这里是不是觉得，这不就是聚合函数+group by函数吗？有啥优势呢？
举个栗子~

需求：我目前有一个工资表，我要获取员工所在部门、当月工资和部门总工资。

name	dept	salary
张三	A	2000
王五	B	3000
李四	A	3000

1. 直接使用group by 函数

create table ryy_test_a(
       name varchar2(10),
       dept varchar2(10),
       salary number
);
select dept,sum(salary) total_salary
from ryy_test_a
group by dept

dept	total_salary
A	5000
B	3000

而要实现需求中显示员工所在的部门和当月工资，还需要进行多个嵌套查询

select name,dept,salary,(select total_salary 
                         from (select dept,sum(salary) total_salary
                               from ryy_test_a
                               group by dept) tmp1
                         where tmp1.dept = tmp2.dept) total_salary
from ryy_test_a tmp2

2. 使用开窗函数

select name,dept,salary,sum(salary)over(partition by dept) total_salary
from ryy_test_a

上述的两个结果都是下表，但是从简洁程度上看，开窗函数还是比较可观的。

name	dept	salary	total_salary
张三	A	2000	5000
王五	B	3000	3000
李四	A	3000	5000

分组排序函数 row_number()over(partition by … order by…)
先根据dept分组，后每个组内部进行降序排序

select name,dept,salary,row_number()over(partition by dept order by salary desc) rank
from ryy_test_a

name	dept	salary	rank
李四	A	3000	1
张三	A	2000	2
王五	B	3000	1

那现在给你个需求：把每个部门中最高工资的员工信息取出来，是不是一下子就能得到答案？

--desc是降序排序，而asc是升序排序，可以根据需求写升降序排序，从而获取所需数据
select * from(
       select name,dept,salary,row_number()over(partition by dept order by salary desc) rank
       from ryy_test_a
       )
where rank < 2

name	dept	salary	rank
李四	A	3000	1
王五	B	3000	1

10、字符串连接函数

1. ||连接运算符：string1 || string2 -->string1string2
2. concat() ：concat(‘A’,‘B’) -->‘AB’

二、定义或关键字相关

1、truncate、delete、drop比较

1. truncate、drop为DDL语句；delete为DML
2. truncate 只用于表；delete、drop可用于表、视图
3. truncate 会清空表的所有行，但表结构及其约束、索引等不变；drop会删表结构、约束、索引;delete删除表数据，但是会保留表结构
4. truncate不会激活触发器，delete会激活

2、DDL和DML区别

DDL：对数据库中对象（比如database、table）–create drop alter
DML：对数据库中数据 --select delete insert update

（1）DDL：建库、表，设置约束 create alter drop

1. 创建数据库 create database…

--创建一个名为 A 的数据库
create database if not exists A  character set utf-8;

2. 创建表格 create table …

--在数据库A中，创建一个名为 table1 的表
use A create table  is not exists table1 ( 
   id int, 
   name varchar2(30)
   );

3. 更改表结构 alter table table1 drop/alter/modify column column_name …

--查看表结构
desc table1;
--删除列名为name的列
alter table table1 drop column name;
--增加列名为name的列
alter table table1 add column name varchar2(30);
--将列名为name的修改为列名user_name
alter table table1 rename column name to user_name;
--将列名为name的字段大小改为50
alter table table1 modify name varchar2(50);
--更改列名的默认值
alter table table1 modify name dedault A;

4. 删除表、数据库 drop table/database

--删除名为table1的表
drop table table1;
--删除名为A的数据库
drop database A;

（2）DML 增删改查 select delete insert update

1. 查询

--一般查询都直接select 列名，因为select * 的效率很低
select * from table1;
 select id, name from table1;
 --去重查询
 select distinct id,name from ryy_test_a;

2. 删除

 delete from table1 ;
 delete from table1 where name = '李四';

3. 插入

create table ryy_test_a(
      id number,
      name varchar2(20)
)
create table ryy_test_b(
      id number,
      name varchar2(20),
      age number
)
select * from ryy_test_a;
select * from ryy_test_b;
--插入一行
insert into ryy_test_a(id,name) values(1,'张三');
insert into ryy_test_a values(2,'李四');
insert into ryy_test_a(name) values('王五');

--插入多行
insert all 
      into ryy_test_a values(4,'小明')
      into ryy_test_a values(5,'大黄')
select 1 from dual;

insert all 
      into ryy_test_b values(6,'修勾',2)
      into ryy_test_b values(7,'修喵',3)
select 1 from dual;
--从一个表中select出来的值insert到
insert into ryy_test_a(
      id,
      name
)select
      id,
      name
from ryy_test_b;

4. 更新

--普通定值更新
update ryy_test_b 
      set age = 10,
          name = '大白'
where name = '修勾'
--用一个表的结果更新另一个表
update ryy_test_b
      set (name,age) = (
          select name, id
                 from ryy_test_a
          where name = '大黄'
      )
where name = '修喵'
--多个条件更新值
update A
SET   A.aa = (
   case when A.bb = a then '1'
   when A.bb = b then '2'
   else '0'
   end
)

3、join

现在有student表，如下：

id	name	teacher_id
001	张三	t001
003	王五	t002

teacher表如下：

teacher_id	teacher_name	course_id
t001	A老师	c01
t001	A老师	c02
t001	A老师	c03
t003	C老师	c05

select * from

1. student a left join teacher b on a.teacher_id = b.teacher_id

id	name	teacher_id	teacher_id	teacher_name	course_id
001	张三	t001	t001	A老师	c01
001	张三	t001	t001	A老师	c02
001	张三	t001	t001	A老师	c03
003	王五	t002

这里显然看得到，left join会导致左表增加一定的数据量。怎么就增加了数据量呢？你可以尝试
select a.* from student a left join teacher b on a.teacher_id = b.teacher_id ，此时你会发现a.*的数据变成了四条，看完这里是不是觉得：啊？所以呢？有啥影响啊？
那假设我的student表多了个money呢？student表变成下表

id	name	teacher_id	money
001	张三	t001	100
003	王五	t002	150

left join 的结果表就变成了

id	name	teacher_id	money	teacher_id	teacher_name	course_id
001	张三	t001	100	t001	A老师	c01
001	张三	t001	100	t001	A老师	c02
001	张三	t001	100	t001	A老师	c03

这个时候你要求每个学生有多少钱，本来张三的money实际只有100，但是此时你sum(money)，一下子变成了300了，是不是就数据翻几倍了。那怎么解决这个问题呢？你可以先预处理student 表，简单来说就是先在student表sum(money)，后面再进行左关联，关联后的表就不再需要进行sum了，也就不会出现money数据翻倍的情况。

2. student a right join teacher b on a.teacher_id = b.teacher_id

id	name	teacher_id	teacher_id	teacher_name	course_id
001	张三	t001	t001	A老师	c01
001	张三	t001	t001	A老师	c02
001	张三	t001	t001	A老师	c03
			t003	C老师	c05

3. student a inner join teacher b on a.teacher_id = b.teacher_id

id	name	teacher_id	teacher_id	teacher_name	course_id
001	张三	t001	t001	A老师	c01
001	张三	t001	t001	A老师	c02
001	张三	t001	t001	A老师	c03

4. student a full join teacher b on a.teacher_id = b.teacher_id

id	name	teacher_id	teacher_id	teacher_name	course_id
001	张三	t001	t001	A老师	c01
001	张三	t001	t001	A老师	c02
001	张三	t001	t001	A老师	c03
003	王五	t002
			t003	C老师	c05

我以前有个错误的认知，以为left join 的结果一定是左表的行数啊，其实不是的，根据前面的left join 表结果来看就可以明白了。而right join 也同理。因此join的结果不一定等于主表。

4、union & union all

1. union : 相当于or，自带去重

select * from t1 union select * from t2

查询的结果：t1和t2的全部集合，但是去除重复部分

2. union all：全部集合，相当于并集

select * from t1 union all  select * from t2

查询的结果：t1和t2的全部集合，不去重