数据库SQL

TPC-C模式（部分）

一个批发供应商通过多个仓库进行运营。这些仓库维护公司销售的物品的库存。我们记录每个仓库中每种可用物品的库存数量。仓库具有唯一标识符、名称以及由街道、城市和国家给出的位置。物品具有唯一标识符、唯一图像标识符、名称和价格。

实体关系图

SQL

SQL不是一种编程语言。

SQL是一种专用于在关系模型中创建和操作数据的查询语言。它不是图灵完备的。由于其受限的表达能力，可以更容易地进行自动优化，从而实现高效的数据独立性。

传统上，SQL语言可以分为五个部分：数据库定义语言（DDL）用于创建表和约束，数据操作语言（DML）用于填充表，数据查询语言（DQL）用于查询表，数据库控制语言（DCL）用于其他管理和调优方面，以及一些扩展，例如过程化SQL。此外，数据库编程语言（例如Oracle中的PL/SQL，PostgreSQL中的PL/pgSQL）允许尽可能多地在数据库服务器上编写应用程序逻辑。

DDL--数据库定义语言

● （Data Definition Language，数据定义语言）语句用于创建一个名为 stocks 的表，并在表中定义了一些完整性约束。

CREATE TABLE stocks (
    w_id INTEGER REFERENCES warehouses(w_id),
    i_id INTEGER REFERENCES items(i_id),
    s_qty SMALLINT CHECK(s_qty > 0),
    PRIMARY KEY (w_id, i_id)
);

代码解释:

1. CREATE TABLE stocks (：这一行开始创建一个名为 stocks 的新表。

2. w_id INTEGER REFERENCES warehouses(w_id),：这一行定义了一个名为 w_id 的列，数据类型为 INTEGER。REFERENCES warehouses(w_id) 表示这个列是一个外键，它引用了 warehouses 表中的 w_id 列。这意味着 stocks 表中的 w_id 值必须在 warehouses 表的 w_id 列中存在，以保证数据的一致性。

3. i_id INTEGER REFERENCES items(i_id),：这一行定义了一个名为 i_id 的列，数据类型同样为 INTEGER。REFERENCES items(i_id) 表示这个列是另一个外键，它引用了 items 表中的 i_id 列。这保证了 stocks 表中的 i_id 值必须在 items 表的 i_id 列中存在。

4. s_qty SMALLINT CHECK(s_qty > 0),：这一行定义了一个名为 s_qty 的列，数据类型为 SMALLINT，这通常用于存储较小的整数。CHECK(s_qty > 0) 是一个约束，用于确保 s_qty 列中的值始终大于 0，这在表示库存数量时非常有用，因为库存数量通常不能为负。

5. PRIMARY KEY (w_id, i_id)：这一行定义了表的主键，由 w_id 和 i_id 两列组成。这意味着表中的每一行都将通过这两个列的值组合来唯一标识，同时保证了表中不会有两行具有相同的 w_id 和 i_id 组合，这有助于维护数据的唯一性和完整性。

功能解释:

通过创建这个 stocks 表，可以跟踪每个仓库（由 w_id 表示）中每个物品（由 i_id 表示）的库存数量（由 s_qty 表示）。外键约束确保了只有存在于 warehouses 和 items 表中的仓库和物品才能在 stocks 表中有库存记录，而 CHECK 约束则确保了库存数量始终为正值。主键约束保证了每个物品在每个仓库中的库存记录的唯一性。这样的设计有助于维护库存数据的准确性和一致性。

DML--数据操作语言

● （Data Manipulation Language，数据操作语言）代码展示了如何在数据库中插入新数据、删除某些数据以及更新数据。

1 INSERT INTO items (i_id, i_im_id, i_name, i_price)
2 VALUES (501, '54868530', 'Remdesivir', 520);

1. INSERT INTO items (i_id, i_im_id, i_name, i_price)：这行代码开始了一个插入操作，目标是 items 表。在括号中指定了要插入数据的列：i_id（物品ID），i_im_id（可能是一个与物品相关的中间ID），i_name（物品名称），以及i_price（物品价格）。

2. VALUES (501, '54868530', 'Remdesivir', 520);：这行指定了上述列对应的值，即插入一个ID为501，中间ID为'54868530'，名称为'Remdesivir'，价格为520的新物品。

1 DELETE FROM stocks
2 WHERE s_id = '394'

1. DELETE FROM stocks：这行代码开始了一个删除操作，目标是 stocks 表。

2. WHERE s_id = '394'：这个条件指定了删除操作的目标行，即 s_id（可能是库存ID或其他标识符）等于'394'的记录。

1 UPDATE warehouses
2 SET s_city = 'Johor Bahru'
3 WHERE s_city='Nusajaya' AND w_country='Malaysia';

1. UPDATE warehouses：这行代码开始了一个更新操作，目标是 warehouses 表。

2. SET s_city = 'Johor Bahru'：这行指定了更新的内容，即将 s_city（可能是仓库所在城市的列）的值改为'Johor Bahru'。

3. WHERE s_city='Nusajaya' AND w_country='Malaysia'：这个条件指定了哪些行将被更新，即那些 s_city 值为'Nusajaya' 且 w_country（仓库所在国家的列）值为 'Malaysia'的记录。

DQL--数据查询语言

● （Data Query Language，数据查询语言）代码展示了如何查询数据库中特定物品（在这个例子中是阿司匹林）的总库存量。

1 SELECT SUM(s.s_qty)
2 FROM items i NATURAL JOIN stocks s
3 WHERE i.i_name='Aspirin';

代码解释:

1. SELECT SUM(s.s_qty)：这一行指定了查询的目标，即计算（求和）s_qty（库存量）列的值。这里使用了 SUM 聚合函数来计算总和，其中 s.s_qty 指的是 stocks 表中的 s_qty 列，s 是 stocks 表的别名。

2. FROM items i NATURAL JOIN stocks s：这一行指定了查询涉及的表。items i 将 items 表赋予了别名 i，stocks s 同样将 stocks 表赋予了别名 s。NATURAL JOIN 是一种连接操作，它基于两个表中具有相同名称的所有列自动执行连接。这意味着 items 表和 stocks 表中具有相同列名的列将用于连接这两个表。

3. WHERE i.i_name='Aspirin'：这一行指定了查询的条件，即只考虑 items 表中 i_name（物品名称）列值为 'Aspirin' 的行。i.i_name 表示 items 表中的 i_name 列，i 是 items 表的别名。

DBTG CODASYL

● 计算数据库中“阿司匹林”总库存量。

1 sum := 0;
2 item.i_name := ''aspirin'';
3 find any item using i_name;
4 find first stock within wh;
5 while DB-status = 0 do
6 begin
7     get stock;
8     sum := sum + stock.s_qty;
9     find next stock within wh;
10 end
11 print(sum);

逐行解释：

1. sum := 0;：初始化变量 sum 为 0，用于累加库存量。

2. item.i_name := ''aspirin'';：将 item 记录的 i_name 属性设置为 "aspirin"，准备用于后续搜索。

3. find any item using i_name;：使用 i_name 属性（在这个例子中是 "aspirin"）查找任意一个匹配的 item 记录。

4. find first stock within wh;：在与找到的 item 记录相关的 stock 记录集合（可能表示为 wh）中查找第一个 stock 记录。

5. while DB-status = 0 do：如果数据库状态为 0（表示操作成功或有更多记录要处理），则继续执行循环。

6. begin：标记循环块的开始。

7. get stock;：获取当前循环中的 stock 记录。

8. sum := sum + stock.s_qty;：将当前 stock 记录的库存量 s_qty 加到 sum 上，累加总库存量。

9. find next stock within wh;：在当前 item 记录相关的 stock 集合中查找下一个 stock 记录。

10. end：标记循环块的结束。

11. print(sum);：打印累加后的总库存量 sum。

内容解释：

这段代码演示了如何在DBTG CODASYL风格的数据库中计算特定物品（在此例为“阿司匹林”）的总库存量。它通过设置搜索条件、遍历相关库存记录并累加其数量来实现，最终打印出总和。这种导航式数据库操作方式与现代关系型数据库的查询语言（如SQL）大相径庭，侧重于记录和集合之间的关系以及通过指针等导航元素进行数据访问。

DCL--数据库控制语言

（Data Control Language，数据控制语言），分别用于在数据库中创建索引和授予用户特定的权限。

CREATE INDEX i_i_price ON items (i_price);

• CREATE INDEX i_i_price：这一行开始了一个创建索引的操作，索引的名称被指定为 i_i_price。索引用于加速数据库查询，特别是对于那些频繁进行搜索、排序或聚合操作的列。
• ON items (i_price)：这指定了索引将被创建在 items 表的 i_price 列上。i_price 很可能代表物品的价格，对这个属性创建索引可以提高基于价格的查询效率。

GRANT UPDATE ON stocks TO john;

• GRANT UPDATE：这一行表示授予的权限类型是更新（UPDATE）权限。这意味着被授权的用户可以修改表中的现有记录。
• ON stocks TO john：这表示更新权限被授予于 stocks 表，并且这项权限被授予给用户 john。这允许 john 对 stocks 表中的记录进行更新操作。

内容解释：

这两段DCL代码示例展示了如何在数据库管理系统中使用数据控制语言进行数据结构的优化和权限的管理。第一段代码通过为 items 表上的 iprice 列创建索引来优化基于价格的查询性能。第二段代码通过授予用户 john 对 stocks 表的更新权限，允许他修改表中的记录。这两种操作都是数据库维护和安全管理中的常见任务。

Stored Functions 储存函数

使用了PL/pgSQL（PostgreSQL的过程语言）来创建这个函数。

CREATE OR REPLACE FUNCTION myage(dob DATE)
RETURNS INTEGER AS $$
BEGIN
    RETURN DATE_PART('year', current_date) - DATE_PART('year', dob)::INTEGER;
END; $$
LANGUAGE PLPGSQL;

逐行解释:

1. CREATE OR REPLACE FUNCTION myage(dob DATE)：这行代码开始定义一个名为 myage 的函数，该函数接受一个参数 dob（出生日期），类型为 DATE。

2. RETURNS INTEGER：指定函数返回值的类型为 INTEGER，在这个场景中，返回值表示一个人的年龄。

3. AS $$：标记函数体的开始，$$ 是一个PostgreSQL中用于表示字符串常量开始和结束的定界符，常用于函数定义中。

4. BEGIN：PL/pgSQL函数体的开始。

5. RETURN DATE_PART('year', current_date) - DATE_PART('year', dob)::INTEGER;：这行是函数的核心逻辑。DATE_PART('year', current_date) 获取当前日期的年份，DATE_PART('year', dob) 获取出生日期的年份。然后计算两者之差，得到年龄。::INTEGER 确保结果是整数类型。

6. END; $$：标记函数体的结束。

7. LANGUAGE PLPGSQL;：指定函数使用的语言为PL/pgSQL。

功能解释:

这个 myage 函数用于计算基于给定的出生日期 dob 的年龄。它通过从当前年份中减去出生年份来实现。这个函数可以被用于数据库查询中，当需要根据存储在数据库中的出生日期计算年龄时，非常方便。例如，你可以在SELECT语句中使用这个函数来检索所有人的年龄。

Triggers 触发器

创建了一个表和一个触发器函数，用于记录数据库表更改的日志。

CREATE TABLE IF NOT EXISTS log (
    ltimestamp TIMESTAMPTZ NOT NULL,
    laction VARCHAR(10) NOT NULL,
    ltable VARCHAR(100) NOT NULL,
    lnew TEXT
);

逐行解释:

1. CREATE TABLE IF NOT EXISTS log：这行代码检查 log 表是否存在，如果不存在，则创建它。这是一种防止因表已存在而导致的创建错误的常见做法。

2. ltimestamp TIMESTAMPTZ NOT NULL：定义了一个名为 ltimestamp 的列，数据类型为 TIMESTAMPTZ（带时区的时间戳），用于记录日志条目的时间。这个列被设置为 NOT NULL，意味着每个日志条目都必须有一个时间戳。

3. laction VARCHAR(10) NOT NULL：定义了一个名为 laction 的列，数据类型为 VARCHAR(10)，用于存储执行的操作类型（如 INSERT、UPDATE 等）。这个列也被设置为 NOT NULL，意味着每个日志条目都需要记录操作类型。

4. ltable VARCHAR(100) NOT NULL：定义了一个名为 ltable 的列，数据类型为 VARCHAR(100)，用于存储被操作的表的名称。这个列同样被设置为 NOT NULL。

5. lnew TEXT：定义了一个名为 lnew 的列，数据类型为 TEXT，用于存储操作后的新数据（可能是一行数据的JSON表示或其他格式）。对于 DELETE 操作，这个列可能为空。

功能解释:

这个 log 表被设计用来记录数据库中表的变更日志。每个日志条目包含了变更发生的时间戳、执行的操作类型、被操作的表的名称以及操作后的新数据。这样的设计使得追踪和审计数据库变更变得简单高效。

要完成记录日志的功能，通常还需要定义一个或多个触发器以及相应的触发器函数，这些触发器会在指定表上的 INSERT、UPDATE 或 DELETE 操作执行时被触发，并执行记录日志的操作。不过，这段代码只包含了创建 log 表的部分，没有包括触发器函数和触发器的创建。

扩展1

CREATE OR REPLACE FUNCTION logchange()
RETURNS TRIGGER AS $$
DECLARE
    action_text TEXT;
BEGIN
    IF TG_OP = 'INSERT' THEN
        action_text := 'INSERT';
    ELSIF TG_OP = 'DELETE' THEN
        action_text := 'DELETE';
        NEW := OLD;
    ELSE
        RETURN NEW;
    END IF;
    INSERT INTO log
    VALUES (current_timestamp, action_text, TG_TABLE_NAME, NEW::TEXT);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

逐行解释:

1. CREATE OR REPLACE FUNCTION logchange()：这行代码开始定义一个名为 logchange 的函数，如果函数已存在，则会替换它。

2. RETURNS TRIGGER：指定函数返回类型为 TRIGGER，意味着这个函数将作为触发器函数使用。

3-4. DECLARE action_text TEXT;：声明一个局部变量 action_text 用于存储操作类型（INSERT 或 DELETE）。

1. BEGIN：标记函数主体的开始。

6-13. IF TG_OP = 'INSERT' THEN ... END IF;：使用 TG_OP 系统变量检查触发器被激活的操作类型。如果是 INSERT，则将 action_text 设置为 'INSERT'。如果是 DELETE，则将 action_text 设置为 'DELETE' 并将 NEW 设置为 OLD 的值（这在 DELETE 操作中有特殊用途，如记录被删除的数据）。如果是其他类型的操作（如 UPDATE），则直接返回 NEW。

14-15. INSERT INTO log ...：向 log 表插入一条新记录，记录当前的时间戳、操作类型、被操作的表名以及操作后的数据状态。

1. RETURN NEW;：返回 NEW 记录，这在 INSERT 和 UPDATE 触发器中是必要的。

2. END;：标记函数主体的结束。

3. $$ LANGUAGE plpgsql;：函数定义的结束，指定使用的编程语言为 plpgsql。

功能解释:

logchange() 函数设计用于作为触发器函数，记录 INSERT 和 DELETE 操作。对于 INSERT 操作，它记录插入的新记录；对于 DELETE 操作，它通过将 NEW 设置为 OLD 来记录删除的记录信息（尽管这个赋值在这个上下文中不是标准做法，且可能不起作用，因为在 DELETE 操作中 NEW 记录通常是不可用的，应直接使用 OLD）。

要使这个函数生效，还需要为特定的表创建触发器，并指定触发器在哪些操作（INSERT、DELETE、UPDATE）上激活，以及触发器调用的是哪个函数。

扩展2

创建了一个触发器，用于在对 stocks 表进行 INSERT 或 DELETE 操作后调用之前定义的触发器函数 logchange()。

CREATE OR REPLACE TRIGGER triglogstock
AFTER INSERT OR DELETE ON stocks
FOR EACH ROW
EXECUTE FUNCTION log_change();

逐行解释:

1. CREATE OR REPLACE TRIGGER triglogstock：这行代码开始定义一个名为 triglogstock 的触发器，如果该触发器已存在，则会被替换。

2. AFTER INSERT OR DELETE ON stocks：指定触发器的激活时机和条件。在这种情况下，触发器会在对 stocks 表执行 INSERT 或 DELETE 操作之后激活。

3. FOR EACH ROW：指定触发器是逐行触发的。这意味着对 stocks 表中的每一行执行 INSERT 或 DELETE 操作时，触发器都会被触发一次。

4. EXECUTE FUNCTION log_change();：定义当触发器被激活时要执行的动作，即调用 log_change() 函数。这个函数将记录每个操作的详细信息，包括操作类型、操作发生的时间、以及相关的行数据。

功能解释:

这个触发器 triglogstock 旨在自动记录对 stocks 表进行的每次 INSERT 和 DELETE 操作。通过为每个操作调用 log_change() 函数，它将操作的详细信息记录到日志表中。这对于数据变更追踪、审计或回溯分析非常有用。

需要注意的是，触发器的执行并不会影响原始操作的结果；它仅仅在操作完成后记录相关信息。这样，即使日志记录失败，也不会影响到原始的 INSERT 或 DELETE 操作。

创建表

Warehouses 仓库

仓库有唯一的标识符、名称和由街道、城市和国家定义的位置。

CREATE TABLE warehouses (
    w_id INTEGER PRIMARY KEY,
    w_name VARCHAR(16) NOT NULL,
    w_street VARCHAR(32) NOT NULL,
    w_city VARCHAR(32) NOT NULL,
    w_country CHAR(16) NOT NULL
);

INSERT INTO warehouses
(w_id, w_name, w_street, w_city, w_country)
VALUES
(301, 'Schmedeman', 'Sunbrook', 'Singapore', 'Singapore');

逐行解释:

1. CREATE TABLE warehouses：开始创建一个名为 warehouses 的新表。

2. w_id INTEGER PRIMARY KEY：定义了一个名为 w_id 的列，数据类型为 INTEGER，并将其设置为表的主键。主键是表中每行唯一标识的列，这意味着 w_id 的每个值都必须是唯一的。

3. w_name VARCHAR(16) NOT NULL：定义了一个名为 w_name 的列，数据类型为 VARCHAR(16)，用于存储仓库的名称。NOT NULL 约束表示这个列的值在每行中都必须有一个非空值。

4. INSERT INTO warehouses (w_id, w_name, w_street, w_city, w_country)：这行代码开始了一个插入操作，指定了要插入数据的表 warehouses 以及目标列 w_id、w_name、w_street、w_city、w_country。

5. VALUES (301, 'Schmedeman', 'Sunbrook', 'Singapore', 'Singapore')：这行指定了上述列对应的值。

功能解释:

这段代码的目的是在数据库中创建一个用于跟踪仓库信息的 warehouses 表，并向表中插入一个示例仓库的信息，包括唯一标识符、名称、街道、城市和国家。这样的设计可以帮助管理仓库的物理位置信息。

Items 项目 

项目有唯一标识符、唯一图像标识符、名称和价格。

CREATE TABLE items (
    i_id INTEGER PRIMARY KEY,
    i_im_id CHAR(8) UNIQUE NOT NULL,
    i_name VARCHAR(64) NOT NULL,
    i_price NUMERIC NOT NULL CHECK(i_price > 0)
);
INSERT INTO items
(i_id, i_im_id, i_name, i_price)
VALUES
(1, '35356226', 'Indapamide', 95.23);

iprice NUMERIC NOT NULL CHECK(i_price > 0)：定义了一个名为 i_price 的列，数据类型为 NUMERIC，用于存储物品的价格。NOT NULL 约束确保每个物品都有一个价格，而 CHECK(i_price > 0) 约束确保这个价格是一个正数。

这段代码的目的是在数据库中创建一个用于跟踪物品信息的 items 表，并向表中插入一个示例物品的信息，包括唯一标识符、唯一图像标识符、名称和价格。这样的设计可以帮助管理物品的详细信息，包括跟踪每个物品的图像和销售价格。

Stocks 仓库

我们会记录每个仓库的库存数量。如果某个物品 则没有这一对的记录。数量 总是等于或大于 1。

CREATE TABLE stocks (
    w_id INTEGER REFERENCES warehouses(w_id),
    i_id INTEGER REFERENCES items(i_id),
    s_qty SMALLINT CHECK(s_qty > 0),
    PRIMARY KEY (w_id, i_id)
);
INSERT INTO stocks VALUES (301, 1, 338);
INSERT INTO stocks VALUES (301, 1, 12);
INSERT INTO stocks VALUES (301, 4, 938);

逐行解释:

1. CREATE TABLE stocks：这行代码开始创建一个名为 stocks 的新表。

2. w_id INTEGER REFERENCES warehouses(wid)：定义了一个名为 w_id 的列，数据类型为 INTEGER。该列通过 REFERENCES 约束与 warehouses 表的 wid 列建立了外键关系，表示仓库的唯一标识符。

3. i_id INTEGER REFERENCES items(i_id)：定义了一个名为 i_id 的列，数据类型为 INTEGER。该列通过 REFERENCES 约束与 items 表的 i_id 列建立了外键关系，表示物品的唯一标识符。

4. s_qty SMALLINT CHECK(sqty > 0)：定义了一个名为 s_qty 的列，数据类型为 SMALLINT。CHECK(s_qty > 0) 约束确保记录的库存量总是大于 0。

5. PRIMARY KEY (w_id, i_id)：设置 w_id 和 i_id 列的组合为表的主键，确保每个仓库中的每种物品都有唯一的库存记录。

功能解释:

这段代码的目的是在数据库中创建一个 stocks 表来跟踪每个仓库中各个物品的库存量。通过将 w_id 和 i_id 设置为主键，确保了每个仓库中的每种物品只能有一个唯一的库存记录。注意，第二条插入语句可能会因为主键约束而导致插入失败，因为它试图为同一个仓库和物品对插入第二个不同的库存记录。在实际使用中，可能需要更新现有记录而不是尝试插入一个新的具有相同 w_id 和 i_id 的记录。

完整性约束（Integrity Constraints）

PRIMARY KEY（主键约束）

• 作用：确保表中每行的唯一性。
• 特点：一个表中只能有一个主键，主键列不能有 NULL 值，每行的主键值必须唯一。
• 示例：CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(100));

UNIQUE（唯一约束）

• 作用：确保一列或列组合的值在表中是唯一的，但可以有 NULL 值。
• 特点：一个表中可以有多个 UNIQUE 约束。
• 示例：CREATE TABLE employees (emp_id INT, email VARCHAR(255) UNIQUE);

NOT NULL（非空约束）

• 作用：确保列中的值不能为 NULL。
• 特点：用于保证某些重要字段（如姓名、电子邮件等）在记录中必须有有效值。
• 示例：CREATE TABLE users (username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL);

FOREIGN KEY（外键约束）

• 作用：确保一个表中的数据匹配另一个表中的值，用于维护跨表的数据一致性和实现关系数据库中的关系。
• 特点：可以有多个 FOREIGN KEY 约束，用于实现表之间的引用关系。
• 示例：CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id));

CHECK（检查约束）

• 作用：确保列中的值满足特定条件。
• 特点：可以定义在列级或表级，用于限制列中值的范围。
• 示例：CREATE TABLE products (product_id INT PRIMARY KEY, price DECIMAL CHECK (price > 0));

Table CHECK 约束和 ASSERTION

• Table CHECK 约束：允许在表级别定义更复杂的约束，涉及多个列。
• ASSERTION 约束：定义跨多个表的约束条件，但请注意，当前大多数数据库管理系统（DBMS）并不支持 ASSERTION 约束。

虽然 CHECK 约束和表级 CHECK 约束在许多数据库系统中都是可用的，ASSERTION 约束却往往不被支持。数据库系统的支持情况可能会有所不同，因此在使用这些约束时最好查阅特定数据库系统的文档。

查询表-- Query Table

点查询（Point Query）

点查询是一种根据等值条件返回最多一个记录的查询。在给定的示例中，查询旨在基于特定的等值条件从 warehouses 表中检索仓库名称。

SELECT w.wname
FROM warehouses w
WHERE w.wid = 123;

这个查询检索 warehouses 表中 wid 为123的仓库的名称。由于 wid 是唯一的（通常作为主键或设置了唯一约束），这个查询要么返回单一的仓库名称（如果存在 wid 为123的记录），要么不返回任何结果（如果不存在这样的记录）。这种类型的查询在需要基于唯一键值快速检索特定记录时非常有用。

多点查询（Multipoint Query）

多点查询是一种根据等值条件返回多条记录的查询。在给定的示例中，查询的目的是从 warehouses 表中基于城市名称等值条件检索多个仓库名称。

SELECT w.wname
FROM warehouses w
WHERE w.wcity = 'Singapore';

这个查询检索所有位于 'Singapore' 城市的仓库的名称。由于城市名称不是一个唯一标识符，可能有多个仓库位于同一个城市，因此这个查询可能返回多条记录，每条记录代表一个位于 'Singapore' 的仓库。这种类型的查询在需要根据非唯一键值检索相关记录集合时非常有用，例如，当需要列出某个特定地区或城市的所有仓库时。

范围查询（Range Query）

范围查询是一种基于某个属性的不等式条件返回多条记录的查询。这里提供了两个示例，都旨在从 stocks 表中检索库存量符合特定范围的物品ID。

SELECT s.iid
FROM stocks s
WHERE s.sqty BETWEEN 0 AND 10;

SELECT s.iid
FROM stocks s
WHERE s.sqty <= 10;

这两个范围查询示例都用于检索库存量在特定范围内的物品ID。第一个查询使用 BETWEEN 操作符定义了一个闭合范围（包括边界值0和10），而第二个查询使用小于或等于（<=）操作符来定义范围（小于或等于10）。这两种查询在需要基于数量或其他数值属性检索记录时非常有用，例如，当需要找出库存紧张（低库存量）的物品时。

前缀匹配查询（Prefix Match Query）

前缀匹配查询是一种基于某些属性的前缀条件返回多条记录的查询。在给定的示例中，查询的目的是检索 warehouses 表中城市名称以特定前缀开头的所有记录。

SELECT w.w_city, w.wname
FROM warehouses w
WHERE w.w_city LIKE 'Si%';

这个查询用于检索所有城市名称以 'Si' 开头的仓库的城市名称和仓库名称。这种类型的查询在需要基于文本模式（如前缀、后缀或特定模式）检索记录时非常有用，例如，当需要列出某个特定前缀开头的所有城市中的仓库时。前缀匹配查询在处理自动补全或搜索建议等功能时特别有用，因为它们可以快速过滤出与用户输入匹配的记录集。

极值查询（Extremal Query）

极值查询是一种根据最大值或最小值的比较条件返回多条记录的查询。在给定的示例中，查询的目的是找出库存数量等于所有库存记录中最大库存数量的物品ID。

SELECT s1.iid
FROM stocks s1
WHERE s1.sqty = ALL (
    SELECT MAX(s2.sqty)
    FROM stocks s2
);

这个查询用于找出其库存数量等于所有库存记录中最大库存数量的物品ID。这可能包括一个或多个物品，如果有多个物品的库存数量恰好等于最大库存数量。这种类型的查询在需要基于某个度量的极值（如最大值、最小值）检索记录时非常有用，例如，当需要找出库存最充足的物品时。

排序查询（Ordering Query）

排序查询是一种返回记录集，其中记录按照指定的顺序排列的查询。

SELECT w.wid, w.wname, w.wcity
FROM warehouses w
ORDER BY w.wname;

SELECT w.wid, w.wname, w.wcity
FROM warehouses w
ORDER BY w.wcity, w.wname;

SELECT w.wid, w.wname
FROM warehouses w
ORDER BY w.wcity;

这些排序查询用于根据一个或多个列的值对结果集进行排序。排序可以基于单个列，也可以是多个列的组合，对于每个额外的排序列，它仅在前一个列的值相等时才会影响排序结果。排序查询在需要按特定顺序展示数据时非常有用，比如在报告生成、用户界面展示或者数据分析时。

分组和聚合查询（Grouping and Aggregate Query）

分组和聚合查询通过将记录划分为多个组来执行，并且通常与聚合函数（如 MAX、MIN、AVG、SUM 等）结合使用，以对每个组执行汇总计算。

SELECT s.iid
FROM stocks s
GROUP BY s.iid;

SELECT s.iid, FLOOR(AVG(s.sqty)) AS averageqty
FROM stocks s
GROUP BY s.iid;

SELECT s.iid, FLOOR(AVG(s.sqty)) AS averageqty
FROM stocks s
GROUP BY s.iid, s.wid;

分组和聚合查询用于对数据集进行分析，以便按照一定的分类键（如物品ID或仓库ID）将记录划分为组，并对每个组应用一个或多个聚合函数以提取汇总信息（如计数、平均值、最大值、最小值等）。这些查询在数据分析、报表生成以及对数据集进行总结和理解方面非常有用。

聚合函数的条件

这些条件必须出现在 HAVING 子句中。HAVING 子句用于指定筛选分组后结果的条件，与 WHERE 子句的区别在于 HAVING 允许使用聚合函数。

SELECT s.wid
FROM stocks s
GROUP BY s.wid
HAVING AVG(s.sqty) >= 550;

这个查询用于找出平均库存量大于或等于550的仓库。首先，它将 stocks 表中的记录按仓库ID (wid) 进行分组，然后计算每个仓库的平均库存量，最后选择那些平均库存量满足条件的仓库。这种类型的查询在需要根据聚合数据（如平均值、总和、最大值等）筛选分组时非常有用，例如，在库存管理中找出库存水平较高的仓库。

联接查询（Join Query）

联接查询用于结合两个或多个表的数据。这里提供了三种形式的联接查询，每种都旨在根据特定条件结合 stocks 表和 warehouses 表的数据。

SELECT s.iid
FROM stocks s, warehouses w
WHERE s.wid = w.wid
AND w.wcity = 'Singapore';

• 这个查询通过在 FROM 子句中列出表并在 WHERE 子句中指定联接条件来执行隐式联接。s.wid = w.wid 确保只有当 stocks 表中的 wid 与 warehouses 表中的 wid 相匹配时，记录才会被包括在结果中。
• w.wcity = 'Singapore' 进一步限制了结果只包括 wcity 为 'Singapore' 的记录。

SELECT s.iid
FROM stocks s CROSS JOIN warehouses w
WHERE s.wid = w.wid
AND w.wcity = 'Singapore';

• CROSS JOIN 生成所有可能的记录对组合，但实际结果集是通过 WHERE 子句过滤得到的，与示例1类似。尽管使用了 CROSS JOIN 关键字，但实际上是通过 WHERE 子句中的条件来实现等值联接的。
• 这种写法通常不推荐，因为它可能会导致大量不必要的组合，特别是在没有适当 WHERE 条件限制的情况下。

SELECT s.iid
FROM stocks s JOIN warehouses w
ON s.wid = w.wid
WHERE w.wcity = 'Singapore';

• 这个查询使用显式的 JOIN 语法，并通过 ON 子句指定联接条件。这是推荐的方法，因为它明确了联接的意图，并使查询更易于理解。
• ON s.wid = w.wid 指定联接条件，而 WHERE w.wcity = 'Singapore' 作为筛选条件，限制结果为那些位于 'Singapore' 的记录。

消除重复

在某些情况下，我们可能需要从查询结果中消除重复的记录。这可以通过使用 DISTINCT 关键字或 UNION 操作来实现。

SELECT DISTINCT s.iid
FROM stocks s, warehouses w
WHERE s.wid = w.wid
AND w.wcity = 'Singapore';

SELECT DISTINCT s.iid：这行代码选择了 stocks 表中的 iid（物品的唯一标识符），并通过添加 DISTINCT 关键字来确保结果集中不包含重复的 iid 值。

SELECT s.iid
FROM stocks s, warehouses w
WHERE s.wid = w.wid
AND w.wcity = 'Singapore'
UNION
SELECT s.iid
FROM stocks s, warehouses w
WHERE s.wid = w.wid
AND w.wcity = 'Singapore';

• UNION 操作用于组合两个或多个 SELECT 语句的结果集，并默认去除重复的记录。在这个示例中，虽然两个 SELECT 语句实际上是相同的，但 UNION 会确保合并后的结果集中不包含重复项。
• 注意：在实际应用中，使用两个完全相同的 SELECT 语句与 UNION 并不常见，因为这不增加任何额外的价值。这里的示例主要是为了演示 UNION 的去重功能。

这两种方法都用于消除查询结果中的重复项。使用 DISTINCT 是一种更直接的方法，适用于从单个查询结果中消除重复项。而 UNION 不仅能消除重复项，还能合并多个查询的结果，但在这个特定的示例中，由于两个 SELECT 语句相同，使用 UNION 并没有提供额外的好处。在需要从多个不同的查询结果中获取唯一记录时，UNION 是一个很好的选择。

自然连接（Natural Join）、等值连接（Equi-Join）和内连接（Inner Join）

自然连接，等值连接和内连接是结合多个表中数据的常见SQL查询类型。这些连接类型基于表之间的相等条件来合并记录。

SELECT s.iid
FROM stocks s NATURAL JOIN warehouses w
WHERE w.wcity = 'Singapore';

NATURAL JOIN 自动基于两个表中具有相同名称的列进行连接。在这个例子中，假设 stocks 表和 warehouses 表都有一个名为 wid 的列，NATURAL JOIN 将基于这个共同的列进行连接。

SELECT s.iid
FROM stocks s INNER JOIN warehouses w ON s.wid = w.wid
WHERE w.wcity = 'Singapore';

• INNER JOIN 明确指定了连接条件，即 s.wid = w.wid，这表示 stocks 表中的 wid 必须与 warehouses 表中的 wid 相等。
• 这个查询的作用与自然连接示例相同，但通过显式指定连接条件，提供了更高的灵活性和控制。

这两个示例都演示了如何连接 stocks 表和 warehouses 表来找出特定条件下的记录。自然连接通过隐式使用共有的列名来简化查询，而内连接则需要显式指定连接条件，这在需要精确控制连接逻辑时非常有用。在实际应用中，选择哪种类型的连接取决于具体情况和个人偏好，但内连接（使用 ON 子句）因其明确性和灵活性而更常被推荐使用。

外连接查询（Outer Join Query）

外连接查询包括左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。它们用于组合多个表中的数据，并且当其中一个表中没有匹配的行时，会用 NULL 填充缺失的值。

SELECT i.iid, s.wid
FROM items i LEFT OUTER JOIN stocks s
ON i.iid = s.iid;

• 这个查询使用左外连接将 items 表和 stocks 表连接起来。它返回 items 表的所有记录，以及与之匹配的 stocks 表中的记录。如果 items 表中的某条记录在 stocks 表中没有匹配项，则对应的 stocks 表中的列（在这个例子中是 s.wid）将显示为 NULL。
• 这种查询通常用于查找有或没有库存信息的所有物品。
• items 全部显示，items在stocks中没有匹配则显示null （# items <= # stocks）

SELECT i.iid, s.wid
FROM stocks s RIGHT OUTER JOIN items i
ON i.iid = s.iid;

• 这个查询使用右外连接将 stocks 表和 items 表连接起来。它返回 items 表的所有记录，以及与之匹配的 stocks 表中的记录。与左外连接相反，如果 items 表中的某条记录在 stocks 表中没有匹配项，则 stocks 表中的列将显示为 NULL。
• 这种查询用途与左外连接类似，但是从不同的方向进行，通常取决于查询的特定需求。

SELECT i.iid, s.wid
FROM stocks s FULL OUTER JOIN items i
ON i.iid = s.iid;

• 这个查询使用全外连接将 stocks 表和 items 表连接起来。它返回 stocks 表和 items 表的所有记录。如果某条记录在另一表中没有匹配项，则该记录的缺失值将显示为 NULL。
• 全外连接用于当你需要从两个表中获取所有记录时，无论它们之间是否存在匹配。

嵌套查询（Nested Query）

SELECT

嵌套查询涉及在外部查询的 SELECT 子句中使用子查询（内部查询）的结果。子查询的结果用于外部查询的计算或条件判断中。

SELECT (
    SELECT s.iid
    FROM stocks s
    WHERE s.wid = w.wid
    AND w.wcity = 'Singapore'
    AND s.iid = s1.iid
)
FROM warehouses w, stocks s1
WHERE w.wcity = 'Singapore'
AND s1.wid = w.wid;

逐行解释:

1-5. 子查询：

• SELECT s.iid FROM stocks s WHERE s.wid = w.wid AND w.wcity = 'Singapore' AND s.iid = s1.iid：这是一个子查询，它从 stocks 表中选择 iid（物品ID），条件是 stocks 表中的 wid 必须等于 warehouses 表中的 wid，且 warehouses 表中的 wcity 必须为 'Singapore'，同时 stocks 表中的 iid 必须等于 s1.iid（s1 是 stocks 表的别名）。

6-8. 外部查询：

• FROM warehouses w, stocks s1：外部查询从 warehouses 表（别名 w）和 stocks 表（别名 s1）中选择数据。
• WHERE w.wcity = 'Singapore' AND s1.wid = w.wid：外部查询的条件是 warehouses 表中的 wcity 必须为 'Singapore'，并且 stocks 表中的 wid 必须等于 warehouses 表中的 wid。

功能解释:

这个嵌套查询的目的是从 warehouses 表和 stocks 表中找出所有位于 'Singapore' 的仓库对应的物品ID。子查询为每个满足条件的 stocks 记录找出相应的 iid，而外部查询则基于 warehouses 表和 stocks 表的 wid 匹配以及 warehouses 表的城市条件来确定哪些记录参与计算。这种查询类型在需要基于另一个查询结果进行过滤或计算时非常有用。

FROM

嵌套查询（Nested Query）是一种在外部查询的 FROM 子句中使用子查询（内部查询）的结果的查询。这种查询通常用于将子查询的结果作为临时表来参与外部查询的计算。

SELECT s.iid
FROM stocks s, (
    SELECT w.wid
    FROM warehouses w
    WHERE w.wcity = 'Singapore'
) AS w1
WHERE s.wid = w1.wid;

逐行解释:

1. SELECT s.iid：这一行指定了外部查询的目标列，即 iid（物品的唯一标识符）。这里，s 是 stocks 表的别名。

2-5. 子查询：

• FROM stocks s, (SELECT w.wid FROM warehouses w WHERE w.wcity = 'Singapore') AS w1：这部分定义了一个子查询，子查询从 warehouses 表中选择 wid（仓库ID），条件是 wcity（仓库所在城市）为 'Singapore'。子查询的结果被视为一个临时表，别名为 w1。

1. WHERE s.wid = w1.wid：这一行定义了外部查询的条件，即 stocks 表中的 wid 必须与子查询 w1 中的 wid 相匹配。

功能解释:

这个嵌套查询的目的是找出所有在 'Singapore' 城市的仓库中有库存的物品ID。子查询首先找出所有位于 'Singapore' 的仓库ID，然后这些仓库ID被用于外部查询，以确定在这些仓库中有库存的物品。这种类型的查询在需要基于一个查询结果对另一个表进行筛选时非常有用。通过将子查询结果作为临时表，可以灵活地组合和重用查询逻辑。

WHERE

嵌套查询（Nested Query）在外部查询的 WHERE 子句中使用子查询（内部查询）的结果。这种类型的查询通常用于基于子查询结果来过滤外部查询的记录。

SELECT s.iid
FROM stocks s
WHERE s.wid IN (
    SELECT w.wid
    FROM warehouses w
    WHERE w.wcity = 'Singapore'
);

WHERE s.wid IN (SELECT w.wid FROM warehouses w WHERE w.wcity = 'Singapore')：这行代码定义了一个子查询，用于从 warehouses 表中选择所有 wcity 为 'Singapore' 的 wid。外部查询然后选择那些 wid 在子查询结果集中的 stocks 记录。

SELECT s.iid
FROM stocks s
WHERE s.wid = ANY (
    SELECT w.wid
    FROM warehouses w
    WHERE w.wcity = 'Singapore'
);

WHERE s.wid = ANY (SELECT w.wid FROM warehouses w WHERE w.wcity = 'Singapore')：这行代码的子查询与第一个示例相同，但这里使用了 = ANY 而不是 IN。ANY 操作符允许 s.wid 等于子查询返回的任何一个 wid 值。

HAVING

嵌套查询在外部查询的 HAVING 子句中使用了一个子查询的结果。HAVING 子句用于对分组后的结果进行筛选，特别是当筛选条件涉及聚合函数时。不过，在此示例中，HAVING 子句用于基于子查询的结果过滤分组，而非直接基于聚合结果。

SELECT s.iid
FROM stocks s
GROUP BY s.wid, s.iid
HAVING s.wid IN (
    SELECT w.wid
    FROM warehouses w
    WHERE w.wcity = 'Singapore'
);

• HAVING s.wid IN (...)：这行代码使用 IN 操作符来筛选那些其 wid 出现在子查询结果中的分组。
• 子查询 SELECT w.wid FROM warehouses w WHERE w.wcity = 'Singapore'：这个子查询从 warehouses 表中选择所有位于 'Singapore' 的仓库的 wid。

功能解释:

这个嵌套查询的目的是选择那些位于 'Singapore' 的仓库中的物品ID。通过分组 stocks 表中的记录，并使用子查询在 HAVING 子句中筛选出位于 'Singapore' 的仓库，可以实现这一目的。这种查询特别适用于当你需要对聚合或分组后的数据进行筛选，且筛选条件依赖于其他表中的数据时。在这个示例中，尽管没有直接使用聚合函数，HAVING 子句依然非常适用于基于子查询结果对分组进行过滤。

关联嵌套查询（Correlated Nested Query）

关联嵌套查询是一种在外部查询的 WHERE 或 HAVING 子句中使用子查询的查询，其中子查询引用了外部查询 FROM 子句中关系的属性。这种查询的关键特点是子查询依赖于外部查询中的每一行。

SELECT s.iid
FROM stocks s
WHERE EXISTS (
    SELECT *
    FROM warehouses w
    WHERE s.wid = w.wid
    AND w.wcity = 'Singapore'
);

• WHERE EXISTS (...)：这行代码使用 EXISTS 操作符，它检查子查询是否返回任何行。如果子查询返回至少一行，那么 EXISTS 表达式的结果为真，外部查询的当前行将被包含在结果集中。
• 子查询 SELECT * FROM warehouses w WHERE s.wid = w.wid AND w.wcity = 'Singapore'：子查询从 warehouses 表中选择所有记录，条件是 warehouses 表中的 wid 必须与外部查询中当前行的 wid 相等，并且 wcity 必须为 'Singapore'。这里的 s.wid = w.wid 是关联条件，使得子查询依赖于外部查询的每一行。

功能解释:

这个关联嵌套查询的目的是从 stocks 表中选择那些对应的仓库位于 'Singapore' 的物品ID。通过在子查询中引用外部查询的 stocks 表的列（s.wid），子查询为外部查询中的每一行 stocks 记录动态地检查是否存在匹配的 warehouses 记录。如果存在至少一个满足条件的 warehouses 记录，那么对应的 stocks 记录的 iid 将被选取。

这种查询类型特别适用于当你需要根据另一表中的数据存在性来过滤记录时。在这个示例中，它用于找出那些其仓库位于特定城市（'Singapore'）的物品。

在SQL中，使用否定操作符，如 NOT IN、<> ALL、NOT EXISTS 和 EXCEPT（或在某些数据库系统中称为 MINUS），可以显著增加SQL查询的表达能力。这些操作符允许构建更复杂的查询逻辑，尤其是在需要排除特定记录或满足某些条件的记录时。以下是这些操作符的简要说明：

NOT IN

• 用途：用于检查一个值不包含在一组值之内。
• 示例：
• SELECT *
• FROM table1
• WHERE column NOT IN (SELECT column FROM table2);
• 在这个示例中，查询返回 table1 中那些其列值不在 table2 中相应列值集合中的所有记录。

<> ALL

• 用途：确保某个列的值不等于子查询返回的所有值。
• 示例：
• SELECT *
• FROM table1
• WHERE column <> ALL (SELECT column FROM table2);
• 查询返回 table1 中那些其列值与 table2 中所有列值都不相等的记录。

NOT EXISTS

• 用途：用于检查子查询是否不返回任何行。
• 示例：
• SELECT *
• FROM table1 t1
• WHERE NOT EXISTS (SELECT * FROM table2 t2 WHERE t1.id = t2.id);
• 查询返回 table1 中那些在 table2 中没有匹配项的记录。

EXCEPT 或 MINUS

• 用途：返回第一个查询的结果，排除那些也出现在第二个查询结果中的行。
• 示例：
• SELECT column
• FROM table1 EXCEPT
• SELECT column
• FROM table2;
• 查询返回 table1 中那些不在 table2 中出现的列值。

嵌套查询允许一个查询内部包含另一个查询，理论上嵌套层数没有限制，但实际上某些数据库系统可能会有限制。

SELECT i.iid
FROM items i
WHERE NOT EXISTS (
    SELECT *
    FROM warehouses w
    WHERE NOT EXISTS (
        SELECT *
        FROM stocks s
        WHERE s.wid = w.wid AND s.iid = i.iid
    )
);

3-9. 关联嵌套子查询：

• WHERE NOT EXISTS (...)：这行代码使用 NOT EXISTS 操作符，它检查子查询是否不返回任何行。如果子查询不返回行，那么 NOT EXISTS 表达式的结果为真，外部查询的当前行将被包含在结果集中。
• 子查询 SELECT * FROM warehouses w WHERE NOT EXISTS (...)：这个子查询从 warehouses 表中选择所有记录，条件是另一个嵌套子查询不返回行。
• 嵌套子查询 SELECT * FROM stocks s WHERE s.wid = w.wid AND s.iid = i.iid：这个子查询从 stocks 表中选择所有记录，条件是 stocks 表中的 wid 必须等于 warehouses 表中的 wid，并且 stocks 表中的 iid 必须等于 items 表中的 iid。

功能解释:

这个查询的目的是从 items 表中选择那些没有在任何 warehouses 中的 stocks 记录的物品ID。换句话说，它查找那些在所有仓库中都没有库存的物品。这通过检查是否存在没有与 items 表中相应 iid 匹配的 stocks 记录的 warehouses 来实现。如果对于某个 items 表中的 iid，所有的 warehouses 都没有对应的库存记录，则该 iid 会被选取。

这种查询类型特别适用于当你需要根据另一表中数据的缺失来过滤记录时。在这个示例中，它用于找出那些在所有仓库中都没有库存的物品。