Sql中子查询的基本使用

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

MySQL子查询

在MySQL中，子查询是一种强大的工具，用于在查询中嵌套另一个查询。子查询可以用于多种场景，包括过滤、比较、计算和连接数据等。本文将介绍MySQL中子查询的各种用法和示例。

在讲解下面用法之前，我们需要先进行数据库和表的创建

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80012
 Source Host           : localhost:3306
 Source Schema         : mydatabase

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

 Date: 01/03/2024 16:45:59
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `p_id` int(11) NULL DEFAULT NULL,
  `location` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1, '中国XX有限公司', NULL, '北京');
INSERT INTO `department` VALUES (2, '技术部', 1, '北京');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `user_sex` tinyint(4) NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'zhangsan', 'newPassword', '2023-12-12 20:00:14', 0, 27);
INSERT INTO `user` VALUES (2, 'lisi', 'newPassword', '2024-02-09 23:03:15', 0, 33);
INSERT INTO `user` VALUES (3, 'wangwu', 'newPassword', '2023-09-19 12:00:14', 1, 60);
INSERT INTO `user` VALUES (4, 'wangwu1', 'newPassword', '2023-11-21 08:20:00', 1, 88);
INSERT INTO `user` VALUES (5, 'wangwu2', 'newPassword', '2024-01-01 00:00:00', 0, 14);
INSERT INTO `user` VALUES (6, 'alice', 'newPassword', '2024-02-29 20:00:14', 1, 12);

-- ----------------------------
-- Table structure for user_department
-- ----------------------------
DROP TABLE IF EXISTS `user_department`;
CREATE TABLE `user_department`  (
  `user_id` int(11) NOT NULL,
  `dept_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`, `dept_id`) USING BTREE,
  INDEX `dept_id`(`dept_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Fixed;

-- ----------------------------
-- Records of user_department
-- ----------------------------

SET FOREIGN_KEY_CHECKS = 1;

成功执行后会建立 user，user_department,department三个表；

子查询的基本用法

MySql中子查询的基本格式如下：

SELECT 列1, 列2, ...
FROM 表名
WHERE 列条件 OPERATOR (SELECT 子查询);

在这个语法中，子查询嵌套在主查询的条件中，用括号括起来，并与主查询的条件一起用。子查询可以返回单个值、单个行或者多行结果

单行子查询

单行子查询返回单个值，可以用来比较、过滤和计算，以下是一个示例

-- 查询表中大于平均年龄的用户
SELECT * FROM `user` 
WHERE age > (SELECT AVG(age) FROM `user`);

执行结果如图：

子查询的语句也可以用放在增添语句中，以下是一个示例：

-- 添加用户 id < 3 的到部门名称为'中国XX有限公司' 的部门中
INSERT INTO user_department (user_id, dept_id)
SELECT id, (SELECT dept_id FROM department WHERE dept_name = '中国XX有限公司')
FROM user
WHERE id < 3;
-- 将其他用户 添加到部门名称为'技术部'的部门中
INSERT INTO user_department (user_id, dept_id)
SELECT id, (SELECT dept_id FROM department WHERE dept_name = '技术部')
FROM user
WHERE id >= 3;

在这个示例中，子查询查询部门名为 ‘中国XX有限公司’ 的部门id,然后在根据主查询进行添加数据
成功执行后如下图：

多行子查询

多行子查询可以返回多个结果行，用于与主查询的条件匹配，以下是一个示例：

-- 查询 部门id 为1 的有哪些 用户
SELECT * FROM `user`
WHERE id in (SELECT user_id FROM user_department WHERE dept_id = 1)

在这个示例中(SELECT user_id FROM user_department WHERE dept_id = 1) 返回了所有来自部门id为1的用户id，然后主查询再查询对应用户信息,成功执行后如下图：

子查询与连接

子查询可以与连接一起使用，用于过滤或比较连接的结果。以下是一个示例:

-- 查询员工与部门之间的联系
SELECT u.username,d.dept_name
FROM `user` u
JOIN user_department u_d ON u.id = u_d.user_id
JOIN department d ON u_d.dept_id = d.dept_id
WHERE u_d.dept_id in (SELECT dept_id FROM department WHERE location = '北京' )

成功执行后如下图：

EXIST 和 NOT EXISTS子查询

EXISTS 和 NOT EXISTS 子查询用于检查主查询中的行是否存在于子查询中。以下是一个示例：

SELECT * 
FROM `user` u
WHERE EXISTS (SELECT * FROM user_department ud WHERE u.id = ud.user_id)

使用了子查询和EXISTS关键字来查询user表中的所有用户，但只返回那些存在于user_department表中的用户记录。
执行成功后如下图：

在这里我们会出现所有用户，大家可以尝试删除user_department一俩列数据会发现查询的user用户也会因此减少对应user

子查询的聚合函数

子查询中可以包含聚合函数，用于计算汇总值。以下是一个示例：

-- 查询小于平均年龄的用户
SELECT * FROM `user` 
WHERE age < (SELECT AVG(age) FROM `user`);

执行成功后如下图：

总结

通过本文的介绍，我们了解了MySQL中子查询的基本语法和各种用法。子查询是一个强大的工具，可以在查询中嵌套另一个查询，用于过滤、比较、计算和连接数据等多种场景。熟练掌握子查询的使用，可以帮助我们更灵活地处理复杂的查询需求。

希望本文能够帮助您更好地理解和使用MySQL中的子查询。