第 3 章 チュートリアル

目次

3.1 サーバーへの接続とサーバーからの切断
3.2 クエリーの入力
3.3 データベースの作成と使用
3.3.1 データベースの作成と選択
3.3.2 テーブルの作成
3.3.3 テーブルへのデータのロード
3.3.4 テーブルからの情報の取り出し
3.4 データベースとテーブルに関する情報の取得
3.5 バッチモードでの MySQL の使用
3.6 一般的なクエリーの例
3.6.1 カラムの最大値
3.6.2 特定のカラムの最大値が格納されている行
3.6.3 グループごとのカラムの最大値
3.6.4 特定のカラムのグループごとの最大値が格納されている行
3.6.5 ユーザー定義の変数の使用
3.6.6 外部キーの使用
3.6.7 2 つのキーを使用した検索
3.6.8 日ごとの訪問数の計算
3.6.9 AUTO_INCREMENT の使用
3.7 Apache での MySQL の使用

この章では、mysql クライアントプログラムを使用して、簡単なデータベースを作成して使用する方法を示すことで、MySQL のチュートリアルを提供します。mysql (端末モニターまたは単にモニターと呼ばれることもあります) は、MySQL Server への接続、クエリーの実行、および結果の表示を可能にするインタラクティブなプログラムです。mysql は、バッチモードでも使用できます。クエリーを前もってファイルに入れておき、mysql にファイルのコンテンツを実行するよう指示します。ここでは、mysql の両方の使用方法について説明します。

mysql で提供されているオプションのリストを表示するには、--help オプションを指定して mysql を起動します。

shell> mysql --help

この章では、mysql がマシンにインストールされていることと、接続可能な MySQL Server が存在することを前提にしています。そうでない場合は、MySQL 管理者に問い合わせてください。(ご自身が管理者の場合は、このドキュメントの第5章「MySQL サーバーの管理などの関連部分を参照してください。)

この章では、データベースの設定と使用の手順全体について説明します。既存のデータベースへのアクセス手順だけを知りたい場合は、データベースおよびそれに含まれるテーブルの作成方法に関するセクションはスキップすることもできます。

この章はチュートリアルであるため、必然的に多くの詳細が省略されています。ここで説明されているトピックの詳細については、ドキュメントの関連セクションを参照してください。

3.1 サーバーへの接続とサーバーからの切断

サーバーに接続するには、通常は mysql の起動時に MySQL ユーザー名を入力し、多くの場合はパスワードも入力する必要があります。ログインするマシンとは異なるマシンでサーバーが稼働している場合は、ホスト名も指定する必要があります。接続に使用する接続パラメータ (使用するホスト、ユーザー名、およびパスワード) については、管理者に問い合わせてください。正しいパラメータがわかったら、次の方法で接続できます。

shell> mysql -h host -u user -pEnter password: ********

host は MySQL サーバーが稼働しているホストの名前、user はユーザーの MySQL アカウントのユーザー名です。設定に応じて適切な値で置き換えてください。******** はユーザーのパスワードです。mysql「Enter password:」というプロンプトが表示されたら入力してください。

それが機能すると、照会情報に続いて mysql> プロンプトが表示されるはずです。

shell> mysql -h host -u user -pEnter password: ********Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 5.6.23-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

mysql> プロンプトは、mysql がコマンド入力の準備が整っていることを示します。

MySQL が稼働しているマシンと同じマシンにログインしている場合は、ホストを省略して次を使用できます。

shell> mysql -u user -p

ログイン時に次のようなエラーメッセージが表示される場合があります。 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)。これは、MySQL サーバーデーモン (Unix) またはサービス (Windows) が起動していないことを示しています。管理者に問い合わせるか、オペレーティングシステムに応じて第2章「MySQL のインストールと更新の適切なセクションを参照してください。

ログイン時によく発生するほかの問題については、セクションB.5.2「MySQL プログラム使用時の一般的なエラー」を参照してください。

一部の MySQL インストールでは、ローカルホストで稼働しているサーバーに、ユーザーが匿名 (名前を指定しない) ユーザーとして接続できます。これに該当するマシンでは、オプションを何も指定せずに mysql を起動すると、そのサーバーに接続できるはずです。

shell> mysql

接続に成功したら、QUIT (または \q) と mysql> プロンプトに入力することで、いつでも切断できます。

mysql> QUITBye

Unix では、Control+D を押しても切断できます。

以降のセクションに示すほとんどの例では、サーバーに接続していることを前提にしています。このことは、mysql> プロンプトによって示されます。

3.2 クエリーの入力

前のセクションで説明したように、サーバーに接続していることを確認します。それだけでは操作するデータベースは選択されていませんが、それでかまいません。この時点では、テーブルの作成、テーブルへのデータのロード、テーブルからのデータの取り出しに今すぐ進むのではなく、クエリーの発行方法を学ぶことが重要です。このセクションでは、mysql の動作に習熟するために、いくつかのクエリーを使用してコマンド入力の基本原則について説明します。

サーバーにバージョン番号と現在の日付を照会する単純なコマンドを次に示します。mysql> プロンプトに続けて、次に示すように入力してから Enter を押します。

mysql> SELECT VERSION(), CURRENT_DATE;+--------------+--------------+
| VERSION() | CURRENT_DATE |
+--------------+--------------+
| 5.6.1-m4-log | 2010-08-06 |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>

このクエリーには、mysql に関するいくつかの事項が示されています。

  • 通常、コマンドは SQL ステートメントとそれに続くセミコロンで構成されます。(セミコロンを省略できる例外もいくつかあります。前述の QUIT もその 1 つです。ほかのものについてはあとで説明します。)

  • コマンドを発行すると、mysql はそれをサーバーに送信して実行し、結果を表示してから、もう 1 つの mysql> プロンプトを出力して、次のコマンドの準備ができたことを示します。

  • mysql は、クエリーの出力を表形式 (行とカラム) で表示します。最初の行には、カラムのラベルが表示されます。以降の行はクエリーの結果です。通常、カラムラベルは、データベーステーブルからフェッチされるカラムの名前です。次の例のように、テーブルカラムではなく式の値を取得する場合、mysql ではカラムのラベルとして式自体が使用されます。

  • mysql では、返された行の数とクエリーの実行にかかった時間が表示されるため、サーバーの大まかなパフォーマンスがわかります。これらの値は、CPU 時間やマシン時間ではなく時計の時間で表されるため、また、サーバー負荷やネットワーク待機時間などの要因に影響されるため、正確ではありません。(簡略化のために、この章のほかの例ではrows in setの行が省略されている場合もあります。)

キーワードは大文字でも小文字でも入力できます。次のクエリーは同等です。

mysql> SELECT VERSION(), CURRENT_DATE;mysql> select version(), current_date;mysql> SeLeCt vErSiOn(), current_DATE;

次に、別のクエリーを示します。これは、mysql を簡単な計算機として使用できることを示しています。

mysql> SELECT SIN(PI()/4), (4+1)*5;+------------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 | 25 |
+------------------+---------+
1 row in set (0.02 sec)

これまでに示したクエリーは、比較的に短い、1 行のステートメントでした。1 行に複数のステートメントを入力することもできます。各ステートメントの末尾にはセミコロンを付加してください。

mysql> SELECT VERSION(); SELECT NOW();+--------------+
| VERSION() |
+--------------+
| 5.6.1-m4-log |
+--------------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2010-08-06 12:17:13 |
+---------------------+
1 row in set (0.00 sec)

1 つのコマンド全体を 1 行に入力する必要はないため、複数の行を必要とする長いコマンドでも問題ありません。mysql は、入力行の末尾を探すのではなく、終端のセミコロンを探すことによってステートメントの末尾を判定します。(つまり、mysql は自由形式の入力を受け入れます。入力行を収集しますが、セミコロンが見つかるまでは実行しません。)

次に、単純な複数行ステートメントを示します。

mysql> SELECT -> USER() -> , -> CURRENT_DATE;+---------------+--------------+
| USER() | CURRENT_DATE |
+---------------+--------------+
| jon@localhost | 2010-08-06 |
+---------------+--------------+

この例では、複数行にわたるクエリーの最初の行を入力したあと、プロンプトが mysql> から -> に変化することに注意してください。これは、mysql がまだ完全なステートメントを検出しておらず、残りの入力を待機していることを示しています。プロンプトは貴重なフィードバックを提供してくれる味方です。そのフィードバックを使用すれば、mysql が何を待機しているのかを、常に認識することができます。

入力途中のコマンドの実行を取り消すには、\c と入力します。

mysql> SELECT -> USER() -> \cmysql>

ここでもプロンプトに注目してください。\c の入力後に mysql> に戻り、mysql が新しいコマンドに対して準備できていることを示すフィードバックが与えられます。

次の表に、表示される各プロンプトとそれらが示す mysql の状態をまとめます。

プロンプト意味
mysql>新しいコマンドを入力できます。
->複数行コマンドの次の行を待機しています。
'>単一引用符 (') で始まった文字列が完了するまで次の行を待機しています。
">二重引用符 (") で始まった文字列が完了するまで次の行を待機しています。
`>逆引用符 (`) で始まった識別子が完了するまで次の行を待機しています。
/*>/* で始まったコメントが完了するまで次の行を待機しています。

一般に、1 行のコマンドを発行しようとして終端のセミコロンを忘れると、複数行ステートメントが発生します。この場合、mysql は追加の入力を待機します。

mysql> SELECT USER() ->

これが起きた場合 (ステートメントを入力したのに -> プロンプトの応答が示される) は、mysql がセミコロンを待機している可能性が高くなります。プロンプトの意味に気付かなければ、何をするべきか理解するまでに時間がかかるかもしれません。セミコロンを入力してステートメントを完了すると、mysql で実行されます。

mysql> SELECT USER() -> ;+---------------+
| USER() |
+---------------+
| jon@localhost |
+---------------+

'> および "> プロンプトは、文字列の収集中に発生します (MySQL が文字列の完了を待機しているという意味です)。MySQL では、' 文字または " 文字で囲んで文字列を記述でき (たとえば、'hello'"goodbye" など)、mysql では、複数行にわたって文字列を入力することができます。'> または "> プロンプトが表示される場合、それは ' または " 引用符で始まる文字列を含む行を入力したが、その文字列を終了させる対応する引用符をまだ入力していないことを意味します。このような場合、引用符の入力を忘れていることがよくあります。例:

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30; '>

この SELECT ステートメントを入力し、Enter を押して結果を待っても、何も起きません。このクエリーがなぜそれほど時間がかかるのかと不思議に思うのではなく、'> プロンプトで示される手がかりに注目してください。このプロンプトは、mysql が完了していない文字列の残りを待機していることを示しています。(このステートメントの間違いに気付きましたか。文字列 'Smith には、2 番目の一重引用符がありません。)

では、どうしますか。もっとも簡単なのは、コマンドを取り消すことです。ただし、この場合は単に \c と入力することはできません。なぜなら、mysql で収集中の文字列の一部とみなされるからです。代わりに、閉じる引用符を入力して (つまり、文字列が完了したことを mysql に認識させ)、次に \c と入力します。

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30; '> '\cmysql>

プロンプトが mysql> に戻り、mysql が新しいコマンドに対して準備できていることが示されます。

`> プロンプトは '> および "> プロンプトと同様ですが、逆引用符を開始したがまだ終了していないことを示しています。

'>">、および `> の各プロンプトが何を意味するかを理解することが重要です。なぜなら、誤って未終了の文字列を入力した場合、それ以降の入力行は QUIT を含む行も含めて、mysql に無視されるように見えるからです。特に、現在のコマンドを取り消すにはまず終端の引用符を入力する必要があることを理解していないと、これにはかなり困惑する可能性があります。

3.3 データベースの作成と使用

コマンドの入力方法を理解したら、データベースにアクセスすることができます。

何匹かのペット (menagerie、動物) を家で飼っているとします。これらのペットについてさまざまな種類の情報の記録をつける場合を考えます。これは、データを保持するためのテーブルを作成し、必要な情報をテーブルにロードすることで実現できます。次に、テーブルからデータを取り出すことで、ペットに関する各種の質問に回答できます。このセクションでは、次の操作の実行方法について説明します。

  • データベースを作成する

  • テーブルを作成する

  • テーブルにデータをロードする

  • さまざまな方法でテーブルからデータを取り出す

  • 複数のテーブルを使用する

menagerie データベースは意図的に単純になっていますが、類似のデータベースを使用するような実際の状況を考えることは難しくありません。たとえば、これに似たデータベースを使用して農場主が家畜の記録をつけたり、獣医が患者の記録をつけたりできます。以降のセクションで使用するいくつかのクエリーとサンプルデータを含む menagerie の配布を、MySQL の Web サイトから入手できます。圧縮 tar ファイル形式および Zip 形式の両方で、http://dev.mysql.com/doc/ で入手できます。

SHOW ステートメントを使用して、サーバーに現在どのようなデータベースが存在するかを調べます。

mysql> SHOW DATABASES;+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+

mysql データベースは、ユーザーのアクセス権限を記述します。test データベースは、多くの場合、ユーザーが各種の試行をするための作業スペースとして用意されています。

このステートメントで表示されるデータベースのリストは、マシンによって異なる場合があります。ユーザーが SHOW DATABASES 権限を持っていない場合、ユーザーにまったく権限のないデータベースは SHOW DATABASES で表示されません。セクション13.7.5.15「SHOW DATABASES 構文」を参照してください。

test データベースが存在する場合は、アクセスしてみます。

mysql> USE testDatabase changed

QUIT と同様に、USE にもセミコロンは必要ありません。(これらのステートメントの末尾にセミコロンを付加しても問題はありません。) USE ステートメントは、1 行に記述する必要があるという点でも特殊です。

test データベースへのアクセス権限を持っている場合は以降の例に使用できますが、そのデータベース内に作成した内容は、アクセス権限を持っているほかのユーザーによって削除される可能性があります。この理由から、自分専用のデータベースを使用する許可を MySQL 管理者に依頼するとよいでしょう。自分のデータベースに menagerie という名前を付けるとします。管理者は次のようなコマンドを実行する必要があります。

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

ここで、your_mysql_name はユーザーに割り当てられた MySQL ユーザー名、your_client_host はサーバーに接続するホストです。

3.3.1 データベースの作成と選択

管理者がユーザーのアクセス権を設定する際にユーザー用のデータベースを作成した場合、ユーザーはそのデータベースを使用し始めることができます。そうでない場合は、ユーザー自身で作成する必要があります。

mysql> CREATE DATABASE menagerie;

Unix の場合、SQL キーワードとは異なり、データベース名では大文字と小文字が区別されるため、このデータベースは常に menagerie と表記する必要があり、MenagerieMENAGERIE などの異なる表記は使用できません。これはテーブル名にも当てはまります。(Windows の場合、この制限は適用されませんが、データベースとテーブルの表記は 1 つのクエリー内では統一する必要があります。ただし、さまざまな理由から、データベースの作成時に使用した表記を常に使用することをお勧めします。)

注記

データベースの作成時に ERROR 1044 (42000): Access denied for user 'monty'@'localhost' to database 'menagerie' のようなエラーが表示される場合があります。これは、ユーザーのアカウントには必要な権限がないことを示しています。これについては管理者に問い合わせるか、セクション6.2「MySQL アクセス権限システム」を参照してください。

データベースを作成しても、そのデータベースは選択されません。使用するには明示的に選択する必要があります。menagerie を現在のデータベースにするには、次のコマンドを使用します。

mysql> USE menagerieDatabase changed

データベースの作成は 1 回だけ必要ですが、使用するには mysql セッションを開始するたびにデータベースを選択する必要があります。そのためには、例のように USE ステートメントを発行します。または、mysql を起動するときにコマンド行でデータベースを選択できます。必要な接続パラメータをすべて指定したあとに、データベースの名前を指定します。例:

shell> mysql -h host -u user -p menagerieEnter password: ********
重要

このコマンド内の menagerie はパスワードではありません。コマンド行で -p オプションのあとにパスワードを入力する場合は、間にスペースを入れずに入力する必要があります (たとえば、-p mypassword ではなく、-pmypassword)。ただし、コマンド行にパスワードを入力することは、同じマシンにログインしているほかのユーザーに覗き見られるおそれがあるため、お勧めできません。

注記

SELECTDATABASE() を使用すると、現在どのデータベースが選択されているかをいつでも調べることができます。

3.3.2 テーブルの作成

データベースの作成は簡単な部分ですが、SHOW TABLES が示すとおり、この時点では空です。

mysql> SHOW TABLES;Empty set (0.00 sec)

難しい部分は、データベースの構造、つまり、どのようなテーブルが必要で各テーブルにどのようなカラムを含めるかを決定することです。

各ペットの記録を格納するテーブルが必要です。このテーブルには、pet という名前を付けることができ、少なくとも各ペットの名前を含めるべきです。名前自体には深い意味はないため、このテーブルにはほかの情報も含めるとよいでしょう。たとえば、家族の複数のメンバーがペットを飼っている場合は、各ペットの所有者を記録することができます。種や性別などの基本的な説明も記録できます。

年齢はどうでしょうか。重要な情報ではありますが、データベースに格納するには適しません。年齢は時間の経過によって変化するため、記録を頻繁に更新する必要が生じます。代わりに、生年月日などの固定値を格納する方が適切です。そうしておけば、年齢が必要になったときに、現在の日付と生年月日の差として計算することができます。MySQL には日付演算を行う関数が用意されているため、これは難しくありません。年齢の代わりに生年月日を格納することには、ほかの利点もあります。

  • たとえば、ペットの誕生日が近づいたらリマインダを生成するといったタスクにデータベースを使用できます。(このようなクエリーには意味がないと感じる場合、現在の週や月で誕生祝いのメッセージを送信する必要のあるクライアントを特定するためにビジネスデータベースを使用する場合と、コンピュータ支援の接客という点で同じ問題であることに注目してください。)

  • 現在の日付ではなく、別の日付を基にして年齢を計算することもできます。たとえば、データベースに死亡日を格納すると、ペットが何歳で死んだかを簡単に計算できます。

pet テーブルに含めると役立ちそうな情報はほかにもあるでしょうが、ここまでに挙げた名前 (name)、所有者 (owner)、種 (species)、性別 (sex)、生年月日 (birth)、および死亡年月日 (death) で十分です。

CREATE TABLE ステートメントを使用して、テーブルのレイアウトを指定します。

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

nameowner、および species の各カラムではカラム値の長さが変化するため、これらのカラムに VARCHAR を選択することは適切です。これらのカラム定義の長さは、すべて同じである必要はなく、20 である必要もありません。通常は、1 から 65535 までの範囲で、もっとも適切と思われる任意の長さを選択できます。選択が適切でなく、あとでより長いフィールドが必要になった場合は、MySQL の ALTER TABLE ステートメントを利用できます。

ペットのレコードで性別を表す値としては、'm''f'、または 'male''female' など、いくつかの種類を選択できます。1 文字の 'm''f' を使用するのがもっとも簡単です。

birth カラムと death カラムに DATE データ型を使用することはかなり明白な選択です。

テーブルを作成したあとは、SHOW TABLES で何か出力されるはずです。

mysql> SHOW TABLES;+---------------------+
| Tables in menagerie |
+---------------------+
| 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 | |
+---------+-------------+------+-----+---------+-------+

DESCRIBE ステートメントは、テーブル内のカラムの名前や型を忘れた場合などに、いつでも使用できます。

MySQL のデータ型に関する詳細は、第11章「データ型を参照してください。

3.3.3 テーブルへのデータのロード

テーブルを作成したら、データを移入する必要があります。これには、LOAD DATA ステートメントと INSERT ステートメントが役立ちます。

ペットのレコードを次のように記述できると仮定します。(MySQL では、日付の形式は 'YYYY-MM-DD' と想定されています。これはユーザーが通常使用する形式とは異なる場合があります。)

nameownerspeciessexbirthdeath
FluffyHaroldcatf1993-02-04 
ClawsGwencatm1994-03-17 
BuffyHarolddogf1989-05-13 
FangBennydogm1990-08-27 
BowserDianedogm1979-08-311995-07-29
ChirpyGwenbirdf1998-09-11 
WhistlerGwenbird 1997-12-09 
SlimBennysnakem1996-04-29 

空のテーブルから始めているため、データを移入する簡単な方法は、各ペットに対応する行を記述したテキストファイルを作成してから、1 つのステートメントでそのファイルの内容をテーブルにロードすることです。

たとえば、テキストファイル pet.txt を作成し、1 行に 1 レコードを記述します。値は、CREATE TABLE ステートメントに指定したカラムの順序に従い、タブで区切って指定します。性別が不明な場合やまだ生きているペットの死亡年月日など、不足している値には NULL 値を使用できます。テキストファイルでこれらを表現するには、\N (バックスラッシュと大文字の N) を使用します。たとえば、Whistler という鳥のレコードは次のようになります (値の間の空白は 1 つのタブ文字です)。

Whistler Gwen bird \N 1997-12-09 \N

テキストファイル pet.txtpet テーブルにロードするには、次のステートメントを使用します。

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

このファイルを Windows で作成した場合、作成に使用したエディタで \r\n が行ターミネータとして使用されているときは、代わりに次のステートメントを使用します。

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet -> LINES TERMINATED BY '\r\n';

(OS X を実行している Apple マシンでは、LINES TERMINATED BY '\r' を使用するとよいでしょう。)

カラム値の区切り文字と行末マーカーは、必要に応じて LOAD DATA ステートメントで明示的に指定できますが、デフォルトではタブと改行です。ステートメントで pet.txt ファイルを正しく読み取るにはこれで十分です。

ステートメントが失敗する場合、使用している MySQL インストールではローカルファイル機能がデフォルトで有効になっていない可能性があります。これを変更する方法については、セクション6.1.6「LOAD DATA LOCAL のセキュリティーの問題」を参照してください。

新しいレコードを 1 つずつ追加する場合は、INSERT ステートメントが役立ちます。もっとも単純な形式では、CREATE TABLE ステートメントに指定したカラムの順序に従って、各カラムの値を入力します。Diane が、Puffball という名前の新しいハムスターを手に入れたとします。次のように、INSERT ステートメントを使用して新しいレコードを追加できます。

mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

ここでは、文字値と日付値を、引用符付きの文字列で指定しています。また、INSERT では、不足している値を表す NULL を直接挿入することができます。LOAD DATA の場合のように \N を使用することはありません。

この例からわかるとおり、初期レコードをロードするために複数の INSERT ステートメントを使用すると、1 つの LOAD DATA ステートメントを使用する場合よりもかなり多くの入力が必要になります。

3.3.4 テーブルからの情報の取り出し

テーブルから情報を取り出すには、SELECT ステートメントを使用します。このステートメントの一般的な形式は次のとおりです。

SELECT what_to_selectFROM which_tableWHERE conditions_to_satisfy;

what_to_select は取得する対象です。これには、カラムのリストか、すべてのカラムを表す * を指定できます。which_table は、データを取り出すテーブルです。WHERE 句はオプションです。指定する場合は、取得対象となる行の条件を 1 つまたは複数 conditions_to_satisfy に指定します。

3.3.4.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 のこの形式は、たとえばテーブルに初期データセットをロードした直後など、テーブル全体を取り出すときに役立ちます。たとえば、Bowser の生年月日が正しくないようだと気付いたとします。血統書の原本を確認すると、正しい生年は 1979 年ではなく 1989 年であるとわかりました。

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

  • ファイル pet.txt を編集して間違いを修正したあと、DELETELOAD 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 は該当するレコードだけを変更するため、テーブルをリロードする必要はありません。

3.3.4.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' などを使用できます。クエリーの結果は同じになります。

name だけでなく、任意のカラムに条件を指定できます。たとえば、1998 年以降に生まれたペットを調べるには、birth カラムを検査します。

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

ANDOR は一緒に使用できますが、ANDOR よりも高い優先順位を持っています。両方の演算子を一緒に使用する場合は、括弧を使用して条件の組み合わせ方を明示的に示すことをお勧めします。

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.3.4.3 特定のカラムの選択

テーブルの行全体ではなく特定のカラムを取得するには、カラムの名前をカンマで区切って指定します。たとえば、ペットの生年月日を調べるには、name カラムと birth カラムを選択します。

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

このクエリーは各レコードから owner カラムを取り出すだけなため、一部は複数回出現しています。出力を最小化するには、キーワード DISTINCT を追加して、一意の出力レコードをそれぞれ 1 回だけ取り出すようにします。

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

3.3.4.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 キーワードはその直前のカラム名 (birth) だけに適用されます。species カラムのソート順序には影響を与えません。

3.3.4.5 日付の計算

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

それぞれのペットが何歳なのかを判別するには、TIMESTAMPDIFF() 関数を使用します。引数は、結果を表現する単位、および差を求める 2 つの日付です。次のクエリーでは、各ペットの生年月日、現在の日付、および年齢が表示されます。出力の最後のカラムラベルに意味を持たせるために、エイリアス (age) が使用されています。

mysql> SELECT name, birth, CURDATE(), -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) 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 |
+----------+------------+------------+------+

このクエリーは正しく動作しますが、結果の行を何らかの順序で表示すると確認しやすくなるでしょう。そのためには、ORDER BY name 句を追加することで、出力を名前でソートできます。

mysql> SELECT name, birth, CURDATE(), -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) 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 |
+----------+------------+------------+------+

出力を name ではなく age でソートするには、異なる ORDER BY 句を使用します。

mysql> SELECT name, birth, CURDATE(), -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) 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, -> TIMESTAMPDIFF(YEAR,birth,death) 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 <> NULL ではなく death IS NOT NULL を使用します。NULL は通常の比較演算子を使用して比較することができない特殊な値であるためです。これについてはあとで説明します。セクション3.3.4.6「NULL 値の操作」を参照してください。

来月誕生日を迎えるペットを調べるにはどうしますか。このような計算の場合、年と日は無関係で、birth カラムの月の部分を抽出するだけで済みます。MySQL には、YEAR()MONTH()DAYOFMONTH() など、日付の一部を抽出する関数がいくつか用意されています。ここでは MONTH() 関数が適しています。動作の仕組みを確認するために、birthMONTH(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 月の場合は多少複雑になります。月の番号 (12) に単に 1 を加算して 13 月に生まれたペットを探すということはできません。そのような月は存在しないからです。代わりに、1 月 (月 1) に生まれたペットを探します。

現在が何月であっても機能するクエリーを記述すると、特定の月の番号を使用する必要がなくなります。DATE_ADD() を使用すると、所定の日付に時間間隔を加算できます。CURDATE() の値に 1 か月を加算してから、月の部分を MONTH() で抽出すると、誕生日を調べる月が得られます。

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

現在の月の値が 12 の場合はモジュロ関数 (MOD) を適用して 0 に折り返してから、1 を加算する方法でも、同じタスクを達成できます。

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

MONTH()1 から 12 までの数値を返します。また、MOD(something,12)0 から 11 までの数値を返します。したがって、MOD() のあとで加算を行わないと、11 月から 1 月に進んでしまいます。

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

=<、または <> などの算術比較演算子を使用して NULL をテストすることはできません。これを自分で確認するために、次のクエリーを実行してみてください。

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+

NULL に関する算術比較は、結果もすべて NULL になるため、このような比較から意味のある結果を得ることはできません。

MySQL では、0NULL は false を意味し、それ以外はすべて true を意味します。ブール演算のデフォルトの真理値は 1 です。

NULL がこのように特殊な方法で扱われているため、前のセクションで、どの動物がもう生きていいないのかを判断するために、death <> NULL ではなく death IS NOT NULL を使用することが必要だったのです。

GROUP BY では、2 つの NULL 値は等しいとみなされます。

ORDER BY を実行する場合、NULL 値は ORDER BY ... ASC では最初に表示され、ORDER BY ... DESC では最後に表示されます。

NULL を操作するときによくある間違いは、NOT NULL と定義されたカラムにはゼロや空の文字列は挿入できないと想定することです。これらは実際に値ですが、一方 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 であるため、NOT NULL カラムに挿入することができます。セクションB.5.5.3「NULL 値に関する問題」を参照してください。

3.3.4.7 パターンマッチング

MySQL では、標準の SQL パターンマッチングに加え、vigrepsed などの Unix ユーティリティーで使用されるものに似た拡張正規表現に基づくパターンマッチング形式が提供されています。

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 文字の名前を探すには、_パターン文字の 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 で提供されているもう 1 種類のパターンマッチングは、拡張正規表現を使用します。このタイプのパターンについて一致をテストする場合は、REGEXP 演算子と NOT REGEXP 演算子 (またはシノニムである RLIKENOT RLIKE) を使用します。

次の表に、拡張正規表現の特徴の一部を示します。

  • .は任意の 1 文字に一致します。

  • 文字クラス[...]は、括弧内のいずれかの文字に一致します。たとえば、[abc]ab、またはcに一致します。文字の範囲を指定するには、ダッシュを使用します。[a-z]は任意の英字に一致し、[0-9]は任意の数字に一致します。

  • *は直前の文字の 0 個以上のインスタンスに一致します。たとえば、x*は任意の数のx文字に一致し、[0-9]*は任意の数の数字に一致し、.*は任意の数の任意の文字に一致します。

  • REGEXP パターンマッチングは、テストする値のいずれかの部分にパターンが一致すれば成功です。(これとは異なり、LIKE パターンマッチングは、パターンが値全体に一致する場合のみ成功です。)

  • テストする値の先頭または末尾にパターンが一致するように指定するには、パターンの先頭に^またはパターンの末尾に$を使用します。

拡張正規表現動作の仕組みを確認するために、前出の LIKE クエリーを REGEXP で書き直したものを次に示します。

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 キーワードを使用して、文字列の 1 つをバイナリ文字列にします。次のクエリーは、名前の先頭にある小文字の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 |
+-------+--------+---------+------+------------+-------+

セクション12.5.2「正規表現」で、正規表現の構文の詳細について説明しています。

3.3.4.8 行のカウント

データベースは、テーブルの中に、特定のタイプのデータがどの程度の頻度で現れるかという質問に答えるために使用されることがよくあります。たとえば、何匹ペットを飼っているのか、それぞれの所有者が何匹のペットを所有しているかを調べたり、または動物に対してさまざまな個体数調査を実施したりすることがあるでしょう。

ペットの総数をカウントすることは、pet テーブルには何行あるかという質問と同等です。このテーブルにはペットごとに 1 つのレコードが存在するからです。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 のすべてのレコードをグループ化しています。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 |
+---------+------+----------+

COUNT() 値に加え、選択するカラムを指定する場合は、それらのカラムを GROUP BY 句で指定する必要があります。そうでない場合は、次のようになります。

  • ONLY_FULL_GROUP_BY SQL モードが有効である場合は、エラーが発生します。

    mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';Query OK, 0 rows affected (0.00 sec)
    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
  • ONLY_FULL_GROUP_BY が有効でない場合、このクエリーはすべての行を 1 つのグループとみなして処理されますが、指定した各カラムに選択される値は不確定です。サーバーによって任意の行の値が自由に選択されます。

    mysql> SET sql_mode = '';Query OK, 0 rows affected (0.00 sec)
    mysql> SELECT owner, COUNT(*) FROM pet;+--------+----------+
    | owner | COUNT(*) |
    +--------+----------+
    | Harold | 8 |
    +--------+----------+
    1 row in set (0.00 sec)

セクション12.19.3「MySQL での GROUP BY の処理」も参照してください。

3.3.4.9 複数のテーブルの使用

pet テーブルはペットの記録を保持します。獣医の診察や出産といったペットの生涯におけるイベントなど、ペットに関するほかの情報を記録するには、別のテーブルが必要です。このテーブルはどのようなものにしたらよいでしょうか。次の情報を含める必要があります。

  • 各イベントがどのペットに関するものかを示すためのペット名。

  • イベントがいつ発生したかを示す日付。

  • イベントを説明するフィールド。

  • イベントを分類できるようにする場合は、イベントタイプのフィールド。

これらを考慮すると、event テーブルの CREATE TABLE ステートメントは次のようになります。

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

pet テーブルの場合と同様に、初期レコードをロードするもっとも簡単な方法として、次の情報を記述したタブ区切りのテキストファイルを作成します。

namedatetyperemark
Fluffy1995-05-15litter4 kittens, 3 female, 1 male
Buffy1993-06-23litter5 puppies, 2 female, 3 male
Buffy1994-06-19litter3 puppies, 3 female
Chirpy1999-03-21vetneeded beak straightened
Slim1997-08-03vetbroken rib
Bowser1991-10-12kennel 
Fang1991-10-12kennel 
Fang1998-08-28birthdayGave him a new chew toy
Claws1998-03-17birthdayGave him a new flea collar
Whistler1998-12-09birthdayFirst birthday

次のようにレコードをロードします。

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

pet テーブルで実行したクエリーから学んだことを基にすれば、原則は同じであるため event テーブルのレコードも取得できるはずです。ただし、event テーブルだけでは質問に回答できない場合はどのようなときでしょうか。

各ペットの出産時の年齢を調べるとします。前に、2 つの日付から年齢を計算する方法を学びました。ペットの出産日は event テーブルにありますが、その日付での年齢を計算するには生年月日が必要で、それは pet テーブルにあります。したがって、このクエリーには両方のテーブルが必要です。

mysql> SELECT pet.name, -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, -> remark -> FROM pet INNER JOIN event ->  ON pet.name = event.name -> WHERE event.type = 'litter';+--------+------+-----------------------------+
| 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 句で 2 つのテーブルを結合しています。

  • 複数のテーブルの情報を組み合わせる (結合する) 場合、1 つのテーブルのレコードとほかのテーブルのレコードがどのように対応するかを指定する必要があります。両方のテーブルに name カラムがあるため、これは簡単です。このクエリーは、ON 句を使用して、2 つのテーブルのレコードを name 値に基づいて対応させています。

    このクエリーは INNER JOIN を使用してテーブルを結合しています。INNER JOIN では、ON 句で指定された条件を両方のテーブルが満たす場合にかぎって、結果にテーブルの行が許可されます。この例では、pet テーブルの name カラムと event テーブルの name カラムが一致する必要があると ON 句で指定しています。名前が一方のテーブルにあって他方にはない場合、ON 句の条件が満たされないため、その行は結果に表示されません。

  • name カラムは両方のテーブルにあるため、このカラムを参照するときはどちらのテーブルのものかを明確に示す必要があります。そのためには、カラム名の前にテーブル名を付加します。

2 つの異なるテーブルでなくても結合は実行できます。テーブル内のレコードをその同じテーブル内のほかのレコードと比較する場合に、テーブルをそれ自体に結合すると役立つことがあります。たとえば、繁殖のつがいにするペットを選ぶ場合、pet テーブルをそれ自体に結合して、同種の雄と雌のつがい候補を生成できます。

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -> FROM pet AS p1 INNER JOIN pet AS p2 ->  ON 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 |
+--------+------+--------+------+---------+

このクエリーでは、テーブル名のエイリアスを指定してカラムを参照し、各カラムがテーブルのどちらのインスタンスに関連するかを必ず明確にしています。

3.4 データベースとテーブルに関する情報の取得

データベースやテーブルの名前を忘れた場合や、特定のテーブルの構造 (カラムの名前など) を忘れた場合はどうしますか。MySQL では、この問題に対処するために、サポートしているデータベースとテーブルについて情報を提供するステートメントがいくつか用意されています。

前出の SHOW DATABASES は、サーバーで管理されているデータベースのリストを表示します。現在どのデータベースが選択されているかを調べるには、DATABASE() 関数を使用します。

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

まだどのデータベースも選択していない場合、結果は NULL になります。

テーブルの名前がはっきりしない場合などに、デフォルトのデータベースにどのようなテーブルが含まれているかを調べるには、次のコマンドを使用します。

mysql> SHOW TABLES;+---------------------+
| Tables_in_menagerie |
+---------------------+
| event |
| pet |
+---------------------+

このステートメントで生成される出力のカラム名は常に Tables_in_db_name になります。ここで、db_name はデータベースの名前です。詳細については、セクション13.7.5.38「SHOW TABLES 構文」を参照してください。

テーブルの構造を知りたい場合は、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 はカラムのデフォルト値を示します。Extra には、カラムに関する特殊な情報が表示されます。カラムが AUTO_INCREMENT オプションで作成された場合、値は空ではなく auto_increment になります。

DESCDESCRIBE の省略形式です。詳細については、セクション13.8.1「DESCRIBE 構文」を参照してください。

既存のテーブルを作成するために必要な CREATE TABLE ステートメントを、SHOW CREATE TABLE ステートメントを使用して取得できます。セクション13.7.5.12「SHOW CREATE TABLE 構文」を参照してください。

テーブルにインデックスが設定されている場合は、SHOW INDEX FROM tbl_name でその情報を表示できます。このステートメントの詳細については、セクション13.7.5.23「SHOW INDEX 構文」を参照してください。

3.5 バッチモードでの MySQL の使用

前のセクションでは、mysql をインタラクティブに使用してクエリーを入力し、結果を表示しました。mysql をバッチモードで実行することもできます。そのためには、実行するコマンドをファイルに記述し、そのファイルから入力を読み取るように mysql に指示します。

shell> mysql < batch-file

mysql を Windows で実行する場合に、ファイル内の一部の特殊文字によって問題が発生するときは、次のように実行できます。

C:\> mysql -e "source batch-file"

コマンド行で接続パラメータを指定する必要がある場合、コマンドは次のようになります。

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

この方法で mysql を使用する場合は、スクリプトファイルを作成してから、そのスクリプトを実行することになります。

スクリプト内の一部のステートメントでエラーが発生してもスクリプトを続行する場合は、--force コマンド行オプションを使用します。

なぜスクリプトを使用するのでしょうか。いくつかの理由を次に示します。

  • クエリーを繰り返し実行する場合 (毎日、毎週など)、スクリプトにすると、実行するたびに入力し直す必要がなくなります。

  • 既存のクエリーのスクリプトファイルをコピーして編集することによって、類似の新しいクエリーを作成できます。

  • バッチモードはクエリーの開発時にも役立ちます。特に、複数行にわたるコマンドまたは複数ステートメントによるコマンドシーケンスを使用する場合に便利です。間違いがあっても、すべてを入力し直す必要はありません。スクリプトを編集して間違いを修正してから、mysql で再度実行するだけで済みます。

  • 多量の出力を生成するクエリーの場合、画面でスクロールアップする出力を見る代わりに、pager を介して出力できます。

    shell> mysql < batch-file | more
  • あとで処理できるように出力をファイルに取り込むことができます。

    shell> mysql < batch-file > mysql.out
  • スクリプトを配布すると、ほかのユーザーも同じコマンドを実行できます。

  • cron ジョブからクエリーを実行する場合など、インタラクティブには使用できないことがあります。この場合はバッチモードを使用する必要があります。

mysql をバッチモードで実行したときのデフォルトの出力形式は、インタラクティブに使用した場合とは異なり、より簡潔になります。たとえば、mysql をインタラクティブに実行すると、SELECT DISTINCT species FROM pet の出力は次のようになります。

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

これに対し、バッチモードの出力は次のようになります。

species
bird
cat
dog
hamster
snake

バッチモードで、インタラクティブ出力形式のデータを取得するには、mysql -t を使用します。実行したコマンドを出力にエコーするには、mysql -vvv を使用します。

source コマンドまたは \. コマンドを使用すると、mysql プロンプトからでもスクリプトを使用できます。

mysql> source filename;mysql> \. filename

詳細については、セクション4.5.1.5「テキストファイルから SQL ステートメントを実行する」を参照してください。

3.6 一般的なクエリーの例

ここでは、MySQL に関する一般的な問題を解決する方法の例を示します。

一部の例では、テーブル shop を使用します。このテーブルには、業者 (ディーラー) の物品 (品番) ごとの価格が格納されます。各業者は物品ごとに 1 つの定価を付けていると仮定すると、(article, dealer) がレコードの主キーになります。

コマンド行ツール mysql を起動し、データベースを選択します。

shell> mysql your-database-name

(ほとんどの MySQL インストールで、test というデータベースを使用できます)。

次のステートメントを実行すると、テーブルを作成し、データを移入できます。

CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45), (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

これらのステートメントを発行したあと、テーブルには次の内容が格納されています。

SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+

3.6.1 カラムの最大値

もっとも大きい品番は?

SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+

3.6.2 特定のカラムの最大値が格納されている行

タスク: もっとも高価な物品の品番、業者、および価格を調べます。

これはサブクエリーを使用して簡単に実行できます。

SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+

ほかにも、LEFT JOIN を使用する方法や、すべての行を価格の降順でソートしてから MySQL 固有の LIMIT 句を使用して最初の行だけを取得する方法もあります。

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
注記

最高価格のものが複数あり、価格が 19.95 の場合、LIMIT を使用した方法では、その中の 1 つしか取得できません。

3.6.3 グループごとのカラムの最大値

タスク: 物品ごとの最高値を調べます。

SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+

3.6.4 特定のカラムのグループごとの最大値が格納されている行

タスク: 物品ごとに最高値を付けている業者 (複数可) を調べます。

この問題は、次のようなサブクエリーを使用して解決できます。

SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+

この例では相関サブクエリーを使用していますが、これは十分でない場合があります (セクション13.2.10.7「相関サブクエリー」を参照してください)。この問題を解決する別の方法は、FROM 句または LEFT JOIN で非相関サブクエリーを使用することです。

非相関サブクエリー

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price;

LEFT JOIN:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

LEFT JOIN の動作は、s1.price が最大値を取るときに、それより大きい値の s2.price は存在せず、s2 行の値は NULL になることに基づいています。セクション13.2.9.2「JOIN 構文」を参照してください。

3.6.5 ユーザー定義の変数の使用

MySQL ユーザー変数を使用すると、クライアント側で一時変数を使用せずに結果を記憶することができます。(セクション9.4「ユーザー定義変数」を参照してください。)

たとえば、最高値および最安値が付けられている物品を取得するには、次を実行します。

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
注記

また、テーブルやカラムといったデータベースオブジェクトの名前をユーザー変数に格納してから、この変数を SQL ステートメントで使用することもできます。ただし、これにはプリペアドステートメントを使用する必要があります。詳細は、セクション13.5「準備済みステートメントのための SQL 構文」を参照してください。

3.6.6 外部キーの使用

MySQL では、InnoDB テーブルで外部キー制約の確認をサポートしています。第14章「InnoDB ストレージエンジンセクション1.8.2.4「外部キーの違い」を参照してください。

2 つのテーブルを結合するだけの場合は、外部キー制約は必要ありません。InnoDB 以外のストレージエンジンの場合、カラムを定義するときに REFERENCES tbl_name(col_name) 句を使用できます。これは実際の効果はありませんが、現在定義しようとしているカラムが別のテーブルのカラムを参照する予定であるという自分のメモまたはコメントとして役立ちます。この構文を使用するときは、次の点を理解しておくことが非常に重要です。

  • MySQL は、col_name が実際に tbl_name に存在するか (また、その tbl_name 自体が存在するか) を確認するためのどのような CHECK も実行しません。

  • MySQL は、tbl_name に対してどのようなアクションも実行しません。たとえば、定義しようとしているテーブルの行に実行されたアクションに対応して行を削除することなどはありません。つまり、この構文にはどのような ON DELETE 動作や ON UPDATE 動作もありません。(REFERENCES 句の一部として ON DELETE 句や ON UPDATE 句を記述することはできますが、これらも無視されます。)

  • この構文はカラムを作成します。どのようなインデックスやキーも作成しません

このように作成したカラムを、次のように結合カラムとして使用できます。

CREATE TABLE person ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(60) NOT NULL, PRIMARY KEY (id)
);
CREATE TABLE shirt ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, style ENUM('t-shirt', 'polo', 'dress') NOT NULL, color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL, owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id), PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM person p INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' AND s.color <> 'white';
+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+

この方法で使用する場合、REFERENCES 句は SHOW CREATE TABLEDESCRIBE の出力に表示されません。

SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

REFERENCES をこのようにカラム定義のコメントまたはリマインダとして使用する方法は、MyISAM テーブルで機能します。

3.6.7 2 つのキーを使用した検索

1 つのキーを使用した OR の処理は、AND の処理と同様にかなり最適化されています。

注意が必要なのは、OR で結合された 2 つの異なるキーを使用して検索する場合です。

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1'

この場合は最適化されています。セクション8.2.1.4「インデックスマージの最適化」を参照してください。

2 つの異なる SELECT ステートメントの出力を結合する UNION を使用することでも、この問題を効率的に解決できます。セクション13.2.9.4「UNION 構文」を参照してください。

SELECT は 1 つのキーだけを検索するため、最適化できます。

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';

3.6.8 日ごとの訪問数の計算

ビットグループ関数を使用して、あるユーザーが Web ページを訪問した月ごとの日数を計算する方法の例を次に示します。

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23);

このテーブルには、ユーザーがページを訪問した日付を表す年月日の値が格納されています。月ごとの訪問日数を取得するには、次のクエリーを実行します。

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;

次の結果が表示されます。

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 | 01 | 3 |
| 2000 | 02 | 2 |
+------+-------+------+

このクエリーでは、年と月の組み合わせに対して異なる日付が何回テーブルに出現するかを、自動的に重複エントリを除去することによって計算しています。

3.6.9 AUTO_INCREMENT の使用

AUTO_INCREMENT 属性を使用すると、新しい行に一意の識別子を生成できます。

CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich');
SELECT * FROM animals;

次の結果が表示されます。

+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+

AUTO_INCREMENT カラムには値が指定されなかったため、MySQL が自動的にシーケンス番号を割り当てました。カラムに明示的に 0 を割り当ててシーケンス番号を生成することもできます。カラムが NOT NULL と宣言されている場合は、NULL 割り当ててシーケンス番号を生成することもできます。

SQL 関数 LAST_INSERT_ID() または C API 関数 mysql_insert_id() を使用すると、最後に生成した AUTO_INCREMENT の値を取得できます。これらの関数は接続に固有の関数であるため、別の接続が同様に挿入を実行していても、戻り値は影響を受けません。

AUTO_INCREMENT カラムには、必要な最大のシーケンス値を保持するのに十分な大きさを持つ最小の整数データ型を使用します。カラムがデータ型の上限値に到達すると、次にシーケンス番号を生成しようとしたときには失敗します。可能であれば、より広い範囲を可能にするために UNSIGNED 属性を使用します。たとえば、TINYINT を使用する場合、許可される最大のシーケンス番号は 127 です。TINYINT UNSIGNED の場合は最大値は 255 です。すべての整数型の範囲は、セクション11.2.1「整数型 (真数値) - INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT」を参照してください。

注記

複数行を同時に挿入する場合、LAST_INSERT_ID()mysql_insert_id() は、実際には最初に挿入した行の AUTO_INCREMENT キーを返します。これにより、レプリケーションセットアップで複数行の挿入を別のサーバーで正しく再現できます。

1 以外の AUTO_INCREMENT 値で開始するには、次のように、その値を CREATE TABLE または ALTER TABLE でセットします。

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

InnoDB の注意

InnoDB テーブルでは、一連の INSERT ステートメントの途中で自動インクリメント値を含むカラムを修正する場合は注意が必要です。たとえば、UPDATE ステートメントを使用して、自動インクリメントカラムに新しくより大きい値を入れると、後続の INSERT重複エラーになる場合があります。DELETE を実行したあとでさらに INSERT ステートメントが続く場合、またはトランザクションを COMMIT したが UPDATE ステートメントのあとではない場合に、自動インクリメント値がすでに存在するかどうかのテストが行われます。

MyISAM の注意

  • MyISAM テーブルには、マルチカラムインデックス内のセカンダリカラムに AUTO_INCREMENT を指定することができます。この場合、AUTO_INCREMENT カラムに生成される値は、MAX(auto_increment_column) + 1 WHERE prefix=given-prefix として計算されます。これは、データを順序付きのグループに分割する場合に便利です。

    CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id)
    ) ENGINE=MyISAM;
    INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich');
    SELECT * FROM animals ORDER BY grp,id;

    次の結果が表示されます。

    +--------+----+---------+
    | grp | id | name |
    +--------+----+---------+
    | fish | 1 | lax |
    | mammal | 1 | dog |
    | mammal | 2 | cat |
    | mammal | 3 | whale |
    | bird | 1 | penguin |
    | bird | 2 | ostrich |
    +--------+----+---------+

    この場合 (AUTO_INCREMENT カラムがマルチカラムインデックスの一部として使用されている場合)、グループ内で最大の AUTO_INCREMENT 値を持つ行を削除すると、そのグループで同じ AUTO_INCREMENT 値が再使用されることになります。これは、通常は AUTO_INCREMENT 値が再使用されることのない MyISAM テーブルの場合にも発生します。

  • AUTO_INCREMENT カラムが複合インデックスの一部である場合、MySQL は AUTO_INCREMENT カラムで始まるインデックスを使用してシーケンス値を生成します (ある場合)。たとえば、animals テーブルにインデックス PRIMARY KEY (grp, id)INDEX (id) が含まれている場合、MySQL はシーケンス値の生成で PRIMARY KEY を無視します。その結果、テーブルには grp 値ごとに 1 つのシーケンスではなく、単一のシーケンスが含まれることになります。

参照情報

AUTO_INCREMENT に関する詳細の参照先を次に示します。

3.7 Apache での MySQL の使用

MySQL データベースを使用してユーザーを認証し、ログファイルを MySQL のテーブルに書き込むプログラムがあります。

Apache 構成ファイルに次を追加することで、MySQL に簡単に読み込めるように Apache のロギング形式を変更することができます。

LogFormat \ "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\", \ \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

この形式のログファイルを MySQL にロードするには、次のようなステートメントを使用します。

LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_nameFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

LogFormat 行がログファイルに書き込むデータに対応して、指定するテーブルのカラムを作成する必要があります。

関連キーワード:  テーブル,カラム,FROM,birth,データベース,ステートメント,クエリー,species,ペット,dog