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"
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.
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.
Function | Description |
---|---|
Return the length of the string | |
Convert the string into lowercase form | |
Fill the left side of the string with spaces | |
Trim the spaces from the left side of the string | |
Padding spaces on the right side of the string | |
Trim spaces from the right side of the string | |
Returns the SOUNDEX value of the string | |
Returns the characters in the string | |
Convert the string to uppercase |
函数 | 描述 |
---|---|
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月的所有行。
数值操作函数当然用于操作数值型数据。这些函数只要用于代数,三角或者几何运算,因此不像字符串或者日期和时间操作函数那样常用。
下表列出常用的数值操作函数:
函数 | 描述 |
---|---|
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!