第14章　SQLデータ管理の基礎

LPIC-1 105.3 相当

Webアプリケーションの多くはデータベースと連携して動いており、Linux管理者もトラブル調査やデータ確認のために、簡単なSQLを読み書きできると役立つ場面が多くあります。この章では、RDBMS（リレーショナルデータベース管理システム）の基本操作を扱います。

なぜLinux管理者がSQLを知る必要があるか

「アプリケーションからデータが正しく登録されているか確認したい」「特定の条件のレコードだけ削除したい」といった場面で、アプリケーションのコードを読まなくても、データベースに直接SQLを発行して確認・対処できると、調査のスピードが大きく変わります。LPIC-1でも、SQLの基本文法とMySQL/MariaDB・PostgreSQLの基本的なCLI操作が出題範囲に含まれています。

SELECTの基本

データを取得する基本のSQLはSELECT文です。

-- usersテーブルから、名前とメールアドレスを取得する
SELECT name, email FROM users WHERE age >= 20 ORDER BY name LIMIT 10;

WHERE句では、=・<>（等しくない）・>・<などの比較演算子や、AND・OR・NOTといった論理演算子を組み合わせられます。文字列の部分一致にはLIKEとワイルドカード（%は任意の文字列、_は任意の1文字）を使い、値がNULL（未設定）かどうかはIS NULL / IS NOT NULLで判定します（= NULLとは書けない点に注意してください）。

集計とグループ化

GROUP BYを使うと、指定した列の値ごとにグループ化した上で、COUNT（件数）、SUM（合計）、AVG（平均）、MAX・MIN（最大・最小）といった集計関数を適用できます。グループ化した結果をさらに条件で絞り込みたい場合は、WHEREではなくHAVINGを使います。

-- 部署ごとの人数を集計し、5人以上の部署だけを表示する
SELECT department, COUNT(*) AS cnt FROM users GROUP BY department HAVING COUNT(*) >= 5;

JOIN（複数テーブルの結合）

実際のデータベースでは、ユーザー情報と注文情報のように、データが複数のテーブルに分かれて格納されているのが普通です。これらを1つの結果として取得するのがJOINです。

-- 注文があるユーザーだけでなく、注文がまだ無いユーザーも含めて一覧にする
SELECT users.name, orders.item FROM users
LEFT OUTER JOIN orders ON users.id = orders.user_id;

「注文があるユーザーだけでよいか（INNER JOIN）」「注文の有無にかかわらず全ユーザーを見たいか（LEFT OUTER JOIN）」で使い分けます。どちらを使うかによって結果の行数が変わるため、意図しないJOINの種類を選んでしまうと、集計結果が実態とずれてしまう点に注意が必要です。

データの追加・変更・削除

INSERT INTO users (name, email) VALUES ('Taro', 'taro@example.com');
UPDATE users SET email = 'new@example.com' WHERE id = 1;
DELETE FROM users WHERE id = 1;

テーブル自体を新しく作るにはCREATE TABLEを使います。主なデータ型には、整数のINT、可変長文字列のVARCHAR(n)、長い文章向けのTEXT、日時のDATETIMEなどがあります。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    created_at DATETIME
);

PRIMARY KEYは、その列（または列の組み合わせ）がテーブル内で行を一意に識別するための指定です。NOT NULLを付けた列には、値を省略して登録することができなくなります。こうした制約をあらかじめテーブル定義に組み込んでおくことで、アプリケーション側のバグによる不正なデータの混入を、データベース側で機械的に防げます。

トランザクションの基本

複数のSQL文をひとまとまりの処理として実行し、途中で失敗した場合はすべて取り消したい、という場面ではトランザクションを使います。BEGIN（またはSTART TRANSACTION）で開始し、問題がなければCOMMITで確定、途中で異常があればROLLBACKで開始前の状態に巻き戻します。

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

例えば口座間の送金処理のように、「片方だけ更新されて、もう片方は更新されない」という中途半端な状態を許してはいけない処理では、トランザクションでひとまとまりに扱うことが重要になります。

MySQL/MariaDBとPostgreSQLのCLI操作

コマンドラインからデータベースに接続するツールは、製品ごとに異なります。

クエリの実行結果をシェルスクリプトに組み込みたい場合は、mysqlコマンドに-eオプションでSQLを直接渡したり、標準入力経由でSQLファイルを流し込んだりする方法がよく使われます。

$ mysql -u user -p -e "SELECT COUNT(*) FROM users;" mydb
$ mysql -u user -p mydb < backup.sql   # SQLファイルの内容をまとめて実行する