Oracle数据库对表的基本操作和一些常用函数

首先，创建一个员工表，sql语句如下：

CREATE TABLE employee(
  id NUMBER(4),
  name VARCHAR2(20) NOT NULL,
  gender CHAR(1) DEFAULT 'M',
  birth DATE,
  salary NUMBER(6,2),
  comn NUMBER(6,2),
  job VARCHAR2(30),
  manager NUMBER(4),
  deptno NUMBER(2)
)

下面，是对这个表的一些基本操作：

RENAME employee TO emp  //将表名改成emp

ALTER TABLE employee ADD hiredate DATE DEFAULT sysdate  //为employee增加列，默认值为当前日期

ALTER TABLE employee MODIFY (job VARCHAR2(40) DEFAULT ‘CLERK‘)  //修改job列的长度为40，并设置默认值

ALTER TABLE employee DROP (manager)  //删除manager列

INSERT INTO employee (id,name,job,salary) VALUES(1001,‘jack‘,‘PROGRAMMER‘,5500)  //向表中插入数据

INSERT INTO employee (id,name,job,birth) VALUES(1002,‘LARRY‘,‘ANALYST‘,TO_DATE(‘1994-10-22‘,‘YYYY-MM-DD‘))  //使用自定义日期格式插入记录，默认格式是‘22-SEP-94‘

UPDATE employee SET salary=10 WHERE name=‘LARRY‘  //更新LARRY的薪水，注意如果插入数据的时候字母是大写，那么修改的时候也要大写

DELETE FROM employee WHERE birth is null  //删除生日为null的记录

CHAR最大取值为2000字节，它可以不指定长度，默认为1，VARCHAR2最大取值为4000字节，它必须指定长度。

字符串函数：

CONCAT和||：返回两个字符串连接后的结果。select CONCAT(‘a‘,‘b‘) from dual  result：ab    select ‘lo‘||‘ve‘ from dual  result：love

LENGTH：返回字符串的长度，如果是VARCHAR2返回字符串的实际长度，如果字符类型是CHAR，长度还要包括后补的空格。

UPPER：把字符转换为大写形式。

LOWER：把字符转换为小写形式。

INTCAP：把每个单词的首字母大写，单词之间用空格和非字母字符分隔。

TRIM：SELECT TRIM(‘e‘ FROM ‘elite‘) FROM dual  //从elite的前后截去‘e‘，如果不写e，就去掉两端的空格。

LTRIM：SELECT LTRIM(‘elite‘,‘e‘) FROM dual  //从elite的左面截去‘e‘，如果不写e，就去掉左端的空格。

RTRIM：SELECT LTRIM(‘elite‘,‘e‘) FROM dual  //从elite的右面截去‘e‘，如果不写e，就去掉右端的空格。

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

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

SUBSTR：SELECT SUBSTR(‘abcdefg‘,-2,2) from dual  //第二个参数等于0或1都是从首字符开始取，如果第二个参数为负数，那么‘g‘为-1，‘f‘为-2，依此类推，然后向右取第三个参数长度的字符。如果没有设置第三个参数或者长度超过了字符的长度，则取到字符串末尾为止。

SELECT INSTR(‘thinking in java‘,‘i‘,4,2) from dual  //返回‘i‘在第一个参数中的位置，第三个参数的含义是从第四个字符(‘n‘)开始检索，2表示的含义是‘i‘第二次出现的位置。

空值函数：

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

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

日期转换函数：

TO_DATE：将字符串按照定制格式转换为日期类型。for example：TO_DATE(‘2002-01-01‘,‘YYYY-MM-DD‘)

TO_CHAR：将其它类型（日期，数值）的数据转换为字符类型，主要应用在日期类型上。for example：TO_CHAR(hiredate,‘YYYY"年"MM"月"DD"日"‘)

常用的日期格式如下图：

日期常用函数：

LAST——DAY(date)：返回给定date所在月的最后一天。

ADD_MONTHS(date,i)：返回给定date加上i个月后的日期值，如果i是负数，则获得减去i个月后的日期值。

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

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

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