---
# System prepended metadata

title: データベース基礎演習(2/3)

---

# データベース基礎演習(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
![LevelbossMike/vim_shortcut_wallpaper](https://i.imgur.com/ufkKmi0.png)

## 複数テーブルの作成

書籍を表す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)

```
