5 - 表的对应关系

表与表之间的关系:1对1，1对多，多对多

一、1对1

第三范式: 1方建主表(id为主键字段), 多方建外键字段(加unique)

 

CREATE TABLE man(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30)
);

 

CREATE TABLE woman(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30),
  husband VARCHAR(32) UNIQUE,
  CONSTRAINT wm_fk FOREIGN KEY(husband) REFERENCES man(id)
);

 注：husband这里要加unique约束，不加则是一对多关系

加unique才是1对1关系

在man中插入数据

INSERT INTO man VALUES('1','小明');
INSERT INTO man VALUES('2','小聪');
INSERT INTO man VALUES('3','老王');

 

 

在woman中插入数据

INSERT INTO woman VALUES('1','小花','2');
INSERT INTO woman VALUES('2','小静','1');
INSERT INTO woman VALUES('3','小红','1');//Error:违反1对1
INSERT INTO woman VALUES('3','小红','10');//Error:违反外键--主表必须存在该外键值

INSERT INTO man VALUES('10','王六');
INSERT INTO woman VALUES('3','小玉','10');//OK

受unique约束，husband不能重复不然

查询夫妻信息

 

SELECT m.name AS 丈夫, w.name AS 妻子 FROM man AS m INNER JOIN woman AS w ON m.id=w.husband;

  

 

二、1对多

第三范式: 1方建主表(id为主键字段), 多方建外键字段(pid--参考主表的主键id，不加unique)

创建person2表

CREATE TABLE person2(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30),
  sex CHAR(1)
);

 

创建car表

CREATE TABLE car(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30),
  price NUMERIC(10,2),
  pid VARCHAR(32),
  CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES person2(id)
);

 

 

往person2表中插入数据

INSERT INTO person2 VALUES('P01','Jack','1');
INSERT INTO person2 VALUES('P02','Tom','1');
INSERT INTO person2 VALUES('P03','Rose','0');

 

 

往car表中插入数据

INSERT INTO car VALUES('C001','BMW',30,'P01');
INSERT INTO car VALUES('C002','BEnZ',40,'P01');
INSERT INTO car VALUES('C003','Audi',40,'P01');
INSERT INTO car VALUES('C004','QQ',5.5,'P02');

外键字段值可以为“NULL”表示该车还未卖出

 

INSERT INTO car(id,NAME,price) VALUES('C005','ABC',10);
INSERT INTO car(id,NAME,price) VALUES('C006','BCD',10);

 

查询哪些人有哪些车

SELECT person2.NAME,car.name FROM person2 INNER JOIN car ON person2.id=car.pid;

 

三、多对多（ 3个表= 2个实体表 + 1个关系表 )

※第三范式: 两个实体都建成独立的主表, 另外再单独建一个关系表（采用联合主键）

1、分别创建两个实体表(没有外键，但有自己的主键, 没有冗余信息)

 

学生表

CREATE TABLE stud(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30)
);

 

 

 课程表

CREATE TABLE ject(
  id VARCHAR(32) PRIMARY KEY,
  NAME VARCHAR(30)
);

 

 

 2、另外补建一个关系表

CREATE TABLE sj(
  studid VARCHAR(32) NOT NULL,
  jectid VARCHAR(32)
);

 

 

注意，要先建联合主键，再添加外键。顺序不能反了。

 创建联合主键

ALTER TABLE sj ADD CONSTRAINT sj_pk PRIMARY KEY(studid,jectid);

 

 添加外键

ALTER TABLE sj ADD CONSTRAINT sj_fk1 FOREIGN KEY(studid) REFERENCES stud(id);
ALTER TABLE sj ADD CONSTRAINT sj_fk2 FOREIGN KEY(jectid) REFERENCES ject(id);

 

 

添加学生数据

INSERT INTO stud VALUES('S001','Jack');
INSERT INTO stud VALUES('S002','Rose');
INSERT INTO stud VALUES('S003','Tom');

 

 

添加课表数据 

INSERT INTO ject VALUES('J001','Java');
INSERT INTO ject VALUES('J002','Oracle');
INSERT INTO ject VALUES('J003','XML');
INSERT INTO ject VALUES('J004','JSP');
INSERT INTO ject VALUES('J005','Game');

 

 

添加关系表数据

INSERT INTO sj VALUES('S001','J001');
INSERT INTO sj VALUES('S001','J003');
INSERT INTO sj VALUES('S001','J004');
INSERT INTO sj VALUES('S002','J002');
INSERT INTO sj VALUES('S002','J003');
INSERT INTO sj VALUES('S002','J004');

 

 

 查询哪些人选了哪些课

SELECT stud.name, ject.NAME FROM stud INNER JOIN sj ON stud.id=sj.studid INNER JOIN ject ON ject.id=sj.jectid;