Oracle学习（5）

2. Oracle中的数值（数字）操作
1)数值（数字）类型

  (1)number(n)   表示整数
        n表示数字的总位数，n的取值是1~38
  
  (2)number(n,m)     表示浮点数（小数）
  n表示数字的总长度，m表示小数点后长度

2)数值函数

(1) round函数：用于四舍五入

语法：round(n,m)

–参数中的n可以是任何数字，指要被处理的数字
–m必须是整数
–m取正数，则四舍五入到小数点后第m位
–m取值为0，则四舍五入到整数位
–m取值为负数，则四舍五入到小数点前m位
–m缺省，默认值是0

create table u3(id number(10,4));
insert into u3 values(45.678);
insert into u3 values(round(45.678,2));    45.68
insert into u3 values(round(45.678,0));    46
insert into u3 values(round(45.678,-1));   50
insert into u3 values(round(45.678));       46

(2) trunc函数：用于截取

语法：trunc(n,m)

n和m的定义和round函数中的n和m相同，不同的是功能上按照截取的方式处理。

insert into u3 values(trunc(45.678,2));  45.67
insert into u3 values(trunc(45.678,0));  45
insert into u3 values(trunc(45.678,-1));  40
insert into u3 values(trunc(45.678));   45

(3) mod函数：求余数

语法：mod(m,n)   求m除以n后的余数

说明：如果n为0，直接返回m的值

select ename,sal,mod(sal,1000) from emp;

(4) ceil和floor函数

语法：ceil(n) 取大于或等于n的最小整数值

  floor(n) 取小于或等于n的最大整数值

例： n=4.5 ceil(4.5） 5 floor(4.5)  4

insert into u3 values(ceil(45.678));    46
insert into u3 values(floor(45.678));  45

3**.Oracle日期操作**

1. 日期类型

(1) date
date类型是Oracle中最常用的日期类型，用于处理日期的类型。

(2) timestamp
与date类型的区别不仅可以保存日期和时间，还能够保存小数秒，最高能精确到ns（纳秒级）。

2）日期的关键字

(1) sysdate 关键字
是一个Oracle的内部函数，返回的是当前的系统时间，精确到秒，默认显示格式DD-MON-YY —03-3月-20

select sysdate from dual;  查询当前系统时间
dual表是Oracle中的一个虚表，表中只有一列，一行数据X

(2) systimestamp
也是Oracle中的一个内部函数，返回当前系统时间，精确到毫秒。

select systimestamp from dual;
03-3月 -20 03.10.55.497000 下午 +08:00

3)日期转换函数
(1) to_date 函数:将字符串按照特定格式转换为日期类型

 语法：to_date(str,fmt)    str表示要转换的字符串
                      fmt表示日期格式

查询1983年1月1日之后入职的员工
select ename,hiredate from emp;
select ename,hiredate from emp where sal>1000;

select ename,hiredate from emp where hiredate>’1983-01-01’;
--ORA-01861: 文字与格式字符串不匹配

将‘1983-01-01’字符串类型转换为日期类型的，使用to_date函数
to_date(‘1983-01-01’,’YYYY-MM-DD’)
select ename,hiredate from emp 
where hiredate>to_date(‘1983-01-01’,’YYYY-MM-DD’);

常用的日期格式如下：

YY 表示2位数字的年 20年 19年 18年
YYYY 表示4位数字的年 2020年 2019年
MM 表示2位数字的月 03月 02月 12月
MON 简写的月份 Jan
MONTH 全拼的月份 January
DD 2位数字的天 01 02 03
DAY 周几的全拼
HH24 24小时制度
HH12 12小时制度
MI 分钟
SS 秒

（2）to_char函数：将其它类型的数据转换为字符串类型

 语法：to_char(date,fmt)    date表示要转换的日期
                          fmt表示转换字符串的指定格式

select hiredate from emp;
select hiredate,to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') from emp;
select hiredate,to_char(hiredate,'YYYY-MONTH-DD HH24:MI:SS') from emp;
select hiredate,to_char(hiredate,'YYYY-MON-DD HH24:MI:SS') from emp;
select hiredate,to_char(hiredate,'YYYY-MON-DAY HH24:MI:SS') from emp;
按照’1980年12月17日’格式查询出入职日期：
select hiredate,to_char(hiredate,’ YYYY”年”MM”月”DD”日” ’) from emp;
说明：外面是‘’，在‘’号的内部在表示字符串是只能用“”号了。
select hiredate,to_char(hiredate,’YYYY’)
||’年’|| to_char(hiredate,’MM’)||’月’|| to_char(hiredate,’DD’)|| ’日’ from emp;

4）日期常用的函数

（1）last_day函数：返回日期所在月的最后一天

    语法：last_day(date):返回date日期所在月的最后一天

select last_day('20-2月-20') from dual;     29-2月 -20
select last_day('03-3月-20') from dual;     31-3月 -20

(2)add_months函数

     语法：add_months(date,i) 返回日期date加上i个月后的日期值。

     说明：--参数i可以是任何数字，大部分时候i的取值都是正值整数。
           --如果i是小数，会被截取整数后再参与运算
           --如果i是负数，会减去i个月后的日期值

select sysdate,add_months(sysdate,3),add_months(sysdate,3.14),
add_months(sysdate,-3) from dual;

(3)months_between

   语法：months_between(date1,date2)

   计算date1和date2两个日期值之间间隔了多少个月。(date1-date2计算)

计算2009年9月1日到2009年12月10日之间间隔了多少个月
select months_between('10-12月-09','01-9月-09') from dual;
练习：计算emp员工表中从入职开始到现在间隔了多少个月
select months_between(sysdate,hiredate) from emp;

(4)next_day函数

   语法：next_day(date,c)  返回date日期的下一个周几，周几是由参数c决定的。

   说明：月： 03  02  3月  2月
         日/天：  1 2 3    星期一  星期二       
         中文环境下，c的取值可以用’星期一’~’星期天’
         英文环境下，c的取值WEDNESDAY
         为了避免麻烦，c的取值用数字1~7表示周日~周六

select sysdate,next_day(sysdate,4) from dual;

(5)extract函数

语法：extract(date from datetime)  从参数datetime
中提取出参数date指定的数据，比如提取年，月，日

参数date的取值： year年 month月 day日
hour时 minute分 second秒

select extract(year from systimestamp) from dual;
select extract(month from systimestamp) from dual;
select extract(day from systimestamp) from dual;
select extract(hour from systimestamp) from dual;  ×
select extract(hour from timestamp '2020-03-04 09:05:10') from dual;
select extract(hour from cast(sysdate as timestamp)) from dual;
select extract(timezone_hour from systimestamp) from dual;   获取时区
select extract(minute from systimestamp) from dual;
select extract(second from systimestamp) from dual;

4、空值操作

1）空值的含义 null

   数据库中一个非常重要的概念，null/NULL，空值

有时候表中某些字段的值，数据未知或暂时不存在，都是null

任何数据类型（数值类型、日期类型、字符串类型）均可以取值为空null

2）插入空值

表test  id列 name列 age列

（1）显式插入null值
insert into test(id,name,age) values(1,’zs’,null);

（2）隐式插入null值
insert into test(id,name) values(2,’ls’); age列隐式插入null

create table test(
   id number(2),
   name varchar2(10),
   age number(3)
);
insert into test(id,name,age) values(1,'zs',20);
insert into test(id,name,age) values(2,'ls',null);
insert into test(id,name) values(3,'ww');

(3)更新null值

注意更新null值时，此列必须没有非空约束才能更新。

update test set age=null where id=1;
update test set name=null where id=2;

（4）null值条件查询

select * from test where name is null;

强调：查询条件中判断列是否等于null值，使用is，而不是用 = 号。

（5）非空约束 not null

 非空约束是确保字段的值不能为空

 create table test(
    id number(2),
    name varchar2(10) not bull
 );
name列加上了非空约束，该name列不允许插入null值，不允许修改为null了。

3）空值相关的函数

（1）nvl函数

    语法：nvl(e1,e2)   将null转为非null值

          --如果e1为null，则取值e2，e2为实际值

–e1和e2可以是任何数据类型，但是两个参数的数据类型必须是一致的

计算员工的收入=底薪sal+提成comm
select ename,sal,comm,sal+comm from emp;
select ename,sal,comm,sal+nvl(comm,0) from emp;

（2）nvl2函数

   语法：nvl2(e1,e2,e3) 和nvl函数功能类似，都是将null转为非空值。

    -- nvl2用来判断e1是否为null，如果不是空，用e2的值；如果是空，用e3的值。

select ename,sal,comm,nvl2(comm,comm+sal,0+sal) from emp;
select ename,sal,comm,nvl2(comm,comm+sal,sal) from emp;
select ename,sal,comm,sal+nvl2(comm,comm,0) from emp;