Oracle数据库之常用数据类型及其操作

Oracle数据库之常用数据类型及其操作
1.Oracle数据库的常用数据类型
2.数据类型操作
2.1：虚表dual
oracle中提供的一个没有数据的表名。

是给一些基础操作(如计算等)提供的基础表。

一下所有函数使用都可以在虚表中验证
比如使用数据库计算1和2的和
select 1+2 from dual;
2.2：字符串函数的使用：
char：定长字符串类型，最大长度2000个字节，不指定长度，默认值是1个字节
varchar2：可变长字符串类型，最大长度为4000个字节，必须制定长度
long:varchar2的加长版，最大长度为2G字节，但是一个表中只能有一个long类型的字段，此字段不能做主键也不能做索引。

clob 可以替代long类型，最大长度为4G*数据块大小字节，没有long类型的限制
默认单位：字节
如：char(10):表示占用内存10个字节。

相当于char(10,byte)
也可以指定单位为字符：
char(10,char):表示占用内存10个字符，占用的字节数与字符集有关
◆函数1：拼接函数
concat(p1,p2):
将字符串进行无缝拼接。

可以使用||代替拼接函数。

p1||p2
◆函数2:统计长度函数
length(p1);
统计p1字符个数。

----------这是字符不是字节。

◆函数3：补位函数。

左补位函数：lpad(p1,n,p2)
表示使用p2,在p1左边进行补位，总长度为n
相当于右对齐
n单位为字节
右补位函数：rpad(p1,n,p2)
表示使用p2,在p1右边进行补位，总长度为n
相当于左对齐
n单位为字节
◆函数4:大小写函数
lower(p1):
将p1中的字母都变成小写
upper(p1):
将p1中的字母都变成小大写写
initcap(p1) :
将p1中的首字母都变成小大写，空格隔开算一个字符串。

"avbb baa" "AvbbBaa";
◆函数5：截去函数：
trim(p2 from p1);
从p1的前后截去p2,p2必须是一个字符
ltrim(p1,p2);
从p1的左边截去含有p2任意字符的字符。

判断p2里面任意一个字符，是否在在p1的左边开始含有，如果有那么，就截去这个字符，如果开始没有但是后面有，那么也不截去
例如：p1"abcdefghigk"
第一种情况:p2:'abc'
先比较a在p1开头里面存在截去，p1为'bcdefghigk'。

b在p1开头里面存在，截去，p1为'cdefghigk'。

c在p1里面存在，截去，
p1为'defghigk'。

d在p1里面不存在，停止，
第二种情况：p2'bc'
先比较'b'在p1开头里面不存在终止
rtrim(p1,p2);
同理。

◆函数6:截去字串操作。

substr(p1,start[,len])
对p1进行截去，从start位置开始往后截取，没有第三个参数的时候，截取到最后，
有第三个参数的时候，len表示截取的长度。

start为0或者1的时候，都表示从头截取。

start为负数的时候，表示从后往前数start个。

◆函数7，返回字符串位置
instr(p1,p2[[,m],n])
作用是返回p2子串在p1中出现的位置。

m:表示从p1的第m个字符开始往检索
n：表示p2第n次出现
instr(p1,p2)返回子串在p1中第一次出现的位置。

instr(p1,p2,m)返回子串在p1中第m位置后第一次出现的位置。

instr(p1,p2,m,n)返回子串在p1中第m位置后第n次出现的位置。

备注：没有子串返回0
2.3. 数值函数操作
number(p,s):
p:数值的有效长度
s:小数点后最多的位置。

可以不传
p-s:整数位置的最大值。

number(p):相当于s=0，p的取值范围在1-38之间
也可以不指定p
number(*,s);p的位置，p的取值范围在1-38之间；
为了考虑数据库的移植和兼容问题，oracle提供了其他的数据类型。

numeric(p,s)相当于number(p,s)
decimal(p,s)/dec(p,s)相当于number(p,s)
integer/int相当于number(38)；
smallint相当于number(38)；
float相当于number
double precision 相当于number
real相当于number,整数有效位19
◆函数1：四舍五入函数
round(p[,n]);
对p进行四舍五入，保留小数点后n位。

n可以不写，相当于0
n可以为负数，保留到小数点前n位。

◆函数2：截取函数
trunc(p[,n]);
对p进行截取操作，保留小数点后n位
n可以不写，相当于0，只截取整数
n可以为负数，截取小数点前n位。

◆函数3：取余函数
mod(m,n);
表示m对n取余
◆函数4：向上取整/向下取整函数
ceil(m)/floor(m)
ceil(m):表示取大于等于m的最小整数
floor(m)：表示取小于等于m的最大整数。

2.4. 日期操作函数
date:日期类型，默认格式'dd-mon-rr',占七个字节大小。

第一个字节表示：表示世纪+100
第二个字节表示：年
第三个字节：月
第四个字节表示：日
第五个字节：时
第六个字节：分
第七个字节：秒
timestamp：时间戳类型，最长占是一个字节
前七个字节和date一样，后四个字节存储的是精确度，可以精确到纳秒。

关键字：
sysdate:返回的是当前系统时间。

返回的类型是date
systimestamp:返回的是当前系统时间，返回类型是timestamp
格式：
YYYY:表示年
MM:月
DD:日
HH24:24小时制的时
HH/hh12:12小时制的时
MI:分
ss:秒
AM:表示上下午。

day/dy:表示星期
d:表示一周的第几天
ddd:表示一年的第几天
◆函数1.
to_char(p1,fmt)；通常在查询语句中使用
作用是p1按照格式fmt进行显示
p1：日期类型：
fmt：字符串，格式
◆函数2：
to_date(p1,fmt)
作用是将p1按照格式fmt变成日期类型
p1:字符串类型
fmt：字符串，格式
◆函数3：
last_day(p1)
查看p1日期所在月的最后一天的日期。

◆函数4：
add_months(p1,n);
表示查看p1日期的n个月后的日期
n：表示增加的月数
n：可以表示小数，但是会trunc截取整数部分
n：可以为负数。

◆函数5：
months_between(p1,p2)
计算p1日期和p2日期相差的月数
可能为小数
计算规则：p1-p2,
可能为负数
◆函数6
next_day(date,num);
计算某一个日期的下一个周几的日期
date 某一日期
num：范围从1到7，表示周日到周六
◆函数7
least(p1[,p2][,p3]........)
比较多个参数中，取最小的值
参数要求：必须统一类型，或者是可以默认转换成统一类型的类型。

◆函数8
greatest(p1[,p2][,p3]........)
同上，只不过是取最大的值，类型转换以第一个参数为准。

默认转换的时候，基于第一个参数的类型。

转换不了的时候会报错。

如果是字符串的话，字符串类型比较大小，比较的是字符对应的ASCII码
◆函数9
extract(分量from date):
作用：从日期date中提取日期的分量
分量有
年：year
月：month
日：day
时：hour
分：minute
秒：second
从sysdate中提取不出，时分秒。

因为他的精确度只到日。

而systimestamp，却可以。

2.5. 控制操作函数
1.null，是空的意思
在表中，在默认情况下，所有的字段的值都可以为空，
2.not null,是一种约束，可以对字段进行约束
表示，这个字段的值不能为空，必须要有有效值。

3.插入语句中，
reg：
inset into emp (empno,ename) values (00,'战三')
没有赋值的字段，就是null
故意插入空值
4.修改时
reg：空值作为条件的时候，
update emp set ename='张三' where comm is null.
reg:修改为空值的时候，
update emp set ename=nul where .......
5.空值用来计算的时候。

null可以理解为无穷大
所以，任何一个数加上null都是null
因此有一些空值函数，
◆nvl(p1,p2):
作用：如果p1的值为null，就使用p2
如果不为null时候，使用p1本身
◆nvl2(p1,p2,p3):Oracle独有的
表示，如果p1为null，使用p3，
如果不是null，使用p2。

2.6. 聚合函数—只能用在分组中
怎么用：所需要的数据，表中不存在，要经过计算才可以得到。

并且得到的数据是由多条记录计算出，显示成一条，也就是表中的数据进行了分组统计。

这样的数据需要使用聚合函数，也叫做分组函数、多行函数。

聚合函数会忽略字段之中的空值。

聚合函数括号内传入字段。

◆max()/min()
max():统计每组之中最大值，
min():统计每组之中的最小值
◆sum():累加每组之中的数据之和。

◆avg():求每组之中的数据平均数,需要进行空值处理。

因为空值的时候，是不算空值所在的记录的。

所有
的聚合函数都不算空值，但是应该想得到空值处理。

◆count():统计每组的记录数，
参数可以传入通配符*来表示任意字段，可以作为统计记录总数。