MySQL講座#13 ビュー

ビューとテーブル

まず最初に習得する新しい道具は「ビュー」。

このビュートはいったい何か。一言で説明すると「SQLの観点から見ると“テーブルと同じもの”」である。実際、SQL文の中で、テーブルなのかビューなのかを意識する必要はほとんどない。実は更新の場合だけは違いを意識する必要があるが、、これはまた後述する。少なくともSELECT文を組み立てる際には、テーブルとビューの違いは気にしなくて構わない。

では、ビューとテーブルの違いはどこにあるのか。それはただ1つ、「実際のデータを保存しているか否か」である。

通常、テーブルを作り、INSERT文でデータを格納すると、データベースにデータが保存できる。そして、このデータベースのデータが実際に保存されるのはどこかというと、コンピュータ内の記憶装置である。したがって、SELECT文でデータ検索しようとするときは、実際にはこの記憶装置（ハードディスク）からデータを引っ張り出して、いろいろな計算を行い、ユーザに返す、という過程をたどる。

一方、ビューの場合、データを記憶装置に保存しない。ではどこにデータを保存しているかというと、ビューの場合、どこにも保存していない。ビューが保存しているのは「SELECT文そのもの」なのである。ビューからデータを取り出そうとするときに、ビューは内部的にそのSELECT文を実行し、一時的に仮想のテーブルを作る。

ビューのメリット

ビューのメリットは大きく分けて2つ。

1つ目は、データを保存しないため、記憶装置の容量を節約できること。例えば、以前にcategoryごと集約したテーブルを作ったが、このテーブルに含まれるデータも、結局のところ記憶装置に保存されるため、その分、記憶装置のデータ領域を消費する。しかし、これと同じデータをビューとして保存するならば、SELECT文だけを保存すれば良いので、記憶装置のデータ領域を節約できる。

2つ目のメリットは、頻繁に使うSELECT文を、いちいち毎回書かなくても、ビューとして保存しておくことで使いまわしがきくことである。一度ビューを作っておけば、後はそれを呼び出すだけで、簡単にSELECT文の結果を得ることができる。集計や条件が複雑で本体のSELECT文が大きくなればなるほど、ビューによる効率化の恩恵は大きなものとなる。

しかも、ビューが含むデータは、元のテーブルと連動して自動的に最新の状態に更新される。ビューは結局のところ「SELECT文」なので、「ビューを参照する」とは「そのSELECT文を実行する」ということである。なので、最新状態のデータを選択できるのである。これは、データをテーブルとして保存した場合にはない利点である。

ビューの作り方

ビューを作成するには、CREATE VIEW文を使用する。

CREATE VIEW ビュー名 (<ビューの列名1>, <ビューの列名2>,・・・)
AS
<SELECT文>

ASキーワードの後にはSELECT文を記述する。SELECT文の列とビューの列は並び順で一致し、SELECT文の最初の列はビューの1番目の列、SELECT文の2番目の列はビューの2番目の列、になる。ビューの列名は、ビュー名の後ろのリストで定義する。

では試しにビューを作ってみる。

CREATE VIEW CategorySum (category, cnt_chojin)
AS
SELECT category, COUNT(*)
FROM chojin
GROUP BY category;

これでCategorySum（カテゴリー合計）という名前のビューが1つ、データベース内に作られた。2行目のキーワード「AS」は絶対に省略してはいけない。ここでのASは、列名やテーブル名に別名をつけるときに使ったASとは異なる。省略するとエラーになるが、構文でそう決められているので、覚えておく必要がある。

ビューの使い方は、テーブルと同じく、SELECT文のFROM句に書くことができる。

SELECT category, cnt_chojin
FROM CategorySum;

結果

+--------------+------------+
| category     | cnt_chojin |
+--------------+------------+
| 完璧超人     |         22 |
| 悪魔超人     |         13 |
| 正義超人     |          9 |
+--------------+------------+

ビューの制限事項① ビュー定義でORDER BY句は使えない

「ビュー定義にはどんなSELECT文も書くことができる」と説明したが、1つだけ例外があり、ORDER BY句だけは使えない。したがって、次のようなビュー定義文は認められていない。

CREATE VIEW CategorySum (category, cnt_chojin)
AS
SELECT category, COUNT(*)
FROM chojin
GROUP BY category
ORDER BY category;

なぜORDER BY句をつかってはいけないかと言う理由は、テーブルと同様、ビューについても「行には順序がない」と定められているからである。

ビューの制限事項② ビューに対する更新

これまで、SELECT文の中では、ビューをテーブルとまったく同様に扱うことが可能だと述べた。それでは、INSERT、DELETE、UPDATEといった更新系SQLにおいては、どうだろうか。

実は、かなり厳しい制限付きではあるが、ビューに対する更新が可能な場合がある。標準SQLでは、

「ビュー定義のSELECT文において、いくつかの条件を満たしている場合、ビューに対する更新が可能」

と定められている。代表的な条件を挙げると、次の通り。

①SELECT句にDISTINCTが含まれていない
②FROM句に含まれるテーブルが1つだけ
③GROUP BY句を使用していない
④HAVING句を使用していない

①、③、④は集約に関するものである。平たく言ってしまうと、今回のCategorySumのように、ビューが元のテーブルを集約した結果を保持している場合、ビューでの変更を元のテーブルにどう反映すればよいのか判断できないのである。

ビューを削除する

ビューを削除するにはDROP VIEWを使う。

例えば、CategorySumビューを削除するなら、以下のように記述する。

DROP VIEW CategorySum;

ビューの応用

CREATE VIEW ChojinHealth (name, category, height, weight, power)
AS
SELECT name, category, height, weight, power
FROM chojin;

ビューの内容を表示。

SELECT * FROM ChojinHealth;

結果

+--------------------------------+--------------+--------+--------+-------+
| name                           | category     | height | weight | power |
+--------------------------------+--------------+--------+--------+-------+
| キン肉マン                     | 正義超人     |    185 |     90 |    95 |
| テリーマン                     | 正義超人     |    190 |     95 |    95 |
| ロビンマスク                   | 正義超人     |    217 |    155 |    96 |
| ウォーズマン                   | 正義超人     |    210 |    150 |   100 |
| ラーメンマン                   | 正義超人     |    209 |    130 |    97 |
| ブロッケンＪｒ                 | 正義超人     |    195 |     90 |    90 |
| ウルフマン                     | 正義超人     |    190 |    102 |    80 |
| ステカセキング                 | 悪魔超人     |    214 |    700 |   250 |
| ブラックホール                 | 悪魔超人     |    201 |    420 |   200 |
以下省略

catgoryごとに、平均身長、最長身長、最小身長、平均体重、最大体重、最小体重を選択する。

SELECT category,
AVG(height) AS 平均身長, MAX(height) AS 最長身長, MIN(height) AS 最小身長,
AVG(weight) AS 平均体重, MAX(weight) AS 最大体重, MIN(weight) AS 最小体重
FROM ChojinHealth
WHERE height IS NOT NULL
GROUP BY category;

結果

+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| category     | 平均身長     | 最長身長     | 最小身長     | 平均体重     | 最大体重     | 最小体重     |
+--------------+--------------+--------------+--------------+--------------+--------------+--------------+
| 完璧超人     |     230.6364 |          302 |          178 |     270.6364 |          880 |           95 |
| 悪魔超人     |     215.4615 |          300 |          104 |     403.8462 |         1000 |          115 |
| 正義超人     |     199.4286 |          217 |          185 |     116.0000 |          155 |           90 |
+--------------+--------------+--------------+--------------+--------------+--------------+--------------+

身長階級表を作成する。

CREATE TABLE heightLevel
(id CHAR(4) NOT NULL,
level VARCHAR(20) NOT NULL,
min INTEGER NOT NULL,
max INTEGER NOT NULL,
PRIMARY KEY (id)
);

階級データを挿入する。

START TRANSACTION;
INSERT INTO heightLevel (id, level, min, max) VALUES('0001', '190cm未満', '0.0', '189');
INSERT INTO heightLevel (id, level, min, max) VALUES('0002', '190cm以上200cm未満', '190', '199');
INSERT INTO heightLevel (id, level, min, max) VALUES('0003', '200cm以上210cm未満', '200', '209');
INSERT INTO heightLevel (id, level, min, max) VALUES('0004', '210cm以上220cm未満', '210', '219');
INSERT INTO heightLevel (id, level, min, max) VALUES('0005', '220cm以上230cm未満', '220', '229');
INSERT INTO heightLevel (id, level, min, max) VALUES('0006', '230cm以上240cm未満', '230', '239');
INSERT INTO heightLevel (id, level, min, max) VALUES('0007', '240cm以上250cm未満', '240', '249');
INSERT INTO heightLevel (id, level, min, max) VALUES('0008', '250cm以上260cm未満', '250', '259');
INSERT INTO heightLevel (id, level, min, max) VALUES('0009', '260cm以上270cm未満', '260', '269');
INSERT INTO heightLevel (id, level, min, max) VALUES('0009', '270cm以上280cm未満', '270', '279');
INSERT INTO heightLevel (id, level, min, max) VALUES('0010', '280cm以上290cm未満', '280', '289');
INSERT INTO heightLevel (id, level, min, max) VALUES('0011', '290cm以上300cm未満', '290', '299');
INSERT INTO heightLevel (id, level, min, max) VALUES('0012', '300cm以上310cm未満', '300', '309');
COMMIT;

select * from heightLevel;

結果

+------+------------------------+-----+-----+
| id   | level                  | min | max |
+------+------------------------+-----+-----+
| 0001 | 190cm未満              |   0 | 189 |
| 0002 | 190cm以上200cm未満     | 190 | 199 |
| 0003 | 200cm以上210cm未満     | 200 | 209 |
| 0004 | 210cm以上220cm未満     | 210 | 219 |
| 0005 | 220cm以上230cm未満     | 220 | 229 |
| 0006 | 230cm以上240cm未満     | 230 | 239 |
| 0007 | 240cm以上250cm未満     | 240 | 249 |
| 0008 | 250cm以上260cm未満     | 250 | 259 |
| 0009 | 260cm以上270cm未満     | 260 | 269 |
| 0010 | 280cm以上290cm未満     | 280 | 289 |
| 0011 | 290cm以上300cm未満     | 290 | 299 |
| 0012 | 300cm以上310cm未満     | 300 | 309 |
+------+------------------------+-----+-----+

身長階級ごとの超人の数をカウントする。

SELECT level, COUNT(*) AS num
FROM ChojinHealth RIGHT OUTER JOIN heightLevel ON
height BETWEEN min AND max
GROUP BY level, min, max
ORDER BY min ASC;

結果

+------------------------+-----+
| level                  | num |
+------------------------+-----+
| 190cm未満              |   3 |
| 190cm以上200cm未満     |   9 |
| 200cm以上210cm未満     |  11 |
| 210cm以上220cm未満     |   5 |
| 220cm以上230cm未満     |   2 |
| 230cm以上240cm未満     |   1 |
| 240cm以上250cm未満     |   1 |
| 250cm以上260cm未満     |   3 |
| 260cm以上270cm未満     |   2 |
| 280cm以上290cm未満     |   2 |
| 290cm以上300cm未満     |   2 |
| 300cm以上310cm未満     |   2 |
+------------------------+-----+

「COUNT(*)」とすると、「230cm以上240cm未満」「240cm以上250cm未満」の超人が存在しないにもかかわらず、heightLevelの「230cm以上240cm未満」「240cm以上250cm未満」がNULLとなっているレコードまで出力され、カウントされてしまっている。

正しくは、COUNT(height)としなければならない。

SELECT level, COUNT(height) AS num
FROM ChojinHealth RIGHT OUTER JOIN heightLevel ON
height BETWEEN min AND max
GROUP BY level, min, max
ORDER BY min ASC;

結果

+------------------------+-----+
| level                  | num |
+------------------------+-----+
| 190cm未満              |   3 |
| 190cm以上200cm未満     |   9 |
| 200cm以上210cm未満     |  11 |
| 210cm以上220cm未満     |   5 |
| 220cm以上230cm未満     |   2 |
| 230cm以上240cm未満     |   0 |
| 240cm以上250cm未満     |   0 |
| 250cm以上260cm未満     |   3 |
| 260cm以上270cm未満     |   2 |
| 280cm以上290cm未満     |   2 |
| 290cm以上300cm未満     |   2 |
| 300cm以上310cm未満     |   2 |
+------------------------+-----+

MySQL講座#12 トランザクション

トランザクションとは何か

トランザクションという言葉は、RDBMSの世界においては、「テーブルのデータに対する更新の単位」を表す。もっと簡単に言うとトランザクションは「データベースに対する1つ以上の更新をまとめて呼ぶときの名称」である。

テーブルに対する更新は、INSERT、DELETE、UPDATEという3つの道具を使って行う。しかし、更新は一般的に1回の操作で終わることはなく、複数の操作をまとめて連続的に行うことが多い。トランザクションとは、このような複数の操作を意味的にわかりやすくひとまとまりにしたもの、と考える。

例えば、トランザクションの例として、こんな状況がある。

いま、chojinテーブルの管理を任されているプログラマやSEだとする。上司がやってきてこんなことを言う。

「正義超人の超人強度を10下げて、その代わりに悪魔超人の超人強度を50上げて」

「UPDATEで更新すればOKだな」となる。

さて、このときトランザクションは次の2つの更新によって構成される。

UPDATE chojin
SET power = power - 10
WHERE category = '正義超人';

UPDATE chojin
SET power = power + 50
WHERE category = '悪魔超人';

上記2つの更新は、必ずセットで行われる必要がある。このように、「ワンセットで行われるべき更新の集合」は、必ず「トランザクション」としてひとまとめに扱う必要がある。

なお、1つのトランザクションに「どの程度の数の更新処理を含むか」あるいは、「どんな処理を含むか」という点についての固定的な基準は、DBMS側にはない。それはあくまで、ユーザの要求に従って決められるものだからである。

トランザクションを作るには

トランザクションを開始するには、「トランザクション開始文」と「トランザクション終了文」で更新を行うDML文（INSERT/UPDATE/DELTE文）を囲む、という形をとっている。

「トランザクション開始文」は、MySQLでは「START TRANSACTION」である。

例えば、上記の2つのUPDATEを使って、トランザクションを作ると、以下のようになる。

START TRANSACTION;
UPDATE chojin
SET power = power - 10
WHERE category = '正義超人';
UPDATE chojin
SET power = power + 50
WHERE category = '悪魔超人';
COMMIT;

COMMIT－処理の確定

COMMITとは、トランザクションに含まれていた処理による変更をすべて反映して、トランザクションを終了するコマンドである。一度コミットしたら、もうトランザクションの開始前の状態に戻すことはできないので、コミットする前には、本当に変更を確定して良いか自問自答する必要がある。

■ROLLBACK－処理の取り消し

ROLLBACKは、トランザクションに含まれていた処理による変更をすべて破棄して、トランザクションを終了するコマンドである。ロールバックしたら、データベースの状態はトランザクションを開始する前の状態に戻る。一般的にコミットと違ってロールバックが大きな損失につながることはない。

トランザクションをローバックする例。

START TRANSACTION;
UPDATE chojin
SET power = power - 10
WHERE category = '正義超人';
UPDATE chojin
SET power = power + 50
WHERE category = '悪魔超人';
ROLLBACK;

上記のサンプルコードは、実行してもテーブルのデータに一切変更が生じない。最終行の「ROLLBACK」によって、処理がすべてキャンセルされるからである。そのため、コミットと違って、ロールバックするときは、比較的気軽に実行しても構わない。

ACID特性

DBMSのトランザクションには、守るべき4つの大事な約束事が標準規格によって取り決められている。「ACID特性」と呼ばれているが、これらの約束は、どんなDBMSも守らなければならない一般的なルールである。

原子性（Atomicity）

トランザクションが終わったとき、そこに含まれていた更新処理は、すべて実行されるか、またはすべて実行されない状態で終わることを保証する性質のこと。例えば、上記の例を使用すると、正義超人の超人強度は10下げる処理は行われたが、悪魔超人の超人強度を50上げる処理は行われていない、という状態でトランザクションが終わることは絶対にない。この場合トランザクションの終了状態は2つとも実行される（COMMIT）か、または2つとも実行されない（ROLLBACK）か、二者択一である。

なぜこのような原子性が重要であるかは、トランザクションが中途半端な終わり方をすることがあり得る場合を考えると分かる。ユーザが2つのUPDATE文を1つのトランザクションとして定義したのに、DBMSが気分によってその片方しかじっこうしてくれない、なんてことがあっては、業務に支障をきたすことは明らかである。

一貫性（Consistency）

トランザクションに含まれる処理は、データベースにあらかじめ設定された制約、例えば主キーやNOT NULL制約を満たす、という性質である。例えば、NOT NULL制約の付加された列をNULLに更新したり、主キーの制約違反のレコードを挿入するようなSQL文は、エラーになり、実行できない。これをトランザクション的な言い方で表現すると、そういう違法なSQLは「ロールバックされた」ということになる。要するにそういうSQLは、一文単位で実行が取り消され、実行されなかったのと同じことになるのである。

なお、一貫性は「整合性」とも呼ばれる。

独立性（Isolation）

トランザクション同士が互いに干渉を受けないことを保証する性質である。この性質によって、トランザクション同士が入れ子になることがない。また、あるトランザクションによる変更は、トランザクション終了時までは、別のトランザクションから隠蔽される。したがって、あるトランザクションがテーブルにレコードを追加していたとしても、コミットされるまでは、ほかのトランザクションからはその新規に追加されたレコードは「見えない」状態にある。

永続性（Durability）

これは耐久性といっても良いのであるが、トランザクションが（コミットにせよロールバックにせよ）終了したら、その時点でのデータの状態が保存されることを保証する性質である。たとえシステム障害が発生してデータが失われたとしても、データベースは何らかの手段でこれを復旧させる手段を持たなければならない。

永続性がないと、せっかく無事にトランザクションをコミットして終了させても、システムにい障害が発生してデータが全部消えて最初から処理を全部やり直す必要がある、といった脱力してしまうような状況が起きてしまう。

この永続性を保証する方法は、実装によって異なるが、一番ポピュラーなものは、トランザクションの実行記録をディスクなどに保存しておき（このような実行記録を「ログ」と呼ぶ）、障害が起きた場合は、このログを使って障害前の状態に復旧する、という方法である。

MySQL講座#11 データの更新（UPDATE文の使い方）

テーブルにINSERT文でデータを登録した後、登録済みのデータを変更したいと思うことがある。例えば、「超人の身長を間違えて登録してしまった」などである。そんなとき、データを削除して再登録するなどという面倒な方法をとる必要はない。UPDATE文によって、テーブルのデータを変更することが可能。

UPDATE文は、INSERT文やDELETE文と同じくDML文に属する。これを利用することで、テーブルのデータを変更することができる。基本的な構文は以下の通り。

UPDATE ＜テーブル名＞ SET ＜列名＞ = ＜式＞

更新対象の列と、更新後の値は、SET句に記述する。

それではまず、chojinテーブルのcrated列（登録日）を全行、「2020年2月9日」で統一してみる。

UPDATE chojin
SET created = '2020-02-09';

結果

+------+--------------------------------+------------+
| id   | name                           | created    |
+------+--------------------------------+------------+
| 0001 | キン肉マン                     | 2020-02-09 |
| 0002 | テリーマン                     | 2020-02-09 |
| 0003 | ロビンマスク                   | 2020-02-09 |
| 0004 | ウォーズマン                   | 2020-02-09 |
| 0005 | ラーメンマン                   | 2020-02-09 |
| 0006 | ブロッケンＪｒ                 | 2020-02-09 |
| 0007 | ウルフマン                     | 2020-02-09 |
以下省略

条件を指定したUPDATE文（探索型UPDATE）

次に、全行更新するのではなく、更新対象の行を制限してみる。行を制限するには、DELETE文のときと同様、WHERE句を使うことで可能。こういう対象行を制限したUPDATE文のことを、「探索型UPDATE」と呼ぶ。構文は次の通り。

UPDATE ＜テーブル名＞
SET ＜列名＞ = ＜式＞
WHERE ＜条件＞;

例えば、categoryが「正義超人」の行に限って、超人強度（power）を10倍にするには、以下のように書く。

現状のデータ。

SELECT id, name, power FROM chojin WHERE category = '正義超人';

結果

+------+--------------------------+-------+
| id   | name                     | power |
+------+--------------------------+-------+
| 0001 | キン肉マン               |    95 |
| 0002 | テリーマン               |    95 |
| 0003 | ロビンマスク             |    96 |
| 0004 | ウォーズマン             |   100 |
| 0005 | ラーメンマン             |    97 |
| 0006 | ブロッケンＪｒ           |    90 |
| 0007 | ウルフマン               |    80 |
| 0043 | スペシャルマン           |     0 |
| 0044 | カナディアンマン         |     0 |
+------+--------------------------+-------+

UPDATE文を実行。

UPDATE chojin
SET power = power * 10
WHERE category = '正義超人';

結果

+------+--------------------------+-------+
| id   | name                     | power |
+------+--------------------------+-------+
| 0001 | キン肉マン               |   950 |
| 0002 | テリーマン               |   950 |
| 0003 | ロビンマスク             |   960 |
| 0004 | ウォーズマン             |  1000 |
| 0005 | ラーメンマン             |   970 |
| 0006 | ブロッケンＪｒ           |   900 |
| 0007 | ウルフマン               |   800 |
| 0043 | スペシャルマン           |     0 |
| 0044 | カナディアンマン         |     0 |
+------+--------------------------+-------+

値を元に戻す。

UPDATE chojin
SET power = power / 10
WHERE category = '正義超人';

結果

+------+--------------------------+-------+
| id   | name                     | power |
+------+--------------------------+-------+
| 0001 | キン肉マン               |    95 |
| 0002 | テリーマン               |    95 |
| 0003 | ロビンマスク             |    96 |
| 0004 | ウォーズマン             |   100 |
| 0005 | ラーメンマン             |    97 |
| 0006 | ブロッケンＪｒ           |    90 |
| 0007 | ウルフマン               |    80 |
| 0043 | スペシャルマン           |     0 |
| 0044 | カナディアンマン         |     0 |
+------+--------------------------+-------+

NULLで更新するには

NULLのデータで更新するため、列「created」のNOT NULL制約を外す。

ALTER TABLE chojin MODIFY COLUMN created DATE;

SHOW COLUMNS FROM chojin;

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | char(4)      | NO   | PRI | NULL    |       |
| name      | varchar(100) | NO   |     | NULL    |       |
| kana      | varchar(100) | NO   |     | NULL    |       |
| category  | varchar(100) | NO   |     | NULL    |       |
| attribute | varchar(100) | YES  |     | NULL    |       |
| height    | int(11)      | YES  |     | NULL    |       |
| weight    | int(11)      | NO   |     | NULL    |       |
| power     | int(11)      | NO   |     | NULL    |       |
| origin    | varchar(100) | NO   |     | NULL    |       |
| created   | date         | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

UPDATEを使うことで、列をNULLで更新することもできる。例えば、idが「0043」のスペシャルマンと「0044」のカナディアンマンの登録日（created）をNULLにしてみる。

UPDATE chojin
SET created = NULL
WHERE id IN ('0043', '0044');

結果

+------+--------------------------+---------+
| id   | name                     | created |
+------+--------------------------+---------+
| 0043 | スペシャルマン           | NULL    |
| 0044 | カナディアンマン         | NULL    |
+------+--------------------------+---------+

複数列の更新

UPDATEのSET句には、複数の列を対象として記述することが可能。上記で正義超人の超人強度を10倍にしたが、同時に身長に50を加える場合どうしたら良いか。

同様に2つのUPDATE文で実行することは可能である。それはそれで正しく更新は行われるが、二度もUPDATE文を実行するのは無駄であるし、SQLの記述量も増える。1つのUPDATE文にまとめて書けば良い。以下、2種類の方法がある。

UPDATE文を1つにまとめる方法①

UPDATE chojin
SET power = power * 10,
height = height + 50
WHERE category = '正義超人';

UPDATE文を1つにまとめる方法②

UPDATE chojin
SET (power, height) = (power * 10, height + 50)
WHERE category = '正義超人';

もちろん、SET句は2列だけでなく、3列並べることも可能。

注意が必要なのは、列をカンマで区切って並べる①の方法は、どのDBMSでも利用することが可能な一方、列をリスト化する②の方法は、一部のDBMSでしか利用できないことである。したがって、基本的には①の方法を使うのが確実である。

MySQL講座#10 データの削除（DELETE文の使い方）

DROP TABLE文とDELETE文

データの登録方法が分かったら、次はデータの削除である。データの削除方法は、大きく分けて2つある。

①DROP TABLE文によって、テーブルそのものを削除する
②DELETE文によって、テーブルは残したまま、テーブル内のすべての行を削除する

①のDROP TABLE文は、テーブルごとすべて削除するため、一度削除した後にデータを再登録するには、CREATE TABLE文でテーブルの作成から始めなければならない。

これに対し、②のDELETE文の場合は、データ（行）を削除してもテーブルは残っているため、INSERT文によってすぐにデータを再登録することができる。

本ページのテーマは「データの削除」ということで、データの削除のみするDELETE文の使い方を学ぶ。

DELETE文の基本構文

DELETE文の基本構文は、非常に単純なものである。

DELETE FROM ＜テーブル名＞;

この基本構文に沿ってDELETE文を実行すると、指定したテーブルのすべての行を削除する。したがって、chojinテーブルの全行削除して空っぽにするならば、以下のように書く。

[sql] DELETE FROM chojin; [/sql]

FROMを忘れて「DELETE ＜テーブル名＞」と書いたり、列名をつけようとして「DELETE ＜列名＞ FROM ＜テーブル名＞」と書いたりする間違いを見かけることがあるが、いずれもエラーとなって正しく動作しない。

後者が間違いである理由は、DELETE文における削除対象は、列ではなく行なので、DELETE文で一部の列だけを削除することはできない。したがって、DELETE文で列名を指定することはできない。当然ながら、アスタリスクを使って「DELETE * FROM chojin;」と書くのも間違いで、エラーになる。

削除対象を制限したDELETE文（探索型DELETE）

テーブルの全行ではなく、一部の行だけを削除する場合は、SELECT文の場合と同様、WHERE句で条件を記述する。このように削除対象のレコードを制限したDELETE文のことを、「探索型DELETE」と呼ぶ。

例えば、超人強度（power）が4000以上の行だけを削除したい場合を考える。

[sql] DELETE FROM chojin WHERE power >= 4000; [/sql]

WHERE句の記述方法は、これまでSELECT文を使ってきたものと全く同じように考えて構わない。

なお、SELECT文と違って、DELETE文にはGROUP BY、HAVING、ORDER BYの3つの句は指定できない。使えるのはWHERE句だけである。GROUP BYやHAVINGというのは、元となるテーブルからデータを選択するときに、「抽出する形を変えたい」という場合に使用する。ORDER BYも、結果の表示順を指定するのが目的である。そのため、テーブルのデータそのものを削除するときには、そもそも出番がない訳である。

削除と切り捨て

テーブルからデータを削除する方法として、標準SQLが用意しているのはDELETE文だけである。しかし、多くのデータベース製品には、「TRUNCATE」というコマンドが用意されている。

TRUNCATEとは「切り捨てる」という意味で、具体的には次のように使う。

TRUNCATE ＜テーブル名＞;

DELETEと違って、TRUNCATEは、必ずテーブルを全行削除する。WHERE句で条件を指定して、一部の行だけを削除するということはできない。したがって、細かい制御はできないが、その代わり、DELETE文よりも削除の処理が高速であるというメリットがある。

実はDELETE文というのは、DMLの中でもかなり実行に時間がかかる処理であるため、全行削除して構わない場合は、TRUNCATEを使うことで実行時間を短縮できる。

MySQL講座#09 データの登録（INSERT文の使い方）

INSERTとは

CREATE TABLEで作成した箱の中に「データ」を詰めていくことで、はじめてデータベースは有用なものとなる。このデータを詰めるために使うSQLが、INSERT（挿入）である。

INSERTを学習するため、「cojin2」という名前のテーブルを作成する。テーブルの内容は、列数が減り、power列の制約が「DEFAULT 0」になっている。

[sql] CREATE TABLE chojin2 (id CHAR(4) NOT NULL, name VARCHAR(100) NOT NULL, kana VARCHAR(100) NOT NULL, category VARCHAR(100) NOT NULL, attribute VARCHAR(100) NOT NULL, power INTEGER DEFAULT 0, created DATE NOT NULL, PRIMARY KEY (id)); [/sql]

INSERT文の基本構文

テーブルにデータを1行追加する。

[sql] INSERT INTO chojin2 (id, name, kana, category, attribute, power, created) VALUES ('0001', 'キン肉マン', 'きんにくまん', '正義超人', 'アイドル超人', '95', NOW()); [/sql]

id列（id）やname列（名前）は文字型なので、挿入する値も'0001'のようにシングルクォーテーションで囲む必要があります。これはcreated（登録日）のような日付型の列も同様である。

また、列名や値をカンマで区切って、外側をカッコ（）でくくった形式をリストと呼ぶ。

列リスト→(id, name, kana, category, attribute, power, created)

値リスト→('0001', 'キン肉マン', 'きんにくまん', '正義超人', 'アイドル超人', '95', NOW())

当然のことながら、テーブル名の後の列リストと、VALUE句の値リストは、列数が一致している必がある。

また、INSERT文は、基本的に1回で1行を挿入する。したがって、複数の行を挿入したい場合は、原則的にその行数だけINSERT文も繰り返し実行する必要がある。

列リストの省略

テーブル名の後の列リストは、テーブルの全列に対してINSERTを行う場合、省略することができる。このとき、VALUE句の値が暗黙のうちに、左から順に各列に割り当てられる。したがって、以下のINSERT文はともに同じデータを挿入する。

列リストあり

[sql] INSERT INTO chojin2 (id, name, kana, category, attribute, power, created) VALUES ('0002', 'テリーマン', 'てりーまん', '正義超人', 'アイドル超人', '95', NOW()); [/sql]

列リストなし

[sql] INSERT INTO chojin2 VALUES ('0002', 'テリーマン', 'てりーまん', '正義超人', 'アイドル超人', '95', NOW()); [/sql]

NULLを挿入する

INSERT文で、ある列にNULLを割り当てたい場合は、VALUE句の値リストにNULLをそのまま記入する。例えば、power列（超人強度）にNULLを割り当てるには、以下のようなINSERT文になる。

[sql] INSERT INTO chojin2 (id, name, kana, category, attribute, power, created) VALUES ('0003', 'ロビンマスク', 'ろびんますく', '正義超人', 'アイドル超人', NULL, NOW()); [/sql]

ただし、NULLを割り当てられる列は、当然のことであるが、NOT NULL制約のついていない列に限られる。NOT NULL制約のついている列にNULLを指定した場合、INSERT文はエラーとなり、データの挿入に失敗する。

なお、「挿入に失敗する」とは、INSERT文を登録しようとしたデータが登録できなかった、ということである。これまでテーブルで登録されていたデータが消えたり壊れてしまうことはない。

デフォルト値を挿入する

テーブルの列には、デフォルト値（初期値）を設定することができる。デフォルト値を設定するには、テーブルを定義するCREATE TABLE文の中で、列に対してDEFAULT制約をつける。

chojin2テーブルの定義で、「DEFAULT 0」という箇所がDEFAULT制約を設定している部分。

テーブルの定義時にデフォルト値が設定されていた場合、自動的にそれをINSERT文の列の値として利用することができる。利用方法には、「明示的な方法」と「暗黙的な方法」がある。

①明示的にデフォルト値を挿入する

VALUES句にDEFAULTキーワードを指定する。

[sql] INSERT INTO chojin2 (id, name, kana, category, attribute, power, created) VALUES ('0003', 'ロビンマスク', 'ろびんますく', '正義超人', 'アイドル超人', DEFAULT, NOW()); [/sql]

こうすることで、RDBMSは自動的に列のデフォルト値を使用してレコードの挿入を行う。

暗黙的にデフォルト値を挿入する

デフォルト値の挿入は、DEFAULTキーワードを使用しなくても行うことが可能である。単純にデフォルト値が設定されている列を、列リストからもVALUESからも省略してしまえば良いのである。

[sql] INSERT INTO chojin2 (id, name, kana, category, attribute, created) VALUES ('0003', 'ロビンマスク', 'ろびんますく', '正義超人', 'アイドル超人', NOW()); [/sql]

この場合もやはりpowerにデフォルト値の0が使われる。

実際にはどちらの方法を使うのが良いか、という点が問題になる。私見ではあるが、「明示的な書き方」をおすすめする。なぜなら、こちらのほうがぱっと見てpowerにデフォルト値が利用されることが分かり、意味の捉えやすいSQLになるからである。

なお、列名の省略の話が出たついでに、もう1つ述べておくと、デフォルト値が設定されていない列を省略した場合は、NULLが割り当てられる。したがって、NOT NULL制約がつけられている列を省略すると、INSERT文はエラーになる。

ほかのテーブルからデータをコピーする

データを挿入する方法として、VALUES句で具体的なデータを指定する以外に、「ほかのテーブルから選択する」という方法もある。ここでは、あるテーブルのデータを選択し、それを別のテーブルへコピーして登録する方法を学ぶ。

まずサンプルテーブルを作る。

[sql] CREATE TABLE chojinCopy (id CHAR(4) NOT NULL, name VARCHAR(100) NOT NULL, kana VARCHAR(100) NOT NULL, category VARCHAR(100) NOT NULL, attribute VARCHAR(100) NOT NULL, height INTEGER NOT NULL, weight INTEGER NOT NULL, power INTEGER NOT NULL, origin VARCHAR(100) NOT NULL, created DATE NOT NULL, PRIMARY KEY (id)); [/sql]

chojinCopyテーブルのテーブル定義は、chojinテーブルを全く同じである。テーブル名だけを変えている。さっそく、chojinCopyテーブルにchojinテーブルのデータを挿入してみる。

SELECTした結果をそのままテーブルにINSERTすることができる。

[sql] INSERT INTO chojinCopy (id, name, kana, category, attribute, height, weight, power, origin, created) SELECT id, name, kana, category, attribute, height, weight, power, origin, created FROM chojin; [/sql]

INSERT … SELECT文を実行すると、元のchojinテーブルに44行のデータが入っていたとすれば、chojinCopyテーブルにも全く同じ44用のデータが追加される。INSERT … SELECT文は、データのバックアップをとるような場合にも使用することができる。

SELECT文のバリエーション

このINSERT文内のSELECT文には、WHERE句やGROUP BY句など使うことができる。テーブル同士でデータをやり取りしたい場合に便利な機能である。

例えば、GROUP BY句を使ったSELECT文をINSERTしてみる。INSERT先のテーブルとして、以下のテーブルを作る。

[sql] CREATE TABLE ChojinCategory (category VARCHAR(20) NOT NULL, avg_height INTEGER, avg_weight INTEGER, PRIMARY KEY (category)); [/sql]

超人の分類（category）ごとに身長の平均（avg_height）と体重の平均（avg_weight）を保持するためのテーブルである。ここに、chojinテーブルからデータを挿入するならば、以下のようにINSERT … SELECT文を使う。

[sql] INSERT INTO ChojinCategory (category, avg_height, avg_weight) SELECT category, AVG(height), AVG(weight) FROM chojin GROUP BY category; [/sql]

[sql] SELECT * FROM ChojinCategory; [/sql]

結果

[sql] +--------------+------------+------------+ | category | avg_height | avg_weight | +--------------+------------+------------+ | 完璧超人 | 231 | 271 | | 悪魔超人 | 215 | 404 | | 正義超人 | 199 | 90 | +--------------+------------+------------+ [/sql]

MySQL講座#08 検索結果を並べ替える

ORDER BY句

簡単なSELECT文へ戻る。

[sql] SELECT id, name, height, weight, power FROM chojin WHERE category = '悪魔超人'; [/sql]

結果

[sql] +------+-----------------------------+--------+--------+-------+ | id | name | height | weight | power | +------+-----------------------------+--------+--------+-------+ | 0008 | ステカセキング | 214 | 700 | 250 | | 0009 | ブラックホール | 201 | 420 | 200 | | 0010 | ミスター・カーメン | 200 | 150 | 200 | | 0011 | ザ・魔雲天 | 285 | 1000 | 50 | | 0012 | アトランティス | 200 | 130 | 250 | | 0013 | スプリングマン | 104 | 600 | 320 | | 0014 | バッファローマン | 250 | 220 | 1000 | | 0015 | スニゲーター | 210 | 180 | 400 | | 0016 | プラネットマン | 254 | 420 | 500 | | 0017 | ジャンクマン | 190 | 115 | 600 | | 0018 | ザ・ニンジャ | 190 | 115 | 360 | | 0019 | アシュラマン | 203 | 200 | 1000 | | 0020 | サンシャイン | 300 | 1000 | 700 | +------+-----------------------------+--------+--------+-------+ [/sql]

今回話題にしたいのは、結果の内容ではなく、結果が表示されるときの並び順である。

さて、この13行の結果は、いったいどのような順番で並んでいるのだろうか。一見すると、idの小さい順（昇順）のように見える。しかし実はこれはただの偶然にすぎない。答えはランダムなのである。したがって、次に同じSELECT文を実行したときには、前回とは違う結果で表示されるかもしれない。

一般に、テーブルからデータを選択する場合、その順番は、特に指定がない限り、どんな順番で並ぶかは全く分からない。同じSELECT文ですら、実行するたびに並び順が変わる可能性がある。

しかし、順番がちゃんと並んでいないと、結果を使いづらい場合も多くある。そういうケースにおいては、SELECT文の文末にORDER BY句をつけることで明示的に行の順序を指定する。

例えば、悪魔超人を、超人強度（power）の低い順に並べる場合は以下のようになる。

[sql] SELECT id, name, height, weight, power FROM chojin WHERE category = '悪魔超人' ORDER BY power; [/sql]

結果

[sql] +------+-----------------------------+--------+--------+-------+ | id | name | height | weight | power | +------+-----------------------------+--------+--------+-------+ | 0011 | ザ・魔雲天 | 285 | 1000 | 50 | | 0009 | ブラックホール | 201 | 420 | 200 | | 0010 | ミスター・カーメン | 200 | 150 | 200 | | 0008 | ステカセキング | 214 | 700 | 250 | | 0012 | アトランティス | 200 | 130 | 250 | | 0013 | スプリングマン | 104 | 600 | 320 | | 0018 | ザ・ニンジャ | 190 | 115 | 360 | | 0015 | スニゲーター | 210 | 180 | 400 | | 0016 | プラネットマン | 254 | 420 | 500 | | 0017 | ジャンクマン | 190 | 115 | 600 | | 0020 | サンシャイン | 300 | 1000 | 700 | | 0019 | アシュラマン | 203 | 200 | 1000 | | 0014 | バッファローマン | 250 | 220 | 1000 | +------+-----------------------------+--------+--------+-------+ [/sql]

このORDER BY句は、いつどんな場合でも、SELECT文の最後に記述する。これは、行の並べ替え（ソート）は、結果を返す直前で行う必要があるからである。また、ORDER BY句に書く列名を「ソートキー」と呼ぶ。ほかの句との順序関係を表すと次のようになる。

1.SELECT句　→　2.FROM句　→　3.WHERE句　→　4.GROUP BY句　→　5.HAVING句　→　6.ORDER BY句

なお、ORDER BY句は、行の指定をしたいと思わなければ書かなくとも良い。

昇順と降順の指定

上記の例とは反対に、超人強度（power）の高い順、つまり降順に並べる場合は、以下のように列の後ろにDESCキーワードを使う。

[sql] SELECT id, name, height, weight, power FROM chojin WHERE category = '悪魔超人' ORDER BY power DESC; [/sql]

結果

[sql] +------+-----------------------------+--------+--------+-------+ | id | name | height | weight | power | +------+-----------------------------+--------+--------+-------+ | 0014 | バッファローマン | 250 | 220 | 1000 | | 0019 | アシュラマン | 203 | 200 | 1000 | | 0020 | サンシャイン | 300 | 1000 | 700 | | 0017 | ジャンクマン | 190 | 115 | 600 | | 0016 | プラネットマン | 254 | 420 | 500 | | 0015 | スニゲーター | 210 | 180 | 400 | | 0018 | ザ・ニンジャ | 190 | 115 | 360 | | 0013 | スプリングマン | 104 | 600 | 320 | | 0008 | ステカセキング | 214 | 700 | 250 | | 0012 | アトランティス | 200 | 130 | 250 | | 0010 | ミスター・カーメン | 200 | 150 | 200 | | 0009 | ブラックホール | 201 | 420 | 200 | | 0011 | ザ・魔雲天 | 285 | 1000 | 50 | +------+-----------------------------+--------+--------+-------+ [/sql]

実は昇順に並べる場合にも、正式にはASCというキーワードがあるのだが、省略した場合は暗黙に昇順に並べるという約束になっている。

このASCとDESCのキーワードは列単位で指定するものなので、1つの列は昇順を指定し、別の列は降順を指定する、ということも可能である。

複数のソートキーを指定する

ここで、上記で示した超人強度（power）の昇順に並べるSELECT文をもう一度見てみる。1000の超人が2人いることがわかる。この同じ超人強度の超人の順序は、特に指定がない限り、またしてもランダムである。

もし、この「同順位」の超人についても細かく並び順を指定したい場合は、もう1つソートキーを追加する必要がある。ここではidの昇順としてみる。すると以下のようになる。

[sql] SELECT id, name, height, weight, power FROM chojin WHERE category = '悪魔超人' ORDER BY power, id; [/sql]

結果

[sql] +------+-----------------------------+--------+--------+-------+ | id | name | height | weight | power | +------+-----------------------------+--------+--------+-------+ | 0011 | ザ・魔雲天 | 285 | 1000 | 50 | | 0009 | ブラックホール | 201 | 420 | 200 | | 0010 | ミスター・カーメン | 200 | 150 | 200 | | 0008 | ステカセキング | 214 | 700 | 250 | | 0012 | アトランティス | 200 | 130 | 250 | | 0013 | スプリングマン | 104 | 600 | 320 | | 0018 | ザ・ニンジャ | 190 | 115 | 360 | | 0015 | スニゲーター | 210 | 180 | 400 | | 0016 | プラネットマン | 254 | 420 | 500 | | 0017 | ジャンクマン | 190 | 115 | 600 | | 0020 | サンシャイン | 300 | 1000 | 700 | | 0014 | バッファローマン | 250 | 220 | 1000 | | 0019 | アシュラマン | 203 | 200 | 1000 | +------+-----------------------------+--------+--------+-------+ [/sql]

このように、ORDER BY句には、複数のソートキーを指定することが可能。左側のキーから優先的に使用され、そのキーで同じ値が存在した場合に、右のキーが参照される、というルールである。もちろん、3列以上のソートキーを使うことも可能。

NULLの順番

これまでの例では、超人強度（power）をソートキーにしてきたが、今度は身長（height）をソートキーに使ってみる。このとき問題になるのは、スペシャルマンとカナディアンマンの行にあるNULLである。いったい、NULLというのはどういう順序づけがされるのだろうか。

以前のlessonで「NULLに比較演算子は使えない」で学んだ内容を思い出せば良い。NULLには比較演算子は使えないのである。すなわち、NULLと数値の順序付けはできない。文字や日付とも比較できない。したがって、NULLを含む列をソートキーにした場合、NULLは先頭または末尾にまとめて表示される。

[sql] SELECT id, name, height FROM chojin WHERE category = '正義超人' ORDER BY height; [/sql]

結果

[sql] +------+--------------------------+--------+ | id | name | height | +------+--------------------------+--------+ | 0043 | スペシャルマン | NULL | | 0044 | カナディアンマン | NULL | | 0001 | キン肉マン | 185 | | 0002 | テリーマン | 190 | | 0007 | ウルフマン | 190 | | 0006 | ブロッケンＪｒ | 195 | | 0005 | ラーメンマン | 209 | | 0004 | ウォーズマン | 210 | | 0003 | ロビンマスク | 217 | +------+--------------------------+--------+ [/sql]

先頭に来るか末尾に来るかは特に決まっていない。

ソートキーに表示用の別名を使う

GROUP BY句には、SELECT句でつけた別の列名は使うことが許されていない。一方、ORDER BY句ではそれが許されている。

[sql] SELECT name, power AS p FROM chojin WHERE category = '悪魔超人' ORDER BY p; [/sql]

結果

[sql] +-----------------------------+------+ | name | p | +-----------------------------+------+ | ザ・魔雲天 | 50 | | ブラックホール | 200 | | ミスター・カーメン | 200 | | ステカセキング | 250 | | アトランティス | 250 | | スプリングマン | 320 | | ザ・ニンジャ | 360 | | スニゲーター | 400 | | プラネットマン | 500 | | ジャンクマン | 600 | | サンシャイン | 700 | | アシュラマン | 1000 | | バッファローマン | 1000 | +-----------------------------+------+ [/sql]

なぜ、GROUP BY句では使えない別名が、ORDER BY句で使えるのか。その理由は、DBMS内部でSQL文が実行される順序に隠されている。SELECT文の実行順序は、句単位で見ると次のようになっている。

FROM　→　WHERE　→　GROUP BY　→　HAVING　→　SELECT　→　ORDER BY

ここで重要なのは「GROUP BYよりも後で、ORDER BYよりも前」というSELECT句の位置である。したがって、GROUP BY句が実行される時点では、SELECT句でつけることになっている別名を認識できない。SELECT句より後ろのORDER BY句ならば、その心配はないという訳である。

ORDER BY句に使える列

ORDER BY句には、テーブルに存在する列であれば、SELECT句に含まれていない列でも指定できる。

[sql] SELECT name, height, weight FROM chojin WHERE category = '正義超人' ORDER BY id; [/sql]

結果

[sql] +--------------------------+--------+--------+ | name | height | weight | +--------------------------+--------+--------+ | キン肉マン | 185 | 90 | | テリーマン | 190 | 95 | | ロビンマスク | 217 | 155 | | ウォーズマン | 210 | 150 | | ラーメンマン | 209 | 130 | | ブロッケンＪｒ | 195 | 90 | | ウルフマン | 190 | 102 | | スペシャルマン | NULL | 0 | | カナディアンマン | NULL | 0 | +--------------------------+--------+--------+ [/sql]

idはSELECT句に含まれていないが、idの昇順で並んでいる。

また、集約関数も使用できる。

[sql] SELECT category, COUNT(<em>) FROM chojin GROUP BY category ORDER BY COUNT(</em>) DESC; [/sql]

結果

[sql] +--------------+----------+ | category | COUNT(*) | +--------------+----------+ | 完璧超人 | 22 | | 悪魔超人 | 13 | | 正義超人 | 9 | +--------------+----------+ [/sql]

列番号は使ってはいけない

ORDER BY句では、SELECT句に含まれる列を参照する列番号を使うことができる。列番号とは、SELECT句で指定した列を左から1,2,3・・・と順番に割り振った番号である。したがって、以下の2つのSELECT分は同じ意味になる。

[sql] SELECT id, name, height, weight FROM chojin WHERE category = '悪魔超人' ORDER BY height DESC, id; [/sql]

結果

[sql] +------+-----------------------------+--------+--------+ | id | name | height | weight | +------+-----------------------------+--------+--------+ | 0020 | サンシャイン | 300 | 1000 | | 0011 | ザ・魔雲天 | 285 | 1000 | | 0016 | プラネットマン | 254 | 420 | | 0014 | バッファローマン | 250 | 220 | | 0008 | ステカセキング | 214 | 700 | | 0015 | スニゲーター | 210 | 180 | | 0019 | アシュラマン | 203 | 200 | | 0009 | ブラックホール | 201 | 420 | | 0010 | ミスター・カーメン | 200 | 150 | | 0012 | アトランティス | 200 | 130 | | 0017 | ジャンクマン | 190 | 115 | | 0018 | ザ・ニンジャ | 190 | 115 | | 0013 | スプリングマン | 104 | 600 | +------+-----------------------------+--------+--------+ [/sql]

[sql] SELECT id, name, height, weight FROM chojin WHERE category = '悪魔超人' ORDER BY 3 DESC, 1; [/sql]

結果

[sql] +------+-----------------------------+--------+--------+ | id | name | height | weight | +------+-----------------------------+--------+--------+ | 0020 | サンシャイン | 300 | 1000 | | 0011 | ザ・魔雲天 | 285 | 1000 | | 0016 | プラネットマン | 254 | 420 | | 0014 | バッファローマン | 250 | 220 | | 0008 | ステカセキング | 214 | 700 | | 0015 | スニゲーター | 210 | 180 | | 0019 | アシュラマン | 203 | 200 | | 0009 | ブラックホール | 201 | 420 | | 0010 | ミスター・カーメン | 200 | 150 | | 0012 | アトランティス | 200 | 130 | | 0017 | ジャンクマン | 190 | 115 | | 0018 | ザ・ニンジャ | 190 | 115 | | 0013 | スプリングマン | 104 | 600 | +------+-----------------------------+--------+--------+ [/sql]

2つ目のSELECT文のORDER BY句は「SELECT句の3番目の列で降順ソートし、続いて1番目の列で昇順ソートする」という意味になる。

列番号で指定する書き方は、列名を書かなくて良いという手軽さがあって非常に便利であるが、次の2つの理由から使うべきではない。 理由1 コードが読みにくい。列番号を使用すると、ORDER BY句を見ただけではどんな列をソートキーにしているのかわからず、SELECT句のリストを先頭から数えなければならない。この例ではSELECT句の列数が少ないため、あまり気にならないかもしれないが、実務ではもっとたくさんの列を含めることもあるし、SELECT句とORDER BY句の間に大きなWHERE句やHAVING句がはさまって、目で追うことが大変な場合もある。 理由2 この順番項目の機能は、SQL-92において、「将来削除されるべき機能」に挙げられた。したがって、現在は問題なくても、将来、DBMSのバージョンアップを行った際に、これまで動いていたSQLが突如エラーになるという厄介な問題を引き起こす可能性がある。その場限りの使い捨てのSQLならまだしも、システムに組み込むSQLでこの機能を使うことは避けたほうがよい。

MySQL講座#07 集約した結果に条件を指定する

HAVING句

GROUP BY句によって、元のテーブルをグループ分けして結果を得ることができるようになった。ここでは、さらにｓのグループに対して条件を指定して選択する方法を考える。例えば、「集約した結果がちょうど7行になるようなグループ」を選択するにはどうすれば良いか。

条件指定といえばWHERE句が真っ先に思い浮かぶかもしれない。しかし、WHERE句はあくまで「レコード（行）」に対してのみしか条件を指定できないため、グループに対する条件指定には使えないという制限がある。

したがって、そういう集合に対する条件を指定する句が、別に必要となる。それがHAVING句なのである。

HAVING句を書く位置は、GROUP BY句の後ろである必要がある。DBMS内部での実行順序も、GROUP BY句の後ろになる。

SELECT　→　FROM　→　WHERE　→　GROUP BY　→　HAVING

ではHAVING句を実際に使用してみる。例えば、categoryで集約したグループに対して、「含まれる行数が9行」という条件を指定すると以下のようになる。

[sql] SELECT category, COUNT(<em>) FROM chojin GROUP BY category HAVING COUNT(</em>) = 9; [/sql]

結果

[sql] +--------------+----------+ | category | COUNT(*) | +--------------+----------+ | 正義超人 | 9 | +--------------+----------+ [/sql]

HAVING句を使用する例をもう1つ挙げてみる。今度も同じくcategoryでグループ化するが、条件を「身長（height）の平均が210以上」に変更する。

HAVING句なしで選択した場合。

[sql] SELECT category, AVG(height) FROM chojin GROUP BY category; [/sql]

結果

[sql] +--------------+-------------+ | category | AVG(height) | +--------------+-------------+ | 完璧超人 | 230.6364 | | 悪魔超人 | 215.4615 | | 正義超人 | 199.4286 | +--------------+-------------+ [/sql]

categoryの3つのグループすべてが結果に表示されていることが分かる。ここにHAVING句で条件を設定する。と以下のようになる。

[sql] SELECT category, AVG(height) FROM chojin GROUP BY category HAVING AVG(height) &gt;= 210; [/sql]

結果

[sql] +--------------+-------------+ | category | AVG(height) | +--------------+-------------+ | 完璧超人 | 230.6364 | | 悪魔超人 | 215.4615 | +--------------+-------------+ [/sql]

身長の平均が199.4286だった正義超人が、結果から消えている。

HAVING句にかける要素

HAVING句も、GROUP BY句を使ったときのSELECT句と同様に、書くことができる要素が制限される。制限内容は全く同じで、HAVING句に書くことができる要素は次の3つになる。

• 定数
• 集約関数
• GROUP BY句で指定した列名（集約キー）

HAVING句でエラーになる例。

[sql] SELECT category, COUNT(*) FROM chojin GROUP BY category HAVING name = 'キン肉マン'; [/sql]

結果

[sql] ERROR 1054 (42S22): Unknown column 'name' in 'having clause' [/sql]

nameという列は、GROUP BY句に含まれていない。したがってHAVING句に書くことも許されない。HAVING句の使い方を考えるときは、「一度集約が終わった段階のテーブルを出発点にしている」と考えると分かりやすい。

このことは、もちろんGROUP BY句を使った場合のSELECT句を考えるときにも当てはまる。テーブルにない列は指定することができない。

HAVING句よりもWHERE句に書いたほうが良い条件

HAVING句にもWHERE句にも書ける条件というのが存在する。それは、「集約キーに対する条件」である。元のテーブルの列のうち、集約キーとして使っているものは、HAVING句にも書くことができる。したがって、以下のSELECT文は正しい構文である。

[sql] SELECT category, COUNT(*) FROM chojin GROUP BY category HAVING category = '悪魔超人'; [/sql]

結果

[sql] +--------------+----------+ | category | COUNT(*) | +--------------+----------+ | 悪魔超人 | 13 | +--------------+----------+ [/sql]

このSELECT文は、以下のように書いた場合と同じ結果を返す。

[sql] SELECT category, COUNT(*) FROM chojin WHERE category = '悪魔超人' GROUP BY category; [/sql]

結果

[sql] +--------------+----------+ | category | COUNT(*) | +--------------+----------+ | 悪魔超人 | 13 | +--------------+----------+ [/sql]

条件を書く場所がWHERE句かHAVING句かの違いだけで、条件の内容は同じ、返す結果も同じ。したがって、どちらの書き方をしても良いのではないか、と思われるかもしれない。

選択される結果を見るだけなら、その通りである。しかし、こういった集約キーに対する条件はWHERE句に書くべきだと考えられる。

理由は、WHERE句とHAVING句の役割の違いという、根本的なものである。HAVING句というものは、「グループ」に対する条件を指定するものである。したがって、単なる「行」に対する条件は、WHERE句で書くようにしたほうが、互いの機能をはっきりさせることができて、読みやすいコードになる。

WHERE句　＝　行に対する条件指定
HAVING句　＝　グループに対する条件指定

WHERE句とHAVING句の実行速度

WHERE句とHAVING句のどちらにも書ける条件を、あえてWHERE句に書くべきもう1つの理由は、パフォーマンス、つまり実行速度に関するものである。一般的に、同じ結果を得られるにせよ、HAVING句よりもWHERE句に条件を記述するほうが、処理速度が速い。

その理由を説明するには、DBMS内部で行われている処理について考える必要がある。COUNT関数などを使って、テーブルのデータを集約する場合、DBMS内部では「ソート」という行の並べ替えが行われている。このソート処理は、かなりマシンに負担をかける、いわゆる「重い」処理である。そのため、なるべくソートする行が少ないほうが、処理が速くなるのである。

WHERE句を使って条件を指定すると、ソート前の行を絞り込むため、ソート対象の行数を減らすことができる。一方、HAVING句はソートが終わってグループ化された後に実行されるため、WHERE句で条件を指定する場合よりソート行数が多くなってしまうのである。こうした内部動作はDBMSによっても異なるが、このソート処理に関しては、多くのDBMSに共通していると言える。

そしてWHERE句が速度の面で有利なもう1つの理由は、WHERE句の条件で指定する列に「索引（インデックス）」を作成することで、処理を大幅に高速化することが可能なことである、この索引という技術は、DBMSのパフォーマンス向上の方法として非常にポピュラーで、かつ効果の高いものであるから、WHERE句にとって非常に有利な材料なのである。