mysql常用的函数应用

一、函数应用分类：

1 字符函数 2 数学函数 3 日期函数 4 流程控制函数 5 分组函数        

用法   SELECT 函数(参数) FROM 表;

1 字符函数

1)length(str)：返字符串长度，以字节为单位

mysql> select length('abc');
+---------------+
| length('abc') |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

2)concat(s1,s2，...): 返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL

# 拼接字符串
mysql> select concat(dept_id, '-', dept_name) from departments;
+---------------------------------+
| concat(dept_id, '-', dept_name) |
+---------------------------------+
| 1-人事部                        |
| 2-财务部                        |
| 3-运维部                        |
| 4-开发部                        |
| 5-测试部                        |
| 6-市场部                        |
| 7-销售部                        |
| 8-法务部                        |
+---------------------------------+
8 rows in set (0.00 sec)

3) upper(str)和ucase(str): 将字符串中的字母全部转换成大写

mysql> select name, upper(email) from employees where name like '李%';
+-----------+----------------------+
| name      | upper(email)         |
+-----------+----------------------+
| 李玉英    | LIYUYING@TEDU.CN     |
| 李平      | LIPING@TEDU.CN       |
| 李建华    | LIJIANHUA@TARENA.COM |
| 李莹      | LIYING@TEDU.CN       |
| 李柳      | LILIU@TARENA.COM     |
| 李慧      | LIHUI@TARENA.COM     |
| 李静      | LIJING@TARENA.COM    |
| 李瑞      | LIRUI@TARENA.COM     |
+-----------+----------------------+
8 rows in set (0.00 sec)

4)lower(str)和lcase(str):将str中的字母全部转换成小写

# 转小写
mysql> select lower('HelloWorld');
+---------------------+
| lower('HelloWorld') |
+---------------------+
| helloworld          |
+---------------------+
1 row in set (0.00 sec)

5)substr(s, start, length): 从子符串s的start位置开始，取出length长度的子串，位置从1开始计算

mysql> select substr('hello world', 7);
+--------------------------+
| substr('hello world', 7) |
+--------------------------+
| world                    |
+--------------------------+
1 row in set (0.00 sec)

# 取子串，下标从7开始取出3个
mysql> select substr('hello world', 7, 3);
+-----------------------------+
| substr('hello world', 7, 3) |
+-----------------------------+
| wor                         |
+-----------------------------+
1 row in set (0.00 sec)

6)instr(str,str1)：返回str1参数，在str参数内的位置

# 子串在字符串中的位置
mysql> select instr('hello world', 'or');
+----------------------------+
| instr('hello world', 'or') |
+----------------------------+
|                          8 |
+----------------------------+
1 row in set (0.00 sec)
7)trim(s): 返回字符串s删除了两边空格之后的字符串

mysql> select trim('  hello world.  ');
+--------------------------+
| trim('  hello world.  ') |
+--------------------------+
| hello world.             |
+--------------------------+
1 row in set (0.00 sec)

2 数学函数实例

1）abs(x)：返回x的绝对值

mysql> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

2)pi(): 返回圆周率π，默认显示6位小数

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

3) mod(x,y): 返回x被y除后的余数

mysql> select mod(10, 3);
+------------+
| mod(10, 3) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

4) ceil(x)、ceiling(x): 返回不小于x的最小整数

mysql> select ceil(10.1);
+------------+
| ceil(10.1) |
+------------+
|         11 |
+------------+
1 row in set (0.00 sec)

5) floor(x): 返回不大于x的最大整数

mysql> select floor(10.9);
+-------------+
| floor(10.9) |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)

6) round(x)、round(x,y): 前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位

mysql> select round(10.6666);
+----------------+
| round(10.6666) |
+----------------+
|             11 |
+----------------+
1 row in set (0.00 sec)

3 日期和时间实例

NOW(): 返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-09 02:28:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now() + 0;
+----------------+
| now() + 0      |
+----------------+
| 20210309022848 |
+----------------+
1 row in set (0.00 sec)

YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time): YEAR(date)返回指定日期对应的年份，范围是1970到2069；QUARTER(date)返回date对应一年中的季度，范围是1到4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值

mysql> select year('20211001');
+------------------+
| year('20211001') |
+------------------+
|             2021 |
+------------------+
1 row in set (0.00 sec)

4  流程控制函数实例

IF(expr,v1,v2): 如果expr是TRUE则返回v1，否则返回v2

mysql> select if(3>0, 'yes', 'no');
+----------------------+
| if(3>0, 'yes', 'no') |
+----------------------+
| yes                  |
+----------------------+
1 row in set (0.00 sec)

IFNULL(v1,v2): 如果v1不为NULL，则返回v1，否则返回v2

mysql> select dept_id, dept_name, ifnull(dept_name, '未设置') from departments;
+---------+-----------+--------------------------------+
| dept_id | dept_name | ifnull(dept_name, '未设置')    |
+---------+-----------+--------------------------------+
|       1 | 人事部    | 人事部                         |
|       2 | 财务部    | 财务部                         |
|       3 | 运维部    | 运维部                         |
|       4 | 开发部    | 开发部                         |
|       5 | 测试部    | 测试部                         |
|       6 | 市场部    | 市场部                         |
|       7 | 销售部    | 销售部                         |
|       8 | 法务部    | 法务部                         |
+---------+-----------+--------------------------------+
8 rows in set (0.00 sec)

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END: 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

 mysql> select dept_id, dept_name,

-> case dept_name
    -> when '运维部' then '技术部门'
    -> when '开发部' then '技术部门'
    -> when '测试部' then '技术部门'
    -> when null then '未设置'
    -> else '非技术部门'
    -> end as '部门类型'
    -> from departments;
+---------+-----------+-----------------+
| dept_id | dept_name | 部门类型        |
+---------+-----------+-----------------+
|       1 | 人事部    | 非技术部门      |
|       2 | 财务部    | 非技术部门      |
|       3 | 运维部    | 技术部门        |
|       4 | 开发部    | 技术部门        |
|       5 | 测试部    | 技术部门        |
|       6 | 市场部    | 非技术部门      |
|       7 | 销售部    | 非技术部门      |
|       8 | 法务部    | 非技术部门      |
|       9 | NULL      | 非技术部门      |
+---------+-----------+-----------------+
9 rows in set (0.00 sec)

5 分组函数

1） sum() ：求和

mysql> select employee_id, sum(basic+bonus) from salary where employee_id=10 and year(date)=2018;
+-------------+------------------+
| employee_id | sum(basic+bonus) |
+-------------+------------------+
|          10 |           116389 |
+-------------+------------------+
1 row in set (0.00 sec)

2）avg() ：求平均值   

mysql> select employee_id, avg(basic+bonus) from salary where employee_id=10 and year(date)=2018;
+-------------+------------------+
| employee_id | avg(basic+bonus) |
+-------------+------------------+
|          10 |       29097.2500 |
+-------------+------------------+
1 row in set (0.00 sec)

3）max() ：求最大值

mysql> select employee_id, max(basic+bonus) from salary where employee_id=10 and year(date)=2018;
+-------------+------------------+
| employee_id | max(basic+bonus) |
+-------------+------------------+
|          10 |            31837 |
+-------------+------------------+
1 row in set (0.00 sec)

4）min() ：求最小值

mysql> select employee_id, min(basic+bonus) from salary where employee_id=10 and year(date)=2018;
+-------------+------------------+
| employee_id | min(basic+bonus) |
+-------------+------------------+
|          10 |            24837 |
+-------------+------------------+
1 row in set (0.00 sec)

5） count() ：计算个数

mysql> select count(*) from departments;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)