多表连接查询

数据存储在多表的原因：

• 为什么不用一个表存储数据，非要使用多个表呢，下面通过例子介绍其原因。
• 假设有如下结构的关系。
  当一名学生考完某课后，其信息就会在student表中以一条记录的形式存储。下面列出student关系所存在的问题。
  (1）数据冗余。某个学生可能考了多门课程，有多个成绩，所以该生的“学号”、“姓名”“性别”和“来源地”就会有多次重复,请参考表10.1所示。
  (2）更新异常。由于数据冗余，如果要更改“张三”的来源地为“福建省”时，必须要更改多条记录，一旦遗忘了更改某条记录，“张三”就会有两个不同的来源地。
  (3）插入异常。如果某学生没有考任何考试，则无法将这名学生的学号、姓名、性别、来源地等信息插入到表内。因为，student中学号和课号组成了一个码，码值的一部分为空的记录,是不能被插入到表中的。
  (4）删除异常。如果一名学生的考试成绩全部作废，需要删除，则其正常信息也会随之被删除。这样就丢掉了一部分有用的信息。
  由于上述原因，数据就被放到了不同的多个表中。

范式：

• 目前，范式分为第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、BCNF范式、第四范式(4NF)、第五范式(5NF）和第六范式(6NF)等范式。这些范式其实是不同程度的规则，它们之间有着层次关系，第一范式是最底层的规则，第二范式是满足第一范式的基础上又多了一些要求的规则，以此类推。
• 一般来说，设计的数据库只要满足第三范式就可以了，所以下面只介绍1NF、2NF和3NF。
  ❀第一范式(1NF）
  第一范式是关系数据库的底线，要想成为关系数据库则必须要满足第一范式。第一范式的内容为，记录的每一个分量都是不可分割的基本数据项。
  ❀第二范式(2NF)
  要满足第二范式，首先必须满足第一范式，即满足第一范式是满足第二范式的前提条件，其次，第二范式增加的要求是，每一个非主属性要完全函数依赖于码。
  可以用分解的方法将一个不满足2NF的表，分解为满足2NF的多个表。
  ❀第三范式(3NF)
  第三范式是在满足第二范式的基础上，还增加了每一个非主属性都不传递依赖于码的要求。

连接查询：

• 连接两表的方法
  • 无连接规则连接
    无连接规则连接后得到的结果是将所有表中的每一行都互相连接，即结果为笛卡尔积。
    语法格式：

  SELECT *(或字段列表)
  FROM 表名1,表名2,...;
  #得到的表数据总数为每个表的数据量相乘
  #如：表1数据数为N,表2数据数为M，则连接后的表的数据数为N*M
  

  • 有链接规则连接
    有连接规则连接，其实就是在无连接规则的基础上，加上WHERE子句指定连接规则的连接方法。
    语法格式：

  SELECT *(或字段列表)
  FROM 表名1,表名2,...
  WHERE 连接规则 [AND 连接规则 AND...];
  #在多表连接时，即使不要求在表独有的字段前加表名,但笔者还是建议加上表名
  #因为这样会很清楚的表示哪个字段属于哪个表,这将对以后的维护起到很好的作用
  

• 使用笛卡尔积解决录入难题
  一般用来解决当有两个表时，假设其中一个表的主码为（字段1），另一个表的主码为（字段2），要生成的表的主码为前面两个表的主码连接起来的，即（字段1，字段2）这种情况。
• 使用表别名简化语句
  在FROM子句中，在表名的后面加上关键字AS和别名即可。
  目的是为了方便阅读
  语法格式：

  SELECT *(或字段列表)
  	FROM 表名1 AS 别名1,表名2 AS 别名2,...
  	WHERE 连接规则 [AND 连接规则 AND...];
  

• 使用INNER JOIN连接查询
  在ANSI SQL规范中建议使用INNER JOIN进行多表连接。如此以来，WHERE子句中就不用再放置连接规则，而只放置查询条件就可以了。
  使用INNERJOIN连接n个表的语法格式为：

  SELECT *(或字段列表)
  FROM 表名1
  INNER JOIN 表名2 ON 连接规则
  INNER JOIN 表名3 ON 连接规则
  .....
  INNER JOIN 表名n ON 连接规则
  #其中,关键字“ON”之后是连接表的规则。
  

高级连接查询：

• 自连接查询
  自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名
  语法格式：

  SELECT *(或字段列表)
  FROM 表名 AS 别名1,表名 AS 别名2
  WHERE 连接规则 AND 条件表达式;
  #以上的表名是同一张表的表名
  

• 内连接查询
  是指所有查询出的结果都是能够在连接的表中有对应记录的。连接符号：INNER JOIN
  • 等值连接
    连接规则由等于号=组合而成的连接，并且列出两个表中所有字段的连接，即SELECT子句中使用星号（*）通配符的连接就属于等值连接。
  • 自然连接
    在等值连接的基础上稍加改动即可得到自然连接，等值连接将两个表中的所有字段全部列出，而自然连接则不将相同的字段显示两次，即在SELECT子句中列出需要显示的字段列表。
  • 不等值连接
    不等值连接的连接规则由等于号以外的运算符组成，例如，由>、>=、<、<=、>或BETWEEN等。
• 外连接查询
  在多表连接查询时，有时希望表的所有记录都被包含进去，即使没能匹配的记录也被查询结果集包含在内。这时，内连接查询已经满足不了需求了，所以应该采用另外一种连接查询方法――外连接查询。外连接有左外连接、右外连接和全外连接三种。
  • 左外连接
    指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为NULL。
    连接符号：LEFT OUTER JOIN或LEFT JOIN
    如图：
    
  • 右外连接
    指以右边的表的数据为基准，去匹配左边的表的数据，如果匹配到就显示，匹配不到就显示为NULL。
    连接符号：RIGHT OUTER JOIN或RIGHT JOIN
    如图：
    
  • 全外连接
    将两个表的所有记录都包含到结果集中，没有对应的值就显示NULL。
    连接符号：FULL OUTER JOIN
    ==注意：MySQL中没有全外连接，要实现全外连接的效果，可以采用关键字UNION来联合左、右连接
    如图：
    
• 交叉连接查询
  交叉连接其实就是无连接规则的连接，这种连接有两种表示方法。
  Ⅰ.用逗号隔开表面
  语法格式：

SELECT *(或字段列表)
FROM 表名1,表名2[,...];

Ⅱ.用CROSS JOIN关键字连接表名
语法格式：

SELECT *(或字段列表)
FROM 表名1 CROSS JOIN 表名2[ CROSS JOIN ...];

组合查询：

• 使用组合查询
  有时，需要将多个查询语句的结果放到一起，以一个查询结果集的形式将其显示出来这时就可以使用组合查询
  组合查询是使用UNION关键字将多个SELECT查询语句组合起来查询的一种查询方法
  语法格式：

SELECT 语句1
UNION
SELECT 语句2
UNION
SELECT 语句3
......
UNION
SELECT 语句n
#查询出来的结果集对于每个语句查询出的重复数据只显示一次
#使用UNION时，如果希望不删除重复值,则可以在UNION后加上ALL关键字。例如,下面的语句不删除重复值记录

• 使用UNION的规则
  • Ⅰ.每个查询语句应当有相同数量的字段
    在使用UNION组合查询语句时，一定要注意每个单独的SELECT子句内的字段个数一定要相同,如果不同则会出现错误
    当独立查询语句的字段个数不同时,可以在字段个数不够的地方使用常量补位，避免错误
  • Ⅱ.每个查询语句中相应的字段的类型必须相互兼容
    在每个查询语句字段个数相等的前提下，相应的字段的类型应当互相兼容
• 使用UNION得到复杂的统计汇总样式
  联合UNION、GROUP BY和聚合函数三者会得到具有很棒的统计汇总样式的查询结果，这也是OR所不能替代的。
• 排序组合查询的结果
  • 虽然组合查询中可以有多个单独的SELECT语句，而且每个独立的SELECT语句又都可以拥有自己的WHERE子句、GROUP BY子句和HAVING子句，但是，整个语句中却只能出现一个ORDER BY子句，而且它的位置必须在整个语句的末尾，就是说只能对组合查询最后的结果进行排序，而并不能只对某个单独的SELECT语句的结果进行排序。
  • 组合查询其实存在一个很有意思的排序问题。当没有ORDER BY子句时，查询结果会根据第一个SELECT子句中字段名列表升序排序。