|
無論是在小得可憐的免費數(shù)據(jù)庫空間或是大型電子商務網(wǎng)站，合理的設計表結構、充分利用空間是十分必要的。這就要求我們對數(shù)據(jù)庫系統(tǒng)的常用數(shù)據(jù)類型有充分的認識。下面我就將我的一點心得寫出來跟大家分享。 
數(shù)字類型按照我的分類方法分為三類：整數(shù)類、小數(shù)類和數(shù)字類。 我所謂的?數(shù)字類?就是指DECIMAL和NUMERIC，它們是同一種類型。它嚴格的說不是一種數(shù)字類型，因為他們實際上是將數(shù)字以字符串形式保存的；他的值的每一位（包括小數(shù)點）占一個字節(jié)的存儲空間，因此這種類型耗費空間比較大。但是它的一個突出的優(yōu)點是小數(shù)的位數(shù)固定，在運算中不會?失真，所以比較適合用于?價格?金額?這樣對精度要求不高但準確度要求非常高的字段。 小數(shù)類，即浮點數(shù)類型，根據(jù)精度的不同，有FLOAT（單精度）和DOUBLE（雙精度）兩種。它們的優(yōu)勢是精確度，F(xiàn)LOAT可以表示絕對值非常小、小到約1.17E-38 （0.000...0117， 小數(shù)點后面有37個零）的小數(shù)，而DOUBLE更是可以表示絕對值小到約 2.22E-308 （0.000...0222， 小數(shù)點后面有307個零）的小數(shù)。FLOAT類型和DOUBLE類型占用存儲空間分別是4字節(jié)和8字節(jié)。如果需要用到小數(shù)的字段，精度要求不高的，當然用FLOAT了！可是說句實在話，我們?民用?的數(shù)據(jù)，哪有要求精度那么高的呢 這兩種類型至今我沒有用過——我還沒有遇到適合于使用它們的事例。 用的最多的，最值得精打細算的，是整數(shù)類型。從只占一個字節(jié)存儲空間的TINYINT到占8個字節(jié)的BIGINT，挑選一個?夠用?并且占用存儲空間最小的類型是設計數(shù)據(jù) 庫時應該考慮的。TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT占用存儲空間分別為1字節(jié)、2字節(jié)、3字節(jié)、4字節(jié)和8字節(jié)，就無符號的整數(shù)而言，這些類型能表示的最大整數(shù)分別為255、65535、16777215、4294967295和18446744073709551615。如果用來保存用戶的年齡（舉例來說，數(shù)據(jù)庫中保存年齡是不可取的），用TINYINT就夠了；九城的《縱橫》里，各項技能值，用MALLINT也夠了；如果要用作一個肯定不會超過16000000行的表的AUTO_INCREMENT的IDENTIFY字段，當然用 MEDIUMINT 不用 INT ，試想，每行節(jié)約一個字節(jié)，16000000行可以節(jié)約10兆多呢！ 日期和時間類型比較簡單，無非是 DATE、TIME、DATETIME、TIMESTAMP和YEAR等幾個類型。只對日期敏感，而對時間沒有要求的字段，就用DATE而不用DATETIME是不用說的了；單獨使用時間的情況也時有發(fā)生——使用TIME；但最多用到的還是用DATETIME。在日期時間類型上沒有什么文章可做，這里就不再詳述。 不要以為字符類型就是 CHAR ！CHAR和VARCHAR的區(qū)別在于CHAR是固定長度，只要你定義一個字段是CHAR(10),那么不論你存儲的數(shù)據(jù)是否達到了10個字節(jié)，它都要占去10個字節(jié)的空間；而VARVHAR則是可變長度的，如果一個字段可能的值是不固定長度的，我們只知道它不可能超過10個字符，把它定義為 VARCHAR(10)是最合算的，VARCHAR 類型的實際長度是它的值的(實際長度+1)。為什么?+1?呢這一個字節(jié)用于保存實際使用了多大的長度呀！從這個?+1?中也應該看到，如果一個字段， 它的可能值最長是10個字符，而多數(shù)情況下也就是用到了10個字符時，用VARCHAR就不合算了：因為在多數(shù)情況下，實際占用空間是11個字節(jié)，比用CHAR(10)還多占用一個字節(jié)！ 舉個例子，就是一個存儲股票名稱和代碼的表，股票名稱絕大部分是四個字的，即8個字節(jié)；股票代碼，上海的是六位數(shù)字，深圳的是四位數(shù)字。這些都是固定長度的，股票名稱當然要用 CHAR(8) ；股票代碼雖然是不固定長度，但如果使用VARVHAR(6)，一個深圳的股票代碼實際占用空間是5個字節(jié)，而一個上海的股票代碼要占用7個字節(jié)！考慮到上海的股票數(shù)目比深圳的多，那么用VARCHAR(6)就不如CHAR(6)合算了。 雖然一個CHAR或VARVHAR的最大長度可以到255，我認為大于20的CHAR是幾乎用不到的——很少有大于20個字節(jié)長度的固定長度的東東吧不是固定長度的就用VARCHAR！大于100的VARCHAR也是幾乎用不到的——比這更大的用TEXT就好了。TINYTEXT，最大長度為255，占用空間也是(實際長度+1)；TEXT，最大長度65535，占用空間是(實際長度+2)；MEDIUMTEXT，最大長度16777215，占用空間是(實際長度+3)；LONGTEXT，最大長度4294967295，占用空間是(實際長度+4)。為什么?+1??+2??+3??+4?你要是還不知道就該打PP了。這些可以用在論壇啊、新聞啊，什么的，用來保存文章的正文。根據(jù)實際情況的不同，選擇從小到大的不同類型。 枚舉（ENUM）類型，最多可以定義65535種不同的字符串從中做出選擇，只能并且必須選擇其中一種，占用存儲空間是一個或兩個字節(jié)，由枚舉值的數(shù)目決定；集合（SET）類型，最多可以有64個成員，可以選擇其中的零個到不限定的多個，占用存儲空間是一個到八個字節(jié)，由集合可能的成員數(shù)目決定。 舉個例子來說，在SQLServer中，你可以節(jié)約到用一個Bit類型來表示性別（男/女），但MySQL沒有Bit，用TINTINT不，可以用ENUM('帥哥','美眉')！只有兩種選擇，所以只需一個字節(jié)——跟TINYINT一樣大，但卻可以直接用字符串'帥哥'和'美眉'來存取。真是太方便啦！ 好了，MySQL的數(shù)據(jù)類型介紹得差不多，我的建庫策略也隨著介紹數(shù)據(jù)類型介紹給大家一些。但這只是其中一部分，篇幅有限，不能再細說；其他的，就靠各人在對數(shù)據(jù)類型理解的基礎上，多多實踐、多多討論。 除非最終檢索它們并利用它們來做點事情，否則將記錄放入數(shù)據(jù)庫沒什么好處。這就是SELECT 語句的用途，即幫助取出數(shù)據(jù)。SELECT 大概是 SQL 語言中最常用的語句，而且怎樣使用它也最為講究；用它來選擇記錄可能相當復雜，可能會涉及許多表中列之間的比較。本節(jié)介紹Select語句關于查詢的最基本功能。 SELECT 語句的語法如下： SELECT selection_list 選擇哪些列 FROM table_list 從何處選擇行 WHERE primary_constraint 行必須滿足什么條件 GROUP BY grouping_columns 怎樣對結果分組 HAVING secondary_constraint 行必須滿足的第二條件 ORDER BY sorting_columns 怎樣對結果排序 LIMIT count 結果限定 注意：所有使用的關鍵詞必須精確地以上面的順序給出。例如，一個HAVING子句必須跟在GROUP BY子句之后和ORDER BY子句之前。 除了詞?SELECT?和說明希望檢索什么的 column_list 部分外，語法中的每樣東西都是可選的。有的數(shù)據(jù)庫還需要 FROM 子句。MySQL 有所不同，它允許對表達式求值而不引用任何表。 普通查詢 SELECT最簡單的形式是從一張表中檢索每樣東西： mysql> SELECT * FROM pet; 其結果為： +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ 查詢特定行： 你能從你的表中只選擇特定的行。例如，如果你想要驗證你對Bowser的出生日期所做的改變，像這樣精選Bowser的記錄： mysql> SELECT * FROM pet WHERE name = "Bowser"; 其結果為： +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 你可以對照前一個例子來驗證。 查詢特定列如果你不想要看到你的表的整個行，就命名你感興趣的列，用逗號分開。例如，如果你想要知道你的動物什么時候出生的，精選name和birth列： mysql> SELECT name, birth FROM pet where owner="Gwen"; 其結果為： +----------+------------+ | name | birth | +----------+------------+ | Claws | 1994-03-17 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | +----------+------------+ 進行表達式計算 前面的多數(shù)查詢通過從表中檢索值已經(jīng)產(chǎn)生了輸出結果。MySQL 還允許作為一個公式的結果來計算輸出列的值。表達式可以簡單也可以復雜。下面的查詢求一個簡單表達式的值（常量）以及一個涉及幾個算術運算符和兩個函數(shù)調用的較復雜的表達式的值。例如，計算Browser生活的天數(shù)： mysql> SELECT death-birth FROM pet WHERE name="Bowser"; 其結果是： +-------------+ | death-birth | +-------------+ | 49898 | +-------------+ 由于MySQL允許對表達式求值而不引用任何表。所以也可以這樣使用: mysql>select (2+3*4.5)/2.5; 其結果為： +---------------+ | (2+3*4.5)/2.5 | +---------------+ | 6.200 | 條件查詢 不必每次查詢都返回所有的行記錄，你能從你的表中只選擇特定的行。為此你需要使用WHERE或者HAVING從句。HAVING從句與WHERE從句的區(qū)別是，HAVING表達的是第二條件，在與其他從句配合使用，顯然不能在WHERE子句中的項目使用HAVING。因此本小節(jié)緊介紹WHERE從句的使用，HAVING從句的使用方法類似。另外WHERE從句也可以實現(xiàn)HAVING從句的絕大部分功能。 為了限制 SELECT 語句檢索出來的記錄集，可使用 WHERE 子句，它給出選擇行的條件。可通過查找滿足各種條件的列值來選擇行。 WHERE 子句中的表達式可使用表1 中的算術運算符、表2 的比較運算符和表3 的邏輯運算符。還可以使用圓括號將一個表達式分成幾個部分?？墒褂贸Ａ?、表列和函數(shù)來完成運算。在本教程的查詢中，我們有時使用幾個 MySQL 函數(shù)，但是 MySQL 的函數(shù)遠不止這里給出的這些。請參閱附錄 一，那里給出了所有MySQL 函數(shù)的清單。 表1 算術運算符 運算符 說明 運算符 說明 加 乘 + * - / 減 除 表2 比較運算符 運算符 說明 運算符 說明 小于 不等于 < != 或 <> <= 小于或等于 大于或等于 >= = > 等于 大于 表3 邏輯運算符 運算符 說明 NOT或 ！ 邏輯非 OR 或 || 邏輯或 AND或 && 邏輯與 例如，如果你想要驗證你對Bowser的出生日期所做的改變，像這樣精選Bowser的記錄： mysql> SELECT * FROM pet WHERE name = "Bowser"; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 輸出證實出生年份現(xiàn)在正確記錄為1990，而不是1909。 字符串比較通常是大小些無關的，因此你可以指定名字為"bowser"、"BOWSER"等等，查詢結果將是相同的。 你能在任何列上指定條件，不只是name。例如，如果你想要知道哪個動物在1998以后出生的，測試birth列： mysql> SELECT * FROM pet WHERE birth >= "1998-1-1"; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 你能組合條件，例如，找出雌性的狗： mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f"; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 上面的查詢使用AND邏輯操作符，也有一個OR操作符： mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird"; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+-------+---------+------+------------+-------+ AND和OR可以混用。如果你這樣做，使用括號指明條件應該如何被分組是一個好主意： mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m") -> OR (species = "dog" AND sex = "f"); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 查詢排序 使用ORDER BY子句對查詢返回的結果按一列或多列排序。ORDER BY子句的語法格式為： ORDER BY column_name [ASC|DESC] [,…] 其中ASC表示升序，為默認值，DESC為降序。ORDER BY不能按text、text和image 數(shù)據(jù)類型進行排 序。另外，可以根據(jù)表達式進行排序。 例如，這里是動物生日，按日期排序： mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | 1989-05-13 | | Fang | 1990-08-27 | | Bowser | 1990-08-31 | | Fluffy | 1993-02-04 | | Claws | 1994-03-17 | | Slim | 1996-04-29 | | Whistler | 1997-12-09 | | Chirpy | 1998-09-11 | | Puffball | 1999-03-30 | +----------+------------+ 為了以逆序排序，增加DESC（下降 )關鍵字到你正在排序的列名上： mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Puffball | 1999-03-30 | | Chirpy | 1998-09-11 | | Whistler | 1997-12-09 | | Slim | 1996-04-29 | | Claws | 1994-03-17 | | Fluffy | 1993-02-04 | | Bowser | 1990-08-31 | | Fang | 1990-08-27 | | Buffy | 1989-05-13 | +----------+------------+ 你能在多個列上排序。例如，按動物的種類排序，然后按生日，首先是動物種類中最年輕的動物，使用下列查詢： mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | Chirpy | bird | 1998-09-11 | | Whistler | bird | 1997-12-09 | | Claws | cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Bowser | dog | 1990-08-31 | | Fang | dog | 1990-08-27 | | Buffy | dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +----------+---------+------------+ 注意DESC關鍵詞僅適用于緊跟在它之前的列名字(birth)；species值仍然以升序被排序。 注意，輸出首先按照species排序，然后具有相同species的寵物再按照birth降序排列。 查詢分組與行計數(shù) GROUP BY 從句根據(jù)所給的列名返回分組的查詢結果，可用于查詢具有相同值的列。 其語法為： GROUP BY col_name，…. 你可以為多個列分組。 例如： mysql>SELECT * FROM pet GROUP BY species; +----------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | +----------+--------+---------+------+------------+-------+ 由以上結果可以看出： 查詢顯示結果時，被分組的列如果有重復的值，只返回靠前的記錄，并且返回的記錄集是排序的。這并不是一個很好的結果。僅僅使用GROUP BY從句并沒有什么意義，該從句的真正作用在于與各種組合函數(shù)配合，用于行計數(shù)。 1、COUNT()函數(shù)計數(shù)非NULL結果的數(shù)目。 你可以這樣計算表中記錄行的數(shù)目： mysql> select count(*) from pet; +----------+ | count(*) | +----------+ | 9 | +----------+ 計算sex為非空的記錄數(shù)目： mysql> select count(sex) from pet; +------------+ | count(sex) | +------------+ | 8 | +------------+ 現(xiàn)在配合GROUP BY 從句使用。 例如：要知道每個主人有多少寵物 mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+ 又如，每種寵物的個數(shù)： mysql> SELECT species,count(*) FROM pet GROUP BY species; +---------+----------+ | species | count(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ １５ 如果你除了計數(shù)還返回一個列的值，那么必須使用GROU BY語句，否則無法計算記錄。 例如上例，使用GROUP BY對每個owner分組所有記錄，沒有它，你得到的一切是一條錯誤消息： mysql> SELECT owner, COUNT(owner) FROM pet; ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause 也可以根據(jù)多個列分組，例如： 按種類和性別組合的動物數(shù)量： mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; +---------+------+----------+ | species | sex | COUNT(*) | +---------+------+----------+ | bird | NULL | 1 | | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+ 查詢多個表 查詢多個表，F(xiàn)ROM子句列出表名，并用逗號分隔，因為查詢需要從他們兩個拉出信息。 當組合(聯(lián)結-join)來自多個表的信息時，你需要指定在一個表中的記錄怎樣能匹配其它表的記錄。這很簡單，因為它們都有一個name列。查詢使用WHERE子句基于name值來匹配2個表中的記錄。 因為name列出現(xiàn)在兩個表中，當引用列時，你一定要指定哪個表。這通過把表名附在列名前做到。 現(xiàn)在有一個event表： mysql>select * from event; +----------+------------+----------+-----------------------------+ | name | date | type | remark | +----------+------------+----------+-----------------------------+ | Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male | | Buffy | 1993-06-23 | litter | 5 puppies, 2 female, 3 male | | Buffy | 1994-06-19 | litter | 3 puppies, 3 female | | Chirpy | 1999-03-21 | vet | needed beak straightened | | Slim | 1997-08-03 | vet | broken rib | | Bowser | 1991-10-12 | kennel | NULL | | Fang | 1991-10-12 | kennel | NULL | | Fang | 1998-08-28 | birthday | Gave him a new chew toy | | Claws | 1998-03-17 | birthday | Gave him a new flea collar | | Whistler | 1998-12-09 | birthday | First birthday | +----------+------------+----------+-----------------------------+ 當他們有了一窩小動物時，假定你想要找出每只寵物的年齡。 event表指出何時發(fā)生，但是為了計算母親的年齡，你需要她的出生日期。既然它被存儲在pet表中，為了查詢你需要兩張表： mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark -> FROM pet, event -> WHERE pet.name = event.name AND type = "litter"; +--------+------+-----------------------------+ | name | age | remark | +--------+------+-----------------------------+ | Fluffy | 2.27 | 4 kittens, 3 female, 1 male | | Buffy | 4.12 | 5 puppies, 2 female, 3 male | | Buffy | 5.10 | 3 puppies, 3 female | +--------+------+-----------------------------+ 同樣方法也可用于同一張表中，你不必有2個不同的表來執(zhí)行一個聯(lián)結。如果你想要將一個表的記錄與同一個表的其他記錄進行比較，聯(lián)結一個表到自身有時是有用的。例如， 為了在你的寵物之中繁殖配偶，你可以用pet聯(lián)結自身來進行相似種類的雄雌配對： mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"; +--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+ 在這個查詢中，我們?yōu)楸砻付▌e名以便能引用列并且使得每一個列引用關聯(lián)于哪個表實例更直觀。 總結 本文總結了SELECT語句檢索記錄的簡單使用方法。其中涉及到的內容主要包括以下一些內容： 1、WHERE從句的用法 2、GROUP BY從句的用法 3、ORDER BY從句的用法 4、連接多個表的簡單介紹 有時，希望除去某些記錄或更改它們的內容。DELETE 和 UPDATE 語句令我們能做到這一點。 用update修改記錄 UPDATE tbl_name SET 要更改的列 WHERE 要更新的記錄 這里的 WHERE 子句是可選的，因此如果不指定的話，表中的每個記錄都被更新。 例如，在pet表中，我們發(fā)現(xiàn)寵物Whistler的性別沒有指定，因此我們可以這樣修改這個記錄： mysql> update pet set sex=’f’ where name=? Whistler?; 用delete刪除記錄 DELETE 語句有如下格式： DELETE FROM tbl_name WHERE 要刪除的記錄 WHERE 子句指定哪些記錄應該刪除。它是可選的，但是如果不選的話，將會刪除所有的記錄。這意味著最簡單的 DELETE 語句也是最危險的。 這個查詢將清除表中的所有內容。一定要當心！ 為了刪除特定的記錄，可用 WHERE 子句來選擇所要刪除的記錄。這類似于 SELECT 語句中的 WHERE 子句。 mysql> delete from pet where name=?Whistler?; 可以用下面的語句清空整個表： mysql>delete from pet; 總結 本節(jié)介紹了兩個SQL語句的用法。使用UPDATE和DELETE語句要十分小心，因為可能對你的數(shù)據(jù)造成危險。尤其是DELETE語句，很容易會刪除大量數(shù)據(jù)。使用時，一定小心。 思考題 1、請親自按照本章所述的步驟，讓MySQL服務器在Linux系統(tǒng)啟動時，自動啟動。并嘗試其它啟動、重啟、關閉服務器的方法。 2、現(xiàn)在有一個位于主機database.domain.net的MySQL服務器，用root用戶的身份，密碼為newpass，連接到數(shù)據(jù)庫test。如何給出合適的命令行如果使用選項文件，如何添加選項 3、在test數(shù)據(jù)庫中建立一個本章舉例中所述的表pet，其結構如下所述： name：30個寬度的定長字符串 owner：30個寬度的定長字符串 species：10個寬度的定長字符串 sex：由m和f組成的非空枚舉類型 birth：date類型 death：date類型 4、本章中pet表的數(shù)據(jù)錄入表中： +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Chirpy | Gwen | bird | f | 1998-09-11 | NULL | | Fang | Benny | dog | m | 1990-08-27 | NULL | | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | | Slim | Benny | snake | m | 1996-04-29 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+------------+ 請把數(shù)據(jù)記錄到一個數(shù)據(jù)文件中，然后使用LOAD DATA INFILE語句裝載數(shù)據(jù)。提示： 如果在Windows環(huán)境中，那么文件的換行符是?
?。 如果是使用實用程序mysqlimport命令行如何書寫。 使用INSERT語句插入新數(shù)據(jù) 語法：INSERT [INTO] tbl_name [(col_name,...)] VALUES (pression,...),… INSERT [INTO] tbl_name SET col_name=expression, ... 讓我們開始利用 INSERT 語句來增加記錄，這是一個 SQL 語句，需要為它指定希望插入數(shù)據(jù)行的表或將值按行放入的表。INSERT 語句具有幾種形式： 可指定所有列的值： 例如： shell> mysql –u root –p mysql> use mytest; mysql> insert into worker values(?tom?,[email protected]?); ?INTO?一詞自 MySQL 3.22.5 以來是可選的。（這一點對其他形式的 INSERT 語句也成立。）VALUES 表必須包含表中每列的值，并且按表中列的存放次序給出。（一般，這就是創(chuàng)建表時列的定義次序。如果不能肯定的話，可使用 DESCRIBE tbl_name 來查看這個次序。） 使用多個值表，可以一次提供多行數(shù)據(jù)。 Mysql>insert into worker values(‘tom’,’[email protected]’),(‘paul’,’[email protected]’); 有多個值表的INSERT ... VALUES的形式在MySQL 3.22.5或以后版本中支持。 可以給出要賦值的那個列，然后再列出值。這對于希望建立只有幾個列需要初始設臵的 記錄是很有用的。 例如： mysql>insert into worker (name) values (‘tom’); 自 MySQL 3.22.5 以來，這種形式的 INSERT 也允許多個值表： mysql>insert into worker (name) values (‘tom’), (‘paul’); 在列的列表中未給出名稱的列都將賦予缺省值。 自 MySQL 3.22 .10 以來，可以 col_name = value 的形式給出列和值。 例如： mysql>insert into worker set name=’tom’; 在 SET 子句中未命名的行都賦予一個缺省值。 使用這種形式的 INSERT 語句不能插入多行。 一個expression可以引用在一個值表先前設臵的任何列。例如，你能這樣： mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); 但不能這樣： mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15); 使用INSERT…SELECT語句插入從其他表選擇的行 當我們在上一節(jié)學習創(chuàng)建表時，知道可以使用select從其它表來直接創(chuàng)建表，甚至可以同時復制數(shù)據(jù)記錄。如果你已經(jīng)擁有了一個表，你同樣可以從select語句的配合中獲益。 從其它表中錄入數(shù)據(jù)，例如： mysql>insert into tbl_name1(col1,col2) select col3,col4 from tbl_name2; 你也可以略去目的表的列列表，如果你每一列都有數(shù)據(jù)錄入。 mysql>insert into tbl_name1 select col3,col4 from tbl_name2; INSERT INTO ... SELECT語句滿足下列條件： 查詢不能包含一個ORDER BY子句。 INSERT語句的目的表不能出現(xiàn)在SELECT查詢部分的FROM子句，因為這在ANSI SQL中被禁止讓從你正在插入的表中SELECT。（問題是SELECT將可能發(fā)現(xiàn)在同一個運行期間內先前被插入的記錄。當使用子選擇子句時，情況能很容易混淆） 使用replace、replace…select語句插入REPLACE功能與INSERT完全一樣，除了如果在表中的一個老記錄具有在一個唯一索引上的新記錄有相同的值，在新記錄被插入之前，老記錄被刪除。對于這種情況，insert語句的表現(xiàn)是產(chǎn)生一個錯誤。 REPLACE語句也可以褐SELECT相配合，所以上兩小節(jié)的內容完全適合REPALCE.。 應該注意的是，由于REPLACE語句可能改變原有的記錄，因此使用時要小心。 使用LOAD語句批量錄入數(shù)據(jù)本章的前面討論如何使用SQL向一個表中插入數(shù)據(jù)。但是，如果你需要向一個表中添加許多條記錄，使用SQL語句輸入數(shù)據(jù)是很不方便的。幸運的是，MySQL提供了一些方法用于批量錄入數(shù)據(jù)，使得向表中添加數(shù)據(jù)變得容易了。本節(jié)以及下一節(jié)，將介紹這些方法。本節(jié)將介紹SQL語言級的解決方法。 1、基本語法 語法：LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name LOAD DATA INFILE語句從一個文本文件中以很高的速度讀入一個表中。如果指定LOCAL關鍵詞，從客戶主機讀文件。如果LOCAL沒指定，文件必須位于服務器上。(LOCAL在MySQL3.22.6或以后版本中可用。) 為了安全原因，當讀取位于服務器上的文本文件時，文件必須處于數(shù)據(jù)庫目錄或可被所有人讀取。另外，為了對服務器上文件使用LOAD DATA INFILE，在服務器主機上你必須有file的權限。見第七章 數(shù)據(jù)庫安全。 REPLACE和IGNORE關鍵詞控制對現(xiàn)有的唯一鍵記錄的重復的處理。如果你指定 REPLACE，新行將代替有相同的唯一鍵值的現(xiàn)有行。如果你指定IGNORE，跳過有唯一鍵的現(xiàn)有行的重復行的輸入。如果你不指定任何一個選項，當找到重復鍵鍵時，出現(xiàn)一個錯誤，并且文本文件的余下部分被忽略時。 如果你使用LOCAL關鍵詞從一個本地文件裝載數(shù)據(jù)，服務器沒有辦法在操作的當中停止文件的傳輸，因此缺省的行為好像IGNORE被指定一樣。 2、文件的搜尋原則 當在服務器主機上尋找文件時，服務器使用下列規(guī)則： 如果給出一個絕對路徑名，服務器使用該路徑名。 如果給出一個有一個或多個前臵部件的相對路徑名，服務器相對服務器的數(shù)據(jù)目錄搜索文件。 如果給出一個沒有前臵部件的一個文件名，服務器在當前數(shù)據(jù)庫的數(shù)據(jù)庫目錄尋找文件。 注意這些規(guī)則意味著一個像?./myfile.txt?給出的文件是從服務器的數(shù)據(jù)目錄讀取，而作為?myfile.txt?給出的一個文件是從當前數(shù)據(jù)庫的數(shù)據(jù)庫目錄下讀取。也要注意，對于下列哪些語句，對db1文件從數(shù)據(jù)庫目錄讀取，而不是db2： mysql> USE db1; mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table; 3、FIELDS和LINES子句的語法 如果你指定一個FIELDS子句，它的每一個子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY和ESCAPED BY)也是可選的，除了你必須至少指定他們之一。 如你不指定一個FIELDS子句，缺省值與如果你這樣寫的相同： FIELDS TERMINATED BY ' ' ENCLOSED BY '' ESCAPED BY '' 如果你不指定一個LINES子句，缺省值與如果你這樣寫的相同： LINES TERMINATED BY '
' 換句話說，缺省值導致讀取輸入時，LOAD DATA INFILE表現(xiàn)如下： 在換行符處尋找行邊界在定位符處將行分進字段不要期望字段由任何引號字符封裝 將由??開頭的定位符、換行符或??解釋是字段值的部分字面字符 LOAD DATA INFILE能被用來讀取從外部來源獲得的文件。例如，以dBASE格式的文件將有由逗號分隔并用雙引號包圍的字段。如果文件中的行由換行符終止，下面顯示的命令說明你將用來裝載文件的字段和行處理選項： mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '
'; 任何字段或行處理選項可以指定一個空字符串('')。如果不是空，F(xiàn)IELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必須是一個單個字符。FIELDS TERMINATED BY和LINES TERMINATED BY值可以是超過一個字符。例如，寫入由回車換行符對（CR+LF）終止的行，或讀取包含這樣行的一個文件，指定一個LINES TERMINATED BY '
'子句。 FIELDS [OPTIONALLY] ENCLOSED BY控制字段的包圍字符。對于輸出(SELECT ... INTO OUTFILE)，如果你省略OPTIONALLY，所有的字段由ENCLOSED BY字符包圍。對于這樣的輸出的一個例子(使用一個逗號作為字段分隔符)顯示在下面： "1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a " quote","102.20" "4","a string containing a ", quote and comma","102.20" 如果你指定OPTIONALLY，ENCLOSED BY字符僅被用于包圍CHAR和VARCHAR字段： 1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20 注意，一個字段值中的ENCLOSED BY字符的出現(xiàn)通過用ESCAPED BY字符作為其前綴來轉義。也要注意，如果你指定一個空ESCAPED BY值，可能產(chǎn)生不能被LOAD DATA INFILE正確讀出的輸出。例如，如果轉義字符為空，上面顯示的輸出顯示如下。 注意到在第四行的第二個字段包含跟隨引號的一個逗號，它(錯誤地)好象要終止字段： 1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20 FIELDS ESCAPED BY控制如何寫入或讀出特殊字符。如果FIELDS ESCAPED BY字符不是空的，它被用于前綴在輸出上的下列字符： FIELDS ESCAPED BY字符 FIELDS [OPTIONALLY] ENCLOSED BY字符 FIELDS TERMINATED BY和LINES TERMINATED BY值的第一個字符 ASCII 0（實際上將后續(xù)轉義字符寫成 ASCII'0'，而不是一個零值字節(jié)） 如果FIELDS ESCAPED BY字符是空的，沒有字符被轉義。指定一個空轉義字符可能不是一個好主意，特別是如果在你數(shù)據(jù)中的字段值包含剛才給出的表中的任何字符。 對于輸入，如果FIELDS ESCAPED BY字符不是空的，該字符的出現(xiàn)被剝去并且后續(xù)字符在字面上作為字段值的一個部分。例外是一個轉義的?0?或?N?（即，?或N，如果轉義字符是??)。這些序列被解釋為ASCII 0（一個零值字節(jié)）和NULL。見下面關于NULL處理的規(guī)則。 總結 為數(shù)據(jù)庫裝載數(shù)據(jù)是管理員的重要職責之一，正因為重要，所以MySQL提供的方法也是非常繁多。其中主要的在本節(jié)已經(jīng)列舉： 1、使用INSERT、REPLACE語句 2、使用INSERT/REPLACE…SELECT語句 3、使用LOAD DATA INFILE語句 4、使用實用程序mysqlimport 一、INSERT和REPLACE INSERT和REPLACE語句的功能都是向表中插入新的數(shù)據(jù)。這兩條語句的語法類似。 它們的主要區(qū)別是如何處理重復的數(shù)據(jù)。 1. INSERT的一般用法 MySQL中的INSERT語句和標準的INSERT不太一樣，在標準的SQL語句中，一次插入一條記錄的INSERT語句只有一種形式。 INSERT INTO tablename(列名…) VALUES(列值); 而在MySQL中還有另外一種形式。 INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…; 第一種方法將列名和列值分開了，在使用時，列名必須和列值的數(shù)一致。如下面的語句 向users表中插入了一條記錄： INSERT INTO users(id, name, age) VALUES(123, '姚明', 25); 第二種方法允許列名和列值成對出現(xiàn)和使用，如下面的語句將產(chǎn)生中樣的效果。 INSERT INTO users SET id = 123, name = '姚明', age = 25; 如果使用了SET方式，必須至少為一列賦值。如果某一個字段使用了省缺值(如默認或自增值)，這兩種方法都可以省略這些字段。如id字段上使用了自增值，上面兩條語句 可以寫成如下形式： INSERT INTO users (name, age) VALUES('姚明',25); INSERT INTO uses SET name = '姚明', age = 25; MySQL在VALUES上也做了些變化。如果VALUES中什么都不寫，那MySQL將使用表中每一列的默認值來插入新記錄。 INSERT INTO users () VALUES(); 如果表名后什么都不寫，就表示向表中所有的字段賦值。使用這種方式，不僅在VALUES中的值要和列數(shù)一致，而且順序不能顛倒。 INSERT INTO users VALUES(123, '姚明', 25); 如果將INSERT語句寫成如下形式MySQL將會報錯。 INSERT INTO users VALUES('姚明',25); 2. 使用INSERT插入多條記錄 看到這個標題也許大家會問，這有什么好說的，調用多次INSERT語句不就可以插入多條記錄了嗎!但使用這種方法要增加服務器的負荷，因為，執(zhí)行每一次SQL服務器都要同樣對SQL進行分析、優(yōu)化等操作。幸好MySQL提供了另一種解決方案，就是使用一條INSERT語句來插入多條記錄。這并不是標準的SQL語法，因此只能在MySQL中使用。 INSERT INTO users(name, age) VALUES('姚明', 25), ('比爾.蓋茨', 50), ('火星人', 600); 上面的INSERT 語句向users表中連續(xù)插入了3條記錄。值得注意的是，上面的INSERT語句中的VALUES后必須每一條記錄的值放到一對(…)中，中間使用","分割。假設有一個表table1 CREATE TABLE table1(n INT); 如果要向table1中插入5條記錄，下面寫法是錯誤的： INSERT INTO table1 (i) VALUES(1,2,3,4,5); MySQL將會拋出下面的錯誤 ERROR 1136: Column count doesn't match value count at row 1 而正確的寫法應該是這樣： INSERT INTO table1(i) VALUES(1),(2),(3),(4),(5); 當然，這種寫法也可以省略列名，這樣每一對括號里的值的數(shù)目必須一致，而且這個數(shù)目必須和列數(shù)一致。如： INSERT INTO table1 VALUES(1),(2),(3),(4),(5); 3. REPLACE語句 我們在使用數(shù)據(jù)庫時可能會經(jīng)常遇到這種情況。如果一個表在一個字段上建立了唯一索引，當我們再向這個表中使用已經(jīng)存在的鍵值插入一條記錄，那將會拋出一個主鍵沖突的錯誤。當然，我們可能想用新記錄的值來覆蓋原來的記錄值。如果使用傳統(tǒng)的做法，必須先使用 DELETE語句刪除原先的記錄，然后再使用INSERT插入新的記錄。而在MySQL中為我們提供了一種新的解決方案，這就是REPLACE語句。使用 REPLACE插入一條記錄時，如果不重復，REPLACE就和INSERT的功能一樣，如果有重復記錄，REPLACE就使用新記錄的值來替換原來的記錄值。 使用REPLACE的最大好處就是可以將DELETE和INSERT合二為一，形成一個原子操作。這樣就可以不必考慮在同時使用DELETE和INSERT時添加事務等復雜操作了。 在使用REPLACE時，表中必須有唯一索引，而且這個索引所在的字段不能允許空值，否則REPLACE就和INSERT完全一樣的。 在執(zhí)行REPLACE后，系統(tǒng)返回了所影響的行數(shù)，如果返回1，說明在表中并沒有重復的記錄，如果返回2，說明有一條重復記錄，系統(tǒng)自動先調用了 DELETE刪除這條記錄，然后再記錄用INSERT來插入這條記錄。如果返回的值大于2，那說明有多個唯一索引，有多條記錄被刪除和插入。 REPLACE的語法和INSERT非常的相似，如下面的REPLACE語句是插入或更新一條記錄。 REPLACE INTO users (id,name,age) VALUES(123, '趙本山', 50); 插入多條記錄： REPLACE INTO users(id, name, age) VALUES(123, '趙本山', 50), (134,'Mary',15); REPLACE也可以使用SET語句 REPLACE INTO users SET id = 123, name = '趙本山', age = 50; 上面曾提到REPLACE可能影響3條以上的記錄，這是因為在表中有超過一個的唯一索引。在這種情況下，REPLACE將考慮每一個唯一索引，并對每一個索引對應的重復記錄都刪除，然后插入這條新記錄。假設有一個table1表，有3個字段a, b, c。它們都有一個唯一索引。 CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE); 假設table1中已經(jīng)有了3條記錄 a b c 1 1 1 2 2 2 3 3 3 下面我們使用REPLACE語句向table1中插入一條記錄。 REPLACE INTO table1(a, b, c) VALUES(1,2,3); 返回的結果如下 Query OK, 4 rows affected (0.00 sec) 在table1中的記錄如下 a b c 1 2 3 我們可以看到，REPLACE將原先的3條記錄都刪除了，然后將(1, 2, 3)插入。 二、UPDATE UPDATE的功能是更新表中的數(shù)據(jù)。這的語法和INSERT的第二種用法相似。必須提供表名以及SET表達式，在后面可以加WHERE以限制更新的記錄范圍。 UPDATE table_anem SET column_name1 = value1, column_name2 = value2, WHERE ; 如下面的語句將users表中id等于123的記錄的age改為24 UPDATE users SET age = 24 WHERE id = 123; 同樣，可以使用UPDATE更新多個字段的值 UPDATE users SET age = 24, name = 'Mike' WHERE id = 123; 上面的UPDATE語句通過WHERE指定一個條件，否則，UPDATE將更新表中的所有記錄的值。 在使用UPDATE更新記錄時，如果被更新的字段的類型和所賦的值不匹配時，MySQL將這個值轉換為相應類型的值。如果這個字段是數(shù)值類型，而且所賦值超過了這個數(shù)據(jù)類型的最大范圍，那么MySQL就將這個值轉換為這個范圍最大或最小值。如果字符串太長，MySQL就將多余的字符串截去。如果設臵非空字段為空，那么將這個字段設臵為它們的默認值，數(shù)字的默認值是0，字符串的默認值是空串(不是null，是"")。 有兩種情況UPDATE不會對影響表中的數(shù)據(jù)。 1. 當WHERE中的條件在表中沒有記錄和它匹配時。 2. 當我們將同樣的值賦給某個字段時，如將字段abc賦為'123'，而abc的原值就是'123'。 和INSERT、REPLACE一樣，UPDATE也返回所更新的記錄數(shù)。但這些記錄數(shù)并不包括滿足WHERE條件的，但卻未被更新的記錄。如下同的UPDATE語句就未更新任何記錄。 UPDATE users SET age = 30 WHERE id = 12; Query OK, 0 rows affected (0.00 sec) 需要注意的時，如果一個字段的類型是TIMESTAMP，那么這個字段在其它字段更新時自動更新。 在有些時候我們需要得到UPDATE所選擇的行數(shù)，而不是被更新的行數(shù)。我們可以通過一些API來達到這個目的。如MySQL提供的C API提供了一個選項可以得到你想要的記錄數(shù)。而MySQL的JDBC驅動得到的默認記錄數(shù)也是匹配的記錄數(shù)。 UPDATE和REPLACE基本類似，但是它們之間有兩點不同。 1. UPDATE在沒有匹配記錄時什么都不做，而REPLACE在有重復記錄時更新，在沒有重復記錄時插入。 2. UPDATE可以選擇性地更新記錄的一部分字段。而REPLACE在發(fā)現(xiàn)有重復記錄時就將這條記錄徹底刪除，再插入新的記錄。也就是說，將所有的字段都更新了。 三、DELETE和TRUNCATE TABLE 在MySQL中有兩種方法可以刪除數(shù)據(jù)，一種是DELETE語句，另一種是TRUNCATE TABLE語句。DELETE語句可以通過WHERE對要刪除的記錄進行選擇。而使用TRUNCATE TABLE將刪除表中的所有記錄。因此，DELETE語句更靈活。 如果要清空表中的所有記錄，可以使用下面的兩種方法： DELETE FROM table1 TRUNCATE TABLE table1 其中第二條記錄中的TABLE是可選的。 如果要刪除表中的部分記錄，只能使用DELETE語句。 DELETE FROM table1 WHERE ; 如果DELETE不加WHERE子句，那么它和TRUNCATE TABLE是一樣的，但它們有一點不同，那就是DELETE可以返回被刪除的記錄數(shù)，而TRUNCATE TABLE返回的是0。 如果一個表中有自增字段，使用TRUNCATE TABLE和沒有WHERE子句的DELETE刪除所有記錄后，這個自增字段將起始值恢復成1.如果你不想這樣做的話，可以在DELETE語句中加上永真的WHERE，如WHERE 1或WHERE true。 DELETE FROM table1 WHERE 1; 上面的語句在執(zhí)行時將掃描每一條記錄。但它并不比較，因為這個WHERE條件永遠為true。這樣做雖然可以保持自增的最大值，但由于它是掃描了所有的記錄，因此，它的執(zhí)行成本要比沒有WHERE子句的DELETE大得多。 DELETE和TRUNCATE TABLE的最大區(qū)別是DELETE可以通過WHERE語句選擇要刪除的記錄。但執(zhí)行得速度不快。而且還可以返回被刪除的記錄數(shù)。而TRUNCATE TABLE無法刪除指定的記錄，而且不能返回被刪除的記錄。但它執(zhí)行得非常快。 與標準的SQL語句不同，DELETE支持ORDER BY和LIMIT子句，通過這兩個子句，我們可以更好地控制要刪除的記錄。如當我們只想刪除WHERE子句過濾出來的記錄的一部分，可以使用LIMIB，如果要刪除后幾條記錄，可以通過ORDER BY和LIMIT配合使用。假設我們要刪除users表中name等于"Mike"的前6條記錄。可以使用如下的DELETE語句： DELETE FROM users WHERE name = 'Mike' LIMIT 6; 一般MySQL并不確定刪除的這6條記錄是哪6條，為了更保險，我們可以使用ORDER BY對記錄進行排序。 DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6; 到現(xiàn)在為止，你只學習了如何根據(jù)特定的條件從表中取出一條或多條記錄。但是，假如你想對一個表中的記錄進行數(shù)據(jù)統(tǒng)計。例如，如果你想統(tǒng)計存儲在表中的一次民意測驗的投票結果。或者你想知道一個訪問者在你的站點上平均花費了多少時間。要對表中的任何類型的數(shù)據(jù)進行統(tǒng)計，都需要使用集合函數(shù)。你可以統(tǒng)計記錄數(shù)目，平均值，最小值，最大值，或者求和。當你使用一個集合函數(shù)時，它只返回一個數(shù)，該數(shù)值代表這幾個統(tǒng)計值之一。 這些函數(shù)的最大特點就是經(jīng)常和GROUP BY語句配合使用，需要注意的是集合函數(shù)不能和非分組的列混合使用。 行列計數(shù) 計算查詢語句返回的記錄行數(shù) 直接計算函數(shù)COUNT(*)的值，例如，計算pet表中貓的只數(shù)： mysql>SELECT count(*) FROM pet WHERE species=’cat’; +----------+ | count(*) | +----------+ | 2 | +----------+ 統(tǒng)計字段值的數(shù)目 例如，計算pet表中species列的數(shù)目： mysql> SELECT count(species) FROM pet; +----------------+ | count(species) | +----------------+ | 9 | +----------------+ 如果相同的種類出現(xiàn)了不止一次，該種類將會被計算多次。如果你想知道種類為某個特定值的寵物有多少個，你可以使用WHERE子句，如下例所示： mysql> SELECT COUNT(species) FROM pet WHERE species='cat' ; 注意這條語句的結果： +----------------+ | COUNT(species) | +----------------+ | 2 | +----------------+ 這個例子返回種類為'cat'的作者的數(shù)目。如果這個名字在表pet中出現(xiàn)了兩次，則次函數(shù)的返回值是2。 而且它和上面提到過的語句的結果是一致的： SELECT count(*) FROM pet WHERE species=’cat’ 實際上，這兩條語句是等價的。 假如你想知道有多少不同種類的的寵物數(shù)目。你可以通過使用關鍵字DISTINCT來得到該數(shù)目。如下例所示： mysql> SELECT COUNT(DISTINCT species) FROM pet; +-------------------------+ | COUNT(DISTINCT species) | +-------------------------+ | 5 | +-------------------------+ 如果種類'cat'出現(xiàn)了不止一次，它將只被計算一次。關鍵字DISTINCT 決定了只有互不相同的值才被計算。 通常，當你使用COUNT（）時，字段中的空值將被忽略。 另外，COUNT()函數(shù)通常和GROUP BY子句配合使用，例如可以這樣返回每種寵物的 數(shù)目： mysql> SELECT species,count(*) FROM pet GROUP BY species; +---------+----------+ | species | count(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ 計算字段的平均值 需要計算這些值的平均值。使用函數(shù)AVG（），你可以返回一個字段中所有值的平均值。 假如你對你的站點進行一次較為復雜的民意調查。訪問者可以在1到10之間投票，表示他們喜歡你站點的程度。你把投票結果保存在名為vote的INT型字段中。要計算你的用戶投票的平均值，你需要使用函數(shù)AVG（）： SELECT AVG(vote) FROM opinion 這個SELECT語句的返回值代表用戶對你站點的平均喜歡程度。函數(shù)AVG（）只能對數(shù)值型字段使用。這個函數(shù)在計算平均值時也忽略空值。 再給出一個實際例子，例如我們要計算pet表中每種動物年齡的平均值，那么使用AVG()函數(shù)和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 | +---------+---------------------+ 計算字段值的和 假設你的站點被用來出售某種商品，已經(jīng)運行了兩個月，是該計算賺了多少錢的時候了。 假設有一個名為orders的表用來記錄所有訪問者的定購信息。要計算所有定購量的總和，你可以使用函數(shù)SUM（）： SELECT SUM(purchase_amount) FROM orders 函數(shù)SUM（）的返回值代表字段purchase_amount中所有值的總和。字段purchase_amount的數(shù)據(jù)類型也許是DECIMAL類型，但你也可以對其它數(shù)值型字段使用函數(shù)SUM（）。 用一個不太恰當?shù)睦诱f明，我們計算pet表中同種寵物的年齡的總和： mysql> SELECT species,SUM(CURDATE()-birth) FROM pet GROUP BY species; 你可以查看結果，與前一個例子對照： +---------+----------------------+ | species | SUM(CURDATE()-birth) | +---------+----------------------+ | bird | 68320 | | cat | 149919 | | dog | 338489 | | hamster | 19890 | | snake | 49791 | +---------+----------------------+ 計算字段值的極值 求字段的極值，涉及兩個函數(shù)MAX()和MIN()。 例如，還是pet表，你想知道最早的動物出生日期，由于日期最早就是最小，所以可以使用MIN()函數(shù)： mysql> SELECT MIN(birth) FROM pet; +------------+ | MIN(birth) | +------------+ | 1989-05-13 | +------------+ 但是，你只知道了日期，還是無法知道是哪只寵物，你可能想到這樣做： SELECT name,MIN(birth) FROM pet; 但是，這是一個錯誤的SQL語句，因為集合函數(shù)不能和非分組的列混合使用，這里name列是沒有分組的。所以，你無法同時得到name列的值和birth的極值。 MIN()函數(shù)同樣可以與GROUP BY子句配合使用，例如，找出每種寵物中最早的出生日期： mysql> SELECT species,MIN(birth) FROM pet GROUP BY species; 下面是令人滿意的結果： +---------+------------+ | species | MIN(birth) | +---------+------------+ | bird | 1997-12-09 | | cat | 1993-02-04 | | dog | 1989-05-13 | | hamster | 1999-03-30 | | snake | 1996-04-29 | +---------+------------+ 另一方面，如果你想知道最近的出生日期，就是日期的最大值，你可以使用MAX()函數(shù)，如下例所示： mysql> SELECT species,MAX(birth) FROM pet GROUP BY species; +---------+------------+ | species | MAX(birth) | +---------+------------+ | bird | 1998-09-11 | | cat | 1994-03-17 | | dog | 1990-08-31 | | hamster | 1999-03-30 | | snake | 1996-04-29 | +---------+------------+ 總結 在本節(jié)中，介紹了一些典型的集合函數(shù)的用法，包括計數(shù)、均值、極值和總和，這些都是SQL語言中非常常用的函數(shù)。 這些函數(shù)之所以稱之為集合函數(shù)，是因為它們應用在多條記錄中，所以集合函數(shù)最常見的用法就是與GROUP BY子句配合使用，最重要的是集合函數(shù)不能同未分組的列混合使用。 日期和時間函數(shù)對建立一個站點是非常有用的。站點的主人往往對一個表中的數(shù)據(jù)何時被更新感興趣。通過日期和時間函數(shù)，你可以在秒級跟蹤一個表的改變。 日期和時間類型是DATETIME、DATE、TIMESTAMP、TIME和YEAR。這些的每一個都有合法值的一個范圍，而?零?當你指定確實不合法的值時被使用。注意，MySQL允許你存儲某個?不嚴格地?合法的日期值，例如1999-11-31，原因我們認為它是應用程序的責任來處理日期檢查，而不是SQL服務器。為了使日期檢查更?快?，MySQL僅檢查月份在0-12的范圍，天在0-31的范圍。上述范圍這樣被定義是因為MySQL允許你在一個DATE或DATETIME列中存儲日期，這里的天或月是零。這對存儲你不知道準確的日期的一個生日的應用程序來說是極其有用的，在這種情況下，你簡單地存儲日期象1999-00-00或1999-01-00。（當然你不能期望從函數(shù)如DATE_SUB()或DATE_ADD()得到類似以這些日期的正確值）。 返回當前日期和時間通過函數(shù)GETDATE（），你可以獲得當前的日期和時間。例如， CURDATE() 返回當前日期 CURRENT_DATE 以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取決于函數(shù)是在一個字符串還是數(shù)字上下文被使用。 mysql> select CURDATE(); +------------+ | CURDATE() | +------------+ | 2001-02-20 | +------------+ mysql> select CURDATE() + 0; +-------------+ | CURDATE()+0 | +-------------+ | 20010220 | +-------------+ CURTIME() 返回當前時間 以'HH:MM:SS'或HHMMSS格式返回當前時間值，取決于函數(shù)是在一個字符串還是在數(shù)字的上下文被使用。 mysql> select CURTIME(); +-----------+ | CURTIME() | +-----------+ | 10:42:38 | +-----------+ mysql> select CURTIME() + 0; +-------------+ | CURTIME()+0 | +-------------+ | 104525 | +-------------+ NOW() 返回當前時期和時間 NOW()以YYYY-MM-DD HH:MM:SS的格式或者YYYYMMDDHHMMSS的格式返回日 期和時間值，取決于上下文。 mysql>select now(); +---------------------+ | now() | +---------------------+ | 2001-02-20 10:45:57 | +---------------------+ mysql>select now()+0; +----------------+ | now()+0 | +----------------+ | 20010220105635 | +----------------+ 這些得到當前日期和時間的函數(shù)，對于日期和時間的計算很方便，尤其是計算一個時間到現(xiàn)在的時間差。例如，在pet表中，我們以天為單位計算寵物的年齡： mysql> SELECT name,CURDATE()-birth FROM pet; +----------+-----------------+ | name | CURDATE()-birth | +----------+-----------------+ | Fluffy | 80016 | | Claws | 69903 | | Buffy | 119707 | | Chirpy | 29309 | | Fang | 109393 | | Bowser | 109389 | | Whistler | 39011 | | Slim | 49791 | | Puffball | 19890 | +----------+-----------------+ 自動記錄數(shù)據(jù)的改變時間 TIMESTAMP列類型提供一種類型，TIMESTAMP值可以從1970的某時的開始一直到2037年，精度為一秒，其值作為數(shù)字顯示。你可以使用它自動地用當前的日期和時間標記INSERT或UPDATE的操作。如果你有多個TIMESTAMP列，只有第一個自動更新。 自動更新第一個TIMESTAMP列在下列任何條件下發(fā)生： 列沒有明確地在一個INSERT或LOAD DATA INFILE語句中指定。 列沒有明確地在一個UPDATE語句中指定且一些另外的列改變值。（注意一個UPDATE設臵一個列為它已經(jīng)有的值，這將不引起TIMESTAMP列被更新，因為如果你設臵一個列為它當前的值，MySQL為了效率而忽略更改。） 你明確地設定TIMESTAMP列為NULL. 除第一個以外的TIMESTAMP列也可以設臵到當前的日期和時間，只要將列設為 NULL，或NOW()。 例如，創(chuàng)建如下的表： mysql> CREATE 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; +------+---------+---------+---------+---------+----------------+ | id | name | english | chinese | history | time | +------+---------+---------+---------+---------+----------------+ | 11 | Tom | 66 | 93 | 67 | 20010220123335 | +------+---------+---------+---------+---------+----------------+ 你可以看到time列紀錄下了數(shù)據(jù)錄入時的時間值。如果你更新改記錄，在查看操作的結果： mysql> UPDATE student SET english=76 WHERE id=11; mysql> SELECT * FROM student; +------+------+---------+---------+---------+----------------+ | id | name | english | chinese | history | 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 | name | english | chinese | history | time | +------+------+---------+---------+---------+----------------+ | 11 | Tom | 76 | 93 | 67 | 20010220130517 | +------+------+---------+---------+---------+----------------+ 通過明確地設臵希望的值，你可以設臵任何TIMESTAMP列為不同于當前日期和時間的值，即使對第一個TIMESTAMP列也是這樣。例如，如果，當你創(chuàng)建一個行時，你想要一個TIMESTAMP被設臵到當前的日期和時間，但在以后無論何時行被更新時都不改變，你可以使用這樣使用： 讓MySQL在行被創(chuàng)建時設臵列，這將初始化它為當前的日期和時間。 當你執(zhí)行隨后的對該行中其他列的更改時，明確設定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 | +------+------+---------+---------+---------+----------------+ 另一方面，你可能發(fā)現(xiàn)，當你想要實現(xiàn)上面這個效果時，很容易用一個你用NOW()初始化的DATETIME列然后不再改變它，這樣也許直接些。 但是，TIMESTAMP列的以后好處是存儲要求比較小，節(jié)省空間。TIMESTAMP的存儲需求是4字節(jié)，而DATETIME列的存儲需求是8字節(jié)。 返回日期和時間范圍當你分析表中的數(shù)據(jù)時，你也許希望取出某個特定時間的數(shù)據(jù)。我們用下面一個表來模仿一個web站點的記錄。 mysql> CREATE TABLE weblog -> ( -> data float, -> entrydate datetime -> ); 然后隨機的增加幾個數(shù)據(jù)： mysql> INSERT weblog VALUES(rand(),now()); rand()函數(shù)返回一個隨機的浮點值，now()函數(shù)返回當前時間。多執(zhí)行上面語句幾次，得到一個作為測試的表。 最為測試你還可以增加一個值： mysql> INSERT weblog VALUES(rand(),?2001-02-08?); 這條語句，插入一個entry為?2001-02-08 00:00:00?的值（假定現(xiàn)在為2001年2月8日），你可以查看這個表的值： mysql> select * from weblog; +-----------+---------------------+ | 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:09 | | 0.0931809 | 2001-02-08 13:58:29 | | 0.198805 | 2001-02-08 13:57:54 | +-----------+---------------------+ 你也許對特定的某一天中――比如說2001年2月18日――訪問者在你站點上的活動感興趣。要取出這種類型的數(shù)據(jù)，你也許會試圖使用這樣的SELECT語句： mysql> SELECT * FROM weblog WHERE entrydate="2001-02-08" 不要這樣做。這個SELECT語句不會返回正確的記錄――它將只返回值為2000-02-08 00:00:00的記錄，換句話說，只返回當天零點零時的記錄。上面語句的結果為： +----------+---------------------+ | data | entrydate | +----------+---------------------+ | 0.973723 | 2001-02-08 00:00:00 | +----------+---------------------+ 要返回正確的記錄，你需要適用日期和時間范圍。有不止一種途徑可以做到這一點。 1、使用關系運算符和邏輯運算符來限制時間范圍 例如，下面的這個SELECT 語句將能返回正確的記錄： mysql> SELECT * FROM weblog -> WHERE entrydate>="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:09 | | 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-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"; 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運算符和模式匹配，是通過比較串值進行的，因此必須使用標準的時間書寫格式，YYYY-MM-DD HH-MM-SS。 比較日期和時間 已知兩個日期，比較它們的前后，可以直接求出它們的差和零值比較，也可以利用已知的時間函數(shù)： TO_DAYS(date) 給出一個日期date，返回一個天數(shù)(從0年的天數(shù))，date可以是一個數(shù)字，也可以是一個串值，當然更可以是包含日期的時間類型。 mysql> select TO_DAYS(960501); +-----------------+ | TO_DAYS(960501) | +-----------------+ | 729145 | +-----------------+ mysql> select TO_DAYS('1997-07-01'); +-----------------------+ | TO_DAYS('1997-07-01') | +-----------------------+ | 729571 | +-----------------------+ 例如：返回2個時間相差的天數(shù)(21世紀已經(jīng)過去了多少天) mysql> select to_days(now())-to_days('20010101'); +---------------------------------------------------+ | to_days(now()-00000012000000)-to_days('20010101') | +---------------------------------------------------+ | 38 | +---------------------------------------------------+ MySQL提供標準的SQL模式匹配，以及一種基于象Unix實用程序如vi、grep和sed的擴展正則表達式模式匹配的格式。 標準的SQL模式匹配 SQL的模式匹配允許你使用?_?匹配任何單個字符，而?%?匹配任意數(shù)目字符(包括零個字符)。在 MySQL中，SQL的模式缺省是忽略大小寫的。下面顯示一些例子。注意在你使用SQL模式時，你不能使用=或!=；而使用LIKE或NOT LIKE比較操作符。 例如，在表pet中，為了找出以?b?開頭的名字： mysql> SELECT * FROM pet WHERE name LIKE "b%"; +--------+--------+---------+------+------------+------------+ | 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 WHERE name LIKE "%fy"; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | 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-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 | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 擴展正則表達式模式匹配 由MySQL提供的模式匹配的其他類型是使用擴展正則表達式。當你對這類模式進行匹配測試時，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它們是同義詞)。 擴展正則表達式的一些字符是： ?.?匹配任何單個的字符。 一個字符類?[...]?匹配在方括號內的任何字符。例如，?[abc]?匹配?a?、?b?或?c?。 為了命名字符的一個范圍，使用一個?-?。 ?[a-z]?匹配任何小寫字母，而?[0-9]?匹配任何數(shù)字。 ? * ?匹配零個或多個在它前面的東西。例如，?x*?匹配任何數(shù)量的?x?字符，?[0-9]*?匹配的任何數(shù)量的數(shù)字，而?.*?匹配任何數(shù) 量的任何東西。 正則表達式是區(qū)分大小寫的，但是如果你希望，你能使用一個字符類匹配兩種寫法。例如，?[aA]?匹配小寫或大寫的?a?而?[a-zA-Z]?匹 配兩種寫法的任何字母。 如果它出現(xiàn)在被測試值的任何地方，模式就匹配(只要他們匹配整個值，SQL模式匹配)。 為了定位一個模式以便它必須匹配被測試值的開始或結尾，在模式開始處使用?^?或在模式的結尾用?$?。 為了說明擴展正則表達式如何工作，上面所示的LIKE查詢在下面使用REGEXP重寫： 為了找出以?b?開頭的名字，使用?^?匹配名字的開始并且?[bB]?匹配小寫或大寫的?b?： mysql> 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 WHERE 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 "[wW]"; +----------+-------+---------+------+------------+------------+ | 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 | +----------+-------+---------+------+------------+------------+ 既然如果一個正規(guī)表達式出現(xiàn)在值的任何地方，其模式匹配了，就不必再先前的查詢中在模式的兩方面放臵一個通配符以使得它匹配整個值， 就像如果你使用了一個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 | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 總結 本介紹了有關字符串模式匹配的有關知識。標準的SQL模式匹配是SQL語言的標準，可以被其它關系數(shù)據(jù)庫系統(tǒng)接受。擴展正規(guī)表達式模式匹配 是根據(jù)Unix系統(tǒng)的標準開發(fā)了，一般只可使用在MySQL上，但是其功能要比標準的SQL模式匹配更強。 本節(jié)將講述SELECT語句的一些高級功能。 列和表的別名 列的別名 精選輸出的列可以用列名、列別名或列位臵在ORDER BY和GROUP BY子句引用，列位臵從1開始。 例如，我們從pet表中檢索出寵物和種類，直接引用列名： mysql> SELECT name,species FROM pet ORDER BY name, species; 其輸出為： +----------+---------+ | name | species | +----------+---------+ | Bowser | dog | | Buffy | dog | | Chirpy | bird | | Claws | cat | | Fang | dog | | Fluffy | cat | | Puffball | hamster | | Slim | snake | | Whistler | bird | +----------+---------+ 在子句中使用列的位臵： mysql> SELECT name,species FROM pet ORDER BY 1,2; 這條語句的輸出與上面并無不同。 最后，你還可以為列命名： mysql> SELECT name AS n,species AS s FROM pet ORDER BY n,s; 注意返回的結果： +----------+---------+ | n | s | +----------+---------+ | Bowser | dog | | Buffy | dog | | Chirpy | bird | | Claws | cat | | Fang | dog | | Fluffy | cat | | Puffball | hamster | | Slim | snake | | Whistler | bird | +----------+---------+ 返回的記錄順序并無不同。但是列的名字有了改變，這一點在使用CREATE TABLE…SELECT語句創(chuàng)建表時是有意義的。 例如，我們想從pet表生成包括其中name，owner字段的表，但是想把name和owner字段的名字重新命名為animal和child，一個很笨的方法就是創(chuàng)建表再錄入數(shù)據(jù)，如果使用別名，則僅僅一條SQL語句就可以解決問題，非常簡單，我們要使用的語句使CREATE TABLE： mysql> CREATE TABLE pet1 -> SELECT name AS animal,owner AS child -> FROM pet; 然后，檢索生成的表，看看是否打到目的： mysql> SELECT * FROM pet1; +----------+--------+ | animal | child | +----------+--------+ | Fluffy | Harold | | Claws | Gwen | | Buffy | Harold | | Chirpy | Gwen | | Fang | Benny | | Bowser | Diane | | Whistler | Gwen | | Slim | Benny | | Puffball | Diane | +----------+--------+ 在子句中使用列的別名 你可以在GROUP BY、ORDER BY或在HAVING部分中使用別名引用列。別名也可以 用來為列取一個更好點的名字： mysql> SELECT species,COUNT(*) AS total FROM pet -> GROUP BY species HAVING total>1; +---------+-------+ | species | total | +---------+-------+ | bird | 2 | | cat | 2 | | dog | 3 | +---------+-------+ 注意，你的 ANSI SQL 不允許你在一個WHERE子句中引用一個別名。這是因為在WHERE代碼被執(zhí)行時，列值還可能沒有終結。例如下列查詢是不合法： SELECT id,COUNT(*) AS total FROM pet WHERE total > 1 GROUP BY species 會有下面的錯誤： ERROR 1054: Unknown column 'total' in 'where clause' WHERE語句被執(zhí)行以確定哪些行應該包括GROUP BY部分中，而HAVING用來決定應該只用結果集合中的哪些行。 表的別名 別名不僅可以應用于列，也可以引用于表名，具體方法類似于列的別名，這里不再重復。 列的別名經(jīng)常用于表自身的連接中。你不必有2個不同的表來執(zhí)行一個聯(lián)結。如果你想要將一個表的記錄與同一個表的其他記錄進行比較，聯(lián)結一個表到自身有時是有用的。 例如，為了在你的寵物之中繁殖配偶，你可以用pet聯(lián)結自身來進行相似種類的雄雌配對： mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1, pet AS p2 -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"; +--------+------+--------+------+---------+ | name | sex | name | sex | species | +--------+------+--------+------+---------+ | Fluffy | f | Claws | m | cat | | Buffy | f | Fang | m | dog | | Buffy | f | Bowser | m | dog | +--------+------+--------+------+---------+ 在這個查詢中，我們?yōu)楸砻付▌e名以便能引用列并且使得每一個列引用關聯(lián)于哪個表實例更直觀。 取出互不相同的記錄 有時候你可能希望取出的數(shù)據(jù)互不重復，因為重復的數(shù)據(jù)可能對你沒有意義。 解決的辦法是使用DISTINCT關鍵字，使用這個關鍵字保證結果集中不包括重復的記錄，也就是說，你取出的記錄中，沒有重復的行。 例如，我們取出pet表中Benny所擁有的寵物的記錄： mysql> SELECT name,owner,species,sex FROM pet WHERE owner="Benny"; +------+------+---------+------+ | name | owner | species | sex | +------+-------+---------+------+ | Fang | Benny | dog | m | | Slim | Benny | snake | m | +------+-------+---------+------+ 注意上面的結果，因為我們要使用它。 假定我們指定DISTINCT關鍵字，并返回列name，species，sex列： mysql> SELECT DISTINCT name,species,sex FROM pet WHERE owner="Benny"; +------+---------+------+ | name | species | sex | +------+---------+------+ | Fang | dog | m | | Slim | snake | m | +------+---------+------+ 你可以看到有兩條結果，這是因為返回的結果集中的行不同，如果我們做以下更改，只返回owner，sex列，你可以觀察變化： mysql> SELECT DISTINCT owner,sex FROM pet WHERE owner="Benny"; +-------+------+ | owner | sex | +-------+------+ | Benny | m | +-------+------+ DISTINCT關鍵字的存在，使查詢只返回不同的記錄行。 如果一個表中，有完全相同的行，你可以使用DISTINCT，以去除冗余的輸出： SELECT DISTINCT * FROM tbl_name NULL值的問題 NULL值可能很奇怪直到你習慣于它。概念上，NULL意味著?沒有值?或?未知值?，且它被看作有點與眾不同的值。為了測試NULL，你不能使用算術比較運算符例如=、<或!=。為了說明它，試試下列查詢： mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 != NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 很清楚你從這些比較中得到毫無意義的結果。相反使用IS NULL和IS NOT NULL操作符： mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+ 在MySQL中，0意味著假而1意味著真。 NULL這樣特殊的處理是為什么，在前面的章節(jié)中，為了決定哪個動物不再是活著的，使用death IS NOT NULL而不是death != NULL是必要的 mysql> SELECT * FROM pet WHERE death IS NOT NULL; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | 1990-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ NULL值的概念是造成SQL的新手的混淆的普遍原因，他們經(jīng)常認為NULL是和一個空字符串''的一樣的東西。不是這樣的！例如，下列語句是完全不同的： mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES (""); 兩個語句把值插入到phone列，但是第一個插入一個NULL值而第二個插入一個空字符串。第一個的含義可以認為是?電話號碼不知道?，而第二個則可意味著?她沒有電話?。 在SQL中，NULL值在于任何其他值甚至NULL值比較時總是假的（FALSE）。包含NULL的一個表達式總是產(chǎn)生一個NULL值，除非在包含在表達式中的運算符和函數(shù)的文檔中指出。在下列例子，所有的列返回NULL： mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL); +------+--------+--------------------------+ | NULL | 1+NULL | CONCAT('Invisible',NULL) | +------+--------+--------------------------+ | NULL | NULL | NULL | +------+--------+--------------------------+ 如果你想要尋找值是NULL的列，你不能使用=NULL測試。下列語句不返回任何行，因為對任何表達式，expr = NULL是假的： mysql> SELECT * FROM my_table WHERE phone = NULL; 要想尋找NULL值，你必須使用IS NULL測試。下例顯示如何找出NULL電話號碼和空的電話號碼： mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = ""; 在MySQL中，就像很多其他的SQL服務器一樣，你不能索引可以有NULL值的列。你必須聲明這樣的列為NOT NULL，而且，你不能插入NULL到索引的列中。 當使用ORDER BY時，首先呈現(xiàn)NULL值。如果你用DESC以降序排序，NULL值最后顯示。當使用GROUP BY時，所有的NULL值被認為是相等的。 為了有助于NULL的處理，你能使用IS NULL和IS NOT NULL運算符和IFNULL()函數(shù)。 對某些列類型，NULL值被特殊地處理。如果你將NULL插入表的第一個IMESTAMP列，則插入當前的日期和時間。如果你將NULL插入一個AUTO_INCREMENT列，則插入順序中的下一個數(shù)字。 大小寫敏感性 1、數(shù)據(jù)庫和表名 在MySQL中，數(shù)據(jù)庫和表對應于在那些目錄下的目錄和文件，因而，內在的操作系統(tǒng)的敏感性決定數(shù)據(jù)庫和表命名的大小寫敏感性。這意味著數(shù)據(jù)庫和表名在Unix上是區(qū)分大小寫的，而在Win32上忽略大小寫。 注意：在Win32上，盡管數(shù)據(jù)庫和表名是忽略大小寫的，你不應該在同一個查詢中使用不同的大小寫來引用一個給定的數(shù)據(jù)庫和表。下列查詢將不工作，因為它作為my_table和作為MY_TABLE引用一個表： mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1; 2、列名 列名在所有情況下都是忽略大小寫的。 3、表的別名 表的別名是區(qū)分大小寫的。下列查詢將不工作，: 因為它用a和A引用別名： mysql> SELECT col_name FROM tbl_name AS a WHERE a.col_name = 1 OR A.col_name = 2; 4、列的別名 列的別名是忽略大小寫的。 5、字符串比較和模式匹配 缺省地，MySQL搜索是大小寫不敏感的(盡管有一些字符集從來不是忽略大小寫的，例如捷克語)。這意味著，如果你用col_name LIKE 'a%'搜尋，你將得到所有以A或a開始的列值。如果你想要使這個搜索大小寫敏感，使用象INDEX(col_name, "A")=0檢查一個前綴?；蛉绻兄当仨毚_切是"A"，使用STRCMP(col_name, "A") = 0。 簡單的比較操作(>=、>、= 、< 、<=、排序和聚合)是基于每個字符的?排序值?。有同樣排序值的字符(象E，e)被視為相同的字符！LIKE比較在每個字符的大寫值上進行(?E?=?e?)。 如果你想要一個列總是被當作大小寫敏感的方式，聲明它為BINARY。 例如： mysql> SELECT "E"="e","E"=BINARY "e"; +---------+----------------+ | "E"="e" | "E"=BINARY "e" | +---------+----------------+ | 1 | 0 | +---------+----------------+ 檢索語句與多個表的連接 SELECT語句不僅可以從單個表中檢索數(shù)據(jù)，也可以通過連接多個表來檢索數(shù)據(jù)。這里將介紹全連接和左連接的作用。 我們創(chuàng)建兩個表作為例子。 mysql> CREATE TABLE first -> ( -> id TINYINT, -> first_name CHAR(10) -> ); 錄入如下數(shù)據(jù)： +------+-----------+ | id | first_name| +------+-----------+ | 1 | Tom | | 2 | Marry | | 3 | Jarry | +------+-----------+ mysql> CREATE TABLE last -> ( -> id TINYINT, -> last_name CHAR(10) ６９ -> ); 錄入數(shù)據(jù) +------+-----------+ | id | last_name | +------+-----------+ | 2 | Stone | | 3 | White | | 4 | Donald | +------+-----------+ 全連接 全連接：在檢索時指定多個表，將每個表用都好分隔，這樣每個表的數(shù)據(jù)行都和其他表的每行交叉產(chǎn)生所有可能的組合，這樣就是一個全連接。所有可能的組和數(shù)即每個表的行數(shù)之和。 那么觀察下面的檢索的結果： mysql> SELECT * FROM first,last; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 2 | Marry | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 3 | Jarry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 你可以看到輸出的結果集中共有3×3=9 行，這就是全連接的結果。 你也可以這樣使用SQL語句： mysql> SELCT first.*,last.* FROM first,last; 輸出結果與上面的例子相同，并無二致。記錄集的輸出的排序是以FROM子句后的表的順序進行，即先排列位臵靠前的表，即使你改變記錄集中列的顯示順序，例如下面的例子： mysql> SELECT last.*,first.* FROM first,last; +------+-----------+------+------------+ | id | last_name | id | first_name | +------+-----------+------+------------+ | 2 | Stone | 1 | Tom | | 2 | Stone | 2 | Marry | | 2 | Stone | 3 | Jarry | | 3 | White | 1 | Tom | | 3 | White | 2 | Marry | | 3 | White | 3 | Jarry | | 4 | Donald | 1 | Tom | | 4 | Donald | 2 | Marry | | 4 | Donald | 3 | Jarry | +------+-----------+------+------------+ 上面的例子是兩個非常小的表的例子，如果是幾個非常大的表的全連接，例如，兩個行數(shù)分別為1000的表，這樣的連接可以產(chǎn)生非常大的結果集合1000×1000＝100萬行。而實際上你并不需要這么多行的結果，通常你需要使用一個WHERE從句來限制返回的記錄集的行數(shù)： mysql> SELECT * FROM first,last WHERE first.id= last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 左連接 左連接：全連接給出FROM子句中所有表都有匹配的行。對于左連接，不僅匹配類似前面的行記錄，而且還顯示左邊的表有而右邊的表中無匹配的行。對于這樣的行，從右邊表選擇的列均被顯示為NULL。這樣，每一匹配的行都從左邊的表被選出，而如果右邊表有一個匹配的行，則被選中，如果不匹配，行仍然被選中，不過，其中右邊相應的列在結果集中均設為NULL。即，LEFT JOIN強制包含左邊表的每一行，而不管右邊表是否匹配。 語法：SELECT FROM table_reference LEFT JOIN table_reference ON conditional_expr 其中table_reference為連接的表，ON子句后接類似WHERE子句的條件。 下面我們詳細講述左連接的使用： 首先，返回一個全連接的結果集： mysql> SELECT * FROM first,last; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 2 | Marry | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 3 | Jarry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 注意上面的結果，下面的例子要與這個例子對照。 我們在給出一個限制條件的查詢： mysql> SELECT * FROM first,last WHERE first.id=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 這個結果類似于是從上一個全連接中選擇出first.id>last.id 的行。 現(xiàn)在我們給出一個真正的左連接的例子，你可以仔細觀察它的結果，要了解檢索的記錄順序： mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | NULL | NULL | | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 上面的結果，即用左邊表的每一行與右邊表匹配，如果匹配，則選擇到結果集中，如果沒有匹配，則結果集中，右邊表相應的列臵為NULL。 為了進一步理解這一點，我們給出一個有點奇怪的例子： mysql> SELECT * FROM first LEFT JOIN last ON first.id=1; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 1 | Tom | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | NULL | NULL | | 3 | Jarry | NULL | NULL | +------+------------+------+-----------+ 因為，在結果的最后兩行有似乎你不希望的結果。記住，如果只有ON子句的條件，那么左邊表的每一行都會返回，只是如果沒有匹配的右邊表（雖然本例沒有約束右邊表的列），則記錄集中顯示為NULL。 前面只是幫助你理解左連接，下面LEFT JOIN的一些有用的技巧。LEFT JOIN最常見的是與WHERE子句共同使用。 使用IS NULL或者IS NOT NULL操作符可以篩選NULL或者非NULL值的列，這是最常見的技巧。 例如，選出first.id=last.id的組合,并且剔除其中沒有右表的匹配記錄： mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NOT NULL; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 你可以看到這樣做的例子結果與語句 SELECT * FROM first,last WHERE first.id=last.id 的輸出是相同的。 又如，檢索id值只在左邊表出現(xiàn)，而不再右邊表出現(xiàn)的記錄： mysql> SELECT first.* FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NULL; +------+------------+ | id | first_name | +------+------------+ | 1 | Tom | +------+------------+ 這個語句是不能用功能相同的帶WHERE子句的全連接代替的。 注意：全連接和左連接的結果集排列順序是不同的，例如： mysql> SELECT * FROM first,last WHERE first.id!=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ mysql> SELECT * FROM first LEFT JOIN last ON first.id!=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 1 | Tom | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 3 | White | | 2 | Marry | 4 | Donald | | 3 | Jarry | 2 | Stone | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 總結 本節(jié)的內容非常繁雜，各小節(jié)之間可能沒有什么聯(lián)系，但是本節(jié)所述的都是檢索數(shù)據(jù)時很常用的技巧，主要的一些內容如下： 1、為表和列使用別名 2、注意NULL值在查詢中的使用 3、注意表名、列名、別名和字符串的大小寫問題 4、如何避免取出重復的記錄 檢索語句與多個表的連接 SELECT語句不僅可以從單個表中檢索數(shù)據(jù)，也可以通過連接多個表來檢索數(shù)據(jù)。這里將介紹全連接和左連接的作用。 我們創(chuàng)建兩個表作為例子。 mysql> CREATE TABLE first -> ( -> id TINYINT, -> first_name CHAR(10) -> ); 錄入如下數(shù)據(jù)： +------+-----------+ | id | first_name| +------+-----------+ | 1 | Tom | | 2 | Marry | | 3 | Jarry | +------+-----------+ mysql> CREATE TABLE last -> ( -> id TINYINT, -> last_name CHAR(10) -> ); 錄入數(shù)據(jù) +------+-----------+ | id | last_name | +------+-----------+ | 2 | Stone | | 3 | White | | 4 | Donald | +------+-----------+ 全連接 全連接：在檢索時指定多個表，將每個表用都好分隔，這樣每個表的數(shù)據(jù)行都和其他表的每行交叉產(chǎn)生所有可能的組合，這樣就是一個全連接。所有可能的組和數(shù)即每個表的行數(shù)之和。 那么觀察下面的檢索的結果： mysql> SELECT * FROM first,last; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+----------- | 1 | Tom | 2 | Stone | | 2 | Marry | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 3 | Jarry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 你可以看到輸出的結果集中共有3×3=9 行，這就是全連接的結果。 你也可以這樣使用SQL語句： mysql> SELCT first.*,last.* FROM first,last; 輸出結果與上面的例子相同，并無二致。記錄集的輸出的排序是以FROM子句后的表的順序進行，即先排列位臵靠前的表，即使你改變記錄集中列的顯示順序，例如下面的例子： mysql> SELECT last.*,first.* FROM first,last; +------+-----------+------+------------+ | id | last_name | id | first_name | +------+-----------+------+------------+ | 2 | Stone | 1 | Tom | | 2 | Stone | 2 | Marry | | 2 | Stone | 3 | Jarry | | 3 | White | 1 | Tom | | 3 | White | 2 | Marry | | 3 | White | 3 | Jarry | | 4 | Donald | 1 | Tom | | 4 | Donald | 2 | Marry | | 4 | Donald | 3 | Jarry | +------+-----------+------+------------+ 上面的例子是兩個非常小的表的例子，如果是幾個非常大的表的全連接，例如，兩個行數(shù)分別為1000的表，這樣的連接可以產(chǎn)生非常大的結果集合1000×1000＝100萬行。而實際上你并不需要這么多行的結果，通常你需要使用一個WHERE從句來限制返回的記錄集的行數(shù)： mysql> SELECT * FROM first,last WHERE first.id= last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 左連接 左連接：全連接給出FROM子句中所有表都有匹配的行。對于左連接，不僅匹配類似前面的行記錄，而且還顯示左邊的表有而右邊的表中無匹配的行。對于這樣的行，從右邊表選擇的列均被顯示為NULL。這樣，每一匹配的行都從左邊的表被選出，而如果右邊表有一個匹配的行，則被選中，如果不匹配，行仍然被選中，不過，其中右邊相應的列在結果集中均設為NULL。即，LEFT JOIN強制包含左邊表的每一行，而不管右邊表是否匹配。 語法：SELECT FROM table_reference LEFT JOIN table_reference ON conditional_expr 其中table_reference為連接的表，ON子句后接類似WHERE子句的條件。 下面我們詳細講述左連接的使用： 首先，返回一個全連接的結果集： mysql> SELECT * FROM first,last; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 2 | Marry | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 3 | Jarry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 注意上面的結果，下面的例子要與這個例子對照。 我們在給出一個限制條件的查詢： mysql> SELECT * FROM first,last WHERE first.id=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 這個結果類似于是從上一個全連接中選擇出first.id>last.id 的行。 現(xiàn)在我們給出一個真正的左連接的例子，你可以仔細觀察它的結果，要了解檢索的記錄順序： mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | NULL | NULL | | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 上面的結果，即用左邊表的每一行與右邊表匹配，如果匹配，則選擇到結果集中，如果沒有匹配，則結果集中，右邊表相應的列臵為NULL。 為了進一步理解這一點，我們給出一個有點奇怪的例子： mysql> SELECT * FROM first LEFT JOIN last ON first.id=1; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 1 | Tom | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | NULL | NULL | | 3 | Jarry | NULL | NULL | +------+------------+------+-----------+ 因為，在結果的最后兩行有似乎你不希望的結果。記住，如果只有ON子句的條件，那么左邊表的每一行都會返回，只是如果沒有匹配的右邊表（雖然本例沒有約束右邊表的列），則記錄集中顯示為NULL。 前面只是幫助你理解左連接，下面LEFT JOIN的一些有用的技巧。LEFT JOIN最常見的是與WHERE子句共同使用。 使用IS NULL或者IS NOT NULL操作符可以篩選NULL或者非NULL值的列，這是最常見的技巧。 例如，選出first.id=last.id的組合,并且剔除其中沒有右表的匹配記錄： mysql> SELECT * FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NOT NULL; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 2 | Marry | 2 | Stone | | 3 | Jarry | 3 | White | +------+------------+------+-----------+ 你可以看到這樣做的例子結果與語句 SELECT * FROM first,last WHERE first.id=last.id 的輸出是相同的。 又如，檢索id值只在左邊表出現(xiàn)，而不再右邊表出現(xiàn)的記錄： mysql> SELECT first.* FROM first LEFT JOIN last ON first.id=last.id -> WHERE last.id IS NULL; +------+------------+ | id | first_name | +------+------------+ | 1 | Tom | +------+------------+ 這個語句是不能用功能相同的帶WHERE子句的全連接代替的。 注意：全連接和左連接的結果集排列順序是不同的，例如： mysql> SELECT * FROM first,last WHERE first.id!=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 3 | Jarry | 2 | Stone | | 1 | Tom | 3 | White | | 2 | Marry | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 4 | Donald | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ mysql> SELECT * FROM first LEFT JOIN last ON first.id!=last.id; +------+------------+------+-----------+ | id | first_name | id | last_name | +------+------------+------+-----------+ | 1 | Tom | 2 | Stone | | 1 | Tom | 3 | White | | 1 | Tom | 4 | Donald | | 2 | Marry | 3 | White | | 2 | Marry | 4 | Donald | | 3 | Jarry | 2 | Stone | | 3 | Jarry | 4 | Donald | +------+------------+------+-----------+ 總結 本節(jié)的內容非常繁雜，各小節(jié)之間可能沒有什么聯(lián)系，但是本節(jié)所述的都是檢索數(shù)據(jù)時很常用的技巧，主要的一些內容如下： 1、為表和列使用別名 2、注意NULL值在查詢中的使用 3、注意表名、列名、別名和字符串的大小寫問題 4、如何避免取出重復的記錄 由于GROUP BY 實際上也同樣會進行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之后的分組操作。當然，如果在分組的時候還使用了其他的一些聚合函數(shù)，那么還需要一些聚合函數(shù)的計算。所以，在GROUP BY 的實現(xiàn)過程中，與 ORDER BY 一樣也可以利用到索引。 在MySQL 中，GROUP BY 的實現(xiàn)同樣有多種(三種)方式，其中有兩種方式會利用現(xiàn)有的索引信息來完成 GROUP BY，另外一種為完全無法使用索引的場景下使用。下面我們分別針對這三種實現(xiàn)方式做一個分析。 1.使用松散(Loose)索引掃描實現(xiàn) GROUP BY 何謂松散索引掃描實現(xiàn) GROUP BY 呢?實際上就是當 MySQL 完全利用索引掃描來實現(xiàn) GROUP BY 的時候，并不需要掃描所有滿足條件的索引鍵即可完成操作得出結果。 下面我們通過一個示例來描述松散索引掃描實現(xiàn) GROUP BY，在示例之前我們需要首先調整一下 group_message 表的索引，將 gmt_create 字段添加到 group_id 和 user_id 字段的索引中： 1 sky@localhost : example 08:49:45> create index idx_gid_uid_gc 2 3 -> on group_message(group_id,user_id,gmt_create); 4 5 Query OK, rows affected (0.03 sec) 6 7 Records: 96 Duplicates: 0 Warnings: 0 8 9 sky@localhost : example 09:07:30> drop index idx_group_message_gid_uid 10 11 -> on group_message; 12 13 Query OK, 96 rows affected (0.02 sec) 14 15 Records: 96 Duplicates: 0 Warnings: 0然后再看如下 Query 的執(zhí)行計劃： 1 sky@localhost : example 09:26:15> EXPLAIN 2 3 -> SELECT user_id,max(gmt_create) 4 5 -> FROM group_message 6 7 -> WHERE group_id < 10 8 9 -> GROUP BY group_id,user_idG 10 11 *************************** 1. row *************************** 12 13 id: 1 14 15 select_type: SIMPLE 16 17 table: group_message 18 19 type: range 20 21 possible_keys: idx_gid_uid_gc 22 23 key: idx_gid_uid_gc 24 25 key_len: 8 26 27 ref: NULL 28 29 rows: 4 30 31 Extra: Using where; Using index for group-by 32 33 1 row in set (0.00 sec) 我們看到在執(zhí)行計劃的 Extra 信息中有信息顯示?Using index for group-by?，實際上這就是告訴我們，MySQL Query Optimizer 通過使用松散索引掃描來實現(xiàn)了我們所需要的 GROUP BY 操作。 下面這張圖片描繪了掃描過程的大概實現(xiàn)：  要利用到松散索引掃描實現(xiàn) GROUP BY，需要至少滿足以下幾個條件： ?GROUP BY 條件字段必須在同一個索引中最前面的連續(xù)位臵; ?在使用GROUP BY 的同時，只能使用 MAX 和 MIN 這兩個聚合函數(shù); ?如果引用到了該索引中 GROUP BY 條件之外的字段條件的時候，必須以常量形式存在; 為什么松散索引掃描的效率會很高? 因為在沒有WHERE子句，也就是必須經(jīng)過全索引掃描的時候， 松散索引掃描需要讀取的鍵值數(shù)量與分組的組數(shù)量一樣多，也就是說比實際存在的鍵值數(shù)目要少很多。而在WHERE子句包含范圍判斷式或者等值表達式的時候， 松散索引掃描查找滿足范圍條件的每個組的第1個關鍵字，并且再次讀取盡可能最少數(shù)量的關鍵字。 2.使用緊湊(Tight)索引掃描實現(xiàn) GROUP BY 緊湊索引掃描實現(xiàn) GROUP BY 和松散索引掃描的區(qū)別主要在于他需要在掃描索引的時候，讀取所有滿足條件的索引鍵，然后再根據(jù)讀取惡的數(shù)據(jù)來完成 GROUP BY 操作得到相應結果。 1 sky@localhost : example 08:55:14> EXPLAIN 2 3 -> SELECT max(gmt_create) 4 5 -> FROM group_message 6 7 -> WHERE group_id = 2 8 9 -> GROUP BY user_idG 10 11 *************************** 1. row *************************** 12 13 id: 1 14 15 select_type: SIMPLE 16 17 table: group_message 18 19 type: ref 20 21 possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc 22 23 key: idx_gid_uid_gc 24 25 key_len: 4 26 27 ref: const 28 29 rows: 4 30 31 Extra: Using where; Using index 32 33 1 row in set (0.01 sec) 這時候的執(zhí)行計劃的 Extra 信息中已經(jīng)沒有?Using index for group-by?了，但并不是說 MySQL 的 GROUP BY 操作并不是通過索引完成的，只不過是需要訪問 WHERE 條件所限定的所有索引鍵信息之后才能得出結果。這就是通過緊湊索引掃描來實現(xiàn) GROUP BY 的執(zhí)行計劃輸出信息。 下面這張圖片展示了大概的整個執(zhí)行過程：  在 MySQL 中，MySQL Query Optimizer 首先會選擇嘗試通過松散索引掃描來實現(xiàn) GROUP BY 操作，當發(fā)現(xiàn)某些情況無法滿足松散索引掃描實現(xiàn) GROUP BY 的要求之后，才會嘗試通過緊湊索引掃描來實現(xiàn)。 當 GROUP BY 條件字段并不連續(xù)或者不是索引前綴部分的時候，MySQL Query Optimizer 無法使用松散索引掃描，設臵無法直接通過索引完成 GROUP BY 操作，因為缺失的索引鍵信息無法得到。但是，如果 Query 語句中存在一個常量值來引用缺失的索引鍵，則可以使用緊湊索引掃描完成 GROUP BY 操作，因為常量填充了搜索關鍵字中的?差距?，可以形成完整的索引前綴。這些索引前綴可以用于索引查找。而如果需要排序GROUP BY結果，并且能夠形成索引前綴的搜索關鍵字，MySQL還可以避免額外的排序操作，因為使用有順序的索引的前綴進行搜索已經(jīng)按順序檢索到了所有關鍵字。 3.使用臨時表實現(xiàn) GROUP BY MySQL 在進行 GROUP BY 操作的時候要想利用所有，必須滿足 GROUP BY 的字段必須同時存放于同一個索引中，且該索引是一個有序索引(如 Hash 索引就不能滿足要求)。而且，并不只是如此，是否能夠利用索引來實現(xiàn) GROUP BY 還與使用的聚合函數(shù)也有關系。 前面兩種 GROUP BY 的實現(xiàn)方式都是在有可以利用的索引的時候使用的，當 MySQL Query Optimizer 無法找到合適的索引可以利用的時候，就不得不先讀取需要的數(shù)據(jù)，然后通過臨時表來完成 GROUP BY 操作。 1 sky@localhost : example 09:02:40> EXPLAIN 2 3 -> SELECT max(gmt_create) 4 5 -> FROM group_message 6 7 -> WHERE group_id > 1 and group_id < 10 8 9 -> GROUP BY user_idG 10 11 *************************** 1. row *************************** 12 13 id: 1 14 15 select_type: SIMPLE 16 17 table: group_message 18 19 type: range 20 21 possible_keys: idx_group_message_gid_uid,idx_gid_uid_gc 22 23 key: idx_gid_uid_gc 24 25 key_len: 4 26 27 ref: NULL 28 29 rows: 32 30 31 Extra: Using where; Using index; Using temporary; Using filesort 這次的執(zhí)行計劃非常明顯的告訴我們 MySQL 通過索引找到了我們需要的數(shù)據(jù)，然后創(chuàng)建了臨時表，又進行了排序操作，才得到我們需要的 GROUP BY 結果。整個執(zhí)行過程大概如下圖所展示： 當 MySQL Query Optimizer 發(fā)現(xiàn)僅僅通過索引掃描并不能直接得到 GROUP BY 的結果之后，他就不得不選擇通過使用臨時表然后再排序的方式來實現(xiàn) GROUP BY了。 在這樣示例中即是這樣的情況。 group_id 并不是一個常量條件，而是一個范圍，而且 GROUP BY 字段為 user_id。所以 MySQL 無法根據(jù)索引的順序來幫助 GROUP BY 的實現(xiàn)，只能先通過索引范圍掃描得到需要的數(shù)據(jù)，然后將數(shù)據(jù)存入臨時表，然后再進行排序和分組操作來完成 GROUP BY。 下面將通過實例分析兩種排序實現(xiàn)方式及實現(xiàn)圖解： 假設有 Table A 和 B 兩個表結構分別如下： 1 sky@localhost : example 01:48:2 1> show create table AG 2 3 *************************** 1. row *************************** 4 5 Table: A 6 7 Create Table: CREATE TABLE `A` ( 8 9 `c1` int(11) NOT NULL default '0', 10 11 `c2` char(2) default NULL, 12 13 `c3` varchar(16) default NULL, 14 15 `c4` datetime default NULL, 16 17 PRIMARY KEY (`c1`) 18 19 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 20 21 sky@localhost : example 01:48:32> show create table BG 22 23 *************************** 1. row *************************** 24 25 Table: B 26 27 Create Table: CREATE TABLE `B` ( 28 29 `c1` int(11) NOT NULL default '0', 30 31 `c2` char(2) default NULL, 32 33 `c3` varchar(16) default NULL, 34 35 PRIMARY KEY (`c1`), 36 37 KEY `B_c2_ind` (`c2`) 38 39 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1、利用有序索引進行排序，實際上就是當我們Query 的ORDER BY 條件和Query 的執(zhí)行計劃中所利用的Index的索引鍵(或前面幾個索引鍵)完全一致，且索引訪問方式為rang、ref 或者index的時候，MySQL可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)。這種方式的ORDER BY 基本上可以說是最優(yōu)的排序方式了，因為MySQL不需要進行實際的排序操作。 假設我們在Table A和B上執(zhí)行如下SQL： 1 sky@localhost : example 01:44:28> EXPLAIN SELECT A.* FROM A,B 2 3 -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c1G 4 5 *************************** 1. row *************************** 6 7 id: 1 8 9 select_type: SIMPLE 10 11 table: A 12 13 type: range 14 15 possible_keys: PRIMARY 16 17 key: PRIMARY 18 19 key_len: 4 20 21 ref: NULL 22 23 rows: 3 24 25 Extra: Using where 26 27 *************************** 2. row *************************** 28 29 id: 1 30 31 select_type: SIMPLE 32 33 table: B 34 35 type: ref 36 37 possible_keys: B_c2_ind 38 39 key: B_c2_ind 40 41 key_len: 7 42 43 ref: example.A.c2 44 45 rows: 2 46 47 Extra: Using where; Using index 我們通過執(zhí)行計劃可以看出，MySQL實際上并沒有進行實際的排序操作，實際上其整個執(zhí)行過程如下圖所示： 2、通過相應的排序算法，將取得的數(shù)據(jù)在內存中進行排序方式，MySQL 比需要將數(shù)據(jù)在內存中進行排序，所使用的內存區(qū)域也就是我們通過ort_buffer_size 系統(tǒng)變量所設臵的排序區(qū)。這個排序區(qū)是每個Thread 獨享的，所以說可能在同一時刻在MySQL 中可能存在多個 sort buffer 內存區(qū)域。 第二種方式在MySQL Query Optimizer 所給出的執(zhí)行計劃(通過 EXPLAIN 命令查看)中被稱為filesort。在這種方式中，主要是由于沒有可以利用的有序索引取得有序的數(shù)據(jù)，MySQL只能通過將取得的數(shù)據(jù)在內存中進行排序然后再將數(shù)據(jù)返回給客戶端。在MySQL中filesort 的實現(xiàn)算法實際上是有兩種的，一種是首先根據(jù)相應的條件取出相應的排序字段和可以直接定位行數(shù)據(jù)的行指針信息，然后在sort buffer 中進行排序。另外一種是一次性取出滿足條件行的所有字段，然后在sort buffer中進行排序。 在MySQL4.1版本之前只有第一種排序算法，第二種算法是從MySQL4.1開始的改進算法，主要目的是為了減少第一次算法中需要兩次訪問表數(shù)據(jù)的 IO 操作，將兩次變成了一次，但相應也會耗用更多的sort buffer 空間。當然，MySQL4.1開始的以后所有版本同時也支持第一種算法，MySQL主要通過比較我們所設定的系統(tǒng)參數(shù) max_length_for_sort_data的大小和Query 語句所取出的字段類型大小總和來判定需要使用哪一種排序算法。如果 max_length_for_sort_data更大，則使用第二種優(yōu)化后的算法，反之使用第一種算法。所以如果希望 ORDER BY 操作的效率盡可能的高，一定要主義max_length_for_sort_data 參數(shù)的設臵。曾經(jīng)就有同事的數(shù)據(jù)庫出現(xiàn)大量的排序等待，造成系統(tǒng)負載很高，而且響應時間變得很長，最后查出正是因為MySQL 使用了傳統(tǒng)的第一種排序算法而導致，在加大了max_length_for_sort_data 參數(shù)值之后，系統(tǒng)負載馬上得到了大的緩解，響應也快了很多。 我們再看看 MySQL 需要使用filesort 實現(xiàn)排序的實例。 假設我們改變一下我們的Query，換成通過A.c2來排序，再看看情況： 1 sky@localhost : example 01:54:23> EXPLAIN SELECT A.* FROM A,B 2 3 -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c2G 4 5 *************************** 1. row *************************** 6 7 id: 1 8 9 select_type: SIMPLE 10 11 table: A 12 13 type: range 14 15 possible_keys: PRIMARY 16 17 key: PRIMARY 18 19 key_len: 4 20 21 ref: NULL 22 23 rows: 3 24 25 Extra: Using where; Using filesort 26 27 *************************** 2. row *************************** 28 29 id: 1 30 31 select_type: SIMPLE 32 33 table: B 34 35 type: ref 36 37 possible_keys: B_c2_ind 38 39 key: B_c2_ind 40 41 key_len: 7 42 43 ref: example.A.c2 44 45 rows: 2 46 47 Extra: Using where; Using index MySQL 從 Table A 中取出了符合條件的數(shù)據(jù)，由于取得的數(shù)據(jù)并不滿足ORDER BY 條件，所以MySQL進行了 filesort 操作，其整個執(zhí)行過程如下圖所示：  在MySQL 中，filesort 操作還有一個比較奇怪的限制，那就是其數(shù)據(jù)源必須是來源于一個Table，所以，如果我們的排序數(shù)據(jù)如果是兩個(或者更多個) Table 通過Join所得出的，那么 MySQL 必須通過先創(chuàng)建一個臨時表(Temporary Table)，然后再將此臨時表的數(shù)據(jù)進行排序，如下例所示： 1 sky@localhost : example 02:46:15> explain select A.* from A,B 2 3 -> where A.c1 > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3G 4 5 *************************** 1. row *************************** 6 7 id: 1 8 9 select_type: SIMPLE 10 11 table: A 12 13 type: range 14 15 possible_keys: PRIMARY 16 17 key: PRIMARY 18 19 key_len: 4 20 21 ref: NULL 22 23 rows: 3 24 25 Extra: Using where; Using temporary; Using filesort 26 27 *************************** 2. row *************************** 28 29 id: 1 30 31 select_type: SIMPLE 32 33 table: B 34 35 type: ref 36 37 possible_keys: B_c2_ind 38 39 key: B_c2_ind 40 41 key_len: 7 42 43 ref: example.A.c2 44 45 rows: 2 46 47 Extra: Using where 這個執(zhí)行計劃的輸出還是有點奇怪的，不知道為什么，MySQL Query Optimizer 將 ?Using temporary? 過程顯示在第一行對Table A 的操作中，難道只是為讓執(zhí)行計劃的輸出少一行? 實際執(zhí)行過程應該是如下圖所示：  備份是最簡單的保護數(shù)據(jù)的方法，本節(jié)將介紹多種備份方法。為了得到一個一致的備份，在相關的表上做一個LOCK TABLES，你只需一個讀鎖定，當你在數(shù)據(jù)庫目錄中做文件的一個拷貝時，這允許其他線程繼續(xù)查詢該表；當你恢復數(shù)據(jù)時，需要一個寫鎖定，以避免沖突。 使用SQL語句備份和恢復 你可以使用SELECT INTO OUTFILE語句備份數(shù)據(jù)，并用LOAD DATA INFILE語句恢復數(shù)據(jù)。這種方法只能導出數(shù)據(jù)的內容，不包括表的結構，如果表的結構文件損壞，你必須要先恢復原來的表的結構。 語法： SELECT * INTO {OUTFILE | DUMPFILE} 'file_name' FROM tbl_name LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name SELECT ... INTO OUTFILE 'file_name'格式的SELECT語句將選擇的行寫入一個文件。文件在服務器主機上被創(chuàng)建，并且不能是已經(jīng)存在的(不管別的，這可阻止數(shù)據(jù)庫表和文件例如?/etc/passwd?被破壞)。SELECT ... INTO OUTFILE是LOAD DATA INFILE逆操作。 LOAD DATA INFILE語句從一個文本文件中以很高的速度讀入一個表中。如果指定LOCAL關鍵詞，從客戶主機讀文件。如果LOCAL沒指定，文件必須位于服務器上。(LOCAL在MySQL3.22.6或以后版本中可用。) 為了安全原因，當讀取位于服務器上的文本文件時，文件必須處于數(shù)據(jù)庫目錄或可被所有人讀取。另外，為了對服務器上文件使用LOAD DATA INFILE，在服務器主機上你必須有file的權限。使用這種SELECT INTO OUTFILE語句，在服務器主機上你必須有FILE權限。 為了避免重復記錄，在表中你需要一個PRIMARY KEY或UNIQUE索引。當在唯一索引值上一個新記錄與一個老記錄重復時，REPLACE關鍵詞使得老記錄用一個新記錄替代。如果你指定IGNORE，跳過有唯一索引的現(xiàn)有行的重復行的輸入。如果你不指定任何一個選項，當找到重復索引值時，出現(xiàn)一個錯誤，并且文本文件的余下部分被忽略時。 如果你指定關鍵詞LOW_PRIORITY，LOAD DATA語句的執(zhí)行被推遲到?jīng)]有其他客戶讀取表后。 使用LOCAL將比讓服務器直接存取文件慢些，因為文件的內容必須從客戶主機傳送到服務器主機。在另一方面，你不需要file權限裝載本地文件。如果你使用LOCAL關鍵詞從一個本地文件裝載數(shù)據(jù)，服務器沒有辦法在操作的當中停止文件的傳輸，因此缺省的行為好像IGNORE被指定一樣。 當在服務器主機上尋找文件時，服務器使用下列規(guī)則： ?如果給出一個絕對路徑名，服務器使用該路徑名。 ?如果給出一個有一個或多個前臵部件的相對路徑名，服務器相對服務器的數(shù)據(jù)目錄搜索文件。 ?如果給出一個沒有前臵部件的一個文件名，服務器在當前數(shù)據(jù)庫的數(shù)據(jù)庫目錄尋找文件。 假定表tbl_name具有一個PRIMARY KEY或UNIQUE索引，備份一個數(shù)據(jù)表的過程如下： 1、鎖定數(shù)據(jù)表，避免在備份過程中，表被更新 mysql>LOCK TABLES READ tbl_name; 2、導出數(shù)據(jù) mysql>SELECT * INTO OUTFILE ‘tbl_name.bak’ FROM tbl_name; 3、解鎖表 mysql>UNLOCK TABLES; 相應的恢復備份的數(shù)據(jù)的過程如下： 1、為表增加一個寫鎖定： mysql>LOCK TABLES tbl_name WRITE; 2、恢復數(shù)據(jù) mysql>LOAD DATA INFILE ‘tbl_name.bak’ ->REPLACE INTO TABLE tbl_name; 如果，你指定一個LOW_PRIORITY關鍵字，就不必如上要對表鎖定，因為數(shù)據(jù)的導入將被推遲到?jīng)]有客戶讀表為止： mysql>LOAD DATA LOW_PRIORITY INFILE ‘tbl_name’ ->REPLACE INTO TABLE tbl_name; 3、解鎖表 使用mysqlimport恢復數(shù)據(jù) 如果你僅僅恢復數(shù)據(jù)，那么完全沒有必要在客戶機中執(zhí)行SQL語句，因為你可以簡單的使用mysqlimport程序，它完全是與LOAD DATA 語句對應的，由發(fā)送一個LOAD DATA INFILE命令到服務器來運作。執(zhí)行命令mysqlimport --help，仔細查看輸出，你可以從這里得到幫助。 shell> mysqlimport [options] db_name filename ... 對于在命令行上命名的每個文本文件，mysqlimport剝去文件名的擴展名并且使用它決定哪個表導入文件的內容。例如，名為?patient.txt?、 ?patient.text?和?patient?將全部被導入名為patient的一個表中。 常用的選項為： -C, --compress 如果客戶和服務器均支持壓縮，壓縮兩者之間的所有信息。 -d, --delete 在導入文本文件前倒空表格。 l, --lock-tables 在處理任何文本文件前為寫入所定所有的表。這保證所有的表在服務器上被同步。 --low-priority，--local，--replace，--ignore分別對應LOAD DATA語句的LOW_PRIORITY，LOCAL，REPLACE，IGNORE關鍵字。 例如恢復數(shù)據(jù)庫db1中表tbl1的數(shù)據(jù)，保存數(shù)據(jù)的文件為tbl1.bak，假定你在服務器主機上： shell>mysqlimport --lock-tables --replace db1 tbl1.bak 這樣在恢復數(shù)據(jù)之前現(xiàn)對表鎖定，也可以利用--low-priority選項： shell>mysqlimport --low-priority --replace db1 tbl1.bak 如果你為遠程的服務器恢復數(shù)據(jù)，還可以這樣： shell>mysqlimport -C --lock-tables --replace db1 tbl1.bak 當然，解壓縮要消耗CPU時間。 象其它客戶機一樣，你可能需要提供-u，-p選項以通過身分驗證，也可以在選項文件my.cnf中存儲這些參數(shù)，具體方法和其它客戶機一樣，這里就不詳述了。 mysql->UNLOCAK TABLES; 用mysqldump備份數(shù)據(jù) 同mysqlimport一樣，也存在一個工具mysqldump備份數(shù)據(jù)，但是它比SQL語句多做的工作是可以在導出的文件中包括SQL語句，因此可以備份數(shù)據(jù)庫表的結構，而且可以備份一個數(shù)據(jù)庫，甚至整個數(shù)據(jù)庫系統(tǒng)。 mysqldump [OPTIONS] database [tables] mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] mysqldump [OPTIONS] --all-databases [OPTIONS] 如果你不給定任何表，整個數(shù)據(jù)庫將被傾倒。 通過執(zhí)行mysqldump --help，你能得到你mysqldump的版本支持的選項表。 1、備份數(shù)據(jù)庫的方法 例如，假定你在服務器主機上備份數(shù)據(jù)庫db_name shell> mydqldump db_name 當然，由于mysqldump缺省時把輸出定位到標準輸出，你需要重定向標準輸出。 例如，把數(shù)據(jù)庫備份到bd_name.bak中： shell> mydqldump db_name>db_name.bak 你可以備份多個數(shù)據(jù)庫，注意這種方法將不能指定數(shù)據(jù)表： shell> mydqldump --databases db1 db1>db.bak 你也可以備份整個數(shù)據(jù)庫系統(tǒng)的拷貝，不過對于一個龐大的系統(tǒng)，這樣做沒有什么實際的價值： shell> mydqldump --all-databases>db.bak 雖然用mysqldump導出表的結構很有用，但是恢復大量數(shù)據(jù)時，眾多SQL語句使恢復的效率降低。你可以通過使用--tab選項，分開數(shù)據(jù)和創(chuàng)建表的SQL語句。 -T，--tab= 在選項指定的目錄里，創(chuàng)建用制表符（tab）分隔列值的數(shù)據(jù)文件和包含創(chuàng)建表結構的SQL語句的文件，分別用擴展名.txt和.sql表示。該選項不能與--databases或--all-databases同時使用，并且mysqldump必須運行在服務器主機上。 例如，假設數(shù)據(jù)庫db包括表tbl1，tbl2，你準備備份它們到/var/mysqldb shell>mysqldump --tab=/var/mysqldb/ db 其效果是在目錄/var/mysqldb中生成4個文件，分別是tbl1.txt、tbl1.sql、tbl2.txt和tbl2.sql。 2、mysqldump實用程序時的身份驗證的問題同其他客戶機一樣，你也必須提供一個MySQL數(shù)據(jù)庫帳號用來導出數(shù)據(jù)庫，如果你不是使用匿名用戶的話，可能需要手工提供參數(shù)或者使用選項文件： 如果這樣： shell>mysql -u root –pmypass db_name>db_name.sql 或者這樣在選項文件中提供參數(shù)： [mysqldump] user=root password=mypass 然后執(zhí)行 shell>mysqldump db_name>db_name.sql 那么一切順利，不會有任何問題，但要注意命令歷史會泄漏密碼，或者不能讓任何除你之外的用戶能夠訪問選項文件，由于數(shù)據(jù)庫服務器也需要這個選項文件時，選項文件只能被啟動服務器的用戶（如，mysql）擁有和訪問，以免泄密。在Unix下你還有一個解決辦法，可以在自己的用戶目錄中提供個人選項文件（~/.my.cnf）,例如，/home/some_user/.my.cnf，然后把上面的內容加入文件中，注意防止泄密。在NT系統(tǒng)中，你可以簡單的讓c:my.cnf能被指定的用戶訪問。 你可能要問，為什么這么麻煩呢，例如，這樣使用命令行： shell>mysql -u root –p db_name>db_name.sql 或者在選項文件中加入 [mysqldump] user=root password 然后執(zhí)行命令行： shell>mysql db_name>db_name.sql 你發(fā)現(xiàn)了什么往常熟悉的Enter password:提示并沒有出現(xiàn)，因為標準輸出被重定向到文件db_name.sql中了，所以看不到往常的提示符，程序在等待你輸入密碼。在重定向的情況下，再使用交互模式，就會有問題。在上面的情況下，你還可以直接輸入密碼。 然后在文件db_name.sql文件的第一行看到: Enter password:#…….. 你可能說問題不大，但是mysqldump之所以把結果輸出到標準輸出，是為了重定向到其它程序的標準輸入，這樣有利于編寫腳本。例如： 用來自于一個數(shù)據(jù)庫的信息充實另外一個MySQL數(shù)據(jù)庫也是有用的： shell>mysqldump --opt database | mysql --host=remote-host -C database 如果mysqldump仍運行在提示輸入密碼的交互模式下，該命令不會成功，但是如果mysql是否運行在提示輸入密碼的交互模式下，都是可以的。 如果在選項文件中的[client]或者[mysqldump]任何一段中指定了password選項，且不提供密碼，即使，在另一段中有提供密碼的選項password=mypass,例如 [client] user=root password [mysqldump] user=admin password=mypass 那么mysqldump一定要你輸入admin用戶的密碼： mysql>mysqldump db_name 即使是這樣使用命令行： mysql>mysqldump –u root –ppass1 db 也是這樣，不過要如果-u指定的用戶的密碼。 其它使用選項文件的客戶程序也是這樣 3、有關生成SQL語句的優(yōu)化控制 --add-locks 生成的SQL 語句中，在每個表數(shù)據(jù)恢復之前增加LOCK TABLES并且之后UNLOCK TABLE。(為了使得更快地插入到MySQL)。 --add-drop-table 生成的SQL 語句中，在每個create語句之前增加一個drop table。 -e, --extended-insert 使用全新多行INSERT語法。（給出更緊縮并且更快的插入語句） 下面兩個選項能夠加快備份表的速度： -l, --lock-tables. 為開始導出數(shù)據(jù)前，讀鎖定所有涉及的表。 -q, --quick 不緩沖查詢，直接傾倒至stdout。 理論上，備份時你應該指定上訴所有選項。這樣會使命令行過于復雜，作為代替，你可以簡單的指定一個--opt選項，它會使上述所有選項有效。 例如，你將導出一個很大的數(shù)據(jù)庫： shell> mysqldump --opt db_name > db_name.txt 當然，使用--tab選項時，由于不生成恢復數(shù)據(jù)的SQL語句，使用--opt時，只會加快數(shù)據(jù)導出。 4、恢復mysqldump備份的數(shù)據(jù) 由于備份文件是SQL語句的集合，所以需要在批處理模式下使用客戶機 如果你使用mysqldump備份單個數(shù)據(jù)庫或表，即： shell>mysqldump --opt db_name > db_name.sql 由于db_name.sql中不包括創(chuàng)建數(shù)據(jù)庫或者選取數(shù)據(jù)庫的語句，你需要指定數(shù)據(jù)庫 shell>mysql db2 < db_name.sql 如果，你使用--databases或者--all-databases選項，由于導出文件中已經(jīng)包含創(chuàng)建和選用數(shù)據(jù)庫的語句，可以直接使用，不比指定數(shù)據(jù)庫，例如： shell>mysqldump --databases db_name > db_name.sql shell>mysql 如果你使用--tab選項備份數(shù)據(jù)，數(shù)據(jù)恢復可能效率會高些 例如，備份數(shù)據(jù)庫db_name后在恢復： shell>mysqldump --tab=/path/to/dir --opt test 如果要恢復表的結構，可以這樣： shell>mysql < /path/to/dir/tbl1.sql 如果要恢復數(shù)據(jù)，可以這樣 shell>mysqlimport -l db /path/to/dir/tbl1.txt 如果是在Unix平臺下使用（推薦），就更方便了： shell>ls -l *.sql | mysql db shell>mysqlimport --lock-tables db /path/to/dir/*.txt 用直接拷貝的方法備份恢復 根據(jù)本章前兩節(jié)的介紹，由于MySQL的數(shù)據(jù)庫和表是直接通過目錄和表文件實現(xiàn)的，因此直接復制文件來備份數(shù)據(jù)庫數(shù)據(jù)，對MySQL來說特別方便。而且自MySQL 3.23起MyISAM表成為缺省的表的類型，這種表可以為在不同的硬件體系中共享數(shù)據(jù)提供了保證。 使用直接拷貝的方法備份時，尤其要注意表沒有被使用，你應該首先對表進行讀鎖定。 備份一個表，需要三個文件： 對于MyISAM表： tbl_name.frm 表的描述文件 tbl_name.MYD 表的數(shù)據(jù)文件 tbl_name.MYI 表的索引文件 對于ISAM表： tbl_name.frm 表的描述文件 tbl_name.ISD 表的數(shù)據(jù)文件 tbl_name.ISM 表的索引文件 你直接拷貝文件從一個數(shù)據(jù)庫服務器到另一個服務器，對于MyISAM表，你可以從運行在不同硬件系統(tǒng)的服務器之間復制文件，例如，SUN服務器和INTEL PC機之間。 總結 本節(jié)介紹了備份恢復數(shù)據(jù)庫的多種方法，讀者可以根據(jù)需要選用，對于文中涉及到的SQL語句、工具主要有： 1、SELECT…INTO OUTFILE和LOAD DATA INFILE 2、mysqldump 3、mysqlimport 對于這些內容，讀者需要注重掌握的是mysqldump實用程序的使用，以及mysql批處理模式運行包含SQL語句的文件的方法。這在備份和恢復數(shù)據(jù)庫表中非常常用。另外要注意的是直接拷貝的方法。
信息發(fā)布：廣州名易軟件有限公司 http://www.jetlc.com
|
名易MyOA協(xié)同綜合辦公平臺
名易MyCRM客戶關系管理平臺
名易MyHR人力資源管理平臺
名易MyIDP智能開發(fā)平臺
名易MyIBP保險業(yè)務管理平臺
名易MyHMS酒店綜合管理系統(tǒng)
名易MyPM項目管理平臺
名易MyTMS物流運輸管理平臺
名易MyVMS汽車綜合管理系統(tǒng)
名易MyIMS貸款管理系統(tǒng)
名易MyEDU教育管理平臺
名易MyPCS生產(chǎn)事務協(xié)調系統(tǒng)
名易MyWIDP微信智能開發(fā)平臺
