SQL Server 教育資料

初級編：SQL Server の基礎

1. SQL Server とは？

SQL Serverは、Microsoftが開発・提供するリレーショナルデータベース管理システム（RDBMS）です。データの格納、管理、取得、分析を行うための強力なツールであり、エンタープライズレベルのアプリケーションから小規模なウェブサイトまで幅広く利用されています。

SQL Serverでは、T-SQL (Transact-SQL) と呼ばれるSQLの拡張言語を使用します。T-SQLは標準SQLに加えて、プログラマビリティ（変数、条件分岐、ループなど）やエラーハンドリングといった機能が強化されています。

2. データベースとテーブルの概念

データベース: 関連するデータの集まり。通常、アプリケーションごとに一つのデータベースを作成します。
テーブル: データベース内でデータを格納する基本的な構造。行（レコード）と列（フィールド）から構成されます。
行（レコード）: テーブル内の単一のデータエントリ。
列（フィールド）: テーブル内の特定の種類のデータ。データ型（例: `INT`, `VARCHAR`, `DATE`）を持ちます。

例: Customers テーブル

CustomerID (INT)	CustomerName (NVARCHAR(255))	ContactName (NVARCHAR(255))	City (NVARCHAR(100))
1	Alfreds Futterkiste	Maria Anders	Berlin
2	Ana Trujillo Emparedados y helados	Ana Trujillo	México D.F.

3. 基本的なT-SQLコマンド

SELECT文：データの検索

テーブルからデータを取得するために使用します。


-- テーブルのすべての列とすべての行を取得
SELECT * FROM Customers;

-- 特定の列のみを取得
SELECT CustomerName, City FROM Customers;

-- 条件を指定して行を絞り込む (WHERE句)
SELECT * FROM Customers WHERE City = N'Berlin'; -- N'...' はUnicode文字列を示す

-- 複数の条件を指定 (AND, OR)
SELECT * FROM Customers WHERE City = N'Berlin' AND CustomerID = 1;
SELECT * FROM Customers WHERE City = N'Berlin' OR City = N'London';

-- 結果のソート (ORDER BY)
SELECT * FROM Customers ORDER BY CustomerName ASC; -- 昇順
SELECT CustomerName, City FROM Customers ORDER BY City DESC, CustomerName ASC; -- 複数列ソート

-- 特定の件数だけ取得 (TOP句)
SELECT TOP 5 * FROM Customers; -- 先頭5件
SELECT TOP 10 PERCENT * FROM Customers; -- 上位10%

INSERT INTO文：データの挿入

テーブルに新しい行を追加します。


-- すべての列にデータを挿入 (列の順序に注意)
INSERT INTO Customers (CustomerID, CustomerName, ContactName, City)
VALUES (3, N'New Customer Inc.', N'John Doe', N'New York');

-- 特定の列にデータを挿入 (残りはNULLまたはデフォルト値)
INSERT INTO Customers (CustomerName, City)
VALUES (N'Another Customer', N'Paris');

UPDATE文：データの更新

既存のテーブルのデータを変更します。


-- 特定の条件に合致する行のデータを更新
UPDATE Customers
SET ContactName = N'Jane Smith', City = N'London'
WHERE CustomerID = 3;

DELETE FROM文：データの削除

テーブルから行を削除します。


-- 特定の条件に合致する行を削除
DELETE FROM Customers WHERE CustomerID = 3;

-- テーブルのすべての行を削除 (注意！元に戻せません)
-- DELETE FROM Customers;

CREATE TABLE文：テーブルの作成

新しいテーブルを作成します。


CREATE TABLE Products (
    ProductID INT PRIMARY KEY, -- 主キー (NULL不可、ユニーク)
    ProductName NVARCHAR(255) NOT NULL, -- NULL不可
    Price DECIMAL(10, 2), -- 桁数10、小数点以下2桁
    StockQuantity INT DEFAULT 0 -- デフォルト値
);

DROP TABLE文：テーブルの削除

既存のテーブルを削除します。（データも構造も完全に削除されます）


DROP TABLE Products;

注意: DELETE FROM は行を削除しますがテーブルは残ります。DROP TABLE はテーブル自体を削除します。

ALTER TABLE文：テーブル構造の変更

既存のテーブルに列を追加したり、変更したりします。


-- 列の追加
ALTER TABLE Customers
ADD Email NVARCHAR(255);

-- 列のデータ型変更 (データがある場合は注意が必要)
ALTER TABLE Customers
ALTER COLUMN Email VARCHAR(100) NOT NULL;

-- 列の削除
ALTER TABLE Customers
DROP COLUMN Email;

応用編：SQL Server でのより複雑なデータ操作

1. データのフィルタリングと集計

WHERE句の高度な使い方

LIKE: 部分一致検索 (`%` は任意の文字列、`_` は任意の一文字)
IN: リスト内のいずれかの値に一致
BETWEEN: 範囲内の値に一致
IS NULL / IS NOT NULL: NULL値のチェック


-- 名前に 'Al' が含まれる顧客
SELECT * FROM Customers WHERE CustomerName LIKE N'%Al%';

-- 都市が 'London' または 'Paris' の顧客
SELECT * FROM Customers WHERE City IN (N'London', N'Paris');

-- 注文日が2024年1月1日から2024年3月31日までの注文
SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-03-31';

-- ContactNameがNULLの顧客
SELECT * FROM Customers WHERE ContactName IS NULL;

集計関数とGROUP BY句

COUNT(), SUM(), AVG(), MIN(), MAX() を使った集計。


-- 全顧客数を取得
SELECT COUNT(*) FROM Customers;

-- 各都市ごとの顧客数をカウント (GROUP BY)
SELECT City, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY City;

-- 各都市で顧客数が2人以上のものを抽出 (HAVING)
SELECT City, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY City
HAVING COUNT(CustomerID) >= 2;

WHERE と HAVING の違い: WHERE はグループ化の前に個々の行をフィルタリングし、HAVING はグループ化された結果をフィルタリングします。

2. JOIN句：複数のテーブルの結合

SQL Serverで複数のテーブルを結合してデータを取得します。

例: Orders テーブルと Customers テーブルを結合

想定テーブル構造:


-- Customersテーブル: CustomerID, CustomerName, ...
-- Ordersテーブル: OrderID, CustomerID, OrderDate, Amount ...


-- 顧客名とそれに対応する注文情報を取得 (INNER JOIN)
-- 両方のテーブルに一致する行のみを返します。
SELECT
    C.CustomerName,
    O.OrderID,
    O.OrderDate,
    O.Amount
FROM
    Customers AS C -- ASは省略可能だが、エイリアスは可読性を高める
INNER JOIN
    Orders AS O ON C.CustomerID = O.CustomerID;

-- すべての顧客と、もしあればその注文情報を取得 (LEFT JOIN)
-- 左側のテーブル (Customers) のすべての行と、右側のテーブル (Orders) の対応する行を返します。
-- 右側に一致する行がない場合はNULLを返します。
SELECT
    C.CustomerName,
    O.OrderID,
    O.OrderDate
FROM
    Customers AS C
LEFT JOIN
    Orders AS O ON C.CustomerID = O.CustomerID;

-- すべての注文と、もしあればその顧客情報を取得 (RIGHT JOIN)
-- 右側のテーブル (Orders) のすべての行と、左側のテーブル (Customers) の対応する行を返します。
-- 左側に一致する行がない場合はNULLを返します。
SELECT
    C.CustomerName,
    O.OrderID,
    O.OrderDate
FROM
    Customers AS C
RIGHT JOIN
    Orders AS O ON C.CustomerID = O.CustomerID;

-- 両方のテーブルのすべての行を結合 (FULL OUTER JOIN)
-- どちらかのテーブルに一致する行があるすべての行を返します。
-- 一致しない場合はNULLを返します。
SELECT
    C.CustomerName,
    O.OrderID,
    O.OrderDate
FROM
    Customers AS C
FULL OUTER JOIN
    Orders AS O ON C.CustomerID = O.CustomerID;

3. サブクエリ（副問い合わせ）

別のSQLクエリの中にネストされたクエリです。メインクエリの結果を絞り込んだり、計算したりするために使用されます。


-- 平均価格より高い価格の製品を取得
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

-- 注文がある顧客の名前を取得 (EXISTS句を使用)
SELECT CustomerName
FROM Customers AS C
WHERE EXISTS (SELECT 1 FROM Orders AS O WHERE O.CustomerID = C.CustomerID);

-- (IN句でも可だが、大量データではEXISTSの方がパフォーマンスが良い場合がある)
-- SELECT CustomerName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders);

上級編：SQL Server の高度な機能とT-SQL

1. `UPDATE FROM` 構文（複数テーブルからの更新）

SQL Serverでは、UPDATE 文に FROM 句を追加することで、他のテーブル（またはサブクエリ）と結合しながらデータを更新できます。これは、関連するテーブルの情報に基づいて、あるテーブルの特定の列を更新したい場合に非常に強力です。


-- 構文例
UPDATE TargetTable
SET TargetTable.ColumnToUpdate = SourceTable.ValueToUse
FROM TargetTable
[INNER JOIN|LEFT JOIN|RIGHT JOIN] SourceTable ON TargetTable.JoinColumn = SourceTable.JoinColumn
WHERE Conditions;

例1: 顧客の最終注文日を `Customers` テーブルに反映する

Customers テーブルに LastOrderDate 列を追加し、Orders テーブルから各顧客の最新の注文日を取得して更新します。


-- CustomersテーブルにLastOrderDate列を追加 (一度だけ実行)
-- ALTER TABLE Customers ADD LastOrderDate DATE;

UPDATE C
SET C.LastOrderDate = O.MaxOrderDate
FROM Customers AS C
INNER JOIN (
    SELECT CustomerID, MAX(OrderDate) AS MaxOrderDate
    FROM Orders
    GROUP BY CustomerID
) AS O ON C.CustomerID = O.CustomerID;

ポイント:

更新対象のテーブルにエイリアス（例: C）を付けて、SET 句でそのエイリアスを使用することで、どのテーブルの列を更新するのかを明確にできます。
INNER JOIN を使用することで、注文がある顧客のみが更新されます。注文がない顧客の LastOrderDate は変更されません。
サブクエリで集計した結果を結合することで、複雑な条件での更新が可能です。

例2: 特定のカテゴリの商品価格を、そのカテゴリの推奨価格に更新する

Products テーブル（ProductID, ProductName, Price, CategoryID）と Categories テーブル（CategoryID, RecommendedPrice）があり、Products の Price を Categories の RecommendedPrice に基づいて更新したい場合。


-- CategoriesテーブルにRecommendedPrice列を追加 (仮)
-- ALTER TABLE Categories ADD RecommendedPrice DECIMAL(10,2);
-- UPDATE Categories SET RecommendedPrice = 120.00 WHERE CategoryID = 1;

UPDATE P
SET P.Price = C.RecommendedPrice
FROM Products AS P
INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID
WHERE P.Price < C.RecommendedPrice; -- 現在の価格が推奨価格より低い場合にのみ更新

2. CTE (Common Table Expressions) / WITH句

一時的な名前付きの結果セットを定義し、より読みやすく、管理しやすい複雑なクエリを作成するために使用されます。特に、複数回参照されるサブクエリや再帰クエリに有用です。


-- 平均価格より高い製品をCTEを使って取得
WITH AverageProductPrice AS (
    SELECT AVG(Price) AS AvgPrice
    FROM Products
)
SELECT
    P.ProductName,
    P.Price
FROM
    Products AS P
INNER JOIN
    AverageProductPrice AS APP ON P.Price > APP.AvgPrice; -- CTEはテーブルのように結合可能

-- 複数のCTEを連結して、注文が多い顧客名を取得
WITH CustomerOrderCounts AS (
    SELECT CustomerID, COUNT(OrderID) AS TotalOrders
    FROM Orders
    GROUP BY CustomerID
),
HighVolumeCustomers AS (
    SELECT CustomerID
    FROM CustomerOrderCounts
    WHERE TotalOrders >= 5 -- 5件以上の注文がある顧客
)
SELECT
    C.CustomerName,
    HVC.TotalOrders -- CTEから取得した列も選択可能
FROM
    Customers AS C
INNER JOIN
    HighVolumeCustomers AS HVC ON C.CustomerID = HVC.CustomerID;

3. ウィンドウ関数

SQL Serverで特に強力な機能の一つです。行のグループに対して計算を実行しますが、GROUP BYのように行をまとめたりはしません。各行に対して独立した結果を返します。ランキング、移動平均、累積合計などに使用されます。

構文: 関数名() OVER ([PARTITION BY 列] [ORDER BY 列 [ASC|DESC]])

PARTITION BY: データセットをパーティション（グループ）に分割します。
ORDER BY: 各パーティション内で結果をソートします。

例: ランキング関数 (`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`)


-- ProductsテーブルにCategoryIdを追加して例示
-- CREATE TABLE Products (ProductID INT, ProductName NVARCHAR(255), Price DECIMAL(10,2), CategoryId INT);
-- INSERT INTO Products VALUES (1, N'ProductA', 100, 1), (2, N'ProductB', 150, 1), (3, N'ProductC', 80, 2), (4, N'ProductD', 120, 2), (5, N'ProductE', 150, 1);

SELECT
    ProductID,
    ProductName,
    Price,
    CategoryId,
    ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS RowNumByPrice,
    RANK() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS RankByPrice,
    DENSE_RANK() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS DenseRankByPrice
FROM
    Products;

各関数の違い:

ROW_NUMBER(): 行にユニークな連番を割り振ります。
RANK(): 同じ値の場合は同じランクを割り振り、次のランクはスキップされます。
DENSE_RANK(): 同じ値の場合は同じランクを割り振り、次のランクはスキップされません。

例: 累積合計 (`SUM() OVER`)


-- OrdersテーブルにOrderDateとAmountを追加して例示
-- CREATE TABLE Orders (OrderID INT, CustomerID INT, OrderDate DATE, Amount DECIMAL(10,2));
-- INSERT INTO Orders VALUES (1, 1, '2025-01-01', 100), (2, 1, '2025-01-05', 150), (3, 2, '2025-01-02', 200), (4, 1, '2025-01-10', 50);

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS CumulativeAmountByCustomer
FROM
    Orders;

4. ストアドプロシージャと関数 (T-SQL プログラミング)

ストアドプロシージャ: データベースに保存される一連のT-SQL文です。複雑なビジネスロジックをカプセル化し、再利用性を高め、パフォーマンスを向上させることができます。引数を受け取り、結果を返すこともできます。
関数: ストアドプロシージャに似ていますが、通常は単一の値を返すか、テーブルを返すことに特化しています。SQLクエリ内で直接呼び出すことができます。

例: ストアドプロシージャの作成と実行


-- ストアドプロシージャの作成
CREATE PROCEDURE GetCustomerOrders
    @CustomerID INT -- 入力パラメータ
AS
BEGIN
    SET NOCOUNT ON; -- 影響を受けた行数のメッセージを非表示にする

    SELECT *
    FROM Orders
    WHERE CustomerID = @CustomerID
    ORDER BY OrderDate DESC;
END;
GO -- バッチの区切り (SQL Server Management Studioなどで使用)

-- ストアドプロシージャの実行
EXEC GetCustomerOrders @CustomerID = 1;
EXEC GetCustomerOrders 2; -- パラメータ名省略も可能 (順序通り)

-- ストアドプロシージャの変更
ALTER PROCEDURE GetCustomerOrders
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, OrderDate, Amount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;
GO

-- ストアドプロシージャの削除
DROP PROCEDURE GetCustomerOrders;
GO

例: スカラー値関数 (ユーザー定義関数) の作成と実行


-- スカラー値関数の作成
CREATE FUNCTION GetCustomerNameById (@CustId INT)
RETURNS NVARCHAR(255)
AS
BEGIN
    DECLARE @Name NVARCHAR(255);
    SELECT @Name = CustomerName FROM Customers WHERE CustomerID = @CustId;
    RETURN @Name;
END;
GO

-- 関数の実行 (SELECT文内で呼び出し)
SELECT dbo.GetCustomerNameById(1);
SELECT CustomerID, dbo.GetCustomerNameById(CustomerID) AS CustomerName FROM Orders;
GO

-- 関数の削除
DROP FUNCTION GetCustomerNameById;
GO

注意:

SQL Serverのユーザー定義関数は、パフォーマンス上の考慮事項があるため、大量のデータ処理には注意が必要です。可能な限りインラインで展開できるCTEやビュー、あるいはストアドプロシージャの利用を検討してください。
オブジェクト名にスキーマ（例: `dbo.GetCustomerNameById` の `dbo`）を付けてアクセスするのが一般的です。

5. トランザクション制御

データベース操作の整合性と信頼性を確保するためのメカニズムです。一連のT-SQL文を単一の論理的な作業単位として扱います。

BEGIN TRANSACTION: トランザクションを開始します。
COMMIT TRANSACTION: トランザクション内のすべての変更をデータベースに永続的に保存します。
ROLLBACK TRANSACTION: トランザクション内のすべての変更を取り消し、トランザクション開始前の状態に戻します。

ACID特性:

Atomicity (原子性): トランザクション内のすべての操作は完全に実行されるか、全く実行されないかのどちらかです。
Consistency (一貫性): トランザクションの開始時と終了時に、データベースは常に整合性のとれた状態に保たれます。
Isolation (独立性): 複数のトランザクションが同時に実行されても、それぞれが互いに影響を与えず、あたかも単独で実行されているかのように見えます。
Durability (永続性): コミットされたトランザクションの変更は、システム障害が発生しても失われません。


-- 例: 口座間送金
BEGIN TRANSACTION;
BEGIN TRY
    -- 口座123から100引く
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 123;

    -- エラーを発生させるための例 (コメントアウトを解除するとロールバックされる)
    -- RAISERROR('テストエラー', 16, 1);

    -- 口座456に100足す
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456;

    -- ここまですべて成功したらコミット
    COMMIT TRANSACTION;
    PRINT '送金が正常に完了しました。';
END TRY
BEGIN CATCH
    -- エラーが発生したらロールバック
    ROLLBACK TRANSACTION;
    PRINT '送金が失敗しました。変更は取り消されました。';
    -- エラー情報の取得 (オプション)
    -- SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

6. インデックスとパフォーマンス最適化

インデックス: テーブルの特定の列に作成されるデータ構造で、データの検索速度を向上させます。SQL Serverでは、クラスター化インデックス（テーブルの物理的なソート順を決定）と非クラスター化インデックス（ソートされたキー値のリストと、データ行へのポインタを含む）があります。
主キー: 通常、テーブルの主キーには自動的にクラスター化インデックスが作成されます。
注意点: インデックスは検索を高速化しますが、データの挿入、更新、削除のパフォーマンスを低下させる可能性があります。また、ディスク領域を消費します。適切なインデックス設計が重要です。

例: インデックスの作成


-- 非クラスター化インデックスの作成
CREATE NONCLUSTERED INDEX IX_Customers_City ON Customers (City);

-- 複数列のインデックス
CREATE NONCLUSTERED INDEX IX_Products_Category_Price ON Products (CategoryID, Price DESC);

-- ユニークインデックス (重複値を許さない)
CREATE UNIQUE NONCLUSTERED INDEX UQ_Products_ProductName ON Products (ProductName);

実行計画 (Execution Plan): SQL Server Management Studio (SSMS) などで、SQLクエリがデータベースによってどのように実行されるかを示す計画です。これを分析することで、パフォーマンスのボトルネックを特定し、クエリやインデックスを最適化する手がかりを得られます。
統計情報: SQL Serverはインデックスや列のデータ分布に関する統計情報を保持しており、クエリオプティマイザが最適な実行計画を立てるために使用します。定期的な統計情報の更新はパフォーマンス維持に重要です。