oracle函数与分组(学习Oracle的第三天)

一、单行函数
  单行函数只处理单个行，并且为每行返回一个结果。
  单行函数分为以下几种：
      1.字符函数：处理字符串
      2.数学函数：用于数学计算
      3.转换函数：数据类型转换
      4.日期函数：处理日期和时间
      5.正则表达式：使用正则表达式搜索数据

  字符函数
1.CONCAT()函数
   语法：CONCAT(x,y)
   将x和y拼接起来，并返回新字符串

  例如：将EMP表的empno和ename列进行拼接
    select CONCAT(empno,ename) from emp; 
    等同于：select empno||ename from emp;

2.INITCAP()函数
    语法：INITCAP(x)    //字符串的首字母变成大写字母，其它字母为小写
       将字母字符串转换为每个词首字母为大写，其它字母为小写
     
     例如：将EMP表的ename列进行转换
     select empno,INITCAP(ename) from emp; 

3.INSTR()函数  --->  类似于java的indexOf();   返回子串位置
    语法：INSTR(x,find_string [,start] [,occurrence])
        返回指定字符串find_string在x中数字位置。可以指定开始搜索的位置start,
    并提供该字符串出现的次数occurrence。start和occurrence默认是1，表示从字符
    串开始的位置开始搜素，并返回第一次出现的位置。
    
    例如：在EMP表的ename列中查找'MITH'出现的位置
        select ename,INSTR(ename,'MITH') from emp;
    例如：在EMP表中ename列，从1位置开始查找第2个'L'出现的位置
    select ename , INSTR(ename,'L',1,2) from emp;

4.LENGTH()函数
    语法：LENGTH(x)  
        返回表达式中的字符数

    例如：计算emp表中雇员名的长度
    select ename,length(ename) from emp;

    同样可以计算数字、日期的长度。
       例如：计算工资的长度
       select ename,sal,length(sal) from emp;

       例如：计算日期的长度
       select ename,hiredate,length(hiredate) from emp;

5.LOWER()和UPPER()函数
    语法：
        LOWER(column|expression)：将字母字符值转换为小写
    UPPER(column|expression)：将字母字符值转换为大写

    例如：分别用大写和小写形式显示雇员姓名
        select UPPER(ename) as 大写,LOWER(ename) as 小写 from emp;
       
6.LPAD()和RPAD()函数  --> 左填充  右填充
    语法：LPAD(x,width [, pad_string])
        在字符串左侧填充pad_string字符，以使总字符宽度为width
      RPAD(x,width [, pad_string]) 
        在字符串右侧填充pad_string字符，以使总字符宽度为width

    例如：
       select RPAD(ename,30,'.') , LPAD(job,18,'*+') from emp;

7.LTRIM(),RTRIM()和TRIM()函数  
    语法：
    LTRIM(x [, trim_string])：从x字符串左侧去掉所有的trim_string字符串，
    如果没有指定trim_string字符串，则默认为去除左侧空格。
    RTRIM(x [, trim_string])：从x字符串右侧去掉所有的trim_string字符串，
    如果没有指定trim_string字符串，则默认为去除右侧空格。   
    TRIM(trim_string FROM x)：从x字符串两侧去除trim_string字符串。

    例如：去掉左侧空格、右侧空格、两侧空格
        select ltrim('    hello,javascipt!') from dual;
    select rtrim('hello,javascipt!        ') from dual;
    select trim('    hello,javascipt!     ') from dual;

8.NVL()函数
    语法：NVL(x,value)
        用于将一个NULL值转换为另外一个值。如果x是NULL值的话返回value值，否则返回x值本身。
     
    例如：查询emp表，如果comm列为NULL值，则显示0
        select empno,ename,NVL(comm,0) from dual;

    语法：NVL2(x,value1,value2)
        如果x值不为NULL，返回value1，否则返回value2

    例如：查看emp表奖金情况
        select empno,ename,NVL2(comm,'有奖金','无奖金') from emp;

9.REPLACE()函数
    语法：
        REPLACE(x,search_string,replace_string)
        从字符串x中搜索search_string字符串，并使用replace_string字符串替换。
        并不会修改数据库中原始值。

    例如：查找EMP表中的ename列，将'SMITH'替换成'史密斯'
        select ename , replace(ename,'SMITH','史密斯') as newname from emp where empno = 7369;  

10.SOUNDEX()函数
    语法：SOUNDEX(x)
        返回代表x字符串的语音的表示形式，可用于查找一些语音相同但是拼写不同的单词。

    例如：select ename from emp where SOUNDEX(ename) = SOUNDEX('smith');

11.SUBSTR()函数
    语法：SUBSTR(x,start [, length])
            返回字符串中的指定的字符，这些字符从字符串的第start个位置开始，长度
        为length个字符；如果start是负数，则从x字符串的末尾开始算起；如果length
    省略，则将返回一直到字符串末尾的所有字符。

    例如：从emp表中提取ename列从第2个字符开始，长度为3的字符串
        select ename , substr(ename,2,3) from emp;

---

二、数学函数

 

例：
select abs(-10) from dual;  -- 10
select ceil(-5.67) from dual;  --  -5
select ceil(5.67) from dual;    --  6
select floor(-5.67) from dual;   --  -6
select floor(5.67) from dual;   --  5
select power(2,4) from dual;   --  16
select mod(10,4) from dual;   --  2
select sqrt(25) from dual;    --  5
select sqrt(26) from dual;   --   5.09901951359278483002822410902278198956
select TRUNC(5.79) from dual;   -- 5
select TRUNC(5.79,0) from dual;   -- 5
select TRUNC(5.79,1) from dual;   -- 5.7
select TRUNC(5.79,-1) from dual;   -- 0
select TRUNC(55.79,-1) from dual;   -- 50
select TRUNC(555.79,-2) from dual;  --  500
select ROUND(5.75) from dual;  -- 6
select ROUND(5.75,1) from dual;  -- 5.8
select ROUND(5.74,1) from dual;  -- 5.7
select ROUND(5.75,-1) from dual;  --  10
 

---

三、转换函数

1.TO_CHAR()函数
    语法：TO_CHAR(x [, format])
        将x转化为字符串。format为转换的格式，可以为数字格式或日期格式。

    例如：将数字转换为字符串
        select TO_CHAR(12345.67) from dual;

    将数字以指定格式输出：
        例如：以指定99,999.99格式输出12345.67
    select TO_CHAR(12345.67,'99,999.99') from dual;  -- 12,345.67
    select TO_CHAR(12345.67,'99,999.99L') from dual;  --   12,345.67￥    
    select TO_CHAR(1234,'9999.99EEEE') from dual;  --    1.23E+03
    select TO_CHAR(1234,'9999.999EEEE') from dual;  --   1.234E+03

2.TO_NUMBER()函数
    语法：TO_NUMBER(x [, format])
        将x转换为数字。可以指定format格式

    例如：
        select TO_NUMBER('970.13')+25.5 from dual;  --995.63
    select TO_NUMBER('-$12,345.67','$999,999.99') from dual;  ---12345.67

3.CAST()函数
    语法：CAST(x AS type)
        将x转换为指定的兼容的数据库类型。

    例如：select CAST(12345.67 AS VARCHAR2(10)),
          CAST('1998-10-25' AS DATE),
          CAST(12345.678 AS NUMBER(10,2))
          FROM dual;  -- 12345.67  1998-10-25   12345.68

 

---

四、.日期函数
    语法：TO_DATE(x [, format])
        将x字符串转换为日期

    例如：select TO_DATE('2013-03-15','YYYY-MM-DD') from dual;  -- 2013-03-15

 

 

---

五、聚合函数
    亦称分组函数、集合函数。聚集函数可以对行集进行操作，并且为每组给出一个结果。
    1.聚集函数可以使用任何有效的表达式
    2.NULL值在聚集函数中将被忽略
    3.可以在聚集函数中使用DISTINCT关键字，排除重复字

聚集函数分为以下几种：
         AVG(x)：返回x的平均值
    COUNT(x)：返回统计的函数
         MAX(x)：返回x的最大值
          MIN(x)：返回x的最小值
          SUM(x)：返回x的总计值
      以下是不常用的：
         MEDIAN(x)：返回中间值
         STDDEV(x): 返回标准偏差
     VARIANCE(x)：返回x的方差
    
    max、min、avg、count、sum都忽略空值

    例如：
    select min(sal) as 最低工资 , max(sal) as 最高工资 , avg(sal) as 平均工资 , sum(sal) as 工资总和 from emp;
         
    统计员工数量：
    select count(*) from emp;

    select sum(comm) as 总金额 from emp;  //null不统计
    select count(comm)  from emp;   //null值不统计

count(*)与count(id)的区别
    1.count(*) 记录的总个数
    2.count(id) id不为空的总个数

    select count(*) as 总员工数 from emp;
    select count(comm)  from emp;   //null值不统计

    

---

六、分组

    1.GROUP BY  分组
    例如：统计EMP表中所在部门，然后统计每组的信息
        select deptno from emp group by deptno;
    统计每个部门的员工数量：
    select deptno , count(deptno)  from emp group by deptno;
    
    例如：对emp表中的职位和部门进行分组
    select deptno , job from emp group by deptno , job;

    select 字段 from 表名 where 条件 group by 字段名 having XXX order by;

    2.对分组使用聚集函数
    可以对分组后的行使用聚集函数，聚集函数会统计每组中的值，对于每组分别统计后返回一个值。

    例如：按照部门编号分组，求出每个部门的人数，平均工资
    select deptno , count(deptno) , avg(sal) from emp group by deptno;

    例如：按照职位分组，求出每个职位的最高和最低工资：
        可以多分组统计的结果，使用聚集函数进行排序
        select job , max(sal) ,min(sal) from emp group by job order by job;
    

    3.注意事项
    (1).如果查询中使用了聚合函数，被查询的列未使用聚集函数处理，那么这些列必须出现在GROUP BY子句后，
    否则，会提示ORA-00937错误。
    例如：
        select deptno , avg(sal) from emp;  //报错
    (2).不能使用聚集函数作为WHERE子句的筛选条件，
    否则，会提示ORA-00937错误。
    例如：查询平均工资大于2000的部门编号和平均工资
        select deptno from emp where avg(sal)>1000;  //报错
    正确SQL语句：
    select deptno , avg(sal) as 平均工资 from emp group by deptno having avg(sal) > 2000;
    或者(子查询) select deptno , 平均工资 from (select deptno , avg(sal) as 平均工资 from emp group by deptno) where 平均工资 > 2000;