footmark

ひよっこエンジニアの足跡

MySQLの基礎を勉強してみた

実践ハイパフォーマンスMySQL 第3版を読むに当たって、基礎を復習しといた方がいいだろう(触って覚えたので勉強らしい勉強はしてこなかった)と基礎からのMySQL [基礎からのシリーズ] (プログラマの種シリーズ)を読んでみました。

その中から自分が知らなかったことを連々と書いていきます。

クエリとSQL

お恥ずかしい話、クエリとSQLの違いを説明しろと言われたらできなかった自分です。。。

  • クエリ:検索や挿入などの命令そのもの
  • SQL:クエリを表現するための言語

mysqladmin の存在

一般にMySQLを操作するときはターミナルで mysql コマンドを打ち、

mysql>

の画面で行いますが、これをMySQLモニタといいます。
これに対し、ターミナルから直接操作できるコマンドが mysqladmin です。

例えば、データベース作成は

mysqladmin -u [ユーザ名] -p[パスワード] CREATE [データベース名]

で行えます。
ちなみにSELECT文なんかは実行できませんでした。
SELECT文をターミナルから直接実行したいときは mysql コマンドに -e オプションを付けてあげるといいみたいです。

mysql [データベース名] -u [ユーザ名] -p[パスワード] -e "[SQL文]"

SQL文はダブルクォーテーションで囲み、シングルクォーテーションは使えません。

しかしこれ、パスワードがターミナル上に見えてなんかよろしくないですね。。。

GROUP BY

SELECTの結果をグループごとに表示することができます。
存在とできることは知っていましたが、詳しい挙動は知りませんでした。
書式は

SELECT [カラム名] FROM [テーブル名] GROUP BY [グループ化するカラム名];

で、GROUP BYの後に指定したカラムの重複を除いた結果が表示されます。
ポイントは表示において指定したカラムの重複はないが、処理自体は全てのレコードが対象だという点です。

例えば以下のようなテーブルがあったとします。

学生テーブル

学籍番号 クラス
101 A
102 A
103 B
104 C
105 C

これに対し次のSQLを実行します。

SELECT * FROM 学生テーブル GROUP BY クラス;

すると次のような結果が返ってきます。

学籍番号 クラス
101 A
103 B
104 C

このとき学籍番号はそれぞれのクラスから適当に取得されます。
処理は全てのレコードが対象であるということですが、これは各クラスの人数を計算したいときなどに利用します。
SQLではこう書きます。

SELECT クラス, COUNT(*) AS 人数 FROM 学生テーブル;

その結果。

クラス 人数
A 2
B 1
C 2

WHERE と HAVING

SELECTの際にWHEREで条件を付けることができますが、GROUP BY 関連の条件句にはこれの他に HAVING があります。
それぞれの挙動は異なり

  • WHERE:グループ化の前に条件付きで取得
  • HAVING:グループ化の後に条件付きで取得

の違いがあります。

内部結合と外部結合

復習の意味も兼ねて。

  • 内部結合:両方のキーが一致しているレコードを取り出す
  • 外部結合:両方のキーが一致していない場合も一方のレコードは全て出力する

外部結合の際、左外部結合と右外部結合は組み合わせて使用してもエラーはでないが、そのような書き方(混在)は避けた方が良い。

自己結合

あるテーブル同士を結合させることです。
同じテーブル同士なのでエイリアスが必須になります。

先の学生テーブルを自己結合させると次のようなテーブルができあがります。

学籍番号1 クラス1 学籍番号2 クラス2
101 A 101 A
102 A 101 A
103 B 101 A
104 C 101 A
105 C 101 A
101 A 102 A
102 A 102 A
103 B 102 A
104 C 102 A
105 C 102 A
101 A 103 B
102 A 103 B
103 B 103 B
104 C 103 B
105 C 103 B
101 A 104 C
102 A 104 C
103 B 104 C
104 C 104 C
105 C 104 C
101 A 105 C
102 A 105 C
103 B 105 C
104 C 105 C
105 C 105 C

要は全ての組み合わせを含むテーブルが作成されます。

順位付けの難しさ

SELECTの結果に順位付けをしたいとします。
これ、ORDER BY で簡単にできそうですが、並び替えるだけで順位の表示まではできません。
これを実現するためには自己結合、ORDER BY 、GROUP BY を組み合わせます。
例に以下のテーブルを用います。

成績テーブル

学籍番号 点数
101 60
102 100
103 30

成績テーブルを自己結合すると

学籍番号1 点数1 学籍番号2 点数2
101 60 101 60
102 100 101 60
103 30 101 60
101 60 102 100
102 100 102 100
103 30 102 100
101 60 103 30
102 100 103 30
103 30 103 30

となります。
これを学籍番号2でグループ化します。
説明のために一旦101のグループを取り出すと、

学籍番号1 点数1 学籍番号2 点数2
101 60 101 60
102 100 101 60
103 30 101 60

となります。
この中から「点数2 <= 点数1」が真となるレコードを数えると、それが順位となります。
今回は

学籍番号1 点数1 学籍番号2 点数2
101 60 101 60
102 100 101 60

この2つです。
実際、成績テーブルを見ても学籍番号101の学生は2位であることが確認できると思います。

これをSQLで表すと

SELECT 成績テーブル1.学籍番号, 成績テーブル1.成績, COUNT(*)
    FROM 成績テーブル AS 成績テーブル1
JOIN 成績テーブル AS 成績テーブル2
    WHERE 成績テーブル2.成績<=成績テーブル1.成績
GROUP BY 成績テーブル2.成績

となります。
面倒くさい。

と思いきや、先のページに

AUTO INCREMENTの順位カラムを持つTABLEを作成し、成績順に並び替えた成績テーブルをコピーしていく方法も紹介されていました。
うーん、順位付けと聞いてパッと思い浮かぶのはこっちかな〜

意外にパッできない「最大値を持つレコードの取得」

順位付けに続いて、最大値を持つレコードの取得も簡単にはできません。
僕が最初に思い付いたのは、並び替えて LIMIT 1 を付けるというものだったんですが、これだと最大値を持つレコードが複数あったときに対応できないとのこと。
確かに。。。
このようなクエリを実現するためには、

  1. 最大値を求める
  2. 最大値を持つレコードを取得

と、2段階で行う必要があります。

感想

結構すんなり行けると思ってたけど多い。
そして来週に続きます。