Oracle

Oracle字符串操作

字符串类型

CHAR和VARCHAR2类型 CHAR是不可变长的字符串，即存不满补空格；VARCHAR2是可变长的，存多少占用多少。 CHAR浪费空间，节省时间；VARCHAR2浪费时间，节省空间。

CHAR和VARCHAR2的存储编码 默认单位是字节，可以指定为字符。 CHAR(10 CHAR)，VARCHAR2(10 CHAR) 20个字节

CHAR和VARCHAR2的最大长度 CHAR最大长度为2000，VARCHAR2最大长度为4000。 CHAR可以不指定长度，默认为1，但是VARCHAR2必须指定长度。

LONG和CLOB类型 LONG：是VARCHAR2加长版，存储变长字符串，最多打2GB。但是每个表只能有一个LONG类型列，不能作为主键，不能建立索引，不能出现在查询条件中。 CLOB：存储定长或变长字符串，最多达4GB。 更多时候是使用CLOB

字符串函数

CONCAT和“||” CONCAT(char1,char2)：连接两个字符串 ||：连接操作符，等价于CONCAT，但是||的效果更好，操作更简便

//连接3个字符串就比较麻烦 CONCAT(CONCAT(char1,char2),char3) char1 || char2 || char3 //后者操作更加简便

如果char1，char2任何一个为NULL，相当于连接了一个空格。

LENGTH LENGTH(char)：返回字符串长度。如果是CHAR类型，则返回的是所有的长度(包括空格的)，如果是VARCAHR2，返回的是实际的长度。

UPPER、LOWER和INITCAP UPPER(char)：将字符转换为大写 LOWER(char）：将字符转换为小写 INITCAP(char)：将字符串的每个单词的首字母转换为大写的 如果输入的参数为NULL，则返回的也是NULL

TRIM、LTRIM和RTRIM 作用：截去字符串 语法形式： —TRIM(c2 FROM c1)：从c1的前后截去c2 —LTRIM(c1,[c2])：从c1的左边截去c2，如果没有c2，则截去空格 —RTRIM(c1.[c2])：从c1右边截去c2，如果没有则截去空格 TRIM更多的是用来截去字符串两边的空格

LPAD、RPAD 补位函数，用于在字符串char1的左端或右端用char2补足到n位，char2可以重复多次。 —LPAD(char1,n,char2)：左补位函数 —RPAD(char1,n,char2)：右补位函数

SUBSTR SUBSTR(char,[m,[n]])：用于获取字符串的子串，返回char中从m位开始取到n位。 如果m=0，则从首位开始；如果m<0，则从尾部的m为开始取到字符串结束； 如果没有设置n，则直接取到末尾为止。

SELECT SUBSTR('doctor who travels in china',8,20) FROM dual

INSTR INSTR(char1,char2[n[,m]])：返回子串char2在源字符串char1的位置。 参数：n，从n位置开始，若没有n则从第一个字符开始；m用于指定子串的第m次出现时的位置，若不设置则为1；如果没有找到char2，则返回0。

Oracle数值操作

数值类型

NUMBER(P)表示整数NUMBER(P,S)表示浮点数

数值函数

ROUND ROUND(n[,m])：用于四舍五入 n：指要被处理的数字。 m：必须为整数，m为正数则四舍五入到小数点后m位；m为0则四舍五入到整数； m为负数则四舍五入到小数点前m位。 m缺省，默认为0。

SELECT ROUDN(45.678,2) FROM dual 45.68 SELECT ROUDN(45.678,0) FROM dual 46 SELECT ROUDN(45.678,-1) FROM dual 50

TRUNC TRUNC(n[,m])：用于截取 n，m的定义和ROUDN(n[,m])相同，不同的是截取方式不同（没有四舍五入）。

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

MOD MOD(m，n)：返回m除以n后的余数 n为0则返回m，注意与数学上的区别。

CEIL和FLOOR CEIL(n)：向上取整 FLOOR(n)：向下取整

Oracle日期操作

日期类型

DATE

TIMESTAMP

日期关键字

SYSDATE 其本质是一个Oracle的内部函数，返回当前系统时间，精确到秒。 默认格式为：DD-MON-RR。

SYSTIMESTAMP 返回当前系统日期和时间，精确到毫秒。

SELECT SYSTIMESTAMP FROM DUAL; SELECT TO_CHAR(STSTIMESTAMP,'ssss.FF') FROM DUAL;

日期转换函数

TO_DATE TO_DATE(char[,fmt[,nlsparams]])：将字符串按照定制的格式转换为日期类型。 —char：转换的字符串 —fmt：格式 —nlsparams：指定日期语言 如：查询2002年以后入职的员工

SELECT enamel,hiredate FROM emp WHERE hiredate > TO_DATE('2002-01-01','YYYY-MM-DD');

TO_CHAR 将其他类型的数据转换为字符串类型

SELECT enamel，TO_CHAR(hiredate,'YYYY“年”MM“月”DD“日”) FROM emp;

日期常用函数

LAST_DAY LAST_DAY(date)：返回日期所在月的最后一天。

SELECT LAST_DAY('20-7月-17') FROM dual 返回2017-7月-31

ADD_MONTHS ADD_MONTHS(date,i)：返回date加上i个月后的日期 参数i为小数则截取整数进行运算。

MONTHS_BETWEEN MONTHS_BETWEEN(date1,date2)：返回两个日期相隔的月数，可返回负值，小数值。

NEXT_DAY NEXT_DAY(date,char)：返回date日期数据的下一个周几，其中char为周几。 直接用1-7表示周日-周六。

SELECT NEXT_DAY(SYSDATE,4) FROM DUAL;

LEAST、CREATEST LEAST(参数1,参数2…)、CREATEST(参数1,参数2…)：比较参数之间的大小，返回最大值或最小值。 参数的类型必须一致

EXTRACT EXTRACT(date FROM datetime)：从参数datetime中提取参数date，比如提取年月日。

SELECT EXTRACT(YEAR FROM STSDATE) FROM DUAL;

空值操作

NULL含义

NULL的含义：空值，任何数据类型均可以取值NULL

NULL的操作

插入NULL值

CREATE TABLE student( id NUMBER(4), name VARCHAR2(20), gender CHAR(1), )

显示插入：

INSERT INTO student VALUES(1000,'jack',NULL);

隐式插入：

INSERT INTO student VALUES(1000,'jack');

更新成NULL值 UPDATE student SET gender=NULL; 注意此种情况只有此列没有非空约束才可以进程操作。

NULL条件查询 SELECT * FROM student WHERE gender IS NULL; 注意不是用“=”，因为NULL不等于任何值。他是空的。

非空约束 NOT NULL ：非空约束，如果进行插入操作，某一列为NOT NULL 则必须插入有效值，否则出错。

空值函数

NVL NVL(参数1,参数2)：将NULL转变为非NULL，如果参数1为NULL，则取值参数2。 参数可以为任何类型，但是参数1和参数2类型必须一致。

NVL2 NVL2(参数1,参数2,参数3)：如果参数1不是NULL，返回参数2，如果是NULL则返回参数3。