多表设计-关联查询
为什么需要多表设计?
多表设计的目的就是为了消除冗余的数据,将一张表拆分成为多张表,或者将多张表中的共同数据提取到一张表中.
举个例子,有如下两张表
编号
姓名
民族
国籍
1
张三
汉族
中国
2
李四
汉族
中国
编号
省份
国籍
1
陕西
中国
2
广州
中国
这两张表中就存在着大量的冗余,如民族,国籍,若是我们对其进行多表设计就可以变为
民族表
民族编号
民族
1
汉族
2
回族
国家表
国家编号
国籍
1
中国
2
巴基斯坦
而最上面的两张表就可以变为
编号
姓名
民族编号
国家编号
1
张三
1
1
2
李四
1
1
编号
省份
国家编号
1
西安
1
1
广州
1
通过这样的方法我们可以将重复的冗余数据消除掉,并且可以做到对一个数据重复利用.
数据库设计范式
1.为了建立冗余较小, 结构合理的数据库,设计数据库时必须遵循一定的规则, 在关系型数据库中这种规则也被称作时范式. 范式是符合某一种设计要求的总结.
2.在目前关系型数据库中有6种范式:
(1)第一范式(1NF);
(2)第二范式(2NF);
(3)第三范式(3NF);
(4)巴斯-科德范式(BCNF);
(5)第四范式(4NF);
(6)第五范式(5NF), 又称完美范式.
3.满足最低要求的范式是第一范式(1NF), 在第一范式的基础上进一步满足更多的规范要求就被称为第二范式(2NF), 其余范式依次类推. 一般来说, 数据库只需要第三范式(3NF)就行了.
第一范式(1NF)
第一范式是最基本的范式, 要确保数据库表中的每一列都保持原子性, 如果数据库表中的所有字段值都是不可分解的原子值, 就说明该数据库表满足了第一范式.
举个例子
编号
姓名
联系方式
1
王麻子
邮箱/电话/qq
满足第一范式后的表结构
编号
姓名
邮箱
电话
1
王麻子
12345676@qq.com
1313131
2212321
第二范式(2NF)
1.第二范式就是要有主键, 要求其他字段都依赖于主键;
2.没有主键就没有唯一性,没有唯一性在集合中就定位不到这行记录, 所以要主键.
3.其他字段为什么要依赖于主键? 因为不依赖于主键, 就找不到他们. 更重要的是, 其他字段组成的这行记录和主键表达的是同一个东西, 而主键是唯一的, 它们只需要依赖于主键,也就成了唯一的.
第三范式(3NF)
第三范式就是要消除传递依赖, 方便理解, 可以看做是"消除冗余".
订单编号
数量
商品编号
商品名称
单价
订单金额
1000
2
2020
手机
2000
4000
商品编号
商品名称
单价
2020
手机
2000
订单编号
数量
订单金额
商品编号
1000
2
4000
2020
一般来说, 数据库只需要第三范式(3NF)就行了.
数据库实例
学生表:姓名,性别,手机号,年级编号,注册时间
年级表:年级编号,年级名称,年级介绍
课程表:课程编号,课程名称,课程介绍
表与表之间的几种关系:
1.一对一关联;
2.一对多关联;
3.多对一关联;
4.多对多关联.
-- 创建年级表
CREATE TABLE t_grade(
g_id INT PRIMARY KEY AUTO_INCREMENT COMMENT'年级编号',
g_name VARCHAR(10) COMMENT'年级名称',
g_desc VARCHAR(50) COMMENT'年级介绍'
)
-- 创建学生表
CREATE TABLE t_stu(
s_id INT PRIMARY KEY AUTO_INCREMENT COMMENT'学号',
s_name VARCHAR(10) COMMENT'姓名',
s_sex CHAR(1) DEFAULT'男' COMMENT'性别',
s_phone INT(11) COMMENT'电话',
s_g_id INT COMMENT'年级编号',
reg_time DATETIME COMMENT'注册时间'
)
介绍一下弱关联关系: 表结构本质上没有联系,表与表之间的关系是人为定义的,删除关联表中的数据对另一个没影响, 如上图的年级编号和年级表是一种弱关联关系.
外键
再说一下强关联关系, 给外键添加约束,强制让外键与对应的主键产生关联关系(外键,对应的都是另一个表中的主键)
1.外键: 引用另外一个数据表的某条记录.
2.外键列的数据类型应于主键列保持一致.
3.数据表之间的关联/引用关系是依靠具体的主键(primary key)和外键(foreign key)建立起来的.
建表时添加外键的语法:
create table 表名(
constraint 约束名 foreign key(外键列) references 主键表(主键列)
)添加外键约束语法:
alter table 表名
add [constraint 约束名(自己起的)]
foreign key(外键列)
references 关联表(主键)
删除外键语法:
alter table 表名 drop foreign key 外键约束名;
数据库实例
为上表添加外键
ALTER TABLE t_stu
ADD CONSTRAINT s_foreign_key
FOREIGN KEY(s_g_id)
REFERENCES t_grade(g_id);-- 创建课程表,多对多关系,一个学生对应多个课程,一个课程对应多个学生
CREATE TABLE t_course(
c_id INT PRIMARY KEY AUTO_INCREMENT COMMENT'课程号',
c_name VARCHAR(10) COMMENT'课程名',
c_desc VARCHAR(20) COMMENT'课程介绍'
)
-- 设计关系表来存储多个数据之间的关系
CREATE TABLE t_stu_course(
sc_id INT PRIMARY KEY AUTO_INCREMENT,
sc_stu_id INT,
sc_course_id INT,
CONSTRAINT stu_id_fk FOREIGN KEY(sc_stu_id) REFERENCES t_stu(s_id),
CONSTRAINT cou_id_fk FOREIGN KEY(sc_course_id) REFERENCES t_course(c_id)
)
注意
1.当主表中没有对应的记录时,不能将记录添加到从表
2.不能更改主表中的值而导致从表中的记录孤立
3.从表存在与主表对应的记录,不能从主表中删除该行
4.删除主表前,先删除从表
关联查询
概述
1.关联查询又称为多表查询, 当查询的字段来自于多个表时, 就会用到连接查询
2.笛卡尔乘积现象: 表1有m行, 表2有n行, 查询结果有m*n行
(1)发生原因: 没有有效的连接条件
(2)如何避免: 添加有效的连接条件
分类
内连接(inner join)
把满足条件的两张表中的交集数据查询出来
语法:
select 结果 from 表1,表2 where 表1.列1=表2.列2
SELECT s_id,s_name,s_sex,s_g_id,g_name,g_id
FROM t_stu,t_grade
WHERE s_g_id = g_id
1.等值连接
#等值连接
/*
语法
select 结果 from 表名 inner join 被连接的表名(外键表) on 外键=主键
*/
SELECT *
FROM t_stu
INNER JOIN t_grade ON s_g_id = g_id
2.非等值连接
#创建一个等级表
CREATE TABLE t_level(
l_name CHAR(1),
l_min_score INT,
l_max_score INT
)
/*非等值连接
语法
select 结果
from 表1
inner join 表2
on 表1.列1 between 表2.列2 and 表2.列3
*/
SELECT
s_name,
l_name
FROM
t_stu
INNER JOIN t_level
ON s_score BETWEEN l_min_score
AND l_max_score
3.自连接: 在一张表中建立连接关系
CREATE TABLE t_area(
a_id INT,
a_name VARCHAR(10),
a_father INT
)
#在一张表中建立连接关系
SELECT
t1.a_name,
t2.a_name
FROM
t_area t1
INNER JOIN t_area t2
ON t1.a_father = t2.a_id;
外连接
1.左外连接(left join)
/*
语法
select 结果
from 表1
left join 表2
on 表1.字段=表2.字段l;
不管表1的字段与表2的字段是否有连接,都会显示表1的所有信息
*/
SELECT *
FROM t_stu s
LEFT JOIN t_grade g
ON s.s_g_id = g.g_id;2.右外连接(right join)
/*
语法
select 结果
from 表1
right join 表2
on 表1.字段1=表2.字段2;
不管表1有没有和表2中的所有数据关联,都会显示出表2的所有
*/
SELECT *
FROM t_stu s
RIGHT JOIN t_grade g
ON s.s_g_id = g.g_id;交叉连接
-- 交叉连接 类似于内连接
SELECT *
FROM t_stu s
CROSS JOIN t_grade g
ON s.s_g_id = g.g_id;
