阅读量:0
第 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 | +---------------+----------------------------+ 分析:使用子查询的另一方法就是创建计算字段