多表查询 学习笔记

最新推荐文章于 2024-07-25 22:24:14 发布
最新推荐文章于 2024-07-25 22:24:14 发布
阅读量903 收藏 11
点赞数 18
分类专栏： MYSQL 文章标签： 学习 笔记 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lonelyxxyo/article/details/140646450
版权

目录

1.多对多

2.一对一

3.内连接

隐式内连接

显式内连接

案例

隐式内连接

 显式内连接

4.外连接

左外连接

右外连接

案例

左外连接

右外连接

5.自连接

案例

6.联合查询

 案例

7.子查询

IN

NOT IN

ANY

SOME

ALL

标量子查询

例子

列子查询

例子

行子查询

例子

表子查询

例子

1.多对多

在数据库设计中，处理学生与课程之间的多对多关系，需要通过建立一张中间表来实现。

假设我们有两张表：学生表（student）和课程表（course）

-- 创建一个名为 `student` 的新表
CREATE TABLE student (
  -- 定义一个名为 `id` 的字段，类型为 INT，自动递增，并设置为表的主键
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  -- 定义一个名为 `name` 的字段，类型为 VARCHAR，最大长度为 10 个字符，用于存储学生姓名
  name VARCHAR(10) COMMENT '姓名',
  -- 定义一个名为 `no` 的字段，类型为 VARCHAR，最大长度为 10 个字符，用于存储学生学号
  no VARCHAR(10) COMMENT '学号'
) -- 为整个表添加注释，说明这是一个学生表
COMMENT '学生表';
-- 创建一个名为 `course` 的新表
CREATE TABLE course (
  -- 定义一个名为 `id` 的字段，类型为 INT，自动递增，并设置为表的主键
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
  -- 定义一个名为 `name` 的字段，类型为 VARCHAR，最大长度为 10 个字符，用于存储课程名称
  name VARCHAR(10) COMMENT '课程名称'
) -- 为整个表添加注释，说明这是一个课程表
COMMENT '课程表';

如需给两张表中添加数据

-- 向 student 表中插入新数据
INSERT INTO student VALUES 
  (NULL, 'xx', 'xx'),  -- 插入第一条记录，id 自动递增，姓名为xx，学号为xx
  ...  
  (NULL, 'xx', 'xx'); 
-- 向 course 表中插入新数据
INSERT INTO course VALUES 
  (NULL, 'xx'), 
  ...  
  (NULL, 'xx');

中间表设计

-- 创建一个名为 `student_course` 的新表，用于存储学生和课程之间的关联
CREATE TABLE student_course (
  -- 定义一个名为 `id` 的字段，类型为 INT，自动递增，并设置为表的主键
  id INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
  -- 定义一个名为 `studentid` 的字段，类型为 INT，不为空，用于存储学生ID
  studentid INT NOT NULL COMMENT '学生ID',
  -- 定义一个名为 `courseid` 的字段，类型为 INT，不为空，用于存储课程ID
  courseid INT NOT NULL COMMENT '课程ID',
  -- 为 `courseid` 字段添加外键约束，引用 `course` 表的 `id` 字段
  CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course (id),
  -- 为 `studentid` 字段添加外键约束，引用 `student` 表的 `id` 字段
  CONSTRAINT fk_studentid FOREIGN KEY (studentid) REFERENCES student (id)
) -- 为整个表添加注释，说明这是一个学生课程中间表
COMMENT '学生课程中间表';

-- 向 `student_course` 表中插入关联记录
INSERT INTO student_course VALUES 
  (NULL, 1, 1),  -- 插入第一条记录，自动递增ID，学生ID为1，课程ID为1
 ...
  (NULL, 3, 4);  -- 插入第n条记录，自动递增ID，学生ID为3，课程ID为4

查询某个学生的所有课程

SELECT c.name AS CourseName
FROM course c
JOIN student_course sc ON c.id = sc.courseid
JOIN student s ON sc.studentid = s.id
WHERE s.id = studentIdValue;

查询某门课程的所有学生

SELECT s.name AS StudentName
FROM student s
JOIN student_course sc ON s.id = sc.studentid
JOIN course c ON sc.courseid = c.id
WHERE c.id = courseIdValue;

2.一对一

创建两个表来表示用户与用户详情之间的一对一关系。

CREATE TABLE tb_user (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '姓名',
    age INT COMMENT '年龄',
    gender CHAR(1) COMMENT '性别，1:男，2:女',
    phone CHAR(11) COMMENT '手机号'
) COMMENT '用户基本信息表';
CREATE TABLE tb_user_edu (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    degree VARCHAR(20) COMMENT '学历',
    major VARCHAR(50) COMMENT '专业',
    primaryschool VARCHAR(50) COMMENT '小学',
    middleschool VARCHAR(50) COMMENT '中学',
    university VARCHAR(50) COMMENT '大学',
    userid INT UNIQUE COMMENT '用户ID',
    CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES tb_user(id)
) COMMENT '用户教育信息表';

插入数据同上...

--消除笛卡尔积无效部分
select * from emp , dept
where emp.dept_id = dept.id;

3.内连接

隐式内连接

隐式内连接不使用JOIN关键字，而是通过WHERE子句来指定连接条件。

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

显式内连接

显式内连接使用INNER JOIN关键字，并通过ON子句来明确指定连接条件。

SELECT 字段列表
FROM 表1
[INNER] JOIN 表2 ON 表1.字段 = 表2.字段;

案例

查询每一个员工的姓名，及关联的部门的名称

表结构: 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;

 显式内连接

-- 未使用别名的查询
SELECT e.name, d.name 
FROM emp e 
INNER JOIN dept d ON e.dept_id = d.id;

-- 使用别名的查询，简化SQL编写
SELECT e.name, d.name 
FROM emp e 
JOIN dept d ON e.dept_id = d.id;

4.外连接

左外连接

左外连接返回左表（表1）的所有记录，即使右表（表2）中没有匹配的记录。如果左表的记录在右表中没有匹配项，则结果集中的右表部分将为NULL。

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

右外连接

右外连接返回右表（表2）的所有记录，即使左表（表1）中没有匹配的记录。如果右表的记录在左表中没有匹配项，则结果集中的左表部分将为NULL。

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

案例

左外连接

查询emp表的所有数据以及对应的部门信息

表结构: emp , dept
连接条件: emp.dept_id = dept.id

-- 使用LEFT OUTER JOIN查询
SELECT e.*, d.name 
FROM emp e 
LEFT OUTER JOIN dept d ON e.dept_id = d.id;

-- 省略OUTER关键字的查询，效果相同
SELECT e.*, d.name 
FROM emp e 
LEFT JOIN dept d ON e.dept_id = d.id;

右外连接

查询dept表的所有数据以及对应的员工信息

表结构: emp , dept
连接条件: emp.dept_id = dept.id

使用右外连接，以确保即使某些部门没有员工，这些部门的信息也会被包括在查询结果中。

-- 使用RIGHT OUTER JOIN查询
SELECT d.*, e.*
FROM emp e 
RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

-- 省略OUTER关键字的查询，效果相同
SELECT d.*, e.*
FROM emp e 
RIGHT JOIN dept d ON e.dept_id = d.id;

5.自连接

SELECT a.字段1, b.字段2
FROM 表名 a, 表名 b
WHERE a.条件字段 = b.条件字段;
SELECT a.字段1, b.字段2
FROM 表名 a
JOIN 表名 b ON a.条件字段 = b.条件字段;

案例

-- 使用自连接查询员工及其所属领导的名字
SELECT a.name AS '员工', b.name AS '领导'
FROM emp a, emp b
WHERE a.managerid = b.id;
-- 使用左外连接查询所有员工及其领导的名字，包括没有领导的员工
SELECT a.name AS '员工', b.name AS '领导'
FROM emp a
LEFT JOIN emp b ON a.managerid = b.id;

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

6.联合查询

联合查询（UNION）用于将两个或多个SELECT语句的结果集合并成一个结果集

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

以下是关于联合查询的要点：

  1. 使用UNION时，会自动去除结果集中的重复行。
  2. 使用UNION ALL时，不会去除重复行，会将两个或多个查询的结果集直接合并在一起。
  3. 联合查询中，每个SELECT语句的字段数量必须相同。
  4. 每个查询中对应的字段类型应该兼容，以便在合并结果时不会出现类型错误。

 案例

使用UNION ALL联合查询薪资低于5000的员工和年龄大于50岁的员工

-- 使用UNION ALL查询薪资低于5000的员工
SELECT * FROM emp WHERE salary < 5000
UNION ALL
-- 使用UNION ALL查询年龄大于50岁的员工
SELECT * FROM emp WHERE age > 50;

 去除可能重复的员工记录（即那些既薪资低于5000又年龄大于50岁的员工）

-- 使用UNION查询薪资低于5000的员工
SELECT * FROM emp WHERE salary < 5000
UNION
-- 使用UNION查询年龄大于50岁的员工
SELECT * FROM emp WHERE age > 50;

7.子查询

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT的任何一个。

一些SQL中常用的操作符及其描述：

IN

  • 描述：IN 操作符用于确定给定的值是否与子查询或列表中的值匹配。如果值在指定的集合范围内，则返回 TRUE，否则返回 FALSE。
  • 例子： 
    SELECT * FROM emp WHERE dept_id IN (1, 2, 3);

NOT IN

  • 描述：NOT IN 操作符用于确定给定的值是否不在子查询或列表中的值匹配。如果值不在指定的集合范围内，则返回 TRUE，否则返回 FALSE。
  • 例子： 
    SELECT * FROM emp WHERE dept_id NOT IN (1, 2, 3);

ANY

  • 描述：ANY 操作符与子查询一起使用，用于比较外部查询的值与子查询返回的任何值。如果外部查询的值满足子查询返回的任意一个值，则返回 TRUE。
  • 例子： 
    SELECT * FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE dept_id = 1);

SOME

  • 描述：SOME 操作符是 ANY 的同义词，可以互换使用。它用于比较外部查询的值与子查询返回的任意值。如果外部查询的值满足子查询返回的任意一个值，则返回 TRUE。
  • 例子： 
    SELECT * FROM emp WHERE salary > SOME (SELECT salary FROM emp WHERE dept_id = 1);

ALL

  • 描述：ALL 操作符与子查询一起使用，用于比较外部查询的值与子查询返回的所有值。如果外部查询的值满足子查询返回的所有值，则返回 TRUE。
  • 例子： 
    SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = 1);

使用操作符时，需要注意子查询必须返回一个列的值，以便与外部查询进行比较。

ANY 和 SOME 在功能上是相同的，可以互换使用。ALL 则要求外部查询的值必须大于或小于（取决于比较操作符）子查询返回的所有值。

1.标量子查询

标量子查询返回单个值（一个标量）。这个值通常用于与外部查询的比较操作。

例子

查询薪资高于部门平均薪资的员工信息。

SELECT *
FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp);

2.列子查询

列子查询返回一列值，这些值通常用于与外部查询中的对应列进行比较。

例子

查询与部门ID为1的员工薪资相同的所有员工。

SELECT *
FROM emp
WHERE salary IN (SELECT salary FROM emp WHERE dept_id = 1);

3.行子查询

行子查询返回一行或多行，每行包含多个列。这个子查询的返回结果通常用于与外部查询中的行进行比较。

例子

查询与部门ID为1且薪资最高的员工具有相同薪资和部门的所有员工。

SELECT *
FROM emp
WHERE (salary, dept_id) = (SELECT MAX(salary), dept_id FROM emp WHERE dept_id = 1);

4.表子查询

表子查询返回多行多列的结果集，通常这个结果集被外部查询当作一个临时表来使用。

例子

查询薪资高于部门平均薪资的所有员工。

SELECT e.*
FROM emp e
WHERE e.salary > (SELECT AVG(salary) FROM emp WHERE dept_id = e.dept_id);
Cici_ovo
关注
  • 18
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
thinkphp学习笔记之多表查询
10-25
在学习thinkphp 的过程中，需要对多表进行操作，但是在实际过程中，总是遇到各种问题，所以写下这篇文章,作为自己的学习历程
SQL学习笔记——表结构转换查询
01-21
1、纵表转横表 建表 --先调用自建的数据库再建表 use ljh create table sc( sname char(20), course char(20), score int) --随机插入几个记录 insert into sc ...转换表结构查询 select sname as 'Nam
Mysql个人学习笔记
风筝
09-04 3万+
mysql 进阶一-基础 distinct concat ifnull #进阶1：基础查询 /* 语法： select 查询列表 from 表名; 类似于：System.out.println(打印东西); 特点： 1、查询列表可以是：表中的字段、常量值、表达式、函数 2、查询的结果是一个虚拟的表格 */ USE myemployees; #1.查询表中的单个字段 SELECT last...
数据结构与算法学习笔记
热门推荐
ityqing的博客
09-25 33万+
本文是王争老师的《算法与数据结构之美》的学习笔记，详细内容请看王争的专栏。有不懂的地方指出来，我做修改。 数据结构与算法思维导图 数据结构指的是“一组数据的存储结构”，算法指的是“操作数据的一组方法”。 数据结构是为算法服务的，算法是要作用再特定的数据结构上的。 最常用的数据结构预算法: 数据结构：数组、链表、栈、队列、散列表、二叉树‘、堆、跳表、图、Tire树 算法： 递归...
MySql学习笔记（五） - 多表查询
Code-Zhao
06-04 1173
1. 子查询    即嵌套在其他查询中的查询。SELECT与剧中，子查询总是从内向外处理。在处理上面的SELECT语句时，MySql实际上执行了两个操作。首先执行：SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'次查询返回两个订单号 20005，20007。然后这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部...
MyBatis学习笔记
深思纵似丁香结，一展芭蕉寸凡心。
01-22 1325
什么是MyBatisMyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。（定义来源官方文档）持久层框架如何获得MyBatismaven仓库GitHub。
Elasticsearch学习笔记
KISS
04-15 26万+
- - - - 第1章 Elasticsearch概述 - 01-开篇 02-技术选型 03-教学大纲 - 第2章 Elasticsearch入门 - 04-入门-环境准备 05-入门-RESTful & JSON 06-入门-Postman客户端工具 07-入门-倒排索引 08-入门-HTTP-索引-创建 09-入门-HTTP-索引-查询 & 删除 10-入门-HTTP-文档-创建（Put & Post） 11-入门-HTTP-查询-主键查询 &...
Redis全套学习笔记
05-25
Redis 通过内存管理、LRU淘汰策略、多路复用等手段保证高效性能。同时，合理配置最大内存、选择合适的持久化策略和网络架构等也至关重要。 8. 应用场景： Redis 广泛应用于缓存、计数系统、实时消息、限流系统、...
AvaloniaUI的学习
最新发布
lishuangquan1987的博客
07-25 490
官方中文文档：https://docs.avaloniaui.net/zh-Hans/docs/welcome。
编程学习之路：从零到一的成长指南
KsuferNotes
07-22 442
在当今数字化时代，编程技能已经成为许多人职业发展的重要组成部分。不论你是已经在IT业从业多年的老手，还是刚刚起步的编程新手，这篇博客将带你走过编程学习的每一个重要阶段，从而顺利完成从零到一的华丽转变。
昇思25天学习打卡营第14天|Vision Transformer图像分类
Xavier_F的博客
07-21 664
本案例详细介绍了ViT模型在ImageNet数据集上的训练、验证和推理过程，重点讲解了模型的核心结构和关键概念，如Multi-Head Attention、Transformer Encoder和位置编码等。通过学习此案例，用户将掌握ViT的基本原理以及其在图像分类任务中的应用潜力，为深入研究计算机视觉领域提供了坚实基础。!
从0开始的STM32HAL库学习6
hwq_1229_0525的博客
07-21 355
配置红色框图中的各种配置时钟源选择外部时钟 21.预分频器Prescaler，下面填0，不分频2.计数模式，计数模式选择为向上计数3.自动重装寄存器，自动重装计数器填为10，计数到10后清空并出发中断4.滤波器的频率分频：不使用影子寄存器。
FastAPI（七十一）实战开发《在线课程学习系统》接口开发-- 查看留言
mr~li的博客
07-24 218
梳理后发现，之前在接口设计的时候，有些欠缺，查看留言，是查看单个的留言，查看留言后，对应的留言变成已读状态。那么我们这个都需要传递一个参数，这个参数应该是留言的id。之前FastAPI（七十）实战开发《在线课程学习系统》接口开发--留言功能开发分享了留言开发，这次我们分享查看留言。在之前设计返回状态码的时候，发现状态码有缺失，我们增加下缺失的。3.判断是否是当前用户的留言，或者接受的方是自己。梳理这里的逻辑，这个接口要依赖登录。5.如果已读且回复了，要带回复的内容。以上则是实现查看留言的接口逻辑。
学习华为IPD流程黑话2.0
产品线负责人
07-23 523
管道管理是指根据资源需求（管道需求）尽早地对可获得性资源（管道供给）进行平衡，任何企业做研发、做产品，它的可用资源都是有限的，可以把企业的资源比喻成管道。既是对产品包进行验证，也是内部技能传递的重要环节，同时能为客户带来价值。这个时候就只需要对切片的概念做一下了解就应该可以直接应用了。而且主要做的是 C 语言，Python 也确实没具体用过。并保证在任意时刻产出的研发价值（即出口流量）的最大化，好了，这个话题就不展开来讲了，后续可以单独展开聊聊。评审的对象是新产品的业务计划，而不是产品的开发计划。
DP学习——外观模式
huibin.yun的博客
07-24 201
学而时习之，温故而知新。
后端开发工程师vue2初识的学习
音符犹如代码的博客
07-24 936
快速认识vue
【学习笔记】无人机系统（UAS）的连接、识别和跟踪（八）-无人机探测与避让（DAA）机制
u011376987的博客
07-21 990
3GPP TS 23.256 技术规范，主要定义了3GPP系统对无人机（UAV）的连接性、身份识别、跟踪及A2X（Aircraft-to-Everything）服务的支持。
学习笔记---java篇（0723）
m0_70630166的博客
07-23 762
java、数据类型、循环、选择等
昇思25天学习打卡营第17天|LLM-基于MindSpore的GPT2文本摘要
wwt72的博客
07-21 622
> wte 嵌入层：dim[in, out]=[21128, 768] ，即使用了 21128 个词汇，每个词汇映射到一个768 维的向量。层，自注意力机制，用于计算输入序列中不同位置的注意力权重。，多层感知机，用于对自注意力层的输出进行进一步的非线性变换。此次活动的 notebook 只可以连续运行8小时，此次目的也不是性能优化，故此，我将训练数据减少到了1/10，此时的部分输出如下。>> layers h 隐网络结构层：Transformer模型的主体，包含 12 个 GPT2Block。
php之多表查询,thinkphp学习笔记之多表查询
05-30
多表查询是关系型数据库中常见的操作之一。在 PHP 中，我们可以使用 SQL 语句来进行多表查询。在 ThinkPHP 框架中，我们可以使用 Query 类来生成 SQL 语句进行多表查询。 以下是一个简单的例子，假设我们有两个表，一个是用户表(users)，另一个是文章表(articles)，它们之间有外键关系(user_id)： ```php use think\Db; // 查询用户和他们的文章 $result = Db::table('users') ->alias('u') ->join('articles a', 'u.id = a.user_id') ->field('u.*, a.title') ->select(); ``` 在上面的例子中，我们使用了 `alias` 方法为 users 表设置别名 u，使用了 `join` 方法来连接 articles 表，使用了 `field` 方法来指定查询的字段。最后使用 `select` 方法来执行查询，返回一个二维数组。 我们还可以使用 Query 类的其他方法来进行更加复杂的多表查询，例如分组、排序、限制查询结果等。具体用法可以参考 ThinkPHP 官方文档中的数据库查询部分。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值