多表设计_关联查询
● 数据库设计范式
● 1.第一范式(确保每列保持原子性)
满足列的原子性(不可拆分)
联系方式—》邮箱,电话,qq,可以被拆分
2. 第二范式就是要有主键,要求其他字段都依赖于主键。
• 没有主键就没有唯一性,没有唯一性在集合中就定位不到这行记录,所以要主键。
• 其他字段为什么要依赖于主键?因为不依赖于主键,就找不到他们。更重要的是,其他字段组成的这行记录和主键表示的是同一个东西,而主键是唯一的,它们只需要依赖于主键,也就成了唯一的。
第三范式:
一张表中存储一类信息,在一场表中关联其他表中的数据时,只需要关联主键列即可
确保每列都和主键列直接相关,而不是间接相关,要求一个数据库表中不包含已在其它表中包含的非主关键字信息
● 外键:引用另外一个数据表的某条记录。
外键列类型与主键列类型保持一致
数据表之间的关联/引用关系是依靠具体的主键(primary key)和外键(foreign key)建立起来的。
create table student(
id int not null auto_increment primary key,
num int,
name varchar(10)
majorid int,
CONSTRAINT 约束名 foreign key(majorid ) references major(id));
-- 外键 有两种情况1.不加外键约束
可以任意的对表数据进行操作,即使两个表中的数据对应不上也没有问题
2.添加外键约束
-- 添加外键约束后,两张表操作时,不能导致外键列和主键列对应的关系不成立
Alter table student add constraint fk_student_major_majorid foreign key(majorid) references major(id)
-- 删除外键约束
Alter table student drop foreign key fk_student_major_majorid
-- 学生选课 一个学生至少选择两个课程
-- 课程信息表 多对多关系设计
Create table course(
Id int primary key auto_increment,
Name varchar(20)
)
-- 添加一个学生选课表 学生和课程关系表 放一个学生学号外键,放一个课程外键
Create table student_course(
stunumber int,
courseid int
)
Alter table student_course add constraint fk_student_course_stunumber foreign key(stunumber) references student(number)
Alter table student_course add constraint fk_student_major_courseid foreign key(courseid) references course(id)
-- 关联查询 多表关联在一起查询
-- 学号 姓名,性别,电话,专业的名称
-- 信息来自于两张表
-- 关联时没有任何的限制,会产生笛卡尔乘积现象,这是我们不想要的结果
Select * from student,major
-- 关联查询 内关联 左外关联 右外关联
内关联
● 把满足了条件的两张表中的交集数据查询出来
语法:
Select 结果 from 表1,表2 where 表1.column1 = 表2.column2
-- Select number,name,gender,phone,name from student inner join major on majorid= id
Select
s.number,s.name,s.gender,s.phone,m.name
from student s
inner join major m on s.majorid = m.id
-- 把满足条件关联在一起
select * from student s,major m where s.majorid = m.id
-- 先产生一个笛卡尔乘积,然后再条件筛选
-- 左外连接 特点 即使不满足连接条件,也会把左边表中的所有数据查询出来Select
*
From
Student s left join major m on s.majorid = m.id
-- 右外连接 即使不满足连接条件,也要把右边表中的所有数据查询出来
Select
*
From
Student s right join major m on s.majorid = m.id
Select
Count(number),
m.name mname
from
student s right join major m on s.majorid = m.id
group by m.name