当前位置：首页 > news >正文

零基础学做网页/seo排名优化公司价格

news 2025/7/1 9:41:52

零基础学做网页,seo排名优化公司价格,怎么在境外做网站,政府网站数字化建设方案六、数据处理函数 6.1 什么是数据处理函数数据处理函数又被称为单行处理函数。特点：一个输入对应一个输出。------> 多行处理函数：多个输入，对应一个输出。 6.2 常见的单行处理函数 6.2.1 lower转换小写 mysql> select lower(e…

六、数据处理函数

6.1 什么是数据处理函数

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

特点：一个输入对应一个输出。------> 多行处理函数：多个输入，对应一个输出。

6.2 常见的单行处理函数

6.2.1 lower转换小写

mysql> select lower(ename) as ename from emp;
+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+
14 rows in set (0.00 sec)
# 14个输入，最后还是14个输出。

6.2.2 upper转换大写

mysql> select upper(ename) as name from emp;

6.2.3 substr取子串

substr(被截取的字符串，起始下标，截取的长度)
# 注意：起始下标从1开始，没有0

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

# 法1
mysql> select ename from emp where ename like 'A%';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)mysql> select ename from emp where substr(ename, 1, 1) = 'A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)

6.2.4 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.00 sec)

6.2.5 length取长度

mysql> select length(ename) 'ename length' from emp;
+--------------+
| ename length |
+--------------+
|            5 |
|            5 |
|            4 |
|            5 |
|            6 |
|            5 |
|            5 |
|            5 |
|            4 |
|            6 |
|            5 |
|            5 |
|            4 |
|            6 |
+--------------+
14 rows in set (0.00 sec)

6.2.6 trim去空格

mysql> select * from emp where ename = ' KING';
Empty set (0.00 sec)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)

6.2.7 str_to_data将字符串转换成日期

6.2.8 data_format格式化日期

6.2.9 format设置千分位

6.2.10 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.00 sec)

6.2.11 round四舍五入

select 后面可以跟某个表的字段名（可以等同看作变量名），也可以跟字面量/字面值（数据）。

mysql> select ename from emp;
+--------+
| ename  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.00 sec)mysql> select 'abc' as bieming from emp;	# 如果写成abc会报错，因为数据库会把他看作一个字段名，从emp表中找abc字段
+---------+
| bieming |
+---------+
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
| abc     |
+---------+
14 rows in set (0.00 sec)mysql> select 1000 as num from emp;
+------+
| num  |
+------+
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
+------+
14 rows in set (0.00 sec)# round四舍五入
mysql> select round(1236.567, 0) as result from emp;	# 保留整位数
+--------+
| result |
+--------+
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
|   1237 |
+--------+
14 rows in set (0.00 sec)mysql> select round(1236.567, 2) as result from emp;	# 保留两位小数
+---------+
| result  |
+---------+
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
| 1236.57 |
+---------+
14 rows in set (0.00 sec)mysql> select round(1236.567, -1) as result from emp;	# 保留到十位
+--------+
| result |
+--------+
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
|   1240 |
+--------+
14 rows in set (0.00 sec)

6.2.12 rand()生成随机数

mysql> select round(rand()*100, 0) from emp;	# 生成100以内的随机数
+----------------------+
| round(rand()*100, 0) |
+----------------------+
|                  100 |
|                   18 |
|                   92 |
|                    4 |
|                   45 |
|                   12 |
|                   24 |
|                   87 |
|                   60 |
|                   39 |
|                   15 |
|                   57 |
|                   43 |
|                   42 |
+----------------------+
14 rows in set (0.00 sec)

6.2.13 ifnull 将null转换成一个具体值

ifnull是空处理函数，专门处理空的。
在所有数据库中，只要有NULL参与的数学运算，最终结果就是NULL。为了避免这个现象，需要使用ifnull函数。ifnull函数用法：ifnull(数据, 被当作哪个值)

计算每个员工的年薪：年薪 = (月薪 + 月补助) * 12

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.01 sec)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.00 sec)

补助为NULL的时候，将补助当做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.00 sec)