week 6 - SQL Select II

Overview

1. Joins

        包括交叉连接(Cross)、内连接(Inner)、自然连接(Natural)、外连接(Outer)

2. ORDER BY to produce ordered output

3. 聚合函数(Aggregate Functions)

        包括最小值(MIN)、最大值(MAX)、求和(SUM)、平均值(AVG)、计数(COUNT)

4.分组和过滤(GROUP BY 和 HAVING)

一、Joins

1.  连接的作用

 在 SELECT 查询(query)中,用于将多个表结合

2. 连接的类型

(1) 交叉连接(CROSS JOIN):生成表的笛卡尔积。 Cartesian product 

(2) 内连接(INNER JOIN):返回满足条件的行对。

(3) 自然连接(NATURAL JOIN):返回在列名相同的列(identically named columns)上具有相同(common)值的行。

(4)外连接(OUTER JOIN):包括内连接结果,同时还保留 (handles)NULL 值。

3. CROSS JOIN

SELECT * FROM A CROSS JOIN B;

SELECT * FROM A, B;

• 通常需要使用 WHERE 条件过滤无关的元组( WHERE clause to filter unrelated tuples)

4. INNER JOIN

• INNER JOIN specifies a condition that pairs of rows must satisfy

SELECT * FROM 

        A INNER JOIN B 

        ON condition;

SELECT * FROM 

        A INNER JOIN B

         USING (col1, col2);

• USING 子句将输出两表中指定列相等的行。

• col1 and col2 must appear in both A and B

Example

SELECT * FROM 

        Buyer INNER JOIN Property 

        ON Price <= Budget;

5. NATURAL JOIN

SELECT * FROM

         A NATURAL JOIN B;

• 自然连接是内连接(INNER JOIN)的特殊情况,连接条件是所有列名相同的列自动匹配。

A NATURAL JOIN is effectively a special case of an INNER JOIN where the USING clause specifies all identically named columns.

• 等价关系(Equivalence)

 在关系代数中,NATURAL JOIN 与 表示的自然连接操作是等价的。

Example

 

6. 连接(JOINS)与 WHERE 子句的对比

(1) 内连接和自然连接不是绝对必要的。

(2) 可以通过在多个表中选择数据并使用适当的 WHERE 子句,获得相同的结果。

JOINS 通常能让查询更加简洁和优雅。

NATURAL JOINS 在 SQL 查询中非常常见。common

不同的数据库管理系统(DBMS)对 JOINS 的支持可能不同。

7. OUTER JOIN

SELECT cols FROM

         table1 type OUTER JOIN table2 

        ON condition;

 type 可以是 LEFT、RIGHT 或 FULL。

(1) Left Outer Join

SELECT * FROM 

        Student LEFT OUTER JOIN Enrolment

        ON Student.ID = Enrolment.ID;

(2) Right Outer Join

(3) Full Outer Join 

 

• MySQL 不支持 FULL OUTER JOIN,可通过 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 的 UNION 模拟。

Only Left and Right outer joins are supported in MySQL. If you really want a FULL outer join

SELECT * 

FROM Student FULL OUTER JOIN Enrolment

ON Student.ID = Enrolment.ID; 

(SELECT * FROM Student LEFT OUTER JOIN

Enrolment ON Student.ID = Enrolment.ID)

UNION

(SELECT * FROM Student RIGHT OUTER JOIN

Enrolment ON Student.ID = Enrolment.ID);

Practice 

Why Using Outer Joins?

外连接的实用性:

在处理某些情况下,外连接是一种更为实用的方法,例如当表中存在 NULL 值时,仍希望保留这些记录的信息。

内连接的局限性

忽略未匹配的记录:

内连接只返回两张表中满足匹配条件的记录,未匹配的记录会被忽略。

外连接的作用:

LEFT OUTER JOIN

左外连接不仅返回匹配的记录,还保留左表中所有未匹配的记录,并用 NULL 填充右表的数据。

多表外连接:

可以连续对多个表执行左外连接,确保保留左表中所有记录,同时逐步添加其他表中的信息。

Solution Using OUTER JOIN

查找 Grade = 'A' 的学生,并输出他们的 ID、姓名、地址(街道、城市、邮编)、电话号码和学位分类。

SELECT ID, Name, aStreet, aTown, aPostcode, pNumber, Classification

FROM

    (Student LEFT OUTER JOIN Phone

    ON Student.pID = Phone.pID)

LEFT OUTER JOIN Address

ON Student.aID = Address.aID

INNER JOIN Degree

ON Student.ID = Degree.ID

WHERE Grad = 'A';

• 第一步:通过 LEFT OUTER JOIN 连接 Student 和 Phone,保留所有学生记录,同时添加电话号码信息。

• 第二步:连接 Student 和 Address,保留(retained所有学生信息,同时补充地址。

• 第三步:通过内连接(INNER JOIN)连接 Degree 表,过滤(filter出满足 Grad = 'A' 的学生。

• Mark 和 Sam 的记录被保留,即使他们分别缺少电话号码和地址。

通过组合 LEFT OUTER JOIN 和 INNER JOIN,可以在保证数据完整性的同时实现精确过滤。

 

二、ORDER BY

SELECT

        [DISTINCT | ALL] column-list 

        FROM table-names 

        [WHERE condition] 

        [GROUP BY column-list] 

        [HAVING condition] 

        [ORDER BY column-list]

([] optional, | or) 

SELECT columns FROM tables 

        WHERE condition 

        ORDER BY cols [ASC | DESC];

• ORDER BY 子句用于对查询结果进行排序。(The ORDER BY clause is used to sort the results of a query)

• 可以指定多个列进行排序,优先按第一列排序,然后按第二列,以此类推。

sort by multiple columns, first by the first column, then the second, and so on.

SELECT Name, Grade 
FROM Students 
ORDER BY Grade ASC, Name DESC;

• 默认是升序(ASC,ascending),也可以使用降序(DESC,descending)

Examples

(1) Sort by marks in ascending order:

SELECT * FROM Grades

         ORDER BY Mark;

(2)ORDER BY 的列应该是 SELECT 结果中包含的列

虽然SQL 的灵活性允许你按照 不在结果中显示的列 或 表达式 排序

SELECT y / 100 AS y2 
        FROM a 
        ORDER BY y DESC;

三、聚合函数(Aggregate Functions)

put arithmetic expressions ​​​​​​​in SELECT.

1. Common Functions:

(1)COUNT:计算行数或列中非 NULL 值的数量。

• COUNT(*) 统计所有行的数量,无论列值是否为 NULL。

• COUNT(column_name) 统计某一列中非 NULL 值的数量。

• COUNT(DISTINCT column):统计列中唯一值的数量,忽略重复项和NULL 值

(2)SUM:计算列中值的总和(sum up)

(3) AVG:计算列的平均值。

(4) MIN/MAX:找出列中的最小值或最大值。

大多数聚合函数(SUMAVG 等)只对单个数值列有效,且只处理数值数据。

work on a single column of numerical data

• COUNT(*) 是一个例外,它不依赖列的类型,可以统计整个表中的行数。

2. Examples

(1) COUNT

(2) SUM,MIN/MAX and AVG 

3. Combining Aggregate Functions

SELECT 

        MAX(Mark) - MIN(Mark) 

                AS Range_of_marks

        FROM Grades;

SELECT SUM(Mark * Credits) / SUM(Credits)

                  AS 'Final Mark'
FROM Modules, Grades
WHERE Modules.Code = Grades.Code
      AND Grades.Name = 'John';

四、分组和过滤(GROUP BY 和 HAVING)

1. GROUP BY 的作用

• 按某列分组并对每组应用聚合函数。

SELECT column_set1 
        FROM tables
        WHERE predicate
        GROUP BY column_set2;

SELECT 语句中的每一项(即 column_set1)要满足:

(1)在 GROUP BY 子句中明确列出(位于 column_set2 中)

(2) 为常量值

(3) 为聚合函数的结果

违反规则的查询

SELECT Name, Subject, SUM(Mark) AS TotalMark
FROM Grades
GROUP BY Name;

• 对于分组后的每一组,Subject 有多个值(如 John 组包含 Math 和 Physics),SQL 不知道该返回哪个值。 

2. HAVING 的作用

• 类似于 WHERE,但 HAVING 是对分组(grouping)后的结果进行过滤。

WHERE and HAVING

(1)WHERE 子句:

• 用于过滤表中的行。

• 在分组和聚合操作之前执行。

• 不能使用聚合函数(例如 SUM() 或 AVG())。

(2) HAVING 子句:

• 用于过滤分组后的结果。

• 在分组和聚合操作之后执行。

• 可以使用聚合函数过滤组。

• Think of a query being processed as follows: 

1. Tables are joined (JOIN)

2. WHERE clauses(WHERE)

3. GROUP BY clauses and aggregates (GROUP BY)

4. Column selection (SELECT)

5. HAVING clauses (HAVING)

6. ORDER BY(ORDER BY)

 SELECT SUM(Amount) AS TotalSales
FROM Sales
WHERE Category = 'Food';

SELECT Category, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Category
HAVING SUM(Amount) > 300;

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

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

相关文章

systemverilog约束中:=和:/的区别

“x dist { [100:102] : 1, 200 : 2, 300 : 5}” 意味着其值等于100或101或102或200或300其中之一&#xff0c; 其权重比例为1:1:1:2:5 “x dist { [100:102] :/ 1, 200 : 2, 300 : 5}” 意味着等于100&#xff0c;101&#xff0c;102或200&#xff0c;或300其…

[Python/网络安全] Git漏洞之Githack工具基本安装及使用详析

前言 本文仅分享Githack工具基本安装及使用相关知识&#xff0c;不承担任何法律责任。 Git是一个非常流行的开源分布式版本控制系统&#xff0c;它被广泛用于协同开发和代码管理。许多网站和应用程序都使用Git作为其代码管理系统&#xff0c;并将其部署到生产环境中以维护其代…

NFT Insider #157:The Sandbox 开启新一期 VoxEdit 比赛

市场数据 加密艺术及收藏品新闻 Artnames 项目上线&#xff0c;将用户姓名转化为个性化 NFT 艺术品 由知名数字艺术家 Arrotu 发起的生成艺术项目「Artnames」正式上线&#xff0c;利用区块链技术将用户姓名转化为独一无二的 NFT 艺术品。该项目于 11 月 14 日启动&#xff0…

计算机是如何工作的

1. 冯诺依曼体系 CPU 中央处理器: 进行算术运算和逻辑判断 存储器: 分为外存和内存, 用于存储数据(使用二进制方式存储) 输入设备: 用户给计算机发号施令的设备 输出设备: 计算机个用户汇报结果的设备 1&#xff09;针对存储空间&#xff1a; 硬盘 > 内存 >> CPU …

简单好用的折线图绘制!

折线图的概念及作用&#xff1a; 折线图&#xff08;Line Chart&#xff09;是一种常见的图表类型&#xff0c;用于展示数据的变化趋势或时间序列数据。它通过一系列的数据点&#xff08;通常表示为坐标系中的点&#xff09;与这些点之间的线段相连&#xff0c;直观地展示变量…

【拥抱AI】Milvus 如何处理 TB 级别的大规模向量数据?

处理 TB 级别的大规模向量数据是 Milvus 的核心优势之一。Milvus 通过分布式架构、高效的索引算法和优化的数据管理策略来实现这一目标。下面将详细介绍 Milvus 如何处理 TB 级别向量数据的流程&#xff0c;包括插入代码示例、指令以及流程图。 1. 分布式架构 Milvus 使用分…

Scrapy管道设置和数据保存

1.1 介绍部分&#xff1a; 文字提到常用的Web框架有Django和Flask&#xff0c;接下来将学习一个全球范围内流行的爬虫框架Scrapy。 1.2 内容部分&#xff1a; Scrapy的概念、作用和工作流程 Scrapy的入门使用 Scrapy构造并发送请求 Scrapy模拟登陆 Scrapy管道的使用 Scrapy中…

k8s集群部署metrics-server

1、Metrics Server介绍 Metrics Server 是集群级别的资源利用率数据的聚合器。从 Kubelets收集资源指标&#xff0c;并通过 Metrics API 在 Kubernetes apiserver 中公开它们&#xff0c;以供 Horizontal Pod Autoscaler 和Vertical Pod Autoscaler 使用。 Metrics API 也可以…

什么是串联谐振

比如有一个由电阻、电容和电感的串联电路中&#xff0c;存在一个频率能使这个电路的电流最大&#xff0c;这个现象就叫谐振。 那么这个频率是多少呢&#xff1f; 交流电频率与电路固有频率一致时&#xff0c;它就能发生谐振&#xff0c;此时这个电路的电流是最大的 这个固有频…

(vue)启动项目报错The project seems to require pnpm but it‘s not installed

(vue)启动项目报错The project seems to require pnpm but it’s not installed 原因 该错误信息表明你的项目需要使用 pnpm 作为包管理工具&#xff0c;但系统中尚未安装 pnpm。 解决方法 【1】删除pnpm.lock 【2】npm install -g pnpm 之后再重新启动 yarn报错&#xff0…

Laravel8.5+微信小程序实现京东商城秒杀方案

一、商品秒杀涉及的知识点 鉴权策略封装掊口访问频次限制小程序设计页面防抖接口调用订单创建事务使用超卖防御 二、订单库存系统方案&#xff08;3种&#xff09; 下单减库存 优点是库存和订单的强一致性&#xff0c;商品不会卖超&#xff0c;但是可能导致恶意下单&#xff…

JVM:即时编译器,C2 Compiler,堆外内存排查

1&#xff0c;即时编译器 1.1&#xff0c;基本概念 常见的编译型语言如C&#xff0c;通常会把代码直接编译成CPU所能理解的机器码来运行。而Java为了实现“一次编译&#xff0c;处处运行”的特性&#xff0c;把编译的过程分成两部分&#xff0c;首先它会先由javac编译成通用的…

屏幕分辨率|尺寸|颜色深度指纹修改

一、前端通过window.screen接口获取屏幕分辨率 尺寸 颜色深度&#xff0c;横屏竖屏信息。 二、window.screen c接口实现&#xff1a; 1、third_party\blink\renderer\core\frame\screen.idl // https://drafts.csswg.org/cssom-view/#the-screen-interface[ExposedWindow ] …

【C#设计模式(15)——命令模式(Command Pattern)】

前言 命令模式的关键通过将请求封装成一个对象&#xff0c;使命令的发送者和接收者解耦。这种方式能更方便地添加新的命令&#xff0c;如执行命令的排队、延迟、撤销和重做等操作。 代码 #region 基础的命令模式 //命令&#xff08;抽象类&#xff09; public abstract class …

Linux基础项目包含(DNS,FTP,Samba)

今天我们来实现一个linux的基础项目&#xff0c;多做项目可以加深我们对每个服务的掌握程度&#xff0c;能更加的熟悉各个服务的使用&#xff0c;以及每个服务之间如何相互应用&#xff0c;我们可以有一个清晰的认知&#xff0c;那么接下来我们就开始项目的实操。 一、 项目背…

鸿蒙面试 --- 性能优化

性能优化可以从三个方面入手 感知流畅、渲染性能、运行性能 感知流畅 在应用开发中&#xff0c;动画可以为用户界面增添生动、流畅的交互效果&#xff0c;提升用户对应用的好感度。然而&#xff0c;滥用动画也会导致应用性能下降&#xff0c;消耗过多的系统资源&#xff0c;…

UWB数字钥匙安全测距和场景应用

1. CCC数字钥匙 2021年7月CCC将UWB定义为第三代数字钥匙的核心技术&#xff0c;并发布CCC R3&#xff08;第三代数字钥匙&#xff09;规范。 CCC R3是基于NFC/BLE/UWB作为基础的无线电技术的使用&#xff0c;该系统采用非对称密码技术对车辆和设备进行相互签名认证&#xff0…

使用Compose Multiplatform开发跨平台的Android调试工具

背景 最近对CMP跨平台很感兴趣&#xff0c;为了练手&#xff0c;在移动端做了一个Android和IOS共享UI和逻辑代码的天气软件&#xff0c;简单适配了一下双端的深浅主题切换&#xff0c;网络状态监测&#xff0c;刷新调用振动器接口。 做了两年多车机Android开发&#xff0c;偶…

ubuntu22开机自动登陆和开机自动运行google浏览器自动打开网页

一、开机自动登陆 1、打开settings->点击Users 重启系统即可自动登陆桌面 二、开机自动运行google浏览器自动打开网页 1、安装google浏览器 sudo wget https://dl.google.com/linux/direct/google-chrome-stable_current_amd64.deb sudo dpkg -i ./google-chrome-stable…

前端学习笔记之文件下载(1.0)

因为要用到这样一个场景&#xff0c;需要下载系统的使用教程&#xff0c;所以在前端项目中就提供了一个能够下载系统教程的一个按钮&#xff0c;供使用者进行下载。 所以就试着写一下这个功能&#xff0c;以一个demo的形式进行演示&#xff0c;在学习的过程中也发现了中文路径…