SQLでCRUD処理を行う

SQL DB PostgreSQL

SinatraのメモアプリをDB化するにあたってSQLでCRUD処理を行う必要が出てきました。 SQLコマンドでCRUD処理を行う方法について改めて整理しました。

結論

下記のSQLコマンドででCRUD処理ができる。

  • Create: INSERT
  • Read: SELECT
  • Update: UPDATE
  • Delete: DELETE

CRUDとは - 意味をわかりやすく - IT用語辞典 e-Words

SQL入門 | PostgreSQLではじめるDB入門

DB、テーブルの作成

今回、DBはPostgreSQLを選択しました。 macにHomebrewを使ってPostgreSQLのインストールを行いました。 PostgreSQLの基本操作はこちらの記事にまとめています。

下記構成でDB、テーブルを作成します。

  • DB名: memo_app
  • テーブル名: memos
カラム名 データ型 制約
id CHAR(36) PRIMARY KEY
title VARCHAR(100) NOT NULL
content VARCHAR(255) なし 
# デフォルトユーザkarlley でデフォルトDBのkarlleyにログイン
$ psql (14.4)
Type "help" for help.

# memo_app DB作成 
karlley=# CREATE DATABASE memo_app;
CREATE DATABASE

# memo_app DBの作成を確認
karlley=# \l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+---------------------
 karlley   | karlley | UTF8     | C       | C     |
 memo_app  | karlley | UTF8     | C       | C     |
 postgres  | karlley | UTF8     | C       | C     |
 template0 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
 template1 | karlley | UTF8     | C       | C     | =c/karlley         +
           |         |          |         |       | karlley=CTc/karlley
(5 rows)

# memo_app DBに切替
karlley=# \c memo_app
You are now connected to database "memo_app" as user "karlley".

# memos テーブル作成
memo_app=# CREATE TABLE memos(id CHAR(36) NOT NULL, title VARCHAR(100) NOT NULL, content VARCHAR(255), PRIMARY KEY (id));
CREATE TABLE

# memos テーブルの作成を確認
memo_app=# \d memos
                       Table "public.memos"
 Column  |          Type          | Collation | Nullable | Default
---------+------------------------+-----------+----------+---------
 id      | character(36)          |           | not null |
 title   | character varying(100) |           | not null |
 content | character varying(255) |           |          |
Indexes:
    "memos_pkey" PRIMARY KEY, btree (id)

スーパーユーザkarlley で作成したmemo_app DBに接続した状態で行います。

Create

CreateはINSERT コマンドで行います。

INSERT INTO テーブル名 (カラム1, カラム2, ...) VALUES (値1, 値2, ...);

下記のレコードを追加します。

レコード1

  • id: bc9db029-408a-4f5b-bf49-5e5af9581532
  • title: memo1
  • content: this is memo1

レコード2

  • id: c9e67c66-4219-411f-ab7f-625a7e5e4ceb
  • title: memo2
  • content: this is memo2
# レコード追加
memo_app=# INSERT INTO memos (id, title, content) VALUES ('bc9db029-408a-4f5b-bf49-5e5af9581532', 'memo1', 'this is memo1');
INSERT 0 1
memo_app=# INSERT INTO memos (id, title, content) VALUES ('c9e67c66-4219-411f-ab7f-625a7e5e4ceb', 'memo2', 'this is memo2');
INSERT 0 1

# レコード確認
memo_app=# SELECT * FROM memos;
                  id                  | title |    content
--------------------------------------+-------+---------------
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 | this is memo1
 c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2 | this is memo2
(2 row)

Read

ReadはSELECT コマンドで行います。

SELECT カラム名 FROM テーブル名;

Createで追加したレコードを検索します。

# titleのみ検索
memo_app=# SELECT title FROM memos;
 title
-------
 memo1
 memo2
(2 rows)

# 全カラムを検索
memo_app=# SELECT * FROM memos;
                  id                  | title |    content
--------------------------------------+-------+---------------
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 | this is memo1
 c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2 | this is memo2
(2 rows)

Update

  • UpdateはUPDATE コマンドで行います。
  • WHERE の有無で更新対象を特定レコード/全レコードを指定できます。
# 特定レコード
UPDATE テーブル名 SET カラム名 = 変更する値 WHERE レコードを特定する条件式;

# 全レコード
UPDATE テーブル名 SET カラム名 = 変更する値;

title カラムの値がmemo1 のレコードの値をmemo1 update に更新します。

# 特定レコードの更新
memo_app=# UPDATE memos SET title = 'memo1 update' Where title = 'memo1';
UPDATE 1

# レコードの更新を確認
memo_app=# SELECT * FROM memos;
                  id                  |    title     |    content
--------------------------------------+--------------+---------------
 c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2        | this is memo2
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 update | this is memo1
(2 rows)

全レコードのcontent カラムの値をupdate content に更新します。

# 全レコード更新
memo_app=# UPDATE memos SET content = 'update content';
UPDATE 2

# レコードの更新を確認
memo_app=# SELECT * FROM memos;
                  id                  |    title     |    content
--------------------------------------+--------------+----------------
 c9e67c66-4219-411f-ab7f-625a7e5e4ceb | memo2        | update content
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 update | update content
(2 rows)

Delete

  • DeleteはDELETE コマンドで行います。
  • WHERE の有無で削除対象を特定レコード/全レコードを指定できます。
  • 削除対象はレコード単位のため特定のカラムの値のみ削除することはできません。
# 特定のレコードの削除
DELETE FROM テーブル名 WHERE レコードを特定する条件式;

# 全レコード
DELETE FROM テーブル名;

title カラムの値がmemo2 のレコードのみ削除します。

# 特定レコードを削除
memo_app=# DELETE FROM memos WHERE title = 'memo2';
DELETE 1

# レコードの削除を確認
memo_app=# SELECT * FROM memos;
                  id                  |    title     |    content
--------------------------------------+--------------+----------------
 bc9db029-408a-4f5b-bf49-5e5af9581532 | memo1 update | update content
(1 row)

全カラムのレコードを削除します。

# 全レコードを削除
memo_app=# DELETE FROM memos;
DELETE 1

# 全レコードの削除を確認
memo_app=# SELECT * FROM memos;
 id | title | content
----+-------+---------
(0 rows)

感想

SQLでのCRUD処理に関する情報が整理されて良かったです。 実際にSinatraのメモアプリに組み込んでプラクティスを進めたいと思います。

参照

CRUDとは - 意味をわかりやすく - IT用語辞典 e-Words

SQL入門 | PostgreSQLではじめるDB入門