データベース基礎 #2

前回の記事では、DBMSやリレーショナルデータモデル、SQLについて簡単に説明し、Dockerを使ってPostgreSQLのテスト環境を構築しました。今回の記事では、その環境を使って、データベースとのやり取りやCRUD（Create, Read, Update, Delete）操作を行うための基本的なSQL文を学びます。

データベースとテーブルの作成

psql -U postgresでPostgreSQLにアクセスする際、デフォルトのスーパーユーザーであるpostgresユーザーとしてアクセスしています。 SELECT * FROM pg_user;でユーザーを確認すると、postgresユーザーがデフォルトですべてのアクションに対する権限を持っていることが確認できます（ユーザーとロールについては今後の記事で説明します）。事前に定義されたテンプレートデータベース（空の状態で初期化されている）をコピーすることで、CREATE DATABASE testdb;で新しいデータベースを作成できます。コマンドのキーワードは慣例的に明確さのために大文字で記述します。

データベースを作成した後、\lというpsqlコマンドを使って全データベースをリストアップし、テンプレートデータベースの下にtestdbが作成されていることを確認できます。デフォルトでは新しいデータベースを作成する際にtemplate1データベースをコピーしますが、TEMPLATE <db_name>でコピー元のデータベースを指定することもできます。デフォルトではpostgresデータベースが使用されるため、\c testdbというpsqlコマンドでデータベースを切り替えることができます。データベース内では、CREATE TABLE <テーブル名> (<列1名> <型>, <列2名> <型>, ...);文を使ってスキーマに基づいてテーブル（またはリレーション）を定義できます（userのような特定の名前は、PostgreSQLを含む一部のDBMSでは許可されていません）。

実践として、自動的にインクリメントされる主キー、varchar(50)型のname、int型のageを持つt1テーブルを CREATE TABLE t1 (id SERIAL NOT NULL PRIMARY KEY, name VARCHAR(50), age INT);で作成できます（自動インクリメントされる主キーは、PostgreSQLではSERIALデータ型とPRIMARY KEYを使って実装できます。代わりに最後にPRIMARY KEY (id)を配置することもでき、他のDBMSではid INT NOT NULL AUTO_INCREMENTのようにAUTO_INCREMENTキーワードと共にそれを使用します）。 \dtpsqlコマンド（またはサイズを表示する\dt+）と\d t1コマンドを使用して、データベース内のすべてのテーブルを確認し、t1の作成を確認し、テーブルの構造を観察できます。

制約とタプルの挿入

上記で使用したNOT NULLキーワードは、idがNULLまたは欠落することを制限する制約の一種です。他にもDEFAULT、UNIQUE、CHECKなどの便利な制約があります。DEFAULT制約は属性のデフォルト値を設定し、 createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMPのようなタイムスタンプの作成に特に便利です。 UNIQUE制約は重複がないことを保証し、email VARCHAR(50) UNIQUEのような重複メールのチェックに便利です。 CHECK制約は属性値の妥当性チェックに便利で、CHECK (age >= 18 AND char_length(password) >= 8)のように使えます。また、FOREIGN KEYという別の便利な制約もありますが、これは次の記事で説明します。

制約を定義するスキーマに基づいてテーブル（リレーション）を作成した後、 INSERT INTO <テーブル名> (<列1名>, <列2名>, ...) VALUES (<列1値>, <列2値>, ...), (<列1値>, <列2値>, ...), ...; で行（タプル）の挿入を開始できます。この文を使って単一の行または複数の行を同時に挿入できます。自動インクリメントされる主キーとデフォルト値は行を挿入する際に指定する必要がないため、 t1ではINSERT INTO t1 (name, age) VALUES ('A', 18), ('B', 20);のように行を挿入できます。ここで重要なのは、SQLの標準では二重引用符"はテーブルやカラム名に使用され、単一引用符'は文字列値に使用されるということです。

読み取り/クエリ

テーブルを作成して行を追加したら、読み取りやクエリを開始できます。テーブル内のすべての行をクエリするには、SELECT * FROM <テーブル名>;を使用します。ここで*はすべての列を表します。これによりターミナルにテーブルが表示されます。LIMIT <数字>やOFFSET <数字>などの句を使用して、特定のオフセットから始まる限られた数の行をクエリすることができます。例えば、SELECT * FROM t1 LIMIT 3 OFFSET 1;はt1テーブルの2行目から始まる3行だけをクエリします。

postgres=# SELECT * FROM t1; 
 id | name | age
----+------+-----
  1 | A    |  18
  2 | B    |  20
  3 | C    |  27
  4 | D    |  25
  5 | E    |  19
(5 rows)
 
postgres=# SELECT id, name FROM t1 ORDER BY id DESC LIMIT 3 OFFSET 1;
 id | name 
----+------
  4 | D
  3 | C
  2 | B
(3 rows)
 
postgres=# SELECT id, name, age FROM t1 ORDER BY age DESC WHERE age >= 20;
 id | name | age
----+------+-----
  3 | C    |  27
  4 | D    |  25
  2 | B    |  20
(3 rows)

また、ORDER BY <列名>を使用して行の表示順序を決定する列を指定することもできます。例えばSELECT * FROM t1 ORDER BY id;のようにします。デフォルトでは昇順ですが、句にDESCを追加することで降順で行を表示することもできます。行はWHERE <条件>を使用してフィルタリングできます。これらの条件は、=、!=、>などの比較演算子や、 AND、OR、NOT、ISなどの論理演算子を使用して定義できます。（例えば、age >= 20 AND age <= 25や BETWEEN 20 AND 25、IS NOT NULLなどの条件を表現できます）。上記の例は、これらのクエリを示しています。

また、%や_などのワイルドカード文字を使用して、文字列属性に基づいて行をフィルタリングすることもできます。 %は任意の長さの任意の文字列を表し、_は任意の1文字を表します。例えば、タイトルのどこかに"database"を含む本の行を WHERE title LIKE '%database%'を使用して検索したり、1月に出版された本を WHERE CAST(publishedAt AS VARCHAR) LIKE '____-01-%'を使用して検索したりできます。これらはアプリケーションからの現実的なクエリです。

更新と削除

UPDATE <テーブル名> SET <列名>=<列の値>, ...;を使用して、テーブル内のすべての行の属性を更新できます。主キーやその他の条件を使用して、更新する行をさらに指定することができます。例えば、 UPDATE t1 SET age=26 WHERE name='D'は、Dの年齢を25から26に更新します。また、SET age=age+1のような算術演算子を使用して、より再利用可能な文を作成することもできます。 ALTER TABLEを使用して列を操作することもできます。例えば、 ALTER TABLE t1 ADD email VARCHAR(255);のように新しい列を追加したり、 ALTER TABLE t1 ALTER COLUMN age TYPE VARCHAR(3);やALTER COLUMN name TYPE VARCHAR(100);のように列の型を変更したりできます。

DELETE FROM <テーブル名>;を使用して、テーブル内のすべての行を削除できます。また、ALTER TABLE <テーブル名> DROP COLUMN <列名>;で列全体を削除することもできます。特定の行を削除するには、WHERE name='D'やWHERE age <= 18などの条件を追加できます。テーブル全体やデータベースを削除するには、それぞれDROP TABLE <テーブル名>とDROP DATABASE <データベース名>を使用します。ただし、テンプレートデータベースは新しいデータベースの作成に使用されるため、 DROP DATABASE文を使用して削除することはできません。

結論

この記事では、テーブルでのCRUD操作を実行するための基本的なSQL文と、PostgreSQLでのいくつかのコマンドについて説明しました。コマンド、構文、およびそれらの対応する動作は異なるリレーショナルDBMS間で類似していますが、その違いに注意することが重要です。次の記事では、SQLについてさらに深く掘り下げ、より複雑なクエリについて説明します。

リソース

Neon. n.d. PostgreSQL Administration. PostgreSQL Tutorial.
PostgreSQL. n.d. PostgreSQL 17.4 Documentation. PostgreSQL.