推荐教程:《Oracle视频教程》
一、概述:
像几乎所有其他的计算机语言一样,SQL支持使用函数来操作数据,函数通常用于对数据执行操作,以便进行转换和操作。
二、函数分类
- 文本函数:用于操作文本字符串(例如,修剪或填充值,以及把值转换成大小写形式)。
- 数值函数:用于对数值型数据执行数学运算(例如:返回绝对值以及执行代数计算)。
- 日期和时间函数:用于操作日期和时间值,以及从这些值中提取特定的部分(例如:返回日期之间的差值以及检查日期有效性)。
- 系统函数:返回特定于所有的DBMS的信息(例如:返回用户登录信息或者检查版本的详细信息)。
1.文本操作函数
示例:
SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
结果:
可以看出,Upper()把文本转换成大写形式。下表列举了一些常用的文本操作函数。
函数 | 描述 |
---|---|
Length() | 返回字符串的长度 |
Lower() | 把字符串转换成小写形式 |
LPad() | 把字符串左边填充空格 |
LTrim() | 从字符串左边修剪空白 |
RPad() | 在字符串右边填充空格 |
RTrim() | 从字符串右边边修剪空白 |
Soundex() | 返回字符串的SOUNDEX值 |
SubString() | 返回字符串内的字符 |
Upper | 把字符串转换成大写形式 |
--1、查询字符长度位6的数:length() select * from table_name where length(column_name) = 6; --2、把字符串转换成小写形式:lower() select lower(column_name) from table_name; --3、将字符串左边填充:lpad(String ,截取长度,添加的字符串)。说是添加字符串也不准确,比较准确的说法是对String进行截取字符串,如果截取长度大于String的长度,则在String的左侧添加字符串进行填补,如果第三个参数未指定,则用空格进行填补。 select lpad('test',10,'ee') from dual; --结果: lpad('test',10,'ee') ---------------------------- eeeeeetest ————————————————------------ --4、从字符串左边修剪空白: LTRIM(c1,[,c2]) 【功能】删除左边出现的字符串 【参数】C1 字符串,c2 追加字符串,默认为空格 【返回】字符型 select ltrim('abcddee','abc') from dual; --结果: ltrim('abcddee','abc') --------------------------- ddee ————————————————----------- --5、将字符串右边填充:rpad(string, c1, [c2]) 【功能】将字符串右边填充 【参数】string:需要操作的字符串 c1:操作后的字符串长度 c2:用于填充的操作符,默认为空格 --6、从字符串右边修剪空白: RTRIM(c1,[,c2]) 【功能】删除右边出现的字符串 【参数】c1:字符串 c2:需要删除的字符串 【返回】字符型 select rtrim('abcddee','ddee') from dual; --结果: ltrim('abcddee','ddee') --------------------------- abc ————————————————----------- --7、返回字符串内的子字符:substring(string, index1, [,length1]) 【功能】返回字符串内的子串 【参数】string:操作的源字符串 index1:子串在源字符串的起始位置 length1:子串的长度 【返回】字符型 select substring('string', 0, 3) from dual; --结果: substr('string', 0, 3) --------------------------- str ————————————————----------- --8、返回字符串的大写形式:upper(string) 【功能】返回字符串的大写形式 【参数】:string:需要操作的源字符串 【返回】字符型 select upper('string') from dual; --结果: upper('string') --------------------------- STRING ————————————————-----------
注意:
SOUNDEX是一个算法,用于把任何文本字符串转换成一种字母数字模式,描述该文本的语音表示。SOUNDEX考虑了类似的发音字符和音节,允许按字符串的发音(而不是按它们是如何输入的)来比较它们。尽管SOUNDEX不是一个SQL概念,Oracle(像许多其他的DBMS一样)还是提供了对SOUNDEX的支持。
下面给出了一个使用Soundex()函数的示例。customers表中有一位Coyote Inc.的顾客,联系名为Y. Lee。但是,如果出现输入错误,而联系人实际上是Y. Lie,则会如何?显然,按正确的联系人名字执行搜索将不会返回任何数据,如下所示:
SELECT cust_name, cust_contact FROM customers where cust_contact = 'Y. Lie'
现在尝试使用Soundex()函数执行相同的搜索,以匹配发音类似于Y. Lie的所有联系人名字。
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y Lie');
在这个示例中,WHERE子句使用Soundex()函数把cust_contact列值和搜索字符串转换成它们的SOUNDEX值。由于Y. Lee和Y. Lie发音相似,它们的SOUNDEX值将会匹配,因此WHERE子句将正确地过滤出想要的数据。
2.日期和时间操作函数
下表列出了一些常用的日期和时间操作函数
函数 | 描述 |
---|---|
Add_Mounth() | 给日期添加月份(也可以减去月份) |
Extract() | 从日期和时间中减去年,月,日,时,分或秒 |
Last_Day() | 返回月份的最后一天 |
Months_Between() | 返回两个月份之间的月数 |
Next_Day() | 返回指定日期后面的那一天 |
Sysdate() | 返回当前日期和时间 |
To_Date() | 把字符串转换成日期 |
--使用案例; --1、查询当前系统时间加3月后的时间:add_month() select add_month(sysdate,3) from dual; --2、查询6月分数据:extract() select * from extract(month from date1); --3、查询月份的最后一天:last_day() 假设位2022/6 select last_day(sysdate) from dual; -->结果:2022/06/30 --4、查询两个时间之间相差的月份数:months_between() select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months from dual; MONTHS ---------- 2.3548387 ———————————————— --5、返回指定日期后面的一天:next_date() select sysdate from dual; SYSDATE ------------------- 2022-07-13 19:30:26 --6、返回当前日期和时间:sysdate() select sysdate from dual; --7、把字符串转换成日期:to_date() select * from table_name where date1 = to_date('2022/07/13', 'yyyy/mm/dd');
注意:最重要的Extract()函数
要记住一件事:数据格式化可能比较棘手。毕竟,”2022-03-04“是指那一天?03是月份以及04是天吗?或者反之亦然?因此,无论何时把一个日期提供给Oracle,都必须明确的说明它是如何格式化的。
SELECT cust_id, order_num FROM orders WHERE order_date = TO_DATE('2015-02-01', 'yyyy-mm-dd');
更灵活的日期运算需要能够提取日期或时间的特定部分。此时,Extract()函数就派上用场了。顾名思义,Extract()用于提取日期和时间的某些部分,允许只处理YEAR、MONTH、DAY、HOUR、MINUTE和SECOND。
下面给出了前面问题的另一种解决方案(该解决方案不需要你弄清楚每个月有多少天,或者担心闰年中的2月):
SELECT cust_id, order_num FROM orders WHERE Extract(Year FROM order_date) = 2015 AND Extract(Month FROM order_date) = 2
分析:
Extract(Year)返回日期中的年份。类似地,Extract(Month)返回日期中的月份。因此,WHERE Extract(Year FROM order_date) = 2015 AND Extract(Month FROM order_date) = 2将检索order_date在2015年和2月的所有行。
3.数值操作函数
数值操作函数当然用于操作数值型数据。这些函数只要用于代数,三角或者几何运算,因此不像字符串或者日期和时间操作函数那样常用。
下表列出常用的数值操作函数:
函数 | 描述 |
---|---|
Abs() | 返回数字的绝对值 |
Cos() | 返回指定角度的三角余弦值 |
exp() | 返回指定数字的指数值 |
mod() | 返回除法运算的余数 |
sin() | 返回指定角度的正弦值 |
sqrt() | 返回指定数字的平方根 |
tan() | 返回指定角度的三角正切值 |
推荐教程:《Oracle视频教程》
以上就是完全掌握Oracle使用数据操作函数的详细内容,更多请关注php中文网其它相关文章!