熊倉 克己
2002 年 4 月 8 日
6. SQL（Transact-SQL）
SQL は、リレーショナルデータベースシステムに対してデータの検索や更新・定義などを行うための言語です。SQL は ANSI や JIS によって標準化されており、ほとんどの RDMS では SQL-92 と呼ばれる規格に準拠した SQL を使用します。
SQL Server では、Transact-SQL と呼ばれる SQL を使用します。Transact-SQL は、ANSI SQL-92 に準拠し、なおかつ拡張機能を追加した SQL です。
Transact-SQL は操作の種類によって次の ３ 種類に分類できます。
・ データ操作言語(DML：Data Manipulate Language)
（データベースへの問合せやデータの追加・更新・削除）
・ データ定義言語(DDL：Data Definition Language)
（表などのオブジェクトの作成・定義変更・削除）
・ データ制御言語(DCL：Data Control Language)
（データの変更要求やトランザクションの管理）
6.1. 単純問合せ
データベースのテーブルから必要な情報を取得するには、SELECT ステートメントを使用します。SELECT ステートメントが実行されると、該当データを検索して結果セットに格納し、ユーザー側へ戻します。
-
SELECT ステートメントの構文
SELECT 選択リスト ...結果セットに返される列や計算式
FROM テーブル名 ...結果セットに返される値が格納されているテーブル名
WHERE 検索条件 ...結果セットに返される行に対する検索条件 -
全ての列の取得
SELECT ＊ FROM Employees
SELECT 句のあとに、列名を指定するかわりに＊ (アスタリスク) を指定すると、テーブル内の全ての列を指定したのと同じ意味になります。
-
特定の列の取得
SELECT EmployeID, LastName FROM Employees
SELECT句のあとの選択リストに列名をカンマで区切って指定すると、テーブル内の指定された列が結果セットに返されます。このとき、結果セット内の列の順序は選択リストでの指定順となり、元のテーブル内での列の順序とは異なります。
-
算術演算結果の取得
SELECT EmployeID, LastName, Salary ＊ 12 FROM Employees
選択リストには列名だけでなく算術演算子を使用した式を指定することができます。このとき、結果セットには計算結果が返されます。
SQL Server で使用可能な算術演算子は次の ５ 種類です。
| 演算子 | 処理 |
| ＋ | 加算 |
| － | 減算 |
| ＊ | 乗算 |
| ／ | 除算 |
| ％ | 剰余 |
SELECT LastName, FirstName, Salary ＊ 12
FROM Employees
WHERE EmployeeID = 5
WHERE 句で条件を指定すると、条件に合った行だけが結果セットに返されます。 WHERE句の条件式は「列名 比較演算子 式」で構成されています。使用可能な比較演算子は次の ６ 種類です。
| 演算子 | 意味 | 例 |
| ＝ | 等しい | EmployeeID = 5 |
| > | より大きい | Salary < 200000 |
| < | より小さい | Salary < 200000 |
| >＝ | 以上 | Salary >= 200000 |
| <＝ | 以下 | Salary <= 200000 |
| <> | 等しくない | Salary <> 200000 |
| LIKE 注1) | 文字列パタンが一致する | FirstName LIKE ‘YAMA%’ |
| BETWEEN | 範囲内にある（～以上～以下） | Salary BETWEEN 150000 AND 200000 |
| IN | リスト内にある | EmployeeID IN (5,10,15) |
| IS NULL注2) | NULL 値である | LastName IS NULL |
注1) LIKE演算子では、比較対象の文字列パターンを指定するために以下のワイルドカードを利用します。
-
％ 任意の文字列
-
任意の １ 文字
-
[] 指定した集合内の任意の １ 文字
-
[^] 指定した集合内ない任意の １ 文字
注2)NULL 値は空白値や長さ０の文字列とは区別されます。また、NULL 値は他の値と比較することができません。NULL 値と検索する場合は IS NULL 演算子のみが有効です。
-
複数の条件を指定
SELECT LastName, FirstName, Salary ＊ 12
FROM Employees
WHERE LastName LIKE ‘YAMA%’ AND Salary > 200000
WHERE 句では、論理演算子の AND や OR を使用して、２ つ以上の条件を組み合わせることができます。また、論理演算子のNOTを使用して条件の意味を逆にすることもできます。論理演算子には優先順位があり、NOT、AND、OR の順に条件が評価されます。
-
データの並べ替え
SELECT LastName, FirstName, Salary ＊ 12
FROM Employees
WHERE Salary > 200000
ORDER BY Salary DESC
ELECT ステートメントによって戻される結果セットの行の並び順は、ORDER BY 句で指定することができます。ORDER BY 句で指定した列の値の昇順または降順 （DESC キーワード指定時） に行が並べ替えられます。
-
重複行の排除
SELECT DISTINCT Salary
FROM Employees
WHERE Salary > 200000
ORDER BY Salary DESC
複数の行で列に同一の値が含まれている場合は、結果セットの行が他の行の内容と同一になることもあります。DISTINCT キーワードを使用すると、検索結果の行から重複行を排除して結果セットに返します。
-
検索結果の列名を変更
SELECT FirstName AS First , LastName AS Last , Salary ＊ 12 AS Sal
FROM Employees
WHERE Salary > 200000
結果セットの列には、元の表の列名とちがう名前 （別名） をつけることができます。結果セットに計算結果が含まれる場合に、これらの式に別名をつけて見やすくします。
6.2. データのグループ化と集計
データを取得するときにデータをグループ化したり集計を作成したりすることができます。
| 集計関数 | 説明 |
| AVG | 数値式の値の平均 |
| COUNT | 式の値の数 |
| MAX | 式の最大値 |
| MIN | 式も最小値 |
| SUM | 数値式の値の合計 |
SELECT SUM(Quantity)
FROM [Order Details]
この例では、Order Details テーブルに含まれる Quantity 列のすべての行を合計します
SELECT ProductID, SUM(Quantity)
FROM [Order Details]
GROUP BY ProductID
ORDER BY ProductID
ある列に対する集計値を求める場合は、GROUP BY 句と共に集計関数を使用します。 GROUP BY 句を使用しても並び替え順序は保証されません。結果を並び替える場合は ORDER BY 句を使用します。
6.3. 副問合せ（サブクエリ）
サブクエリとは、SQL ステートメントに埋め込まれたSELECTステートメントです。サブクエリを使用して、複雑なクエリを一連の論理的な手順に分解して、結果として、クエリを１つのステートメントで解決します。サブクエリは、クエリが別のクエリの結果に依存する場合に便利です。
-
派生テーブルとしてのサブクエリ
SELECT O.OrderID, O.CustomerID
FROM ( SELECT OrderID, CustomerID FROM Orders ) AS O
派生テーブルを作成するには、FROM 句でテーブルの代わりにサブクエリを使用します。別名を使って参照します。
-
式の代わりのサブクエリ
SELECT ProductName AS '製品名' ,UnitPrice AS '価格'
,( SELECT AVG(UnitPrice) FROM Products) AS '平均'
,UnitPrice-(SELECT AVG(UnitPrice) FROM Products) AS '差'
FROM Products
サブクエリは、SELECT、UPDATE、INSERT、DELETE の各ステートメントの中で、式の代わりに使用することができます。サブクエリの結果は単一の値である必要があります。この例は、製品の価格と平均価格の差を求めています。
-
相関副問合せ（相関サブクエリ）
相関サブクエリは、外部クエリが選択する各行を変更する動的な式として使用することができます。
外部クエリが選択する各行に対して一度ずつサブクエリを実行します。このサブクエリは、その行に対する式として評価され外部クエリに渡されます。
SELECT OrderID, CustomerID
FROM Orders AS O
WHERE 50 < (SELECT Quantity
FROM [Order Details] AS D
WHERE O.OrderID = D.OrderID
AND D.ProductID = 21))
この例は、製品番号21を50個より多く注文した顧客の一覧を返します。
相関サブクエリは実質的には、動的に実行されるサブクエリと外部クエリが選択する行との結合（JOIN）になります。
通常、相関サブクエリは結合として記述し直すことができます。
6.4. 結合問合せ
結合は、複数のテーブルをクエリして各テーブルの行と列を含む結果セットを生成します。両方のテーブルの任意の列や、任意の列に基ずく式によって、テーブルを結合します。結合には、内部結合、外部結合、クロス結合の 3 種類があります。３ つ以上のテーブルを結合したり、自己結合を使用して、テーブルをそのテーブル自体と結合することができます。
-
結合の構文
SELECT 選択リスト ...結果セットに返される列や計算式
FROM テーブル名 ...結果セットに返される値が格納されているテーブル名
JOIN ...結合するテーブルと結合方法を指定
ON ...結合するためのテーブル間の共通の列を指定
WHERE 検索条件 ...結果セットに返される行に対する検索条件 JOIN 句は結合するテーブルと結合方法（内部結合、外部結合、クロス結合）を指定します。 ON句は結合するためのテーブル間の共通の列を指定します。
-
内部結合
SELECT o.OrderID, c.CompanyName, o.OrderDate
FROM Orders AS o
INNER JOIN Customers AS c
ON o.CustomerID = c.CustomerID
内部結合は、両方のテーブルの共通の列の値を比較してテーブルを結合します。条件に合う行のみ返します。
-
外部結合
SELECT c.CustomerID,c.CompanyName,
o.OrderID, o.OrderDate
FROM Customers AS c LEFT OUTER JOIN Orders AS o
ON o.CustomerID = c.CustomerID
内部結合では、両方のテーブルで結合条件に合致する行がある場合のみ結果として返されます。これに対し、外部結合では、結合条件に一致しない行も結果に含めることができます。左外部結合の場合、参照された左テーブルのすべての行が返されます。
この例では、Customers テーブルが左テーブルです。注文の無い顧客情報も返されます。
-
テーブルの自己結合
SELECT a.CustomerID, b.CustomerID, a.Phone
FROM Customers AS a
JOIN Customers AS b
ON a.Phone = b.Phone
WHERE a.CustomerID < b.CustomerID
１つのテーブル内で結合することを自己結合といいます。このとき、テーブルに別名をつけることにより、同じテーブル同士でも、見かけ上、別のテーブルとして処理されます。この例では、同一の電話番号を持つ顧客情報を返します。同一行同士で結合される場合や、2 度結合される場合もあるので、WHERE句で除外しています。
6.5. データの更新
テーブルのデータを追加、変更、削除することができます。以下の SQL ステートメントを使用します。
| 処理 | SQL ステートメント |
| 追加 | INSRT |
| 変更 | UPDATE |
| 削除 | DELETE |
-
新しい行の挿入
INSERT Shippers
(ShipperID, CompanyName, Phone)
VALUES (4,'Japan Express','(503)666-1234')
INSERT ステートメントにより、テーブルに新しい行を追加することができます。各列に値を指定して、新しい行をテーブルに追加します。
-
既存のテーブルから行を追加
INSERT Shippers
SELECT ShipperID, CompanyName, Phone
FROM Japan_Shippers
既存の表から行を追加することもできます INSERT・・・SELECT ステートメントは SELECT ステートメントの結果セットを指定したテーブルに追加します。
-
行の更新
UPDATE Products
SET UnitPrice = (UnitPrice * 1.1)
WHERE CategoryID = 8
UPDATE ステートメントを使用して、既存の行の値を更新することができます。複数行の値を一度に更新することもできます。WHERE 句を使用することで更新する行を特定できます。SET 句により更新内容を指定できます。この例は、区分が ８ の商品の価格 10 % アップしています。
-
行の削除
DELETE Orders
WHERE DATEDIFF(Year, OrderDate, GETDATE()) >= 2
DELETE ステートメントを使って、テーブルから行を削除することができます。WHERE 句を使用することで削除する行を特定できます。削除された行はトランザクションログに記憶されます。この例は、注文日が、2 年以前の行を削除しています。
-
テーブルの切り捨て
TRUNCATE TABLE
ORDERS
TRUNCATE TABLE ステートメントを使用して、テーブル内のすべての行を削除することができます。削除された行はトランザクションログに記憶されません。
-
トランザクションの使用
ランザクションとは、論理的な １ つの作業単位として実行される一連の操作です。データの論理的一貫性を適用する時点でトランザクションの開始と終了を行う必要があります。
BEGIN TRANSACTION
UPDATE Savings SET balance= balance ? 100
WHERE CustomerID = 7890
UPDATE checking SET balance= balance ＋ 100
WHERE CustomerID = 7890
COMMIT TRANSACTION
トランザクションの開始の宣言は BEGIN TRANSACTION で行い、終了の宣言は COMMIT TRANSACTION を使用します。
6.6. データベース定義
データベースを定義するには、CREATE DATABASE ステートメントを使用します。トランザクションログも作成されます。
CREATE DATABASE Sales
ON PRIMARY ( NAME = 'Sales_Data',
FILENAME = 'D:\Sales_Data.MDF' ,
SIZE = 10, MAXSIZE = 100, FILEGROWTH = 4 )
LOG ON ( NAME = 'Sales_Log',
FILENAME = 'D:\Sales_Log.LDF' ,
SIZE = 5, MAXSIZE = 50, FILEGROWTH = 2 )
ＯＮ 句でデータファイルを定義し、LOG ON 句でログファイルを定義します。
| パラメータ | 説明 |
| PRIMARY | プライマリファイルグループに含むファイルを指定します |
| FILENAME | 物理ファイル名とファイルヘのパスを指定します。 |
| SIZE | ファイルのサイズを指定します。 |
| MAXSIZE | 拡張可能なファイルの最大サイズを指定します。 |
| FILEGROWTH | ファイル拡張の増分値を指定します。 |
データファイルが拡張されたり、データの変更が頻繁に行われると、ファイルのサイズを拡張したり、ファイルを追加したりする必要があります。データベースの拡張の管理には ALTER DATABASE ステートメントを使用します。
ALTER DATABASE Sales
ADD FILE
( NAME = Sales_Data2,
FILENAME=‘D:\ Sales_Data2.ndf’,
SIZE=20MB, MAXSIZE=50MB )
GO
ALTER DATABASE Sales
MODIFY FILE ( NAME = ‘Sales_Log’,
SIZE = 20MB)
GO
この例では、データファイルを追加し、トランザクションログサイズを拡大しています。
6.7. テーブルの作成と削除
テーブルを作成するには、CREATE TABLE ステートメントを使用します。
CREATE TABLE Shippers (
ShipperID int NOT NULL ,
CompanyName char (40) NOT NULL ,
Phone char (24) NULL
)
この時、テーブル名、列名、データの型を指定する必要があります。又、NULL 値を許可するかを指定します。テーブル名はデータベース内で一意である必要があります。列名は、テーブル内で一意である必要がありますが、同一データベース内の異なる複数のテーブルでは同じ列名を使用できます。
6.8. ビューの使用
ビューは、SELECT ステートメントによって定義された論理的なテーブルです。実際のデータが格納されているテーブルに対して、仮想的なテーブルです。ビューの中には実際のデータが保存されていません。ビュー内の行と列はビューを参照したときに動的に作成されます。
-
ビューの定義
CREATE VIEW TotalView
AS
SELECT ProductID, SUM(UnitPrice*Quantity) AS Total
FROM [Order Details]
GROUP BY ProductID
ビューの定義は CREATE VIEW ステートメントで行います。ビューは SELECT ステートメントに名前をつけたものといえます。
ビューの使用する利点には、以下の項目があります。
-
ビューを使用したデータの変更
ビューを使用してデータを変更することができます。ビューに対する変更は、実際は基になっているテーブルを変更することになります。基になっているテーブルが １ つで、SELECT ステートメントに集計関数や GROUP BY 句を使用していないビューからは、データの挿入、更新、削除が自由に行えます。ビューを使用してデータの変更を行う場合は以下の点に注意する必要があります。
6.9. 整合性制約の定義
データ整合性は、データベースに格納されているデータの一貫性と正確性を定義します。制約により、列に有効なデータ値が入力され、テーブル間の関係が保たれます。
データ整合性には以下の種類があります。
| 整合性の種類 | 説明 | 制約の種類 |
| ドメイン | ある列で有効な一連のデータ値を指定し、その値が列の値として妥当性を保証します | DEFAULT CHECK |
| 実体 | 値の重複を防ぎ、一意の値を持つことを保証します | PRIMARY KEY UNIQUE |
| 参照 | 主キーと外部キーのリレーションシップが常に維持されます。 | FOREGIN KEY |
-
DEFAULT 制約
INSERT ステートメントの実行時に値が指定されていない場合に、DAFAULT制約で指定された値が列に入力されます。列ごとに 1 つだけ定義できます。
ALTER TABLE Customers
ADD CONSTRAINT DF_ContactName
DEFAULT ‘UNDEFINE’ FOR ContactName
この例では、ContactName 列の既定値を’UNDEFINE’とします。
-
CHECK 制約
INSERT や UPDATE ステートメントの実行時にデータの整合性をチェックします。
ALTER TABLE Employees
ADD CONSTRAINT CK_BirthDate
CHECK ( BirthDate < GETDATE())
この例では、誕生日は、現在の日付より前でなければなりません。
-
PRIMARY KEY 制約
行の一意に識別する主キーをテーブルに対して指定します。テーブルごとに １ つだけ定義できます。NULL 値は許可されません。
ALTER TABLE Suppliers
ADD CONSTRAINT PK_ Suppliers
PRIMARY KEY CLUSTERED (SupplierID)
この例では、Suppliers テーブルの主キーとして SupplierID を定義します。
-
UNIQUE 制約
UNIQUE 制約は、ひとつの列に重複した値を格納できないことを指定します。既に主キーが存在し、さらに別の列に対しても値を一意に設定したい場合に使用します。NULL 値は、１ つだけ許可されます。
ALTER TABLE Suppliers
ADD CONSTRAINT U_CompanyName
UNIQUE NONCLUSTERED (CompanyName)
この例では、Suppliers テーブルの CompanyName に対し UNIQUE 制約を定義します。
-
FOREIGN KEY 制約 FOREIGN KEY 制約は参照整合性を適用します。FOREIGN KEY 制約は PRIMARY KEY 制約または UNIQUE 制約が設定された列への参照を定義します。
ALTER TABLE Products
ADD CONSTRAINT FK_Products_Categories
FOREIGN KEY (CategoryID)
REFERENCES Categories (CategoryID)
この例では、FOREIGN KEY 制約を使用して Products テーブルの CategoryID 列を Categories テーブルの CategoriyID 列と関連付けます。