Open4

SQLキャッチアップ

horihori
  • SQL
    • DBを操作する言語
    • できること
      • データ操作（DML）: SELECT、INSERT、UPDATE、DELETE
      • データ定義（DDL）: CREATE、ALTER、DROP
      • データ制御（DCL）: GRANT、REVOKE、COMMIT、ROLLBACK
    • RDBMS上で実行
      • SQLを使うには、DBを管理するシステムが必要となる（RDBMS）
        • Oracle Database
        • MySQL
        • Postgresql
horihori

データ操作（DML）の基本

SELECT - データの取得

  • テーブルからデータを検索・取得する
  • 基本構文：SELECT 列名 FROM テーブル名 WHERE 条件
  • 全列取得：SELECT * FROM users
  • 条件指定：SELECT name, age FROM users WHERE age >= 20
  • 並び替え：ORDER BY 列名 ASC/DESC
  • 件数制限：LIMIT 10

JOIN - テーブルの結合

  • 複数テーブルを関連付けてデータを取得する
  • INNER JOIN：両テーブルに存在するデータのみ取得 SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id
  • LEFT JOIN：左テーブルの全データ＋右テーブルの一致データ SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id
  • RIGHT JOIN：右テーブルの全データ＋左テーブルの一致データ
  • 多対多の結合：中間テーブル経由で結合 FROM categories LEFT JOIN book_categories ON categories.id = book_categories.category_id LEFT JOIN books ON book_categories.book_id = books.id
  • 結合条件：ONで指定、正確な外部キー関係を理解することが重要

集計関数とGROUP BY

  • COUNT()：行数をカウント SELECT COUNT(*) FROM users / SELECT COUNT(DISTINCT city) FROM users
  • SUM()：合計値 SELECT SUM(price) FROM orders
  • AVG()：平均値 SELECT AVG(age) FROM users
  • MAX()/MIN()：最大/最小値 SELECT MAX(salary), MIN(salary) FROM employees
  • GROUP BY：グループ化して集計 SELECT department, COUNT(*) FROM employees GROUP BY department
  • HAVING：集計結果の絞り込み SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 1000

INSERT - データの追加

  • テーブルに新しい行を追加する
  • 基本構文：INSERT INTO テーブル名 (列1, 列2) VALUES (値1, 値2)
  • 全列指定：INSERT INTO users (id, name, age) VALUES (1, '田中', 25)
  • 列省略時：INSERT INTO users VALUES (1, '田中', 25)
  • 複数行追加：VALUES (値1), (値2), (値3)で一度に複数行追加可能

UPDATE - データの更新

  • 既存データを変更する
  • 基本構文：UPDATE テーブル名 SET 列名 = 新値 WHERE 条件
  • 単一更新：UPDATE users SET age = 26 WHERE id = 1
  • 複数列更新：UPDATE users SET name = '鈴木', age = 30 WHERE id = 2
  • WHERE句なし：全行が更新されるため要注意

DELETE - データの削除

  • テーブルから行を削除する
  • 基本構文：DELETE FROM テーブル名 WHERE 条件
  • 条件削除：DELETE FROM users WHERE age < 18
  • WHERE句なし：全データ削除となるため要注意
  • TRUNCATEとの違い：DELETEは行単位、TRUNCATEはテーブル全体を高速削除

共通の注意点

  • WHERE句の重要性：UPDATE/DELETEでは条件指定を忘れると全データが対象になる
  • JOINの結合条件：正しい外部キー関係を指定しないと意図しないデータが取得される
  • GROUP BY使用時：SELECTにはGROUP BY指定カラムか集計関数のみ記述可能
  • 集計でのNULL：COUNT(*)以外の集計関数はNULLを除外して計算
  • ORDER BYの複数条件：優先順位を考慮して指定 ORDER BY num DESC, name ASC
  • トランザクション：BEGIN/COMMITで変更を確定、ROLLBACKで取り消し
  • 実行前確認：まずSELECTで対象データを確認してから操作することを推奨
horihori

データ定義（DDL）の基本

CREATE - オブジェクトの作成

  • データベースオブジェクト（テーブル、インデックス等）を新規作成する
  • 基本構文：CREATE TABLE テーブル名 (列名 データ型 制約)
  • シンプルなテーブル：CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50))
  • 制約付き：CREATE TABLE orders (id INT PRIMARY KEY, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id))
  • インデックス作成：CREATE INDEX idx_name ON users(name)
  • IF NOT EXISTS：既存チェック付きで安全に作成

ALTER - オブジェクトの変更

  • 既存のテーブル構造を変更する
  • 基本構文：ALTER TABLE テーブル名 ADD/MODIFY/DROP ...
  • カラム追加：ALTER TABLE users ADD email VARCHAR(100)
  • カラム変更：ALTER TABLE users MODIFY age INT NOT NULL
  • カラム削除：ALTER TABLE users DROP COLUMN email
  • 制約追加：ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email)
  • テーブル名変更：ALTER TABLE users RENAME TO customers

DROP - オブジェクトの削除

  • データベースオブジェクトを完全に削除する
  • 基本構文：DROP TABLE テーブル名
  • テーブル削除：DROP TABLE users
  • 安全な削除：DROP TABLE IF EXISTS users
  • カスケード削除：DROP TABLE users CASCADE（依存オブジェクトも削除）
  • データベース削除：DROP DATABASE test_db
  • インデックス削除：DROP INDEX idx_name

主要なデータ型

  • 数値型：INT（整数）、DECIMAL(10,2)（小数）、FLOAT（浮動小数点）
  • 文字列型：VARCHAR(n)（可変長）、CHAR(n)（固定長）、TEXT（長文）
  • 日付型：DATE（日付）、TIME（時刻）、DATETIME（日時）、TIMESTAMP
  • 論理型：BOOLEAN（真偽値）
  • AUTO_INCREMENT：主キーの自動採番（MySQL）、SERIAL（PostgreSQL）

主要な制約

  • PRIMARY KEY：主キー（一意かつNOT NULL）
  • FOREIGN KEY：外部キー（他テーブル参照）
  • NOT NULL：NULL値を禁止
  • UNIQUE：重複値を禁止
  • DEFAULT：デフォルト値を設定
  • CHECK：値の条件を指定 CHECK (age >= 0)

共通の注意点

  • DROP操作の危険性：削除は取り消し不可、必ずバックアップを取る
  • ALTER実行時：大規模テーブルでは処理時間が長い、メンテナンス時間帯に実行
  • 命名規則：テーブル名は複数形（users）、カラム名はスネークケース（created_at）
  • DBMS依存：MySQL/PostgreSQL/SQL Serverで構文に差異があるため確認が必要
  • 開発時の順序：CREATE → ALTER（必要時）→ DROP（不要時）の順で管理
horihori

データ制御（DCL）の基本

GRANT - 権限の付与

  • ユーザーやロールにデータベース操作の権限を与える
  • 基本構文：GRANT 権限 ON オブジェクト TO ユーザー
  • 全権限付与：GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'
  • SELECT権限：GRANT SELECT ON users TO 'reader'@'%'
  • 複数権限：GRANT SELECT, INSERT, UPDATE ON orders TO 'operator'@'localhost'
  • 権限の伝播：GRANT SELECT ON users TO 'admin' WITH GRANT OPTION
  • テーブル作成権限：GRANT CREATE ON database.* TO 'developer'

REVOKE - 権限の取り消し

  • 付与された権限を取り消す
  • 基本構文：REVOKE 権限 ON オブジェクト FROM ユーザー
  • 全権限取消：REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'localhost'
  • 特定権限取消：REVOKE DELETE ON orders FROM 'operator'@'localhost'
  • 複数権限取消：REVOKE INSERT, UPDATE ON users FROM 'guest'@'%'
  • GRANT OPTION取消：REVOKE GRANT OPTION ON users FROM 'admin'
  • CASCADE指定：権限の連鎖的取消（付与した先の権限も取消）

COMMIT - トランザクションの確定

  • トランザクション内の変更を永続化する
  • 基本構文：COMMIT
  • 自動コミット無効化：SET AUTOCOMMIT = 0
  • トランザクション開始：BEGIN または START TRANSACTION
  • 変更確定：INSERT INTO users VALUES (1, '田中'); COMMIT;
  • COMMIT後は変更を取り消せない
  • 明示的コミット：COMMIT WORK（標準SQL）

ROLLBACK - トランザクションの取り消し

  • トランザクション内の変更を破棄する
  • 基本構文：ROLLBACK
  • 全変更取消：BEGIN; DELETE FROM users; ROLLBACK;（削除が取り消される）
  • セーブポイントまで戻る：ROLLBACK TO SAVEPOINT sp1
  • セーブポイント作成：SAVEPOINT point1
  • 部分的な取消：セーブポイントを活用して特定地点まで戻る
  • エラー時の自動ロールバック：制約違反等で自動実行される場合あり

主要な権限の種類

  • SELECT：データ参照権限
  • INSERT：データ追加権限
  • UPDATE：データ更新権限
  • DELETE：データ削除権限
  • CREATE：テーブル等の作成権限
  • DROP：テーブル等の削除権限
  • ALTER：テーブル構造の変更権限
  • INDEX：インデックスの作成・削除権限
  • ALL PRIVILEGES：全権限

トランザクション制御

  • BEGIN/START TRANSACTION：トランザクション開始
  • COMMIT：変更を確定して終了
  • ROLLBACK：変更を破棄して終了
  • SAVEPOINT：中間地点の設定
  • AUTOCOMMIT：自動コミットモード（MySQL等）
  • 分離レベル：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE

共通の注意点

  • 権限設計の原則：最小権限の原則（必要最小限の権限のみ付与）
  • GRANT/REVOKE後：FLUSH PRIVILEGESで権限テーブルを再読み込み（MySQL）
  • トランザクションの重要性：データの整合性を保つため適切に使用
  • COMMIT前の確認：一度COMMITすると取り消し不可
  • デッドロック対策：長時間のトランザクションは避ける
  • 本番環境：権限管理は特に慎重に、定期的な権限の棚卸しを推奨