西北望鄉(xiāng)何處是，東南見(jiàn)月幾回圓。

月亮又慢悠悠的掛上了天空，趁著睡前夢(mèng)囈，我就帶領(lǐng)各位可愛(ài)的讀者們探索MySql最后的子查詢部分。

說(shuō)明:有些查詢結(jié)果出來(lái)結(jié)果截圖與題目要求不一樣會(huì)出現(xiàn)多余的字段是為了方便展示結(jié)果的可讀性。實(shí)際操作的讀者可以刪除SELECT后面多余的字段得到正確的結(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高？#①查詢Abel的工資SELECT salaryFROM employeesWHERE last_name = ’Abel’;

#②查詢員工的信息，滿足Salary>①結(jié)果SELECT *FROM employeesWHERE salary>(SELECT salary FROM employees WHERE last_name=’Abel’);

#案例2.返回job_id與141號(hào)員工相同，salary比143號(hào)員工多的員工姓名，job_id,工資。#①查141員工的job_idSELECT job_idFROM employeesWHERE employee_id=’141’;

#②查143員工的salarySELECT salaryFROM employeesWHERE employee_id=’143’;

#③最后合并結(jié)果SELECT CONCAT(last_name,first_name) AS 姓名,job_id AS 工種編號(hào),salary AS 工資FROM employeesWHERE 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。SELECT MIN(salary)FROM employees;

SELECTlast_name AS 姓,salary AS 工資,job_id AS 工種編號(hào)FROM employeesWHERE salary=( SELECT MIN(salary) FROM employees );

#案例4.查詢最低工資大于50號(hào)部門(mén)最低工資的部門(mén)id和其最低工資。#①查50部門(mén)的最低工資SELECT MIN(salary)FROM employeesWHERE department_id=50;

#分組后，篩選條件①.【不用排除沒(méi)有部門(mén)的所以不篩選部門(mén)編號(hào)】SELECT department_id AS 部門(mén)編號(hào),MIN(salary) AS 月薪FROM employees#WHERE department_idGROUP BY department_idHAVING 月薪>( SELECT MIN(salary) FROM employees );

#2.列子查詢(多行子查詢)#返回多行#使用多行比較操作符

#案例1.返回location_id是1400或1700的部門(mén)中的所有員工姓名。#①查詢location_id是1400或1700的部門(mén)編號(hào)SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700);

#②查詢員工姓名，要求部門(mén)號(hào)是①列表的某一個(gè)SELECT CONCAT(last_name,first_name) AS 姓名FROM employeesWHERE department_id IN ( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) );

用ANY替代IN與上面同樣的結(jié)果SELECT CONCAT(last_name,first_name) AS 姓名FROM employeesWHERE department_id = ANY( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) );

#案例.返回location_id不是1400或1700的部門(mén)中的所有員工姓名。SELECT CONCAT(last_name,first_name) AS 姓名FROM employeesWHERE department_id NOT IN( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));==============================SELECT CONCAT(last_name,first_name) AS 姓名FROM employeesWHERE department_id <> ALL( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));

#案例2.返回其他工種中比job_id為IT_PROG部門(mén)任意一工資低的員工工號(hào),# 姓名,job_id以及salary#①把IT_PROG部門(mén)中的工資查出來(lái)SELECT DISTINCT salaryFROM employeesWHERE job_id=’IT_PROG’;

#②把不是IT_PROG部門(mén)信息查出來(lái)SELECT *FROM employeesWHERE job_id != ’IT_PROG’;

#③合并①與②在員工表中查出來(lái)SELECT employee_id AS 員工編號(hào),CONCAT(last_name,first_name) AS 姓名,job_id AS 工種編號(hào),salary AS 工資FROM employeesWHERE job_id != ’IT_PROG’AND salary<ANY( SELECT salary FROM employees WHERE job_id=’IT_PROG’ );

用MAX代替ANY與上面同樣的效果SELECT employee_id AS 員工編號(hào),CONCAT(last_name,first_name) AS 姓名,job_id AS 工種編號(hào),salary AS 工資FROM employeesWHERE job_id <> ’IT_PROG’AND salary<( SELECT MAX(salary) FROM employees WHERE job_id=’IT_PROG’ );

#案例3.返回其他部門(mén)中比job_id為‘IT_PROG’部門(mén)所有工資都低的員工#的員工號(hào)，姓名，job_id以及salary。#①先把IT_PROG部門(mén)的工資查出來(lái)。SELECT DISTINCT salaryFROM employeesWHERE job_id=’IT_PROG’;

SELECT employee_id AS 員工號(hào),CONCAT(last_name,first_name) AS 姓名,job_id AS 工種編號(hào),salary AS 工資FROM employeesWHERE salary<ALL( SELECT DISTINCT salary FROM employees WHERE job_id=’IT_PROG’) AND job_id <> ’IT_PROG’;=============================MIN替代ALLSELECT employee_id AS 員工號(hào),CONCAT(last_name,first_name) AS 姓名,job_id AS 工種編號(hào),salary AS 工資FROM employeesWHERE salary<(SELECT MIN(salary)FROM employeesWHERE job_id=’IT_PROG’) AND job_id <> ’IT_PROG’;

#3.行子查詢(結(jié)果集一行多列或者多行多列)#案例1.查詢員工編號(hào)最小并且工資最高的員工信息.引入SELECT MIN(employee_id)FROM employees;=================SELECT MAX(salary)FROM employees;

SELECT *FROM employeesWHERE employee_id = ( SELECT MIN(employee_id) FROM employees)AND salary = ( SELECT MAX(salary) FROM employees );

這種查詢結(jié)果使用虛擬字段，單行操作符必須一致可以使用。查出來(lái)與上面同樣的效果。SELECT *FROM employeesWHERE (employee_id,salary)=( SELECT MIN(employee_id), MAX(salary) FROM employees );

#二.SELECT子查詢#僅僅支持標(biāo)量子查詢，結(jié)果是一行一列#案例1.查詢每個(gè)部門(mén)的員工個(gè)數(shù)SELECT d.*,(SELECT COUNT(*) FROM employees)FROM departments d;

添加條件SELECT d.*,(SELECT COUNT(*)FROM employees eWHERE e.department_id=d.department_id) AS 個(gè)數(shù)FROM departments d;

#案例2.查詢員工號(hào)=102的部門(mén)名。SELECT department_nameFROM departments;==============SELECT employee_idFROM employeesWHERE employee_id = 102;

SELECT employee_id,( SELECT department_name FROM departments d WHERE e.department_id=d.department_id)FROM employees eWHERE employee_id=102;

#三.FROM 后面注意:將子查詢結(jié)果充當(dāng)一張表，要求必須起別名#案例:查詢每個(gè)部門(mén)的平均工資等級(jí)。SELECT ROUND(AVG(salary),2),department_idFROM employeesGROUP BY department_id;

SELECT e.平均工資,j.grade_levelFROM job_grades AS j,(SELECT ROUND(AVG(salary),2) AS 平均工資,department_id FROM employees GROUP BY department_id) AS eWHERE e.平均工資 BETWEEN j.lowest_sal AND j.highest_sal;

#1999語(yǔ)法，老師答案SELECT e.*,j.grade_levelFROM (SELECT ROUND(AVG(salary),2) AS 平均工資,department_idFROM employeesGROUP BY department_id) AS eINNER JOIN job_grades jON e.平均工資 BETWEEN j.lowest_sal AND j.highest_sal;

#四.EXISTS后面(相關(guān)子查詢)語(yǔ)法:EXISTS(完整的查詢語(yǔ)句)備注:完整的查詢語(yǔ)句可以是一行一列，可以使一行多列注意:先走外查詢，然后根據(jù)某個(gè)字段的值再去過(guò)濾EXISTS 判斷(布爾類型)值存不存在,結(jié)果只有兩種:1有，0沒(méi)有#引入SELECT EXISTS(SELECT employee_id FROM employees);

查詢工資3W的員工信息SELECT EXISTS(SELECT * FROM employees WHERE salary=30000);

#案例引入.查詢員工名和部門(mén)名#查員工名與部門(mén)編號(hào)SELECT first_name,department_idFROM employeesWHERE department_id;

#查部門(mén)名SELECT department_nameFROM departments;

#查員工名與部門(mén)名SELECT e.first_name,d.department_nameFROM employees eINNER JOIN ( SELECT department_name,department_id FROM departments) AS dON e.department_id=d.department_id;

#案例1..查有員工的部門(mén)名SELECT department_nameFROM departments dWHERE EXISTS( SELECT * FROM employees e WHERE d.department_id=e.department_id );

使用IN代替EXISTS，同樣是上面的結(jié)果SELECT department_nameFROM departments dWHERE d.department_id IN( SELECT department_id FROM employees );

#案例2.查詢沒(méi)有女朋友的男神信息#IN方法SELECT *FROM boys boWHERE bo.id NOT IN( SELECT boyfriend_id FROM beauty be);===============#EXISTS方法SELECT *FROM boys boWHERE NOT EXISTS( SELECT boyfriend_id FROM beauty be WHERE bo.id=be.boyfriend_id);

進(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í)。網(wǎng)頁(yè)搜索內(nèi)容，內(nèi)容從不同的表中檢索聯(lián)合起來(lái)返回給用戶。特點(diǎn):1.要求多條查詢語(yǔ)句的查詢列數(shù)是一致的。2.要求多條查詢語(yǔ)句的查詢的每一列的類型和順序最好一致。3.使用UNION關(guān)鍵字默認(rèn)去重，如果使用UNION ALL全部展示，包含重復(fù)項(xiàng)

感謝能認(rèn)真讀到這里的伙伴們，MySql查詢部分結(jié)束，相信屏幕前的你照著我博客里的模板可以完成一些簡(jiǎn)單的SQL查詢語(yǔ)句，SQL既然學(xué)了，以后還是要多練習(xí)一下，SQL1992與1999語(yǔ)法在主流的關(guān)系型數(shù)據(jù)庫(kù)都是通用的。后續(xù)我會(huì)繼續(xù)進(jìn)行對(duì)MySql的知識(shí)進(jìn)行擴(kuò)展，感興趣的同志互相關(guān)注一唄！o(^?^)o

到此這篇關(guān)于MySql中子查詢內(nèi)查詢示例詳解的文章就介紹到這了,更多相關(guān)MySql 子查詢內(nèi)查詢內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！