MySQL基础笔记(4)-子查询_mysql 子查询练习-优快云博客

MySql的子查询是多表查询的一个重要组成部分，常常和连接查询一起使用，是多表查询的基础，本文将带着大家学习常见的子查询相关知识以及一些例子用以巩固所学。

一、什么是子查询

当一个查询是另一个查询的条件时，称之为子查询。子查询可以使用几个简单命令构造功能强大的复合命令。子查询最常用于SELECT-SQL命令的WHERE子句中。子查询是一个 SELECT 语句，它嵌套在一个 SELECT、SELECT…INTO 语句、INSERT…INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。

二、建表语句

列出子查询练习需要用到的sql语句

-- /创建部门表/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);

-- /创建雇员表/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);

-- /创建工资等级表/
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);

-- /插入dept表数据/
INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

-- /插入emp表数据/
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);

-- /插入salgrade表数据/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

三、子查询的使用格式

SELECT xxx WHERE XX =  (SELECT xx FROM XX)...

四、子查询练习

1、查出教研部所有员工的姓名和工资

分析：需要查出的字段有员工姓名和工资，这两个字段都由emp表提供，所以只需要从emp中提取数据，但是emp表中只有一个部门编号deptno字段，所以还需要查出教研部的部门编号，这里是一个条件查询。

SELECT e.ename,e.sal FROM emp e WHERE e.deptno = (SELECT d.deptno FROM dept d WHERE d.dname = '教研部');

2、查出每个部门的名称以及所包含的员工数

分析：要查出部门名称和员工数，我的做法是使用内连接，让部门表和员工表进行连接，然后筛选出员工部门ID和部门表ID相等的数据，筛选出部门名和使用COUNT()函数，然后根据部门名分组，这是最好的方法

SELECT d.dname ,COUNT(*)  FROM dept d INNER JOIN emp e ON d.deptno = e.deptno GROUP BY d.dname;

有朋友可能会问，为什么不用左外连接而用内连接呢？需求中不是要查询所有的部门吗？如果使用左外连接是可以查出所有的部门名称，但是假如没有员工与该部门相对应的话就会出现假数据，像这一条数据：

这条数据仍然会被查询出来，计入COUNT（）函数中，显示该部门有1条数据，即1个员工，这是错误的

3、查出至少有四个个员工的部门。显示部门编号、部门名称、部门位置、部门人数

这个Sql有难度，是第2题的升级版，需要查询出特定字段并且封装成临时表temp进行查询

SELECT dept.dname,temp.num FROM dept INNER JOIN (SELECT deptno,COUNT(*) num FROM emp GROUP BY deptno) temp ON dept.deptno=temp.deptno WHERE temp.num >= 4 ;

4、列出薪金比谢逊高的所有员工

SELECT e1.ename FROM emp e1 WHERE e1.sal >(SELECT e2.sal FROM emp e2  WHERE e2.ename = '谢逊');

5、列出所有员工的姓名及其直接上级的姓名

因为是查询所有员工,所以要用左连接,不能用内连接

SELECT e1.ename 员工姓名,e2.ename 上级姓名 FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr = e2.empno;

6、列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称