MySQL多表查询讲解
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 [条件]unionselect * from 表1 right join 表2 on [条件]6.2 实战
以全连接的形式查询部门和员工数据
select * from employee left join dept on employee.dept_id = dept.did unionselect * 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 where 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 setselect 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]endfrom 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 s1when [value1] then [result1]when [value2] then [result2]when [value3] then [result3]else [resultOther] endfrom 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) 赞 (0)