子查询

含义：出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称主查询或外查询

分类：按照子查询出现的位置

SELECT 后面：

 仅支持标量子查询

FROM 后面：

 支持表子查询

WHERE 或者 having 后面：

 支持标量子查询
 列子查询
 行子查询

EXISTS 后面（相关子查询）

 表子查询

按结果集的行列数不同：

标量子查询：结果集只有一行一列
列子查询：结果集只有一列多行
行子查询：结果集有一行多列
表子查询：结果集一般为多行多列

WHERE 或者 having 后面：

 1.支持标量子查询（单行子查询）
 2.列子查询（多行子查询）
 3.行子查询（多列多行）

特点：

1.子查询都会放在（）内
2.子查询一般放在条件的右侧
3.标量子查询，一般搭配着单行操作符使用>< = >= <=
4.列子查询，一般搭配多行操作符使用IN 、ANY/SOME、ALL

一.标量子查询

1.谁的工资比Abel高？

SELECT salary FROM employees e 
WHERE  salary> (
SELECT salary 
FROM employees e 
WHERE last_name='Abel')

2.返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name,job_id,salary FROM employees e 
WHERE job_id=(
            SELECT job_id 
            FROM employees e 
            WHERE employee_id=141 
            )
and salary>(
            SELECT salary 
            FROM employees e 
            WHERE employee_id=143
            )

3.返回公司工资最少的员工的last_name,job_id和salary

SELECT min(salary) FROM employees e 
SELECT last_name,job_id,salary FROM employees e 
WHERE salary = (SELECT min(salary) FROM employees e )

4.查询最低工资大于50号部门的最低工资的部门id和其最低工资

SELECT min(salary),department_id FROM employees e 
GROUP BY department_id
HAVING min(salary)>
(SELECT min(salary) FROM employees e
WHERE department_id=50)

二.列子查询（多行子查询）

1.返回多行
2.使用多行比较操作符
题目
1.返回部门中location_id是1400或者1700的所有员工姓名

SELECT  last_name FROM employees e 
WHERE department_id  IN (
SELECT department_id FROM departments d 
WHERE location_id IN(1400,1700))

2.返回其他工种中比job_id='IT_PROG'任一工资低的员工号，姓名，job_id,salary

SELECT employee_id ,last_name ,job_id ,salary  FROM employees e
WHERE salary< (
SELECT DISTINCT  max(salary) FROM employees e 
WHERE job_id  IN ('IT_PROG')
)AND job_id !='IT_PROG'

或者

SELECT employee_id ,last_name ,job_id ,salary  FROM employees e
WHERE salary<ANY(
SELECT DISTINCT salary FROM employees e 
WHERE job_id  IN ('IT_PROG')
)AND job_id !='IT_PROG'

3.返回其他工种中比job_id='IT_PROG'所有工资低的员工号，姓名，job_id,salary

SELECT employee_id ,last_name ,job_id ,salary  FROM employees e
WHERE salary <all(
SELECT salary FROM employees e 
WHERE job_id='IT_PROG'
)
AND job_id !='IT_PROG'

或者

SELECT employee_id ,last_name ,job_id ,salary  FROM employees e
WHERE salary <(
SELECT min(salary) FROM employees e 
WHERE job_id='IT_PROG'
)
AND job_id !='IT_PROG'

三.行子查询（结果是一行多列，或者一列多行）使用少

1.查询员工编号最小，并且工资最高的员工信息

SELECT * FROM employees e 
WHERE employee_id=(
SELECT min(employee_id) FROM employees e 
)AND salary=(SELECT  max(salary) FROM employees e)

使用行子查询

SELECT * FROM employees e 
WHERE (employee_id,salary)=(
SELECT min(employee_id), max(salary) FROM employees e
)

二，放在select 后面(仅仅支持标量子查询)
1.查询每个部门员工个数

SELECT d.*,(
SELECT count(*)
FROM employees e 
WHERE d.department_id=e.department_id
) 个数
FROM departments d

2.查询员工号=102的部门名

SELECT (
SELECT d.department_name FROM employees e 
JOIN departments d 
WHERE  e.department_id=d.department_id
AND e.employee_id=102
) 部门名

三.FROM 后面(将子查询的结果当作一张表，要求必须起别名)
1.查询每个部门平均工资和工资等级

SELECT avg_depart.ag,jg.grade_level,department_id
FROM (
SELECT avg(salary) ag,department_id 
FROM employees e ,job_grades
GROUP BY department_id 
) avg_depart
INNER JOIN job_grades jg 
ON avg_depart.ag BETWEEN lowest_sal AND highest_sal 

四.放到exists的子查询

语法：
exists(完整的查询语句)
结果是1或者0

案例1:查询有员工的部门名

SELECT department_name  FROM departments d 
WHERE EXISTS (
 SELECT last_name,d.department_name  FROM employees e 
 WHERE  e.department_id =d.department_id 
 )

1.查询没有女朋友的男生信息

使用IN
SELECT * FROM boys o
WHERE o.id NOT IN  (
SELECT boyfriend_id FROM beauty e
WHERE e.boyfriend_id =o.id
)

使用EXISTS
SELECT * FROM boys o
WHERE NOT EXISTS  (
SELECT boyfriend_id FROM beauty e
WHERE e.boyfriend_id =o.id
)

测试题
1.查询和Zlokey相同部门的员工姓名和工资

SELECT last_name,salary,department_id FROM employees e 
WHERE department_id=(
SELECT department_id FROM employees e 
WHERE last_name='Zlotkey'
)

2.查询工资比公司平均工资高的员工的员工号，姓名，工资

SELECT job_id ,last_name ,salary  FROM employees e 
WHERE salary>(SELECT avg(salary) FROM employees e 
)

3.查询各部门中工资比本部门平均工资高的员工和员工号，姓名，工资(难)

SELECT employee_id  ,last_name ,salary  
FROM employees e 
INNER JOIN (
SELECT avg(salary)ag,department_id  
FROM employees 
GROUP BY department_id 
) AS ag_dep
ON e.department_id =ag_dep.department_id
WHERE salary >ag_dep.ag

方式二：
WITH tmp1 as(
SELECT  avg(salary) avg_salary,e.department_id de
FROM  employees e 
GROUP BY de
),
tmp2 as(
SELECT * FROM employees a 
inner JOIN tmp1
ON tmp1.de= department_id
WHERE avg_salary<salary
)
SELECT employee_id ,last_name ,salary  FROM  tmp2

4.查询和姓名中包含字母u的员工 所在相同部门的员工的员工号和姓名

SELECT employee_id,last_name 
FROM employees 
WHERE department_id in(
SELECT DISTINCT  department_id FROM employees 
WHERE last_name LIKE '%u%'
)

5.查询在部门为location_id 为1700的部门工作的员工的员工号

SELECT employee_id ,department_id FROM employees e 
WHERE department_id =any (
SELECT department_id  FROM departments
WHERE location_id=1700
)

6.查询管理者是king的员工姓名和工资

SELECT last_name,salary  FROM  employees 
WHERE manager_id IN (
SELECT employee_id   
FROM  employees 
WHERE last_name='K_ing' 
)

7.查询工资最高的员工姓名，要求first_name,last_name 显示在一列,列名是姓名

SELECT  CONCAT(first_name,' ',last_name) FROM employees e 
WHERE salary =(
SELECT max(salary) 
FROM employees e 
)

四、分页查询

应用场景：当要显示的数据，一页显示不全，需要分页显示

语法：LIMIT offset,SIZE;

offset:要显示的起始索引（起始索引从0开始）

SIZE：要显示的条目个数

公式：要显示的页数page,每页的条目数size

SELECT 查询列表
FROM 表
LIMIT （page-1）*SIZE,SIZE;

1.查询前5条员工信息

SELECT * FROM  employees e 
LIMIT 0,5

2.查询第11-第15条员工信息

SELECT * FROM  employees e 
LIMIT 13,15

1.查询工资最低的员工信息

SELECT * FROM  employees e 
WHERE salary=(
SELECT min(salary) FROM employees e 
)

2.查询平均工资最低的部门信息

SELECT  d.*
FROM departments d 
WHERE d.department_id =(
SELECT department_id di FROM employees e 
GROUP BY department_id
HAVING avg(salary)=(
SELECT min(avg_sa.ag)FROM (
SELECT avg(salary) ag,department_id  
FROM employees e 
GROUP BY department_id  
) avg_sa
) 
)

用连接查询的方式来做

SELECT *  FROM  departments d 
JOIN (
SELECT department_id di FROM employees e 
GROUP BY department_id 
ORDER BY avg(salary) asc 
LIMIT 1
) avg_dep
ON d.department_id =avg_dep.di

或者：

SELECT *  FROM  departments d 
WHERE department_id= (
SELECT department_id di FROM employees e 
GROUP BY department_id 
ORDER BY avg(salary) asc 
LIMIT 1
) 

3.查询查询平均工资最低的部门信息和该部门的平均工资

SELECT d.* ,avg_de.ag FROM departments d 
JOIN (
SELECT avg(salary) ag ,department_id  FROM employees e 
GROUP BY department_id 
ORDER BY ag ASC
LIMIT 1
)avg_de
ON d.department_id =avg_de.department_id

4.查询平均工资最高的job信息

SELECT avg(salary),job_id  FROM employees e 
GROUP BY job_id
ORDER BY avg(salary) DESC 
LIMIT 1

5.查询平均工资高于公司平均工资的部门有哪些

SELECT avg(salary) ag,department_id  FROM  employees e
GROUP BY department_id
HAVING ag>(
SELECT avg(salary) FROM  employees a 
)

6.查询公司所有管理人员的员工编号

SELECT employee_id FROM employees e2
WHERE e2.employee_id IN (
SELECT DISTINCT  manager_id  FROM employees
)

7.各部门中，最高工资中最低的部门，最低工资是多少

SELECT max(salary) ,department_id FROM employees e2 
GROUP BY department_id
ORDER BY max(salary) asc
LIMIT 1

8.查询平均工资最高的部门的manager 的详细信息

SELECT employee_id ,last_name ,email ,salary  FROM employees e2 
WHERE department_id =(
SELECT  department_id  FROM (
SELECT avg(salary) ag,department_id  FROM employees e 
GROUP BY department_id
ORDER BY avg(salary) DESC
LIMIT 1) max_sal
)
AND ISNULL(manager_id) 

测试题目
1.查询参加考试学生中，每个学生的平均分、最高分

SELECT avg(score),min(score) ,studentno  FROM `result` r 
GROUP BY studentno 

2.查询姓张的每个学生的最低分大于60的学号

SELECT s.studentno FROM student s 
 inner JOIN `result` r
ON s.studentno =r.studentno
WHERE  s.studentname like'%张_%'
GROUP BY studentno
HAVING  min(score )>60

4.查询生日在1998-1-1的学生姓名，专业名

SELECT  studentname,majorname FROM  student s2 
JOIN major m 
ON m.majorid=s2.majorid
WHERE DATEDIFF(borndate,'1998-1-1') >0
5.查询每个专业的男生人数和女生人数
SELECT count(*) 个数 ,sex,majorid
FROM student s
GROUP BY sex,majorid

6.查询专业和张翠山一样的学生的最低分

SELECT  * FROM  (SELECT studentno FROM student s2 
WHERE majorid=(
SELECT majorid FROM  student s2 
WHERE studentname='张翠山')
) stu_no
INNER JOIN `result` r 
ON stu_no.studentno =r.studentno
ORDER BY  r.score ASC 
LIMIT 1

7.查询大于60分的姓名，密码，专业

SELECT majorname,studentname ,loginpwd FROM major m  
 JOIN (
 SELECT studentname ,loginpwd ,majorid FROM student s 
INNER JOIN `result` r 
ON s.studentno =r.studentno 
WHERE r.score >60
 ) b1
 ON m.majorid =b1.majorid

或者:

SELECT majorname,studentname ,loginpwd 
FROM student s 
JOIN result r ON s.studentno =r.studentno 
JOIN major m ON  m.majorid =s.majorid 
WHERE score>60

8.按照邮箱位数分组，查询每组学生个数

SELECT count(*) FROM student s 
GROUP  by  LENGTH (email)

9.查询学生名，专业名，分数

SELECT studentname ,majorname,score FROM student s
JOIN `result` r ON s.studentno =r.studentno 
JOIN major m ON s.majorid =m.majorid 

10.查询哪个专业没有学生

SELECT * FROM major m 
left JOIN  student s 
ON s.majorid =m.majorid 
WHERE s.studentno IS NULL

或：

SELECT * FROM student s 
right JOIN  major m 
ON s.majorid =m.majorid 
WHERE s.studentno IS NULL

11.查询没成绩的学生人数

SELECT * FROM student s 
left JOIN `result` r 
ON  r.studentno =s.studentno 
WHERE r.score  IS NULL