mysql自学笔记五（Navicat Premium 15）

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

分类：
按子查询出现的位置：
				select后面：
								仅仅支持标量子查询
								
				from后面：
								支持表子查询
								
				where或having后面：
								标量子查询
								列子查询
								
								行子查询
				exists后面（相关子查询）
								表子查询
								
按结果集的行列数不同：
				标量子查询（结果只有一行一列）
				列子查询（结果集只有一列多行）
				行子查询（结果集有一行多列）
				表子查询（结果集一般为多行多列）
*/

#一、where或having后面
/*

1、标量子查询（单行子查询）
2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：
①子查询都放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= <>
列子查询，一般搭配着多行操作符使用
in any some all
④子查询的执行优先于主查询执行，主查询的条件

*/

#1、标量子查询（单行子查询）
#谁的工资比Abel高？
SELECT * FROM employees WHERE salary >( SELECT salary FROM employees WHERE last_name = 'Abel' );

#返回job_id与141号员工相同，salary比143号员工多的员工 姓名 job_id 和工资
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 
	);

#返回公司工资最少的员工的last_name、job_id和salary

select last_name,job_id,salary from employees where salary=(select min(salary) from employees);

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

SELECT
	department_id,
	min( salary ) 
FROM
	employees 
GROUP BY
	department_id 
HAVING
	min( salary )>(
	SELECT
		min( salary ) 
	FROM
		employees 
	WHERE
		department_id = 50 
	);
	
	
#非法使用标量子查询
SELECT department_id, min( salary ) FROM employees GROUP BY department_id HAVING min( salary )>( SELECT salary FROM employees WHERE department_id = 50 );


#2、列子查询（多行子查询）
	
#返回location_id是1400或1700的部门中所有员工的姓名
SELECT
	last_name 
FROM
	employees 
WHERE
	department_id IN (
	SELECT
		department_id 
	FROM
		departments 
	WHERE
	location_id IN ( 1400, 1700 ));

#返回其他部门中比job_id为IT_PROG部门任一工资低的员工的：工号、姓名、job_id以及salary
SELECT
	employee_id,
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary < ANY ( SELECT MAX( salary ) FROM employees WHERE job_id = 'IT_PROG' ) 
	AND job_id <> 'IT_PROG';
	
#返回其他部门中比job_id为IT_PROG部门所有工资低的员工的：工号、姓名、job_id以及salary
SELECT
	employee_id,
	last_name,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary < all ( SELECT min( salary ) FROM employees WHERE job_id = 'IT_PROG' ) 
	AND job_id <> 'IT_PROG';

#行子查询（结果集一行多列或多行多列）
#查询员工编号最小并且工资最高的员工信息
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 count(*),department_id from employees group by department_id;

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 d.department_id=e.department_id where e.employee_id=102) 部门名;

#三、from后面
/*
	将子查询结果充当一张表，要求必须起别名
*/
#查询每个部门的平均工资的工资等级
select grade_level,department_id from (select avg(salary) a,department_id from employees group by department_id) s inner join job_grades g on s.a between g.lowest_sal and g.highest_sal;

#四、exists后面（相关子查询）
select exists(select employee_id from employees where salary=30000);

#查询有员工名部门名
select department_name from departments d where EXISTS(select * from employees e where d.department_id=e.department_id);

#查询一下没用女朋友的男神信息
1、
SELECT
	bo.* 
FROM
	boys bo 
WHERE
	bo.id NOT IN ( SELECT boyfriend_id FROM beauty );
2、
SELECT
	bo.* 
FROM
	boys bo 
WHERE
	NOT EXISTS ( SELECT boyfriend_id FROM beauty b WHERE bo.id = b.boyfriend_id );
	
	
#查询和 Zlotkey相同部门的员工姓名和工资
SELECT last_name, salary FROM employees WHERE department_id =( SELECT department_id FROM employees WHERE last_name = 'Zlotkey' );

#查询工资比公司平均工资高的员工的员工号、姓名、工资
select employee_id,last_name,salary from employees WHERE salary>(select avg(salary) from employees);
	
#查询各部门工资比本部门平均工资高的员工的员工号、姓名和工资
1、 SELECT
employee_id,
last_name,
salary,
e.department_id 
FROM
	employees e,(
	SELECT
		avg( salary ) a,
		department_id 
	FROM
		employees 
	GROUP BY
		department_id 
	) ea 
WHERE
	salary > a 
	AND e.department_id = ea.department_id;

2、 SELECT
employee_id,
last_name,
salary,
e.department_id 
FROM
	employees e
	INNER JOIN ( SELECT avg( salary ) a, department_id FROM employees GROUP BY department_id ) ea ON e.department_id = ea.department_id 
WHERE
	salary > a;

#查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT
	employee_id,
	last_name 
FROM
	employees 
WHERE
	department_id IN ( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' );

#查询在部门的location_id为1700的部门工作的员工的员工号
1、 SELECT
employee_id 
FROM
	employees e
	INNER JOIN ( SELECT department_id FROM departments WHERE location_id = 1700 ) d ON e.department_id = d.department_id;

2、 SELECT
employee_id 
FROM
	employees e 
WHERE
	department_id = ANY ( SELECT department_id FROM departments WHERE location_id = 1700 );

#查询管理者是king的员工的姓名和工资
SELECT
	last_name,
	salary 
FROM
	employees e 
WHERE
	manager_id IN ( SELECT employee_id FROM employees WHERE last_name = 'k_ing' );

#查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名

SELECT
	CONCAT( first_name, '.', last_name ) '姓.名' 
FROM
	employees 
HAVING
	max( salary );