MYSQL解决方案随笔

    3.8 解决方案随笔
    本节内容相当杂；介绍了怎样编写解决各种问题的查询。多数内容是在邮件清单上看到的解决问题的方案（谢谢清单上的那些朋友，他们为解决方案作了很多工作）。
    3.8.1将子选择编写为连接
    MySQL自3.24版本以来才具有子选择功能。这项功能的缺少是MySQL中一件常常令人惋惜的事，但有一件事很多人似乎没有认识到，那就是用子选择编写的查询通常可以用连接来编写。实事上，即使MySQL具有了子查询，检查用子选择编写的查询也是一件苦差事；用连接而不是用子选择来编写会更为有效。
    1. 重新编写选择匹配值的子选择
    下面是一个包含一个子选择查询的样例，它从score 表中选择所有测试的学分（即，忽略测验的学分）：

    可通过将其转换为一个简单的连接，不用子选择也可以编写出相同的查询，如下所示：

    下面的例子为选择女学生的学分：

    可将其转换为连接，如下所示：

    这里是一个模式，子选择查询如下形式：

    这样的查询可转换为如下形式的连接：

    2. 重新编写选择非匹配值的子选择查询
    另一种常用的子选择查询是查找一个表中有的而另一个表中没有的值。正如以前所看到的那样，“那些未给出的值”这一类的问题是LEFT JOIN 可能有用的一个线索。下面的查询包含一个子选择（它寻找那些全勤的学生）：


    3.8.2 检查表中未给出的值
    我们已经在3 . 6节“检索记录”中看到，在要想知道一个表中哪些值不出现在另一表中时，可对两个表使用LEFT JOIN 并查找那些从第二个表中选中NULL 的行。并用下列两个表举例：

    现在让我们来考虑一种更为困难的情况，“缺了哪些值”。对于第1章中提到的学分保存方案中，有一个列出学生的student 表，一个列出已经出现过的学分事件的event 表，以及列出每个学生的每次学分事件学分的一个score 表。但是，如果一个学生在某个测试或测验的同一天病了，那么score 表中将不会有这个学生的该事件的学分，因此，要进行测验或测试的补考。我们怎样查找这些缺少了的记录，以便能保证让这些学生进行补考？问题是要对所有的学分事件确定哪些学生没有某个学分事件的学分。换个说法，就是我们希望知道学生和事件的哪些组合不出现在学分表中。这就是我们希望LEFT JOIN 所做的事。这个连接不像前例中那样简单，因为我们不仅仅要查找不出现在单列中的值；还需要查找两列的组合。
    我们想要的这种组合是所有学生/事件的组合，它们由student 表与event 表的叉积产生：
    FROM student, event
  　然后我们取出此连接的结果，与score 表执行一个LEFT JOIN 语句找出匹配者：
    FROM student, event
    LEFT JOIN score ON student.student_id = score.student.id
                    AND event.event_id = score.event_id
    请注意，ON 子句使得score 表中的行根据不同表中的匹配者进行连接。这是解决本问题的关键。LEFT JOIN 强制为由student 和event 表的叉连接生成的每行产生一个行，即使没有相应的score 表记录也是这样。这些缺少的学分记录的结果行可通过一个事实来识别，就是来自score 表的列将全是NULL 的。我们可在WHERE 子句中选出这些记录。来自score 表的任何列都是这样，但因为我们查找的是缺少的学分，测试score 列从概念上可能最为清晰：
    WHERE score.score IS NULL
    可利用ORDER BY 子句对结果进行排序。两种最合理的排序分别是按学生和按事件进行，我们选择第一种：
    ORDER BY student.student_id, event.event_id
    现在需要做的就是命名我们希望在输出结果中看到的列。最终的查询如下：
    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
    运行此查询得出如下结果：

    这里有一个问题要引起注意。此输出列出了学生的ID 和事件的ID。student_id 列出现在student 和score 表中，因此，开始您可能会认为选择列表可以给出student.student_id 或score . student _ id。但实际不是这样，因为能够找到感兴趣记录的基础是所有学分表字段返回NULL。选择score.student_id 将只在输出中产生NULL 值的列。类似的推理可应用到event_id 列，它也出现在event 和score 表中。
    3.8.3 执行UNION 操作
    如果想通过从具有相同结构的多个表中建立一个结果集，可在某些数据库系统中使用某种UNION 语句来实现。MySQL没有UNION（至少直到3 . 2 4版还没有），但有许多办法来解决这个问题，下面是两种可行的方案：
    ■ 执行多个SELECT 查询，每个表执行一个。如果不关心所选出行的次序，这样做就行了。
    ■ 将每个表中的行选入一个临时存储表，然后选择该表的内容。这样可对行按所需的次序进行排序。在MySQL3.23版及以后的版本中，可通过允许服务器创建存储表来解决这个问题。而且，还可以使该表为临时表，以便在您与服务器的会话结束时，自动删除该表。
    在下面的代码中，我们明确地删除该表使服务器释放与其有关的资源。如果客户机会话将继续执行进一步的查询，这样做很有好处。为了取到更好的性能，还可以利用HEAP（在内存中）表。

    对于3 . 2 3版本，除了必须自己明确定义hold_tbl 表中的列外，其想法是类似的，而且结尾处的DROP TABLE 是强制性的，用来防止在以下客户机会话生命周期之后继续存在：

    3.8.4 增加序列号列
    如果用ALTER TABLE 增加AUTO_INCREMENT 列，则该列用序列号自动地填充。下面这组mysql会话中的语句示出了怎样创建一个表，在其中存放数据，然后增加一个AUTO_INCREMENT 列：

　   3.8.5 对某个已有的列进行排序
　   如果有一个数值列，可对其按如下进行排序（或对其重排序，如果已对其排过序，但删除了行并且想要对值重新排序使其连续）：
　   ALTER TABLE t MODIFY i INT NULL
　   UPDATE t SET i = NULL
　   ALTER TABLE t MODIFY i INT UNSIGNEDAUTO_INCREMENT NOT NULL PRIMARY KEY
　   但是有一种更容易的方法，那就是删除该列，然后再作为一个AUTO_INCREMENT 列追加它。ALTER TABLE 允许指定多个活动，因此，上述工作可在单个语句中完成：
    ALTER TABLE t
    DROP i,
    ADD i INT UNSIGNEDAUTO_INCREMENT NOT NULL PRIMARY KEY
    3.8.6 非正常次序的串
    假如有一个表示体育机构人员的表，如橄榄球队，如果按人员职位进行排序，以便以特殊的顺序表示它，如：教练、教练助理、四分卫、流动后卫、接球员、巡逻员等。可将列定义为ENUM 并按希望出现的顺序定义枚举元素。对该列的排序将会以所指定的顺序自动进行。
    3.8.7 建立计数表
    在第2章的“使用序列”小节中，我们介绍了怎样利用L A S T _ INSERT_ID(expr) 生成一个序列。那个例子说明了怎样利用单列的表进行计数。那样做对于只需要单个计数器的情形能够满足需要，但是，如果需要几个计数器，该方法将会引起不必要的表重复。假如有一个Web 站点并且想要在几个页面上放置“此页面已经被访问nnn 次”这样的计数器。那么为每个具有一个计数器的页面建立一个单独的表就有些多余了。避免创建多个计数器表的一种方法是建立一个两列的表。其中一列存放计数值；另一列存放计数器名。这时仍然可以使用LAST _ INSERT_ID( ) 函数，但可用计数器名来决定用哪一行。这个表如下所示：
    CREATE TABLE counter
    (
    count INT UNSIGNED,
    name varchar(255) NOT NULL PRIMARY KEY
    )
    其中计数器名为一个串，从而可以调用任何想要的计数器，我们将其定义为PRIMARY KEY 以免名称重复。这里假定使用这个表的应用程序知道他们将使用的名称。对于前面所说的Web 计数器，可通过利用文件树中每个页面的路径名作为其计数器名的方法，保证计数器名的唯一性。例如，要为站点的主页建立一个新计数器，可执行下列语句：
    INSERT INTO counter(name) VALUES("index.html")
    它用零值初始化称为“ index.html”的计数器。为了生成序列中的下一个值，增加表中相应行的计数值，然后用LAST _ INSERT_ID( ) 检索它：
    UPDATE counter
    SET count = LAST_INSERT_ID(count+1)
    WHERE name = "index.html"
    SELECT LAST_INSERT_ID()
    另一种方法是不用LAST _ INSERT_ID( ) 增加计数器的值，如下所示：
    UPDATE counter SET count = count+1WHERE name = "index.html"
    SELECT count FROM counter WHERE name = "index.html"
    然而，如果另一个客户在您发布UPDATE语句与SELECT 语句之间增加了该计数器的值，则这种方法工作不正常。不过可在此两条语句的前后分别放置LOCK TABLES 和U N L O C KTABLES，在您使用该计数器时阻塞其他客户，以解决上述问题。但用L A S T _ INSERT_ID( )方法完成同样的工作更为容易一些。因为它的值是客户专用的，您总能得到自己插入的值，而不是其他客户插入的值，而且不必阻塞其他客户使代码复杂化。
    3.8.8 检查表是否存在
    在应用程序内部知道一个表是否存在有时很有用。为了做到这一点，可使用下列任一条语句：
    SELECT COUNT(*) FROM tb1_name
    SELECT * FROM tb1_name WHERE 1=0
    如果指定的表存在，则上述两条语句都将执行成功，如果不存在，则都失败。它们是这种测试的很好的查询。它们执行速度快，所以不会费太多的时间。这种方法最适合您自己编写的应用程序，因为您可以测试查询的成功与失败并采取相应的措施。但在从mysql运行的批量脚本中不特别有用，因为发生错误时除了终止运行外不可能做任何事（或者可以忽略相应的错误，但是显然无法再运行该查询了）。

原文转自：http://www.ltesting.net