从零开始学习SQL数据库操作：选择、更新和删除数据

现在我们已经掌握了 创建数据库和表的 基础知识，我们可以开始深入了解 SQL 交互的核心内容：选择、更新和删除数据。

我们将从这些查询的基本结构开始，然后深入介绍强大的操作，并提供足够的细节，让您感到危险。

从表中选择数据

如前所述，SQL 操作具有相当严格的操作顺序，子句必须遵守这些操作顺序才能进行有效的查询。我们首先剖析一个常见的 SELECT 陈述：

SELECT
  column_name_1,
  column_name_2
FROM
  schema_name.table_name
WHERE
  column_name_1 = "Value";

这可能是最常见的 SELECT 查询结构。首先，我们列出要选择的列的名称，并用逗号分隔。要接收所有列，我们可以简单地说 SELECT *。

这些列需要来自某个地方，因此我们指定接下来要引用的表。FROM table_name 它可以采用 (non-PostgreSQL) 或(PostgreSQL) 的形式 FROM schema_name.table_name。理论上，这里的分号会产生有效的查询，但我们通常希望选择满足特定条件的行。

这就是该 WHERE 子句的用武之地：仅返回条件返回“true”WHERE 的行。在上面的示例中，我们验证字符串是否完全匹配 ”Value”。

仅选择不同的值

通常有用的方法是在列中选择不同的值。换句话说，如果某个值存在于 100 行中的同一列中，则运行 DISTINCT 查询只会向我们显示该值一次。这是查看专栏独特内容的好方法，而无需深入研究所述值的分布。其效果类似于美国参议院或选举团：忘记群众，支持怀俄明州 2020：

SELECT DISTINCT column_name
FROM table_name;

抵消和限制查询中的结果

OFFSET 选择数据时，和的组合 LIMIT 有时很关键。如果我们从一个包含数十万行的数据库中进行选择，我们会浪费大量的系统资源来一次获取所有行；相反，我们可以让应用程序或 API 对结果进行分页。

LIMIT 后面跟着一个整数，本质上表示“返回不超过 X 个结果”。

OFFSET 后面还跟着一个整数，它表示返回结果的数字起点，又名：“返回第 X 个结果之后出现的所有结果：”

SELECT
 *
FROM
 table_name
LIMIT 50 OFFSET 0;

以上返回前 50 个结果。如果我们想在应用程序端构建分页结果，我们可以像这样构建查询：

from SQLAlchemy import engine, session

# Set up a SQLAlchemy session
Session = sessionmaker()
engine = create_engine('sqlite:///example.db')
Session.configure(bind=engine)
sess = Session()

# Appication variables
page_number = 3
page_size = 50
results_subset = page_number * results limit

# Query
session.query(TableName).limit(page_size).offset(results_subset)

这样的应用程序可以在 page_number 用户每次单击下一页时递增 1，然后适当地修改我们的查询以返回下一页结果。

另一个用途 OFFSET 可能是从失败的脚本停止的地方继续。如果我们将整个数据库写入 CSV 并遇到失败。我们可以通过设置 OFFSET 等于 CSV 中的行数来继续脚本停止的地方，以避免再次运行整个脚本。

对结果进行排序

现在最后要考虑的是使用 ORDER BY 子句对结果进行排序。我们可以按任何指定的列对结果进行排序，并说明我们希望结果升序 (ASC) 还是降序 (DESC)：

SELECT
  *
FROM
  schema_name.table_name
WHERE
  column_name_1 = "Value"
ORDER BY
  updated_date DESC
LIMIT 50 OFFSET 10;

复杂的 SELECT 语句

当然，我们可以选择具有 WHERE 比精确匹配更深入的逻辑的行。这些操作中最通用的操作之一是 LIKE.

将正则表达式与 LIKE 一起使用

LIKE 可能是选择具有字符串值的列的最强大方法。有了 LIKE，我们可以利用正则表达式来构建高度复杂的逻辑。让我们从我最喜欢的一些开始：

SELECT
  *
FROM
  people
WHERE
  name LIKE "%Wade%";

传递一个 LIKE 两边都有百分号的字符串本质上是一个“包含”语句。% 相当于通配符，因此 % 无论该人的名字、中间名还是姓氏是 Wade，放在字符串的任一侧都会返回 true。查看其他有用的组合 %：

• a%：查找以“a”开头的任何值。

• %a：查找以“a”结尾的任何值。

• %or%：查找任意位置有“or”的值。

• _r%：查找第二个位置有“r”的任何值。

• a_%_%：查找以“a”开头且长度至少为 3 个字符的任何值。

• a%o：查找以“a”开头并以“o”结尾的任何值。

寻找不相似的值

的相反 LIKE 当然是 NOT LIKE，它运行相同的条件，但返回相反的真 / 假值 LIKE：

SELECT
  *
FROM
  people
WHERE
  name NOT LIKE "%Wade%";

带有日期时间列的条件

日期时间列对于选择数据非常有用。MONTH(column_name)与普通字符串不同，我们可以分别使用、DAY(column_name)和轻松从 DateTime 中提取月、日和年的数值 YEAR(column_name)。例如，MONTH()在包含 DateTime 的列上使用 2019-01-26 05:42:34 将返回 1，即一月。由于值以整数形式返回，因此在日期范围内查找结果很简单：

SELECT
  *
FROM
  posts
WHERE YEAR(created_at) 
查找具有 NULL 值的行
NULL 是一种特殊的数据类型，本质上表示“某些东西的缺失”，因此没有条件永远不会 equal NULL。相反，我们找到包含以下值的行 IS NULL：
SELECT
  *
FROM
  posts
WHERE author IS NULL;
对于熟悉验证数据类型的人来说，这应该不会感到惊讶。
当然，相反的情况是 NOT NULL：
SELECT
  *
FROM
  posts
WHERE author IS NOT NULL;
插入数据
查询 INSERT 创建一个新行，并且相当简单：我们声明要插入数据的列，然后是要插入到所述列中的值：
INSERT INTO table_name (column_1, column_2, column_3)
VALUES ("value1", "value2", "value3");
许多事情都可能导致插入失败。其一，值的数量必须与我们指定的列数相匹配；如果我们不这样做，我们要么提供了太少的值，要么提供了太多的值。
其次，vales 必须尊重列的数据类型。如果我们尝试将整数插入到 DateTime 列中，我们将收到错误。
最后，我们必须考虑表的键和约束。如果存在指定某些列不能为空或必须唯一的键，则也必须遵守这些键。
作为一个速记技巧，如果我们将值插入到表的所有列中，我们可以跳过显式列出列名称的部分：
INSERT INTO table_name
VALUES ("value1", "value2", "value3");
以下是使用真实数据的插入查询的快速示例：
INSERT INTO friends (id, name, birthday)
VALUES (1, 'Jane Doe', '1990-05-30');
更新记录：基础知识
更新行是事情变得有趣的地方。我们可以做的事情有很多，所以让我们继续努力：
UPDATE table_name
SET column_name_1 = 'value'
WHERE column_name_2 = 'value';
这很简单：行中与我们的条件匹配的列的值。请注意，SET 总是出现在 之前 WHERE。这是对真实数据的相同查询：
UPDATE celebs
SET twitter_handle = '@taylorswift13'
WHERE id = 4;
更新记录：有用的逻辑
使用 CONCAT 连接字符串
您会发现根据行中已存在的数据更新行是常见的做法：换句话说，清理或修改数据。一个很棒的字符串运算符是 CONCAT(). CONCAT("string_1", "string_2")将连接传递给单个字符串的所有字符串。
CONCAT()下面是一个结合使用 NOT LIKE 来确定哪些帖子摘录不以标点符号结尾的真实示例。如果摘录不以标点符号结尾，我们在末尾添加一个句点：
UPDATE
  posts
SET
  custom_excerpt = CONCAT(custom_excerpt, '.')
WHERE
  custom_excerpt NOT LIKE '%.'
  AND custom_excerpt NOT LIKE '%!'
  AND custom_excerpt NOT LIKE '%?';
使用替换
REPLACE()在 SQL 中的工作原理与在几乎所有编程语言中的工作原理一样。我们传递 REPLACE()三个值：


要修改的字符串。


字符串中将被替换的子字符串。


更换的价值。


我们可以用 做很多聪明的事情 REPLACE()。这是一个更改博客文章的特色图像以包含“视网膜图像”后缀的示例：
UPDATE
  posts
SET
  feature_image = REPLACE(feature_image, '.jpg', '@2x.jpg');
场景：基于日期的文件夹结构
前几天，我在处理涉及更改 CDN 的噩梦情况时遇到了一个有趣的练习。它涉及我们迄今为止所回顾的所有内容，并很好地说明了仅使用 SQL 即可实现的目标。
为数百个帖子移动数百张图像的挑战来自于文件结构的形式。Ghost 喜欢将图像保存在过时的文件夹结构中，例如 2019/02/image.jpg。我们之前的 CDN 根本不遵守这一点，因此将所有图像转储在一个文件夹中。不理想。
值得庆幸的是，我们可以利用帖子的元数据来辨别此文件结构。由于图像是在创建帖子时添加到帖子中的，因此我们可以使用帖子表中的 created_at 列来找出正确的日期文件夹：
UPDATE
  postsSET
  feature_image = CONCAT("https://cdn.example.com/posts/",
	YEAR(created_at),
	"/",
	LPAD(MONTH(created_at), 2, '0'),
	"/",
	SUBSTRING_INDEX(feature_image, '/', - 1)
  );
让我们分解一下我们的内容 CONCAT：


https://cdn.example.com/posts/：我们新 CDN 的基本 URL。


YEAR(created_at)：从我们的帖子创建日期中提取年份（对应于文件夹）。


LPAD(MONTH(created_at), 2, ‘0’)：使用 MONTH(created_at)返回前几个月的一位数，但我们的文件夹结构希望始终有两位数的月份（即：2018/01/ 而不是 2018/1/）。我们可以使用 LPAD()此处“填充”日期，以便月份始终为两位数长，较短的日期将用数字 0 填充。


SUBSTRING_INDEX(feature_image, ‘/’, – 1)：我们通过查找现有图像 URL 中最后一个斜杠后面的所有内容来获取每个帖子图像的文件名。


现在每个图像的结果将如下所示：
https://cdn.example.com/posts/2018/02/image.jpg
删除记录
让我们以最后一种类型的查询（删除行）结束今天的内容：文章来源地址 https://www.toymoban.com/diary/sql/575.html
DELETE FROM celebs
WHERE twitter_handle IS NULL;
到此这篇关于从零开始学习 SQL 数据库操作：选择、更新和删除数据的文章就介绍到这了, 更多相关内容可以在右上角搜索或继续浏览下面的相关文章，希望大家以后多多支持 TOY 模板网！