第 5 章 「INSERT,DELETE,UPDATEステートメント」 ～ 初めて学ぶ Transact-SQL ～
NEC
Eラーニング事業部
鈴木 智行
2003 年 3 月 17 日
目次
.gif){kind=icon}
1. 復習問題の解答例
2. トランザクションとロック
3. INSERTステートメント
4. DELETEステートメント
5. UPDATE ステートメント
1. 復習問題の解答例
第 4 章の復習問題の解答例は以下の通りですが、必ずしも一字一句同じである必要はありません。ロジックはいろいろありますので、意図したとおりの正しい結果が返ってくれば正解と考えてください。
問題 1
商品テーブルと製造会社テーブルから商品名とその商品を製造した会社名を取得するための Transact-SQL 文を記述しなさい
use 販売 select 商品名,会社名 from 商品 join 製造会社 on 商品.会社コード = 製造会社.会社コード
（解説）2つのテーブルの内部結合です。これが基本になるのでしっかり覚えてください。
問題 2
顧客テーブルと商品テーブルと注文テーブルから顧客名に対する商品名、数量、yy/mm/dd 形式に整形された注文日を取得するための Transact-SQL 文を記述しなさい、ただし商品は注文されてなくても表示すること
use 販売 select 顧客名,商品名,数量,convert(char(8),日付,11) as 注文日 from 商品 as s left outer join 注文 as o on s.商品コード = o.商品コード inner join 顧客 as c on o.顧客コード = c.顧客コード
（解説）3 つのテーブルの結合が必要ですが、その中でも注文されていない商品の表示をしなければいけないため商品テーブルに対しては外部結合を使用します。
問題 3
商品テーブルと商品情報テーブルと注文テーブルから商品名とその商品の総売上と総利益を取得するための Transact-SQL 文を記述しなさい、ただし売上は販売単価*数量で列名は 「総売上」 という名前、利益は(販売単価-原価)*数量で列名は 「総利益」 という名前とし、総利益が 3000 以下の商品は除外すること
use 販売 select 商品名,sum(販売単価*数量) as 総売上, sum((販売単価-原価)*数量) as 総利益 from 商品情報 as d join 商品 as s on d.商品コード = s.商品コード join 注文 as o on s.商品コード = o.商品コード group by 商品名 having sum((販売単価-原価)*数量) > 3000
（解説）having 総利益 > 3000 ではエラーになるので注意してください。
問題 4
顧客テーブルと注文テーブルから 1 回の注文で数量を 30 以上注文したことがある顧客名一覧を出力するための Transact-SQL 文をサブクエリで記述しなさい
use 販売 select c.顧客名 from 顧客 as c where c.顧客コード IN (select o.顧客コード from 注文 as o where 数量 >= 30)
（解説）条件を満たす顧客は複数存在する可能性があるため、 IN キーワードと併用してサブクエリを使用します。
問題 5
顧客テーブルと注文テーブルから今までに注文を 3 回以上行ったことがある顧客名一覧を出力するための Transact-SQL 文をサブクエリで記述しなさい
use 販売
select c.顧客名 from 顧客 as c
where 3 <=
(select count(o.顧客コード)
from 注文 as o
where c.顧客コード = o.顧客コード
group by o.顧客コード)
（解説）顧客が注文した 1 回分の情報は注文テーブルで 1 行として表現されるため、count 関数を使用して顧客の注文回数を集計します。
2. トランザクションとロック
更新系のステートメント (INSERT,DELETE,UPDATE) を紹介する前に、トランザクションとロックについて理解しておくことが必要です。
2.1 トランザクション
トランザクションとは”意味をもった作業単位”を意味します。
例えばトランザクションを”通信販売での洋服の購入”を通して考えてみましょう。通常”通信販売での洋服の購入”は以下のような流れで行われることが多いと思います。
1 画像を見て気に入った洋服のサイズ在庫があれば配送してもらうように自宅住所を記入する
2 クレジットカード番号やパスワードを入力し支払い回数を決める
3 宅配業者が配送し、自宅で洋服を受け取る
以上がすべて正常に行われてはじめて、洋服が自分のものになる（すなわち” 通信販売での洋服の購入”が終了する）わけです。しかし自宅で洋服を見てみたら、実際のイメージとはかけ離れていたので返品したい（すなわち” 通信販売での洋服の購入”をなかったものにしたい）ケースもあるでしょう。
この場合、上記の流れをトランザクションと定義すれば（” 通信販売での洋服の購入”を作業単位と定義すれば）、洋服が気に入らない際に返品し支払いもなかったことにできるのです。
SQL Server 2000 の既定では、1 ステートメント実行毎に処理が確定します。これを自動コミットモードといいます (図2-1-1)。
.gif)
図 2-1-1 自動コミットモード
” 通信販売での洋服の購入”の例ではこのモードの場合、どのような洋服が自宅に届いたとしてもクレジットでお金を支払わなければならないことになります。
しかし SQL Server 2000 で処理単位を定義したい場合は、一連の操作を BEGIN TRANSACTION ステートメント（トランザクション処理の開始を宣言する）と COMMIT TRANSACTION ステートメント（トランザクション処理を終了し、データ変更処理を確定する）でひとくくりにします。これを明示的なトランザクションモードと呼びます（図 2-1-2）。
.gif)
図 2-1-2 明示的なトランザクションモード
操作を取り消したい場合には ROLLBACK TRANSACTION ステートメントを記述します。” 通信販売での洋服の購入”の例では操作を取り消す（ロールバック）ことで、気に入らない洋服が届いた場合の返品処理ができるようになります。
またこの他に ORACLE などと互換性をもつ暗黙的なトランザクションモードもあります。暗黙的なトランザクションモードは SET IMPLICIT_TRANSACTIONS ON ステートメントを実行すると、INSERT や DELETE や UPDATE などのステートメントが実行されたタイミングでトランザクションが自動開始されるため、COMMIT TRANSACTION ステートメントもしくは ROLLBACK TRANSACTION ステートメントを記述してトランザクションの終了を定義する (すなわち BEGIN TRANSACTION ステートメントを記述しない)モードです。
2.2 ロック
ロックとはデータを保護して矛盾を防止する機能です。データベースには同時に複数のクライアントから複数のトランザクションが同じデータにアクセスする可能性があるため、データが矛盾しないように管理しなければいけません。例えば 2-1 の” 通信販売での洋服の購入”で考えてみた場合、もしロックでデータが保護されていなければ、気に入った洋服の在庫がデータベース上ではあったとしても、同じタイミングでその洋服が売れて他の顧客へ配送してしまったため、いざ配送の段階で物理在庫がないといった矛盾した現象が起こる可能性もあるわけです（図 2-2-1）。
.gif)
図 2-2-1 データが矛盾している状態
ロックには表 2-2-2 で示す単位（粒度と呼ぶ場合もある）があります。
種類 |
説明 |
|---|---|
RID |
テーブルの 1 行 |
KEY |
インデックスの1行 |
PAG |
8KB のデータページまたはインデックスページ |
TAB |
全てのデータとインデックスを含むテーブル全体 |
表 2-2-2 ロックの単位（抜粋）
ロックの単位が小さければ小さいほどトランザクションの同時実行性があがりますが、ロックを管理するために必要なメモリが増加します。ロックの単位が大きければ大きいほどロックを管理するためのメモリは少なくなりますが、トランザクションの同時実行性はさがります。ロックの単位とトランザクションの同時実行性はトレードオフの関係になるので環境に応じてロックの単位を考慮する必要があります。もちろん皆さんがロックの単位を指定して（これをロックヒントという）トランザクションを実行することも可能ですが、なにも指定しなければ SQL Server は適切なロックの単位を選んでトランザクションを実行します。これを動的ロックといい、動的ロックは SQL Server 2000 のデフォルトです。
また、ロックには表 2-2-3 で示す種類と互換性があります。ロックの互換性とはあるデータに対してロックを同時に取得できるかどうかを示したものです。
表2-2-3 ロックの種類（抜粋）と互換性
例えば上記の場合、更新ロックがかけられたデータに対して更新ロックはかけられませんから、”通信販売での洋服の購入”の例で考えた場合、気に入った洋服の在庫があった際に（在庫数を減らす可能性があるため）更新ロックを利用し、同時に他のトランザクションからの更新ロックを防ぐことができるため、データベース上の在庫数と物理在庫が矛盾することはなくなるわけです（図 2-2-4）。
種類 |
説明 |
互換性 |
互換性 |
互換性 |
|---|---|---|---|---|
|
|
S |
X |
U |
共有(S) |
データの読み取り処理で取得されるロックです |
○ |
× |
○ |
排他(X) |
データの変更処理で取得されるロックです |
× |
× |
× |
更新(U) |
更新予定データの読み取り処理で収録されるロックです。実際の更新の際に排他ロックに変換されます |
○ |
× |
× |
表 2-2-3 ロックの種類 （抜粋） と互換性
例えば上記の場合、更新ロックがかけられたデータに対して更新ロックはかけられませんから、"通信販売での洋服の購入"の例で考えた場合、気に入った洋服の在庫があった際に（在庫数を減らす可能性があるため）更新ロックを利用し、同時に他のトランザクションからの更新ロックを防ぐことができるため、データベース上の在庫数と物理在庫が矛盾することはなくなるわけです（図 2-2-4）。
.gif)
図 2-2-4 データが矛盾していない状態
3. INSERTステートメント
INSERT ステートメントは、テーブルに行単位でデータを追加します。基本的な構文は下記の通りです。
INSERT テーブル名 ( 列リスト ) VALUES （値 1 , 値 2 , ....... ）
VALUES 句で追加するデータを指定します。列リストを指定しなければ、全ての列に対する値が必要になりますが、列リストを使えば指定した列に対応する値のみで構いません。ただし指定しない列に値が必要ないというわけではなく、デフォルト制約 （既定値）、NULL 許可 (NULL 値)が列に対して設定されていないと INSERT ステートメントはエラーになります（画面 3-1）。
.gif)
画面 3-1 指定していない列に既定値や NULL 値が入らないとエラーになる
また INSERT ステートメントは 1 行しか追加できませんが、INSERT...SELECT ステートメントを使用すれば複数の行をまとめて追加することができます。
INSERT テーブル名 SELECT........
ただし INSERT...SELECT ステートメントを利用する場合は以下の点に気をつけてください。
INSERT 対象のテーブルが存在する
SELECT で取得した列のデータ型と INSERT 対象の列のデータ型が同じ、もしくは互換性がある
省略される列については既定値が設定されているか NULL 値が許可されている
4. DELETEステートメント
DELETE ステートメントは、テーブルから行単位でデータを削除します。基本的な構文は下記の通りです。
DELETE テーブル名 WHERE 条件
WHERE 句で条件を指定すれば、条件を満たした複数の行が削除されますし、WHERE 句を指定しなければテーブルの全データが削除されます。これと同様な機能を実現するステートメントとして TRUNCATE TABLE ステートメントがあります。
TRUNCATE TABLE テーブル名
DELETE ステートメントは各行の削除内容を記録しますが、TRUNCATE TABLE ステートメントは各行の削除内容を記録せず、データページの割り当ての解除を行います。そのため DELETE ステートメントより高速に働きますが削除内容を記録しないため、それを使ってデータを復旧することができなくなります。
5. UPDATE ステートメント
UPDATE ステートメントは、既存にあるデータを更新します。基本的な構文は下記の通りです。
UPDATE テーブル名
SET 列名 = 値, ...
WHERE 条件
WHERE 句で指定する条件で更新する行を特定し、SET 句で対象とする列の値を更新します。このとき WHERE 句を指定しなければ SET 句で指定した列が全て新しい値に更新されます。
UPDATE ステートメントを利用する場合の注意点として挙げられるのは、特にサブクエリを使用した場合です。SQL Server 2000 では同一行を 2 回更新することはできないので、例えば注文テーブルの数量を売上情報テーブルの注文数量に1件ずつ足し込んでいくようなことはできません（図 5-1）。
.gif)
図 5-1 単一の UPDATE ステートメントで同一行を 2 回更新できない
したがってこのような場合はサブクエリ内で集計関数を利用して集計値で注文数量を更新します（図 5-2）
.gif)
図 5-2 集計関数を利用して更新する
これを実現するTransact-SQLは以下のように記述します。
update 売上情報 set 注文数量 = (select sum(数量) from 注文 where 売上情報.商品コード = 注文.商品コード and 注文年 = year(日付) and 注文月 = month(日付))
6. まとめ
今回のコラムでは「Transact-SQL とは何か？」、「既存にあるデータベースに対してどのように記述すればデータ検索、追加、削除、更新ができるか？」 を基本的な部分を中心にご紹介しました。しかし Transact-SQL はこのような DML（データ操作言語）だけではなく、他にも DCL （データ制御言語） や DDL （データ定義言語） などがあり、テーブルの作成やテーブルにアクセスするユーザーに許可を与えたい時には、これらも必要になってきますのでぜひ勉強してください。また同じ結果を得るにもたくさんのロジック記述方法があります。1つの方法だけでなく、いろいろな方法で記述できるようなスキルも磨いてください。その際の最適な記述を選択する方法は、「クエリのパフォーマンスチューニング」という私が以前記述したコラムなども一助になると思います、ぜひ参考にしてください。
.jpg)
鈴木 智行: NEC Eラーニング事業部に所属。入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。 Windows 2000 および SQL Server 2000 での MCSE,MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア（データベース）も取得済。 SQL Server の優位性をアピールできるように Oracle Gold も取得した。今後 Oracle Plutinum を取得予定し、日々データベースを極めることに努力している。