数据库（MySQL）—— 多表查询

server/2024/10/21 9:16:29/

数据库（MySQL）—— 多表查询

  • 多表关系
  • 一对多
  • 多对多
  • 一对一
  • 多表查询概述
    • 数据准备
    • 查询形式
    • 笛卡尔积
  • 分类
    • 连接查询
      • 内连接
      • 外连接
        • 左外连接
        • 右外连接
      • 自连接
      • 联合查询

今天我们来进入MySQL中一个非常重要的部分：多表查询：

多表关系

多表关系是数据库设计中常见的概念，指的是在关系型数据库中，两个或多个数据表之间存在的关联关系。这些关系可以是一对一（1:1）、一对多（1:N）或多对多（M:N）等类型。多表关系的建立有助于实现数据的规范化存储和高效查询。

  1. 一对一关系（1:1）：在这种关系中，一个表中的记录与另一个表中的记录有且仅有一个对应关系。例如，一个学生表（Student）和一个学生详情表（StudentDetail），每个学生都有一个唯一的详情记录，反之亦然。
  2. 一对多关系（1:N）：在这种关系中，一个表中的一条记录可以与另一个表中的多条记录相关联。例如，一个班级表（Class）和学生表（Student），一个班级可以有多个学生，但每个学生只能属于一个班级。
  3. 多对多关系（M:N）：在这种关系中，一个表中的一条记录可以与另一个表中的多条记录相关联，反之亦然。例如，一个学生表（Student）和一个课程表（Course），一个学生可以选修多门课程，同时一门课程也可以被多个学生选修。为了实现这种关系，通常需要引入一个中间表（如StudentCourse），该表记录学生和课程之间的关联信息。

在数据库设计中，通过定义主键（Primary Key）和外键（Foreign Key）来建立和维护多表关系。主键用于唯一标识一个表中的记录，而外键则用于在一个表中引用另一个表的主键。当两个表之间存在关联关系时，可以在一个表的外键列中存储另一个表的主键值，从而实现表的关联查询。

通过合理地设计多表关系，可以提高数据库的查询性能和数据完整性。同时，多表关系也有助于实现数据的逻辑分离和模块化，使得数据库结构更加清晰易懂。

一对多

  • 案例: 部门 与 员工的关系
  • 关系: 一个部门对应多个员工，一个员工对应一个部门
  • 实现: 在多的一方建立外键，指向一的一方的主键

对应的SQL语句如下：

-- 创建emp表
CREATE TABLE emp(-- id号id      int PRIMARY KEY AUTO_INCREMENT COMMENT '员工id',name    VARCHAR(10) UNIQUE COMMENT '员工姓名',age     TINYINT UNSIGNED COMMENT '员工年龄',dept_id TINYINT UNSIGNED COMMENT '部门编号',-- 外键CONSTRAINT fk_key_dept_id FOREIGN KEY(dept_id) REFERENCESdept(id)
)COMMENT '员工表';-- 部门表
CREATE TABLE dept(id TINYINT UNSIGNED PRIMARY KEY  COMMENT '部门编号',name VARCHAR(10) COMMENT '部门名字'
)COMMENT '部门表';

在这里插入图片描述

多对多

  • 案例: 学生 与 课程的关系
  • 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
  • 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
CREATE TABLE students(id int UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '序号',name VARCHAR(10) UNIQUE COMMENT '学生姓名',no int UNSIGNED UNIQUE COMMENT '学生学号'
)COMMENT '学生表';-- 选课表
CREATE TABLE courses(id int PRIMARY KEY AUTO_INCREMENT COMMENT '序号',name VARCHAR(10) COMMENT '课程名字'
)COMMENT '选课表';-- 中间表
CREATE TABLE stu_cour(id int PRIMARY KEY AUTO_INCREMENT COMMENT '序号',studentid int UNSIGNED NOT NULL COMMENT '学生ID',courseid int  NOT NULL COMMENT '课程ID',-- 外键CONSTRAINT fk_stu_no FOREIGN KEY (studentid) REFERENCESstudents(id),CONSTRAINT  fk_cour_no FOREIGN KEY (courseid) REFERENCEScourses(id)
)COMMENT '学生选课详情表';

在这里插入图片描述

一对一

  • 案例: 用户 与 用户详情的关系
  • 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
  • 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
CREATE TABLE tb_user(id int PRIMARY KEY AUTO_INCREMENT COMMENT '序号',age TINYINT UNSIGNED COMMENT '年龄',name VARCHAR(10) NOT NULL COMMENT '姓名',gender char(1) NOT NULL COMMENT '性别',phone char(11) UNIQUE COMMENT '电话号码'
)COMMENT '用户基本信息表';CREATE TABLE tb_user_edu(id int PRIMARY KEY AUTO_INCREMENT COMMENT '序号',degree VARCHAR(10) NOT NULL COMMENT '学历',major VARCHAR(10) NOT NULL COMMENT '学位',primaryschool VARCHAR(20) NOT NULL COMMENT '小学',middleschool VARCHAR(20) NOT NULL COMMENT '中学',university VARCHAR(20) NOT NULL COMMENT '大学',userid int UNIQUE COMMENT '用户编号',CONSTRAINT fk_to_id FOREIGN KEY(userid) REFERENCEStb_user(id) -- 外键
)COMMENT '用户教育信息表';insert into tb_user(id, name, age, gender, phone) values
(null,'黄渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'码云',55,'1','18800008888'),
(null,'李彦宏',50,'1','18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
university, userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

在这里插入图片描述

多表查询概述

数据准备

删除之前 emp, dept表的测试数据
执行如下脚本，创建emp表与dept表并插入测试数据

DROP TABLE IF EXISTS emp,dept;
create table dept(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '部门名称'
)comment '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办'), (6, '人事部');-- 创建emp表，并插入数据
create table emp(id int auto_increment comment 'ID' primary key,name varchar(50) not null comment '姓名',age int comment '年龄',job varchar(20) comment '职位',salary int comment '薪资',entrydate date comment '入职时间',managerid int comment '直属领导ID',dept_id int comment '部门ID'
)comment '员工表';-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

查询形式

原来查询单表数据，执行的SQL形式为：select * from emp;

那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept; 具体的执行结果如下:
在这里插入图片描述
此时,我们看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录
(17) 与 部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。接下来，就来简单介绍下笛卡尔积。

笛卡尔积

笛卡尔积（Cartesian Product）是数学中的一个概念，它表示两个集合A和B的所有可能的有序对的集合。用符号表示为A × B，其中“×”表示笛卡尔积运算。具体来说，A × B = {(a, b) | a ∈ A 且 b ∈ B}。

例如，设集合A = {1, 2}，集合B = {a, b, c}，则A × B = {(1, a), (1, b), (1, c), (2, a), (2, b), (2, c)}。

在这里插入图片描述
而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。
在这里插入图片描述
在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可：

select * from emp , dept where emp.dept_id = dept.id;

在这里插入图片描述

分类

连接查询

  • 内连接：相当于查询A、B交集部分数据
  • 外连接：
    左外连接：查询左表所有数据，以及两张表交集部分数据
    右外连接：查询右表所有数据，以及两张表交集部分数据
    自连接：当前表与自身的连接查询，自连接必须使用表别名

我们先来介绍内连接：

内连接

在这里插入图片描述
内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构。

隐式内连接：

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

显式内连接：

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ; 1

案例:

A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id

select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;

在这里插入图片描述
我们也可以取别名简化操作：

-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

在这里插入图片描述
外连接：

B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) — INNER JOIN …ON …
表结构: emp , dept
连接条件: emp.dept_id = dept.id

select emp.name,dept.id
from emp inner join
dept on emp.dept_id = dept.id;

在这里插入图片描述

表的别名:
tablea as 别名1 , tableb as 别名2 ;
tablea 别名1 , tableb 别名2 ;

外连接

在这里插入图片描述
外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：

左外连接

案例:
A. 查询emp表的所有数据, 和对应的部门信息
由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

select * from
emp left join
dept on emp.dept_id = dept.id;

在这里插入图片描述

右外连接

案例

查询dept表的所有数据, 和对应的员工信息(右外连接)
由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;

在这里插入图片描述

注意事项：
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

而对于自连接查询，可以是内连接查询，也可以是外连接查询。

案例：

A. 查询员工 及其 所属领导的名字
表结构: emp

select a.name, b.name from emp a,emp b
where a.managerid = b.id;

或者这样也行：

select a.name,b.name from emp a
inner join emp b
on a.managerid = b.id;

在这里插入图片描述

查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
表结构: emp a , emp b

这个时候我们可以用上左外连接：

select a.name,b.name from emp a
left join emp b
on a.managerid = b.id;

在这里插入图片描述

注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

联合查询

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

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

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

案例:

A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 那这里呢，我们也可以通过union/union all来联合查询.

在这里插入图片描述
union all查询出来的结果，仅仅进行简单的合并，并未去重。

select * from emp where salary < 5000
union
select * from emp where age > 50;

在这里插入图片描述
union 联合查询，会对查询出来的结果进行去重处理。


http://www.ppmy.cn/server/31920.html

相关文章

vue3中reactive和ref的比较

vue3中reactive和ref的比较

reactiveref❌ 只支持对象和数组&#xff08;引用数据类型&#xff09;✅ 支持基本数据类型 引用数据类型✅ 在 <script> 和 <template> 中无差别使用✅ 支持基本数据类型 引用数据类型❌ 重新分配一个新对象会丢失响应性✅ 重新分配一个新对象不会失去响应能直接…
阅读更多...
深入探索Element-UI：构建高效Web前端的利器

深入探索Element-UI：构建高效Web前端的利器

深入探索Element-UI&#xff1a;构建高效Web前端的利器 引言&#xff1a;前端框架的选择与Element-UI的定位一、Element-UI初探二、快速上手&#xff1a;安装与配置三、核心组件深度解析四、实用功能与进阶技巧五、性能优化与最佳实践六、实战案例分析七、与其他技术栈的集成 安…
阅读更多...
Oracle 数据库全面升级为 23ai

Oracle 数据库全面升级为 23ai

从 11g 到 12c 再到 19c&#xff0c;今天&#xff0c;我们迎来了 23ai &#xff01; “ Oracle AI Vector Search allows documents, images, and relational data that are stored in mission-critical databases to be easily searched based on their conceptual content Ge…
阅读更多...
深度学习之基于Tensorflow卷积神经网络公共区域行人人流密度可视化系统

深度学习之基于Tensorflow卷积神经网络公共区域行人人流密度可视化系统

欢迎大家点赞、收藏、关注、评论啦 &#xff0c;由于篇幅有限&#xff0c;只展示了部分核心代码。 文章目录 一项目简介 二、功能三、系统四. 总结 一项目简介 一、项目背景 在公共区域&#xff0c;如商场、火车站、地铁站等&#xff0c;人流密度的监控和管理对于确保公共安全…
阅读更多...
MongoDB聚合运算符：$sum

MongoDB聚合运算符：$sum

MongoDB聚合运算符&#xff1a;$sum 文章目录 MongoDB聚合运算符&#xff1a;$sum语法使用返回的数据类型非数值或缺失字段的处理数组操作数 举例应用于$group阶段应用于$project阶段应用于$setWindowFields阶段 $sum聚合运算符返回数值的合计值&#xff0c;计算式 $sum会忽略…
阅读更多...
react18子组件设置接收默认值和值类型验证

react18子组件设置接收默认值和值类型验证

父组件传值 import ChildCom from ./components/ChildCom export default function Person {return(<div><ChildCom name"alan-ben" age{18} score{[98, 97, 100]} /></div>) } 子组件接收并验证类型 import React from react import PropTypes…
阅读更多...
《人大金仓数据库》未来发展的展望

《人大金仓数据库》未来发展的展望

《人大金仓数据库》作为中国社会科学院经济研究所主办的重要数据平台&#xff0c;具有广泛的学术影响力和社会价值。未来&#xff0c;随着信息技术的不断发展和应用场景的不断拓展&#xff0c;人大金仓数据库将迎来更加广阔的发展空间和机遇。本文将对《人大金仓数据库》未来发…
阅读更多...
Linux服务器常用命令总结

Linux服务器常用命令总结

view查找日志关键词 注意日志级别&#xff0c;回车后等一会儿&#xff0c;因为文件可能比较大加载完需要时间 当内容显示出来后&#xff0c;使用“/关键词”搜索 回车就能搜到&#xff0c;n表示查找下一个&#xff0c;N表示查找上一个 find 查找 find Family -name book …
阅读更多...
最新文章

Copyright @ 2022~2023