小徐带你连接sql99子查询和分页查询的用法详解

子查询重点难点

子查询含义：

  出现在其他语句中的select语句，称为子查询或内查询

  外部的查询语句，称为主查询或外查询

子查询分类

一：按子查询出现的位置分类

select		后面
				仅仅支持标量子查询
from			后面
				支持表子查询
where或having后面			重点
				标量子查询(单行)	重点
				列子查询(多行)		重点
				行子查询
exists	后面（相关主查询)
			表子查询

按结果集的行列数不同分类

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

一：where或having后面

		1：标量子查询（单行子查询）
		
		2：列子查询（多行子查询）
		
		3：行子查询（多行多列）
		
**特点：**
		1：子查询放在小括号内
		
		2：子查询一般放在条件的右侧
		
		3：标量子查询，一般搭配着多行操作符使用
		<，		>	，		<=	，	>=，		=	，	<>
		
		列子查询，一般搭配着多行操作符使用
		in	，	any/some，	all
		
		**4：子查询的执行优先主查询执行，主查询的条件用到了子查询的结果**

一：标量子查询

谁的工资比Abel高

SELECT last_name 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 DISTINCT
		department_id 
	FROM
		departments 
WHERE
	location_id IN ( 1400, 1700 ));

返回其他工种中比job_id为IT_PROG部门任意工资低的员工的姓名，Job_id以及salary

SELECT
	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
	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后面

查询每个部门的员工个数 难点重点

SELECT d.*,( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id ) '员工个数' 
FROM
	departments d 
GROUP BY
	d.department_id;

查询员工号=102的部门名 难点重点

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

三：from后面

注意：

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

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

SELECT
	avg_temp.*,
	j.grade_level 
FROM
	( SELECT avg( salary ) ag, department_id FROM employees GROUP BY department_id ) AS avg_temp
	INNER JOIN job_grades j ON avg_temp.ag BETWEEN j.lowest_sal 
	AND j.highest_sal;

四：exists后面(相关子查询）

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

案例：查询有员工的部门名

#第一种方式使用in方式
SELECT
	department_id,
	department_name 
FROM
	departments 
WHERE
	department_id IN ( SELECT department_id FROM employees );
#	第二种使用exists方式
SELECT
	department_id,
	department_name 
FROM
	departments d 
WHERE
	EXISTS ( SELECT * FROM employees e WHERE e.department_id = d.department_id );

查询没有女朋友的男神信息

#第一种是用exists方式
SELECT
	bo.* 
FROM
	boys bo 
WHERE
	NOT EXISTS ( SELECT boyfriend_id FROM beauty b WHERE bo.id = b.boyfriend_id );
	#第二种使用in的方式
	SELECT
	bo.* 
FROM
	boys bo 
WHERE
	bo.id NOT IN ( SELECT boyfriend_id FROM beauty );

练习题：

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

SELECT last_name, salary FROM employees e WHERE e.department_id =( SELECT department_id FROM employees WHERE last_name = 'zlotkey' );

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

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

查询各部门中工资比本部门平均工资高的员工的员工号，姓名，工资 重点难点

SELECT
	employee_id,
	last_name,
	salary,
	e.department_id 
FROM
	employees e
	INNER JOIN ( SELECT avg( salary ) ag, department_id FROM employees GROUP BY department_id ) ag_temp ON e.department_id = ag_temp.department_id 
WHERE
	salary > ag_temp.ag;

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

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

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

SELECT
	employee_id 
FROM
	employees 
WHERE
	department_id IN ( SELECT DISTINCT department_id FROM departments WHERE location_id = 1700 );

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

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	manager_id IN ( SELECT employee_id FROM employees WHERE last_name = 'k_ing' );

查询工资最高的员工的姓名，要求first_name和last_name显示一列，列名为姓.名 重点 concat函数拼接字符串

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

分页查询

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

语法分页

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

分页特点：

  		1：limit语句放在查询的最好
  		
  		2：公式
  		
  			要显示的页数page，每页的条目数size
  			
  			select		查询列表
  			
  			from		表
  			
  			limit	(page-1)*size，size；

案例：查询前前五条员工信息

SELECT * FROM employees
LIMIT 0,5;


SELECT * FROM employees
LIMIT 5;

查询第11条到第25条

SELECT * FROM employees
LIMIT 10,15;

查询有奖金的员工信息，并且工资较高的前10名显示出来

SELECT
	* 
FROM
	employees 
WHERE
	commission_pct IS NOT NULL 
ORDER BY
	salary DESC 
	LIMIT 0,
	10;

**

查询语句设计到的所有关键字，以及sql执行的先后顺序如图

**