バナー画像

MySQLで、サブクエリーの結果を共通で使えるwith句が使えるのをご存じでしょうか。しかし、コマンドラインのMySQLクライアントツールで、with句を含む複雑なSQLのデバックは容易ではありません。GUIな管理ツールが必要です。そこで、比較的簡単に使えるMySQLの管理ツール、phpMyAdminを使ったwith句を含むSQLの実行手順を紹介します。

SQLにおけるwith句とは

Oracle SQLやDB2といったエンタープライズ用途向けのSQLに慣れたベテランエンジニアなら、複数のサブクエリーを使う複雑なSQLでも、with句を使ってスマートに書いています。しかし、以前のMySQLではwith句が使えませんでした。しかし、最新のMySQLなら、withが使えます。

エンタープライズ用途向けのSQLをフリーのデータベースで利用するなら、ぜひ、MySQLのバージョン8以降の利用を検討してください。MySQLのバージョン8では、Oracle SQLやDB2などのエンタープライズ用途向けだった多くの機能が導入されており、with句も使えるようになりました。まずは、MySQLで使えるようになったwith句について解説します。

SQLのwith句とは

with句は、1つのSQL文の中で共通したテーブルを簡略的に呼び出す機能です。そして、with句で呼び出すテーブルのことを、再帰共通表式(テーブル)と呼びます。

なお、with句がよく使われるのはサブクエリーです。サブクエリーとは、クエリーの中でデータベースに格納されたテーブルの代わりに、クエリーによって作られたテーブルを使うための機能で、SQLの中に幾つものサブクエリーを記述することが可能です。

そのため、サブクエリーが入れ子になるなど、複雑なSQLが使われるケースがあり、その中には同じサブクエリーを幾つも使っていることも珍しくありません。そのような場合、同じサブクエリーをwith句で共通化することで、複雑なSQLをシンプルに書くことが可能です。

MySQLの8.0以降ではwith句が使える

ただし、with句は全てのデータベース管理ソフトで使えた訳ではありません。MySQLのバージョン5では、使えませんでした。しかし、MySQLのバージョン8にからは、それまで使えなかったエンタープライズ向けの機能が使えるようになりました。そして、使えるようになった機能の一つが、再帰共通表式(テーブル)を定義するwith句です。

なお、MySQLは、開発していた会社がエンタープライズ向けデータベース管理ソフトで有名なOracleに買収されたことで、Oracle社が開発するMySQLとオープンソースのmariadbに分かれました。残念ながらオープンソースのmariadbの最新版は、MySQLの最新版との完全な互換性はありません。とはいえ、mariadb最新版なら、with句をサポートしています。ぜひ、そちらのも利用も検討してください。

with句の使用例

次にwith句の使用例を紹介します。

with句を使ったSQLの例

with w1 as ( select * from score )
select T1.mon, T1.name, T1.subtotal,
( select avg(w1.subtotal) from w1 ) as avg,
( select max(w1.subtotal) from w1 ) as max
from  score T1;

この例では、1行目のwith句でサブクエリーを宣言しています。そして、その検索結果のw1を使って平均値と最大値を計算し、テーブルに格納されたデータといっしょに出力します。

上記のSQLをwith句を使わないで書いた場合、次のようになります。

サブクエリーのみで書いたSQLの例

select T1.mon, T1.name, T1.subtotal,
( select avg(sub.subtotal) from
  ( select * from score ) as sub ) as avg,
( select max(sub.subtotal) from
  ( select * from score ) as sub ) as max
from score T1;

with句を使うことで、サブクエリーを共通化できることから、簡潔に書くことが可能です。

MySQLを試すならphpMyAdminが最適

MySQLに限らず、SQLが使えるデータベース管理ソフトを学ぼうとしたら、以前は、コマンドラインから専用のクライアントツールを実行するのが一般的でした。具体的には、MySQLならmysqlコマンドです。

しかし、mysqlコマンドは、使いやすいとは言えません。MySQLを学ぶなら、格納されたデータが見やすく、SQLを実行できるグラフィカルな管理システムの利用をお勧めします。中でも無料で比較的簡単に使えるのがphpMyAdminです。次から、phpMyAdminを使ってSQLを実行する方法についてご紹介します。

phpMyAdminはWebブラウザで使う

phpMyAdminは、Webサービスの構築によく利用されるプログラム言語のPHPで実装されたMySQL専用の管理ツールです。そして、主にLinuxで構築されたWebサーバーに設置して利用します。また、WordPressが使えるレンタルサーバーでは標準で使えることが多いことから、身近なMySQLの管理ツールと言えるでしょう。

なお、phpMyAdminを利用するには、WebブラウザからphpMyAdminのURLを指定して、MySQLのアカウントでログインする必要があります。ただし、Webブラウザで利用することから、通信できるデータの量に制限があり、全てのデータが表示できなかったり、大きなテーブルをファイル書き出せない、などの制限があります。

phpMyAdminでSQLを実行する

phpMyAdminはデータベースに格納されたデータを直接編集する機能の他、SQLを実行し、その結果を表示する機能も備えています。

SQLを実行するには、phpMyAdminにログイン後、左側のタブでデータベースを選択して、右側の「SQL」タグを選んでください。画面が切り替わり、SQLを実行するための画面が表示されます。そこに、SQLを書くか、メモ帳などのテキストエディタに書いたSQLをコピーしてください。そして、実行ボタンを押すと、SQLが実行されます。その実行結果は、テーブル形式で表示されます。

ただし、SQLを書くエリアは、SQL専用のエディタではありません。さらにファイルに保存する機能の無いので、with句を含むSQLのようなサブクエリーを幾つも使ったSQLのデバックには向いていません。そのため、別のエディタでSQLを編集に、コピーして実行するという使い方をおすすめします。

SQLのwith構文をphpMyAdminで試してみる

次から、MySQLでも使えるようになったwith構文を使ったSQLを、phpMyAdminのSQL実行環境で実行する手順を紹介します。

まずはデータベースを選択する

phpMyAdminにログインすると左側にデータベースの一覧が表示され、右側にシステム情報などが表示されます。しかし、この状態では、まだ、データベースが選択されていません。SQLの編集画面を表示できますが、この状態でSQLを書き、実行ボタンを押してもエラーになるので注意しましょう。

まずは、操作対象となるデータベースを選んでください。データベースを選ぶと、画面右側の上部に、サーバー名をデータベース名が表示されます。必ずこの状態で、SQLタブで画面を切り替えてください。

SQLを書いて実行する

phpMyAdminのSQLタブをクリックして切り替えた画面には、SQLを編集するエリアと、各種スイッチなどが表示されます。もちろん、ここでSQLをゼロから書くことも可能ですが、ファイルに保存する機能がありません。あらかじめテキストエディタで作っておき、ファイルに保存してからコピーするようにしましょう。

SQLが書けたら画面下部にある実行ボタンを押してください。SQLの実行結果が表示されます。また、SQLにエラーがあれば、コンソールに表示されるのと同じメッセージが表示されるので、SQLを修正して再度実行してください。

phpMyAdminでのデバック方法

今回実行するwith構文を含んだ複雑なSQLは、予定している結果が出るまでに何度もSQLを書き直すのが一般的です。そういったSQLのデバックにphpMyAdminのSQL実行環境を利用する場合は、「クエリボックスを保持する」スイッチを利用し、SQLと実行結果を同時に表示する機能を利用しましょう。

また、どうしてもエラーが見つけられない場合は、フォーマットボタンを利用してSQLを整形してみてください。間違いが見つけられやすくなります。

さらにSQLの実行結果が大きい場合は、「クリップボードにコピー」を利用しましょう。これを使って表計算ソフトなどにコピーすれば、チェックが簡単になります。

まとめ

MySQLのwith句は、複雑なSQLを解り易く作れる便利な機能です。しかし、それをコマンドラインでデバックするのは、簡単ではありません。それなら、比較的簡単に使えるMySQLの管理ツールのphpMyAdminを活用しましょう。phpMyAdminは、データベース管理ソフトとしては機能が少なく制限も多いものの、無料で設置が容易などのメリットがあり、SQLのデバックなどにも使えます。ぜひ、phpMyAdminを利用して、複雑なwith構文を含むSQLをマスターしましょう。

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。