Oracle数据库-第三章：单值函数

函数分为:

  1.单值函数

  1.字符函数

  2.日期函数

  3.转换函数

  4.数字函数

  2.分组函数(后面的章节再做学习)

 

哑表dual

  dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。

  例如:

  显示1+1的结果,可以看出,dual很多时候是为了构成select的标准语法

  select 1+1 from dual;

 

   

字符函数

  LOWER                Converts to lowercase

  UPPER                Converts to uppercase

  INITCAP        Converts to initial capitalization

  CONCAT        Concatenates values

  SUBSTR        Returns substring

  LENGTH        Returns number of characters

  NVL                Converts a null value        

 

  lower 把字符转为小写

  例如:把'HELLO'转换为小写

  select lower('HELLO')

  from dual;

 

  例如:把s_emp表中的last_name列的值转换为小写

  select lower(last_name)

  from s_emp;

 

  upper 把字符转换为大写

  例如:把'world'转换为大写

  select upper('world')

  from dual;

 

  例如:把s_emp表中的last_name列的值转换为大写

  select upper(last_name)

  from s_emp;

         

 

  例如:查询s_emp表中名字为Ngao的人信息

  这样是查不到:

  select last_name,salary,dept_id

  from s_emp

  where last_name='NGAO';

      这样就可以查询到了:

  select last_name,salary,dept_id

  from s_emp

  where upper(last_name)='NGAO';

 

  initcap 把字符串首字母大写

  例如:把'hELLO'转换为首字母大写,其余字母小写

  select initcap('hELLO')

  from dual;

 

 

  concat 把俩个字符串连接在一起(类似之前的||的作用)

  例如:把'hello'和'world'俩个字符串连接到一起,并且起个别名为msg

  select concat('hello','world') msg

          from dual;

 

  例如:把first_name和last_name俩个列的值连接到一起

  select concat(first_name,last_name) as name

          from s_emp;

 

 

  substr 截取字符串

  例如:截取'hello'字符串,从第2个字符开始(包含第二个字符),截取后面连续的3个字符

  select substr('hello',2,3)

  from dual;

 

 

  length 获得字符串长度

  例如:获得'world'字符串的长度

  select length('world')

  from dual;

 

  例如:获得s_emp表中last_name列的每个值的字符长度

  select length(last_name)

  from s_emp;

 

  nvl 替换列中为null的值

  在前面的章节已经使用过了

 

 

 

 

数字函数

  ROUND                Rounds value to specified decimal

  TRUNC                Truncates value to specified decimal

  MOD                Returns remainder of division  

 

 

  round 四舍五入

  round(arg1,arg2)

  第一个参数表示要进行四舍五入操作的数字

  第二个参数表示保留到哪一位

 

  例如:

  保留到小数点后面2位

  select round(45.923,2)

  from dual;

 

  保留到个位 (个十百千万...)

  select round(45.923,0)

  from dual;

 

  保留到十位 (个十百千万...)

  select round(45.923,-1)

  from dual;

 

 

  trunc 截取到某一位

  trunc(arg1,arg2)

  和round的用法一样,但是trunc只舍去不进位

 

  例如:

  截取到小数点后面2位

  select trunc(45.929,2)

  from dual;

 

  截取到个位 (个十百千万...)

  select trunc(45.923,0)

  from dual;

 

  截取到十位 (个十百千万...)

  select trunc(45.923,-1)

  from dual;                        

 

 

  mod 取余

  mod(arg1,arg2)        

  第一个参数表示要进行取余操作的数字

  第二个参数表示参数1和谁取余

 

  例如:

  把10和3进行取余 (10除以3然后获取余数)

  select mod(10,3)

  from dual;

 

 

 

日期函数

      MONTHS_BETWEEN   Number of months between two dates

      ADD_MONTHS           Add calendar months to date

  NEXT_DAY                   Next day of the date specified

  LAST_DAY               Last day of the month

  ROUND                   Round to date at midnight

  TRUNC                   Remove time portion  from date

 

 

             sysdate关键字

表示系统的当前时间        

例如:

显示时间:当前时间

select sysdate from dual;

 

注意:sysdate进行加减操作的时候,单位是天

例如:

显示时间:明天的这个时候

select sysdate+1 from dual;

 

例如:

显示时间:昨天的这个时候

select sysdate-1 from dual;

 

例如:

显示时间:1小时之后的这个日期

select sysdate+1/24 from dual;

 

 

  months_between 俩个日期之间相差多少个月(单位是月)

例如:

30天之后和现在相差多少个月

select months_between(sysdate+30,sysdate)

from dual;

 

 

  add_months  返回一个日期数据:表示一个时间点,往后推x月的日期

例如:

'01-2月-2016'往后推2个月

select add_months('01-2月-2016',2)

from dual;        

 

例如:

当前时间往后推4个月

select add_months(sysdate,4)

    from dual;

 

注意:这个数字也可以是负数,表示往前推x月

 

  next_day 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天

例如:

离当前时间最近的下一个星期5是哪一个天

select next_day(sysdate,'星期五')

from dual;

注意:

如果要使用'FRIDAY',那么需要把当前会话的语言环境修改为英文

 

 

  last_day 返回一个日期数据:表示一个日期所在月份的最后一天

例如:

当前日期所在月份的最后一天(月底)

select last_day(sysdate)

from dual;

 

 

  round  对日期进四舍五入,返回操作后的日期数据

例如:

把当前日期四舍五入到月

select round(sysdate,'MONTH')

from dual;

测试:15号16号分别是舍弃还是进位

 

把当前日期四舍五入到年

select round(sysdate,'YEAR')

from dual;

 

  trunc  对日期进行截取 和round类似,但是只舍弃不进位

 

类型转换函数

  TO_CHAR converts a number or date string to a character string.

u          TO_NUMBER converts a character string containing digits to a number.

u    TO_DATE converts a character string of a date to a date value.

 

 

  to_char 把日期转换为字符

 

    例如:

把当前日期按照指定格式转换为字符串

select to_char(sysdate,'yyyy')

from dual;

 

  日期格式：

  yyyy：四位数的年份

  rrrr：四位数的年份

  yy：两位数的年份

  rr：两位数的年份

  mm：两位数的月份（数字）

  D：一周的星期几

  DD：一月的第几天

  DDD ：一年的第几天

  YEAR：英文的年份

  MONTH：英文全称的月份

  mon：英文简写的月份

  ddsp：英文的第几天(一个月的)

  ddspth：英文序列数的第几天(一个月的)

  DAY：全英文的星期

  DY：简写的英文星期

  hh：小时

  mi：分钟

  ss：秒

 

例如:

测试常见的一些日期数据转换为字符串的格式

select to_char(sysdate,'yyyy MM D DD DDD YEAR MONTH ddsp ddspth DAY DY') from dual;

 

select to_char(sysdate,'dd-mm-yy')

from dual;

 

select to_char(sysdate,'dd-mm-yy HH24:MI:SS AM')

from dual;

 

  千年虫:

在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时被称为千年虫

 

数据库中表示日期中年份的有俩种: yy和rr

之前一直使用的时候yy格式,后来才有的rr格式

yy表示使用一个俩位数表示当前年份:

1990 ---yy数据库格式---> 90

1968 ---yy数据库格式---> 68

1979 ---yy数据库格式---> 79

 

rr格式表示: 另外参照图片:rr日期格式规则.png

如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：

规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。

规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。

规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。

规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。

 

注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。

 

  to_char 把数字转换为字符

例如:

    select to_char(salary,'$999,999.00')

from s_emp;

 

fm表示去除结果显示中的开始的空格

select to_char(salary,'fm$999,999.00')

from s_emp;

 

L表示系统本地的货币符号

select to_char(salary,'fmL999,999.00')

from s_emp;

 

  to_number 把字符转换为数字

例如:

select to_number('1000')

from dual;

 

//这个写法是错的 abc不能转换为数字

select to_number('abc')

from dual;

 

  to_date 把字符转换为日期

例如:

select to_date('10-12-2016','dd-mm-yyyy')

from dual;

 

select to_date('25-5月-95','dd-month-yy')

from dual;

 

select to_date('95/5月/25','yy/month/dd')

from dual;

 

//session语言环境设置为英文下面可以运行

select to_date('25-MAY-95','dd-MONTH-yy')

from dual;

 

oracle数据库中表示一个日期数据的几种方式

  1.使用sysdate

  2.使用oracle默认的日期格式 例如:'25-MAY-95'

  3.使用日期函数ADD_MONTHS/NEXT_DAY/LAST_DAY/ROUND/TRUNC

  4.使用转换函数to_date

 

 

函数之间的嵌套

  格式:F3(F2(F1(arg0,arg1),arg2),arg3)

  例如:

  先把'hello'和'world'连接起来,再转换为字母大写然后再从第4个字符开始,连着截取4个字符

  select substr(upper(concat('hello','world')),4,4)

  from dual;