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

ops/2025/2/27 12:25:58/

目录

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

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

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)
idnamedept_id
1张三101
2李四NULL
部门表 (departments)
iddept_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示例
  • 三、易错点与注意事项
  • 四、总结与最佳实践

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

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT*FROM t1 WHERE column1=（SELECT column1 FROM t2);

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。
根据子查询结果不同，分为：

1.标量子查询（子查询结果为单个值）
2.列子查询(子查询结果为一列)
3.行子查询(子查询结果为一行)
4.表子查询（子查询结果为多行多列)

根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

1. 标量子查询

特征：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，返回单个值（一行一列），这种子查询成为标量子查询。
常用的操作符：= <> > >= < <=
场景：在WHERE/SELECT/HAVING等位置作为条件值使用

-- 查询高于平均工资的员工
--a.查询平均员工的工资（一行一列，即只有一条数据，所以称为标量子查询）
SELECT AVG(salary) FROM employees;
--b.查询高于平均员工工资的员工信息(>)
SELECT name, salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees); -- 返回单个数值

2. 列子查询

特征：返回单列多行数据
常用的操作符：IN、NOTIN、ANY、SOME、ALL
场景： 子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
在这里插入图片描述

--1.查询销售部和市场部的所有员工信息
--a.查询销售部和市场部的部门ID
select id from dept where name='销售部' or name='市场部';
--b.根据部门id查询员工信息
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');--2.查询比财务部所有人工资都高的员工信息
--法一（使用max()函数）
--a. 查询财务部所有人员工资
--1.首先拿到财务部的id
select id from dept where name='财务部';
--2.根据财务部id，在员工表查询最高的财务部员工的工资
select max(salary) from emp where dept_id=(select id from dept where name='财务部');
--b.在员工表查询比比财务部所有人工资都高的员工信息
select * from emp where salary>(select max(salary) from emp where dept_id=(select id from dept where name='财务部'));--法二（使用all关键字）
--a. 查询财务部所有人员工资
--1.首先拿到财务部的id
select id from dept where name='财务部';
--2.根据财务部id，在员工表查询财务部员工的工资
select salary from emp where dept_id=(select id from dept where name='财务部');
--b.在员工表查询比比财务部所有人工资都高的员工信息
select * from emp where salary>all(select salary from emp where dept_id=(select id from dept where name='财务部'));--3.查询比研发部其中任意一人工资高的员工信息(相当于只要比工资的最小值大就可以,这里可以用some，any)
--a.查询研发部所有人工资
--1.首先拿到研发部的id
select id from dept where name='研发部';
--2.根据研发部id，在员工表查询研发部员工的工资
select salary from emp where dept_id=(select id from dept where name='研发部');
--b.比研发部其中任意一人工资高的员工信息
select * from emp where salary>any(select salary from emp where dept_id=(select id from dept where name='研发部'));

3. 行子查询

特征：子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
场景：多条件同时比较 (c1,c2)=(c3,c4) 一行多列=一行多列（注意这里是单行数据所以才可以用=）
常用的操作符：=、<>、IN、NOT IN

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

4. 表子查询

特征：返回多行多列结果集，所以用关键字in(查询语句)
场景：作为临时表参与连接查询

-- 查询各部门最高薪员工
--a.查询各个部门的最高薪水，并作为一张临时表存在SELECT dept_id, MAX(salary) AS max_salaryFROM employeesGROUP BY dept_id;
--b.查询各部门最高薪的员工信息
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; --1．查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
--a．查询“鹿杖客”，"宋远桥”的职位和薪资
select job,salary from emp where name='鹿杖客'or name='宋远桥';
--b.查询与“鹿杖客”,“宋远桥”的职位和薪资相同的员工信息(多行多列所以这里要用in，不能用=)
select * from emp where(job,salary) in (select job,salary from emp where name='鹿杖客'orname='宋远桥');--查询入职日期是"2006-01-01”之后的员工信息，及其部门信息
--a.入职日期是"2006-01-01”之后的员工信息,并作为一张临时表存在
select * from emp where entrydate >'2006-01-01';
--b．查询这部分员工，对应的部门信息;
select e.*，d.* from (select * from emp where entrydate >'2006-01-01') e left join dept d on e.dept_id =d.id;

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;

http://www.ppmy.cn/ops/161672.html

相关文章

ZIP64扩展和普通ZIP文件有什么区别？

ZIP64扩展和普通ZIP文件有什么区别？

ZIP64扩展是ZIP文件格式的一个扩展&#xff0c;旨在解决传统ZIP格式的限制&#xff0c;尤其是文件大小和数量的限制。以下是ZIP64扩展与普通ZIP文件的主要区别&#xff1a; 1. 文件大小限制 普通ZIP文件&#xff1a; 单个文件大小限制为 4GB&#xff08;2^32字节&#xff09;。…
阅读更多...
bash脚本----变量的算术运算

bash脚本----变量的算术运算

整数运算&#xff1a;推荐使用 $((...)) 或 ((...))&#xff0c;因为它们是现代 Bash 的标准语法&#xff0c;效率高且语法简洁。 浮点运算&#xff1a;可以使用 bc 或 awk&#xff0c;具体选择取决于你的需求和偏好。 兼容性&#xff1a;如果需要兼容 POSIX 标准&#xff0c…
阅读更多...
OpenCV（10）：视频目标跟踪、视频背景减除

OpenCV（10）：视频目标跟踪、视频背景减除

1 视频目标跟踪 在计算机视觉领域&#xff0c;视频目标跟踪是一个非常重要的任务。视频目标跟踪广泛应用于监控、自动驾驶、人机交互等多个领域。OpenCV 提供了多种目标跟踪算法&#xff0c;其中 MeanShift 和 CamShift 是两种经典且常用的算法。本文将详细讲解这两种算法的原理…
阅读更多...
Git原理+使用(超详细)

Git原理+使用(超详细)

Git初识 当我们写项目代码时&#xff0c;需要不断的更新版本&#xff0c;那么就需要一个东西去管理这些不同版本的文件—版本控制器。 目前最主流的版本控制器就是Git。它是一个可以记录工程的每一次改动和版本迭代的管理系统&#xff0c;同时方便多人协同作业。 &#xff0…
阅读更多...
el-table

el-table

el-table合并 参考&#xff1a; https://www.jianshu.com/p/6067708bd1ee https://blog.csdn.net/weixin_43862642/article/details/122055745 某个列合并&#xff0c;前面序号也要跟着对应合并 <el-table:data"list":span-method"objectSpanMethod" …
阅读更多...
协方差（Covariance）与得分函数：从Fisher信息矩阵看统计关联

协方差（Covariance）与得分函数：从Fisher信息矩阵看统计关联

协方差与得分函数&#xff1a;从Fisher信息矩阵看统计关联 协方差&#xff08;Covariance&#xff09;是统计学中一个基础但强大的概念&#xff0c;它描述了两个随机变量之间的关系。在Fisher信息矩阵中&#xff0c;协方差以一种特别的形式出现&#xff1a;得分函数的协方差。…
阅读更多...
Spring Boot 项目中如何在 `pom.xml` 文件中引入本地 JAR 包

Spring Boot 项目中如何在 `pom.xml` 文件中引入本地 JAR 包

文章目录 Spring Boot 项目中如何在 pom.xml 文件中引入本地 JAR 包1. 准备工作2. 将本地 JAR 包安装到本地 Maven 仓库2.1 使用 mvn install:install-file 命令2.2 验证安装 3. 在 pom.xml 中引入本地 JAR 包3.1 添加依赖3.2 完整示例 4. 使用 system 作用域引入本地 JAR 包&a…
阅读更多...
开源一款DDS信号发生扩展板-FreakStudio多米诺系列

开源一款DDS信号发生扩展板-FreakStudio多米诺系列

原文链接&#xff1a; FreakStudio的博客 摘要 信号发生扩展板通过SPI接口生成可调频率和幅度的正弦波、方波和三角波&#xff0c;频率小于1MHz。支持幅度调节&#xff0c;提供原始和6倍放大输出接口。配备5阶低通滤波器、噪声抑制功能&#xff0c;优化信号稳定性。 往期推…
阅读更多...
最新文章

Copyright @ 2022~2023