🧾

SQL 基礎

2024/08/11に公開

SQL

tech

はじめ

SQLとは何か？

SQL (Structured Query Language) は、リレーショナルデータベース管理システム (RDBMS) においてデータを操作するための標準言語。
SQLを使用することで、データベースに対してデータの検索、挿入、更新、削除、管理などが行える。

基本構文

SELECT 文: データを取得する

SELECT 文は、データベースから必要なデータを抽出するために使用される。基本的な構文は、取得したい列を指定し、対象となるテーブルを選択する。また、WHERE 句を使用して条件を指定したり、ORDER BY 句で結果を並び替えたり、GROUP BY 句でデータをグループ化することができる。

-- 従業員テーブルから first_name と last_name を取得
SELECT first_name, last_name FROM employees;

-- Sales 部門に所属する従業員の first_name と last_name を取得
SELECT first_name, last_name FROM employees WHERE department = 'Sales';

-- 従業員の last_name を昇順で並べ替えて取得
SELECT first_name, last_name FROM employees ORDER BY last_name ASC;

-- 部門ごとの従業員数をカウント
SELECT department, COUNT(*) FROM employees GROUP BY department;

INSERT 文: データを挿入する

INSERT 文は、新しいデータをテーブルに追加するために使用される。挿入する列を指定し、それに対応する値を指定することで、データをテーブルに挿入する。

-- 新しい従業員のレコードを挿入
INSERT INTO employees (first_name, last_name, department) VALUES ('John', 'Doe', 'Engineering');

UPDATE 文: データを更新する

UPDATE 文は、既存のデータを更新するために使用される。特定の条件を指定して、その条件に一致するデータを更新する。

-- 指定された employee_id の従業員の部門を HR に更新
UPDATE employees SET department = 'HR' WHERE employee_id = 123;

DELETE 文: データを削除する

DELETE 文は、特定の条件に一致するデータをテーブルから削除するために使用される。WHERE 句を使用して、削除する対象を指定する。

-- 指定された employee_id の従業員のレコードを削除
DELETE FROM employees WHERE employee_id = 123;

条件付きクエリ

WHERE 句: 特定の条件でデータをフィルタリング

WHERE 句は、特定の条件に基づいてデータをフィルタリングするために使用される。例えば、=、<>、> などの比較演算子を用いて条件を指定する。
また、複数の条件を組み合わせる場合は、AND、OR、NOT といった論理演算子を使用する。

-- 注文日が 2024-08-11 の注文を取得
SELECT * FROM orders WHERE order_date = '2024-08-11';

-- 数量が 10 を超え、ステータスが 'Shipped' の注文を取得
SELECT * FROM orders WHERE quantity > 10 AND status = 'Shipped';

LIKE: パターンマッチングを使用して検索

LIKE 演算子は、指定したパターンに一致するデータを検索するために使用される。
% は任意の文字列、_ は任意の一文字を表すワイルドカードとして利用できる。

-- 姓が 'S' で始まる顧客を検索
SELECT * FROM customers WHERE last_name LIKE 'S%';

-- 商品コードが 'A_12' で始まる商品を検索
SELECT * FROM products WHERE product_code LIKE 'A_12%';

結合 (JOIN)

INNER JOIN: 共通のキーでテーブルを結合

INNER JOIN は、2つのテーブル間で共通のキーに基づいてデータを結合する。
結合の結果には、両方のテーブルで一致する行のみが含む。

-- 注文と対応する顧客情報を結合して取得
SELECT orders.order_id, customers.first_name, customers.last_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN: 左のテーブルのすべての行を取得し、右のテーブルから一致する行を取得

LEFT JOIN は、左側のテーブルのすべての行と、右側のテーブルから一致する行を取得する。
右側のテーブルに一致する行がない場合、NULL が返される。

-- 従業員と対応する部門情報を結合して取得（部門がない従業員も含む）
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;

RIGHT JOIN: 右のテーブルのすべての行を取得し、左のテーブルから一致する行を取得

RIGHT JOIN は、LEFT JOIN の逆で、右側のテーブルのすべての行と、左側のテーブルから一致する行を取得する。

-- プロジェクトとそのマネージャー情報を結合して取得（マネージャーがいないプロジェクトも含む）
SELECT projects.project_name, employees.first_name, employees.last_name FROM projects RIGHT JOIN employees ON projects.manager_id = employees.employee_id;

FULL OUTER JOIN: 両方のテーブルからすべての行を取得

FULL OUTER JOIN は、両方のテーブルから一致する行、および一致しない行をすべて取得する。一致しない場合は、対応する列に NULL が表示される。

-- 両方のテーブルから一致するデータと一致しないデータの両方を取得
SELECT a.id, a.value, b.value FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.id;

集約関数

SQLには、集計結果を取得するための集約関数が用意されている。
これらの関数を使用することで、データの要約情報を簡単に取得できる。

COUNT(): 行数をカウントする。特定の条件に一致する行数を求める際に使用する。
SUM(): 数値列の合計を計算する。例えば、売上合計を求める際に使用する。
AVG(): 数値列の平均値を計算する。例えば、平均点を求める際に使用する。
MIN(): 指定した列の最小値を取得する。
MAX(): 指定した列の最大値を取得する。

-- Sales 部門の従業員数をカウント
SELECT COUNT(*) FROM employees WHERE department = 'Sales';

-- Engineering 部門の総給与額を計算
SELECT SUM(salary) FROM employees WHERE department = 'Engineering';

-- HR 部門の平均給与を計算
SELECT AVG(salary) FROM employees WHERE department = 'HR';

-- 全従業員の最小給与を取得
SELECT MIN(salary) FROM employees;

-- 全従業員の最大給与を取得
SELECT MAX(salary) FROM employees;

サブクエリ

サブクエリは、クエリの中に埋め込まれた別のクエリ。サブクエリを使用することで、より複雑なデータ抽出が可能になる。
例えば、特定の条件を満たすデータを取得するためにサブクエリを使用したり、サブクエリをネストして使用することもできる。

データベース管理

-- サブクエリを使用して Engineering 部門に所属する従業員を取得
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = (  SELECT department_id  FROM departments  WHERE department_name = 'Engineering' );

CREATE DATABASE: 新しいデータベースを作成

CREATE DATABASE 文は、新しいデータベースを作成するために使用される。
データベースの名前を指定して作成する。

-- 新しいデータベースを作成
CREATE DATABASE company_db;

CREATE TABLE: 新しいテーブルを作成

CREATE TABLE 文は、新しいテーブルを作成するために使用される。
テーブルの列名、データ型、制約を指定して作成する。

-- 新しいテーブルを作成
CREATE TABLE employees (  employee_id INT PRIMARY KEY,  first_name VARCHAR(50),  last_name VARCHAR(50),  department_id INT,  salary DECIMAL(10, 2) );

ALTER TABLE: 既存のテーブルを変更

ALTER TABLE 文は、既存のテーブルの構造を変更するために使用される。
列の追加、削除、データ型の変更などを行うことができる。

-- hire_date カラムを既存のテーブルに追加 ALTER TABLE employees ADD hire_date DATE;

DROP TABLE: テーブルを削除

DROP TABLE 文は、テーブルを削除するために使用される。
テーブルとそのデータが完全に削除されるため、慎重に使用する必要がある。

-- old_employees テーブルを削除
DROP TABLE old_employees;

TRUNCATE TABLE: テーブルのデータを削除

TRUNCATE TABLE 文は、テーブル内のすべてのデータを削除するが、テーブルの構造は保持される。
DELETE 文に比べて高速に実行される。

-- temp_data テーブルのすべてのデータを削除 TRUNCATE TABLE temp_data;

インデックスとビュー

インデックスとビューは、データベースのパフォーマンスを向上させたり、データの管理を容易にするための機能。

インデックス: テーブル内の特定の列にインデックスを作成することで、検索速度を向上させます。ただし、インデックスを多用しすぎると、挿入や更新時のパフォーマンスが低下する可能性がある。
ビュー: ビューは、複雑なクエリ結果を仮想的なテーブルとして保存するもの。ビューを使用することで、繰り返し使用するクエリを簡単に実行できる。

インデックスの作成

-- last_name カラムにインデックスを作成して検索を高速化
CREATE INDEX idx_last_name ON employees(last_name);

ビューの作成

-- Sales 部門の従業員数と総給与を集計するビューを作成
CREATE VIEW sales_summary AS SELECT department, COUNT(*) AS num_employees, SUM(salary) AS total_salary FROM employees WHERE department = 'Sales' GROUP BY department;

トランザクション管理

トランザクションは、データベースに対する一連の操作をまとめて扱うためのもの。
トランザクションを使用することで、データの整合性を確保できる。

BEGIN TRANSACTION: トランザクションの開始

BEGIN TRANSACTION 文は、トランザクションの開始を宣言する。

COMMIT: 変更を確定

COMMIT 文は、トランザクション中のすべての変更を確定し、データベースに反映させます。

ROLLBACK: 変更を取り消す

ROLLBACK 文は、トランザクション中のすべての変更を取り消し、データベースの状態をトランザクション開始前に戻る。

-- トランザクションを使用して複数の操作を一括実行
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;

COMMIT;

ROLLBACK の使用例

-- エラーが発生した場合、トランザクションをロールバックして変更を取り消す
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;

ROLLBACK;

エラーハンドリング

データベース操作中に発生するエラーを適切に処理することは重要。
SQLでは、エラーコードや例外処理を使用してエラーに対処する。

例外処理の基本: SQLにおけるエラーハンドリングの基礎を理解する。
エラーコードの確認方法: エラーが発生した際に、エラーコードを確認して原因を特定する方法を解説する。

-- トランザクション内でエラーが発生した場合、エラーハンドリングを行いロールバック
BEGIN TRY  BEGIN TRANSACTION;   UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;  UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;   COMMIT; END TRY BEGIN CATCH  ROLLBACK;  PRINT ERROR_MESSAGE(); END CATCH;