Oracle_2

Oracle字符串操作

字符串类型

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

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

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

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

字符串函数

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

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

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

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

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

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

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

6. 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

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

Oracle数值操作

数值类型

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

数值函数

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

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

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

   SELECT TRUNC(45.678,2) FROM dual     45.67SELECT TRUNC(45.678,0) FROM dual     45SELECT TRUNC(45.678,-1) FROM dual     40

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

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

Oracle日期操作

日期类型

1. DATE
   

2. TIMESTAMP
   

日期关键字

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

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

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

日期转换函数

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

   SELECT enamel,hiredateFROM empWHERE hiredate > TO_DATE('2002-01-01','YYYY-MM-DD');

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

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

日期常用函数

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

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

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

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

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

   SELECT NEXT_DAY(SYSDATE,4) FROM DUAL;

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

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

   SELECT EXTRACT(YEAR FROM STSDATE) FROM DUAL;

空值操作

NULL含义

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

NULL的操作

1. 插入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');

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

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

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

空值函数

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

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