「データベースが使えるようになりたい」と言われたので書いた
ある日うちの奥さん（エンジニアではない）から、「データベースが使えるようになりたいから教えてほしい」と相談を受けた。
一口にデータベースと言ってもいくつかあるので詳細を聞いてみると、彼女が意欲を示している「データベース」とは MySQL や PostgreSQL のこと*1で、今の職場に残されたレガシーなデータベースから古い情報を取り出すのに使うとのことだった。MySQL や PostgreSQL などのデータベース*2にはいくつかの機能があるが、情報の取り出しだけができればとりあえずは良いらしい。とはいえ「データベース使えますよ」と自信を持って言うために、情報の追加や更新、削除もできるようになっておきたい、ということだった。
そこで、そもそもデータベースはどんなもので、どういうことができるのか、どうすればできるのかを以下に簡単にまとめてみる。以下は奥さん向けに書いたものなので、普段エンジニア以外が耳慣れない言葉などはなるべく一度説明してから登場させるか、一度並べてから説明を後述することにする。ではいってみよう。
- 使う側にとっての「データベース」って何だろう
- 使うための用意
- 管理人と話そう
- 建物に入ろう
- 建物から帰ろう
- 建物の中でできること
- SQLコマンド
- MySQLコマンド
- コマンドを知る前に
- コマンドの説明をする前に本棚を作ろう
- コマンドを知ろう
- 定義済みのデータベースを一覧取得する（SHOW DATABASES）
- 操作したいデータベースを選ぶ（USE データベース名）
- 定義済みのテーブルを一覧取得する（SHOW TABLES）
- 定義済みのテーブルひとつを確認する（DESC テーブル名）
- テーブルの中から全カラムの情報を取得する（SELECT * FROM テーブル名）
- テーブルの中から特定のカラムの情報を取得する（SELECT カラム名 FROM テーブル名）
- テーブルの中から指定件数のデータを取得する（SELECT カラム名 FROM テーブル名 LIMIT 件数）
- テーブルの中から指定条件に完全一致するデータを取得する（SELECT カラム名 FROM テーブル名 WHERE カラム名 = '合致させたい内容'）
- テーブルの中から指定条件に部分一致するデータを取得する（SELECT カラム名 FROM テーブル名 WHERE カラム名 LIKE '合致させたい内容'）
- テーブルの中から複数条件のすべてに一致するデータを取得する（SELECT カラム名 FROM テーブル名 WHERE 条件 AND 条件）
- テーブルの中から複数条件のいずれかまたは両方に一致するデータを取得する（SELECT カラム名 FROM テーブル名 WHERE 条件 OR 条件）
- テーブルの取得結果をカラムの内容で並び替える（SELECT カラム名 FROM テーブル名 ORDER BY カラム名 昇順または降順）
- テーブルの取得条件に以上、以下を使う（SELECT カラム名 FROM テーブル名 WHERE カラム名 >= 数値 OR カラム名 <= 数値）
- テーブルの全件数を取得する（SELECT COUNT(*) FROM テーブル名）
- テーブルの内容を特定のカラムの内容でグループ分けする（SELECT * FROM テーブル名 GROUP BY カラム名）
- 指定されたカラムの最大値、最小値を取得する（SELECT MAX(カラム名), MIN(カラム名) FROM テーブル名 GROUP BY カラム名）
- 指定されたカラムに登録されている内容の合計値を取得する（SELECT SUM(カラム名) FROM テーブル名）
- 指定されたカラムに登録されている内容の文字数を取得する（SELECT LENGTH(カラム名) FROM テーブル名）
- 指定されたカラムに登録されている内容を重複を取り除いて表示する（SELECT DISTINCT カラム名 FROM テーブル名）
- 複数のカラムの内容や文字列を結合して結果に含める（SELECT CONCAT(文字列やカラム名, 文字列やカラム名, ...) FROM テーブル名）
- 取得結果に別名をつける（SELECT 取得結果 AS 別名）
- ふたつのテーブルを組み合わせて結果を取得する
- テーブルにデータを追加する（INSERT INTO テーブル名 ( カラム名 ) VALUES ( データ )）
- テーブルのデータを更新する（UPDATE テーブル名 SET カラム名 = 変更内容 WHERE 条件）
- データを削除する（DELETE FROM テーブル名 WHERE 条件）
- データベースの定義（CREATE DATABASE データベース名）
- データベース定義文の確認（SHOW CREATE DATABASE データベース名）
- データベースの削除（DROP DATABASE データベース名）
- テーブルの定義（CREATE TABLE テーブル名...）
- テーブルの定義文を確認する（SHOW CREATE TABLE テーブル名）
- テーブルの削除（DROP TABLE テーブル名）
- テーブルにカラムを追加する（ALTER TABLE テーブル名 ADD COLUMN カラム名 カラム設定）
- テーブルからカラムを削除する（ALTER TABLE テーブル名 DROP COLUMN カラム名）
- コマンドを入力していて起こりがちなトラブル
- 終わりに
使う側にとっての「データベース」って何だろう
データベースの用途を一言で表すなら、「情報を取り出しやすいように整理して置いておける場所」*3だと思う。言わば、たくさん本をしまっておいてすぐに取り出すことのできるとても便利な本棚と、その本棚から本を取ってきてくれる超素早い管理人がセットになったようなもの。
データベースを構成するもの
- 本棚
- 管理人（めちゃくちゃ素早い。本棚の目録を持っている）
※ 目録がなかったので代わりに金棒になりました。
「データベースってエクセルとかスプレッドシートと同じようなもんなんじゃないの」という話を聞くことがあるのは、その発言をする人がExcelやGoogle Spreadsheetsを「情報を取り出しやすいように整理して置いておける場所」として使っているからで、要するにその人にとっての用途が同じだから「同じようなもん」だと感じるのだろうと思う。ExcelやGoogle Spreadsheetsでできるように、「検索」機能を使って文字の部分一致検索をしたり、特定のセルの内容に一致する他のシートの行を引っ張ってきたりということは、データベースでもできる。
使うための用意
データベースを使うためには、以下のものが必要になる。すでにデータベースが用意されている環境なら、以下の中から不足しているものだけを用意しよう。説明していない言葉が出てくるが、ここでは列記するだけで後で説明する。
- データベースサーバ
- クライアント
データベースサーバとクライアントって何？
『データベースサーバ』は上でのたとえで言うところの、本棚と管理者と、それらがいる建物のことを指す。『クライアント』はあなたの召使いで、あなたが「あの本取ってきて」と頼むとデータベースサーバに出かけていき、管理者に「あの本取ってきて」と話しかけ、そこで本を見てその内容のコピーを取り、あなたのところまで持ち帰ってくる。他の人が見に来る可能性があるので、本そのものを持ち帰ることはできない。
この仕組みは、インターネットで見ているWebサイトと同じだと考えることもできる。あなたが今使っているブラウザ（ChromeやSafariやIEやEdgeなど）が『クライアント』で、彼に*4「 http://mizunokura.hatenablog.com/ という場所に書かれている内容を取ってきて」と頼むと、その場所にある建物まで出かけて行き、そこにいる管理者に見せてくれるよう話しかけ、見せてもらった内容のコピーを取り、あなたのところまで持ち帰ってくる。
データベースサーバの用意
すでにどこかにあることがわかっている場合
その場合、以下の情報を集める必要がある。ここでも先に列記してから後で説明する。
- データベースサーバの種別
- アドレス
- 認証情報
- ポート番号
データベースサーバの種別
データベースサーバにはいくつかの種類がある。
その他にもたくさんの種類があるが、市場シェア割合が高いのはこの4つ。それぞれに異なるクライアントを使用するので、種別を知っておく必要がある。こればっかりは聞くしかないので識者に質問しよう。種別が判明したら「MySQL クライアント インストール Mac」などで検索し、自分のパソコンにクライアントをインストールしよう。
アドレス
建物で例えるなら住所のこと。クライアントが出かけて行く先。IPアドレス（192.168.1.1 とか）だったり、ホストアドレス（db0001.local とか）だったりする。これも聞くしかないので聞こう。
認証情報
クライアントが建物に入っていくための名前（ユーザー名）とパスワード。これも聞くしかないので聞こう。
ポート番号
じつは、建物にはたくさん出入り口が用意されている。しかしながらセキュリティを確保するために、指定された出入り口以外を使うことはできないことになっている。MySQL の場合、初期設定では 3306 番の出入り口を使用する。これも聞くしかないので聞こう。聞いて「わからない」と言われたら、たいてい 3306 番だと思っておそらく問題ない。
とりあえず自分が使えればいい場合
その場合は、自分のパソコンの中に建物を建てて本棚を置いて管理人を連れてくることができる。データベースサーバの種別を決め、パソコンにインストールしよう（ググろう）。
みんなで使いたい場合
その場合も「自分が使えればいい場合」と考え方はほとんど同じだが、自分のパソコンの中ではなく、みんなが使うことのできる共通の場所に建物を建てる必要がある。その方法などなどを説明し始めると話が逸れるので割愛する。
クライアントの用意
上述の通り、データベースサーバの種別ごとに異なるので、種別を調べてから「MySQL クライアント インストール Mac」などで検索し、自分のパソコンにクライアントをインストールしよう。
管理人と話そう
データベースサーバとクライアントの準備ができたら、さっそく建物の中にいる管理人に挨拶してみよう。都合上ここからは MySQL に絞って説明していく。
建物に入ろう
データベースサーバという建物の中に入っていくためには、上述の「アドレス」と「認証情報」と「ポート番号」とを使う必要がある。コマンドラインクライアント（黒背景に白文字の画面で使用するクライアント）の場合、ターミナルに以下を入力（$は入力しない）して、エンターキーを押して実行する。
$ mysql -u ユーザー名 -h アドレス --port ポート番号 -p
このような、パソコンに対する命令文を「コマンド」と呼ぶ。
また、上記のコマンドのポート番号については初期設定で3306になっているので省略できる。さらに、パスワードが設定されていない場合にも省略でき、自分のパソコンの中に建物を建てた場合アドレスも省略できるので、以下の内容で実行することができる。
$ mysql -u ユーザー名
自分でデータベースサーバをインストールした場合、ユーザー名は、とくに変更していないのであれば root になっている。
例えば以下のような表示が出てくれば、建物に入ること（ログイン）に成功している。
$ mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.21 Homebrew Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
上の mysql> の部分がクライアントの待機状態を示す文字列で、「管理人になんて言ってきましょうか」と自分に聞いてきてると考えると良いかもしれない。
パスワードの入力を促される場合
実行時に -p を付けている場合、以下のようにパスワードの入力を促される場合がある。パスワードを入力しよう。画面に入力している文字が何も表示されないが、そのまま入力してエンターキーを押せば問題ない。
$ mysql -u ユーザー名 -p Enter password:
パスワードを間違えていると以下のようなエラーメッセージが表示される。
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
また、以下のようにパスワードをコマンドに含めてしまうこともできる。
$ mysql -u ユーザー名 -pパスワード
MySQL サーバが起動していない場合
以下のようなエラーメッセージが出る場合、MySQL データベースサーバが起動してない。例えるなら、建物も本棚も管理人もいない状態である。
$ mysql -uroot ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Mac で homebrew を使ってインストールした場合、以下のコマンドを使って起動することができる。
$ brew services start mysql
建物から帰ろう
いつでもクライアントを終了できるように、その方法を確認しておこう。mysql> の表示に続けて quit と入力し、エンターキーを押せば良い。
mysql> quit
また、 Ctrl+D キー でもクライアントを即座に終了することができる。
建物の中でできること
MySQL データベースサーバでは、大きく分けてふたつのことができる。どちらもクライアントが管理人に対して話しかけることで効果を発揮する。
SQLコマンド
SQLコマンドとは、データベースを操作する際に使用するために作られた命令文の集合*5で、最初のバージョンが発表されてから各データベースサーバがそれぞれに取り入れ、さらに独自の拡張を行ってきたもの。あまりにも各データベースサーバごとに異なってしまうのを防ぐために作られた標準統一規格が存在するので、基本的にはある程度共通しているが、異なっている部分もある。
大きく分けて、以下の操作ができる。カッコ内は本棚の例えをした場合のイメージ。
- データ定義（本棚や、本棚に置く内容を決めたりする）
- データ操作（本棚に本を置いたり、削除したり、上書きしたりする）
- データ制御（建物にはいれる人を決めたり、本棚の内容を変えるのを制限したりする）
MySQLコマンド
SQLコマンドに定義されていないものの、各データベースサーバが必要に応じて追加した独自の命令文の集合。それぞれ独自のコマンドなので、データベースサーバごとに大きく異なっている。「MySQLコマンド」という言葉は筆者が名付けただけなので、一般には通じないことに注意。
コマンドを知る前に
ここから各コマンドについて説明するが、その前に知っておくべきこと、用意しておくべきことを説明する。
コマンドの説明の前に知っておくべきこと
データベースサーバ内では、以下の概念を取り扱う。
- データベース
- テーブル
- カラム
- ユーザー
データベース
「情報を取り扱うための入れ物の入れ物」。本棚の例えで言うところの、管理人が管理しているたくさんの本棚の群れのこと。Excelなら1つのxlsxファイル。Google Spreadsheetsなら1つのスプレッドシート。
テーブル
「情報を取り扱うための入れ物」。本棚の例えで言うところの、たくさんの本棚のうちのひとつ。Excelなら1つのxlsxファイルの中の1枚のシート。Google Spreadsheetsなら1つのスプレッドシートの中の1枚のシート。
カラム
「入れ物の中の間仕切りと、仕切られた空間につけられた名前」。本棚の例えで言うところの、「書名」や「著者名」といった分類するための情報区分。ExcelやGoogle Spreadsheetsなら、たいてい1行目に記載するその列の名前。
ユーザー
「操作をする人」。本棚の例えで言うところの、建物にはいる人の名前とパスワード。Excelなら操作している自分自身。Google Spreadsheetsなら、Google にログインするアカウント。
コマンドの説明をする前に本棚を作ろう
コマンドの説明をするにあたって、手元に動作を確認する環境があると理解しやすくなる。MySQL の場合は公式にデータベースとテーブルのセットを用意してくれているので、ダウンロードして使うと良い。
参照: https://dev.mysql.com/doc/index-other.html
ダウンロードしたZIPファイルを解凍して作成された world.sql を mysql クライアントに渡してやると、世界の国々の人口や首都、都市の人口などのデータ、言語などが入ったデータベースを作成してくれる。
$ mysql -u ユーザー名 < ./world.sql
※ ./world.sql の部分は各自ダウンロードしたファイルがある場所までのディレクトリパスを入力する。もしそれが Mac のダウンロードフォルダなら、
$ mysql -u ユーザー名 < ~/Downloads/world.sql
とすることで作成できる。
コマンドを知ろう
ここからコマンドについて説明していく。基本的にエンターキーが押されるまでクライアントは管理人に話しかけに行かない。また、基本的に末尾に ; （セミコロン）や ¥G が付いていなければ、管理人はコマンド入力がまだ続いていると判断して、何もしない((USE など一部コマンドは除く))。以下ではコマンドを大文字表記しているが、小文字でも問題ない。
定義済みのデータベースを一覧取得する（SHOW DATABASES）
データベースサーバ内のデータベース（建物内にある本棚群）の一覧を取ってきてもらい、見るMySQLコマンド。
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 5 rows in set (0.00 sec)
ここで表示されたのは、データベースの名前だ。 world 以外の4つは、MySQL データベースサーバインストール時から用意されているデータベース管理用のもの（管理人が使うもの）なので、直接触る必要はない。
操作したいデータベースを選ぶ（USE データベース名）
操作したい本棚の名前を指定するMySQLコマンド。
mysql> use world; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
本棚を指定しないまま、先に本棚を指定しているべきコマンドを入力した場合、以下のエラーになる。
mysql> show tables; ERROR 1046 (3D000): No database selected
ログイン時（建物に入る時）に以下のようにすることで、USE データベース名 を入力することなくデータベースを指定することもできる。
$ mysql -u ユーザー名 -h アドレス --port ポート番号 -p データベース名 # または $ mysql -u ユーザー名 データベース名 # など
定義済みのテーブルを一覧取得する（SHOW TABLES）
データベース内のテーブル（本棚群の中の本棚）の一覧を取ってきてもらい、見るMySQLコマンド。
mysql> SHOW TABLES; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec)
定義済みのテーブルひとつを確認する（DESC テーブル名）
データベース内のテーブルひとつの情報を取ってきてもらい、見るMySQLコマンド。ここでは、上で確認した中にある country テーブルの情報を確認しよう。
mysql> DESC country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.01 sec)
Field はカラムに付けられた名前。 Type はカラムに入れられるデータの種類。Null はそのカラムにnull（空のデータ）が入ることを許容するかどうか。Key はそのカラムの一意制約（後述）の情報。Default はそのカラムに値を指定しなかった場合に自動的で入る値。Extra は付随情報。
また Field には、日本語を使うこともできる。
テーブルの中から全カラムの情報を取得する（SELECT * FROM テーブル名）
テーブルの中に定義されている全カラムの情報を取得するコマンド。FROM のあとに続けて、データを取ってきてほしいテーブル名を指定する。ここでは、上で情報を確認した country テーブルの内容を取得してみよう。
mysql> SELECT * FROM country; +------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+--------------------------------------+---------+-------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+--------------------------------------+---------+-------+ | ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW | | AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF | | AGO | Angola | Africa | Central Africa | 1246700.00 | 1975 | 12878000 | 38.3 | 6648.00 | 7984.00 | Angola | Republic | José Eduardo dos Santos | 56 | AO | （中略） | ZAF | South Africa | Africa | Southern Africa | 1221037.00 | 1910 | 40377000 | 51.1 | 116729.00 | 129092.00 | South Africa | Republic | Thabo Mbeki | 716 | ZA | | ZMB | Zambia | Africa | Eastern Africa | 752618.00 | 1964 | 9169000 | 37.2 | 3377.00 | 3922.00 | Zambia | Republic | Frederick Chiluba | 3162 | ZM | | ZWE | Zimbabwe | Africa | Eastern Africa | 390757.00 | 1980 | 11669000 | 37.8 | 5951.00 | 8670.00 | Zimbabwe | Republic | Robert G. Mugabe | 4068 | ZW | +------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+--------------------------------------+---------+-------+ 239 rows in set (0.01 sec)
239行もあるので、画面が文字で埋まってしまい大変なことになる。そこで、ひとまず名前だけを取ってきてみたい。
テーブルの中から特定のカラムの情報を取得する（SELECT カラム名 FROM テーブル名）
DESC テーブル名 で確認したカラム名（Field の部分）を指定することで、そのカラムの情報だけを取ってくることができる。
mysql> SELECT Name FROM country; +----------------------------------------------+ | Name | +----------------------------------------------+ | Aruba | | Afghanistan | | Angola | （中略） | South Africa | | Zambia | | Zimbabwe | +----------------------------------------------+ 239 rows in set (0.00 sec)
また、カラム名はカンマ（,）で区切ることで複数指定することもできる。また、全カラム取得時に指定したアスタリスク（*）は、じつは「全カラム」という意味を持っている。
mysql> SELECT Code, Name FROM country; +------+----------------------------------------------+ | Code | Name | +------+----------------------------------------------+ | ABW | Aruba | | AFG | Afghanistan | | AGO | Angola | （中略） | ZAF | South Africa | | ZMB | Zambia | | ZWE | Zimbabwe | +------+----------------------------------------------+ 239 rows in set (0.00 sec)
これで多少見やすくはなったが、行数が多いのでまだすこしわかりにくいと思う。そこで、今度は指定した件数だけを取ってきてみたい。
テーブルの中から指定件数のデータを取得する（SELECT カラム名 FROM テーブル名 LIMIT 件数）
LIMIT 件数 を指定することで、指定件数のデータだけを取ってくることができる。
mysql> SELECT Code,Name FROM country LIMIT 10; +------+----------------------+ | Code | Name | +------+----------------------+ | ABW | Aruba | | AFG | Afghanistan | | AGO | Angola | | AIA | Anguilla | | ALB | Albania | | AND | Andorra | | ANT | Netherlands Antilles | | ARE | United Arab Emirates | | ARG | Argentina | | ARM | Armenia | +------+----------------------+ 10 rows in set (0.00 sec)
これで画面が平和になった。しかし、今度は11行目以降が見えなくなってしまった。次の10件を取ってきてもらうには、 OFFSET 件数 を末尾でさらに指定する。
mysql> SELECT Code,Name FROM country LIMIT 10 OFFSET 10; +------+-----------------------------+ | Code | Name | +------+-----------------------------+ | ASM | American Samoa | | ATA | Antarctica | | ATF | French Southern territories | | ATG | Antigua and Barbuda | | AUS | Australia | | AUT | Austria | | AZE | Azerbaijan | | BDI | Burundi | | BEL | Belgium | | BEN | Benin | +------+-----------------------------+ 10 rows in set (0.00 sec)
同様に、次の21行目以降を見るためには OFFSET 20 を指定すれば良い。これで少しずつ見ることができるようなった。では、OFFSET の件数を変えて、日本（Japan）の情報だけを見てみよう。
mysql> SELECT Code,Name FROM country LIMIT 10 OFFSET 100; +------+--------------------------------+ | Code | Name | +------+--------------------------------+ | IOT | British Indian Ocean Territory | | IRL | Ireland | | IRN | Iran | | IRQ | Iraq | | ISL | Iceland | | ISR | Israel | | ITA | Italy | | JAM | Jamaica | | JOR | Jordan | | JPN | Japan | +------+--------------------------------+ 10 rows in set (0.00 sec)
…あった。Japan は110行目にようやく見つかった。しかし、今回のように239件しかないテーブルならともかく、たとえば10万件あるテーブルの中から特定のデータ1件を見つけるために、OFFSET 10、20、40…と繰り返すのは手間がかかりすぎる。そこで、日本のように名前がわかっている国のデータを、名前で探せるようにしたい。
テーブルの中から指定条件に完全一致するデータを取得する（SELECT カラム名 FROM テーブル名 WHERE カラム名 = '合致させたい内容'）
WHERE に続けて、カラム名と合致させたい内容を指定することで、条件に合うデータ行だけを取得することができる。ここでは国名（Name）が日本（Japan）になっているデータを取ってきてほしいので、WHERE Name = 'Japan' と指定する。
mysql> SELECT * FROM country WHERE Name = 'Japan'; +------+-------+-----------+--------------+-------------+-----------+------------+----------------+------------+------------+--------------+-------------------------+-------------+---------+-------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+-------+-----------+--------------+-------------+-----------+------------+----------------+------------+------------+--------------+-------------------------+-------------+---------+-------+ | JPN | Japan | Asia | Eastern Asia | 377829.00 | -660 | 126714000 | 80.7 | 3787042.00 | 4192638.00 | Nihon/Nippon | Constitutional Monarchy | Akihito | 1532 | JP | +------+-------+-----------+--------------+-------------+-----------+------------+----------------+------------+------------+--------------+-------------------------+-------------+---------+-------+ 1 row in set (0.00 sec)
無事取ってこれた。しかし横長になってしまってすこし見づらい。その場合は、末尾の ; を ¥G に変えることで、横並びに表示されているデータを縦並びに表示することができる。
mysql> SELECT * FROM country WHERE Name = 'Japan'\G
*************************** 1. row ***************************
Code: JPN
Name: Japan
Continent: Asia
Region: Eastern Asia
SurfaceArea: 377829.00
IndepYear: -660
Population: 126714000
LifeExpectancy: 80.7
GNP: 3787042.00
GNPOld: 4192638.00
LocalName: Nihon/Nippon
GovernmentForm: Constitutional Monarchy
HeadOfState: Akihito
Capital: 1532
Code2: JP
1 row in set (0.00 sec)
では、同じように大陸（Continent）がアジア（Asia）になっている国の一覧を取ってきてみよう。
mysql> SELECT * FROM country WHERE Continent = 'Asia'\G
（中略）
*************************** 51. row ***************************
Code: YEM
Name: Yemen
Continent: Asia
Region: Middle East
SurfaceArea: 527968.00
IndepYear: 1918
Population: 18112000
LifeExpectancy: 59.8
GNP: 6041.00
GNPOld: 5729.00
LocalName: Al-Yaman
GovernmentForm: Republic
HeadOfState: Ali Abdallah Salih
Capital: 1780
Code2: YE
51 rows in set (0.00 sec)
今度は逆に見辛くなってしまったので、横長にしてみる。
mysql> SELECT * FROM country WHERE Continent = 'Asia'; +------+----------------------+-----------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------+----------------------------------+---------+-------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+----------------------+-----------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------+----------------------------------+---------+-------+ | AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF | | ARE | United Arab Emirates | Asia | Middle East | 83600.00 | 1971 | 2441000 | 74.1 | 37966.00 | 36846.00 | Al-Imarat al-´Arabiya al-Muttahida | Emirate Federation | Zayid bin Sultan al-Nahayan | 65 | AE | | ARM | Armenia | Asia | Middle East | 29800.00 | 1991 | 3520000 | 66.4 | 1813.00 | 1627.00 | Hajastan | Republic | Robert Kotšarjan | 126 | AM | | AZE | Azerbaijan | Asia | Middle East | 86600.00 | 1991 | 7734000 | 62.9 | 4127.00 | 4100.00 | Azärbaycan | Federal Republic | Heydär Äliyev | 144 | AZ | | BGD | Bangladesh | Asia | Southern and Central Asia | 143998.00 | 1971 | 129155000 | 60.2 | 32852.00 | 31966.00 | Bangladesh | Republic | Shahabuddin Ahmad | 150 | BD | | BHR | Bahrain | Asia | Middle East | 694.00 | 1971 | 617000 | 73.0 | 6366.00 | 6097.00 | Al-Bahrayn | Monarchy (Emirate) | Hamad ibn Isa al-Khalifa | 149 | BH | | BRN | Brunei | Asia | Southeast Asia | 5765.00 | 1984 | 328000 | 73.6 | 11705.00 | 12460.00 | Brunei Darussalam | Monarchy (Sultanate) | Haji Hassan al-Bolkiah | 538 | BN | | BTN | Bhutan | Asia | Southern and Central Asia | 47000.00 | 1910 | 2124000 | 52.4 | 372.00 | 383.00 | Druk-Yul | Monarchy | Jigme Singye Wangchuk | 192 | BT | | CHN | China | Asia | Eastern Asia | 9572900.00 | -1523 | 1277558000 | 71.4 | 982268.00 | 917719.00 | Zhongquo | People'sRepublic | Jiang Zemin | 1891 | CN | | CYP | Cyprus | Asia | Middle East | 9251.00 | 1960 | 754700 | 76.7 | 9333.00 | 8246.00 | Kýpros/Kibris | Republic | Glafkos Klerides | 2430 | CY | | GEO | Georgia | Asia | Middle East | 69700.00 | 1991 | 4968000 | 64.5 | 6064.00 | 5924.00 | Sakartvelo | Republic | Eduard Ševardnadze | 905 | GE | | HKG | Hong Kong | Asia | Eastern Asia | 1075.00 | NULL | 6782000 | 79.5 | 166448.00 | 173610.00 | Xianggang/Hong Kong | Special Administrative Region of China | Jiang Zemin | 937 | HK | | IDN | Indonesia | Asia | Southeast Asia | 1904569.00 | 1945 | 212107000 | 68.0 | 84982.00 | 215002.00 | Indonesia | Republic | Abdurrahman Wahid | 939 | ID | | IND | India | Asia | Southern and Central Asia | 3287263.00 | 1947 | 1013662000 | 62.5 | 447114.00 | 430572.00 | Bharat/India | Federal Republic | Kocheril Raman Narayanan | 1109 | IN | | IRN | Iran | Asia | Southern and Central Asia | 1648195.00 | 1906 | 67702000 | 69.7 | 195746.00 | 160151.00 | Iran | Islamic Republic | Ali Mohammad Khatami-Ardakani | 1380 | IR | | IRQ | Iraq | Asia | Middle East | 438317.00 | 1932 | 23115000 | 66.5 | 11500.00 | NULL | Al-´Iraq | Republic | Saddam Hussein al-Takriti | 1365 | IQ | | ISR | Israel | Asia | Middle East | 21056.00 | 1948 | 6217000 | 78.6 | 97477.00 | 98577.00 | Yisra’el/Isra’il | Republic | Moshe Katzav | 1450 | IL | | JOR | Jordan | Asia | Middle East | 88946.00 | 1946 | 5083000 | 77.4 | 7526.00 | 7051.00 | Al-Urdunn | Constitutional Monarchy | Abdullah II | 1786 | JO | | JPN | Japan | Asia | Eastern Asia | 377829.00 | -660 | 126714000 | 80.7 | 3787042.00 | 4192638.00 | Nihon/Nippon | Constitutional Monarchy | Akihito | 1532 | JP | | KAZ | Kazakstan | Asia | Southern and Central Asia | 2724900.00 | 1991 | 16223000 | 63.2 | 24375.00 | 23383.00 | Qazaqstan | Republic | Nursultan Nazarbajev | 1864 | KZ | | KGZ | Kyrgyzstan | Asia | Southern and Central Asia | 199900.00 | 1991 | 4699000 | 63.4 | 1626.00 | 1767.00 | Kyrgyzstan | Republic | Askar Akajev | 2253 | KG | | KHM | Cambodia | Asia | Southeast Asia | 181035.00 | 1953 | 11168000 | 56.5 | 5121.00 | 5670.00 | Kâmpuchéa | Constitutional Monarchy | Norodom Sihanouk | 1800 | KH | | KOR | South Korea | Asia | Eastern Asia | 99434.00 | 1948 | 46844000 | 74.4 | 320749.00 | 442544.00 | Taehan Min’guk (Namhan) | Republic | Kim Dae-jung | 2331 | KR | | KWT | Kuwait | Asia | Middle East | 17818.00 | 1961 | 1972000 | 76.1 | 27037.00 | 30373.00 | Al-Kuwayt | Constitutional Monarchy (Emirate) | Jabir al-Ahmad al-Jabir al-Sabah | 2429 | KW | | LAO | Laos | Asia | Southeast Asia | 236800.00 | 1953 | 5433000 | 53.1 | 1292.00 | 1746.00 | Lao | Republic | Khamtay Siphandone | 2432 | LA | | LBN | Lebanon | Asia | Middle East | 10400.00 | 1941 | 3282000 | 71.3 | 17121.00 | 15129.00 | Lubnan | Republic | Émile Lahoud | 2438 | LB | | LKA | Sri Lanka | Asia | Southern and Central Asia | 65610.00 | 1948 | 18827000 | 71.8 | 15706.00 | 15091.00 | Sri Lanka/Ilankai | Republic | Chandrika Kumaratunga | 3217 | LK | | MAC | Macao | Asia | Eastern Asia | 18.00 | NULL | 473000 | 81.6 | 5749.00 | 5940.00 | Macau/Aomen | Special Administrative Region of China | Jiang Zemin | 2454 | MO | | MDV | Maldives | Asia | Southern and Central Asia | 298.00 | 1965 | 286000 | 62.2 | 199.00 | NULL | Dhivehi Raajje/Maldives | Republic | Maumoon Abdul Gayoom | 2463 | MV | | MMR | Myanmar | Asia | Southeast Asia | 676578.00 | 1948 | 45611000 | 54.9 | 180375.00 | 171028.00 | Myanma Pye | Republic | kenraali Than Shwe | 2710 | MM | | MNG | Mongolia | Asia | Eastern Asia | 1566500.00 | 1921 | 2662000 | 67.3 | 1043.00 | 933.00 | Mongol Uls | Republic | Natsagiin Bagabandi | 2696 | MN | | MYS | Malaysia | Asia | Southeast Asia | 329758.00 | 1957 | 22244000 | 70.8 | 69213.00 | 97884.00 | Malaysia | Constitutional Monarchy, Federation | Salahuddin Abdul Aziz Shah Alhaj | 2464 | MY | | NPL | Nepal | Asia | Southern and Central Asia | 147181.00 | 1769 | 23930000 | 57.8 | 4768.00 | 4837.00 | Nepal | Constitutional Monarchy | Gyanendra Bir Bikram | 2729 | NP | | OMN | Oman | Asia | Middle East | 309500.00 | 1951 | 2542000 | 71.8 | 16904.00 | 16153.00 | ´Uman | Monarchy (Sultanate) | Qabus ibn Sa´id | 2821 | OM | | PAK | Pakistan | Asia | Southern and Central Asia | 796095.00 | 1947 | 156483000 | 61.1 | 61289.00 | 58549.00 | Pakistan | Republic | Mohammad Rafiq Tarar | 2831 | PK | | PHL | Philippines | Asia | Southeast Asia | 300000.00 | 1946 | 75967000 | 67.5 | 65107.00 | 82239.00 | Pilipinas | Republic | Gloria Macapagal-Arroyo | 766 | PH | | PRK | North Korea | Asia | Eastern Asia | 120538.00 | 1948 | 24039000 | 70.7 | 5332.00 | NULL | Choson Minjujuui In´min Konghwaguk (Bukhan) | Socialistic Republic | Kim Jong-il | 2318 | KP | | PSE | Palestine | Asia | Middle East | 6257.00 | NULL | 3101000 | 71.4 | 4173.00 | NULL | Filastin | Autonomous Area | Yasser (Yasir) Arafat | 4074 | PS | | QAT | Qatar | Asia | Middle East | 11000.00 | 1971 | 599000 | 72.4 | 9472.00 | 8920.00 | Qatar | Monarchy | Hamad ibn Khalifa al-Thani | 2973 | QA | | SAU | Saudi Arabia | Asia | Middle East | 2149690.00 | 1932 | 21607000 | 67.8 | 137635.00 | 146171.00 | Al-´Arabiya as-Sa´udiya | Monarchy | Fahd ibn Abdul-Aziz al-Sa´ud | 3173 | SA | | SGP | Singapore | Asia | Southeast Asia | 618.00 | 1965 | 3567000 | 80.1 | 86503.00 | 96318.00 | Singapore/Singapura/Xinjiapo/Singapur | Republic | Sellapan Rama Nathan | 3208 | SG | | SYR | Syria | Asia | Middle East | 185180.00 | 1941 | 16125000 | 68.5 | 65984.00 | 64926.00 | Suriya | Republic | Bashar al-Assad | 3250 | SY | | THA | Thailand | Asia | Southeast Asia | 513115.00 | 1350 | 61399000 | 68.6 | 116416.00 | 153907.00 | Prathet Thai | Constitutional Monarchy | Bhumibol Adulyadej | 3320 | TH | | TJK | Tajikistan | Asia | Southern and Central Asia | 143100.00 | 1991 | 6188000 | 64.1 | 1990.00 | 1056.00 | Toçikiston | Republic | Emomali Rahmonov | 3261 | TJ | | TKM | Turkmenistan | Asia | Southern and Central Asia | 488100.00 | 1991 | 4459000 | 60.9 | 4397.00 | 2000.00 | Türkmenostan | Republic | Saparmurad Nijazov | 3419 | TM | | TMP | East Timor | Asia | Southeast Asia | 14874.00 | NULL | 885000 | 46.0 | 0.00 | NULL | Timor Timur | Administrated by the UN | José Alexandre Gusmão | 1522 | TP | | TUR | Turkey | Asia | Middle East | 774815.00 | 1923 | 66591000 | 71.0 | 210721.00 | 189122.00 | Türkiye | Republic | Ahmet Necdet Sezer | 3358 | TR | | TWN | Taiwan | Asia | Eastern Asia | 36188.00 | 1945 | 22256000 | 76.4 | 256254.00 | 263451.00 | T’ai-wan | Republic | Chen Shui-bian | 3263 | TW | | UZB | Uzbekistan | Asia | Southern and Central Asia | 447400.00 | 1991 | 24318000 | 63.7 | 14194.00 | 21300.00 | Uzbekiston | Republic | Islam Karimov | 3503 | UZ | | VNM | Vietnam | Asia | Southeast Asia | 331689.00 | 1945 | 79832000 | 69.3 | 21929.00 | 22834.00 | Viêt Nam | Socialistic Republic | Trân Duc Luong | 3770 | VN | | YEM | Yemen | Asia | Middle East | 527968.00 | 1918 | 18112000 | 59.8 | 6041.00 | 5729.00 | Al-Yaman | Republic | Ali Abdallah Salih | 1780 | YE | +------+----------------------+-----------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------+----------------------------------+---------+-------+ 51 rows in set (0.00 sec)
このように、 = で指定した内容に完全一致する行だけが取得できた。では、世界の中で、日本と同じように国名の先頭が 'J' で始まる国はいくつあるだろうか。
テーブルの中から指定条件に部分一致するデータを取得する（SELECT カラム名 FROM テーブル名 WHERE カラム名 LIKE '合致させたい内容'）
=の代わりにLIKEを使うことで、完全一致ではなく部分一致でデータを探すことができる。指定したい箇所以外の文字はパーセント（%）で表記する。たとえば「国名がJではじまる」であれば WHERE Name LIKE 'J%' とし、「国名がnで終わる」であれば WHERE Name LIKE '%n' とし、「国名の先頭と末尾以外がapa」であれば、 WHERE Name Like '%apa%' と指定する。
mysql> SELECT * FROM country WHERE Name LIKE 'J%'; +------+---------+---------------+--------------+-------------+-----------+------------+----------------+------------+------------+--------------+-------------------------+--------------+---------+-------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+---------+---------------+--------------+-------------+-----------+------------+----------------+------------+------------+--------------+-------------------------+--------------+---------+-------+ | JAM | Jamaica | North America | Caribbean | 10990.00 | 1962 | 2583000 | 75.2 | 6871.00 | 6722.00 | Jamaica | Constitutional Monarchy | Elisabeth II | 1530 | JM | | JOR | Jordan | Asia | Middle East | 88946.00 | 1946 | 5083000 | 77.4 | 7526.00 | 7051.00 | Al-Urdunn | Constitutional Monarchy | Abdullah II | 1786 | JO | | JPN | Japan | Asia | Eastern Asia | 377829.00 | -660 | 126714000 | 80.7 | 3787042.00 | 4192638.00 | Nihon/Nippon | Constitutional Monarchy | Akihito | 1532 | JP | +------+---------+---------------+--------------+-------------+-----------+------------+----------------+------------+------------+--------------+-------------------------+--------------+---------+-------+ 3 rows in set (0.00 sec)
J で始まる国は、ジャマイカとヨルダンと日本だけだった。では、同じように国名が 'K' で始まり、かつアジア大陸にある国だけを取ってきてほしい場合はどうすればいいだろう。その場合は、 WHERE Continent = 'Asia' と WHERE Name LIKE 'K%' のふたつの条件を組み合わせなければならない。
テーブルの中から複数条件のすべてに一致するデータを取得する（SELECT カラム名 FROM テーブル名 WHERE 条件 AND 条件）
複数条件を指定したい場合、WHERE のあとにふたつ以上の条件を AND でつなげることで指定することができる。WHERE Continent = 'Asia' と WHERE Name LIKE 'K%' なら、WHERE Continent = 'Asia' AND Name LIKE 'K%'（WHERE はひとつで良い）になる。
mysql> SELECT * FROM country WHERE Name LIKE 'K%' AND continent = 'Asia'; +------+------------+-----------+---------------------------+-------------+-----------+------------+----------------+----------+----------+------------+-----------------------------------+----------------------------------+---------+-------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+------------+-----------+---------------------------+-------------+-----------+------------+----------------+----------+----------+------------+-----------------------------------+----------------------------------+---------+-------+ | KAZ | Kazakstan | Asia | Southern and Central Asia | 2724900.00 | 1991 | 16223000 | 63.2 | 24375.00 | 23383.00 | Qazaqstan | Republic | Nursultan Nazarbajev | 1864 | KZ | | KGZ | Kyrgyzstan | Asia | Southern and Central Asia | 199900.00 | 1991 | 4699000 | 63.4 | 1626.00 | 1767.00 | Kyrgyzstan | Republic | Askar Akajev | 2253 | KG | | KWT | Kuwait | Asia | Middle East | 17818.00 | 1961 | 1972000 | 76.1 | 27037.00 | 30373.00 | Al-Kuwayt | Constitutional Monarchy (Emirate) | Jabir al-Ahmad al-Jabir al-Sabah | 2429 | KW | +------+------------+-----------+---------------------------+-------------+-----------+------------+----------------+----------+----------+------------+-----------------------------------+----------------------------------+---------+-------+ 3 rows in set (0.00 sec)
カザフスタンとキルギスとクウェートが条件に合致した。では、国名の先頭が 'K' もしくは 'P' の国ではどうだろうか。
テーブルの中から複数条件のいずれかまたは両方に一致するデータを取得する（SELECT カラム名 FROM テーブル名 WHERE 条件 OR 条件）
いずれかまたは両方に合致させたい複数条件を指定する場合、WHERE のあとにふたつ以上の条件を OR でつなげることで指定することができる。WHERE Name LIKE 'P%' と WHERE Name LIKE 'K%' なら、WHERE Name LIKE 'P%' OR Name LIKE 'K%'（WHERE はひとつで良い）になる。
mysql> SELECT Name, Continent FROM country WHERE Name LIKE 'P%' OR Name LIKE 'K%'; +------------------+---------------+ | Name | Continent | +------------------+---------------+ | Kazakstan | Asia | | Kenya | Africa | | Kyrgyzstan | Asia | | Kiribati | Oceania | | Kuwait | Asia | | Pakistan | Asia | | Panama | North America | | Pitcairn | Oceania | | Peru | South America | | Philippines | Asia | | Palau | Oceania | | Papua New Guinea | Oceania | | Poland | Europe | | Puerto Rico | North America | | Portugal | Europe | | Paraguay | South America | | Palestine | Asia | +------------------+---------------+ 17 rows in set (0.00 sec)
無事取得することができた。さらにアジア大陸の国だけに絞る場合、 Continent = 'Asia' を条件に追加する必要がある。大陸の指定は、Pで始まる国でもKで始まる国でも絞り込みたい条件なので、 OR で追加することはできない。そのまま AND でつなげた場合、以下のようになる。
mysql> SELECT Name, Continent FROM country WHERE Name LIKE 'P%' OR Name LIKE 'K%' AND Continent = 'Asia'; +------------------+---------------+ | Name | Continent | +------------------+---------------+ | Kazakstan | Asia | | Kyrgyzstan | Asia | | Kuwait | Asia | | Pakistan | Asia | | Panama | North America | | Pitcairn | Oceania | | Peru | South America | | Philippines | Asia | | Palau | Oceania | | Papua New Guinea | Oceania | | Poland | Europe | | Puerto Rico | North America | | Portugal | Europe | | Paraguay | South America | | Palestine | Asia | +------------------+---------------+ 15 rows in set (0.00 sec)
Kで始まる国についてはアジア大陸だけに絞り込まれているが、Pで始まる国については絞り込めていない。これは、 WHERE Name LIKE 'P%' OR Name LIKE 'K%' AND Continent = 'Asia' という条件が、「国名がPで始まる」もしくは「国名がKで始まり、かつ大陸がアジア」という意味になってしまっているからだ。これを「国名がPで始まる」または「国名がKで始まる」、かつ「大陸がアジア」としたい場合、またはの条件のほうをカッコで囲み、WHERE ( Name LIKE 'P%' OR Name LIKE 'K%' ) AND Continent = 'Asia' のようにする。
mysql> SELECT Name, Continent FROM country WHERE ( Name LIKE 'P%' OR Name LIKE 'K%' ) AND Continent = 'Asia'; +-------------+-----------+ | Name | Continent | +-------------+-----------+ | Kazakstan | Asia | | Kyrgyzstan | Asia | | Kuwait | Asia | | Pakistan | Asia | | Philippines | Asia | | Palestine | Asia | +-------------+-----------+ 6 rows in set (0.00 sec)
無事絞り込めた。ところで国名がアルファベット順になっていないので、これを並び替えたい。
テーブルの取得結果をカラムの内容で並び替える（SELECT カラム名 FROM テーブル名 ORDER BY カラム名 昇順または降順）
SELECT での取得結果を並び替えて表示したい場合、 ORDER BY カラム名 に続けて、昇順（ASC）または降順（DESC）を指定すれば良い（指定しなかった場合昇順になる）。
mysql> SELECT Name, Continent FROM country WHERE ( Name LIKE 'P%' OR Name LIKE 'K%' ) AND Continent = 'Asia' ORDER BY Name ASC; +-------------+-----------+ | Name | Continent | +-------------+-----------+ | Kazakstan | Asia | | Kuwait | Asia | | Kyrgyzstan | Asia | | Pakistan | Asia | | Palestine | Asia | | Philippines | Asia | +-------------+-----------+ 6 rows in set (0.00 sec)
アルファベットの昇順になった。これを応用して、たとえば世界の面積の大きな国のランキングを見ることもできる。
mysql> SELECT Name, SurfaceArea FROM country ORDER BY SurfaceArea DESC LIMIT 10; +--------------------+-------------+ | Name | SurfaceArea | +--------------------+-------------+ | Russian Federation | 17075400.00 | | Antarctica | 13120000.00 | | Canada | 9970610.00 | | China | 9572900.00 | | United States | 9363520.00 | | Brazil | 8547403.00 | | Australia | 7741220.00 | | India | 3287263.00 | | Argentina | 2780400.00 | | Kazakstan | 2724900.00 | +--------------------+-------------+ 10 rows in set (0.00 sec)
テーブルの取得条件に以上、以下を使う（SELECT カラム名 FROM テーブル名 WHERE カラム名 >= 数値 OR カラム名 <= 数値）
>=,<=,>,< を使うことで、以上、以下、より大きい、未満、を指定することができる。たとえば平均余命（LifeExpectancy）が80歳以上の国を取ってくる場合、 WHERE LifeExpectancy >= 80 のように指定する。
mysql> SELECT Name, LifeExpectancy FROM country WHERE LifeExpectancy >= 80; +------------+----------------+ | Name | LifeExpectancy | +------------+----------------+ | Andorra | 83.5 | | Japan | 80.7 | | Macao | 81.6 | | Singapore | 80.1 | | San Marino | 81.1 | +------------+----------------+ 5 rows in set (0.00 sec)
逆に平均余命が40歳以下の国は、以下のように取ってくることができる。
mysql> SELECT Name, LifeExpectancy FROM country WHERE LifeExpectancy <= 40; +------------+----------------+ | Name | LifeExpectancy | +------------+----------------+ | Angola | 38.3 | | Botswana | 39.3 | | Mozambique | 37.5 | | Malawi | 37.6 | | Rwanda | 39.3 | | Zambia | 37.2 | | Zimbabwe | 37.8 | +------------+----------------+ 7 rows in set (0.00 sec)
テーブルの全件数を取得する（SELECT COUNT(*) FROM テーブル名）
特定のテーブルで管理されているデータの全件数が取得したい場合、 SELECT * FROM テーブル名 で全件取得することで確認することもできるが、取得対象カラム名に COUNT(*) を指定することで、もっとスマートに件数だけを取ってきてもらうこともできる。
mysql> SELECT COUNT(*) FROM country; +----------+ | COUNT(*) | +----------+ | 239 | +----------+ 1 row in set (0.00 sec)
COUNT(*) は COUNT(1) と表記しても良い。
mysql> SELECT COUNT(1) FROM country; +----------+ | COUNT(1) | +----------+ | 239 | +----------+ 1 row in set (0.03 sec)
では、大陸別の国の数を取ってきてみたい。大陸（Continent）でまとめることができれば、数えることができそうだ。
テーブルの内容を特定のカラムの内容でグループ分けする（SELECT * FROM テーブル名 GROUP BY カラム名）
GROUP BY カラム名 を指定することで、指定したカラムの内容でまとめた結果を取得することができる。これは主に COUNT() などの複数のカラムの内容をまとめるコマンドと併用する。併用しなかった場合、以下のようにまとめられたうちの1件だけが表示される。
mysql> SELECT Name, Continent FROM country GROUP BY Continent; +----------------+---------------+ | Name | Continent | +----------------+---------------+ | Afghanistan | Asia | | Albania | Europe | | Aruba | North America | | Angola | Africa | | American Samoa | Oceania | | Antarctica | Antarctica | | Argentina | South America | +----------------+---------------+ 7 rows in set (0.00 sec)
ここで COUNT(*) を併用すると、以下のようになる。
mysql> SELECT COUNT(1), Continent FROM country GROUP BY Continent; +----------+---------------+ | COUNT(1) | Continent | +----------+---------------+ | 51 | Asia | | 46 | Europe | | 37 | North America | | 58 | Africa | | 28 | Oceania | | 5 | Antarctica | | 14 | South America | +----------+---------------+ 7 rows in set (0.00 sec)
アフリカ大陸の国が一番多いことがわかった。COUNT() と同じように、GROUP BY と組み合わせて取得結果を操作するコマンドは他にもある。
指定されたカラムの最大値、最小値を取得する（SELECT MAX(カラム名), MIN(カラム名) FROM テーブル名 GROUP BY カラム名）
MAX()とMIN() を使うことで、最大値と最小値を取得することができる（MAX()とMIN()はもちろん個別に使うこともできる）。
mysql> SELECT MAX(SurfaceArea), MIN(SurfaceArea), Continent FROM country GROUP BY Continent; +------------------+------------------+---------------+ | MAX(SurfaceArea) | MIN(SurfaceArea) | Continent | +------------------+------------------+---------------+ | 9572900.00 | 18.00 | Asia | | 17075400.00 | 0.40 | Europe | | 9970610.00 | 53.00 | North America | | 2505813.00 | 78.00 | Africa | | 7741220.00 | 12.00 | Oceania | | 13120000.00 | 59.00 | Antarctica | | 8547403.00 | 12173.00 | South America | +------------------+------------------+---------------+ 7 rows in set (0.00 sec)
アジア最大の国は9572900.00平方km、最小の国は18.00平方kmであることがわかった。（それぞれ = で指定して国名を調べてみても面白いかもしれない）
指定されたカラムに登録されている内容の合計値を取得する（SELECT SUM(カラム名) FROM テーブル名）
SUM(カラム名) を使うことで、合計値が取得できる。
mysql> SELECT SUM(SurfaceArea), Continent FROM country GROUP BY Continent; +------------------+---------------+ | SUM(SurfaceArea) | Continent | +------------------+---------------+ | 31881005.00 | Asia | | 23049133.90 | Europe | | 24214470.00 | North America | | 30250377.00 | Africa | | 8564294.00 | Oceania | | 13132101.00 | Antarctica | | 17864926.00 | South America | +------------------+---------------+ 7 rows in set (0.01 sec)
各大陸の国の面積の合計値（＝各大陸の面積？）が取得できた。
指定されたカラムに登録されている内容の文字数を取得する（SELECT LENGTH(カラム名) FROM テーブル名）
LENGTH(カラム名) を指定することで、その内容の文字数を取得することができる。SUM() と組み合わせて、以下のようなこともできる。
mysql> SELECT SUM(LENGTH(Name)), Continent FROM country GROUP BY Continent; +-------------------+---------------+ | SUM(LENGTH(Name)) | Continent | +-------------------+---------------+ | 396 | Asia | | 425 | Europe | | 446 | North America | | 547 | Africa | | 359 | Oceania | | 127 | Antarctica | | 113 | South America | +-------------------+---------------+ 7 rows in set (0.01 sec)
各大陸の国名の英語表記名の文字数合計が取得できた。（国名が20文字以上の国の一覧や、5文字以下の国一覧を取ってきても面白いかもしれない）
指定されたカラムに登録されている内容を重複を取り除いて表示する（SELECT DISTINCT カラム名 FROM テーブル名）
DISTINCT カラム名 を使うことで、重複を取り除いた結果を取得することができる。
mysql> SELECT DISTINCT Continent FROM country; +---------------+ | Continent | +---------------+ | North America | | Asia | | Africa | | Europe | | South America | | Oceania | | Antarctica | +---------------+ 7 rows in set (0.00 sec)
大陸名（Continent）を指定することで、その内容を重複なしで取ってこれた。
複数のカラムの内容や文字列を結合して結果に含める（SELECT CONCAT(文字列やカラム名, 文字列やカラム名, ...) FROM テーブル名）
CONCAT() を使うことで、複数の文字の結合をすることができる。'Hello!' ' ' 'World!' の3つの文字列を組み合わせるには、以下のようにする。
mysql> SELECT CONCAT('Hello!', ' ', 'World!');
+---------------------------------+
| CONCAT('Hello!', ' ', 'World!') |
+---------------------------------+
| Hello! World! |
+---------------------------------+
1 row in set (0.01 sec)
文字列の代わりにカラム名を指定することもできる。たとえば国名と大陸名をくっつけて表示したい場合、以下のようにする。
mysql> SELECT CONCAT(Name, ' (', Continent, ')') FROM country LIMIT 10;
+--------------------------------------+
| CONCAT(Name, ' (', Continent, ')') |
+--------------------------------------+
| Aruba (North America) |
| Afghanistan (Asia) |
| Angola (Africa) |
| Anguilla (North America) |
| Albania (Europe) |
| Andorra (Europe) |
| Netherlands Antilles (North America) |
| United Arab Emirates (Asia) |
| Argentina (South America) |
| Armenia (Asia) |
+--------------------------------------+
10 rows in set (0.00 sec)
CONCAT(Name, ' (', Continent, ')') の表示が少し見づらいので、これに別名をつけてみたい。
取得結果に別名をつける（SELECT 取得結果 AS 別名）
AS 別名 を追加することで、別名をつけることができる。
mysql> SELECT CONCAT(Name, ' (', Continent, ')') AS NameContinent FROM country LIMIT 10;
+--------------------------------------+
| NameContinent |
+--------------------------------------+
| Aruba (North America) |
| Afghanistan (Asia) |
| Angola (Africa) |
| Anguilla (North America) |
| Albania (Europe) |
| Andorra (Europe) |
| Netherlands Antilles (North America) |
| United Arab Emirates (Asia) |
| Argentina (South America) |
| Armenia (Asia) |
+--------------------------------------+
10 rows in set (0.00 sec)
この別名は、並び替えのカラム名にも指定することができる。
mysql> SELECT CONCAT(Name, ' (', Continent, ')') AS NameContinent FROM country ORDER BY NameContinent LIMIT 10;
+-------------------------------------+
| NameContinent |
+-------------------------------------+
| Afghanistan (Asia) |
| Albania (Europe) |
| Algeria (Africa) |
| American Samoa (Oceania) |
| Andorra (Europe) |
| Angola (Africa) |
| Anguilla (North America) |
| Antarctica (Antarctica) |
| Antigua and Barbuda (North America) |
| Argentina (South America) |
+-------------------------------------+
10 rows in set (0.01 sec)
ふたつのテーブルを組み合わせて結果を取得する
さて、ここまで一通りの取得操作を見てきた。これで、ひとつのテーブルからデータを取ってくることは、一通りできるようになったと思う。では、ここからはふたつのテーブルの内容を組み合わせて表示する方法を説明していく。すこし複雑になるので、わからなくなったら、無理せずまたここに戻ってきてほしい。
いままで country テーブルを使って説明してきたが、 world データベースの中には他にもテーブルがある。
mysql> SHOW TABLES; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0.00 sec)
city を見てみよう。
mysql> DESC city; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.03 sec)
ID番号、名前、国コード、県名、人口が登録されているようだ。では、試しに都市名（Name）が 'Tripoli'（トリポリ）になっている都市を探してみよう。
mysql> select * from city where Name = 'Tripoli'; +------+---------+-------------+-----------+------------+ | ID | Name | CountryCode | District | Population | +------+---------+-------------+-----------+------------+ | 2439 | Tripoli | LBN | al-Shamal | 240000 | | 2441 | Tripoli | LBY | Tripoli | 1682000 | +------+---------+-------------+-----------+------------+ 2 rows in set (0.00 sec)
該当する都市が2件見つかった。それぞれ CountryCode が LBN, LBY となっているが、これだけでは国名がはっきりしない*6。国コードを使って、以下のように個別に調べてやることもできる。
mysql> select * from country WHERE Code = 'LBN'; +------+---------+-----------+-------------+-------------+-----------+------------+----------------+----------+----------+-----------+----------------+---------------+---------+-------+ | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+---------+-----------+-------------+-------------+-----------+------------+----------------+----------+----------+-----------+----------------+---------------+---------+-------+ | LBN | Lebanon | Asia | Middle East | 10400.00 | 1941 | 3282000 | 71.3 | 17121.00 | 15129.00 | Lubnan | Republic | Émile Lahoud | 2438 | LB | +------+---------+-----------+-------------+-------------+-----------+------------+----------------+----------+----------+-----------+----------------+---------------+---------+-------+ 1 row in set (0.00 sec)
ふたつのテーブルに該当する行を結合して取得する（INNER JOIN）
LBN はレバノンだとわかった。しかし、こうやっていちいち該当するデータを別のテーブルから検索してくるのは手間がかかりすぎる。そこで、city と country の内容を結合して表示したい。結合するには、 INNER JOIN を使用する。city テーブルに対して country テーブルを結合するので、 SELECT * FROM city INNER JOIN country WHERE Name = 'Tripoli'; とする。これを実行すると以下のようになる。
mysql> SELECT * FROM city INNER JOIN country WHERE Name = 'Tripoli'; ERROR 1052 (23000): Column 'Name' in where clause is ambiguous
エラーメッセージの中の ambiguous とは「曖昧な」という意味で、エラーメッセージ全体では「WHERE の中に書いてある 'Name' というカラム名は曖昧だ」と言われている。どういうことだろうか。それぞれのテーブルをよく見てみよう。
mysql> DESC city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> DESC country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
Name カラムに注目しよう。両方のテーブルに 'Name' カラムがあることがわかるはずだ。「曖昧だ」というのはつまり、「city テーブルの中の Name カラムなのか、country テーブルの中の Name カラムなのかが曖昧だ」という意味なのだ。どちらのテーブルの Name カラムなのか、はっきりと指定してやる必要がある。指定したいのは city テーブルなので、city.Name のように書くことができる。SELECT * FROM city INNER JOIN country WHERE city.Name = 'Tripoli'; これを実行すると、以下のようになる。
mysql> SELECT * FROM city INNER JOIN country WHERE city.Name = 'Tripoli'; +------+---------+-------------+-----------+------------+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+--------------------------------------+---------+-------+ | ID | Name | CountryCode | District | Population | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+---------+-------------+-----------+------------+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+--------------------------------------+---------+-------+ | 2439 | Tripoli | LBN | al-Shamal | 240000 | ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW | | 2439 | Tripoli | LBN | al-Shamal | 240000 | AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF | | 2439 | Tripoli | LBN | al-Shamal | 240000 | AGO | Angola | Africa | Central Africa | 1246700.00 | 1975 | 12878000 | 38.3 | 6648.00 | 7984.00 | Angola | Republic | José Eduardo dos Santos | 56 | AO | （中略） | 2441 | Tripoli | LBY | Tripoli | 1682000 | ZAF | South Africa | Africa | Southern Africa | 1221037.00 | 1910 | 40377000 | 51.1 | 116729.00 | 129092.00 | South Africa | Republic | Thabo Mbeki | 716 | ZA | | 2441 | Tripoli | LBY | Tripoli | 1682000 | ZMB | Zambia | Africa | Eastern Africa | 752618.00 | 1964 | 9169000 | 37.2 | 3377.00 | 3922.00 | Zambia | Republic | Frederick Chiluba | 3162 | ZM | | 2441 | Tripoli | LBY | Tripoli | 1682000 | ZWE | Zimbabwe | Africa | Eastern Africa | 390757.00 | 1980 | 11669000 | 37.8 | 5951.00 | 8670.00 | Zimbabwe | Republic | Robert G. Mugabe | 4068 | ZW | +------+---------+-------------+-----------+------------+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+--------------------------------------+---------+-------+ 478 rows in set (0.01 sec)
またも画面が文字で埋まってしまった。トリポリという都市は2つしかなかったはずなのに、なぜ478件も出てきてしまったのだろうか。それは、結合の対象にするカラム名を指定していないからだ。結合対象のカラムを指定していない場合、データベースの管理人は以下のように考えてしまう。
- city テーブルから、Name が Tripoli になっている行を取ってくる。2件だった。
- country テーブルから、全件取ってくる。239件だった。
- 結合対象カラムが指定されていないので、cityの結果1件ずつに、それぞれ239件のcountryの結果を組み合わせよう。2件 x 239件 ＝ 478件の結果ができた。
これは望んだ結果ではないので、コマンドを修正しよう。city.CountryCode と country.Code を結合してほしいので、指定を追加する。 INNER JOIN テーブル名 ON 結合対象指定 と入力するので、 SELECT * FROM city INNER JOIN country ON city.CountryCode = country.Code WHERE city.Name = 'Tripoli' となった。
mysql> SELECT * FROM city INNER JOIN country ON city.CountryCode = country.Code WHERE city.Name = 'Tripoli'; +------+---------+-------------+-----------+------------+------+------------------------+-----------+-----------------+-------------+-----------+------------+----------------+----------+----------+-----------+-------------------+--------------------+---------+-------+ | ID | Name | CountryCode | District | Population | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 | +------+---------+-------------+-----------+------------+------+------------------------+-----------+-----------------+-------------+-----------+------------+----------------+----------+----------+-----------+-------------------+--------------------+---------+-------+ | 2439 | Tripoli | LBN | al-Shamal | 240000 | LBN | Lebanon | Asia | Middle East | 10400.00 | 1941 | 3282000 | 71.3 | 17121.00 | 15129.00 | Lubnan | Republic | Émile Lahoud | 2438 | LB | | 2441 | Tripoli | LBY | Tripoli | 1682000 | LBY | Libyan Arab Jamahiriya | Africa | Northern Africa | 1759540.00 | 1951 | 5605000 | 75.5 | 44806.00 | 40562.00 | Libiya | Socialistic State | Muammar al-Qadhafi | 2441 | LY | +------+---------+-------------+-----------+------------+------+------------------------+-----------+-----------------+-------------+-----------+------------+----------------+----------+----------+-----------+-------------------+--------------------+---------+-------+ 2 rows in set (0.00 sec)
さきほどよりは平和な結果になった。しかし横長で見えづらいので、見たい情報だけに絞って表示しよう。 * の部分を見たいカラムの指定に変更する。だからといってただ単にカラム名を記述してしまうと、以下のようになってしまう。
mysql> SELECT ID, Name, CountryCode, District, Population, Name FROM city INNER JOIN country ON city.CountryCode = country.Code WHERE city.Name = 'Tripoli'; ERROR 1052 (23000): Column 'Name' in field list is ambiguous
また「曖昧だ」とエラーメッセージが出てくるので、同じようにテーブル名込みで指定しよう。
mysql> SELECT city.ID, city.Name, city.CountryCode, city.District, city.Population, country.Name FROM city INNER JOIN country ON city.CountryCode = country.Code WHERE city.Name = 'Tripoli'; +------+---------+-------------+-----------+------------+------------------------+ | ID | Name | CountryCode | District | Population | Name | +------+---------+-------------+-----------+------------+------------------------+ | 2439 | Tripoli | LBN | al-Shamal | 240000 | Lebanon | | 2441 | Tripoli | LBY | Tripoli | 1682000 | Libyan Arab Jamahiriya | +------+---------+-------------+-----------+------------+------------------------+ 2 rows in set (0.00 sec)
しかし、結果の Name にテーブル名が含まれていないので、別名をつけてやることにする。
mysql> SELECT city.ID, city.Name AS CityName, city.CountryCode, city.District, city.Population, country.Name AS CountryName FROM city INNER JOIN country ON city.CountryCode = country.Code WHERE city.Name = 'Tripoli'; +------+----------+-------------+-----------+------------+------------------------+ | ID | CityName | CountryCode | District | Population | CountryName | +------+----------+-------------+-----------+------------+------------------------+ | 2439 | Tripoli | LBN | al-Shamal | 240000 | Lebanon | | 2441 | Tripoli | LBY | Tripoli | 1682000 | Libyan Arab Jamahiriya | +------+----------+-------------+-----------+------------+------------------------+ 2 rows in set (0.01 sec)
また、別名と組み合わせることはできないが、 テーブル名.* を使ってカラム指定を省略することができる。* を使っていないテーブルのカラムには、別名をつけても問題ない。
mysql> SELECT city.*, country.Name AS CountryName FROM city INNER JOIN country ON city.CountryCode = country.Code WHERE city.Name = 'Tripoli'; +------+---------+-------------+-----------+------------+------------------------+ | ID | Name | CountryCode | District | Population | CountryName | +------+---------+-------------+-----------+------------+------------------------+ | 2439 | Tripoli | LBN | al-Shamal | 240000 | Lebanon | | 2441 | Tripoli | LBY | Tripoli | 1682000 | Libyan Arab Jamahiriya | +------+---------+-------------+-----------+------------+------------------------+ 2 rows in set (0.00 sec)
ふたつのテーブルに該当する行と、片方にだけある行を結合して取得する（LEFT OUTER JOIN, RIGHT OUTER JOIN）
ところで、 countryLanguage テーブルを見てみよう。
mysql> DESC countryLanguage;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | float(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
CountryCode が 'JPN' になっているレコードを見てみると、以下のようになっている。
mysql> select * from countryLanguage WHERE CountryCode = 'JPN'; +-------------+----------------------+------------+------------+ | CountryCode | Language | IsOfficial | Percentage | +-------------+----------------------+------------+------------+ | JPN | Ainu | F | 0.0 | | JPN | Chinese | F | 0.2 | | JPN | English | F | 0.1 | | JPN | Japanese | T | 99.1 | | JPN | Korean | F | 0.5 | | JPN | Philippene Languages | F | 0.1 | +-------------+----------------------+------------+------------+ 6 rows in set (0.01 sec)
日本で話されている言語と、話者の割合が管理されていることがわかる。 Language を指定すれば、その言語が話されている国と割合がわかる。
mysql> select * from countryLanguage WHERE Language = 'English'; +-------------+----------+------------+------------+ | CountryCode | Language | IsOfficial | Percentage | +-------------+----------+------------+------------+ | ABW | English | F | 9.5 | | AIA | English | T | 0.0 | | ANT | English | F | 7.8 | （中略） | WSM | English | T | 0.6 | | ZAF | English | T | 8.5 | | ZWE | English | T | 2.2 | +-------------+----------+------------+------------+ 60 rows in set (0.01 sec)
英語が60カ国で話されていることがわかった。では、国の一覧と、その国の英語話者の割合を一覧にしてみよう。INNER JOIN で、 country テーブルの一覧と countryLanguage テーブルを Language = 'English' で絞り込んだ結果一覧とを結合する。
mysql> SELECT country.Code, country.Name AS CountryName, countryLanguage.Language, countryLanguage.IsOfficial, countryLanguage.Percentage FROM country INNER JOIN countryLanguage ON country.Code = countryLanguage.CountryCode WHERE Language = 'English'; +------+--------------------------------------+----------+------------+------------+ | Code | CountryName | Language | IsOfficial | Percentage | +------+--------------------------------------+----------+------------+------------+ | ABW | Aruba | English | F | 9.5 | | AIA | Anguilla | English | T | 0.0 | | ANT | Netherlands Antilles | English | F | 7.8 | （中略） | WSM | Samoa | English | T | 0.6 | | ZAF | South Africa | English | T | 8.5 | | ZWE | Zimbabwe | English | T | 2.2 | +------+--------------------------------------+----------+------------+------------+ 60 rows in set (0.01 sec)
たしかに結果一覧が出たが、一覧には、英語を話していない国も一緒に含めたい。INNER JOIN では、結合するふたつのテーブル両方にある行しか結果一覧に含めない。そこで、この場合には OUTER JOIN を使用する。
mysql> SELECT country.Code, country.Name AS CountryName, countryLanguage.Language, countryLanguage.IsOfficial, countryLanguage.Percentage FROM country LEFT JOIN countryLanguage ON country.Code = countryLanguage.CountryCode AND countryLanguage.Language = 'English'; +------+----------------------------------------------+----------+------------+------------+ | Code | CountryName | Language | IsOfficial | Percentage | +------+----------------------------------------------+----------+------------+------------+ | ABW | Aruba | English | F | 9.5 | | AFG | Afghanistan | NULL | NULL | NULL | | AGO | Angola | NULL | NULL | NULL | （中略） | ZAF | South Africa | English | T | 8.5 | | ZMB | Zambia | NULL | NULL | NULL | | ZWE | Zimbabwe | English | T | 2.2 | +------+----------------------------------------------+----------+------------+------------+ 239 rows in set (0.00 sec)
また、この場合には countryLanguage.Language = 'English' の条件を WHERE に含めず、LEFT JOIN 手^ブル名 ON の後に入力する。 WHERE に含めた場合は、以下のようになる。
mysql> SELECT country.Code, country.Name AS CountryName, countryLanguage.Language, countryLanguage.IsOfficial, countryLanguage.Percentage FROM country LEFT JOIN countryLanguage ON country.Code = countryLanguage.CountryCode WHERE countryLanguage.Language = 'English'; +------+--------------------------------------+----------+------------+------------+ | Code | CountryName | Language | IsOfficial | Percentage | +------+--------------------------------------+----------+------------+------------+ | ABW | Aruba | English | F | 9.5 | | AIA | Anguilla | English | T | 0.0 | | ANT | Netherlands Antilles | English | F | 7.8 | （中略） | WSM | Samoa | English | T | 0.6 | | ZAF | South Africa | English | T | 8.5 | | ZWE | Zimbabwe | English | T | 2.2 | +------+--------------------------------------+----------+------------+------------+ 60 rows in set (0.00 sec)
結果が60件しか出てこず、 INNER JOIN と同じになっている。これは、上のSQLを管理人が以下のように理解するからである*7。
- country テーブルから全件を取得する。結果は239件。
- countryLanguage テーブルから全件を取得する。結果は984件。
- 1と2の結果一覧を
country.Code = countryLanguage.CountryCodeの条件で結合する。 - 3の結果のうち、
countryLanguage.Language = 'English'に該当する行だけに絞り込む。結果は60件。
これを ON に書くことで、以下のように解釈してもらえます。
- country テーブルから全件を取得する。結果は239件。
- countryLanguage テーブルのうち
countryLanguage.Language = 'English'に該当する行だけに絞り込む。結果は60件。 - 1に2を
country.Code = countryLanguage.CountryCodeの条件で結合する。1のデータのうち2に該当する行が無いものも、結合結果に含める。結果は239件。
A LEFT JOIN B の場合には A にしかないデータも結果に含まれ、 A RIGHT JOIN B の場合には B にしかないデータも結果に含まれる。LEFT と RIGHT とでは、このテーブルの結合の方向が異なる。
結合の説明はここまで。
テーブルにデータを追加する（INSERT INTO テーブル名 ( カラム名 ) VALUES ( データ )）
世界に新しい国ができたとか、country テーブルに不足しているデータがあった場合、データを追加する必要がでてくる。Excel では空白の行にデータを追加するが、MySQL ではコマンドでデータを追加する。追加先のテーブルを確認しよう。
mysql> DESC country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
追加するデータを用意して、INSERT コマンドを入力する。ここでは、オーランド諸島自治領が突然独立したものとして、データを追加する*8。
mysql> INSERT INTO country ( Code, Name, Continent, Region, SurfaceArea, IndepYear, Population, LifeExpectancy, GNP, GNPOld, LocalName, GovernmentForm, HeadOfState, Capital, Code2 ) VALUES ( 'ALA', 'Aland Islands', 'Europe', 'Nordic Countries', 13517.00, 1920, 28700, 82.5, 0, 0, 'Landskapet Åland', 'Parliamentary System', 'Sauli Väinämö Niinistö', null, 'AX' ); Query OK, 1 row affected (0.02 sec)
追加した結果を SELECT して確認する。
mysql> SELECT * FROM country WHERE Code = 'ALA'\G
*************************** 1. row ***************************
Code: ALA
Name: Aland Islands
Continent: Europe
Region: Nordic Countries
SurfaceArea: 13517.00
IndepYear: 1920
Population: 28700
LifeExpectancy: 82.5
GNP: 0.00
GNPOld: 0.00
LocalName: Landskapet Åland
GovernmentForm: Parliamentary System
HeadOfState: Sauli Väinämö Niinistö
Capital: NULL
Code2: AX
1 row in set (0.00 sec)
このままでは首都のマリエハムンが登録されていないので、Capital のデータを更新して、首都を設定しよう。DESC country; の結果を見ればわかるように、 Capital カラムは int(11) というカラムタイプになっている。これは「11桁の数字」という意味で、スウェーデンの場合、以下のようになっている。
mysql> SELECT Code, Name, Capital FROM country WHERE Name = 'Sweden'; +------+--------+---------+ | Code | Name | Capital | +------+--------+---------+ | SWE | Sweden | 3048 | +------+--------+---------+ 1 row in set (0.00 sec)
この 3048 は、以下のように city テーブルの ID カラムの番号を意味している。
mysql> select * from city where ID=3048; +------+-----------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+----------+------------+ | 3048 | Stockholm | SWE | Lisboa | 750348 | +------+-----------+-------------+----------+------------+ 1 row in set (0.00 sec)
オーランド諸島の首都マリエハムンを city テーブルで検索すると、以下のようになっている。
mysql> select * from city where Name='Mariehamn'; Empty set (0.01 sec)
無い。city テーブルにも追加する必要がある。
mysql> desc city; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
確認すると、 city.ID には auto_increment という記載がある。これは「自動的に最大値+1を設定する」という意味で、新規にデータを追加した場合、何も指定しなければ最大値+1を設定してくれるようになっている。INSERT では基本的に ID は指定せず、以下のようにする。
mysql> INSERT INTO city ( Name, CountryCode, District, Population ) VALUES ( 'Mariehamn', 'ALA', 'Aland', 11186 ); Query OK, 1 row affected (0.03 sec)
追加結果を確認する。
mysql> select * from city where Name='Mariehamn'; +------+-----------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+----------+------------+ | 4080 | Mariehamn | ALA | Aland | 11186 | +------+-----------+-------------+----------+------------+ 1 row in set (0.00 sec)
ID は 4080 が割り当てられている。 これで、country テーブルの Capital を更新する準備ができた。
テーブルのデータを更新する（UPDATE テーブル名 SET カラム名 = 変更内容 WHERE 条件）
INSERT で追加したオーランド諸島の首都IDを、新規に追加した city.ID である 4080 で更新する。更新前、データは以下のように NULL （空データ）になっている。
mysql> SELECT * FROM country WHERE Code = 'ALA'\G
*************************** 1. row ***************************
Code: ALA
Name: Aland Islands
Continent: Europe
Region: Nordic Countries
SurfaceArea: 13517.00
IndepYear: 1920
Population: 28700
LifeExpectancy: 82.5
GNP: 0.00
GNPOld: 0.00
LocalName: Landskapet Åland
GovernmentForm: Parliamentary System
HeadOfState: Sauli Väinämö Niinistö
Capital: NULL
Code2: AX
1 row in set (0.00 sec)
これを更新する。
mysql> UPDATE country SET Capital = 4080 WHERE Code = 'ALA'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
更新結果を確認する。
mysql> SELECT Code, Name, Capital FROM country WHERE Code = 'ALA'; +------+---------------+---------+ | Code | Name | Capital | +------+---------------+---------+ | ALA | Aland Islands | 4080 | +------+---------------+---------+ 1 row in set (0.00 sec)
UPDATE 時に WHERE で条件を指定しなかった場合、 country テーブルの全データが更新されてしまうので、意図していなければ気をつけてコマンドを入力しよう。実行した場合、以下のようになる。
mysql> UPDATE country SET Capital = 4080; Query OK, 240 row affected (0.01 sec) Rows matched: 240 Changed: 240 Warnings: 0
影響を受けた行が意図せず240行になっている。
データを削除する（DELETE FROM テーブル名 WHERE 条件）
先程追加したオーランド諸島のデータを削除してみよう。実施する前に、対象のデータを確認する。
mysql> SELECT * FROM country WHERE Code = 'ALA'\G
*************************** 1. row ***************************
Code: ALA
Name: Aland Islands
Continent: Europe
Region: Nordic Countries
SurfaceArea: 13517.00
IndepYear: 1920
Population: 28700
LifeExpectancy: 82.5
GNP: 0.00
GNPOld: 0.00
LocalName: Landskapet Åland
GovernmentForm: Parliamentary System
HeadOfState: Sauli Väinämö Niinistö
Capital: 4080
Code2: AX
1 row in set (0.00 sec)
削除を実施する。しかし、以下のようにエラーになった。
mysql> DELETE FROM country WHERE Code = 'ALA'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`world`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`))
これは、「country テーブルの削除しようとしているデータに紐付いている他のテーブルのデータがあるので、削除できない」というエラーだ。その他のテーブルは city で、カラム名は CountryCode とのことだそうだ。つまり、 city テーブルにある CountryCode = 'ALA' のデータがあるので、country テーブルの Code = 'ALA' のデータも削除できないのだ。先に city テーブルのデータを削除する必要がある。削除する前に確認しよう。
mysql> SELECT * FROM city WHERE CountryCode = 'ALA'; +------+-----------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------+-------------+----------+------------+ | 4080 | Mariehamn | ALA | Aland | 11186 | +------+-----------+-------------+----------+------------+ 1 row in set (0.00 sec)
確認してから、削除する。
mysql> DELETE FROM city WHERE CountryCode = 'ALA'; Query OK, 1 row affected (0.02 sec)
削除してから、また確認しよう。
mysql> SELECT * FROM city WHERE CountryCode = 'ALA'; Empty set (0.00 sec)
削除に成功したので、country からもデータを削除する。
mysql> DELETE FROM country WHERE Code = 'ALA'; Query OK, 1 row affected (0.02 sec)
削除してから、必ず確認する。
mysql> SELECT * FROM country WHERE Code = 'ALA'\G Empty set (0.00 sec)
今度は削除に成功した。
ここまでで、基本的な確認とデータ操作の説明を終わる。ここからは、データベースやテーブルの定義、変更などを簡単に説明する。
データベースの定義（CREATE DATABASE データベース名）
性質の異なる情報を同じ場所に保存しておくと、整理や取り出す時に混乱のもとになりがちだ。あれもこれも入れられる箱は入れる時には何も考えなくて良いので便利だが、いざ中身を見るときには中は大変なことになっているだろう。それを防ぐため、必要に応じてデータベースを分ける。
mysql> CREATE DATABASE `world2`; Query OK, 1 row affected (0.03 sec)
データベース定義文の確認（SHOW CREATE DATABASE データベース名）
一度作成したデータベースは、その作成時に使われたコマンドを再確認することができる。今作成した world2 データベースの定義文を見てみよう。
mysql> SHOW CREATE DATABASE world2; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | world2 | CREATE DATABASE `world2` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)
/*!40100 DEFAULT CHARACTER SET utf8 */ の部分はデータベースで使用するデフォルトの文字コードの設定だが、文字コードについて説明し始めると長くなるのでここでは割愛する。
データベースの削除（DROP DATABASE データベース名）
定義済みのデータベースを削除することもできる。
mysql> DROP DATABASE world2; Query OK, 0 rows affected (0.09 sec)
あっけなく消えてしまう。バックアップを保存していない場合などは気軽に復元できないので気をつけること。
テーブルの定義（CREATE TABLE テーブル名...）
データベース同様に、テーブルも定義することができる。
mysql> CREATE TABLE test ( id serial, name text, created datetime default now() ); Query OK, 0 rows affected (0.05 sec) mysql> DESC test; +---------+---------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+-------------------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | name | text | YES | | NULL | | | created | datetime | YES | | CURRENT_TIMESTAMP | | +---------+---------------------+------+-----+-------------------+----------------+ 3 rows in set (0.00 sec)
ここでは詳細は説明しない。カラムの種別などこまごまとした設定ができる。
テーブルの定義文を確認する（SHOW CREATE TABLE テーブル名）
こちらもデータベース同様に、一度定義されたテーブルは、その作成時に使われたコマンドを再確認することができる。
mysql> SHOW CREATE TABLE test; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` text, `created` datetime DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
テーブルの削除（DROP TABLE テーブル名）
定義済みのテーブルを削除することもできる。
mysql> DROP TABLE test; Query OK, 0 rows affected (0.01 sec)
こちらもあっけなく消えてしまう。バックアップを保存していない場合などはやはり気軽に復元できないので気をつけること。
テーブルにカラムを追加する（ALTER TABLE テーブル名 ADD COLUMN カラム名 カラム設定）
カラムを追加して、定義済みのテーブルの内容を変更することができる。
mysql> ALTER TABLE test ADD COLUMN modified datetime default null ON UPDATE CURRENT_TIMESTAMP AFTER created; Query OK, 0 rows affected (1.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +----------+---------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+-------------------+-----------------------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | name | text | YES | | NULL | | | created | datetime | YES | | CURRENT_TIMESTAMP | | | modified | datetime | YES | | NULL | on update CURRENT_TIMESTAMP | +----------+---------------------+------+-----+-------------------+-----------------------------+ 4 rows in set (0.00 sec)
テーブルからカラムを削除する（ALTER TABLE テーブル名 DROP COLUMN カラム名）
定義済みのテーブルからカラムを削除することができる。
mysql> ALTER TABLE test DROP COLUMN modified; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +---------+---------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+-------------------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | name | text | YES | | NULL | | | created | datetime | YES | | CURRENT_TIMESTAMP | | +---------+---------------------+------+-----+-------------------+----------------+ 3 rows in set (0.00 sec)
コマンドを入力していて起こりがちなトラブル
最後に簡単に、よく起こりがちなトラブルについて触れておく。
コーテーションのとじ忘れ
以下のような表示になり、コマンドが終えられなくなってしまうことがある。
mysql> SELECT * FROM country WHERE Name ='Japan;
'>
エンターキーを押し続けても、以下のように下に流れていくだけだ。
mysql> SELECT * FROM country WHERE Name ='Japan;
'>
'>
'>
これは 'Japan のように ' が不足しているから起きている現象で、次の ' を待っているので、コマンドが終わらなくなってしまっている。'; を入力してエンターキーを押すと、以下のように待ち状態を抜けられるので、改めて入力しよう。
mysql> SELECT * FROM country WHERE Name ='Japan;
'>
'>
'> ';
Empty set (0.01 sec)
他にあるあるなトラブルを思い出したら追加していく。
終わりに
なんだか随分長くなってしまった。書いてみて思ったが、やはりテーブルの結合が初学者のつまづきがちなポイントだと思う。とはいえ何度か使って慣れていけば、何も問題はないと思う。また、ここで説明した内容はデータベースサーバの持つ機能のうちのほんの一部でしかないし、説明を簡単にするために敢えて触れていないことや簡略化したことも多くある。データベースサーバには、大抵のやりたいことは叶えてくれるだけの機能が用意されているし、そのために内部はもっと複雑になっている。ぜひ、実際の動作やマニュアルをあたってみてほしい。
などと偉そうに書いてみたものの、調べてみると自分自身知らないことがたくさんあったので、それらについてここで説明しないまでも、とても勉強になったのでした。知識を自分なりにまとめるってすごく大切。
