sql筆記 P87-101

子查詢

/*

含義：出現(xiàn)在其他語(yǔ)句中的select語(yǔ)句，稱為子查詢或內(nèi)查詢

外部的查詢語(yǔ)句，稱為主查詢或外查詢

分類：

? ? 按子查詢出現(xiàn)的位置：

????select 后面：

? ? ? ? 僅僅支持標(biāo)量子查詢

? ? from 后面：

? ? ? ? 支持表子查詢

? ? where 或 having 后面

? ? ? ? 支持標(biāo)量子查詢 （單行）

? ? ? ? 列子查詢 ? ? ? ? ? ? （多行）

? ? ? ? 行子查詢

? ? exists 后面 （相關(guān)子查詢）

? ? ? ? 表子查詢

按結(jié)果集的行列數(shù)不同：

? ? 標(biāo)量子查詢（結(jié)果集只有一行一列）

? ? 列子查詢（結(jié)果集只有一列多行）

? ? 行子查詢（結(jié)果集有一行多列） ? ? ? ? ? ??

? ?表子查詢（結(jié)果集一般為多行多列） ? ? ? ? ? ? ? ? ? ? ??

*/

#一、where 或 having后面

1、標(biāo)量子查詢（單行子查詢）

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

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

特點(diǎn)：

-子查詢放在小括號(hào)內(nèi)

-子查詢一般放在條件的右側(cè)

-標(biāo)量子查詢，一般搭配著單行操作符使用><>=<=<>

-列子查詢，一般搭配著多行操作符使用IN、ANY/SOME、ALL

-子查詢的執(zhí)行優(yōu)先于主查詢執(zhí)行，主查詢的條件用到了子查詢的結(jié)果

#1、標(biāo)量子查詢

#案例1、誰(shuí)的工資比Abel高

1、查詢Abel的工資

select salary

from employees

where last_name = 'Abel'

2、查詢員工的信息滿足salary>1的結(jié)果

select *?

from employees?

where salary > (

select salary

from employees

where last_name = 'Abel'

)

#案例2、返回job_id與141號(hào)員工相同，salary比143號(hào)員工多的員工姓名，job_id和工資

#1、查詢141號(hào)員工的job_id

select job_id

from employees

where employee_id = 141

#2、查詢143號(hào)員工的salary

select salary?

from employees

where employee_id = 143

#3、查詢員工的姓名、job_id和工資，要求job_id=1并且salary>2

select last_name, job_id, salary

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=1

select?last_name, job_id, salary

from employees

where salary = (

select min(salary)?

from employees

)

#案例4：查詢最低工資大于50號(hào)部門(mén)最低工資的部門(mén)id和其最低工資

#1、查詢50號(hào)部門(mén)的最低工資

select min(salary)

from employees

where department_id = 50

#2、查詢每個(gè)部門(mén)的最低工資

select min(salary)

from employees

GROUP BU department_id

#3、在2基礎(chǔ)上篩選，滿足min(salary) > 1

select min(salary)

from employees

GROUP BU department_id

having min(salary)(

select min(salary)

from employees

where department_id = 50

)

#非法使用標(biāo)量子查詢，子查詢的結(jié)果不是一行一列

select min(salary)

from employees

GROUP BU department_id

having min(salary)(

select salary

from employees

where department_id = 50

)

#2、列子查詢（多行子查詢）

#案例1、返回location_id是1400或1700的部門(mén)中的所有員工姓名

#1、查詢location_id是1400或1700的部門(mén)編號(hào)

select distinct department_id

from departments

where location_id IN(1400, 1700)

@2、查詢員工姓名，要求部門(mén)號(hào)是1列表中的某一個(gè)

select last_name?

from employees

where department_id IN (

select distinct department_id

from departments

where location_id IN(1400, 1700)

)

#案例2、返回其他工種中比job_id為‘IT_PROG’工種任一工資低的員工的工號(hào)、姓名、job_id以及salary

#1、查詢job_id為‘IT_PROG’部門(mén)任一工資

select distinct salary

from employees

where job_id ?=?‘IT_PROG’

#2、查詢員工的工號(hào)、姓名、job_id以及salary，salary<any(1)的任意一個(gè)

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’工種所有工資低的員工的工號(hào)、姓名、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、行子查詢<結(jié)果集一行多列，多行多列>

#案例：查詢員工編號(hào)最小并且工資最高的員工信息

select *?

from employees

where (employee_id, salary) = (

? ? select MIN(employee_id), MAX(salary)?

? ? from employees

)

#1、查詢最小的員工編號(hào)

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

)

#二、select 后面，僅僅支持標(biāo)量子查詢

#案例：查詢每個(gè)部門(mén)的員工個(gè)數(shù)

select d.*, (

select count(*)?

from employees e

where e.department_id = d.department_id

) 個(gè)數(shù)

?from departments d

#案例二：查詢員工號(hào)=102的部門(mén)名

select (

select department_name?

from departments d?

inner join employees e

ON d.department_id = e.department_id

where e.employee_id = 102

)

#三、from后面

/*

將子查詢結(jié)果充當(dāng)一張表，要求必須起別名

*/

#案例：查詢每個(gè)部門(mén)的平均工資的工資等級(jí)

#1、查詢每個(gè)部門(mén)的平均工資

selec AVG(salary), department_id

from employees

GROUP BY department_id

#2、連接1的結(jié)果集和job_grades表，篩選條件平均工資 between lowest_sal and highest_sal

select ag_dep.*, g.grade_level

from (

selec 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后面 （相關(guān)子查詢）

/*

語(yǔ)法：

exists (完整的查詢語(yǔ)句)

結(jié)果：

1或者0

*/

select EXISTS(select employee_id from employees where salary = 30000)

#案例1：查詢有員工的部門(mén)名

#in

select department_name?

from departments d?

where d.department_id IN (

? ? select department_id

? ? from employees

)

#exists

select epartment_name

from departments

where EXISTS (

? ? select *?

? ? from employees e

? ? where d.department_id = e.department_id

)

#案例2：查詢沒(méi)有女朋友的男神信息

# 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

)

#練習(xí)

#1查詢和zlotkey相同部門(mén)的員工姓名和工資

#1、查詢zlotkey的部門(mén)

select departmetn_id

from employees?

where last_name = 'zlotkey'

#2、查詢部門(mén)號(hào)=1的姓名和工資

select last_name, salary

from employees?

where department_id = (

select departmetn_id

from employees?

where last_name = 'zlotkey'

)

#2.查詢工資比公司平均工資搞的員工的員工號(hào)、姓名和工資

#1.查詢平均工資

select AVG(salary)

from employees

#2.查詢工資>1的員工號(hào)，姓名和工資

select last_name, employees_id, salary

from employees

where salary > (

select AVG(salary)

from employees

)

#3、查詢各部門(mén)中工資比本部門(mén)平均工資搞的員工的員工號(hào)，姓名和工資

#1.查詢各部門(mén)的評(píng)平均工資

select AVG(salary), department_id

from employees

GROUP BY department_id

#2.連接1結(jié)果集和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_dep

ON e.department_id = ag_dep.department_id

where salary > ag_dep.ag

#4、查詢姓名中包含字母u的員工在相同部門(mén)的員工號(hào)和姓名

#1.查詢姓名中包含字母u的員工的部門(mén)‘

select distinct department_id?

from employees

where last_name LIKE '%u%'

#2.查詢部門(mén)號(hào)=1中的任意一個(gè)的員工號(hào)和姓名

select last_name, employee_id

from employees

where department_id IN (

????select distinct department_id?

????from employees

????where last_name LIKE '%u%'????

)

#5.查詢?cè)诓块T(mén)的location_id 為1700的部門(mén)工作的員工的員工號(hào)

#1.查詢?cè)诓块T(mén)的location_id 為1700的部門(mén)

select departmetn_id

from departments

where location_id = 1700

#2.查詢部門(mén)號(hào)=1中的任意一個(gè)的員工號(hào)

select employee_id

from employees

where departmetn_id = ANY (

????select departmetn_id

????from departments

????where location_id = 1700

)

#6、查詢管理者是king的員工姓名和工資

#1.查詢姓名為king的員工編號(hào)

select employee_id

from employees

where last_name = 'K_ing'

#2.查詢哪個(gè)員工的manager_id = 1

select last_name, salary

from employees

where manager_id IN (

????select employee_id????

????from employees

????where last_name = 'K_ing'

)

#7、查詢工資最高的員工的姓名，要求frist_name和last_naem 顯示為一列，列名為姓,名

#1.查詢最高工資

select MAX(salary)

from employees

#2.查詢工資=1的姓，名

select CONCAT(first_name, last_name) "姓.名"

from employess

where salary = (

????select MAX(salary)????

????from employees

)

#進(jìn)階8：分頁(yè)查詢

/*

應(yīng)用場(chǎng)景：當(dāng)要顯示的數(shù)據(jù)，一頁(yè)顯示不全，需要分頁(yè)提交sql請(qǐng)求

語(yǔ)法：

? ? select 查詢列表

? ? from 表

? ?【join type ?join 表2

? ? where 篩選條件

? ? group by 分組字段

? ? having 分組后的篩選

? ? order by 排序的字段】

? ? limit offset, size;

? ? offset要顯示條目的起始索引（起始索引從0開(kāi)始）

? ? size 要顯示的條目個(gè)數(shù)

特點(diǎn)：

? ? -limit語(yǔ)句放在查詢語(yǔ)句的最后 ? ?

? ? -公式：要顯示的頁(yè)數(shù)page，每頁(yè)的條目數(shù)size

? ? select 查詢列表

? ? from 表

? ? limit ? (page - 1) * size, size

*/

#案例1：查詢前五條員工信息

select * from employees limit 0,5

select * from employees limit 5

#案例2：查詢第11條---第25條

select * from employees limit 10,15

案例3：有獎(jiǎng)金的員工信息，并且工資較高的前10名顯示出來(lái)

select * from employees where commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10

#作業(yè)

#1、查詢工資最低的員工信息

#1.查詢最低的工資

select MIN(salary)

from employees

#2.查詢last_name, salary要求salary=1

select last_name, salary

from employees

where salary = (

????select MIN(salary)

????from employees

)


#2、查詢平均工資最低的部門(mén)信息

#方式一

#1.各部門(mén)的平均工資

select AVG(salary), department_id

from employees

GROPU BY ddepartment_id

#2.查詢1結(jié)果上的最低平均工資

select MIN(ag)

from (

????select AVG(salary) ag , department_id

????from employees

????GROPU BY ddepartment_id

) ag_dep?

#3.查詢哪個(gè)部門(mén)的平均工資=2

select AVG(salary), department_id

from employees

GROPU BY ddepartment_id

having AVG(salary) = (

????select MIN(ag)

????from (

????????select AVG(salary) ag , department_id

????????from employees

????????GROPU BY ddepartment_id

????) ag_dep?

)

#4.查詢部門(mén)信息

select d.*

from departments d

where d.department_id = (

select ?department_id

from employees

GROPU BY ddepartment_id

having AVG(salary) = (

select MIN(ag)

????from (

????????select AVG(salary) ag , department_id

????????from employees

????????GROPU BY ddepartment_id

????) ag_dep?

)

)

#方式二

#1.各部門(mén)的平均工資

select AVG(salary), department_id

from employees

GROPU BY ddepartment_id

#2.求出最低平均工資的部門(mén)編號(hào)

select AVG(salary), department_id

from employees

GROPU BY ddepartment_id

ORDER BY?AVG(salary)

LIMIT 1

#.3.查詢部門(mén)信息

select *

from departments?

where = department_id = (

????select AVG(salary), department_id

????from employees

????GROPU BY ddepartment_id

????ORDER BYAVG(salary)

????LIMIT 1

)

#3、查詢平均工資最低的部門(mén)信息和該部門(mén)的平均工資

#1.各部門(mén)的平均工資

select AVG(salary), department_id

from employees

GROPU BY ddepartment_id

#2.求出最低平均工資的部門(mén)編號(hào)

select AVG(salary), department_id

from employees

GROPU BY ddepartment_id

ORDER BY?AVG(salary)

LIMIT 1

#3.查詢部門(mén)信息

select d.*, ag

from departments d

join (

????select AVG(salary), department_id

????from employees

????GROPU BY ddepartment_id

????ORDER BY?AVG(salary)

????LIMIT 1

) ag_dep

ON d.department_id = ag_dep.department_id


#4、查詢平均工資最高job信息

#1.查詢每個(gè)job的平均工資

select AVG(salary), job_id

from employees

GROUP BY job_id

ORDER BY AVG(salary) DESC?

LIMIT 1

#2.查詢job信息

select *

from jobs

where job_id = (

? ??select ?job_id

????from employees

????GROUP BY job_id

????ORDER BY AVG(salary) DESC?

????LIMIT 1

)

#5、查詢平均工資高于公司平均工資的部門(mén)有哪些

#1.查詢平均工資

select AVG(salary)?

from employees

#2.查詢每個(gè)部門(mén)的平均工資

select AVG(salary),?department_id

from employees

GROUP BY department_id

#3.篩選2結(jié)果集，滿足平均工資>1

select AVG(salary), department_id

from employees

GROUP BY department_id

HAVING AVG(salary) (

????select AVG(salary)?

????from employees

)

#6、查詢出公司中所有manager的詳細(xì)信息

#1.查詢所有manage的員工編號(hào)

select DISTINCT manager_id

from employees

#2.查詢?cè)敿?xì)信息，滿足employe_id=1

select *?

from employees

where employee_id = ANY (

????select DISTINCT manager_id

????from employees

)

#7、各個(gè)部門(mén)中，最高工資中最低的那個(gè)部門(mén)的最低工資是多少

#1.查詢各部門(mén)的最高工資中最低的部門(mén)編號(hào)

select department_id

from employees

GROUP BY department_id

ORDER BY MAX(salary)

LIMIT 1

#2.查詢1結(jié)果的那個(gè)部門(mén)的最低工資

select?MAX(salary), departmetn_id

from employees

where department_id = (

????select?department_id

????from employees

????GROUP BY department_id

????ORDER BY MAX(salary)

????LIMIT 1

)

#8、查詢平均工資最高的部門(mén)的manage的詳細(xì)信息:last_name, departmetn_id, email, salary

#1.查詢平均工資最高的部門(mén)編號(hào)

select department_id

FROM employees

GROUP BY department_id

ORDER BY AVG(salary) DESC

LIMIT 1

#2.講employees和departments連接查詢，篩選條件是1

select?last_name, d.departmetn_id, email, salary

from employees e

INNER JOIN departments d ON d.manager_id = e.employee_id

where d.department_id = (

????select department_id

????FROM employees

????GROUP BY department_id

????ORDER BY AVG(salary) DESC

????LIMIT?

)


#進(jìn)階9：聯(lián)合查詢

/*

union 聯(lián)合 ?合并：講多條查詢語(yǔ)句的結(jié)果合并成一個(gè)結(jié)果

語(yǔ)法：

查詢語(yǔ)句1

union

查詢語(yǔ)句2

union

...

應(yīng)用場(chǎng)景：

要查詢的結(jié)果來(lái)自于多個(gè)表，且多個(gè)表沒(méi)有直接的連接關(guān)系，但查詢的信息一致時(shí)

特點(diǎn)：

1、要求多條查詢語(yǔ)句的查詢列表數(shù)一致的

2、要求多條查詢語(yǔ)句的查詢的每一列的類型和順序最好一致

3、union關(guān)鍵字默認(rèn)去重，如果使用union all 可以包含重復(fù)項(xiàng)

*/

# 引入的案例：查詢部門(mén)編號(hào)>90或郵箱中包含a的員工信息

select * from employees where email LIKE '%a%' OR department_id > 90;

select * from employees where email LIKE '%a%'

UNION?

select * from employees wheredepartment_id > 90;

#案例：查詢中國(guó)用戶中性別=男的信息以及外國(guó)用戶中性別=男的用戶信息

select id, cname, csex from t_ca where csex = '男'

UNION ALL

select t_id, tName, tGender from t_ua where tGender = 'male'

語(yǔ)法：

select 查詢列表? ? ? ? ? ? ? ? ? ? 7

from 表1 別名? ? ? ? ? ? ? ? ? ? 1

連接類型? ?join? 表2? ? ? ? ? ? 2

on 連接條件? ? ? ? ? ? ? ? ? ? ? ? 3

where? 篩選? ? ? ? ? ? ? ? ? ? ? ? 4

group by 分組列表? ? ? ? ? ? ? 5

having? 篩選? ? ? ? ? ? ? ? ? ? ? ? 6

order by 排序列表? ? ? ? ? ? ? ? 8

limit? ? 其實(shí)條目索引，條目數(shù)? ?9

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • #五、流程控制函數(shù) #1. if函數(shù)：if else 的函數(shù) SELECT IF(10 > 5, '大', '小'...
    kevinXiao閱讀 401評(píng)論 0贊 0
  • #進(jìn)階1：基礎(chǔ)查詢 /* 語(yǔ)法： select 查詢列表 from 表名; 類似于: System.out.pri...
    kevinXiao閱讀 343評(píng)論 0贊 0
  • SQL語(yǔ)言基礎(chǔ) 本章，我們將會(huì)重點(diǎn)探討SQL語(yǔ)言基礎(chǔ)，學(xué)習(xí)用SQL進(jìn)行數(shù)據(jù)庫(kù)的基本數(shù)據(jù)查詢操作。另外請(qǐng)注意本章的S...
    厲鉚兄閱讀 5,457評(píng)論 2贊 46
  • 我是黑夜里大雨紛飛的人啊 1 “又到一年六月，有人笑有人哭，有人歡樂(lè)有人憂愁，有人驚喜有人失落，有的覺(jué)得收獲滿滿有...
    陌忘宇閱讀 8,830評(píng)論 28贊 54
  • 首先介紹下自己的背景： 我11年左右入市到現(xiàn)在，也差不多有4年時(shí)間，看過(guò)一些關(guān)于股票投資的書(shū)籍，對(duì)于巴菲特等股神的...
    瞎投資閱讀 5,936評(píng)論 3贊 8

友情鏈接更多精彩內(nèi)容