1 值與運算式不論在執行查詢或資料異動的時候,你都可能會使用各種不同種類的值(literal values)來完成你的工作: 不同種類的值會有不同的用法與規定,可以搭配使用的運算子和函式也不一樣。根據資料類型可以分為下列幾種:
1.1 數值數值分為「精確值(exact-value)」與「近似值(approximate-value)」兩種。精確值在使用時不會因為進位而產生差異;使用近似值的時候,可能會因為進位而產生些微的差異。精確值使用一個明確的數字來表示一個整數或小數數值:
一般來說,使用精確值在執行各種算數運算的時候,所得到的結果都不會有誤差的問題,你只要特別注意範圍就可以了。例如下列這個比較奇怪的查詢需求: 包含小數的數字,在整數部份的限制與整數相同,小數位數會有這樣的限制: 近似值的的數字通常稱為「科學表示法」,它使用下列的方式來表示一個數值: 這兩種表示方式所代表的數值是這樣計算的:
註:「XE+Y」格式中的「+」可以省略,例如「5E+3」與「5E3」是一樣的。 使用近似值來表示一個數值的時候,你一定要牢記它是一個「近似值」,也就是它真正儲存的數值可能不是你所看到的。下列的情況是你比較容易理解的: 不過下列的狀況就會有不一樣的結果:
第一個運算值採用精確值的方式,所以它們一定會相等;第二個運算使用近似值的方式,所以它們不一定相等。 1.2 字串值字串值是以單引號或雙引號包圍的文字資料,就文字資料來說,你不會拿文字執行加、減、乘、除這類的算數運算。如果你拿字串來執行算數運算的話,MySQL會先把字串中的內容轉換為數字,然後再執行算數運算:
如果字串內容包含不是數值的文字,MySQL在執行轉換的時候會出現警告訊息:
字串與字串可以執行連接的運算,就是把一些字串的內容連接起來後,產生一個新的字串。要執行字串連接的工作,可以使用「||」運算子,這個運算子在條件的判斷中是「或」的意思,如果你直接使用「||」運算子連接字串的話:
這是因為在預設的設定下,MySQL把「||」運算子當成數值的「或」運算,所以會出現這樣的情況;你可以透過設定MySQL的SQL模式,來改變這個預設處理方式:
這個設定會把「||」運算子用在字串值的時候,把它當成「連接」運算子:
註:字串的連接也可以使用函式來處理,在這章的後面討論;另外字串的比較因為跟編碼有關,會在後面的章節詳細討論。 1.3 日期與時間值日期與時間值(temporal values)有下列幾種:
在日期與時間值中西元年的部份,可以使用四個或兩個數字。如果指定的兩個數字是「70」到「99」之間,就代表「1970」到「1999」;如果是「00」到「69」之間,就代表「2000」到「2069」。日期值中預設的分隔字元是「-」,你也可以使用「/」,所以「2000-1-1」與「2000/1/1」都是正確的日期值。 日期時間資料可以使用在條件的判斷外,也可以用來「運算」,不過當然不是數值的算數運算,而是「一個日期的36天後是哪一天」這類的運算,而且只能使用「+」與「-」的運算。它的語法是: 語法中的單位可以使用下列表格中的單位關鍵字:
註:上列「單位關鍵字」並沒有列出所有的單位關鍵字,全部的單位關鍵字請參考MySQL手冊「12.5. Date and Time Functions」。 1.4 NULL值「NULL」值的處理比任何其它型態的值都來得奇怪一些,它也是一個很常見的資料,可以用來表示「未知的資料」;而且它最特別的地方是「NULL值與其它任何值都不一樣,包含NULL自己」。 「NULL」是一個SQL關鍵字,大小寫都可以。你已經知道判斷一個欄位資料是否為「NULL」值的時候,跟其它一般資料判斷是不一樣的;如果算數運算式或比較運算式中有任何「NULL」值的話,結果都會是「NULL」:
上列的查詢所得到的結果全部都是「NULL」。所以在比較「NULL」值的時侯要使用下列的方式: 2 函式在你在執行查詢或維護資料的時候,可能會有下列這個比較特殊的需求: 以這樣的需求來說,你當然不用自己去計算兩個日期之間的天數,MySQL提供許多不同的函式(functions),可以完成這類的需求,不論在執行查詢或維護的敘述中,都可以使用這些函式。函式基本的用法會像這樣: 註:MySQL規定函式預設的寫法是函式名稱和左括號之間不可以有任何空格,否則會造成錯誤;你可以執行「SET sql_mode=’IGNORE_SPACE’」,這個設定讓你可以在函式名稱和左括號之間加入空格也不會出錯。 以上列「計算兩個日期之間的天數」來說,就會在查詢敘述中使用到這樣的函式: MySQL提供的函式非常多,你不用把每一個函式的名稱和用法都背起來,就算是為了參加認證考試也一樣。這個章節只有介紹「部份」函式,並不是全部,所以你在瞭解這章討論的函式以後,需要到MySQL參考手冊中的「Chapter 12. Functions and Operators」,進一步認識MySQL還有提供哪一些函式。 2.1 字串函式字串資料的處理是一種很常見的工作,處理字串的函式也非常多,所以這裡使用分類的方式來介紹。下列是處理字串內容的相關函式:
「LPAD」與「RPAD」在處理報表資料的時候,很常用來控制報表內容的格式。例如下列的需求: 使用「LPAD」函式讓查詢後得到的字串內容向右對齊: 下列是截取字串內容的函式:
下列是一個測試這些函式的查詢敘述: 下列是連接字串的函式:
你可以使用「||」運算子連接字串,「CONCAT」函式也可以完成同樣的需求。唯一的差異是要先設定「sql_mode」為「PIPES_AS_CONCAT」後,才可以使用「||」運算子連接字串;而「CONCAT」函式不用執行任何設定就可以連接字串。 「CONCAT_WS」函式提供一種比較方便的字串連接功能,例如下列這個使用「||」運算子連接字串的查詢敘述: 改成使用「CONCAT_WS」函式的話,就會比較簡單一些: 註:「CONCAT」與「CONCAT_WS」兩個函式的參數可以接受任何型態的資料,它們都會把全部的資料轉為字串後連接起來;「CONCAT」函式的參數中如果有「NULL」值,結果會是「NULL」;「CONCAT_WS」函式的參數中如果有「NULL」值,「NULL」值會被忽略。 下列是取得字串資訊的函式:
使用「LENGTH」函式可以完成類似「國家名稱長度排行榜」的查詢: 註:「LENGTH」與「CHAR_LENGTH」的差異在「第六章、字元集與資料庫」與「第七章、儲存引擎與資料型態」中會詳細的討論。 如果有需要的話,你也會搭配許多函式來完成你的工作,例如: 上列的敘述可以查詢「名稱是一個單字以上的國家」。 2.2 數學函式下列是數值捨去與進位的函式:
下列是一個測試這些函式的查詢敘述: 在這些函式中,「TRUNCATE」函式的用法會比較不一樣: 下列是算數運算的函式:
每次使用「RAND」函式的時候,它都會傳回一個大於等於0而且小於等於1的小數數字,通常會把它稱為「亂數」,這個數值是由MySQL隨機產生的。如果你的敘述中需要一個固定範圍內的亂數,可以搭配「RAND」函式套用下列的公式來產生: 使用「RAND」函式也可以完成「隨機查詢」的需求: 註:MySQL還有提供的許多不同應用的數學函式,例如三角函式,你可以查詢MySQL參考手冊中的「12.4.2. Mathematical Functions」。 2.3 日期時間函式下列是取得日期與時間的函式:
「CURDATE」與「CURTIME」可以取得目前伺服器的日期與時間,搭配其它函式就可以完成下列的「建國最久的國家排行」查詢: 「EXTRACT」函式用來取得日期時間資料的指定「單位」,例如日期中的月份,使用的「單位」與這一章之前在「日期與時間值」中討論的一樣,這個函式讓你不用記太多「YEAR」或「MONTH」這類函式的名稱: 下列是計算日期與時間的函式:
在計算日期方面的函式,MySQL也提供兩種不同的用法: 上列函式中使用的「單位」與這一章之前在「日期與時間值」中討論的一樣。 2.4 流程控制函式在處理一般工作的時候,使用各種SQL敘述與函式,通常就可以完成你的需求;可是在實際的應用上,難免會遇到類似下列這樣比較複雜一點的需求: 像這種依照條件判斷結果而顯示不同資料的需求,可以使用下列這個「IF」函式來處理: 使用「IF」函式可以在查詢的時候,依照員工進公司的日期判斷是資深或是一般員工: 如果要依照資深員工與一般員工計算不同的獎金,也可以使用「IF」函式來完成: 「IF」函式可以用來判斷一個條件「成立」或「不成立」兩種狀況的需求;但是像下列的需求就不適合使用「IF」函式了: 如果要完成多種條件的判斷,就要使用下列的「CASE」語法,它應該不能算是一個函式,因為它的長像實在不像是一個函式: 套用上列的語法,就可以判斷出所有員工的新資等級: 在「CASE」的語法中,要判斷一種條件就使用一個「WHEN」來完成;如果有「所有條件以外」的情況要處理的話,就可以使用「ELSE」來處理: 如果要依照員工新資等級計算不同的獎金,也可以使用「CASE」語法來完成這個需求: 「CASE」除了上列介紹的語法外,還有另外一種寫法可以處理一些比較特別的需求,例如下列七大洲的名稱與縮寫對照表:
如果要在SQL敘述中有類似這樣的需求,就可以使用下列這種「CASE」的語法: 套用上列的語法就可以完成這樣的查詢: 以上列的查詢來說,你也可以換成這樣的寫法:
經由這樣的對照,應該可以很容易看得出來,使用哪一種寫法來完成這個查詢會好一些。 2.5 其它函式
當資料庫中有「NULL」資料出現的時候,就可能會發生下列這樣奇怪的結果: 所以要得到正確的結果,就要使用「IFNULL」函式來特別處理NULL值的運算: 「ISNULL」函式用來判斷一個指定的資料是否為「NULL」,它的效果跟之前在「第三章、基礎查詢、條件比較」中討論的「IS NULL」和「IS NOT NULL」運算子是一樣的,你可以自己決定要使用哪一種來執行判斷。 3 群組查詢資料庫通常是用來儲存龐大數量的資料,這也是它最善長跟主要的工作,所以查詢並計算資料的統計分析資訊也是一種很常見的需求: 你也可能會進一步的查詢更詳細的統計與分析資訊: 3.1 群組函式想要完成上列討論的統計與分析查詢,你會用到下列的「群組函式」:
使用上列的群組函式可以很容易的查詢需要的統計與分析資訊: 這些函式套用在數值資料時會比較明確一些,把它們用在日期資料也是可以完成「員工最早和最晚進公司的日期」的查詢需求: 在這些群組函式中,「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資料庫在執行上列的查詢敘述後,並不會產生任何錯誤,為了預防這樣的狀況,你可以執行下列的設定:
在「sql_mode」的設定中加入「ONLY_FULL_GROUP_BY」,表示多了下列的規定: 如果查詢敘述違反「ONLY_FULL_GROUP_BY」的規定,就會產生錯誤訊息:
|