SQL编程-EW帮帮网

数据库管理系统

Selecting literals

SQL 中 用 SELECT 直接选取字面值（literals）并用 AS 起列别名，然后用 UNION 把多条 SELECT 的结果合并成一个表。具体解析如下：

基本语法
- SELECT [表达式] AS [列名], [表达式] AS [列名]
  这里 [表达式] 可以是字面值（如字符串 "daisy"）、列名或计算式。
- 如果你直接选字面值，不是从已有表里取数据，那么结果就是**一行一列（或多列）**的临时表。
- 在这个场景里，expression 就是你“写进去”的值，而 AS name 就是告诉 SQL：把这个值放到结果表里并命名这一列为 name。

举个对应的直观解释：

SELECT "daisy" AS parent, "hank" AS child

也就是说：

"daisy" → 写入到 parent 列
"hank" → 写入到 child 列
如果后面有 UNION，就会把下一条 SELECT 的行追加到这个结果表中

不过要注意，它并不是向数据库的物理表“插入”数据（那是 INSERT 的工作），而只是生成一个临时查询结果集，查询结束它就不存在了。

例子含义

SELECT "daisy" AS parent, "hank" AS child UNION
SELECT "ace"  , "bella" UNION
SELECT "ace"  , "charlie" UNION
SELECT "finn" , "ace" UNION
SELECT "finn" , "dixie" UNION
SELECT "finn" , "ginger" UNION
SELECT "ellie", "finn";

每一条 SELECT 表示一个 (parent, child) 对，即“父节点”与“子节点”的关系。
UNION 会把多条查询结果合并成一个结果集，并去重。

核心要点
- Selecting literals：直接用字符串常量生成数据行，不依赖数据库已有表。
- AS 别名：给每列起一个名字，方便后续引用或理解。
- UNION：合并多条 SELECT 结果，最终得到一张完整的映射表。
- 这种技巧在构造小规模测试数据、临时映射表、或不依赖原有表的查询时很方便。

SQL的语法逻辑：

SQL 的执行的顺序：

FROM
- 先从 parents 这个表里取出所有数据（也就是它的全部行）。
WHERE
- 对每一行，判断 parent > child 是否为真（用字符串的字典序比较）。
- 只保留条件成立的行，其余的行被过滤掉。
SELECT
- 在保留下来的行里，只取 parent 这一列作为输出。
返回结果
- 最终得到一列（parent），每一行都是满足 parent > child 条件的值。

计算（Arithmetic）

1. 基本算术运算符

你可以直接在 SQL 查询中使用这些运算符：

+ 加法
- 减法
* 乘法
/ 除法（结果通常是浮点数）
% 取余（某些数据库用 MOD() 函数代替）

例子：

SELECT 3 + 5 AS sum, 10 - 4 AS difference, 6 * 7 AS product, 8 / 2 AS quotient, 10 % 3 AS remainder;

返回结果就是一行五列，分别显示加、减、乘、除、取余的结果。

2. 在表的列上运算

你可以对列的数据做运算，比如工资加奖金、价格打折等：

SELECT price, price * 0.9 AS discounted_price
FROM products;

这会返回 products 表中的 price 列，以及按 9 折计算的新列。

3. 数学函数

SQL 还提供了很多数学函数（不同数据库可能有差异）：

ABS(x) 绝对值
ROUND(x, n) 四舍五入到 n 位小数
CEIL(x) / FLOOR(x) 向上/向下取整
POWER(x, y) 幂运算
SQRT(x) 平方根

例子：

SELECT ABS(-5), ROUND(3.14159, 2), POWER(2, 3), SQRT(16);

4. 与其他子句配合

算术运算不仅能在 SELECT 里用，还能在 WHERE、ORDER BY 等地方用：

SELECT name, price, quantity, price * quantity AS total_cost
FROM orders
WHERE price * quantity > 100
ORDER BY total_cost DESC;

这会筛选总价大于 100 的订单，并按总价从高到低排序。

Union:

在关系代数里查询结果本质是一个（多重）集合，没有顺序的语义；
UNION（去重并集）只是把两边的行合到一起并做去重（通常内部会做排序或哈希以去重，但这不构成对外可依赖的顺序），所以如果你不写 ORDER BY，显示顺序就是“乱序/未定义”；

Join

SQL 中 隐式连接 (Implicit Join) 和 显式连接 (Explicit Join) 的写法区别：

连接的本质
- 一个 JOIN 操作会把两张（或更多）表的行匹配起来。
- 匹配的条件需要你指定，比如 child = name 表示把 parents 表里 child 列的值，和 dogs 表里 name 列的值相等的行配对。
- 额外条件（如 fur = "curly"）会进一步筛选结果。
隐式连接（Implicit Join）
```
SELECT parent
FROM parents, dogs
WHERE child = name
  AND fur = "curly";
```
- 语法：FROM 表1, 表2（用逗号分隔）
- 所有匹配条件都放在 WHERE 子句里。
- 缺点：多表连接时可读性差，不易区分匹配条件和过滤条件。
显式连接（Explicit Join）
```
SELECT parent
FROM parents
JOIN dogs ON child = name
WHERE fur = "curly";
```
- 语法：FROM 表1 JOIN 表2 ON 匹配条件
- 匹配条件紧跟 ON，过滤条件放在 WHERE。
- 优点：更清晰地区分连接条件和过滤条件，可读性更好，也更符合 SQL 标准。
总结
- 隐式：用逗号 + WHERE，匹配和过滤混在一起。
- 显式：用 JOIN ... ON ...，匹配条件和过滤条件分开。
- 现代 SQL 推荐用显式 JOIN，因为结构清晰、维护方便。

Sibling

这 SQL 自连接（self join），也就是把一张表当成两张表来用，然后在它们之间做匹配。

1. 场景

有一张 parents 表，记录 (parent, child) 关系，比如家谱、树结构中的父子节点。
目标是：找出所有兄弟姐妹的成对组合（同一个父亲/母亲，但名字不一样）。

2. 关键 SQL

SELECT a.child AS first, b.child AS second
FROM parents AS a, parents AS b
WHERE a.parent = b.parent 
  AND a.child < b.child;

解释：

FROM parents AS a, parents AS b
- 把同一张 parents 表当成两个表来看，起别名 a 和 b。
- 这样 a 表和 b 表的列可以区分开（a.child vs b.child）。
a.parent = b.parent
- 条件 1：父亲（parent）相同，表示他们是兄弟姐妹。
a.child < b.child
- 条件 2：用字典序（或数值）比较，让 first 永远比 second 小，这样避免重复（bella,charlie 和 charlie,bella 只保留一个）。
SELECT a.child AS first, b.child AS second
- 只输出兄弟姐妹的名字对，分别叫 first 和 second。

3. 执行逻辑

FROM 会生成 parents 和 parents 的笛卡尔积（所有组合）。
WHERE 会过滤，只留下同父母的不同孩子组合。
a.child < b.child 保证每对兄弟姐妹只出现一次，不会重复倒置。
最终输出兄弟姐妹的配对列表。

4. 图和结果表

右边树图是节点关系。
左下表格：

    first    second
    bella    charlie
    ace      daisy
    ace      ginger
    daisy    ginger

表示：

- `bella` 和 `charlie` 是兄弟姐妹（父母是 `ace`）。
    
- `ace`、`daisy`、`ginger` 是同一个父母 `finn` 的不同孩子，因此有多对组合。

5.例题

a 表：用来表示 祖父母 → 父母 这一跳
- a.parent 是祖父母
- a.child 是父母
b 表：用来表示 父母 → 孙子女 这一跳
- b.parent 是父母
- b.child 是孙子女

有了这个默认角色分工，WHERE 条件就必须把这两跳连起来：

a.child = b.parent

这样才能形成：

a.parent（祖父母） → a.child/b.parent（父母） → b.child（孙子女）

选项 3 正好符合这个逻辑；而选项 2 反过来匹配了 a.parent = b.child，就相当于把辈分链路倒着走了。

笛卡尔积：

这段 SQL 的运行过程，其实就是 多表连接（join）+ 条件过滤，只是这里两个 dogs 表是同一张表起了两个别名。

1. 代码结构

SELECT grandog
FROM grandparents, dogs AS c, dogs AS d
WHERE grandog = c.name
  AND granpup = d.name
  AND c.fur = d.fur;

grandparents：表 1，里面有 grandog（祖狗）和 granpup（孙狗）列。
dogs AS c：表 2，是 dogs 表的第一份，别名叫 c，用来表示祖狗的详细信息。
dogs AS d：表 3，是 dogs 表的第二份，别名叫 d，用来表示孙狗的详细信息。

2. FROM 的含义

FROM grandparents, dogs AS c, dogs AS d

会生成三个表的笛卡尔积（所有行组合），但后面会用 WHERE 条件把不匹配的组合去掉。
因为 dogs 被引用了两次，所以需要起别名 c 和 d，以区分是祖狗信息还是孙狗信息。

3. WHERE 条件的作用

grandog = c.name
- 把 grandparents 表里的 grandog 和 dogs c 表的 name 匹配，找到祖狗的那一行狗数据。
granpup = d.name
- 把 grandparents 表里的 granpup 和 dogs d 表的 name 匹配，找到孙狗的那一行狗数据。
c.fur = d.fur
- 要求祖狗和孙狗的毛色 (fur) 一样。

4. SELECT 的输出

SELECT grandog

只输出祖狗的名字，结果中每一行都是满足条件（毛色相同的祖狗–孙狗配对）中的祖狗。

5. 总体执行流程

先生成 grandparents × dogs(c) × dogs(d) 的组合（笛卡尔积）。
用 WHERE 把：
- grandog 对应到 c.name
- granpup 对应到 d.name
- c.fur = d.fur
  这三个条件都满足的组合留下。
从这些组合里取出 grandog 列作为最终结果。

SQL流程

A SELECT statement describes an output table based on input rows. To write one:

Describe the input rows using FROM and WHERE clauses.
Group those rows and determine which groups should appear as output rows using GROUP BY and HAVING clauses.
Format and order the output rows and columns using SELECT and ORDER BY clauses.

SELECT (Step 3) FROM (Step 1) WHERE (Step 1) GROUP BY (Step 2) HAVING (Step 2) ORDER BY (Step 3);

Step 1 may involve joining tables (using commas) to form input rows that consist of two or more rows from existing tables.

The WHERE, GROUP BY, HAVING, and ORDER BY clauses are optional.

当然，这里是关于“SQL中的聚合函数”视频的博客笔记：

SQL聚合函数

深入了解SQL中的聚合函数，解释它们如何对成组的行进行操作，而不是处理单个行。

聚合函数与标准SQL表达式的区别

首先对比了聚合函数和标准SQL表达式。通常，SELECT、WHERE和ORDER BY子句中的SQL表达式是逐行处理的。例如，WHERE子句根据每一行的条件来过滤数据。

然而，聚合函数则不同，它会从一组行中计算出一个单一的值。为了更好地说明这一点，视频引入了一个“动物”数据集，其中包含各种动物的腿数和重量。

第一个演示的聚合函数是MAX(legs)，它返回了“腿数”列中的最大值4。这与逐行处理的方式形成了鲜明对比，后者会输出六行数据。

深入了解各种聚合函数

接下来，详细介绍了多种聚合函数：

MIN和SUM: SUM(weight)计算了所有动物的总重量（12,056）。值得注意的是，传递给MAX的参数可以是任意表达式，比如MAX(legs - weight + 5)，这会在聚合前对列值进行组合计算。
多重聚合: 您可以在一个SELECT语句中组合多个聚合函数，例如MAX(legs)和MIN(weight)，它们是独立计算的。
与WHERE子句结合使用: 聚合函数可以与WHERE子句一同使用，以便在聚合前过滤行。例如，可以只选择“种类”不是“霸王龙”的动物来计算MIN(legs)和MAX(weight)。
AVG和COUNT: AVG(legs)得出的平均腿数为3.0，而COUNT(*)（或COUNT(column_name)）返回的总行数为6。

`DISTINCT`在聚合函数中的应用

讲解了DISTINCT关键字在聚合函数中的用法：

COUNT(DISTINCT legs): 这个表达式返回了唯一的腿数值（2和4），结果为2。
SUM(DISTINCT weight): 这个表达式只对唯一的重量值求和，排除了重复值。

聚合函数与非聚合列的混合使用

最后，探讨了在SELECT语句中混合使用聚合函数和非聚合列的情况。

当MAX(weight)与kind（种类）一起被选中时，输出结果会包含与最大重量值相关联的kind（霸王龙），因为聚合操作选择了与最大值对应的整行。
然而，也提醒我们，这种行为在某些情况下可能会产生误导，例如对于AVG函数，或者当多行共享相同的聚合值时（如MAX(legs)）。在这种情况下，返回的kind可能是任意的，或者不能真正代表平均值。例如，选择AVG(weight)和kind会返回“霸王龙”作为种类，尽管它并不是“平均”的动物。

Grouping Rows

分组聚合的艺术

`GROUP BY` 的基本概念

首先介绍了聚合函数的核心思想，即对一组行中的所有表达式值进行操作。在默认情况下，最终表中的所有行构成一个大组，因此应用聚合函数后只会产生单行结果。

然而，SQL允许在SELECT语句中定义多个组，从而对每个组分别执行聚合。这通过引入GROUP BY和HAVING子句的新形式SELECT语句得以实现。分组的数量由GROUP BY子句中指定表达式的唯一值决定。

通过一个“动物”表示例，展示了GROUP BY legs如何根据legs列中的唯一值（例如4条腿和2条腿）对行进行分区。每个分区（组）随后在输出中产生单独的行，并对每个组应用聚合函数（如MAX weight）。

`GROUP BY` 的进阶用法

接着展示了更多GROUP BY的应用实例：

多维度分析: 您可以轻松选择每个组中的腿数，统计具有特定腿数的动物数量，并计算每个腿类别中动物的最大重量。
多列分组: GROUP BY不仅限于单列，还可以按多个列进行分组。例如，通过GROUP BY legs, weight，可以得到腿数和重量的唯一组合。

例如，所有4条腿的动物在一组，所有2条腿的动物在另一组。

但是，当你GROUP BY两个东西（比如 GROUP BY legs, weight）时，分组的规则就更严格了：

只有当两行数据的 legs列的值相同， 并且 weight列的值也相同时，它们才会被分到同一个组里。

举个例子：
- 一只4条腿、重100公斤的动物。
- 另一只4条腿、重150公斤的动物。
- 还有一只4条腿、重100公斤的动物。
如果执行 GROUP BY legs, weight，那么第一只和第三只动物会成为一个组（因为它们都是4条腿且100公斤），而第二只动物会自己成为另一个独立的组（因为它虽然也是4条腿，但重量是150公斤）。
任意表达式分组: 您甚至可以按任意表达式进行分组。视频中演示了按weight / legs的结果进行分组，并强调了SQL默认执行整数除法。

使用 `HAVING` 子句过滤分组

引入了HAVING子句，它作为一种在聚合后过滤分组的方法。与WHERE子句过滤单个行不同，HAVING子句本身可以包含聚合函数。

通过一个例子进行了说明：使用weight / legs作为分组表达式，然后利用HAVING COUNT(*) > 1来筛选出那些组内行数大于1的分组。

SELECT：决定最终结果表里显示哪些列，以及它们的列名（也就是输出每行包含的值和列标签）。
FROM：指定数据来源表（或子查询），也就是要从哪张表里取数据。
WHERE：先在原始数据里筛选出符合条件的行（作用在输入行上）。
GROUP BY：按照某些字段对筛选后的数据分组，从而形成输出的分组行（通常和聚合函数一起用，比如 COUNT、SUM）。
HAVING：对分组后的结果再进行一次条件筛选（作用在输出行上，通常配合聚合函数）。

换句话说，WHERE 是在分组之前过滤原始数据，HAVING 是在分组之后过滤聚合结果。

你问到了一个非常关键的点！这是一个常见的混淆，我来帮你理清楚。

CREATE TABLE 不一定要加 AS。AS 只是在特定的一种创建方式下才需要。

你可以通过以下两种主要方式来创建表：

使用`CREATE TABLE`和`DROP TABLE`管理你的数据表**

`CREATE TABLE`：创建你的数据表

首先详细讲解了如何使用CREATE TABLE语句来创建新的数据表。

基本语法: CREATE TABLE语句用于为你的表命名。虽然完整的语法相当复杂（视频中展示了SQLite的文档作为例子），但核心用法非常直观。
避免错误: 你可以在语句中加入IF NOT EXISTS选项。这样做的好处是，只有在不存在同名表的情况下，SQL才会执行创建操作，从而避免了因表已存在而导致的错误。
两种创建方式:
1. 从查询结果创建: 你可以使用AS SELECT语句来创建一个新表，这个新表的内容是SELECT查询返回的结果。
2. 直接定义列: 更常见的方式是在括号内直接指定表的列定义。你需要为每一列命名，并可以为其添加一个或多个“列约束”。
重要的列约束:
- UNIQUE: 确保该列中的所有值都是唯一的，不允许出现重复值。
- DEFAULT: 为列设置一个默认值。当插入新行但没有为该列提供值时，将自动使用这个默认值。
实用示例:
- 创建一个名为numbers的表，包含N和note两列。
- 为N列添加UNIQUE约束，使其唯一。
- 为note列设置DEFAULT值为’no comment’。
- 创建一个空表也很有用，因为你可以在之后随时向其中添加数据行。

方式一：直接定义表的结构（不使用 AS）

这是最常见、最基础的创建方式。你明确地告诉数据库，这个新表叫什么名字，里面有哪些列，每一列叫什么名字，以及存储什么类型的数据。

语法结构：

CREATE TABLE 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
);

示例：

CREATE TABLE numbers (
    N INT UNIQUE,
    note TEXT DEFAULT 'no comment'
);

总结： 当你从零开始，手动定义一个全新的、空的表结构时，不需要 AS。

一次添加多行的语法

INSERT INTO 表名 (列1, 列2) VALUES
    (第一行的值1, 第一行的值2),
    (第二行的值1, 第二行的值2),
    (第三行的值1, 第三行的值2);

实际操作示例：

我们要往 numbers 表里一次性加入三条新记录。

INSERT INTO numbers (N, note) VALUES
    (100, 'Entry A'),
    (200, 'Entry B'),
    (300, 'Entry C');

方式二：从一个查询结果来创建表（需要使用 AS）

有时候，你希望新表的内容和结构直接来自于一个 SELECT 查询的结果。这时，AS 就派上用场了。AS 在这里的意思是“作为…”，即“将这个查询的结果作为新表的内容”。

语法结构：

CREATE TABLE 新表名 AS
SELECT 列1, 列2, ... FROM 现有表名 WHERE ...;

示例：

假设我们有一个 animals 表，我们想创建一个只包含4条腿动物的新表 four_legged_animals。

CREATE TABLE four_legged_animals AS
SELECT * FROM animals WHERE legs = 4;

总结： 当你想复制另一个查询的结果来快速创建一个带有数据的新表时，才需要 AS。

`DROP TABLE`：移除你的数据表

接下来，讲解了如何使用DROP TABLE语句来删除一个已经存在的表。

基本语法: DROP TABLE的用法非常简单，只需在语句后面跟上你想要删除的表名即可。
安全删除: 为了防止因尝试删除一个不存在的表而引发错误，你可以使用DROP TABLE IF EXISTS。这样，只有当表确实存在时，删除操作才会被执行，否则SQL会直接跳过，不会报错。

：精通INSERT、UPDATE和DELETE

`INSERT`：向表中添加新生命

首先详细讲解了如何向表中插入新的数据行。

灵活的插入方式：INSERT语句非常灵活。你可以明确指定要为哪些列插入数据，也可以省略列名（此时需要为所有列按顺序提供值）。数据来源既可以是直接提供的VALUES，也可以是另一个SELECT查询的结果。
添加多行：在VALUES子句后面使用多个由括号括起来并用逗号分隔的值集，可以一次性高效地插入多行数据。
实战演练：
- 通过创建一个Primes（素数）表示例。这个表有两列：n（用于存放整数）和prime（用于标记n是否为素数）。
- 为了演示，表被先删除再重建，并为n列添加了UNIQUE约束，同时为prime列设置了默认值1（在SQLite中，1通常代表“真”）。
- 演示了如何只插入n的值，此时prime列会自动使用默认值1。这很方便，但也导致像4和6这样的合数最初被错误地标记为素数。
- 接着，展示了一种更高效的填充方法：通过INSERT INTO ... SELECT语句，利用表中已有的数据快速生成并插入更多整数（例如，通过SELECT N + 6 FROM Primes）。

`UPDATE`：修改现有数据

接下来，转向如何更新表中已经存在的行。

UPDATE语句的威力：其基本语法是 UPDATE 表名 SET 列名 = 新表达式 WHERE 条件。
WHERE子句至关重要：WHERE条件是UPDATE的灵魂。它精确地指定了哪些行需要被更新。如果没有WHERE子句，表中所有行的指定列都会被更新，这通常是灾难性的。
修正数据：在Primes表的例子中，UPDATE语句被用来纠正之前插入的错误数据。通过一系列的UPDATE操作，将所有大于2的2的倍数、大于3的3的倍数、以及大于5的5的倍数的prime值都更新为0（假），从而正确地将合数标记出来。

`DELETE`：移除不再需要的数据

最后一部分讲解了如何使用DELETE语句从表中删除行。

精确删除：与UPDATE类似，DELETE语句通常与WHERE子句结合使用，以删除满足特定条件的行。语法是 DELETE FROM 表名 WHERE 条件。
清空表的风险：如果DELETE语句后面没有WHERE子句，它将删除表中的所有行。这会留下一个空表（表结构还在），这与DROP TABLE（直接删除整个表，包括结构）是不同的。
最终清理：在示例的最后，一条DELETE语句被用来删除Primes表中所有prime值为0的行。执行后，表中就只剩下干净的素数了。

Python与SQL的强强联合：用代码轻松操控数据库

第一步：连接Python与SQL数据库

视频的核心是介绍了Python内置的sqlite3模块。这个模块让你无需安装任何额外的库，就能直接使用SQLite数据库。

建立连接: 一切始于建立一个“连接”。通过sqlite3.Connection()这个类，并向它传递一个数据库文件名（例如 n.DB），你就可以创建一个连接对象。如果这个文件不存在，Python会自动为你创建它。这个连接对象就是你之后执行所有SQL操作的“遥控器”。

第二步：创建和修改表

一旦连接建立，你就可以像在SQL客户端里一样，用Python来执行SQL命令了。

创建表: 你可以将一个完整的SQL CREATE TABLE语句写成一个Python字符串，然后通过连接对象来执行它。视频中演示了 CREATE TABLE nums AS SELECT 2 UNION SELECT 3，这种方式利用一个SELECT查询的结果快速创建了一个名为nums的新表。
插入数据: 插入数据时，Python的编程能力开始大放异彩。你可以利用Python的表达式（例如 range(4, 7)）来批量生成你想插入的数据。为了安全地将这些Python变量放入SQL语句中，我们使用占位符 ? 来预留“空位”，然后将变量值传递进去。这不仅方便，还能有效防止SQL注入攻击。

第三步：读取和验证数据

从数据库中取回数据同样简单。

读取数据: 执行SELECT语句（例如 SELECT * FROM nums）会返回一个“游标”（cursor）对象。这个游标就像一个指向你查询结果的指针。
获取结果: 调用游标的 fetchall() 方法，就可以将查询结果一次性取回，其格式是一个由元组（tuple）组成的列表，列表中的每个元组就代表一行数据。

关键步骤：提交更改

这是最容易被忽略但又至关重要的一步。

提交事务: 当你对数据库进行了修改（如INSERT, UPDATE, DELETE）之后，这些改动最初只是保存在内存中。你必须调用连接对象的 commit() 方法，例如 DB.commit()，来将所有挂起的更改永久地写入到数据库文件中。不这样做的话，你的所有修改都会在程序结束时丢失。
验证数据: 视频最后通过一个SQLite客户端直接打开了n.DB文件，查询nums表，验证了通过Python代码插入的数据（2, 3, 4, 5, 6）确实已经被成功并持久地保存了。

SQL编程