oracle 常用26个函数，date函数，小白必看！（内有福利）

   ********** oracle 常用26个函数 ****************
                                                               ——老子亲笔
                                                               
-- 概念
                                            
Oracle 提供一系列用于执行特定操作的函数

-- 1.sysdate: 当前的日期包含秒
 
select sysdate from dual;       -- 在虚拟表 dual 中显示当前电脑的时间
select current_date from dual;  -- 这也是 在虚拟表 dual 中显示当前电脑的时间
select emp.*,sysdate from emp;  -- 给emp 表增加时间字段 并且还是当前的时间
select next_day(sysdate,'星期一') from dual;-- 这个是显示 电脑时间往后的时间 选择显示星期一的时间 ，运行，电脑就会自动显示 下一个星期一的时间

--2.length(str)函数：显示一个字符串的长度 结果为一个数字 （str表示一个字符串）

select length('aaa  ') from dual;     -- 这个显示结果为 5 因为字符串aaa 后面还有两个空格
select sname,length(sname)from student;--显示这个表中 sname 列的所有名字的 长度
select sname,length(sname)from student where length(sname)=3;--显示这个表中 sname列里面 名字长度在3个字节的 所有人的名字

--3.concat(str1,str2):str1,str2都是字符串，将字符串str1和str2拼接在一起 跟 || 是一个意思

select concat('aaa','bbb') from dual;-- 输出 就是 aaabbb连在一块了
select concat(concat('aaa','bbb'),'ccc' )from dual;--这个还可以嵌套使用 输出就是hiaaabbbccc连在一块了

-- ||  也是拼接 的意思

select 'aa'||'bb'||'cc' 别名 from dual;
select emp.*,'aa'||'bb'  from emp;

--查询当前用户下数据库中所有的表名

select table_name from user_tables; 

--查询 emp表中的全部字段 也就是列名 只显示列名

select column_name from user_tab_columns where table_name='EMP';

-- 查询emp表的所有的列的所有信息 包括 约束 列名 啥的

select * from user_tab_columns where table_name='EMP';

--单引号连续使用 表 转义符 语法 函数*****（重点）*************

--‘  chr(39) 代表的是一个 单引号（单引号也可以用这个来代替）

注意：字符串要用单引号括起来,在字符串(单引号中)中使用两个连着的单引号，这时第一个单引号是一个转义符号

select 'drop table '||table_name||';' from user_tables;--原本单引号就是给字符串一个形式 说明单引号里面的就是字符串

select '''' from dual;--这个输出 就是一个单引号 因为最外围的一对大单引号里面有一对单引号 里面的这一对单引号 就会变成一个字符串形式的单引号
--我自己的理解
（单引号一起使用必须是 成对使用 比如4对 两对 三对）
两对单引号一起使用 那么最最外围的一对单引号遥相呼应 还是起到一个单引号的作用，那么这对单引号里面包含的单引号就不再具有单引号的作用
就会变成 一个字符串形式的单引号 输出之后就会在表里出现单引号这个符号。最外围单引号里面的一对单引号 就是一个字符串形式单引号，有四个那就是
两个，有八个 那就是四个 以此类推

select ',''' from dual; 
                                          （一个）      （两个）       （一个）
select 'insert into dept values('||deptno||','''||dname||''','''||loc||''');' from dept;

--4.chr(ASCCII):
a（97）-- chr(97) 这代表的就是 a
b（98）-- .....
c（99）-- ....

A（65）
B（66）
C（67）

select chr(65) from dual;--从65开始26个字母都是大写
select chr(97) from dual;--从97开始从a到z 都是小写
select chr(39) from dual;--‘  chr(39) 代表的是一个 单引号
select chr(41) from dual;--(  这个代表的是一个括号

select
'aa'||sno||','||chr(39)||cno||chr(39)||','||chr(39)||score||chr(39)||');'
from sc;
 
 5.--substr(str,index,len):字符串截取函数，str是一个字符串，index截取的开始位置，len截取的长度（第三个参数可省略）
   --表示从字符串str的index位置开始截取len个长度的字符
   
select substr('abcdefg',2,2) from dual;-- 字符串 第二个开始截取 截取两个字符 输出是 bc

select substr('abcdefg',2) from dual;-- 若是不加截取长度 那么这个 就是从第二个位置开始 直接截取到这个字符串的最后

select substr('abcdefg',2,6) from dual;--字符串 第二个开始截取 截取6个字符 输出是 bcdefg

select substr('abcdefg',2,8) from dual;--字符串 第二个开始截取 截取8个字符 输出是 bcdefg（截取长度超过字符串长度 那么能截多少截多少）

select  substr('我爱你',1,3) from dual;
 
--6.trim([s from ]str):str表示字符串，s表示字符，
--表示去除字符串两端的空格，如果加s，表示去除字符串两端的字符s

select trim('   我爱你    ') from dual;--去除我爱你两边的空格

select trim('a' from 'abaaaaaaaaaaaaaaaaaaa') from dual;-- 去除这个字符串里面所有的a

--（6.1 ）ltrim(str[,'s']):去除字符串左边的空格或者字符s
--（6.2 ）rtrim(str[,'s'])：去除字符串右边的空格或者字符s
select ltrim('    abcd    ') from dual;
select rtrim('    abcd    ') from dual;
select ltrim('$abcd$','$') from dual; -- 去除字符串左边的$
select rtrim('$abcd$','$') from dual; -- 去除字符串右边的$

--7.replace(str,s,d):替换函数 str字符串，s要替换的内容，
--d替换成内容,表示将字符中str中的s替换成d
select replace('我爱你','爱','恨') from dual;
select replace('iloveyou','love','hei') from dual;

--8.lpad/rpad(str,len[,s]):str表示字符串，len表示扩展后的字符串长度，
--s:表示要扩展的内容，可选参数
-- lpad表示在字符串的【左边】填充空格或者让字符串str加上扩展内容s的总长度达到len的长度
-- rpad表示在字符串的【右边】填充空格或者让字符串str加上扩展内容s的总长度达到len的长度

select lpad('aa',8) from dual;    --  是在字符串左边加上6个空格使之都达到最后总长度8个的长度
select rpad('aa',8,'b') from dual;-- 是字符串扩展后的长度为8 扩展内容为b 也就是 在字符串右边增加6个d 变成总长为8的字符串

--9.initcap(str)：str表示字符串，将字符串中的英文单词首字母大写------------------------------------
select initcap('ab cdef yubn') from dual;
--10.lower/upper(str):将字符串全部转换成小写/大写
-- lower 小写                                                        【英文大写小写 函数】
-- upper 大写
select lower(upper('AAAAAAA')lower('aaaaaaa')) from dual;
select upper('aaaaaaa') from dual;
select lower(ename) from emp;
-----------------------------------------------------------------------------------------------------

--11.instr(str,s[,n1,n2]):在字符串中找某个字符的位置，str字符串，
--s：要查询的字符，n1表示位置开始查询，n2表示这个字符第几次出现
select instr('adsaaasdddffkjbsafkjfsakj','f') from dual; -- （第一个数从1开始）就是f在这个字符串里面排第几个位置

select instr('aabbcc','b',2,2) from dual;--表示从字符串第二个位置开始查询b 第二次出现的位置 是4

-- 12.floor(n):取整函数，只取整数部分，不进行四舍五入
3.14
select floor(3.14) from dual;
--13.mod(m,n)：求m除以n的余数的函数
select mod(3,2) from dual;

--14.round(n,s):四舍五入函数,n表示数字，s表示精度,默认是0
select round(3.155,2) from dual;-- 这个精度表示的是 显示小数点之后的几位数 
--15.power(m,n):求m的n次方 （这个求的是得数 就是幂 也就是m的n 次幂）
select power(2,100) from dual;
--16.to_number(str[,format]):将字符串转换成数字,str字符串，format格式字符串
select to_number('78.98') from dual;--就是说这个括号里写啥数字就是啥数字（没啥屌用）

--17.to_date(str,format):将字符串转换成固定格式的日期，【主要就是一个输出时间的函数】
--str是一个日期，format:日期格式
yyyy:表示4位的年
MM：表示两位月
dd:表示两位的天
hh24:表示24小时制的小时
mi:表示分钟
ss：表示秒
day:表示星期
-- yyyy-MM-dd hh:mm:ss
-- day星期几

select to_date('2021-08-04 17:33:34','yyyy-MM-dd hh24:mi:ss') from dual;
-- 这一个to_date 是一个显示时间的函数 里面必须跟上时间 时间的格式 一起输出才可以 要不然就会报错

--18.to_char(date,format):将日期转换成字符串，date是一个日期，format:日期格式
select to_char(sysdate,'dd') from dual;

--19.add_months(date,n):给日期加减n个月
select add_months(sysdate,-12) from dual;

--20.months_between(date1,date2):求两个日期相差多少个月

select months_between(sysdate,to_date('20200804','yyyy-mm-dd')) from dual;

--21.last_day(date):取日期当前月的最后一天
select last_day(sysdate) from dual;
--22.trunc():截断函数  --截断数字类型，第二个参数表示精度，不会四舍五入

select trunc(3333.1415926,2)from dual;--显示到小数点之后的2位数
select trunc(3333.1415926,-3)from dual;--把小数点之前的3位数变成零
select trunc(sysdate,'MM')from dual;
select trunc(sysdate,'dd')from dual;
select trunc(sysdate,'day')from dual;
select trunc(to_date('2021-9-12','yyyy-mm-dd'),'yyyy') from dual;--【这也是显示2021年这个年份 但是时间这个东西如果截取的话 
--截不到的地方全部显示1/1】

/*
23.decode(exp,res1,value1,res2,value2,...,default)
exp 表达式的
res1，res2 ,..：表达式的结果

如果表达式结果和某一个res值相等，就会返回res后面对应的value
如果表达式的结果没有一个res值和它匹配，它会返回default默认值

*/

select decode(ssex,'男','boy','女','gril','未知') from student;

--24.nvl(变量,默认值)：如果变量的值是空时,函数返回默认值，
--如果变量的值不为空返回变量的值
select * from emp;

select emp.ename,sum(emp.sal+nvl(emp.comm,100000)) from emp group by emp.ename;
--把emp表的姓名分成一个组 显示姓名 跟 工资奖金总和（这些姓名对应的工资加上｛奖金是零就加十万/奖金不为零就加原本的奖金｝）
select  sal,nvl(comm,0) from emp;--comm不为零就显示comm/comm为零就显示 后面的0

/*
25.行列转换  --pivot函数:行转列的函数
1.行转列
     行转列的大致意思是，将表中多行数据转成一行的多个字段输出。
     需要行转列的业务逻辑通常是，将表中的一个字段分类统计后作为多个结    
     果字段输出。
如：*/

--pivot函数:行转列的函数
【先用最笨的办法做】
通俗的说一下行转列的意思 如下
---------------------------
姓名 学科 成绩
张三 数学 100
张三 英语 90        -- 这个就是许多的 【行】
张三 物理 88
--经过行转列之后 如下

姓名 数学 英语 物理 -- 这个就是转成了一【列】
张三 100  90   88
--------------------------
create table studentScores(
       username varchar2(20),   --这是创建一个表
       subject  varchar2(30),
       score    number
);
insert into studentScores values('张三','语文',90);
insert into studentScores values('张三','英语',80);
insert into studentScores values('张三','数学',70);-- 给表添加内容
insert into studentScores values('李四','语文',60);
insert into studentScores values('李四','英语',61);
insert into studentScores values('李四','数学',62);

select * from studentScores;

select username,
sum(case when subject='语文' then score else 0 end) 语文, --这是通过最基本，最笨的方式去达到行转列
sum(case when subject='英语' then score else 0 end) 英语, --当科目的等于英语的时候显示成绩 要是没有英语 那就显示成绩为零
sum(case when subject='数学' then score else 0 end) 数学  -- sum 算是这些的总和输出 写了三个sum 也就创建了三个字段成了三段
from studentScores group by username;                     --这样加上username 一共四个字段 一行四列 成功的完成了行转列

【下面使用pivot函数去做行转列】

/*select username,"'语文'","'英语'","'数学'" from studentScores 
--用pivot函数查出来的字段名称就是'语文',注意这两个单引号也是名称一部分，
--所以这行要用双引号括起来
pivot(sum(score) ----聚合列取值
   for subject in('语文','英语','数学'));
*/

--【pivot函数:行转列的函数 实际使用示例】

select username,"'语文'","'英语'","'数学'" from studentScores 
pivot(sum(score)                                              
for subject in('语文','英语','数学'));

{以上代码的详细解释}
--这个呢你就照着写就行。pivot后面(sum(score)是聚合取值，意思是把所有的成绩都归为一个组，一个总和的意思，sum这里是总和的意思
--然后这个for...in（）这个，就是显示当学科subject在括号里的语文数学之类的时候 对应的前面pivot(sum(score)里面的那个成绩
--然后这个in（）括号里的这些啊 就是要行转列的 列名，但是这个列名在from前面并没有定义，所以就要在from前面再写上真正的行转列后的列名
--但是这个列明是一个字符串 在in（）用的时候加上了单引号，所以在from前面用的时候得加上双引号
--这个代码的总意思为 新的列名 username 语文 英语 数学 ，这些个列名的内容为 当subject对应的等于语文/英语/数学 时 ，也会对应的分配
--pivot(sum(score)里面的成绩集合，然后显现在表面上【pivot(sum(score)其实sum也可以换成max这里面的每个学科对应的最大值成绩的集合】

--【如下】
select username,"'语文'","'英语'","'数学'" from studentScores 
pivot(max(score)  -- sum 变成了max                                            
for subject in('语文','英语','数学'));

2.【这个与上面那个反着来就行】
/*列转行
列转行更容易理解，将表中多个列,每个列拉成一行数据。
如：
res2;
*/

/*
  unpivot函数是列转行函数
*/
select username,subject,score from studentScores2 
unpivot(score for subject in(yuwen,yingyu,shuxue));

--这个unpivot score 中的 score 为列转行中 多个列下的值变成一列的值取个统一的别名
--这个for subject 中的subject  为在结果中为那些即将要成为一列的原多列取统一别名
--最后这个in（）当新起的列名subject中有 yuwen yingyu shuxue 时 他下面就显示score中的对应的哪个值                 

【以下是建表过程】
create table studentScores2(
       username varchar2(20),
       yuwen  number,
       yingyu   number,
       shuxue   number
);

insert into studentScores2 values('张三',90,80,70);
insert into studentScores2 values('李四',60,61,62);

select * from studentScores2;

select username,'yuwen' as subject，yuwen as score from studentScores2
union all
select username,'shuxue'，shuxue from studentScores2                  ---这是用最笨的方法做的
union all
select username,'yingyu'， yingyu from studentScores2

************************************************** 特别的时间小知识 ***********************************************************************
--时间小知识

时间写的时候 几月份几号 前面要带着零 应该用这种形式写（1981-05-01）
时间写的时候 后面的YYYY-MM-DD 可以用大写 也可以用小写
 to_char(hiredate,'mm')   /*把时间中的某一段 变成数字输出出来
                          这是hiredate 列 里面的月份（就是把这个列里面的月份提出来）
                          把它变成一个数字 显示的是你想要的月份 输出结果是一个数字*/
                          
 to_char(sysdate,'MM');-- 这是当前电脑时间的 月份（把月份变成一个数字输出出来 显示结果是一个数字） 
----------------------------------------------------------------------------------
一。/*想要输出时间 就要用这个*/ 
    to_date后面要紧跟('1981-05-01','yyyy-MM-dd')格式，这两个是一体的 不可分开 to_date('1981-05-01','yyyy-MM-dd')
    -- 这一个to_date 是一个显示时间的函数 里面必须跟上时间 时间的格式 一起输出才可以 要不然就会报错

二。/*想要输出时间的一个具体值 月份 日 年*/
    to_char后面紧跟(hiredate,'mm') =8; 意思为 单独拿出一个月份等于八的 时间

--17.to_date(str,format):将字符串转换成固定格式的日期，【这个意思是专门写入时间】
--【在子查询中要用到某一个时间 那这个时间之前就必须带to_date 这是专门写时间的前缀 时间后面一定要带'yyyy-mm-dd'这是是时间的格式】
-- 这一个to_date 是一个显示时间的函数 里面必须跟上时间 时间的格式 一起输出才可以 要不然就会报错
-str是一个日期，format:日期格式 to_date('1981-05-01','yyyy-MM-dd')

-- yyyy-MM-dd hh:mi:ss

yyyy:表示4位的年
MM：表示两位月
dd:表示两位的天
hh24:表示24小时制的小时
mi:表示分钟
ss：表示秒
day:表示星期

select to_date('2021-08-04 17:33:34','yyyy-MM-dd hh24:mi:ss') from dual;

--18.to_char(date,format):将日期转换成字符串，date是一个日期，format:日期格式 【这个的意思是专门输出整个时间 或者是时间的月份 年份 几号等等】

select to_char(sysdate,'dd') from dual;--输出后出现在这个时间的 几号 dd 是几号的意思

--19.add_months(date,n):给日期加减n个月 （包括跟to_char 配合使用）    【专门更改时间的月份】
select add_months(sysdate,-1) from dual;--这就是给当前日期减一个月 然后输出整个日期

select to_char (add_months(hiredate,1),'mm') from emp;/*把emp表里面hiredate这个列的时间全部增加一个月 
                                                         然后把这个增加完的时间中的月份单独提出来 给变成一个数字 输出出来*/
                                                         
select to_char (add_months(sysdate,1),'mm') from dual;--显示当前时间 下个月 的 时间 然后八月份 变成一个数字 输出出来给dual这个表
select * from emp
select add_months(hiredate,1) from emp ;--这个等于创建一个伪列 在emp表里显示 这个表生日这一列里面所有的月份都加上1
-- add_months(date,n)使用实例
——11．把hiredate列看做是员工的生日，求下月过生日的员工 

select ename from emp where to_char(hiredate,'mm')= to_char (add_months(sysdate,1),'mm') ;

--20.months_between(date1,date2):求两个日期相差多少个月  

select months_between(sysdate,to_date('20200804','yyyy-mm-dd')) from dual;--sysdate这个时间跟20200804这个时间相差多少个月

--21.last_day(date):取日期当前月的最后一天
select last_day(sysdate) from dual;
select last_day(to_date('2021-09','yyyy-mm')) from dual;--这是看21年9月份的最后一天是几号 前面的时间可以写成'202109'/'2021-9'都行

--22.trunc():截断函数  --截断数字类型，第二个参数表示精度（就是显示小数点之后的几位数/要是参数是负数则把小数点之前的几位数变成零），不会四舍五入

select trunc(3333.1415926,2)from dual;--显示到小数点之后的2位数
select trunc(3333.1415926,-3)from dual;--把小数点之前的3位数变成零
select trunc(sysdate,'yyyy')from dual;
select trunc(sysdate,'MM')from dual;
select trunc(sysdate,'dd')from dual;
select trunc(sysdate,'day')from dual;
select trunc(to_date('2021-9-12','yyyy-mm-dd'),'yyyy') from dual;--【这也是显示2021年这个年份 但是时间这个东西如果截取的话 
--截不到的地方全部显示1/1】

***************************************** 以上是特别的时间小知识 *******************************************************