[疯狂Java]SQL库函数：行函数、组函数、select分组、筛选组

1. SQL库函数：

    1) 就跟C语言标准库函数一样，SQL将一些经常使用的到的功能封装成标准库函数供用户使用，这些库函数底层的实现效率非常高，比用户自行编写同样的函数效率会高很多，因此遇到这些常用功能就尽量使用库函数；

    2) SQL并不是编程语言，更没有面向对象这一说法，因此SQL的函数跟C语言一样，是一种独立的执行单元，不需要任何类、对象来调用，而是最高级执行体单独执行；

    3) SQL函数的大致分为两类：

         i. 行函数：

            a. 其参数可以是变量、常量，变量可以是select出来的一行中的列；

            b. 行函数也称为单行函数，它只能对select出来的单独一行（一次一行）起到作用（可以利用选择出来的一行的列或列的算术表达式等作为参数），但不能同时对多行进行统计计算；

            c. 行函数只能返回一个值，并支持嵌套调用（用一个行函数的返回值作为另一个行函数的参数）；

        ii. 组函数：

            a. select出的多行可以组成一个组；

            b. 组函数可以对多行组成的组进行统计，即同时作用于多行；

            c. 一般使用组函数时要对查询结果进行分组，然后再利用组函数对每组进行统计，分组可以按照某列的值相等来分；

2. 常用的行函数：

    1) 计算字符串长度：char_length(字符串);

    2) sin：sin(数值);

    3) 返回当前日期/时间：curdate(); curtime();

    4) 对一个字符串进行MD5加密：md5(字符串); // 返回加密后的字符串

    5) 判断null的三函数：很常用

         a. ifnull(expr1, expr2);  // expr1为null返回expr2，否则返回expr1

         b. nullif(expr1, expr2);  // expr1=expr2返回null，否则返回epxr1

         c. isnull(expr1);  // expr1为null返回true，否则返回false，其实还是运算符is null更好用

    6) 三目运算符函数（类似于C语言的? : )：if(expr1, expr2, expr3);  // 荣誉感以expr1为true且不为0或null就返回expr2，否则返回expr3

！！即在该方法中0和null都表示false；

    7) 分支选择函数——case：MySQL单独提供的，但是非常好用，因此MySQL管理员尽量要掌握

         a. C语言的用法——匹配返回：

case 要比较的值
when 比较值1 then 返回值1
when 比较值2 then 返回值2
...
else 其余情况下的返回值
end

！因此case函数最终返回的是一个值，只不过是根据要比较的值返回一个想要的值；

         b. 条件返回：

case
when 比较条件1 then 返回值1
when 比较添加2 then 返回值2
...
else 其余情况下的返回值
end

！！case是根据到底符合那种标胶条件来返回想要的值的；

！！示例：

select teacher_name,
	case teacher_id
		when 1 then 'Java Teacher'
		when 2 then 'C++ Teacher'
		else 'Other Teacher'
	end
from teacher_table;

select student_name,
	case
		when student_age <= 15 then 'primary'
		when student_age <= 20 then 'upgrade'
		else 'super'
	end
from student_table;

！！case具有短路特性，即从上往下一个一个条件检查，检查到第一个符合的就立马返回而不继续执行下面的检查了！！

3. 组函数：

    1) 即前面提到的计算多行记录统计值的函数，多行组成一组，每组返回一个值！

    2) 如何分组可以人为指定，但是如果不指定分组那就把所有查询出来的记录（行）当成一个组来处理，因此计算得到的结果永远只有一行，因为只有一组；

    3) 最常用的5大组函数（统计函数）：distinct表示去掉重复的expr，all表示不去重，默认情况下就是不去重，因此对于不去重的情况可以不写all

         a. avg([distinct|all]expr);  // 计算组内expr的平均值

         b. count(*); // 计算组内所有记录的条数

         c. count(distinct expr); // 计算组内所有记录的条数，但是expr重复的不算，并且expr等于null的也不算！！通俗地将就是计算expr列共有多少个值（不包括null）

         d. max(expr); // 返回组内最大的expr

         e. min(expr); // 返回组内最小的expr，最大最小不必去重，去不去重结果都一样，去重反而还有一定的额外开销

         f. sum([distinct|all]expr); // 返回组内expr的和，需要考虑是否去重

    4) 有时候计算avg、sum的时候需要把null值当成0看待，但是SQL默认distinct和不加distinct都将忽略null，因此如果有这样的需求是应该是ifnull函数，例如：

select avg(ifnull(num, 0)) from tableX;

4. select分组：

    1) select可以对查询结果中的记录进行分组，使用关键字group by，可以将其作为属性放在select的最后；

    2) 格式：select ... from ... group by expr1[, expr2[, expr3...]];

    3) 表示查询出来的记录会按照expr1的值分组，expr1相等的记录分为一组，然后在expr1相等的组中继续分组，按照expr2的值进行分组，然后再继续在expr2值相等的组中继续分...；

    4) 示例：select count(*) from student_table group by teacher, class_date;

！！这里表示先将记录按照授课老师分组，在每个授课老师下再按照授课日期分组，而count(*)的计算结果是最细分的那个组，即class_date组中的人数！

！！！记住！分组后计算的统计值作用于最细分的那个组！！

！！在这里假如2个老师A和B，A在1号有3个学生上课，2号有4个学生上课，B老师在2号有7个学生上课，在3号有10个学生上课，那么上面的select语句将输出4行，分别是3、4、7、10；

    5) 标准SQL对分组统计的规定：

         i. 分组的目的就是为了统计！！

         ii. 因此一旦group by分组了，那么就必须在select后出现组函数（选择统计量），否则就会报错；

         iii. 因为分组的目的就是为了把一组当成一条记录来看待，一条记录查询出一个结果，但是如果select后面是普通的列（选择一个字段），那么你要用一组中的哪条记录来代表这个组呢？？没有任何依据，因此自相矛盾，直接拒绝查询；

    6) 分组统计的说明字段：

         i. 如果单单执行上面的查询语句”select count(*) from student_table group by teacher, class_date;“会得到4个结果，但仅仅就是4个值而已，你都不知道哪个值代表哪个组，可读性很差；

         ii. 为了显示这些值分别代表哪些组可以在select后加上作为分组依据的列，例如：select count(*), teacher, class_date from student_table group by teacher, class_date;

         iii. 这和5)的规定不矛盾，因为只要select中出现一个组函数即可，只要有一个组函数（统计值）就能代表一个组了；

         iv. 其实最好的是用常量字符串加以说明：select count(*) as "count", concat(teacher, class_date) as "group" from student_table group by teacher, class_date;

5. 筛选组：

    1) where语句用来筛选行，而标准SQL为了和筛选行相区别用关键字having来筛选组；

    2) 既然分组统计后一整个组会被当做一行来处理（相当于把一整组当做一条记录来处理），因此筛选行时一定要拿能代表一整组的值来做条件判断，因此having中的条件判断中必须要包含组函数（统计值）；

    3) 例如：select count(*) from student_table group by student_name having count(*) > 10; // 把人数超过十的组筛选出来

    4) 不要where和having混用，一个筛选行，一个筛选组，用错直接报错！