提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
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中的子查询。