MySQL 入門チュートリアル 5 - データ テーブルからの情報の取得

SELECT ステートメントは、データテーブルから情報を取得するために使用されます。ステートメントの一般的な形式は次のとおりです。

SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;

what_to_select は、表示したいものを示します。列のテーブル、または * は「すべての列」を意味します。 what_table は、データを取得するテーブルを示します。 WHERE 句はオプションです。この項目が選択されている場合、condition_to_satisfy は行が満たす必要がある取得条件を指定します。

1. すべてのデータを選択します

最も単純な SELECT 形式は、テーブルからすべてのレコードを取得することです:

mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

テーブル全体を参照したい場合は、この形式の SELECT を使用できます。たとえば、初期データ セットにはその後ロードされたばかりです。あるいは、クッパの誕生日は正しくないと考えているかもしれません。元の家系図を確認すると、正しい出生年は 1979 年ではなく 1989 年であることがわかります。

これを修正するには少なくとも 2 つの方法があります:

· ファイル「pet.txt」を編集してエラーを修正し、DELETE と LOAD DATA を使用してテーブルをクリアし、再ロードします:

 mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

ただし、これを行うと、Puffball レコードを再入力する必要があります。

·UPDATE ステートメントを使用して、問題のあるレコードのみを修正します。

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

UPDATE は、データベース テーブルを再ロードせずに、問題のあるレコードのみを変更します。

2. 特別な行を選択します

上記のように、テーブル全体を取得するのは簡単です。 SELECT ステートメントから WHERE 句を削除するだけです。ただし、一般に、特にテーブルが非常に大きくなった場合には、テーブル全体を表示する必要はありません。代わりに、通常は特定の質問に答えることに関心があり、その場合、必要な情報に制限が課されます。あなたのペットに関する質問に対して彼らが答える質問の一部を見てみましょう。テーブルから特定の行のみを選択できます。たとえば、Bowser の誕生日に加えた変更を確認したい場合は、次のように Bowser のレコードを選択します。

mysql> SELECT * FROM pet WHERE name = 'Bowser'; +--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

この出力により、正しい年のレコードが 1979 年ではなく 1989 年であることが確認されます。

文字列比較は通常、サイズに影響されないため、名前を「bowser」、「BOWSER」などとして指定でき、クエリ結果は同じになります。

名前だけでなく、任意の列に条件を指定できます。たとえば、1998 年以降に生まれた動物を知りたい場合は、出生列をテストします:

mysql> SELECT * FROM pet WHERE birth > '1998-1-1'; +----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

たとえば、雌犬を検索するために条件を組み合わせることができます:

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

上記のクエリでは AND 論理演算子が使用されており、も OR 演算です。 記号:

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; +----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+

AND と OR は混合できますが、AND は OR よりも優先されます。 2 つの演算子を使用している場合は、括弧を使用して条件をグループ化する方法を示すことをお勧めします。

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')     -> OR (species = 'dog' AND sex = 'f'); +-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

3. 特別な列を選択します

テーブル内のすべての行を表示したくない場合は、興味のある列をカンマで区切って入力します。たとえば、動物がいつ生まれたかを知りたい場合は、名前と誕生の列を選択します:

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

ペットの所有者を調べるには、次のクエリを使用します:

mysql> SELECT owner FROM pet; +--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

このクエリは単に各動物の所有者を取得するだけであることに注意してください。レコード列があり、一部の列は複数回表示されます。出力を最小限にするには、キーワード DISTINCT を追加して、一意の各出力レコードを取得します。

mysql> SELECT DISTINCT owner FROM pet; +--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

WHERE 句を使用して、行の選択と列の選択を組み合わせることができます。たとえば、犬と猫の生年月日をクエリするには、次のクエリを使用します。

mysql> SELECT name, species, birth FROM pet     -> WHERE species = 'dog' OR species = 'cat'; +--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+

4. カテゴリ行

前の例で、結果行が特定の順序で表示されていないことに気づいたかもしれません。ただし、多くの場合、行が特定の方法で並べ替えられている場合、クエリ出力を調べるのが簡単になります。結果を並べ替えるには、ORDER BY 句を使用します。以下は、動物の誕生日を日付順に並べたものです:

mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

文字タイプの列では、他のすべての比較演算と同様に、並べ替え機能は通常、大文字と小文字を区別して実行されます。これは、大文字と小文字が異なる同一の列の順序が定義されていないことを意味します。特定の列に対して、BINARY を使用して、次のような大文字と小文字を区別する分類関数を適用できます。 ORDER BY BINARY col_name.

デフォルトの並べ替えは、最小値が最初となる昇順です。降順で並べ替えるには、並べ替える列名に DESC (降順) キーワードを追加します。

mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+

複数の列を並べ替えたり、異なる列を異なる方向に並べ替えたりできます。たとえば、動物種を昇順で並べ替えてから、各動物種を生年月日で降順 (最も若い動物から順) に並べ替えるには、次のクエリを使用します。

mysql> SELECT name, species, birth FROM pet     -> ORDER BY species, birth DESC; +----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

DESC キーワードは、その前にあるキーワードにのみ適用されることに注意してください。列名 (誕生) は、種列の並べ替え順序には影響しません。

5. 日付の計算

MySQL には、年齢の計算や日付部分の抽出など、日付の計算に使用できる関数がいくつか用意されています。

各ペットの年齢を確認するには、現在の日付と生年月日の差を計算します。現在の日付の暦年が生年月日より前の場合は、1 年減算されます。次のクエリは、各ペットの誕生日、現在の日付、および年齢の値を年番号で表示します。

mysql> SELECT name, birth, CURDATE(),     -> (YEAR(CURDATE())-YEAR(birth))     -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))     -> AS age     -> FROM pet; +----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

ここで、YEAR()は日付の年の部分を抽出し、RIGHT()は日付のMM-DD(暦年)部分の右端の5文字を​​抽出します。 MM-DD値を比較する式部分の値は、通常1または0となります。CURDATE()の年が生まれ年より前の場合は、その年を1減算する必要があります。式全体は少し複雑です。出力列のラベルをより意味のあるものにするために alias (age) を使用します。

尽管查询可行，如果以某个顺序排列行，则能更容易地浏览结果。添加ORDER BY name子句按照名字对输出进行排序则能够实现。

mysql> SELECT name, birth, CURDATE(),    -> (YEAR(CURDATE())-YEAR(birth))    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))    -> AS age

-> FROM pet ORDER BY name;

+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+

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

mysql> SELECT name, birth, CURDATE(),     -> (YEAR(CURDATE())-YEAR(birth))     -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))     -> AS age     -> FROM pet ORDER BY age; +----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

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

mysql> SELECT name, birth, death,     -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))     -> AS age     -> FROM pet WHERE death IS NOT NULL ORDER BY age; +--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+

查询使用death IS NOT NULL而非death != NULL，因为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(CURDATE(),INTERVAL 1 MONTH));

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

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

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

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或 NULL意味着假而其它值意味着真。布尔运算的默认真值是1。

对NULL的特殊处理即是在前面的章节中，为了决定哪个动物不再是活着的，使用death IS NOT NULL而不使用death != NULL的原因。

在GROUP BY中，两个NULL值视为相同。

执行ORDER BY时，如果运行 ORDER BY ... ASC，则NULL值出现在最前面，若运行ORDER BY ... DESC，则NULL值出现在最后面。

NULL操作的常见错误是不能在定义为NOT NULL的列内插入0或空字符串，但事实并非如此。在NULL表示"没有数值"的地方有数值。使用IS [NOT] NULL则可以很容易地进行测试，如下所示：

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL; +-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+

因此完全可以在定义为NOT NULL的列内插入0或空字符串，实际是NOT NULL。

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]*”

匹配任何数量的数字，而

“.*”

匹配任何数量的任何字符。

• 如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。

• 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用
  “^”

  或

  在模式的结尾用“$”

  。

为了说明扩展正则表达式如何工作，下面使用REGEXP重写上面所示的LIKE查询：

为了找出以

“b”

开头的名字，使用

“^”

匹配名字的开始：

mysql> SELECT * FROM pet WHERE name REGEXP '^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 |
+--------+--------+---------+------+------------+------------+

如果你想强制使REGEXP比较区分大小写，使用BINARY关键字使其中一个字符串变为二进制字符串。该查询只匹配名称首字母的小写‘b’。

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

为了找出以

“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”

的名字，使用以下查询：

mysql> SELECT * FROM pet WHERE name REGEXP '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       |
+----------+-------+---------+------+------------+------------+

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

数据库经常用于回答这个问题，“某个类型的数据在表中出现的频度?”例如，你可能想要知道你有多少宠物，或每位主人有多少宠物，或你可能想要对你的动物进行各种类型的普查。计算你拥有动物的总数目与“在pet表中有多少行?”是同样的问题，因为每个宠物有一个记录。COUNT(*)函数计算行数，所以计算动物数目的查询应为：

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(*) FROM pet; ERROR 1140 (42000): 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 |
+---------+------+----------+

9. 使用1个以上的表

pet表追踪你有哪个宠物。如果你想要记录其它相关信息，例如在他们一生中看兽医或何时后代出生，你需要另外的表。这张表应该像什么呢？需要：· 它需要包含宠物名字以便你知道每个事件属于哪个动物。

· 需要一个日期以便你知道事件是什么时候发生的。

· 需要一个描述事件的字段。

· 如果你想要对事件进行分类，则需要一个事件类型字段。

综合上述因素，event表的CREATE TABLE语句应为：

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

对于pet表，最容易的方法是创建包含信息的用定位符分隔的文本文件来装载初始记录：

name	date	type	remark
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,     -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,     -> remark     -> FROM pet, event     -> WHERE pet.name = event.name AND event.type = &#39;litter&#39;; +--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

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

• FROM子句列出两个表，因为查询需要从两个表提取信息。

• 当从多个表组合(联结)信息时，你需要指定一个表中的记录怎样能匹配其它表的记录。这很简单，因为它们都有一个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     |
+--------+------+--------+------+---------+

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

 以上就是MySQL入门教程5 —— 从数据表中检索信息的内容，更多相关内容请关注PHP中文网（www.php.cn）！