`

mysql 应用入门 （2）

MySQL应用服务器正则表达式F#脚本 
阅读更多

日期计算

MySQL提供几个函数，你能用来执行在日期上的计算，例如，计算年龄或提取日期的部分。

为了决定你的每个宠物有多大，用出生日期和当前日期之间的差别计算年龄。通过变换2个日期到天数，取差值，并且用365除(在一年里的天数)： 

mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 FROM pet;
+----------+-------------------------------------+
| name     | (TO_DAYS(NOW())-TO_DAYS(birth))/365 |
+----------+-------------------------------------+
| Fluffy   |                                6.15 |
| Claws    |                                5.04 |
| Buffy    |                                9.88 |
| Fang     |                                8.59 |
| Bowser   |                                9.58 |
| Chirpy   |                                0.55 |
| Whistler |                                1.30 |
| Slim     |                                2.92 |
| Puffball |                                0.00 |
+----------+-------------------------------------+

尽管查询可行，关于它还有能被改进的一些事情。首先，如果行以某个次序表示，其结果能更容易被扫描。第二，年龄列的标题不是很有意义的。

第一个问题通过增加一个ORDER BY name子句按名字排序输出来解决。为了处理列标题，为列提供一个名字以便一个不同的标签出现在输出中(这被称为一个列别名)： 

mysql> SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age
    -> FROM pet ORDER BY name;
+----------+------+
| name     | age  |
+----------+------+
| Bowser   | 9.58 |
| Buffy    | 9.88 |
| Chirpy   | 0.55 |
| Claws    | 5.04 |
| Fang     | 8.59 |
| Fluffy   | 6.15 |
| Puffball | 0.00 |
| Slim     | 2.92 |
| Whistler | 1.30 |
+----------+------+

为了按age而非name排序输出，只要使用一个不同ORDER BY子句： 

mysql>  SELECT name, (TO_DAYS(NOW())-TO_DAYS(birth))/365 AS age
    ->  FROM pet ORDER BY age;
+----------+------+
| name     | age  |
+----------+------+
| Puffball | 0.00 |
| Chirpy   | 0.55 |
| Whistler | 1.30 |
| Slim     | 2.92 |
| Claws    | 5.04 |
| Fluffy   | 6.15 |
| Fang     | 8.59 |
| Bowser   | 9.58 |
| Buffy    | 9.88 |
+----------+------+

一个类似的查询可以被用来确定已经死亡动物的死亡年龄。你通过检查death值是否是NULL来决定那些是哪些动物，然后，对于那些有非NULL值，计算在death和birth值之间的差别： 

mysql>  SELECT name, birth, death, (TO_DAYS(death)-TO_DAYS(birth))/365 AS age
    ->  FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5.91 |
+--------+------------+------------+------+

差询使用death IS NOT NULL而非death != NULL，因为NULL是特殊的值，这以后会解释。见8.4.4.6 用NULL值工作。

如果你想要知道哪个动物下个月过生日，怎么办？对于这类计算，年和天是无关的，你简单地想要提取birth列的月份部分。MySQL提供几个日期部分的提取函数，例如YEAR()、MONTH()和DAYOFMONTH()。在这里MONTH()是适合的函数。为了看它怎样工作，运行一个简单的查询，显示birth和MONTH(birth)的值： 

mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

用下个月的生日找出动物也是容易的。假定当前月是4月，那么月值是4并且你寻找在5月出生的动物 (5月)， 象这样： 

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

当然如果当前月份是12月，就有点复杂了。你不是只把加1到月份数(12)上并且寻找在13月出生的动物，因为没有这样的月份。相反，你寻找在1月出生的动物(1月) 。

你甚至可以编写查询以便不管当前月份是什么它都能工作。这种方法你不必在查询中使用一个特定的月份数字，DATE_ADD()允许你把时间间隔加到一个给定的日期。如果你把一个月加到NOW()值上，然后用MONTH()提取月份部分，结果产生寻找生日的月份： 

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

完成同样任务的一个不同方法是加1以得出当前月份的下一个月(在使用取模函数(MOD)后，如果它当前是12，则“绕回”月份到值0)： 

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;

注意，MONTH返回在1和12之间的一个数字，且MOD(something,12)返回在0和11之间的一个数字，因此必须在MOD()以后加1，否则我们将从11月( 11 )跳到1月(1)。

8.4.4.6 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这样特殊的处理是为什么，在前面的章节中，为了决定哪个动物不再是活着的，使用death IS NOT NULL而不是death != NULL是必要的。

8.4.4.7 模式匹配

MySQL提供标准的SQL模式匹配，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。

SQL的模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零个字符)。在 MySQL中，SQL的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL模式时，你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符。

为了找出以“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]”匹配任何数字。
  • “ * ”匹配零个或多个在它前面的东西。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配的任何数量的数字，而“.*”匹配任何数量的任何东西。
  • 正则表达式是区分大小写的，但是如果你希望，你能使用一个字符类匹配两种写法。例如，“[aA]”匹配小写或大写的“a”而“[a-zA-Z]”匹配两种写法的任何字母。
  • 如果它出现在被测试值的任何地方，模式就匹配(只要他们匹配整个值，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       |
+----------+-------+---------+------+------------+------------+

既然如果一个正规表达式出现在值的任何地方，其模式匹配了，就不必再先前的查询中在模式的两方面放置一个通配符以使得它匹配整个值，就像如果你使用了一个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  |
+-------+--------+---------+------+------------+-------+

8.4.4.8 行计数

数据库经常用于回答这个问题，“某个类型的数据在一张表中出现的频度?”例如，你可能想要知道你有多少宠物，或每位主人有多少宠物，或你可能想要在你的动物上施行各种类型的普查。

计算你拥有动物的总数字与“在pet表中有多少行?”是同样的问题，因为每个宠物有一个记录。COUNT()函数计数非NULL结果的数目，所以数你的动物的查询看起来像这样： 

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

在前面，你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你可以使用COUNT()函数：

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

注意，使用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

COUNT()和GROUP BY对以各种方式分类你的数据很有用。下列例子显示出实施动物普查操作的不同方式。

每种动物数量： 

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

每中性别的动物数量： 

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

（在这个输出中，NULL表示“未知性别”。）

按种类和性别组合的动物数量： 

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 |
+---------+------+----------+

当你使用COUNT()时，你不必检索整个一张表。例如, 先前的查询，当只在狗和猫上施行时，看起来像这样： 

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = "dog" OR species = "cat"
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

或，如果你仅需要知道已知性别的按性别的动物数目： 

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

8.4.5 使用多个数据库表

pet表追踪你有哪个宠物。如果你想要记录他们的其他信息，例如在他们一生中事件看兽医或何时后代出生，你需要另外的表。这张表应该像什么呢？

  • 它需要包含宠物名字因此你知道每个事件属于此动物。
  • 它需要一个日期因此你知道事件什么时候发生的。
  • 需要一个字段描述事件。
  • 如果你想要可分类事件，有一个事件类型字段将是有用的。

给出了这些考虑，为event表的CREATE TABLE语句可能看起来像这样： 

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

就象pet表，最容易的示通过创建包含信息的一个定位符分隔的文本文件装载初始记录：

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
Fang 1991-10-12 kennel
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

象这样装载记录： 

mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event;

基于你从已经运行在pet表上的查询中学到的，你应该能执行在event表中记录的检索；原则是一样的。但是什么时候是event表本身不足以回答你可能问的问题呢？

当他们有了一窝小动物时，假定你想要找出每只宠物的年龄。 event表指出何时发生，但是为了计算母亲的年龄，你需要她的出生日期。既然它被存储在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         |
+--------+------+-----------------------------+

关于该查询要注意的几件事情：

  • FROM子句列出两个表，因为查询需要从他们两个拉出信息。
  • 当组合(联结-join)来自多个表的信息时，你需要指定在一个表中的记录怎样能匹配其它表的记录。这很简单，因为它们都有一个name列。查询使用WHERE子句基于name值来匹配2个表中的记录。
  • 因为name列出现在两个表中，当引用列时，你一定要指定哪个表。这通过把表名附在列名前做到。

你不必有2个不同的表来执行一个联结。如果你想要将一个表的记录与同一个表的其他记录进行比较，联结一个表到自身有时是有用的。例如，为了在你的宠物之中繁殖配偶，你可以用pet联结自身来进行相似种类的雄雌配对： 

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     |
+--------+------+--------+------+---------+

在这个查询中，我们为表名指定别名以便能引用列并且使得每一个列引用关联于哪个表实例更直观。

8.5 获得数据库和表的信息

如果你忘记一个数据库或表的名字，或一个给定的表的结构是什么(例如，它的列叫什么)，怎么办？ MySQL通过提供数据库及其支持的表的信息的几个语句解决这个问题。

你已经见到了SHOW DATABASES，它列出由服务器管理的数据库。为了找出当前选择了哪个数据库，使用DATABASE()函数： 

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

如果你还没选择任何数据库，结果是空的。

为了找出当前的数据库包含什么表(例如，当你不能确定一个表的名字)，使用这个命令： 

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

如果你想要知道一个表的结构，DESCRIBE命令是有很用的；它显示有关一个表的每个列的信息： 

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Field显示列名字，Type是为列的数据类型，Null表示列是否能包含NULL值，Key显示列是否被索引而Default指定列的缺省值。

如果你在一个表上有索引，SHOW INDEX FROM tbl_name生成有关它们的信息。

8.6 以批处理模式使用mysql

在前面的章节中，你交互式地使用mysql输入查询并且查看结果。你也可以以批模式运行mysql。为了做到这些，把你想要运行的命令放在一个文件中，然后告诉mysql从文件读取它的输入： 

shell> mysql < batch-file

如果你需要在命令行上指定连接参数，命令可能看起来像这样： 

shell> mysql -h host -u user -p < batch-file
Enter password: ********

当你这样使用mysql时，你正在创建一个脚本文件，然后执行脚本。

为什么要使用一个脚本？有很多原因：

  • 如果你重复地运行查询(比如说，每天或每周)，把它做成一个脚本使得你在每次执行它时避免重新键入。
  • 你能通过拷贝并编辑脚本文件从类似的现有的查询生成一个新查询。
  • 当你正在开发查询时，批模式也是很有用的，特别对多行命令或多行语句序列。如果你犯了一个错误，你不必重新打入所有一切，只要编辑你的脚本来改正错误，然后告诉mysql再次执行它。
  • 如果你有一个产生很多输出的查询，你可以通过一个分页器而不是盯着它翻屏到你屏幕的顶端来运行输出： 
    shell> mysql < batch-file | more
  • 你能捕捉输出到一个文件中进行更一步的处理： 
    shell> mysql < batch-file > mysql.out
  • 你可以散发脚本给另外的人，因此他们也能运行命令。
  • 一些情况不允许交互地使用，例如, 当你从一个cron任务中运行查询时。在这种情况下，你必须使用批模式。

当你以批模式运行mysql时，比起你交互地使用它时，其缺省输出格式是不同的(更简明些)。例如，当交互式运行SELECT DISTINCT species FROM pet时，输出看起来像这样： 

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

但是当以批模式运行时，像这样： 

species
bird
cat
dog
hamster
snake

如果你想要在批模式中得到交互的输出格式，使用mysql -t。为了回显以输出被执行的命令，使用mysql -vvv。

8.7 双胞胎项目的查询（实例）

在Analytikerna 和 Lentus，我们为一个大的研究项目工程一直在做系统和现场工作。这个项目是Institute of Environmental Medicine at Karolinska Institutet Stockholm 和 the Section on Clinical Research in Aging and Psychology at the University of Southern California的合作项目。

双胞胎研究的更多信息可在下列链接找到： 

http://www.imm.ki.se/TWIN/TWINUKW.HTM

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

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

8.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 sceening 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 */
        and substring(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 */
        and not (h.status = 'Refused' or h.status = 'Aborted'
        or h.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)以使查询更快。

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

上面所用的表的当前记录数是：

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

8.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
        and t1.id = t2.id
        /* Just the sceening 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;
分享到：
| mysql 使用入门（1）
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    MySQL数据库应用从入门到精通_第2版

    本书《MySQL数据库应用从入门到精通_第2版》旨在帮助读者从零基础开始，逐步掌握MySQL的使用技巧和高级功能，从而实现数据库管理与开发的精通。 一、MySQL基础知识 在入门阶段，首先会介绍MySQL的安装与配置，包括...

    MySQL数据库应用从入门到精通 第2版

    本书《MySQL数据库应用从入门到精通 第2版》旨在帮助初学者系统地掌握MySQL的使用，通过详细的实例和截图，使得学习过程更加直观易懂。 一、MySQL基础知识 MySQL是一个多用户、多线程的SQL数据库管理系统，它使用...

    MySQL数据库应用从入门到精通 王飞飞 第2版 pdf

    资源为扫描版MySQL数据库应用从入门到精通 王飞飞 第2版 pdf的百度网盘地址，不用担心失效，失效的话有上传者邮件联系方式，联系作者获取最新地址。长期有效。

    数据库MySQL入门.pdf

    MySQL入门讲义 MySQL是属于Oracle Corporation（甲骨文公司）推出的关系型数据库管理系统， MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。 MySQL最流行的关系型数据库...

    mysql从入门到精通

    《MySQL从入门到精通》是一本介绍MySQL数据库基础知识与应用的书籍，适用于程序员、数据库管理员和一般的MySQL用户。本书旨在帮助读者全面掌握MySQL数据库的运行原理、数据和表的类型，以及高级SQL索引、优化、编程...

    《MySQL数据库入门》-黑马程序员 配套书籍资源 .zip

    MySQL是一个流行的开源关系型数据库管理系统（RDBMS），它以其高效、稳定和易用性在全球范围内广泛应用于网站开发、数据分析、企业级应用等场景。学习MySQL首先需要理解数据库的基本概念，如数据表、字段、记录和...

    PHP5和MySQL从入门到精通

    2. **MySQL基础**：讲解如何安装和配置MySQL服务器，创建数据库和表，以及使用SQL语言进行数据查询、插入、更新和删除操作。 3. **PHP与MySQL的交互**：阐述如何通过PHP连接MySQL数据库，执行SQL语句，处理查询结果...

    完整精品数据库课件 MySQL从入门到精通 第01章 初始MySQL（共19页）.ppt

    总的来说，这个“MySQL从入门到精通”的课程内容详尽，涵盖了从数据库基础知识到高级应用的所有方面，是学习MySQL的理想资源。通过系统学习，你可以掌握从安装配置到性能优化，从基本操作到复杂查询，从单一用户管理...

    MySQL入门+进阶资源合集.pdf

    通过以上内容的学习，你可以深入了解MySQL的核心概念、掌握基本和高级的数据库操作技能，并且能够运用这些知识来优化数据库性能和开发更高效的数据库应用。无论是对于初学者还是有一定经验的开发者来说，这些知识点...

    MySQL数据库入门.rar

    2. "01-MySQL数据库入门 教学设计.doc"：这部分可能详细介绍了MySQL的背景，安装过程，以及基本的数据库概念。 3. "02-数据库和表的基本操作 教学设计.doc"：这里将介绍如何创建数据库，定义表结构，以及插入、查询...

    Mysql快速入门资料

    MySQL是一款广泛使用的开源关系型数据库管理系统，尤其在Java开发领域中有着重要的...这份"Mysql快速入门资料"应该包含了以上所有内容，通过深入学习和实践，初学者能够迅速掌握MySQL的基本操作和在Java开发中的应用。

    mysql入门很简单

    在数据库泛型中，范式（数据库规范化的规则）是核心概念，关系数据库常用的范式有第一范式（1NF）、第二范式（2NF）、第三范式（3NF）和BCNF。设计数据库时，至少需要满足第一范式，这是关系数据库的基础要求。 在...

    Mysql从入门到精通.pdf

    MySQL通常用于网页服务器，能够处理大量数据、高并发访问请求，且易于使用、价格低廉，是构建动态网站或应用程序的理想选择。 在给定文件的内容中，提供了关于MySQL的基础知识点，下面是对这些知识点的详细说明： ...

    MySQL数据库应用-从入门到精通

    《MySQL数据库应用-从入门到精通》这本书旨在帮助读者系统地学习并掌握MySQL的相关知识，从基础概念到高级应用，涵盖全面，旨在提升读者的数据库管理技能。配套PPT文档提供了直观的视觉辅助，使学习过程更为高效。 ...

    mysql数据库应用从入门到精通第二版

    《MySQL数据库应用从入门到精通第二版》是一本旨在帮助读者深入理解和掌握MySQL技术的书籍，尤其适合初学者和有一定基础的学习者。PDF版本使得读者可以方便地在电子设备上阅读和查阅。 在本书中，你将学习到以下...

    MySQL5.7从入门到精通 带书签 高清

    这份“MySQL5.7从入门到精通”的资源旨在为初学者和有经验的开发者提供一个全面的学习指南，通过高清带书签的PDF文档，使得学习过程更加高效和便捷。 一、MySQL基础 MySQL的基础部分通常包括数据库的概念、安装与...

    Mysql5.7入门到精通PDF带目录 高清

    总之，“MySQL 5.7入门到精通”涵盖了MySQL数据库的基础知识到高级应用，无论你是刚接触数据库的新手，还是希望提升MySQL技能的专业人士，这个教程都将为你提供宝贵的资源。通过系统学习并实践，你将能够熟练地运用...

    MySQL数据库应用从入门到精通 第2版 .rar

    本书《MySQL数据库应用从入门到精通 第2版》由王菲菲、崔洋和贺亚茹三位专家编著，旨在帮助读者从零基础开始，逐步掌握MySQL的使用技巧和高级特性，最终达到精通的水平。 在入门阶段，本书将介绍数据库的基本概念，...

Magicbox
Global site tag (gtag.js) - Google Analytics