表与表之间一般存在三种关系，即一对一，一对多，多对多关系。

下面分别就三种关系讲解数据库相关设计的思路和思考过程；

(1)一对一关系

例如，下面的一张表，保存了人的相关信息，有男有女，要求查处所有的夫妻。

sql代码:

CREATE TABLE IF NOT EXISTS person(

id INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(10),

sex CHAR(1),

husband INT,

wife INT

);

INSERT INTO person VALUES(1,'小花','0',3,0);

INSERT INTO person VALUES(2,'小明','1',0,4);

INSERT INTO person VALUES(3,'张三','1',0,1);

INSERT INTO person VALUES(4,'小丽','0',2,0);

INSERT INTO person VALUES(5,'王五','1',0,0);

sql代码对应的person表:

 

从表中可以看出，小花和张三是夫妻，小明和小丽是夫妻。通常为了查询方便，需要两个表，但实际项目中为了省空间，通常只建一个表，要实现一对一查询，可以建立两个视图(虚表)

* 有个小细节提一下，视图只是个逻辑概念，并不独立占用物理内存，它依附于数据表，对视图进行修改时相应的表数据也会盖改动*

CREATE VIEW men AS (SELECT * FROM person WHERE sex='1');

CREATE VIEW women AS (SELECT * FROM person WHERE sex='0');

men视图:

 

women视图:

执行查询:

方式一(原始方法):SELECT men.sname AS husband,women.sname AS wife FROM men,women WHERE men.id = women.husband AND women.id = men.wife;

方式二(连接方法):SELECT men.sname AS husband,women.sname AS wife FROM men INNER JOIN women ON men.id = women.husband AND women.id = men.wife;

二、一对多关系

例如:一个人可以拥有多辆汽车，要求查询某个人拥有的所有车辆。

分析：这种情况其实也可以采用 一张表，但因为一个人可以拥有多辆汽车，如果采用一张表，会造成冗余信息过多。好的设计方式是，人和车辆分别单独建表，那么如何将两个表关联呢？有个巧妙的方法，在车辆的表中加个外键字段(人的编号)即可。

* (思路小结：’建两个表，一’方不动，’多’方添加一个外键字段)*

sql代码:

//建立人员表

CREATE TABLE human(

id VARCHAR(12) PRIMARY KEY,

sname VARCHAR(12),

age INT,

sex CHAR(1)

);

INSERT INTO human VALUES('H001','小王',27,'1');

INSERT INTO human VALUES('H002','小明',24,'1');

INSERT INTO human VALUES('H003','张慧',28,'0');

INSERT INTO human VALUES('H004','李小燕',35,'0');

INSERT INTO human VALUES('H005','王大拿',29,'1');

INSERT INTO human VALUES('H006','周强',36,'1');

//建立车辆信息表

CREATE TABLE car(

id VARCHAR(12) PRIMARY KEY,

mark VARCHAR(24),

price NUMERIC(6,2),

hid VARCHAR(12),

CONSTRAINT fk_human FOREIGN KEY(hid) REFERENCES human(id)

);

INSERT INTO car VALUES('C001','BMW',65.99,'H001');

INSERT INTO car VALUES('C002','BenZ',75.99,'H002');

INSERT INTO car VALUES('C003','Skoda',23.99,'H001');

INSERT INTO car VALUES('C004','Peugeot',20.99,'H003');

INSERT INTO car VALUES('C005','Porsche',295.99,'H004');

INSERT INTO car VALUES('C006','Honda',24.99,'H005');

INSERT INTO car VALUES('C007','Toyota',27.99,'H006');

INSERT INTO car VALUES('C008','Kia',18.99,'H002');

INSERT INTO car VALUES('C009','Bentley',309.99,'H005');sql代码对应的人员表:

 

sql代码对应的车辆信息表:

 

执行查询:SELECT human.sname AS 车主,car.mark AS 车辆 FROM human,car WHERE human.id = car.hid;

`SELECT human.sname AS 车主,car.mark AS 车辆 FROM human INNER JOIN car WHERE human.id = car.hid;

三、多对多关系`

例如：学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。

分析：这种方式可以按照类似一对多方式建表，但冗余信息太多，好的方式是实体和关系分离并单独建表，实体表为学生表和课程表，关系表为选修表，其中关系表采用联合主键的方式(由学生表主键和课程表主键组成)建表。

sql代码：

//建立学生表

CREATE TABLE student(

id VARCHAR(10) PRIMARY KEY,

sname VARCHAR(12),

age INT,

sex CHAR(1),

class VARCHAR(6)

);

INSERT INTO student VALUES('p0001','王军',20,1,'c101');

INSERT INTO student VALUES('p0002','张宇',21,1,'c101');

INSERT INTO student VALUES('p0003','刘飞',22,1,'c102');

INSERT INTO student VALUES('p0004','赵燕',18,0,'c103');

INSERT INTO student VALUES('p0005','曾婷',19,0,'c103');

INSERT INTO student VALUES('p0006','周慧',21,0,'c104');

INSERT INTO student VALUES('p0007','小红',23,0,'c104');

INSERT INTO student VALUES('p0008','杨晓',18,0,'c104');

INSERT INTO student VALUES('p0009','李杰',20,1,'c105');

INSERT INTO student VALUES('p0010','张良',22,1,'c105');

//建立课程表

CREATE TABLE course(

id VARCHAR(10) PRIMARY KEY,

sname VARCHAR(12),

credit NUMERIC(2,1),

teacher VARCHAR(12)

);

INSERT INTO course VALUES('C001','Java',3.5,'李老师');

INSERT INTO course VALUES('C002','高等数学',5.0,'赵老师');

INSERT INTO course VALUES('C003','JavaScript',3.5,'王老师');

INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师');

INSERT INTO course VALUES('C005','数据库',3.5,'廖老师');

INSERT INTO course VALUES('C006','操作系统',3.5,'张老师');

//建立选修表

CREATE TABLE sc(

sid VARCHAR(10),

cid VARCHAR(10)

);

联合主键：WHY?即为什么要使用联合主键？

像成绩单表，可以用科目号与学生的学号作为联合主键

也就是一个学生可以对应多个科目，一个科目也可以对应多个学生

但是一个学生对应的科目不能重复，一个科目对应的学生也不能重复

学号与科目号两个值可以唯一确定某个学生的唯一的一个科目

ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sid,cid);

ALTER TABLE sc ADD CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id);

ALTER TABLE sc ADD CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id);

这三句话可以在视图模式下创建表，即将第三个表的两个字段都设为主键，然后添加这两个字段指向另外两个表的主键。

INSERT INTO sc VALUES('S0001','C001');

INSERT INTO sc VALUES('S0001','C002');

INSERT INTO sc VALUES('S0001','C003');

INSERT INTO sc VALUES('S0002','C001');

INSERT INTO sc VALUES('S0002','C004');

INSERT INTO sc VALUES('S0003','C002');

INSERT INTO sc VALUES('S0003','C005');

INSERT INTO sc VALUES('S0004','C003');