DQL语句（二）-------函数和分组

6、函数

• 函数是数据库自有的，我们可以直接调用，使用函数可以计算出我们需要的数据

• MySQL 函数 大全

• 单行函数

  • 字符串函数

    • -- 长度length()，截取substr()，大小写upper(str),lower(str)，拼接concat()，替换replace()
      SELECT
      	upper( ename ),
      	lower( ename ),
      	length( ename ),
      	substr( ename, 1, 3 ),
      	substr( ename, 5, 1 ),
      	concat( ename, '`s job is ', job ),
      	REPLACE ( ename, 'T', 't' ) 
      FROM
      	emp;
      

  • 数值函数（使用一个dual虚拟表）

    • -- 绝对值abs(),正负值sign(),向上取整ceil(),向下取整floor(),x的y次幂power(x,y),e的y次幂exp(y),以x为底的y的对数log(x,y),以e为底的y的对数ln(y),x除以y的余数mod(x,y),四舍五入round(),截取后的值truncate(),x的平方根sqrt(x),三角函数
      SELECT
      	abs(- 10 ),
      	sign(- 10 ),
      	sign( 10 ),
      	ceil( 3.5 ),
      	floor( 3.5 ),
      	power( 2, 3 ),
      	exp( 3 ),
      	log( 2, 32 ),
      	ln( 5 ),
      	MOD ( 8, 3 ),
      	round( 3.6 ),
      	round( 3.4 ),
      	TRUNCATE ( 343.3,- 1 ),
      	sqrt( 16 ),
      	sin( pi()/ 6 ),
      	cos( pi()/ 3 ),
      	tan( pi()/ 4 ) 
      FROM
      DUAL
      

  • 日期函数

    • -- 获取当前系统时间now(),sysdate(),current_timestamp()都是返回日期时间，current_date()只返回日期，current_time()只返回时间
      SELECT
      	now(),
      	sysdate(),
      	CURRENT_DATE (),
      	CURRENT_TIME (),
      	CURRENT_TIMESTAMP () 
      FROM
      DUAL
      
      -- 分别获取年月日时分秒,和第几个星期
      SELECT
      	EXTRACT(
      	YEAR 
      	FROM
      	now()),
      	EXTRACT(
      	MONTH 
      	FROM
      	now()),
      	EXTRACT(
      	DAY 
      	FROM
      	now()),
      	EXTRACT(
      	HOUR 
      	FROM
      	now()),
      	EXTRACT(
      	MINUTE 
      	FROM
      	now()),
      	EXTRACT(
      	SECOND 
      	FROM
      	now()),
      	EXTRACT(
      	WEEK 
      	FROM
      	now()) 
      FROM
      DUAL
      
      
      -- 日期加减操作
      SELECT
      	NOW(),
      	ADDDATE( NOW(), 4 ) 
      FROM
      	DUAL 
      	
      -- 日期初始化
      SELECT
      	DATE_FORMAT( now(), '%Y年%m月%d日' ) 
      FROM
      DUAL
      

  • 其他函数

    • -- 空值处理 ifnull(exp1,exp2) ---------> exp1!=null?exp1:exp2
      select sal+ifnull(comm,0) from emp
      
      -- 加密算法
      -- MD5(str)为不可逆加密
      SELECT MD5('帅哥') FROM DUAL
      -- AES加密为可逆加密
      SELECT
      	AES_ENCRYPT( 'boy', '123' ),
      	AES_DECRYPT( AES_ENCRYPT( 'boy', '123' ), 123 ) 
      FROM
      DUAL
      

  • 转换函数

    • -- 日期转字符串date_format(date,expr)
      -- 字符串转日期str_to_date(expr,date)
      -- 数字转字符串---直接拼接字符串
      -- 字符串转数字
      

• 多行函数

  • 不管处理多少条数据只返回一条数据

  • 常见多行函数

    • max最大值

    • min最小值

    • avg平均值

    • sum求和

    • count求数量

    • select max(sal),min(sal),sum(sal),count(empno),avg(sal) from emp
      

  • 常见的问题

    • 字符串按ASCII编码从左到右边依次比较，先比较第一个再比较第二个······

    • 空值不会代入计算

    • sum(字符串)算出来是0，也不会报错

    • 表中的列和函数不能同时查询，如：select ename,min(sal) from emp;

      这样出来的ename总是第一条数据的ename

7、分组

​ group by 按xxx分组

​ having 分组后的数据进行过滤，就相当于分组后的where，where是行级过滤，having是组级过滤，能用where就不用having

​

-- 查询每种工作的平均薪资
select job , avg(sal) from emp group by job;
-- 查询每个部门的最高薪资和最低薪资
select max(sal) ,min(sal) from emp ;
select deptno, max(sal), min(sal) from emp group by deptno;
-- 查询每个部门的人数和每月工资总数
select deptno, count(empno) , sum(sal) from emp group by deptno;
-- 查询每个部门，每种工作的平均薪资
select deptno,job , avg(sal) from emp group by deptno,job ;
select deptno,job , avg(sal) from emp group by deptno,job order by depto ,job;
-- 查询个人姓名的平均薪资--尽量对多数据进行分组
select ename，max(sal), min(sal) from emp group by ename;
-- 查询平均薪资高于2500的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>=2500;
select deptno,avg(sal) from emp group by deptno having ename like '%A%';
-- 查询20部门的平均薪资
select deptno,avg(sal) from emp group by deptno having deptno = 20;
select deptno,avg(sal) from emp where deptno = 20 group by deptno;

-- 查询10 20部门中,并且在二月份入职员工中，每个部门中平均薪资高于150O的工作是什么，并按照部门，工作平均薪资进行排序select  from emp where deptno in (10,20) ;
select deptno ,job ,avg(sal) from emp where deptno in (10,20) group by deptno ,job having avg(sal)>1500
select deptno ,job ,avg(sal) from emp where deptno in (10,20) group by deptno ,job having avg(sal)>1500 order by deptno ,avg(sal) ;

8、关键字执行顺序

​ from---->where----->group by------>having------->select------>order by

简单select查询参考上一篇博客DQL语句（一）