Oracle主要数据类型
概述各种数据库所支持的数据类型大同小异与标准SQL语言中的数据类型可能略有出入
类型varchar可以在声明字段时设置它的长度上限而且使用它之后就不必再考虑空格的存在
若插入的字符串的长度低于长度上限系统就会自动将其缩减为字符串的真实长度
number(mn)既可以用来表示整型也可以表示浮点型但m不可以超过如果n为或者省略n就代表它是整数
date用来存放日期和时间
blob通常是在应用程序中使用到它而不是在数据库中利用SQL指令直接使用
比如通过JDBC技术访问数据库读写blob或clob类型的字段即读写长的字符串信息等等
char它是一种定长的字符类型在Oracle数据库不区分字符和字符串它们被统称为字符型或文本型
所谓定长的字符型是指插入的字符串若没有达到约定的字段长度系统就会在字符串尾部自动补空格
同样读取时的字段长度永远是声明时的字段长度而且在比较字符串内容的时候也需要考虑到空格的过滤
nchar它也是定长的字符串类型它是SQL语言标准中规定的通常采用Unicode编码来保存不同国家或不同语言的字符
varcharSQL标准在定义varchar时并没有保证能够向前和向后兼容即有可能随着语言标准的修改而产生不兼容的问题
所以Oracle定义了同varchar型类似的varchar型就是为了在Oracle以后的版本中都永远支持varchar类型
Oracle这么做就是为了确保此类型向前后兼容以达到能够在Oracle系列数据库中进行数据的导入和导出的目的
long它和varchar的差别在于它不支持对字符串内容进行检索即查询时不可以对它的内容进行条件查询
而varchar和charnchar型等等都可以在查询的时候直接检索字符串的内容
补充select * from v$nls_parameters;数据库的配置信息以数据表的形式存在通常称其为关于数据的数据或数据字典
实际上它查询的是数据字典中的一个视图其中NLS_CHARACTERSET对应的是当前的数据库字符集
缺省均为使用数据库字符集教程中使用的是安装时默认的ZHSGBK字符集即汉字占个字节英文占个字节
而NLS_NCHAR_CHARACTERSET对应的是nchar或nvarchar类型所采用的辅助字符集即ALUTF字符集
实际上ALUTF是一种位定长的Unicode编码的字符集而数据库字符集以及这种国家字符集都可以修改
但数据库字符集修改后可能会面临很严重的后果除非是数据库管理员普通用户不必对这方面进行深究
数据库中的数据导入导出的时候如果源数据库和目标数据库所采用的字符集不同也很容易出问题
函数
概述函数可以认为是能够完成相对独立的功能的一段代码的集合Oracle函数相当于其它语言中的方法或过程
Oracle函数可以分为单行函数和多行函数两大类Oracle函数都是有返回值的
所谓的单行函数是针对查询结果中的每一行都起作用都会返回一个结果
多行函数也就是所谓分组函数是针对一组查询的记录或者说多行返回一个结果
单行操作数据项接受参数并返回处理结果对每一返回行均起作用可修改数据类型可嵌套使用
单行函数分为字符函数数值函数日期函数转换函数通用函数
多行也称分组函数即对一组数据进行运算针对一组数据(多行记录)只能返回一个结果
多行函数包括avg()count()max()min()sum()等
比如select avg(sal) max(sal) min(sal) sum(sal) max(hiredate) min(hiredate) from emp;
续一使用Oracle的系统函数中的单行函数可实现诸多功能如对数据进行计算控制数据的输出格式
设置和改变日期的显示格式进行数据类型转换使用NVL等函数处理空值实现IFTHENELSE多路分支逻辑等等
续二转换函数不会改变表中数据的字段类型和值它就相当于将数据复制了一份所转换的是复制之后的数据
数据类型转换包括隐含转换和显式转换两种方式建议使用显式的数据类型转换确保SQL语句的可靠性
续三通用函数适用于包括空值在内的任何类型数据通常用来实现空值的处理空值的过滤或设置缺省值等
通用函数包括nvl()nvl()nullif()coalesce()case表达式decode()等
嵌套单行函数可以嵌套使用嵌套层次无限制分组函数最多可嵌套两层嵌套函数的执行顺序是由内到外
单行比如select empnolpad(initcap(trim(ename)) ) 姓名 job sal from emp;
多行比如select max(avg(sal)) from emp group by deptno;其实这里再使用分组函数就没有意义了
说明通常数据库层面提供的函数只是进行数据的简单的处理或者说是只能实现极为常规的功能
所以就不应该或者说是不要指望在数据库查询的层面来实现特别复杂的业务逻辑
如果应用程序的逻辑跟数据库混在一起的话会不利于代码的维护和更新
而且也不利于数据库的管理包括数据移植数据库导入导出等等
日期类型
概述在计算机操作系统或者各种高级编程语言中日期通常会被保存成一个长整数通常记录的是毫秒
Oracle内部以数字格式存储日期和时间信息世纪年月日小时分钟秒
缺省的日期格式是DD—MON—YY可使用sysdata函数获取当前系统日期和时间
运算日期型数据可以直接加或减一个数值结果认为日期约定的该数值代表的是相加减的天数
两个日期型数据可以相减结果为二者相差多少天二者不能因为日期相加是没有意义的
NVL()函数
概述它用于将空值null替换为指定的缺省值适用于字符数字日期等类型数据
格式NVL(expexp)如果表达式exp值为null则返回exp值否则返回exp值
举例select empno ename sal comm sal+nvl(comm) from emp;
select empno ename job nvl(job No job yet) from emp;
NVL()函数
概述它用于实现条件表达式功能
格式NVL(expexpexp)如果表达式exp值不为null则返回exp值否则返回exp值
举例select empno ename sal comm nvl(comm sal+comm sal) 总收入 from emp;
NULLIF()函数
概述它用于数据等价性比较并根据比较结果返回null或其中一个被比较的数值实际开发中应用并不是很多
格式nullif(expexp)如果表达式exp与exp的值相等则返回null否则返回exp的值
举例select name 原名 nullif(pen_name name) 化名 from author;
COALESCE()函数
概述它用于实现数据“接合”功能
格式coalesec(expexp)依次考察各参数表达式遇到非null值即停止并返回该值
若表达式均为null值则返回null通常最后一个表达式都是能确保不是空值的字段
举例select empno ename sal comm coalesec(sal+commsal) 总收入 from emp;
CASE表达式
概述它用于实现多路分支结构
格式case exp when comparison_exp then return_exp
[when comparison_exp then return_exp
when comparison_expn then return_expn
else else_exp]
end
举例select empno ename sal
case deptno when then 财务部
when then 研发部
when then 销售部
else 未知部门
end 部门
from emp;
说明CASE中的每一个表达式(如deptno财务部等)都可以是复合而成的
这种对齐方式的书写是为了增加可读性当然也可以把代码写在同一行上
其中case到end之间的整体就相当于普通查询中的一个字段end后面的“部门”是别名
DEDODE()函数
概述和case表达式类似它也用于实现多路分支结构
格式decode(col|expression search result
[ search result]
[ default])
举例select empno ename sal
decode(deptno 财务部
研发部
销售部
未知部门)
部门
from emp;
COUNT()函数
格式count(*)返回组中总记录数目
count(exp)返回表达式exp值非空的记录
count(distinct(exp))返回表达式exp值不重复的非空的记录数目
举例select count(*) from emp;缺省的情况下整个表就是一组
select count(comm) from emp;返回emp表中comm字段不为空的记录(行)数目
select count(distinct(deptno)) from emp;查找deptno值为非空且不重复的记录数目
分组函数与空值
概述分组函数省略列中的空值可使用NVL()函数强制分组函数处理空值
举例select avg(comm) from emp;等价于sum(comm)/count(comm)
select sum(comm) from emp;计算表中非空的comm值的总和
select avg(nvl(comm)) from emp;等价于avg(nvl(comm))/count(*)
GROUP BY子句
概述它用于将表中数据分成若干小组
格式select column group_function(column)
from table
[where condition]
[group by group_by_expression]
[order by column];
举例select deptno avg(sal) from emp group by deptno;
说明出现在SELECT列表中的字段如果不是包含在组函数中那么该字段必须同时在GROUP BY子句中出现
包含在GROPY BY子句中的字段则不必须出现在SELECT列表中子句执行顺序是where→group by→order by缺省按升序排列
补充select deptno job avg(sal) from emp group by deptno job order by deptno desc;基于多个字段的分组
select deptno avg(sal) from emp;非法
注意如果没有GROUP BY子句SELECT列表中不允许出现字段(单行函数)与分组函数混用的情况
WHERE中不允许使用分组函数如select deptno avg(sal) from emp where avg(sal)> group by deptno;非法
这跟子句执行的顺序有关where子句最先执行在执行where子句的时候还没有执行过group by子句
于是程序不知道这是在分组也不曾计算过avg(sal)的组内平均工资所以在where子句中不允许使用分组函数
由于还没有执行过group by子句所以此时就不确定如何怎么分组以及分多少个组
所以where子句中只能进行初级过滤此时可以使用HAVING子句实现对平均工资的过滤
HAVING子句
概述它用于过滤分组
格式select column group_function(column)
from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];
举例select deptno job avg(sal)
from emp
where hiredate >= to_date(yyyymmdd)
group by by deptno job
having avg(sal) >
order by deptno job;
