一、字符函数

1. lower(char) upper(char)

lower(char) --将字符串转化为小写的格式
upper(char) --将字符串转化为大写的格式 

2. length(char)

length(char) --返回字符串的长度
例: select * from emp where length(ename) = 5;

3. replace(char, search_string, replace_string)

replace(char, search_string, replace_string)     --替换字符串        
  char -等待替换的字符串  search_string -搜索需要替换的字符串  replace_string -替换字符串

select replace(ename,'.net','Java') from emp;  --显示所有员工的姓名,用"Java"替换所有".net" 

4. substr(char, m, n)

substr(char, m, n)    substr(某个列名里的String,开始位置,截取长度)
                m代表第几个位置(从1开始),n代表取n个字符的意思    m为负数,则表示从右边开始数第几个位置       
示例:
select substr('hello world', 2) FROM DUAL;          --结果:ello world
select substr('hello world', -2) FROM DUAL;         --结果:ld
select substr('This is a test', 6, 2)               --结果:is
select substr('This is a test', -3, 3)              --结果:est

5. instr( string1, string2, start_position,nth_appearance )
  string1:源字符串,要在此字符串中查找;
  string2:要在string1中查找的字符串;
  start_position:代表从string1的哪个位置开始查找,此参数可选,如果省略则默认为1。字符串索引从1开始,如果此参数为正,从左到右开始检索;如果此参数为负,则从右到左检索;
  nth_appearance:代表要查找第几次出现的string2,此参数可选,如果省略默认为1,不能为负。

instr('源字符串' , '目标字符串' ,'开始位置','第几次出现')

select instr('syranmo','s') from dual;        -- 返回 1, 位置索引从1开始
select instr('syranmo','ra') from dual;       -- 返回 3 
select instr('syran mo','at',1,2) from dual;  -- 返回 0, 没有找到返回值是0

--由于ra只出现2次,而start_position为3,即结果返回0
select instr('oracle traning', 'ra', 1, 3) from dual;

6. substr 和 instr

substr和instr结合使用来实现截取字符串中 特定字符前后的字符串

--截取"hello,world"字符串中','分隔符之前的字符串   结果:hello
select substr('hello,world', 1, instr('hello,world', ',')-1) from dual;

--截取"hello,world"字符串中','分隔符之后的字符串   结果:world                             
select substr('hello,world', instr('hello,world', ',')+1) from dual;

-- 截取"hello,world,HH"字符串中第1次出现的','字符和第2次出现的','字符之间的字符串  结果:world
select substr('hello,world,HH', instr('hello,world,HH', ',',1)+1, instr('hello,world,HH', ',', 2)-1) from dual;

7. Concat 和 '||'

--concat只能连接两个字符串,连接多个需要嵌套调用不方便
SQL> select concat('aa','bb') from dual;

--||直接连接多个字符串
SQL> select 'aa'||'bb'||'cc' from dual;

二、数学函数

1. round(n, m) 保留几位小数(四舍五入)
  如果省掉m,则四舍五入到整数;如果m是正数,则四舍五入到小数点的m位后;如果m是负数,则四舍五入到小数点的m位前。

select round(23.75123) from dual;       --返回24 
select round(23.75123, -1) from dual;   --返回20 
select round(23.75123, 1) from dual;    --返回23.8 
select round(23.75123, 2) from dual;    --返回23.75 

2. trunc(n, m) --对数字和日期进行操作

trunc(number):
- 截取数字(几位小数),无需四舍五入;
- 如果省掉m,就截去小数部分;
- 如果m是正数就截取到小数点的m位后;
- 如果m是负数,则截取到小数点的前m位。

select trunc(23.75123) from dual;     --返回23 
select trunc(23.75123, -1) from dual; --返回20 
select trunc(23.75123, 1) from dual;  --返回23.7 
select trunc(23.75123, 2) from dual;  --返回23.75 

trunc(Date):
- trunc(sysdate,'yyyy'); --返回当前年的第一天
- trunc(sysdate, 'mm');//返回当前月的第一天
- trunc(sysdate, 'dd');//返回当前时间的年月日
- trunc(sysdate, 'd');//返回当前星期的第一天
- trunc(sysdate, 'hh');//返回当前小时

select trunc(sysdate) from dual --2018-12-22 今天的日期为2018-12-22
select trunc(sysdate,'mm') from dual --2018-12-01 返回当月第一天.
select trunc(sysdate,'d') from dual --2018-12-16 (星期天)返回当前星期的第一天

3. 取余、取商、取整、绝对值、与0判断函数

取余:
    select mod(10,2) from dual;    –0 
    select mod(10,3) from dual;    –1    

取商:
    select trunc(20/3) from dual;   -6

取整:
    floor(n) / trunc(n)    无需四舍五入有  
    ceil(n) 四舍五入

绝对值:
    abs(n) 返回数字n的绝对值

sign
    语法: sign(表达式);
    若表达式>0则返回1,若表达式<0则返回-1,=0返回0

三、转换函数

转换函数用于将数据类型从一种转为另外一种

1. to_char( )函数

用作日期转换: select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

    yyyy:四位数字的年份 2004年 
    mm:两位数字的月份 8 月–>08 
    dd:两位数字的天 30 号–>30 
    hh24: 8点–>20 
    hh12:8点–>08 
    mi、ss–>显示分钟\秒 

处理数字(常见)
  to_char(number,'格式');
  select to_char(888) from dual;   --'888'
  select to_char(123,'09999')  from dual;   --'00123'
  select to_char(1314.73, '9999.9') from dual;   --'1314.7'
  select to_char(1314.73, '9,999.99') from dual;   --'1,314.73'
  select to_char(1314.73, '$9,999.00') from dual;  --'$1,314.73'

2. to_date( )函数

--将字符串转换成date类型的数据
select to_date('2018-08-13 09:25:30','yyyy-mm-dd hh24:mi:ss') from dual;

3. to_number( )

将char或varchar2类型的string转换为一个number类型的数值,

    select to_number('123') from dual;              --结果:123
    select to_number('00123') from dual;           --结果:123
    to_number和to_char恰好是两个相反的函数:
    select to_char(123) from dual;     --结果:'123'

可以用来实现进制转换;16进制转换为10进制:
    select to_number('19f','xxx') from dual;                       --415
    select to_number('f','xx') from dual;                            --15

四、日期和时间函数

在Oracle中,系统提供了许多用于处理日期和时间的函数,通过这些函数可以实现计算需要的特定日期和时间,常用的日期和时间函数如下:

1. sysdate --返回系统当前日期

select sysdate as nowtime from dual;

2. next_day()

--n为1到7的数字,分別对应周日到周六     查找下一个星期几对应的日期
select next_day(sysdate,n) from dual;

3. months_between(d1, d2)
  返回d1和d2之间的数目,若d1和d2的日期都相同,或者都是该月的最后一天,则返回一个整数,否则返回的结果将包含一个小数


五、聚合函数

1. avg() --平均值运算

--查询平均成绩大于80的学生姓名和平均成绩 
select A.sname,avg(B.num) from student A,score B,course C
  where 1=1
    and A.sid = B.student_id
    and B.course_id =C.cid
    and C.cname in('生物','物理','体育','美术')
  group by A.sname
  having avg(B.num) > 80

用avg函数进行平均运算时会忽略空值(即原始数据中如果存在空值,最终计算的平均值不对)
avg(nvl(comm,0))或者sum(comm)/count(*)

2. sum() --某列求和

--求每日的销量总和和每日的销量均值
select to_char(sysdate,'yyyy/mm/dd'),sum(销量),avg(销量) from table group by 日期;    

3. count() --统计结果集的行数

--count(*),count(1),count(列名)效率其实没多大区别,区别在于统计null 
count(*)/count(1)将返回表格中所有存在的行的总数包括值为null的行
count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入)

4. max() / min()

-- 查询当天最大的价格
select nowday,max(price) from goods group by nowday;

-- 查询每个人员最小离开时间和最大离开时间
select personid,max(leavetime),min(leavetime) from person_leave group by personid;

六、其他函数

1. decode函数

使用decode判断字符串是否一样,decide()函数,它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
decode (expression, search_1, result_1, default)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)
比较表达式和搜索字,如果匹配,返回结果;如果不匹配,返回default值;default可以不定义,则返回空值。

select id,name,decode(lever,1,'教授',2,'副教授',3,'导师','讲师') position from some_tables where t.lever in (1,2,3);

逻辑如下:
    if(lever==1) then return '教授'(翻译值1)
    else if(lever==2) then return '副教授'(翻译值2)
    else if(lever==3) then return '导师'
    else return '讲师'(默认值)

使用decode实现表或试图的行转列:
select name,
    sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",
    sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",
    sum(decode(subject, 'English', nvl(score, 0), 0)) "English"
from student_score
group by name;

2. case when then else end

case具有两种格式,简单case函数和case搜索函数。

--简单case函数,使用表达式确定返回值.
  case search_expression                       --case sex
  when expression1 then result1                --when '1' then '男'
  when expression2 then result2                --when '2' then '女'                                                                                     
  ...
  else default_result                          --else '其他' end
  end

 --case搜索函数,使用条件确定返回值.
  case
  when condition1 then result1                --case when sex = '1' then '男'
  when condistion2 then result2               --when sex = '2' then '女'
  ...
  else default_result                         --else '其他' end
  end

这两种方式都能实现相同的功能,简单case函数的写法相对比较简洁,但是和case索函数相比功能方面会有些限制

3. nvl() 和 nvl2()

  • nvl(value1,value2):如果value1的值为null,那么函数返回value2,如果value1不为null,函数返回value1的值;
  • nvl2(value1,value2,value3):如果value1的值为null,函数返回value3,否则函数返回value2。

4. union / union all

union/union all 都要求两个sql查询列要相同

union: 对两个结果集进行并集操作(不包括重复行),同时进行默认规则的排序;
union All: 对两个结果集进行并集操作(包括重复行),不进行排序

从效率上说,union all要比union快很多,如果可以确认合并的两个结果集中不包含重复的数据,尽量使用union all,因为union需要进行排序去除重复记录,效率低

5. + 号 / left join... on...

+ 左右外连接,哪边有 + 号哪边就可以为空
select * from a,b where a.id=b.id(+);   --(+)写在where后面,不能与or/in连用,b表是附属表

left join on:
select * from a left join b on a.id=b.id;   --左连接,写在 from 与where之间

效率上没区别,不过left join可读性高、功能更全面、通用性强、而且是新标准,建议使用left join

6. group by / order by

group by 分组,用来统计某些字段的信息
order by 排序,根据升降序显示结果集 asc升序   desc降序 

当group by和order by 一起使用时,先执行group by 在执行 order by

group by 是分组,首先用group by的前提是你的select里边用了聚合函数如sum(),avg(),min(),max(),没有聚合函数用不了group by不然会报错;
其次出现在select里边的除聚合函数以外的所有字段,都必须作为group by的条件,也就是说select中的字段必须出现在group by中,group by中的分组条件不一定出现在select中

星河滚烫,你是人间理想