**目錄**
[TOC]
# 1 值與運算式
不論在執行查詢或資料異動的時候，你都可能會使用各種不同種類的值(literal values)來完成你的工作：
不同種類的值會有不同的用法與規定，可以搭配使用的運算子和函式也不一樣。根據資料類型可以分為下列幾種：
* 數值：可以用來執行算數運算的數值，包含整數與小數，分為精確值與近似值兩種
* 字串：使用單引號或雙引號包圍的文字
* 日期/時間：使用單引號或雙引號包圍的日期或時間
* 空值：使用「NULL」表示的值
* 布林值：「TRUE」或「1」表示「真」，「FALSE」或「0」表示「假」
## 1.1 數值
數值分為「精確值(exact-value)」與「近似值(approximate-value)」兩種。精確值在使用時不會因為進位而產生差異；使用近似值的時候，可能會因為進位而產生些微的差異。精確值使用一個明確的數字來表示一個整數或小數數值：
* 整數：沒有小數的數字，范圍從-9223372036854775808到9223372036854775807
* 小數：包含小數的數字，整數范圍與上面一樣，小數位數最多可以有30個
一般來說，使用精確值在執行各種算數運算的時候，所得到的結果都不會有誤差的問題，你只要特別注意范圍就可以了。例如下列這個比較奇怪的查詢需求：
包含小數的數字，在整數部份的限制與整數相同，小數位數會有這樣的限制：
近似值的的數字通常稱為「科學表示法」，它使用下列的方式來表示一個數值：
這兩種表示方式所代表的數值是這樣計算的：
* XE+Y，X * 10Y，例如5E+3，代表的數字為5000
* XE-Y，X * 10-Y，例如5E-3，代表的數字為0.005
注：「XE+Y」格式中的「+」可以省略，例如「5E+3」與「5E3」是一樣的。
使用近似值來表示一個數值的時候，你一定要牢記它是一個「近似值」，也就是它真正儲存的數值可能不是你所看到的。下列的情況是你比較容易理解的：
不過下列的狀況就會有不一樣的結果：
第一個運算值采用精確值的方式，所以它們一定會相等；第二個運算使用近似值的方式，所以它們不一定相等。
## 1.2?字串值
字串值是以單引號或雙引號包圍的文字資料，就文字資料來說，你不會拿文字執行加、減、乘、除這類的算數運算。如果你拿字串來執行算數運算的話，MySQL會先把字串中的內容轉換為數字，然后再執行算數運算：
如果字串內容包含不是數值的文字，MySQL在執行轉換的時候會出現警告訊息：
字串與字串可以執行連接的運算，就是把一些字串的內容連接起來后，產生一個新的字串。要執行字串連接的工作，可以使用「||」運算子，這個運算子在條件的判斷中是「或」的意思，如果你直接使用「||」運算子連接字串的話：
這是因為在預設的設定下，MySQL把「||」運算子當成數值的「或」運算，所以會出現這樣的情況；你可以透過設定MySQL的SQL模式，來改變這個預設處理方式：
~~~
SET sql_mode = 'PIPES_AS_CONCAT'
~~~
這個設定會把「||」運算子用在字串值的時候，把它當成「連接」運算子：
注：字串的連接也可以使用函式來處理，在這章的后面討論；另外字串的比較因為跟編碼有關，會在后面的章節詳細討論。
## 1.3?日期與時間值
日期與時間值(temporal values)有下列幾種：
* 日期：年年年年-月月-日日，`2007-01-01`
* 日期時間：年年年年-月月-日日 時時:分分:秒秒，`2007-01-01 12:00:00`
* 時間：時時:分分:秒秒：`12:00:00`
在日期與時間值中西元年的部份，可以使用四個或兩個數字。如果指定的兩個數字是「70」到「99」之間，就代表「1970」到「1999」；如果是「00」到「69」之間，就代表「2000」到「2069」。日期值中預設的分隔字元是「-」，你也可以使用「/」，所以「2000-1-1」與「2000/1/1」都是正確的日期值。
日期時間資料可以使用在條件的判斷外，也可以用來「運算」，不過當然不是數值的算數運算，而是「一個日期的36天后是哪一天」這類的運算，而且只能使用「+」與「-」的運算。它的語法是：
語法中的單位可以使用下列表格中的單位關鍵字：
* YEAR：年
* QUARTER：季
* MONTH：月
* DAY：日
* HOUR：時
* MINUTE：分
* SECOND：秒
注：上列「單位關鍵字」并沒有列出所有的單位關鍵字，全部的單位關鍵字請參考MySQL手冊「12.5\. Date and Time Functions」。
## 1.4 NULL值
「NULL」值的處理比任何其它型態的值都來得奇怪一些，它也是一個很常見的資料，可以用來表示「未知的資料」；而且它最特別的地方是「NULL值與其它任何值都不一樣，包含NULL自己」。
「NULL」是一個SQL關鍵字，大小寫都可以。你已經知道判斷一個欄位資料是否為「NULL」值的時候，跟其它一般資料判斷是不一樣的；如果算數運算式或比較運算式中有任何「NULL」值的話，結果都會是「NULL」：
~~~
SELECT NULL = NULL, NULL < NULL, NULL != NULL, NULL + 3
~~~
上列的查詢所得到的結果全部都是「NULL」。所以在比較「NULL」值的時侯要使用下列的方式：
# 2 函式
在你在執行查詢或維護資料的時候，可能會有下列這個比較特殊的需求：
以這樣的需求來說，你當然不用自己去計算兩個日期之間的天數，MySQL提供許多不同的函式(functions)，可以完成這類的需求，不論在執行查詢或維護的敘述中，都可以使用這些函式。函式基本的用法會像這樣：
注：MySQL規定函式預設的寫法是函式名稱和左括號之間不可以有任何空格，否則會造成錯誤；你可以執行`SET sql_mode='IGNORE_SPACE'`，這個設定讓你可以在函式名稱和左括號之間加入空格也不會出錯。
以上列「計算兩個日期之間的天數」來說，就會在查詢敘述中使用到這樣的函式：
MySQL提供的函式非常多，你不用把每一個函式的名稱和用法都背起來，就算是為了參加認證考試也一樣。這個章節只有介紹「部份」函式，并不是全部，所以你在了解這章討論的函式以后，需要到MySQL參考手冊中的「Chapter 12\. Functions and Operators」，進一步認識MySQL還有提供哪一些函式。
## 2.1 字串函式
字串資料的處理是一種很常見的工作，處理字串的函式也非常多，所以這里使用分類的方式來介紹。下列是處理字串內容的相關函式：
* LOWER(字串)：將[字串]轉換為小寫
* UPPER(字串)：將[字串]轉換為大寫
* LPAD(字串1, 長度, 字串2)：如果[字串1]的長度小于指定的[長度]，就在[字串1]左邊使用[字串2]補滿
* RPAD(字串1, 長度, 字串2)：如果[字串1]的長度小于指定的[長度]，就在[字串1]右邊使用[字串2]補滿
* LTRIM(字串)：移除[字串]左邊的空白
* RTRIM(字串)：移除[字串]右邊的空白
* TRIM(字串)：移除[字串]左、右的空白
* REPEAT(字串, 個數)：重復[字串]指定的[個數]
* REPLACE(字串1, 字串2, 字串3)：將[字串1]中的[字串2]替換為[字串3]
「LPAD」與「RPAD」在處理報表資料的時候，很常用來控制報表內容的格式。例如下列的需求：
使用「LPAD」函式讓查詢后得到的字串內容向右對齊：
下列是截取字串內容的函式：
* LEFT(字串, 長度)：傳回[字串]左邊指定[長度]的內容
* RIGHT(字串, 長度)：傳回[字串]右邊指定[長度]的內容
* SUBSTRING(字串, 位置)：傳回[字串]中從指定的[位置]開始到結尾的內容
* SUBSTRING(字串, 位置,?
長度)：傳回[字串]中從指定的[位置]開始，到指定[長度]的內容
下列是一個測試這些函式的查詢敘述：
下列是連接字串的函式：
* CONCAT(參數 [,…])：傳回所有參數連接起來的字串
* CONCAT_WS(分隔字串, 參數 [,…])：傳回所有參數連接起來的字串，參數之間插入指定的[分隔字串]
你可以使用「||」運算子連接字串，「CONCAT」函式也可以完成同樣的需求。唯一的差異是要先設定「sql_mode」為「PIPES_AS_CONCAT」后，才可以使用「||」運算子連接字串；而「CONCAT」函式不用執行任何設定就可以連接字串。
「CONCAT_WS」函式提供一種比較方便的字串連接功能，例如下列這個使用「||」運算子連接字串的查詢敘述：
改成使用「CONCAT_WS」函式的話，就會比較簡單一些：
注：「CONCAT」與「CONCAT_WS」兩個函式的參數可以接受任何型態的資料，它們都會把全部的資料轉為字串后連接起來；「CONCAT」函式的參數中如果有「NULL」值，結果會是「NULL」；「CONCAT_WS」函式的參數中如果有「NULL」值，「NULL」值會被忽略。
下列是取得字串資訊的函式：
* LENGTH(字串)：傳回[字串]的長度(bytes)
* CHAR_LENGTH(字串)：傳回[字串]的長度(字元個數)
* LOCATE(字串1, 字串2)：傳回[字串1]在[字串2]中的位置，如果[字串2]中沒有[字串1]指定的內容就傳回0
使用「LENGTH」函式可以完成類似「國家名稱長度排行榜」的查詢：
注：「LENGTH」與「CHAR_LENGTH」的差異在「第六章、字元集與資料庫」與「第七章、儲存引擎與資料型態」中會詳細的討論。
如果有需要的話，你也會搭配許多函式來完成你的工作，例如：
上列的敘述可以查詢「名稱是一個單字以上的國家」。
## 2.2 數學函式
下列是數值舍去與進位的函式：
* ROUND(數字)：四舍五入到整數
* ROUND(數字, 位數)：四舍五入到指定的位數
* CEIL(數字)、CEILING(數字)：進位到整數
* FLOOR(數字)：舍去所有小數
* TRUNCATE(數字, 位數)：將指定的[數字]舍去指定的[位數]
下列是一個測試這些函式的查詢敘述：
在這些函式中，「TRUNCATE」函式的用法會比較不一樣：
下列是算數運算的函式：
* PI()：圓周率
* POW(數字1, 數字2)、POWER(數字, 數字2)：[數字1]的[數字2]平方
* RAND()：亂數
* SQRT(數字)：[數字]的平方
每次使用「RAND」函式的時候，它都會傳回一個大于等于0而且小于等于1的小數數字，通常會把它稱為「亂數」，這個數值是由MySQL隨機產生的。如果你的敘述中需要一個固定范圍內的亂數，可以搭配「RAND」函式套用下列的公式來產生：
使用「RAND」函式也可以完成「隨機查詢」的需求：
注：MySQL還有提供的許多不同應用的數學函式，例如三角函式，你可以查詢MySQL參考手冊中的「12.4.2.?
Mathematical Functions」。
## 2.3 日期時間函式
下列是取得日期與時間的函式：
* CURDATE()：取得目前日期，相同功能：CURRENT_DATE、CURRENT_DATE()
* CURTIME()：取得目前時間，相同功能：CURRENT_TIME、CURRENT_TIME()
* YEAR(日期)：傳回[日期]的年
* MONTH(日期) 數字 傳回[日期]的月
* DAY(日期)：傳回[日期]的日，相同功能：DAYOFMONTH()
* MONTHNAME(日期)：傳回[日期]的月份名稱
* DAYNAME(日期)：傳回[日期]的星期名稱
* DAYOFWEEK(日期)：傳回[日期]的星期，1到7的數字，表示星期日、一、二…
* DAYOFYEAR(日期)：傳回[日期]的日數，1到366的數字，表示一年中的第幾天
* QUARTER(日期)：傳回[日期]的季，1到4的數字，代表春、夏、秋、冬
* EXTRACT(單位 FROM 日期/時間)：傳回[日期]中指定的[單位]資料
* HOUR(時間)：傳回[時間]的時
* MINUTE(時間)：傳回[時間]的分
* SECOND(時間)：傳回[時間]的秒
「CURDATE」與「CURTIME」可以取得目前伺服器的日期與時間，搭配其它函式就可以完成下列的「建國最久的國家排行」查詢：
「EXTRACT」函式用來取得日期時間資料的指定「單位」，例如日期中的月份，使用的「單位」與這一章之前在「日期與時間值」中討論的一樣，這個函式讓你不用記太多「YEAR」或「MONTH」這類函式的名稱：
下列是計算日期與時間的函式：
* ADDDATE(日期, 天數)：傳回[日期]在指定[天數] 以后的日期
* ADDDATE(日期, INTERVAL 數字 單位)：傳回[日期]在指定[數字]的[單位]以后的日期
* ADDTIME(日期時間, INTERVAL數字 單位)：傳回[日期時間]在指定[數字]的[單位]以后的日期時間
* SUBDATE(日期, 天數)：傳回[日期]在指定[天數] 以前的日期
* SUBDATE(日期, INTERVAL 數字 單位)：傳回[日期]在指定[數字]的[單位]以前的日期
* SUBTIME(日期時間, INTERVAL數字 單位)：傳回[日期時間]在指定[數字]的[單位]以前的日期時間
* DATEDIFF(日期1, 日期2)：計算兩個日期差異的天數
在計算日期方面的函式，MySQL也提供兩種不同的用法：
上列函式中使用的「單位」與這一章之前在「日期與時間值」中討論的一樣。
## 2.4 流程控制函式
在處理一般工作的時候，使用各種SQL敘述與函式，通常就可以完成你的需求；可是在實際的應用上，難免會遇到類似下列這樣比較復雜一點的需求：
像這種依照條件判斷結果而顯示不同資料的需求，可以使用下列這個「IF」函式來處理：
使用「IF」函式可以在查詢的時候，依照員工進公司的日期判斷是資深或是一般員工：
如果要依照資深員工與一般員工計算不同的獎金，也可以使用「IF」函式來完成：
「IF」函式可以用來判斷一個條件「成立」或「不成立」兩種狀況的需求；但是像下列的需求就不適合使用「IF」函式了：
如果要完成多種條件的判斷，就要使用下列的「CASE」語法，它應該不能算是一個函式，因為它的長像實在不像是一個函式：
套用上列的語法，就可以判斷出所有員工的新資等級：
在「CASE」的語法中，要判斷一種條件就使用一個「WHEN」來完成；如果有「所有條件以外」的情況要處理的話，就可以使用「ELSE」來處理：
如果要依照員工新資等級計算不同的獎金，也可以使用「CASE」語法來完成這個需求：
「CASE」除了上列介紹的語法外，還有另外一種寫法可以處理一些比較特別的需求，例如下列七大洲的名稱與縮寫對照表：
* Asia：AS
* Europe：EU
* Africa：AF
* Oceania：OA
* Antarctica：AN
* North America：NA
* South America：SA
如果要在SQL敘述中有類似這樣的需求，就可以使用下列這種「CASE」的語法：
套用上列的語法就可以完成這樣的查詢：
以上列的查詢來說，你也可以換成這樣的寫法：
~~~
SELECT Name, Continent,
CASE
WHEN Continent='Asia' THEN 'AS'
WHEN Continent='Europe' THEN 'EU'
WHEN Continent='Africa' THEN 'AF'
WHEN Continent='Oceania' THEN 'OA'
WHEN Continent='Antarctica' THEN 'AN'
WHEN Continent='North America' THEN 'NA'
WHEN Continent='South America' THEN 'SA'
END ContinentCode
FROM country
~~~
經由這樣的對照，應該可以很容易看得出來，使用哪一種寫法來完成這個查詢會好一些。
## 2.5 其它函式
* IFNULL(參數, 運算式)：如果[參數]為NULL就傳回[運算式]的值；否則傳回[參數]的值
* ISNULL(參數)：如果[參數]為NULL就傳回TRUE；否則傳回FALSE
當資料庫中有「NULL」資料出現的時候，就可能會發生下列這樣奇怪的結果：
所以要得到正確的結果，就要使用「IFNULL」函式來特別處理NULL值的運算：
「ISNULL」函式用來判斷一個指定的資料是否為「NULL」，它的效果跟之前在「第二章、基礎查詢、條件比較」中討論的「IS」和「」運算子是一樣的，你可以自己決定要使用哪一種來執行判斷。
# 3 群組查詢
資料庫通常是用來儲存龐大數量的資料，這也是它最善長跟主要的工作，所以查詢并計算資料的統計分析資訊也是一種很常見的需求：
你也可能會進一步的查詢更詳細的統計與分析資訊：
## 3.1 群組函式
想要完成上列討論的統計與分析查詢，你會用到下列的「群組函式」：
* MAX(運算式)：最大值
* MIN(運算式)：最小值
* SUM(運算式)：合計
* AVG(運算式)：平均
* COUNT([DISTINCT]*|運算式)：使用「DISTINCT」時，重復的資料不會計算；使用[*]時，計算表格紀錄的數量：使用[運算式]時，計算的數量不會包含「NULL」值
使用上列的群組函式可以很容易的查詢需要的統計與分析資訊：
這些函式套用在數值資料時會比較明確一些，把它們用在日期資料也是可以完成「員工最早和最晚進公司的日期」的查詢需求：
在這些群組函式中，「COUNT」函式的用法會比較不一樣：
利用「COUNT」函式的特性，也可以查詢一些特別的資訊：
## 3.2 GROUP_CONCAT函式
「GROUP_CONCAT」函式是比較特別的一個群組函式，它用來將一些字串資料「串接」起來。在執行一般查詢的時候，會根據查詢的資料，將許多紀錄傳回來給你：
使用「GROUP_CONCAT」函式的話，只會回傳一筆紀錄，這筆紀錄包含所有字串資料串接起來的內容：
下列是「GROUP_CONCAT」函式的語法：
上列的范例是「GROUP_CONCAT」函式最簡單的用法，你還可以在函式中使用與「ORDER BY」子句一樣的用法來指定資料的排列順序：
「GROUP_CONCAT」函式連接字串的時候，預設是使用逗號分隔資料，你可以自己指定分隔的字串：
在「GROUP_CONCAT」函式中還可以使用類似在「基礎查詢、限制查詢」中討論過的「DISTINCT」來排除重復的資料，例如：
在「GROUP_CONCAT」函式中使用「DISTINCT」也會有同樣的效果：
## 3.3 GROUP BY與HAVING子句
在上列使用群組函式的所有范例中，都是將「FROM」子句中指定的表格當成是一整個「群組」，群組函式所處理的資料是表格中所有的紀錄。如果希望依照指定的資料來計算分組統計與分析資訊，在執行查詢的時候，可能會有下列幾種不同的結果：
上列的范例使用「GROUP BY」子句指定分組的設定，下列是分組查詢中的語法：
「GROUP BY」子句指定是依照你自己的需求來決定的，同樣以人口數量合計來說，不同的指定可以得到不同的統計資訊：
使用不同的群組函式，就可以得不同的資訊：
如果需要的話，你可以在一個查詢中，一次取得所有需要的統計與分析資訊：
在查詢群組統計與分析資訊的時候，你可以指定多個群組設定取得更詳細的資訊：
使用「GROUP BY」指定群組的設定以后，回傳的群組查詢資料都會依照指定的群組排序，預設定排序方式是遞增排序，使用「DESC」關鍵字可以指定排序的方式為遞減排序：
使用「GROUP BY」子句的時候可以搭配「WITH ROLLUP」：
使用「WITH ROLLUP」以后，效果會作用在查詢中的每一個群組函式：
在「GROUP BY」子句中有多個群組設定的時候，你可以在最后面加入「WITH ROLLUP」：
在執行群組查詢的時候，一般的條件設定同樣使用「WHERE」子句就可以了：
可是以類似上列的查詢來說，把查詢條件從「亞洲的地區」換成「人口合計大于一億的地區」，如果還是把條件設定放在「WHERE」子句的話：
包含群組函式的條件設定就一定要放在「HAVING」子句中
依照需求在執行群組查詢的時候，應該不會出現下列的查詢敘述：
MySQL資料庫在執行上列的查詢敘述后，并不會產生任何錯誤，為了預防這樣的狀況，你可以執行下列的設定：
~~~
SET sql_mode = 'ONLY_FULL_GROUP_BY'
~~~
在「sql_mode」的設定中加入「ONLY_FULL_GROUP_BY」，表示多了下列的規定：
如果查詢敘述違反「ONLY_FULL_GROUP_BY」的規定，就會產生錯誤訊息：
