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

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

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

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

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

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


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

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

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

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

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

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