3.8 解決方案隨筆
    本節(jié)內(nèi)容相當(dāng)雜；介紹了怎樣編寫解決各種問題的查詢。多數(shù)內(nèi)容是在郵件清單上看到的解決問題的方案（謝謝清單上的那些朋友，他們?yōu)榻鉀Q方案作了很多工作）。
    3.8.1 將子選擇編寫為連接
    MySQL自3.24版本以來才具有子選擇功能。這項功能的缺少是MySQL 中一件常常令人惋惜的事，但有一件事很多人似乎沒有認(rèn)識到，那就是用子選擇編寫的查詢通常可以用連接來編寫。實事上，即使MySQL 具有了子查詢，檢查用子選擇編寫的查詢也是一件苦差事；用連接而不是用子選擇來編寫會更為有效。
    1. 重新編寫選擇匹配值的子選擇
    下面是一個包含一個子選擇查詢的樣例，它從score 表中選擇所有測試的學(xué)分（即，忽略測驗的學(xué)分）：

    可通過將其轉(zhuǎn)換為一個簡單的連接，不用子選擇也可以編寫出相同的查詢，如下所示：

    下面的例子為選擇女學(xué)生的學(xué)分：

    可將其轉(zhuǎn)換為連接，如下所示：

    這里是一個模式，子選擇查詢?nèi)缦滦问剑?br>
    這樣的查詢可轉(zhuǎn)換為如下形式的連接：

    2. 重新編寫選擇非匹配值的子選擇查詢
    另一種常用的子選擇查詢是查找一個表中有的而另一個表中沒有的值。正如以前所看到的那樣，“那些未給出的值”這一類的問題是LEFT JOIN 可能有用的一個線索。下面的查詢包含一個子選擇（它尋找那些全勤的學(xué)生）：


    3.8.2 檢查表中未給出的值
    我們已經(jīng)在3 . 6節(jié)“檢索記錄”中看到，在要想知道一個表中哪些值不出現(xiàn)在另一表中時，可對兩個表使用LEFT JOIN 并查找那些從第二個表中選中NULL 的行。并用下列兩個表舉例：

    現(xiàn)在讓我們來考慮一種更為困難的情況，“缺了哪些值”。對于第1 章中提到的學(xué)分保存方案中，有一個列出學(xué)生的student 表，一個列出已經(jīng)出現(xiàn)過的學(xué)分事件的event 表，以及列出每個學(xué)生的每次學(xué)分事件學(xué)分的一個score 表。但是，如果一個學(xué)生在某個測試或測驗的同一天病了，那么score 表中將不會有這個學(xué)生的該事件的學(xué)分，因此，要進(jìn)行測驗或測試的補考。我們怎樣查找這些缺少了的記錄，以便能保證讓這些學(xué)生進(jìn)行補考？問題是要對所有的學(xué)分事件確定哪些學(xué)生沒有某個學(xué)分事件的學(xué)分。換個說法，就是我們希望知道學(xué)生和事件的哪些組合不出現(xiàn)在學(xué)分表中。這就是我們希望LEFT JOIN 所做的事。這個連接不像前例中那樣簡單，因為我們不僅僅要查找不出現(xiàn)在單列中的值；還需要查找兩列的組合。
    我們想要的這種組合是所有學(xué)生/事件的組合，它們由student 表與event 表的叉積產(chǎn)生：
    FROM student, event
  　然后我們?nèi)〕龃诉B接的結(jié)果，與score 表執(zhí)行一個LEFT JOIN 語句找出匹配者：
    FROM student, event
    LEFT JOIN score ON student.student_id = score.student.id
                    AND event.event_id = score.event_id
    請注意，ON 子句使得score 表中的行根據(jù)不同表中的匹配者進(jìn)行連接。這是解決本問題的關(guān)鍵。LEFT JOIN 強(qiáng)制為由student 和event 表的叉連接生成的每行產(chǎn)生一個行，即使沒有相應(yīng)的score 表記錄也是這樣。這些缺少的學(xué)分記錄的結(jié)果行可通過一個事實來識別，就是來自score 表的列將全是NULL 的。我們可在WHERE 子句中選出這些記錄。來自score 表的任何列都是這樣，但因為我們查找的是缺少的學(xué)分，測試score 列從概念上可能最為清晰：
    WHERE score.score IS NULL
    可利用ORDER BY 子句對結(jié)果進(jìn)行排序。兩種最合理的排序分別是按學(xué)生和按事件進(jìn)行，我們選擇第一種：
    ORDER BY student.student_id, event.event_id
    現(xiàn)在需要做的就是命名我們希望在輸出結(jié)果中看到的列。最終的查詢?nèi)缦拢?br>    SELECT
        student.name, student.student_id,
        event.date, event,event_id, event.type
    FROM
        student,event
        LEFT JOIN score ON student.student_id = score.student_id
                        AND event.event_id = score.event_id
    WHERE
        score.score IS NULL
    ORDER BY
        student.student_id, event.event_id
    運行此查詢得出如下結(jié)果：

    這里有一個問題要引起注意。此輸出列出了學(xué)生的ID 和事件的I D。student_id 列出現(xiàn)在student 和score 表中，因此，開始您可能會認(rèn)為選擇列表可以給出student.student_id 或score . student _ id。但實際不是這樣，因為能夠找到感興趣記錄的基礎(chǔ)是所有學(xué)分表字段返回N U L L。選擇score.student_id 將只在輸出中產(chǎn)生NULL 值的列。類似的推理可應(yīng)用到event_id 列，它也出現(xiàn)在event 和score 表中。
    3.8.3 執(zhí)行UNION 操作
    如果想通過從具有相同結(jié)構(gòu)的多個表中建立一個結(jié)果集，可在某些數(shù)據(jù)庫系統(tǒng)中使用某種UNION 語句來實現(xiàn)。MySQL 沒有UNION（至少直到3 . 2 4版還沒有），但有許多辦法來解決這個問題，下面是兩種可行的方案：
    ■ 執(zhí)行多個SELECT 查詢，每個表執(zhí)行一個。如果不關(guān)心所選出行的次序，這樣做就行了。
    ■ 將每個表中的行選入一個臨時存儲表，然后選擇該表的內(nèi)容。這樣可對行按所需的次序進(jìn)行排序。在MySQL 3.23版及以后的版本中，可通過允許服務(wù)器創(chuàng)建存儲表來解決這個問題。而且，還可以使該表為臨時表，以便在您與服務(wù)器的會話結(jié)束時，自動刪除該表。
    在下面的代碼中，我們明確地刪除該表使服務(wù)器釋放與其有關(guān)的資源。如果客戶機(jī)會話將繼續(xù)執(zhí)行進(jìn)一步的查詢，這樣做很有好處。為了取到更好的性能，還可以利用HEAP（在內(nèi)存中）表。

    對于3 . 2 3版本，除了必須自己明確定義hold_tbl 表中的列外，其想法是類似的，而且結(jié)尾處的DROP TABLE 是強(qiáng)制性的，用來防止在以下客戶機(jī)會話生命周期之后繼續(xù)存在：

    3.8.4 增加序列號列
    如果用A LTER TABLE 增加AUTO_INCREMENT 列，則該列用序列號自動地填充。下面這組mysql 會話中的語句示出了怎樣創(chuàng)建一個表，在其中存放數(shù)據(jù)，然后增加一個AUTO_INCREMENT 列：

　   3.8.5 對某個已有的列進(jìn)行排序
　   如果有一個數(shù)值列，可對其按如下進(jìn)行排序（或?qū)ζ渲嘏判?，如果已對其排過序，但刪除了行并且想要對值重新排序使其連續(xù)）：
　   ALTER TABLE t MODIFY i INT NULL
　   UPDATE t SET i = NULL
　   ALTER TABLE t MODIFY i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
　   但是有一種更容易的方法，那就是刪除該列，然后再作為一個A U TO_INCREMENT 列追加它。A LTER TABLE 允許指定多個活動，因此，上述工作可在單個語句中完成：
    ALTER TABLE t
    DROP i,
    ADD i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
    3.8.6 非正常次序的串
    假如有一個表示體育機(jī)構(gòu)人員的表，如橄欖球隊，如果按人員職位進(jìn)行排序，以便以特殊的順序表示它，如：教練、教練助理、四分衛(wèi)、流動后衛(wèi)、接球員、巡邏員等。可將列定義為ENUM 并按希望出現(xiàn)的順序定義枚舉元素。對該列的排序?qū)运付ǖ捻樞蜃詣舆M(jìn)行。
    3.8.7 建立計數(shù)表
    在第2章的“使用序列”小節(jié)中，我們介紹了怎樣利用L A S T _ I N S E RT_ID(expr) 生成一個序列。那個例子說明了怎樣利用單列的表進(jìn)行計數(shù)。那樣做對于只需要單個計數(shù)器的情形能夠滿足需要，但是，如果需要幾個計數(shù)器，該方法將會引起不必要的表重復(fù)。假如有一個Web 站點并且想要在幾個頁面上放置“此頁面已經(jīng)被訪問nnn 次”這樣的計數(shù)器。那么為每個具有一個計數(shù)器的頁面建立一個單獨的表就有些多余了。避免創(chuàng)建多個計數(shù)器表的一種方法是建立一個兩列的表。其中一列存放計數(shù)值；另一列存放計數(shù)器名。這時仍然可以使用LAST _ INSERT_ID( ) 函數(shù)，但可用計數(shù)器名來決定用哪一行。這個表如下所示：
    CREATE TABLE counter
    (
    count INT UNSIGNED,
    name varchar(255) NOT NULL PRIMARY KEY
    )
    其中計數(shù)器名為一個串，從而可以調(diào)用任何想要的計數(shù)器，我們將其定義為PRIMARY KEY 以免名稱重復(fù)。這里假定使用這個表的應(yīng)用程序知道他們將使用的名稱。對于前面所說的Web 計數(shù)器，可通過利用文件樹中每個頁面的路徑名作為其計數(shù)器名的方法，保證計數(shù)器名的唯一性。例如，要為站點的主頁建立一個新計數(shù)器，可執(zhí)行下列語句：
    INSERT INTO counter(name) VALUES("index.html")
    它用零值初始化稱為“ index.html”的計數(shù)器。為了生成序列中的下一個值，增加表中相應(yīng)行的計數(shù)值，然后用LAST _ INSERT_ID( ) 檢索它：
    UPDATE counter
    SET count = LAST_INSERT_ID(count+1)
    WHERE name = "index.html"
    SELECT LAST_INSERT_ID()
    另一種方法是不用LAST _ INSERT_ID( ) 增加計數(shù)器的值，如下所示：
    UPDATE counter SET count = count+1 WHERE name = "index.html"
    SELECT count FROM counter WHERE name = "index.html"
    然而，如果另一個客戶在您發(fā)布U P D ATE 語句與SELECT 語句之間增加了該計數(shù)器的值，則這種方法工作不正常。不過可在此兩條語句的前后分別放置LOCK TABLES 和U N L O C KTABLES，在您使用該計數(shù)器時阻塞其他客戶，以解決上述問題。但用L A S T _ I N S E RT_ID( )方法完成同樣的工作更為容易一些。因為它的值是客戶專用的，您總能得到自己插入的值，而不是其他客戶插入的值，而且不必阻塞其他客戶使代碼復(fù)雜化。
    3.8.8 檢查表是否存在
    在應(yīng)用程序內(nèi)部知道一個表是否存在有時很有用。為了做到這一點，可使用下列任一條語句：
    SELECT COUNT(*) FROM tb1_name
    SELECT * FROM tb1_name WHERE 1=0
    如果指定的表存在，則上述兩條語句都將執(zhí)行成功，如果不存在，則都失敗。它們是這種測試的很好的查詢。它們執(zhí)行速度快，所以不會費太多的時間。這種方法最適合您自己編寫的應(yīng)用程序，因為您可以測試查詢的成功與失敗并采取相應(yīng)的措施。但在從mysql 運行的批量腳本中不特別有用，因為發(fā)生錯誤時除了終止運行外不可能做任何事（或者可以忽略相應(yīng)的錯誤，但是顯然無法再運行該查詢了）。