mysql学习指南 - 一对多、多对多关系表设计

– 一对多关系，一个部门有多个员工
– 新建 department 表
CREATE TABLE department (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR (45) NOT NULL,
PRIMARY KEY (id)
);

– 新建 employee 表
CREATE TABLE employee (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45) NOT NULL,
department_id INT NULL,
PRIMARY KEY(id),
INDEX department_id_idx (department_id),
CONSTRAINT department_id
FOREIGN KEY(department_id)
REFERENCES department(id)
ON DELETE SET NULL
ON UPDATE SET NULL
);

– 插入数据
INSERT INTO department (id, name)
VALUES
(1, ‘人事部’),
(2, ‘财务部’),
(3, ‘市场部’),
(4, ‘技术部’),
(5, ‘销售部’),
(6, ‘客服部’),
(7, ‘采购部’),
(8, ‘行政部’),
(9, ‘品控部’),
(10, ‘研发部’);

SELECT * FROM department;

INSERT INTO employee (id, name, department_id)
VALUES
(1, ‘张三’, 1),
(2, ‘李四’, 2),
(3, ‘王五’, 3),
(4, ‘赵六’, 4),
(5, ‘钱七’, 5),
(6, ‘孙八’, 5),
(7, ‘周九’, 5),
(8, ‘吴十’, 8),
(9, ‘郑十一’, 9),
(10, ‘王十二’, 10);

SELECT * FROM employee;

– 通过 join on 关联查询 id 为 5 的部门的所有的员工
SELECT * FROM department JOIN employee ON department.id = employee.department_id WHERE department.id = 5;

SELECT * FROM department LEFT JOIN employee ON department.id = employee.department_id;

– 所有员工都有部门，所以和 inner join 结果一样
select * from department RIGHT join employee on department.id = employee.department_id;

– 多对多关系，文章和标签
– 文章一个表、标签一个表、这两个表都不保存外键，然后添加一个中间表来保存双方的外键

– 创建 article 表
CREATE TABLE article (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(50) NOT NULL,
content TEXT NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO article (title, content)
VALUES
(‘文章1’, ‘这是文章1的内容。’),
(‘文章2’, ‘这是文章2的内容。’),
(‘文章3’, ‘这是文章3的内容。’),
(‘文章4’, ‘这是文章4的内容。’),
(‘文章5’, ‘这是文章5的内容。’);

– 创建 tag 表
CREATE TABLE tag (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO tag (name)
VALUES
(‘标签1’),
(‘标签2’),
(‘标签3’),
(‘标签4’),
(‘标签5’);

– 创建中间表 article_tag
CREATE TABLE article_tag (
article_id INT NOT NULL,
tag_id INT NOT NULL,
PRIMARY KEY (article_id,tag_id),
INDEX tag_id_idx (tag_id),
CONSTRAINT article_id
FOREIGN KEY (article_id)
REFERENCES article (id)
ON DELETE CASCADE
on UPDATE CASCADE,
CONSTRAINT tag_id
FOREIGN KEY (tag_id)
REFERENCES tag (id)
ON DELETE CASCADE
on UPDATE CASCADE
);

– 插入数据
INSERT INTO article_tag (article_id, tag_id)
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 2),
(2, 3),
(2, 4),
(3, 3),
(3, 4),
(3, 5),
(4, 4),
(4, 5),
(4, 1),
(5, 5),
(5, 1),
(5, 2);

– as 作为表面或者列名可以 省略, 别名含特殊字符时建议加引号
SELECT * FROM article a JOIN article_tag at ON a.id = at.article_id
JOIN tag t ON t.id = at.tag_id
WHERE a.id = 1;

– 多对多 关联查询
SELECT
t.NAME AS 标签名,
a.title AS 文章标题
FROM
article a
JOIN article_tag at ON a.id = at.article_id
JOIN tag t ON t.id = at.tag_id
WHERE
a.id = 1;