データ操作基本

データ挿入

-- 顧客情報登録
INSERT INTO CustomerInfo(CustomerID, LoginName, FullName, ContactEmail, Phone)
VALUES(5001, 'tanaka_t', '田中太郎', 't.tanaka@example.co.jp', '080-XXXX-XXXX')

-- 商品マスタ登録
INSERT INTO ProductMaster(ProductCode, ItemName, UnitPrice, StockQty)
VALUES('PRD_300', 'デジタルカメラ', 79800.0, 150)

-- 注文登録（動的ID生成）
DECLARE @NewOrderID VARCHAR(20)
SET @NewOrderID = FORMAT(GETDATE(), 'yyyyMMddHHmmssfff')
INSERT INTO OrderData(OrderID, CustomerID, ProductCode, Quantity, OrderDate)
VALUES(@NewOrderID, 5001, 'PRD_300', 2, GETDATE())

データ更新

UPDATE CustomerInfo 
SET FullName = '鈴木一郎' 
WHERE CustomerID = 5002

データ削除

DELETE FROM CustomerInfo 
WHERE CustomerID = 5003

データ検索

SELECT * FROM ProductMaster
SELECT * FROM OrderData WHERE OrderID > '20230101000000000'

テーブル結合操作

内部結合

SELECT * 
FROM OrderData AS O
INNER JOIN CustomerInfo AS C ON O.CustomerID = C.CustomerID
WHERE C.CustomerID = 5001

左外部結合

SELECT *
FROM CustomerInfo AS C
LEFT JOIN OrderData AS O ON C.CustomerID = O.CustomerID

完全外部結合

SELECT *
FROM OrderData AS O
FULL JOIN ProductMaster AS P ON O.ProductCode = P.ProductCode

データ集計と整列

-- 顧客別注文件数
SELECT CustomerID, COUNT(OrderID) AS TotalOrders
FROM OrderData
GROUP BY CustomerID

-- 注文件数3件以上の顧客
SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM OrderData
GROUP BY CustomerID
HAVING COUNT(OrderID) >= 3

-- 注文件数降順ソート
SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM OrderData
GROUP BY CustomerID
ORDER BY OrderCount DESC

パターン検索

-- 特定ドメインのメール検索
SELECT * FROM CustomerInfo
WHERE ContactEmail LIKE '%@example.co.jp'

-- 文字列パターン検索
SELECT * FROM CustomerInfo
WHERE ContactEmail LIKE 'suzuki%'

-- 否定条件検索
SELECT * FROM CustomerInfo
WHERE ContactEmail NOT LIKE '%@gmail.com'

ビュー定義

-- 既存ビュー削除
IF OBJECT_ID('V_OrderSummary', 'V') IS NOT NULL
    DROP VIEW V_OrderSummary

-- 注文概要ビュー作成
CREATE VIEW V_OrderSummary AS
SELECT 
    O.OrderID, 
    O.CustomerID,
    O.ProductCode,
    O.OrderDate,
    C.FullName,
    C.ContactEmail,
    P.ItemName,
    P.UnitPrice
FROM OrderData AS O
JOIN CustomerInfo AS C ON O.CustomerID = C.CustomerID
JOIN ProductMaster AS P ON O.ProductCode = P.ProductCode

ストアドプロシージャとトランザクション

CREATE PROCEDURE DeleteProduct
    @ProductCode NVARCHAR(20),
    @Result INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRANSACTION
    BEGIN TRY
        DELETE FROM OrderData WHERE ProductCode = @ProductCode
        DELETE FROM ProductMaster WHERE ProductCode = @ProductCode
        SET @Result = 1
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        SET @Result = -1
        ROLLBACK TRANSACTION
    END CATCH
END

カーソル操作

CREATE PROCEDURE GetProductList
    @ProductNames NVARCHAR(MAX) OUTPUT
AS
BEGIN
    DECLARE @CurrentName NVARCHAR(100)
    DECLARE product_cursor CURSOR FOR
        SELECT ItemName FROM ProductMaster
    
    OPEN product_cursor
    FETCH NEXT FROM product_cursor INTO @CurrentName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ProductNames = CONCAT_WS('|', @ProductNames, @CurrentName)
        FETCH NEXT FROM product_cursor INTO @CurrentName
    END
    
    CLOSE product_cursor
    DEALLOCATE product_cursor
END

トリガー実装

削除時トリガー

CREATE TRIGGER tr_CustomerDelete
ON CustomerInfo
INSTEAD OF DELETE
AS
BEGIN
    DELETE FROM OrderData 
    WHERE CustomerID IN (SELECT CustomerID FROM DELETED)
    
    DELETE FROM CustomerInfo 
    WHERE CustomerID IN (SELECT CustomerID FROM DELETED)
END

登録時トリガー

CREATE TRIGGER tr_OrderInsert
ON OrderData
AFTER INSERT
AS
BEGIN
    UPDATE P
    SET P.StockQty = P.StockQty - I.Quantity
    FROM ProductMaster P
    INNER JOIN INSERTED I ON P.ProductCode = I.ProductCode
END