数据处理函数

数据处理函数又被称为单行处理函数,单行处理函数的特点:一个输入对应一个输出

与单行处理函数相对的是:多行处理函数,多行处理函数的特点:多个输入,对应1个输出

常见的单行处理函数

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.00 sec)
upper转大写

同理转小写

substr取子串

格式:

substr(被截取的字符串,起始下标,截取的长度)

例:

mysql> select substr(ename,1,1) from emp;
+-------------------+
| substr(ename,1,1) |
+-------------------+
| S                 |
| A                 |
| W                 |
| J                 |
| M                 |
| B                 |
| C                 |
| S                 |
| K                 |
| T                 |
| A                 |
| J                 |
| F                 |
| M                 |
+-------------------+
14 rows in set (0.00 sec)

注:起始下标从1开始,没有0,也可以起别名

例:

mysql> select substr(ename,1,1) as enme from emp;

例:找出员工名字第一个字母是A的员工信息

  • 模糊查询
mysql> select ename from emp where substr(ename,1,1)='A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)
  • substr函数
mysql> select ename from emp where ename like 'A%';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)
length取长度

例:找每个人的ename的长度

mysql> select length(ename) as enamelength from emp;
+-------------+
| enamelength |
+-------------+
|           5 |
|           5 |
|           4 |
|           5 |
|           6 |
|           5 |
|           5 |
|           5 |
|           4 |
|           6 |
|           5 |
|           5 |
|           4 |
|           6 |
+-------------+
14 rows in set (0.00 sec)
trim去掉前后空格

例:找一个叫KING的人,但他数据传入进来的时候前后有空格

mysql> select * from emp where ename = trim('  KING   ');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)
round四舍五入
mysql> select 'abc' from emp; //select后面直接跟"字面量/字面值"

例:

mysql> select 'abc' from emp;
+-----+
| abc |            //这个"abc"其实是别名
+-----+
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
+-----+
14 rows in set (0.00 sec)
mysql> select 'abc' as bieming from emp;
+---------+
| bieming |
+---------+
| abc     |
| abc     |
| abc     |
mysql> select 1000 as num from emp;
+------+
| num  |
+------+
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |

注:select后面可以跟某个表的字段名(也可以等同看做变量名),也可以跟字面量/字面值(数据)

round讲解:

例:保留0位小数

mysql> select round(1234.567,0) as result from emp;
+--------+
| result |
+--------+
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
|   1235 |
+--------+
14 rows in set (0.00 sec)

例:保留1位小数

mysql> select round(1234.567,1) as result from emp;
+--------+
| result |
+--------+
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
+--------+
14 rows in set (0.00 sec)

保留1位整数

mysql> select round(1234.567,-1) as result from emp;
+--------+
| result |
+--------+
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
|   1230 |
+--------+
14 rows in set (0.00 sec)
rand()生成随机数

例:

mysql> select rand() from emp;
+---------------------+
| rand()              |
+---------------------+
|  0.5278769457041071 |
|  0.8024213926889202 |
|  0.4284761570659244 |
|  0.7351166379965065 |
|  0.3901547495184045 |
|  0.7454238643361478 |
|  0.5566551038591704 |
|  0.5470039570210539 |
| 0.06505450426140288 |
|  0.6842606809774979 |
| 0.22613992283692577 |
| 0.07791760198578015 |
|  0.7111682721517685 |
| 0.32208858553514685 |
+---------------------+
14 rows in set (0.00 sec)

生成100以内的随机数

mysql> select round(rand()*100) from emp;
+-------------------+
| round(rand()*100) |
+-------------------+
|                48 |
|                42 |
|                66 |
|                 5 |
|                27 |
|                21 |
|                22 |
|                50 |
|                82 |
|                61 |
|                57 |
|                 3 |
|                45 |
|                16 |
+-------------------+
14 rows in set (0.00 sec)
ifnull可以将null转换成一个具体值

ifnull是空处理函数,专门处理的,因为在所有的数据库当中,只要有NULL参与的数学运算,最终结果就是NULL

例:

mysql> select ename,sal + comm as salcomm from emp;
+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  |    NULL |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  |    NULL |
| MARTIN | 2650.00 |
| BLAKE  |    NULL |
| CLARK  |    NULL |
| SCOTT  |    NULL |
| KING   |    NULL |
| TURNER | 1500.00 |
| ADAMS  |    NULL |
| JAMES  |    NULL |
| FORD   |    NULL |
| MILLER |    NULL |
+--------+---------+
14 rows in set (0.00 sec)

ifnull函数格式:

ifnull(数据,当数据为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.02 sec)

case函数

这个函数完整的格式为:

case .. when .. then ..when ..then ..else ..end

就相当于Java中的if else语句

例:当员工的工作岗位是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.00 sec)
Last modification:February 14th, 2021 at 04:27 pm