次の方法で共有

第 2 回 データベースアクセスとデータベース定義～ MCA をめざそう!! MCA データベース Microsoft SQL Server 2000 対応 編 ～

  • [アーティクル]

熊倉 克己

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演算子では、比較対象の文字列パターンを指定するために以下のワイルドカードを利用します。

  1. ％ 任意の文字列

  2. 任意の １ 文字

  3. [] 指定した集合内の任意の １ 文字

  4. [^] 指定した集合内ない任意の １ 文字

注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 値を許可するかを指定します。テーブル名はデータベース内で一意である必要があります。列名は、テーブル内で一意である必要がありますが、同一データベース内の異なる複数のテーブルでは同じ列名を使用できます。

  • テーブルの削除

    テーブルの削除には DROP TABLE を使用します。テーブルを削除すると、テーブル定義とデータが削除されます。

                DROP TABLE  Shippers

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 句を使用していないビューからは、データの挿入、更新、削除が自由に行えます。ビューを使用してデータの変更を行う場合は以下の点に注意する必要があります。

    • 基になる複数のテーブルを同時に変更できない

    • 特定の列（計算値、組込み関数、集計関数）を変更できない

    • ビューによって参照されない列に影響を与える場合
      NULL値が許可されていない列、規定値を持たない列

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 列と関連付けます。