天天看點
傳回

MySQL查詢語句之子查詢（六）一、where或having後面二、select後面三、from後面四、exists後面(相關子查詢)

文章目錄

  • 一、where或having後面
    • 1.1 标量子查詢
    • 1.2 列子查詢(多行子查詢)
    • 1.3 行子查詢（結果集一行多列或多行多列）
  • 二、select後面
  • 三、from後面
  • 四、exists後面(相關子查詢)

子查詢含義：

    出現在其他語句的select語句,稱為

子查詢或内查詢

    外部的查詢語句,稱為

主查詢或外查詢

分類:

按子查詢出現的位置:

    select後面:

          僅僅支援标量子查詢

    from後面:

          支援表子查詢

    where或having後面:★★★

          标量子查詢(單行)√

          列子查詢 (多行)√

          行子查詢(用的少)

    exists後面(相關子查詢)

          表子查詢

按結果集的行列數不同:

    标量子查詢(結果集隻有一行一列)

    列子查詢(結果集一列多行)

    行子查詢(結果集一行多列)

    表子查詢(結果集一般為多行多列)

一、where或having後面

支援：

1.标量子查詢（單行子查詢）

2.列子查詢（多行子查詢）

3.行子查詢（多行多列）

特點：

①子查詢放在小括号内

②子查詢一般放在條件的右側

③标量子查詢，一般單行操作符使用

>,<,=,>=,<=,<>

列子查詢,一般搭配多行操作符使用

in,any/sum,all

④子查詢的執行優先于主查詢的執行,主查詢的條件用到了子查詢的結果

1.1 标量子查詢

案例1: 誰的工資比Abel高?

step1：查詢Abel的工資

show databases;
use myemployees;
select salary from employees where last_name = 'Abel';

step2：查詢員工資訊,滿足salary>step1的結果

select * from employees
where salary >(
	select salary 
    from employees 
    where last_name = 'Abel'
); # where後面标量子查詢(結果集是一個資料)，放在()内

案例2: 傳回job_id與141号員工相同,salary比143号員工多的員工姓名,job_id和工資

step1：查詢141号員工的job_id

select job_id
from employees
where employee_id=141;

step2：查詢143号員工的salary

select salary
from employees
where employee_id=143;

step3：查詢員工的姓名,job_id和工資,要求job_id=step1并且salary>step2

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

step1：查詢公司的最低工資

select min(salary)
from employees;

step2：查詢last_name,job_id,和salary,要求salary=step1結果

select last_name,job_id,salary
from employees
where salary=(
	select min(salary)
	from employees
);

案例4: 查詢最低工資大于50号部門最低工資的部門id和其最低工資

step1：查詢50号部門的最低工資

select min(salary)
from employees
where department_id=50;

step2：查詢每個部門的最低工資

select min(salary),department_id
from employees
group by department_id;

step3：篩選step2滿足min(salary)>step1

select min(salary),department_id
from employees
group by department_id
having min(salary)>(
	select min(salary)
	from employees
	where department_id=50
); # 子查詢在having之後

非法使用子查詢

select min(salary),department_id
from employees
group by department_id
having min(salary)>(
	select salary
	from employees
	where department_id=50
); # 不是一行一列

1.2 列子查詢(多行子查詢)

案例1: 傳回location_id是1400或1700的部門中的所有員工姓名

step1:查詢location_id是1400或1700的部門編号

select department_id
from departments
where location_id in (1400,1700); # 兩行

step2:查詢員工姓名,要求部門号是step1清單中的某一個

select last_name
from employees
where department_id in(
	select distinct department_id
	from departments
	where location_id in (1400,1700)
); # 将in換成 =any 效果一樣;not in換成<>all效果一樣

案例2: 傳回其它工種中比job_id為‘IT_PROG’工種部門任一工資低的員工的員工号、姓名、job_id以及salary

step1: 查詢job_id為‘IT_PROG’部門任一工資

select distinct salary
from employees
where job_id='IT_PROG';

step2: 查詢員工号、姓名、job_id以及salary,salary<any(step1)

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'; # 工種不是IT_PROG且工資低于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'; # 去掉any，裡面為max也行

案例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'; # 比IT_PROG部門所有（all）都低

或

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'; # 去掉all，裡面為min也行

1.3 行子查詢（結果集一行多列或多行多列）

案例： 查詢員工編号最小且工資最高的員工資訊

select *
from employees
where (employee_id,salary)=(
	select min(employee_id),max(salary)
	from employees
);

傳統做法:

s1:查詢最小的員工編号

show databases;
use myemployees;
select min(employee_id)
from employees;

s2:查詢最高工資

select max(salary)
from employees;

s3:查詢員工資訊

select * 
from employees
where employee_id=(
	select min(employee_id)
	from employees
)and salary=(
	select max(salary)
	from employees
); # 不用and，使兩個字段放在（）裡亦可

二、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後面

将子查詢結果充當一張表,要求必須起别名

案例: 查詢每個部門的平均工資的工資等級

s1：查詢每個部門的平均工資

select avg(salary),department_id
from employees
group by department_id;

select * from job_grades;

s2:連接配接s1的結果集和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;

四、exists後面(相關子查詢)

文法:

exists(完整的查詢語句)

結果:

1:存在,0:不存在

案例1: 查詢有員工的部門名

in(能使用exists就能用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: 查詢沒有女朋友的男神資訊

not in

use girls;
select bo.*
from boys bo
where bo.id not in(
	select boyfriend_id
	from beauty
);

not exists

select bo.*
from boys bo
where not exists(
	select boyfriend_id
	from beauty b
	where bo.id=b.boyfriend_id
);
MySQL查詢語句之子查詢（六）
上一篇: T-SQL語句 多表查詢
下一篇: Mybatis的緩存機制（一）