EXISTS 和NOT EXISTS子查詢

EXISTS子查詢

語法:

SELECT ……… FROM 表名 WHERE EXISTS (子查詢);

例:

SELECT `studentNo` AS 學號,`studentResult` 成績 FROM `result`

WHERE EXISTS(

/*查詢LOgic Java最后一次考試成績大于80的記錄*/

SELECT * FROM `result` WHERE `subjectNo`=(

SELECT `subjectNo` FROM `subject` WHERE `subjectName` = `Logic Java`

)AND `examDate` = (

SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo`=(

SELECT `subjectNo` FROM `subject` WHERE `subjectName` = `Logic Java`

)

)AND `studentResult` >80

)AND `subjectNo`=(

SELECT `subjectNo` FROM `subject`? WHERE `subjectName` = `Logic Java`

)ORDER BY `studentResult` DESC LIMIT 5;

附注:EXISTS關鍵字后的參數是一個任意的子查詢,如果該子查詢有返回行,則EXISTS子查詢???????? 的結果為true ,此時再執行外層查詢,反之查詢結果沒有返回行,則為false,外層查詢將不???????? 執行.

NOT EXISTS子查詢

語法:

SELECT ……… FROM 表名 WHERE NOT EXISTS (子查詢);

例:

SELECT AVG(studentResult)+5 AS 平均分 FROM result

WHERE NOT EXISTS(

/*查詢Logic Java最后一個次考試成績小于60 的記錄*/

SELECT * FROM `result` WHERE `subjectNo` = (

SELECT `subjectNo` FROM `subject` WHERE `subjectName` = `Logic Java`

)AND `examDate` = (

SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (

SELECT `subjectNo` FROM `subject` WHERE `subjectName` = `Logic Java`

)

)AND `sutdentResult`>60

) AND `examDate` = (

SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (

SELECT `subjectNo` FROM `subject` WHERE `subjectName` = `Logic Java`

)

)

附注:

NOT EXISTS 為EXISTS的取反操作,表示不存在.

當子查詢沒有返回行時,結果為true,反之亦然.

子查詢注意事項

子查詢語句可以嵌套在SQL語句中任何表達式出現的位置

嵌套在SELECT語句中的SELECT子句中的子查詢如下:

語法:?SELECT (子查詢) FROM 表名;

3.嵌套在SELECT語句的FROM子句中的子查詢語句如下:

語法:?SELECT ?* ?FROM (子查詢) AS 別名;

4 只要出現在子查詢中而沒有出現在父查詢中的表不能包含在輸出列.

總結

以上是生活随笔為你收集整理的mysql高级查询 二_MySQL高级查询(二)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。