LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Ryosuke1025(Ryosuke)in株式会社ゆめみ

PostgreSQL入門

Last updated at Posted at 2025-06-02

はじめに

本記事では、サンプルを用いて、PostgreSQLの基礎的な構文一覧を、視覚的かつ簡単に学ぶことを目的とします。PostgreSQLはRDBMS(Relational Database Management System)の一つで、以下の分類順で、構文を解説していきます。

  • ①データ定義言語 DDL (Data Definition Language)
  • ②データ操作言語 DML (Data Manipulation Language)
  • ③トランザクション制御言語 TCL (Transaction Control Language)
  • ④データ制御言語 DCL (Data Control Language)

本記事の説明ではPostgreSQL-Sampleを使用しており、ローカルホストでPostgreSQLに接続して実行しています。

用語定義

カラム・・・テーブルの列(属性)

レコード・・・テーブルの行(データ1件)

フィールド・・・テーブルの列と行が交差する箇所(値)

候補キー・・・レコードを一意に識別するためのカラム、またはカラムの組み合わせ

主キー・・・候補キーのうちの1つ

外部キー・・・別テーブルの主キーを参照することで、テーブル間の関連付けができるカラム

クエリ・・・データベースに対する質問・要求（SQL文）

トランザクション・・・データベースなどのシステムにおける一連の処理の単位

SQLの基礎的な構文一覧

①データ定義言語（DDL）

まずはじめに、主キーをidとした顧客テーブルを作成します。

CREATE TABLE（テーブル作成）

CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,         -- 顧客ID
    name TEXT NOT NULL,            -- 顧客名
    email TEXT NOT NULL UNIQUE,    -- メールアドレス
    phone TEXT,                    -- 電話番号
    address TEXT                   -- 住所
);

SERIALは自動的に連番（1, 2, 3, ...）を生成する整数型カラムを作るための型です。このテーブルだと、idが主キー、emailが候補キーになります。また、name, emailは必ず値が入ります。

ALTER TABLE（テーブル変更）

ALTER TABLE customers ADD COLUMN age INTEGER;

これでage(年齢)という新たなカラムを追加できます。

DROP TABLE（テーブル削除）

DROP TABLE IF EXISTS customers;

CHECK（制約）

CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,         -- 顧客ID
    name TEXT NOT NULL,            -- 顧客名
    email TEXT NOT NULL UNIQUE,    -- メールアドレス
    phone TEXT CHECK (             -- 電話番号
        phone IS NULL OR (
            LENGTH(phone) >= 10 AND 
            LENGTH(phone) <= 15
        )
    ),                    
    address TEXT                   -- 住所
);

このように、CHECKを使うと、特定のカラムに値の制約を加えることができます。今回の場合だと、10文字以上, 15文字以下という制約を加えており、これを満たさないレコードを挿入しようとすると、実行時にエラーになります。

②データ操作言語（DML）

SELECT（基本的なデータ取得）

SELECT * FROM customers;

*を付けることで、全てのレコードに対するカラムの値を取得できます。ただし、以下のように、明示的に取得するカラムを書くこともできます。

SELECT id, name, email, phone, address FROM customers;

INSERT（データ挿入）

INSERT INTO customers (name, email, phone, address) VALUES
  ('山田太郎', 'taro.yamada@example.com', '03-1234-5678', '東京都新宿区新宿1-1-1'),
  ('佐藤花子', 'hanako.sato@example.com', '06-8765-4321', '大阪府大阪市北区梅田2-2-2'),
  ('鈴木一郎', 'ichiro.suzuki@example.com', '045-123-4567', '神奈川県横浜市西区みなとみらい3-3-3'),
  ('田中美香', 'mika.tanaka@example.com', '052-987-6543', '愛知県名古屋市中区栄4-4-4'),
  ('高橋健太', 'kenta.takahashi@example.com', '092-345-6789', '福岡県福岡市博多区博多5-5-5');

スクリーンショット 2025-05-20 0.15.27.png

WHERE（条件指定）

SELECT * FROM customers
WHERE address = '愛知県名古屋市中区栄4-4-4';

スクリーンショット 2025-05-22 1.10.22.png

このクエリは単一の条件でフィルタリングしていますが、以下のようにAND演算を使って、複数の条件でフィルタリングすることもできます。

SELECT * FROM customers
WHERE address = '愛知県名古屋市中区栄4-4-4'
AND phone = '052-987-6543';

IN（値リスト検索）

SELECT * FROM customers
WHERE name IN ('佐藤花子', '鈴木一郎');

スクリーンショット 2025-05-26 23.18.23.png

WHEREのみで表現する場合以下のように冗長になってしまいます。

SELECT * FROM customers
WHERE name = '佐藤花子' OR 
name = '鈴木一郎';

このように、いずれかの値に一致していれば良いという場合に、簡潔に書けるのが利点です。

LIKE（パターンマッチング）

SELECT * FROM customers
WHERE address LIKE '愛知県%';

これで、WHEREの例と同じレコードが取り出せます。
LIKEは部分一致演算で、完全一致していなくても取り出すことができます。

UPDATE（データ更新）

UPDATE customers
SET phone = '03-9876-5432', address = '東京都渋谷区渋谷1-2-3'
WHERE name = '山田太郎';
SELECT * FROM customers

スクリーンショット 2025-05-21 23.40.33.png

DELETE（データ削除）

DELETE FROM customers
WHERE name = '高橋健太';
SELECT * FROM customers

スクリーンショット 2025-05-21 23.47.00.png

ORDER BY（並べ替え）

SELECT * FROM customers
ORDER BY phone;

スクリーンショット 2025-05-21 23.59.02.png

これは昇順に並べていますが、以下のようにDESCを付与すると、降順に並べられます。

SELECT * FROM customers
ORDER BY phone DESC;

GROUP BY（グループ化）

SELECT 
    substring(address from '^([^都道府県]+[都道府県])') as prefecture,
    COUNT(*) as customer_count
FROM customers
GROUP BY prefecture;

スクリーンショット 2025-05-22 0.17.31.png

正規表現について、本記事では取り上げないですが、このように書くと、あるカラムの値ごとにグループ化し、それぞれのグループに含まれるレコード数を取り出すことができます。

HAVING（グループに対する条件指定）

SELECT 
    substring(address from '^([^都道府県]+[都道府県])') as prefecture,
    COUNT(*) as customer_count
FROM customers
GROUP BY prefecture
HAVING substring(address from '^([^都道府県]+[都道府県])') LIKE '%県';

スクリーンショット 2025-05-22 1.04.34.png

LIMIT/OFFSET（結果件数の制限）

SELECT * FROM customers ORDER BY id LIMIT 3;

スクリーンショット 2025-05-25 23.43.03.png

また、OFFSETを使うことで、特定の件数をスキップできます。以下の例だと、最初の3件スキップして、2件取得しています。

SELECT * FROM customers
ORDER BY id
LIMIT 2 OFFSET 3;

ここで、主キーをorder_id, 外部キーをcustomer_idとした注文テーブルを新たに作成します。

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  price INTEGER NOT NULL,
  order_date TEXT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

そして、以下のように任意のレコードを追加します。

INSERT INTO orders (order_id, customer_id, product_name, price, order_date) VALUES
  (101, 1, 'ノートパソコン', 120000, '2023-05-15'),
  (102, 3, 'スマートフォン', 85000, '2023-05-16'),
  (103, 1, 'ワイヤレスイヤホン', 25000, '2023-05-20'),
  (104, 2, 'タブレット', 60000, '2023-05-22'),
  (105, 4, 'スマートウォッチ', 35000, '2023-05-25');

スクリーンショット 2025-05-23 0.21.33.png

また、customersに1件レコードを追加します。

INSERT INTO customers (name, email, phone, address) VALUES
  ('高橋健太', 'kenta.takahashi@example.com', '092-345-6789', '福岡県福岡市博多区博多5-5-5');

スクリーンショット 2025-05-24 0.50.09.png

CASE式（条件分岐）

SELECT 
  product_name,
  price,
  CASE 
    WHEN price >= 100000 THEN '高額商品'
    WHEN price >= 50000 THEN '中額商品'
    WHEN price >= 20000 THEN '低額商品'
    ELSE '格安商品'
  END AS price_category
FROM orders
ORDER BY price DESC;

スクリーンショット 2025-05-25 23.16.44.png

INNER JOIN（内部結合）

SELECT c.name, c.email, o.product_name, o.price, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

スクリーンショット 2025-05-23 0.49.48.png

このように、外部キーによって、2つのテーブルを結合することもできます。INNER JOINでは、2つのテーブルで一致しているレコードのみ取り出して、結合します。

LEFT JOIN, RIGHT JOIN, FULL JOIN（外部結合）

SELECT c.name, c.email, c.address, o.product_name, o.price, o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id

スクリーンショット 2025-05-24 0.46.16.png

LEFT JOINは、左テーブルの全レコードを取り出して、埋まっていないフィールドをNULLとして結合します。同様に、RIGHT JOINなら右テーブルのレコード、FULL JOINなら両方のテーブル全てのレコードを取り出して結合します。今回は、左テーブルがcustomers, 右テーブルがordersとなります。

BETWEEN（範囲検索）

SELECT * FROM orders
WHERE price BETWEEN 20000 AND 50000

スクリーンショット 2025-05-25 23.37.41.png

ここで、1人の電話番号をNULLにしてみます。

UPDATE customers
SET phone = NULL
WHERE name = '田中美香';

スクリーンショット 2025-05-26 22.44.31.png

IS NULL, IS NOT NULL（NULL値の判定）

SELECT name, email, phone
FROM customers
WHERE phone IS NULL;

スクリーンショット 2025-05-26 22.46.56.png

IS NULLで、NULLかどうかを判定できます。逆にIS NOT NULLで、NULLではないかどうかを判定できます。

COUNT, SUM, AVG, MAX, MIN（集計関数）

SELECT COUNT(*) AS total_customers FROM customers;

スクリーンショット 2025-05-26 23.25.24.png

SELECT SUM(price) AS total_sales FROM orders;

スクリーンショット 2025-05-26 23.26.19.png

COUNTは指定したレコードの数、SUMは合計を算出します。同様に、AVGは平均、MAXは最大値、MINは最小値を算出します。

DISTINCT（重複除去）

SELECT DISTINCT customer_id FROM orders;

スクリーンショット 2025-05-27 23.07.43.png

ここで新たに、従業員テーブルを作成して、任意のデータを挿入します。

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    address TEXT
);

INSERT INTO employees (employee_id, name, email, phone, address) VALUES
  (11, '山田太郎', 'taro.yamada@company.com', '03-1111-2222', '東京都千代田区丸の内1-1-1'),
  (12, '佐藤花子', 'hanako.sato@company.com', '06-2222-3333', '大阪府大阪市中央区本町2-2-2'),
  (13, '田中一郎', 'ichiro.tanaka@company.com', '052-3333-4444', '愛知県名古屋市東区東桜3-3-3'),
  (14, '高橋健太', 'kenta.takahashi@company.com', '092-4444-5555', '福岡県福岡市中央区天神4-4-4');

スクリーンショット 2025-05-27 23.32.28.png

UNION, UNION ALL, INTERSECT, EXCEPT（集合演算）

SELECT name FROM customers
UNION
SELECT name FROM employees;

スクリーンショット 2025-05-28 1.46.06.png

UNIONは、2つのクエリ結果の和(重複を許容しない)を抽出します。UNION ALLは和(重複を許容する)、同様に、INTERSECTは積、EXCEPTは差(1つ目のクエリ結果にあって、2つ目のクエリ結果にないもの)を抽出します。

サブクエリ

SELECT order_id, customer_id, product_name, price
FROM orders
WHERE price > (SELECT AVG(price) FROM orders);

今回の例では、orders内のpriceの平均値を取り出し、その値よりも大きい価格を持つレコードを取り出しています。つまり、サブクエリとはクエリの中のクエリです。

③トランザクション制御言語（TCL）

トランザクション制御言語を学ぶ前に、トランザクションの4つの特性(ACID 特性)から学びましょう。以下のようなシナリオを考えます。

ユーザーAの口座残高：10万円
ユーザーBの口座残高：5万円
ユーザーCの口座残高：7万5千円
ユーザーA → ユーザーBに 1万円を送金する

特性 用語定義 例を用いた説明
原子性（Atomicity） トランザクション内の処理は全て成功するか、全て失敗して何も起きなかったことになる。 Aの残高が1万円減り、Bの残高が1万円増える。途中でエラーが発生したらどちらの口座も変更されない。
一貫性(Consistency) トランザクション前後で、システム全体の整合性が保たれる。 処理後のAとBの合計残高は 15万円で変わらない。Bだけが増えたり、Aが減っただけになることはない。
独立性（Isolation） 他のトランザクションの影響を受けずに処理される（同時実行でも矛盾しない）。 同時にCがAに振込を行っていても、Aの残高が衝突しないように分離されて処理される。
永続性（Durability） トランザクションが完了したら、結果は確実に保存され、システム障害でも失われない。 振込完了後にサーバーが落ちても、Aの減額とBの増額は記録として残る。ログやディスクへの書き込みで保証される。

上記の例を元に、以下のような口座アカウントテーブルを作成する。

CREATE TABLE bank_accounts (
    account_id SERIAL PRIMARY KEY,
    account_name VARCHAR(50) NOT NULL,
    balance INTEGER NOT NULL
);

INSERT INTO bank_accounts (account_name, balance) VALUES
('山田太郎', 100000),
('佐藤花子', 50000),
('田中次郎', 75000);

スクリーンショット 2025-06-01 15.46.11.png

BEGIN, COMMIT(正常終了)

-- BEGINでトランザクションを開始
BEGIN;

-- 山田太郎から佐藤花子に10000円送金
UPDATE bank_accounts SET balance = balance - 10000 WHERE account_name = '山田太郎';
UPDATE bank_accounts SET balance = balance + 10000 WHERE account_name = '佐藤花子';

-- 変更を確認
SELECT * FROM bank_accounts;

-- COMMITで変更を確定
COMMIT;

-- 確定後の状態
SELECT * FROM bank_accounts
ORDER BY account_id;

コミット前後のテーブルが以下の通りです。

スクリーンショット 2025-06-01 16.32.13.png

BEGIN, ROLLBACK(異常終了)

-- 新しいトランザクション開始
BEGIN;

-- 田中次郎から山田太郎に10000円送金
UPDATE bank_accounts SET balance = balance - 10000 WHERE account_name = '田中次郎';
UPDATE bank_accounts SET balance = balance + 10000 WHERE account_name = '山田太郎';

-- 変更を確認
SELECT * FROM bank_accounts ORDER BY account_id;

COMMIT前は以下のように、仮の変更後のデータが見えるになっていますが、実際は書き換えられていません。

スクリーンショット 2025-06-01 16.36.38.png

-- ROLLBACKで変更を取り消し
ROLLBACK;

-- 取り消し後の状態（元に戻る）
SELECT * FROM bank_accounts ORDER BY account_id;

ROLLBACK後、データが元に戻されます。
スクリーンショット 2025-06-01 16.37.58.png

④データ制御言語（DCL）

CREATE USER, DROP USER（ユーザー管理）

CREATE USER user1;

上記のよう記述するとユーザーを作成できます。

DROP USER user1;

逆に、このように記述するとユーザーを削除できます。

GRANT, REVOKE（権限管理）

GRANT SELECT ON customers TO user1;

これで、ユーザー1に対してcustomersテーブルへのSELECT操作の権限を与えることができます。

REVOKE SELECT ON customers FROM user1;

逆に、上記のように記述すると、ユーザー1に対してcustomersテーブルへのSELECT操作の権限を剥奪することができます。

GRANT SELECT, INSERT, UPDATE ON customers TO user1;

上記のように、複数の操作に対して一括で権限を付与(剥奪)することもできます。

GRANT ALL PRIVILEGES ON customers TO user1;

また、上記のように記述すると、全ての操作に対して一括で権限を付与(剥奪)することもできます

なお、本番環境では管理者のみが実施し、設定したパスワードを使って権限を与えます。

今回取り扱っていない、やや難易度の高い重要な構文

①データ定義言語（DDL）

  • CREATE INDEX（インデックス作成）
  • CREATE VIEW（ビュー作成）
  • TRUNCATE TABLE（テーブルデータ全削除）

②データ操作言語（DML）

  • EXISTS/NOT EXISTS（存在判定・非存在判定）
  • WITH（共通テーブル式・一時的な名前付きクエリ）
  • SELF JOIN（自己結合）
  • COALESCE（NULL値の置換）
  • RETURNING句（INSERT/UPDATE後の値取得）
  • ON CONFLICT（UPSERT操作）
  • ILIKE（大文字小文字を無視したLIKE）
  • EXPLAIN（実行計画表示）
  • 文字列関数（SUBSTRING, LENGTH, CONCAT等）
  • 日付関数（CURRENT_DATE, EXTRACT等）
  • 数値関数（ROUND, CEILING, FLOOR等）
  • 型変換関数（CAST, CONVERT等）
  • Window関数（ROW_NUMBER, RANK等）
  • 相関サブクエリ（外側クエリと関連するサブクエリ）

③トランザクション制御言語（TCL）

  • SET TRANSACTION ISOLATION LEVEL（分離レベル）

④データ制御言語（DCL）

  • CREATE ROLE, DROP ROLE（ロール管理）

おわりに

本記事では、できる限りシンプルな例を用いて書く構文を説明させて頂きました。ただし、実務ではもっと複雑なクエリを扱うと思います。また、データベースを学ぶ上で、データベース操作だけではなく、データベース設計も学ぶ必要があります。データベース設計に関しては、また今度記事を書こうと思っています。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?