MySQL多表查询讲解

时间：2021-03-08 16:21:57 来源：作者：

1. 开始之前

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `did` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `dname` varchar(60) DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
BEGIN;
INSERT INTO `dept` VALUES (1, '研发部');
INSERT INTO `dept` VALUES (2, '人事部');
INSERT INTO `dept` VALUES (3, '测试部');
INSERT INTO `dept` VALUES (4, '销售部');
INSERT INTO `dept` VALUES (5, '生产部');
COMMIT;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(60) NOT NULL COMMENT '姓名',
  `age` tinyint(4) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(2) NOT NULL DEFAULT '1' COMMENT '性别，1男，2女',
  `salary` decimal(10,2) NOT NULL COMMENT '薪资',
  `hire_date` date NOT NULL COMMENT '聘用日期',
  `dept_id` int(11) DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of employee
-- ----------------------------
BEGIN;
INSERT INTO `employee` VALUES (1, '菜虚鲲', 20, 2, 10000.00, '2020-01-10', 1);
INSERT INTO `employee` VALUES (2, '奥力给', 30, 1, 18000.00, '2020-01-08', 1);
INSERT INTO `employee` VALUES (3, '老八', 28, 1, 7000.00, '2020-01-07', 1);
INSERT INTO `employee` VALUES (4, '小张', 25, 1, 8000.00, '2020-01-10', 1);
INSERT INTO `employee` VALUES (5, '小红', 20, 2, 6000.00, '2020-01-05', 2);
INSERT INTO `employee` VALUES (6, '小丽', 23, 2, 6500.00, '2020-01-05', 2);
INSERT INTO `employee` VALUES (7, '小花', 21, 2, 5500.00, '2020-01-10', 2);
INSERT INTO `employee` VALUES (8, '马小跳', 25, 1, 7000.00, '2020-01-01', 3);
INSERT INTO `employee` VALUES (9, '张大骚', 30, 1, 9000.00, '2020-01-07', 3);
INSERT INTO `employee` VALUES (10, '马冬梅', 31, 2, 5000.00, '2020-01-07', 4);
INSERT INTO `employee` VALUES (11, '川坚果', 60, 1, 100.00, '2020-01-08', NULL);
COMMIT;

2. 多表联合查询

2.1 语法

select 字段1，字段2... from 表1，表2... [where 条件]

2.2 实战

注意:多表联合查询需要添加条件，否则会直接输出左表*右表，这种结果称之为笛卡尔乘积。

集合A中的数据乘以集合B中的数据等于笛卡尔乘积

MySQL> select * from employee,dept;
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
| id | name      | age | sex | salary   | hire_date  | dept_id | did | dname     |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |   1 | 研发部 |
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |   2 | 人事部 |
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |   3 | 测试部 |
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |   4 | 销售部 |
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |   5 | 生产部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   1 | 研发部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   2 | 人事部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   3 | 测试部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   4 | 销售部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   5 | 生产部 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |   1 | 研发部 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |   2 | 人事部 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |   3 | 测试部 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |   4 | 销售部 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |   5 | 生产部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |   1 | 研发部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |   2 | 人事部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |   3 | 测试部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |   4 | 销售部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |   5 | 生产部 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 |   1 | 研发部 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 |   2 | 人事部 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 |   3 | 测试部 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 |   4 | 销售部 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 |   5 | 生产部 |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 |   1 | 研发部 |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 |   2 | 人事部 |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 |   3 | 测试部 |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 |   4 | 销售部 |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 |   5 | 生产部 |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 |   1 | 研发部 |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 |   2 | 人事部 |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 |   3 | 测试部 |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 |   4 | 销售部 |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 |   5 | 生产部 |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 |   1 | 研发部 |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 |   2 | 人事部 |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 |   3 | 测试部 |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 |   4 | 销售部 |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 |   5 | 生产部 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |   1 | 研发部 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |   2 | 人事部 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |   3 | 测试部 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |   4 | 销售部 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |   5 | 生产部 |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 |   1 | 研发部 |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 |   2 | 人事部 |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 |   3 | 测试部 |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 |   4 | 销售部 |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 |   5 | 生产部 |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    |   1 | 研发部 |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    |   2 | 人事部 |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    |   3 | 测试部 |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    |   4 | 销售部 |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    |   5 | 生产部 |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
55 rows in set (0.00 sec)

正确的查询方式是:以两表中相互关联的字段作为查询条件进行查询。

mysql> select * from employee,dept where employee.dept_id = dept.did;

查询结果

+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
| id | name      | age | sex | salary   | hire_date  | dept_id | did | dname     |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |   1 | 研发部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   1 | 研发部 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |   1 | 研发部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |   1 | 研发部 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 |   2 | 人事部 |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 |   2 | 人事部 |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 |   2 | 人事部 |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 |   3 | 测试部 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |   3 | 测试部 |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 |   4 | 销售部 |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
10 rows in set (0.00 sec)

输出结果为两个集合的交集。

3. Innner内连接查询

3.1 语法

select 字段1，字段2... from 表1 inner join 表2 on [条件];

3.2 实战

查询员工和部门信息

select * from employee inner join dept on employee.dept_id = dept.did;

输出结果

mysql> select * from employee inner join dept on employee.dept_id = dept.did; 
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
| id | name      | age | sex | salary   | hire_date  | dept_id | did | dname     |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |   1 | 研发部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   1 | 研发部 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |   1 | 研发部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |   1 | 研发部 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 |   2 | 人事部 |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 |   2 | 人事部 |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 |   2 | 人事部 |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 |   3 | 测试部 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |   3 | 测试部 |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 |   4 | 销售部 |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
10 rows in set (0.04 sec)

其输出结果和多表联合查询一致。

如果附加其他条件，可以直接用and连接符连接在on语句的后面

mysql> select * from employee inner join dept on employee.dept_id = dept.did and employee.salary >= 10000;

输出结果

+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
| id | name      | age | sex | salary   | hire_date  | dept_id | did | dname     |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |   1 | 研发部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   1 | 研发部 |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
2 rows in set (0.00 sec)

4. Left左外连接查询

左外连接查询，即左表的数据全部显示。

4.1 语法

select * from 表1 left join 表2 on [条件];

4.2 实战

查询员工和部门的所有信息

select * from employee left join dept on employee.dept_id = dept.did;

输出结果

mysql> select * from employee left join dept on employee.dept_id = dept.did;
+----+-----------+-----+-----+----------+------------+---------+------+-----------+
| id | name      | age | sex | salary   | hire_date  | dept_id | did  | dname     |
+----+-----------+-----+-----+----------+------------+---------+------+-----------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |    1 | 研发部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |    1 | 研发部 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |    1 | 研发部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |    1 | 研发部 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 |    2 | 人事部 |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 |    2 | 人事部 |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 |    2 | 人事部 |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 |    3 | 测试部 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |    3 | 测试部 |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 |    4 | 销售部 |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    | NULL | NULL      |
+----+-----------+-----+-----+----------+------------+---------+------+-----------+
11 rows in set (0.00 sec)

左表中的数据全部显示，右表中的数据只显示符合条件的，不符合条件的以NULL填充

更直观的展现上述关系

mysql> select * from employee left join dept on employee.dept_id = dept.did and dept.did = 1;
+----+-----------+-----+-----+----------+------------+---------+------+-----------+
| id | name      | age | sex | salary   | hire_date  | dept_id | did  | dname     |
+----+-----------+-----+-----+----------+------------+---------+------+-----------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |    1 | 研发部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |    1 | 研发部 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |    1 | 研发部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |    1 | 研发部 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 | NULL | NULL      |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 | NULL | NULL      |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 | NULL | NULL      |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 | NULL | NULL      |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 | NULL | NULL      |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 | NULL | NULL      |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    | NULL | NULL      |
+----+-----------+-----+-----+----------+------------+---------+------+-----------+
11 rows in set (0.01 sec)

5. Right右外连接查询

右外连接查询，与左外连接查询正好相反，即右表的数据全部显示。

5.1 语法

select * from 表1 right join 表2 on [条件];

5.2 实战

查询员工和部门信息

select * from employee right join dept on employee.dept_id = dept.did;

输出结果

mysql> select * from employee right join dept on employee.dept_id = dept.did;
+------+-----------+------+------+----------+------------+---------+-----+-----------+
| id   | name      | age  | sex  | salary   | hire_date  | dept_id | did | dname     |
+------+-----------+------+------+----------+------------+---------+-----+-----------+
|    1 | 菜虚鲲 |   20 |    2 | 10000.00 | 2020-01-10 |       1 |   1 | 研发部 |
|    2 | 奥力给 |   30 |    1 | 18000.00 | 2020-01-08 |       1 |   1 | 研发部 |
|    3 | 老八    |   28 |    1 | 7000.00  | 2020-01-07 |       1 |   1 | 研发部 |
|    4 | 小张    |   25 |    1 | 8000.00  | 2020-01-10 |       1 |   1 | 研发部 |
|    5 | 小红    |   20 |    2 | 6000.00  | 2020-01-05 |       2 |   2 | 人事部 |
|    6 | 小丽    |   23 |    2 | 6500.00  | 2020-01-05 |       2 |   2 | 人事部 |
|    7 | 小花    |   21 |    2 | 5500.00  | 2020-01-10 |       2 |   2 | 人事部 |
|    8 | 马小跳 |   25 |    1 | 7000.00  | 2020-01-01 |       3 |   3 | 测试部 |
|    9 | 张大骚 |   30 |    1 | 9000.00  | 2020-01-07 |       3 |   3 | 测试部 |
|   10 | 马冬梅 |   31 |    2 | 5000.00  | 2020-01-07 |       4 |   4 | 销售部 |
| NULL | NULL      | NULL | NULL | NULL     | NULL       | NULL    |   5 | 生产部 |
+------+-----------+------+------+----------+------------+---------+-----+-----------+
11 rows in set (0.00 sec)

6. UNION全连接查询

全连接查询：其结果是在内连接查询的基础上显示左右两边没有的数据。
写法：左连接查询 UNION 右连接查询

6.1 语法

select * from 表1 left join 表2 on [条件]
union
select * from 表1 right join 表2 on [条件]

6.2 实战

以全连接的形式查询部门和员工数据

select * from employee left join dept on employee.dept_id = dept.did
 union
select * from employee right join dept on employee.dept_id = dept.did;

输出结果

mysql> select * from employee left join dept on employee.dept_id = dept.did
    -> union
    -> select * from employee right join dept on employee.dept_id = dept.did;
+------+-----------+------+------+----------+------------+---------+------+-----------+
| id   | name      | age  | sex  | salary   | hire_date  | dept_id | did  | dname     |
+------+-----------+------+------+----------+------------+---------+------+-----------+
|    1 | 菜虚鲲 |   20 |    2 | 10000.00 | 2020-01-10 |       1 |    1 | 研发部 |
|    2 | 奥力给 |   30 |    1 | 18000.00 | 2020-01-08 |       1 |    1 | 研发部 |
|    3 | 老八    |   28 |    1 | 7000.00  | 2020-01-07 |       1 |    1 | 研发部 |
|    4 | 小张    |   25 |    1 | 8000.00  | 2020-01-10 |       1 |    1 | 研发部 |
|    5 | 小红    |   20 |    2 | 6000.00  | 2020-01-05 |       2 |    2 | 人事部 |
|    6 | 小丽    |   23 |    2 | 6500.00  | 2020-01-05 |       2 |    2 | 人事部 |
|    7 | 小花    |   21 |    2 | 5500.00  | 2020-01-10 |       2 |    2 | 人事部 |
|    8 | 马小跳 |   25 |    1 | 7000.00  | 2020-01-01 |       3 |    3 | 测试部 |
|    9 | 张大骚 |   30 |    1 | 9000.00  | 2020-01-07 |       3 |    3 | 测试部 |
|   10 | 马冬梅 |   31 |    2 | 5000.00  | 2020-01-07 |       4 |    4 | 销售部 |
|   11 | 川坚果 |   60 |    1 | 100.00   | 2020-01-08 | NULL    | NULL | NULL      |
| NULL | NULL      | NULL | NULL | NULL     | NULL       | NULL    |    5 | 生产部 |
+------+-----------+------+------+----------+------------+---------+------+-----------+
12 rows in set (0.00 sec)

7. 嵌套查询

嵌套查询：在一个sql语句中使用多个select，第一次的查询结果可作为第二次查询结果的条件/表名使用。

7.1 作为表名使用

select * from (select id,name,age from employee) as em where em.id = 1;

查询结果

mysql> select * from (select id,name,age from employee) as em where em.id = 1;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | 菜虚鲲 |  20 |
+----+-----------+-----+
1 row in set (0.00 sec)

解释：把（select id,name,age from employee）语句的返回结果当作一个临时表，临时表的表名为em。

举例：查询平均工资在7000以上的部门
解题思路：
第一步，求部门平均工资

mysql> select AVG(salary) as ag,dept.dname from employee,dept where employee.dept_id = dept.did group by dept.did;
+--------------+-----------+
| ag           | dname     |
+--------------+-----------+
| 10750.000000 | 研发部 |
| 6000.000000  | 人事部 |
| 8000.000000  | 测试部 |
| 5000.000000  | 销售部 |
+--------------+-----------+
4 rows in set (0.00 sec)

第二步，把部门平均工资表当作临时表，进行查询。

select dname from 
		(
			select 
				AVG(salary) as ag,
				dept.dname 
				from employee,dept w
				here employee.dept_id = dept.did 
				group by dept.did
		) as dept_avg_salary 
	where dept_avg_salary.ag > 7000;

运行结果：

mysql> select dname from (select AVG(salary) as ag,dept.dname from employee,dept where employee.dept_id = dept.did group by dept.did) as dept_avg_salary where dept_avg_salary.ag > 7000;
+-----------+
| dname     |
+-----------+
| 研发部 |
| 测试部 |
+-----------+
2 rows in set (0.00 sec)

7.2 作为查询条件使用

举例：找出工资最高的员工的所有信息

select * from employee,dept 
	where employee.dept_id = dept.did 
	and employee.salary = (select MAX(salary) from employee);

查询结果

mysql> select * from employee,dept where employee.dept_id = dept.did and employee.salary = (select MAX(salary) from employee);
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
| id | name      | age | sex | salary   | hire_date  | dept_id | did | dname     |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   1 | 研发部 |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
1 row in set (0.01 sec)

举例：求工资大于所有人平均工资的员工的所有信息

select * from employee,dept 
	where employee.dept_id = dept.did 
	and employee.salary > (select AVG(salary) from employee);

查询结果

mysql> select * from employee,dept where employee.dept_id = dept.did and employee.salary > (select AVG(salary) from employee);
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
| id | name      | age | sex | salary   | hire_date  | dept_id | did | dname     |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |   1 | 研发部 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |   1 | 研发部 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |   1 | 研发部 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |   3 | 测试部 |
+----+-----------+-----+-----+----------+------------+---------+-----+-----------+
4 rows in set (0.00 sec)

7.3. 嵌套查询关键字

7.3.1 ANY、SOME

两者意义相同，与比较运算符(=、>、>=、<、<=、<>)结合起来使用，any/some作用于子查询语句，只要子查询语句中有一个符合条件，就返回true。

select s1 from t1 where s1 > any (select s1 from t2);

假设子查询语句返回结果有三个result1，result2，result3，则上述语句等同于：

select s1 from t1 where s1 > result1 or s1 > result2 or s1 > result3;

7.3.2 IN

in 与 =any 相同。相当于:

select s1 from t1 where s1 = result1 or s1 = result2 or s1 = result3;

7.3.3 ALL

当子查询语句中的所有项都符合条件时，才返回true。

select s1 from t1 where s1 > all (select s1 from t2);

等同于:

select s1 from t1 where s1 > result1 and s1 > result2 and s1 > result3;

7.3.4 EXISTS、NOT EXISTS

语法:

select ... from tableName exists(subquery);

当子查询语句subquery返回列时，exists表达式为true，此时执行前面的查询语句。子查询语句没有返回任何列时，exists语句为false，不执行前面的查询语句。

mysql> select * from employee where exists (select * from employee where id =1);
+----+-----------+-----+-----+----------+------------+---------+
| id | name      | age | sex | salary   | hire_date  | dept_id |
+----+-----------+-----+-----+----------+------------+---------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    |
+----+-----------+-----+-----+----------+------------+---------+
11 rows in set (0.00 sec)

当子查询语句没有返回任何列时

mysql> select * from employee where exists (select * from employee where id =12);
Empty set

select NULL 返回了列，所以:

mysql> select NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
 
mysql> select * from employee where exists (select NULL) and salary > 10000;
+----+-----------+-----+-----+----------+------------+---------+
| id | name      | age | sex | salary   | hire_date  | dept_id |
+----+-----------+-----+-----+----------+------------+---------+
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 |
+----+-----------+-----+-----+----------+------------+---------+
1 row in set (0.00 sec)

ot exists与exists相反。

8 判断查询

8.1 IF

语法:

if(条件表达式,"结果为true","结果为false");

举例，将薪资大于大于8000的员工薪资级别设置为小康，小于8000设置为一般:

select *,if(salary > 8000,"小康","一般") as salary_level from employee;

输出结果:

mysql> select *,if(salary > 8000,"小康","一般") as salary_level from employee;
+----+-----------+-----+-----+----------+------------+---------+--------------+
| id | name      | age | sex | salary   | hire_date  | dept_id | salary_level |
+----+-----------+-----+-----+----------+------------+---------+--------------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 | 小康       |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 | 小康       |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 | 一般       |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 | 一般       |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 | 一般       |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 | 一般       |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 | 一般       |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 | 一般       |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 | 小康       |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 | 一般       |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    | 一般       |
+----+-----------+-----+-----+----------+------------+---------+--------------+
11 rows in set (0.00 sec)

8.2 if … else if … else 形式

语法:

select ...,case when [条件1] then [result]
				when [条件2] then [result]
				else [result]
				end
from tableName;

举例:
根据员工工资划分员工生活水平，小于7000为贫穷，7000 到 9000为一般，9000-10000为中等，10000-12000为中等偏上，大于12000为有钱。

mysql> select name,salary,case  when salary < 7000 then '贫穷'
    -> 							when salary < 9000 then '一般'
    -> 							when salary < 10000 then '中等'
    -> 							when salary < 12000 then '中等偏上'
    -> 							else '有钱'
    -> 					  end as living_standard
    -> from employee;
+-----------+----------+-----------------+
| name      | salary   | living_standard |
+-----------+----------+-----------------+
| 菜虚鲲 | 10000.00 | 中等偏上    |
| 奥力给 | 18000.00 | 有钱          |
| 老八    | 7000.00  | 一般          |
| 小张    | 8000.00  | 一般          |
| 小红    | 6000.00  | 贫穷          |
| 小丽    | 6500.00  | 贫穷          |
| 小花    | 5500.00  | 贫穷          |
| 马小跳 | 7000.00  | 一般          |
| 张大骚 | 9000.00  | 中等          |
| 马冬梅 | 5000.00  | 贫穷          |
| 川坚果 | 100.00   | 贫穷          |
+-----------+----------+-----------------+
11 rows in set (0.00 sec)

其形式类似于if…else if…else if…else形式。

8.3 switch case形式

语法:

select ..., case s1
			when [value1] then [result1]
			when [value2] then [result2]
			when [value3] then [result3]
			else [resultOther] end
from tableName;

举例，直接显示出员工性别:

mysql> select *,case sex
    -> 				when 1 then '男'
    -> 				when 2 then '女'
    -> 				else '未知' end as employee_sex
    -> from employee;
+----+-----------+-----+-----+----------+------------+---------+--------------+
| id | name      | age | sex | salary   | hire_date  | dept_id | employee_sex |
+----+-----------+-----+-----+----------+------------+---------+--------------+
|  1 | 菜虚鲲 |  20 |   2 | 10000.00 | 2020-01-10 |       1 | 女          |
|  2 | 奥力给 |  30 |   1 | 18000.00 | 2020-01-08 |       1 | 男          |
|  3 | 老八    |  28 |   1 | 7000.00  | 2020-01-07 |       1 | 男          |
|  4 | 小张    |  25 |   1 | 8000.00  | 2020-01-10 |       1 | 男          |
|  5 | 小红    |  20 |   2 | 6000.00  | 2020-01-05 |       2 | 女          |
|  6 | 小丽    |  23 |   2 | 6500.00  | 2020-01-05 |       2 | 女          |
|  7 | 小花    |  21 |   2 | 5500.00  | 2020-01-10 |       2 | 女          |
|  8 | 马小跳 |  25 |   1 | 7000.00  | 2020-01-01 |       3 | 男          |
|  9 | 张大骚 |  30 |   1 | 9000.00  | 2020-01-07 |       3 | 男          |
| 10 | 马冬梅 |  31 |   2 | 5000.00  | 2020-01-07 |       4 | 女          |
| 11 | 川坚果 |  60 |   1 | 100.00   | 2020-01-08 | NULL    | 男          |
+----+-----------+-----+-----+----------+------------+---------+--------------+
11 rows in set (0.00 sec)

Tags：多表查询点击:() 评论:()

声明：本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com)，我们将及时更正、删除，谢谢。

▌相关推荐

SQL多表查询

一、表的加法：union 如果想保留两个表中的重复值，那么使用union all，那么重复值语文课程就被保留下来了二、表的联结交叉联结(也叫笛卡尔积 cross jion）表中的每一行与另...【详细内容】

2021-08-27　　Tags: 多表查询点击:(51)　　评论:(0)　　加入收藏

MySQL多表查询讲解

1. 开始之前-- ------------------------------ Table structure for dept-- ----------------------------DROP TABLE IF EXISTS `dept`;CREATE TABLE `dept` ( `did` int...【详细内容】

2021-03-08　　Tags: 多表查询点击:(149)　　评论:(0)　　加入收藏

▌简易百科推荐

MySQL 使用 Mysqldump 备份导入数据导致主从异常

作者：雷文霆爱可生华东交付服务部 DBA 成员，主要负责Mysql故障处理及相关技术支持。爱好看书，电影。座右铭，每一个不曾起舞的日子，都是对生命的辜负。本文来源：原创投稿 *爱可生...【详细内容】

2021-12-24　　爱可生　　　　Tags:MySQL 　点击:(6)　　评论:(0)　　加入收藏

分享MySQL记录锁、间隙锁、临键锁小案例演示，你学废了吗

生成间隙(gap)锁、临键(next-key)锁的前提条件是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过，详细内容可以看这篇文章...【详细内容】

2021-12-14　　python数据分析　　　　Tags:MySQL记录锁　点击:(17)　　评论:(0)　　加入收藏

mysqlbinlog基本认识

binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二...【详细内容】

2021-12-14　　linux上的码农　　　　Tags:mysql 　点击:(13)　　评论:(0)　　加入收藏

百万级数据下的mysql深度解析

为查询优化你的查询大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查...【详细内容】

2021-12-09　　元宇宙iwemeta　　　　Tags:mysql 　点击:(15)　　评论:(0)　　加入收藏

Mysql数据库tinyint，int，bigint，char，varchar究竟用哪个？

测试的目的和原因，公司有很多程序员，每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑，那么我就来测试一下究竟哪种使...【详细内容】

2021-12-08　　吴彬的分享　　　　Tags:Mysql数据库　点击:(14)　　评论:(0)　　加入收藏

Win下部署多个MySQL数据库实例

当你们考虑项目并发的时候，我在部署环境，当你们在纠结使用ArrayList还是LinkedArrayList的时候，我还是在部署环境。所以啊，技术不止境，我在部环境。今天这篇文章缕一下在同一台服...【详细内容】

2021-12-08　　秃头码哥　　　　Tags:MySQL数据库　点击:(16)　　评论:(0)　　加入收藏

MySQL中常用的15个查询子句

对于数据分析来说，MySQL使用最多的是查询，比如对数据进行排序、分组、去重、汇总及字符串匹配等，如果查询的数据涉及多个表，还需要要对表进行连接，本文就来说说MySQL中常用的查询...【详细内容】

2021-12-06　　笨鸟学数据分析　　　　Tags:MySQL 　点击:(19)　　评论:(0)　　加入收藏

15个MySQL常用基本SQL语句

在学习SQL语句之前，首先需要区分几个概念，我们常说的数据库是指数据库软件，例如MySQL、Oracle、SQL Server等，而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】

2021-11-24　　笨鸟学数据分析　　　　Tags:SQL语句　点击:(23)　　评论:(0)　　加入收藏

用 MySQL 实现分布式锁，你听过吗？

概述以前参加过一个库存系统，由于其业务复杂性，搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说，有定时任务域xx.cron，和SystemA域...【详细内容】

2021-11-05　　Java云海　　　　Tags:分布式锁　点击:(31)　　评论:(0)　　加入收藏

MySQL高级SQL语句

MySQL的进阶查询一、按关键字排序使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序，默认排序方式【升序是从小到大】DESC:降序【降序是从大到小】ORDER BY的...【详细内容】

2021-11-05　　Java热点　　　　Tags:SQL语句　点击:(27)　　评论:(0)　　加入收藏

推荐资讯

远程软件发展迅猛，ToDe	倒计时！企业QQ即将下架
极简Windows11与iPhon	iPhone信号问题，花10元
惊人数据：App Store中4	个人所得税递延纳税报
非常实用的 Python 库	等离子电视技术先进，为

无相关信息