Zenn
💡

SQL ~ゼロからはじめるデータベース操作~ を読んで【②】

2024/03/23に公開
SQL
DB
tech

前回の続き。
https://zenn.dev/db_engineer/articles/28a9825c6d03da

第1章　データベースとSQL

・データベースとは何か

データベース（DB）とは必要な情報を効率良く利用できるように加工したデータの集まり。
データベースマネジメントシステム（DBMS）とは、データベースを管理するシステム。
DBMSは以下の5タイプがあります。

タイプ 名前 管理形式
階層型データベース Hierarichal Database データを階層構造で表現。RDBの普及により利用ケースは少ない。
リレーショナルデータベース Relational Database（RDB） Excelシートのように列と行からなる２次元表の形式でデータを管理。SQLを用いてデータを操作する。
オブジェクト指向データベース Object Oriented Database（OODB） オブジェクト指向言語（JavaやC++）のデータとそれを操作する処理を、オブジェクト単位で管理する。
XMLデータベース XML Database（XMLDB） XMLはインターネット上でやり取りするデータの形式。このXML形式のデータを操作する。
キー・バリュー型データストア Key-Value Store（KVS） 検索に使用するキーと値の組み合わせの形でデータを保存する。

RDBのDBMSは「RDBMS」と呼ばれ、代表的なRDBMSは次の5つがあります。

タイプ 備考
Oracle Database Oracle社のRDBMS
SQL Server Microsoft社のRDBMS
DB2 IBM社のRDBMS
PostgresSQL オープンソースのRDBMS
MySQL オープンソースのRDBMS

本書では、PostgresSQLを使用して、RDBMSを操作する方法を学びます。

・データベースの構成

RDBMSの一般的なシステム構成は以下です。

RDBMSへSQL文を送信することで、リレーショナルデータベースのデータを取得できます。

データベースのテーブルは、Excelシートのように列（カラム）と行（レコード）からなる２次元表の形式になっており、1つのデータベースには複数のテーブルを保存することができます。

データの読み書きは行単位で実行され、行と列が交わるマスには1つのデータしか入れられないルールがあります。

・SQLの概要

データベースを操作するSQLには、ISO（国際標準化機構）で定められた標準規格があり、それに準拠したSQLを「標準SQL」と呼び、RDBMSのタイプによってSQLが異なるそうです。

SQL文は以下の3種類に分類され、本書ではDMLの解説が中心になります。

タイプ 役割 コマンド
DDL
（Data Definition Language）		 データベースやテーブルを操作する CREATE：作成する
DROP：削除する
ALTER：構成を変更する
RENAME：テーブル名を変更する
DML
（Data Manipulation Language）		 テーブルの行を操作する SELECT：行を検索する
INSERT：新規行を作成する
UPDATE：行を更新する
DELETE：行を削除する
DCL
（Data Control Language）		 データベースの変更や、ユーザの権限を操作する COMMIT：データベースに行った変更を確定する
ROLLBACK：データベースに行なった変更を取り消す
GRANT：ユーザへ操作権限を付与する
REVOKE：ユーザの操作権限を奪う

SQL文には以下の記述ルールがあります。

No ルール 備考
① SQL文の最後は「;」で締める セミコロン（;）により文の終わりを示す必要がある
② コマンドに大文字/小文字の区別はない テーブルのデータは大文字/小文字は区別されるが、
コマンド（CREATE、SELECT等）は区別されないためどちら表記でもOK
③ 定数は入力ロールが異なる 文字列や日付はシングルクォーテーション「' '」で囲む必要があり、
数値は囲む必要はない
④ 単語間は区切りが必要 単語間は半角スペースか改行で区切る必要がある

・テーブルの作成

まず、テーブルを格納するデータベースは前章で作成した「shop」を使用します。

> psql -d postgres -c "\l+ shop"
                                          List of databases
 Name | Owner  | Encoding | Collate | Ctype | Access privileges |  Size   | Tablespace | Description
------+--------+----------+---------+-------+-------------------+---------+------------+-------------
 shop | hogeon | UTF8     | C       | C     |                   | 8665 kB | pg_default |
(1 row)

テーブルは以下の条件に沿って作成します。

命名ルール

No ルール
① 使用可能な文字は以下3種類
・半角アルファベット
・半角数字
・アンダーバー（_）
② テーブルの最初の文字はアルファベット
③ 同じ名前のテーブルは作成できない

データ型の指定

No データ型 備考
① INTEGER型 整数を入れる列に指定する
少数は入れられない
② CHAR型 文字列を入れる列に指定する
CHAR(10)などで( )内の数値で文字列の長さを指定
指定数に満たない文字列の場合は、半角スペースが入る
文字列は大文字/小文字が区別される
③ VARCHAR型 CHAR型と同じ文字列を入れる列に指定する
CHAR型との違いは指定数に満たない文字列の場合は、半角スペースは入らない
※OracleではVARCHAR2型を使用する
④ DATE型 日付（年月日）を入れる列に指定する
※Oracleでは時分秒まで含む

制約の設定

No 制約 備考
① NOT NULL制約 NULL（空）はNOT（無）、必ずデータを入れるという制約
② 主キー制約 1つの行を特定できる列を設定
主キーを指定すれば特定の行を取り出せる
プライマリキーとも呼ばれる

上記の各条件に沿って以下の商品テーブルを作成します。

商品テーブルでの列名 Shohinテーブルで定義した列名
商品ID shohin_id
商品名 shohin_mei
商品分類 shohin_bunrui
販売単価 hanbai_tanka
仕入単価 shiire_tanka
登録日 torokubi 
### 1コマンドで実行
shop=# CREATE TABLE Shohin (shohin_id CHAR(4) NOT NULL, shohin_mei VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32) NOT NULL, hanbai_tanka INTEGER, shiire_tanka INTEGER, torokubi DATE, PRIMARY KEY (shohin_id));

### 複数回のコマンド実行でも可能
shop=# CREATE TABLE Shohin
shop-# (shohin_id    CHAR(4)    NOT NULL,
shop(# shohin_mei    VARCHAR(100)  NOT NULL,
shop(# shohin_bunrui VARCHAR(32)   NOT NULL,
shop(# hanbai_tanka  INTEGER  ,
shop(# shiire_tanka  INTEGER  ,
shop(# torokubi      DATE     ,
shop(# PRIMARY KEY (shohin_id));
CREATE TABLE
shop=#

テーブルが作成されたことを確認します。

shop=# \d Shohin
                          Table "public.shohin"
    Column     |          Type          | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
 shohin_id     | character(4)           |           | not null |
 shohin_mei    | character varying(100) |           | not null |
 shohin_bunrui | character varying(32)  |           | not null |
 hanbai_tanka  | integer                |           |          |
 shiire_tanka  | integer                |           |          |
 torokubi      | date                   |           |          |
Indexes:
    "shohin_pkey" PRIMARY KEY, btree (shohin_id)

shop=#

・テーブルの削除と変更

テーブルの削除

実行するとテーブルの復元はできないため注意が必要です。

DROP TABLE <テーブル名>;

Shohinテーブルを削除して再作成してみました。

### 削除
shop=# DROP TABLE Shohin;
DROP TABLE
shop=#

### 削除されたことを確認
shop=# \d Shohin
Did not find any relation named "Shohin".
shop=#

### 再作成
shop=# CREATE TABLE Shohin (shohin_id CHAR(4) NOT NULL, shohin_mei VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32) NOT NULL, hanbai_tanka INTEGER, shiire_tanka INTEGER, torokubi DATE, PRIMARY KEY (shohin_id));
CREATE TABLE
shop=#

### 再作成されたことを確認
shop=# \d Shohin
                          Table "public.shohin"
    Column     |          Type          | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
 shohin_id     | character(4)           |           | not null |
 shohin_mei    | character varying(100) |           | not null |
 shohin_bunrui | character varying(32)  |           | not null |
 hanbai_tanka  | integer                |           |          |
 shiire_tanka  | integer                |           |          |
 torokubi      | date                   |           |          |
Indexes:
    "shohin_pkey" PRIMARY KEY, btree (shohin_id)

shop=#

テーブルの変更

テーブルの変更も復元コマンドはないので、追加/削除それぞれで対応する必要があります。

### 列を追加する場合
ALTER TABLE <テーブル名> ADD COLUMN <列の定義>;

### 列を削除する場合
ALTER TABLE <テーブル名> DROP COLUMN <列の定義>;

商品テーブルへ以下「商品名（仮名）」の列を追加と削除を試してみます。

商品テーブルでの列名 Shohinテーブルで定義した列名
商品名（仮名） shohin_mei_kana 
### 列の追加
shop=# ALTER TABLE Shohin ADD COLUMN shohin_mei_kana VARCHAR(100) NOT NULL;
ALTER TABLE
shop=#

### 追加を確認
shop=# \d Shohin
                           Table "public.shohin"
     Column      |          Type          | Collation | Nullable | Default
-----------------+------------------------+-----------+----------+---------
 shohin_id       | character(4)           |           | not null |
 shohin_mei      | character varying(100) |           | not null |
 shohin_bunrui   | character varying(32)  |           | not null |
 hanbai_tanka    | integer                |           |          |
 shiire_tanka    | integer                |           |          |
 torokubi        | date                   |           |          |
 shohin_mei_kana | character varying(100) |           | not null |
Indexes:
    "shohin_pkey" PRIMARY KEY, btree (shohin_id)

shop=#

### 列の削除
shop=# ALTER TABLE Shohin DROP COLUMN shohin_mei_kana;
ALTER TABLE
shop=#

### 削除を確認
shop=# \d Shohin
                          Table "public.shohin"
    Column     |          Type          | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
 shohin_id     | character(4)           |           | not null |
 shohin_mei    | character varying(100) |           | not null |
 shohin_bunrui | character varying(32)  |           | not null |
 hanbai_tanka  | integer                |           |          |
 shiire_tanka  | integer                |           |          |
 torokubi      | date                   |           |          |
Indexes:
    "shohin_pkey" PRIMARY KEY, btree (shohin_id)

shop=#

テーブルへのデータ登録

次は作成したテーブルへデータを登録します。

BEGIN TRANSACTION;
INSERT INTO Shohin VALUES ('値1','値2','値3'...);
COMMIT;

shop=# BEGIN TRANSACTION;
BEGIN
shop=*# INSERT INTO Shohin VALUES ('0001','Tシャツ','衣服',1000,500,'2009-09-20');
INSERT 0 1
shop=*# INSERT INTO Shohin VALUES ('0002','穴あけパンチ','事務用品',500,320,'2009-09-11');
INSERT 0 1
shop=*# INSERT INTO Shohin VALUES ('0003','カッターシャツ','衣服',4000,2800,NULL);
INSERT 0 1
shop=*# INSERT INTO Shohin VALUES ('0004','包丁','キッチン用品',3000,2800,'2009-09-20');
INSERT 0 1
shop=*# INSERT INTO Shohin VALUES ('0005','圧力鍋','キッチン用品',6800,5000,'2009-01-15');
INSERT 0 1
shop=*# INSERT INTO Shohin VALUES ('0006','フォーク','キッチン用品',500,NULL,'2009-09-20');
INSERT 0 1
shop=*# INSERT INTO Shohin VALUES ('0007','おろしがね','キッチン用品',880,790,'2008-04-28');
INSERT 0 1
shop=*# INSERT INTO Shohin VALUES ('0008','ボールペン','事務用品',100,NULL,'2009-11-11');
INSERT 0 1
shop=*# COMMIT;
COMMIT
shop=#

### 登録されたデータを確認
shop=# SELECT * FROM Shohin;
 shohin_id |   shohin_mei   | shohin_bunrui | hanbai_tanka | shiire_tanka |  torokubi
-----------+----------------+---------------+--------------+--------------+------------
 0001      | Tシャツ        | 衣服          |         1000 |          500 | 2009-09-20
 0002      | 穴あけパンチ   | 事務用品      |          500 |          320 | 2009-09-11
 0003      | カッターシャツ | 衣服          |         4000 |         2800 |
 0004      | 包丁           | キッチン用品  |         3000 |         2800 | 2009-09-20
 0005      | 圧力鍋         | キッチン用品  |         6800 |         5000 | 2009-01-15
 0006      | フォーク       | キッチン用品  |          500 |              | 2009-09-20
 0007      | おろしがね     | キッチン用品  |          880 |          790 | 2008-04-28
 0008      | ボールペン     | 事務用品      |          100 |              | 2009-11-11
(8 rows)

shop=#

書評

章の前半がデータベースというシステムの仕組みと考え方、役割の解説的な部分で、
後半が、実際にSQLを使ってテーブルの作成/削除/変更からデータ登録まで実践するまでの内容でした。
RDBMSのタイプによって微妙に実行するコマンドは違うようなので、使い分けが必要な感じです。
章末に練習問題があるので、学習した内容のおさらいには丁度いいです。

Discussion