Oracle 数据库基础（二）【数据类型】

本文由简悦 SimpRead 转码，原文地址 https://blog.csdn.net/qq_43437465/article/details/90636737

一、Oracle 字符串操作

1.1 字符串类型

1.1.1 CHAR 和 VARCHAR2 类型

表示字符串数据类型，用来在表中存放字符串信息，比如姓名，职业，地址等。

CHAR 存放定长字符，即存不满补空格
VARCHAR2 存放可变长字符，存多少占用多少

eg:

如保存字符‘HELLOWORLD’, 共十个英文字母：

CHAR（100）：10 个字母，补齐 90 个空格，实际占用 100 （浪费空间，节省时间）
VARCHAR2（100）：10 个字母，实际占用 10（浪费时间，节省空间）

1.1.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 个字节

1.1.3 CHAR 和 VARCHAR2 的最大长度

CHAR 最大取值为 2000 字节
最多保存 2000 个英文字符，1000 个汉字（GBK）
VARCHAR2 最大取值为 4000 字节
最多可保存 4000 个英文字符，2000 个汉字（GBK）

?? 注意：
CHAR 可以不指定长度，默认为 1， VARCHAR2 必须指定长度。

1.1.4 LONG 和 CLOB 类型

LONG：VARCHAR2 加长版，存储变长字符串，最多可达 2GB 的字符数据

LONG 有诸多限制：
（1）每个表只能有一个 LONG 类型列
（2）不能作为主键
（3）不能建立索引
（4）不能出现在查询条件中

CLOB：存储定长或变长字符串，最多可达 4GB 的字符串数据
?? Oracle 建议开发中使用 CLOB 替代 LONG 类型

eg：

CREATE TABLE student(
 id  NUMBER(4),
 name CHAR(20),
 detail CLOB
 );

1.2 字符串函数

1.2.1 CONCAT 和 “||”

CONCAT（char1，char2）
返回两个字符串连接后的结果，两个参数 char1，char2 是要连接的两个字符串。
等价操作：连接操作符 “||”
如果 char1，char2 任何一个为 NULL，相当于连接了一个空格

eg 1:

/*
 字符串函数
 CONCAT()函数，用来连接字符串
 */
 SELECT CONCAT(ename,sal)
 FROM emp

eg 2 :

SELECT CONCAT( CONCAT(ename,','),sal)
 FROM emp

eg 3：

SELECT ename||','||sal
 FROM emp

?? 注意：建议多个字串连接时，用 “||” 更直观

1.2.2 LENGTH

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

eg:

--LENGTH函数，查看字符串长度
 SELECT ename,LENGTH(ename) FROM emp

1.2.3 UPPER,LOWER,INITCAP

大小写转换函数，用来转换字符的大小写

UPPER(char)：用于将字符转换为大写形式
LOWER(char)：用于将字符转换为小写形式
INITCAP(char)：用于将字符串中每个单词的首字母大写，其他字符小写

?? 注意：

（1）对于 INITCAP 而言，可以使用空格隔开多个单词，那么每个单词首字母都会大写。

（2）如果输入的参数是 NULL 值，仍然返回 NULL 值。
eg:

SELECT UPPER('helloword'),
        LOWER('HELLOWORD'),
        INITCAP('HELLOWORD')
 FROM  dual

此处的dual是伪表的意思
伪表：dual
 当查询的内容不和任何表中数据有关系时，可以使用伪表，伪表只会查询出一条记录。

1.2.4 TRIM,LTRIM,RTRIM

去除当前字符串中两边的指定重复字符，LTRIM 仅去除左侧的，RTRIM 则仅去除右侧的。

eg 1 :

SELECT TRIM('A' FROM 'AAAALIVEAAAAA')
 FROM dual

eg 2 :

SELECT LTRIM('AAAAALIVEAAAA','A')
 FROM dual

eg 3 :

SELECT RTRIM('AAAALIVEAAAA','A')
 FROM dual

eg 4 :

SELECT RTRIM('LIVEDBAC','ABCD')
 FROM dual

SELECT LTRIM('BBDAACDCLIVE','ABCD')
 FROM dual

?? 注意：
由后面两个例子发现， RTRIM、 LTRIM 可以去掉字符串中指定的多个字符，凡是出现的给定的字符统统去掉（不是按照字符给定的顺序去掉的）。

1.2.5 LPAD,RPAD 补位函数

eg 1:

SELECT LPAD(sal,5,'$')
 FROM emp

eg 2:
?? ?? ?? ?? ?? ??
实用的地方：实现左对齐右对齐

实现右对齐：

SELECT LPAD(sal,5,' ')
 FROM emp

实现左对齐：

 SELECT RPAD(sal,5,' ')
 FROM emp

1.2.6 SUBSTR 截取字符串

SUBSTR（char , [m,[,n]]）
用于获取字符串的子串，返回 char 中从 m 位开始取 n 个字符
如果 m = 0，则从首字符开始，如果 m 取负数，则从尾部开始
如果没有设置 n，或者 n 的长度超过了 char 的长度，则取到字符串末尾为止。

?? 注意：
（1）数据库中的下标都是从 1 开始的
（2）截取的位置可以是负数，若是则表示从倒数第几个字符开始截取

eg 1 :

SELECT SUBSTR('THINKING IN JAVA',13，4)
 FROM dual

eg 2 :

SELECT SUBSTR('THINKING IN JAVA',-4，4)
 FROM dual

1.2.7 INSTR

INSTR（char1,char2[,n,m]）函数
查找 char2 在 char1 中的位置
n 为从第几个字符开始检索
m 为第几次出现
n,m 不写则默认都是 1
eg :

SELECT INSTR('THINKING IN JAVA','IN',3，1)
 FROM dual

二、Oracle 数值操作

2.1 数值类型

2.1.1 NUMBER (P) 表示整数

完整语法： NUMBER（precision , scale）

如果没有设置 scale，则默认取值为 0，即 NUMBER(P) 表示整数
P 表示数字的总位数，取值为 1~38

用法：
一般用来在表中存放如编码、年龄、次数等用整数记录的数据

2.1.2 NUMBER (P ， S) 表示浮点数

NUMBER（precision , scale）
precision： NUMBER 可以存储的最大数字长度（不包括左右两边的 0）
scale：在小数点右边的最大数字长度（包括左侧 0）
指定了 s 但是没有指定 p，则默认 p 为 38，如：
列名 NUMBER (* , s)

用法 :
经常用来做表中存放金额，成绩等有小数位数的数据

2.2 数值函数

2.2.1 ROUND

ROUND(n [ , m]) ：用于四舍五入
参数中的 n 可以是任何数字，指要被处理的数字
m 必须是整数
m 取正数则四舍五入到小数点后第 m 位
m 取 0 值则四舍五入到整数位
m 取负数，则四舍五入到小数点前 m 为
m 缺省，默认值是 0

eg 1:

SELECT ROUND(45.68，2) FROM dual

eg 2 ：

SELECT ROUND(45.678，0) FROM dual

eg 3 :

SELECT ROUND(55.678，-2) FROM dual

2.2.2 TRUNC

TRUNC (n [ , m]) ：用于截取数字

eg 1 :

SELECT TRUNC(45.68，2) FROM dual;

eg 2 ：

SELECT TRUNC(45.678，0) FROM dual;

eg 3 :

SELECT TRUNC(55.678，-2) FROM dual

2.2.3 MOD

MOD(m,n) 求余数
eg 1 :

SELECT ename,sal, MOD(sal,1000) FROM emp

?? 注意：
如果 n 为 0 则直接返回 m

2.2.4 CEIL,FLOOR

向上取整，向下取整

eg 1 :

SELECT CEIL(45.678) FROM dual;

eg 2 ：

SELECT FLOOR(45.678) FROM dual;

三、Oracle 日期操作

3.1 日期类型

3.1.1 DATE

DATE 是 Oracle 中最常用的日期类型，用来保存日期和时间。DATE 表示的日期范围可以是公元前 4712 年 1 月 1 日至公元 9999 年 12 月 31 日。
DATE 类型在数据库中的存储固定为 7 个字节，格式为：

第 1 字节：世纪 + 100
第 2 字节：年
第 3 字节：月
第 4 字节：天
第 5 字节：小时 + 1
第 6 字节：分 + 1
第 7 字节：秒 + 1

3.1.2 TIMESTAMP

TIMESTAMP 是 Oracle 常用的日期类型。与 DATE 的区别是不仅可以保存日期和时间，还能保存小数秒，最高精度可以到 ns（纳秒）

数据库内部用 7 或者 11 个字节存储，精度为 0，用 7 字节存储，与 DATE 功能相同，精度大于 0 则用 11 字节存储。
格式为：
第 1 字节~ 第 7 字节：和 DATE 相同
第 8~11 字节：纳秒，采用 4 个字节存储，内部运算类型为整形。

3.2 日期关键字

3.2.1 SYSDATE

SYSDATE 其本质是一个 Oracle 的内部函数，返回当前的系统时间，精确到秒。

默认显示格式：
DD-MON-RR

eg:

SELECT SYSDATE FROM dual

3.2.2 SYSTIMESTAMP

内部函数，返回当前系统日期和时间，精确到毫秒。
eg:

SELECT SYSTIMESTAMP FROM dual

3.3 日期转换函数

3.3.1 TO_DATE

TO_DATE(char[,fmt [, nlsparams]])：将字符串按照定制格式转换为日期类型。
char 要转换的字符串
fmt：格式
nlsparams：指定日期语言
常用的日期格式：

eg 1:

SELECT TO_DATE('2008年08月08日20:08:08','YYYY"年"MM"月"DD"日"HH24:MI:SS') `在这里插入代码片`
FROM dual

注意：

此处说明一下，虽然指定了时分秒字段，但不显示，因为 Oracle SQL developer 默认格式就是 DD-MON-RR
在日期格式字符串中凡不是英文，符号，数字的其他字符，都需要使用双引号括起来
eg:

SELECT TO_DATE('2008年08月08日20:08:08','YYYY"年"MM"月"DD"日"HH24:MI:SS') 
FROM dual

日期的计算：
日期可以与一个数字进行加减法，这相当于加减指定的天
两个日期可以进行减法，差为相差的天。
eg:

--查看员工入职多少天了
SELECT ename,SYSDATE-hiredate FROM emp

3.3.2 TO_CHAR

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

TO_CHAR（date[,fmt[,nlsparams]]）：将日期类型数据 date 按照 fmt 的格式输出字符串。nlsparams 用于指定日期语言。
eg 1 :

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM dual

3.3.3 LAST_DAY(date)

LAST_DAY(date) ：返回日期 date 所在月的最后一天。按照自然月计算某些业务逻辑，或者安排月末周期性活动时很有用处。
eg：

--查看当月底
SELECT LAST_DAY (SYSDATE) FROM dual

3.3.4 ADD_MONTHS(date,i)

ADD_MONTHS(date,i)：返回日期 date 加上 i 个月后的日期值。
参数 i 可以是任何数字，大部分时候取正值整数
如果 i 是小数，将会被截取整数后再参与运算
如果 i 是负数，则获得的是减去 i 个月后的日期值。
eg 1:

查看每个员工入职20周年纪念日
SELECT ename,ADD_MONTHS(hiredate,12*20) FROM emp

3.3.5 MONTHS_BETWEEN(date1,date2)

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

eg:

SELECT MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp

3.3.6 NEXT_DAY(date,char)

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

?? 注意：

NEXT_DAY 不是明天！！！！
1 表示周日，2 表示周一，依次类推

eg:

查看下一个周天是几月几号
SELECT NEXT_DAY(SYSDATE,7) FROM dual

3.3.7 LEAST,GREATEST

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

eg 1:

SELECT LEAST(SYSDATE,TO_DATE('2008-08-08','YYYY-MM-DD')) FROM dual

eg 2 ：

?? 注意：
求最小值与最大值，除了日期外，常用的数字也可以比较大小

SELECT LEAST(98,27) FROM dual

3.3.8 EXTRACT

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

eg:

查看1980年入职的员工
SELECT ename,sal FROM emp WHERE EXTRACT(YEAR FROM hiredate)=1980

四、Oracle 空值操作

4.1 NULL 的含义

数据库里的重要概念：NULL，即空值
有时表中的某些字段值，数据未知或暂时不存在，取值 NULL
任何数据类型均可取值 NULL

4.2 NULL 的操作

4.2.1 插入 NULL 值

eg:

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

INSERT INTO student VALUES(0001,'李莫愁','F');

INSERT INTO student VALUES(0002,'林平之',NULL);  //--显式插入NULL值

INSERT INTO student(id,name) VALUES(0003,'张无忌');//--隐式插入NULL值
SELECT * FROM student
COMMIT

4.2.2 更新成 NULL 值

UPDATE student SET gender = NULL;

?? 注意：

这种更新只有在此列没有非空约束的情况下才可操作
如果某列有非空约束，则无法更新为 NULL 值，上述语句会报错

eg:

--将1更新为NULL值
UPDATE student SET gender= NULL WHERE id=0001
SELECT * FROM student

4.2.3 NULL 条件查询

判断字段的值是否为 NULL 需要使用 IS NULL 或者 IS NOT NULL

eg:

CREATE TABLE student(
   id   NUMBER(4),
   name CHAR(20),
   gender CHAR(1)
);
INSERT INTO student VALUES(0001,'李莫愁','F');
INSERT INTO student VALUES(0002,'林平之',NULL);
INSERT INTO student(id,name) VALUES(0003,'张无忌');
DELETE FROM student WHERE gender IS NULL;
SELECT * FROM student

4.2.4 NULL 值的运算操作

NULL 与任何数字运算结果还是 NULL
NULL 与字符串拼接等于什么都没干

eg：

查看每个员工的收入
SELECT ename,sal,comm,sal+comm FROM emp

4.3 空值函数

4.3.1 NVL

空值函数 NVL（arg1,arg2）
当 arg1 为 NULL，函数返回 arg2 的值，若不为 NULL，则返回 arg1 本身。
所以该函数的作用是将 NULL 值替换为一个非 NULL 值。

eg:

SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp

?? 注意：

在此说明一下，上面例子在查询员工的工资时，直接使用 sal+comm(工资 + 绩效)，这样有点问题，因为有的员工的绩效值为 NULL，而且 NULL 与任何数字运算结果还是 NULL，所以在使用 sal+comm 时会出现员工的工资为 NULL。
所以此时使用 NVL(comm,0) 就很好地解决了这个问题，这个函数将所有绩效值为 NULL 的全部替换为 0，所以避免了员工工资出现 NULL 值的情况。

4.3.2 NVL2

NVL2（arg1,arg2,arg3）
当 arg1 不为 NULL，则函数返回 arg2
当 arg1 位 NULL，则函数返回 arg3
该函数是根据一个值是否为 NULL 来返回两个不同结果

eg:

/*
查看每个人的绩效情况，即：
有绩效的，显示为“有绩效”；
没绩效的，显示为“没有绩效”
*/
SELECT ename,sal,NVL2(comm,'没有绩效'，'有绩效'）FROM emp

posted @ 2020-03-14 20:57 冬日的暖阳阅读(308) 评论(0) 收藏举报

刷新页面返回顶部

冬日暖阳

一个爱好技术的男子，一个外冷内热的男人！

Oracle 数据库基础（二）【数据类型】

一、Oracle 字符串操作

1.1 字符串类型

1.1.1 CHAR 和 VARCHAR2 类型

1.1.2 CHAR 和 VARCHAR2 的存储编码

1.1.3 CHAR 和 VARCHAR2 的最大长度

1.1.4 LONG 和 CLOB 类型

1.2 字符串函数

1.2.1 CONCAT 和 “||”

1.2.2 LENGTH

1.2.3 UPPER,LOWER,INITCAP

1.2.4 TRIM,LTRIM,RTRIM

1.2.5 LPAD,RPAD 补位函数

1.2.6 SUBSTR 截取字符串

1.2.7 INSTR

二、Oracle 数值操作

2.1 数值类型

2.1.1 NUMBER (P) 表示整数

2.1.2 NUMBER (P ， S) 表示浮点数

2.2 数值函数

2.2.1 ROUND

2.2.2 TRUNC

2.2.3 MOD

2.2.4 CEIL,FLOOR

三、Oracle 日期操作

3.1 日期类型

3.1.1 DATE

3.1.2 TIMESTAMP

3.2 日期关键字

3.2.1 SYSDATE

3.2.2 SYSTIMESTAMP

3.3 日期转换函数

3.3.1 TO_DATE

3.3.2 TO_CHAR

3.3.3 LAST_DAY(date)

3.3.4 ADD_MONTHS(date,i)

3.3.5 MONTHS_BETWEEN(date1,date2)

3.3.6 NEXT_DAY(date,char)

3.3.7 LEAST,GREATEST

3.3.8 EXTRACT

四、Oracle 空值操作

4.1 NULL 的含义

4.2 NULL 的操作

4.2.1 插入 NULL 值

4.2.2 更新成 NULL 值

4.2.3 NULL 条件查询

4.2.4 NULL 值的运算操作

4.3 空值函数

4.3.1 NVL

4.3.2 NVL2

公告