数据库部分知识的总结

1.group by ,having
having 用于where和group by查询出来的分组进行过滤，查出满足条件的分组结果，它是一个过滤声明，是在查询返回结果集以后对查询结果进行过滤。
group by:对 select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合
where先执行->再groupby分组；groupby先分组->having在执行(select from where group by having order by limit)
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出
2.UNION ,UNION ALL
UNION 去重复且排序
UNION ALL 不去重不排序
效率上说UNION ALL比UNION要快很多，所以如果可以确认需要合并的2个集合中不包含重复数据且不许要排序的话，就使用UNION ALL
3.SQL查询左连接、右连接、内连接等
左连接是以左表为基础,根据on后给出的两表的条件将两表连接起来，结果会将左表所有的查询信息列出,而右表只列出on后条件与左表满足的部分。
右连接是以右表为基础，根据on后面给出的两表的条件将两表连接起来，结果会将右边所有的查询信息列出,而左表只列出on后条件与右表满足的部分。
内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行
全连接返回2张表所有的行
子查询是把一个查询的结果在另一个查询中使用。
a)ANY关键词可以理解为“对于子查询返回的列中的任一数值，如果比较结果为true，则返回true”。 
b)ALL 的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”
4.行转列
1)通过case when ... then ...else ... end实现行转列
需要得到每位同学的每一门成绩，目前的表中的信息如下：
ID    NAME     SUBJECT  GRADE
1     张三     语文       90
2     张三     数学       100
3     李斯     语文       85
4     李斯     数学       99
可是我们看到每一个同学出现了多行信息，这并不是我们想要的结果，我们更希望每个同学显示为1行。所以我们可以通过case when ... then ...else ... end实现行转列：
SELECT NAME
SUM(CASE WHEN SUBJECT="语文" THEN GRADE ELSE 0 END) AS YUWEN,
SUM(CASE WHEN SUBJECT="数学" THEN GRADE ELSE 0 END) AS SHUXUE
FORM NameAndSubjectAndGrade GROUP BY NAME;
2)pivot功能是将行转换为列
SELECT * FROM NameAndSubjectAndGrade PIVOT(MAX(GRADE) FOR SUBJECT IN ("语文","数学")) a
5.建表方面，怎么考虑自己的项目是三范式还是反范式?
第一范式：如果数据表中的每一个字段都是不可再分割的最小数据单元，则满足第一范式
第二范式：非主键列是否依赖主键（包括一列通过某一列间接依赖主键），要是有依赖关系的就是第二范式；
第三范式：非主键列是否是直接依赖主键，不能是那种通过传递关系的依赖的。要是符合这种就是第三范式；
范式存在的好处:范式可以避免数据冗余,减少数据库空间,减轻维护数据完整性的麻烦
所谓反范式，故名思义，跟范式所要求的正好相反，在反范式的设计模式，我们可以允许适当的数据的冗余，用这个冗余去取操作数据时间的缩短。
也就是利用空间来换取时间,把数据冗余在多个表中，当查询时可以减少或者是避免表之间的关联；
场景分析:
如我们现在要对一个 学校的课程表进行操作，现在有两张表，一张是学生信息student（a_id,a_name,a_adress,b_id）表，一张是课程表 subject(b_id,b_subject)，
现在我们需要一个这样的信息，把选择每个课程的的课程名称和学生姓名输出来：
SQL语句为：select  B.b_id,B.b_subject,A_a_name from student A ,subject B;
当上面的数据量不多时，我们这样去查询没有问题；当我们的两张表的数据都是在百万级的时候，我们去查上面的信息， 问题出现了，这个查询动不动就是几百毫秒，
甚至更慢，这样的查询效率根本不能满足我们对于网页速度的要求（一般不能超过100毫秒），怎么办？当然要反范式，在课程表里面添加冗余字段——学生姓名，这样我们就可以通过下面的查询达到同样的目的：
SQL语句为：select  b_id,b_subject,a_name from subject B;
将两个查询放在一起查看执行计划，就会发现，第一个查询开销占了92%，而第二个才8%，也就是说，第二个查询比起第一个查询，效率上优化了10倍以上，成果显著啊。
当我们的数据量非常大，目前除了对数据库的设计改动外，还可以通过对数据层进行缓存处理
6.索引
索引的主要目的是提高数据库系统的性能,加快数据的查询速度与减少系统的响应时间.SQL索引有两种，聚集索引和非聚集索引
例如字典中，拼音查询法就是聚集索引，部首查询就是一个非聚集索引.所以说聚集索引存储记录是物理上连续存在的，而非聚集索引是逻辑上的联系，物理存储并不连续
建立索引的原则；
1) 定义主键的数据列一定要建立索引。
2) 定义有外键的数据列一定要建立索引。
3) 对于经常查询的数据列最好建立索引。
4) 对于需要在指定范围内的快速或频繁查询的数据列;
5) 经常用在WHERE子句中的数据列。
6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。