首页 > 代码库 > （三）Oracle字符串操作
（三）Oracle字符串操作
1. Oracle字符串操作
1.1. 字符串函数
1.1.1. CONCAT和“||”
CONCAT是字符串连接函数，语法是：CONCAT(char1, char2)
用于返回两个字符串连接后的结果，两个参数char1、char2是要连接的两个字符串。concat只能有两个参数，所以如果连接三个字符串时，需要两个concat函数。比如连接emp表中的name列和salary列，中间用“：”隔开：
SELECT CONCAT(CONCAT(name, ‘ : ‘), sal)FROM emp;
在连接两个以上操作符时并不是很方便。concat的等价操作是连接操作符”||”。当多个字串连接时，用||符号更直观。下述SQL语句实现相同的效果：
SELECT ename || ‘ : ‘ || sal FROMemp;
1.1.2. LENGTH LENGTHB
LENGTH(char)用于返回参数字符串的长度。例如：
SELECT ename, LENGTH(name) FROM emp;
1.1.3. UPPER、LOWER和INITCAP
这三个函数全部是英文的大小写转换函数，用来转换字符的大小写：
UPPER(char)用于将字符转换为大写形式
LOWER(char)用于将字符转换为小写形式
NITCAP(char)用于将字符串中每个单词的首字符大写，其它字符小写
如果这三个函数的输入参数是NULL值，仍然返回NULL值。例如：
SELECT UPPER(‘hello world‘), LOWER(‘HELLO WORLD‘), INITCAP(‘hello world‘) FROM DUAL;
SELECT UPPER(‘hello world‘), LOWER(‘HELLO WORLD‘), INITCAP(‘hello world‘) FROM DUAL;
将列出参数“hello world”的大写、小写和首字符大写的形式。一般用来查询数据表中不确定大小写的情况。
1.1.4. TRIM、LTRIM、RTRIM
这三个TRIM函数的作用都是截去子字符串。语法形式及解释：
TRIM(c2 FROM c1) 表示从c1的前后截去c2
LTRIM(c1[, c2]) 表示从c1的左边（Left）截去c2
RTRIM(c1[, c2]) 表示从c1的右边（Right）截去c2
在后两个函数中，如果没有参数c2，就去除空格。例如：
SELECT TRIM(‘e‘ from ‘elite‘) AS t1,LTRIM(‘elite‘, ‘e‘) AS t2,RTRIM(‘elite‘, ‘e‘) AS t3 FROM DUAL;
其中最常用的是TRIM，经常用来去掉字符串前后的空格。
1.1.5. LPAD、RPAD
PAD意即补丁，LPAD和RPAD两个函数都叫做补位函数，LPAD表示LEFT PAD，在左边打补丁，RPAD表示RIGHT PAD，在右边打补丁。语法如下：
LPAD(char1, n, char2) 左补位函数
RPAD(char1, n, char2) 右补位函数
参数的含义：在字符串参数char1的左端或右端用char2补足到n位，其中参数char2可重复多次。例如在EMP表中使用左补位，将sal用$补齐6位。
1.1.6. SUBSTR
SUBSTR表示在一个字符串中截取子串，语法是：
SUBSTR(char, [m[, n]])
用于返回char中从m位开始取n个字符的子串，字符串的首位计数从1开始。参数含义如下：
如果m = 0，则从首字符开始，如果m取负数，则从尾部开始
如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止
SELECT SUBSTR(‘Doctor Who travels in TARDIS‘, 8, 25) FROM DUAL;
1.1.7. INSTR
用来返回在一个字符串中子串的位置。语法是：
INSTR(char1, char2[, n [, m]])
参数的含义：
返回子串char2在源字符串char1中的位置
从n的位置开始搜索，没有指定n，从第1个字符开始搜索
m用于指定子串的第m次出现次数，如果不指定取值1
如果在char1中没有找到子串char2 ，返回0
例如：SELECT INSTR(‘Doctor Who‘, ‘Who‘) words FROM DUAL;
结果将返回8.
2. Oracle数值操作
2.1. 数值函数
2.1.1. ROUND
数值函数指参数是数值类型的函数。语法如下：
ROUND(n[, m])用于将参数n按照m的数字要求四舍五入。其中：
- 参数中的n可以是任何数字，指要被处理的数字
- m必须是整数
- m取正数则四舍五入到小数点后第m位
- m取0值则四舍五入到整数位
- m取负数，则四舍五入到小数点前m位
- m缺省，默认值是0
例如：
- SELECT ROUND(45.678, 2) FROM DUAL; --45.68
- SELECT ROUND(45.678, 0) FROM DUAL;--46
- SELECT ROUND(45.678, -1) FROM DUAL;--50
2.1.2. trunc
TRUNC(n[, m])的功能是截取，其中n和m的定义和ROUND(n[, m])相同，不同的是功能上按照截取的方式处理数字n。例如：
- SELECT TRUNC(45.678, 2) FROM DUAL; --45.67
- SELECT TRUNC(45.678, 0) FROM DUAL;--45
- SELECT TRUNC(45.678, -1) FROM DUAL;--40
2.1.3. MOD
MOD(m, n)是取模函数，返回m除以n后的余数，如果n为0则直接返回m。例如：
--薪水值按1000取余数
SELECTename, sal, MOD(sal, 1000) FROM emp;
2.1.4. CEIL和FLOOR
CEIL(n)、FLOOR(n)这两个函数顾名思义，一个是天花板，就是取大于或等于n的最小整数值，一个是地板，就是取小于或等于n的最大整数值。比如数字n = 4.5，那么它的CEIL是5.0，它的FLOOR是4.0。在SQL语句中的例子如下：
- SELECT CEIL(45.678) FROM DUAL; --46
- SELECT FLOOR(45.678) FROM DUAL;--45
3. Oracle日期操作
CREATE TABLE test(
c1 DATE,
c2 TIMESTAMP(9));
31. 日期关键字
3.1.1. SYSDATE
SYSDATE本质是一个Oracle的内部函数，用来返回当前的系统时间，精确到秒，默认显示格式是DD-MON-RR，只有年月日并不显示时间。例如：
- SELECT SYSDATE FROM DUAL;
如果数据库是英文环境，将显示“03-MAY-14”的形式，如果是中文环境，将显示“03-5月-14”的形式。如果想显示时分秒，需要将格式转换一下：
- SELECT TO_CHAR(SYSDATE,‘yyyy-mm-dd day hh24:mi:ss‘) FROM DUAL;
运行结果是：
在建表时，可以将系统时间SYSDATE作为某一列的默认值，当插入新的记录，将会取当时的系统时间，作为数据表的一列数据保存起来。例如学生表，学生的注册时间列即默认取值数据记录插入的时间：
- CREATE TABLE student (id NUMBER(4),
- name CHAR(20),
- registerDate DATE DEFAULT SYSDATE);
3.1.2. SYSTIMESTAMP
SYSTIMESTAMP也是Oracle的内部日期函数，返回当前系统日期和时间，精确到毫秒。例如：
- SELECT SYSTIMESTAMP FROM DUAL;
- SELECT TO_CHAR(SYSTIMESTAMP,‘SSSS.FF‘) FROM DUAL;
3.2. 日期转换函数
3.2.1. TO_DATE
日期数据有时需要和字符串数据相互转换，需要用到日期转换函数，包括TO_CHAR和TO_DATE。
TO_DATE的功能是将字符串按照定制格式转换为日期类型，语法格式是：
- TO_DATE(char[, fmt[, nlsparams]])
其中：char是要转换的字符串，fmt是转换格式，nlsparams是指定日期语言。其中比较重要的是格式，常用的日期格式如下：
例子：查询2002年以后入职的员工：
- SELECTename, hiredate
- FROM emp
- WHERE hiredate>
- TO_DATE(‘2002-01-01‘,
- ‘YYYY-MM-DD‘);
3.2.2. TO_CHAR
TO_CHAR的作用是将其它类型（日期，数值）的数据转换为字符类型，主要应用在日期类型上。语法格式：
- TO_CHAR(date[, fmt[, nlsparams]])
其中fmt是格式，将日期类型数据date按照fmt格式输出字符串，nlsparams用于指定日期语言。例如：
- SELECTename,TO_CHAR(hiredate, ‘YYYY"年"MM"月"DD"日"‘)
- FROM emp;
3.3. 日期常用函数
3.3.1. LAST_DAY
LAST_DAY(date)：返回日期date所在月的最后一天，一般是在按照自然月计算某些业务逻辑，或者安排月末周期性活动时很有用处。例子：
- SELECT LAST_DAY(SYSDATE) FROM DUAL;--查询当月的最后一天
- SELECT LAST_DAY(‘20-2月-09‘) FROM DUAL;--查询09年2月的最后一天
3.3.2. ADD_MONTHS
ADD_MONTHS(date, i)：返回日期date加上i个月后的日期值.
其中：
- 参数i可以是任何数字，大部分时候取正值整数
- 如果i是小数，将会被截取整数后再参与运算
- 如果i是负数，则获得的是减去i个月后的日期值
例如计算职员入职20周年纪念日：
- SELECTename, ADD_MONTHS(hiredate, 20 * 12) "20周年"
- FROM emp;
3.3.3. MONTHS_BETWEEN
MONTHS_BETWEEN(date1, date2)：计算date1和date2两个日期值之间间隔了多少个月，实际运算是date1-date2，如果date2时间比date1晚，会得到负值。
除非两个日期间隔是整数月，否则会得到带小数位的结果，比如计算2009年9月1日到2009年10月10日之间间隔多少个月，会得到1.29个月。例如计算职员入职多少个月：
- SELECTename, MONTHS_BETWEEN(SYSDATE, hiredate) hiredate FROM emp；
3.3.4. NEXT_DAY
NEXT_DAY(date, char)：返回date日期数据的下一个周几，周几是由参数char来决定的。在中文环境下，直接使用”星期三”这种形式，英文环境下，需要使用”WEDNESDAY”这种英文的周几。为避免麻烦，可以直接用数字1-7表示周日-周六。
需要注意的是NEXT_DAY不要按字面意思理解为明天。查询下个周三是几号：
- SELECT NEXT_DAY(SYSDATE, 4) next_wedn FROM DUAL;
3.3.5. EXTRACT
EXTRACT直译是抽取或提取：
EXTRACT(date FROM datetime)：从参数datetime中提取参数date指定的数据，比如提取年、月、日。例如取出当前日期的年：
- SELECT EXTRACT(YEAR FROM SYSDATE) current_year FROM DUAL;
取出指定时间的小时：
- SELECT EXTRACT(HOUR FROM TIMESTAMP ‘2008-10-10 10:10:10‘)
- FROM DUAL;
4. 空值操作
4.1. NULL的含义
NULL是数据库里的重要概念，即空值。当表中的某些字段值，数据未知或暂时不存在，取值NULL。
Java中的简单数据类型是不能取值NULL的，在数据库中，任何数据类型均可取值NULL。
4.2. NULL操作
4.2.1. 插入NULL值
在数据表中插入记录时，如果要插入NULL值，可以用显式指定NULL值的方式，或者不插入某个字段值，即隐式表示NULL值。例如表student中：
- CREATE TABLE student(id NUMBER(4), name CHAR(20), gender CHAR(1));
- INSERT INTO student VALUES(1000, ‘李莫愁‘, ‘F‘);
- INSERT INTO student VALUES(1001, ‘林平之‘, NULL);--显式插入NULL值
- INSERT INTO student(id, name) VALUES(1002, ‘张无忌‘);--隐式插入NULL值
4.2.2. 更新成NULL值
把数据表的某个字段更新为NULL值，和更新为其他数据的语法是相同的。比如更新
- UPDATE student SET gender = NULL; --全表都被更新
注意这种更新只有在此列没有非空约束的情况下才可操作。如果gender列有非空约束，则无法更新为NULL值，上述语句会报错。
4.2.3. NULL条件查询
在条件查询中，因为NULL不等于任何值，所以不能用“列名=NULL”这种形式查询。必须用“列名 IS NULL”来判断，或者用“列名 IS NOT NULL”来查询非空数据。
- SELECT * FROM student WHERE gender IS NULL;
4.2.4. 非空约束
非空(NOT NULL)约束是约束条件的一种，用于确保数据表中某个字段值不为空。
因为在默认情况下，任何数据类型的列都允许有空值，但系统的业务逻辑可能会要求某些列不能取空值。这时需要在建表时指定该列不允许为空。
一旦某个字段被设置了非空约束条件，这个字段中必须存在有效值。即：当执行插入数据的操作时，必须提供这个列的数据，当执行更新操作时，不能给这个列的值设置为NULL。
- --创建数据表student，其中gender列不允许为空
- CREATE TABLE student
- (id NUMBER(4),
- name CHAR(20),
- gender CHAR(1) NOT NULL);
4.3. 空值函数
4.3.1. NVL
NVL(expr1, expr2)：将NULL转变为非NULL值。如果expr1为NULL，则取值expr2, expr2是非空值。
其中expr1和expr2可以是任何数据类型，但两个参数的数据类型必须是一致的。
计算员工月收入，如果comm列为空值的话，最终计算结果将是空，不符合逻辑，所以先将取NULL值的comm列转换为0，再相加。
- SELECTename, sal, comm,sal + nvl(comm, 0) salary
- FROM emp;
4.3.2. NVL2
NVL2(expr1, expr2, expr3)：和NVL函数功能类似，都是将NULL转变为非空值。NVL2用来判断expr1是否为NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3。
- SELECTename, sal, comm,
- nvl2(comm, sal + comm, sal) salary
- FROM emp;
1. SQL(基础查询)
1.1. 基本查询语句
1.1.1. FROM子句
SQL查询语句的语法如下：
- SELECT <*, column [alias], …> FROM table;
其中：SELECT用于指定要查询的列，FROM指定要从哪个表中查询。如果要查询所有列，可以在SELECT后面使用*号，如果只查询特定的列，可以直接在SELECT后面指定列名，列名之间用逗号隔开。例句如下，查询dept表中的所有记录：
- SELECT * FROM dept;
1.1.2. 使用别名
在SQL语句中可以通过使用列的别名改变标题的显示样式，或者表示计算结果的含义，使用语法是列的别名跟在列名后，中间可以加或不加一个“AS”关键字。例如：
- SELECT empno AS id ,ename "Name", sal * 12 "Annual Salary" FROM emp;
别名可以直接写，不必用双引号引起来。但是如果希望别名中区分大小写字符，或者别名中包含字符或空格，则必须用双引号引起来。
1.1.3. WHERE子句
在SELECT语句中，可以在WHERE子句中使用比较操作符限制查询结果，是可选的。
当查询条件中和数字比较，可以使用单引号引起，也可以不用，当和字符及日期类型的数据比较，则必须用单引号引起。例如查询部门10下的员工信息：
- SELECT * FROM empWHERE deptno = 10;
查询职员表中职位是’SALESMAN’的职员：
- SELECT ename, sal, job FROM emp WHERE job = ‘SALESMAN‘;
1.1.4. SELECT子句
如果只查询表的部分列，需要在SELECT后指定列名，例如：
- SELECT empno, ename, sal, job FROM emp;
1.2. 查询条件
1.2.1. 使用>, <, >=, <=, !=, <>, =
在WHERE子句中的查询条件，可以使用比较运算符来做查询。比如：查询职员表中薪水低于2000元的职员信息：
- SELECT ename, sal FROM emp WHERE sal< 2000;
查询职员表中不属于部门10的员工信息（!=等价于<>）：
- SELECT ename, sal, job FROM emp WHERE deptno != 10;
查询职员表中在2002年1月1号以后入职的职员信息，比较日期类型数据：
- SELECT ename, sal, hiredate FROM emp
- WHERE hiredate>to_date(‘2002-1-1‘,‘YYYY-MM-DD‘);
1.2.2. 使用AND，OR关键字
在SQL操作中，如果希望返回的结果必须满足多个条件，应该使用AND逻辑操作符连接这些条件，如果希望返回的结果满足多个条件之一即可，应该使用OR逻辑操作符连接这些条件。例如：查询薪水大于1000并且职位是’CLERK’的职员信息：
- SELECT ename, sal, job FROM emp
- WHERE sal> 1000 AND job = ‘CLERK‘;
查询薪水大于1000或者职位是’CLERK’的职员信息：
- SELECT ename, sal, job FROM emp
- WHERE sal> 1000 OR job = ‘CLERK‘;
（三）Oracle字符串操作