三、sql基础:单行函数--【Oracle数据库】

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_29668759/article/details/79965300

三、单行函数(Single function)

3.1 字符函数(Character function)
3.1.1 大小写转换函数(Case-manipulation functions)
Lower("ABCdef")  --> abcdef
UPPER("ABCdef")  --> ABCDEF
INITCAP(abc def)  --> Abc Def

示例:
Oracle数据库中的数据是大小写敏感的,student变表的name字段有一个值是XIAOMING,我们去查:select * from student where name = ‘xiaoming’;查不出数据,我们应该把name这一列全部转化为小写再去匹配’xiaoming’:
select * from student where Lower(name) = ‘xiaoming’;

3.1.2 字符串转换函数(Character-manipulation functions)
Concat("hello","World")  --> helloWorld
Substr("helloworld",1,5)  --> hello  --从第1位开始截取5位
Substr("helloworld",6)  -->world  --从第6位截到最后
Length("helloworld")  --> 10
Instr("helloworld","w")  --> 6  --在第一个参数中第几位
Lpad('12345',10,"*")  --> *****12345  --向左用*补齐到10位
Rpad('12345',10,"*")  -->12345*****
Trim('h' from 'helloworld')  -->elloworld
Trim('helloworld')  -->去掉字符串前后的空格

3.2 数字操作函数
round(123.234,2)  -->123.23  --保留两位(四舍五入)
trunc(123.234,2)  -->123.23  --保留两位(无四舍五入)
mod(1600,300)  -->100  --求余数

例外:
round(45.66,-1) –> 50 -1表示保留小数点前1位(四舍五入)


3.3日期操作函数

注意:
oracle中的日期格式是:DD-MMM-YY (01-SEP-95),在我们的数据库中设置了默认的日期格式是oracle的话,在下面的函数中输入该格式的字符串就会默认是一个Data类型,而输入一个常用的’2017/09/08’就会被认为是个字符串,后面有两个函数解决这个问题。
实际开发中常用YYYY/MM/DD

months_between('01-SEP-95','11-JAN-94')  --> 19.67741  1995年9月1日比1994你那1月11日大了19个月
add_months('11-JAN-94',6)  -->11-Jul-94  加6个月
next_day('01-SEP-95','FRIDAY')  -->下一个星期五
next_day('01-SEP-95',1)  -->下一个周的第一天(周日)
last_day('01-SEP-95')  -->这个月的最后一天
round('25-JUL-95','MONTH')  -->1-Aug-95  把日期舍弃并四舍五入[95年8月25号-->95年9月1号]
round('25-JUL-95','YEAR')  -->舍弃月份并四舍五入
trunc('25-JUL-25','MONTH')
trunc('25-JUL-25','YEAR')  -->无四舍五入
补充round(sysdate,'yyyy') -->2018/1/1
3.3.1 关于日期类型的两个转换函数

(1)字符串转日期
to_date(‘2016/07/01’,’yyyy/mm/dd’)
(2)日期类型转字符串
to_char(sysdate,”yyyy/mm/dd hh:mi:ss”)

3.3.2 用我们常用的日期格式操作上面的函数
next_day('2017/09/08',1)  ---> 报错,原因见开头注意
next_day(to_date('2017-09-08','YYYY-MM-DD'),1)  -->正确。

也就是说,上面几个函数第一个参数要接受一个Date,之所以上面写的’01-SEP-95’这种不报错是因为数据库设置了默认日期格式是oracle格式。

3.3.3 日期运算

日期是可以运算的,例如有一张表,包含了入职日期(hire_date)列,那我要算入职周数呢?
select (sysdate - hire_date)/7 as weeks from emp;
结果:
—————————
| WEEKS(SYSDATE-HIRE_DATE) |
—————————
| 9 |
—————————


3.4 不同类型的数据转换
3.4.1 隐式转换(oracle自动转换,不需要我们写函数)

对于赋值语句:
varchar2/char –> number
varcgar2/char –> date
number –> varchar2
date –> varchar2
[需要注意的是,字符串和日期相互转换时,只有符合oracle日期格式的才能装换]

对于表达式比较操作:
varchar2/char –> number
varchar2/char –> date
[日期同上]
实例
select * from student where name = 123; –> 报错
oracle不会把123转换为字符串类型,而是会把name中的字符串转换成为123这种数字类型,xiaoming123这种字符串转换即报错。

3.4.2 显式转换
to_number() -- varchar --> number
to_char()  -- number/date --> varchar
to_date()  -- varchar --> date

日期转字符串
to_char(date , ‘formate’)
format:格式yyyy-mm-dd,可以不写

数字转字符串
to_char(number , ‘format’)
这里也是可以通过format控制格式:
9 表示一个数字
0 强制显示0
S 放一个$
L 放一个当前环境的币种
. 显示一个小数点
, 显示一个千分位
实例:比如我们要显示格式化的薪资
select salary , to_char(salary , ‘L99,999.00’) from employees;
结果: 6000.00 ¥6,000.00

字符串转数字
to_number(char , ‘format’);
按格式转,这样例如’¥8900’这种字符串都可以直接转成8900数字。
to_number(‘8900,99999’);这样才不会报错。


3.5 其他常用单行函数
nvl(ex1 , ex2)  --> 如果ex1为空,则返回ex2
nvl(ex1 ,ex2 , ex3)  --> 如果ex1为空,则返回ex3,不为空,则返回ex2
nullif(ex1 , ex2)   --> 如果ex1和ex2相等,则返回空,否则返回ex1;
coalesce(ex1,ex2,ex3.....,exn)   --> 从ex1开始往后找,直到找到一个不为空的,返回,结束。没找到,返回NULL

3.6 条件表达式
3.6.1 CASE语句

根据不同的职位显示不同的薪水

select emp_name , job_id , salary,
        case job_id
            when 'IT_PROG' then 1.1*salary
            when 'ST_PROG' then 1.2*salary
            else
                salary
        end "SHOW_SALARY" from emp;
    结果:
    ----------------------------------
    | EMP_NAME | SALARY | SHOW_SALARY | 
    ----------------------------------
    | zhao     | 1000   | 1100        |
    ----------------------------------
3.6.2 decode语句

同上:

select emp_name , job_id , salary,
        decode(job_id,
            'IT_PROG' ,
            1.1*salary ,
            'SG_PROG' ,
            1.2*salary ,
            salary
            ) show_salary from emp;
阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页