> For the complete documentation index, see [llms.txt](https://engineer.toggle.co.jp/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://engineer.toggle.co.jp/chapter-1-toggle-holdings-engineer-101/ji-chu-yan-xiu/sql.md). # SQL ## **SQLとは** SQL（Structured Query Language）は、データベースを操作するための標準的な言語です。具体的には、データベースに格納された情報の取得、更新、挿入、削除などの操作を行うために使用されます。 ### **データベースの基本概念** 1. **データベース (Database)**: 組織化されたデータの集合。これは、ユーザーの情報、商品のリスト、取引記録など、あらゆる種類のデータを保存するためのものです。 **例:** 図書館のシステムで、本や蔵書情報を管理するデータベースが存在する。 2. **テーブル (Table)**: データベース内のデータを格納する主要な構造。テーブルは行と列で構成されます。 **例:** 「顧客」テーブルでは、顧客ID、氏名、住所、電話番号などの列がある。 3. **レコード (Record)**: テーブル内の個々のデータ項目。一つのレコードはテーブルの一行に相当します。 **例:** 「顧客」テーブルの一行が一人の顧客の情報を表すレコードとなる。 4. **フィールド (Field)**: レコード内の各データ項目。テーブルの列に相当します。 **例:** 「顧客」テーブルの「氏名」の列に格納される情報。 ### **SQLの歴史** 1. 1970年代初頭、IBMの研究者たちがリレーショナルデータベース管理システム (RDBMS) の原型を作り上げる過程で、SQLが考案されました。 2. その後、SQLはアメリカ国家規格協会 (ANSI) と国際標準化機構 (ISO) によって、データベース言語としての標準と認定されました。 ### クエリとは データベースに対して特定の操作や情報の取得を要求するための命令文のことを指します。リレーショナルデータベース管理システム（RDBMS）でデータの取得、挿入、更新、削除などの操作を行うために使用されます。 例えば、あるテーブルから特定のデータを取得する際や、新しいデータを追加する際、またはデータを変更・削除する際に、データベースに対してその操作を伝えるための文を「クエリ」と呼びます。 具体的な例を挙げると、`employees`というテーブルから全てのデータを取得したい場合のSQLクエリは以下のようになります： ```sql SELECT * FROM employees; ``` このように、クエリはデータベースとのコミュニケーションツールのようなもので、何をどのように操作したいのかをデータベースに伝える役割を果たします。 ### 基本的なSQLクエリ 1. **データの問い合わせ**: データベースから必要な情報を取得するためのSELECT文を使用します。 **例:** 顧客テーブルから特定の顧客の情報を取得する。 ```sql SELECT * FROM 顧客 WHERE 顧客ID = 1; ``` 2. **データの挿入**: 新しいデータをテーブルに追加するためのINSERT文を使用します。 **例:** 顧客テーブルに新しい顧客の情報を追加する。 ```sql INSERT INTO 顧客 (氏名, 住所, 電話番号) VALUES ('田中太郎', '東京都', '03-1234-5678'); ``` 3. **データの更新**: 既存のデータを変更するためのUPDATE文を使用します。 **例:** 顧客テーブルで特定の顧客の電話番号を変更する。 ```sql UPDATE 顧客 SET 電話番号 = '03-9876-5432' WHERE 顧客ID = 1; ``` 4. **データの削除**: データをテーブルから削除するためのDELETE文を使用します。 **例:** 顧客テーブルから特定の顧客の情報を削除する。 ```sql DELETE FROM 顧客 WHERE 顧客ID = 1; ``` 5. **並び替え:** `ORDER BY`句を使うと、取得したレコードの順序を指定したカラムに基づいて並べ替えることができます。 **例**: `employees`テーブルから全ての従業員の情報を`last_name`のアルファベット順に取得する場合 ```sql SELECT * FROM employees ORDER BY last_name; ``` 降順に並べ替えたい場合は`DESC`を使用します。 **例**: `employees`テーブルから給与(`salary`)が高い順に従業員の情報を取得する場合 ```sql SELECT * FROM employees ORDER BY salary DESC; ``` ## 関数と集計 ### 文字列、数値、日付関数 SQLには、さまざまなデータ型を操作するための関数が用意されています。以下はそれぞれの関数の具体例を示すものです。 * **文字列関数** * **UPPER / LOWER**: 文字列を大文字や小文字に変換する。 ```sql SELECT UPPER(first_name), LOWER(last_name) FROM employees; ``` * **CONCAT**: 文字列を結合する。 ```sql SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; ``` * **数値関数** * **ROUND**: 数値を四捨五入する。 ```sql SELECT ROUND(salary) FROM employees; ``` * **ABS**: 絶対値を取得する。 ```sql SELECT ABS(-123.45); ``` * **日付関数** * **CURRENT\_DATE**: 現在の日付を取得する。 ```sql SELECT CURRENT_DATE; ``` * **DATE\_PART**: 日付から特定の部分（年、月、日など）を抽出する。 ```sql SELECT DATE_PART('year', birth_date) AS birth_year FROM employees; ``` ### 集約関数 (SUM, AVG, COUNT) 集約関数は、テーブルの複数の行から単一の値を取得するための関数です。 * **SUM**: 特定のカラムの合計値を取得する。 ```sql SELECT SUM(salary) AS total_salary FROM employees; ``` * **AVG**: 平均値を取得する。 ```sql SELECT AVG(salary) AS average_salary FROM employees; ``` * **COUNT**: レコード数をカウントする。 ```sql SELECT COUNT(*) AS number_of_employees FROM employees; ``` ### GROUP BYとHAVING句 `GROUP BY`句は、特定のカラムの値に基づいてデータをグループ化し、そのグループごとに集約関数を適用するために使用されます。 * **GROUP BY**: 特定のカラムを基にデータをグループ化する。 ```sql SELECT department_id, COUNT(*) AS number_of_employees FROM employees GROUP BY department_id; ``` `HAVING`句は、`GROUP BY`句で作成されたグループに対して条件を適用するために使用されます。 * **HAVING**: グループ化した後のデータに条件を適用する。 ```sql SELECT department_id, COUNT(*) AS number_of_employees FROM employees GROUP BY department_id HAVING COUNT(*) > 10; ``` このクエリは、10人以上の従業員がいる部門だけを表示します。 ## 結合（JOIN） 「結合（JOIN）」は、関連する複数のテーブルから関連する情報を一度に取得するための非常に強力なツールです。正しく使用すると、データベース内の関連する情報を効率的に取得できます。 ### JOINの種類 **INNER JOIN** `INNER JOIN`は、2つのテーブル間で共通のレコードのみを取得するための結合方法です。 **例**：`employees`テーブルと`departments`テーブルを結合し、従業員と所属部門の情報を取得する場合。 ```sql SELECT employees.first_name, employees.last_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.id; ``` **LEFT JOIN (または LEFT OUTER JOIN)** `LEFT JOIN`は、左テーブルの全てのレコードと、それと一致する右テーブルのレコードを取得します。一致しない場合、右テーブルのフィールドはNULLになります。 **例**：`employees`テーブルの全従業員と、もしあればその所属部門の情報を取得する場合。 ```sql SELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id; ``` **RIGHT JOIN (または RIGHT OUTER JOIN)** `RIGHT JOIN`は、`LEFT JOIN`の逆で、右テーブルの全てのレコードと、それと一致する左テーブルのレコードを取得します。 **FULL OUTER JOIN** 両方のテーブルの全てのレコードを取得し、一致するレコードがない場合はNULLを返します。 ### 結合の際のON条件 `ON`条件は、2つのテーブルをどのカラムを基にして結合するかを指定します。この条件は、通常、2つのテーブル間の関係性を示すキー（例: 主キーと外部キー）に基づいています。 **例**：`employees`テーブルの`department_id`と`departments`テーブルの`id`を基に結合する場合。 ```sql ... JOIN departments ON employees.department_id = departments.id; ``` ### 複数テーブルの結合 SQLでは、2つ以上のテーブルを一度に結合することができます。 **例**：`employees`, `departments`, および`locations`テーブルを結合し、従業員、所属部門、およびその場所の情報を取得する場合。 ```sql SELECT employees.first_name, departments.department_name, locations.location_name FROM employees INNER JOIN departments ON employees.department_id = departments.id INNER JOIN locations ON departments.location_id = locations.id; ``` ## サブクエリ サブクエリはSQLの高度な特性の1つであり、複雑なデータの要求や条件を処理するための強力なツールとなります。適切な使用法を理解し、効率的なクエリの書き方を習得することが重要です。 ### サブクエリの基本 サブクエリとは、SQLクエリの中に含まれる別のSQLクエリのことを指します。サブクエリは、主クエリの実行に必要な中間結果を生成するためや、比較のための値を取得するために使用されます。 **例**：`employees`テーブルから、平均給与よりも高い給与をもらっている従業員の名前を取得する場合。 ```sql SELECT first_name, last_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); ``` ### EXISTS、IN、ALL、ANYといった演算子との使用 #### **EXISTS** `EXISTS`はサブクエリが1行以上の結果を返す場合に真を返します。 **例**：`departments`テーブルに従業員が1人以上所属している部門を取得する場合。 ```sql SELECT department_name FROM departments WHERE EXISTS (SELECT 1 FROM employees WHERE employees.department_id = departments.id); ``` #### **IN** `IN`は、指定したカラムの値がサブクエリの結果のいずれかと一致する場合に真を返します。 **例**：`departments`テーブルの`id`が`10`または`20`の従業員の名前を取得する場合。 ```sql SELECT first_name, last_name FROM employees WHERE department_id IN (10, 20); ``` #### ALL `ALL`は、指定したカラムの値がサブクエリのすべての結果と比較され、すべての比較で真を返す場合に真を返します。 #### **ANY** `ANY`は、指定したカラムの値がサブクエリのいずれかの結果と比較され、いずれかの比較で真を返す場合に真を返します。 ### FROM句やSELECT句でのサブクエリ #### **FROM句でのサブクエリ** サブクエリは`FROM`句内でテーブルとしても使用できます。 **例**：`employees`テーブルから最も高い給与と同じ給与をもらっている従業員の名前を取得する場合。 ```sql SELECT first_name, last_name FROM ( SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC LIMIT 1 ) AS highest_paid_employee; ``` #### **SELECT句でのサブクエリ** サブクエリは、列の値として`SELECT`句内でも使用できます。 **例**：各従業員の給与と、全従業員の平均給与を取得する場合。 ```sql SELECT first_name, last_name, salary, (SELECT AVG(salary) FROM employees) AS average_salary FROM employees; ``` ## データの挿入、更新、削除 データの挿入、更新、削除は、データベースでの基本的な操作の一部です。これらの操作はしばしば一緒に使用されるため、正しく使用する方法を理解することが重要です。さらに、トランザクション管理を使用してデータの整合性を保つ能力も、データベースの効果的な運用のためには不可欠です。 ### INSERT文 `INSERT`文は、新しい行をテーブルに追加するために使用します。 **例**：`employees`テーブルに新しい従業員の情報を追加する場合。 ```sql INSERT INTO employees (first_name, last_name, salary) VALUES ('Taro', 'Yamada', 50000); ``` ### UPDATE文 `UPDATE`文は、既存の行のデータを変更するために使用します。 **例**：`employees`テーブルの特定の従業員の給与を更新する場合。 ```sql UPDATE employees SET salary = 55000 WHERE first_name = 'Taro' AND last_name = 'Yamada'; ``` ### DELETE文 `DELETE`文は、テーブルの行を削除するために使用します。 **注意**: WHERE句を省略すると、テーブルのすべての行が削除されるので注意が必要です。 **例**：`employees`テーブルから特定の従業員の情報を削除する場合。 ```sql DELETE FROM employees WHERE first_name = 'Taro' AND last_name = 'Yamada'; ``` ### トランザクション管理 トランザクションは、1つ以上のSQL文のグループを1つの単位として扱うことができます。これにより、すべてのSQL文が成功するか、すべてが失敗するかのどちらかになります。これは、データの整合性を保つための重要な特性です。 **例**：新しい部門を追加し、その後でその部門のマネージャーを追加する場合。 ```sql BEGIN; INSERT INTO departments (department_name) VALUES ('New Department'); INSERT INTO employees (first_name, last_name, role, department_id) VALUES ('Hanako', 'Tanaka', 'Manager', (SELECT id FROM departments WHERE department_name = 'New Department')); COMMIT; ``` トランザクション中に何か問題が発生した場合は、`ROLLBACK`を使用してすべての変更を取り消すことができます。 ## インデックスとパフォーマンス最適化 インデックスとクエリの最適化は、大量のデータを持つデータベースでのパフォーマンスを維持・向上させるための重要な手段です。適切なインデックスの設計とクエリの書き方は、データベースの応答時間を大幅に短縮することができます。 ### インデックスの原理と利点 **原理**: インデックスは、データベースのテーブルの特定のカラム上に作成されるデータ構造で、レコードの検索速度を向上させることができます。本の索引と同様に、特定のキー値に関連するデータの位置を高速に検索することができます。 **利点**: * データ検索の高速化 * テーブルの行を順番にスキャンするフルテーブルスキャンを減少させる * ソートや結合の操作を高速化することができる場合がある ### インデックスの作成と削除 **インデックスの作成**: `employees`テーブルの`last_name`カラムにインデックスを作成する例: ```sql CREATE INDEX idx_lastname ON employees (last_name); ``` **インデックスの削除**: 上記で作成したインデックスを削除する例: ```sql DROP INDEX idx_lastname ON employees; ``` ### クエリの最適化テクニック * **適切なインデックスの選択**: 頻繁にクエリで使用されるカラムやWHERE句、ORDER BY句で使用されるカラムにインデックスを作成する。 * **不要なカラムの省略**: SELECT句で必要なカラムのみを指定することで、データの取得量を減少させる。 ```sql -- 良くない例 SELECT * FROM employees; -- 良い例 SELECT first_name, last_name FROM employees; ``` * **JOINの最適化**: 必要なテーブルのみを結合し、結合条件にインデックスが存在するカラムを使用する。 * **サブクエリの使用を最小限に**: 可能な限りサブクエリよりもJOINを使用する。サブクエリは適切に使用しないとパフォーマンスの低下を招く場合がある。 * **データベースエンジンのクエリプランの確認**: 多くのデータベースシステムには、クエリプランを表示するツールが提供されている。これを使用して、クエリの実行方法を確認し、最適化のヒントを得る。 ## クエリを書く際の注意事項 1. **パフォーマンス**: * **インデックスの使用**: よくアクセスされるカラムや検索条件にはインデックスを適用することで、検索速度を高めることができます。 * **適切なJOINの利用**: 必要なテーブルだけをJOINするようにし、不要なデータの取得を避ける。 * **サブクエリの最適化**: サブクエリは過度に使用するとパフォーマンスが低下する可能性があるので注意が必要です。 2. **セキュリティ**: * **SQLインジェクション**: ユーザー入力をそのままクエリに組み込むのではなく、バインド変数やプリペアドステートメントを使用することで対策を取る。 * **最小権限の原則**: 必要最低限の権限だけをSQLユーザーに付与し、不要な権限は避ける。 3. **可読性**: * **整形**: クエリを整形し、インデントやスペースを使って可読性を向上させる。 * **コメントの活用**: 複雑なロジックや特定の処理の理由など、後から読む人が理解しやすいようにコメントを入れる。 4. **WHERE句などの条件指定の重要性**: * **データの絞り込み**: データベースからデータを取得する際、不要なデータを取得してしまうと、無駄にリソースを消費し、パフォーマンスが低下する可能性があります。WHERE句やJOINの条件を明確に指定することで、必要なデータだけを効率的に取得することができます。 * **計算コストの削減**: WHERE句やJOINの条件を先に書くことで、データベースの計算量やI/Oの量が削減され、クエリのパフォーマンスが向上することが期待されます。 * **誤操作の防止**: DELETEやUPDATEなどの操作を行う際、条件を先に明確にしておくことで、意図しないデータの変更や削除を防ぐことができます。 5. **データの整合性**: * **トランザクションの利用**: 複数の操作を一つのまとまった処理として実行する際にはトランザクションを使用し、失敗時にはロールバックできるようにする。 * **FOREIGN KEY制約の活用**: テーブル間の関連性を強化し、不整合なデータの入力を防ぐ。 6. **テスト**: * **テスト環境の利用**: 本番環境での直接のクエリ実行は避け、テスト環境での動作確認を行う。 * **バックアップ**: 重要な操作を行う前にはデータベースのバックアップを取得する。 ## 高度なSQL機能 ### **ビュー、ストアドプロシージャ、トリガー** **ビュー (View)**: ビューは、一つ以上のテーブルから取得されるデータの保存されていない仮想テーブルです。 **例**：`employees` テーブルから、名前と給与だけを表示するビューを作成する。 ```sql CREATE VIEW EmployeeNamesAndSalaries AS SELECT first_name, last_name, salary FROM employees; ``` **ストアドプロシージャ (Stored Procedure)**: 一連のSQL文を一つのグループとして保存し、そのグループを名前で呼び出すことができるものです。 **トリガー (Trigger)**: 特定のイベント（INSERT、UPDATE、DELETEなど）が発生した時に自動的に実行されるSQL文やストアドプロシージャ。 ### **CTE (Common Table Expressions) と Window関数** **CTE**: 一時的な結果セットを提供する、使い捨てのビューと考えることができます。WITH句は、CTEを定義する際に使用されるSQLのキーワードです。WITH句の後にCTEの名前と内容を記述し、その後の主クエリでこの一時的なテーブルを使用することができます。 **例**：`employees`テーブルから、給与が平均以上の従業員をリストする。 ```sql WITH SalaryAverage AS ( SELECT AVG(salary) as avg_salary FROM employees ) SELECT first_name, last_name, salary FROM employees WHERE salary > (SELECT avg_salary FROM SalaryAverage); ``` **Window関数**: データセット内の指定された「窓」を超えて計算を行います。ROW\_NUMBER(), RANK(), DENSE\_RANK(), LAG(), LEAD() などの関数があります。 **ROW\_NUMBER()** この関数は、結果セットの各行に一意の連番を付ける際に使用されます。 **例**: `employees` テーブルから、部門ごとに給与が高い順に従業員を並べ替え、それぞれの従業員にランクを付ける。 ```sql SELECT first_name, last_name, salary, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department FROM employees; ``` **RANK()** `RANK()`関数も各行にランクを付けるために使用されますが、同じ値を持つ行には同じランクが付けられ、次のランクは飛ばされます。 **例**: 同上のケースを使って`RANK()`を使用。 ```sql SELECT first_name, last_name, salary, department, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department FROM employees; ``` **LAG() と LEAD()** `LAG()`は、現在の行よりも前の行の値を取得するために使用されます。同様に、`LEAD()`は後の行の値を取得します。 **例**: `sales` テーブルから、各売上レコードと、その前の月の売上を取得。 ```sql SELECT month, sales_amount, LAG(sales_amount) OVER (ORDER BY month) AS prev_month_sales FROM sales; ```