Completely master Oracle's use of data manipulation functions

This article brings you relevant knowledge about Oracle, which mainly introduces the related issues of using data operation functions. Like almost all other computer languages, SQL supports the use of functions to operate. Data and functions are usually used to perform operations on data for conversion and manipulation. Let's take a look at them together. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

1. Overview:

Like almost all other computer languages, SQL supports the use of functions to manipulate data. Functions are often used to perform operations on data for transformation and manipulation.

2. Function classification

• Text functions: used to operate text strings (for example, trim or fill values, and convert values ​​into uppercase and lowercase).
• Numeric functions: Used to perform mathematical operations on numerical data (for example: returning absolute values ​​and performing algebraic calculations).
• Date and time functions: used to manipulate date and time values, and extract specific parts from these values ​​(for example: returning the difference between dates and checking date validity) .
• System functions: Return information specific to all DBMSs (for example: return user login information or check version details).

1. Text operation function

Example:

SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

Result:

As you can see, Upper() converts the text into uppercase. The following table lists some commonly used text manipulation functions.

##Length() Return the length of the stringLower()Convert the string into lowercase formLPad()Fill the left side of the string with spacesLTrim()Trim the spaces from the left side of the stringRPad ()Padding spaces on the right side of the stringRTrim()Trim spaces from the right side of the stringSoundex()Returns the SOUNDEX value of the stringSubString()Returns the characters in the stringUpperConvert the string to uppercase

--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.日期和时间操作函数

下表列出了一些常用的日期和时间操作函数

Function	Description

函数	描述
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视频教程》

The above is the detailed content of Completely master Oracle's use of data manipulation functions. For more information, please follow other related articles on the PHP Chinese website!