Day05 子查询、分页查询

子查询

一、where或having后面

含义:
	出现在其他语句中的select语句，称子查询或内查询
	外部的查询语句，城为主查询或外查询
分类:
	按子查询出现的位置:
		select后面
			仅仅支持---标量子查询（单行子查询）
		from后面
			支持---表子查询
		where或having后面  ★
			支持---标量子查询(单行)★、列子查询(多行)★、行子查询（使用较少）
		exists后面（相关子查询）
			支持---表子查询
			
	按结果集的行列数不同:
		标量子查询（结果集只有一行一列）
		列子查询（结果集只有一列多行）
		行子查询（结果集有一行多列）
		表子查询（结果集一般为多行多列）

where或having后面
		1、标量子查询（单行子查询）
		2、列子查询（一列多行查询、多行子查询）
		
		3、行子查询（多列多行）
		
	特点:
		①子查询都会放在小括号内
		②子查询一般放在条件的右侧
		③-标量子查询，一般搭配着单行操作符使用
			单行操作符（ > < >= <= <> ）
		  -列子查询，一般搭配着多行操作符
			多行操作符（ IN、ANY/SOME、ALL ）
		④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询（一行一列）

非法使用标量子查询的情况
要求子查询的结果为一行一列

标量子查询（一行一列）
		
			#案例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和其最低工资	
				SELECT `department_id`,MIN(`salary`)
				FROM `employees`
				GROUP BY `department_id`
				HAVING MIN(`salary`)>(
					SELECT MIN(`salary`)
					FROM `employees`
					WHERE `department_id`=50
				)

2.列子查询（多行子查询，一列多行）

列子查询（多行子查询，一列多行）

			#案例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"工种任一工资低的员工号、姓名、job_id以及salary
				SELECT `employee_id`,`last_name`,`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"部门所有工资都底的员工的员工号、姓名、job_id以及salary
				SELECT `employee_id`,`last_name`,`job_id`,`salary`
				FROM `employees`
				WHERE `salary` < ALL(
					SELECT `salary` 
					FROM `employees`
					WHERE `job_id`="IT_PROG"
				)AND `job_id`!="IT_PROG";

3.行子查询（结果集一行多列或多行多列)

行子查询（结果集一行多列或多行多列）

			#案例: 查询员工编号最小并且工资最高的员工信息
				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后面

		#案例: 查询每个部门的员工个数
			SELECT d.*,(
				SELECT COUNT(*)
				FROM `employees` e
				WHERE e.`department_id`=d.`department_id`
			) 个数
			FROM `departments` d;

三、from后面

from后面	

		#案例: 查询每个部门的平均工资的工资等级
			SELECT ag_dep.*,jg.`grade_level`
			FROM (
				SELECT AVG(`salary`) ag,`department_id` dep
				FROM `employees`
				GROUP BY `department_id`
			) AS ag_dep
			INNER JOIN `job_grades` jg
			WHERE ag_dep.ag BETWEEN `lowest_sal` AND `highest_sal`;

四、exists后面（相关子查询）

  	语法:
  	    exists(完整的查询语句) 
  	    结果:	0 或 1	

#演示:SELECT EXISTS(SELECT `employee_id` FROM `employees` WHERE `salary`=900000000);
		
		#案例1: 查询有员工的部门名
			#exists方式
				SELECT `department_name`
				FROM `departments` d
				WHERE EXISTS(
					SELECT *
					FROM `employees` e
					WHERE d.`department_id`=e.`department_id`
				);
			#in方式
				SELECT `department_name`
				FROM `departments` d
				WHERE `department_id` IN(
						SELECT `department_id`
						FROM `employees` e
				);

分页查询★

应用场景: 当要显示的数据，一页显示不全，需要分页提交sql请求
语法:
	select 查询列表
	from 表名
	【join type join 表2
	on 连接条件
	where 分组前筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset,】size;
	
	offset 要显示条目的起始索引（起始索引从0开始）
	size 要显示的条目个数
	
特点:
	①limit语句放在查询语句的最后面
	②公式★★★
	要显示的页数 page，每页条目数size
		select 查询列表
		from 表名
		limit (page-1)*size,size;★★★
		
		size=10
		page=
		1  10
		2  20
		3  30

	#案例1: 查询前5条员工信息
		SELECT * FROM `employees` LIMIT 0,5;
		SELECT * FROM `employees` LIMIT 5; #若从第一条条目开始可省略offset
		
	#案例2: 查询第11条-25条员工信息
		SELECT * FROM `employees` LIMIT 10,15;
		
	#案例3: 有奖金的员工信息，并且工资较高的前十名
		SELECT * 
		FROM `employees`
		WHERE `commission_pct` IS NOT NULL
		ORDER BY `salary` DESC
		LIMIT 10;