Mysql主键、外键、冗余——多表查询

目录

        数据冗余
        数据完整性
        主键
        外键
        建立存在外键的表
        根据需求初始化数据
 

数据冗余

        在数据库系统中,数据重复的现象就是数据冗余,例如,在企业员工信息数据表中,使用如下表列出的数据库来记录员工信息.

        

         两条数据都出现了'人事管理部',在数据库中我们就认为是数据冗余,因为存在重复的数据,有简化的必要.

        减少数据冗余最常见的方法时分类储存,如下表:

民族编码表:

编码编码表:

        通过这样的方法,可以减少数据的冗余,但是会增加数据查找的复杂性.例如:如果要查找民族为"汉族"的员工信息,原来只需要查一个表,现在就需要检索,然后还要根据部门编码检索部门编码表,这样无疑增加了查找的复杂性,降低了效率,因此在数据库中,通常允许有必要的冗余.

数据的完整性

        数据的完整性是指数据库中的数据的准确性.如果两个或者多个表由于其存在的信息相互关联,那么只要修改了其中一个表,与之关联的所有表都要做出相应的修改,如果不这么做,储存的数据就会不在准确,也就是说,失去了数据的完整性.

        数据准确性是通过数据表的设计和约束来实现的.例如:在储存学生信息的表中,如果允许任意输入学员信息的话,那么可以重复输入同一个学员信息;如果不对表中储存的年龄信息加入限制,那么学员信息的年龄可能会出现负数或一个很大的数字,这样的数据都不具备完整性.

        为了实现数据完整性,数据库需要做如下两个方面的工作:

        (1)检验每行数据是否符合要求;

        (2)检验每列数据是否符合要求;

        为了实现上述需求,MySQL提供了一定类型的约束:

        (1)实体完整性约束

        实体完整性要求表中每一行数据都反映不同的实体,不能存在相同的数据行.通过主键或标识列可完成.

        (2)域完整性约束

        域完整性指的是给定列输入的有效性.通过限制数据类型,检查约束,输入格式,外键约束,默认值,非空约束等多种方法,可以实现表的域完整性.

        (3)引用完整性约束

        在输入或删除数据时,引用完整性约束来保持表之间定义的关系.

        例如:在管理学员信息的时候,一个表用来储存学员的信息,另一个表用来储存考试成绩的详细情况,并且考试成绩表中的一列数值就是学员信息表中的编号,用来表示学员的信息,如下图: 

主键(Primary key)

        如果在表中存储了很多行数据,就会引发这样的问题:如果判断表中没有重复的数据行?如果判断一个学生的信息没有被输入两次?

        这就需要一个列,这个列的值用来唯一标识表中的每一行,用于强制表的实体完整性,这样的列定义为表的主键.

外键

        一般情况下,学生的信息和学生的考试成绩存放在不同的数据表中.在成绩表中,可以储存一行学生的学号来表示哪个学生的考试成绩,这时又引发了一个问题,如果在成绩表中输入的学号根本不存在怎么办?

        这个时候,就应当建立一种"引用"的关系,确保"从表"中的某个数据项在"主表"中必须存在,避免发生以上错误.

        "外键"就是用来达到这个目的的,它是相对于主键而言,就是从表中对应的主表列,在从表中称呼为外键或者引用键,它的值要求与主表的主键或者唯一键相对应,外键用来强制引用完整性,一个表可以有多个外键.

        

建立存在外键的表

        在前面讲了关于主键和外键的知识,现在我们就来通过实际操作,用图形界面根据(Navicat)和SQL语句创建主键表.

        需求:

 

创建学生信息表

 

        如果使用SQL语句创建:

create table student(
studentNo varchar(10) not null primary key,
LoginPwd varchar(20),
studentName varchar(64),
Sex varchar(2),
phone varchar(25),
address varchar(200),
emaill varchar(200)
);

创建科目信息表

create table subject(
sid int(11)not null primary key,
subjectName varchar(20)
);

创建成绩信息表

create table result(
id int(10)not null primary key,
studentNo varchar(10),
sid int(10),
score float(10),
examDate datetime
);

        到目前为止,还没有为成绩信息表创建外键,现在看看这么创建.

 

        外键的名称是自己取的,要求以fk_开头.

        通过代码创建外键:

ALTER TABLE ch07.result

ADD CONSTRAINT fk_student_no FOREIGN KEY(studentNo) REFERENCES ch07.student(studentNo),

ADD CONSTRAINT fk_subjectid FOREIGN KEY(sid) REFEENCES ch07.subject(sid);

        到现在,主键表和外键表都已经创建完成了.

根据需求初始化数据

        学员信息表

        通过insert语句给student表中插入上面的数据.

        

 

        通过insert语句给subject表添加数据

 

         通过insert语句给result表插入数据.