PREPARATION
OSS-DB入門
第5回 SQL基礎 I
さて、今回からはいよいよデータベースの真骨頂とも言える、SQLを用いたデータの操作を行っていきます。
内容を、SQL基礎I・IIの2回に分け、『SQL基礎I』ではINSERT/SELECT/UPDATE/DELETE（俗に言うCRUD）の使い方を中心に、そして次回の『SQL基礎II』ではテーブル結合やビュー、ストアドプロシージャーといったもう少し踏み込んだ機能を見ていく予定でいます。
実際に皆さんにPostgreSQLを操作してもらうことを主眼に置いた構成としていますので、SQLの細かい文法に関する説明は省略しています。このあたりはリファレンスマニュアルを参考にしながら読み進めてください。
では、早速始めましょう！
内容を、SQL基礎I・IIの2回に分け、『SQL基礎I』ではINSERT/SELECT/UPDATE/DELETE（俗に言うCRUD）の使い方を中心に、そして次回の『SQL基礎II』ではテーブル結合やビュー、ストアドプロシージャーといったもう少し踏み込んだ機能を見ていく予定でいます。
実際に皆さんにPostgreSQLを操作してもらうことを主眼に置いた構成としていますので、SQLの細かい文法に関する説明は省略しています。このあたりはリファレンスマニュアルを参考にしながら読み進めてください。
では、早速始めましょう！
1．SQLとは
SQL（Structured Query Language）とは、リレーショナルデータベース（RDB）を操作・定義するための問い合わせ言語です。ISOによってSQLの仕様が策定されており、PostgreSQL 9.1はその標準仕様であるSQL:2008にほぼ準拠しています。
SQLは、その性質によって、DDL(Data Definition Language：データ定義言語)、DML(Data Manipulation Language：データ操作言語)、DCL(Data Control Language：データ制御言語)の3種類に大別されます。データベースの設計フェーズにあたる第3回『データベースとテーブルの作成』と第4回『データベースの正規化』では主にDDLを扱ってきましたが、今回からは具体的なデータ操作を行っていきますので、DMLを中心に紹介していきます。
SQLは、その性質によって、DDL(Data Definition Language：データ定義言語)、DML(Data Manipulation Language：データ操作言語)、DCL(Data Control Language：データ制御言語)の3種類に大別されます。データベースの設計フェーズにあたる第3回『データベースとテーブルの作成』と第4回『データベースの正規化』では主にDDLを扱ってきましたが、今回からは具体的なデータ操作を行っていきますので、DMLを中心に紹介していきます。
2. データ操作の基礎
DMLの代表である4つのSQLについて概要を確認しておきましょう。
INSERT
- データの挿入
- 書式 ： INSERT INTO テーブル名 [(列名)] VALUES (挿入データ)
SELECT
- データの抽出
- 書式 ： SELECT 列名 FROM テーブル名 WHERE 検索条件
UPDATE
- データの更新
- 書式 ： UPDATE テーブル名 SET 列名 = 更新データ WHERE 検索条件
DELETE
- データの削除
- 書式 ： DELETE テーブル名 WHERE 検索条件
※書式については "\h コマンド名" でも確認することができます。
基本的なデータ操作はこれら4つのコマンドでほぼ実現することができるでしょう。
また、PostgreSQLでは、デフォルトでさまざまな種類の演算子や関数が用意されています。検索条件に数値の大小や文字列比較、日付間隔などを指定して処理対象のデータを絞り込むことができますし、検索結果をグループに分類して、それぞれのグループの合計や平均値を求めたりするような集約処理も可能です。
DMLと演算子・関数を組み合わせることにより、ユーザの要望に応じた結果を柔軟に取得することができるようになっています。
詳細については、最後にオンラインマニュアルのリンクをつけましたので、そちらを参照していただくとして、次の節からはデータ操作の実践へと移ってまいります。
INSERT
- データの挿入
- 書式 ： INSERT INTO テーブル名 [(列名)] VALUES (挿入データ)
SELECT
- データの抽出
- 書式 ： SELECT 列名 FROM テーブル名 WHERE 検索条件
UPDATE
- データの更新
- 書式 ： UPDATE テーブル名 SET 列名 = 更新データ WHERE 検索条件
DELETE
- データの削除
- 書式 ： DELETE テーブル名 WHERE 検索条件
※書式については "\h コマンド名" でも確認することができます。
基本的なデータ操作はこれら4つのコマンドでほぼ実現することができるでしょう。
また、PostgreSQLでは、デフォルトでさまざまな種類の演算子や関数が用意されています。検索条件に数値の大小や文字列比較、日付間隔などを指定して処理対象のデータを絞り込むことができますし、検索結果をグループに分類して、それぞれのグループの合計や平均値を求めたりするような集約処理も可能です。
DMLと演算子・関数を組み合わせることにより、ユーザの要望に応じた結果を柔軟に取得することができるようになっています。
詳細については、最後にオンラインマニュアルのリンクをつけましたので、そちらを参照していただくとして、次の節からはデータ操作の実践へと移ってまいります。
3．データの準備
まずは、前回正規化したテーブルにデータを登録していきましょう。前回までと同様、仮想環境にossdbユーザでログインし、端末を立ち上げてください。
4つのテーブルorders、products、supplier、telbookをまだ作っていない場合は、前回の記事を参照し、CREATE TABLEを実行して作成してください。
データの挿入にはINSERT文を使用します。
例えば、商品テーブルに、価格150円のりんごを登録する場合には、次のようなSQLを実行します。
INSERT INTO products (name, price, etc) VALUES ('りんご', 150, '青森産');
※ここではまだSQLは実行しないでください。後でまとめてデータ登録を行います。
ただし、インポートするデータが大量にある場合は、COPYコマンドの使用をお勧めします。COPYコマンドは、標準入力や、CSVのような外部ファイルからのデータ入力を一括して処理することができるうえに、処理自体も大量データを想定して最適化されているので、INSERTを繰り返し実行するよりも効率的です。
今回は、データインポート用のSQLファイル(ossdb_sample.sql)を用意しました。まず、このファイルを、Webブラウザの機能を使ってダウンロードし、適当なディレクトリに保存してください。次に、psqlコマンドの-fオプションを使って読み込み実行すると、COPY文を使ったインポート処理が行われます。
psql -d ossdb -f /home/ossdb/ossdb_sample.sql
※データのダウンロード先が /home/ossdb/ossdb_sample.sql であると仮定しています。
※COPYコマンドは標準SQLではないため、他のRDBMSでは使用することができません。CSVなどから一括インポートする方法は、RDBMSごとに独自の方法があり、COPYコマンドは PostgreSQLのみで利用できる方法です。
COPYコマンドの詳しい解説については、オススメ！OSS-DB情報の『第7回 テキストファイル(CSVなど)の入出力』を参照してください。
4．SQLの実行
データの準備が整ったところで、具体的なSQLの使い方を見ていきましょう。
例えば、次のようなケースを考えてみます。
-----------------------------------
松山花子さんが紀州果樹園を買収しました。
みかん園と紀州果樹園が同じグループとなったことで、みかんの輸送コスト改善が図られ仕入れ原価が3円落ちることになりました。
これによって、月当たりいくら程度のコストが浮くことになるのかを見積もり、入荷計画の見直しを行いたいと思います。
-----------------------------------
この課題をどのように解決するか、実際にSQLを実行しながら一緒に考えていきましょう。
例えば、次のようなケースを考えてみます。
-----------------------------------
松山花子さんが紀州果樹園を買収しました。
みかん園と紀州果樹園が同じグループとなったことで、みかんの輸送コスト改善が図られ仕入れ原価が3円落ちることになりました。
これによって、月当たりいくら程度のコストが浮くことになるのかを見積もり、入荷計画の見直しを行いたいと思います。
-----------------------------------
この課題をどのように解決するか、実際にSQLを実行しながら一緒に考えていきましょう。
（1）まず最初に、紀州果樹園の現代表者を確認しましょう。
任意のレコードを抽出するにはSELECT文を使用します。また、検索条件の指定には WHERE句を使用します。
supplierテーブルの中からfarm_nameが'紀州果樹園'のレコードを抽出するSQLは次のようになります。
SELECT * FROM supplier WHERE farm_name= '紀州果樹園';
任意のレコードを抽出するにはSELECT文を使用します。また、検索条件の指定には WHERE句を使用します。
supplierテーブルの中からfarm_nameが'紀州果樹園'のレコードを抽出するSQLは次のようになります。
SELECT * FROM supplier WHERE farm_name= '紀州果樹園';
紀州果樹園の現代表者は「紀伊國屋文夫」さんであることが分かります。
（2）代表者を松山花子さんに変更しましょう。
紀州果樹園の現代表者が「紀伊國屋文夫」であることが分かりましたので、それを「松山花子」さんに変更しましょう。
レコードの更新にはUPDATE文を使用します。更新対象を限定するためにもWHERE句の指定は忘れないようにしてください。WHERE句を付け忘れると全レコードが更新されることになるので注意しましょう。
UPDATE supplier SET rep_name = '松山花子' WHERE farm_name = '紀州果樹園';
UPDATEを実行したら、ちゃんと変更されたのかSELECT文を使用して確認してみましょう。紀州果樹園の代表者が「松山花子」さんに変更されたことが分かります。
紀州果樹園の現代表者が「紀伊國屋文夫」であることが分かりましたので、それを「松山花子」さんに変更しましょう。
レコードの更新にはUPDATE文を使用します。更新対象を限定するためにもWHERE句の指定は忘れないようにしてください。WHERE句を付け忘れると全レコードが更新されることになるので注意しましょう。
UPDATE supplier SET rep_name = '松山花子' WHERE farm_name = '紀州果樹園';
UPDATEを実行したら、ちゃんと変更されたのかSELECT文を使用して確認してみましょう。紀州果樹園の代表者が「松山花子」さんに変更されたことが分かります。
（3）仕入原価の低下に伴う入荷コスト削減分を算出しましょう。
紀州果樹園から8月にどれだけの数のみかんを入荷していたのでしょうか。入荷数の合計が分かれば、削減コストを見積もることができます。
データの範囲を指定するにはBETWEEN演算子を、また、特定カラムの合計値を求めるにはSUM関数を使用します。
SELECT SUM(order_num) * 3 saved_cost FROM orders
WHERE arrival_date BETWEEN '2012-08-01' AND '2012-08-31' AND
farm_name = '紀州果樹園';
※"saved_cost"は列の別名です。"SELECT 列名 別名…"のように書くことで、列に別名を割り当てることができます。
紀州果樹園から8月にどれだけの数のみかんを入荷していたのでしょうか。入荷数の合計が分かれば、削減コストを見積もることができます。
データの範囲を指定するにはBETWEEN演算子を、また、特定カラムの合計値を求めるにはSUM関数を使用します。
SELECT SUM(order_num) * 3 saved_cost FROM orders
WHERE arrival_date BETWEEN '2012-08-01' AND '2012-08-31' AND
farm_name = '紀州果樹園';
※"saved_cost"は列の別名です。"SELECT 列名 別名…"のように書くことで、列に別名を割り当てることができます。
この結果から、8月と同程度の入荷数であれば1494円のコストが浮くことが予想されます。
（4）直近のオーダーをキャンセルすることにします。
9月は梨園からもみかんを入荷する予定でいましたが、それを紀州果樹園に振 り替えることにしました。梨園と交渉したところ、直近の9月2日に届いたみかんは返品を受け付けてもらえることになりました。返品分はordersテーブル から削除しましょう。削除にはDELETE文を使用します。
DELETE FROM orders WHERE name='みかん' AND farm_name='梨園' AND arrival_date='2012-09-02';
9月は梨園からもみかんを入荷する予定でいましたが、それを紀州果樹園に振 り替えることにしました。梨園と交渉したところ、直近の9月2日に届いたみかんは返品を受け付けてもらえることになりました。返品分はordersテーブル から削除しましょう。削除にはDELETE文を使用します。
DELETE FROM orders WHERE name='みかん' AND farm_name='梨園' AND arrival_date='2012-09-02';
なお、レコードを削除する場合、一度削除したレコードは再現することができないため、レコードの削除には注意が必要です。レコードの削除を実施する場合には、削除したいレコードを一度確認してから削除することをお勧めします。
以上、具体例を通して、INSERT/SELECT/UPDATE/DELETEおよび集約関数の使い方を見てきましたが、SQLに馴染むことができたでしょうか。練習問題も用意してみたので、余力のある方はこちらにもチャレンジしてみてください。
以上、具体例を通して、INSERT/SELECT/UPDATE/DELETEおよび集約関数の使い方を見てきましたが、SQLに馴染むことができたでしょうか。練習問題も用意してみたので、余力のある方はこちらにもチャレンジしてみてください。
練習問題
問題1： 和歌山県の仕入先は紀州果樹園以外にもあるのでしょうか。文字列の部分一致を判定できるLIKE演算子を使って、和歌山県にある仕入先をすべて表示してみましょう。
解答例：
SELECT * FROM supplier WHERE address LIKE '和歌山県%';
問題2： 8月のみかんの入荷数は商品全体の中で何番目でしょう。特定のカラム値でグルーピングするにはGROUP BY句を、レコードの並べ替えには ORDER BY 句を利用してください。
解答例：
SELECT name, SUM(order_num) FROM orders
GROUP BY name
ORDER BY SUM DESC;
問題3： 4章の（3）では8月のみの入荷数を求めましたが、月による変動が大きい可能性も考えられます。月別の入荷数を求めてみましょう。日付データから月の値だけを取り出すにはEXTRACT関数を利用してください。
解答例：
SELECT farm_name, EXTRACT(MONTH FROM arrival_date) arrival_month,
SUM(order_num) FROM orders
WHERE farm_name='津軽ファーム'
GROUP BY farm_name, arrival_month;
解答例：
SELECT * FROM supplier WHERE address LIKE '和歌山県%';
問題2： 8月のみかんの入荷数は商品全体の中で何番目でしょう。特定のカラム値でグルーピングするにはGROUP BY句を、レコードの並べ替えには ORDER BY 句を利用してください。
解答例：
SELECT name, SUM(order_num) FROM orders
GROUP BY name
ORDER BY SUM DESC;
問題3： 4章の（3）では8月のみの入荷数を求めましたが、月による変動が大きい可能性も考えられます。月別の入荷数を求めてみましょう。日付データから月の値だけを取り出すにはEXTRACT関数を利用してください。
解答例：
SELECT farm_name, EXTRACT(MONTH FROM arrival_date) arrival_month,
SUM(order_num) FROM orders
WHERE farm_name='津軽ファーム'
GROUP BY farm_name, arrival_month;
まとめ
SQLの基礎をひととおり見てきましたが、いかがでしたか。
SQLを使用することで、数多くのデータの中から目的とするものを効率的に見つけられることが実感できたでしょうか。
中には、SELECTしてその結果をもとにUPDATE文を書いて、と意外と手間がかかるなと感じた方もいるでしょう。
しかしSQLの威力はまだまだこんなものではありません。
次回のSQL基礎IIでは、更に踏み込んでSQLを扱った例を紹介していく予定です。
お楽しみに。
SQLを使用することで、数多くのデータの中から目的とするものを効率的に見つけられることが実感できたでしょうか。
中には、SELECTしてその結果をもとにUPDATE文を書いて、と意外と手間がかかるなと感じた方もいるでしょう。
しかしSQLの威力はまだまだこんなものではありません。
次回のSQL基礎IIでは、更に踏み込んでSQLを扱った例を紹介していく予定です。
お楽しみに。
●SQL基礎Iで紹介した各SQL、及び集約関数については、以下のPostgreSQL 日本語ドキュメントのリファレンスも合わせてご覧ください。
1．INSERT文
http://www.postgresql.jp/document/9.1/html/sql-insert.html
2．COPY文
http://www.postgresql.jp/document/9.1/html/sql-copy.html
3．SELECT文
http://www.postgresql.jp/document/9.1/html/sql-select.html
4．UPDATE文
http://www.postgresql.jp/document/9.1/html/sql-update.html
5．DELETE文
http://www.postgresql.jp/document/9.1/html/sql-delete.html
6. 関数と演算子
http://www.postgresql.jp/document/9.1/html/functions.html
執筆：
神谷 貴広 氏（OSS-DB Silver認定者）株式会社オークニー ジオソリューション事業部
PostGISを含めたFOSS4G(Free Open Source Software for Geospatial)ソフトウェアを活用して、地図、位置情報を組み合わせたアプリケーションの設計・開発に従事。一方で、クラウド、スマートフォン、ソーシャルメディアと言った新しい価値を世の中が求める中、位置情報、地図を絡めた新しいサービスを模索中。
アドバイザー：
森 亮 氏 株式会社オークニー代表取締役
2002年にオークニーを設立し、代表取締役に就任。マッピング・ GIS・LBS・ITS関連プロジェクトのビジネスコンサルティングを経て、現在は会社経営の傍ら、『入門Webマッピング』（Tyler Mitchell原著；オライリー・ジャパン発行）の翻訳、Open Source Geospatial財団日本支部の代表者を務めるなど、FOSS4G（オープンソース地理空間ソフトウェア）の普及活動に取り組んでいる。
監修：
松田 神一
LPI-Japan
Platinum Sponsors
© EDUCO All Rights Reserved.