MySQLの基礎を勉強してみた
実践ハイパフォーマンスMySQL 第3版を読むに当たって、基礎を復習しといた方がいいだろう(触って覚えたので勉強らしい勉強はしてこなかった)と基礎からのMySQL [基礎からのシリーズ] (プログラマの種シリーズ)を読んでみました。
その中から自分が知らなかったことを連々と書いていきます。
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 を付けるというものだったんですが、これだと最大値を持つレコードが複数あったときに対応できないとのこと。
確かに。。。
このようなクエリを実現するためには、
- 最大値を求める
- 最大値を持つレコードを取得
と、2段階で行う必要があります。
感想
結構すんなり行けると思ってたけど多い。
そして来週に続きます。