12 月 连接字符串 字母 最大数 rac bsp 本质 zhs16gbk 中文字符
一. Oracle 字符串操作
字符串类型 1. CHAR 和 VARCHAR2 类型表示字符串数据类型,用来在表中存放字符串信息;
CHAR 存放定长字符,即存不满补空格(浪费空间,节省时间);VARCHAR2 存放变长字符,存多少占用多少(浪费时间,节省空间);
按照字符的自然顺序排序。
2. CHAR 和 VARCHAR2 的存储编码默认单位是字节,可指定为字符
— CHAR(10),等价于 CHAR(10 BYTE)
— 指定单位为字符:CHAR(10 CHAR),20 个字节
— VARCHAR2(10),等价于 VARCHAR2(10 BYTE)
— 指定单位为字符:VARCHAR2(10 CHAR),20 个字节
每个英文字符占用一个字节,每个中文字符按编码不同,占用 2-4 个字节
— ZHS16GBK:2 个字节
— UTF-8:2-4 个字节
3. CHAR 和 VARCHAR2 的最大长度CHAR 最大取值为 2000 字节,最多保存 2000 个英文字符
VARCHAR2 最大取值为 4000 字节
CHAR 可以不指定长度,默认为 1,VARCHAR2 必须指定长度
4. LONG 和 CLOB 类型
LONG:VARCHAR2 加长版,存储变长字符串,最多达 2GB 的字符串数据
LONG 有诸多限制:每个表只能有一个 LONG 类型列;不能作为主键;不能建立索引;不能出现在查询条件中
CLOB:存储定长或变长字符串,最多达 4GB 的字符串数据
ORACLE 建议开发中使用 CLOB 替代 LONG 类型
字符串函数1. CONCAT 和 "||"
CONCAT(char1, char2);
— 返回两个字符串连接后的结果,两个参数 char1,char2 是要连接的两个字符串
等价操作:连接操作符 "||"
如过 char1 和 char2 任何一个为 NULL,相当于连接了一个空格
"||" 在数据库中是连接字符串,相当于 java 中的 "+",注意和 java"||" 区分
eg:
java 中:"hello" + "world" ==> "helloworld"
DB 中:'hello'||'world' ==> 'hello world'
oracle 中:CONCAT('hello','world') ==> 'hello world'
2. FROM DUAL(虚表)DUAL:虚表,没有这么一个表,只为了满足 SELECT 的语法要求。
— 我们常用虚表来测试表达式的结果。
— 在数据库中,我们想测试某个表达式的结果只能使用 SELECT 语句来实现
— 什么时候使用虚表:当 SELECT 语句中没有任何表中的字段参与时
— eg: 假设表 emp 中 name 和 sal 两个字段存储的内容分别是名字和薪资
SELECT name||':'||sal FROM emp
则所得结果为:boss:5000
3. LENGTH
LENGTH(char):用于返回字符串的长度
如果字符类型是 VARCHAR2,返回字符的实际长度,如果字符类型是 CHAR,长度还要包括后补的空格
eg: SELECT name,LENGTH(name) FROM emp;
所得结果:boss 4(name 为 CHAR)
4. UPPER,LOWER 和 INITCAP大小写转换函数,用来转换字符的大小写
UPPER(char) 用于将字符转换为大写形式
LOWER(char) 用于将字符转换为小写形式
INITCAP(char) 用于将字符串中每个单词的首字符大写,其它字符小写,单词之间用空格和非字母字符分隔
如果熟人的参数是 NULL 值,仁返回 NULL 值
5. TRIM,LTRIM,RTRIM作用:截去子串
语法形式:
— TRIM(c2 FROM c1) 从 c1 的前后截去 c2
— LTRIM(c1[, c2])从 c1 的左边 (Left) 截去 c2
— RTRIM(c1[, c2])从 c1 的右边 (Right) 截去 c2
如果没有 c2 就去除空格
eg:SELECT TRIM('e' from 'eeeeliteeee') FROM DUAL;
参数中 from 前面只能是单一字符
若没有 from 以及前面的字符,则是去除空白
eg:SELECT LTRIM('eeeeliteeee','e') FROM DUAL;
SELECT RTRIM('eeeliteee','e') FROM DUAL;
不指定第二个参数,默认是去除空白
6. LPAD, RPAD补位函数,用于在字符串 char1 的左端或右端用 char2 补足到 n 位,char2 可重复多次
— LPAD(char1, n, char2) 左补位函数
— RPAD(char1, n, char2) 右补位函数
在 emp 表中使用左补位,将 sal 用 $ 补齐 20 位
eg:SELECT name, LPAD(sal, 20,'$') as"salary" FROM emp;
作用:要求显示 20 个字符,若 sal 的值不足长度,则补充若干个'$',以达到 20 个字符
eg:SELECT RPAD('aaaaAAAAA') FROM DUAL;
得到结果为 aaaaA
7. SUBSTR
SUBSTR(char, [m[, n]])
— 用于获取字符串的子串,返回 char 中从 m 位开始取 n 个字符
如果 m=0,则从首字符开始,如果 m 取负数,则从尾部开始
如果没有设置 n,或者 n 的长度超过了 char 的长度,则取到字符串末尾为止
字符串的首位计数从 1 开始
8. INSTR
INSTR(char1, char2[, n [, m]]);
返回子串 char2 在原字符串 char1 中的位置
参数:
— 从 n 的位置开始搜索,没有指定 n,从第一个字符开始搜索
— m 用于指定子串的第 m 次出现次数,如果不指定取值 1
— 如果在 char1 中没有找到子串 char2,返回 0
二. Oracle 数值操作数值类型 1. NUMBER(P) 表示整数完整语法:NUMBER(precision, scale)
— 如果没有设置 scale,则默认取值 0,即 NUMBER(p) 表示整数
— P 表示数字的总位数,取值为 1-38
用来在表中存放如编码,年龄,次数等用整数记录的数据
2. NUMBER(P, S) 表示浮点数NUMBER(precision, scale)
— precision:NUMBER 可以存储的最大数字长度(不包括左右两边的 0)
— scale:在小数点右边的最大数字长度(包括左侧 0)
指定了 s 但是没有指定 p,则 p 默认为 38
经常用于表中存放金额,成绩等有小数的数据。
NUMBER 的变种数据类型:内部实现是 NUMBER,可以将其理解为 NUMBER 的别名,目的是多种数据库及编程语言兼容
— NUMERIC(p, s):完全映射至 NUMBER(p, s)
— DECIMAL(p, s) 或 DEC(p, s):完全映射至 NUMBER(p, s) 类型
— INTEGER 或 INT:完全映射至 NUMBER(38) 类型
— SMALLINT:完全映射至 NUMBER(38) 类型
— FLOAT(b):映射至 NUMBER 类型
— DOUBLE PRECISION:映射至 NUMBER 类型
— REAL:映射至 NUMBER 类型
数值函数 1. ROUNDROUND(n[, m]):用于四舍五入
— 参数中的 n 可以是任何数字,指要被处理的数字
— m 必须是整数
— m 取正数则四舍五入到小数点后第 m 位
— m 取 0 值则四舍五入到整数位
— m 取负数,则四舍五入到小数点前 m 位
— m 缺省,默认是 0
eg:SELECT ROUND(45.678, -1) FROM DUAL; —50
2. TRUNCTRUNC(n[, m]):用于截取
— n 和 m 的定义和 ROUND(n[, m]) 相同,不同的是功能上按照截取的方式处理数字 n
eg:SELECT TRUNC(45.678, -1) FROM DUAL; —40
3. MOD
MOD(m, n):返回 m 除以 n 后的余数
—n 为 0 则直接返回 m
4. CEIL 和 FLOORCEIL(n),FLOOR(n) 这两个函数,一个是取大于或等于 n 的最小整数值,另一个是取小于或等于 n 的最大整数值
eg:SELECT CEIL(45.678) FROM DUAL; —46
SELECT FLOOR(45.678) FROM DUAL; —45
三. Oracle 日期操作日期类型 1. DATEORACLE 中最常用的日期类型,用来保存日期和时间
DATE 表示的日期范围可以是公元前 4712 年 1 月 1 日至公元 9999 年 12 月 31 日
DATE 类型在数据库中的存储固定为 7 个字节,格式为:
— 第一个字节:世纪 + 100
— 第二个字节:年
— 第三个字节:月
— 第四个字节:天
— 第五个字节:小时 + 1
— 第六个字节:分 + 1
— 第七个字节:秒 + 1
2. TIMESTAMPORACLE 常用的日期类型
与 DATE 的区别是不仅可以保存日期和时间,还能保存小数秒,最高精度可以到 ns(纳秒)
数据库内部用 7 或者 11 字节存储,精度为 0,用 7 字节存储,与 DATE 功能相同,精度大于 0 则用 11 字节存储
格式为:
— 第 1 字节 - 第 7 字节:和 DATE 相同
— 第 8-11 字节:纳秒,采用 4 个字节存储,内部运算类型为整型
日期关键字 1. SYSDATE其本质是一个 ORACLE 的内部函数,返回当前的系统时间,精确到秒
默认显示格式是 DD-MON-RR
2. SYSTIMESTAMP内部函数,返回当前系统日期和时间,精确到毫秒
日期转换函数 1. TO_DATETO_DATE(char[, fmt[, nlsparams]]):将字符串按照定制格式转换为日期类型
— char:要转换的字符串
— fmt:格式
— nlsparams:指定日期语言
— 常用的日期格式见表
YY | 2 位数字的年份 |
YYYY | 4 位数字的年份 |
MM | 2 位数字的月份 |
MON | 简拼的月份 |
MONTH | 全拼的月份 |
DD | 2 位数字的天 |
DY | 周几的缩写 |
DAY | 周几的全拼 |
HH24 | 24 小时制的小时 |
HH12 | 12 小时制的小时 |
MI | 显示分钟 |
SS | 显示秒 |
将其它类型的书籍转换为字符类型
TO_CHAR(date[, fmt[, nlsparams]]):将日期类型数据 date 按照 fmt 的格式输出字符串。nlsparams 用于指定日期语言
需要注意的是:在日期格式字符串中,出现的非关键字符或符号的其它字符时,需要使用双引号
eg:SELECT TO_CHAR(SYSDATE,"yyyy"year"mm"month"dd"day"hh:mi:ss") FROM DUAL;
两个日期可以进行减法操作,差为相差的天数
LAST_DAY(date):返回日期 date 所在月的最后一天
在按照自然月计算某些业务逻辑,或者安排月末周期性活动时很有用处
eg:SELECT LAST_DAY(SYSDATE) FROM DUAL; —30-9 月-17
SELECT LAST_DAY('20-2 月 - 09') FROM DUAL; —28-2 月-09
2. ADD_MONTHSADD_MONTHS(date, i):返回日期 date 加上 i 个月后的日期值
— 参数 i 可以是任何数字,大部分时候取正值整数
— 如果 i 是小数,将会被截取整数后再参与运算
— 如果 i 负数,则获得的是减去 i 个月后的日期值
eg:计算职员入职 20 周年纪念日
SELECT name, ADD_MONTHS(hiredate, 20 * 12) as '20 周年' FROM emp;
3. MONTHS_BETWEENMONTHS_BETWEEN(date1, date2):计算 date1 和 date2 两个日期值之间间隔了多少个月
实际运算是 date1-date2,如果 date2 时间比 date1 晚,会得到负值
除非两个日期间隔是整数月,否则会得到带小数位的结果。
— 此时可以使用 FLOOR 得到整月
eg:SELECT name, FLOOR(MONTHS_BETWEEN(SYSDATE, hiredate)) FROM emp;
4. NEXT_DAYNEXT_DAY(date, char):返回 date 日期数据的下一个周几,周几是由参数 char 来决定的
在中文环境下,直接使用 "星期三" 这种形式,英文环境下,需要使用 "WEDNESDAY" 这种英文的周几。位避免麻烦,可以直接使用 1-7 表示周日-周六
NEXT_DAY 不是明天
SELECT NEXT_DAY(SYSDATE, 3) FROM DUAL; —离现在最近的周 2(不包含今天)
5. LEAST, GREATESTGREAGEST(expr1[, expr2[, expr3]]...)
LEAST(expr1[, expr2[,expr3]]...)
也被称作比较函数,可以有多个参数值,返回结果是参数列表中最大或最小的值
参数类型必须一致
在比较之前,在参数列表中第二个以后的参数会被隐含的转换为第一个参数的数据类型,所以如果可以转换,则继续比较,如果不能转换将会报错。
6. EXTRACTEXTRACT(date FROM datetime):从参数 date time 中提取参数 date 指定的数据,比如提取年,月,日
eg:SELECT EXTRACT(HOUR FROM TIMESTAMP'2008-01-01 10:10:10') FROM DUAL;
四. 空值操作 NULL 的含义数据库里的重要概念:NULL,即空值
有时表中的某些字段值,数据未知或暂时不存在,取值 NULL
任何数据类型均可取值 NULL
NULL 的操作1. 插入 NULL 值和更新成 NULL 时只有在非空约束时才可操作
查询条件:WHERE name IS NULL/ WHERE name IS NOT NULL
任何数据和 NULL 相加都是 NULL
2. 非空约束非空(NOT NULL)约束用于确保字段值不为空
默认情况下,任何列都允许有空值
当某个字段被设置了非空约束条件,这个字段中必须存在有效值。
NVL(expr1, expr2):将 NULL 转变为非 NULL 值
— 如果 expr1 为 NULL,则取值 expr2,expr2 是实际值
— expr1 和 expr2 可以是任何数据类型,但两个参数的数据类型必须是一致的
2. NVL2
NVL2(expr1, expr2, expr3):和 NULL 函数功能类似,都是将 NULL 转变为实际值
NVL2 用来判断 expr1 是否为 NULL,如果不是 NULL,返回 expr2,如果是 NULL,返回 expr3
Oracle 数据基础 (一)
来源: http://www.bubuko.com/infodetail-2452963.html