结项总结

    最近项目完结，有点闲暇时间，来总结总结项目中用到的之前没用过的sql语句和数据库知识。下面用自己的理解来记录一下~

• 范式

　　第一范式：要求我们字段要划分到最小单元，即表头不能有合并单元格项

　　第二范式：要求数据库设计有主键

　　第三范式：要求外键字段不能有除了引用表主键以外的字段

• 聚合函数

　　sum、count、ave等等。比如学成成绩表中，我们要计算成绩在60-80分之间的总分和人数，可以这样写

select 
count(case when score >= 60 and score <= 80 then 1 else null end) as stuNum
,sum(case when score >= 60 and score <=80 then score else 0 end) as sumScore
from 学生成绩表

　　由于项目数据处理部分数据比较多，这样的sql在代码中成群结队，之前没想到这么多使用innerjoin来写的，后来一一改成了这种聚合函数的方式，虽说不见得效率提高了，但是代码确实比较好看了。

　　上面的sql是求总表的，如果需求是求各个班级的分数分布情况，比如统计每个班的及格人数，所有的班级成绩存在一张表里。那么可以这样写

select 
class as 班级
,count(case when score >= 60 and score <= 80 then 1 else null end) as stuNum
,sum(case when score >= 60 and score <=80 then score else 0 end) as sumScore
from 学生成绩表
group by class -- 班级

　　之前做过一个验证码的生成接口，需求是这样的：每一场考试对应一张考试表，考试表中有一个叫验证码的字段，用户添加考试的时候后台自动生成一个考试验证码，并存入数据库。

　　要求：1、验证码由数字和小写字母组成，长度为6；2、生成的验证码为数据库中不存在的。

　　就这两个要求，第一次实现的时候使用一个死循环，生成一个验证码然后去后台查询是否有存在的，若有，则丢弃然后重新生成。如果没有就break然后返回。

　　这个解决方案被老大看到了一顿教育，说咱们系统里面一定不能再循环里面写查询，更何况你这个还是死循环，回去改。

　　于是回去换了一个思路，将数据库里面考试表中所有的验证码查询出来，然后存到一个list里面，然后开始死循环，生成一个验证码，然后用contain函数判断列表里面是否存在，若存在则丢弃，若不存在则break并且返回。

　　后来数据达到两千多的时候感觉就有点卡，不知道2000就卡是不是数据库的原因，那时候用的是sqlserver，于是考虑换思路。换思路的方向改成在数据库内部吧所有验证码拼接成一个字符串，用逗号分隔，然后返回一个超长字符串，然后生成验证码去这个String中判断是否存在。好了，顺利完成，恩，那个sql这样写的：

　　目标数据库是SQL Server，别的数据库不知道是否支持OUTER APPLY关键字。

　　原理就是最内层的那个select ... for xml auto，这会生成一个字段名=值这种类型的xml格式字符串，然后把其中的数据库名、字段名和多余的负号去掉就只剩下值了，然后替换的时候保留逗号，一个逗号分隔的超长字符串就出来了。

　　有一个问题是没测试这个字符串会不会因为太长而出问题~不过我已经离开那家公司了，回头有兴趣再测一测吧。