Oracle数据库对表数据的基本操作和Oracle字符串、数值、日期、空值操作及它们相对应的函数操作

一、Oracle数据库对表数据的基本操作

  1.1 插入数据  

insert into table_name(字段名)values(字段值);

  1.2 修改数据

        update table_name set 字段名=字段值 where 条件限定

规则：多个字段修改中间用','分隔
  1.3 删除语句

delete from table_name where 条件;

    不加条件限定语句，会删除表中所有的数据

注意：针对DML语句来讲，我们在数据进行操作，虽然我感官上认为已经操作成，但实际上是没有写入数据库 中，只有调用commit关 键字，DML语句才会真正意义上的提交。rollback表示回退，针对没有调用commit之前 有效。savepoint  a;记住要给点名，rollback to a;回退到保存点 ，---savepoint碰到commit就失效了。

二、Oracle字符串、数值、日期、空值操作及它们相对应的函数操作

1.字符串操作

1.1 字符串类型

（1）字符串的字符编码
字符串存储长度都是按字节计算的。
每一个英文在数据库中占一个字节。
每一个中文在数据库中GBK字符编码占2个字节，UTF-8字符编码占2-4个字节。

（2）char和varchar的区别

char :定长，达不到定长长度空格补齐；浪费空间换取查询效率。
varchar:变长，实际数据多少就多少。节约空间但是比char查询效率低。

char最大长度：2000个字节，也就2000个英文字母，gbk:1000个汉字
varchar最大长度：4000个字节，也就4000个英文字母，gbk:2000个汉字
char可以不指定长度，默认一个字节。
varchar必须指定长度。

（3）long和clob的区别
long可以认为是varchar的加长版，存变长，最大2G。
long的不足：（过时了，基本不用）每一张表只能出现一个long类型的（不能是主键、不能建立索引、不能出现 在查询条件中），但可以有多个clob。
clob存储可以是定长也可以是变长，最大4GB。一般用clob代替long。

long是把值真正存在记录上的，而clob只是在记录上存一个指针。long值存在记录上，行链接和行迁移就

是不可避免的，这种情况多了，很影响速度的。

1.2 字符串函数操作

（1）字符串拼接函数concat和||

    concat()用于字符串的拼接，返回两个字符串连接后的结果。但多个拼接比较繁琐，建议用字符串拼接符号||

   

   

（2）length()返回字符串的长度，varchar2返回实际长度，char返回的是定长。

（3）将字符串中的字母进行转换

upper()：将字符串所有字母转换为大写形式。

lower()：将字符串所有字母转换为小写形式。

initcap()：将所有的字符串单词的首字母转换为大写形式，单词之间用空格和非字母字符分隔。

注意：函数括号中间是单引号，不能写双引号

（4）trim()函数用于截取字符串（dual:虚表或伪表）

ltrim()函数用于截取左边的字符串

rtrim()函数用于截取右边的字符串

将上面三个函数整合到一张表中，分别用别名显示在表中的字段

（5）补位函数 pad 有左补位lpad()和右补位r(pad);

LPAD()：SELECT LPAD('12',5,'$#') from dual //把'12'用'$#'在左面补满5位。

RPAD()：SELECT LPAD('12',5,'$#') from dual //把'12'用'$#'在右面补满5位。

       

（6）截取字符串中字串函数substr()  

SUBSTR：SELECTSUBSTR('abcdefg',-2,2) from dual //第二个参数等于0或1都是从首字符开始取，

如果第二个参数为负数，那么'g'为-1，'f'为-2，以此类推，然后向右取第三个参数长度的字符。

如果没有设置第三个参数或者长度超过了字符的长度，则取到字符串末尾为止。

     

（7）instr()函数用来返回字符串中字串的位置。

     

SELECT INSTR('thinking in java','i',4,2)from dual //返回'i'在第一个参数中的位置，第三个参数的

含义是从第四个字符('n')开始检索，2表示的含义是'i'第二次出现的位置。

2.数值操作

2.1 数值类型

number(p)：表示数值类型，p表示整数位也表示数值的总位数，取值的范围是1-38。（用来存id 年龄等数字）
number(p,s)：p表示总位数（包括s），取值1-38，s表示小数后面的位数。（用来存金额）
注意：如果指定是s,没有指定p,p默认就是38位。

2.2数值函数操作

（1）ROUND()对数值进行四十五入操作

//第二个参数必须是整数，默认的是0.可以是负数也可以正数，表示小数点后第几位

（2）TRUNC()函数用于截取数字(舍弃)

（3）MOD()函数用于取模
SELECT ENAME,SAL,MOD(SAL,1000) AS RESULT FROM EMP;//表示m取模于n后的结果(m就是SAL,n就是 1000)
SELECT ENAME,SAL,MOD(SAL,0)AS RESULT FROM EMP;//如果n为0,直接返回m

（4）CEIL()/FLOOR()取天花板或地板函数
SELECT CEIL(46.768)AS RESULT FROM DUAL;//47取比当前小数大的整数

SELECT FLOOR(46.768)AS RESULT FROM DUAL; //46取比当前小数小的整数 

3.日期操作

3.1 日期类型

DATE内部7个字节（分别表示 世纪 年 月 日 时 分 秒）
TIMESTAMP：时间戳。最大精度达到11个字节，前7位于DATE相同，8-11表示纳秒，4个字节存储。
日期关键字：SYSDATE。用于获取系统时间，精确到时分秒，但是只显示年月日 。
可以将SYSDATE作为某一列的默认值也可以作为新值插入到表中。
SYSTIMESTAMP也是Oracle的内部日期函数，返回当前系统日期和时间，精确到毫秒。

3.2 日期函数操作

（1） 日期转换函数
字符串转为日期
TO_DATE()：将给定的字符串以指定的格式进行转换
--查询2003以前入职的员工
SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE<TO_DATE('2003-01-02','YYYY-MM-DD');
        日期转换为字符串 
        TO_CHAR()：是将日期按照指定的格式转换成字符串
    --将此时的系统时间转换为  xxxx年xx月xx日 xx时xx分xx秒
    SELECT TO_CHAR(SYSTIMESTAMP,'YYYY"年"MM"月"DD"日"HH24"点"MI"分"SS"秒"DAY')FROM DUAL;
（2）日期常用函数
LAST_DAY(date)：返回给定date所在月的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
--指定返回17年3月份的最后一天是多少
SELECT LAST_DAY('01-3月-17')FROM DUAL;

ADD_MONTHS(DATE ,I)： 第二个参数 I 可以正数、负数也可以是小数，小数位自动舍弃，正数往后，负数往  前。
--查询员工入职后的20周年纪念日。
SELECT ENAME,ADD_MONTHS(HIREDATE,20*12)AS A FROM EMP;

--计算3个月后的今天是哪一天，星期几？
SELECT TO_CHAR((ADD_MONTHS(SYSDATE,3*12)),'YYYY-MM-DD DAY')FROM DUAL;

--计算10年前的今天是哪一天，星期几？
SELECT TO_CHAR((ADD_MONTHS(SYSDATE,-10*12)),'YYYY-MM-DD DAY')FROM DUAL;

MONTHS_BETWEEN(date1, date2) ：

计算date1-date2两个日期值之间间隔了多少个月，如果date2时间比date1晚，会得到负值。
除非两个日期间隔是整数月，否则会得到带小数位的结果，比如计算2010年9月1日到2010年

10月10日之间间隔多少个月，会得到1.29个月。

SELECT ename, months_between(hiredate,SYSDATE)hiredate FROM emp;

NEXT_DAY(date，char)：返回给定date日期数据的下一个周几，周几是由参数char来决定的。在中文环境    下，直接使用”星期三”这种形式，英文环境下，需要使用”WEDNESDAY”这种英文的周几。为避免麻烦，可以             直接用数字1-7表示周日-周六。

下个星期三是哪一天
SELECT TO_CHAR((NEXT_DAY(SYSDATE,'星期三')),'YYYY-MM-DD DAY')AS NEXTWEEK FROM DUAL;
下个星期四是哪一天
SELECT TO_CHAR((NEXT_DAY(SYSDATE,5)),'YYYY-MM-DD DAY')AS NEXTWEEK FROM DUAL;

注意：相对于当天而言如果本周星期四还未过去，则显示本周的周四，如果过了就显示下一周的星期四。

   常用的日期格式如下图：

 

（3）日期比较函数
参数类型必须匹配，如果后面参数类型，不同于前面的参数的数据类型，会做自动转换，能转换就比较，不能转 换就报错。但自动转换要注意符合前面参数的格式
比较后，哪个小返回哪个。
SELECT LEAST(SYSDATE,'09-9月-16')FROM DUAL;

EXTRACT():抽取或提取
从当前的系统时间中提取今年的数字
SELECT EXTRACT(YEAR FROM SYSDATE)AS YEAR FROM DUAL;
从指定的时间中获取hour
SELECT EXTRACT(HOUR FROM TIMESTAMP'2016-11-5 15:33:33)FROM DUAL;

GREATEST(expr1[, expr2[, expr3]]…) LEAST(expr1[, expr2[, expr3]]…) 两个函数都可以有多个参数值，但参数
类型必须一致，返回结果是参数列表中最大或最小的值，在比较之前，在参数列表中第二个以后的参数会被隐含
的转换为第一个参数的数据类型，所以如果可以转换，则继续比较，如果不能转换将会报错。

4.空值操作

4.1 NULL的含义

NULL是数据库里的重要概念，即空值。当表中的某些字段值，数据未知或暂时不存在，取值NULL。

4.2 向表中插入空值----不能违反非空约束
显示插入：null
隐式插入：不给值 

4.3 更新空值

把数据表的某个字段更新为NULL值，和更新为其他数据的语法是相同的。

注意：更新空值时，要加上条件限制。
否则会将数据库表中该列中所有的字段值更新为null。
在更新为null值的时候，也不能违反非空约束。

4.4 空值函数：
NVL(expr1, expr2)：如果expr1为NULL，则取值expr2。

NVL2(expr1, expr2, expr3)：NVL2用来判断expr1是否为NULL，如果不是NULL，返回expr2，

如果是NULL，返回expr3。