小工具知识网

当前位置: 首页 > 行业知识 > mysql中的函数

行业知识

mysql中的函数

2026-11-18 14:50:34 来源:互联网转载

函数

数据处理函数/单行处理函数

���据处理函数又被称为单行处理函数

单行处理函数的特点:一个输入对应一个输出。

和多行处理函数相对的是:多行处理函数。

多行处理函数的特点:多个输入对应一个输出。

lower转小写
mysql> select lower(ename) from emp;+--------------+| lower(ename) |+--------------+| smith        || allen        || ward         || jones        || martin       || blake        || clark        || scott        || king         || turner       || adams        || james        || ford         || miller       |+--------------+14 rows in set (0.09 sec)mysql> select lower(ename) as name from emp;+--------+| name   |+--------+| smith  || allen  || ward   || jones  || martin || blake  || clark  || scott  || king   || turner || adams  || james  || ford   || miller |+--------+14 rows in set (0.04 sec)
substr截取字符串
mysql> select substr(ename,1,1) as ename from emp;+-------+| ename |+-------+| S     || A     || W     || J     || M     || B     || C     || S     || K     || T     || A     || J     || F     || M     |+-------+14 rows in set (0.06 sec)
concat拼接字符串
mysql> select concat(empno,ename) from emp;+---------------------+| concat(empno,ename) |+---------------------+| 7369SMITH           || 7499ALLEN           || 7521WARD            || 7566JONES           || 7654MARTIN          || 7698BLAKE           || 7782CLARK           || 7788SCOTT           || 7839KING            || 7844TURNER          || 7876ADAMS           || 7900JAMES           || 7902FORD            || 7934MILLER          |+---------------------+14 rows in set (0.03 sec)
首字母转小写
mysql> select concat(lower(substr(ename,1,1)),substr(ename,2,length(ename)-1)) as result from emp;+--------+| result |+--------+| sMITH  || aLLEN  || wARD   || jONES  || mARTIN || bLAKE  || cLARK  || sCOTT  || kING   || tURNER || aDAMS  || jAMES  || fORD   || mILLER |+--------+14 rows in set (0.05 sec)
length 查询长度
mysql> select length(ename) from emp;+---------------+| length(ename) |+---------------+|             5 ||             5 ||             4 ||             5 ||             6 ||             5 ||             5 ||             5 ||             4 ||             6 ||             5 ||             5 ||             4 ||             6 |+---------------+14 rows in set (0.05 sec)
去除空格
mysql> select * from emp where ename = trim('  SMITH');+-------+-------+-------+------+------------+--------+------+--------+| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL    | COMM | DEPTNO |+-------+-------+-------+------+------------+--------+------+--------+|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL |     20 |+-------+-------+-------+------+------------+--------+------+--------+1 row in set (0.50 sec)
ifnull函数

计算每个员工的年薪

mysql> select ename,(sal+comm)*12 as yearsal from emp;+--------+----------+| ename  | yearsal  |+--------+----------+| SMITH  | NULL     || ALLEN  | 22800.00 || WARD   | 21000.00 || JONES  | NULL     || MARTIN | 31800.00 || BLAKE  | NULL     || CLARK  | NULL     || SCOTT  | NULL     || KING   | NULL     || TURNER | 18000.00 || ADAMS  | NULL     || JAMES  | NULL     || FORD   | NULL     || MILLER | NULL     |+--------+----------+14 rows in set (0.05 sec)

因为comm存在null,而null参与的所有运算结果为null。

使用ifnull

如果津贴comm为null,将comm当做0处理

mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;+--------+----------+| ename  | yearsal  |+--------+----------+| SMITH  |  9600.00 || ALLEN  | 22800.00 || WARD   | 21000.00 || JONES  | 35700.00 || MARTIN | 31800.00 || BLAKE  | 34200.00 || CLARK  | 29400.00 || SCOTT  | 36000.00 || KING   | 60000.00 || TURNER | 18000.00 || ADAMS  | 13200.00 || JAMES  | 11400.00 || FORD   | 36000.00 || MILLER | 15600.00 |+--------+----------+14 rows in set (0.05 sec)
case…when…then…when…then…else…end

当员工的工作岗位是MANAGER的时候,工资上调10%,当工作岗位是SALESMAN的时候,工资上调50%,其它正常。

mysql> select ename,job,sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;+--------+-----------+---------+---------+| ename  | job       | oldsal  | newsal  |+--------+-----------+---------+---------+| SMITH  | CLERK     |  800.00 |  800.00 || ALLEN  | SALESMAN  | 1600.00 | 2400.00 || WARD   | SALESMAN  | 1250.00 | 1875.00 || JONES  | MANAGER   | 2975.00 | 3272.50 || MARTIN | SALESMAN  | 1250.00 | 1875.00 || BLAKE  | MANAGER   | 2850.00 | 3135.00 || CLARK  | MANAGER   | 2450.00 | 2695.00 || SCOTT  | ANALYST   | 3000.00 | 3000.00 || KING   | PRESIDENT | 5000.00 | 5000.00 || TURNER | SALESMAN  | 1500.00 | 2250.00 || ADAMS  | CLERK     | 1100.00 | 1100.00 || JAMES  | CLERK     |  950.00 |  950.00 || FORD   | ANALYST   | 3000.00 | 3000.00 || MILLER | CLERK     | 1300.00 | 1300.00 |+--------+-----------+---------+---------+14 rows in set (0.05 sec)

分组函数(多行处理函数)

多行处理函数的特点:输入多行,最终输出一行

​ 五个:

  1. count 计数

  2. sum 求和

  3. avg 平均值

  4. max 最大值

  5. min 最小值

    注意:

    分组函数在使用的时候必须进行分组,然后使用。

    如果没有对数据进行分组,整张表默认为一组。

找出最高工资
mysql> select max(sal) from emp;+----------+| max(sal) |+----------+|  5000.00 |+----------+1 row in set (0.19 sec)
找出最低工资
mysql> select min(sal) from emp;+----------+| min(sal) |+----------+|   800.00 |+----------+1 row in set (0.11 sec)
计算工资和
mysql> select sum(sal) from emp;+----------+| sum(sal) |+----------+| 29025.00 |+----------+1 row in set (0.12 sec)
计算平均工资
mysql> select avg(sal) from emp;+-------------+| avg(sal)    |+-------------+| 2073.214286 |+-------------+1 row in set (0.02 sec)
计算员工数量总和
mysql> select count(ename) from emp;+--------------+| count(ename) |+--------------+|           14 |+--------------+1 row in set (0.02 sec)

注意:分组函数自动忽略null,你不需要提前对null进行处理(count也会忽略null,不会对null计数)

count(*) 和count(具体字段)的区别
mysql> select count(*) from emp;+----------+| count(*) |+----------+|       14 |+----------+1 row in set (0.29 sec)
mysql> select count(comm) from emp;+-------------+| count(comm) |+-------------+|           4 |+-------------+1 row in set (0.02 sec)

count(具体字段):表示统计该字段下所有不为null的元素的总和。

count(*):统计表当中的总行数。(只要有一行数据count则++)

分组函数组合使用
mysql> select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;+----------+----------+----------+-------------+----------+| sum(sal) | min(sal) | max(sal) | avg(sal)    | count(*) |+----------+----------+----------+-------------+----------+| 29025.00 |   800.00 |  5000.00 | 2073.214286 |       14 |+----------+----------+----------+-------------+----------+1 row in set (0.02 sec)

分组查询(非常重要)

什么是分组查询

在实际应用中,可能有这样的需求,需要先进行分组,然后对每一组的数据进行操作。

这个时候我们需要使用分组查询,怎么进行分组查询呢?

select… from… group by…;

select…

from…

where…

group by…

order by…

上面语句关键字执行顺序

  1. from

  2. where

  3. group by

  4. select

  5. order by

    分组函数必须在分组后面执行

    mysql> select ename,sal from emp where sal>min(sal);1111 - Invalid use of group function

    该语句中先执行where语句而group by在where后面执行,所以错误。

计算每个工作岗位的工资总和
mysql> select job,sum(sal) from emp group by job;+-----------+----------+| job       | sum(sal) |+-----------+----------+| CLERK     |  4150.00 || SALESMAN  |  5600.00 || MANAGER   |  8275.00 || ANALYST   |  6000.00 || PRESIDENT |  5000.00 |+-----------+----------+5 rows in set (0.02 sec)
计算每个工作岗位的平均薪资
mysql> select job,avg(sal) from emp group by job;+-----------+-------------+| job       | avg(sal)    |+-----------+-------------+| CLERK     | 1037.500000 || SALESMAN  | 1400.000000 || MANAGER   | 2758.333333 || ANALYST   | 3000.000000 || PRESIDENT | 5000.000000 |+-----------+-------------+5 rows in set (0.06 sec)
计算每个工作岗位的最高薪资
mysql> select job,max(sal) from emp group by job;+-----------+----------+| job       | max(sal) |+-----------+----------+| CLERK     |  1300.00 || SALESMAN  |  1600.00 || MANAGER   |  2975.00 || ANALYST   |  3000.00 || PRESIDENT |  5000.00 |+-----------+----------+5 rows in set (0.14 sec)
找出每个部门,不同工作岗位的最高工资总和(两个字段联合分组)
mysql> select deptno,job,max(sal) from emp group by deptno,job order by deptno;+--------+-----------+----------+| deptno | job       | max(sal) |+--------+-----------+----------+|     10 | CLERK     |  1300.00 ||     10 | MANAGER   |  2450.00 ||     10 | PRESIDENT |  5000.00 ||     20 | ANALYST   |  3000.00 ||     20 | CLERK     |  1100.00 ||     20 | MANAGER   |  2975.00 ||     30 | CLERK     |   950.00 ||     30 | MANAGER   |  2850.00 ||     30 | SALESMAN  |  1600.00 |+--------+-----------+----------+9 rows in set (0.03 sec)
使用having可以对分完组的数据进一步进行过滤

having不能单独使用,必须和group by 联合使用

找出每个部门最高工资,要求显示最高工资大于3000的
//找出每个部门的最高工资mysql> select deptno,max(sal) from emp group by deptno; +--------+----------+| deptno | max(sal) |+--------+----------+|     20 |  3000.00 ||     30 |  2850.00 ||     10 |  5000.00 |+--------+----------+3 rows in set (0.03 sec)//显示最高工资大于3000mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000; +--------+----------+| deptno | max(sal) |+--------+----------+|     10 |  5000.00 |+--------+----------+1 row in set (0.13 sec)//以上sql语句效率低,可以先找出大于3000的然后进行分组。mysql> select deptno,max(sal) from emp where sal>3000 group by deptno; +--------+----------+| deptno | max(sal) |+--------+----------+|     10 |  5000.00 |+--------+----------+1 row in set (0.11 sec)

优化策略:where和having,优先选择where,where实在完成不了的,再选择having

where完成不了的

找出每个部门平均工资,要求显示平均薪资高于2500的

mysql> select deptno,avg(sal) from emp group by deptno; +--------+-------------+| deptno | avg(sal)    |+--------+-------------+|     20 | 2175.000000 ||     30 | 1566.666667 ||     10 | 2916.666667 |+--------+-------------+3 rows in set (0.02 sec)mysql> select deptno,avg(sal) from emp  group by deptno having  avg(sal)>2500; +--------+-------------+| deptno | avg(sal)    |+--------+-------------+|     10 | 2916.666667 |+--------+-------------+1 row in set (0.03 sec)

where完成不了的

找出每个部门平均工资,要求显示平均薪资高于2500的

mysql> select deptno,avg(sal) from emp group by deptno; +--------+-------------+| deptno | avg(sal)    |+--------+-------------+|     20 | 2175.000000 ||     30 | 1566.666667 ||     10 | 2916.666667 |+--------+-------------+3 rows in set (0.02 sec)mysql> select deptno,avg(sal) from emp  group by deptno having  avg(sal)>2500; +--------+-------------+| deptno | avg(sal)    |+--------+-------------+|     10 | 2916.666667 |+--------+-------------+1 row in set (0.03 sec)

上一篇:主机压力测试没问题了怎么回事

下一篇:揭秘ASP源码破解,法律风险与犯罪真相探究