Oracle学习总结——常用函数具有工具属性

字符函数

lower将字符串转化为小写

select lower(‘HellO’) from dual;

查询员工名字为patel的信息

select last_name,salary

from s_emp

where lower(last_name)=lower(’patel’)

upper 将字符串转化为大写

select upper(‘hello’) from dual;

initcap 将字符串中的单词首字母转化为大写

select initcap(‘hello world’) from dual;

concat 做字符串拼接，同 ||

select concat(‘hello’,’world’) from dual;

last_name.first_name

select concat(last_name,concat(‘.’,first_name))

from s_emp;

注意：函数可以进行嵌套，从内部往外部执行

length 显示字符串的长度

select length(‘hello’) from dual;

nvl处理列存在的null值的情况（时间，字符串，数字）

注意：nvl处理列为null的时候，第二个参数

和第一个参数类型要匹配

利用lower()或upper()函数实现查询时不区分大小写

eg:

select id,last_name

from s_emp

where lower(last_name) = lower('用户输入的字符串');

数字函数

round 四舍五入

//参数为一个取整，看的是小数点后第一位

select round(35.478) from dual;//35

第二个参数正数表示小数点后取几位

select round(35.478,1) from dual;//35.5

select round(35.478,2) from dual;//35.48

第二参数负数小数点前看的位数，指向的位置

制0看是否有进位

select round(35.478,-1) from dual;//40

select round(35.478,-2) from dual;//0

trunc 截取

select trunc(35.478) from dual;//35

select trunc(35.478,1) from dual;//35.4

select trunc(35.478,-1) from dual;//30

mod 取余

select mod(1500,200) from dual;//100

时间函数

months_between 两个时间点相差几个月

select months_between(’01-8月-18’,’01-9月-18’)

from dual;//1

add_months 给某个时间增加几个月

select add_months(sysdate,2)

from dual;

next_day 下一个星期几是几号

select next_day(sysdate,‘星期五’)

from dual;

last_day 查询指定时间所出月的最后一天是几号

select last_day(sysdate)

from dual;

round 基于时间四舍五入 day month year

按天四舍五入，看星期，逢周四进入下一个星期的第一天

小于周四回到本周的第一天

select round(to_date(’04-9月-18’,’dd-Mon-yy’),’day’)

from dual;

按照星期四舍五入，看天数，逢16号进入下一个月的第一天

    小于16号的进入本月的第一天

select round(to_date(’04-9月-18’,’dd-Mon-yy’),’month’)

from dual;

按照年截取，看月份，逢7月进入下一年的第一天

小于7月的进入单前年份的第一天

select round(to_date(’04-9月-18’,’dd-Mon-yy’),’year’)

from dual;

trunc 基于时间截取

按天截取，回到单前星期的第一天

select trunc(to_date(’04-9月-18’,’dd-Mon-yy’),’day’)

from dual;

按照月截取，回到单前月的第一天

select trunc(to_date(’04-9月-18’,’dd-Mon-yy’),’month’)

from dual;

按照年截取，回到单前年的第一天

select trunc(to_date(’04-9月-18’,’dd-Mon-yy’),’year’)

from dual;

转换函数

to_date() 将字符串转化为时间

第二个参数表示时间的格式

简体中文环境

select to_date(’04-9月-18’,’dd-Mon-yy’)

from dual;

英文

select to_date(’04-May-18’,’dd-Mon-yy’)

from dual;

时间格式英文环境下 dd-mm-yy

select to_date(’04-May-18’,’dd-mm-yy’)

from dual;

select to_date(’04-09-18’,’dd-mm-yy’)

from dual;

to_number 将字符串类型的数字转化为数字类型

select to_number(‘23232’)

from dual;

to_char

将数字类型转化为特定格式的字符串

第一个参数表示数字，第二个参数表示格式

select to_char(34.567，‘fm$999,999.999’)

from dual;

$ 表示美元符号

9 表示占位，不补位

0 表示占位 补位

，千分符

.小数点

L 本地币种，和操作系统有关

fm 去掉打印字符串中的空格

将时间类型转化为特定格式的字符串

select to_char(sysdate,’’)

from dual;

第二个参数格式：

YYYY 完整的年份

MM 月份

D 单前时间指定星期的第几天

DD 单前时间指定月份的第几天

DDD 单前年份的第几天

year 英文的年份

month 英文的月份

ddsp 英文的本月几号

ddspth  英文的本月第几号

day英文版本的星期几

dy 英文版本的星期几缩写

hh:mi:ss 小时分钟秒

AM 和PM效果一样，显示上下午

hh24:mi:ss显示时间小时按照24进制

注意：在时间转化为字符串的过程中

格式中有特定的单词符号等，用双引号引起来

select to_char(sysdate,’dd “of” Month’)

from dual;

千年虫

在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，

假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情

况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算

就会出现问题。这个事情当时被称为千年虫

数据库中表示日期中年份的有俩种: yy和rr

之前一直使用的时候yy格式,后来才有的rr格式

yy表示使用一个俩位数表示当前年份:

1990 ---yy数据库格式---> 90

1968 ---yy数据库格式---> 68

1979 ---yy数据库格式---> 79

rr格式表示: 另外参照图片:rr日期格式规则.png

如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：

规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。

规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。

规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。

规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。

注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。

多表查询中的运算：union、union all，minus、intersect

union并集（重复的元素只保留一个）

select id

from s_emp

where id<5 //1,2,3,4

union

select id

from s_emp

where id>3 and id <10 //4,5,6,7,8,9

结果1，2，3，4，5，6，7，8，9

union all并集(不会去掉重复的元素)

select id

from s_emp

where id<5 //1,2,3,4

union all

select id

from s_emp

where id>3 and id <10 //4,5,6,7,8,9

结果1，2，3，4，4，5，6，7，8，9

minus 第一个结果去掉和第二个结果相同的数据

select id

from s_emp

where id<5 //1,2,3,4

minus

select id

from s_emp

where id>3 and id <10 //4,5,6,7,8,9

结果1，2，3

注意：rownum是伪列数据库表不存在该列，查询数据的时候数据库会给每一行数据分配一个rownum，rownum从1开始的连续数字的标记

rownum=1 获取第一条数据，其它的数字不能写

rownum<=n n可以是大于0的任意值

rownum>=m m只能为0，获取所有的数据

eg:部门表中第3条到第5条数据？

select id,name,region_id

from s_dept

where rownum<=5

minus

select id,name,region_id

from s_dept

where rownum<=2；

intersect交集

select id

from s_emp

where id<5 //1,2,3,4

intersect

select id

from s_emp

where id>3 and id <10 //4,5,6,7,8,9

结果4

组函数：对数据库中的表进行分组，分完组之后每组给出一个结果（一行）

group by 基于某列或某几列对表进行分组

分完组之后给出一行结果,分组的标准该列值相同的

归位一组

having 对group by 分组之后的结果一行一行

的过滤用的，having可以不出现，一旦出现只能

在group by的后面

select

from table_name…

where 条件语句（对from查询的结果处理）

group by column_name…

having 条件语句（对group by 分组的结果处理）

order by column_name [asc|desc]...

执行的流程 from->where->group by->

having->select->order by

注意：group by在where后面执行，所有分组

函数不能出现where中

group by 后面出现的列可以出现在select

后面，也可以不出现，但是select后面出现的列

一定要出现在group by后面

分组函数 select having order by

分组函数（对分好的组中数据处理）

先不用group by，把整张表的数据作为

一组

avg求平均值

查询员工的平均工资

select avg(salary)

from s_emp;

select avg(distinct salary)

from s_emp;

count统计有多少行

查询员工总数

select count(*)

from s_emp;

max求最大值

min求最小值

查询员工的最大工资和最小工资

select max(salary),min(salary)

from s_emp;

stddev 标准差

查询薪水的标准差

select stddev(salary)

from s_emp;

sum 求总和

查询每个月发薪水的总和

select sum(salary)

from s_emp;

variance方差

查询薪水的方差

select variance(salary)

from s_emp;

和group by结合

avg求平均值

查询的每个部门平均工资

select avg(salary),dept_id

from s_emp

group by dept_id;

count统计有多少行

查询每个部门员工总数

select count(*)

from s_emp

group by dept_id;

max求最大值

min求最小值

查询每个部门员工的最大工资和最小工资

select max(salary),min(salary)

from s_emp

group by dept_id;

stddev 标准差

查询每个部门薪水的标准差

select stddev(salary)

from s_emp

group by dept_id;

sum 求总和

查询每个部门每个月发薪水的总和

select sum(salary)

from s_emp

group by dept;

variance方差

查询每个部门薪水的方差

select variance(salary)

from s_emp

group by dept_id;

select avg(salary),dept_id,last_name

from s_emp

group by dept_id,last_name;

注意：group by后面出现多列的时候

按照组合分组，

案列

查询s_emp表中部门的平均工资大于

等于1400的部门

select avg(salary),dept_id

from s_emp

group by dept_id

having avg(salary)>=1400;

查询s_emp表中部门的平均工资大于等

于1400的部门,并且显示出这些部门的

名字,同时按照部门编号进行排序

select avg(salary),s.dept_id,d.name

from s_emp s,s_dept d

where s.dept_id=d.id

group s.dept_id,d.name

having avg(s.salary)>=1400

order by s.dept_id;

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 211,948评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,371评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,490评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,521评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,627评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,842评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,997评论 3赞 408
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,741评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,203评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,534评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,673评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,339评论 4赞 330
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,955评论 3赞 313
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,770评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,000评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,394评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,562评论 2赞 349

推荐阅读更多精彩内容

  • Oracle笔记（一）
    Oracle笔记系列这几篇是来自一位老师的笔记，分享给大家放在简书上，主要方便自己时常复习，还有学习Oracle的...
    panda_Hi阅读 836评论 0赞 1
  • Oracle学习总结——经验技巧体会
    特殊的表dual dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记...
    panda_Hi阅读 1,203评论 0赞 2
  • Oracle笔记（二）
    Oracle笔记系列这几篇是来自一位老师的笔记，分享给大家放在简书上，主要方便自己时常复习，还有学习Oracle的...
    panda_Hi阅读 1,123评论 0赞 0
  • Oracle数据库学习一
    1.简介 数据存储有哪些方式？电子表格，纸质文件，数据库。 那么究竟什么是关系型数据库？ 目前对数据库的分类主要是...
    乔震阅读 1,710评论 0赞 2
  • 管理“归无”？
    传统企业大都采取自上而下掌控的模式进行管理。随着互联网信息的高度发达，个人需求的个性化与多样性以及就业机会的增多，...
    w小郭阅读 106评论 0赞 0