Oracle数据库常用函数使用示例

Oracle数据库基础简介及实践

1、开始(p1~p2)
2、SQL语句编写思路(p3)
3、Oracle常用运算符介绍(p4~p5)
4、DML语句介绍(p6~p11)
5、Oracle常用函数介绍(p12~p30)
6、SQL语句函数运用(p31~p34)
7、通过日志获取SQL语句(p35~p49)
8、使用case系统提交问题(p50~p51)
9、学习资源及答疑(p52)
10、结尾(p53)

 

1、SQL语句编写思路
1、首先确定最终输出结果的列，包括几个方面：
1)这些列来自1个表还是多个表，如果是多个表则可能用到多表查询(等值、不等值、左连接、右连接、外连接、自连接)
2)确定获取列的值，是直接查询得到，还是需要进行(函数、分组、运算、列的子查询)操作才能得到，列是否需要别名
2、确定输出列的值和查询条件是否来自多个表，如果来自多个表则要用到多表查询
3、确定输出列的值，是否可以直接查询获得还是要通过子查询才能获得，如果要用到子查询，则需要加上where
4、根据输出列的结果和条件，判断是否要用到分组(group by)，比如分类、统计、分组、最大、最小、平均、每个等字眼就需要用到group by
5、是否对分组后的结果进行过滤，需要则要用到having
6、是否对输出的结果进行排序，需要则要用到order by

2、Oracle常用运算符介绍
1、使用||可连接字符
2、使用distinct可以消除重复行
3、运算符：
1) 算术(+ - * / )
2) 比较(> >= < <= = != <> ) any(值1,值2...) all(值1,值2...) 不能单独使用，要配合>,>=,<,<=来使用
3) 逻辑(and or not)

4、SQL操作符：
1) in(值1,值n)
2) like '模式字符串'：_代表任意一个字符，%代表0到n个字符
3) between 数值1 and 数值2：不仅可用于数值，还可以用于日期时间
4) is null、is not null：匹配空值，非空值。对于空值一定不能用=或!=
5)union (取并集，重复的记录行只显示1行) 、union all(取并集)、intersect(取交集)、minus(取差集，返回左边表差集后的记录)
5、伪列：
rowid: Oracle内部对每个表的每一行都有一个唯一的标识
rownum: Oracle对每次查询结果集的每一行记录都有一个行号，对rownum只能使用<或=

3、 DML语句介绍
1、查询(select)
select distinct * |列 as 别名|表达式|函数|列运算|子查询
from 表1 别名1,表n 别名n |子查询
where 条件1(> >= < <= <>) 条件2 | 范围(between 条件1 and 条件2)
In(子查询)
like (条件_%)
组合(or|and|not)
子查询(select 语句)
group by列
having 分组函数(max,count,sum等) 运算符 普通值|子查询
order by 列 别名 或 数字

2、新增(insert into)
insert into 表名(列名1,..) values(值1,..)
--新增信息
insert into check
(PRIMARY_ID,
NAME,
DISTRICT,
UPDATED_DATE)
values
(sys_guid(), sysdate);

--只复制表结构加入了一个永远不可能成立的条件1=2，则此时表示的是只复制表结构，但是不复制表内容
create table 用户名.目标表名 as select * from 用户名.源表名 where 1=2;

--完全复制表(包括创建表和复制表中的记录)
create table 用户名.目标表名 as select * from 用户名.源表名;

--将多个表数据插入一个表中
insert into 用户名.目标表名(字段1,字段n) (select 字段1,字段n)
from 用户名.源表名 union all select 字段1,字段n from 表;

3、修改(需加commit;关键字来提交)
update 表名 set 列名=列改变值(where 条件表达式);
update check_store cs set cs.DISTRICT='310113' where cs.NAME='数据包';
commit;

4、删除(需加commit;关键字来提交)
delete from 表名 (where 条件表达式);
delete from check_operate co where where co.NAME='数据包';
commit;

4、Oracle常用函数介绍
1、日期字符函数
1)to_char
--2016-11-03
select to_char(sysdate, 'yyyy-mm-dd') from dual;
2)to_date
select to_date(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual;
3)to_number
to_number(varchar2 or char,'format model')
9：代表一个数字
0：强迫0显示
$：显示美元符号
L：强制显示一个当地的货币符号
.：显示一个小数点
,：显示一个千位分隔符号

--1234.678
select to_number('$1234.678', '$9999.999') from dual;
--15
select to_number('f', 'x') from dual;
2、聚合函数
1)最大值函数：max([distinct|all]x)，最小值函数：min ([distinct|all]x)
可以作用于数值型数据、字符串、日期时间数据类型的数据，max(null),min(null)返回null
对字符型数据的最大值，是按照首字母由A~Z的顺序排列，越往后，其值越大。
对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。
对于日期时间类型的数据也可以求其最大/最小值，其大小排列就是日期时间的早晚，越早认为其值越小。

2)求和函数sum([distinct|all]x)，统计记录数函数：count(*|[distinct|all]x)，求平均值函数：avg([distinct|all]x)
count(*)：将返回表格中所有存在的行的总数(包括null的行)
count(列名)：将返回表格中除去null以外的所有行的总数
count(distinct+列名)：与count(列名)相同
3、特殊函数
1) 唯一字符串函数： sys_guid()
生成由32位长度由大写字母和数字组成的唯一字符串
select sys_guid() from dual;
结果：4061D1372CEA31E4E05318E01F0A5902

第3个参数不填写会被填充空格
2）左填充函数：lpad(string,padded_length,[pad_string])
select lpad(1, 6, 't') from dual;--ttttt1
select lpad(1234567, 6, 't') from dual;--123456
3)右填充函数：lpad(string,padded_length,[pad_string])
select rpad(1, 6, 't') from dual;--1ttttt
select rpad('abcdefg', 6, 't') from dual;--abcdef
4)随机包函数

1) 返回0~1间的38位精度的随机数函数(包括0.0，不包括1.0)：dbms_random.value
--0.474199333601534
select dbms_random.value from dual;
2)返回a~b之间的随机数的函数(包括a=1，不包括b=10)：dbms_random.value(1,10) ：
--4.63158069726374
select dbms_random.value(1,10) from dual;
3)产生正态分布的随机数的函数： dbms_random.normal

/*
注意：normal函数返回从正态分布的一组数。此正态分布标准偏差为1，期望值为0。
这个函数返回的数值中有68%是介于-1与+1之间， 95%介于-2与+2之间，99%介于-3与+3之间
*/
---0.241547252937121
select dbms_random.normal from dual;
4)返回指定长度的的字符串的函数： dbms_random.string(opt char, len NUMBER)

/*
'u'或'U'–>返回大写字母
'l'或'L'–>返回小写字母
'a'或'A'–>大小写字母混合
'x'或'X'–>大写字母和数字混合
'p'或'P'–>任意可显示字符
*/
--LSNB
select dbms_random.string('u',4) from dual;
5)返回一个随机数的函数： dbms_random.random

--范围：-power(2,31) <= random < power(2,31)
--35725665
select dbms_random.random from dual;
6)返回绝对值函数：abs
--1949
select abs(-1949) from dual;
7)返回大于或等于给出数字的最小整数：ceil
--7
Select ceil(6.5) from dual;
8)返回小于或等于给出数字的最大整数：floor
--6
Select floor(6.5) from dual;

 

9) 返回保留指定位数的数字：round(number,digits)
/*
要四舍五入的数，digits是要小数点后保留的位数
如果 digits 大于 0或不填写，则四舍五入到指定的小数位
如果 digits 等于 0，则四舍五入到最接近的整数
如果 digits 小于 0，则在小数点左侧进行四舍五入
*/
--3.687
select round(3.6873,3) from dual;

10)nvl(expr1,expr2)
expr1为null，返回expr2，否则返回expr1
参数expr1、expr2可以是任何数据类型，但应该保持相同。
若两者数据类型不一致，则Oracle数据库会隐式的转换其中一个的数据类型使其保持和另一个一致，若无法转换则会返回错误。
--空
select nvl(null,'空') from dual;
--1
select nvl('1','空') from dual;

11)nvl2(expr1,expr2,expr3)
如果expr1不是null值，则expr2，否则就返回expr3。
参数可以返回任何数据类型的值，但是expr2和expr3不能是LONG型的数据类型
--空
select nvl2(null,'非空','空') from dual;
--非空
select nvl2('1','非空','空') from dual;

12)nullif(expr1,expr2)
参数expr1,expr2
常量、列名、函数、子查询或算术运算符、按位运算符以及字符串运算符的任意组合，参数中不能有null。
返回类型与第一个 expr1 相同
如果两个表达式相等，则返回空值null
如果两个表达式不相等，则返回expre1的值
--
select nullif(1,1) from dual;
--1
select nullif('1','') from dual;
--1
select nullif('1',' ') from dual;

13)返回字符串位置的函数：instr(source,search, start_position,nth_appearance)
source：源字符串
search：被查找的字符串
start_position：从源字符串中哪个位置开始查找字符串，可省略，默认为1，正整数，从左到右查找，负整数从右到左查找
nth_appearance：第几次出现被查找的字符串，可不填写，默认为1，不能为负数
也可以找到单个字符
--8
select instr('2016-11-03 16:24:20', '-', 1, 2) from dual;

14) 返回截取后的字符串：substr(strings|express,start,[length])
strings|express ：被截取的字符串或字符串表达式
start：从哪个位置开始截取，正数(在字符串的指定位置开始)，负数(从字符串结尾的指定位置开始)，0(在字符串中的第1个位置处开始)
length：可选，指定要截取的字符串长度，缺省时返回字符表达式的值结束前的全部字符
--01
select substr('2016-11-03',2,2) from dual;
--03
select substr('2016-11-03',-2,2) from dual;
--16-11-03
select substr('2016-11-03',3) from dual;

15) 值转换函数：decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
条件可以为字段或表达式
当条件等于值1时，函数翻译值1，条件等于值2时，函数翻译值2，都不符合时，返回缺省值
-- 9大于3
select decode(sign(9-3),1,'9大于3',-1,'9小于3',0,'9等于3','不知道') from dual;
16)判断正负数函数：sign(变量1-变量2)
根据(变量1-变量2)的值是0、正数、负数，返回0、1、-1

17) translate(Str,fromStr,toStr)
1、将Str中的字符串，替换后返回，按fromStr与toStr一一对应的方式，如果不能一一对应则被视为空值。
2、如果fromStr字符串长度比toStr长，则fromStr字符串比toStr字符串，多出的字符将被删除。
3、translate中的任何参数为null，那么结果也是null。
--1 9XXX999 将数字转换为9，其他的大写字母转换为X，然后返回
select translate('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') result
from dual;

--2 2229 将数字保留，将其他的大写字母移除
select translate('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789') as result
from dual;
--3 我是Ch人,我爱Ch 按照字符来处理，不是按照字节来处理，如果toStr的字符数比fromStr多的话，多出的字符会被删除
select translate('我是中国人,我爱中国', '中国', 'China') "result"
from dual;

--4 I m 中国ese, I love 中国 如果fromStr的字符数大于toStr，多出的字符会被删除
select translate('I am Chinese, I love China', 'China', '中国') as "result"
from dual;
--5 如果参数为空或空字符串，整个返回null
select translate('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', null) "结果"
from dual;
--6 **人 只显示账户中的姓名最后一个字，其余的用星号代替
select translate('中国人',
substr('中国人', 1, length('中国人') - 1),
rpad('*', length('中国人'), '*')) as "结果"
from dual;

18)排序函数：
1、row_number() over([partition by colum] order by colum)
为有序组中的每一行返回一个唯一的排序值，序号由 order by 子句指定，从 1 开始，即使具有相等的值，排位也不同。
partition by colum 按列值进行区分，各分组内在进行排序。
2、dense_rank() over([partition by colum] order by colum)
计算一个值在一个组中的地位，由 1 开头，具有相等值得行排位相同，并且排位是连续的。
3、rank() over([partition by colum] order by colum)
计算一个值在一个组中的地位，由 1 开头，具有相等值得行排位相同，序数随后跳跃相应的数值。

5、SQL语句函数运用

分组排序的示例
--按日期统计(角色1) 用户的数量
select trunc(cr.created_date, 'dd'),
count(case
when cr.role_id = 'rolea' then
1
else
null
end) as 角色1
from check_role cr
group by trunc(cr.created_date, 'dd')
order by trunc(cr.created_date, 'dd') desc;

使用伪列，字符串操作示例
--返回最新版本号
select flowId
from (select row_number() over(partition by cr.key_ order by cr.version_ desc) rnum,
substr(cr.key_,
instr(cr.key_, '_', 1, 2) + 1,
length(cr.key_)) cityId,
cr.ID_ flowId,
cr.version_
from check_record cr
where cr.key_ like 'check_test%')
where rnum = 1;

decode函数示例
--我的消息表
select decode(tn.type,1,'类型1',2,'类型2',3,'类型3',4,'类型4') as "产品类型", tn.user_id as "用户编号", count(*) as "消息条数"
from test_news tn
where tn.user_id is not null
and tn.created_date >=
to_date('2014-11-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by decode(tn.type,1,'类型1',2,'类型2',3,'类型3',4,'类型4'), tn.user_id
order by decode(tn.type,1,'类型1',2,'类型2',3,'类型3',4,'类型4') desc, tn.user_id desc, count(*) desc