10 数据库函数
执行函数的语法如下：
function_name (argl, arg2...)
单行函数
下面通过一些例子来介绍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');
处理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;
case函数
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;
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;
group by子句
在默认情况下，组函数会把所有记录当成一组，为了对记录进行显式分组，可以在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 的连接查询
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;
