1、.第4章MYSQL高級特性本章要點：v 集合函數與時間函數v 字符串的模式匹配v 如何創建索引v 檢索數據中的一些技巧第三章向你初步介紹了SQL。你學會了如何用SELECT語句進行查詢，你還學會了如何建立自己的表以及如何錄入數據等。在這一章里，你將加深你SQL語言知識。你將學習如何建立索引來加快查詢速度。你還將學會如果用更多的SQL語句和函數來操作表中的數據。尤其是對檢索語句SELECT的使用，其中技巧繁多，可以說這是SQL語言中最重要的語句，也是使用者最常使用的語句。本章將詳細介紹SELECT語句的用法。無論如何，這里的介紹都不能包括SQL語言的所有技巧，讀者應該在平時的不斷使用中積累經驗。

2、4.1 集合函數到現在為止，你只學習了如何根據特定的條件從表中取出一條或多條記錄。但是，假如你想對一個表中的記錄進行數據統計。例如，如果你想統計存儲在表中的一次民意測驗的投票結果。或者你想知道一個訪問者在你的站點上平均花費了多少時間。要對表中的任何類型的數據進行統計，都需要使用集合函數。你可以統計記錄數目，平均值，最小值，最大值，或者求和。當你使用一個集合函數時，它只返回一個數，該數值代表這幾個統計值之一。這些函數的最大特點就是經常和GROUP BY語句配合使用，需要注意的是集合函數不能和非分組的列混合使用。4.1.1 行列計數l 計算查詢語句返回的記錄行數直接計算函數COUNT(*)的值，例

3、如，計算pet表中貓的只數：mysql>SELECT count(*) FROM pet WHERE species=cat;+-+| count(*) |+-+| 2 |+-+4.1.2統計字段值的數目例如，計算pet表中species列的數目：mysql> SELECT count(species) FROM pet;+-+| count(species) |+-+| 9 |+-+如果相同的種類出現了不止一次，該種類將會被計算多次。如果你想知道種類為某個特定值的寵物有多少個，你可以使用WHERE子句，如下例所示： mysql> SELECT COUNT(species)

4、FROM pet WHERE species='cat' ;注意這條語句的結果：+-+| COUNT(species) |+-+| 2 |+-+這個例子返回種類為'cat'的作者的數目。如果這個名字在表pet中出現了兩次，則次函數的返回值是2。 而且它和上面提到過的語句的結果是一致的：SELECT count(*) FROM pet WHERE species=cat實際上，這兩條語句是等價的。假如你想知道有多少不同種類的的寵物數目。你可以通過使用關鍵字DISTINCT來得到該數目。如下例所示： mysql> SELECT COUNT(DISTINCT s

5、pecies) FROM pet;+-+| COUNT(DISTINCT species) |+-+| 5 |+-+如果種類'cat'出現了不止一次，它將只被計算一次。關鍵字DISTINCT 決定了只有互不相同的值才被計算。 通常，當你使用COUNT（）時，字段中的空值將被忽略。另外，COUNT()函數通常和GROUP BY子句配合使用，例如可以這樣返回每種寵物的數目：mysql> SELECT species,count(*) FROM pet GROUP BY species;+-+-+| species | count(*) |+-+-+| bird | 2 | c

6、at | 2 | dog | 3 | hamster | 1 | snake | 1 |+-+-+4.1.3 計算字段的平均值需要計算這些值的平均值。使用函數AVG（），你可以返回一個字段中所有值的平均值。 假如你對你的站點進行一次較為復雜的民意調查。訪問者可以在1到10之間投票，表示他們喜歡你站點的程度。你把投票結果保存在名為vote的INT型字段中。要計算你的用戶投票的平均值，你需要使用函數AVG（）： SELECT AVG(vote) FROM opinion 這個SELECT語句的返回值代表用戶對你站點的平均喜歡程度。函數AVG（）只能對數值型字段使用。這個函數在計算平均值時也忽略空值

7、。 再給出一個實際例子，例如我們要計算pet表中每種動物年齡的平均值，那么使用AVG()函數和GROUP BY子句：mysql> SELECT species,AVG(CURDATE()-birth) FROM pet GROUP BY species;返回的結果為：+-+-+| species | AVG(CURDATE()-birth) |+-+-+| bird | 34160 | cat | 74959.5 | dog | 112829.66666667 | hamster | 19890 | snake | 49791 |+-+-+4.1.4 計算字段值的和假設你的站點被用來出售

8、某種商品，已經運行了兩個月，是該計算賺了多少錢的時候了。假設有一個名為orders的表用來記錄所有訪問者的定購信息。要計算所有定購量的總和，你可以使用函數SUM（）： SELECT SUM(purchase_amount) FROM orders 函數SUM（）的返回值代表字段purchase_amount中所有值的總和。字段purchase_amount的數據類型也許是DECIMAL類型，但你也可以對其它數值型字段使用函數SUM（）。用一個不太恰當的例子說明，我們計算pet表中同種寵物的年齡的總和：mysql> SELECT species,SUM(CURDATE()-birth) F

9、ROM pet GROUP BY species;你可以查看結果，與前一個例子對照：+-+-+| species | SUM(CURDATE()-birth) |+-+-+| bird | 68320 | cat | 149919 | dog | 338489 | hamster | 19890 | snake | 49791 |+-+-+4.1.5 計算字段值的極值求字段的極值，涉及兩個函數MAX()和MIN()。例如，還是pet表，你想知道最早的動物出生日期，由于日期最早就是最小，所以可以使用MIN()函數：mysql> SELECT MIN(birth) FROM pet;+-+|

10、 MIN(birth) |+-+| 1989-05-13 |+-+但是，你只知道了日期，還是無法知道是哪只寵物，你可能想到這樣做：SELECT name,MIN(birth) FROM pet;但是，這是一個錯誤的SQL語句，因為集合函數不能和非分組的列混合使用，這里name列是沒有分組的。所以，你無法同時得到name列的值和birth的極值。MIN()函數同樣可以與GROUP BY子句配合使用，例如，找出每種寵物中最早的出生日期：mysql> SELECT species,MIN(birth) FROM pet GROUP BY species;下面是令人滿意的結果：+-+-+| sp

11、ecies | MIN(birth) |+-+-+| bird | 1997-12-09 | cat | 1993-02-04 | dog | 1989-05-13 | hamster | 1999-03-30 | snake | 1996-04-29 |+-+-+另一方面，如果你想知道最近的出生日期，就是日期的最大值，你可以使用MAX()函數，如下例所示： mysql> SELECT species,MAX(birth) FROM pet GROUP BY species; +-+-+| species | MAX(birth) |+-+-+| bird | 1998-09-11 |

12、cat | 1994-03-17 | dog | 1990-08-31 | hamster | 1999-03-30 | snake | 1996-04-29 |+-+-+4.1.6 總結在本節中，介紹了一些典型的集合函數的用法，包括計數、均值、極值和總和，這些都是SQL語言中非常常用的函數。這些函數之所以稱之為集合函數，是因為它們應用在多條記錄中，所以集合函數最常見的用法就是與GROUP BY子句配合使用，最重要的是集合函數不能同未分組的列混合使用。4.2 操作日期和時間日期和時間函數對建立一個站點是非常有用的。站點的主人往往對一個表中的數據何時被更新感興趣。通過日期和時間函數，你可以在秒級

13、跟蹤一個表的改變。日期和時間類型是DATETIME、DATE、TIMESTAMP、TIME和YEAR。這些的每一個都有合法值的一個范圍，而“零”當你指定確實不合法的值時被使用。注意，MySQL允許你存儲某個“不嚴格地”合法的日期值，例如1999-11-31，原因我們認為它是應用程序的責任來處理日期檢查，而不是SQL服務器。為了使日期檢查更“快”，MySQL僅檢查月份在0-12的范圍，天在0-31的范圍。上述范圍這樣被定義是因為MySQL允許你在一個DATE或DATETIME列中存儲日期，這里的天或月是零。這對存儲你不知道準確的日期的一個生日的應用程序來說是極其有用的，在這種情況下，你簡單地存儲

14、日期象1999-00-00或1999-01-00。（當然你不能期望從函數如DATE_SUB()或DATE_ADD()得到類似以這些日期的正確值）。4.2.1 返回當前日期和時間通過函數GETDATE（），你可以獲得當前的日期和時間。例如，l CURDATE() 返回當前日期CURRENT_DATE 以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取決于函數是在一個字符串還是數字上下文被使用。 mysql> select CURDATE();+-+| CURDATE() |+-+| 2001-02-20 |+-+mysql> select CURDAT

15、E() + 0;+-+| CURDATE()+0 |+-+| 20010220 |+-+l CURTIME() 返回當前時間以'HH:MM:SS'或HHMMSS格式返回當前時間值，取決于函數是在一個字符串還是在數字的上下文被使用。 mysql> select CURTIME();+-+| CURTIME() |+-+| 10:42:38 |+-+mysql> select CURTIME() + 0;+-+| CURTIME()+0 |+-+| 104525 |+-+l NOW() 返回當前時期和時間NOW()以YYYY-MM-DD HH:MM:SS的格式或者YYY

16、YMMDDHHMMSS的格式返回日期和時間值，取決于上下文。mysql>select now();+-+| now() |+-+| 2001-02-20 10:45:57 |+-+mysql>select now()+0;+-+| now()+0 |+-+| 20010220105635 |+-+這些得到當前日期和時間的函數，對于日期和時間的計算很方便，尤其是計算一個時間到現在的時間差。例如，在pet表中，我們以天為單位計算寵物的年齡：mysql> SELECT name,CURDATE()-birth FROM pet;+-+-+| name | CURDATE()-bir

17、th |+-+-+| Fluffy | 80016 | Claws | 69903 | Buffy | 119707 | Chirpy | 29309 | Fang | 109393 | Bowser | 109389 | Whistler | 39011 | Slim | 49791 | Puffball | 19890 |+-+-+4.2.2 自動記錄數據的改變時間TIMESTAMP列類型提供一種類型，TIMESTAMP值可以從1970的某時的開始一直到2037年，精度為一秒，其值作為數字顯示。你可以使用它自動地用當前的日期和時間標記INSERT或UPDATE的操作。如果你有多個TIMES

18、TAMP列，只有第一個自動更新。 自動更新第一個TIMESTAMP列在下列任何條件下發生： l 列沒有明確地在一個INSERT或LOAD DATA INFILE語句中指定。 l 列沒有明確地在一個UPDATE語句中指定且一些另外的列改變值。（注意一個UPDATE設置一個列為它已經有的值，這將不引起TIMESTAMP列被更新，因為如果你設置一個列為它當前的值，MySQL為了效率而忽略更改。） l 你明確地設定TIMESTAMP列為NULL. 除第一個以外的TIMESTAMP列也可以設置到當前的日期和時間，只要將列設為NULL，或NOW()。 例如，創建如下的表：mysql> CREATE

19、TABLE student-> (-> id int,-> name char(16),-> english tinyint,-> chinese tinyint,-> history tinyint,-> time timestamp-> );向表中插入記錄，可以查看效果：mysql> INSERT student(id,name,englisht,Chinese,history) VALUES(11,”Tom”,66,93,67);查看記錄的存儲情況：mysql> SELECT * FROM student;+-+-+-+-+-+

20、-+| id | name | english | chinese | history | time |+-+-+-+-+-+-+| 11 | Tom | 66 | 93 | 67 | 20010220123335 |+-+-+-+-+-+-+你可以看到time列紀錄下了數據錄入時的時間值。如果你更新改記錄，在查看操作的結果：mysql> UPDATE student SET english=76 WHERE id=11;mysql> SELECT * FROM student;+-+-+-+-+-+-+| id | name | english | chinese | histo

21、ry | time |+-+-+-+-+-+-+| 11 | Tom | 76 | 93 | 67 | 20010220125736 |+-+-+-+-+-+-+可以清楚的看到，time列的時間被自動更改為修改記錄的時間。有時候你希望不更改任何值，也能打到修改TIMESTAMP列的值，這時只要設置該列的值為NULL，MySQL就可以自動更新TIMESTAMP列的值：mysql> UPDATE student SET time=NULL WHERE id=11;mysql> select * from student where id=11;+-+-+-+-+-+-+| id | n

22、ame | english | chinese | history | time |+-+-+-+-+-+-+| 11 | Tom | 76 | 93 | 67 | 20010220130517 |+-+-+-+-+-+-+通過明確地設置希望的值，你可以設置任何TIMESTAMP列為不同于當前日期和時間的值，即使對第一個TIMESTAMP列也是這樣。例如，如果，當你創建一個行時，你想要一個TIMESTAMP被設置到當前的日期和時間，但在以后無論何時行被更新時都不改變，你可以使用這樣使用： l 讓MySQL在行被創建時設置列，這將初始化它為當前的日期和時間。 l 當你執行隨后的對該行中其他列的更

23、改時，明確設定TIMESTAMP列為它的當前值。 例如，當你在修改列時，可以把原有的值付給TIMESTAMP列：mysql> UPDATE student SET english=66,time=time WHERE id=11;mysql> select * from student where id=11;+-+-+-+-+-+-+| id | name | english | chinese | history | time |+-+-+-+-+-+-+| 11 | Tom | 66 | 93 | 67 | 20010220130517 |+-+-+-+-+-+-+另一方面，

24、你可能發現，當你想要實現上面這個效果時，很容易用一個你用NOW()初始化的DATETIME列然后不再改變它，這樣也許直接些。 但是，TIMESTAMP列的以后好處是存儲要求比較小，節省空間。TIMESTAMP的存儲需求是4字節，而DATETIME列的存儲需求是8字節。4.2.3 返回日期和時間范圍當你分析表中的數據時，你也許希望取出某個特定時間的數據。我們用下面一個表來模仿一個web站點的記錄。mysql> CREATE TABLE weblog-> (-> data float,-> entrydate datetime-> );然后隨機的增加幾個數據：mysq

25、l> INSERT weblog VALUES(rand(),now();rand()函數返回一個隨機的浮點值，now()函數返回當前時間。多執行上面語句幾次，得到一個作為測試的表。最為測試你還可以增加一個值：mysql> INSERT weblog VALUES(rand(),”2001-02-08”);這條語句，插入一個entry為”2001-02-08 00:00:00”的值（假定現在為2001年2月8日），你可以查看這個表的值：mysql> select * from weblog;+-+-+| data | entrydate |+-+-+| 0.973723 |

26、2001-02-08 00:00:00 | 0.437768 | 2001-02-08 13:57:06 | 0.327279 | 2001-02-08 13:57:09 | 0.0931809 | 2001-02-08 13:58:29 | 0.198805 | 2001-02-08 13:57:54 |+-+-+你也許對特定的某一天中比如說2001年2月18日訪問者在你站點上的活動感興趣。要取出這種類型的數據，你也許會試圖使用這樣的SELECT語句： mysql> SELECT * FROM weblog WHERE entrydate="2001-02-08"

27、不要這樣做。這個SELECT語句不會返回正確的記錄它將只返回值為2000-02-08 00:00:00的記錄，換句話說，只返回當天零點零時的記錄。上面語句的結果為： +-+-+| data | entrydate |+-+-+| 0.973723 | 2001-02-08 00:00:00 |+-+-+要返回正確的記錄，你需要適用日期和時間范圍。有不止一種途徑可以做到這一點。1、使用關系運算符和邏輯運算符來限制時間范圍例如，下面的這個SELECT 語句將能返回正確的記錄： mysql> SELECT * FROM weblog -> WHEREentrydate>="

28、;2001-02-08" AND entrydate<"2001-02-09" ;這個語句可以完成任務，因為它選取的是表中的日期和時間大于等于2001-02-08 00:00:00并小于2001-02-09 00:00:00的記錄。換句話說，它將正確地返回2000年2月8日這一天輸入的每一條記錄。 其結果為：+-+-+| data | entrydate |+-+-+| 0.973723 | 2001-02-08 00:00:00 | 0.437768 | 2001-02-08 13:57:06 | 0.327279 | 2001-02-08 13:57:0

29、9 | 0.0931809 | 2001-02-08 13:58:29 | 0.198805 | 2001-02-08 13:57:54 |+-+-+2、另一種方法是，你可以使用LIKE來返回正確的記錄。通過在日期表達式中包含通配符“”，你可以匹配一個特定日期的所有時間。這里有一個例子： mysql> SELECT * FROM weblog WHERE entrydate LIKE '2001-02-08%' ;這個語句可以匹配正確的記錄。因為通配符“”代表了任何時間。 +-+-+| data | entrydate |+-+-+| 0.973723 | 2001-02

30、-08 00:00:00 | 0.437768 | 2001-02-08 13:57:06 | 0.327279 | 2001-02-08 13:57:09 | 0.0931809 | 2001-02-08 13:58:29 | 0.198805 | 2001-02-08 13:57:54 |+-+-+3、上面兩種方法的異同由于使用關系運算符進行的是比較過程，時轉換成內部的存儲格式后進行的，因此，因此時間的書寫可以不是那么嚴格要求。例如，下面幾種寫法是等價的：mysql> SELECT * FROM weblog WHERE entrydate>="2001-02-08&

31、quot;mysql> SELECT * FROM weblog WHERE entrydate>="2001-2-8"mysql> SELECT * FROM weblog WHERE entrydate>="2001*02*08"mysql> SELECT * FROM weblog WHERE entrydate>="20010208"SELECT * FROM weblog WHERE entrydate>="2001/2/8"而使用LIKE運算符和模式匹配，是通過

32、比較串值進行的，因此必須使用標準的時間書寫格式，YYYY-MM-DD HH-MM-SS。4.2.5 比較日期和時間已知兩個日期，比較它們的前后，可以直接求出它們的差和零值比較，也可以利用已知的時間函數：TO_DAYS(date) 給出一個日期date，返回一個天數(從0年的天數)，date可以是一個數字，也可以是一個串值，當然更可以是包含日期的時間類型。 mysql> select TO_DAYS(960501);+-+| TO_DAYS(960501) |+-+| 729145 |+-+mysql> select TO_DAYS('1997-07-01');+-+

33、| TO_DAYS('1997-07-01') |+-+| 729571 |+-+例如：返回2個時間相差的天數(21世紀已經過去了多少天)mysql> select to_days(now()-to_days('20010101');+-+| to_days(now()-00000012000000)-to_days('20010101') |+-+| 38 |+-+4.3 字符串模式匹配MySQL提供標準的SQL模式匹配，以及一種基于象Unix實用程序如vi、grep和sed的擴展正則表達式模式匹配的格式。 4.3.1 標準的SQL模式匹

34、配SQL的模式匹配允許你使用“_”匹配任何單個字符，而“%”匹配任意數目字符(包括零個字符)。在 MySQL中，SQL的模式缺省是忽略大小寫的。下面顯示一些例子。注意在你使用SQL模式時，你不能使用=或!=；而使用LIKE或NOT LIKE比較操作符。 例如，在表pet中，為了找出以“b”開頭的名字： mysql> SELECT * FROM pet WHERE name LIKE "b%"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Buffy | Harold

35、 | dog | f | 1989-05-13 | NULL | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |+-+-+-+-+-+-+為了找出以“fy”結尾的名字： mysql> SELECT * FROM pet WHERE name LIKE "%fy"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Fluffy | Harold | cat | f | 1993-02-04 | NULL | Buf

36、fy | Harold | dog | f | 1989-05-13 | NULL |+-+-+-+-+-+-+為了找出包含一個“w”的名字： mysql> SELECT * FROM pet WHERE name LIKE "%w%"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Claws | Gwen | cat | m | 1994-03-17 | NULL | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-

37、29 | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |+-+-+-+-+-+-+為了找出包含正好5個字符的名字，使用“_”模式字符： mysql> SELECT * FROM pet WHERE name LIKE "_"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Claws | Gwen | cat | m | 1994-03-17 | NULL | Buffy | Harold | dog | f |

38、 1989-05-13 | NULL |+-+-+-+-+-+-+4.3.2 擴展正則表達式模式匹配由MySQL提供的模式匹配的其他類型是使用擴展正則表達式。當你對這類模式進行匹配測試時，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它們是同義詞)。 擴展正則表達式的一些字符是： “.”匹配任何單個的字符。 一個字符類“.”匹配在方括號內的任何字符。例如，“abc”匹配“a”、“b”或“c”。為了命名字符的一個范圍，使用一個“-”。“a-z”匹配任何小寫字母，而“0-9”匹配任何數字。 “ * ”匹配零個或多個在它前面的東西。例如，“x*”匹配任何數量的“x”

39、字符，“0-9*”匹配的任何數量的數字，而“.*”匹配任何數量的任何東西。 正則表達式是區分大小寫的，但是如果你希望，你能使用一個字符類匹配兩種寫法。例如，“aA”匹配小寫或大寫的“a”而“a-zA-Z”匹配兩種寫法的任何字母。 如果它出現在被測試值的任何地方，模式就匹配(只要他們匹配整個值，SQL模式匹配)。 為了定位一個模式以便它必須匹配被測試值的開始或結尾，在模式開始處使用“”或在模式的結尾用“$”。 為了說明擴展正則表達式如何工作，上面所示的LIKE查詢在下面使用REGEXP重寫： 為了找出以“b”開頭的名字，使用“”匹配名字的開始并且“bB”匹配小寫或大寫的“b”： mysql>

40、; SELECT * FROM pet WHERE name REGEXP "bB"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Buffy | Harold | dog | f | 1989-05-13 | NULL | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |+-+-+-+-+-+-+為了找出以“fy”結尾的名字，使用“$”匹配名字的結尾： mysql> SELECT * FROM pet WHE

41、RE name REGEXP "fy$"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Fluffy | Harold | cat | f | 1993-02-04 | NULL | Buffy | Harold | dog | f | 1989-05-13 | NULL |+-+-+-+-+-+-+為了找出包含一個“w”的名字，使用“wW”匹配小寫或大寫的“w”： mysql> SELECT * FROM pet WHERE name REGEXP "w

42、W"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Claws | Gwen | cat | m | 1994-03-17 | NULL | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |+-+-+-+-+-+-+既然如果一個正規表達式出現在值的任何地方，其模式匹配了，就不必再先前的查詢中在模式的兩方面放置一個通配符以使得

43、它匹配整個值，就像如果你使用了一個SQL模式那樣。 為了找出包含正好5個字符的名字，使用“”和“$”匹配名字的開始和結尾，和5個“.”實例在兩者之間： mysql> SELECT * FROM pet WHERE name REGEXP ".$"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Claws | Gwen | cat | m | 1994-03-17 | NULL | Buffy | Harold | dog | f | 1989-05-13 | NULL |+-+-+-+-+-+-+你也可以使用“n”“重復n次”操作符重寫先前的查詢： mysql> SELECT * FROM pet WHERE name REGEXP ".5$"+-+-+-+-+-+-+| name | owner | species | sex | birth | death |+-+-+-+-+-+-+| Claws | Gwen | cat | m | 1994-03-17 | NULL | Buffy