这里通过一个用户表和角色表关联的案例，向大家展示，项目中对于表与表之间关系的处理步骤

一、案例介绍

（1）user 用户表：

（2）role 角色表：

（3）用户与角色关联的关系：
用户与角色之间是多对多关系，我们通过 users_role 表来描述其关联，在实体类中 User 中存在 List，在Role中有 List.

二、用户与角色关联

用户与角色是多对多的关系，我们要建立它们之间的关系，只需要在中间表 user_role 插入数据即可
（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('S0001','王军',20,1,'c101');
INSERT INTO student VALUES('S0002','张宇',21,1,'c101');
INSERT INTO student VALUES('S0003','刘飞',22,1,'c102');
INSERT INTO student VALUES('S0004','赵燕',18,0,'c103');
INSERT INTO student VALUES('S0005','曾婷',19,0,'c103');
INSERT INTO student VALUES('S0006','周慧',21,0,'c104');
INSERT INTO student VALUES('S0007','小红',23,0,'c104');
INSERT INTO student VALUES('S0008','杨晓',18,0,'c104');
INSERT INTO student VALUES('S0009','李杰',20,1,'c105');
INSERT INTO student VALUES('S0010','张良',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)
);

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');
INSERT INTO sc VALUES('S0005','C001');
INSERT INTO sc VALUES('S0006','C004');
INSERT INTO sc VALUES('S0007','C002');
INSERT INTO sc VALUES('S0008','C003');
INSERT INTO sc VALUES('S0009','C001');
INSERT INTO sc VALUES('S0009','C005');

sql代码对应的学生表：

 
sql代码对应的课程表：

 
sql代码对应的选课表：

 
执行查询：(没有选课的学生信息和无学生选的课程信息) 

普通方式：

SELECT * FROM student WHERE student.id NOT IN (SELECT sid FROM sc);
SELECT * FROM course WHERE course.id NOT IN (SELECT cid FROM sc);

关联方式：

SELECT student.* FROM student LEFT JOIN sc ON student.id=sc.sid LEFT JOIN course ON course.id = sc.cid WHERE course.sname IS NULL;
SELECT course.* FROM course LEFT JOIN sc ON course.id=sc.cid LEFT JOIN student ON student.id = sc.sid WHERE student.sname IS NULL;

转自：https://blog.csdn.net/lengjinghk/article/details/52140276

多表关联：

多张数据表之间是可以有一定的关联关系，这种关联关系可以通过外键约束实现

多表的分类：

• 一对一
• 一对多
• 多对多

一对一：

一张表对应一张表

适用场景举例：

人和身份证。一个人只能有一个身份证，一个身份证只能对应一个人！

建表原则：

在任意一个表建立外键，去关联另外一个表的主键

演示：

-- 创建Person表 
CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,  			-- 主键id
	name VARCHAR(20)								-- 姓名
);

-- 添加数据
INSERT INTO person VALUES (NULL,'韩信'), (NULL,'李白');

-- 创建card表
CREATE TABLE card(
	id INT PRIMARY KEY AUTO_INCREMENT,				-- 主键id
	number VARCHAR(50),								-- 身份证号码
	pid INT UNIQUE,									-- 外键列
	CONSTRAINT cp FOREIGN KEY (pid) REFERENCES person(id) -- 外键关联主键
);

-- 添加数据
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

一对多：

一张表对应多张表

适用场景举例：

• 商品分类和商品。一个分类下可以有多个商品。

建表原则：

在多的一方，建立外键约束，来关联一的一方主键

演示：

-- 创建category表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO category VALUES (NULL,'手机数码'),(NULL,'电脑办公');

-- 创建product表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	cid INT,
	CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id)  -- 添加外键约束
);
-- 添加数据
INSERT INTO product VALUES (NULL,'华为P30',1),(NULL,'小米note3',1),
(NULL,'联想电脑',2),(NULL,'苹果电脑',2);

多对多：

多张表对应多张表

适用场景举例：

学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！

建表原则：

需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键

演示：

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO student VALUES (NULL,'韩信'),(NULL,'干将');

-- 创建course表
CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);

-- 添加数据
INSERT INTO course VALUES (NULL,'打野'),(NULL,'中路');

-- 创建中间表
CREATE TABLE stu_course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	sid INT, -- 用于和student表的id进行外键关联
	cid INT, -- 用于和course表的id进行外键关联
	CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
	CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 添加外键约束
);

-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);