学习大数据DAY03 SQL基础语法3

目录

函数

什么是函数？

自定义函数

数据类型转换

1. 隐式转换

2. 显示转换

空值赋值 nvl

TRANSLATE(c1,c2,c3)

作业

---

---

SQL 基础语法（三）

函数

什么是函数？

SQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。

通

常用于返回特定的数据，可以很方便地转换和处理数据,Oracle 数据库中主要使用

两种类

型的函数：

1.

单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个

结果，

①字符函数：对字符串操作。

②数字函数：对数字进行计算，返回一个数字。

③转换函数：可以将一种数据类型转换为另外一种数据类型。

④日期函数：对日期和时间进行处理。

2.聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。

自定义函数

--加法计算器 输入两个数 返回一个和

create or replace function getSum(n1 in number,n2 in number)

return number

as

he number(4);

begin

he:=n1+n2;

return he;

end;

select getSum(3,5) from dual

select empno,deptno,sal,comm,getSum(empno,deptno) from emp

数据类型转换

1. 隐式转换

指 Oracle 在执行 SQL 语句时自动将数据类型转换成需要的数据类型。隐式转换可能

会

导致精度损失或数据不准确，因此在使用时需要谨慎。

2. 显示转换

显式转换是指在 SQL 语句中使用一些函数强制将某个数据类型转换成另一个数据类

型。

to_number ：字符转换为数字

to_date()：作用将字符类型按一定格式转化为日期类型。

to_char 数字转化为字符

to_char 日期转化为字符,必须加单引号,并且区分大小写

to_char 的其他用法

Select to_char(sysdate, 'ss' ) from dual 取当前时间秒部分

Select to_char(sysdate, 'mi' ) from dual 取当前时间分钟部分

Select to_char(sysdate, 'HH24' ) from dual 取当前时间小时部分

Select to_char(sysdate, 'DD' ) from dual 取当前时间日期部分

Select to_char(sysdate, 'MM' ) from dual 取当前时间月部分

Select to_char(sysdate, 'YYYY' ) from dual 取当前时间年部分

Select to_char(sysdate, 'w' ) from dual 取当前时间是一个月中的第几周(从 1 日

开始算)

Select to_char(sysdate, 'ww' ) from dual 取当前时间是一年中的第几周（从 1.1

开始算） Select to_char(sysdate, 'iw' ) from dual 取当前时间是一年中的第几周（按实际

日历的）

Select to_char(sysdate, 'd' ) from dual 取当前时间是一周的第几天，从星期天

开始，周六结束

Select to_char(sysdate, 'day' ) from dual 取当前日是星期几，和数据库设置的

字符集有关，会输出 'Tuesday' 或星期几

Select to_char(sysdate, 'ddd' ) from dual 当前日是一年中的第几天

空值赋值 nvl

【语法】NVL (expr1, expr2)

【功能】若 expr1 为 NULL，返回 expr2；expr1 不为 NULL，返回 expr1。

注意两者的类型要一致

【语法】NVL2 (expr1, expr2, expr3)

【功能】expr1 不为 NULL，返回 expr2；expr2 为 NULL，返回 expr3。

expr2 和 expr3 类型不同的话，expr3 会转换为 expr2 的类型

TRANSLATE(c1,c2,c3)

【功能】将字符表达式值中，指定字符替换为新字符

c1 希望被替换的字符或变量

c2 查询原始的字符集

c3 替换新的字符集，将 c2 对应顺序字符，替换为 c3 对应顺序字符

如果 c3 长度大于 c2，则 c3 长出后面的字符无效

如果 c3 长度小于 c2，则 c2 长出后面的字符均替换为空(删除)

如果 c3 长度为 0，则返回空字符串。

如果 c2 里字符重复，按首次位置为替换依据

select

TRANSLATE('重庆的人','重庆的','上海男'),

TRANSLATE('重庆的人','重庆的重庆','北京男士们'),

TRANSLATE('重庆的人','重庆的重庆','1 北京男士们'),

TRANSLATE('重庆的人','1 重庆的重庆','北京男士们') from dual;

返回：上海男人,北京男人,1 北京人,京男士人

select length('AG GGG') from dual;

select ename,length(ename) from emp ;

select length('学') from dual;

--字符串拼接函数

select 'sss'||ename from emp;

select concat('AAA','BBB') from dual;

select concat(empno,ename) from emp; --chr():将一个 ASCII 码转换成字符

select chr(65) from dual;

select chr(32) from dual;

select 'aaaaa'''||'bbbb' from dual;

select 'aaaaa'||chr(39)||'bbbb' from dual;

--ascii(字符)：将一个字符转换成 ASCII 码值

select ascii('a') from dual;

select ascii('A') from dual;

select ascii(' ') from dual;

select ascii(chr(65)) from dual;

--instr(源字符串,目标字符串,开始位置,匹配序号)

select instr('syranmrao','a',1,1) from dual;

select instr('syranmrao','a',5,1) from dual;

select instr('syranmrao','r',4,1) from dual;

--当 start 为负数时，表示从右往左倒数第 start 位开始找，但是实际返回的位置

还是从前往后第一个字符的位置

select instr('ansyranmrao','r',-3,1) from dual;

select instr('syranmrao','ra',0,1) from dual;---当 start 为 0，超出范围则

返回 0

select instr('syranmrao','ra',2,0) from dual;---当 n 为 0，超出范围则返回 0

--子字符串

select substr('aa|bb|cc|dd|ee',5,3) from dual;

select substr('aa|bb|cc|dd|ee',-5,3) from dual;

select substr('aa|bb|cc|dd|ee',0,3) from dual;---start 是 0 的时候跟第 1 位

开始是一样的

select substr('aa|bb|cc|dd|ee',1,-3) from dual;---len 是负数的时候没有意

义，截不出来东西

select substr('aa|bb|cc|dd|ee',1,0) from dual;---len 是 0 的时候也没有意义，

截不出来东西

select substr('abcdefg',3) from dual;

--将字符串的英文单词首字母大写其他字母小写(以空格来区分单词的)

select initcap(ename) from emp;

select initcap('hello world') from dual;

--大小写转换

select lower('AER112345') from dual;

select upper('Hello World') from dual;

--替换函数

select replace('asdFFasdf','FF','A') from dual;

select replace('广西 桂林市秀峰区',' ','省') from dual; --round 四舍五入

select round(4.834859,2) from dual;

--求余函数

select mod(5,3) from dual;

--translate

select translate('abcdefga','abc','wo') 返回值 from dual;

--转换函数

select to_number( '0.9890' ) +3 from dual;

select to_date( '1999-09-09 ' , 'yyyy/MM/dd' ) from dual;

select to_date( '19990909' , 'yyyyMMdd' ) from dual;

select to_date( '1999/09/09 22:56:18' , 'yyyy/MM/dd hh24:mi:ss' ) from dual;

select to_date( 19990909 , 'yyyyMMdd' ) from dual; ---数字也可以去转

select to_char( '.83' , '0.99' ) from dual;

select to_char(sysdate, 'dd' ) from dual;

select to_char(sysdate, 'mm' ) from dual;

select to_char(sysdate, 'yyyy' ) from dual;

select to_char(sysdate, 'yyyy-mm-dd' ) from dual;

select to_char(sysdate, 'yyyy/MM/dd hh24:mi:ss' ) from dual;

select last_day(sysdate) from dual;

select next_day(sysdate, 4 ) from dual;

select add_months(sysdate, -3 ) from dual --求 3 个月前的今天

select sysdate - hiredate from emp; ----得出结果以天为单位的

select months_between(sysdate,to_date( '2020-3-3' , 'yyyy/MM/dd' )) from emp

select comm,nvl2(comm,comm, 1 ) from emp;

作业

--获取一份 emp 表方便查看
select * from emp;
--1.求姓名为 SMITH 的员工的工作名称长度
select length(job)
from emp
where ename='SMITH';
--2.求名字长度为 6 的员工姓名，编号
select ename,empno
from emp
where length(ename)=6 ;
--3.求员工的工资，格式如：姓名是：SMITH,工资是：800
select '姓名是'||ename||',工资是:'||sal
from emp--4.找出 emp 表中员工名字包含 T 字母的员工
select ename from emp where ename like '%T%';
--5.找出 emp 表里名字第 4 位之后含 T 字母的员工
select ename from emp where ename like '___%T%';
--6.找出 emp 表里名字倒数第 3 位之前含 M 字母的员工
select ename from emp where ename like '%M%__';
--7.求第五位是 H 的员工姓名
select ename from emp where ename like '____H%';
--8.求姓名中带有三个 L 的姓名
select ename from emp where ename like '%L%L%L%';
--9.将员工姓名首字母大写，其余字母小写
select initcap(ename) from emp;
--10.将员工姓名首字母小写，其余字母大写
select replace(upper(ename),substr(ename,1,1),lower(substr(ename,1,1)))
from emp;
--11.查询工资是偶数的员工
select ename,sal from emp
where mod(sal,2)=0 ;
--获取一份 emp 总表方便查看
select * from emp;
--12.求 1981 年 2 月 20 号入职的员工
select ename from emp where hiredate=to_date('19810220','yyyymmdd');
--13.求 1981 年入职的员工
select ename,hiredate
from emp
where to_char(hiredate,'yyyy')='1981';
--14.求 2 月份入职的员工
select ename,hiredate
from emp
where to_char(hiredate,'mm')='02';
--15.取当前月倒数第 5 天入职的员工
select ename,hiredate
from emp
where hiredate=last_day(sysdate)-4 ;
--16.取每个月倒数第 5 天入职的员工
select ename,hiredate
from emp
where hiredate=last_day(hiredate)-4 ;
--17.求下下个周三的日期
select next_day(sysdate,4)+7 from dual;
--18.求 3 个月后的今天select add_months(sysdate,3)
from dual;
--19.求每个员工的月收入
select ename,sal+nvl(comm,0)
from emp;
--20.如果 comm 为空就显示“No Commission”，如果不为空就显示 comm 的值
select ename,nvl2(comm,to_char(comm),'No Commission')
from emp;
--21.求员工的一年的总收入（工资和佣金）
select ename,(sal+nvl(comm,0))*12
from emp;