MySQL之SQL——DQL(4)——子查询、分页查询、联合查询

目录

7.子查询

(1)where或having后面

1)标量子查询

2)列子查询(多行子查询)

3)行子查询

(2)select后面

(3)from后面

(4)exists后面(相关子查询)

8.分页查询

9.联合查询


7.子查询

含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询

分类:
按子查询出现的位置:
	select后面:
		仅仅支持标量子查询
	
	from后面:
		支持表子查询

	where或having后面:★
		标量子查询(单行) 
		列子查询  (多行) 
		行子查询 (使用较少)
		
	exists后面(相关子查询)
		表子查询

按结果集的行列数不同:
	标量子查询(结果集只有一行一列)
	列子查询(结果集只有一列多行)
	行子查询(结果集有一行多列)
	表子查询(结果集一般为多行多列)

(1)where或having后面

1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)

特点:
    1.子查询放在小括号内
    2.子查询一般放在条件的右侧
    3.标量子查询,一般搭配着单行操作符使用
      > < >= <= = <>

      列子查询:一般搭配着多行操作符使用
      in、any/some、all

    4.子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果

1)标量子查询

案例1:谁的工资比 Abel 高?

  • 1.查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'

  • 2.查询员工的信息,满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'

);

案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id 和工资

  • 1.查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

  • 2.查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

  • 3.查询员工的姓名,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

  • 1.查询公司的 最低工资
SELECT MIN(salary)
FROM employees;

  • 2.查询last_name,job_id和salary,要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

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

  • 1.查询40号部门的最低工资
SELECT  MIN(salary)
FROM employees
WHERE department_id = 40;

  • 2.查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;

  • 3. 在2基础上筛选,满足min(salary)>1
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT  MIN(salary)
    FROM employees
    WHERE department_id = 40
);

非法使用标量子查询的情况

  • 错误1:在>,<等符号后子查询的结果不是一行一列
  • SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary)>(
    	SELECT salary
    	FROM employees
    	WHERE department_id = 50
    );
  • 错误2:子查询的结果不存在
  • SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary)>(
    	SELECT MIN(salary)
    	FROM employees
    	WHERE department_id = 250
    );

2)列子查询(多行子查询)

多行比较操作符:

举例:
in (10,20,30)   :表示是否等于10,20,30其中一个

any和some是相同的意思
>any(10,20,30)  : 表示大于10,20,30中的任意一个都可以

>all(10,20,30)  :表示大于10,20,30中所有的

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

  • 1.查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);

  • 2.查询员工姓名,要求部门号是1列表中的某一个
SELECT last_name
FROM employees
WHERE department_id  IN(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)
);

或

SELECT last_name
FROM employees
WHERE department_id = ANY(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)
);

案例2:返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

  • 1.查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

  • 2.查询员工号、姓名、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_id<>'IT_PROG';

或

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'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_id<>'IT_PROG';

或

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
    SELECT MIN( salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

3)行子查询

案例:查询员工编号最小并且工资最高的员工信息

SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

按照标量子查询如下:

  • 1.查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;

 

 

  • 2.查询最高工资
SELECT MAX(salary)
FROM employees;

 

 

  • 3.查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
    SELECT MIN(employee_id)
    FROM employees
)AND salary=(
    SELECT MAX(salary)
    FROM employees
);

(2)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	
) 部门名;

(3)from后面

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

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

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

  • 2.连接1的结果集和job_grades表,筛选条件平均工资 between lowest_sal and highest_sal
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;

(4)exists后面(相关子查询)

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

先执行外查询,再执行子查询,子查询涉及外查询的字段

示例1:

SELECT EXISTS(SELECT employee_id FROM employees);

示例2:

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

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

使用in:

SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
);

使用exists:

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

使用exists:

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

示例表中所有男神都有女朋友

8.分页查询

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

语法:
	select 查询列表
	from 表
	【join type 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;
	
	size=10
	page  
	1	0
	2  	10
	3	20

案例1:查询前五条员工信息

SELECT * FROM  employees LIMIT 0,5;

或

SELECT * FROM  employees LIMIT 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;

9.联合查询

union 联合 合并:将多条查询语句的结果合并成一个结果

语法:
查询语句1
union
查询语句2
union
...


应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时

特点:★
1、要求多条查询语句的查询列数是一致的!
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重,如果使用union all 可以包含重复项

案例:查询部门编号>90或邮箱包含a的员工信息

以前的写法:

SELECT * 
FROM employees 
WHERE email LIKE '%a%' OR department_id>90;

联合查询写法:

SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值