MySQL8 中文参考（八）（1）-阿里云开发者社区

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

原文：dev.mysql.com/doc/refman/8.0/en/multiple-tables.html

5.3.4.9 使用多个表

pet表记录了你拥有的宠物。如果你想记录关于它们的其他信息，比如它们生活中的事件，比如去兽医那里或者生产幼崽的时间，你需要另一个表。这个表应该是什么样子？它需要包含以下信息：

宠物名称，以便你知道每个事件涉及哪个动物。
一个日期，以便你知道事件发生的时间。
一个描述事件的字段。
一个事件类型字段，如果你想对事件进行分类。

鉴于这些考虑，event表的CREATE TABLE语句可能如下所示：

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
       type VARCHAR(15), remark VARCHAR(255));

与pet表一样，最简单的方法是通过创建一个包含以下信息的制表符分隔文本文件来加载初始记录。

名称	日期	类型	备注
绒绒	1995-05-15	分娩	4 只小猫，3 只雌性，1 只雄性
巴菲	1993-06-23	分娩	5 只小狗，2 只雌性，3 只雄性
巴菲	1994-06-19	分娩	3 只小狗，3 只雌性
啁啾	1999-03-21	兽医	需要修直喙
苗条	1997-08-03	兽医	肋骨骨折
鲍泽	1991-10-12	狗舍
方格	1991-10-12	狗舍
方格	1998-08-28	生日	给了他一个新的咬咬玩具
爪子	1998-03-17	生日	给了他一个新的跳蚤项圈
威斯勒	1998-12-09	生日	第一个生日
名称	日期	类型	备注

加载记录如下：

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

根据你在pet表上运行的查询所学到的知识，你应该能够在event表中执行检索；原则是相同的。但是event表本身何时不足以回答你可能提出的问题？

假设你想找出每只宠物产下幼崽时的年龄。我们之前看到如何从两个日期计算年龄。母亲的产仔日期在event表中，但要计算她在那天的年龄，你需要她的出生日期，这个日期存储在pet表中。这意味着查询需要两个表：

mysql> SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age,
       remark
       FROM pet INNER JOIN event
         ON pet.name = event.name
       WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

关于这个查询有几点需要注意：

FROM子句连接了两个表，因为查询需要从两个表中提取信息。
当从多个表中合并（连接）信息时，你需要指定如何将一个表中的记录与另一个表中的记录匹配。这很容易，因为它们都有一个name列。查询使用ON子句根据name值匹配两个表中的记录。
查询使用INNER JOIN来合并这两个表。INNER JOIN允许只有当两个表都满足ON子句中指定的条件时，才能在结果中出现来自任一表的行。在这个例子中，ON子句指定pet表中的name列必须与event表中的name列匹配。如果一个名字出现在一个表中而在另一个表中没有出现，那么该行不会出现在结果中，因为ON子句中的条件不满足。
因为name列在两个表中都存在，所以在引用该列时必须明确指定是指哪个表。这可以通过在列名前加上表名来实现。

执行连接操作并不一定需要两个不同的表。有时，如果要比较表中的记录与该表中的其他记录，则将表与自身连接是有用的。例如，要找出你的宠物中的配对，你可以将pet表与自身连接，以生成相同物种的活体雄性和雌性的候选配对：

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
       FROM pet AS p1 INNER JOIN pet AS p2
         ON p1.species = p2.species
         AND p1.sex = 'f' AND p1.death IS NULL
         AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+

在这个查询中，我们为表名指定别名，以便引用列并清楚地知道每个列引用与哪个表实例相关联。

5.4 获取有关数据库和表的信息

原文：dev.mysql.com/doc/refman/8.0/en/getting-information.html

如果您忘记了数据库或表的名称，或者给定表的结构是什么（例如，其列叫什么）？MySQL 通过几个语句解决了这个问题，这些语句提供有关其支持的数据库和表的信息。

您之前已经看到SHOW DATABASES，它列出了服务器管理的数据库。要找出当前选择的数据库是哪个，请使用DATABASE()函数：

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

如果您尚未选择任何数据库，则结果为NULL。

要查找默认数据库包含哪些表（例如，当您不确定表名时），请使用此语句：

mysql> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

该语句生成的输出中列的名称始终为Tables_in_*db_name*，其中*db_name*是数据库的名称。有关更多信息，请参阅 Section 15.7.7.39, “SHOW TABLES Statement”。

如果您想了解表的结构，DESCRIBE语句很有用；它显示表的每个列的信息：

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Field表示列名，Type是列的数据类型，NULL指示列是否可以包含NULL值，Key指示列是否已索引，Default指定列的默认值。Extra显示有关列的特殊信息：如果使用AUTO_INCREMENT选项创建列，则值为auto_increment而不是空。

DESC是DESCRIBE语句的简写形式。有关更多信息，请参阅 Section 15.8.1, “DESCRIBE Statement”。

您可以使用SHOW CREATE TABLE语句获取创建现有表所需的CREATE TABLE语句。请参阅 Section 15.7.7.10, “SHOW CREATE TABLE Statement”。

如果表上有索引，则SHOW INDEX FROM *tbl_name*会提供有关它们的信息。有关此语句的更多信息，请参阅 Section 15.7.7.22, “SHOW INDEX Statement”。

5.5 在批处理模式下使用 mysql

译文：dev.mysql.com/doc/refman/8.0/en/batch-mode.html

在前面的章节中，你以交互方式使用mysql输入语句并查看结果。你也可以以批处理模式运行mysql。要做到这一点，将你想要运行的语句放在一个文件中，然后告诉mysql从文件中读取输入：

$> mysql < *batch-file*

如果你在 Windows 下运行mysql并且文件中有一些导致问题的特殊字符，你可以这样做：

C:\> mysql -e "source *batch-file*"

如果你需要在命令行上指定连接参数，命令可能如下所示：

$> mysql -h *host* -u *user* -p < *batch-file*
Enter password: ********

当你以这种方式使用mysql时，你正在创建一个脚本文件，然后执行该脚本。

如果你希望脚本在其中的某些语句产生错误时继续运行，你应该使用--force命令行选项。

为什么要使用脚本？以下是一些原因：

如果你重复运行一个查询（比如，每天或每周一次），将其制作成脚本可以避免每次执行时重新输入它。
你可以通过复制和编辑脚本文件从现有类似的查询生成新的查询。
在开发查询时，批处理模式也很有用，特别是对于多行语句或多语句序列。如果出现错误，你不必重新输入所有内容。只需编辑你的脚本以纠正错误，然后告诉mysql再次执行它。
如果你有一个产生大量输出的查询，你可以通过一个分页器运行输出，而不是看着它从屏幕顶部滚动出去：

$> mysql < *batch-file* | more

你可以将输出捕获到一个文件中以供进一步处理：

$> mysql < *batch-file* > mysql.out

你可以将你的脚本分发给其他人，这样他们也可以运行这些语句。
有些情况不允许交互使用，例如，当你从cron作业中运行查询时。在这种情况下，你必须使用批处理模式。

当你以批处理模式运行mysql时，默认的输出格式与交互式使用时不��（更简洁）。例如，当在交互式模式下运行mysql时，SELECT DISTINCT species FROM pet的输出如下：

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

在批处理模式下，输出看起来像这样：

species
bird
cat
dog
hamster
snake

如果你想在批处理模式下获得交互式输出格式，使用mysql -t。要将执行的语句回显到输出中，使用mysql -v。

你也可以在mysql提示符下使用source命令或\.命令来运行脚本：

mysql> source *filename*;
mysql> \. *filename*

查看第 6.5.1.5 节，“从文本文件执行 SQL 语句”，获取更多信息。

5.6 常见查询示例

原文：dev.mysql.com/doc/refman/8.0/en/examples.html

5.6.1 列的最大值

5.6.2 某列最大值所在的行

5.6.3 每组列的最大值

5.6.4 某列的分组最大值所在的行

5.6.5 使用用户定义变量

5.6.6 使用外键

5.6.7 在两个键上搜索

5.6.8 计算每天的访问量

5.6.9 使用 AUTO_INCREMENT

这里是如何使用 MySQL 解决一些常见问题的示例。

一些示例使用表shop来保存每个文章（物品编号）对于某些交易商（经销商）的价格。假设每个交易商对于每篇文章有一个固定价格，那么(article, dealer)是记录的主键。

启动命令行工具mysql并选择一个数据库：

$> mysql *your-database-name*

要创建和填充示例表，请使用以下语句：

CREATE TABLE shop (
    article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

执行完这些语句后，表应该包含以下内容：

SELECT * FROM shop ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  3.45 |
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | B      |  1.45 |
|       3 | C      |  1.69 |
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+

5.6.1 列的最大值

原文：dev.mysql.com/doc/refman/8.0/en/example-maximum-column.html

“最高的项目编号是多少？”

SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
|       4 |
+---------+

5.6.2 持有某一列最大值的行

原文：dev.mysql.com/doc/refman/8.0/en/example-maximum-row.html

任务：找到最贵文章的编号、经销商和价格。

这可以通过子查询轻松完成：

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

另一个解决方案是使用LEFT JOIN，如下所示：

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

你也可以通过按价格降序排序所有行，并使用 MySQL 特定的LIMIT子句仅获取第一行，像这样：

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

注意

如果有几篇价格都是 19.95 的最贵文章，使用LIMIT解决方案只会显示其中一篇。

- 5.6.3 每组的列最大值

原文：dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group.html

任务：找到每篇文章的最高价格。

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article
ORDER BY article;
+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

5.6.4 持有某一列分组最大值的行

原文：dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html

任务：对于每篇文章，找到价格最高的经销商或经销商。

这个问题可以通过这样的子查询来解决：

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article)
ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

前面的示例使用了相关子查询，这可能效率低下（参见 Section 15.2.15.7, “Correlated Subqueries”）。解决问题的其他可能性包括在 FROM 子句中使用无关联子查询、LEFT JOIN 或具有窗口函数的公共表达式。

无关联子查询：

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price
  FROM shop
  GROUP BY article) AS s2
  ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;

LEFT JOIN：

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;

LEFT JOIN 的工作原理是，当 s1.price 达到最大值时，没有比它更大的 s2.price，因此对应的 s2.article 值为 NULL。参见 Section 15.2.13.2, “JOIN Clause”。

具有窗口函数的公共表达式：

WITH s1 AS (
   SELECT article, dealer, price,
          RANK() OVER (PARTITION BY article
                           ORDER BY price DESC
                      ) AS `Rank`
     FROM shop
)
SELECT article, dealer, price
  FROM s1
  WHERE `Rank` = 1
ORDER BY article;

5.6.5 使用用户定义变量

原文：dev.mysql.com/doc/refman/8.0/en/example-user-variables.html

你可以使用 MySQL 用户变量来记住结果，而无需将它们存储在客户端的临时变量中。（参见第 11.4 节，“用户定义变量”.）

例如，要找到价格最高和最低的文章，可以这样做：

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

注意

也可以将数据库对象的名称（如表或列）存储在用户变量中，然后在 SQL 语句中使用这个变量；但是，这需要使用准备语句。更多信息请参见第 15.5 节，“准备语句”。

5.6.6 使用外键

原文：dev.mysql.com/doc/refman/8.0/en/example-foreign-keys.html

MySQL 支持外键，允许跨表引用相关数据，并支持外键约束，有助于保持相关数据的一致性。

外键关系涉及一个持有初始列值的父表，以及一个引用父列值的子表。外键约束定义在子表上。

以下示例通过单列外键关联parent和child表，并展示了外键约束如何强制执行引用完整性。

使用以下 SQL 语句创建父表和子表：

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
) ENGINE=INNODB;

插入一行到父表中，如下所示：

mysql> INSERT INTO parent (id) VALUES ROW(1);

验证数据是否已插入。你可以通过简单地选择所有parent表中的行来做到这一点，如下所示：

mysql> TABLE parent;
+----+
| id |
+----+
|  1 |
+----+

使用以下 SQL 语句向子表中插入一行：

mysql> INSERT INTO child (id,parent_id) VALUES ROW(1,1);

插入操作成功是因为parent_id 1 存在于父表中。

尝试将具有在父表中不存在的parent_id值的行插入到子表中会被拒绝，并显示错误，如下所示：

mysql> INSERT INTO child (id,parent_id) VALUES ROW(2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) 
REFERENCES `parent` (`id`))

这个操作失败是因为指定的parent_id值在父表中不存在。

尝试删除先前插入到父表中的行也会失败，如下所示：

mysql> DELETE FROM parent WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) 
REFERENCES `parent` (`id`))

这个操作失败是因为子表中的记录包含了引用的 id（parent_id）值。

当一个操作影响到父表中具有匹配行的键值时，结果取决于FOREIGN KEY子句的ON UPDATE和ON DELETE子句指定的引用动作。省略ON DELETE和ON UPDATE子句（如当前子表定义中）等同于指定RESTRICT选项，它拒绝影响父表中具有匹配行的键值的操作。

为了演示ON DELETE和ON UPDATE引用动作，删除子表并重新创建它以包括带有CASCADE选项的ON UPDATE和ON DELETE子句。CASCADE选项在删除或更新父表中的行时，会自动删除或更新子表中匹配的行。

DROP TABLE child;
CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=INNODB;

使用下面显示的语句向子表中插入一些行：

mysql> INSERT INTO child (id,parent_id) VALUES ROW(1,1), ROW(2,1), ROW(3,1);

验证数据是否已插入，如下所示：

mysql> TABLE child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
|    3 |         1 |
+------+-----------+

更新父表中的 ID，将其从 1 更改为 2，使用下面显示的 SQL 语句：

mysql> UPDATE parent SET id = 2 WHERE id = 1;

通过选择所有父表中的行来验证更新是否成功，如下所示：

mysql> TABLE parent;
+----+
| id |
+----+
|  2 |
+----+

验证ON UPDATE CASCADE引用动作是否已更新子表，如下所示：

mysql> TABLE child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         2 |
+------+-----------+

为了演示ON DELETE CASCADE引用动作，删除父表中parent_id = 2的记录；这将删除父表中的所有记录。

mysql> DELETE FROM parent WHERE id = 2;

因为子表中的所有记录都与parent_id = 2相关联，所以ON DELETE CASCADE参照操作会从子表中删除所有记录，如下所示：

mysql> TABLE child;
Empty set (0.00 sec)

关于外键约束的更多信息，请参见第 15.1.20.5 节，“外键约束”。

MySQL8 中文参考（八）（2）https://developer.aliyun.com/article/1565882

MySQL8 中文参考（八）（1）

5.3.4.9 使用多个表

5.4 获取有关数据库和表的信息

5.5 在批处理模式下使用 mysql

5.6 常见查询示例

5.6.1 列的最大值

5.6.2 持有某一列最大值的行

- 5.6.3 每组的列最大值

5.6.4 持有某一列分组最大值的行

5.6.5 使用用户定义变量

5.6.6 使用外键

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL8 中文参考（八）（1）

5.3.4.9 使用多个表

5.4 获取有关数据库和表的信息

5.5 在批处理模式下使用 mysql

5.6 常见查询示例

5.6.1 列的最大值

5.6.2 持有某一列最大值的行

- 5.6.3 每组的列最大值

5.6.4 持有某一列分组最大值的行

5.6.5 使用用户定义变量

5.6.6 使用外键

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像