(Oracle基础篇) Oracle 内置函数_oracle 将字母替换-CSDN博客

难点：字符串日期转化为时间

将字符转化为数字

思路：

内置函数包含：单行函数和多行函

单行函数：数字符函数、数字函数、日期函数、转换函数

多行函数：avg、count、max、min、sum

字符函数：大小写转换函数LOWER UPPER INITCAP

字符处理函数CONCAT SUBSTR LENGTH INSTR LPAD|RPAD TRIM REPLACE

实例：

--大小写转化函数

LOWER (strexp) 返回字符串,并将所有的字符小写.

UPPER (strexp) 返回字符串,并将所有的字符大写.

INITCAP(strexp) 将字符串的(每个单词的)第一个字母变为大写,后面的小写

LOWER (strexp) 返回字符串,并将所有的字符小写

UPPER (strexp) 返回字符串,并将所有的字符大写
INITCAP(strexp) 将字符串的(每个单词的)第一个字母变为大写;

字符函数处理：1、CONCAT(strexp, strexp): 连接两个字符串

2、Substr(str,start_index,length) :从指定的位置截取指定长度的字符串

3、LENGTH(strexp):返回字符串的长度

4、INSTR(C1,C2,I,J):在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1、被搜索的字符串 ; C2、希望搜索的字符串 ;I、搜索的开始位置,默认为

1 ;J 、匹配序号,默认为1.

例如：INSTR(‘CORPORATE FLOOR’,‘OR’, 3, 2)中，源字符串为‘CORPORATE

FLOOR’, 目标字符串为‘OR’，起始位置为3，取第2个匹配项的位置。
5、LPAD( string1, padded_length, [ pad_string ] ) 在列的左边粘贴字符

其中string1是需要粘贴字符的字符串
padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，

lpad函数将会把字符串截取成padded_length;
pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参

数未写，lpad函数将会在string1的左边粘贴空格。

6、RPAD(粘贴字符) RPAD 在列的右边粘贴字符

数字函数:是对数字进行操作的函数

Round：传回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果。

ROUND( number, decimal_places )

number : 需四舍五入处理的数值，

decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 )

ROUND(45.926, 2) 45.93

TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。

TRUNC(45.926, 2) 45.92

mod（number1，number2）两个数值相除并返回其余数。运算符执行 number1 除以 number2 操作

MOD(1600, 300) 100

日期函数：

MONTHS_BETWEEN(date2,date1) 两日期相差多少月

select months_between('01-4月-08','02-8月-08') from dual;

select months_between(to_date('1-4月-08','dd-mon- yyyy'),to_date('2-8月-08','dd-mon-yyyy')) from dual

Add_months(date1,number) 加月份到日期得到新的日期

select add_months(to_date('01-4月-08','dd-mon-yy'),6) from dual;

NEXT_DAY(date,'day') 给出日期date和星期x之后计算下一个星期的日期

select next_day(to_date('01-4月-08'),'星期二') from dual;

Last_day(date1) 计算指定日期所在月份的最后一天的日期

select last_day(to_date('01-4月-08')) from dual;

Round(date1) 取得按年或月四舍五入得到的新日期

Trunc(date1) 取得按年或月截取得到的新日期

转换函数：

TO_CHAR(date,'fmt') ：是字符类型的函数，转化日期为字符

格式('fmt')

1、必须用单引号括起来,并且是大小写敏感

2、可包含任何有效的日期格式

3、fmt值的宽度正好能容纳所有的有效数字

日期转换规范:

YYYY YYY YY	以数字表示全年(分别代表4位、三位、两位)的数字年
YEAR	年的拼写
MM	两位数字月
MONTH	月的全拼
MON	月名称的缩写
DD	数字日
DAY	星期的全拼
D	星期中的第几天
DY	表示三位缩写的星期

--中文环境

--修改语言环境

alter session set nls_language='SIMPLIFIED CHINESE'

--测试

select sysdate from dual;

select to_char(sysdate,'dd-mon-yyyy') from dual;

select next_day(sysdate,'星期五') from dual;

select to_date('01-11月-2007','dd-mon-yyyy') from dual;

select to_char(sysdate,'DDSPTH-mon-yyyy') from dual;

--DDSPTH 日期的英文形式

--英文环境

--修改语言环境

alter session set nls_language=’AMERICAN’;

--测试

select sysdate from dual;

select to_char(sysdate,'dd-mon-yyyy') from dual;

select next_day(sysdate,'friday') from dual;

select to_date('01-nov-2007','dd-mon-yyyy') from dual;

select to_date('01-nov-2007','DDSPTH-mon-yyyy') from dual;

数字类型格式控制符

9	代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示）
0	强制显示该位，如果当前位有数字，显示数字，否则显示0
$	增加美元符号显示
L	增加本地货币符号显示(RMB)
.	小数点符号
,	千分位符号 3,000,000,000.00

l Salary ：3000.45

l select to_char(salary,'$99,999.99') from employees

------------------------ $3,000.45

l select to_char(salary,'$99,999.999') from employees

------------------------ $3,000.450

l select to_char(salary,'$000,000.00') from employees

------------------------ $003,000.45

l select to_char(salary,'L000,000.00') from employees

------------------------RMB003,000.45

例子：

--单行函数

select lower('I LOVE YOU') from dual

--查询ename 转化为小写

select lower(ename) from emp

select upper('aaaa') from dual

select initcap('tHE sOAP') from dual

--****************************************************************************************************

--字符处理函数

1、CONCAT(strexp, strexp): 连接两个字符串

select concat('I',' LOVE YOU') from dual

--输出员工的姓名

select concat(first_name,last_name) from employees

2、Substr(str,start_index,length) :从指定的位置截取指定长度的字符串

select substr('abcdefg',2,3) from dual

3、LENGTH(strexp):返回字符串的长度

select length('abcdef') from dual

--输出员工姓名的长度

select length(first_name),length(last_name),length(concat(first_name,last_name)) from employees

4、INSTR(C1,C2,I,J):在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1、被搜索的字符串 ; C2、希望搜索的字符串 ;I、搜索的开始位置,默认为

1 ;J?、匹配序号,默认为1.

例如：INSTR(‘CORPORATE FLOOR’,‘OR’, 3, 2)中，源字符串为‘CORPORATE

FLOOR’, 目标字符串为‘OR’，起始位置为3，取第2个匹配项的位置.

select instr('corporate floorddddor','or',1,3) from dual

5、LPAD( string1, padded_length, [ pad_string ] ) 在列的左边粘贴字符

其中string1是需要粘贴字符的字符串 .

padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，

lpad函数将会把字符串截取成padded_length; .

pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参

数未写，lpad函数将会在string1的左边粘贴空格。

select lpad('aaa',10,'*') from dual

select lpad('aaa',10) from dual

select lpad('aaagggkkkppp',10,'*') from dual

6、RPAD(粘贴字符) RPAD? 在列的右边粘贴字符

select rpad('aaa',10,'*') from dual

select rpad('aaa',10) from dual

select rpad('aaagggkkkppp',10,'*') from dual

--****************************************************************************************************

Trim():截取字符串两端特殊字符

select trim(' ddd ') from dual

select ' ddd ' from dual

replace(str,search_str[,replace_str]):将每次在str中出现的search_str用replace_str替换

select replace('HE LOVE YOU','HE','I') from dual

--****************************************************************************************************

--数学函数

round：传回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果。

round(num1,小数位数) 或

round(num1) --此时小数位数默认为0

select round(3000.45) from dual

select round(3000.55) from dual

select round(3000.559,2) from dual

TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，

只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。

select trunc(3000.45) from dual

select trunc(3000.55) from dual

select trunc(3000.559,2) from dual

mod（number1，number2）两个数值相除并返回其余数。运算符执行 number1 除以 number2 操作

select mod(1600,300) from dual

--****************************************************************************************************

--日期的处理

TO_CHAR(date,'fmt') ：是字符类型的函数，转化日期为字符

格式('fmt')

1、必须用单引号括起来,并且是大小写敏感

2、可包含任何有效的日期格式

3、fmt值的宽度正好能容纳所有的有效数字

--sysdate

--修改中文语言环境

alter session set nls_language='SIMPLIFIED CHINESE'

--输出格式yyyy-mm-dd

select to_char(sysdate,'yyyy-mm-dd') from dual

--输出格式yyyy-month-dd

select to_char(sysdate,'yyyy-month-dd') from dual

select to_char(sysdate,'yyyy-MONTH-DD') from dual

--输出格式yyyy-mon-dd

select to_char(sysdate,'yyyy-MON-DD') from dual

select to_char(sysdate,'yyyy-MON-DD day') from dual

select to_char(sysdate,'yyyy-MON-DD-dy') from dual

--输入日期的字符串表示格式:

--MON-DD-YYYY DAY

select to_char(sysdate,'MON-DD-YYYY DAY') from dual

select to_char(sysdate,'MON-DD-YYYY DAY D') from dual

--修改英文语言环境

alter session set nls_language='AMERICAN'

--yyyy mm dd --中英文一致

select to_char(sysdate,'yyyy-mm-dd') from dual

select to_char(sysdate,'yyyy-month-dd') from dual

select to_char(sysdate,'year-mon-dd') from dual

select to_char(sysdate,'YEAR-MON-DD') from dual

select to_char(sysdate,'YEAR-MON-DD-DAY-D-DY') from dual

--ddspth(DDSPTH) 日期的英文表示

select to_char(sysdate,'YEAR-MON-DDSPTH-DAY-D-DY') from dual

select to_char(sysdate,'year-mon-ddspth-day-d-dy') from dual

--开发中使用中英文通用

select to_char(sysdate,'yyyy-mm-dd') from dual

--HH24 MI SS

select to_char(sysdate,'year-mon-ddspth HH24:MI:SS') from dual

--AM和PM没有区别

select to_char(sysdate,'year-mon-ddspth HH24:MI:SS AM') from dual

select to_char(sysdate,'year-mon-ddspth HH24:MI:SS PM') from dual

--输出月 "of" 年用双引号括起来以加字符串

select to_char(sysdate,'month "of" yyyy') from dual

--字符(符合一定格式)转化为日期

用TO_DATE函数将字符转换为日期

格式:TO_DATE(char[, 'fmt'])

--中文环境

--==把字符串 08-4月-2008 转化为日期(各个部分必须对应,包括顺序)

select to_date('08-4月-2008','dd-mon-yyyy') from dual --正确的

select to_date('08-4月-2008','mon-dd-yyyy') from dual --错误的

--反面看问题

--==把字符串 4月-08-2008 转化为日期

select to_date('4月-08-2008','mon-dd-yyyy') from dual --正确的

--12月-25-2009 4月-08-2008

select to_char(sysdate,'mon-dd-yyyy') from dual

alter session set nls_language='AMERICAN'

select to_char(sysdate,'ddspth-mon-yyyy') from dual

--开发中

select to_date('2008-12-12','yyyy-mm-dd') from dual

--****************************************************************************************************

TO_CHAR(number,'fmt')：是字符类型的函数，转化数字为字符

select to_char(3000.45,'999999999.9999') from dual

select to_char(3000.45,'$999999999.9999') from dual

select to_char(3000.45,'L999999999.9999') from dual

select to_char(3000.45,'L00000000000.00000') from dual

select to_char(3000.45,'L00,000,000,000.00000') from dual

--****************************************************************************************************

使用TO_NUMBER函数将字符转换为数字

TO_NUMBER(char [, 'fmt']))

--将$3000.45转化为数字

select to_number('$3000.45','$9999.99') from dual

--将$3,000,888.45转化为数字

select to_number('$3,000,888.45','$9,999,999.99') from dual

--将3,000,888.45转化为数字

select to_number('3,000,888.45','9,999,999.99') from dual

--****************************************************************************************************

日期函数

MONTHS_BETWEEN(date2,date1) --计算两个日期相差多少个月

Add_months(date1,number) --加月份到日期得到新的日期

NEXT_DAY(date,'day') --给出日期date和星期x之后计算下一个星期的日期

Last_day(date1) --计算指定日期所在月份的最后一天的日期

--计算 '01-6月-2006' 和 '03-4月-2008' 相差多少个月

select months_between(to_date('01-6月-2006','dd-mon-yyyy'),to_date('03-4月-2008','dd-mon-yyyy')) from dual

--'03-4月-2008' 加上10个月计算新的日期

select add_months(to_date('03-4月-2008','dd-mon-yyyy'),10) from dual

--'03-4月-2008' 加上10个月,在加两天计算新的日期

select add_months(to_date('03-4月-2008','dd-mon-yyyy'),10)+2 from dual

----'01-4月-2008' 加上10个月,在加两天计算新的日期

select next_day(to_date('01-4月-2008','dd-mon-yyyy'),'星期五') from dual

select next_day(to_date('03-4月-2008','dd-mon-yyyy'),'星期三') from dual

select last_day(to_date('02-2月-2008','dd-mon-yyyy')) from dual

--Round(date1) Trunc(date1)

select round(to_date('05-4月-2008','dd-mon-yyyy'),'YY') from dual --本年第一天

select round(to_date('05-4月-2008','dd-mon-yyyy'),'MM') from dual --本月第一天

select round(to_date('05-4月-2008','dd-mon-yyyy'),'DD') from dual --当前的日期

select round(to_date('05-4月-2008','dd-mon-yyyy'),'DY') from dual --本周的第一天

select round(to_date('02-4月-2008','dd-mon-yyyy'),'DY') from dual --本周的第一天

select round(to_date('03-4月-2008','dd-mon-yyyy'),'DY') from dual --本周的第一天(星期四进位)

select trunc(to_date('05-4月-2008','dd-mon-yyyy'),'YY') from dual --本年第一天

select trunc(to_date('05-4月-2008','dd-mon-yyyy'),'MM') from dual --本月第一天

select trunc(to_date('05-4月-2008','dd-mon-yyyy'),'DD') from dual --当前的日期

select trunc(to_date('05-4月-2008','dd-mon-yyyy'),'DY') from dual --本周的第一天

select trunc(to_date('02-4月-2008','dd-mon-yyyy'),'DY') from dual --本周的第一天

select trunc(to_date('03-4月-2008','dd-mon-yyyy'),'DY') from dual --本周的第一天(星期四进位)

--****************************************************************************************************

导出：exp

导入：imp

常用分组函数

AVG ([DISTINCT|ALL]n)

COUNT ({ *|[DISTINCT|ALL]expr})

MAX ([DISTINCT|ALL]expr)

MIN ([DISTINCT|ALL]expr)

SUM ([DISTINCT|ALL]n)

分组函数在计算时省略列中的空值

NVL函数迫使分组函数包括空值

SELECT AVG(NVL(commission_pct,0)) 2 FROM employees

Group by分组查询和having子句

SELECT MAX(salary)

FROM employees

GROUP BY department_id;

HAVING MAX(salary)>1000

SELECT   department_id,MAX(salary)
      FROM     employees
      GROUP BY department_id
      HAVING department_id>20

总结执行流程:

在整个语句执行的过程中，最先执行的是Where子句，在对表数据进行过滤后，符合条件的数据通过Group by进行分组，分组数据通过Having子句进行组函数过滤，最终的结果通过order by子句进行排序，排序的结果被返回给用户。