--------------------------------查詢部分------------------------------------
-->全部查詢
SELECT * FROM stuInfo;

-->條件查詢
SELECT * FROM stuInfo WHERE stuNO=10001;

-->統(tǒng)計(jì)函數(shù)查詢
SELECT COUNT(*) AS '行數(shù)' ,AVG(labExam) AS '平均數(shù)',
MIN(labExam) AS '最小值' ,MAX(labExam) AS '最大值'
FROM stuMark;

-->TOP 應(yīng)用
SELECT TOP 3 * FROM stuInfo;

-->表間查詢
SELECT * FROM stuInfo AS info,stuMark AS mark???--?常用形式
WHERE info.stuNO = mark.stuNO;
SELECT * FROM stuInfo AS info INNER JOIN stuMark AS mark?--?內(nèi)連接
ON info.stuNO = mark.stuNO;
SELECT * FROM stuInfo AS info RIGHT JOIN stuMark AS mark?--?右連接
ON info.stuNO = mark.stuNO;
SELECT * FROM stuInfo AS info LEFT JOIN stuMark AS mark??--?左連接
ON info.stuNO = mark.stuNO;?
SELECT * FROM stuInfo AS info FULL JOIN stuMark AS mark??--?全連接
ON info.stuNO = mark.stuNO;

-->分組統(tǒng)計(jì)
SELECT COUNT(*) FROM stuInfo GROUP BY stuSex;

-->排序查詢
SELECT * FROM stuInfo ORDER BY stuNO DESC;
SELECT * FROM stuInfo ORDER BY stuNO ASC;

-->嵌套查詢
SELECT * FROM stuInfo AS info INNER JOIN stuMark AS mark
ON info.stuNO = mark.stuNO
WHERE mark.labExam > (SELECT AVG(labExam) FROM stuMark)

-->獲取兩個(gè)表中同種類型所有數(shù)值 ，
SELECT stuNO FROM stuInfo UNION SELECT stuNO FROM stuMark??--?去重復(fù)
SELECT stuNO FROM stuInfo UNION ALL SELECT stuNO FROM stuMark??--?不去重復(fù)
SELECT stuAge FROM stuInfo UNION SELECT stuNO FROM stuMark??--?相同類型羅列
SELECT * FROM stuMark WHERE stuNO IN(SELECT stuNO FROM stuInfo UNION SELECT stuNO FROM stuMark)

-->獲取一個(gè)表中數(shù)據(jù)不在另一個(gè)表中的所有值
SELECT stuNO FROM stuInfo EXCEPT SELECT stuNO FROM stuMark??--?在信息表中而不再成績(jī)表中學(xué)生的學(xué)號(hào)

-->獲取兩張表中共有的數(shù)據(jù)
SELECT stuNO FROM stuInfo INTERSECT SELECT stuNO FROM stuMark??--?成績(jī)表中和信息表中共有的學(xué)生學(xué)號(hào)

?

轉(zhuǎn)載于:https://blog.51cto.com/2950606/780187

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的SQL syntax-log2的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。