13.2.10数据库函数
正如前面看到的连接字符串使用的concat函数,每个数据库都会在标准的SQL基础上扩展一些函数,这些函数用于进行数据处理或复杂计算,它们通过对一组数据进行计算,得到最终需要的输出结果。函数一般都会有一个或者多个输入,这些输入被称为函数的参数,函数内部会对这些参数进行判断和计算,最终只有一个值作为返回值。函数可以出现在SQL语句的各个位置,比较常用的位置是select之后和where子句中。
根据函数对多行数据的处理方式,函数被分为单行函数和多行函数,单行函数对每行输入值单独计算,每行得到一个计算结果返回给用户;多行函数对多行输入值整体计算,最后只会得到一个结果。单行函数和多行函数的示意图如图13.13所示。
SQL中的函数和Java语言中的方法有点相似,但SQL中的函数是独立的程序单元,也就是说,调用函数时无须使用任何类、对象作为调用者,而是直接执行函数。执行函数的语法如下:
1
| function_name(arg1,arg2,...)
|
多行函数也称为聚集函数、分组函数,主要用于完成一些统计功能,在大部分数据库中基本相同。但不同数据库中的单行函数差别非常大,
单行函数
MySQL中的单行函数具有如下特征。
- 单行函数的参数可以是变量、常量或数据列。单行函数可以接收多个参数,但只返回一个值。
- 单行函数会对每行单独起作用,每行(可能包含多个参数)返回一个结果。
- 使用单行函数可以改变参数的数据类型。单行函数支持嵌套使用,即内层函数的返回值是外层函数的参数。
MySQL的单行函数分类如图13.14所示。
单行函数分类
MySQL数据库的数据类型大致分为数值型、字符型和日期时间型,所以MySQL分别提供了对应的函数。转换函数主要负责完成类型转换,其他函数又大致分为如下几类。
每个数据库都包含了大量的单行函数,这些函数的用法也存在一些差异,但有一点是相同的——每个数据库都会为一些常用的计算功能提供相应的函数,这些函数的函数名可能不同,用法可能有差异,但所有数据库提供的函数库所能完成的功能大致相似,读者可以参考各数据库系统的参考文档来学习这些函数的用法。下面通过一些例子来介绍MySQL单行函数的用法。
char_length函数
选出teacher_table表中teacher_name列的字符长度
1
2
| select char_length(teacher_name)
from teacher_table;
|
查询效果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
| 1 | Yeeku |
| 2 | Leegang |
| 3 | Martine |
+------------+--------------+
3 rows in set
mysql> select char_length(teacher_name)
from teacher_table;
+---------------------------+
| char_length(teacher_name) |
+---------------------------+
| 5 |
| 7 |
| 7 |
+---------------------------+
3 rows in set
|
sin函数
计算teacher_name列的字符长度的sin值
1
2
3
4
|
select char_length(teacher_name) 长度,
sin(char_length(teacher_name)) sin
from teacher_table;
|
查询结果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
| 1 | Yeeku |
| 2 | Leegang |
| 3 | Martine |
+------------+--------------+
3 rows in set
mysql> # 长度,sin(长度)
select char_length(teacher_name) 长度,
sin(char_length(teacher_name)) sin
from teacher_table;
+------+---------------------+
| 长度 | sin |
+------+---------------------+
| 5 | -0.9589242746631385 |
| 7 | 0.6569865987187891 |
| 7 | 0.6569865987187891 |
+------+---------------------+
3 rows in set
|
计算1.57的sin值,约等于1
查询结果:
1
2
3
4
5
6
7
| mysql> select sin(1.57);
+--------------------+
| sin(1.57) |
+--------------------+
| 0.9999996829318346 |
+--------------------+
1 row in set
|
在这种用法下interval是关键字,需要一个数值,还需要一个单位
1
| SELECT DATE_ADD('1998-01-02', interval 2 MONTH);
|
运行结果:
1
2
3
4
5
6
7
| mysql> SELECT DATE_ADD('1998-01-02', interval 2 MONTH);
+------------------------------------------+
| DATE_ADD('1998-01-02', interval 2 MONTH) |
+------------------------------------------+
| 1998-03-02 |
+------------------------------------------+
1 row in set
|
获取当前日期
运行结果:
1
2
3
4
5
6
7
| mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2019-05-26 |
+------------+
1 row in set
|
获取当前时间
运行结果:
1
2
3
4
5
6
7
| mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:02:32 |
+-----------+
1 row in set
|
MD5加密函数
运行结果:
1
2
3
4
5
6
7
| mysql> select MD5('testing');
+----------------------------------+
| MD5('testing') |
+----------------------------------+
| ae2b1fca515949e5d54fb22b8ed95575 |
+----------------------------------+
1 row in set
|
处理null的函数
MySQL提供了如下几个处理nul的函数。
ifnull(表达式1,表达式2):如果表达式1为null,则返回表达式2,否则返回表达式1。nullif(表达式1,表达式2):如果表达式1和表达式2相等,则返回null,否则返回表达式1。if(表达式1,表达式2,表达式3):有点类似于?:三目运算符,如果表达式1为true,不等于不等于0,且不等于null,则返回表达式2,否则返回表达式3。isnull(表达式1):判断表达式1是否为null,如果为null则返回true,否则返回false。
ifnull函数
如果 student_name列为null,则返回’没有名字
1
2
| select ifnull(student_name,'没有名字')
from student_table;
|
查询结果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| mysql> select *
from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
| 1 | 张三 | 1 |
| 2 | 张三 | 1 |
| 3 | 李四 | 1 |
| 4 | 王五 | 2 |
| 5 | _王五 | 2 |
| 6 | NULL | 2 |
| 7 | 赵六 | 3 |
+------------+--------------+--------------+
7 rows in set
mysql> select ifnull(student_name,'没有名字')
from student_table;
+---------------------------------+
| ifnull(student_name,'没有名字') |
+---------------------------------+
| 张三 |
| 张三 |
| 李四 |
| 王五 |
| _王五 |
| 没有名字 |
| 赵六 |
+---------------------------------+
7 rows in set
|
nullif函数
如果student_name列等于’张三’,则返回null
1
2
| select nullif(student_name,'张三')
from student_table;
|
查询结果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
| 1 | 张三 | 1 |
| 2 | 张三 | 1 |
| 3 | 李四 | 1 |
| 4 | 王五 | 2 |
| 5 | _王五 | 2 |
| 6 | NULL | 2 |
| 7 | 赵六 | 3 |
+------------+--------------+--------------+
7 rows in set
mysql> select nullif(student_name,'张三')
from student_table;
+-----------------------------+
| nullif(student_name,'张三') |
+-----------------------------+
| NULL |
| NULL |
| 李四 |
| 王五 |
| _王五 |
| NULL |
| 赵六 |
+-----------------------------+
7 rows in set
|
if函数
如果student_name列为null,则返回’没有名字’,否则返回有名字
1
2
| select if(isnull(student_name),'没有名字','有名字')
from student_table;
|
查询结果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
| 1 | 张三 | 1 |
| 2 | 张三 | 1 |
| 3 | 李四 | 1 |
| 4 | 王五 | 2 |
| 5 | _王五 | 2 |
| 6 | NULL | 2 |
| 7 | 赵六 | 3 |
+------------+--------------+--------------+
7 rows in set
mysql> select if(isnull(student_name),'没有名字','有名字')
from student_table;
+----------------------------------------------+
| if(isnull(student_name),'没有名字','有名字') |
+----------------------------------------------+
| 有名字 |
| 有名字 |
| 有名字 |
| 有名字 |
| 有名字 |
| 没有名字 |
| 有名字 |
+----------------------------------------------+
7 rows in set
|
case函数
MySQL还提供了一个case函数,该函数是一个流程控制函数。case函数有两个用法,case函数第一个用法的语法格式如下:
第一种语法
1
2
3
4
5
6
| case value
when compare_value1 then result1
when compare_value2 then result2
...
else result
end
|
case函数用value和后面的compare_value1、compare_value2、…依次进行比较,如果value和指定的compare_value1相等,则返回对应的result1,否则返回else后的result。例如如下SQL语句:
1
2
3
4
5
6
| select student_name,case java_teacher
when 1 then 'Java老师'
when 2 then 'C老师'
else '其他老师'
end 老师类型
from student_table;
|
查询结果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
| 1 | 张三 | 1 |
| 2 | 张三 | 1 |
| 3 | 李四 | 1 |
| 4 | 王五 | 2 |
| 5 | _王五 | 2 |
| 6 | NULL | 2 |
| 7 | 赵六 | 3 |
+------------+--------------+--------------+
7 rows in set
mysql> select student_name,case java_teacher
when 1 then 'Java老师'
when 2 then 'C老师'
else '其他老师'
end 老师类型
from student_table;
+--------------+----------+
| student_name | 老师类型 |
+--------------+----------+
| 张三 | Java老师 |
| 张三 | Java老师 |
| 李四 | Java老师 |
| 王五 | C老师 |
| _王五 | C老师 |
| NULL | C老师 |
| 赵六 | 其他老师 |
+--------------+----------+
7 rows in set
|
case函数第二个用法的语法格式如下:
1
2
3
4
5
6
| case
when condition1 then result1
when condition2 then result2
...
else result
end
|
第二种用法
在第二个用法中,condition1、condition2都是一个返回boolean值的条件表达式,因此这种用法更加灵活。例如如下SQL语句:
1
2
3
4
5
6
7
|
select student_name,case
when student_id<=3 then '初级班'
when student_id<=6 then '中级班'
else '高级班'
end 班级类型
from student_table;
|
查询效果:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
| mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
| 1 | 张三 | 1 |
| 2 | 张三 | 1 |
| 3 | 李四 | 1 |
| 4 | 王五 | 2 |
| 5 | _王五 | 2 |
| 6 | NULL | 2 |
| 7 | 赵六 | 3 |
+------------+--------------+--------------+
7 rows in set
mysql> #id小于3的为初级班,3~6的为中级班,其他的为高级班
select student_name,case
when student_id<=3 then '初级班'
when student_id<=6 then '中级班'
else '高级班'
end 班级类型
from student_table;
+--------------+----------+
| student_name | 班级类型 |
+--------------+----------+
| 张三 | 初级班 |
| 张三 | 初级班 |
| 李四 | 初级班 |
| 王五 | 中级班 |
| _王五 | 中级班 |
| NULL | 中级班 |
| 赵六 | 高级班 |
+--------------+----------+
7 rows in set
|
小结
虽然此处介绍了一些MySQL常用函数的简单用法,但通常不推荐在Java程序中使用特定数据库的函数,因为这将导致程序代码与特定数据库耦合;如果需要把该程序移植到其他数据库系统上时,可能需要打开源程序,重新修改SQL语句。
原文链接: 13.2.10 数据库函数