当前位置: 首页 > news >正文

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)
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示例
  • 三、易错点与注意事项
  • 四、总结与最佳实践

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

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;

相关文章:

Mysql基础-多表查询(详细版)

目录 一、表的关系类型与适用场景二、连接方式与使用场景三、易错点与注意事项四、总结 一、表的关系类型与适用场景 1. 一对一关系 场景&#xff1a;一个表的记录对应另一个表的唯一记录 案例&#xff1a;用户表 用户详情表 CREATE TABLE users (id INT PRIMARY KEY,name…...

港科大提出开放全曲音乐生成基础模型YuE:可将歌词转换成完整歌曲

YuE是港科大提出的一个开源的音乐生成基础模型&#xff0c;专为音乐生成而设计&#xff0c;专门用于将歌词转换成完整的歌曲&#xff08;lyrics2song&#xff09;。它可以生成一首完整的歌曲&#xff0c;时长几分钟&#xff0c;包括朗朗上口的声乐曲目和伴奏曲目。YuE 能够模拟…...

Python学习第十七天之PyTorch保姆级安装

PyTorch安装与部署 一、准备工作二、pytorch介绍三、CPU版本pytorch安装1. 创建虚拟环境2. 删除虚拟环境1. 通过环境名称删除2. 通过环境路径删除 3. 配置镜像源4. 安装pytorch1. 首先激活环境变量2. 进入pytorch官网&#xff0c;找到安装指令 5. 验证pytorch是否安装成功 四、…...

有关与 WSL 2 的主要区别的信息,请访问 https://aka.ms/wsl2

https://learn.microsoft.com/zh-cn/windows/wsl/install-manual#step-4—download-the-linux-kernel-update-package...

什么是 Java 中的线程安全?

回答 Java 中的线程安全&#xff08;Thread Safety&#xff09;指的是在多线程环境下&#xff0c;当多个线程同时访问和操作共享资源&#xff08;如对象、变量、数据结构等&#xff09;时&#xff0c;能够保证程序的正确性&#xff0c;不会出现数据不一致、竞争条件&#xff0…...

计算机视觉(opencv-python)入门之图像的读取,显示,与保存

在计算机视觉领域&#xff0c;Python的cv2库是一个不可或缺的工具&#xff0c;它提供了丰富的图像处理功能。作为OpenCV的Python接口&#xff0c;cv2使得图像处理的实现变得简单而高效。 示例图片 目录 opencv获取方式 图像基本知识 颜色空间 RGB HSV CV2常用图像处理方…...

QT:Graphics View的坐标系介绍

在 Qt 的 Graphics View 框架中&#xff0c;存在三种不同的坐标系&#xff0c;分别是 物品坐标系&#xff08;Item Coordinates&#xff09;、场景坐标系&#xff08;Scene Coordinates&#xff09; 和 视图坐标系&#xff08;View Coordinates&#xff09;。这三种坐标系在图形…...

530 Login fail. A secure connection is requiered(such as ssl)-java发送QQ邮箱(简单配置)

由于cs的csdN许多文章关于这方面的都是vip文章&#xff0c;而本文是免费的&#xff0c;希望广大网友觉得有帮助的可以多点赞和关注&#xff01; QQ邮箱授权码到这里去开启 授权码是16位的字母&#xff0c;填入下面的mail.setting里面的pass里面 # 邮件服务器的SMTP地址 host…...

vs2015下使用openmp

一 OPENMP 简介 OpenMP(Open Multi-Processing)是一个基于共享内存的并行编程API,通过编译器指令实现多线程并行开发。其核心特性包括: 1)通过简单的#pragma指令实现并行化 2)支持增量并行(逐步优化代码) 3)跨平台(Windows/Linux/macOS) 4)支持C/C++/Fortra …...

Docker 搭建 Gitlab 服务器 (完整详细版)

参考 Docker 搭建 Gitlab 服务器 (完整详细版)_docker gitlab-CSDN博客 Docker 安装 (完整详细版)_docker安装-CSDN博客 Docker 日常命令大全(完整详细版)_docker命令-CSDN博客 1、Gitlab镜像 # 查找Gitlab镜像 docker search gitlab # 拉取Gitlab镜像 docker pull gitlab/g…...

【万字长文】开源之播对话白鲸开源CEO郭炜--乐观主义的开源精神走得更远

本文为白鲸开源科技CEO郭炜1小时深度访谈全记录 来源于&#xff1a;开源之播」Episode15:对话郭炜–乐观主义的开源精神走得更远 大家好&#xff0c;我是郭炜&#xff0c;开源圈的“郭大侠”。作为 Apache 基金会的成员&#xff0c;我曾参与并孵化了多个开源项目&#xff0c;如…...

机试刷题_674. 最长连续递增序列【python】

674. 最长连续递增序列 class Solution:def findLengthOfLCIS(self, nums: List[int]) -> int:if not nums:return 0if len(nums)1:return 1left 0right len(nums)-1tmp []tmp.append(nums[0])res 0while left<right:if nums[left]<nums[left1]:tmp.append(nums[l…...

ipe网络安全

&#x1f345; 点击文末小卡片 &#xff0c;免费获取网络安全全套资料&#xff0c;资料在手&#xff0c;涨薪更快 什么是IDS&#xff1f; IDS是英文"Intrusion Detection Systems"的缩写&#xff0c;中文意思是"入侵检测系统"。 大家还记得「网络安全」安…...

QT:QPen、QBrush、与图形抗锯齿的关联

QPen QPen 是 Qt 框架中用于定义绘图时使用的画笔属性的类。在使用 QPainter 进行 2D 绘图时&#xff0c;QPen 可以控制线条的外观&#xff0c;比如线条的颜色、宽度、样式&#xff08;如实线、虚线等&#xff09;、端点样式&#xff08;如方形端点、圆形端点等&#xff09;和…...

android keystore源码分析

架构 Android Keystore API 和底层 Keymaster HAL 提供了一套基本的但足以满足需求的加密基元&#xff0c;以便使用访问受控且由硬件支持的密钥实现相关协议。 Keymaster HAL 是由原始设备制造商 (OEM) 提供的动态加载库&#xff0c;密钥库服务使用它来提供由硬件支持的加密服…...

【12】智能合约开发入门

12-1 在线合约开发 Cloud IDE简介 基本框架 Cloud IDE是BaaS合约平台提供的在线合约开发工具 IDE是一个去中心化应用&#xff08;Dapp&#xff09;&#xff0c;通过JavaScript SDK直接与区块链平台通信&#xff0c;进行合约部署和调用 核心功能 合约工程管理 合约编辑与编…...

web安全——分析应用程序

文章目录 一、确定用户输入入口点二、确定服务端技术三、解析受攻击面 一、确定用户输入入口点 在检查枚举应用程序功能时生成的HTTP请求的过程中&#xff0c;用户输入入口点包括&#xff1a; URL文件路径 通常&#xff0c;在查询字符?之前的URL部分并不视为用户输入入口&am…...

Wpf 之Generic.xaml

在 WPF 中&#xff0c;Generic.xaml 是一个特殊的资源文件&#xff0c;它会被自动加载&#xff0c;不需要显式添加。这是 WPF 的命名约定。当 WPF 初始化自定义控件时&#xff0c;它会专门查找这个名字的文件。 这个名字是硬编码在 WPF 框架中的&#xff0c;不能改变。 Generi…...

VidSketch:具有扩散控制的手绘草图驱动视频生成

浙大提出的VidSketch是第一个能够仅通过任意数量的手绘草图和简单的文本提示来生成高质量视频动画的应用程序。该方法训练是在单个 RTX4090 GPU 上进行的&#xff0c;针对每个动作类别使用一个小型、高质量的数据集。VidSketch方法使所有用户都能使用简洁的文本提示和直观的手绘…...

解锁C# XML编程:从新手到实战高手的蜕变之路

一、引言&#xff1a;XML 在 C# 中的关键地位 在 C# 开发的广袤领域中&#xff0c;XML&#xff08;可扩展标记语言&#xff0c;eXtensible Markup Language&#xff09;宛如一颗璀璨的明星&#xff0c;占据着举足轻重的地位。它以其独特的结构化和自描述特性&#xff0c;成为了…...

Neo4j 集群管理:原理、技术与最佳实践深度解析

Neo4j 的集群技术是其企业级高可用性、可扩展性和容错能力的核心。通过深入分析官方文档,本文将系统阐述其集群管理的核心原理、关键技术、实用技巧和行业最佳实践。 Neo4j 的 Causal Clustering 架构提供了一个强大而灵活的基石,用于构建高可用、可扩展且一致的图数据库服务…...

C# 类和继承(抽象类)

抽象类 抽象类是指设计为被继承的类。抽象类只能被用作其他类的基类。 不能创建抽象类的实例。抽象类使用abstract修饰符声明。 抽象类可以包含抽象成员或普通的非抽象成员。抽象类的成员可以是抽象成员和普通带 实现的成员的任意组合。抽象类自己可以派生自另一个抽象类。例…...

服务器--宝塔命令

一、宝塔面板安装命令 ⚠️ 必须使用 root 用户 或 sudo 权限执行&#xff01; sudo su - 1. CentOS 系统&#xff1a; yum install -y wget && wget -O install.sh http://download.bt.cn/install/install_6.0.sh && sh install.sh2. Ubuntu / Debian 系统…...

C++课设:简易日历程序(支持传统节假日 + 二十四节气 + 个人纪念日管理)

名人说:路漫漫其修远兮,吾将上下而求索。—— 屈原《离骚》 创作者:Code_流苏(CSDN)(一个喜欢古诗词和编程的Coder😊) 专栏介绍:《编程项目实战》 目录 一、为什么要开发一个日历程序?1. 深入理解时间算法2. 练习面向对象设计3. 学习数据结构应用二、核心算法深度解析…...

macOS 终端智能代理检测

&#x1f9e0; 终端智能代理检测&#xff1a;自动判断是否需要设置代理访问 GitHub 在开发中&#xff0c;使用 GitHub 是非常常见的需求。但有时候我们会发现某些命令失败、插件无法更新&#xff0c;例如&#xff1a; fatal: unable to access https://github.com/ohmyzsh/oh…...

《信号与系统》第 6 章 信号与系统的时域和频域特性

目录 6.0 引言 6.1 傅里叶变换的模和相位表示 6.2 线性时不变系统频率响应的模和相位表示 6.2.1 线性与非线性相位 6.2.2 群时延 6.2.3 对数模和相位图 6.3 理想频率选择性滤波器的时域特性 6.4 非理想滤波器的时域和频域特性讨论 6.5 一阶与二阶连续时间系统 6.5.1 …...

2025-05-08-deepseek本地化部署

title: 2025-05-08-deepseek 本地化部署 tags: 深度学习 程序开发 2025-05-08-deepseek 本地化部署 参考博客 本地部署 DeepSeek&#xff1a;小白也能轻松搞定&#xff01; 如何给本地部署的 DeepSeek 投喂数据&#xff0c;让他更懂你 [实验目的]&#xff1a;理解系统架构与原…...

41道Django高频题整理(附答案背诵版)

解释一下 Django 和 Tornado 的关系&#xff1f; Django和Tornado都是Python的web框架&#xff0c;但它们的设计哲学和应用场景有所不同。 Django是一个高级的Python Web框架&#xff0c;鼓励快速开发和干净、实用的设计。它遵循MVC设计&#xff0c;并强调代码复用。Django有…...

【笔记】AI Agent 项目 SUNA 部署 之 Docker 构建记录

#工作记录 构建过程记录 Microsoft Windows [Version 10.0.27871.1000] (c) Microsoft Corporation. All rights reserved.(suna-py3.12) F:\PythonProjects\suna>python setup.py --admin███████╗██╗ ██╗███╗ ██╗ █████╗ ██╔════╝…...

[特殊字符] Spring Boot底层原理深度解析与高级面试题精析

一、Spring Boot底层原理详解 Spring Boot的核心设计哲学是约定优于配置和自动装配&#xff0c;通过简化传统Spring应用的初始化和配置流程&#xff0c;显著提升开发效率。其底层原理可拆解为以下核心机制&#xff1a; 自动装配&#xff08;Auto-Configuration&#xff09; 核…...