数据库SQL入门阅读第2课

对于每个 Web 开发人员来说，熟悉数据库交互非常重要。在本系列的第二部分中，我们将继续探索 sql 语言并将我们学到的知识应用于 mysql 数据库。我们将学习索引、数据类型和更复杂的查询结构。

你需要学习什么

请参阅此处第一篇文章中的“您需要什么”部分：数据库SQL入门阅读。

如果您想在自己的开发服务器上按照本文中的示例进行操作，请执行以下操作：

1. 打开 MySQL 控制台并登录。

2. 如果您还没有，请使用 CREATE 查询创建一个名为“my_first_db”的数据库。

3. 使用 USE 语句切换到数据库。

数据库索引

索引（或键）主要用于提高对表的数据检索操作（例如 SELECT）的速度。

它们是良好数据库设计的重要组成部分，很难将它们归类为“优化”。在大多数情况下，它们包含在初始设计中，但也可以稍后通过 ALTER TABLE 查询添加。

索引数据库列的最常见原因是：

• 几乎每个表都应该有一个 PRIMARY KEY 索引，通常作为“id”列。

• 如果希望列包含唯一值，则它应该有一个 UNIQUE 索引。

• 如果您要经常对列执行搜索（在 WHERE 子句中），它应该有一个常规 INDEX。

• 如果一个列用于与另一个表的关系，如果可能，它应该是一个 FOREIGN KEY，否则只有一个常规索引。

首要的关键

几乎每个表都应该有一个 PRIMARY KEY，在大多数情况下作为带有 AUTO_INCREMET 选项的 INT。

如果您还记得第一篇文章，我们在 users 表中创建了一个“user_id”字段，它是一个 PRIMARY KEY。这样，在 Web 应用程序中，我们可以通过他们的 ID 号来引用所有用户。

存储在 PRIMARY KEY 列中的值必须是唯一的。此外，每个表上不能有多个 PRIMARY KEY。

让我们看一个示例查询，为美国州列表创建一个表：

也可以这样写：

独特的

由于我们希望状态名称是唯一值，因此我们应该稍微更改前面的查询示例：

默认情况下，索引将以列名命名。如果你愿意，你可以给它分配一个不同的名字：

现在索引被命名为“state_name”而不是“name”。

指数

假设我们要添加一列来表示每个州加入的年份。

我刚刚添加了 join_year 列并将其编入索引。这种类型的索引没有唯一性限制。

您也可以将其命名为 KEY 而不是 INDEX。

更多关于性能

添加索引会降低 INSERT 和 UPDATE 查询的性能。因为每次向表中添加新数据时，索引数据也会自动更新，这需要额外的工作。SELECT 查询的性能提升通常远远超过这一点。但是，不要只在每个表列上添加索引而不考虑您将运行的查询。

样品表

在我们进一步进行更多查询之前，我想创建一个包含一些数据的示例表。

这将是美国各州的列表，以及它们的加入日期（该州批准美国宪法或加入联邦的日期）及其当前人口。您可以将以下内容复制粘贴到您的 MySQL 控制台：

CREATE TABLE states (
  id INT AUTO_INCREMENT,
  name VARCHAR(20),
  join_year INT,
  population INT,
  PRIMARY KEY (id),
  UNIQUE (name),
  KEY (join_year)
);
 
 
INSERT INTO states VALUES
(1, 'Alabama', 1819, 4661900),
(2, 'Alaska', 1959, 686293),
(3, 'Arizona', 1912, 6500180),
(4, 'Arkansas', 1836, 2855390),
(5, 'California', 1850, 36756666),
(6, 'Colorado', 1876, 4939456),
(7, 'Connecticut', 1788, 3501252),
(8, 'Delaware', 1787, 873092),
(9, 'Florida', 1845, 18328340),
(10, 'Georgia', 1788, 9685744),
(11, 'Hawaii', 1959, 1288198),
(12, 'Idaho', 1890, 1523816),
(13, 'Illinois', 1818, 12901563),
(14, 'Indiana', 1816, 6376792),
(15, 'Iowa', 1846, 3002555),
(16, 'Kansas', 1861, 2802134),
(17, 'Kentucky', 1792, 4269245),
(18, 'Louisiana', 1812, 4410796),
(19, 'Maine', 1820, 1316456),
(20, 'Maryland', 1788, 5633597),
(21, 'Massachusetts', 1788, 6497967),
(22, 'Michigan', 1837, 10003422),
(23, 'Minnesota', 1858, 5220393),
(24, 'Mississippi', 1817, 2938618),
(25, 'Missouri', 1821, 5911605),
(26, 'Montana', 1889, 967440),
(27, 'Nebraska', 1867, 1783432),
(28, 'Nevada', 1864, 2600167),
(29, 'New Hampshire', 1788, 1315809),
(30, 'New Jersey', 1787, 8682661),
(31, 'New Mexico', 1912, 1984356),
(32, 'New York', 1788, 19490297),
(33, 'North Carolina', 1789, 9222414),
(34, 'North Dakota', 1889, 641481),
(35, 'Ohio', 1803, 11485910),
(36, 'Oklahoma', 1907, 3642361),
(37, 'Oregon', 1859, 3790060),
(38, 'Pennsylvania', 1787, 12448279),
(39, 'Rhode Island', 1790, 1050788),
(40, 'South Carolina', 1788, 4479800),
(41, 'South Dakota', 1889, 804194),
(42, 'Tennessee', 1796, 6214888),
(43, 'Texas', 1845, 24326974),
(44, 'Utah', 1896, 2736424),
(45, 'Vermont', 1791, 621270),
(46, 'Virginia', 1788, 7769089),
(47, 'Washington', 1889, 6549224),
(48, 'West Virginia', 1863, 1814468),
(49, 'Wisconsin', 1848, 5627967),
(50, 'Wyoming', 1890, 532668)

GROUP BY：分组数据

GROUP BY 子句将生成的数据行分组。这是一个例子：

那么刚刚发生了什么？表中有 50 行，但此查询返回了 34 个结果。这是因为结果按“join_year”列分组。换句话说，对于 join_year 的每个不同值，我们只能看到一行。由于某些州具有相同的 join_year，因此我们得到的结果少于 50 个。

例如，1787 年只有一行，但该组中有 3 个州：

所以这里有三个州，但只有特拉华州的名字出现在前面的 GROUP BY 查询之后。实际上，它可能是三种状态中的任何一种，我们不能依赖这条数据。那么使用 GROUP BY 子句有什么意义呢？

如果不使用诸如 COUNT() 之类的聚合函数，它将几乎毫无用处。让我们看看其中一些函数的作用以及它们如何为我们提供一些有用的数据。

COUNT(*)：计算行数

这可能是 GROUP BY 查询中最常用的功能。它返回每组中的行数。

例如，我们可以使用它来查看每个 join_year 的状态数：

将所有内容分组

如果您使用 GROUP BY 聚合函数，并且未指定 GROUP BY 子句，则整个结果将放在一个组中。

表中所有行数：

满足 WHERE 子句的行数：

MIN()、MAX() 和 AVG()

这些函数返回最小值、最大值和平均值：

GROUP_CONCAT()

此函数使用给定的分隔符将组内的所有值连接成一个字符串。

在第一个 GROUP BY 查询示例中，我们每年只能看到一个州名。您可以使用此功能查看每个组中的所有名称：

如果调整大小的图像难以阅读，这是查询：

SUM（）

您可以使用它来累加数值。

IF() & CASE：控制流

与其他编程语言类似，SQL 对控制流有一些支持。

IF（）

这是一个接受三个参数的函数。第一个参数是条件，如果条件为真，则使用第二个参数，如果条件为假，则使用第三个参数。

这是一个更实际的示例，我们将它与 SUM() 函数一起使用：

SELECT
 
    SUM(
        IF(population > 5000000, 1, 0)
    ) AS big_states,
 
    SUM(
        IF(population <= 5000000, 1, 0)
    ) AS small_states
 
FROM states;

第一个 SUM() 调用计算大状态的数量（人口超过 500 万），第二个调用计算小状态的数量。这些 SUM() 调用中的 IF() 调用根据条件返回 1 或 0。

结果如下：

CASE

这类似于您在编程中可能熟悉的 switch-case 语句。

假设我们要将每个状态分类为三个可能的类别之一。

SELECT
COUNT(*),
CASE
    WHEN population > 5000000 THEN 'big'
    WHEN population > 1000000 THEN 'medium'
    ELSE 'small' END
    AS state_size
FROM states GROUP BY state_size;

如您所见，我们实际上可以对 CASE 语句返回的值进行 GROUP BY。这是发生的事情：

HAVING：隐藏字段的条件

HAVING 子句允许我们将条件应用于“隐藏”字段，例如聚合函数的返回结果。所以它通常与 GROUP BY 一起使用。

例如，让我们看看我们用于按连接年份计算状态数的查询：

结果是 34 行。

但是，假设我们只对计数大于 1 的行感兴趣。我们不能为此使用 WHERE 子句：

这就是 HAVING 变得有用的地方：

请记住，此功能可能并非在所有数据库系统中都可用。

子查询

可以获取一个查询的结果并将其用于另一个查询。

在这个例子中，我们将得到人口最多的州：

内部查询将返回所有州的最高人口。外部查询将使用该值再次搜索表。

你可能认为这是一个不好的例子，我有点同意。相同的查询可以更有效地编写为：

这种情况下的结果是相同的，但是这两种查询之间有一个重要的区别。也许另一个例子会更好地证明这一点。

在这个例子中，我们将获得最后加入联盟的州：

这次的结果有两行。如果我们在这里使用了 ORDER BY ... LIMIT 1 类型的查询，我们将不会收到相同的结果。

IN（）

有时您可能希望使用内部查询返回的多个结果。

以下查询查找多个州加入联盟的年份，并返回这些州的列表：

SELECT * FROM states WHERE join_year IN (
    SELECT join_year FROM states
    GROUP BY join_year
    HAVING COUNT(*) > 1
) ORDER BY join_year;

更多关于子查询

子查询可能会变得相当复杂，因此我不会在本文中深入探讨它们。如果您想了解更多关于它们的信息，请查看MySQL 手册。

另外值得注意的是，子查询有时性能不佳，因此应谨慎使用。

UNION：组合数据

使用 UNION 查询，我们可以组合多个 SELECT 查询的结果。

此示例结合了以字母“N”开头的州和人口众多的州：

(SELECT * FROM states WHERE name LIKE 'n%')
UNION
(SELECT * FROM states WHERE population > 10000000);

请注意，纽约很大，其名称以字母“N”开头。但它只显示一次，因为重复的行会自动从结果中删除。

UNION 的另一个好处是您可以在不同的表上组合查询。

假设我们有员工、经理和客户的表格。每个表都有一个电子邮件字段。如果我们想用一个查询来获取所有的电子邮件，我们可以运行这个：

(SELECT email FROM employees)
UNION
(SELECT email FROM managers)
UNION
(SELECT email FROM customers WHERE subscribed = 1);

它将获取所有员工和经理的所有电子邮件，但仅获取已订阅接收电子邮件的客户的电子邮件。

INSERT Continued

我们已经在上一篇文章中讨论过 INSERT 查询。既然我们今天探讨了数据库索引，我们可以讨论一下 INSERT 查询的更高级功能。

INSERT... 在重复密钥更新时

这几乎就像一个条件语句。查询首先尝试执行给定的 INSERT，如果由于 PRIMARY KEY 或 UNIQUE KEY 的重复值而失败，则改为执行 UPDATE。

我们先创建一个测试表。

它是一个放置产品的桌子。“库存”栏是我们库存的产品数量。

现在尝试插入一个重复的值，看看会发生什么。

正如预期的那样，我们得到了一个错误。

假设我们收到了一个新的面包机并想要更新数据库，但我们不知道是否已经有记录。我们可以检查现有记录，然后在此基础上进行另一个查询。或者我们可以在一个简单的查询中完成所有操作：

replace INTO

这与 INSERT 完全相同，但有一个重要例外。如果找到重复的行，它首先将其删除，然后执行 INSERT，因此我们不会收到错误消息。

请注意，由于这实际上是一个全新的行，因此 id 增加了。

INSERT IGNORE

这是一种抑制重复错误的方法，通常是为了防止应用程序崩溃。有时您可能想尝试插入一个新行，然后让它失败而没有任何抱怨，以防万一发现重复。

没有返回错误，也没有更新任何行。

数据类型

每个表列都需要有一个数据类型。到目前为止，我们已经使用了 INT、VARCHAR 和 DATE 类型，但我们没有详细讨论它们。我们还应该探索其他几种数据类型。

首先，让我们从数值数据类型开始。我喜欢将它们分成两个不同的组：整数与非整数。

整数数据类型

整数列只能包含自然数（没有小数）。默认情况下，它们可以是负数或正数。但是如果设置了 UNSIGNED 选项，它只能保存正数。

MySQL 支持 5 种类型的整数，具有各种大小和范围：

非整数数值数据类型

这些数据类型可以保存十进制数：FLOAT、DOUBLE 和 DECIMAL。

FLOAT是 4 个字节，DOUBLE是 8 个字节，它们的工作方式类似。但是 DOUBLE 具有更好的精度。

DECIMAL(M,N)根据精度级别有不同的大小，可以定制。M是最大位数，N是小数点右边的位数。

例如，DECIMAL(13,4) 最多有 9 个整数位和 4 个小数位。

字符串数据类型

顾名思义，我们可以在这些数据类型列中存储字符串。

CHAR(N)最多可以容纳 N 个字符，并且具有固定的大小。例如，CHAR(50) 将始终占用每行 50 个字符的空间，而不管其中字符串的大小。绝对最大值为 255 个字符

VARCHAR(N)的工作原理相同，但存储大小不固定。N 仅用于最大尺寸。如果存储一个短于 N 个字符的字符串，它将在硬盘驱动器上占用更少的空间。绝对最大大小为 65535 个字符。

TEXT数据类型的变体更适合长字符串。TEXT 限制为 65535 个字符，MEDIUMTEXT 限制为 1670 万个字符，LONGTEXT 限制为 43 亿个字符。MySQL 通常将它们存储在服务器上的不同位置，以便表的主存储保持相对较小和快速。

日期类型

DATE存储日期并以“YYYY-MM-DD”格式显示它们，但不包含时间信息。它的范围是 1001-01-01 到 9999-12-31。

DATETIME包含日期和时间，并以“YYYY-MM-DD HH:MM:SS”格式显示。它的范围为“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。它占用 8 个字节的空间。

TIMESTAMP的工作方式与 DATETIME 类似，但有一些例外。它只需要 4 个字节的空间，范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。因此，例如，它可能不适合存储出生日期。

TIME只存储时间，YEAR只存储年份。

其他

MySQL 还支持其他一些数据类型。您可以在此处查看它们的列表。您还应该在此处查看每种数据类型的存储大小。

结论

感谢您阅读这篇文章。SQL 是 Web 开发人员库中的一种重要语言和工具。