IT工程師數位筆記本

一、mysql查詢的五種子句

where子句（條件查詢）：按照“條件表達式”指定的條件進行查詢。

group by子句（分組）：按照“屬性名”指定的字段進行分組。group by子句通常和count()、sum()等聚合函數一起使用。

having子句（篩選）：有group by才能having子句，只有滿足“條件表達式”中指定的條件的才能夠輸出。

order by子句（排序）：按照“屬性名”指定的字段進行排序。排序方式由“asc”和“desc”兩個參數指出，默認是按照“asc”來排序，即升序。

limit（限制結果集）。

1、where——基礎查詢

where常用運算符：

2、group by 分組

“Group By”從字面意義上理解就是根據“By”指定的規則對數據進行分組，所謂的分組就是將一個“數據集”劃分成若干個“小區域”，然后針對若干個“小區域”進行數據處理。

select 類別, sum(數量) as 數量之和
from A
group by 類別

注：group by語句中select指定的字段必須是“分組依據字段”，其他字段若想出現在select中則必須包含在聚合函數中。

mysql中五種常用的聚合函數：

（1）max(列名)：求最大值。

（2）min(列名)：求最小值。

（2）sum(列名)：求和。

（4）avg(列名)：求平均值。

（5）count(列名)：統計記錄的條數。

3、having

having子句可以讓我們篩選成組后的各種數據，where子句在聚合前先篩選記錄，也就是說作用在group by和having子句前。而 having子句在聚合后對組記錄進行篩選。 

示例：

select 類別, sum(數量) as 數量之和 from A
group by 類別
having sum(數量) > 18

示例：Having和Where的聯合使用方法

select 類別, SUM(數量)from A
where 數量 >8
group by 類別
having SUM(數量) >10

where和having的區別：

作用的對象不同。WHERE 子句作用于表和視圖，HAVING 子句作用于組。
WHERE 在分組和聚集計算之前選取輸入行（因此，它控制哪些行進入聚集計算）， 而 HAVING 在分組和聚集之后選取分組的行。因此，WHERE 子句不能包含聚集函數； 因為試圖用聚集函數判斷那些行輸入給聚集運算是沒有意義的。 相反，HAVING 子句總是包含聚集函數。（嚴格說來，你可以寫不使用聚集的 HAVING 子句， 但這樣做只是白費勁。同樣的條件可以更有效地用于 WHERE 階段。）
在上面的例子中，我們可以在 WHERE 里應用數量字段來限制，因為它不需要聚集。 這樣比在 HAVING 里增加限制更加高效，因為我們避免了為那些未通過 WHERE 檢查的行進行分組和聚集計算。
綜上所述：
having一般跟在group by之后，執行記錄組選擇的一部分來工作的。where則是執行所有數據來工作的。
再者having可以用聚合函數，如having sum(qty)>1000

例子：where + group by + having + 函數 綜合查詢

練習表：

查詢出兩門及兩門以上不及格者的平均成績(注意是所有科目的平均成績)

錯誤情況1：題意理解錯誤，理解成查出不及格科目的平均成績。

錯誤情況2：count()不正確，SQL錯誤。

count(a),無論a是什么，都只是數一行；count時，每遇到一行，就數一個a，跟條件無關！

正解：count(score<60)達不到想要的結果，并不是條件的問題，而是無論count()里的表達式是什么都會數一行。score<60 返回 1 或 0；所以可以用sum(score<60)來計算不及格的科目數！

4、order by 排序

（1）order by price  //默認升序排列
（2）order by price desc //降序排列
（3）order by price asc //升序排列，與默認一樣
（4）order by rand() //隨機排列，效率不高

5、limit

limit [offset,] N
offset 偏移量，可選，不寫則相當于limit 0,N
N  取出條目

示例：取價格第4-6高的商品

select good_id,goods_name,goods_price from goods order by good_price desc limit 3,3;

總結：

select子句順序

二、mysql子查詢

1、where型子查詢（把內層查詢結果當作外層查詢的比較條件）

（1）查詢id最大的一件商品(使用排序+分頁實現)

SELECT goods_id,goods_name,shop_price FROM goods ORDER BY goods_id DESC LIMIT 1;

（2）查詢id最大的一件商品(使用where子查詢實現)

SELECT goods_id,goods_name,shop_price FROM goods WHERE goods_id = (SELECT MAX(goods_id) FROM goods);

（3）查詢每個類別下id最大的商品(使用where子查詢實現)

SELECT goods_id,goods_name,cat_id,shop_price FROM goods WHERE goods_id IN (SELECT MAX(goods_id) FROM goods GROUP BY cat_id);

2、from型子查詢(把內層的查詢結果當成臨時表，供外層sql再次查詢。查詢結果集可以當成表看待。臨時表要使用一個別名。)

（1）查詢每個類別下id最大的商品(使用from型子查詢)

 SELECT goods_id,goods_name,cat_id,shop_price FROM
(SELECT goods_id,goods_name,cat_id,shop_price FROM goods ORDER BY cat_id ASC,goods_id DESC) AS tmp
GROUP BY cat_id;

子查詢查出的結果集看第二張圖，可以看到每個類別的第一條的商品id都為該類別下的最大值。然后將這個結果集作為一張臨時表，巧妙的使用group by 查詢出每個類別下的第一條記錄，即為每個類別下商品id最大。

3、exists型子查詢（把外層sql的結果，拿到內層sql去測試，如果內層的sql成立，則該行取出。內層查詢是exists后的查詢。）

（1）從類別表中取出其類別下有商品的類別(如果該類別下沒有商品，則不取出)[使用where子查詢]

SELECT c.cat_id,c.cat_name FROM category c WHERE c.cat_id IN (SELECT g.cat_id FROM goods g GROUP BY g.cat_id);

（2）從類別表中取出其類別下有商品的類別(如果該類別下沒有商品，則不取出)[使用exists子查詢]

 SELECT c.cat_id,c.cat_name FROM category c WHERE EXISTS (SELECT 1 FROM goods g WHERE g.cat_id = c.cat_id);

exists子查詢，如果exists后的內層查詢能查出數據，則表示存在；為空則不存在。

三、連接查詢

學習連接查詢，先了解下"笛卡爾積"，看下百度給出的解釋：

      在數據庫中，一張表就是一個集合，每一行就是集合中的一個元素。表之間作聯合查詢即是作笛卡爾乘積，比如A表有5條數據，B表有8條數據，如果不作條件篩選，那么兩表查詢就有 5 X 8 = 40 條數據。

先看下用到的測試表基本信息：我們要實現的功能就是查詢商品的時候，從類別表將商品類別名稱關聯查詢出來。

行數：類別表14條，商品表4條。

結構：商品表和類別表都有一個cat_id

1、全相乘(不是全連接、連接查詢)，全相乘是作笛卡爾積

兩表全相乘，就是直接從兩張表里查詢；從查詢的截圖看出，總共查出了 4 X 14 = 56 條記錄，這些記錄是笛卡爾乘積的結果，即兩兩組合；

但我們要的是每個商品信息顯示類別名稱而已，這里卻查出了56條記錄，其中有52條記錄都是無效的數據，全相乘的查詢效率低。

SELECT goods_id,goods_name,cat_name FROM mingoods,category;

如果在兩張表里有相同字段，做聯合查詢的時候，要區別表名，否則會報錯誤(模糊不清)。

SELECT goods_name,cat_id,cat_name FROM mingoods,category;　

       添加條件，使兩表關聯查詢，這樣查出來就是商品和類別一一對應了。雖然這里查出來4條記錄，但是全相乘效率低，全相乘會在內存中生成一個非常大的數據(臨時表)，因為有很多不必要的數據。

　   如果一張表有10000條數據，另一張表有10000條數據，兩表全相乘就是100W條數據，是非常消耗內存的。而且，全相乘不能好好的利用索引，因為全相乘生成一張臨時表，臨時表里是沒有索引的，大大降低了查詢效率。

SELECT g.goods_name,g.cat_id AS g_cat_id, c.cat_id AS c_cat_id, c.cat_name FROM mingoods g, category c WHERE g.cat_id = c.cat_id;

2、左連接查詢 left join ... on ...

語法：

select A.filed, [A.filed2, .... ,] B.filed, [B.filed4...,] from <left table> as A  left join <right table> as B on <expression>

假設有A、B兩張表，左連接查詢即 A表在左不動，B表在右滑動，A表與B表通過一個關系來關聯行，B表去匹配A表。

2.1、先來看看on后的條件恒為真的情況

SELECT g.goods_name,g.cat_id, c.cat_id ,c.cat_name FROM mingoods g LEFT JOIN category c ON 1;

     跟全相乘相比，從截圖可以看出，總記錄數仍然不變，還是 4 X 14 = 56 條記錄。但這次是商品表不動，類別表去匹配，因為每次都為真，所以將所有的記錄都查出來了。左連接，其實就可以看成左表是主表，右表是從表。

2.2 、根據cat_id使兩表關聯行

SELECT g.goods_name,g.cat_id,c.cat_id,c.cat_name FROM mingoods g LEFT JOIN category c ON g.cat_id = c.cat_id;

使用左連接查詢達到了同樣的效果，但是不會有其它冗余數據，查詢速度快，消耗內存小，而且使用了索引。左連接查詢效率相比于全相乘的查詢效率快了10+倍以上。

　　左連接時，mingoods表(左表)不動，category表(右表)根據條件去一條條匹配，雖說category表也是讀取一行行記錄，然后判斷cat_id是否跟mingoods表的相同，但是，左連接使用了索引，cat_id建立了索引的話，查詢速度非常快，所以整體效率相比于全相乘要快得多，全相乘沒有使用索引。

2.3、查詢出第四個類別下的商品，要求顯示商品名稱

SELECT g.goods_name,g.cat_id,c.cat_name,g.shop_price FROM goods g LEFT JOIN category c ON g.cat_id = c.cat_id WHERE g.cat_id = 4;

2.4 、對于左連接查詢，如果右表中沒有滿足條件的行，則默認填充NULL。

SELECT g.goods_name,g.cat_id AS g_cat_id, c.cat_id AS c_cat_id,c.cat_id FROM mingoods g LEFT JOIN mincategory c ON g.cat_id = c.cat_id;

3、右連接查詢 right join ... on ...

語法：

select A.field1,A.field2,..., B.field3,B.field4  from <left table> A right join <right table> B on <expression>

右連接查詢跟左連接查詢類似，只是右連接是以右表為主表，會將右表所有數據查詢出來，而左表則根據條件去匹配，如果左表沒有滿足條件的行，則左邊默認顯示NULL。左右連接是可以互換的。

SELECT g.goods_name,g.cat_id AS g_cat_id,  c.cat_id AS c_cat_id,c.cat_name FROM mingoods g RIGHT JOIN mincategory c ON g.cat_id = c.cat_id;

4、內連接查詢 inner join ... on ...

語法：

select A.field1,A.field2,.., B.field3, B.field4 from <left table> A inner join <right table> B on <expression>

內連接查詢，就是取左連接和右連接的交集，如果兩邊不能匹配條件，則都不取出。

SELECT g.goods_name,g.cat_id, c.* from mingoods g INNER JOIN mincategory c ON g.cat_id = c.cat_id;

5、全連接查詢 full join ... on ...

語法：

select ... from <left table> full join <right table> on <expression>

全連接會將兩個表的所有數據查詢出來，不滿足條件的為NULL。

全連接查詢跟全相乘查詢的區別在于，如果某個項不匹配，全相乘不會查出來，全連接會查出來，而連接的另一邊則為NULL。

6、聯合查詢 union

語法：

select A.field1 as f1, A.field2 as f2 from <table1> A union (select B.field3 as f1, field4 as f2 from <table2> B)

union是求兩個查詢的并集。union合并的是結果集，不區分來自于哪一張表，所以可以合并多張表查詢出來的數據。

6.1、將兩張表的數據合并查詢出來

SELECT id, content, user FROM comment UNION (SELECT id, msg AS content, user FROM feedback);

6.2、union查詢，列名不一致時，以第一條sql語句的列名對齊

SELECT id, content, user FROM comment UNION (SELECT id, msg, user FROM feedback);

6.3、使用union查詢會將重復的行過濾掉

SELECT content,user FROM comment UNION (SELECT msg, user FROM feedback);

6.4、使用union all查詢所有，重復的行不會被過濾

SELECT content,user FROM comment UNION ALL (SELECT msg, user FROM feedback);

6.5、union查詢，如果列數不相等，會報列數不相等錯誤

6.6、union 后的結果集還可以再做篩選

SELECT id,content,user FROM comment UNION ALL (SELECT id, msg, user FROM feedback) ORDER BY id DESC; 

      union查詢時，order by放在內層sql中是不起作用的；因為union查出來的結果集再排序，內層的排序就沒有意義了；因此，內層的order by排序，在執行期間，被mysql的代碼分析器給優化掉了。

 (SELECT id,content,user FROM comment ORDER BY id DESC) UNION ALL (SELECT id, msg, user FROM feedback ORDER BY id DESC);

order by 如果和limit一起使用，就顯得有意義了，就不會被優化掉。

( SELECT goods_name,cat_id,shop_price FROM goods WHERE cat_id = 3 ORDER BY shop_price DESC LIMIT 3 )
UNION
( SELECT goods_name,cat_id,shop_price FROM goods WHERE cat_id = 4 ORDER BY shop_price DESC LIMIT 2 );

 6.7、練習

 SELECT name, SUM(money) FROM ( ( SELECT * FROM A ) UNION ALL ( SELECT * FROM B ) ) tmp GROUP BY name;

連接查詢總結：

1、在數據庫中，一張表就是一個集合，每一行就是集合中的一個元素。連接查詢即是作笛卡爾積，比如A表有1W條數據，B表有1W條數據，那么兩表查詢就有 1W X 1W = 100W 條數據　

2、如果在兩張表里有相同字段，做聯合查詢的時候，要區別表名，否則會報錯誤(ambiguous 模糊不清)

3、全相乘效率低，全相乘會在內存中生成一個非常大的數據(臨時表)，因為有很多不必要的數據。

 如果一張表有10000條數據，另一張表有10000條數據，兩表全相乘就是100W條數據，是非常消耗內存的。

而且，全相乘不能好好的利用索引，因為全相乘生成一張臨時表，臨時表里是沒有索引的，大大降低了查詢效率。

4、左連接查詢時，以左表為主表，會將左表所有數據查詢出來；左表不動，右表根據條件去一條條匹配，如果沒有滿足條件的記錄，則右邊返回NULL。

右連接查詢值，以右表為主表，會將右表所有數據查詢出來，右表不動，左表則根據條件去匹配，如果左表沒有滿足條件的行，則左邊返回NULL。　

左右連接是可以互換的：A left join B  ==  B right join A (都是以A為主表) 。

左右連接既然可以互換，出于移植兼容性方面的考慮，盡量使用左連接。

5、連接查詢時，雖說也是讀取一行行記錄，然后判斷是否滿足條件，但是，連接查詢使用了索引，條件列建立了索引的話，查詢速度非常快，所以整體效率相比于全相乘要快得多，全相乘是沒有使用索引的。

使用連接查詢，查詢速度快，消耗內存小，而且使用了索引。連接查詢效率相比于全相乘的查詢效率快了10+倍以上。

6、內連接查詢，就是取左連接和右連接的交集，如果兩邊不能匹配條件，則都不取出。

7、MySql可以用union(聯合查詢)來查出左連接和右連接的并集。

union查詢會過濾重復的行，union all 不會過濾重復的行。

union查詢時，union之間的sql列數必須相等，列名以第一條sql的列為準；列類型可以不一樣，但沒太大意義。

union查詢時，order by放在內層sql中是不起作用的；因為union查出來的結果集再排序，內層的排序就沒有意義了；因此，內層的order by排序，在執行期間，被mysql的代碼分析器給優化掉了。

 但是，order by 如果和limit一起使用，就顯得有意義了，會影響最終結果集，就不會被優化掉。order by會根據最終是否會影響結果集而選擇性的優化。

注：union和union all的區別，union會去掉重復的記錄，在結果集合并后悔對新產生的結果集進行排序運算，效率稍低，union all直接合并結果集，如果確定沒有重復記錄，建議使用union all。

8、 LEFT JOIN 是 LEFT OUTER JOIN 的縮寫，同理，RIGHT JOIN 是 RIGHT OUTER JOIN 的縮寫；JOIN 是 INNER JOIN 的縮寫。

關聯查詢

1、使用join關鍵字關聯查詢

（1）、內連接（inner join）

連接兩張表，連接條件使用on關鍵字，內連接只會顯示匹配的數據記錄。

eg:查詢學生姓名、科目、分數

select a.name 姓名,b.subject 科目,b.score 分數 from student a inner join score b on a.id = b.sid;

（2）、左連接（left join）

返回左表中所有記錄以及右表中符合連接條件的所有記錄。

eg: 使用左連接查詢學生姓名、科目、分數

select a.name 姓名,b.subject 科目,b.score 分數 from student a left join score b on a.id = b.sid;

（3）、右連接（right join）

返回右表中所有記錄以及左表中符合連接條件的所有記錄。

eg:使用右連接查詢學生姓名、科目、分數

select a.name 姓名,b.subject 科目,b.score 分數 from student a right join score b on a.id = b.sid;

注：內外連接區別：內連接只會顯示匹配的數據記錄，外連接例如左連接會把左邊表中所有記錄顯示出來，即使在右邊表中沒有匹配記錄也會顯示左表的數據，右連接反之。

2、使用表和表之間相同id關聯查詢

這種關聯方式和內連接一樣，只會顯示出匹配的數據

select a.name 姓名,b.subject 科目,b.score 分數 from student a,score b where a.id = b.sid;