Quartz 5

MySQL 数据库 3（Day51）

Properties1
tags来源, 数据库, mysql, sql, 表关系, 外键, 查询, data

6分钟阅读

MySQL 数据库 3（Day51）

主题：表与表三种关系（多对一/多对多/一对一）、外键（Foreign Key）与级联更新/删除、ALTER TABLE 修改表、INSERT … SELECT 复制数据、记录增删改（INSERT/UPDATE/DELETE）、单表查询结构与 WHERE 条件过滤（比较运算/AND/OR/BETWEEN/IN/LIKE/REGEXP）

结构总览

MySQL 数据库 3
  ├─ 表与表三种关系
  │    ├─ 多对一：单向外键（student.class_id → class.id）
  │    ├─ 多对多：中间表 + 两个外键（book2author）
  │    └─ 一对一：外键 + UNIQUE（customer_id）
  ├─ 外键与级联
  │    ├─ FOREIGN KEY ... REFERENCES
  │    ├─ ON UPDATE CASCADE / ON DELETE CASCADE
  │    └─ 常见错误：插入不存在父表 id、级联未配置
  ├─ 修改表与复制表
  │    ├─ ALTER TABLE 修改表结构
  │    └─ INSERT ... SELECT 跨表/跨库复制数据
  ├─ 记录详细操作
  │    ├─ INSERT（增加）
  │    ├─ UPDATE（修改）
  │    └─ DELETE（删除）
  └─ 单表查询
       ├─ SELECT 结构：DISTINCT / WHERE / GROUP BY / HAVING / ORDER BY / LIMIT
       ├─ CASE WHEN 条件分支
       ├─ 比较运算：= > >= !=
       ├─ AND / OR / BETWEEN / IN
       ├─ LIKE 模糊匹配（% 任意长度、_ 单字符）
       └─ REGEXP 正则匹配（^开头、$结尾）

关键要点

三种表关系：

关系实现方式示例
多对一（Many-to-One）单向外键多个学生通过 class_id 指向一个班级
多对多（Many-to-Many）中间表 + 两个外键book2author 连接 book 与 author
一对一（One-to-One）外键 + UNIQUE 约束customer_id 唯一，一个客户最多对应一条学生记录

多对一（外键）： 学生表通过 class_id 外键关联班级表 class.id。

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(16),
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES class(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
  • 外键保证引用完整性：插入不存在的 class_id=4444 会报错；ON UPDATE CASCADE 级联更新（父表 id 修改时子表自动跟随）、ON DELETE CASCADE 级联删除（父表记录删除时子表关联记录一并删除）。

多对多（中间表）： book2author 同时保存 book_id 与 author_id 两个外键，各自级联，从而把多对多拆成两个多对一。

一对一（外键 + UNIQUE）： customer_id INT UNIQUE + 外键引用 customer(id)，保证同一个客户在 student 表中最多出现一次。

修改表与复制表： ALTER TABLE 修改表结构；跨库复制数据用 INSERT ... SELECT：INSERT db3.test2(id, name, email, reg_time) SELECT id, name, email, reg_time FROM db2.test1;。排错：源/目标字段数量不一致、目标表不存在、库名表名写错。

记录操作： INSERT（增加）、UPDATE（修改）、DELETE（删除）三类。

单表查询结构：

SELECT DISTINCT 字段1, 字段2, ...
FROM 库.表
WHERE 过滤条件
GROUP BY 分组字段
HAVING 过滤条件
ORDER BY 排序字段
LIMIT 条数;

CASE 条件分支： 根据字段值生成新列。

SELECT id,
    (CASE
        WHEN name = 'egon' THEN CONCAT(name, '_vip')
        WHEN name = 'alex' THEN CONCAT(name, '_BIGSB')
        ELSE name
    END) AS new_name,
    age
FROM employee;

WHERE 过滤条件对比：

语法作用示例
比较运算等值/大小/不等id = 3、id > 3、id >= 3、id != 3
AND多个条件同时成立id > 3 AND id < 5
OR满足一个即可id < 3 OR id > 10
BETWEEN范围闭区间id BETWEEN 3 AND 5（等价 id >= 3 AND id <= 5）
IN多个指定值id IN (3, 5, 7)（等价多个 OR）
LIKE模糊匹配name LIKE 'ji%'（% 任意长度）、name LIKE 'ji_'（_ 单字符）、'__' 两字符
REGEXP正则匹配name REGEXP '^jin'（以 jin 开头）、name REGEXP 'n$'（以 n 结尾）

当日总结

  • 表关系三种：多对一（单向外键）、多对多（中间表两个外键）、一对一（外键 + UNIQUE）
  • 外键配合 ON UPDATE/DELETE CASCADE 实现级联更新与级联删除
  • ALTER TABLE 修改表、INSERT ... SELECT 复制数据
  • 记录操作三类：INSERT / UPDATE / DELETE
  • 单表查询完整结构：SELECT DISTINCT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
  • WHERE 过滤：比较运算、AND、OR、BETWEEN、IN、LIKE（%/ _）、REGEXP（

相关页面

最近更新

关系图谱

反向链接