13.2.10 数据库函数

发表于 2019-05-26 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.2 SQL语法 Waline：本文字数： 7.7k 阅读时长 ≈ 7 分钟

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;

查询效果:

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值

# 长度,sin(长度)
select char_length(teacher_name) 长度,
sin(char_length(teacher_name)) sin
from teacher_table;

查询结果:

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	select sin(1.57);

查询结果:

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);

运行结果:

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	select CURDATE();

运行结果:

mysql> select CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-05-26 |
+------------+
1 row in set

获取当前时间

1	select curtime();

运行结果:

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:02:32  |
+-----------+
1 row in set

MD5加密函数

1	select MD5('testing');

运行结果:

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;

查询结果:

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;

查询结果:

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;

查询结果:

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函数第一个用法的语法格式如下:

第一种语法

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语句:

select student_name,case java_teacher
when 1 then 'Java老师'
when 2 then 'C老师'
else '其他老师'
end 老师类型
from student_table;

查询结果:

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函数第二个用法的语法格式如下:

case
when condition1 then result1
when condition2 then result2
...
else result
end

第二种用法

在第二个用法中,condition1、condition2都是一个返回boolean值的条件表达式,因此这种用法更加灵活。例如如下SQL语句:

#id小于3的为初级班,3~6的为中级班,其他的为高级班
select student_name,case
when student_id<=3 then '初级班'
when student_id<=6 then '中级班'
else '高级班'
end 班级类型
from student_table;

查询效果:

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语句。