小白学习MySQL Day17-18 20240903-0904

小白stepbystep

已于 2024-09-04 20:04:58 修改

阅读量806

点赞数 25

文章标签：学习

于 2024-09-04 12:00:35 首次发布

本文链接：https://blog.csdn.net/stepbystep_0/article/details/141871489

版权

一、集合操作 Set Operations

概念

在数据库查询中，可以将查询结果视为集合。

集合操作：是对两个或多个集合进行的操作，以产生新的集合。

常见的集合操作包括并集（Union）、交集（Intersect）和差集（Except）。

1、UNION

UNION操作用于合并两个或多个 SELECT语句的结果集，并 自动去除重复的行。

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2 ;

注意：

每个SELECT语句中的列数必须相同。

列的数据类型也必须相似。

默认去除重复行，若需包含重复行，可使用UNION ALL。

示例：

第一步，计算每个学生的平均成绩：

1.按名字分组 GROUP BY Name。

2. 对于每一个组，AVG(Mark)会遍历组中的所有Mark值，计算均值，并将这个值返回。

3.使用别名指定平均值的列名 AVG(Mark) AS Average。

4.输出SELECT列表。

第二步，计算所有学生的平均成绩：

1.AVG(MARK)会遍历Mark列中的所有值，计算均值，并将这个值返回。

2.使用别名指定平均值的列名 AVG(Mark) AS Average。

3. 选择一个硬编码的字符串 'Total' 作为名字，

将总平均值作为一个特殊的条目添加到结果集中。

第三步，将上述两个查询的结果合并为一个单一的结果集。

2、INTERSECT (MySQL不直接支持)

3、EXCEPT（MySQL不直接支持）

二、处理缺失或未知的数据

1、NULL

NULL 是缺失或未知属性值的占位符。它本身不是一个值。

代表了一个属性的状态，该状态当前是未知的或者对于这个元组（记录）来说是不适用的。

情境：知道存在某个值，但不知道具体是什么。

示例：比如，一个新员工刚被加入，但尚未决定他属于哪个分支机构。

两种类型的NULL值：

A-marks：数据适用但未知（例如，某人的年龄）。

I-marks：数据不适用（例如，没有电话的人的电话号码，或者未婚人士的配偶姓名）。

• 选择操作 Selection operation: if we check tuples for “Mark > 40” and for some tuple Mark is NULL, do we include it?

任何与NULL的比较操作都会返回NULL（即未知），而不是 TRUE 或 FALSE 。

如果 Mark 是 NULL ，则“Mark > 40”的结果也是 NULL ，通常这样的元组不会被包含在结果集中。

• 元组比较 Comparing tuples in two relations：are two tuples (with NULLs) and the same or not?

直接使用=来比较两个NULL值会返回NULL（即未知），而不是TRUE。

因此，NULL值的比较需要特别处理，使用IS NULL 或 IS NOT NULL来检查NULL值。

• 重复项 NULL s treated as duplicates？

NULL值通常不被视为重复项。使用 GROUP BY 或 DISTINCT 时，如果没有明确指定如何处理 NULL 值，则所有 NULL 值可能被视为一个组或一个单一的重复项。

COUNT(*)：求行数。包括所有行，无论列值是否为NULL。

COUNT(column_name)：只计算指定列中非NULL值的行数。

SUM和AVG：忽略NULL值，因为NULL不是有效的数值数据。

• 算术运算 Arithmetic operations behaviours with argument NULL：

任何与NULL进行的算术运算都会返回NULL，因为NULL表示未知值，与任何值运算的结果也是未知的。

使用“三值逻辑”代替经典的两值逻辑来评估条件：

• 当没有NULL值参与时，条件评估为真或假。

• 如果涉及到NULL值，条件可能会评估为第三个值（'未定义'或'未知'）。

E.g.

(1) SQL NULL in Conditions (WHERE predicate):

(2) SQL NULLs in Arithmetic:

(3) SQL NULLs in Aggregation:

(4) SQL NULLs in GROUP BY:

(5) SQL NULLs in ORDER BY:

若与未知进行：AND,OR,比较操作,算术运算，则结果为未知。

2、DEFAULT

为表中的列指定在没有明确提供值时自动填充的值。这种机制有助于减少NULL值的使用。

NULL通常表示“未知”或“缺失”的数据。

可以选择一个在实际应用中具有明确含义的DEFAULT值来替代NULL。

VARCHAR类型的一些示例默认值: 为数据提供了更多的上下文和清晰度

none（无）
unknown（未知）
not supplied（未提供）
not applicable（不适用）

SQL allows both NULL s and defaults:

• A table to hold data on employees

• All employees have a name

• All employees have a salary (default 5000)

• Some employees have phone numbers, if not we use NULL s

CREATE TABLE employee

(

Name VARCHAR(50) NOT NULL,

Salary INT DEFAULT 5000 NOT NULL,

Phone VARCHAR(15) NULL

);

• SQL allows you to insert NULL s:

INSERT INTO employee

VALUES ('John', 12000, NULL);

UPDATE employee SET phone = NULL

WHERE name = 'Mark';

• Check for NULL s:

SELECT name FROM employee

WHERE phone IS NULL;

SELECT name FROM employee

WHERE phone IS NOT NULL;

三、实例:SQL查询的进阶应用

Finalists(third year) should be treated differently to other years.

• Write one SELECT for the finalists

• Write a second SELECT for the first and second years

• Merge the results using a UNION

第一步，搭建大框架

QUERY FOR FINALISTS

UNION

QUERY FOR OTHERS

第二步，表连接

方法1：NATURAL JOIN operation

方法2：CROSS JOIN and WHERE clause

• Both subqueries need information from all the tables.

• The student ID, name and year

• The marks for each module and the year taken

• The number of credits for each module

第三步，过滤查询结果

方法1：INNER JOIN连接

SELECT some_information
FROM Student AS s #使用别名，便于后续多次引用时简化查询
INNER JOIN Grade AS g
ON s.ID = g.ID
INNER JOIN Module AS m
ON g.Code = m.Code
WHERE g.YearTaken IN (2,3) AND s.Year = 3 
#过滤出特定学生的特定成绩：当前处于第三年的学生，他们在第二年和第三年的成绩记录。
UNION
SELECT some_information
FROM Student AS s
INNER JOIN Grade AS g
ON s.ID = g.ID
INNER JOIN Module AS m
ON g.Code = m.Code
WHERE g.YearTaken=s.Year AND s.Year IN (1,2);
#过滤出特定学生的特定成绩：当前处于第一或第二年的学生，他们在第一年或第二年的成绩记录。

方法2：CROSS JOIN and WHERE clause

SELECT some_information
FROM Student, Module, Grade
WHERE Student.ID = Grade.ID
AND Module.Code = Grade.Code
AND YearTaken IN (2,3)
AND Year = 3
#过滤出特定学生的特定成绩：当前处于第三年的学生，他们在第二年和第三年的成绩记录。
UNION
SELECT some_information
FROM Student, Module, Grade
WHERE Student.ID = Grade.ID
AND Module.Code = Grade.Code
AND YearTaken = Year
AND Year IN (1,2);
#过滤出特定学生的特定成绩：当前处于第一或二年的学生，他们在第一年或第二年的成绩记录。

第四步，分组与聚合

GROUP BY 
    Year, Student.ID, First, Last

计算Finalists(third year)学生的平均分：40%的second year成绩均值+60%的third year成绩均值

The average is difficult 平均值计算困难

• We don’t have any statements to separate years 2 and 3 easily 没有明确的陈述来区分年份

• We can exploit the fact that 40 = 20 * 2 and 60 = 20 * 3, so YearTaken and the weighting

have the same relationship.

利用数字关系来间接处理数据：第2年权重为2，第3年权重为3，且都基于20这个基数。

百分制加权平均分=（课程1成绩*该课程学分+课程2成绩*该课程学分+……）/总学分

Finalists(third year)学生的百分制加权平均分：

SUM(((20*YearTaken)/100)*Mark*Credits)/120 AS AverageMark

First or second year学生的百分制加权平均分：

SUM(Mark*Credits)/120 AS AverageMark

第五步，写SELECT列表

第六步，排序

ORDER BY
Year desc, AverageMark desc, Last, First, ID;

最终结果

SELECT 
    Year, Student.ID, Last, First,
    SUM(((20*YearTaken)/100)*Mark*Credits)/120 AS AverageMark
FROM Student, Module, Grade
WHERE Student.ID = Grade.ID
AND Module.Code = Grade.Code
AND YearTaken IN (2,3)
AND Year = 3
GROUP BY Year, Student.ID, Last, First
UNION
SELECT 
    Year, Student.ID, Last, First,
    SUM(Mark*Credits)/120 AS AverageMark
FROM Student, Module, Grade
WHERE Student.ID = Grade.ID
AND Module.Code = Grade.Code
AND YearTaken = Year
AND Year IN (1,2)
GROUP BY Year, Student.ID, Last, First
ORDER BY
    Year desc, AverageMark desc, Last, First, ID;