MySql--子查询

子查询

按结果集的行列数不同：

• 标量子查询（结果只有一行一列）

• 列子查询（结果集只有一列多行）

• 行子查询（结果集只有一行多列）

• 表子查询（结果集一般为多行多列）

按子查询出现的位置：

• select后面：仅仅支持标量子查询；

• from后面：支持表子查询；

• where或having后面：标量子查询，列子查询，行子查询； ★

• exists后面（相关子查询）：表子查询 （返回的是true或者false，true再执行外查询，false执行就是空的）

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


#案例一:谁的工资比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
);


#案例三：查询公司工资最少的员工的last_name,job_id和salary；
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 department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id =50
);

 

#2.列子查询（一列多行）
/*
使用多行比较操作符
IN / NOT IN
ANY / SOME 
ALL
*/
#案例一；返回locating_id是1400或1700的部门中所有员工姓名
SELECT last_name 
FROM employees 
WHERE department_id IN (
	SELECT department_id
	FROM departments
	WHERE location_id = 1400 OR location_id = 1700
);



#案例二：返回其他工种中比  job_id为'IT_PROG'工种任一工资低  的员工号，姓名，job_id以及salary；
SELECT salary
FROM employees
WHERE job_id ='IT_PROG';

SELECT last_name ,job_id,salary
FROM employees
WHERE salary < ANY(
	SELECT salary
	FROM employees
	WHERE job_id ='IT_PROG'
)AND job_id<>'IT_PROG';
#或
SELECT last_name ,job_id,salary
FROM employees
WHERE salary <(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id ='IT_PROG'
)AND job_id<>'IT_PROG';


#案例三：返回其他工种中比job_id为'IT_PROG'工种  所有  工资低的员工号，姓名，job_id以及salary；
SELECT last_name ,job_id,salary
FROM employees
WHERE salary < ALL(
	SELECT salary
	FROM employees
	WHERE job_id ='IT_PROG'
)AND job_id<>'IT_PROG';
#或
SELECT last_name ,job_id,salary
FROM employees
WHERE salary <(
	SELECT MIN(salary)
	FROM employees
	WHERE job_id ='IT_PROG'
)AND job_id<>'IT_PROG';





#3.行子查询（一行多列）
#案例一：查询员工编号最小并且工资最高的员工信息
SELECT MIN(employee_id)
FROM employees


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

/*
二.select后面
仅仅支持标量子查询
*/
#案例一：查询每个部门的员工个数
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id;
#或
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees  AS e
	GROUP BY department_id
	HAVING e.department_id = d.`department_id`
)
FROM departments AS d;
#或
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees  AS e
	WHERE e.department_id = d.`department_id`
)
FROM departments AS d;


#案例二：查询员工号 =102 的部门名；
SELECT department_name
FROM departments
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE employee_id = 102
);

/*
三.from 后面（一个新表）

*/
#案例一：查询每个部门的平均工资的工资等级                                                        
/*


四、exists 后面（相关子查询）特殊点：先查询父查询；
exists（。。。。。）结果是0或者1，bool值；
*/
#案例一：查询有员工的部门名
SELECT department_name
FROM departments AS d
WHERE EXISTS(
	SELECT *
	FROM employees AS e
 	WHERE e.`department_id` =d.`department_id`;
);
#或
SELECT department_name
FROM departments AS d
WHERE d.`department_id`IN(
	SELECT department_id
	FROM employees
);
#案例二：查询没有女朋友的男神信息；
SELECT bo.*
FROM boys AS bo
WHERE NOT EXISTS(
	SELECT  boyfriend_id
	FROM beauty AS b
	WHERE bo.`id` = b.`boyfriend_id`
);
#或
SELECT bo.*
FROM boys AS bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty AS b
); 
#测试题
#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';

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

#2.查询工资比工资平均工资高的员工的员工号，姓名和工资；
SELECT AVG(salary)
FROM employees;

SELECT employee_id,last_name,salary
FROM employees
WHERE salary >(
	SELECT AVG(salary)
	FROM employees
);

#3.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资；
SELECT department_id ,AVG(salary)
FROM employees
GROUP BY department_id;

SELECT employee_id,last_name ,salary
FROM employees AS e
INNER JOIN (
	SELECT department_id,AVG(salary) AS ag
	FROM employees
	GROUP BY department_id
) AS d
ON e.`department_id` = d.department_id
WHERE salary >ag;

#4.查询 姓名中包含字母u的员工  在相同部门  的员工的员工号和姓名；
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%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 DISTINCT department_id
FROM departments
WHERE location_id = 1700;

SELECT *
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
);
#6.查询管理者是K_ing的员工姓名和工资；
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing';

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 MAX(salary)
FROM employees;

SELECT CONCAT(first_name,last_name) AS '姓.名'
FROM employees
WHERE salary =(
	SELECT MAX(salary)
	FROM employees
);