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：找出列中的最小值或最大值。

大多数聚合函数（SUM、AVG 等）只对单个数值列有效，且只处理数值数据。

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;