# データベース基礎演習(2/3)
## ファイルからの実行
先ほどまではSQLをすべて手動で入力してきましたが、
あらかじめファイルに保存したSQLを実行できたほうが便利です。
### echoによるファイル作成
`.exit`でunixのターミナルに戻って、ファイルを作成します。
```
$ echo "select * from book where price < 1200;" > sale.sql
$ cat sale.sql
select * from book where price < 1200;
$ ll sale.sql
```
`echo`コマンドは入力した内容をそのまま出力します。`> [ファイル名]`と
ある場合、unixはファイルを作成して出力内容を書き込みます。
作成したSQLはunixターミナル（外側）から次のように実行できます。
```
$ sqlite3 -header my.db < sale.sql
id|title|price
2|manual of neko|1000
3|manual of neko|500
```
### テーブル形式での表示
研修用のサーバ上には、表組に変換する`tbl`コマンドがインストールされているので、
以下のように`| tbl`を末尾に追加すると見やすく出力することもできます。
```
$ sqlite3 -header my.db < sale.sql | tbl
+----+----------------+-------+
| id | title | price |
+----+----------------+-------+
| 2 | manual of neko | 1000 |
+----+----------------+-------+
| 3 | manual of neko | 500 |
+----+----------------+-------+
```
### sedによるファイル修正
作成したSQLは価格1200円未満のレコードを出力するものでした。
この金額を1000円未満に変更したいとします。色々な方法がありますが、
`sed`コマンドを利用することもできます。
```
$ sed -i -e 's/1200/1000/' sale.sql
$ cat sale.sql
select * from book where price < 1000;
```
### viによるファイル編集
テスト用にデータを追加するため、`insert`文を何件か並べたファイルを作成しましょう。
`vim`コマンドを利用します。`vim`はターミナルから利用できるテキストエディタで、若干操作に癖がありますが、便利で強力な機能がそろっています。
```
$ vim newbooks.sql
```
全画面表示になり、左下に"newbooks"[New File]と表示されます。
### 入力モード
キーボードの`i`を押して、挿入モードに変更します。
左下が-- INSERT ---に変わりました。
まずは1行入力してみましょう。
```
insert into book (title, price) values ("a yellow bear", 1200);
```
入力したら`esc`キーを押します。挿入モードから編集モードに戻りました。
### 行コピー
`yy9p`と入力してみましょう。`yy`は行コピー、`9p`は9回ペーストを意味します。
insert文が10行に増えました。
```
insert into book (title, price) values ("a yellow bear", 1200);
insert into book (title, price) values ("a yellow bear", 1200);
..省略
insert into book (title, price) values ("a yellow bear", 1200);
insert into book (title, price) values ("a yellow bear", 1200);
```
### 移動と検索
2行目の"yellow bear"を"red bear"に、金額を3300円に変更しましょう。
`gg`を押すと先頭行に移動します。`j`と押すと1行下へ移動。
`/yellow`と押すと修正箇所へ移動します。
```
insert into book (title, price) values ("a [カーソル]yellow bear", 1200);
insert into book (title, price) values ("a yellow bear", 1200);
..省略
insert into book (title, price) values ("a yellow bear", 1200);
insert into book (title, price) values ("a yellow bear", 1200);
```
### 単語変更
`cw`を押すと"yellow"を 削除して挿入モードになるので、`red`と入力して`esc`を押します。
```
insert into book (title, price) values ("a red bear", 1200);
insert into book (title, price) values ("a yellow bear", 1200);
..省略
insert into book (title, price) values ("a yellow bear", 1200);
insert into book (title, price) values ("a yellow bear", 1200);
```
### 連続検索
次の行の"bear"を"rat"に変えましょう。`/bear`と入力して`enter`を押すと、
同じ行の"bear"にカーソルが移動します。ここで`n`と入力すると、次の検索場所に
移動するので、二行目を修正できます。
1200->3300の変更も同様に実施できます。
### 保存
保存するときには、`:wq`と入力して`enter`を押します。
### マニュアルなど
vimのコマンドは独特ですが、色々なサマリーが公開されているので、
自分にあった使いやすいものを見つけて利用すると良いでしょう。
[これからvimコマンドを使おうとしている方々へ](https://qiita.com/HiromuMasuda0228/items/5ef842ee50e7ac5e85f2)
LevelbossMike/vim_shortcut_wallpaper
## 複数テーブルの作成
書籍を表すbookテーブルに加えて、書店を表すshopテーブルを作成します。
それぞれの本は、いずれかの書店に必ず所属するものとします。
以下のような更新用のSQLファイル、add_shop.sqlを作成して
テーブルの作成およびデータの追加を一括して行ってみましょう。
vimを利用する場合、キーワードへの色付けや、インデントの自動付与など
入力補助機能が利用できます。
```sql
create table shop (
id integer not null primary key autoincrement,
name text not null
);
insert into shop(name) values("meguro");
insert into shop(name) values("gotanda");
alter table book add shop_id integer default 1;
```
```
$ sqlite3 my.db < add_shop.sql
$ sqlite3 my.db
sqlite> .schema
CREATE TABLE book (
...省略
```
`alter`コマンドで変更したテーブルの構造は、`.schema`コマンドで
確認することができます。
## 複数テーブルの結合表示
`select`文では、複数のテーブルを結合して表示することができます。
```
> select * from shop,book;
...省略
> select * from shop,book where shop.id = book.shop_id;
...省略
```
`where`句が無いと、関係のないお店と本が並べて表示されてしまいます。
本が4冊、お店が3店舗あるとき、関連を無視して4x3レコードが表示されます。
`where shop.id = book.shop_id`と制約することにより、関連した
本とお店を並べて取得することができます。
## 集計表示
各お店の本の在庫は何冊でしょうか。次のような集計関数で求めることができます。
```
> select shop.id, shop.name, count(*) from shop,book where shop.id = book.shop_id;
```
`count`は件数を集計しますが、合計を集計する`sum`なども使用することができます。
## NULLを許容するテーブル結合
古書店では特別会員向けに、予約販売を開始することになりました。
会員を表すuserテーブルを作成し、bookテーブルに予約販売日カラムと、販売先会員番号を追加します。
ただし、予約販売の対象でない本については、それぞれ値をNULLとします。
次のような更新用SQLを作成して実行してみましょう。
```sql
create table user (
id integer not null primary key autoincrement,
name text not null
);
insert into user(name) values("taro");
insert into user(name) values("hanako");
alter table book add sell_date date;
alter table book add user_id integer;
update book set sell_date = '2021-04-04', user_id = 1 where id = 1;
```
NULL値は`select`文の結果として非表示になってしまうので、
sqliteのコンソールで`.nullvalue NULL`と表示設定を行います。
```
sqlite> .nullvalue NULL
sqlite> select * from book;
1|book of inu|1000|1|2021-04-04|1
1|manual of neko|1000|1|NULL|NULL
1|manual of neko|1000|1|NULL|NULL
```
予約販売の予定がない本については、カラムの値がNULLになっています。
## NULLを条件とした表示
本のうち、予約販売日がNULLであるレコードを表示してみましょう。
```
> select * from book where sell_date = NULL;
Error: near "where"
```
エラーになってしまいました。実はNULLは比較の値としては利用できません。
NULLであるか、ないかの判定は、専用の構文が用意されています。試してみましょう。
```sql
select * from book where sell_date is null;
select * from book where sell_date is not null;
```
## 外部結合
全ての本の一覧を表示します。ただし、予約販売があれば予約者の名前を表示し、
無ければ空欄にします。次のSQLで実現できるでしょうか。
```sql
select book.id, book.title, user.name
from book,user
where book.user_id = user.id;
```
販売予定のない本が表示されません。
それでは、販売予定がない＝user_idがnullである先は
必ず表示させる以下のSQLではどうでしょうか。
```sql
select book.id, book.title, user.name
from book,user
where book.user_id = user.id or book.user_id is null;
```
ところが、この方法だと販売予定の無い書籍が、全会員と結合して表示されます。
（where句なしのテーブル結合を思い出しましょう）
次のように複数の問い合わせ結果を取得する`union`句を使えば実現できます。
```sql
select book.id, book.title, user.name
from book,user
where book.user_id = user.id
union
select book.id, book.title, null
from book
where book.user_id is null;
```
しかし書き方が冗長ですし、実は性能的にも問題があります。("sql union 性能"などで調べてみましょう)
次のように、`left outer join`句を使用してテーブルの結合方法を指定すると、簡潔に期待通りの出力を得ることができます。
```sql
select book.id, book.title, user.name
from book
left outer join user on book.user_id = user.id;
```
# 練習問題
実例を通じて今まで学んだことを活用してみましょう。
問題ではテーブル名やカラム名は指定されませんが、適切な名前をつけることができる力は重要です。練習してみましょう。
## 問題１
次のような動物園を管理するテーブルを作成して下さい。{}は繰り返し項目を表します。
`展示室（展示室番号、展示室名、｛動物番号、動物名、動物種別｝）`
次のような問い合わせを実行できるSQLを作成して下さい。
・展示室と動物の一覧、ただし空の展示室であっても表示する
・１体の動物の展示室を変更する
## 問題２
次のような従業員の勤務状況を管理するテーブルを作成して下さい。
`従業員（従業員番号、従業員名、｛出勤日、出金状況｝）`
・従業員の、ある月における、欠勤日の日数を集計して下さい。
・ある日に欠勤している従業員の一覧を出力して下さい。
ヒント：`where id between 1 and 4`で1以上4以下を指定できます。
## 問題３
次の様な商品売上伝票を管理するテーブルを作成して下さい。
`売上（売上番号、売上日、顧客番号、顧客名、｛商品番号、商品名、商品数量、商品単価｝）`
・各売上の売上金額を表示して下さい。
ヒント：`select a * b, a + b from ~`など、カラム同士を計算することができます。
・特定の顧客の売上金額合計を表示して下さい。
ヒント：`select sum(a+b) from `など、計算結果を更に合計することができます。
・特定の日付の、特定の商品の売上数量、合計金額を表示して下さい。
## 参考情報
postgresqlの実行計画。
同一のSQLであっても、外部表の参照回数により、Index Scanではなく、
Hash Joinが使用されることがある。
極端な例では下記の通り、条件式の１件の違いにより、処理時間が1000倍程度相違することがある。
```
tamura=# select count(*) from book;
count
---------
1000000
(1 row)
tamura=# select count(*) from customer;
count
--------
100000
(1 row)
tamura=# explain analyze select * from book, customer where book.customer_id = customer.id and book.id < 577;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.72..2943.27 rows=598 width=40) (actual time=0.042..2.932 rows=577 loops=1)
-> Index Scan using book_pkey on book (cost=0.42..25.89 rows=598 width=22) (actual time=0.029..0.278 rows=577 loops=1)
Index Cond: (id < 577)
-> Index Scan using customer_pkey on customer (cost=0.29..4.88 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=577)
Index Cond: (id = book.customer_id)
Planning time: 0.432 ms
Execution time: 3.061 ms
(7 rows)
tamura=# explain analyze select * from book, customer where book.customer_id = customer.id and book.id < 578;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2887.43..2914.48 rows=599 width=40) (actual time=35.522..36.054 rows=578 loops=1)
Hash Cond: (book.customer_id = customer.id)
-> Index Scan using book_pkey on book (cost=0.42..25.91 rows=599 width=22) (actual time=0.032..0.161 rows=578 loops=1)
Index Cond: (id < 578)
-> Hash (cost=1637.00..1637.00 rows=100000 width=18) (actual time=34.138..34.139 rows=100000 loops=1)
Buckets: 131072 Batches: 1 Memory Usage: 5994kB
-> Seq Scan on customer (cost=0.00..1637.00 rows=100000 width=18) (actual time=0.018..11.248 rows=100000 loops=1)
Planning time: 0.463 ms
Execution time: 36.553 ms
(9 rows)
```
Sign in with Wallet
Connect another wallet