MySQL-多表查询

一、表之间的关系

1. 表关系的三种类型

在关系型数据库中，表之间的关系主要分为三种：

(1) 一对多关系 (One To Many)

• ​​定义​​：A表的一行对应B表的多行，而B表的一行只对应A表的一行

• ​​特点​​：

  • 最常见的关系类型

  • "一"方是主表，"多"方是从表

• ​​示例​​：

  • 商品分类表(category) 和 商品表(products)

  • 部门表 和 员工表

• ​​实现方式​​：在"多"方表中添加外键指向"一"方表的主键

(2) 多对多关系 (Many To Many)

• ​​定义​​：A表的一行对应B表的多行，同时B表的一行也对应A表的多行

• ​​特点​​：

  • 必须有中间关系表记录关联关系

  • 中间表至少包含两个外键字段

• ​​示例​​：

  • 学生表 和 课程表（通过选课表关联）

  • 客户表 和 产品表（通过订单表关联）

• ​​实现方式​​：创建中间表，包含两个外键分别指向两张表的主键

(3) 一对一关系 (One To One)

• ​​定义​​：A表的一行对应B表的一行，反之亦然

• ​​特点​​：

  • 较少使用的关系类型

  • 通常用于拆分大表或提高安全性

• ​​示例​​：

  • 员工基础信息表 和 员工详细信息表

  • 个人信息表 和 身份证表

• ​​实现方式​​：在任意一方添加外键指向另一方的主键，并添加唯一约束

二、外键与外键约束

1. 外键概念

• ​​定义​​：如果B表的某列引用了A表的主键列，则该列称为B表的外键

• ​​主表与从表​​：

  • A表（被引用的表）称为主表

  • B表（包含外键的表）称为从表

2. 外键约束

• ​​作用​​：

  • 保证数据完整性：确保从表的外键值必须在主表的主键中存在

  • 维护关联关系：防止删除或修改主表数据时导致从表数据孤立

• ​​具体约束​​：

  1. 插入约束：从表插入的外键值必须在主表主键中存在

  2. 删除约束：主表记录被从表引用后，不能被直接删除

• ​​语法示例​​：

  -- 创建外键约束
  ALTER TABLE products
  ADD CONSTRAINT fk_category
  FOREIGN KEY (category_id) REFERENCES category(id);

3. 外键约束的优缺点

• ​​优点​​：

  • 保证数据一致性和完整性

  • 自动维护表间关系

• ​​缺点​​：

  • 增加数据库开销，降低性能

  • 使数据库操作更复杂

  • 跨数据库迁移困难

• ​​实际开发建议​​：

  • 在代码层面维护关系，而非依赖数据库外键约束

  • 对于高并发系统，避免使用外键约束

三、关联查询操作

1. 笛卡尔积（交叉连接）

• ​​概念​​：将左表的每一行与右表的每一行无条件组合

• ​​结果行数​​：左表行数 × 右表行数

• ​​语法​​：

  SELECT * FROM table1, table2;
  -- 或
  SELECT * FROM table1 CROSS JOIN table2;

• ​​问题​​：结果集庞大且大多无意义，实际使用少

2. 有条件连接

通过指定连接条件筛选有意义的结果集，分为四种类型：

(1) 内连接 (INNER JOIN)

• ​​定义​​：返回两表中满足连接条件的记录（交集）

• ​​语法​​：

  SELECT columns
  FROM table1
  INNER JOIN table2 ON join_condition;
  -- INNER可省略

• ​​特点​​：

  • 只返回满足条件的记录

  • 不满足条件的记录被排除

• ​​示例​​：

  -- 查询每个分类下的商品（无商品的分类不显示）
  SELECT c.id AS cid, c.name AS cname, p.id AS pid, p.name AS pname
  FROM category c
  JOIN products p ON c.id = p.category_id;

(2) 左连接 (LEFT JOIN)

• ​​定义​​：返回左表全部记录 + 右表匹配记录（不匹配则右表字段为NULL）

• ​​语法​​：

  SELECT columns
  FROM table1
  LEFT JOIN table2 ON join_condition;

• ​​特点​​：

  • 保留左表所有记录

  • 右表无匹配时显示NULL

• ​​示例​​：

  -- 查询所有分类及其商品（无商品的分类也显示）
  SELECT c.id AS cid, c.name AS cname, p.id AS pid, p.name AS pname
  FROM category c
  LEFT JOIN products p ON c.id = p.category_id;

(3) 右连接 (RIGHT JOIN)

• ​​定义​​：返回右表全部记录 + 左表匹配记录（不匹配则左表字段为NULL）

• ​​语法​​：

  SELECT columns
  FROM table1
  RIGHT JOIN table2 ON join_condition;

• ​​特点​​：

  • 保留右表所有记录

  • 左表无匹配时显示NULL

• ​​示例​​：

  -- 查询所有商品及其分类（无分类的商品也显示）
  SELECT c.id AS cid, c.name AS cname, p.id AS pid, p.name AS pname
  FROM category c
  RIGHT JOIN products p ON c.id = p.category_id;

(4) 全连接 (FULL JOIN)

• ​​定义​​：返回左右表所有记录（左连接 + 右连接）

• ​​MySQL实现​​：MySQL不支持FULL JOIN，需用UNION组合左连接和右连接

• ​​语法​​：

  (SELECT columns FROM table1 LEFT JOIN table2 ON condition)
  UNION
  (SELECT columns FROM table1 RIGHT JOIN table2 ON condition);

• ​​特点​​：

  • 返回两表所有记录

  • 不匹配的部分填充NULL

• ​​示例​​：

  -- 查询所有分类和商品（无商品的分类和无分类的商品都显示）
  (SELECT c.id AS cid, c.name AS cname, p.id AS pid, p.name AS pnameFROM category cLEFT JOIN products p ON c.id = p.category_id)
  UNION
  (SELECT c.id AS cid, c.name AS cname, p.id AS pid, p.name AS pnameFROM category cRIGHT JOIN products p ON c.id = p.category_id);

3. 连接类型比较

四、自关联查询

1. 概念与应用场景

• ​​定义​​：同一张表作为左右表进行关联查询

• ​​特点​​：

  • 必须使用表别名区分左右表

  • 本质是特殊的一对多关系

• ​​应用场景​​：

  • 层级数据结构（省-市-区）

  • 组织架构（部门-子部门）

  • 树状分类体系

2. 自关联表设计

• ​​设计原则​​：使用单个表存储多级数据

• ​​表结构​​：

  CREATE TABLE areas (id INT PRIMARY KEY,title VARCHAR(20) NOT NULL,pid INT, -- 父级IDFOREIGN KEY (pid) REFERENCES areas(id)
  );

• ​​数据示例​​：

  id	title	pid
  1	中国	NULL
  2	江苏	1
  3	浙江	1
  4	南京	2
  5	苏州	2
  6	杭州	3

3. 自关联查询示例

-- 查询江苏省下的所有市
SELECT c.id AS cid, c.title AS cname,p.id AS pid, p.title AS pname
FROM areas p -- 省表
JOIN areas c -- 市表
ON p.id = c.pid
WHERE p.title = '江苏省';-- 三级关联查询（省-市-区）
SELECT q.id AS qid, q.title AS qname, -- 区c.id AS cid, c.title AS cname, -- 市p.id AS pid, p.title AS pname  -- 省
FROM areas p
JOIN areas c ON p.id = c.pid -- 省→市
JOIN areas q ON c.id = q.pid; -- 市→区

五、子查询操作

1. 子查询概念

• ​​定义​​：在一个SELECT语句中嵌套另一个SELECT语句

• ​​主查询​​：外层SELECT语句

• ​​子查询​​：内层SELECT语句，辅助主查询

• ​​作用​​：

  • 作为查询条件

  • 作为临时数据源

  • 作为计算字段

2. 子查询类型与示例

(1) 作为查询条件

-- 查询价格高于平均价的商品
SELECT * 
FROM products
WHERE price > (SELECT AVG(price) FROM products);-- 查询与'华为手机'同分类的商品
SELECT *
FROM products
WHERE category_id = (SELECT category_id FROM products WHERE name = '华为手机'
);

(2) 作为数据源（临时表）

-- 查询每个分类的商品数量
SELECT c.name, t.total
FROM category c
JOIN (SELECT category_id, COUNT(*) AS totalFROM productsGROUP BY category_id
) t ON c.id = t.category_id;

(3) 作为查询字段

-- 查询每个分类的商品数量（使用相关子查询）
SELECT c.name,(SELECT COUNT(*) FROM products p WHERE p.category_id = c.id) AS total
FROM category c;

3. 子查询使用技巧

1. ​​标量子查询​​：返回单一值的子查询，可用于WHERE条件

2. ​​列子查询​​：返回一列值的子查询，配合IN、ANY、ALL使用

3. ​​相关子查询​​：子查询依赖外部查询的值

4. ​​性能优化​​：

   • 避免嵌套过深

   • 优先使用JOIN替代子查询

   • 为子查询中的条件字段添加索引

六、SQL语句执行顺序

理解SQL执行顺序是编写高效查询的关键：

1. ​​FROM & JOIN​​：确定数据来源，执行表连接

2. ​​WHERE​​：对行级数据进行过滤

3. ​​GROUP BY​​：对数据进行分组

4. ​​聚合函数​​：对每个分组执行聚合计算

5. ​​HAVING​​：对分组后的结果进行过滤

6. ​​SELECT​​：选择要输出的列

7. ​​DISTINCT​​：对结果进行去重

8. ​​ORDER BY​​：对结果进行排序

9. ​​LIMIT​​：限制返回的行数

​​执行流程​​：

  SQL语句从上到下，依次执行顺序

        FROM/JOIN

        WHERE

        GROUP BY

        聚合函数

        HAVING

        SELECT

        DISTINCT

        ORDER BY

        LIMIT

七、最佳实践与常见错误

1. 关联查询最佳实践

1. ​​明确连接类型​​：根据业务需求选择INNER/LEFT/RIGHT JOIN

2. ​​使用表别名​​：提高可读性，避免列名冲突

3. ​​指定列名​​：避免SELECT *，只查询需要的列

4. ​​注意NULL值​​：使用COALESCE处理NULL结果

5. ​​性能优化​​：

   • 为连接条件字段添加索引

   • 避免在WHERE中对连接字段使用函数

   • 限制结果集大小

2. 常见错误与解决方案

1. ​​歧义列名错误​​

   • 原因：多表存在相同列名

   • 解决：使用表名前缀或别名限定列名

2. ​​分组查询错误​​

   • 错误：SELECT name, AVG(price) FROM products GROUP BY category_id;

   • 解决：确保SELECT字段要么是分组字段，要么被聚合函数处理

3. ​​多级自连接数据缺失​​

   • 原因：使用INNER JOIN过滤了不完整层级

   • 解决：使用LEFT JOIN保留父级数据

4. ​​子查询返回多行错误​​

   • 错误：WHERE price > (SELECT price FROM products)

   • 解决：使用聚合函数或IN/ANY/ALL操作符