Mysql学习四 子查询与分页查询、联合查询

子查询

/*
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询
分类：
出现的位置：
select后面： 仅支持标量子查询
from后面： 支持表子查询
where或having后面： 标量子查询、列子查询、行子查询
exists后面（相关子查询） 表子查询
结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列）
表子查询（结果集一般为多行多列）
*/

where或having后面的

/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列子查询）
注意：
子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用 <> > < = >= <=
列子查询，一般搭配着多行操作符 IN、ANY/SOME、ALL
IN/NOT IN :等于列表中的任意一个
ANY/SOME：和子查询返回的某一个值比较
ALL：和子查询返回的所有值比较
*/

标量子查询

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

列子查询（多行子查询）

#返回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 	DISTINCT 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 	DISTINCT 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	d.*,(
	SELECT 	COUNT(*)
	FROM 	employees 
	WHERE	employees.`department_id` =d.`department_id`
)
FROM 	departments d;

#查询员工号=102的部门名

SELECT (
	SELECT 	department_name
	FROM 	departments 
	INNER JOIN employees 
	ON departments.`department_id`=employees.`department_id`
	WHERE employees.`employee_id`=102
) 部门名;

from后面

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

SELECT ag_dep.*,g.`grade_level`
FROM(
	SELECT 	AVG(salary) ag,department_id
	FROM 	employees
	GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists后面（相关子查询）

/*
语法：exists（完整的查询语句）
结果：0或1
*/

SELECT EXISTS(SELECT employee_id FROM employees);

#查询有员工的部门名

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

分页查询

/*
应用场景：要显示的数据，一页显示不全，需要分页提交sql请求
语法：

select 查询列表
from 表
【
join type join 表2
on 	连接条件
where 	筛选条件
group by 分组字段
having  分组后的筛选
order by  排序的字段
】
limit offset，size；
offset 要显示条目的起始索引（从0开始）
size 要显示的条目个数

*/
#查询前五条员工信息

SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;
 

联合查询

/*
union 联合 合并：将多条查询语句的结果合并为一个结果
语法：
查询语句1
union
查询语句2……
应用场景： 要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：使用union默认去重，可以使用union all不去重
*/