IBM-数据工程-V-笔记-全-

IBM 数据工程 V 笔记（全）

001：数据科学SQL入门 🚀

在本课程中，我们将学习SQL（结构化查询语言）及其在数据科学领域的基础应用。SQL是与数据库进行通信的强大语言，是数据科学家必备的核心技能之一。掌握SQL不仅能提升你的专业形象，还能帮助你更高效地访问和分析存储在关系数据库中的数据。

数据科学家的市场需求与SQL的重要性 💼

数据科学家的市场需求很高。根据数据，该职位的平均年薪为10万美元，工作满意度评分为4.4分（满分5分）。这使其成为Glassdoor评选的美国最佳职位榜首。

Glassdoor分析了其平台上的数据科学家招聘信息，发现SQL被列为数据科学家最需要的三大技能之一。

在踏入数据科学领域之前，掌握该领域的基础知识至关重要，这能让你在众多求职者中脱颖而出。

为什么学习SQL？ 🔍

SQL是你需要掌握的基础技能之一。它是一种用于与数据库通信的强大语言。任何处理数据的应用程序都需要将数据存储在某个地方，无论是大数据、政府或小型初创公司的简单表格，还是跨多个服务器的大型数据库，甚至是运行自身小型数据库的手机。

以下是对于有志于数据科学的人学习SQL的一些优势：

  • 提升专业形象：SQL是雇主最渴求的技能之一，掌握它能显著提升你作为数据科学家的专业形象。
  • 理解关系数据库：学习SQL能让你很好地理解关系数据库的工作原理。
  • 直接访问数据：要利用所有这些信息，需要能够与存储数据的数据库进行通信。
  • 提高自主性：即使你使用能自动生成SQL查询的报告工具，掌握编写自己的SQL语句也很有用，这样你就不必等待其他团队成员为你创建SQL语句。

本课程内容概览 📚

在本课程中，你将学习SQL语言和关系数据库的基础知识。课程包含有趣的测验和动手实验作业，让你获得使用数据库的实际经验。

在前几个模块中，你将直接操作数据库，并逐步掌握SQL的实用知识。

上一节我们介绍了SQL的基础概念，本节中我们来看看如何像数据科学家一样应用它。

接着，你将学习如何连接数据库并运行SQL查询，就像数据科学家通常所做的那样。在这个过程中，你将使用Python和Jupyter Notebooks来连接关系数据库，以访问和分析数据。

课程实践与总结 🎯

课程接近尾声时，还包含一项作业，你将有机会应用所学的概念。

本节课中我们一起学习了SQL在数据科学中的核心地位、学习SQL的多重优势以及本课程的基本结构和学习路径。现在，让我们开始数据科学SQL的学习之旅。

002：数据库与SQL基础入门

在本节课中，我们将学习SQL语言和关系型数据库模型的基础知识。课程包含实验练习、章节复习题以及最终的考试。完成本课后，你将能够讨论SQL基础概念并解释关系型数据库模型的各个方面。

🎯 什么是SQL与关系型数据库？

在本视频中，我们将了解SQL和关系型数据库。学习结束后，你将能够描述SQL、数据库、关系型数据库，并列出5个基本的SQL命令。

那么，什么是SQL？什么是关系型数据库？

什么是SQL？

SQL是一种用于关系型数据库的语言，用于查询或从数据库中获取数据。SQL是其原名“结构化查询语言”的缩写。

代码示例： SQL用于与数据库交互。

什么是数据？

数据是以文字、数字甚至图片形式存在的事实集合。数据是任何企业最重要的资产之一，几乎无处不在被使用和收集。例如，你的银行存储关于你的数据：姓名、地址、电话号码、账号等。你的信用卡公司和PayPal账户也存储你的数据。数据非常重要，因此需要安全存储并能被快速访问。

什么是数据库？

数据库无处不在且每日被使用，但常常被忽视。数据库是一个数据存储库，是一个存储数据的程序。数据库还提供了添加、修改和查询数据的功能。根据不同的需求，存在不同类型的数据库，数据可以以多种形式存储。

什么是关系型数据库？

当数据以表格形式存储时，数据被组织在表中，就像电子表格一样，包含列和行。这就是关系型数据库。

列包含项目的属性，例如姓氏、名字、电子邮件地址、城市。表是相关事物的集合，例如员工列表或图书作者列表。在关系型数据库中，你可以在表之间建立关系。

因此，数据库是数据的存储库，而用于管理数据库中数据的软件工具集称为数据库管理系统，简称DBMS。

数据库管理系统

术语“数据库”、“数据库服务器”、“数据库系统”、“数据服务器”和“数据库管理系统”经常互换使用。对于关系型数据库，它被称为关系型数据库管理系统，简称RDBMS。

RDBMS是一套控制数据（如访问、组织和存储）的软件工具。RDBMS是许多行业（包括银行、交通、医疗等）应用程序的支柱。

示例： 关系型数据库管理系统的例子包括MySQL、Oracle Database、DB2 Warehouse和DB2 on Cloud。

🔑 五个基本SQL命令

对于大多数使用数据库的人来说，有五个简单的命令：

以下是五个核心的SQL操作命令：

  1. 创建表
    • CREATE TABLE - 用于在数据库中创建新表。

  1. 插入数据

    • INSERT - 用于向表中插入新数据行。

  2. 查询数据

    • SELECT - 用于从表中查询（选择）数据。

  1. 更新数据
    • UPDATE - 用于修改表中现有的数据。

  1. 删除数据
    • DELETE - 用于从表中删除数据行。

这些是数据科学中SQL的构建模块。

📝 总结

在本节课中，我们一起学习了：

  • 能够描述什么是SQL、数据、数据库以及关系型数据库。
  • 知道RDBMS代表关系型数据库管理系统。
  • 能够列出五个基本的SQL命令：创建表、插入数据以填充表、从表中选择数据、更新表中的数据以及从表中删除数据。

003：使用SELECT语句检索数据

在本节课中，我们将学习如何从关系数据库表中检索数据，具体是通过选择表中的列来实现。课程结束时，你将能够：从关系数据库表中检索数据、定义谓词的用途、识别使用WHERE子句的SELECT语句语法，并列举关系数据库管理系统支持的比较运算符。

数据库管理系统的主要目的不仅是存储数据，还要便于数据的检索。

在创建了关系数据库表并向表中插入数据之后，我们通常需要查看这些数据。

为了查看数据，我们使用SELECT语句。

SELECT语句是一种数据操作语言（DML）语句。数据操作语言语句用于读取和修改数据。SELECT语句通常被称为查询，执行此查询得到的输出称为结果集或结果表。

SELECT语句最简单的形式是：SELECT * FROM table_name。

以“图书”实体为例，我们会使用实体名称“book”和实体属性作为表的列来创建表。数据通过INSERT语句作为行添加到book表中。在图书实体示例中，执行SELECT * FROM book会得到一个包含四行的结果集，显示表book中所有列的所有数据行。

此外，你也可以通过在SELECT语句中单独指定列名来检索所有行的所有列。

你并不总是需要检索表中的所有列。你可以只检索列的一个子集。例如，你可以只从book表中检索两列：book_id和title。

在这种情况下，SELECT语句是：SELECT book_id, title FROM book。此时，四行中的每一行只显示这两列。同时请注意，显示的列顺序始终与SELECT语句中指定的顺序一致。

然而，如果我们想知道book_id为“B1”的图书的标题，该怎么办呢？

关系操作通过允许我们使用WHERE子句来帮助我们限制结果集。WHERE子句总是需要一个谓词。谓词是一个计算结果为真、假或未知的条件。谓词用在WHERE子句的搜索条件中。

因此，如果我们需要知道book_id为“B1”的图书标题，我们使用WHERE子句和谓词book_id = ‘B1’。

语句如下：SELECT book_id, title FROM book WHERE book_id = ‘B1’。

请注意，现在结果集被限制为仅有一行，即条件评估为真的那一行。

前面的示例在WHERE子句中使用了比较运算符“等于”。

关系数据库管理系统还支持其他比较运算符。

以下是关系数据库管理系统支持的主要比较运算符列表：

  • = 等于
  • > 大于
  • < 小于
  • >= 大于或等于
  • <= 小于或等于
  • <> 或 != 不等于

现在，你已经能够从关系数据库表中检索数据和选择列，定义了谓词的用途，识别了使用WHERE子句的SELECT语句语法，并列举了关系数据库管理系统支持的比较运算符。

在本节课中，我们一起学习了使用SELECT语句从数据库检索数据的基础知识，包括选择特定列、使用WHERE子句和谓词进行条件过滤，以及常用的比较运算符。这些是进行数据查询和操作的核心技能。

004：COUNT、DISTINCT与LIMIT

在本节课中，我们将学习三个与SELECT语句结合使用的实用表达式：COUNT、DISTINCT和LIMIT。这些功能对于数据汇总、去重和结果集控制至关重要。

🧮 1. COUNT函数：统计行数

上一节我们介绍了基础的SELECT查询。本节中，我们首先来看看COUNT函数。COUNT是一个内置的数据库函数，用于检索符合查询条件的行数。

其基本语法公式如下：

SELECT COUNT(*) FROM table_name;

例如，要获取一个名为Meals的表中，country列值为“Canada”的行数，可以使用以下查询：

SELECT COUNT(*) FROM medals WHERE country = 'Canada';

🔍 2. DISTINCT关键字：获取唯一值

了解了如何计数后，我们来看看如何从结果集中移除重复值。DISTINCT关键字用于从结果集中删除重复值，从而获取列中的唯一值列表。

其基本语法公式如下：

SELECT DISTINCT column_name FROM table_name;

以下是DISTINCT的一个典型应用场景：
在之前提到的medals表中，一个国家可能多次获得金牌。如果我们想获取所有获得过金牌的唯一国家列表（即去除同一国家的重复记录），可以使用以下查询：

SELECT DISTINCT country FROM medals WHERE medal_type = 'gold';

⏹️ 3. LIMIT子句：限制返回行数

最后，我们来学习如何控制返回的数据量。LIMIT子句用于限制从数据库检索的行数。

其基本语法公式如下：

SELECT * FROM table_name LIMIT number;

这在初步检查数据时非常有用，可以避免检索可能非常庞大的整个结果集。例如，仅查看medals表中2018年的前5条记录：

SELECT * FROM medals WHERE year = 2018 LIMIT 5;

📝 总结

本节课中，我们一起学习了三个与SELECT语句结合使用的核心表达式：用于统计行数的COUNT函数、用于获取唯一值的DISTINCT关键字，以及用于限制返回行数的LIMIT子句。掌握这些功能将帮助你更高效地进行数据查询和分析。

005：插入语句详解

在本节课中，我们将学习如何向关系数据库表中填充数据，即使用 INSERT 语句。课程结束时，你将能够识别 INSERT 语句的语法，并解释向表中添加数据的两种方法。

🗂️ 插入语句简介

上一节我们介绍了如何创建表。表创建完成后，需要向其中填充数据。向表中插入数据，我们使用 INSERT 语句。

INSERT 语句用于向表中添加新的数据行。它属于数据操作语言（Data Manipulation Language，简称 DML）语句。DML 语句主要用于读取和修改数据。

📝 INSERT 语句语法

基于之前创建的作者实体示例，我们使用实体名 author 及其属性作为表的列创建了 author 表。现在，我们将通过向表中添加行来填充数据。

向 author 表添加数据，我们使用 `INSERT`` 语句。其基本语法如下：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

在这个语句中：

  • table_name 指定目标表。
  • column_name list 列出表中的每一列。
  • VALUES 子句指定要添加到表中各列的数据值。

🔧 插入单行数据

为了添加一行关于 Raoul Chong 的数据，我们插入一行数据，其中 author_id 为 A1，last_name 为 Chong，first_name 为 Raoul，email 为 rfc@ibm.com，city 为 Toronto，country 为 CA（代表加拿大）。

author 表有六列，因此 INSERT 语句列出了六个用逗号分隔的列名，随后是为每列提供的、同样用逗号分隔的值。

关键点：VALUES 子句中提供的值的数量必须与列名列表中指定的列数相等。这确保了每一列都有一个对应的值。

以下是插入单行数据的示例：

INSERT INTO author (author_id, last_name, first_name, email, city, country)
VALUES ('A1', 'Chong', 'Raoul', 'rfc@ibm.com', 'Toronto', 'CA');

🔄 插入多行数据

表不需要一次只填充一行。通过在 VALUES 子句中指定每一行，可以一次性插入多行数据。在 VALUES 子句中，每一行数据用逗号分隔。

以下是同时插入两行数据的示例：

INSERT INTO author (author_id, last_name, first_name, email, city, country)
VALUES
    ('A1', 'Chong', 'Raoul', 'rfc@ibm.com', 'Toronto', 'CA'),
    ('A2', 'Ahuja', 'Rav', 'ra@ibm.com', 'Toronto', 'CA');

✅ 课程总结

本节课我们一起学习了 INSERT 语句。你现在应该能够：

  1. 识别 INSERT INTO table_name (columns) VALUES (values); 的基本语法。
  2. 解释向数据库表添加数据的两种方法：一次插入单行数据，或一次插入多行数据。

掌握 INSERT 语句是操作和管理数据库内容的基础。

006：更新与删除语句 📝

在本节课中，我们将学习如何在关系数据库表中修改和删除数据。课程结束后，你将能够识别UPDATE语句和DELETE语句的语法，并理解WHERE子句在这些语句中的重要性。

更新数据：UPDATE语句

创建表并填充数据后，可以使用UPDATE语句修改表中的数据。UPDATE语句是数据操作语言（DML）语句之一，用于读取和修改数据。

基于作者实体示例，我们使用实体名称“author”和实体属性作为表的列创建了表。随后向作者表添加行以填充数据。一段时间后，你可能需要修改表中的数据。要修改作者表中的数据，我们使用UPDATE语句。

UPDATE语句的语法如下：

UPDATE table_name
SET column_name = value
WHERE condition;

在这个语句中，table_name 标识目标表，column_name 标识要更改的列，value 是要设置的新值，WHERE condition 指定哪些行需要更新。

示例：更新作者姓名

假设需要将作者ID为A2的作者姓名从“Raav Ahuja”更改为“Lakshmi Kata”。以下是操作步骤：

首先，查看更新前的所有行：

SELECT * FROM author;

然后，执行UPDATE语句：

UPDATE author
SET last_name = 'Kata', first_name = 'Lakshmi'
WHERE author_id = 'A2';

更新后，再次查看所有行以确认更改：

SELECT * FROM author;

你将看到第二行的姓名已从“Raav Ahuja”更改为“Lakshmi Kata”。

重要提示：如果不指定WHERE子句，表中的所有行都将被更新。例如，省略WHERE子句会导致所有作者姓名都被更改为“Lakshmi Kata”。

删除数据：DELETE语句

有时可能需要从表中删除一行或多行数据。DELETE语句用于删除行，它也是数据操作语言（DML）语句，用于读取和修改数据。

DELETE语句的语法如下：

DELETE FROM table_name
WHERE condition;

要删除的行由WHERE条件指定。

示例：删除特定作者

基于作者实体示例，假设需要删除作者ID为A2和A3的行。以下是操作步骤：

执行DELETE语句：

DELETE FROM author
WHERE author_id IN ('A2', 'A3');

重要提示：如果不指定WHERE子句，表中的所有行都将被删除。

总结

本节课中，我们一起学习了UPDATE语句和DELETE语句的语法及其应用。UPDATE语句用于修改现有数据，而DELETE语句用于删除数据。在这两个语句中，WHERE子句都至关重要，因为它指定了要更新或删除的具体行，避免了对整个表的意外操作。

通过掌握这些语句，你将能够有效地管理和维护数据库中的数据。

007：关系数据库概念 📚

在本节课中，我们将学习不同类型的数据库模型，了解如何使用模型将数据映射到表，并定义表之间的关系。课程结束时，你将能够解释关系模型的优势，理解实体名称和属性如何映射到关系数据库表，区分实体与属性，识别一些常用的数据类型，并描述主键的功能。

关系模型的优势 🏆

关系模型是数据库中最常用的数据模型，因为它支持数据独立性。数据存储在简单的数据结构——表中。这提供了逻辑数据独立性、物理数据独立性和物理存储独立性。

实体关系模型介绍 🔗

实体关系数据模型是关系数据模型的一种替代方案。它使用简化的图书馆数据库作为示例。下图展示了一个实体关系图，它代表了称为表的实体及其之间的关系。

在图书馆示例中，我们有“书籍”实体。一本书可以由一位或多位作者撰写。图书馆可以拥有一本或多本图书副本。每个副本在同一时间只能被一位借阅者借出。

实体关系模型建议将数据库视为实体的集合。

实体关系模型的作用 🛠️

实体关系模型本身并不单独用作模型，而是作为设计关系数据库的工具。在ER模型中，实体是独立于数据库中任何其他实体而存在的对象。

实体与属性 🧱

ER图的基本构建块是实体和属性。实体可以是名词，如人、地点或事物。

在ER图中，实体被绘制为矩形。实体拥有属性，属性是描述实体特征的数据元素。

属性为我们提供了关于实体的更多信息。在ER图中，属性被绘制为椭圆形。

从实体到表的映射 📊

以简化的图书馆为例，“书籍”是一个实体的例子。属性是实体的特定属性或特征，能告诉我们更多关于实体的信息。

“书籍”实体具有诸如书名、书籍版本、出版年份等属性。每个属性只连接到一个实体。

在数据库中，“书籍”实体成为一个表，而其属性则成为表中的列。

表的结构 📋

表是行和列的组合。在映射过程中，实体成为表。此时，表尚未具备行和列的形式。属性被转换为表中的列，从而提供了实际的行列表形式。随后，我们为每列添加一些数据值，从而完成表的构建。

数据类型 📝

每个属性存储不同格式的数据值，如字符、数字、日期、货币等。在书籍表示例中，书名由字符组成。由于书名长度不一，我们可以为“书名”列设置可变字符数据类型 VARCHAR。对于长度固定的字符列，我们使用 CHAR。“版本”和“年份”列将是数字类型。ISBN 列将是 CHAR 类型，因为它包含连字符和数字等。

构建完整的数据库模型 🗃️

以书籍实体映射为例，我们可以为简化的图书馆示例创建其余的表，使用诸如“作者”、“作者列表”、“借阅者”、“借阅记录”和“副本”等实体名称。实体的属性将成为表的列。

主键与关系 🔑

每个表都被分配一个主键。关系表的主键唯一标识表中的每个元组或行，防止数据重复，并提供定义表之间关系的方法。

表还可以包含外键，外键是在其他表中定义的主键，用于创建表之间的链接。

总结 📖

现在你知道了，关系模型的关键优势在于逻辑和物理数据独立性以及存储独立性。实体是独立的对象，可以拥有多个称为属性的特征。

当映射到关系数据库时，实体被表示为表，属性被映射为列。

常见的数据类型包括字符类型（如 CHAR 和 VARCHAR）、数字类型（如 INTEGER 和 DECIMAL）以及时间戳类型（包括 DATE 和 TIME）。

主键唯一标识表中的特定行，并防止数据重复。

008：SQL语句类型 - DDL与DML 🗂️

在本节课中，我们将学习SQL语句的两种主要类型：数据定义语言（DDL）和数据操作语言（DML）。我们将了解它们各自的作用、常见的语句类型以及它们之间的区别。

SQL语句用于与关系型数据库中的实体（如表）、属性（即列）及其包含数据值的元组（即行）进行交互。

SQL语句主要分为两大类：数据定义语言（DDL） 语句和数据操作语言（DML） 语句。

🏗️ 数据定义语言（DDL）

上一节我们介绍了SQL语句的分类，本节中我们来看看数据定义语言（DDL）。

数据定义语言（DDL）语句用于定义、更改或删除数据库对象，例如表。

以下是常见的DDL语句类型：

  • CREATE：用于创建表并定义其列。
    CREATE TABLE 表名 (
    列名1 数据类型,
    列名2 数据类型,
    ...
);
  • ALTER：用于修改表结构，包括添加和删除列，以及修改列的数据类型。
    ALTER TABLE 表名 ADD 列名 数据类型;
ALTER TABLE 表名 DROP COLUMN 列名;
  • TRUNCATE：用于删除表中的所有数据，但保留表本身的结构。
    TRUNCATE TABLE 表名;
  • DROP：用于删除整个表。
    DROP TABLE 表名;

🔧 数据操作语言（DML）

了解了如何定义和修改数据库结构后，本节我们来看看如何操作表中的数据。

数据操作语言（DML）语句用于读取和修改表中的数据。这些操作有时也被称为CRUD操作，即对表中的行进行创建（Create）、读取（Read）、更新（Update）和删除（Delete）。

以下是常见的DML语句类型：

  • INSERT：用于向表中插入一行或多行数据。
    INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);
  • SELECT：用于从表中读取或选择一行或多行数据。
    SELECT 列1, 列2 FROM 表名 WHERE 条件;
  • UPDATE：用于编辑表中的一行或多行数据。
    UPDATE 表名 SET 列1 = 新值 WHERE 条件;
  • DELETE：用于从表中删除一行或多行数据。
    DELETE FROM 表名 WHERE 条件;

📝 总结

本节课中我们一起学习了SQL的两种核心语句类型。

现在你知道了，DDL（数据定义语言） 语句用于定义或更改数据库中的对象（如表），而DML（数据操作语言） 语句用于操作或处理表中的数据。理解这两者的区别是有效使用SQL管理数据库的基础。

009：创建表语句

在本节课中，我们将学习如何使用SQL的CREATE TABLE语句来创建关系数据库表。你将了解如何将实体名称和属性转化为数据库中的表结构。

🏗️ CREATE TABLE 语句概述

CREATE TABLE是最常见的数据定义语言（DDL）语句之一。它的基本语法结构如下：

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

语句以CREATE TABLE开始，后跟要创建的表名。其余部分用一对圆括号括起来。括号内的每一行定义一个列，包括列名、数据类型以及可选的约束条件。每个列的定义用逗号分隔。

📝 创建表的基本示例

为了更好地理解，我们来看一个简单的例子。假设我们要为加拿大的省份创建一个表。

以下是创建该表的SQL语句：

CREATE TABLE provinces (
    ID CHAR(2) PRIMARY KEY NOT NULL,
    name VARCHAR(24)
);

在这个例子中，我们使用了两种数据类型：

  • CHAR(2)：固定长度为2的字符串，用于存储省份的缩写代码（如AB、BC）。
  • VARCHAR(24)：可变长度字符串，最大长度为24个字符，用于存储省份的全名（如Alberta、British Columbia）。

执行此语句后，数据库中会创建一个包含两列的表：ID列存储省份代码，name列存储省份全名。

📚 图书馆数据库案例详解

上一节我们介绍了创建表的基本语法，本节中我们来看一个更复杂的例子。我们将基于一个图书馆数据库来创建表，该数据库包含作者、书籍、借阅者等多个实体。

让我们从创建author（作者）表开始。表名将是author，其属性（如作者ID、名、姓等）将成为表的列。

以下是创建作者表的SQL命令：

CREATE TABLE author (
    author_id CHAR(2) PRIMARY KEY NOT NULL,
    last_name VARCHAR(15) NOT NULL,
    first_name VARCHAR(15) NOT NULL,
    email VARCHAR(40),
    city VARCHAR(15),
    country CHAR(2)
);

在这个表定义中，我们需要注意以下几点：

以下是本语句中定义的关键约束：

  • author_id被指定为主键。主键约束确保表中每一行的该列值都是唯一的，不允许重复。
  • last_name和first_name列被标记为NOT NULL。这个约束确保这些字段不能包含空值，因为作者必须拥有姓名。

✅ 课程总结

本节课中，我们一起学习了SQL中CREATE TABLE语句的用法。我们了解到：

  1. CREATE TABLE是用于在数据库中创建实体或表的DDL语句。
  2. 该语句的核心是定义表的列，包括列名、数据类型以及可选的约束（如主键、非空约束）。
  3. 通过具体的示例，我们掌握了如何将现实世界的实体（如省份、作者）及其属性转化为规范的数据库表结构。

理解并掌握CREATE TABLE语句是构建和管理数据库的基石。

010：修改、删除与清空表

在本节课中，我们将学习如何使用 SQL 语句来修改现有表的结构、删除整个表，以及快速清空表中的所有数据。这些操作是数据库管理和维护中的核心技能。

概述 📋

ALTER TABLE、DROP TABLE 和 TRUNCATE TABLE 是三种用于管理数据库表结构的重要 SQL 语句。它们分别用于修改表定义、删除整个表以及清空表内所有数据。掌握这些语句的语法和使用场景，对于灵活管理数据模型至关重要。

修改表结构：ALTER TABLE 语句 🔧

上一节我们介绍了创建表，本节中我们来看看如何修改一个已存在的表。ALTER TABLE 语句用于更改现有表的结构，例如添加或删除列、修改列的数据类型，以及添加或删除键和约束。

其基本语法如下：

ALTER TABLE table_name action;

与 CREATE TABLE 语句不同，ALTER TABLE 语句不使用括号来包裹参数。语句中的每一行都指定了要对表进行的一项更改。

以下是 ALTER TABLE 语句的几种常见用法：

添加新列

例如，为了在图书馆数据库的 author 表中添加一个存储作者电话号码的列，可以使用以下语句：

ALTER TABLE author ADD COLUMN telephone_number BIGINT;

在这个例子中，列的数据类型是 BIGINT，它可以容纳长达19位的数字。

修改列的数据类型

要修改列的数据类型，需使用 ALTER COLUMN 子句，并为列指定新的数据类型。

例如，使用数字数据类型存储电话号码意味着无法将括号、加号或破折号作为号码的一部分。你可以将列更改为使用字符数据类型来解决这个问题。以下代码展示了如何修改 author 表：

ALTER TABLE author ALTER COLUMN telephone_number SET DATA TYPE CHAR(20);

注意：修改包含现有数据的列的数据类型可能会导致问题，特别是当现有数据与新数据类型不兼容时。例如，如果列中已包含非数字数据，尝试将列从字符数据类型更改为数字数据类型将不会成功。你会看到通知日志中的错误信息，并且该语句不会执行。

删除列

如果你的需求发生变化，不再需要某个额外的列，你可以再次使用 ALTER TABLE 语句，这次配合 DROP COLUMN 子句来删除该列，如下所示：

ALTER TABLE author DROP COLUMN telephone_number;

删除表：DROP TABLE 语句 🗑️

与使用 DROP COLUMN 从表中删除列类似，你可以使用 DROP TABLE 语句从数据库中删除整个表。默认情况下，如果你删除一个包含数据的表，数据将随表一起被删除。

该语句的语法是：

DROP TABLE table_name;

因此，要删除 author 表，你可以使用以下语句：

DROP TABLE author;

清空表数据：TRUNCATE TABLE 语句 ⚡

有时，你可能只想删除表中的数据，而不是删除表本身。虽然你可以使用不带 WHERE 子句的 DELETE 语句来实现，但使用 TRUNCATE TABLE 通常更快、更高效。

TRUNCATE TABLE 语句用于删除表中的所有行。其语法如下：

TRUNCATE TABLE table_name IMMEDIATE;

IMMEDIATE 指定立即处理该语句，且此操作无法撤销。

因此，要清空 author 表，你可以使用以下语句：

TRUNCATE TABLE author IMMEDIATE;

总结 🎯

本节课中我们一起学习了三个关键的 SQL 表管理语句：

  1. ALTER TABLE 语句：用于更改现有表的结构，例如添加、修改或删除列。
  2. DROP TABLE 语句：用于删除数据库中的现有表（及其数据）。
  3. TRUNCATE TABLE 语句：用于快速删除表中的所有数据行，但保留表结构。

理解并正确使用这些语句，将帮助你有效地维护和调整数据库结构，以适应不断变化的数据需求。

011：如何在云上创建数据库实例 🗄️

在本节课中，我们将学习关于云数据库的核心概念，并演示如何在IBM Cloud上创建一个DB2数据库服务实例，以便为后续的SQL查询练习提供环境。

云数据库概述

上一节我们介绍了学习SQL需要一个可用的数据库。本节中，我们来看看什么是云数据库。云数据库是一种通过云平台构建和访问的数据库服务。它具备传统数据库的多数功能，同时兼具云计算的灵活性。

使用云数据库的优势包括：

  • 易于使用：用户几乎可以从任何地方，通过供应商的API、Web界面或自己的应用程序访问云数据库。
  • 可扩展性：云数据库可以在运行时动态扩展或收缩其存储和计算能力，以适应不断变化的需求，组织只需为实际使用的资源付费。
  • 灾难恢复：在发生自然灾害、设备故障或停电时，数据可通过在云上地理分布式区域的远程服务器备份来确保安全。

常见的云关系数据库

以下是几个常见的云关系数据库服务示例：

  • IBM DB2 on Cloud
  • Databases for PostgreSQL on IBM Cloud
  • Oracle Database Cloud Service
  • Microsoft Azure SQL Database
  • Amazon Relational Database Service (RDS)

这些云数据库可以作为虚拟机运行（由您自行管理），也可以作为托管服务提供，具体取决于供应商。数据库服务可以是单租户或多租户，这取决于所选的服务计划。

数据库服务实例

要在云中运行数据库，首先需要在您选择的云平台上配置一个数据库服务实例。数据库即服务实例为用户提供了对云中数据库资源的访问，而无需设置底层硬件、安装数据库软件和管理数据库。

数据库服务实例将您的数据存储在相关的表中。数据加载到数据库实例后，您可以通过Web界面或应用程序中的API连接到该实例。

应用程序 --(SQL查询)--> 数据库实例 --(操作数据)--> 返回结果集

连接建立后，您的应用程序可以向数据库实例发送SQL查询。数据库实例将SQL语句解析为对数据库中数据和对象的操作，并将检索到的任何数据作为结果集返回给应用程序。

在IBM Cloud上创建DB2实例

现在，让我们看看如何在IBM Cloud上为DB2创建一个数据库实例。IBM DB2 on Cloud是一个在云中为您提供的SQL数据库。您可以像使用任何数据库软件一样使用它，但无需承担高昂的硬件设置或软件安装和维护开销。

以下是创建DB2服务实例的步骤：

  1. 导航至IBM Cloud目录：访问IBM Cloud控制台，进入目录（Catalog）。
  2. 选择DB2服务：在目录中搜索并选择“DB2”服务。请注意，DB2服务有多个变体，包括DB2 Hosted和DB2 Warehouse。出于我们的目的，我们将选择带有免费轻量版计划的“Db2”服务。
  3. 选择服务计划：选择“Lite”免费计划。如果需要，可以更改默认设置，例如输入服务实例名称、选择部署区域以及该服务的组织和空间。
  4. 创建实例：点击“创建”按钮。
  5. 访问与管理：创建完成后，您可以从IBM Cloud仪表板的“服务”列表中查看您创建的IBM DB2服务。通过此仪表板，您可以管理数据库实例，例如点击“打开控制台”按钮来启动数据库实例的Web控制台。Web控制台允许您创建表、加载数据、浏览表中的数据以及执行SQL查询。
  6. 获取连接凭证：为了从您的应用程序访问数据库实例，您将需要服务凭证。首次使用时，您需要创建一组新的凭证。您也可以选择为不同的应用程序和用户创建多组凭证。凭证创建后，您可以将其视为一个JSON代码片段。凭证包含建立数据库连接所需的详细信息，主要包括：
    • 数据库名称 和 端口号
    • 主机名：您的数据库实例所在的云服务器名称
    • 用户名 和 密码：用于连接的用户ID。请注意，您的用户名默认也是您的表将被创建于其中的模式名称。

总结

本节课中，我们一起学习了云数据库的基本概念、优势以及常见的云数据库服务。我们重点描述了数据库服务实例的作用，并逐步演示了如何在IBM Cloud上创建一个DB2数据库服务实例。现在您已经知道如何在云上创建数据库实例，下一步就是亲自去创建一个，以便开始您的SQL实践。

012：使用字符串模式与范围检索数据 📊

在本节课中，我们将学习从关系数据库表中检索数据的一些高级技巧。具体来说，我们将探讨如何使用字符串模式、数值范围和值集合来简化SELECT语句，从而更灵活地查询数据。

数据库检索的基本形式

数据库管理系统的主要目的不仅是存储数据，还要便于以简洁的形式检索数据。最简单的SELECT语句是：

SELECT * FROM table_name;

基于简化的图书馆数据库模型和book表，执行SELECT * FROM book;会返回四行数据，显示表中所有列的所有数据行。

或者，你也可以检索列的子集。例如，仅从book表中选择两列，如book_id和title。

使用WHERE子句限制结果集

你可以通过使用WHERE子句来限制结果集。例如，你可以选择book_id为B1的书籍标题。

SELECT title FROM book WHERE book_id = 'B1';

但如果我们不完全知道在WHERE子句中指定什么值怎么办？WHERE子句总是需要一个谓词，即一个评估为真、假或未知的条件。

使用字符串模式进行搜索

如果我们记不清作者的名字，但记得他们的名字以字母“R”开头，该怎么办？在关系数据库中，我们可以使用字符串模式来搜索匹配此条件的数据行。

以下是使用字符串模式的一些示例。如果我们记不清作者的名字，但记得他们的名字以“R”开头，我们可以在WHERE子句中使用LIKE谓词。LIKE谓词用于在列中搜索模式。

百分号（%）用于定义缺失的字母。百分号可以放在模式之前、之后或前后都有。百分号被称为通配符，用于替代其他字符。

因此，如果我们记不清作者的名字，但记得他们的名字以字母“R”开头，我们可以在WHERE子句中添加LIKE谓词。

例如：

SELECT first_name FROM author WHERE first_name LIKE 'R%';

这将返回author表中所有作者名字以字母“R”开头的行。结果集将返回两行：作者Raoul和Rph。

使用数值范围进行查询

如果我们想检索页数大于290但小于300的书籍列表，可以这样编写SELECT语句，指定WHERE子句为WHERE pages >= 290 AND pages <= 300。

但在关系数据库中，我们可以使用数值范围来指定相同的条件。我们使用比较运算符BETWEEN AND，而不是使用大于或等于的比较运算符。

BETWEEN AND比较两个值，范围值包含在内。在这种情况下，我们重写查询，将WHERE子句指定为WHERE pages BETWEEN 290 AND 300。结果集相同，但SELECT语句编写起来更简单、更快捷。

使用值集合进行查询

在某些情况下，数据值无法按范围分组。例如，如果我们想知道作者来自哪些国家。

如果我们想检索来自澳大利亚或巴西的作者，可以编写SELECT语句，在WHERE子句中重复这两个国家值。

然而，如果我们想检索来自加拿大、印度和中国的作者，WHERE子句会变得非常冗长，需要重复列出所需的国家条件。

相反，我们可以使用IN运算符。IN运算符允许我们在WHERE子句中指定一组值。该运算符接受一个表达式列表进行比较。在这种情况下，国家是澳大利亚或巴西。

总结

在本节课中，我们一起学习了如何使用字符串模式、数值范围和值集合来简化SELECT语句。通过这些技巧，你可以更灵活地查询数据库，即使在不完全记得具体值的情况下也能高效检索数据。

013：排序结果集

在本节课中，我们将学习如何从关系数据库表中检索数据，并掌握对结果集进行排序的高级技巧。排序功能能让查询结果更有序、更易于分析。

🎯 概述

数据库管理系统的主要目的不仅是存储数据，还要便于数据的检索。SELECT 语句是检索数据的基础工具。本节课我们将重点学习如何使用 ORDER BY 子句对查询结果进行排序，包括升序和降序排列，以及指定排序列的不同方法。

🔍 基础SELECT语句与结果集

最简单的 SELECT 语句形式是 SELECT * FROM table_name。它用于从指定表中检索所有列的所有数据行。

以简化的图书馆数据库模型中的 book 表为例，执行 SELECT * FROM book 会返回包含四行数据的结果集。

该语句会显示 book 表中所有列的所有数据行。

📝 引入ORDER BY子句进行排序

我们也可以选择只列出书名，例如：SELECT title FROM book。然而，默认的结果集可能没有任何特定顺序。按字母顺序显示结果集会使其更便于查阅。为此，我们需要使用 ORDER BY 子句。

为了按字母顺序显示结果集，我们在 SELECT 语句中添加 ORDER BY 子句。

ORDER BY 子句用于在查询中根据指定列对结果集进行排序。在下面的例子中，我们在 title 列上使用了 ORDER BY。

代码示例：

SELECT title FROM book ORDER BY title;

默认情况下，结果集按升序排序。因此，此示例的结果集会按书名的字母顺序排列。

⬇️ 降序排序

要按降序排序，需使用关键字 DESC。

代码示例：

SELECT title FROM book ORDER BY title DESC;

现在，结果集根据指定的 title 列按降序排序。请注意前三行的顺序：它们书名的前三个单词相同，因此排序从字符开始不同的位置进行。

🔢 使用列序号指定排序

另一种指定排序列的方法是使用列序号。

代码示例：

SELECT title, pages FROM book ORDER BY 2;

在这个例子中，ORDER BY 2 表示使用查询中列序列的第二个列作为排序依据。在 SELECT 语句中，指定的第二列是 pages，因此排序顺序基于 pages 列中的值。

pages 列表示书籍的页数。如图所示，结果集按页数升序排列。

✅ 总结

在本节课中，我们一起学习了如何对 SELECT 语句的结果集进行排序。我们掌握了使用 ORDER BY 子句进行升序（默认）和降序（使用 DESC）排序的方法，并了解了可以通过列名或列序号来指定用于排序的列。这些技巧能帮助你更有效地组织和分析从数据库中检索到的数据。

014：分组结果集

在本节课中，我们将学习从关系数据库表中检索数据的高级技巧，特别是如何对结果集进行排序和分组。课程结束时，你将能够解释如何从结果集中消除重复值，并描述如何进一步限制结果集。

有时，SELECT 语句的结果集可能包含重复值。以我们简化的图书馆数据库模型中的作者表为例，country 列列出了作者所属国家的两位字母代码。

如果我们仅选择 country 列，会得到所有国家的列表。例如：

SELECT country FROM author ORDER BY 1;

ORDER BY 子句会对结果集进行排序。

这个结果集列出了作者所属的国家，并按国家字母顺序排序。在本例中，结果集显示了20行，对应20位作者。但有些作者来自同一个国家，因此结果集中包含重复项。

然而，我们可能只需要一份作者来源国家的列表。在这种情况下，重复项没有意义。

为了消除重复项，我们使用关键字 DISTINCT。

SELECT DISTINCT country FROM author ORDER BY 1;

使用 DISTINCT 关键字可以将结果集减少到只有六行。

但如果我们还想知道有多少作者来自同一个国家呢？

现在我们知道20位作者来自六个不同的国家。但我们可能还想了解每个国家具体有多少位作者。

为了显示列出国家及其作者数量的结果集，我们在 SELECT 语句中添加 GROUP BY 子句。

SELECT country, COUNT(country) FROM author GROUP BY country;

GROUP BY 子句根据一个或多个列的匹配值将结果分组。在这个例子中，国家被分组，然后使用 COUNT 函数进行计数。

请注意结果集中第二列的列标题。数值 2 被显示为列名，因为该列名在表中并不直接存在。结果集中的第二列是由 COUNT 函数计算得出的。

我们可以为结果集中的列指定一个更有意义的名称，而不是使用默认的 2。

我们使用 AS 关键字来实现这一点。在这个例子中，我们将派生列名 2 改为 count。

SELECT country, COUNT(country) AS count FROM author GROUP BY country;

这有助于澄清结果集的含义。

现在我们已经得到了来自不同国家的作者数量，我们可以通过设置一些条件来进一步限制行数。例如，我们可以检查是否有超过四位作者来自同一个国家。

要为 GROUP BY 子句设置条件，我们使用关键字 HAVING。

HAVING 子句与 GROUP BY 子句结合使用。非常重要的一点是，WHERE 子句是针对整个结果集的，而 HAVING 子句仅与 GROUP BY 子句配合工作。

要检查是否有超过四位作者来自同一个国家，我们在 SELECT 语句中添加以下内容：

SELECT country, COUNT(country) AS count FROM author GROUP BY country HAVING COUNT(country) > 4;

只有拥有五位或更多作者的国家才会被列在结果集中。

在这个例子中，这些国家是中国（有六位作者）和印度（也有六位作者）。

在本节课中，我们一起学习了如何从结果集中消除重复值，以及如何使用 GROUP BY 和 HAVING 子句对结果进行分组和进一步限制。

015：内置数据库函数

在本节课中，我们将学习SQL数据库中内置的函数。这些函数允许我们直接在数据库内对数据进行操作，从而减少从数据库检索的数据量，提高处理效率。

🧮 聚合函数（列函数）

上一节我们介绍了内置函数的基本概念。本节中，我们来看看聚合函数。聚合函数接收一组相似的值（例如一列中的所有值）作为输入，并返回单个值或NULL。

以下是常见的聚合函数示例：

  • SUM：用于计算一列中所有值的总和。
    • 公式：SUM(column_name)
    • 示例：SELECT SUM(cost) FROM PetRescue;
  • MIN：用于获取一列中的最小值。
    • 公式：MIN(column_name)
    • 示例：SELECT MIN(quantity) FROM PetRescue WHERE animal = 'Dog';
  • MAX：用于获取一列中的最大值。
    • 公式：MAX(column_name)
    • 示例：SELECT MAX(quantity) FROM PetRescue;
  • AVG：用于返回一列的平均值。
    • 公式：AVG(column_name)
    • 示例：SELECT AVG(cost) FROM PetRescue;

注意：使用聚合函数时，默认情况下结果集中的列会被赋予一个编号。可以使用AS关键字为结果列显式命名，例如：SELECT SUM(cost) AS sum_of_cost FROM PetRescue;。

聚合函数也可以应用于数据的子集，而不仅仅是整个列。例如，可以结合WHERE子句使用。

🔢 标量与字符串函数

了解了聚合函数后，我们来看看标量函数。标量函数对单个值执行操作，例如对数值进行四舍五入。

以下是一些标量函数，特别是字符串函数的示例：

  • ROUND：对数值进行四舍五入。
    • 公式：ROUND(column_name)
    • 示例：SELECT ROUND(cost) FROM PetRescue;
  • LENGTH：返回字符串的长度。
    • 公式：LENGTH(column_name)
    • 示例：SELECT LENGTH(animal) FROM PetRescue;
  • UPPER / UCASE：将字符串转换为大写。
    • 公式：UPPER(column_name) 或 UCASE(column_name)
    • 示例：SELECT UPPER(animal) FROM PetRescue;
  • LOWER / LCASE：将字符串转换为小写。
    • 公式：LOWER(column_name) 或 LCASE(column_name)
    • 示例：SELECT LOWER(animal) FROM PetRescue;

标量函数同样可以在WHERE子句中使用。这在不确定表中数据存储为大写、小写还是混合格式时，用于匹配条件非常有用。例如：SELECT * FROM PetRescue WHERE LOWER(animal) = 'cat';。

此外，还可以让一个函数对另一个函数的输出进行操作，例如：SELECT DISTINCT UPPER(animal) FROM PetRescue;。

📝 总结

本节课中，我们一起学习了SQL的内置函数。我们首先介绍了聚合函数，如SUM、MIN、MAX和AVG，它们用于对列数据进行汇总计算。接着，我们探讨了标量函数和字符串函数，如ROUND、LENGTH、UPPER和LOWER，它们用于对单个数据值进行操作和格式化。掌握这些函数能帮助我们在数据库层面高效地处理和转换数据。

016：SQL日期与时间函数详解

在本节课中，我们将学习SQL数据库中用于处理日期和时间的内置函数。这些函数能帮助我们提取、计算和操作日期时间数据，是数据科学和数据分析中不可或缺的工具。

🗓️ 日期与时间数据类型

大多数数据库都包含用于存储日期和时间的特殊数据类型。

在DB2数据库中，主要包含三种日期时间类型：DATE、TIME和TIMESTAMP。

以下是这些类型的详细说明：

  • DATE：包含8位数字，格式为YYYYMMDD，分别代表年、月、日。
  • TIME：包含6位数字，格式为HHMMSS，分别代表时、分、秒。
  • TIMESTAMP：包含20位数字，格式为YYYY-MM-DD-HH.MM.SS.XXXXXX，其中XXXXXX代表微秒。它包含了年、月、日、时、分、秒和微秒。

🔧 日期时间提取函数

SQL提供了一系列函数，用于从日期时间值中提取特定的部分。

以下是常用的日期时间提取函数：

  • DAY()：提取日期中的“日”部分。
  • MONTH()：提取日期中的“月”部分。
  • DAYOFMONTH()：提取日期是该月中的第几天。
  • DAYOFWEEK()：提取日期是该周中的第几天。
  • DAYOFYEAR()：提取日期是该年中的第几天。
  • WEEK()：提取日期是该年中的第几周。
  • HOUR()：提取时间中的“小时”部分。
  • MINUTE()：提取时间中的“分钟”部分。
  • SECOND()：提取时间中的“秒”部分。

📝 函数应用示例

上一节我们介绍了各种提取函数，本节中我们来看看如何在查询中使用它们。

提取日期部分

DAY()函数可用于从日期中提取“日”的部分。例如，要获取所有涉及猫的救援日期中的“日”：

SELECT DAY(RESCUE_DATE) FROM PETRESCUE WHERE ANIMAL = ‘Cat’;

在WHERE子句中使用函数

日期时间函数也可以用在WHERE子句中进行条件过滤。例如，要统计五月份（即第5个月）的救援次数：

SELECT COUNT(*) FROM PETRESCUE WHERE MONTH(RESCUE_DATE) = 05;

➕ 日期时间运算

除了提取，我们还可以对日期和时间进行算术运算。

日期加法

例如，要找出每个救援日期三天后的日期（假设救援需要在三天内处理完毕）：

SELECT RESCUE_DATE + 3 DAYS FROM PETRESCUE;

使用特殊寄存器计算时间差

数据库还提供了特殊寄存器，如CURRENT_DATE（当前日期）和CURRENT_TIME（当前时间）。例如，要计算从每个救援日期到今天已经过去了多少天：

SELECT CURRENT_DATE - RESCUE_DATE FROM PETRESCUE;

执行此查询的结果将以“年、月、日”的格式显示时间间隔。

🎯 课程总结

本节课中，我们一起学习了SQL数据库中用于处理日期和时间的内置函数。我们了解了DB2中的主要日期时间数据类型（DATE, TIME, TIMESTAMP），掌握了如何使用DAY()、MONTH()等函数提取日期时间的特定部分，并学会了在查询和WHERE子句中应用这些函数。最后，我们还探索了如何进行日期时间的加减运算，以及如何使用CURRENT_DATE等特殊寄存器进行时间差计算。掌握这些函数将极大地增强你处理和分时间序列数据的能力。

017：子查询与嵌套选择 🧩

在本节课中，我们将学习如何编写子查询或嵌套的SELECT语句。子查询是SQL中一项强大的功能，它允许我们将一个查询嵌套在另一个查询内部，从而构建更复杂、更灵活的数据检索操作。

什么是子查询？ 🔍

子查询，也称为嵌套SELECT语句，类似于常规查询，但被放置在括号内并嵌套在另一个查询中。这使得我们能够构建比单独使用简单查询更强大的查询语句。

一个嵌套查询的基本结构示例如下：

SELECT column1, column2
FROM table1
WHERE column1 = (SELECT column1 FROM table2 WHERE condition);

为什么需要子查询？ 🤔

上一节我们介绍了子查询的基本概念，本节中我们来看看为什么需要它。考虑一个来自之前视频的employees表，它包含员工ID、姓名、薪水等列。

假设我们想检索薪水高于平均薪水的所有员工列表。一个直观但错误的尝试可能是：

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

运行此查询会导致错误，提示聚合函数AVG()的使用无效。这是因为像AVG()这样的内置聚合函数，不能直接在WHERE子句中进行计算。

为了克服这个限制，我们可以使用子查询。

在WHERE子句中使用子查询 🛠️

以下是解决上述问题的正确方法，即在WHERE子句中嵌套一个子查询来计算平均薪水：

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

在这个例子中，子查询(SELECT AVG(salary) FROM employees)首先执行，计算出平均薪水，然后这个结果被用于外层查询的WHERE条件中进行比较。

在SELECT列表中使用子查询（列表达式） 📊

子查询不仅可以用在WHERE子句中，还可以用在查询的其他部分，例如SELECT的列列表中。这类子查询被称为列表达式。

假设我们想比较每位员工的薪水与公司平均薪水。直接尝试SELECT employee_id, salary, AVG(salary) AS avg_salary FROM employees;会报错，因为没有指定GROUP BY子句。

我们可以通过在SELECT列表中使用子查询来规避这个错误：

SELECT employee_id, salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

这样，子查询会为结果集中的每一行都计算并返回相同的平均薪水值。

在FROM子句中使用子查询（派生表/表表达式） 🗂️

另一种选择是将子查询作为FROM子句的一部分。这类子查询有时被称为派生表或表表达式，因为外层查询将子查询的结果作为一个数据源来使用。

例如，我们可以创建一个不包含敏感信息（如出生日期、薪水）的员工信息派生表：

SELECT *
FROM (SELECT employee_id, first_name, last_name, department_id
      FROM employees) AS employee_info_all;

在这个简单的例子中，我们本可以直接在外层查询中指定这些列。然而，在处理多表连接等更复杂的场景时，派生表会变得非常强大和有用。

总结 📝

本节课中我们一起学习了子查询和嵌套查询的用法。我们了解到，子查询可以帮助我们构建更丰富的查询逻辑，并克服聚合函数在WHERE子句等位置使用的限制。

具体来说，你学会了：

  • 子查询的基本概念和作用。
  • 如何在WHERE子句中使用子查询来过滤数据。
  • 如何在SELECT列列表中使用子查询作为列表达式。
  • 如何在FROM子句中使用子查询来创建派生表或表表达式。

掌握子查询是提升SQL技能的关键一步，它为你处理复杂的数据检索需求打开了新的大门。

018：使用多个表

在本节课中，我们将学习如何编写查询以访问数据库中的多个表。你将掌握使用子查询和隐式连接这两种方法来组合不同表中的数据。

概述

在之前的课程中，我们学习了如何从单个表中查询数据。然而，实际的数据通常分布在多个相关的表中。为了获取更完整的信息，我们需要学会同时访问多个表。本节将介绍两种实现此目的的方法：子查询和隐式连接。

使用子查询访问多个表

上一节我们介绍了子查询的基本概念。本节中我们来看看如何利用子查询来处理多个表。

子查询允许我们将一个查询的结果作为另一个查询的条件。以下是几种常见的使用场景。

场景一：基于另一表的存在性筛选数据

如果我们只想检索employees表中那些Department ID也存在于departments表中的员工记录，可以使用以下子查询：

SELECT * FROM employees
WHERE Department_ID IN (
    SELECT Department_ID FROM departments
);

在这个例子中，外部查询访问employees表，而子查询在departments表上执行，用于过滤外部查询的结果集。

场景二：通过关联表进行复杂筛选

假设我们想检索位于特定地点（例如 L0002）的所有员工列表。employees表本身没有地点信息，但departments表中有一个Location_ID列。

因此，我们可以使用来自departments表的子查询作为employees表查询的输入：

SELECT * FROM employees
WHERE Department_ID IN (
    SELECT Department_ID FROM departments
    WHERE Location_ID = ‘L0002’
);

场景三：从关联表中获取信息

现在，让我们检索薪资超过70,000美元的员工所在的部门ID和部门名称。

我们需要一个在employees表上的子查询来满足薪资条件，然后将其作为外部查询的输入，以从departments表中获取匹配的部门信息：

SELECT Department_ID, Department_Name FROM departments
WHERE Department_ID IN (
    SELECT Department_ID FROM employees
    WHERE Salary > 70000
);

使用隐式连接访问多个表

除了子查询，我们还可以通过在查询的FROM子句中指定多个表来访问它们。这种方法被称为“隐式连接”。

理解隐式连接

考虑以下示例：

SELECT * FROM employees, departments;

这里我们在FROM子句中指定了两个表。这会导致一个表连接，但请注意，我们并没有显式地使用JOIN操作符。

此示例中的连接结果称为全连接或笛卡尔积，因为第一个表中的每一行都与第二个表中的每一行进行连接。如果你检查结果集，会发现行数比两个表单独的行数要多得多。

使用WHERE子句限制结果

我们可以使用WHERE子句来限制结果集，例如，只返回部门ID匹配的行：

SELECT * FROM employees, departments
WHERE employees.Department_ID = departments.Department_ID;

请注意，在WHERE子句中，我们在列名前加上了表名作为前缀。这是为了完全限定列名，因为不同的表可能有完全相同的列名。

使用表别名简化查询

由于表名有时可能很长，我们可以为表名使用更短的别名，如下所示：

SELECT * FROM employees E, departments D
WHERE E.Department_ID = D.Department_ID;

这里我们为employees表定义了别名E，为departments表定义了别名D，然后在WHERE子句中使用这些别名。

选择特定列

如果我们想查看每位员工的ID及其部门名称，可以输入以下代码：

SELECT E.Employee_ID, D.Department_Name
FROM employees E, departments D
WHERE E.Department_ID = D.Department_ID;

与之前类似，SELECT子句中的列名也可以用别名作为前缀：

SELECT E.Employee_ID, D.Department_ID
FROM employees E, departments D
WHERE E.Department_ID = D.Department_ID;

总结

本节课中我们一起学习了如何使用子查询和隐式连接来操作多个数据库表。子查询适合用于基于另一个表的结果进行过滤或数据检索，而隐式连接则通过在FROM子句中列出多个表并配合WHERE子句来关联它们，提供了一种组合表数据的直接方式。掌握这些基础方法，是进行更复杂数据分析和操作的重要一步。

019：使用Python访问数据库 🐍

在本节课中，我们将学习如何使用Python连接和操作数据库。数据库是数据科学家的重要工具。通过本模块的学习，你将能够解释使用Python连接数据库的基本概念，并在Jupyter Notebook中创建表、加载数据、使用SQL查询数据，最终进行分析。

概述 📋

在实验任务中，你将学习如何在云端创建数据库实例、连接到数据库、使用SQL从数据库查询数据，并使用Python进行分析。你将能够解释连接Python应用程序与数据库的基本概念，描述SQL API，并列举一些流行的基于SQL的数据库管理系统所使用的专有API。

Python的优势回顾 🚀

让我们快速回顾一下使用Python连接数据库的一些优势。Python是一种流行的脚本语言，其生态系统非常丰富，为数据科学提供了易于使用的工具。一些最受欢迎的包包括NumPy、Pandas、Matplotlib和SciPy。

Python易于学习，语法简单。由于其开源特性，Python已被移植到许多平台。只要注意避免使用任何系统依赖的功能，你的Python程序可以在这些平台上运行，无需任何修改。

Python支持关系型数据库系统。Python数据库API（通常称为DB API）的存在使得编写访问数据库的Python代码更加容易。与Python相关的详细文档也易于获取。

Jupyter Notebook简介 📓

Notebook在数据科学领域非常流行，因为它们运行在一个允许创建和共享包含实时代码、公式、可视化和解释性文本的文档的环境中。Notebook界面是一个用于编程的虚拟笔记本环境。

Notebook界面的例子包括Mathematica Notebook、Maple Worksheet、MATLAB Notebook、IPython Jupyter、R Markdown、Apache Zeppelin、Apache Spark Notebook和Databricks Cloud。

在本模块中，我们将使用Jupyter Notebook。Jupyter Notebook是一个开源Web应用程序，允许你创建和共享包含实时代码、公式、可视化和叙述性文本的文档。

以下是使用Jupyter Notebook的一些优势：

  • 支持超过40种编程语言，包括Python、R、Julia和Scala。
  • 可以通过电子邮件、Dropbox、GitHub和Jupyter Notebook查看器与他人共享Notebook。
  • 你的代码可以生成丰富的交互式输出，包括HTML、图像、视频、LaTeX和自定义MIME类型。
  • 你可以利用大数据工具（如Apache Spark），并通过Python、R和Scala探索相同的数据，同时使用Pandas、scikit-learn、ggplot2和TensorFlow进行分析。

Python访问数据库的典型流程 🔄

这是用户通过编写在Jupyter Notebook（一种基于Web的编辑器）上的Python代码访问数据库的典型方式。Python程序通过一种机制与数据库管理系统（DBMS）通信。

Python代码使用API调用连接到数据库。我们将解释SQL API和Python DB API的基础知识。

应用程序编程接口（API）是一组你可以调用的函数，用于获取对某种服务的访问权限。SQL API由库函数调用组成，作为DBMS的应用程序编程接口。

为了将SQL语句传递给DBMS，应用程序会调用API中的函数，并调用其他函数从DBMS检索查询结果和状态信息。

下图展示了一个典型SQL API的基本操作：

  1. 应用程序通过一个或多个API调用开始其数据库访问，这些调用将程序连接到DBMS。
  2. 为了将SQL语句发送到DBMS，程序在缓冲区中将语句构建为文本字符串，然后进行API调用以将缓冲区内容传递给DBMS。
  3. 应用程序进行API调用来检查其DBMS请求的状态并处理错误。
  4. 应用程序通过一个API调用结束其数据库访问，该调用将其与数据库断开连接。

流行的SQL DBMS专有API 📚

现在，让我们学习一些流行的基于SQL的数据库管理系统所使用的专有API的基本概念。

每个数据库系统都有自己的库。如下表所示，它列出了一些应用程序及其对应的SQL API。

以下是几个主要的数据库连接API：

  • MySQL C API：提供对MySQL客户端/服务器协议的低级访问，使C程序能够访问数据库内容。
  • Psycopg2 API：连接Python应用程序与PostgreSQL数据库。
  • ibm_db API：用于连接Python应用程序与IBM DB2数据库。
  • pyodbc API：用于连接到SQL Server数据库。
  • ODBC：用于Microsoft Windows操作系统的数据库访问。
  • OCI：由Oracle数据库使用。
  • JDBC：由Java应用程序使用。

总结 🎯

本节课中，我们一起学习了使用Python访问数据库的核心流程。我们回顾了Python在数据科学中的优势，介绍了Jupyter Notebook这一强大的交互式编程环境，并解释了应用程序通过SQL API与数据库交互的基本原理。最后，我们列举了连接不同数据库系统（如MySQL、PostgreSQL、DB2、SQL Server等）所需的特定Python库或API。掌握这些知识是进行数据获取和后续分析的重要基础。

020：使用DB API编写代码

在本节课中，我们将学习如何使用Python的DB API与数据库进行交互。我们将介绍DB API的基本概念、数据库游标的作用，并通过一个简单的示例演示如何编写代码来连接数据库、执行查询并获取结果。

🔗 DB API简介

上一节我们介绍了Python与数据库交互的基本方式。本节中，我们来看看DB API的具体作用。

DB API是Python访问关系型数据库的标准API。它允许你编写一个程序，与多种关系型数据库进行交互，而无需为每种数据库编写单独的程序。

公式：DB API = Python标准接口 + 多种数据库支持

使用DB API的主要优势如下：

以下是使用DB API的一些优点：

  • 易于实现和理解。
  • 鼓励不同数据库模块之间的一致性。
  • 代码在不同数据库间更具可移植性。
  • 扩展了Python的数据库连接能力。

📚 数据库连接库

每个数据库系统都有其对应的Python库来实现DB API标准。

以下是几种常见数据库及其对应的Python连接库：

  • IBM DB2：使用 ibm_db 库。
  • MySQL：使用 mysql-connector-python 库。
  • PostgreSQL：使用 psycopg2 库。
  • MongoDB：使用 pymongo 库。

🧭 DB API的核心概念

DB API主要围绕两个核心对象展开：连接对象和游标对象。

连接对象 (Connection Object)

连接对象用于建立和管理与数据库的连接及事务。通过 connect() 构造函数创建连接，它返回一个连接对象。该对象提供以下主要方法：

以下是连接对象的关键方法：

  • cursor()：返回一个新的游标对象。
  • commit()：提交所有待处理的事务到数据库。
  • rollback()：回滚到任何待处理事务的起点。
  • close()：关闭数据库连接。

游标对象 (Cursor Object)

游标对象用于执行查询并管理获取结果的操作。它的行为类似于文本处理系统中的光标，可以在结果集中移动，将数据提取到应用程序中。

类比：就像程序通过文件句柄访问文件内容一样，程序通过游标访问查询结果。文件句柄跟踪在文件中的当前位置，游标则跟踪在查询结果中的当前位置。

关于游标的重要说明：

  • 从同一连接创建的游标不是隔离的。一个游标对数据库所做的更改会立即被其他游标看到。
  • 从不同连接创建的游标是否隔离，取决于事务支持的实现方式。

💻 使用DB API的代码流程

现在，让我们通过一个典型的Python应用程序流程，来看看如何使用DB API查询数据库。

以下是使用DB API进行数据库查询的基本步骤：

  1. 导入数据库模块：导入特定数据库的库（如 ibm_db）。
  2. 建立连接：使用 connect() 函数，传入数据库名称、用户名和密码等参数，获取连接对象。
    conn = ibm_db.connect(database, user, password)
  3. 创建游标：通过连接对象的 cursor() 方法创建游标对象。
    cursor = conn.cursor()
  4. 执行查询：使用游标对象的 execute() 方法运行SQL查询。
    cursor.execute("SELECT * FROM table")
  5. 获取结果：使用游标对象的方法（如 fetchall()）获取查询结果。
    rows = cursor.fetchall()
  6. 关闭连接：完成所有操作后，务必使用 close() 方法关闭连接，以释放资源。
    conn.close()

📝 课程总结

本节课中，我们一起学习了Python DB API的核心知识。我们了解了DB API作为标准接口的优势，认识了连接对象和游标对象这两个关键概念及其方法，并掌握了使用DB API连接数据库、执行查询和关闭连接的完整代码流程。记住，始终在操作结束后关闭数据库连接是一个重要的好习惯。

021：使用IBM DB API连接数据库 📚

在本节课中，我们将学习如何使用IBM DB API连接数据库。我们将了解IBM DB API的基本概念，以及使用Python连接IBM DB2数据库所需的凭证和步骤。课程将通过Jupyter Notebook中的代码示例进行演示。

IBM DB API概述 🔍

IBM DB API提供了一系列有用的Python函数，用于访问和操作IBM数据服务器数据库中的数据。这些功能包括连接数据库、准备和执行SQL语句、从结果集中获取行、调用存储过程、提交和回滚事务、处理错误以及检索元数据。

连接数据库所需信息 🔑

要连接到DB2数据库，需要以下信息：

  • 驱动程序名称
  • 数据库名称
  • 主机DNS名称或IP地址
  • 主机端口
  • 连接协议
  • 用户ID
  • 用户密码

使用Python代码连接数据库 💻

上一节我们介绍了连接数据库所需的信息，本节中我们来看看如何在Python中实际建立连接。

以下是创建DB2数据库连接的代码示例：

import ibm_db

# 创建存储连接凭证的DSN对象
dsn = (
    "DRIVER={IBM DB2 ODBC DRIVER};"
    "DATABASE=数据库名;"
    "HOSTNAME=主机地址;"
    "PORT=端口号;"
    "PROTOCOL=TCPIP;"
    "UID=用户名;"
    "PWD=密码;"
)

# 使用connect函数建立非持久连接
try:
    conn = ibm_db.connect(dsn, "", "")
    print("连接成功")
except:
    print("无法连接到数据库")
finally:
    # 关闭连接以释放资源
    if conn:
        ibm_db.close(conn)

我们创建一个连接对象dsn来存储连接凭证。然后，使用IBM DB API的connect函数来建立一个非持久连接，并将dsn对象作为参数传递给该函数。如果成功连接到数据库，代码将输出“连接成功”，否则输出“无法连接到数据库”。最后，通过关闭连接来释放所有资源。

请记住，始终关闭连接非常重要，这样可以避免未使用的连接占用资源。

总结 📝

本节课中我们一起学习了如何使用IBM DB API连接数据库。我们了解了API的基本功能，明确了连接数据库所需的各项凭证，并通过具体的Python代码示例演示了在Jupyter Notebook中建立和关闭DB2数据库连接的全过程。掌握这些步骤是进行后续数据操作的基础。

022：创建表、加载数据和查询数据

在本节课中，我们将要学习使用Python进行数据库操作的基本概念，包括如何创建表、向表中加载数据以及如何查询数据。我们将以IBM DB2 on Cloud数据库和Jupyter Notebooks为例，演示这些任务的具体实现步骤。

完成本课程后，你将能够理解与创建表、加载数据和查询数据相关的基本概念，并掌握使用Python和IBM DB2数据库执行这些操作的方法。

🔗 连接到数据库

首先，我们需要建立与数据库的连接。我们使用ibm_db API的connect方法来获取一个连接资源。

import ibm_db
conn = ibm_db.connect("DATABASE=<dbname>;HOSTNAME=<hostname>;PORT=<port>;PROTOCOL=TCPIP;UID=<username>;PWD=<password>;", "", "")

在上面的代码中，你需要将尖括号<>中的占位符替换为你自己的数据库名称、主机名、端口、用户名和密码。

🏗️ 创建表

在DB2中，有多种创建表的方式，例如使用DB2提供的Web控制台，或者从任何SQL、R或Python环境中创建。本节中我们来看看如何从Python应用程序中在DB2中创建表。

以下是一个商业卡车数据库的示例表结构。我们将学习如何使用Python代码在DB2中创建这个“卡车”表。

要创建表，我们使用ibm_db API的ibm_db.exec_immediate函数。该函数的参数如下：

  • connection: 一个有效的数据库连接资源，由ibm_db.connect或ibm_db.pconnect函数返回。
  • statement: 一个包含SQL语句的字符串。
  • options: 一个可选参数，是一个指定返回结果集游标类型的字典。

以下是创建名为trucks的表的Python代码：

createTableSQL = """
CREATE TABLE trucks (
    serial_no VARCHAR(20) PRIMARY KEY NOT NULL,
    model VARCHAR(20) NOT NULL,
    manufacturer VARCHAR(20) NOT NULL,
    engine_size VARCHAR(20) NOT NULL,
    truck_class VARCHAR(20) NOT NULL
)
"""
createStmt = ibm_db.exec_immediate(conn, createTableSQL)

我们使用ibm_db.exec_immediate函数，并将之前创建的连接资源conn作为第一个参数传递给该函数。下一个参数是SQL语句，即用于创建trucks表的CREATE TABLE查询。新创建的表将包含五列，其中serial_no是主键。

📥 加载数据

上一节我们介绍了如何创建表，本节中我们来看看如何向表中插入数据。我们同样使用ibm_db.exec_immediate函数。

连接资源conn作为第一个参数传递给该函数。下一个参数是SQL语句，即用于向trucks表插入数据的INSERT INTO查询。

以下是向表中插入一行数据的示例：

insertSQL = "INSERT INTO trucks VALUES('A1234', 'Lion', 'Ford', '5.4L', 'Class 8')"
insertStmt = ibm_db.exec_immediate(conn, insertSQL)

执行上述代码后，一行新数据将被添加到trucks表中。类似地，我们可以多次使用ibm_db.exec_immediate函数来添加更多行数据。

🔍 查询数据

现在，你的Python代码已经连接到数据库实例，数据库表也已创建并填充了数据。让我们看看如何使用Python代码从DB2上创建的trucks表中获取数据。

我们再次使用ibm_db.exec_immediate函数。连接资源conn作为第一个参数传递。下一个参数是SQL语句，即SELECT * FROM table查询。

selectSQL = "SELECT * FROM trucks"
selectStmt = ibm_db.exec_immediate(conn, selectSQL)
# 获取并打印结果
row = ibm_db.fetch_tuple(selectStmt)
while row:
    print(row)
    row = ibm_db.fetch_tuple(selectStmt)

Python代码将返回输出，显示trucks表中数据的各个字段。你可以通过参照DB2控制台来检查SELECT查询返回的输出是否正确。

🐼 使用Pandas检索数据

除了直接使用ibm_db，我们还可以使用Pandas库来更便捷地检索和处理数据库表中的数据。Pandas是一个流行的Python库，它包含高级数据结构和操作工具，旨在使Python中的数据分析和处理变得快速而简单。

我们将数据从trucks表加载到一个名为df的DataFrame中。DataFrame是一种类似电子表格的表格数据结构，包含一个有序的列集合，每列可以是不同的值类型。

import pandas as pd
import ibm_db_dbi
# 将ibm_db连接转换为DBAPI-2兼容的连接
pconn = ibm_db_dbi.Connection(conn)
# 使用pandas的read_sql函数执行查询并直接获取DataFrame
df = pd.read_sql("SELECT * FROM trucks", pconn)
print(df.head())

📝 总结

本节课中我们一起学习了使用Python进行数据库操作的核心流程。我们首先建立了与IBM DB2数据库的连接，然后逐步实现了创建表、向表中插入数据以及查询表中数据的操作。最后，我们还介绍了如何使用强大的Pandas库来简化数据检索过程，将查询结果直接转换为易于分析的DataFrame格式。

掌握这些基本操作是进行数据工程和数据分析的重要基础。

023：使用Python分析数据 🐍📊

在本节课中，我们将学习如何使用Python对数据进行基础的探索性分析。我们将以麦当劳菜单营养数据为例，演示如何将数据存储到IBM DB2云数据库中，并使用Python进行基本的数据分析。

概述

探索性数据分析是数据科学的关键步骤，它能帮助我们理解数据的结构、发现模式并识别异常值。本节将结合数据库操作与Python编程，展示一个完整的数据分析流程。

数据准备与加载 🗃️

上一节我们介绍了课程目标，本节中我们来看看如何准备和加载数据。

我们将使用麦当劳菜单营养数据集。麦当劳是一家美国快餐公司，也是全球收入最大的餐饮连锁店。其菜单不仅包含汉堡、薯条、软饮料、奶昔和甜点等快餐食品，也增加了沙拉、鱼类、冰沙和水果等选项。麦当劳提供其菜单项目的营养分析，以帮助消费者平衡饮食。

该数据集来源于Kaggle上的麦当劳菜单营养事实。我们需要在DB2数据库中创建一个表来存储这些数据。

以下是加载数据到DB2表的四个步骤：

  1. 源：将电子表格加载到DB2控制台。
  2. 目标：选择目标模式（Schema）。
  3. 定义：选择将数据加载到现有表或创建新表。若创建新表，需指定表名，并可在数据预览中定义列和数据类型。
  4. 完成：检查设置并开始加载。加载完成后，可以查看已加载数据的统计信息，并浏览表内容。

连接数据库与数据检索 🔗

数据成功加载到关系数据库后，我们可以运行Python脚本来检索和分析数据。DB2支持使用内置分析API、R或Python进行数据分析。

在本课程中，我们将在Jupyter Notebook中运行Python脚本。首先，我们需要连接到数据库。

我们使用ibm_db API的connect方法获取连接资源，然后使用SQL SELECT查询来验证表中加载的行数。输出显示为260行，这与DB2控制台中显示的行数一致。

# 示例：验证行数
query = "SELECT COUNT(*) FROM mcdonalds_nutrition"
# ... 执行查询并获取结果

使用Pandas进行数据分析 🐼

现在，让我们看看如何使用pandas从数据库表中检索数据。

我们使用read_sql方法将数据从mcdonalds_nutrition表加载到名为df的DataFrame中。该方法需要传入SQL SELECT查询语句和连接对象作为参数。

import pandas as pd
import ibm_db
# 假设conn是已建立的数据库连接
df = pd.read_sql("SELECT * FROM mcdonalds_nutrition", conn)

我们可以使用head方法查看创建的DataFrame df的前几行。

接下来是了解数据的时候了。Pandas提供了一系列常见的数学和统计方法。我们使用describe方法来查看DataFrame中数据的汇总统计信息。

summary_stats = df.describe()

探索describe方法的输出，我们可以看到数据框中有260个观测值（即食品项目），以及9个独特的食品类别。同时，我们还能看到不同变量在260个食品项目上的汇总统计信息，如频数、均值、中位数、标准差等。例如，总脂肪的最大值为118。

深入分析：以钠含量为例 🧂

让我们进一步研究数据，尝试理解食品中的一种营养素——钠。

钠的主要来源是食盐。过量摄入钠可能导致血压升高和体液潴留。饮食中常见的钠摄入目标是每天少于2000毫克。

使用麦当劳的营养数据集，我们进行一些基本的数据分析来回答这个问题：哪种食品的钠含量最高？

我们首先使用可视化来探索食品的钠含量。利用seaborn包提供的swarmplot方法，我们创建一个分类散点图，将类别放在X轴，钠含量放在Y轴，数据源为包含麦当劳营养数据集的DataFrame df。

散点图显示了按类别划分的不同食品项目的钠值。我们注意到散点图上有一个约3600的高钠值。

让我们进一步探索这个高钠值，并确定菜单上哪些食品项目具有这个钠值。我们使用Python进行一些基本的数据分析来查找钠含量最高的食品项目。

以下是分析步骤：

  1. 使用describe方法了解钠的汇总统计信息。注意钠的最大值是3600。
    df[‘Sodium’].describe()
  2. 使用idxmax方法找出DataFrame中钠最大值对应的索引值。输出是82。
    max_sodium_index = df[‘Sodium’].idxmax()
  3. 使用at方法查找索引为82的行的食品名称。
    item_name = df.at[max_sodium_index, ‘Item’]

最终，我们发现菜单上钠含量最高的食品是40块装麦乐鸡。

数据可视化探索 📈

可视化对于初步的数据探索非常有用，可以帮助我们理解数据中的关系、模式和异常值。

首先，我们创建一个以蛋白质为X轴、总脂肪为Y轴的散点图。散点图是流行的可视化工具，通过每个观测值的一个点来显示两个变量之间的关系。

我们可以使用seaborn包提供的jointplot函数，输入X轴的蛋白质、Y轴的总脂肪，以及包含麦当劳营养数据集的DataFrame df。

输出的散点图显示了一个有趣的形状。它展示了两个变量——蛋白质和脂肪之间的相关性。相关性是衡量两个变量之间关联程度的指标，其值介于-1和+1之间。我们看到散点图上的点更接近一条正向的直线，因此这两个变量之间存在正相关关系。

在散点图的右上角，我们看到了皮尔逊相关系数值（0.81）以及表示相关显著性的p值，这是一个很好的值，表明变量之间确实存在相关性。

该图还显示了两个直方图：一个在顶部（蛋白质变量），一个在右侧（总脂肪变量）。我们还注意到散点图上有一个点偏离了总体模式，这可能是一个异常值。

使用箱线图进行可视化 📦

接下来，我们看看如何使用箱线图可视化数据。

箱线图是显示一个或多个变量分布的图表。箱体部分捕获了中间50%的数据，而线条和点则指示了可能的偏态和异常值。

让我们为糖含量创建一个箱线图。我们将使用seaborn包中的boxplot函数，并将名为Sugars的列作为输入。

输出结果显示在右侧，箱线图显示食品中糖的平均值约为30克。我们还注意到一些异常值，表明存在糖含量极高的食品项目。数据集中存在糖含量约为128克的食品项目，糖果可能是菜单上这些高糖含量食品之一。

总结 🎯

本节课中，我们一起学习了如何使用Python进行基础的探索性数据分析。我们从将数据加载到IBM DB2云数据库开始，然后使用pandas检索和描述数据。我们以钠含量为例，演示了如何通过统计方法和可视化工具（如散点图和箱线图）来深入分析特定变量、识别异常值（如钠含量最高的食品），并探索变量之间的关系（如蛋白质与脂肪的相关性）。

现在你已经了解了如何使用pandas和可视化工具进行基本的探索性数据分析，接下来可以继续进行本模块的实验练习，以巩固所学概念。

024：使用真实数据集

在本节课中，我们将学习如何在实际工作中处理真实世界的数据集。我们将重点介绍CSV文件的特性、如何将其加载到数据库中，以及查询数据时需要注意的关键事项，例如列名的大小写、特殊字符处理等。

📁 理解CSV文件格式

许多真实世界的数据集以.CSV文件的形式提供。这些是文本文件，其中的数据值通常由逗号分隔。在某些情况下，也可能使用其他分隔符，例如分号。

🐕 示例数据集：Dogs.csv

在本视频中，我们将使用一个名为Dogs.csv的虚构数据集作为示例。该数据集包含狗的名字和品种，我们将用它来说明一些概念，这些概念随后可以应用于真实数据集。

以下是Dogs.csv文件的部分内容示例：

ID Name of dog Breed (dominant breed if not pure breed)
1 Wolfy German Shepherd
2 Fluffy Pomeranian
3 Huggy Labrador

第一行通常包含属性标签，这些标签映射到数据库表中的列名。在本例中，第一行包含三个属性名称：ID、Name of dog和Breed (dominant breed if not pure breed)。

🚀 加载CSV文件到数据库

正如我们所见，CSV文件的第一行通常是包含属性名称的表头行。

如果您使用数据库控制台中的可视化加载工具将数据加载到数据库中，请确保启用“首行为表头”选项。

这将把CSV文件第一行中的属性名称映射为数据库表中的列名，其余行则映射为表中的数据行。

请注意，默认的列名可能并不总是对数据库或查询友好。如果出现这种情况，您可能需要在创建表之前编辑这些列名。

🔤 查询大小写混合的列名

现在，我们来讨论查询小写或大小写混合（即大写和小写组合）的列名。

假设我们使用CSV文件中的默认列名加载了Dogs.csv文件。如果我们尝试使用查询SELECT id FROM dogs;来检索ID列的内容，可能会收到错误提示，指出id无效。

这是因为数据库解析器默认假定列名为大写。然而，当我们把CSV文件加载到数据库时，ID列名是大小写混合的（即大写I和小写D）。

在这种情况下，要选择具有大小写混合名称的列中的数据，我们需要在双引号内指定正确大小写的列名，如下所示：

SELECT "ID" FROM dogs;

请确保在列名周围使用双引号，而不是单引号。

⚠️ 查询包含空格和特殊字符的列名

如果列名包含空格，数据库默认可能会将它们映射为下划线。

例如，在Name of dog列中，三个单词之间有空格。数据库可能会将其更改为name_of_dog。

其他特殊字符，如括号或方括号，也可能被映射为下划线。

因此，在编写查询时，请确保在引号内使用正确的大小写格式，并将特殊字符替换为下划线，如下例所示：

SELECT "ID", "name_of_dog", "breed__dominant_breed_if_not_pure_breed_" FROM dogs;

请注意双引号内单词之间的下划线分隔符。同时，注意breed和dominant之间的双下划线，如示例所示。最后，查询末尾breed单词后的尾随下划线也很重要，它用于替代原列名中的右括号。

🐍 在Jupyter Notebooks中使用引号

在Jupyter Notebooks中使用引号时，您可能会先将查询分配给Python变量，然后在笔记中执行它们。

在这种情况下，如果您的查询包含双引号（例如，用于指定大小写混合的列名），您可以通过对Python变量使用单引号来包裹整个SQL查询，而对列名使用双引号来区分引号。

例如：

selectQuery = 'SELECT "ID" FROM dogs;'

现在，如果您需要在查询中指定单引号（例如，在WHERE子句中指定一个值），该怎么办？在这种情况下，您可以使用反斜杠作为转义字符，如下所示：

selectQuery = 'SELECT * FROM dogs WHERE "name_of_dog" = \'Huggy\';'

📝 处理长查询以提高可读性

如果您有非常长的查询，例如连接查询或嵌套查询，将查询拆分为多行以提高可读性可能会很有用。

在Python Notebooks中，您可以使用反斜杠字符表示延续到下一行，如下例所示：

%sql SELECT "ID", "name_of_dog" \
FROM dogs \
WHERE "name_of_dog" = 'Huggy';

此时，花点时间回顾一下这些特殊字符会很有帮助。

请记住，如果您在Python Notebook中将查询拆分为多行而没有使用反斜杠，可能会收到错误。

✨ 使用SQL Magic命令

在Jupyter Notebooks中使用SQL Magic时，可以在单元格的第一行使用%%sql。这意味着单元格的其余内容将由SQL Magic解释。

例如：

%%sql
SELECT "ID", "name_of_dog"
FROM dogs
WHERE "name_of_dog" = 'Huggy';

再次请注意示例中显示的特殊字符。当使用%%sql时，每行末尾不需要反斜杠。

🔍 限制检索的行数

此时您可能会问，如何限制检索的行数？这是一个很好的问题，因为一个表可能包含数千甚至数百万行，而您可能只想查看一些样本数据，或者只看几行以了解表中包含的数据类型。

您可能想直接使用SELECT * FROM table_name;来检索结果到Pandas DataFrame，然后对其使用.head()函数。但这样做可能会导致查询运行很长时间。

相反，您可以使用LIMIT子句来限制结果集。例如，使用以下查询仅检索名为census_data的表中的前三行：

SELECT * FROM census_data LIMIT 3;

🎯 总结

在本节课中，我们一起探讨了处理真实世界数据集时的一些注意事项和技巧。我们学习了CSV文件的结构、如何正确加载数据、处理大小写混合及包含特殊字符的列名、在Jupyter Notebooks中编写查询的注意事项，以及如何使用LIMIT子句高效地预览数据。掌握这些基础知识将帮助您更自信地处理实际数据分析任务。

025：数据科学SQL数据库和SQL 🗃️

P25：获取表和列的详细信息

在本节课中，我们将学习如何从数据库中获取表及其列的详细信息。这对于在拥有多个表或记不清确切名称时，有效地浏览和管理数据库结构至关重要。

获取数据库中的表列表

有时，数据库可能包含多个表，您可能无法准确记住表名。例如，您可能不确定表是叫 dog、dogs 还是 four_legged_mammals。

数据库系统通常包含系统表或目录表，您可以从这些表中查询表列表及其属性。不同数据库系统的目录名称不同：

  • 在 DB2 中，称为 SYSIBM.SYSTABLES（视频中提到的“CisCAT tables”可能为口误或特定版本）。
  • 在 SQL Server 中，称为 INFORMATION_SCHEMA.TABLES。
  • 在 Oracle 中，称为 ALL_TABLES 或 USER_TABLES。

上一节我们介绍了获取表列表的必要性，本节中我们来看看在 DB2 中的具体操作方法。

要获取 DB2 数据库中的表列表，可以运行以下查询：

SELECT * FROM SYSIBM.SYSTABLES;

这个 SELECT 语句会返回包括系统表在内的所有表，因此最好对结果进行过滤。以下是更精确的查询示例：

SELECT TABSCHEMA, TABNAME, CREATE_TIME
FROM SYSIBM.SYSTABLES
WHERE TABSCHEMA = 'ABC12345';

请注意：您需要将 'ABC12345' 替换为您自己的 DB2 用户名。

当执行 SELECT * FROM SYSIBM.SYSTABLES 时，您会获得表的所有属性。有时我们只关心特定属性，例如表的创建时间。

假设您创建了多个名称相似的表，例如 dog1、dog_test、dogtest1 等，但您想确认其中哪个是最后创建的。为此，您可以发出如下查询：

SELECT TABSCHEMA, TABNAME, CREATE_TIME
FROM SYSIBM.SYSTABLES
WHERE TABSCHEMA = 'QCM54853'
ORDER BY CREATE_TIME DESC;

输出结果将包含您模式（Schema）中所有表的模式名、表名和创建时间。

获取表中的列列表

接下来，我们来讨论如何获取表中列的列表。

如果您记不清某列的确切名称，例如不确定它是否包含小写字符或下划线，在 DB2 中，您可以运行如下查询：

SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TABNAME = 'DOGS';

补充信息：在 MySQL 中，您可以简单地运行命令：

SHOW COLUMNS FROM dogs;

或者，您可能想了解特定属性，例如列的数据类型及其长度。在 DB2 中，可以执行如下语句：

SELECT COLNAME, TYPENAME, LENGTH
FROM SYSIBM.SYSCOLUMNS
WHERE TABNAME = 'DOGS';

以下是从 Jupyter Notebook 中检索名为 Chicago_Crime_Data 的真实表的列属性结果示例。请注意输出中，某些列名显示不同的大小写。例如，列标题 Arrest 的首字母 A 是大写，其余字符是小写。

请记住：当您在查询中引用此列时，不仅必须将单词 Arrest 用双引号括起来，还必须在引号内保持正确的大小写。

总结

本节课中我们一起学习了如何检索数据库中的表和列信息。我们了解了：

  1. 如何使用系统目录表（如 DB2 的 SYSIBM.SYSTABLES）来获取表列表及其属性（如创建时间）。
  2. 如何使用系统目录表（如 DB2 的 SYSIBM.SYSCOLUMNS）来获取表的列列表及其详细信息（如数据类型和长度）。
  3. 注意在查询中引用具有特定大小写格式的列名时，需要使用双引号并保持原样。

掌握这些技巧将帮助您更高效地探索和理解数据库结构。

026：视图（Views）👁️

在本节课中，我们将要学习SQL中的一个重要概念——视图。我们将了解视图的定义、适用场景以及如何创建和使用视图。

视图是一种表示一个或多个表（或视图）中数据的替代方式。它可以包含来自一个或多个基表或现有视图的全部或部分列。创建视图相当于为结果表创建了一个命名的规范，之后可以像查询普通表一样查询这个视图。你还可以通过对视图执行插入、更新和删除操作来修改基表中的数据。

当你定义一个视图时，存储的是视图的定义。视图所代表的数据则存储在基表中，而非视图本身。

何时使用视图？🤔

上一节我们介绍了视图的基本概念，本节中我们来看看视图的主要应用场景。

以下是视图的几个典型用途：

  • 数据筛选：用于展示给定表中的特定数据，从而可以隐藏敏感信息，如税务信息、出生日期或薪资。
  • 数据整合：以有意义的方式组合两个或多个表的数据。
  • 简化数据访问：通过授予对视图的访问权限，而不授予对底层基表的访问权限，来简化数据访问。
  • 数据隔离：仅展示与使用该视图的流程相关的部分数据。

例如，你可以创建一个视图，仅显示员工表中的非敏感数据，如员工ID、姓名、地址、职位ID、经理ID和部门ID。该视图不会显示薪资或出生日期等敏感信息。

如何创建视图？🔨

了解了视图的用途后，本节我们将学习创建视图的具体语法。

你可以使用 CREATE VIEW 语句来创建一个基于一个或多个表（或视图）的视图。

创建视图的基本语法如下：

CREATE VIEW view_name [(column1, column2, ...)]
AS
SELECT column1, column2, ...
FROM base_table_name
[WHERE condition];

语法说明：

  • CREATE VIEW：用于创建视图的关键字。
  • view_name：为视图指定一个名称（长度最多128个字符）。
  • (column1, column2, ...)：可选。列出你想在视图中包含的列。你可以使用别名来为这些列命名。
  • AS SELECT ...：指定构成视图的列和数据来源。
  • FROM base_table_name：指定视图所基于的基表名称。
  • WHERE condition：可选。用于筛选视图中包含哪些行。

以下是一个创建视图的示例。该语句创建了一个名为 F_Fin 的视图，它基于 employees 表，并且只包含经理ID为30002的行。

CREATE VIEW F_Fin AS
SELECT emp_id, f_name, l_name, job_id, manager_id, dept_id
FROM employees
WHERE manager_id = 30002;

视图是动态的。它由用于创建它的 SELECT 语句所返回的数据构成。当你在另一个SQL语句中使用视图时，其行为就像你使用了一个返回该视图内容的 SELECT 语句一样。

用于创建视图的 SELECT 语句可以引用其他视图和表，并且可以使用 WHERE、GROUP BY 和 HAVING 子句。但它不能使用 ORDER BY 子句，也不能引用主机变量。

创建视图后，你可以使用 SELECT 语句来查看视图中的信息，以验证是否只包含了经理ID为30002的行。

SELECT * FROM F_Fin;

如何删除视图？🗑️

最后，如果你需要完全移除一个视图，可以使用 DROP VIEW 语句。

DROP VIEW view_name;

课程总结 📝

本节课中我们一起学习了SQL视图。

  • 视图是访问表中数据的另一种方式。
  • 视图可以包含来自多个基表和现有视图的指定列。
  • 视图一旦创建，就可以像表一样被查询，并且可以通过视图修改基表中的数据。
  • 视图是动态的，只有视图的定义被存储，数据仍保存在基表中。
  • 你可以使用 CREATE VIEW 语句基于一个或多个表（或现有视图）来创建视图。
  • 可以使用 DROP VIEW 语句来删除视图。

027：存储过程

在本节课中，我们将要学习存储过程。我们将了解存储过程是什么，使用存储过程的好处，以及如何创建和使用存储过程。

什么是存储过程？🤔

存储过程是一组存储在数据库服务器上并执行的SQL语句。因此，无需从客户端向服务器发送多条SQL语句，你可以将它们封装在服务器上的一个存储过程中，然后从客户端发送一条语句来执行它们。

存储过程的优势 💪

以下是使用存储过程的主要好处：

  • 减少网络流量：因为只需要一次调用即可执行多条语句。
  • 提升性能：处理过程发生在存储数据的服务器上，只有最终结果会传回客户端。
  • 代码复用：多个应用程序可以为相同的工作使用同一个存储过程。
  • 增强安全性：A. 你无需向客户端开发人员暴露所有表和列的信息；B. 你可以使用服务器端逻辑在数据被系统接受之前进行验证。

需要注意的是，SQL并非一个功能完备的编程语言，因此不应尝试将所有业务逻辑都写在存储过程中。

如何创建存储过程 🛠️

上一节我们介绍了存储过程的概念和优势，本节中我们来看看如何在Db2 on cloud上使用SQL创建一个存储过程。

首先，使用 CREATE PROCEDURE 语句，指定过程名称及其将接受的任何参数。在下面的例子中，UPDATE_SALARY 过程将接受一个员工编号和一个评级，它将根据评级来更新员工的薪资。

CREATE PROCEDURE UPDATE_SALARY (IN emp_id INT, IN rating INT)

接着，声明你使用的语言。

LANGUAGE SQL

然后，将你的过程逻辑包裹在 BEGIN 和 END 语句中。在这个例子中，给评级为1的员工加薪10%，其他所有员工加薪5%。

BEGIN
    IF rating = 1 THEN
        UPDATE employees SET salary = salary * 1.10 WHERE employee_id = emp_id;
    ELSE
        UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_id;
    END IF;
END

请注意，你可以在过程逻辑中直接使用传递给过程的参数信息。

如何调用存储过程 📞

你可以从外部应用程序或动态SQL语句中调用存储过程。要调用我们刚刚创建的 UPDATE_SALARY 存储过程，请使用 CALL 语句，后跟存储过程的名称并传递所需的参数。在这个例子中，参数是员工ID和该员工的评级。

CALL UPDATE_SALARY(12345, 1);

总结 📝

本节课中我们一起学习了存储过程。我们了解到存储过程是在服务器上执行的一组SQL语句。与向服务器发送SQL语句相比，存储过程提供了许多好处。你可以在动态SQL语句和外部应用程序中使用存储过程。

028：原子事务 🧩

在本节课中，我们将要学习数据库中的原子事务。我们将了解事务是什么，为什么它们对维护数据一致性至关重要，以及如何使用SQL命令来管理事务。

什么是事务？🤔

上一节我们介绍了课程概述，本节中我们来看看事务的基本概念。

一个事务是一个不可分割的工作单元。它可以由一个或多个SQL语句组成。但要使事务被视为成功，要么所有这些SQL语句都必须成功完成，使数据库进入一个新的稳定状态；要么一个都不完成，使数据库保持事务开始前的状态。

例如，当你使用银行卡购物时，必须发生很多事情：商品必须加入购物车、支付必须被处理、你的账户必须扣除正确金额、商店账户必须收到款项、该商品的库存必须相应减少。

深入分析示例 💳

让我们更详细地看一个例子。如果Rose购买了一双价值200美元的靴子，那么需要执行多个更新操作。

以下是完成此购买可能需要执行的SQL语句：

  1. 使用UPDATE语句减少Rose的账户余额。
    UPDATE accounts SET balance = balance - 200 WHERE name = 'Rose';
  2. 使用另一个UPDATE语句为鞋店账户增加200美元。
    UPDATE accounts SET balance = balance + 200 WHERE name = 'Shoe Shop';
  3. 使用最后一个UPDATE语句将鞋店中靴子的库存水平减少一。
    UPDATE inventory SET stock = stock - 1 WHERE product = 'Boots' AND shop = 'Shoe Shop';

核心要求是：如果这些更新语句中的任何一条失败，整个事务都应该失败，以保持数据处于一致状态。

理解ACID事务 🛡️

上一节我们看到了一个多步骤操作的例子，本节中我们来定义确保此类操作安全性的属性。

示例中的这类事务被称为ACID事务。ACID是一个缩写，代表：

  • 原子性：所有更改必须全部成功执行，或者全部不执行。
  • 一致性：事务前后数据必须处于一致状态。
  • 隔离性：事务运行时，其他进程不能更改所涉及的数据。
  • 持久性：事务所做的更改必须持久保存。

如何管理事务？🛠️

了解了ACID属性后，本节我们来看看实际操作中如何控制事务。

在Db2 on Cloud中，使用 BEGIN 命令启动一个事务。这个命令通常是隐式的。在此之后发出的任何命令都是事务的一部分，直到发出 COMMIT 或 ROLLBACK 命令。

  • 如果所有命令都成功完成，则发出 COMMIT 命令，将数据库中的所有更改保存到一个一致的、稳定的状态。
    COMMIT;
  • 如果任何命令失败（例如，Rose的账户没有足够的钱支付），则可以发出 ROLLBACK 命令，撤销所有更改，使数据库恢复到之前一致的稳定状态。
    ROLLBACK;

在应用程序中使用事务 💻

上一节我们介绍了在SQL环境中直接管理事务，本节中我们看看如何在编程语言中集成事务。

SQL语句可以从Java、C、R和Python等语言调用。这需要使用数据库特定的访问API，例如Java的JDBC或Python的IBMDB数据库连接器。

大多数语言使用完全相同的SQL命令来启动和控制事务，包括 COMMIT 和 ROLLBACK。记住 BEGIN 通常是隐式的，不需要显式调用。

以下是一个概念性示例（伪代码）：

# 伪代码示例
try:
    # 执行SQL语句1 (UPDATE account...)
    # 执行SQL语句2 (UPDATE shop_balance...)
    # 执行SQL语句3 (UPDATE inventory...)
    connection.commit()  # 所有成功则提交
except Exception as e:
    connection.rollback()  # 任何失败则回滚

将SQL命令合并到应用程序代码中，使你能够创建错误检查例程，进而控制事务是提交还是回滚。

总结 📚

本节课中我们一起学习了：

  • 事务代表一个完整的工作单元，可以包含一个或多个SQL语句。
  • ACID事务要求所有SQL语句必须全部成功完成，或者全部不完成，这确保了数据库始终处于一致状态。
  • ACID代表原子性、一致性、隔离性、持久性。
  • SQL命令 BEGIN、COMMIT 和 ROLLBACK 用于管理ACID事务。
  • SQL命令可以从C、R和Python等编程语言中调用，从而在应用程序中实现复杂的事务逻辑和错误处理。

029：联接概述 🔗

在本节课中，我们将要学习SQL中一个核心且强大的功能——联接（JOIN）操作。我们将了解联接操作符的定义，解释主键和外键在联接中的作用，并列出不同类型的联接操作符。

什么是联接操作符？ 🤔

一个简单的SELECT语句可以从单个表的一列或多列中检索数据。更复杂的情况是从两个或更多表中检索数据，这导致了结果集生成的多种可能性。

为了组合来自两个表的数据，你需要使用联接操作符。一个联接操作基于这些表中某些列之间的关系，将来自两个或更多表的行组合起来。

数据库中的关系与键 🔑

在简化的图书馆数据库示例中，作者（author）和书籍（book）是实体。实体关系图代表了作者、书籍以及其他实体（如借阅者、借阅记录、副本和作者列表）的关系数据模型。信息被拆分到不同的表中。

例如，如果你想知道哪位借阅者借出了哪本书的哪个副本，你需要从三个表中收集数据：borrower（借阅者）、loan（借阅记录）和copy（副本）表。这时你就需要使用联接操作符。

首先，你需要识别这些表之间的关系，即每个表中用于链接表的列。

在实体关系图中，注意author_id、book_id、borrower_id和copy_id旁边有主键图标。主键（Primary Key） 唯一标识表中的每一行。

同时，在屏幕下半部分的实体中，某些属性旁边标有(FK)。这标识了外键（Foreign Key），它是一组引用另一个实体的主键的列。

例如，loan实体有一个带有(FK)标识的borrower_id属性。在这个例子中，borrower_id属性是loan实体中的外键，它引用了borrower实体的主键。

因此，如果你想知道哪位借阅者有书在借，你需要从borrower和loan表中收集数据，你将需要两个表中的borrower_id。

联接多个表 🔄

到目前为止，你已经看到了组合两个表的例子。但如果你需要组合来自三个或更多不同表的数据呢？你只需在联接中添加新的表。

例如，如果你想知道哪些借阅者有书在借，以及他们借的是哪本书的哪个副本：

  1. 首先，通过匹配borrower_id，联接borrower表和loan表的信息。
  2. 然后，通过匹配copy_id，联接loan表和copy表的信息。

联接的类型 📊

SQL提供了几种不同类型的联接。你可以提取对应于所涉及两个表交集的数据集，也可以选择一个更大的数据集，甚至可以选择组合这两个表中所有数据。

以下是主要的联接类型：

  • 内联接（INNER JOIN）：最常见的联接类型。它只显示两个表中在公共列（通常是一个表的主键，在第二个表中作为外键存在）上具有匹配值的行。

    • 公式/逻辑描述：结果集 = 表A ∩ 表B（基于匹配条件）

  • 外联接（OUTER JOIN）：返回匹配的行，甚至返回一个或另一个表中不匹配的行。外联接有多种变体，可用于细化你的结果集。

总结 📝

本节课中我们一起学习了：

  1. 可以使用联接操作符来组合来自两个或更多表的行。
  2. 被联接的表通过一个公共列相关，该列通常是一个表的主键，并作为外键出现在另一个表中。
  3. 联接主要有两种类型：内联接和外联接，它们分别用于获取不同范围的数据组合。

掌握联接是进行复杂数据查询和分析的基础，下一节我们将深入探讨内联接的具体语法和应用场景。

030：内部连接

在本节课中，我们将要学习SQL中的内部连接。我们将了解什么是内部连接，何时使用它，并掌握其基本语法。通过学习，你将能够描述内部连接的工作原理，并运用它从多个关联表中提取匹配的数据。

什么是连接操作？

连接操作基于两个或多个表之间某些列的关系，将这些表的行组合起来。

表连接主要有两种类型：内部连接和外部连接。

最常见的连接类型是内部连接，它只显示两个表中在公共列上具有匹配值的行。这个公共列通常是一个表的主键，同时也是另一个表的外键。

内部连接的语法

上一节我们介绍了连接操作的基本概念，本节中我们来看看内部连接的具体语法。

以下是一个内部连接SELECT语句的语法示例：

SELECT B.borrower_id, B.last_name, B.country, L.borrower_id, L.loan_date
FROM borrower AS B
INNER JOIN loan AS L
ON B.borrower_id = L.borrower_id;

想象一下，你想检索所有借书人及其借书日期的列表。这需要从borrower表和loan表中获取数据。

在FROM子句中，我们指定了borrower表和loan表之间的连接：borrower INNER JOIN loan。我们使用别名B代表borrower表，L代表loan表。

在JOIN子句左侧指定的表称为左表。在这个例子中，borrower表是左表。

对于这个连接，我们从borrower表中选择borrower_id、last_name和country列，从loan表中选择borrower_id和loan_date列。

在ON子句中，我们指定了连接谓词。在这个例子中，条件是borrower表中的borrower_id等于loan表中的borrower_id。

请注意，在这个连接中，每个列名前都带有字母B或L作为前缀。在SQL中，这被称为别名。使用别名比重复书写整个表名要方便得多。

结果集说明

以下是内部连接查询的结果集特点：

结果集仅显示两个表中具有相同borrower_id的行。

borrower_id、last_name和country列取自borrower表，并与来自loan表的borrower_id和loan_date列连接在一起。

只有当borrower_id匹配时，相应的行才会被显示出来。

总结

本节课中我们一起学习了SQL的内部连接。

你了解到，内部连接只返回那些在公共列上具有匹配值的行。这个公共列通常是一个表的主键，同时作为外键存在于第二个表中。

来自连接表中没有匹配值的行，不会出现在最终的结果集中。

通过掌握内部连接，你能够有效地从多个相关的数据库表中组合和提取所需的信息。

031：外部连接 🔗

在本节课中，我们将要学习SQL中的外部连接。外部连接是连接操作的一种，它允许我们不仅获取两个表中匹配的行，还能获取那些在另一个表中没有匹配项的行。这对于数据分析中需要查看完整数据集，包括缺失关联数据的情况非常有用。

概述

与内连接不同，外部连接会返回连接列中具有匹配值的行，同时也会返回表之间没有匹配的行。SQL提供了三种类型的外部连接：左外部连接、右外部连接和全外部连接。

左外部连接

上一节我们介绍了外部连接的基本概念，本节中我们来看看左外部连接的具体机制。

在左外部连接中，连接谓词左侧第一个表的所有行都会被包含在结果中，而只有连接谓词右侧第二个表中匹配的行会被包含。

左连接会匹配左表的所有行，并将信息与右表中符合查询指定条件的行相结合。

以下是左外部连接的基本语法示例：

SELECT borrower.borrower_id, borrower.last_name, borrower.country, loan.loan_date
FROM borrower
LEFT JOIN loan ON borrower.borrower_id = loan.borrower_id;

在这个例子中，borrower表是SELECT语句FROM子句中指定的第一个表，因此它是左表，而loan表是右表。borrower表位于连接操作符的左侧，因此将选择borrower表的所有行，并根据查询中指定的条件（本例中是borrower_id列）与loan表的内容相结合。

左连接从borrower表中选择每个borrower_id，并显示loan表中的loan_date。结果集显示了borrower表中的每个borrower_id以及该借款人的贷款日期。最后三行没有贷款日期，因此borrower_id和loan_date显示为NULL值。

右外部连接

了解了左连接后，我们接下来探讨右外部连接。

在右外部连接中，连接谓词右侧第二个表的所有行都会被包含，而只有连接谓词左侧第一个表中匹配的行会被包含。

右连接会匹配右表的所有行，并将信息与左表中符合查询指定条件的行相结合。

以下是右外部连接的基本语法示例：

SELECT loan.borrower_id, loan.loan_date, borrower.last_name, borrower.country
FROM borrower
RIGHT JOIN loan ON borrower.borrower_id = loan.borrower_id;

在这个例子中，borrower表是左表，loan表是右表。在FROM子句中，loan表位于连接操作符的右侧，因此将选择loan表的所有行，并根据查询中指定的条件与borrower表的内容相结合。

对于右连接，将从loan表中选择borrower_id和loan_date列，并从borrower表中选择borrower_id、last_name和country列，条件是loan表中的borrower_id与borrower表中的borrower_id匹配。

结果集显示了loan表中的每个borrower_id以及该借款人的贷款日期，前提是该borrower_id在borrower表中也存在。对于最后一行，borrower表中没有匹配的行，因此borrower_id、last_name和country显示为NULL值。这可能表明图书馆存在一个问题：有一本书借给了一个未知的人。

全外部连接

最后，我们来学习功能最全面的全外部连接。

全外部连接返回右表和左表的所有行。因此，全连接可能返回一个非常大的结果集。

全连接的结果集是符合查询指定条件的两个表的所有行，加上右表中所有不匹配的行。

以下是全外部连接的基本语法：

SELECT borrower.borrower_id, borrower.last_name, borrower.country, loan.borrower_id, loan.loan_date
FROM borrower
FULL JOIN loan ON borrower.borrower_id = loan.borrower_id;

对于全连接，您选择borrower表的所有行和loan表的所有行。

结果集显示了borrower表中列出的所有八条记录以及loan表中的相应数据。再次地，有三行返回NULL值，因为借款人Peters、Lee和Wang从未借过书。最后一行返回loan表中的borrower_id和loan_date值，但从borrower表返回NULL值。在这种情况下，borrower表中没有匹配项，这本书的借款人是未知的。

总结

本节课中我们一起学习了SQL中外部连接的三种类型。

  • 左外部连接：返回左表的所有行，以及右表中与左表匹配的行。同时返回左表中在右表没有匹配项的所有行。
  • 右外部连接：返回右表的所有行，以及左表中与右表匹配的行。同时返回右表中在左表没有匹配项的所有行。
  • 全外部连接：返回两个表中所有匹配的行，以及两个表中所有没有匹配项的行。

掌握这些连接类型，可以帮助您根据不同的数据分析需求，灵活地组合和查询数据库中的表。

posted @ 2026-03-26 08:53  布客飞龙II  阅读(6)  评论(0)    收藏  举报