sql--DQL_函数

2019独角兽企业重金招聘Python工程师标准>>>

一、函数的分类：

1、单行函数：输入一行数据，每一行都会返回一个结果。

  a、字符函数 ：输入的是字符，返回的也是字符 常见的函数（upper 、lower、initcap首字母大写、concat、length(按字符来统计)、lengthb（按字节的方式来统计）、lengthc（按照的是unique码统计）、substr、instr、trim、ltrim、rtrim、replace

    b、数值函数：abs, mod, trunc, round, ceil ,floor

    c、日期函数  ：输入的是一个日期返回的是日期或数值

                  sysdate ， months_between (距某个日期相隔了几个月)，add_months , next_day, last_day ,trunc

    d、转换函数  :to_char , to_number, to_date

    e、通用函数  :nvl , nvl2 ,nullif

    f、分支函数  : decode() ,case...when...then

    g、正则表达式函数  :regexp_substr,regexp_instr,regexp_like

2、多行函数/聚合函数/分组函数：输入多行，返回的结果为一行

          count（），sum（），avg（）平均值，max（），min（），group by  分组， having 分组或筛选，rollup滚动函数，cube交叉分组

 

二、单行函数例子：

1、字符函数例子

>>> select upper('Aerrr') from dual; //dual表是虚拟表用来做测试的 upper 将字符串中的所有值全返回为大写

>>>select lower('AAAAA') from dual;//将字符串中的所有值全返回为小写

>>>select lowe(dename) from dept;// 将返回的结果全部以小写方式显示

>>>select initcap(dname) from emp;// 将返回的结果首字母大写

>>>select initcap('eat  tea') from dual;//结果是Eat Tea(字符串,m,n)  from 表名；

//substr 字符串的截取，m表示从哪个位置开始，n表示截  取多少个字符，m和n都是number型。m为正表示从左向右截取，m若为负表示从右向左数然后，

>>>>>>select concat(dname,sal ) from emp;//将dname和sal连接显示，只能显示两个，如要三个需要函数的嵌套

>>>select length('ddfggjijg') from dual; //统计字符串的长度

>>>select length（dname）,lengthb(dname),lengthc(dname) from dept;//英文环境下统计出来的都是一样的，中文环境下结果会不一样。在项目中用来判断是否有中文

 

$export NLS_LANG='simplified chinese_china.al32utf8'  //导入中文

 

>>>select substr(字符串,m,n)  from 表名； //substr 字符串的截取，m表示从哪个位置开始，n表示截  取多少个字符，m和n都是number型。m为正表示从左向右截取，m若为负表示从右向左数然后，向左数n个截取 。 某些时候，可以用来替代like

>>>select ename,instr(ename,'s',1) from emp;  //显示某个字符在字符串的哪个位置,默认从第一个位置开始找。

>>>select ename instr(dname,'S',m,n) from emp； //m表示重哪个字符开始找，n表示找第几次出现的

 

trim (‘s’ from 字符串)   截断函数，默认是both是前端和末尾，只前端|后端用leading|trailing 

SQL> select trim('a' from 'abbsabbaaa') from dual;   //截取了字符串的开头和结尾的a

TRIM('
------
bbsabb

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

TRIM('
------
bbsabb

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

TRIM('
------
bbsabb

 

SQL> select trim(leading 'a' from 'abbsabbaaa') from dual;  //只截取字符串左侧即开头

TRIM(LEAD
---------
bbsabbaaa

SQL> select trim(trailing 'a' from 'abbsabbaaa') from dual;  //只截取字符串右侧即结尾

TRIM(TR
-------
abbsabb

 

>>>ltrim (ename,'a')   //从左开始找，直到找到没有a的。只能在11g中使用

>>>rtrim(ename,‘abc’)  //从右开始找

>>>lpad（depno ,m ,c ）  //左填充函数,将字符从左开始填充，填充够多少位 ，实现右对齐。m表示字符填充完后字符的长度，c表示用什么字符去填充，默认使用空格

>>>rpad //

>>>select replace(‘ssskeinghasdf’,'s','a')   //将字符串中的s替换成a

 

2、数值函数例子

>>>select ads(3),abs(-4) from dual;//取绝对值

>>>select round(234.507,1) from dual;//四舍五入，后面的数字表示保留多少为小数,可为负数

>>>select trunc（-234.567,2）from dual ; //取整函数，2的位置也可为负数

>>>select ceil(234.12),ceil(-234.12) from dual;//向上取整

>>>select floor(234.54),floor(-234.54) from dual;//向下取整

>>>select mod(3,5) from dual;  // 取余，结果是3

3、日期函数

sysdate : dd-mm-yy   //系统时间

系统时间的存储方式:

年月日： yyyy-mm-dd hh24:mm:ss:ssss

               yyyy/mm/dd/ hh24:mi:ss

               yyyymmddhh24:mi:ss

年  ： yyyy/yy/rr

月    :  mon/mm

天    : dd/dy

 

SQL> select current_date from dual;

CURRENT_D
---------
22-NOV-16

SQL> select sysdate from dual;

SYSDATE
---------
22-NOV-16

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; //修改系统的时间格式，只在本次会话中生效

Session altered.

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2016-11-22 17:25:59

SQL> select sysdate from dual;

SYSDATE
-------------------
2016-11-22 17:26:03

SQL> 

>>>select  sysdate from dual; //当前系统时间

>>>select current_date from dual; //当前时间

 

moths_between(x,y)  //x的时间比你的y的时间超前则会得到一个负数

>>>select hiredate,month_between(sysdate,hiredate) from emp; //表示入职了多少个月

 

add_months(x,n) //x表示当前时间，n为正数，表示在当前时间向后退n个月，负数表示向前推n个月

>>>select hiredate,add_months(sysdate,3) from emp; //入职后的三个月，即转正时间

 

next_day(sysdate,星期几)  //在当前时间下的下一个星期几

>>>select next_day(sysdate,5| 'friday' |'星期五') from dual;//英文环境用英文，中文环境只能用中文

 

last_day()  //一个月的最后一天

>>>select last_day(sysdate ) from dual;

 

trunc()  //第一天

>>>select trunc(sysdate,'yyyy|mm|dd')  from dual;//表示当年|月|周的第一天

4、转化函数

a、to_char()  //转化为字符型

日期转为字符型

to_char(date,'yy')

to_char(date,'yyyy')

to_char(date,'mm')

to_char(date,'dd')

to_char(date,'yyyy-mm-dd')

to_char(date,'yyyy/mm/dd')

to_char(date,‘yyyymmdd')

>>>select ename,hiredate from emp where to_char(hiredate,'yyyy')='1982' ;//将日期型转化为字符型

>>>select ename,hiredate from emp where to_char(hiredate,'yyyy-mm-dd')>'1982-01-01' ;//前后两个的格式一定要一致

数字转化为字符：

>>>select * from dept where deptno='10';

select to_char(101,'xxxx') from dual; //xxxx是语法占位无意义，时进制转化出来为十六进制

b、字符转化为数字 to_number

>>>select to_number('afe','xxxx') from dual ;//十六进制转化为十进制

 

日期不能直接转为数字，我们可以先转为字符，再转为数字

to_number ---date-(to_char ---to_number)

c、to_date  //转化为日期格式

数字转化为日期

to_date(to_char(num))

5、通用函数

nvl(x,y) //如果x的值为空，则为他填充y

nvl(x,y,z)  //如果x的值为空，则填充y值，不为空则为他赋值z

nullif(x,y)  //如果x与y一致，则返回空，不一致则返回第一个值

6、分支语句：

if  then  else

decode（）  case...when..then

decode(deptno,x,y,z)  //如果x的值等于字符串，则返回y，否则返回z

10---sal+10%

20---sal+20%

30---sal+30%

>>>select ename,deptno,sal,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1,3,sal) as  dsal  from emp;

case<>when<>then

else

end case

7 、正则表达式函数

regexp_like----like

regexp_linstr----linstr

regexp_substr------substr  //截取

regexp_replace----replace

regxp_count-----count  //10g开始的参数

^ /$ /[0-9]/[a-z]/*/./问号

regexp_like(源字符串,'正则表达式'，‘i|c’)   //i表示不区分大小写，c表示要区分大小写。默认不区分。

>>>select ename from emp where regexp_like(ename,'^s',‘i’);  //以S开头的

regexp_instr(源字符串,‘正则表达式’，n,m) //n表示从第几个字符开始，m表示第几次出现的。

>>>select ename from emp where regexp_instr(ename,'A|S',1)>0;  
//返回包含有S的员工
>>>select ename ,regexp_instr(ename,'A|S',1) from emp;
//

regexp_substr（）

>>>select ename,substr(ename,1,2) from emp; 
//从第1个开始，截取第二次出现的
>>>select ename,regexp_substr(ename,'*B*',1) from emp;
//截取包含有B的
>>>select ename,regexp_substr(ename,[A|B|S]) from emp;
//截取包含有ABS的，默认从第一个开始
>>>select ename,regexp_substr(ename,‘N$’) from emp;
//截取以N结尾的

regexp_replace

regxp_count

三、多行函数/聚合函数/分组函数

除了count会处理空值，其他都不会处理空值

count(  ) *

group by  //分组

having  //分组后只能用having过滤，不能用where去过滤

rollup(n)--//滚动分组，n表示列数，执行n+1 次分组

          group by n

           group by 0

rollup(a,b)--//需要滚动3次

           group by a，b

            group by a

             group by 0

cube（n） 2^n  //交叉滚动分组  多用来做报表

            group by 0

            group by n

cube（a，b）

            group by 0

            group by a

             group by b

             group by a,b

 

>>>select(*) from emp;
///计数
>>>select sum(sal) from emp;
//求和
>>>select avg(sal) from emp;
//求平均
>>>select max(sal)，min(sal) from emp;
//求最大最小值
>>>select deptno,max(sal) from emp group by deptno;
//每个部门最高的薪水
>>>select max(sal) from emp group by job;
//每个职位最高薪水
>>>select deptno,max(sal) from emp group by deptno having max(sal)>3000;
>>>select deptno,max(sal) from emp where deptno<>10 group by deptno having max(sal)>=3000;
//除了10号部门，哪个部门的薪水大于3000的

SQL> select deptno,avg(sal) from emp group by deptno
  2  union all
  3  select null,avg(sal) from emp;

    DEPTNO   AVG(SAL)
---------- ----------
	30 1566.66667
	20	 2175
	10 2916.66667
	   2073.21429

SQL> select deptno, avg(sal) from emp group by rollup(deptno);

    DEPTNO   AVG(SAL)
---------- ----------
	10 2916.66667
	20	 2175
	30 1566.66667
	   2073.21429

// 联合查询和滚动查询，两条语句查询出来的结果是一样的，但是rollup只需一条

SQL> select deptno ,avg(sal) from emp group by deptno;

    DEPTNO   AVG(SAL)
---------- ----------
	30 1566.66667
	20	 2175
	10 2916.66667

//没有总的平均值

 

某个部门下的某个职位的平均薪水 group by

SQL> select sal,deptno from emp where job='CLERK';

       SAL     DEPTNO
---------- ----------
       800	   20
      1100	   20
       950	   30
      1300	   10

SQL> select deptno,job,avg(sal) from emp group by deptno,job;

    DEPTNO JOB	       AVG(SAL)
---------- --------- ----------
	20 CLERK	    950   //20这个部门下的CLERK这个职位额的平均值
	30 SALESMAN	   1400
	20 MANAGER	   2975
	30 CLERK	    950
	10 PRESIDENT	   5000
	30 MANAGER	   2850
	10 CLERK	   1300
	10 MANAGER	   2450
	20 ANALYST	   3000

9 rows selected.

 

cube 一个条件的时候，和两个条件的时候

SQL> select deptno ,sum(sal) from emp group by cube(deptno);

    DEPTNO   SUM(SAL)
---------- ----------
		29025
	10	 8750
	20	10875
	30	 9400
SQL> select deptno ,job,avg(sal) from emp group by cube(deptno,job);  //相当于会有2的n次方个组合

    DEPTNO JOB	       AVG(SAL)
---------- --------- ----------
		     2073.21429   //总的平均值    
	   CLERK	 1037.5   //根据job分的平均值
	   ANALYST	   3000
	   MANAGER   2758.33333
	   SALESMAN	   1400
	   PRESIDENT	   5000
	10	     2916.66667  //根据deptno的平均值   
	10 CLERK	   1300  //在deptno下，每一个job的平均值
	10 MANAGER	   2450
	10 PRESIDENT	   5000
	20		   2175
	20 CLERK	    950
	20 ANALYST	   3000
	20 MANAGER	   2975
	30	     1566.66667
	30 CLERK	    950
	30 MANAGER	   2850
	30 SALESMAN	   1400

18 rows selected.

 

grouping sets //合并分组

grouping

>>>select deptno,job ,avg (sal) from emp group by grouping sets(deptno,job); 
//把两个结果合并到一起
>>>select deptno,job ,avg (sal) grouping(deptno) from emp group by grouping sets(deptno,job);
//grouping 用来验证列是否参与分组，若参与分组值为0，没有参与分组值为1.只能跟一个列

 

转载于:https://my.oschina.net/liubaizi/blog/792428