Oracle03-内置函数

1.内置函数的分类.

• 内置函数：Oracle自带的函数，我能只需要去调用就可以了。
• Oracle里面的内置函数分为如下几类：

1. 转换函数；
2. 日期函数；
3. 字符函数；
4. 数学函数；
5. 集合函数。

2.转换函数.

2.1 to_char();

作用：把一个非字符类型转换为字符类型；
案例：调用当前的系统日期：select sysdate from dual；

2.2 to_date();

作用：把字符类型的时间转换为日期类型；
案例：把字符类型时间 ‘2021-08-18’ 转换成日期类型,；
select to_date(‘2021-08-18’,‘yyyy/mm/dd’) from dual；

2.3 to_number();

作用：把字符字符类型的数字转换为数值类型；
select 100+to_number(‘200’) from dual；

3.日期函数.

3.1 add_months().

1. 作用：在指定的日期上加上指定数量的月份，求当时的日期；
2. 注意点：只能作用于日期类型；
3. 案例：求当前系统时间100个月之后的日期
4. select add_months(sysdate,100) from dual；

3.2 extract().

1. 作用：单独取日期里面的年月日；
2. 注意点：只能作用于日期类型；
3. 案例：取当前系统时间的年份；
4. select extract(year from sysdate) from dual；

3.3 months_between().

1. 作用：求两个时间之间相差的月份；
2. 注意点：只能作用于日期类型；
3. 案例：求2020-2-2和当前系统时间之间相差的月份；
4. select months_between(sysdate,to_date(‘2017-2-2’,‘yyyy/mm/dd’)) from dual；

3.4 Last_day().

1. 作用：求本月的最后一天；
2. 案例：求当前月份的最后一天日期；
3. select last_day(sysdate) from dual；

3.5 Next_day().

1. 作用：求下个指定星期的日期；
2. 案例：求下周六的日期;
3. select next_day(sysdate,‘星期六’) from dual；

4.字符函数.

select upper(‘abcdAsdfa’) from dual; --全部转为大写；

select lower(‘abcdAsdfa’) from dual; --全部转为小写；

select ltrim(‘abcd’,‘a’) from dual; --去掉左边的字符(第一个字符若是a则去除，反之不去除)；

select rtrim(‘acccsdfdsccccbcd’,‘c’) from dual; --去掉右边的字符(第一个字符若是c则去除，反之不去除)；

select initcap(‘adad’) from dual； --将首字母转换为大写；

select substr(‘abcdefghi’,3,4) from dual; --从第三位开始截，截4个；

select instr(‘abcdefg’,‘c’) from dual; --判断c的位置，下标从1开始；

select concat(‘aa’,‘bb’) from dual; --这是拼接的意思，用的少，因为我们可以用||替代；

select ascii(‘a’) from dual; --求a的ic码；

select char(77) from dual; --将ic码转换成对应的值字符；

select length(‘abcd张’) from dual; --求字符的个数，不是字节的个数；

select lpad(‘abde’,9,‘x’) from dual; --左填充，从左边开始用‘x’填充，直到字节的长度变为9为止；

select rpad(‘abde’,9,‘x’) from dual; --右填充，从 边开始用‘x’填充，直到字节的长度变为9为止；

select decode(‘c’,‘a’,1,‘b’,2,‘c’,3) from dual; --结果为3，意思是给几个结果进行判断，如果前面的符是a就输出1，如果是b就输出2，如果是c就输出3；

5.数学函数.

* select ceil(57.12) from dual;                       --向上取整；
* select floor(57.98) from dual;                     --向下取整；

* select round(54.99) from dual;                   --结果是55，四舍五入；

* select round(54.59,1) from dual;                --四舍五入并且保留小数点后1位数；

* select power(3,2) from dual；                      --3的2次方；

* select mod(6,7) from dual;                            --取余/模；

* select sqrt(4) from dual;                               --开根号；

* select sign(0) from dual;                               --如果是正数结果就为1，如果是负数结果就为-1，如果是0，结果就为0；

6.聚合函数.

sum : 求和

max: 求最大值

min: 求最小值

avg: 求平均值

count: 求个数

7.综合案例.

--1、查询emp表中的所有信息
select * from emp
--2、显示emp表的雇员名称和工资。
select ename,sal from emp
--3、查询emp表中部门编号为20的并且sal（工资）大于3000的所有员工信息
select * from emp where deptno=20 and sal>=3000
--4、查询emp表中部门编号为20的或者sal（工资）大于3000的所有员工信息
select * from emp where deptno=20 or sal>3000
--5、使用between and 查询工资在2000和4000之间的员工（用and 重新实现）
select * from emp where sal between 2000 and 4000
--6、使用in 查询 部门编号10，20的所有员工
select * from emp where deptno in(10,20)
--7、使用like查询所有名字中包括 W的员工信息
select * from emp where ename like '%W%'
--8、使用like查询所有员工名字中第二子字母为W的员工信息
select * from emp where ename like '_W%'
--9、查询所有员工信息并按照部门编号和工资进行排序
select * from emp order by empno,sal desc;
--10、显示雇员工资上浮20%的结果。
select ename,sal*1.2 from emp
--11、显示EMP表的雇员名称以及工资和津贴的和。
select ename,sal+nvl(comm,0) from emp
--12、在查询中使用列别名。特殊的别名如别名中有空格
select ename"姓 名" from emp 
--13、显示DEPT表的内容，使用别名将表头转换成中文显示。
select deptno"部门编号",dname"部门名字",loc"部门地址" from dept
--14、显示DEPT表的内容，按以下的形式:部门ACCOUNTING所在的城市为NEW YORK
select dname||'所在的城市为'||loc from dept   
--15、使用DISTINCT消除重复行显示。显示emp表中的的职位
select distinct job from emp
--16、查询雇员姓名和工资，并按工资从小到大排序。
select ename,sal from emp order by sal;
--17、查询雇员姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示。
select ename,hiredate from emp order by hiredate desc;
--18、查询雇员信息，先按部门从小到大排序，再按雇佣时间的先后排序。
select * from emp order by deptno,hiredate;
--19、按工资和工作月份的乘积排序。
select * from emp order by sal*extract(month from hiredate)
--20、显示职务为“SALESMAN”的雇员的姓名、职务和工资。
select ename,job,sal  from emp where job='SALESMAN'
--21、显示工资大于等于3000的雇员姓名、职务和工资。
select ename,job,sal from emp where sal>=3000
--23、显示部门编号为10的雇员姓名和雇佣时间
select ename,hiredate from emp where deptno =10;
--24、显示工资在1000～2000之间(不包括1000和2000)的雇员信息。
select * from emp where sal>1000 and sal<2000;
--25、显示部门10中工资大于1500的雇员。
select * from emp where deptno=10 and sal>1500
--26、显示职务为CLERK或MANAGER的雇员信息。
select * from emp where job='CLERK' or job='MANAGER'
--27、显示部门10以外的其他部门的雇员。
select * from emp where deptno!=10
--28、显示部门10和部门20中工资小于1500的雇员。
select * from emp where sal<1500 and deptno in(10,20);
--29、显示经理编号没有填写的雇员。
select * from emp where mgr is null;
--30、如果不知道表的字段内容是大写还是小写，可以转换后比较。
select upper(ename) from emp;

select lower(ename)from emp;
--31、显示名称以“W”开头的雇员，并将名称转换成以大写开头。
select initcap(ename) from emp where ename like 'W%';
--32、显示雇员名称中包含“S”的雇员名称及名称长度。
select ename,length(ename) from emp where ename like '%S%'
--33、显示部门表中部门和所在城市列表，中间以下划线“_”连接，城市名转换成以大写字母开头。
select dname ||'_'||initcap(loc)from dept;
--34、返回系统的当前日期
select sysdate from dual
--35、返回2003年2月的最后一天。
select last_day(to_date('2003-2','yyyy/mm'))from dual;
--36、假定当前的系统日期是2003年2月6日，求再过1000天的日期。
select to_date('2003-02-02','yyyy-mm-dd')+1000 from dual;
--37、假定当前的系统日期是2003年2月6日，显示部门10雇员的雇佣天数。
select to_date('2003-02-06','YYYY-MM-DD')-hiredate from emp where deptno=10;
--38、显示雇员名称和雇佣的星期数
select ename,floor((sysdate-hiredate)/7 )from emp
--39、显示从本年1月1日开始到现在经过的天数(当前时间取SYSDATE的值)。
select sysdate-to_date('2022-04-28','yyyy-mm-dd') from dual 
--40、将日期显示转换成中文的年月日。
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;
--41、将雇佣日期转换成字符串并按新格式显示。（17 12月 1980）
select to_char(hiredate,'DD MM YYYY') from emp;
--42、使用nvl函数转换空值。（如果是数值转换成0 字符转换为 空）
select nvl(comm,0) from emp