MySQL04

子查询

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

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

where或having后面

特点：

1. 子查询放在小括号内
2. 子查询一般放在条件右侧
3. 标量子查询，一般搭配着单行操作符使用
4. 子查询的执行优先于主查询，主查询的条件用到了子查询的结果

标量子查询（单行子查询）*******

#案例1：谁的工资比Abel高？
#1.查询Abel的工资
SELECT salary
FROM employees
WHERE last_name='Abel';
#2.查询员工的信息，满足salary>1的结果
SELECT *
FROM employees
WHERE salary>(

	SELECT salary
	FROM employees
	WHERE last_name='Abel'
);

#案例2：返回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

);

#案例3：返回公司工资最少的员工的姓名，工作编号和工资
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门编号和其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id =50
);

列子查询（多行子查询）***

#案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

#案例2：返回其他工种中比job_id为'IT_PROG'的工种任一工资低的员工的员工号、姓名、工作编号和工资
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';

#案例3：返回其它部门中比job_id为'IT_PROG'部门所有工资都低的员工的员工号、姓名、工作编号和工资
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,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

select后面

仅支持标量子查询

#案例1：查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id=d.`department_id`
) 个数
FROM departments 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
) 部门名;

from后面

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

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

#1.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

SELECT * FROM job_grades,
#2.连接1的结果和job_grades表，筛选条件平均工资
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（完整的查询语句）

结果1或0

#案例1：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT*
	FROM employees e
	WHERE d.department_id=e.`department_id`
);

#案例2：查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE bo.id  NOT IN(
	SELECT boyfriend_id
	FROM beauty
);

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

分页查询

/*
应用场景：当要显示的数，一页显示不全，需要分页提交sql请求
语法：
	select 查询列表
	from 表
	【join typejoin 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序字段】
	limit 起始索引，条目数；
特点：
	1.limit放在查询语句最后
	2.公式
	  要显示的页数 page，每页的条目数size
	  （page-1）*size，size
*/

#案例1：查询前5条员工信息
SELECT * 
FROM employees
LIMIT 0,5;

#案例2：查询第11条到第25条
SELECT *
FROM employees
LIMIT 10,15;

#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

联合查询

/*
union 联合 将多条查询语句的结果合并成一个结果
语法：
	查询语句1
	union
	查询语句2
	union
	······
	
应用场景:
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：
	1.要求多条查询语句的查询列数是一致的
	2.要求多条查询语句的每一列的类型和顺序是一致的
	3.union关键字默认是去重的，可以使用union all去掉重复项
*/

#案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

#案例：查询中国用户男性的信息以及外国用户男性的信息
SELECT * FROM t_ca WHERE csex='男'
UNION
SELECT * FROM t_ua WHERE tGender='male';