数据库表关系相关案例

一、一对一（一条记录对应一条记录）

看了很多网上的案例，现在汇总如下：

一张表的一条记录只能与另一张表的一条记录相对应，反之，另一张表的一条记录只能对应这张表的一条记录；

将常用的和不常用的字段分离，如果每次查询都是查询所有的记录会影响效率（分表设计），当数据量很大时会影响查询效率，所以将大字段的不常用的字段分离，可以减轻数据库的压力；

简单设计如下：

总表：

CREATE TABLE person(
  id INT PRIMARY KEY,
  NAME VARCHAR(10),
  sex CHAR(1),
  wife INT,
  husband INT
);

INSERT INTO person VALUES(1,'小花','0', 0,3);
INSERT INTO person VALUES(2,'玉芬','0', 0,4);
INSERT INTO person VALUES(3,'张三','1', 1,0);
INSERT INTO person VALUES(4,'李四','1', 2,0);
INSERT INTO person VALUES(5,'王五','0', 0,0);
INSERT INTO person VALUES(6,'张五','2', 0,0);

可以分解为如下两个表：

CREATE TABLE wife(
  id INT PRIMARY KEY,
  NAME VARCHAR(10), 
  sex CHAR(1)
);

CREATE TABLE husband(
  id INT PRIMARY KEY,
  NAME VARCHAR(10),
  sex CHAR(1),
  wid INT UNIQUE,
  CONSTRAINT husband_fk FOREIGN KEY(wid) REFERENCES wife(id)
); 

数据表间一对一关系的表现有两种，一种是外键关联（上面是外键关联），一种是主键关联，这样就确保一对一的关系了，如下：

CREATE TABLE woman(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50) NOT NULL,
sex VARCHAR(6) NOT NULL
);
--主键自增
INSERT INTO woman VALUES(0,'小红','女');
INSERT INTO woman VALUES(0,'小紫','女');
INSERT INTO woman VALUES(0,'小橙','女');
INSERT INTO woman VALUES(0,'小蓝','女');

CREATE TABLE man(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50) NOT NULL,
sex VARCHAR(6) NOT NULL,
wife_id INT
);
--主键自增
INSERT INTO man VALUES(0,'张三','男',1);
INSERT INTO man VALUES(0,'张飞','男',2);
INSERT INTO man VALUES(0,'张鱼','男',3);
INSERT INTO man VALUES(0,'张放','男',4);

SELECT w.name AS '妻子', m.name AS '丈夫'  FROM  woman w,man m WHERE w.id=m.wife_id

如果懒得建表可以建立两个视图（表的一个窗口）来处理

CREATE VIEW women AS SELECT * FROM person WHERE sex='0';
CREATE VIEW men AS SELECT * FROM person WHERE sex='1';

查询的话用（和查询表的命令语句一样）：

SELECT women.name AS 妻子,  men.name AS 丈夫  FROM women,men WHERE women.husband = men.id;
或
SELECT women.name AS 妻子,  men.name AS 丈夫  FROM women INNER JOIN  men ON women.husband = men.id;

二、一对多（一条记录对应多条记录）：

部门与员工

CREATE TABLE dep(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30)
);

INSERT INTO dep VALUES (0,'市场部');
INSERT INTO dep VALUES (0,'技术部');
INSERT INTO dep VALUES (0,'财务部');
INSERT INTO dep VALUES (0,'开发部');


CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
did INT,
FOREIGN KEY(did) REFERENCES dep(id) 
);

INSERT INTO  emp VALUES(0,'张三',1);
INSERT INTO  emp VALUES(0,'张飞',1);
INSERT INTO  emp VALUES(0,'张牛',1);
INSERT INTO  emp VALUES(0,'张鱼',1);

--一对多,一个部门对应多个员工
SELECT d.name AS '部门',e.name '员工' FROM dep d,emp e WHERE e.`did`=d.`id`;

 三、多对多的关系（多条记录对应多条记录）：

老师和学生表，一个老师教过很多学生，一个学生被很多老师教过，多对多成立；

CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
sex VARCHAR(6)
);

INSERT INTO teacher VALUES(0,'李明','男');
INSERT INTO teacher VALUES(0,'李黑','男');
INSERT INTO teacher VALUES(0,'李白','男');

DROP TABLE student;
CREATE TABLE student(
s_id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
sex VARCHAR(6)
);

INSERT INTO student VALUES(0,'杰克','男');
INSERT INTO student VALUES(0,'红克','男');
INSERT INTO student VALUES(0,'橙克','男');
INSERT INTO student VALUES(0,'蓝克','男');
INSERT INTO student VALUES(0,'绿克','男');


DROP TABLE teacher_student;

CREATE TABLE teacher_student(
id INT PRIMARY KEY AUTO_INCREMENT,
tid INT,
sid INT,
FOREIGN KEY(tid) REFERENCES teacher(t_id),
FOREIGN KEY(sid) REFERENCES student(s_id)
);

  
INSERT INTO teacher_student VALUES(0,1,1);
INSERT INTO teacher_student VALUES(0,1,2);
INSERT INTO teacher_student VALUES(0,1,3);
INSERT INTO teacher_student VALUES(0,1,4);

--查询tid为1的老师（李明）教过学生的姓名
SELECT s.`NAME` FROM student s   JOIN (SELECT sid FROM teacher_student WHERE tid=1) ts ON s.s_id=ts.sid;

--查询教过学生（sid=1）的老师都有谁
SELECT t.name FROM teacher t LEFT JOIN (SELECT tid FROM teacher_student WHERE sid=1)ts ON t.`t_id`=ts.tid

INSERT INTO teacher_student VALUES(0,2,1);
INSERT INTO teacher_student VALUES(0,3,1);

DELETE FROM teacher_student WHERE id=5