【教程】MySQL数据库学习笔记（七）——多表操作（持续更新）

文首标志
写在前面：
如果文章对你有帮助，记得点赞关注加收藏一波，利于以后需要的时候复习，多谢支持！

【MySQL数据库学习】系列文章

第一章 《认识与环境搭建》
第二章 《数据类型》
第三章 《数据定义语言DDL》
第四章 《数据操作语言DML》
第五章 《约束》
第六章 《数据查询语言DQL》
第七章 《多表操作》

文章目录

  • 【MySQL数据库学习】系列文章
    • 一、多表关系
      • （一）多表关系概念
      • （二）外键约束
        • 1.一对多关系
        • 2.多对多关系
    • 二、多表联合查询
      • （一）交叉连接查询
      • （二）内连接查询

一、多表关系

（一）多表关系概念

在实际的项目中，往往需要进行处理多表数据，而多表的关系通常可以概括为以下几种。

  • 一对一关系：例如一个学生只有一个身份证号，表现为一张表的一行对应另一张表的一行。但这种关系使用较少，因为通常一对一关系可以合成为一张表。
  • 一对多关系：例如一个部门有着多个员工，表现为一张表的一行对应另一张表的多行。
  • 多对多关系：例如学生和选课之间，一个学生可以选多节课，而一节课也可以被多个学生所选，表现在一张表对应另一张表的多行的同时，另一张表的一行也对应这张表的多行。通常多对多的关系需要中间表将其分割为一对多的关系。

（二）外键约束

外键约束会在表中建立一种关系，这种关系使得从表（子表）中的列（外键）引用主表（父表）中的列（主键或唯一键）。通过这种方式，可以确保子表中的数据在父表中有对应的条目。

这用于确保数据的一致性和完整性，具体而言，则是用于维护表与表之间的关系，确保在一个表中引用的值在另一个表中存在。

外键约束有着以下特点。

  • 主表必须已经存在于数据库，或者是当前正在创建的表。
  • 必须为主表定义主键。
  • 主键不能包含空值，但允许在外键中出现空值。
  • 在主表的表名后面指定列名或列名的组合，而这个列或者列组合必须是主表的主键或者候选键。
  • 外键中列的数目必须和主键中列的数目相同。
  • 外键中列的数据类型必须和主键中列的数据类型相同。

如果想要创建外键约束，有两种方式。

方式1：在创建表时设置外键约束。

在CREATE TABLE语句中，通过FOREIGN KEY关键字来指定外键，具体的语法格式如下。

CONSTRAINT 外键名 FOREIGN KEY 字段名1,字段名2,... REFERENCES 主表名 主键列1,主键列2,...

下面是简单的示例。

USE mydb1;-- 创建主表（部门表）
CREATE TABLE IF NOT EXISTS dept (did VARCHAR(20) PRIMARY KEY, -- 部门编号，设置主键name VARCHAR(20) -- 部门名字
);-- 创建从表（员工表）
CREATE TABLE IF NOT EXISTS emp (eid VARCHAR(20) PRIMARY KEY, -- 员工编号，设置主键ename VARCHAR(20), -- 员工名字age INT, -- 员工年龄dept_id VARCHAR(20), -- 员工所属部门编号CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(did) -- 外键约束
);

创建完外键约束后，可以通过模型查看外键约束关系。点击表，选中两个表，右键选择“逆向表到模型”即可查看。
在这里插入图片描述
可以看到，两张表之间的外键约束已经建立。
在这里插入图片描述
除此之外，还有另一种创建外键约束的方式。

方式2：在修改表时设置外键约束。

在ALTER TABLE语句中，通过FOREIGN KEY关键字来指定外键，具体的语法格式如下。

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY 字段名1,字段名2,... REFERENCES 主表名 主键列1,主键列2,...

下面是简单的示例。

-- 创建主表（部门表）
CREATE TABLE IF NOT EXISTS dept (did VARCHAR(20) PRIMARY KEY, -- 部门编号，设置主键name VARCHAR(20) -- 部门名字
);-- 创建从表（员工表）
CREATE TABLE IF NOT EXISTS emp (eid VARCHAR(20) PRIMARY KEY, -- 员工编号，设置主键ename VARCHAR(20), -- 员工名字age INT, -- 员工年龄dept_id VARCHAR(20), -- 员工所属部门编号
);-- 创建外键约束
ALTER TABLE emp ADD CONSTRAINT emp_fk FOREIGN KEY (dept_id) REFERENCES dept(did);
1.一对多关系

为了验证外键约束的作用，首先应该将上面创建的两张空表，进行一对多关系的数据填充。

-- 1.添加主表数据
INSERT INTO dept VALUES ('1001','研发部');
INSERT INTO dept VALUES ('1002','销售部');
INSERT INTO dept VALUES ('1003','财务部');
INSERT INTO dept VALUES ('1004','人事部');-- 2.添加从表数据
INSERT INTO emp VALUES ('1','刘邦',25,'1001');
INSERT INTO emp VALUES ('2','樊哙',24,'1001');
INSERT INTO emp VALUES ('3','张良',26,'1001');
INSERT INTO emp VALUES ('4','韩信',25,'1002');
INSERT INTO emp VALUES ('5','萧何',27,'1002');
INSERT INTO emp VALUES ('6','曹参',23,'1003');
INSERT INTO emp VALUES ('7','陈平',26,'1003');
INSERT INTO emp VALUES ('8','周勃',28,'1004');

注意，当删除数据的时候，有外键依赖的主表数据是不能删除的，除非先清除从表中依赖主表的外键，否则会报错。但反之，从表中的外键都是可以随意删除的。

而如果希望删除外键约束时，需要在ALTER TABLE语句中使用DROP关键字来删除外键约束。具体语法如下所示。

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名

简单的实现示例则如下所示。

ALTER TABLE emp DROP FOREIGN KEY emp_fk;

这样就能够删除刚才在上面的示例中在从表emp中创建的emp_fk外键约束。

2.多对多关系

对于多对多关系，比如之前提到的学生和选课的关系，此时学生表和选课表都是主表，而简化其关系的中间表则是从表，其中的外键列依赖于学生表和选课表两个主表。

具体的实现示例如下。

-- 创建学生表（主表）
CREATE TABLE IF NOT EXISTS student (sid INT PRIMARY KEY auto_increment, -- 学生编号name VARCHAR(20), -- 学生姓名age INT, -- 学生年龄gender VARCHAR(20) -- 学生性别
);
-- 创建课程表（主表）
CREATE TABLE IF NOT EXISTS course (cid INT PRIMARY KEY auto_increment, -- 课程编号cname VARCHAR(20) -- 课程名
);
-- 创建中间表（从表）
CREATE TABLE IF NOT EXISTS score (sid INT,cid INT,score DOUBLE
);-- 创建外键约束
ALTER TABLE score ADD FOREIGN KEY (sid) REFERENCES student(sid);
ALTER TABLE score ADD FOREIGN KEY (cid) REFERENCES course(cid);-- 学生表数据填充
INSERT INTO student VALUES (1,'刘邦',21,'男'),(2,'吕雉',19,'女'),(3,'项羽',20,'男');
-- 课程表数据填充
INSERT INTO course VALUES (1,'语文'),(2,'数学'),(3,'英语');
-- 中间表数据填充
INSERT INTO score VALUES (1,1,78),(1,2,75),(2,1,88),(2,3,90),(3,2,80),(3,3,65);

查看表的模型即可看到外键约束已创建完毕。
在这里插入图片描述

二、多表联合查询

多表联合查询（也称为联接查询）用于从多个表中检索相关数据，因为在实际项目需要时，可能需要显示的查询结果来自于两个或两个以上的表。

多表查询有以下分类。

  • 交叉连接查询
  • 内连接查询
  • 外连接查询
  • 子查询
  • 表自关联

作为使用的数据，仍然主要沿用上面的部门和员工表示例，只不过不加入外键约束。

CREATE TABLE IF NOT EXISTS dept (did VARCHAR(20) PRIMARY KEY, -- 部门编号，设置主键name VARCHAR(20) -- 部门名字
);
CREATE TABLE IF NOT EXISTS emp (eid VARCHAR(20) PRIMARY KEY, -- 员工编号，设置主键ename VARCHAR(20), -- 员工名字age INT, -- 员工年龄dept_id VARCHAR(20) -- 员工所属部门编号
);
INSERT INTO dept VALUES
('1001','研发部'),
('1002','销售部'),
('1003','财务部'),
('1004','人事部');
INSERT INTO emp VALUES 
('01','刘邦',25,'1001'),
('02','樊哙',24,'1001'),
('03','张良',26,'1001'),
('04','韩信',25,'1001'),
('05','萧何',27,'1002'),
('06','曹参',23,'1002'),
('07','陈平',26,'1002'),
('08','周勃',28,'1003'),
('09','彭越',27,'1003'),
('10','吕雉',24,'1005');

（一）交叉连接查询

交叉连接（Cross Join） 是 SQL 中的一种连接类型，它返回两个表的笛卡尔积，可以理解为一张表的每一行都和另一张表的任意一行进行匹配（假如A表有m行数据，B表有n行数据，则返回m*n行数据）。笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选。

其语法格式为以下所示。

SELECT * FROM 表1,表2,...

具体实现示例如下所示。

SELECT * FROM dept,emp;

返回结果如下。
在这里插入图片描述

（二）内连接查询

内连接（INNER JOIN） 是 SQL 中最常用的连接类型之一，用于从两个或多个表中提取符合条件的记录。内连接只返回满足连接条件的记录，实际上是求的两张表的交集，可以将表中的相关数据组合在一起，从而进行更加复杂的查询和分析。

其具体语法格式如下所示。

-- 隐式内连接
SELECT * FROM A表,B表 WHERE 条件; -- 可以理解为从笛卡尔积中筛选出符合条件的值
-- 显式内连接
SELECT * FROM A表 INNER JOIN B表 ON 条件; -- INNER可省略

具体示例如下所示。

-- 查询每个部门的所属员工
SELECT * FROM dept,emp WHERE dept.did = emp.dept_id;
SELECT * FROM dept INNER JOIN emp ON dept.did = emp.dept_id;-- 查询研发部和销售部的所属员工
SELECT * FROM dept,emp WHERE dept.did = emp.dept_id AND name IN ('研发部','销售部');
SELECT * FROM dept INNER JOIN emp ON dept.did = emp.dept_id AND name IN ('研发部','销售部');-- 查询每个部门的员工数，并升序排序
SELECT a.name,count(*) FROM dept a JOIN emp b ON a.did = b.dept_id GROUP BY a.did;-- 查询人数大于3的部门，并按照人数降序排序
SELECT a.name,count(*) AS count FROM dept a JOIN emp b ON a.did = b.dept_id GROUP BY a.did HAVING count >= 3 ORDER BY count DESC;

我是EC，一个永远在学习中的探索者，关注我，让我们一起进步！

文末标志

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/17869.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

Mysql数据库

Mysql数据库

一.数据定义语言DDL 一.概述 DDL用于定义和管理数据库的结构 DDL关键字：1.CREATE; 2.ALTER; 3.DROP 二.SQL命名规定和规范 1.标识符命名规则 2.标识符命名规范 三.库管理 1. CREATE DATABASE 数据库名; 2. CREATE DATABASE IF NOT EXISTS 数据库名; 3. CREATE…
阅读更多...
C++，STL容器适配器，priority_queue：优先队列深入解析

C++，STL容器适配器，priority_queue：优先队列深入解析

文章目录 一、容器概览与核心特性核心特性速览二、底层实现原理1. 二叉堆结构2. 容器适配器架构三、核心操作详解1. 容器初始化2. 元素操作接口3. 自定义优先队列四、实战应用场景1. 任务调度系统2. 合并K个有序链表五、性能优化策略1. 底层容器选择2. 批量建堆优化六、注意事项…
阅读更多...
django上传文件

django上传文件

1、settings.py配置 # 静态文件配置 STATIC_URL /static/ STATICFILES_DIRS [BASE_DIR /static, ]上传文件 # 定义一个视图函数，该函数接收一个 request 参数 from django.shortcuts import render # 必备引入 import json from django.views.decorators.http i…
阅读更多...
mapbox 从入门到精通 - 目录

mapbox 从入门到精通 - 目录

👨‍⚕️ 主页： gis分享者 👨‍⚕️ 感谢各位大佬 点赞👍 收藏⭐ 留言📝 加关注✅! 👨‍⚕️ 收录于专栏：mapbox 从入门到精通 文章目录 一、🍀总目录1.1 ☘️ mapbox基础1.2 ☘️…
阅读更多...
【Qt】：概述（下载安装、认识 QT Creator）

【Qt】：概述（下载安装、认识 QT Creator）

🌈 个人主页：Zfox_ 🔥 系列专栏：Qt 目录 一：🔥 介绍 🦋 什么是 QT🦋 QT 发展史🦋 Qt版本🦋 QT 优点 一：🔥 搭建Qt开发环境 &#x1f9…
阅读更多...
设置mysql的主从复制模式

设置mysql的主从复制模式

mysql设置主从复制模式似乎很容易，关键在于1）主库启用二进制日志，2）从库将主库设为主库。另外，主从复制，复制些什么？从我现在获得的还很少的经验来看，复制的内容有表，用户…
阅读更多...
halo发布文章的插件问题分析

halo发布文章的插件问题分析

前言 在准备发文到 halo 系统的时候提示错误如下，全是乱码 尝试将 halo 插件卸载后，再将插件目录下的文件全部删除 插件目录在 C:\Users\Administrator\.vscode\extensions\halo-dev.halo-1.3.0 然后再重新安装插件，在进行初始化的时候依然…
阅读更多...
Spring Data Neo4j

Spring Data Neo4j

文章目录 Spring Data Neo4j简介Neo4j-OGM与SDN的区别 开发体验版本说明项目地址项目结构创建项目配置连接信息激活事务管理器创建实体类Movie类Person类ActedIn关系类 创建Dao层service层测试案例CRUD TestPersonService TestActedIn Test 执行结果查询 Spring Data Neo4j简介…
阅读更多...
Java发展史

Java发展史

JavaEE的由来 语言的诞生 Java的前身是Oak语言，其目的是搞嵌入式开发开发智能面包机 叮~~~🍞🍞🍞 产品以失败告终 巅峰 网景公司需要网景浏览器打开网页，Oak->Java，进行前端开发（相关技…
阅读更多...
怎么让DeepSeek自动化写作文案

怎么让DeepSeek自动化写作文案

在数字化时代，内容创作已成为企业争夺用户注意力的核心竞争力。面对海量信息需求，企业往往面临内容创作效率低下、质量参差不齐、周期长等问题。如何用技术手段解决这些痛点，成为企业迫切需要破解的难题。今天，我们将以DeepSeek为…
阅读更多...
Mysql之主从复制

Mysql之主从复制

目录 1.概述 2.工作原理 3.综合案例 3.1前期准备 3.2主库配置 3.3从库配置 3.4常见问题 3.4.1主从同步出现一下错误：Slave_IO_Running: No 3.4.1主从同步出现一下错误：Slave_IO_Running: Connecting? 3.5数据测试 1.概述 MySQL的主从复制&am…
阅读更多...
从无序到有序：上北智信通过深度数据分析改善会议室资源配置

从无序到有序：上北智信通过深度数据分析改善会议室资源配置

当前企业普遍面临会议室资源管理难题，预约机制不完善和临时会议多导致资源调度不合理，既有空置又有过度拥挤现象。 针对上述问题，上北智信采用了专业数据分析手段，巧妙融合楼层平面图、环形图、折线图和柱形图等多种可视化工具&a…
阅读更多...
使用pyCharm创建Django项目

使用pyCharm创建Django项目

使用pyCharm创建Django项目 1. 创建Django项目虚拟环境（最新版版本的Django) 使用pyCharm的创建项目功能，选择Django,直接创建。 2. 创建Django项目虚拟环境（安装特定版本） 2.1创建一个基础的python项目 2.2 安装指定版本的D…
阅读更多...
RabbitMQ 在 Spring Boot中使用方式

RabbitMQ 在 Spring Boot中使用方式

文章目录 作用MQ docker 安装MQ使用RabbitMQ的整体架构及核心概念：RabbitMQ的整体架构及核心概念：消费者消息推送限制交换机与队列## 项目使用MQDirect: 直连模式Fanout: 广播模式Topic: 主题模式Headers: 头信息模式 使用DEMO地址异常问题记录 作用 Ra…
阅读更多...
力扣动态规划-30【算法学习day.124】

力扣动态规划-30【算法学习day.124】

前言 ###我做这类文章一个重要的目的还是记录自己的学习过程，我的解析也不会做的非常详细，只会提供思路和一些关键点，力扣上的大佬们的题解质量是非常非常高滴！！！ 习题 1.零钱兑换 题目链接:322. 零钱兑…
阅读更多...
AI在电竞比分网中的主要应用场景

AI在电竞比分网中的主要应用场景

AI在电竞体育比分网的数据应用非常广泛，能够显著提升数据分析、预测、用户体验和商业价值。以下是AI在电竞比分网中的主要应用场景： 1. 实时数据采集与分析 比赛数据实时更新：AI通过自动化系统实时采集比赛数据（如击杀数、经济差、…
阅读更多...
【Spring Boot】Spring 魔法世界：Bean 作用域与生命周期的奇妙之旅

【Spring Boot】Spring 魔法世界：Bean 作用域与生命周期的奇妙之旅

前言 ???本期讲解关于spring原理Bean的相关知识介绍~~~ ??感兴趣的小伙伴看一看小编主页：-CSDN博客 ?? 你的点赞就是小编不断更新的最大动力 ??那么废话不多说直接开整吧~~ 目录 ???1.Bean的作用域 ??1.1概念 ??1.2Bean的作用域 ??1.3代码演示…
阅读更多...
用 Python 实现 DeepSeek R1 本地化部署

用 Python 实现 DeepSeek R1 本地化部署

DeepSeek R1 以其出色的表现脱颖而出，不少朋友想将其本地化部署，网上基于 ollama 的部署方式有很多，但今天我要带你领略一种全新的方法 —— 使用 Python 实现 DeepSeek R1 本地化部署，让你轻松掌握，打造属于自己的 AI…
阅读更多...
软考-系统架构设计师（月更版）

软考-系统架构设计师（月更版）

1.需求管理的主要活动包括变更控制，版本控制，需求跟踪，需求状态跟踪 需求跟踪是单个需求和其他系统元素之间的依赖关系和逻辑联系建跟踪， 这些元素包括各种类型的需求、业务规则、系统架构和构件、源代码、测试用例，以…
阅读更多...
IOTDB安装部署

IOTDB安装部署

IOTDB一般用于工业互联网，至于具体的介绍请自行搜索 1.环境准备 安装前需要保证设备上配有 JDK>1.8 的运行环境，并配置好 JAVA_HOME 环境变量。 设置最大文件打开数为 65535。 关闭防火墙 systemctl stop firewalld.service systemctl disable …
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023