python之SQL表的关系

1. 前言

实际中，一张表不能包含所有数据，因此实际数据库中会存在各种有关联的表。

2. 表之间的关系

表之间的关系为一对一关系，一对多关系，多对多关系。

• 一对一关系
  人和身份证关系。
  一个人只有一个身份证，一个身份证只对应一个人。
• 一对多关系
  省会和城市的关系。
  一个省会只有有很多城市，一个城市只有一个省会。
• 多对多关系
  比如课程表和学生表的关系。
  一个课程可有多个学生，一个学生可有多个课程。

为了更好的理清表之间的关系，可以适用PowerDesigner软件对表进行梳理，如：

3. 表创建

3.0 说明

• 在表的创建的时候，要有面向对象的思维，即将每一个表当作一个对象，只含有该对象的数据。
• 在创建表描述表之间的关系，采用外键进行描述。
• 用SQL创建表关系时，创建的是一行数据与主表主键之间的关系，主键是唯一的，反应的是“一”，如果可以多行数据对应同一个“一”，则是多对一关系；如果只能一行数据对用一个“一”，就是一对一关系。
• SQL语句只能反应一对多的关系。

3.1 一对一关系表

3.1.1 要求

• 外键位置：在其中任意一张表中都可以
• 外键要求：
  由于是一对一关系，即每一行的数据都是唯一的，因此作为的外键的字段一定要有唯一约束

3.1.2 表的创建

这里以身份证和人关系进行说明。。

• 第一种创建方式
  产生一个外键，关联人表中的id

-- person表
create table t_person(
	id int primary key,
	name varchar(100),
	age int
);

-- id表
create table t_idcard(
	card_id int primary key,
	create_time date,
	p_id int unique,
	foreign key (p_id) references t_person (id);
);

• 第二种创建方式
  利用主键唯一且非空的特性，某一张表中都存在主键id，将主键Id进行关联

-- person表
create table t_person(
	id int primary key,
	name varchar(100),
	age int
);

-- id表
create table t_idcard(
	id int primary key,
	card_id int primary key,
	create_time date,
	foreign key (id) references t_person (id);
);

3.2 一对多关系表

3.2.1 要求

• 外键位置：
  设一条表A中的数据对应多条表B中的数据，因为外键是可以重复的，主键不能重复。因此，外键的位置应该放在表B中。
• 外键要求：
  由于是一对多关系，则不需要表B中的外键为唯一。

3.2.2 实例

这里以省会和城市关系为例

-- 省会表
create table t_captal(
	id int primary key, 
	name varchar(100)
);

-- 城市表
create table city(
	id int primary key,
	name varchar(100),
	p_captal int,
	foreign key (p_captal) references t_captal (id); 
);

3.3 多对多关系表

3.3.1 要求

• 外键位置：
  表A和表B存在多对多关系，由于在SQL语句中只能反应出一对多的关系。所以需要另建一张的新的表，作为中间表，生成两个外键，关联A表和B表。
  中间表的生成，应该这样思考，中间表反映的是表A中的一行数据和表B中的一行数据对应关系。

• 外键要求：
  不需要唯一。

3.3.2 案例

以学生表和课程表为例：

-- 学生表
create table t_stu(
	id int primary key,
	name varchar(100)
);

-- 课程表
create table t_subj(
	id int primary key, 
	name varchar(100)
);

-- 中间表，一个学生和一个课程，中间表可以为成绩
create table t_score(
	id int primary key,
	score int,
	p_stu int,
	p_subj int,
	foreign key (p_stu) references t_stu (id),
	foreign key (p_subj) references t_subj (id)
);