MySQL多表关系

1.多表关系

一对多

案例：部门和员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方主键

-- 创建部门表
CREATE TABLE dept (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
​
-- 创建员工表
CREATE TABLE emp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES dept(id)
);
​
-- 向部门表插入数据
INSERT INTO dept (name) VALUES
('研发部'),
('市场部'),
('财务部'),
('销售部');
​
-- 向员工表插入数据
INSERT INTO emp (name, age, dept_id) VALUES
('张无忌', 20, 1),
('杨逍', 33, 2),
('赵敏', 18, 3),
('常遇春', 43, 4);

多对多

案例：学生与课程的关系

关系：一个学生可以选多门课程，一个课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别管理两方主键

-- 创建学生表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY comment '主键ID',
    name VARCHAR(50) NOT NULL comment '姓名',
    no VARCHAR(20) NOT NULL UNIQUE comment '学号'
) comment '学生表';
​
-- 创建课程表
CREATE TABLE course (
    id INT AUTO_INCREMENT PRIMARY KEY comment '主键ID',
    name VARCHAR(50) NOT NULL comment '课程名'
) comment '课程表';
​
-- 创建学生课程关系表
CREATE TABLE student_course (
    id INT AUTO_INCREMENT PRIMARY KEY comment '主键ID',
    student_id INT comment '学生ID',
    course_id INT comment '课程ID',
    FOREIGN KEY (student_id) REFERENCES student(id) ,
    FOREIGN KEY (course_id) REFERENCES course(id) 
);
​
-- 向学生表插入数据
INSERT INTO student (name, no) VALUES
('黛绮丝', '2000100101'),
('谢逊', '2000100102'),
('殷天正', '2000100103'),
('韦一笑', '2000100104');
​
-- 向课程表插入数据
INSERT INTO course (name) VALUES
('Java'),
('PHP'),
('MySQL'),
('Hadoop');
​
-- 向学生课程关系表插入数据
INSERT INTO student_course (student_id, course_id) VALUES
(1, 1),
(1, 2),
(2, 2),
(1, 3),
(3, 3),
(2, 4),
(4, 1),
(2, 1),
(4, 4);

一对一

案例：用户与用户详细信息的关系

关系：一对一用多表拆分把基础字段放在一张表，另外字段放另一张表，提升操作效率

实现：在任意一方加入外键，关联另一方的主键，且设外键为唯一(unique)

-- 创建用户基本信息表
CREATE TABLE tb_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    gender TINYINT NOT NULL,
    phone VARCHAR(15) NOT NULL
);
​
-- 创建用户教育信息表
CREATE TABLE tb_user_edu (
    id INT AUTO_INCREMENT PRIMARY KEY,
    degree VARCHAR(20) NOT NULL,
    major VARCHAR(50) NOT NULL,
    primaryschool VARCHAR(100) NOT NULL,
    middleschool VARCHAR(100) NOT NULL,
    university VARCHAR(100) NOT NULL,
    FOREIGN KEY (id) REFERENCES tb_user(id)
);
​
-- 向用户基本信息表插入数据
INSERT INTO tb_user (name, age, gender, phone) VALUES
('黄渤', 45, 1, '18800001111'),
('冰冰', 35, 2, '18800002222'),
('马云', 55, 1, '18800008888'),
('李彦宏', 50, 1, '18800009999');
​
-- 向用户教育信息表插入数据
INSERT INTO tb_user_edu (degree, major, primaryschool, middleschool, university) VALUES
('本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院'),
('硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院'),
('本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学'),
('本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学');