10 数据库函数

正如前面看到的连接字符串使用的concat函数，每个数据库都会在标准的SQL基础上扩展一些函数,这些函数用于讲行数据处理或复杂计算,它们通过对一组数据进行计算,得到最终需要的输出结果。
函数一般都会有一个或者多个输入，这些输入被称为函数的参数，函数内部会对这些参数进行判断和计算，最终只有一个值作为返回值。函数可以出现在SQL语句的各个位置，比较常用的位置是select之后和where子句中。

根据函数对名行数据的处理方式，函数被分为单行函数和名行函数，单行函数对每行输入值单独计 算，每行得到个计算结果返回给用户；多行函数对多行输入值整体计算，最后只会得到一个结果。单行函数和多行函数的示意图如图13.13所示。

SQL中的函数和Java语言中的方法有点相似，但SQL中的函数是独立的程序单元，也就是说，用函数时无须使用任何类、对象作为调用者，而是直接执行函数。执行函数的语法如下：

  function_name (argl, arg2...)

多行函数也称为聚集函数、分组函数，主要用于完成一些统计功能，在大部分数据库中基本相同。但不同数据库中的单行函数差别非常大，MySQL中的单行国

数具有如下特征。

》单行函数的参数可以是变量、常量成数据列，单行函数可以接收多个参数，但只返回一个值。

》单行函数会对每行单独起作用，每行(可能包含多个参数)返回一个结果。

》使用单行函数可以改变参数的数据类型。单行函数支持嵌套使用，即内层函数的返回值是外层函数的参数。

MySQL的单行函数分类如图13.14所示。

MySQL数据库的数据类型大致分为数值型、字符型和日期时间型,所以MySQL分别提供了对应的函数。转换函数主要负责完成类型转换,其他函数又大致分为如下几类。

》位函数

》流程控制函数

》加密解密函数

》信息函数

每个数据库都包含了大量的单行函数，这些函数的用法也存在一些差异，但有一点是相同的一一每个数据库都会为一些常用的计算功能提供相应的函数，这些函数的函数名可能不同，用法可能有差异， 但所有数据库提供的函数库所能完成的功能大致相似，读者可以参考各数据库系统的参考文档来学习这些函数的用法。下面通过一些例子来介绍MySQL单行函数的用法。

#选出teacher_table表中teacher_name列的字符长度
select char_length (teacher_name)
from teacher table;
#计算teacher_name列的字符长度的sin值
select sin(char_length(teacher_name))
from teacher_table;
#计算1.57的sin值，约等于1
select sin(1.57);
#为指定日期添加一定的时间
#在这种用法下interval是关键字，需娶一个数值，还需要一个单位
SELECT DATE_ADD('1998-01-02',interval 2 MONTH);
#这种用法更简单
select ADDDATE('1998-01-02'，3);
#获取当前日期
select CURDATE();
#获取当前时间
select curtime();
#下面的MD5是MD5加密函数
select MD5('testing');

MySQL提供了如下几个处理null的函数，

》 ifnul(expr1, expr2):如果cxpr1为null，则返回expr2，否则返回expr1.

》nullif(expr1,expr2):如果erpr1和cxpr2相等，则返回null，否则返回expr1.

》if(expr1,expr2,expr3):有点类似于?:三目运算符，如果expr1为true、不等干0，且不等于null,则返回expr2，否则返回expr3。

isnull(expr1):判断expr1是否为null，如果为null则返回true，否则返回false

#如果student_name列为nul1，则返回'没有名字'
select ifnull(student_name,'没有名字')
from student_table;
#如果student name列等于'张三'，则返回null
selectnulliE(student name,'张三')
from student_table;
如果studen_name列为null，则返回'没有名字'，否则'有名字'
select if(isnull(student name)，'没有名字’,'有名字')
from student_table;

MySQL还提供了一个case函数，该函数是一个流程控制函数。case函数有两个用法，case 函数第一个用法的语法格式如下:

case value
uhen compare_value1 then resultl
when compare_value2 then result2
...
else result
end

case函数用value和后面的compare_value1、 compare_value2…依次讲行比较，如果value和指定的compare value1相等，则返回对应的result1，否则返回else后的result。例如如下SQL语句:

#如果java_teacher为1，则返回'Java老师'，为2返回'Ruby老师"，否则返回'其他老师'
select student_namer case java_teacher
when 1 then 'Java老师"
when 2 then 'Ruby老师
else'其他老师'
end
from student_table;

case函数第二个用法的语法格式如下:

case
when conditionl 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'高级班'
erd
from student_table;

虽然此处介绍了一些MySOL常用函数的简单用法，但通常不推荐在Java程序中使用特定数据库的函数，因为这将导致程序代码与特定数据库耦合;如果需要把该程序移植到其他数据库系统上时，可能需要打开源程序，重新修改SOL语句

11 分组和组函数

组函数也就是前而提到的多行函数，组函数将一组记录作为整体计算，每组记录返回一个结果，而不是每条记录返回一个结果。常用的组函数有如下5个。

》avg([distinct|all]exor);计算多行expr的平均值，其中，expr可以是变量、常量或数据列，但其数据类型必须是数值型。还可以在变量、列前使用distinct或all关键字，如果使用distinct，则表明不计算重复值，all用和不用的效果完全一样，表明需要计算重复值。

》count({*|[distinct[alll]expr}):计算多行expr的总条数，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型，用星号(*)表示统计该表内的记录行数;distinct表示不计算重复值。

》max(expr);计算名行expr的最大值，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型。

》min(expr):计算多行expr的最小值，其中expr可以是变量、常量或数据列，其数据类型可以是任意类型。

》 sum([distinct|all]expr):计算多行expr的总和，其中expr可以是变量、常量或数据列，但其数具类型必须是数值型；distinct表示不计算重复值。

#计算student_table表中的记录条数
select count(*)
from student_table;
#计算java_teacher列总共有多少个值
select count(distinct iava_teacher)
from student_table;
#统计所有student_id的总和
select sum(student_id)
from student table;
#计算的结果是20 * 记录的行数
select sum(20)
from student_table;
#选出student_table表中student_id最大的值
select max(student_id)
from student_table;
#选出teacher_table表中teacher_id最小的值
select min (teacher_id)
from teacher_table.
#因为sum里的expr是常量34，所以每行的值都相同
#使用distinct强制不计算重复值，所以下面计算结果为34
select sum(distinct 34)
fron student_table;
#使用count统计记录行数时，null不会被计算在内
select count(student name)
from student_table;

对于可能出现null的列，可以使用ifnull函数来处理该列。

#计算java_teacher列所有记录的平均值
select avg(ifnull(java_teacher, O))
from student_ table;

值得指出的是，distinct和*不同时使用，如下SQL语句有错误。

select count(distinct *)
from student_table;

在默认情况下，组函数会把所有记录当成一组，为了对记录进行显式分组，可以在select语句后使用group by子句，group by子句后通常跟一个或多个列名，表明查询结果根据一列或多列进行分组——当一列或多列组合的值完全相同时，系统会把这些记录当成一组。SQL语句如下:

#count(*)将会对每组得到一个结果
select count(*)
from student_table
#将java_teacher列值相同的记录当成一组
group by java_teacher;

如果对多列进行分组，则要求多列的值完全相同才会被当成一组。SQL语句如下:

select count*()
from student table
# 当java_teacher、 student_name两列的值完全相同时才会被当成一组
group by java_teacher, student_name;

对于很多数据库而言，分组计算时有严格的规则——如果查询列表中使用了组函数，或者select语句中使用了group by分组子句，则要求出现在select列表中的字段，要么使用组函粉包起来，要么必须出现在group by子句中。这条规则很容易理解，因为一日使用了组函数或使用了group by子句，都将导致多条记录只有一条输出，系统无法确定输出多条记录中的那一条记录。

对干MySQL来说，并没有上面的规则要求，如果某个数据列既没有出现在group by之后，也没有使用组函数包起来，则MySQL会输出该列的第一条记录的值。图13.15显示了MySQL的处理结果。

如果需要对分组进行过滤，则应该使用having子句，having子句后面也是一个条件表达式，只有满足该条件表达式的分组才会被选出来。having子句和where子句非常容易混淆，它们都有过滤功能，但它们有如下区别。

》不能在where子句中讨滤组，where子句仅用于过滤行。过滤组必须使用having子句。

》不能在where子句中使用组函数，having子句才可使用组函数

SOL语句如下:

select
from student_table
group by java_teacher
#对组进行过滤
having count(*)>2;

12 多表连接查询

很多时候,需要选择的数据并不是来自一个表,而是来自多个数据表,这就需要使用多表连接查询。 例如，对于上面的student_table和teacher_table两个数据表，如果希望查询出所有学生以及他的老师名字，这就需要从两个表中取数据。

多表连接查询有两种规范，较早的SQL 92 规范支持如下几种多表连接查询。

》等值连接。

》非等值连接。

》外连接。

》广义笛卡儿积。

SQL 99 规范提供了可读性更好的多表连接语法，并提供了更多类型的连接查询。SQL 99 支持如下 几种多表连接查询。

》交叉连接。

》自然连接。

》使用using子句的连接。

》使用on子句的连接。

》全外连接或者左、右外连接。

1. SQL 92 的连接查询

SQL92 的多表连接语法比较简洁，这种语法把多个数据表都放在from之后，多个表之间以逗号隔开:连接条件放在where之后,与查询条件之间用and逻辑运算符连接。如果连接条件要求两列值相等，则称为等值连接，否则称为非等值连接;如果没有任何连接条件，则称为广义笛卡儿积。SQL 92中多表连接查询的语法格式如下:

select column1, column2 ...
from tablel, table2 ...
[where join_condition]

多表连接查询中可能出现两个或多个数据列具有相同的列名,则需要在这此同名列之间使用表名前缀或表别名前缀作为限制，避免系统混淆。

实际上,所有的列都可以增加表名前缀或表别名前缀。只是进行单表查询时,绝不可能出现同名列，所以系统不可能混淆，因此通常省略表名前缀。

如下的SQL语句查询出所有学生的资料以及对应老师的姓名。

select s,*, teacher name
并指定多个数据表，并指定表别名
from student table s, teacher table t
样使用where指定连接条件
where s.java teacher m t.teacher id;

执行上面查询语句,将看到如图13.16所示的结果。


上面的查询结果正好满足要求，可以看到每个学生以及他对应的老师的名字。实际上，多表查询的过程可理解成一个嵌套循环，这个嵌套循环的伪码如下:

//依次遍历teacher table表中的每条记录
for t in teacher_table
  //遍历student table表中的每条记录
  for s in student_table{
    //当满足连接条件时，输出两个表连接后的结果 
    if (s.java_teacher = t.teacher_id)
        outputs + t
    }
  }
}

理解了上面的伪码之后,接下来即可很轻易地理解多表连接查询的运行机制。如果求广义笛卡儿积，则where子句后没有任何连接条件,相当于没有上面的if语句,广义笛卡儿积的结果会有n*m条记录。 只要把where后的连接条件去掉，就可以得到广义笛卡儿积，SQL语句如下:

#不使用连接条件，得到广义笛卡儿积
select s.*  ,teacher_name
#指定多个数据表，并指定表别名
from student_table s, teacher_table t;

与此类似的是，非等值连接的执行结果可以使用上面的嵌套循环来计算，SQL语句如下:

select s.*, teacher_name
#指定多个数据表，并指定表别名
from student_table s, teacher_table t
#使用where指定连接条件，非等值连接
where s.java_teacher > t.teacher_id;

上面SQL语句的执行结果相当于if条件换成了s.java teacher > t.teacher id。

如果还需要对记录进行过滤，则将过滤条件和连接条件使用and连接起来，SQL语句如下:

select s.*  teacher_name
#指定多个数据表，并指定表别名
from student_table s, teacher_table t
#使用where指定连接条件，并指定student_name列不能为null
where s.java_teacher = t.teacher_id and student_name is not null;

虽然MySQL不支持SQL 92中的左外连接、右外连接，但本书还是有必要了解一下SQL 92中的左外连接和右外连接。SQL 92中的外连接就是在连接条件的列名后增加括号包起来的外连接符(+或*， 不同的数据库有一定的区别)，当外连接符出现在左边时称为左外连接,出现在右边时则称为右外连接。SQL语句如下:

select s.*, teacher name
from student_table s, teacher_table t
#右外连接
where s.java_teacher = t.teacher_id(*);

外连接就是在外连接符所在的表中增加一个“万能行”，这行记录的所有数据都是null，而且该行可以与另一个表中所有不满足条件的记录进行匹配,通过这种方式就可以把另一个表中的所有记录选来，不管这些记录是否满足连接条件。

除此之外，还有一种自连接，正如前面介绍外键约束时提到的自关联，如果同一个表中的不同记录之间存在主、外键的束关联，例如把员工、经理保存在同一个表里，则需要使用自连接查询。

注意：

自连接只是连接的一种用法、并不是一种连接类型、不管是SQL 92还是SQL 99都可 以使用自连接查询、自连接的本质就是把一个表当成两个表来用。

下面的SOL句建立了一个自关联的数据表，并向表中插入了4条数据。

create table emp_table
(
emp_id int auto_ineroment primary key,
enp_nane varchar(255),
manager_id int,
foreign key(manager_id) referenees emp_table(emp_id)
);
insert into emp_table
values (null, '唐僧'，null),
(null,'孙悟空'，1)，
(null，'猪八戒'，1)，
(null，'沙憎'，1);

如果需要查询该数据表中的所有员工名，以及每个员工对应的经理名，则必须使用自连接查询。所谓自连接就是把一个表当成两个表来用，这就需要为一个表起两个别名，而且查询中用的所有数据列都要加表别名前缀，因为两个表的数据列完全一样。下面的自连接查询可以查询出所有的员工名，以及对应的经理名。

select emp.emp_id, emp.emp_name员工名，mgr.emp_name经理名
from emp table_emp, emp_table mqr
where emp.manager_id = mgr.emp_id;