MySQL 語法整理（2）

接著上一篇博客，我們繼續來學習MySQL的語法，本篇重點講多表查詢，包括嵌套多層的查詢，大家做好心里準備哈！

廢話不多說，我們先來講第一個陳述句，union：將查詢結果合并在一起并去重，前面我們學了自然連接，是橫向增加表的鍵，那么這個union就是縱向增加結果表的記錄，

/*比如我想查詢2009年秋季和2010年春季開課的course_id，以及該課上課的房間號*/
(
	SELECT course_id , room_number
	FROM section
	WHERE semester='Fall' AND YEAR = 2009
)
UNION -- union會自動幫我們的結果去重
(
	SELECT course_id , room_number
	FROM section
	WHERE semester = 'Spring' AND YEAR = 2010
);

/*將結果合并在一起，但是不去重*/
(
	SELECT course_id
	FROM section
	WHERE semester='Fall' AND YEAR=2009
)
UNION ALL
(
	SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR=2010
);

union查詢結果如圖

上述我們是找到2009年秋季和2010年春季開課的課程，其實實際是對兩個查詢結果進行了集合并運算，那假如我現在想找出既在2009年秋季開課，同時也在2010年春季的課程呢？可以發現，我這次是對兩個查詢結果做了集合交運算，很可惜，MySQL沒有一個單詞或者陳述句來直接實作這個操作，但是我們可以通過where條件篩選來實作

/*通過對兩個表where來實作集合交運算*/
SELECT T.course_id
FROM
	(SELECT course_id
	FROM section
	WHERE  semester='Fall' AND YEAR = 2009
	) AS T , -- T表儲存的是2009年秋季開課的course_id
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR = 2010
	) AS S -- S表儲存的是2010年春季開課的course_id
WHERE T.course_id = S.course_id;  -- 這里的T中course_id會逐個與S中的course_id進行比較，相等的話就保存到
-- 結果表中

這就是集合交運算，那么同理啊，集合差運算也是類似

/*通過where中not in 操作實作集合差*/
/*這里我們想找出只在2009年秋季開課而不在2010年春季開課的課程*/
SELECT T.course_id
FROM
	(SELECT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009) AS T
WHERE T.course_id NOT IN -- 這里用到了not in
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR = 2010);

/*主意啊，上述兩個表的順序一但調換，結果是不同的！！大家可以自己理解一下為什么不同*/
/*這里我們想找出只在2010年春季開課而不在2009年秋季開課的課程*/
SELECT S.course_id
FROM
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR = 2010) AS S
WHERE S.course_id NOT IN
	(SELECT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009);

查詢方法與上面類似，當T集合的一條記錄在S集合中沒有出現，此時就會回傳真，并把該記錄儲存到結果表中，

接下來我們來學一下一些常用函式，這些也為我們后續學習做好基礎

/*利用avg或者其他函式求值*/
SELECT AVG(salary) AS avg_salary
FROM instructor
WHERE dept_name = 'Comp. Sci.';

/*通常利用這個來查找表中有多少行*/
SELECT COUNT(*) AS number
 FROM course;

/*還有像max()還有min()函式大家可以自行去練習，這里我就不舉例子了*/

學習這些函式有什么作用呢？很大程度是為我們下面要學的命令做鋪墊，group by：顧名思義，就是將表的各種記錄分組，

接下來給大家舉個例子，讓大家對group by有個更好的掌握

 /*統計一下2010年春季有各個系的老師有多少人開課*/
 /*group by 后面接的select陳述句語法比較嚴格，需要謹慎，一般接函式或者分組的鍵，比如這里的dept_name*/
SELECT dept_name , COUNT(DISTINCT instructor.ID) AS inst_count
FROM instructor , teaches
WHERE instructor.ID = teaches.ID AND -- 第一個=是找出某個老師上的課的記錄
	semester = 'Spring' AND  -- 第二個=是在上一個=的基礎上，找出春季開課，也就是找出該老師春季教的課
	YEAR = 2010  -- 第三個=在上面的結果中，找出是2010年開的課，可以發現三個=其實一直在做集合交運算
GROUP BY dept_name; -- 注意group by是在where后面的，對結果表進行分組

這是最終的查詢結果，可以發現，每一個分組，就只有一個記錄，因此select最后必須有代表這個組的鍵

這個時候給大家一個問題：我能不能select這些組中的name鍵，如下代碼所示：

SELECT dept_name , NAME -- 這樣做的意義是什么？
FROM instructor , teaches
WHERE instructor.ID = teaches.ID AND
	semester = 'Spring' AND
	YEAR = 2010
GROUP BY dept_name;

事實上我的MySQL是可以執行的，但是這個陳述句是錯誤的，大家可以看到，最后select是對不同組的表選擇鍵，比如我可以選擇各個組表的dept_name，或者各個組表的平均工資等等，但是要選擇老師名字是什么意思？比如計算機系中有三個老師，我該選擇哪一個老師的名字呢？可以發現是毫無意義的查詢，在Oracle或者其他資料庫中這個陳述句是會報錯的！因此：group by 后面接的select陳述句語法比較嚴格，需要謹慎，一般接函式或者分組的鍵，比如這里的dept_name，

上述我們學習了group by陳述句，那假如我想找出系平均工資大于42000的系呢？這個時候，having陳述句可以很好的幫我們解決這個問題，having：對gruop by的結果再進行選擇

SELECT dept_name,AVG(salary) AS avg_salary
 FROM instructor
 GROUP BY dept_name
 HAVING AVG(salary) > 42000; -- 這里是篩選group by之后的結果

學到這里其實我們已經可以做很多查詢了，接下來我會給大家一個復雜點的例子，

 /*求出2009年兩個學期中有超過兩個學生去學習的課程的平均學分*/
 SELECT course_id,semester,YEAR,sec_id,AVG(tot_cred)
-- FROM takes,student
-- WHERE takes.ID = student.ID AND YEAR = 2009
FROM takes NATURAL JOIN student -- 上面的takes.ID = student.ID相當于NATURAL JOIN，表示找出學生上過的課的記錄
WHERE YEAR = 2009
/*這里按如下順序進行分組*/
GROUP BY course_id,semester,YEAR,sec_id -- 比如當course_id組中按semester又分多個組，如此繼續分，
-- 這里給大家思考，如果按主鍵分組，我們最后可以得到什么，其實就是得到原表的每一條記錄！
HAVING COUNT(takes.ID)>=2;

查詢結果如圖

limit：限定查詢結果的記錄數目，通常配合一些函式，

/*limit陳述句用于篩選一定數目的記錄，當實際記錄少于限定的記錄也沒關系*/
/*這里找出平均工資前二的系名和該系的平均工資*/
SELECT dept_name,AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
ORDER BY avg_salary DESC
LIMIT 2; -- limit是對結果表的記錄進行限制，所以放在一定放在where，group by后面
/*
LIMIT 0,5 -- 表示顯示第一到第五條的記錄，注意記錄是從0下表開始算的，

group by + with rollup：表示在進行分組統計的基礎上再次對每一小組進行匯總統計

/*匯總一般是求一個sum*/
SELECT dept_name , SUM(salary) , COUNT(*)
FROM instructor
GROUP BY dept_name WITH ROLLUP;

查詢記錄如圖，最后那一條就是with rollup的欄位，但是可以發現，對于無法統計的鍵dept_name是空值

本篇已經講解了很多內容，包括集合交，集合差以及一些函式和group by的命令，下一篇博客將繼續講解更多多表查詢的語法和命令，包括嵌套使用where和from，