MySQL数据库建模实践：从简单表创建到复杂关系构建

原创

51执沐 2025-04-15 15:02:16 ©著作权

文章标签 数据库 mysql android 数据主键 文章分类 Html/CSS 前端开发

©著作权归作者所有：来自51CTO博客作者51执沐的原创作品，请联系作者获取转载授权，否则将追究法律责任

摘要：本文通过一系列MySQL建表及数据插入示例，深入展示了数据库建模过程。涵盖员工与部门的一对多关系、用户与身份证的一对一关系、学生与课程的多对多关系，以及餐饮业务中的分类、菜品、套餐及其关联关系的建模，为数据库设计提供了实践参考。

关键词：MySQL；数据库建模；表关系；数据插入

参考资料：黑马程序员建议去看一下视频，清晰很多↑（2023javaweb教程 day07mysql）

一、引言

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多(多对一)
多对多
一对一

二、多对一关系建模：员工与部门

2.1 员工表设计

创建名为 tb_emp 的员工表，用于存储员工的详细信息，包括唯一标识 id、用户名 username、密码 password、姓名 name、性别 gender、图像 image、职位 job、入职时间 entrydate、归属部门ID dept_id 以及创建和修改时间 create_time、update_time。

CREATE TABLE tb_emp (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(32) DEFAULT '123456' COMMENT '密码',
    name VARCHAR(10) NOT NULL COMMENT '姓名',
    gender TINYINT UNSIGNED NOT NULL COMMENT '性别, 说明: 1 男, 2 女',
    image VARCHAR(300) COMMENT '图像',
    job TINYINT UNSIGNED COMMENT '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
    entrydate DATE COMMENT '入职时间',
    dept_id INT UNSIGNED COMMENT '归属的部门ID',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '修改时间'
) COMMENT '员工表';

2.2 部门表设计

创建 tb_dept 表，用于存储部门信息，包含部门唯一标识 id、部门名称 name 以及创建和修改时间 create_time、update_time。

CREATE TABLE tb_dept (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '部门名称',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '修改时间'
) COMMENT '部门表';

2.3 插入测试数据

向 tb_dept 表插入多个部门数据，同时向 tb_emp 表插入员工数据，并通过 dept_id 关联到相应部门，体现一对多关系。

-- 插入部门数据
INSERT INTO tb_dept (id, name, create_time, update_time) VALUES
(1, '学工部', NOW(), NOW()), (2, '教研部', NOW(), NOW()), (3, '咨询部', NOW(), NOW()),
(4, '就业部', NOW(), NOW()), (5, '人事部', NOW(), NOW());

-- 插入员工数据
INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time) VALUES
(1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000 - 01 - 01', 2, NOW(), NOW()),
(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015 - 01 - 01', 2, NOW(), NOW()),
-- 省略其他员工数据...
(17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015 - 03 - 21', NULL, NOW(), NOW());

三、一对一关系建模：用户与身份证

3.1 用户表设计

创建 tb_user 表，记录用户基本信息，包括 id、姓名 name、性别 gender、手机号 phone 和学历 degree。

CREATE TABLE tb_user (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name VARCHAR(10) NOT NULL COMMENT '姓名',
    gender TINYINT UNSIGNED NOT NULL COMMENT '性别, 1 男  2 女',
    phone CHAR(11) COMMENT '手机号',
    degree VARCHAR(10) COMMENT '学历'
) COMMENT '用户信息表';

3.2 身份证表设计

创建 tb_user_card 表，存储用户身份证相关信息，通过 user_id 与 tb_user 表建立一对一关联，并设置外键约束。

CREATE TABLE tb_user_card (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    nationality VARCHAR(10) NOT NULL COMMENT '民族',
    birthday DATE NOT NULL COMMENT '生日',
    idcard CHAR(18) NOT NULL COMMENT '身份证号',
    issued VARCHAR(20) NOT NULL COMMENT '签发机关',
    expire_begin DATE NOT NULL COMMENT '有效期限 - 开始',
    expire_end DATE COMMENT '有效期限 - 结束',
    user_id INT UNSIGNED NOT NULL UNIQUE COMMENT '用户ID',
    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES tb_user(id)
) COMMENT '用户信息表';

3.3 插入测试数据

向 tb_user 和 tb_user_card 表插入对应数据，展示一对一关系。

INSERT INTO tb_user VALUES (1, '白眉鹰王', 1, '18812340001', '初中'), (2, '青翼蝠王', 1, '18812340002', '大专'), (3, '金毛狮王', 1, '18812340003', '初中'), (4, '紫衫龙王', 2, '18812340004', '硕士');

INSERT INTO tb_user_card VALUES (1, '汉', '1960 - 11 - 06', '100000100000100001', '朝阳区公安局', '2000 - 06 - 10', NULL, 1),
(2, '汉', '1971 - 11 - 06', '100000100000100002', '静安区公安局', '2005 - 06 - 10', '2025 - 06 - 10', 2),
(3, '汉', '1963 - 11 - 06', '100000100000100003', '昌平区公安局', '2006 - 06 - 10', NULL, 3),
(4, '回', '1980 - 11 - 06', '100000100000100004', '海淀区公安局', '2008 - 06 - 10', '2028 - 06 - 10', 4);

四、多对多关系建模：学生与课程

4.1 学生表设计

创建 tb_student 表，记录学生的基本信息，如 id、姓名 name 和学号 no。

CREATE TABLE tb_student (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '姓名',
    no VARCHAR(10) COMMENT '学号'
) COMMENT '学生表';

4.2 课程表设计

创建 tb_course 表，用于存储课程信息，包含 id 和课程名称 name。

CREATE TABLE tb_course (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '课程名称'
) COMMENT '课程表';

4.3 中间表设计

创建 tb_student_course 中间表，通过 student_id 和 course_id 分别关联学生表和课程表，以体现多对多关系，并设置外键约束。

CREATE TABLE tb_student_course (
    id INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    student_id INT NOT NULL COMMENT '学生ID',
    course_id INT NOT NULL COMMENT '课程ID',
    CONSTRAINT fk_courseid FOREIGN KEY (course_id) REFERENCES tb_course (id),
    CONSTRAINT fk_studentid FOREIGN KEY (student_id) REFERENCES tb_student (id)
) COMMENT '学生课程中间表';

4.4 插入测试数据

向 tb_student、tb_course 和 tb_student_course 表插入数据，展示学生与课程的多对多关系。

INSERT INTO tb_student (name, no) VALUES ('黛绮丝', '2000100101'), ('谢逊', '2000100102'), ('殷天正', '2000100103'), ('韦一笑', '2000100104');

INSERT INTO tb_course (name) VALUES ('Java'), ('PHP'), ('MySQL'), ('Hadoop');

INSERT INTO tb_student_course (student_id, course_id) VALUES (1, 1), (1, 2), (1, 3), (2, 2), (2, 3), (3, 4);

五、餐饮业务案例建模

5.1 分类表设计

MySQL数据库建模实践：从简单表创建到复杂关系构建_android

创建 category 表，用于管理菜品和套餐的分类信息，包括分类 id、名称 name、类型 type、顺序 sort、状态 status 以及创建和更新时间 create_time、update_time。

CREATE TABLE category (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '分类名称',
    type TINYINT UNSIGNED NOT NULL COMMENT '类型 1 菜品分类 2 套餐分类',
    sort TINYINT UNSIGNED NOT NULL COMMENT '顺序',
    status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态 0 禁用，1 启用',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '更新时间'
) COMMENT '分类表';

5.2 菜品表设计

MySQL数据库建模实践：从简单表创建到复杂关系构建_数据库_02

创建 dish 表，存储菜品详细信息，通过 category_id 关联到相应分类，包括菜品 id、名称 name、分类ID category_id、价格 price、图片 image、描述 description、状态 status 以及创建和更新时间 create_time、update_time。

CREATE TABLE dish (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '菜品名称',
    category_id INT UNSIGNED NOT NULL COMMENT '菜品分类ID',
    price DECIMAL(8, 2) NOT NULL COMMENT '菜品价格',
    image VARCHAR(300) NOT NULL COMMENT '菜品图片',
    description VARCHAR(200) COMMENT '描述信息',
    status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态, 0 停售 1 起售',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '更新时间'
) COMMENT '菜品表';

5.3 套餐表设计

MySQL数据库建模实践：从简单表创建到复杂关系构建_数据库_03

创建 setmeal 表，用于管理套餐信息，通过 category_id 关联到相应分类，包括套餐 id、名称 name、分类ID category_id、价格 price、图片 image、描述 description、状态 status 以及创建和更新时间 create_time、update_time。

CREATE TABLE setmeal (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(20) NOT NULL UNIQUE COMMENT '套餐名称',
    category_id INT UNSIGNED NOT NULL COMMENT '分类id',
    price DECIMAL(8, 2) NOT NULL COMMENT '套餐价格',
    image VARCHAR(300) NOT NULL COMMENT '图片',
    description VARCHAR(200) COMMENT '描述信息',
    status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态 0 停售 1 起售',
    create_time DATETIME NOT NULL COMMENT '创建时间',
    update_time DATETIME NOT NULL COMMENT '更新时间'
) COMMENT '套餐';

5.4 套餐菜品关联表设计

MySQL数据库建模实践：从简单表创建到复杂关系构建_主键_04

创建 setmeal_dish 表，用于建立套餐与菜品之间的关联关系，包括关联记录 id、套餐ID setmeal_id、菜品ID dish_id 和份数 copies。

CREATE TABLE setmeal_dish (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    setmeal_id INT UNSIGNED NOT NULL COMMENT '套餐id ',
    dish_id INT UNSIGNED NOT NULL COMMENT '菜品id',
    copies TINYINT UNSIGNED NOT NULL COMMENT '份数'
) COMMENT '套餐菜品关系';