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

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

說明:有些查詢結(jié)果出來結(jié)果截圖與題目要求不一樣會出現(xiàn)多余的字段是為了方便展示結(jié)果的可讀性。實際操作的讀者可以刪除SELECT后面多余的字段得到正確的結(jié)果。

#WHERE或HAVING后面#1.標量子查詢(單行子查詢)#2.列子查詢(多行子查詢)#3.行子查詢(多列多行)#特點:# ①子查詢放在小括號內(nèi)# ②子查詢一般放在條件的右側(cè)# ③標量子查詢:一般搭配著單行操作符使用# 單行操作符: > < >= <= <> !-# 列子查詢，一般搭配著多行操作符使用# IN,ANY/SOME(任意),ALL# ④子查詢的執(zhí)行優(yōu)先與主查詢執(zhí)行,主查詢的條件用到了子查詢的結(jié)果。

#1.標量子查詢#案例1:誰的工資比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號員工相同，salary比143號員工多的員工姓名，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 工種編號,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 工種編號FROM employeesWHERE salary=( SELECT MIN(salary) FROM employees );

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

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

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

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

#②查詢員工姓名，要求部門號是①列表的某一個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的部門中的所有員工姓名。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部門任意一工資低的員工工號,# 姓名,job_id以及salary#①把IT_PROG部門中的工資查出來SELECT DISTINCT salaryFROM employeesWHERE job_id=’IT_PROG’;

#②把不是IT_PROG部門信息查出來SELECT *FROM employeesWHERE job_id != ’IT_PROG’;

#③合并①與②在員工表中查出來SELECT employee_id AS 員工編號,CONCAT(last_name,first_name) AS 姓名,job_id AS 工種編號,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 員工編號,CONCAT(last_name,first_name) AS 姓名,job_id AS 工種編號,salary AS 工資FROM employeesWHERE job_id <> ’IT_PROG’AND salary<( SELECT MAX(salary) FROM employees WHERE job_id=’IT_PROG’ );

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

SELECT employee_id AS 員工號,CONCAT(last_name,first_name) AS 姓名,job_id AS 工種編號,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 員工號,CONCAT(last_name,first_name) AS 姓名,job_id AS 工種編號,salary AS 工資FROM employeesWHERE salary<(SELECT MIN(salary)FROM employeesWHERE job_id=’IT_PROG’) AND job_id <> ’IT_PROG’;

#3.行子查詢(結(jié)果集一行多列或者多行多列)#案例1.查詢員工編號最小并且工資最高的員工信息.引入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é)果使用虛擬字段，單行操作符必須一致可以使用。查出來與上面同樣的效果。SELECT *FROM employeesWHERE (employee_id,salary)=( SELECT MIN(employee_id), MAX(salary) FROM employees );

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

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

#案例2.查詢員工號=102的部門名。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é)果充當一張表，要求必須起別名#案例:查詢每個部門的平均工資等級。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語法，老師答案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)子查詢)語法:EXISTS(完整的查詢語句)備注:完整的查詢語句可以是一行一列，可以使一行多列注意:先走外查詢，然后根據(jù)某個字段的值再去過濾EXISTS 判斷(布爾類型)值存不存在,結(jié)果只有兩種:1有，0沒有#引入SELECT EXISTS(SELECT employee_id FROM employees);

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

#案例引入.查詢員工名和部門名#查員工名與部門編號SELECT first_name,department_idFROM employeesWHERE department_id;

#查部門名SELECT department_nameFROM departments;

#查員工名與部門名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..查有員工的部門名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.查詢沒有女朋友的男神信息#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);

進階9:聯(lián)合查詢UNION 聯(lián)合 合并:將多條查詢語句的結(jié)果合并成一個結(jié)果。語法: 查詢語句1 UNION 查詢語句2 UNION ...應用場景:要查詢的結(jié)果來自于多個表，且多個表沒有直接的連接關(guān)系，但查詢信息一致時。網(wǎng)頁搜索內(nèi)容，內(nèi)容從不同的表中檢索聯(lián)合起來返回給用戶。特點:1.要求多條查詢語句的查詢列數(shù)是一致的。2.要求多條查詢語句的查詢的每一列的類型和順序最好一致。3.使用UNION關(guān)鍵字默認去重，如果使用UNION ALL全部展示，包含重復項

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

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