MySQL使用教程（六）

3.7. 孪生项目的查询

3.7.1. 查找所有未分发的孪生项

3.7.2. 显示孪生对状态的表

这个项目是Institute of Environmental Medicine atKarolinska Institutet Stockholm 和 the Section on Clinical Research in Aging andPsychology at the University of Southern California的合作项目。

该项目包括筛选部分，即通过电话回访在瑞典超过 65 岁的所有孪生。满足某种标准的孪生进入下一阶段。在下一阶段中，医生/护士小组将访问想参加的孪生。部分检查包括物理检查和神经、心理检查、实验室试验、神经成像、心理状况评估和家族历史搜集。并且，应根据医疗和环境风险因素来搜集数据。

可从以下链接找到孪生研究的更多信息：

http://www.mep.ki.se/twinreg/index_en.html

用一个用Perl和MySQL编写的web接口来管理项目的后面部分。

每天晚上所有会谈的数据被移入一个MySQL数据库。

3.7.1. 查找所有未分发的孪生项

下列查询用来决定谁进入项目的第二部分：

SELECT

   CONCAT(p1.id, p1.tvab) + 0 AS tvid,

   CONCAT(p1.christian_name, ' ', p1.surname) AS Name,

   p1.postal_code AS Code,

   p1.city AS City,

   pg.abrev AS Area,

   IF(td.participation = 'Aborted', 'A', ' ') AS A,

   p1.dead AS dead1,

   l.event AS event1,

   td.suspect AS tsuspect1,

   id.suspect AS isuspect1,

   td.severe AS tsevere1,

   id.severe AS isevere1,

   p2.dead AS dead2,

   l2.event AS event2,

   h2.nurse AS nurse2,

   h2.doctor AS doctor2,

   td2.suspect AS tsuspect2,

   id2.suspect AS isuspect2,

   td2.severe AS tsevere2,

   id2.severe AS isevere2,

   l.finish_date

FROM

   twin_project AS tp

    /*For Twin 1 */

   LEFT JOIN twin_data AS td ON tp.id = td.id

             AND tp.tvab = td.tvab

   LEFT JOIN informant_data AS id ON tp.id = id.id

             AND tp.tvab = id.tvab

   LEFT JOIN harmony AS h ON tp.id = h.id

             AND tp.tvab = h.tvab

   LEFT JOIN lentus AS l ON tp.id = l.id

             AND tp.tvab = l.tvab

    /*For Twin 2 */

   LEFT JOIN twin_data AS td2 ON p2.id = td2.id

              AND p2.tvab = td2.tvab

   LEFT JOIN informant_data AS id2 ON p2.id = id2.id

             AND p2.tvab = id2.tvab

   LEFT JOIN harmony AS h2 ON p2.id = h2.id

             AND p2.tvab = h2.tvab

   LEFT JOIN lentus AS l2 ON p2.id = l2.id

             AND p2.tvab = l2.tvab,

   person_data AS p1,

   person_data AS p2,

   postal_groups AS pg

WHERE

    /*p1 gets main twin and p2 gets his/her twin. */

    /*ptvab is a field inverted from tvab */

   p1.id = tp.id AND p1.tvab = tp.tvab AND

    p2.id= p1.id AND p2.ptvab = p1.tvab AND

    /*Just the screening survey */

   tp.survey_no = 5 AND

    /*Skip if partner died before 65 but allow emigration (dead=9) */

   (p2.dead = 0 OR p2.dead = 9 OR

    (p2.dead = 1 AND

     (p2.death_date = 0 OR

       (((TO_DAYS(p2.death_date) -TO_DAYS(p2.birthday)) / 365)

       >= 65))))

    AND

    (

    /*Twin is suspect */

   (td.future_contact = 'Yes' AND td.suspect = 2) OR

    /*Twin is suspect - Informant is Blessed */

   (td.future_contact = 'Yes' AND td.suspect = 1

                               AND id.suspect =1) OR

    /*No twin - Informant is Blessed */

   (ISNULL(td.suspect) AND id.suspect = 1

                        AND id.future_contact ='Yes') OR

    /*Twin broken off - Informant is Blessed */

   (td.participation = 'Aborted'

    AND id.suspect = 1 AND id.future_contact = 'Yes') OR

    /*Twin broken off - No inform - Have partner */

   (td.participation = 'Aborted' AND ISNULL(id.suspect)

                                  AND p2.dead =0))

    AND

   l.event = 'Finished'

    /*Get at area code */

    ANDSUBSTRING(p1.postal_code, 1, 2) = pg.code

    /*Not already distributed */

    AND(h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)

    /*Has not refused or been aborted */

    ANDNOT (h.status = 'Refused' OR h.status = 'Aborted'

    ORh.status = 'Died' OR h.status = 'Other')

ORDER BY

   tvid;

一些解释：

·         CONCAT(p1.id,p1.tvab) + 0 AS tvid

我们想要在id和tvab的连接上以数字顺序排序。结果加0使得MySQL把结果变为一个数字。

·         列id

这标识一对孪生。它是所有表中的一个键。

·         列tvab

这标识孪生中的一个。它的值为1或2。

·         列ptvab

这是tvab的一个逆运算。当tvab是1，它是2，反之亦然。它用来保存输入并且使MySQL的优化查询更容易。

这个查询表明，怎样用联结(p1和p2)从同一个表中查找表。在例子中，这被用来检查孪生的一个是否在65岁前死了。如果如此，行不返回值。

上述所有孪生信息存在于所有表中。我们对id,tvab（所有表）和id,ptvab (person_data) 上采用键以使查询更快。

在我们的生产机器上(一台200MHzUltraSPARC)，这个查询返回大约 150-200 行并且时间不超过一秒。

表	行数
person_data	71074
lentus	5291
twin_project	5286
twin_data	2012
informant_data	663
harmony	381
postal_groups	100

3.7.2. 显示孪生对状态的表

每一次会面以一个称为event的状态码结束。下面显示的查询被用来显示按事件组合的所有孪生的表。这表明多少对孪生已经完成，多少对的其中之一已完成而另一个拒绝了，等等。

SELECT

       t1.event,

       t2.event,

       COUNT(*)

FROM

       lentus AS t1,

       lentus AS t2,

       twin_project AS tp

WHERE

        /*We are looking at one pair at a time */

       t1.id = tp.id

       AND t1.tvab=tp.tvab

        ANDt1.id = t2.id

        /*Just the screening survey */

       AND tp.survey_no = 5

        /*This makes each pair only appear once */

       AND t1.tvab='1' AND t2.tvab='2'

GROUP BY

       t1.event, t2.event;

3.8. 与Apache一起使用MySQL

还有一些项目，你可以从MySQL数据库鉴别用户，并且你还可以将日志文件写入MySQL数据库表。

你可以将以下内容放到Apache配置文件中，更改Apache日志格式，使MySQL更容易读取：

LogFormat\

       "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \

       \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

要想将该格式的日志文件装载到MySQL，你可以使用以下语句:

LOAD DATAINFILE '/local/access_log' INTO TABLE tbl_name

FIELDSTERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

所创建的表中的列应与写入日志文件的LogFormat行对应。

---

这是MySQL参考手册的翻译版本，关于MySQL参考手册，请访问dev.mysql.com。原始参考手册为英文版，与英文版参考手册相比，本翻译版可能不是最新的。