Mysql基础-多表查询（详细版）

目录

• 一、表的关系类型与适用场景
• 二、连接方式与使用场景
• 三、易错点与注意事项
• 四、总结

---

一、表的关系类型与适用场景

1. 一对一关系

场景：一个表的记录对应另一个表的唯一记录
案例：用户表 + 用户详情表

CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50)
);CREATE TABLE user_details (user_id INT PRIMARY KEY,address VARCHAR(100),FOREIGN KEY (user_id) REFERENCES users(id)
);

2. 一对多关系

场景：主表的一条记录对应从表的多条记录
案例：部门表 + 员工表

CREATE TABLE departments (id INT PRIMARY KEY,name VARCHAR(50)
);CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50),dept_id INT,FOREIGN KEY (dept_id) REFERENCES departments(id)
);

3. 多对多关系

场景：两个表的记录可以相互对应多条记录
案例：学生表 + 课程表（通过中间表实现）

CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(50)
);CREATE TABLE courses (id INT PRIMARY KEY,name VARCHAR(50)
);CREATE TABLE student_courses (student_id INT,course_id INT,PRIMARY KEY (student_id, course_id),FOREIGN KEY (student_id) REFERENCES students(id),FOREIGN KEY (course_id) REFERENCES courses(id)
);

---

二、连接方式与使用场景

1. 内连接（INNER JOIN）

场景：需要两表同时存在匹配记录的数据，相当于查询的是两张表的交集，不能查空。

-- 查询所有有部门的员工信息
SELECT e.name, d.name AS dept_name
FROM employees e
INNER JOIN departments d 
ON e.dept_id = d.id;--等价写法(这种写法平时项目里用的更多)
SELECT e.name, d.name AS dept_name
FROM employees e，departments d 
ON e.dept_id = d.id;  

2. 左外连接（LEFT JOIN）

场景：保留左表所有记录，右表无匹配时显示NULL（相比右外，实际开发用的更多）

相当于查询的是两张表的交集，但是能查空

-- 查询所有员工（包括未分配部门的） 
SELECT e.name, d.name AS dept_name
FROM employees e
LEFT JOIN departments d 
ON e.dept_id = d.id;
--两表出现相同字段要起别名

3. 右外连接（RIGHT JOIN）

场景：保留右表所有记录，左表无匹配时显示NULL

-- 查询所有部门（包括没有员工的）
SELECT d.name AS dept_name, e.name
FROM employees e
RIGHT JOIN departments d 
ON e.dept_id = d.id;

补充对比示例说明：

假设有以下两个表：

员工表 (employees)

id	name	dept_id
1	张三	101
2	李四	NULL

部门表 (departments)

id	dept_name
101	技术部
102	市场部

---

不同连接的结果差异：

-- 内连接（INNER JOIN）
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
-- 结果：只有张三 + 技术部-- 左连接（LEFT JOIN）
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;-- 结果：
-- 张三 + 技术部
-- 李四 + NULL

---

❗ 关键区别：

• 连接类型	是否要求右表有数据	是否保留左表所有数据	典型场景
  INNER JOIN	必须	否	查询「完整关联信息」
  LEFT JOIN	不必须	是	查询「左表全部+右表能关联的部分」

---

🧠 易错点提醒：

1. 不要混淆「存在数据」和「匹配条件」

   • 即使两表都有数据，但若 不满足连接条件，内连接也会过滤掉
   • 例如：员工表有 dept_id=100，部门表没有 id=100 的记录时，该员工不会出现在内连接结果中

2. 默认 JOIN 行为差异

   -- 以下两种写法等价
   SELECT * FROM A INNER JOIN B ON A.id = B.a_id;--显式内连接
   SELECT * FROM A, B WHERE A.id = B.a_id; -- 隐式内连接
   

4. 全外连接/联合查询（FULL OUTER JOIN）

场景：同时保留两表所有记录（MySQL会用到关键字 union或union all）

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

--将薪资低于5000的员工，和年龄大于50的员工全部查询出来。
--union all 包含重复数据
select * from emp where salary < 5000
union all
select * from emp where age > 50;
--union  去除重复数据
select*fromemp where salary< 5000
union
select * from emp where age > 50;

tip： 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

5. 交叉连接（CROSS JOIN）

场景：生成笛卡尔积，常用于组合场景

-- 生成颜色与尺寸的所有组合
SELECT colors.name, sizes.name
FROM colors
CROSS JOIN sizes;

6. 自连接（SELF JOIN）

场景：同一表内数据关联查询

tip：自连接一定要起别名

-- 查找员工的上级经理
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m 
ON e.manager_id = m.id; 

---

三、易错点与注意事项

1. 忘记关联条件导致笛卡尔积

   -- 错误！缺少ON条件，将产生百万级数据
   SELECT * FROM employees, departments;  
   

2. NULL值处理问题

   -- 外连接后过滤条件应放在ON子句
   SELECT * 
   FROM A 
   LEFT JOIN B 
   ON A.id = B.a_id AND B.status = 1; -- ✔ 正确写法
   

3. 多次连接时的别名冲突

   -- 必须为每个表指定唯一别名
   SELECT o.order_no, c1.name AS city_from, c2.name AS city_to
   FROM orders o
   LEFT JOIN cities c1 ON o.from_city = c1.id
   LEFT JOIN cities c2 ON o.to_city = c2.id;    
   

4. 连接顺序影响性能

    -- 大表在前可能导致性能问题
   SELECT * 
   FROM huge_table  -- ✘ 大表在前
   INNER JOIN small_table ON ...  
   

---

四、总结

连接类型	适用场景	特点说明
INNER JOIN	需要严格匹配的数据	结果集最小，性能最好
LEFT JOIN	保留左表全部数据	常用于主表查询
RIGHT JOIN	保留右表全部数据	可用LEFT JOIN替代
FULL JOIN	需要两表所有数据	MySQL需用UNION模拟
CROSS JOIN	生成组合数据	谨慎使用，易产生大数据量
SELF JOIN	层级关系/树形结构查询	必须使用别名

最佳实践建议：

1. 优先使用INNER JOIN，需要保留全部数据时再用外连接
2. 多表连接时，按数据量从小到大排列连接顺序
3. 始终为连接的表指定明确的别名
4. 复杂查询建议分步调试，先验证单表结果再组合
5. 超过3个表连接时，建议使用EXPLAIN分析执行计划

MySQL 子查询全面指南

目录

• 一、子查询类型与使用场景
• 二、不同子查询的SQL示例
• 三、易错点与注意事项
• 四、总结与最佳实践

---

一、子查询类型与使用场景

1. 标量子查询

特征：返回单个值（一行一列）
场景：在WHERE/SELECT/HAVING等位置作为条件值使用

-- 查询高于平均工资的员工
SELECT name, salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees); -- 返回单个数值 

2. 列子查询

特征：返回单列多行数据
场景：配合IN/ANY/ALL等运算符使用

-- 查询有订单的客户
SELECT name 
FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders  -- 返回客户ID列表
);

3. 行子查询

特征：返回单行多列数据
场景：多条件同时比较

-- 查询与张三同部门同职位的员工
SELECT name 
FROM employees
WHERE (dept_id, position) = (SELECT dept_id, position FROM employees WHERE name = '张三'
);

4. 表子查询

特征：返回多行多列结果集
场景：作为临时表参与连接查询

-- 查询各部门最高薪员工
SELECT e.dept_id, e.name, e.salary
FROM employees e
INNER JOIN (SELECT dept_id, MAX(salary) AS max_salaryFROM employeesGROUP BY dept_id
) AS tmp 
ON e.dept_id = tmp.dept_id AND e.salary = tmp.max_salary; 

5. 相关子查询

特征：子查询引用外层查询的字段
场景：逐行处理关联数据

-- 查询工资高于部门平均的员工
SELECT name, salary, dept_id
FROM employees e1
WHERE salary > (SELECT AVG(salary)FROM employees e2WHERE e2.dept_id = e1.dept_id  -- 引用外层字段
);  

6. EXISTS/NOT EXISTS

特征：检查子查询是否存在结果
场景：存在性验证

-- 查询从未下单的客户
SELECT name 
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id
);   

---

二、不同子查询的SQL示例

1. 在SELECT中使用

-- 显示员工及其部门人数
SELECT name,dept_id,(SELECT COUNT(*) FROM employees e2 WHERE e2.dept_id = e1.dept_id) AS dept_total
FROM employees e1;

2. 在UPDATE中使用

-- 将技术部员工薪资提高10%
UPDATE employees
SET salary = salary * 1.1
WHERE dept_id = (SELECT id FROM departments WHERE dept_name = '技术部'
);

3. 在HAVING中使用

-- 查询订单数超过平均值的客户
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > (SELECT AVG(order_count) FROM (SELECT COUNT(*) AS order_countFROM ordersGROUP BY customer_id) tmp
);

---

三、易错点与注意事项

1. 性能陷阱

   -- 错误：每行执行子查询导致性能低下
   SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count
   FROM customers c;
   -- ✅ 应改用LEFT JOIN优化

2. NULL值问题

   -- 当子查询可能返回NULL时
   SELECT * 
   FROM products
   WHERE price > (SELECT MAX(price) FROM discontinued_products);
   -- 如果子查询结果为NULL，整个WHERE条件会失效

3. 多行比较错误

   -- 错误：标量子查询返回多行
   SELECT name 
   FROM employees
   WHERE salary = (SELECT salary FROM employees WHERE dept_id = 2
   );
   -- ✅ 应改用IN或LIMIT 1

4. 列不匹配错误

   -- 错误：行子查询列数不匹配
   SELECT * 
   FROM tableA 
   WHERE (col1, col2) = (SELECT col1 FROM tableB
   );

---

四、总结与最佳实践

子查询类型	适用场景	性能建议
标量子查询	单值比较	优先用于简单条件
EXISTS	存在性检查	比COUNT(*)效率高
相关子查询	逐行依赖外层数据	避免在大数据量场景使用
表子查询	复杂数据过滤	考虑改用临时表或视图

黄金法则：

1. 能用连接查询解决的问题，优先使用JOIN（通常性能更好）
2. 需要聚合结果作为条件时，子查询更合适
3. 对于大数据表，避免在WHERE子句中使用相关子查询
4. 始终检查子查询可能返回的NULL值和空结果集
5. 必要时使用LIMIT控制子查询返回行数

性能优化提示：

-- 原始慢查询
SELECT * FROM products
WHERE category_id IN (SELECT category_id FROM popular_categories  -- 假设返回大量结果
);-- 优化方案：改用JOIN
SELECT p.* 
FROM products p
INNER JOIN popular_categories pc 
ON p.category_id = pc.category_id;