MySQL基础2

多表设计

   为什么需要多表关联？

主要原因就是因为我们在使用sql查询时，如果使用单表查询，会发现常常有大量的数据冗余，所以我们需要多表关联将冗余数据拆分。

举例说明，如下图。

我们会发现，此图查询同学，后面的民族和年纪就是很经典的数据冗余，那么其实简单的数据只需要多表关联，就能填入相应编号即可，不用非要把名称写出来。 就像图中建立了民族表和年纪表。那么主表中只需要填入相应编号即可。这样 就将一个冗余的数据给通过多表关联给拆分开来。 

 那么什么情况下用到多张表?

    例如教务管理系统
     学生信息,专业,课程...
      学生信息
      专业信息
      课程信息
    在一个比较复杂的系统中,有许多种信息,建议把不同的信息,存储在不同的表中,但是表与表之间又互相有联系,那么就需要进行表的设计。

多表设计——关联查询

数据库设计范式

1. 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的 规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计 要求的总结。

2. 目前关系数据库有5种范式：第一范式（1NF）、第二范式（2NF）、第 三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范 式（5NF，又称完美范式）。

3. 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步 满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般 来说，数据库只需满足第三范式(3NF）就行了。

这里看似内容很多，但作为初学者在多表设计的时候只需要做到第三范式就可以了。

第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就 说明该 数据库表满足了第一范式。

如下图。

 在主表中的联系方式列就说的很模糊，联系方式有很多种，但是他这里并没有明说。所以这就不符合第一范式，要确保所有字段值都是不可分解的原资质。

第二范式就是要有主键，要求其他字段都依赖于主键。

1. 没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。

2.其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其 他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依 赖于主键，也就成了唯一的。

这个其实就很容易理解，倘若表中有完全相同的两个数据，如果没有主键那么就查询不到自己想要的值了。

第三范式就是要消除传递依赖，方便理解，可以看做是“消除冗余”。

外键：引用另外一个数据表的某条记录。

 外键列类型与主键列类型保持一致 数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来 的。

create table score(

cid int not null auto_increment primary key,

score int,

sid int,

CONSTRAINT 约束名 foreign key(sid) references student(sid) );

约束名规则: 例：FK_ForeignTable_PrimaryTable_On_ForeignColumn

 可以为表添加为外键。
   ALTER TABLE student ADD CONSTRAINT majorid_fk FOREIGN KEY(majorid) REFERENCES major(id)    
为外键添加约束后的要求
    1.当主表中没有对应的记录时，不能将记录添加到从表
    2、不能更改主表中的值而导致从表中的记录孤立
    3、从表存在与主表对应的记录，不能从主表中删除该行
    4、删除主表前，先删从表

 学生关联专业这种关系, 称为 多对一  也可称为  一对多关系。举例说明，作为学生表而言，多个学生可以选择一个专业，这就是多对一。而作为专业表来说，一个专业可被做个学生选择，这就是一对多。

一对一关系(外键不重复即可) ，距离如说明，一个身份证号,对应一个人的信息
       

多对多关系，  就好比专业和课程关系   学生选修课。我们需要再创建一个关系表,通过关系表来保存多条记录的关系。因为如果也使用外键来连接，那么会使表的结构变的十分复杂。

关联查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询 笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

按功能分类：

内连接  ：等值连接 非等值连接 自连接

外连接 ：左外连接 右外连接

等值连接

把满足了条件的两张表中的交集数据查询出来

语法：Select 结果 from 表1，表2 where 表1.column1 = 表2.column2

 

 如上图所示，等值连接，只将条件符合的列表查询了出来。

左外连接&右外连接

select 结果 from 表1 left join 表2 on 表1.column1 = 表2.column2

 

 如上图所示，左外连接不仅将符合条件的查询了出来，也将左表，即student表的所有项查询了出来。

那么右外连接同此情况。

select 结果 from 表1 right join 表2 on 表1.column1 = 表2.column2

 非等值连接

 内连接

 内连接通常用用作表中的各个等级关系的嵌套。如省的id套市的id，市的id又套区的id。

嵌套完就可以吧指定省份的相应城市，指定城市的相应区级地方显示出来。

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或 外查询.

分类：

按子查询出现的位置：

select后面：支持标量子查询

from后面：支持表子查询

where：支持标量子查询，列子查询

按功能、结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

表子查询（结果集一般为多行多列）