MySQL日记-子查询

/*
子查询
出现在其他语句中的select语句，称为子查询或内查询

外部的查询语句，称为主查询

分类：
按子查询出现的位置
select后面：
只支持标量子查询

from后面：
支持表子查询

(重点)where或having后面：
标量子查询  √
列子查询    √
行子查询

exists(存在)后面 称为（相关子查询）：
表子查询

按结果集的行列数不同
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集只有一行多列)
表子查询(结果集一般为多行多列)

*/

/*

#where或having后面：
#标量子查询 一行一列
#列子查询 一列多行
#行子查询

#特点：
1.子查询放在小括号内
2子查询一般放在条件内侧
3标量子查询一般搭配着单行操作符使用（单行操作符：> < = <= >= <>）
列子查询一般搭配着多行操作符使用(in any/some all)

*/

#标量子查询

#谁的工资比abel高
#①查询abel的工资
SELECT salary
FROM employees
WHERE last_name=‘abel’;

#②查询员工信息，满足salary>①

SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name=‘abel’
);

#案例2:返回job_ id与141号员工相同，salary比143号员工多的员工姓名，job_ id和工资

SELECT job_id
FROM employees
WHERE employee_id=141;

SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
)
AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143
);

#案例3:返回公司工资最少的员工的last_ name, job_ _id和salary

SELECT last_name, job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
)

#案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id=50;

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);

#列子查询（多行查询）
/*
in 等于列表中的任意一个
any/some 和子查询返回的一个值比较 a>any(10,8) a大于8，10任意一个就行（min）
all 和子查询返回的所有值比较 （max）

*/

#案例1:返回location_ id是1400或1700的 部门中的所有员工姓名
#1.查询location_id时1400或1500的部门编号
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700);
#2.查询员工姓名，要求部门编号是1列表中的一个

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

SELECT salary
FROM employees
WHERE job_id=‘it_prog’

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY
(
SELECT salary
FROM employees
WHERE job_id=‘it_prog’
);

#案例3:返回其它部门中比job_ id为‘IT_ PROG’ 部门所有工资都低的员工的员工号、姓名、job_ id以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL
(
SELECT salary
FROM employees
WHERE job_id=‘it_prog’
);

#行子查询

#查询员工编号最小且工资最高的员工信息
#查询最小员工编号
SELECT MIN(employee_id)
FROM employees
#查询最高工资
SELECT MAX(salary)
FROM employees

#查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
) AND salary = (
SELECT MAX(salary)
FROM employees
);

SELECT *
FROM employees
WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);

#放在select后面

#查询每个部门的员工个数

SELECT d.* ,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
) 人数
FROM departments d

#查询员工号102的部门名

SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON e.department_id=d.department_id
WHERE e.employee_id=102
);

#from后面

#查询每个部门的平均工资的工资等级

#查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

SELECT avg_d.*,grade_level
FROM (
SELECT AVG(salary) asl,department_id
FROM employees
GROUP BY department_id
) avg_d
INNER JOIN job_grades
ON avg_d.asl BETWEEN lowest_sal AND highest_sal;

#放在exists(布尔类型)后面(相关子查询)

SELECT EXISTS(SELECT last_name FROM employees);

#查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE e.department_id=e.department_id
);

#使用in
SELECT d.department_name
FROM departments d
WHERE d.department_id IN (
SELECT department_id
FROM employees
);

#查询没有女朋友的男性信息

SELECT b.*
FROM boys b
WHERE b.id NOT IN(
SELECT boyfriend_id
FROM beauty
);

#查询各个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN(
SELECT AVG(salary) asl,department_id di
FROM employees
GROUP BY department_id
) ad
ON e.department_id=ad.di
WHERE salary>ad.asl;