SQLを使いこなしたい！虎の巻 #2 ~SQL操作 入門編~

寒すぎて最近あたたかいココアを解禁しました、新卒エンジニアのたいがーです?

前回、Docker環境下でPostgreSQLを使えるように環境を構築しました。今回はその続きですので、前回のブログをご覧になっていない方は是非一緒に環境を構築してみてください！

今回やってみること

こちら3点をやっていきます。

• 新しいデータベースを作成する
• 新しいテーブルを作成する
• 行を挿入 / 更新 / 削除 / 整列 / 範囲を指定する

今回は、私の"家探しの条件"をDBで作っていきたいと思います。

新しいデータベースを作成する

それではデータベースを作成してみます。

データベースユーザー、データベースを作成する

- 1週目に作成したコンテナにアクセス -
$ docker exec -it posgres bash
- データベースユーザー「user1」を作成する -
# createuser -U postgres user1
- データベースを作成する -
# createdb -U postgres -O user1 -E UTF8 --locale=C -T template0 howtosearchhouse
- 作成されたことを確認 -
#  psql -U postgres -l
- howtosearchhouseというデータベースが作成されているか確認する -
- 作成したデータベースユーザーで、作成したデータベースにアクセスする -
# psql -U user1 howtosearchhouse

これでデータベースが作成できました。続いて、テーブルを作成していきます。

新しいテーブルを作成する

今回はSQLファイルをローカルに作成し、コンテナにコピーします。まずはローカルにファイルを作成します。

create-table.sql

CREATE TABLE conditions(
  優先順位 INTEGER
, 番号 INTEGER PRIMARY KEY
, 優先事項 VARCHAR(50) NOT NULL unique
, ジャンル VARCHAR(30)
);

CREATE TABLE genre(
  番号 INTEGER PRIMARY KEY
, ジャンル VARCHAR(30)
);

次にコンテナ名/IDを調べ、そのコンテナにコピーします。

テーブルを作成する

- コンテナ名かIDを調べる -
$ docker ps
- そのコンテナにローカルからコピーする - 
$ docker cp ~/create-table.sql [コンテナ名/IDのどちらか]:create-table.sql
- コンテナに入る -
$ docker exec -it posgres bash
- データベースにアクセスする -
# psql -U user1 howtosearchhouse
- SQLファイルを実行する -
howtosearchhouse=> \i create-table.sql
- DBが作成されているか確認する -
howtosearchhouse=> \dt
- 定義したテーブルが作成されているか確認する -

これでテーブルを作成できました。続いてテーブルを変更していきます。

行を挿入 / 更新 / 削除 / 整列 / 範囲を指定する

新しい行を挿入する

まずは、insert文を使って、新しい行を挿入していきます。

１行追加する

howtosearchhouse=> insert into conditions(番号, 優先事項, ジャンル) values(101, 'バストイレ別', '水回り');

一度、テーブルの全ての行を表示させてみます。

conditionsの全ての行を表示

howtosearchhouse=> select * from conditions;

表示結果は、こちらです。

表示結果

 優先順位 | 番号  |      優先事項    |   ジャンル   
--------+------+-----------------+--------------
        |  101 | バストイレ別       | 水回り

次に、複数行を追加してみます。

複数行を追加する

howtosearchhouse=> insert into conditions(番号, 優先事項, ジャンル)
howtosearchhouse-> values(102, '病院に近い', '近所')
howtosearchhouse-> ,(103, '1階以外', 'セキュリティ')
howtosearchhouse-> ,(104, '飲食店が隣じゃない', '虫')
howtosearchhouse-> ,(105, 'コンビニが近い', '近所');

今度は表示させたい行を指定し、表示させてみます。

howtosearchhouse=> select ジャンル, 優先事項 from conditions;

実行結果

     ジャンル   |      優先事項      
--------------+--------------------
 水回り         | バストイレ別
 近所          | 病院に近い
 セキュリティ     | 1階以外
 虫            | 飲食店が隣じゃない
 近所          | コンビニが近い
(5 rows)

さらに、続いてジャンルだけを絞って表示します。

行を絞って検索する

howtosearchhouse=> select ジャンル, 優先事項 from conditions where ジャンル = '近所';

表示結果は、こちらです。

 ジャンル   |    優先事項    
----------+----------------
 近所     | 病院に近い
 近所     | コンビニが近い
(2 rows)

続いて、テーブルを更新していきます。

行を更新する

続いてupdate句を使い、先ほどのDBの中で優先順位をつけていきます。。

優先順位を更新し、表示する

howtosearchhouse=> update conditions set 優先順位=1 where 番号 = 101;
UPDATE 1
howtosearchhouse=> update conditions set 優先順位=2 where 番号= 103;
UPDATE 1
howtosearchhouse=> update conditions set 優先順位=3 where 番号=105;
UPDATE 1
howtosearchhouse=> update conditions set 優先順位=4 where 番号=102;
UPDATE 1
howtosearchhouse=> update conditions set 優先順位=5 where 番号=104;
UPDATE 1
howtosearchhouse=> select * from conditions;

すると、このような結果になります。

実行結果

 優先順位 | 番号 |      優先事項      |   ジャンル   
----------+------+--------------------+--------------
        1 |  101 | バストイレ別       | 水回り
        2 |  103 | 1階以外         | セキュリティ
        3 |  105 | コンビニが近い     | 近所
        4 |  102 | 病院に近い        | 近所
        5 |  104 | 飲食店が隣じゃない | 虫
(5 rows)

次に、削除を試してみます。

行を削除する

行を指定し、削除していきます。

１行削除する

howtosearchhouse=> delete from conditions where 番号 = 104;

実行結果はこちらです。

実行結果

 優先順位 | 番号 |      優先事項      |   ジャンル   
----------+------+--------------------+--------------
        1 |  101 | バストイレ別       | 水回り
        2 |  103 | 1階以外         | セキュリティ
        3 |  105 | コンビニが近い     | 近所
        4 |  102 | 病院に近い        | 近所
(4 rows)

行を整列する

続いて、番号順に並び替えてみます。

番号順に並び替える

howtosearchhouse=> select * from conditions order by 番号;

実行結果はこちらです。

実行結果

 優先順位 | 番号 |    優先事項    |   ジャンル   
----------+------+----------------+--------------
        1 |  101 | バストイレ別   | 水回り
        4 |  102 | 病院に近い     | 近所
        2 |  103 | 1階以外        | セキュリティ
        3 |  105 | コンビニが近い | 近所
(4 rows)

範囲を指定する

最後に範囲を指定していきます。まずは先頭のn行だけ出力するのにlimit句を使います。

先頭2行だけを表示する

howtosearchhouse=> select ジャンル, 優先事項 from conditions order by 優先順位 limit 2;

実行結果はこちらです。

実行結果

   ジャンル   |   優先事項   
--------------+--------------
 水回り       | バストイレ別
 セキュリティ | 1階以外
(2 rows)

次に、offset句を使い、先頭２行をスキップし表示してみます。

先頭2行をスキップする

howtosearchhouse=> select ジャンル, 優先事項 from conditions order by 優先順位 offset 2;

実行結果はこちらです。

実行結果

ジャンル |    優先事項    
----------+----------------
 近所     | コンビニが近い
 近所     | 病院に近い
 (2 rows)

最後にlimit句とoffset句を組み合わせ、真ん中2行を表示させてみます。

番号順に並び替え、4行のうち間2行を表示させる

select ジャンル, 優先事項 from conditions order by 番号 limit 2 offset 1;

実行結果はこちらです。

実行結果

 優先順位 | 番号 |  優先事項  |   ジャンル   
----------+------+------------+--------------
        4 |  102 | 病院に近い | 近所
        2 |  103 | 1階以外    | セキュリティ
(2 rows)

今回出てきたSQLで使う句の書き順に注意する

最後に、SQL文でwhere, order by, limitやoffsetの順番から書き順を変更すると、Syntaxエラーになってしまうので注意が必要です。注意しましょう。

1. select
2. from
3. where
4. order by
5. limit / offset

(limitとoffsetの表記手順はどちらが先でも大丈夫です。)

まとめ

今回は簡単なSQL文を作成してみました。

次回からはもう少し複雑なSQL文を作成して行こうと思います。以上、たいがーでした?

参考資料

• わかりみSQL
• docker cp