子查询

#1.where或having后面

#单行，多行，多列多行

/*

（）内 条件的右边

*/

#谁的工资比abel高

SELECT salary
FROM employees
WHERE last_name= 'Abel';

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

#返回job_id与141员工相同，salary比143员工高的员工姓名和job id和工资

SELECT job_id
FROM employees
WHERE employee_id=141;

SELECT salary
FROM employees
WHERE employee_id=143;

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) ;

#查询工资最少的员工的名字，job id和工资

SELECT MIN(salary)
FROM employees

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

#查询最低工资大于50号部门的最低工资的部门id和最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id=50

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

#多行子程序

/*

in | not in

any | some 等同于min

all 等同于 max

*/

#返回location id是1400或1700的部门中的所有员工姓名

SELECT DISTINCT	  department_id
FROM departments
WHERE location_id IN (1400,1700)

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

SELECT
last_name,
department_id
FROM
employees
WHERE manager_id =ANY
(SELECT DISTINCT
department_id
FROM
departments
WHERE location_id IN (1400, 1700)) ;

#其他部门比job id 为IT_proG部门的任意工资低的员工的工资，姓名

SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'

SELECT salary,last_name,job_id
FROM employees
WHERE salary < SOME(SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG')
AND job_id <> 'IT_PROG';

#其他部门比job id 为IT——proG部门的所有工资低的员工的工资，姓名

SELECT salary,last_name,job_id
FROM employees
WHERE salary < ALL(SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG')
AND 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
d.**,
(SELECT
COUNT(**)
FROM
employees e
WHERE e.department_id = d.department_id) 个数
FROM
departments d ;

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

查询员工号=102的部门名

SELECT
d.department_name
FROM
departments d,
employees e
WHERE e.employee_id = 102
AND e.department_id = d.department_id ;

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

放在from后面

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

#平均工资
SELECT AVG(salary)
FROM employees
GROUP BY department_id;

SELECT
av_dep.平均工资,
jg.grade_level,
av_dep.部门号
FROM
(SELECT
AVG(salary) 平均工资,
department_id 部门号
FROM
employees
GROUP BY 部门号) av_dep
JOIN job_grades jg
ON av_dep.平均工资 BETWEEN jg.lowest_sal
AND jg.highest_sal ;

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

SELECT
bo.*
FROM
boys bo
WHERE NOT EXISTS
(SELECT
boyfriend_id
FROM
beauty b
WHERE bo.id=b.boyfriend_id) ;

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

#myemployees中每个部门比平均工资多的员工的信息

#平均工资/部门

SELECT AVG(salary),department_id FROM employees
GROUP BY department_id;

SELECT
*
FROM
employees e
JOIN
(
SELECT
AVG(salary) av,
department_id
FROM
employees
GROUP BY department_id
) av_d
ON av_d.department_id = e.department_id
WHERE e.salary > av_d.av ;

#查询所有manager的信息

SELECT DISTINCT manager_id
FROM employees;

SELECT
*
FROM
employees
WHERE employee_id IN
(SELECT DISTINCT
manager_id
FROM
employees) ;

#查询平均工资最高的部门manager的信息信息

#平均工资最高的部门id
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
ORDER	BY AVG(salary) DESC
LIMIT 1;

SELECT d.manager_id
FROM departments d
JOIN (
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
ORDER	BY AVG(salary) DESC
LIMIT 1
)av_s
ON d.department_id=av_s.department_id

SELECT e.*
FROM employees e
WHERE e.employee_id=(SELECT d.`manager_id`
FROM `departments` d
JOIN (

	SELECT `department_id`,AVG(salary)
	FROM `employees`
	GROUP BY department_id
	ORDER	BY AVG(salary) DESC
	LIMIT 1

)av_s
ON d.`department_id`=av_s.`department_id`
);

SELECT e.*
FROM employees e
JOIN departments d
ON d.manager_id=e.employee_id
WHERE d.department_id=(SELECT `department_id`
FROM `employees`
GROUP BY `department_id`
ORDER	BY AVG(salary) DESC
LIMIT 1
);
0%