函数
个人的Oracle3
1 基本概念以及预备尝试
a 所谓单行函数就是作用与表里的每一行数据,执行一次的函数
select employee_id , first_name , abs(salary) from employees;
abs()是计算绝对值的函数
b DUAL表
一行一列的特殊表,属于SYS用户,如果我们只需要函数执行一次,可以使用这个表。
dual表里的数据没有任何意义,只是为了维护select的完整性。
select abs(-10) from ??? ;
如果问号处我们写入employees,那么该条查询语句会针对表里的每一行执行一次。虽然abs(-10)与表里数据没有任何关系,但仍然会返回107行结果。如果填入dual,那么因为dual表里只有一行数据,所以该条查询语句只会有一个结果。
2 字符串相关函数
length(str) 获取字符串长度
查询员工的姓,及其长度 select last_name ,length(last_name) from employees; 查询姓长度为5的员工 select last_name from employees where length(last_name) = 5;
字符串拼接
**语法:
select 'a' || 'b' from dual**<br>
也可使用
select concat(‘a’,’b’) from dual;
截取字符串 substr(str,begin,length)
对str 做截取,从begin(⭐下标从1开始) 开始截length位
select substr('abcdefg',2,4) from dual;
对’abcdefg’字符串的第二位元素开始截取到四个元素
其结果为:”bcde”
查找姓以S开头的员工信息 select * from employees where substr(first_name,1,1) = 'S';
查找子字符串 inster(str1,str2,begin)
在str1 中找 str2 , 从begin (下标1开始)开始查找, 找到返回str2首字符所在的下标,否则返回0。
查询名字中含有gt字符的员工 select employee_id , fist_name , salary from employees where instr(first_name,'gt',1)! = 0
字符串转换大小写 lower(str) upper(str)
将字符串str 转换大小写
查询姓是'a'或'A'开头的员工信息 select employee_id , first_name ,salary from employees where lower(substr(first_name,1,1)) ='a'; 解释:首先截取到当前姓的第一个字符,并且转换为小写 ,最后在对比。
3 数字相关函数
取模 mod(num1,num2)
类似Java|C语言中的:num1 % num2;
打印员工编号为奇数的员工 select * from employees where mod(employee_id,2)=1; 解释 :员工id 模 2 若等 1 的时候满足条件
截取数字n trunc(n,length)
对数字 n 进行截取,精确到小数点后length位。
截取数字 select trunc(1234.54321,1) from dual; -----------1234.5
四舍五入 round(n1,length)
对数字n1四舍五入,精确到小数点后length位。
四舍五入 select round(123.4567,2) from dual; -----------123.45 select round(123.4567) from dual; -----------123
取绝对值 abs(num)
对num 取绝对值(前面提到过)
获取随机数 dbms_random.random()
产生一个很大的随机数(可正可负)
请数据产生一个正整数 select mod(abs(dbms_random.random()),100) from dual; 解释:产生一个-99~99之间的数后取绝对值 请随机查询表中的一行数据 select employee_id ,first_name , salary from employees where employee_id = mod(abs(dbms_random.random()),107)+100; 解释:首先产生一个很大的随机数模107(数据总条数)在取绝对值,+100是为了让最后产生的随机数在100~207之间;
4 日期相关操作及函数
时间与日期是数据库中常用的数据类型,在处理很多运算的时候都需要用到。比如银行应用在处理开户业务时要存储开户日期,电信应用在结算通讯费用是也要用到通话时间。下面我们就来学习oracle数据库中关于日期和实践的相关运算和函数
系统时间 sysdate
获取当前系统时间 select sysdate from dual;
在sysdate提供的时间数据中,不仅包含年月日的信息,还包括时分秒的信息,
甚至可以精确到毫秒。本例中我们只看到了年月日信息是因为oracle默认的日
期显示格式省略了时分秒等额外信息。oracle里默认的日期格式为’yyyy-mm-dd hh:mi:ss’
获取十天后的日期 select sysdate+10 from dual;
注意:对于日期进行算数讯算时,默认单位是天
对月份的算数运算 add_months(myDate,num)
显示上个月的今天 selrct add_months(sysdate,-1)from dual;
获取月份的最后一天 last_day()
获取当前月份的最后一天 select last_day(sysdate) from dual;
计算两个日期之间相聚多少个月 返回小数
select months_between('1994-07-13',sysdate) from dual;
根据日期的格式做四舍五入
求当年日期距离上一年近还是下一年近 select round(sysdate , 'yyyy') from dual;-------2018-01-01
(8-20日书写)
根据日期的格式进行截取
获取年的第一天 select trunc(sysdate,'yyyy') from dual; 获取月的第一天 select trunc(sysdate,'mm') from dual 获取周的第一天 select trunc(sysdate,'day') from dual; 当天的零时零分零秒 select trunc(sysdate) from dual;
注意:Oracle是以为周日为一周的第一天
5 类型转换相关操作和函数
字符串转数字 to_number(str)
将给定的字符串str转换成数字类型值
将字符串1234转换成数字1234 select to_number('1234') from dual;
注意:如果给定的str不是有纯数字组成,sql无法执行,报异常。
将给定类型转字符串 to_char()
to_char(myNumber,’格式字符串’)
将数字变成字符串常见格式:0或者9表示占位,还可以使用各种货币符号¥$等。
请用科学计数法显示员工的工资信息 select employee_id,first_name,to_char(salary,'$999,999.00') from employees;
注意:当实际数值超过给定的字符串格式时,数据无法正常显示,会出现若干*符号。
to_char(myDate,’格式字符串’)
将给定的日期值转换成字符串时间表示【重点】
因为Oracle默认的日期显示格式省略了时分秒等额外信息。如果我们想在现实时间的时候看到时分秒的信息的话,就需要手动指定日期的显示格式了。
种类|常用格式字符串
--|---------------------
年|yy yyyy rr rrrr year(英语年份)
月|mm mon month
日|dd ddsp ddth ddspth
星期|d dy day
小时|hh am,ap hh24
钟|mi
秒|ss
to_date(strDate,日期格式字符串)
根据给定的日期格式字符串转成日期
输出 2008-08-08 是周几 selrct to_char(to_date('08-08-08','mm-dd-yy'),'day') from dual;
6 NVL函数 类似与三目运算
问题引入:
与java 语言类似,Oracle中使用NULL代表一个值不存在。需要注意的是NULL值参与运算总返回NULL,这给我们的运算过程带来问题。当我们计算employees中每个员工每月实发工资(工资+奖金,奖金是工资*奖金比率)时,问题就出现了。
我们发现很多员工的实发工资变成了NULL,这很显然是不对的。造成这个错误的原因在于有的员工的commission_pct 为NULL , 在计算总工资时,由于有空值参与,所以导致整个运算结果为空。
问题解决
nvl(n1,n2)
判断n1是否是null,是null执行n2,否则执行n1。(n1==null?n2:n1)实际上我们希望当commission_pct为NULL时,它的值按0来处理。这时我们就可以使用NVL函数解决此问题(将NULL转化为默认值进行计算)。我们将刚才的SQL改写成如下形式:
select employee_id , first_name , salary ,commission_pct, (salary + salary * nvl(commission_pct,0)) as 总月薪 from employees;
我们看到,原来总工资为空的数据现在都恢复正常了
今天的知道汇总到此结束 由于我这变网络还是一个问题所以有些文档先写在本地,找机会上传!
如果我的汇总对你有帮助请点赞关注 如果你对我的代码有疑惑或者意见欢迎提出,本人邮箱:lfw615@foxmail.com
谢谢