SQL 入門演習

【概要】

SQLは，リレーショナルデータベースの標準的な言語であり，テーブル定義，問い合わせ（クエリ），データ操作などを行う．LIKE演算子を用いたパターンマッチングでは，「%」や「_」のワイルドカード文字と組み合わせることで，「typeの値に'AA'を含む行」や「先頭がBである行」などの柔軟な検索ができる．GROUP BY句を用いてデータをグループ化し，各グループの行数をCOUNT関数でカウントするなどの集計処理も重要である．例えば，product_nameの値ごとにグループ化して各グループの行数を集計できる．また，HAVING句を用いてグループ化後の集計結果に対して条件を適用でき，「行数が24を超えるグループのみを抽出」といった処理ができる．

【目次】

演習で行うこと
SQLite Manager Tool で既存のデータベースを開く
SQL を用いたテーブル定義と一貫性制約の記述
テーブルへの行の挿入
条件を満足する行のみの表示
LIKE と % の組み合わせ：文字列のパターンマッチ
ORDER BY を用いたソート
GROUP BY と COUNT の組み合わせ：グループごとの数え上げ

教材の利用条件: クリエイティブコモンズ表示-非営利-継承 4.0 国際ライセンス（CC BY-NC-SA 4.0）に基づき、著作者表示・非営利目的・同一ライセンスでの再配布を条件として自由に利用可能である。

演習で行うこと

LIKE と % の組み合わせ：文字列のパターンマッチ
ORDER BY を用いたソート
GROUP BY と COUNT の組み合わせ：グループごとの数え上げ
GROUP BY と COUNT と HAVING の組み合わせ：COUNT で数えたタプル数に条件を指定し，タプルを絞り込む

SQLite Manager Tool で既存のデータベースを開く

すでに作成済みのデータベースを，下記の手順で開く．

ツールバーの「DBオープン (Ctrl+O)」ボタンをクリックする．
データベースファイルを開く
* Ubuntu での実行例（「/home/ubuntuuser/mydb」を開く場合）
データベースファイル /home/ubuntuuser/mydb を選び，「開く」をクリックする．

* Windows での実行例（「C:\SQLite\mydb」を開く場合）
データベースファイル C:\SQLite\mydb を選び，「開く」をクリックする．

SQL を用いたテーブル定義と一貫性制約の記述

以前の授業で定義した products テーブルを使う． products テーブルのテーブル定義が残っている場合には，ここの操作は必要ない． products テーブルのテーブル定義が残っていない場合には，次の手順で定義する．

SQL を用いて，products テーブルを定義し，一貫性制約を記述する．

products テーブルの定義

次の SQL を入力し，「▶ SQL実行 (F5)」を押下する

create table products (
    id            integer  primary key autoincrement not null,
    product_name  text     unique not null,
    type          text     not null,
    price         real,
    created_at    datetime not null,
    updated_at    datetime );

* 「SQLエディタ」欄には，SQL プログラムを書くことができる．

出力欄の確認
「出力」欄にエラーメッセージが表示されていないことを確認する．

テーブルへの行の挿入

以前の授業で定義した products テーブルを使う．

下記の操作により，演習用のデータ（1000行）を，products テーブルに格納する．

演習用データ（1000行分のCSVファイル）を用意する．
ツールバーの「CSV読込」ボタンをクリックするか，「SQLエディタ」に次のドットコマンドを記述し，「▶ SQL実行 (F5)」を押下する．
```
.import "products.csv" "products"
```
「出力」欄に，1000件のレコードが読み込まれたことを示す完了メッセージが表示されることを確認する．

条件を満足する行のみの表示

id の値が 123 であるという条件での検索． 次の SQL を入力し，「▶ SQL実行 (F5)」を押下する．結果表示欄に，id が 123 の行のみが表示されることを確認する．

SELECT * FROM products WHERE id = 123;

LIKE と % の組み合わせ：文字列のパターンマッチ

「aa」あるいは「aA」あるいは「Aa」あるいは「AA」を含むという条件での検索．検索条件では「'%AA%'」と書いている．大文字と小文字の両方が検索条件にマッチする（LIKEはASCII文字については大文字小文字を区別しない）．

SELECT * FROM products WHERE type LIKE '%AA%';

先頭が「B」あるいは「b」であるという条件での検索．検索条件では「'B%'」と書いている．大文字と小文字の両方が検索条件にマッチする．

SELECT * FROM products WHERE type LIKE 'B%';

末尾が「C」あるいは「c」であるという条件での検索．検索条件では「'%C'」と書いている．大文字と小文字の両方が検索条件にマッチする．

SELECT * FROM products WHERE type LIKE '%C';

ORDER BY を用いたソート

末尾が「C」あるいは「c」であるという条件での検索．検索条件では「'%C'」と書いている．検索結果を「ORDER BY product_name」でソートする．

SELECT * FROM products WHERE type LIKE '%C' ORDER BY product_name;

今度は，「ORDER BY product_name DESC」でソートする．末尾に「DESC」を付けることにより，結果が降順になる．

SELECT * FROM products WHERE type LIKE '%C' ORDER BY product_name DESC;

GROUP BY と COUNT の組み合わせ：グループごとの数え上げ

「substr(＜属性名＞, X, Y)」は，X文字目から，Y文字分取り出すという意味である．例えば，「substr(product_name, 1, 1)」は，先頭文字を（文字を1個だけ）取り出すという意味である．

GROUP BY はグループ化である．

SELECT substr(product_name, 1, 1), COUNT(*) FROM products GROUP BY substr(product_name, 1, 1);

上の図では，「先頭がAのものが16行，先頭がBのものが15行，先頭がCのものが1行」という意味になる．

product_name の先頭文字は a から z, A から Z の 52 通りある．

次は，行数で絞り込む．次のSQLでは，行数が24を超えるものに絞り込んでいる．

SELECT substr(product_name, 1, 1), COUNT(*)
FROM products
GROUP BY substr(product_name, 1, 1)
HAVING COUNT(*) > 24;