MySQL查询之子查询

@十三阿哥

已于 2024-03-18 14:11:42 修改

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： # MySQL 文章标签： mysql 数据库 sql

于 2023-05-08 22:55:41 首次发布

本文链接：https://blog.csdn.net/TianYanRen111/article/details/130567998

MySQL 专栏收录该内容

24 篇文章

订阅专栏

0. 概念

SQL语句中嵌套SELECT语句，称为嵌套查询，又叫子查询。
查询可以基于一个表或多个表。子查询可以添加到SELECT、UPDATE和DELETE中，而且可以进行多层嵌套。子查询常用操作符有 ANY(SOME)，ALL、IN、EXISTS。也可以使用比较运算符，如<、<=、>、>=和!=等。。。

1. 数据源

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80016
 Source Host           : localhost:3306
 Source Schema         : tempdb

 Target Server Type    : MySQL
 Target Server Version : 80016
 File Encoding         : 65001

 Date: 08/05/2023 21:40:13
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `id` int(11) NOT NULL,
  `c_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '高三 1 班');
INSERT INTO `class` VALUES (2, '高三 2 班');
INSERT INTO `class` VALUES (3, '高三 3 班');
INSERT INTO `class` VALUES (4, '高三 4 班');
INSERT INTO `class` VALUES (5, '高三 5 班');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `num` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `c_id` int(11) NULL DEFAULT NULL,
  `g_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`num`) USING BTREE,
  INDEX `f_std_cls`(`c_id`) USING BTREE,
  CONSTRAINT `f_std_cls` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, '张三', 23, '354456354', 1, NULL);
INSERT INTO `student` VALUES (102, '王五', 34, '4334', 1, 101);
INSERT INTO `student` VALUES (103, '李四', 32, '131434', 3, 101);
INSERT INTO `student` VALUES (104, '赵无极', 23, '4234', 2, NULL);
INSERT INTO `student` VALUES (105, '韩夫子', 34, '23', 2, 102);
INSERT INTO `student` VALUES (106, '高俅', 25, '42543', 4, 103);
INSERT INTO `student` VALUES (107, '范瑶', 26, '2345', NULL, 104);
INSERT INTO `student` VALUES (108, '斯巴达克斯', 27, '422156 ', NULL, 104);

SET FOREIGN_KEY_CHECKS = 1;

在这里插入图片描述

2. 标量子查询

子查询的返回结果为单个值。
常用的操作符：=、<>、>、>=、<、<=。

// 查询高三1班的所有学生
// a. 首先在class表中查询高三 1 班的id;
// b. 再在student查询c_id=id的记录;

mysql> select id from class where c_name='高三 1 班';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> select * from student where c_id=1;
+-----+------+------+-----------+------+------+
| num | name | age  | tel       | c_id | g_id |
+-----+------+------+-----------+------+------+
| 101 | 张三 |   23 | 354456354 |    1 | NULL |
| 102 | 王五 |   34 | 4334      |    1 |  101 |
+-----+------+------+-----------+------+------+
2 rows in set (0.00 sec)

// 采用子查询进行查询
mysql> select * from student where c_id=(select id from class where c_name='高三 1 班');
+-----+------+------+-----------+------+------+
| num | name | age  | tel       | c_id | g_id |
+-----+------+------+-----------+------+------+
| 101 | 张三 |   23 | 354456354 |    1 | NULL |
| 102 | 王五 |   34 | 4334      |    1 |  101 |
+-----+------+------+-----------+------+------+
2 rows in set (0.11 sec)

3. 列子查询

子查询的返回结果为1列，可以是多行。
常用的操作符：IN、NOT IN、ANY、SOME、ALL。
IN：在指定的集合范围之内、多选一；
NOT IN：不在指定的集合范围之内；
ANY：子查询返回列表中，有任意一个满足即可；
SOME：与ANY等同，使用SOME的地方都可以使用ANY；
ALL：子查询返回列表的所有值都必须满足；

// IN的使用

// 查询 高三1班 和 高三2班的所有学生信息
// a. 查询1班 和 2班的id;
// b. 根据班级id来查询学生信息
mysql> select id from class where c_name="高三 1 班" OR  c_name="高三 2 班";
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> select * from student where c_id IN (1,2);
+-----+--------+------+-----------+------+------+
| num | name   | age  | tel       | c_id | g_id |
+-----+--------+------+-----------+------+------+
| 101 | 张三   |   23 | 354456354 |    1 | NULL |
| 102 | 王五   |   34 | 4334      |    1 |  101 |
| 104 | 赵无极 |   23 | 4234      |    2 | NULL |
| 105 | 韩夫子 |   34 | 23        |    2 |  102 |
+-----+--------+------+-----------+------+------+
4 rows in set (0.00 sec)

// 使用列子查询
mysql> select * from student where c_id IN (select id from class where c_name="高三 1 班" OR  c_name="高三 2 班");
+-----+--------+------+-----------+------+------+
| num | name   | age  | tel       | c_id | g_id |
+-----+--------+------+-----------+------+------+
| 101 | 张三   |   23 | 354456354 |    1 | NULL |
| 102 | 王五   |   34 | 4334      |    1 |  101 |
| 104 | 赵无极 |   23 | 4234      |    2 | NULL |
| 105 | 韩夫子 |   34 | 23        |    2 |  102 |
+-----+--------+------+-----------+------+------+
4 rows in set (0.00 sec)

// ALL/SOME的使用

// 查询比高三2班 所有人年龄都高的学生
// a. 查询高三2班的id;
// b. 根据id查询所有2班学生的age;
// c. 比2班 所有人年龄都要大的学生;

mysql> select id from class where c_name="高三 2 班";
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> select age from student where c_id=2;
+------+
| age  |
+------+
|   23 |
|   34 |
+------+
2 rows in set (0.00 sec)

mysql> select * from student where age > ALL(select age from student where c_id=(select id from class where c_name="高三 2 班"));
Empty set (0.00 sec)

// ANY的使用

// 查询比高三2班 任意一人年龄都高的学生
// a. 查询高三2班的id;
// b. 根据id查询所有2班学生的age;
// c. 比2班 任意一人年龄都要大的学生;

mysql> select id from class where c_name="高三 2 班";
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> select age from student where c_id=2;
+------+
| age  |
+------+
|   23 |
|   34 |
+------+
2 rows in set (0.00 sec)

mysql> select * from student where age > ANY(select age from student where c_id=(select id from class where c_name="高三 2 班"));
+-----+------------+------+---------+------+------+
| num | name       | age  | tel     | c_id | g_id |
+-----+------------+------+---------+------+------+
| 102 | 王五       |   34 | 4334    |    1 |  101 |
| 103 | 李四       |   32 | 131434  |    3 |  101 |
| 105 | 韩夫子     |   34 | 23      |    2 |  102 |
| 106 | 高俅       |   25 | 42543   |    4 |  103 |
| 107 | 范瑶       |   26 | 2345    | NULL |  104 |
| 108 | 斯巴达克斯 |   27 | 422156  | NULL |  104 |
+-----+------------+------+---------+------+------+
6 rows in set (0.00 sec)

4. 行子查询

子查询的返回结果为1行，可以是多列。
常用的操作符：=、<>、IN、NOT IN。

mysql> update student set age=23 where num=102;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+-----+------------+------+-----------+------+------+
| num | name       | age  | tel       | c_id | g_id |
+-----+------------+------+-----------+------+------+
| 101 | 张三       |   23 | 354456354 |    1 | NULL |
| 102 | 王五       |   23 | 4334      |    1 |  101 |
| 103 | 李四       |   32 | 131434    |    3 |  101 |
| 104 | 赵无极     |   23 | 4234      |    2 | NULL |
| 105 | 韩夫子     |   34 | 23        |    2 |  102 |
| 106 | 高俅       |   25 | 42543     |    4 |  103 |
| 107 | 范瑶       |   26 | 2345      | NULL |  104 |
| 108 | 斯巴达克斯 |   27 | 422156    | NULL |  104 |
+-----+------------+------+-----------+------+------+
8 rows in set (0.00 sec)

// 查询与 张三 年龄及班级相同的学生;
// Way1
mysql> select age, c_id from student where name="张三";
+------+------+
| age  | c_id |
+------+------+
|   23 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from student where age>23 AND c_id=1;
+-----+------+------+------+------+------+
| num | name | age  | tel  | c_id | g_id |
+-----+------+------+------+------+------+
| 102 | 王五 |   34 | 4334 |    1 |  101 |
+-----+------+------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from student where age=23 AND c_id=1;
+-----+------+------+-----------+------+------+
| num | name | age  | tel       | c_id | g_id |
+-----+------+------+-----------+------+------+
| 101 | 张三 |   23 | 354456354 |    1 | NULL |
| 102 | 王五 |   23 | 4334      |    1 |  101 |
+-----+------+------+-----------+------+------+
2 rows in set (0.00 sec)

// way3
mysql> select age, c_id from student where name="张三";
+------+------+
| age  | c_id |
+------+------+
|   23 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from student where (age,c_id)=(23,1);
+-----+------+------+-----------+------+------+
| num | name | age  | tel       | c_id | g_id |
+-----+------+------+-----------+------+------+
| 101 | 张三 |   23 | 354456354 |    1 | NULL |
| 102 | 王五 |   23 | 4334      |    1 |  101 |
+-----+------+------+-----------+------+------+
2 rows in set (0.00 sec)

// Way3：行子查询
mysql> select * from student where (age,c_id)=(select age, c_id from student where name="张三");
+-----+------+------+-----------+------+------+
| num | name | age  | tel       | c_id | g_id |
+-----+------+------+-----------+------+------+
| 101 | 张三 |   23 | 354456354 |    1 | NULL |
| 102 | 王五 |   23 | 4334      |    1 |  101 |
+-----+------+------+-----------+------+------+
2 rows in set (0.00 sec)

5. 表子查询

子查询的返回结果为多行多列(一个表)。
常用的操作符：IN。

mysql> update student set age=34 where num=102;
Query OK, 1 row affected (0.15 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update student set c_id=2 where num=102;
Query OK, 1 row affected (0.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update student set c_id=1 where num=104;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+-----+------------+------+-----------+------+------+
| num | name       | age  | tel       | c_id | g_id |
+-----+------------+------+-----------+------+------+
| 101 | 张三       |   23 | 354456354 |    1 | NULL |
| 102 | 王五       |   34 | 4334      |    2 |  101 |
| 103 | 李四       |   32 | 131434    |    3 |  101 |
| 104 | 赵无极     |   23 | 4234      |    1 | NULL |
| 105 | 韩夫子     |   34 | 23        |    2 |  102 |
| 106 | 高俅       |   25 | 42543     |    4 |  103 |
| 107 | 范瑶       |   26 | 2345      | NULL |  104 |
| 108 | 斯巴达克斯 |   27 | 422156    | NULL |  104 |
+-----+------------+------+-----------+------+------+
8 rows in set (0.00 sec)

// 查询与 张三或王五的 年龄和班级相同的学生信息
mysql> select age,c_id from student where name="张三" OR name="王五";
+------+------+
| age  | c_id |
+------+------+
|   23 |    1 |
|   34 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from student where(age, c_id) IN (select age,c_id from student where name="张三" OR name="王五");
+-----+--------+------+-----------+------+------+
| num | name   | age  | tel       | c_id | g_id |
+-----+--------+------+-----------+------+------+
| 101 | 张三   |   23 | 354456354 |    1 | NULL |
| 104 | 赵无极 |   23 | 4234      |    1 | NULL |
| 102 | 王五   |   34 | 4334      |    2 |  101 |
| 105 | 韩夫子 |   34 | 23        |    2 |  102 |
+-----+--------+------+-----------+------+------+
4 rows in set (0.10 sec)

// 查询年龄>=24的学生信息, 及其班级信息
mysql> select * from student where age>=24;
+-----+------------+------+---------+------+------+
| num | name       | age  | tel     | c_id | g_id |
+-----+------------+------+---------+------+------+
| 102 | 王五       |   34 | 4334    |    2 |  101 |
| 103 | 李四       |   32 | 131434  |    3 |  101 |
| 105 | 韩夫子     |   34 | 23      |    2 |  102 |
| 106 | 高俅       |   25 | 42543   |    4 |  103 |
| 107 | 范瑶       |   26 | 2345    | NULL |  104 |
| 108 | 斯巴达克斯 |   27 | 422156  | NULL |  104 |
+-----+------------+------+---------+------+------+
6 rows in set (0.00 sec)

mysql> select s.*,c.* from (select * from student where age>=24) AS s LEFT JOIN class AS c ON s.c_id=c.id;
+-----+------------+------+---------+------+------+------+-----------+
| num | name       | age  | tel     | c_id | g_id | id   | c_name    |
+-----+------------+------+---------+------+------+------+-----------+
| 102 | 王五       |   34 | 4334    |    2 |  101 |    2 | 高三 2 班 |
| 103 | 李四       |   32 | 131434  |    3 |  101 |    3 | 高三 3 班 |
| 105 | 韩夫子     |   34 | 23      |    2 |  102 |    2 | 高三 2 班 |
| 106 | 高俅       |   25 | 42543   |    4 |  103 |    4 | 高三 4 班 |
| 107 | 范瑶       |   26 | 2345    | NULL |  104 | NULL | NULL      |
| 108 | 斯巴达克斯 |   27 | 422156  | NULL |  104 | NULL | NULL      |
+-----+------------+------+---------+------+------+------+-----------+
6 rows in set (0.00 sec)