MYSQL必知必會2

文章目錄

• 使用資料處理函數
• 彙總資料
• 分組資料
• 使用子查詢
• 聯結表
• 進階聯結

使用資料處理函數

MySQL中常用的文本處理函數:

• Left(“xxxx”,len) :表示從左邊開始算起擷取xxx字元串的len個字元.如果len是0或者負數，那麼傳回的就是一個空串，否則len大于xxx的長度，傳回就是xxx字元串，否則就是len個字元.

  

  

• Right(“xxx”,len):和Left(“xxx”,len)用法基本相同，隻不過是從右邊開始算起的。
• Length(“xxx”)：擷取xxx的長度
• Lower(“xxx”)：擷取xxx的小寫
• Upper(“xxx”)：擷取xxx的大寫
• Trim(“xxx”)：去除xxx首尾的空格
• RTrim(“xxx”): 去除xxx右邊的空格
• LTrim(“xxx”):去除xxx左邊的空格
• Locate(“x”,“yyyy”):擷取x是yyy的第幾個字元，如果在yyy中沒有，則傳回0，如果有，并且如果有多個的話，那麼傳回的是第一次出現x的位置.

  

• Substring(“xxx”,positon):擷取xxx的一個子串。

  如果position等于0或者絕對值大于xxx的長度，那麼得到的是空串；

  否則，如果position是大于0，并且小于xxx長度的正數，那麼表示從左邊開始算起的第position個字元，到xxx最後一個字元結束的子串。

  如果position是一個負數，并且絕對值小于xxx的長度，得到的是從右邊開始往左邊算第positon個字元開始，一直到xxx最後一個字元結束的子串。

  

• Soundex(“xxx”):通過發音進行比較的。進而擷取和xxx發音相似的資料。

在MySQL中，日期和時間采用相應的資料類型和特殊的格式存儲，以便能快速和有效地排序或過濾，并且節省實體存儲空間。而在MySQL中，因為日期和時間函數總是被用來讀取、統計和處理一些數值，是以，日期和時間函數在MySQL中具有重要的作用。

常見的日期和時間處理函數:

但是假設我們需要檢索某一個月下的資料，應該怎麼辦呢？

有人會說，我是用BETWEEN … AND …不就好啦。例如2001-08月下的資料，我們隻要BETWEEN “2001-08-01” AND "2001-08-31"既可以檢索到在2001-08的所有資料了。然而，這樣寫存在一些問題：我們需要明确知道目前這個月份有多少天才可以確定BETWEEN… AND… 的範圍是正确的，并且如果是2月，那麼我們還需判斷年份是否為閏年。是以使用BETWEEN…AND…很麻煩。是以MySQL推薦我們使用Year(date)來擷取date的年份，使用Month(date)來擷取date的月份，通過這樣的操作，我們就可以檢索出給出日期這一個月份的資料了。

在MySQL中常見的數值處理函數有:

彙總資料

在MySQL中常見的聚集函數有:

值得注意的是，上面4個聚集函數（除了Count函數比較特殊）對于NULL是忽略的，而Count()之是以說是特殊，是因為如果Count(*)這樣寫的話，那麼不會忽略NULL值，而是将NULL作為新的一行輸出，并且會統計NULL的值，而如果是Count(column)統計column這一列的時候，就會忽略這一列的NULL值。

MAX()傳回指定列中的最大值，是以必須要指定列名。MIN()函數傳回指定列的最小值，是以同樣需要指定列名。用法和MAX()函數相似，不僅可以進行數字或者日期的比較，同樣可以對非數值資料進行比較。

然而将聚集函數使用在WHERE中會怎樣呢？

例如下面的例子:

這時候出現了InValid use of group function,提示我們聚集函數的不合法利用。但是為什麼呢？

這是因為聚集函數是不可以使用在WHERE子句後面的,具體原因如果有大佬知道的話，請指教哈！

分組資料

分組是在SELECT語句中的GROUP BY子句中建立的。

在講解GROUP BY的用法的時候，我們先來看一下它的使用準則：

• GROUB BY子句可以包含任意數目的列。這使得能對分組進行嵌套，為資料分組提供更細緻的控制。
• 如果在GROUP BY子句中嵌套了分組，資料将在最後規定的分組上進行彙總。換句話說，在建立分組時，指定的所有列都一起計算（是以不能從個别的列取回資料）。
• GROUP BY子句中列出的每個列都必須是檢索列或有效的表達式（但不能是聚集函數）。如果在SELECT中使用表達式，則必須在GROUP BY子句中指定相同的表達式。不能使用别名。
• 除聚集計算語句外，SELECT語句中的每個列都必須在GROUP BY子句中給出。

  

• 如果分組列中含有NULL值，則NULL将作為一個分組傳回。如果列中有多行NULL值，他們将分為一組。但是是否對NUL進行統計，這時候需要取決于聚集函數Count()，例如提到了Count()聚集函數相對特殊，如果是Count(*)，那麼這時候并不會忽略NULL，否則，如果是Count(column)來計算column這一列，那麼可以忽略NULL值。

  

• GROUP BY子句必須出現在WHERE子句之後，ORDER BY子句之前。盡管上面的例子中利用GROUP BY輸出的vent_id是有序的，但是也有可能輸出的不是有序的，是以需要利用ORDER BY來實作分組的有序。

  

  

  

  除了能用GROUP BY分組資料外，MySQL還允許過濾分組，規定包括哪些分組，排除哪些分組。例如可能想要列出至少有兩個訂單的所有顧客。為了得出這種資料，必須基于完整的分組而不是個别的行進行過濾。

  因為WHERE過濾的是指定的行而不是分組，是以此時需要利用到了HAVING.兩者的唯一差別在于WHERE過濾的是行，而HAVING過濾的是分組.是以此時，在利用GROUP BY進行分組之後，利用HAVING過濾掉一些不符合條件的分組，此時作用和WHERE一樣，同樣是實作過濾的效果，隻是針對的對象不同而已。

  

  

那麼是不是說，有了HAVING，就不需要使用WHERE了嗎？答案是否定的，因為在一些情況下，需要針對行來進行過濾，此時就會用到了WHERE.

是以學到現在，我們将可以知道SELECT語句格式将進一步完善為:

SELECT

XXX1,XXX2,XXX3,…XXXN

FROM table_name

WHERE XXXXX – 利用WHERE來對行進行過濾

GROUP BY yyy – 利用yyy進行分組

HAVING ZZZ – 利用HAVING來對分組進行過濾

ORDER BY TTT – 利用TTT進行升序排序，如果需要實作降序排序，那麼需要在TTT地後面添加DESC關鍵詞

LIMIT m OFFSET n; – 從行n開始檢索（即第n + 1行）,傳回不多于m行，之是以說不多于，是因為可能最後資料少于m行，是以隻能将所有的資料傳回

使用子查詢

SQL允許建立子查詢，即嵌套在其他查詢中的查詢。而子查詢主要用在下面幾種地方：

• 使用子查詢進行過濾

  例如下面的例子，需要查詢每個部門的員工資訊的時候，我們使用子查詢：

  

  

  

• 作為計算字段使用子查詢

  

聯結表

在了解聯結之前，有必要了解一下關系表。

分解資料為多個表能更有效的存儲和處理，并且有更大的可伸縮性。但是這些好處是有代價的。如果将資料從存放再多個表中怎樣用單條SELECT語句檢索出資料？這時候就要依賴聯結了。

而建立聯結的方式有兩種：

• SELECT XXX1,XXX2,…XXXN FROM talbe_name1,table_name2 WHERE table_name1的某一列 = table_name2的主鍵（table_name2的主鍵也是table_name1的外鍵）。

• SELECT XXX1,XXX2,…XXXN FROM

  table_name1 INNER JOIN table_name2

  ON table_name1的某一列 = table_name2的主鍵（table_name2的主鍵也是table_name1的外鍵）.此時的ON相當于WHERE，是以對應的條件就是上面WHERE的條件

  

而對于第一條文法中，WHERE子句作為過濾條件，它隻包含那些比對給定條件（這裡是聯結條件的行），如果沒有使用WHERE，那麼就會将第一個表中的每一行和第二個表中的每一行進行配對，是以會輸出table_row1 * table_row2行資料。這就是笛卡爾積。

值得注意的是，并不是說使用了ON就不可以在使用WHERE了，隻是兩者的順序有要求，如果需要在ON中使用WHERE進行過濾的話，那麼需要将WHERE放在ON之後，否則就會發生報錯。

是以在明白WHERE需要放在ON子句之後，那麼我們也将知道了GROUP BY\HAVING\ORDER BY\LIMIT這些子句同樣放在ON之後。

進階聯結

在講解進階來聯結之前，我們先來講一下表的别名。我們之前講到，可以利用AS來對列起别名，那麼同樣可以利用AS對表起别名。通過給表起别名，不僅可以縮短MySQL語句，同時含可以允許在單條SELECT語句中多次使用相同的表。

值得注意的是，表别名隻在查詢中使用。與列别名不同，表别名不會傳回到客戶機。

• 自聯結：就是自己和自己進行連接配接。例如下面的例子，統計和name = "小胡"同一個部門的員工:

使用自聯結中需要格外注意的問題是:因為是自聯結，是以輸出的時候必須明确輸出的是哪個表中的資料，否則就會發生報錯。因為兩個表中都存在相同名字的列，進而導緻MySQL無法辨認要輸出的是哪張表的資料。同時過濾的時候，需要格外注意，不可以是利用輸出表來進行判斷過濾，即類似上面的例子，不可以利用e1.name = "小胡”過濾.

• 自然聯結:所謂自然聯結，就是說在輸出聯結表中的所有資料的時候，可能會出現多個名字相同的列，是以為了避免出現這種情況，推出了自然聯結。

  

• 外聯結：使用外部聯結，最後輸出的時候包括了沒有關聯的行。此時利用的不再是INNER JOIN (這個是内部聯結），而是OUTER JOIN，并且常常配合LEFT \ RIGHT使用。而LEFT \ RIGNT用來表示最後輸出的是OUTER JOIN哪邊的表的所有行。例如是LEFT OUTER JOIN，最後輸出的是包括左邊表的所有行。否則，如果是RIGHT OUTER JOIN，那麼輸出的是包括右邊表的所有行.

上面輸出的列是普通的列，那麼我們可以輸出聚集函數的列嗎？答案當然可以了，上面也已經提到了，因為ON得作用相當于WHERE，是以需要将GROUP BY\ HAVING\ORDER BY\LIMIT放在ON得後面。

然而下面的例子中發生了報錯，錯誤原因請大佬指教.

通過上面得學習，我們需要總結一下使用聯結和聯結條件：