Oracle数据库的基本操作（六）—— 函数（Ⅰ）

一、系统函数

1.字符串函数

（1）upper:将指定字段的字母转成大写

select * from Student where upper(Sdept) = 'CS';

（2）lower: 将指定字段的字母转成小写

select * from Student where lower(Sdept) = 'cs';

（3）initcap:将首字母转大写，其他字母小写；

select initcap('hello world!')from dual;

（4）substr:截取字符串

• 从字符串从左开始的第7个字符起截取5个字符；

  select substr('Hello World!',7,5) from dual;
  

• 从字符串从右开始的第7个字符起截取5个字符；

  select substr('Hello World!',-7,5) from dual;
  

• 从字符串的左边开始的第7个字符起截取所有的字符；

  select substr('Hello World!',7) from dual;
  

（5）length:求字符串长度

select length('A1234! 张') from dual;

2.数学函数

（1）mod:求余

select mod(24,5), mod(24,0) from dual;

（2）round:四舍五入

（3）trunc:按位截取，不就行四舍五入

• 精确到百分位

  select round(3456.6543,2) from dual;
  

• 截取到百分位

  select trunc(3456.6543,2) from dual;
  

• 精确到百位

  select round(3456.6543,-2) from dual;
  

• 截取到百位

  select trunc(3456.6543,-2) from dual;
  

• 精确到个位

  select round(3456.6543,0)from dual;
  select round(3456.6543)from dual;
  

• 截取到个位

  select trunc(3456.6543,0) from dual;
  select trunc(3456.6543) from dual;
  

3.日期函数

（1）系统时间：sysdate

select sysdate from dual;

（2）计算两个日期之间的月份数：months_between

• “日”为同一天，返回整数

  select months_between(to_date('2020.3.8','yyyy.mm.dd'),to_date('2019.10.8','yyyy.mm.dd')) from dual;
  

• “日”不为同一天，返回小数

  select months_between(to_date('2020.3.8','yyyy.mm.dd'),to_date('2019.10.9','yyyy.mm.dd')) from dual;
  

（3）计算两个日期之间的天数：两个日期直接相减；

• 与系统日期相差7天的日期

  select sysdate - 7 from dual;
  

• 计算“2020-10-1”与“2020-3-6”之间的天数

  select to_date('2020-10-1','yyyy-mm-dd') - to_date('2020-3-6','yyyy-mm-dd') from dual;
  

（4）计算两个日期之间的年份数

select trunc( months_between(to_date('2020.3.8','yyyy.mm.dd'),to_date('2017.3.8','yyyy.mm.dd'))/12)from dual;

（5）在指定日期的月份增加或减少多少个月：add_months;

• 查询系统日期后一年和前一年的日期

  select add_months(sysdate,12),add_months(sysdate,-12) from dual;
  

（6）指定日期的下一个学期几所在的日期（1表示星期日）

• 查询系统日期的下一个星期日的日期

  select next_day(sysdate,1)from dual;
  

• 查询系统日期的下一个星期四

  select next_day(sysdate,'星期四')from dual;
  

• 查询系统日期的下一个星期五（用Friday）
  先将nls_date_language改为英文

然后再进行查询
select  next_day(sysdate,'Friday') from dual;

• 将nls_date_language改为中文
  

（7）指定日期所在的月的最后一天：last_day;

select last_day(sysdate) from dual;

（8）对日期进行四舍五入：round;

• 逢7月往年份进一；逢16日往月份进一

（9）对日期进行截取：trunc

• 按年份对‘2020-6-16’进行四舍五入

  select round(to_date('2020-6-16','yyyy-mm-dd'),'year') from dual;
  

• 按年份对‘2020-6-16’进行截取

  select trunc(to_date('2020-6-16','yyyy-mm-dd'),'year') from dual;
  

• 按年份对‘2020-7-16’进行四舍五入

  select round(to_date('2020-7-16','yyyy-mm-dd'),'year') from dual;
  

• 按年份对‘2020-7-16’进行截取

  select trunc(to_date('2020-7-16','yyyy-mm-dd'),'year') from dual;
  

• 按月份对‘2020-7-16’进行四舍五入

  select round(to_date('2020-7-16','yyyy-mm-dd'),'month') from dual;
  

• 按月份对‘2020-7-16’进行截取

  select trunc(to_date('2020-7-16','yyyy-mm-dd'),'month') from dual;
  

• 按月份对‘2020-7-15’进行四舍五入

  select round(to_date('2020-7-15','yyyy-mm-dd'),'month') from dual;
  

• 按月份对‘2020-7-15’进行截取

  select trunc(to_date('2020-7-15','yyyy-mm-dd'),'month') from dual;
  

• 按天数对‘系统日期’进行四舍五入

  select round(sysdate) from dual;
  

• 按天数对‘系统日期’进行截取

  select trunc(sysdate) from dual;
  

4.转换函数

（1）将日期或数值转换为字符串；

• 将系统日期转换为字符串

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

• 将‘-12345’转换为字符串

  select to_char(-12345,'L99G999D99') from dual;
  --L表示货币符号，G表示分组分隔符，D表示小数点，9表示一个数字
  

（2）将字符串转换为日期：to_date;

（3）将字符串转换为数值：to_number;

select to_number('-100.00') + 100 from dual;

（4）进行数据类型的转换：cast

select cast('123.4567' as number(10,2)),cast(123.4567 as char(8)) from dual;

5.decode函数

• 语法

  decode(表达式,if1,then1,if2,then2,if3,then3,...,else);
  

• 举例

  select Sno,Sname,Sdept,decode(upper(Sdept),'CS','计科系','IS','信息系','MA','数学系','其他') from Student;
  

6.nvl：置换空值

select nvl(null,250),nvl(250,100) from dual;

select Cno,Cname,nvl(Cpno,'-')from Course;