SQL> create table users

  2  (id varchar2(10),

  3  name varchar2(11),

  4  cardid varchar2(18),

  5  deptno varchar2(10),

  6  regdate date,

  7  age number(4,0));

表已创建。

SQL> insert into users values('1','abc','12345619920505','01',sysdate,'');

已创建 1 行。

SQL> select * from users;

ID         NAME        CARDID             DEPTNO     REGDATE               AGE

---------- ----------- ------------------ ---------- -------------- ----------

1          abc         12345619920505     01         30-1月 -22

SQL> select substr(cardid,7,8) from users;

SUBSTR(CARDID,7,

----------------

19920505

SQL> select replace(deptno,'01','信息技术') from users;

REPLACE(DEPTNO,'01','信息技术')

--------------------------------------------------------------------------------

信息技术

SQL> select mod(age,10) from users;

MOD(AGE,10)

-----------

SQL> update usres set age=25;

update usres set age=25

       *

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> update users set age=25;

已更新 1 行。

SQL> select mod(age,10) from users;

MOD(AGE,10)

-----------

          5

SQL> select extract(year from regdate) from users;

EXTRACT(YEARFROMREGDATE)

------------------------

                    2022

SQL> select * from users where extract(month from regdate) = 5;

未选定行

SQL> select * from users where extract(month from regdate) = 1;

ID         NAME        CARDID             DEPTNO     REGDATE               AGE

---------- ----------- ------------------ ---------- -------------- ----------

1          abc         12345619920505     01         30-1月 -22             25

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2022-01-30 12:57:45

SQL> select to_char(sysdate,'YYYY-MM-DD HH12:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2022-01-30 12:58:01

SQL> select to_date('2015-5-22','YYYY-MM-DD') from dual;

TO_DATE('2015-

--------------

22-5月 -15

SQL> select to_char(12345.678,'$99,999.999') from dual;

TO_CHAR(1234

------------

 $12,345.678

SQL> select to_char(12345.678,'$99,999,999') from dual;

TO_CHAR(1234

------------

     $12,346

SQL> select to_char(12345.678,'S99,999,999') from dual;

TO_CHAR(123

-----------

    +12,346

SQL> select to_number('$1,000','$9999') from dual;

TO_NUMBER('$1,000','$9999')

---------------------------

1.系统时间

sysdate，默认dd-mon-rr  日-月-年

2.日期操作

add_monthw(date,i)

i可以任何整数，如果i是小数，会自动截取整数

next_day(date,char)

如果char的值是‘星期一’，则返回date是指定日期的下一个周一是哪天

last_day(date) 返回日期所在月的最后一天

months_between(date1,date2)---2个日期之间间隔的月份

SQL> select sysdate from dual;

SYSDATE

--------------

30-1月 -22

SQL> select add_months(sysdate,3),add_months(sysdate,-3) from dual;

ADD_MONTHS(SYS ADD_MONTHS(SYS

-------------- --------------

30-4月 -22     30-10月-21

SQL> select next_day(sysdate,'星期一') from dual;

NEXT_DAY(SYSDA

--------------

31-1月 -22

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA

--------------

31-1月 -22

SQL> select month_between('20-5月-15','10-1月-15') from dual;

select month_between('20-5月-15','10-1月-15') from dual

       *

第 1 行出现错误:

ORA-00904: "MONTH_BETWEEN": 标识符无效

SQL> select months_between('20-5月-15','10-1月-15') from dual;

MONTHS_BETWEEN('20-5月-15','10-1月-15')

---------------------------------------

                             4.32258065

SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)

------------------------

                    2022

SQL> select extract(month from sysdate) from dual;

EXTRACT(MONTHFROMSYSDATE)

-------------------------

                        1

SQL> select extract(day from sysdate) from dual;

EXTRACT(DAYFROMSYSDATE)

-----------------------

                     30

SQL> select extract(hour from timestamp '2015-10-1 12:12:12') from dual;

EXTRACT(HOURFROMTIMESTAMP'2015-10-112:12:12')

---------------------------------------------

                                           12

length 计算字符的长度

select length('abc ') from dual;

contact(char1,char2)字符串连接函数----2个参数都必须是字符类型数据

与||操作符的作用相同

trim(c2 from c1) 从字符串c1中去除字符c2

ltrim(c1[,c2]) 去除头部的c2

rtrim(c1,[,c2])

trim(c1) 去除首尾的空格的作用

replace(char,s_string,[,r_string])

省略r_string用空格替换

replace和trim不同，trim一次只可以替换一个字符，replace可以替换字符串

SQL> select concat('ab','cd') from dual;

CONC

----

abcd

SQL> select length('abcd ') from dual;

LENGTH('ABCD')

--------------

             5

SQL> select 'ab'||'cd' from dual;

'AB'

----

abcd

SQL> select trim('ab' from 'abcde') from dual;

select trim('ab' from 'abcde') from dual

       *

第 1 行出现错误:

ORA-30001: 截取集仅能有一个字符

SQL> select trim('a' from 'abcde') from dual;

TRIM

----

bcde

SQL> select ltrim('ababaa','a') from dual;

LTRIM

-----

babaa

SQL> select rtrim('ababaa','a') from dual;

RTRI

----

abab

SQL> select replace('abcde','a','A') from dual;

REPLA

-----

Abcde

SQL> select replace('abcde','a') from dual;

REPL

----

bcde

SQL> select replace('abcde','ab','A') from dual;

REPL

----

Acde

substr 截取字符串

initcap 首字母大写

SQL> select upper('abcd'),lower('DEW33e'),initcap('asd') from dual;

UPPE LOWER( INI

---- ------ ---

ABCD dew33e Asd

SQL> select substr('abcd',2,3),substr('abcde',2),substr('abcde',-2,1) from dual;

SUB SUBS S

--- ---- -

bcd bcde d

round(n[,m])

省略m：0

m>0 小数点后m位

m<0 小数点前m位

ceil(n) 取最大值

floor(n) 取最小值

SQL> select round(23,4),round(23.45,1),round(23.45,-1);

select round(23,4),round(23.45,1),round(23.45,-1)

                                                *

第 1 行出现错误:

ORA-00923: 未找到要求的 FROM 关键字

SQL> select round(23,4),round(23.45,1),round(23.45,-1) from dual;

ROUND(23,4) ROUND(23.45,1) ROUND(23.45,-1)

----------- -------------- ---------------

         23           23.5              20

SQL> select ceil(23.45),floor(23.45) from dual;

CEIL(23.45) FLOOR(23.45)

----------- ------------

         24           23

SQL> select abs(23.45),abs(-23.45),abs(0) from dual;

ABS(23.45) ABS(-23.45)     ABS(0)

---------- ----------- ----------

     23.45       23.45          0

SQL> select mod(5,2) from dual;

  MOD(5,2)

----------

         1

SQL> select mod(5,null) from dual;

MOD(5,NULL)

-----------

SQL> select power(2,3),power(null,2) from dual;

POWER(2,3) POWER(NULL,2)

---------- -------------

         8

SQL> select sqrt(16) from dual;

  SQRT(16)

----------

         4

函数的作用

方便数据统计

处理查询结果

函数分类

1. 内置函数

2. 自定义函数

   
   

1. 数值函数----数值进行操作的函数

2. 字符函数----

3. 日期函数----

4. 转换函数----数据类型进行转换

   
   

在查询语句中使用函数

将小写字母转换成大写字母

查询时间间隔

四舍五入

5.转换函数

5.1日期转换成字符：

to_char(date,fmt，params)

date：将要转换的日期：

fmt：转换的格式（见截图）

params:日期的语言，通常都不写，取默认

select sysdate from dual;--2021/5/12 0:48:37

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;--2021-05-12 00:48:24

5.2字符转换成日期：

to_date(char,fmt,params):按照系统默认格式显示日期

select to_date('2021-05-12','YYYY_MM_DD') from dual;--2021/5/12

5.3数字转换成字符：

to_char(number,fmt);

fmt格式比如下面这种：

9:显示数字并忽略前面的0

0:显示数字，位数不足，用0补齐

.或D:显示小数点

,或G:显示千位符

$:美元符号

s:加正负号（前后都可以，但是不能同时都加上）

select to_char(12345.678,'$99,999.999') from dual;-- $12,345.678

转换的时候注意小数点，否则会四舍五入

5.4字符转换成数字：

to_number(char,fmt)

fmt是转换的格式，可省略

select to_number('$1,000','$9999') from dual;----1000

4.日期函数

系统时间:sysdate

日期操作:

add_months(date,i):

i为任何一个整数，若为小数，则自动取整，若为负数，则减去相应月数；

select add_months(sysdate,3) from dual;

next_day(date,char);

如果char值是'星期一'，则返回date指定日期的下周一是哪天；

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

last_day(date):

返回某月最后一天

extrcat(date from datetime):(ORACLE貌似不支持)

返回datetime相应的日期部分，若返回日期，则写成year from datetime；

select extrcat(day from sysdate) from dual

3.字符函数

3.1  大小写转换函数：

upper(char)：转换为大写;    lower(char)：转换为小写;    initcap(char)将首字母转换为大写；

（用处：比如注册用户名时不区分大小写）

3.2 获取子字符串函数：substr(char,m,n)从m位开始截取n个字符串

（注：m=0从首字母开始截取，m<0 从后面开始截取）

eg：

select substr('60040019951001xxxx',7,8) from dual;

select substr('60040019951001xxxx',-12,8) from dual;

3.3 获取字符串长度函数：

length(char);若char单引号内有空格，也会被计算在内。

3.4 字符串连接函数：

concat(char1,char2);与||操作符作用一样

select concat('ab','cd') from dual;

select 'ab'||'cd' from dual;

3.5 去除子串函数：

trim(c2 from c1);从字符串c1中去除字符c2

select trim('a' from  'abcdea') from dual;--会去除所有带a的

ltrim(c1,c2);从c1中去除c2

select ltrim('extend#123456' , 'extend#') from dual;

rtrim(c1,c2);

select rtrim('abccfdaa' , 'a') from dual;--会将u右侧遇到的a都去除

trim(c1):表示去除首位空格，同理其余两个为去除左/右的空格；

3.6 替换函数：

replace(char,string1,string2)

1.数值函数：

四舍五入函数：

round（n,m）,省略m时：0，m>0,小数点后几位，m<0 小数点前几位；

取整函数：

ceil（n）:取最大值；（这里如果有小数，就会+1，eg：23.45取24）

floor（n）:取最小值（eg：23.45取23）n

绝对值：ABS(n):

取余：MOD（M,N）;若n为null，则结果也为空

eg：select  from mod(acc_code，50) from dual;

power（m,n）:m 的n次幂，2^3=8

sort(n):平方根

2.三角函数

--在查询中使用函数
--员工信息表
create table xcc_users (
id varchar2(10),name varchar2(11),cardid varchar2(18),
deptno varchar2(10),regdate date,ace number(4));
--插入一条数据
insert into xcc_users (id,name,cardid,deptno,regdate,ace)values('1','abc','123456789012345678','01','22-5月-15','25');
select * from xcc_users;
1.在员工信息表查询出员工的生日
select substr(cardid,7,8) from xcc_users;
2.将部门号全部替换成'信息技术'
select replace(deptno,'01','信息技术')from xcc_users;
3.将员工信息表中的年龄字段与10取余数
select mod(ace,10) from xcc_users;
4.取得员工入职的年份
select extract(year from regdate) from xcc_users;
5.查询出5月份入职的员工信息
select * from xcc_users where extract(month from regdate)=5;

-- 转换函数 - 对不同数据的转换
1.日期转换字符的函数  
TO_CHAR(转换的日期[,转换的格式[,日期的语言]])
默认 格式:DD-MON-RR
--日期的语音 一般不用指定
格式有 YY YYYY YEAR,MM MONTH ,DD DAY ,HH24 HH12,MI SS
 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  --示例

2.字符转换成日期的函数
TO_DATE(要转换的日期[,要转换的格式[,日期的语言]])
select to_date('2015-11-11','yyyy-mm-dd hh24:mi:ss') from dual;

3.数字转换字符的函数
TO_CHAR (要转换的数字,[,转换格式])
-- 转换格式 9:显示数字并忽略前面的0 ,0：显示数字，位数不足，用0补齐
-- .或D:显示小数点 , ,或G：显示千位数 $:美元符号, S加正负号(前后都可以)
select to_char(1234.654) from  dual;
select to_char(1234.654,'s$99,90.9999') from  dual;

4.字符转换成数字的函数
TO_NUMBER(要转换的字符,[,格式])
select to_number('$1,000','$9999') from dual;

-- 系统时间
SYSDATE  默认格式: DD-MON-RR  日月年
SELECT SYSDATE FROM DUAL; --示例


-- 日期操作
1.用于添加指定的月份,返回在指定日期添加的月份
ADD_MONTHS(date,i)  
--i如果是小数，则截取整数部分，如果负数，相对于为原日期监区月份
select add_months(sysdate,3),add_months(sysdate,-3) from dual;   --示例
 
 2.   NEXT_DAY(date,char) 返回下一个星期几是哪天
--date 计算的日期 ,
--char 星期几 如果char的值是星期一，则返回date指定日期的下周一是哪天
select next_day(sysdate,'星期一') from dual; --示例

3.LAST_DAY(date) 用于返回date所在月的最后一天
select last_day(sysdate) from dual;

4. MONTHS_BETWEEN(date1,date2) 表示两个日期之间相隔的月份
select months_between(sysdate,'01-1月-21') from dual;

5.EXTRACT(date from datetime) 返回 年或月或日,时/分/秒等
--date 要返回 datetime 的年 year或月 month 或日day
-- datetime 计算的日期时间
 select extract(year from sysdate) from dual; --示例 返回日期年份
  select extract(month from sysdate) from dual; --示例 返回月份
  select extract(day from sysdate) from dual; --示例 返回日
 select extract(hour from timestamp '2015-10-1 17:25:13') from dual; --返回 小时

--获取字符串长度函数
1.   length  字符串长度,空格也包括
select length('a bc ') from dual;

2.  CONCAT(char1,char2) 字符串连接函数,与||操作符的作用一样
 select concat('ab','cd')from dual;--示例
select 'ab'||'cd' from dual;--示例

3.TRIM(c2 FROM c1)去除子串函数 ,表示从字符串c1中去除字符c2 ,一次只能去除一个字符
select trim('a' from 'abcd') from dual;--示例
select trim(' a  3 ') from dual;--示例 只有一个参数，可以去除首尾空格
3.1  ltrim(c1[,c2]) 去除子串函数 ,表示从字符串c1中去除字符c2 ,可以去除多个,只有一个参数，可以去除左边空格
select ltrim('abcabcf' ,'aebcd') from dual;--示例
3.2 RTRIM(c1[,c2]) 去除子串函数 ,表示从字符串c1中去除字符c2 ,这个是去除尾部的第一个,只有一个参数，可以去除右边空格
select rtrim('abcabcfd' ,'abcd') from dual;--示例

4.替换函数 REPLACE(char,s_string[,r_string]) 省略r_string用空格替换
select replace('abced','a','A') from dual;

--   字符函数
-- 1.大小写转换函数
UPPER(char) 小写转大写 ,LOWER(char) 大写转小写, INITCAP 首字母转换为大写 ;
SELECT UPPER('Abc'),lower('ADc'),initcap('abD') FROM dual; --示例

获取/截取,子字符串函数  
SUBSTR(char,[m[,n]])   
--char 源字符串,m 取子串的开始位置,n 截取子串的位数
-- n可以省略，当n省略时表示从m的位置截取到末尾
--当M为零，从头部开始截取,M为负数,从尾部开始截取
SELECT SUBSTR('abcdefg',0,1),SUBSTR('abcdefg',1,2),substr('abcdefg',-3,3) FROM DUAL;

oracle里的一个特殊的表,dual表，有一些函数必须指定目标表，可以写这个，这个里面是一个一行一列的表;
-- 数值函数
-- 1.四舍五入
--ROUND(n,[,m])
--省略m:0
--m>0:小数点后m位,m<0:小数点前m位
--n表示要进行四舍五入的值,m表示保留小数点后几位或前几位
select round(23.4),round(23.45,1),round(23.45,-1) from dual  ;
select * from dual;
-- 2.取整函数
CEIL(n) 取最大值,向上取整,后面多大都会进一位
FLOOR(n) 取最小值,向下取整,后面多小都会进一位
select ceil(23.45),floor(23.45) from dual;
-- 3.常用函数
ABS(n) 取绝对值，如果是正就返回本身,负数就返回正的，0还是0;
select abs(23.45),abs(-23) from dual;
MOD(m,n)  取余数 ,m表示除数,n表示被除数 ,表示m/n,如果m或n有一个是null值，返回null;
select mod(5,2),mod(10,null),mod(10,2) from dual;
POWER(m,n) 表示返回m的n次幂
select power(2,3),power(null,2) from dual;
SQRT(n) 表示平方根，sqrt(16),结果是4
select sqrt(16) from dual;
-- 4.三角函数
正弦值 SIN(N),反弦值 ASIN(N)
余弦值 COS(N),反余弦值 ACOS(N)
正切值 TAN(N),反正切值 ATAN(N)

函数的作用：方便数据的统计，处理查询结果

函数分类

数据库自带函数/自定义函数

还可分为

数值函数，字符函数，日期函数，转换函数

使用小技巧

补充一下“

replace 是整体替换

translate 是逐个替换

replace('qwertra','we','W')
translate('qwertra','we','W')

执行体会一下

字符函数总结