3-单行命令.md

一、简要介绍

​ 单行处理，多行输出

特点：一行一行处理，每一行输出一个结果。

注意注意注意
单行函数中有 null 参与的数字运算最终值 总是null

1.+ - * / 四则运算

---

2.多行命令中没有————-

​

二、常用

1. lower (字段) 转小写

   mysql> select lower(job) from emp;
   +------------+
   | lower(job) |
   +------------+
   | clerk      |
   | salesman   |
   | salesman   |
   | manager    |
   | salesman   |
   | manager    |
   | manager    |
   | analyst    |
   | president  |
   | salesman   |
   | clerk      |
   | clerk      |
   | analyst    |
   | clerk      |
   +------------+
   14 rows in set (0.01 sec)

2. upper (字段) 转大小

3. substr(字段,起始下表,截取长度) 取字符 ==在sql的字符串中，字符串的下表是从1开始的==

   mysql> select substr(job,1,3) from emp;
   ## CLE
   +-----------------+
   | substr(job,1,3) |
   +-----------------+
   | CLE -->clerk    |
   | SAL             |
   | SAL             |
   | MAN             |
   | SAL             |
   | MAN             |
   | MAN             |
   | ANA             |
   | PRE             |
   | SAL             |
   | CLE             |
   | CLE             |
   | ANA             |
   | CLE             |
   +-----------------+
   14 rows in set (0.00 sec)

4. concat(a,b) 将 b 字符串拼接到 a 的尾部。

   mysql> select concat(job,mgr) from emp;
   +-----------------+
   | concat(job,mgr) |
   +-----------------+
   | CLERK7902       |
   | SALESMAN7698    |
   | SALESMAN7698    |
   | MANAGER7839     |
   | SALESMAN7698    |
   | MANAGER7839     |
   | MANAGER7839     |
   | ANALYST7566     |
   | NULL            |
   | SALESMAN7698    |
   | CLERK7788       |
   | CLERK7698       |
   | ANALYST7566     |
   | CLERK7782       |
   +-----------------+
   14 rows in set (0.00 sec)

额外的小知识

1. length(字段) 获取字段的长度

2. round(数字,保留的小数位),保留位为0则只保留整数位，且保留的值会进行四舍五入

   mysql> select round(0.55,0) from emp;
   +---------------+
   | round(0.55,0) |
   +---------------+
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   |             1 |
   +---------------+
   14 rows in set (0.00 sec)

3. rand() 生成0到1的随机数

   mysql> select rand() from emp;
   +----------------------+
   | rand()               |
   +----------------------+
   |   0.6586841099510753 |
   |   0.4807403501875143 |
   |   0.4276494518179907 |
   |   0.6960262392610556 |
   |  0.19718287158495082 |
   |   0.8978356708752323 |
   |   0.8976297703549543 |
   |   0.7946418698827195 |
   |   0.2803200690823794 |
   | 0.017674766497383608 |
   |  0.24741365597342482 |
   |  0.18404401110861823 |
   |  0.17797911835646174 |
   |    0.337763589190099 |
   +----------------------+
   14 rows in set (0.00 sec)

4. 四则运算

   1. mysql> select sal+comm from emp;
      +----------+
      | sal+comm |
      +----------+
      |     NULL |
      |  1900.00 |
      |  1750.00 |
      |     NULL |
      |  2650.00 |
      |     NULL |
      |     NULL |
      |     NULL |
      |     NULL |
      |  1500.00 |
      |     NULL |
      |     NULL |
      |     NULL |
      |     NULL |
      +----------+
      14 rows in set (0.00 sec)

Lower 转换小写

upper 转换大写

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

length() 取长度

trim 去空格

str_to_date 将字符串转换成日期

date_format 格式化日期

format 设置千分位

round 四舍五入

rand() 生成随机数

Ifnull 可以将 null 转换成一个具体值