Live2D

MySQL基础_基础查询_进阶7_子查询

配套视频见下方网址,相关文件见视频评论区
[尚硅谷MySQL教程](https://www.bilibili.com/video/BV12b411K7Zu)

进阶7:子查询

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

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

from后面
    支持表子查询
    
where或having后面:☆
    标量子查询 √
    列子查询   √
    
    行子查询(用的较少)
    
exists后面(相关子查询)
    表子查询

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

*/

/*
一、where或having后面
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)

特点:
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询,一般搭配着单行操作符使用

< >= <= = <>

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

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

*/

标量子查询

案例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号员工的job_id

SELECT job_id
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 job_id
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和其最低工资

①查询50号部门的最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id=50;

②查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;

③在②基础上筛选,满足MIN(salary)>①

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
);

非法使用标量子查询(错)

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary #没有加min函数
FROM employees
WHERE department_id=50
);

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id=250 #该部门号不存在
);

posted @ 2020-09-23 15:47  xbycf  阅读(272)  评论(0编辑  收藏  举报