数据库与Mysql - 5

子查询

含义

 	 出现在其他语句中的SELECT语句,称为子查询或内查询
 	 内部嵌套其他SELECT语句的查询,称为主查询或父查询
 	 #示例:
 	 SELECT first_name from employees  
 	 WHERE department_id IN(
 	 			SELECT department_id from departments
 	 			where location_id =1700
 	 )；
 	 括号内的为子查询.括号外的为主查询;

分类

按子查询出现的位置:

	select后面:
		仅仅标量子查询
	from后面:
		支持表子查询
	where或having后面★:
		标量子查询★
		列子查询  ★
		行子查询
	exist后面:
		表子查询

按结果集的行列数不同:

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

详细

1.where或having后面

1.标量子查询(单行子查询)
2.列子查询(多行子查询)
3.行子查询(多列多行)
特点:
①:子查询放在小括号内
②:子查询一般放在条件的右侧
③:标量子查询,一般搭配着单行操作符(> < >= <= = <>)使用
  列子查询,一般搭配着多行操作符(IN ANY/SOME ALL)使用
④ 子查询的执行优先于主查询
  主查询会用到子查询返回的结果

案例 - 标量子查询

案例1:谁的工资被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 和工资

#① 查询141号员工的job_id

SELECT job_id
from employees
where employee_id = 141
#② 查询143号员工的salary

SELECT salary
From employees
WHERE employee_id = 143

#③ 查询员工的姓名,job_id 和工资 ,要求job_id =① 且 salary > ②

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

#② 查询last_name,job_id和salary,要求salary=①

select last_name,job_id,salary
from employees
where salary=(
		SELECT MIN(salary)
		FROM employees
)

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

#① 查询50号工资的最低工资

select MIN(salary)
from employees
where department_id = 50

#② 查询每个部门的最低工资

select MIN(salary),department_id
from employees
Group by department_id

#③筛选

select MIN(salary),department_id
from employees
Group by department_id
Having MIN(salary)>(
		select MIN(salary)
		from employees
		where department_id = 50
)

非法使用标量子查询

select MIN(salary),department_id
from employees
Group by department_id
Having MIN(salary)>(
		select salary
		from employees
		where department_id = 50
)
##	MIN为单行操作符后面的子查询应该为标量子查询,若改为列子查询就会报错

案例 - 列子查询(多行子查询)

多行比较操作符

操作符	含义
IN/NOT IN	等于列表中的任意一个
ANY/SOME	和子查询返回的某一个值比较
ALL	和子查询返回的所有值比较

案例1:返回location_id是1400或1700的部门中的所有员工姓名:

#① 查询location_id 是1400或1700的部门编号

select distinct department_id
from department
whrere location_id IN(1400,1700)

#② 查询员工姓名,要求部门号是①列中的某一个

select last_name
from employees
WHERE department_id IN(
		select distinct department_id
		from department
		whrere location_id IN(1400,1700)
)

案例2:返回其他部门中比job_id为'IT_PRIG'部门任一工资低的员工的:工号 姓名 job_id salary

#① 查询job_id 为 IT_PRIG 部门任一工资

select distinct salary
from employees
where job_id = 'IT_PROG';

#② 查询员工号 姓名 job_id 以及salary,salary＜①的任一个

select last_name,employee_Id,job_id,salary
from employees
where salary<ANY(
	select distinct salary
	from employees
	where job_id = 'IT_PROG'
)AND job<>'IT_PROG';

案例3:返回其他部门中比job_id为'IT_PROG'部门所有工资都低的员工的 员工号 姓名 job_id 以及salary

select last_name,employee_Id,job_id,salary
from employees
where salary<ALL(
	select distinct salary
	from employees
	where job_id = 'IT_PROG'
)AND job<>'IT_PROG';

案例-行子查询(结果集一行多列或多行多列)

案例1:查询员工编号最小且工资最高的员工信息
使用行子查询前:

#① 查询最小的员工编号

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

where后子查询结束

---

2.Select 后跟子查询

–仅仅支持标量子查询—

案例1:查询每个部门的员工个数

select d.*,(
	select count(*)
	from employees e
	where e_department_id = d.department_id
)	个数
from department d;

案例2:查询员工号=102的部门名

select (
		select department_name
		from departments d
		inner join employees e
		on d.department_id = e.department_id
		where e.employee_id = 102
)

***SELECT后跟子查询***

---

3.FROM后跟子查询

将子查询结果充当一张表,要求必须起别名

案例1:查询每个部门的平均等级

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

select AVG(salary),departmen_id
from employees
GROUP BY department_id

#连接①的结果集和job_grade表,筛选条件平均工资 between lowest_sal and highest_sal

select	ag,department_id,grade_level
from(
	select AVG(salary),departmen_id
	from employees
	GROUP BY department_id
)ag_dep
inner join job_grade g
on	ag_dep.ag	between lowest_sal and highest_sal

3.EXIST后跟子查询(相关子查询)

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

SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.department_id = e.department_id
);

案例2:查询没有女朋友的男神信息

#in

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

#exist

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

子查询 - 习题

查询和Zlotkey相同部门的员工姓名和工资

#① 查询Zlotkey的部门

SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'

#② 查询部门号=①的姓名和工资

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

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

#① 查询平均工资

SELECT AVG(salary)
FROM employees

#② 查询工资 > ①的员工号 姓名和工资

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

查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

#① 查询各部门的平均工资

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

#② 连接①结果集和employees表,进行筛选

SELECT employee_id,last_name,salary,department_id
FROM employees e
INNER JOIN(
		SELECT AVG(salary),department_id
		FROM employees
		GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id;

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

#① 查询姓名中包含字母u的员工的部门

	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'

#②查询部门号 = ①中任一个的员工号和姓名

	SELECT job_id,last_name,department_id
	FROM employees
	WHERE department_id IN(
			SELECT DISTINCT department_id
			FROM employees
			WHERE last_name LIKE '%u%'
	);

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

	#① 查询location_id为1700的部门
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700

#② 查询部门号 = ①中的任意一个的员工号
	SELECT employee_id
	FROM   employees
	WHERE  department_id = ANY(
				SELECT DISTINCT department_id
				FROM departments
				WHERE location_id = 1700
	);

查询管理者是King的员工姓名和工资

	#① 查询姓名为KIng的员工编号
	SELECT employee_id
	FROM employees
	WHERE last_name = 'K_ing'

	#②查询那个员工的manager_id = ①
	SELECT last_name,salary
	FROM employees
	WHERE manager_id IN(
				SELECT employee_id
				FROM employees
				WHERE last_name = 'K_ing'
	);

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

	#① 查询最高工资
	SELECT MAX(salary)
	FROM employees

	#② 查询工资 = ① 的员工的姓名
	SELECT CONCAT(first_name,last_name) "姓,名"
	FROM employees
	WHERE salary = (
		SELECT MAX(salary)
		FROM employees
	);

1. 查询工资最低的员工信息: last_name, salary
2. 查询平均工资最低的部门信息

#① 各部门的平均工资

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

#② 求出最低平均工资的部门编号

SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;

#③ 查询部门信息

SELECT *
FROM departments
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1	
);

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

方式一:通过内连接连接两表进行查询
------------------------------------------------------------------------------------
#① 各部门的平均工资

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

#② 求出最低平均工资的部门编号

SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;

#③ 查询部门信息和平均工资

SELECT d.*,ag
FROM departments d
JOIN(
		SELECT department_id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary)
		LIMIT 1;
) ag_dep
ON d.department_id = ag_dep.department_id;
------------------------------------------------------------------------------------

4. 查询平均工资最高的 job 信息
5. 查询平均工资高于公司平均工资的部门有哪些?
6. 查询出公司中所有 manager 的详细信息.
7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少

# ① 查询各部门的最高工资中最低的哪一个的部门编号

SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIt 1;

# ② 查询哪个部门的最高工资 = ①

SELECT MIN(salary)
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIt 1
);

8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
9.查询姓张的每个学生的最低分大于60的学号和姓名

SELECT s.studentno,s.studentname,MIN(score)
FROM student s
JOIN result r
ON s.studentno = r.studentno
WHERE s.studentname LIKE '张%'
GROUP BY s.studentno
HAVING MIN(score) > 60;

10.查询生日在"1988-1-1"后的学生姓名 专业名称

	SELECT studentname,majorname
	FROM student s
	JOIN major m
	ON s.majorid = m.majorid
	WHERE DATEDIFF(borndate,'1988-1-1')>0

11.查询每个专业的男生人数和女生人数分别是多少

方式一:
--------------------------------------------------------------------------------
SELECT COUNT(*) 个数,sex,majorid
FROM student
GROUP BY sex,majorid

方式二:
---------------------------------------------------------------------------------
SELECT majorid,
	   (SELECT COUNT(*) FROM student WHERE sex = '男' AND majorid = s.majorid) 男
	   (SELECT COUNT(*) FROM student WHERE sex = '女' AND majorid = s.majorid) 女
FROM student s
GROUP BY majorid;