第 14 章 使用子查询

14.1 子查询

嵌套在其他查询中的查询

14.2 利用子查询进行过滤

输入: SELECT database_name FROM innodb_table_stats WHERE table_name REGEXP 'orders'; 输出: +---------------+ | database_name | +---------------+ | crashcourse   | +---------------+ ​ 输入: SELECT database_name,last_update FROM innodb_index_stats WHERE database_name REGEXP 'crashcourse'; 输出: +---------------+---------------------+ | database_name | last_update         | +---------------+---------------------+ | crashcourse   | 2024-01-09 17:24:26 | | crashcourse   | 2024-01-09 17:24:26 | | crashcourse   | 2024-01-09 17:24:26 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:24:16 | | crashcourse   | 2024-01-09 17:24:16 | | crashcourse   | 2024-01-09 17:24:16 | +---------------+---------------------+ ​ 利用子查询结合:     输入: SELECT database_name,last_update FROM innodb_index_stats WHERE database_name = (SELECT database_name FROM innodb_table_stats WHERE table_name REGEXP 'orders');     输出: +---------------+---------------------+ | database_name | last_update         | +---------------+---------------------+ | crashcourse   | 2024-01-09 17:24:26 | | crashcourse   | 2024-01-09 17:24:26 | | crashcourse   | 2024-01-09 17:24:26 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:22:59 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:24:36 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:23:01 | | crashcourse   | 2024-01-09 17:24:16 | | crashcourse   | 2024-01-09 17:24:16 | | crashcourse   | 2024-01-09 17:24:16 | +---------------+---------------------+ 分析: 在 SELECT 语句中，子查询总是从内向外处理

14.3 作为计算字段使用子查询

输入: SELECT COUNT(*) AS root FROM global_grants WHERE USER REGEXP 'root'; 输出:  +------+ | root | +------+ |   29 | +------+ ​ 输入: SELECT help_topic_id,help_topic.name FROM help_topic WHERE help_topic_id <= (SELECT COUNT(*) AS root FROM global_grants WHERE USER REGEXP 'root') ORDER BY help_topic_id; 输出: +---------------+----------------------------+ | help_topic_id | name                       | +---------------+----------------------------+ |             0 | HELP_DATE                  | |             1 | HELP_VERSION               | |             2 | AUTO_INCREMENT             | |             3 | HELP COMMAND               | |             4 | ASYMMETRIC_DECRYPT         | |             5 | ASYMMETRIC_DERIVE          | |             6 | ASYMMETRIC_ENCRYPT         | |             7 | ASYMMETRIC_SIGN            | |             8 | ASYMMETRIC_VERIFY          | |             9 | CREATE_ASYMMETRIC_PRIV_KEY | |            10 | CREATE_ASYMMETRIC_PUB_KEY  | |            11 | CREATE_DH_PARAMETERS       | |            12 | CREATE_DIGEST              | |            13 | TRUE                       | |            14 | FALSE                      | |            15 | BIT                        | |            16 | TINYINT                    | |            17 | BOOLEAN                    | |            18 | SMALLINT                   | |            19 | MEDIUMINT                  | |            20 | INT                        | |            21 | INTEGER                    | |            22 | BIGINT                     | |            23 | DECIMAL                    | |            24 | DEC                        | |            25 | FLOAT                      | |            26 | DOUBLE                     | |            27 | DOUBLE PRECISION           | |            28 | DATE                       | |            29 | DATETIME                   | +---------------+----------------------------+ 分析：使用子查询的另一方法就是创建计算字段