レッスン 1: データベース オブジェクトの作成とクエリ

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)Microsoft Fabric の SQL データベース

このレッスンでは、データベースを作成する方法、データベースにテーブルを作成する方法、およびテーブル内のデータにアクセスして変更する方法を説明します。 これは、Transact-SQL の入門レッスンであるため、これらのステートメントに使用できる多くのオプションは使用せず、その説明も行いません。

データベース エンジンへの Transact-SQL ステートメントの書き込みと送信を行うには、次の方法を実行します。

• SQL Server Management Studioを使用する。 このチュートリアルでは、Management Studio を使用していることを前提としていますが、 Microsoft ダウンロード センターから無料でダウンロードできる Management Studio Express を使用することもできます。

• sqlcmd ユーティリティ ユーティリティを使用する。

• 作成したアプリケーションから接続する。

コードは、そのステートメントを送信する方法にかかわらず、 データベース エンジン で同じ権限を使用して同様に実行します。

Management Studio で Transact-SQL ステートメントを実行するには、Management Studio を開き、SQL Server データベース エンジンのインスタンスに接続します。

Prerequisites

このチュートリアルを実行するには、SQL Server Management Studio と SQL Server インスタンスへのアクセスが必要です。

• SQL Server Management Studio をインストールします。

SQL Server インスタンスがない場合は、インスタンスを作成します。 インスタンスを作成するには、次のリンクからプラットフォームを選択します。 SQL 認証を選択する場合は、SQL Server のログイン資格情報を使用します。

• Windows: SQL Server 2022 Developer Edition をダウンロードします。
• Linux: クイック スタート: Docker を使用して SQL Server Linux コンテナー イメージを実行する。

データベースを作成する

多くの Transact-SQL ステートメント同様、CREATE DATABASE ステートメントは、データベースの名前が必須パラメーターです。 CREATE DATABASE には、データベース ファイルを配置するディスクの場所など、多くのオプションのパラメーターもあります。 SQL Server でオプション パラメーターを指定せずに CREATE DATABASE を実行すると、これらの多くのパラメーターでは既定値が使用されます。

1. クエリ エディターのウィンドウで、次のコードを入力します。ただし、実行はしないでください。

   CREATE DATABASE TestData
   GO
   

2. ポインターを使用して CREATE DATABASEの語句を選択し、 F1キーを押します。 CREATE DATABASE アーティクルが開きます。 この方法を使用して、このチュートリアルで使用する CREATE DATABASE やその他のステートメントの全構文を見つけることができます。

3. クエリ エディターで、 F5 キーを押してステートメントを実行し、 TestDataという名前のデータベースを作成します。

データベースを作成すると、 SQL Server によって model データベースのコピーが作成され、その名前がデータベース名に変更されます。 オプション パラメーターでデータベースに大きな初期サイズを指定しなければ、この操作は数秒で完了します。

テーブルを作成する

適用対象:SQL ServerAzure SQL DatabaseAzure Synapse AnalyticsAnalytics Platform System (PDW)

テーブルを作成するには、テーブルの名前と、テーブル内の各列の名前とデータ型を入力する必要があります。 また、各列で null 値を許可するかどうかを指定することをお勧めします。 テーブルを作成するには、テーブルを追加するスキーマに対して CREATE TABLE アクセス許可と ALTER SCHEMA アクセス許可を持っている必要があります。 db_ddladmin 固定データベース ロールには、これらのアクセス許可があります。

ほとんどのテーブルに、テーブルの 1 つ以上の列で構成された主キーがあります。 主キーは常に一意です。 データベース エンジンによって、主キーの値がテーブルで重複しないように制限されます。

それぞれの説明のデータ型とリンクの一覧については、「 データ型」を参照してください。

クエリ エディター接続から TestData データベースへの切り替え

接続を TestData データベースに変更するには、クエリ エディターのウィンドウで次のコードを入力して実行します。

USE TestData
GO

テーブルの作成

クエリ エディターのウィンドウで、次のコードを入力して実行し、Products という名前の単純なテーブルを作成します。 テーブルの列は ProductID、 ProductName、 Price、 ProductDescriptionという名前です。 ProductID 列がテーブルの主キーです。 int、 varchar(25)、 money、 varchar(max) は、すべてデータ型です。 行を挿入または変更するときにデータを入力しなくてもよい列は、 Price と ProductionDescription のみです。 このステートメントには、スキーマというオプションの要素 (dbo.) が含まれています。 スキーマは、テーブルを所有するデータベース オブジェクトです。 管理者の場合は、 dbo が既定のスキーマです。 dbo はデータベース所有者を表します。

CREATE TABLE dbo.Products
    (ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription varchar(max) NULL)
GO

テーブルでのデータの挿入と更新

Products テーブルを作成したら、INSERT ステートメントを使用してテーブルにデータを挿入する準備ができました。 データが挿入されたら、 UPDATE ステートメントを使用して行の内容を変更します。 WHERE ステートメントのUPDATE句を使用して、更新を 1 行に制限します。 4 つのステートメントによって、次のデータが入力されます。

基本的な構文は、 INSERT、テーブル名、列リスト、 VALUES、挿入する値の一覧です。 行の先頭にある 2 つのハイフンは、その行がコメントであることを示します。この行のテキストはコンパイラによって無視されます。 この場合、コメントは構文に許可されているバリエーションを記述します。

データをテーブルに挿入

1. 次のステートメントを実行し、前のタスクで作成した Products テーブルに行を挿入します。

   -- Standard syntax
   INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
       VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
   GO
   

   挿入が成功した場合は、次の手順に進みます。

   挿入が失敗した場合は、 Product テーブルにその製品 ID を持つ行が既に含まれている可能性があります。 続行するには、テーブル内のすべての行を削除し、前の手順を繰り返します。 TRUNCATE TABLE を実行すると、テーブル内のすべての行が削除されます。

   次のコマンドを実行して、テーブル内のすべての行を削除します。

   TRUNCATE TABLE TestData.dbo.Products;
   GO
   

   テーブルを切り詰めた後、この手順で INSERT コマンドを繰り返します。

2. 次のステートメントは、フィールド一覧 (かっこ内) と値一覧の両方にある ProductID と ProductName の配置を交換することで、パラメーターの順序を変更する方法を示しています。

   -- Changing the order of the columns
   INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
       VALUES ('Screwdriver', 50, 3.17, 'Flat head')
   GO
   

3. 次のステートメントは、値が正しい順序で示されている限り、列の名前はオプションであることを示しています。 この構文は一般的ですが、他のユーザーがコードを理解しにくいため、推奨されません。 NULL が Price 列に指定されているのは、この製品の価格が不明ためです。

   -- Skipping the column list, but keeping the values in order
   INSERT dbo.Products
       VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
   GO
   

4. 既定のスキーマ内のテーブルにアクセスして変更している限り、スキーマ名は省略可能です。 ProductDescription 列では NULL 値が許可されており、値が提供されていないため、 ProductDescription 列の名前と値はステートメントから完全に省略できます。

   -- Dropping the optional dbo and dropping the ProductDescription column
   INSERT Products (ProductID, ProductName, Price)
       VALUES (3000, '3 mm Bracket', 0.52)
   GO
   

Products テーブルの更新

次の UPDATE ステートメントを入力して実行し、2 番目の製品の ProductName を Screwdriverから Flat Head Screwdriverに変更します。

UPDATE dbo.Products
    SET ProductName = 'Flat Head Screwdriver'
    WHERE ProductID = 50
GO

テーブルからデータを読み取る

SELECT ステートメントを使用して、テーブル内のデータを読み取る。 SELECTステートメントは最も重要な Transact-SQL ステートメントの 1 つであり、構文にはさまざまなバリエーションがあります。 このチュートリアルでは、5 つの基本バージョンを使用します。

テーブル内のデータを読み取る

1. 次のステートメントを入力して実行し、 Products テーブルのデータを読み取ります。

   -- The basic syntax for reading data from a single table
   SELECT ProductID, ProductName, Price, ProductDescription
       FROM dbo.Products
   GO
   

2. アスタリスク (*) を使用すると、テーブルの列をすべて選択できます。 アスタリスクはアドホック クエリで使用します。 永続的なコード内では列一覧を指定して、新しい列が後からテーブルに追加された場合でも、予測された列がステートメントによって返されるようにしてください。

   -- Returns all columns in the table
   -- Does not use the optional schema, dbo
   SELECT * FROM Products
   GO
   

3. 返す必要のない列は省略できます。 列は、一覧の順序で返されます。

   -- Returns only two of the columns from the table
   SELECT ProductName, Price
       FROM dbo.Products
   GO
   

4. ユーザーに返される行を制限するには、 WHERE 句を使用します。

   -- Returns only two of the records in the table
   SELECT ProductID, ProductName, Price, ProductDescription
       FROM dbo.Products
       WHERE ProductID < 60
   GO
   

5. 列内の値は、列が返されたときに操作できます。 次の例では、 Price 列に対して数学的演算を実行します。 このようにして変更された列には、AS キーワードを使用して名前を指定しない限り、名前が付けられません。

   -- Returns ProductName and the Price including a 7% tax
   -- Provides the name CustomerPays for the calculated column
   SELECT ProductName, Price * 1.07 AS CustomerPays
       FROM dbo.Products
   GO
   

SELECT ステートメント内で役に立つ関数

SELECT ステートメント内のデータを操作するために使用できる関数の詳細については、次の記事を参照してください。

ビューとストアド プロシージャの作成

ビューはストアド SELECT ステートメントであり、ストアド プロシージャはバッチとして実行される 1 つ以上の Transact-SQL ステートメントです。

ビューに対しては、テーブルと同じようにクエリが実行されます。パラメーターは使用できません。 ストアド プロシージャは、ビューよりも複雑です。 ストアド プロシージャには、入力パラメーターと出力パラメーターの両方を含めることができます。また、 IF ステートメントや WHILE ステートメントなど、コードのフローを制御するステートメントを含めることができます。 データベース内のすべての繰り返しアクションにストアド プロシージャを使用することをお勧めします。

この例では、 CREATE VIEW を使用して、 Products テーブル内の 2 つの列のみを選択するビューを作成します。 次に、 CREATE PROCEDURE を使用して、価格パラメーターを受け取り、指定されたパラメーター値より低コストの製品のみを返すストアド プロシージャを作成します。

ビューを作成する

次のステートメントを実行して、SELECT ステートメントを実行するビューを作成し、製品の名前と価格をユーザーに返します。

CREATE VIEW vw_Names
   AS
   SELECT ProductName, Price FROM Products;
GO

ビューのテスト

ビューはテーブルと同じように処理されます。 ビューにアクセスするには SELECT ステートメントを使用します。

SELECT * FROM vw_Names;
GO

ストアド プロシージャの作成

次のステートメントでは、 pr_Namesという名前のストアド プロシージャを作成し、 @VarPrice という名前の、 moneyデータ型の入力パラメーターを受け入れます。 このストアド プロシージャによって、 Products less than データ型から money 文字データ型に変更される入力パラメーターと連結されるステートメント varchar(10) が出力されます。 次に、ビューに対して SELECT ステートメントが実行され、 WHERE 句の一部として入力パラメーターが渡されます。 これによって、入力パラメーター値よりも価格が安い製品がすべて返されます。

CREATE PROCEDURE pr_Names @VarPrice money
   AS
   BEGIN
      -- The print statement returns text to the user
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
      -- A second statement starts here
      SELECT ProductName, Price FROM vw_Names
            WHERE Price < @VarPrice;
   END
GO

ストアド プロシージャのテスト

ストアド プロシージャをテストするには、次のステートメントを入力して実行します。 このプロシージャによって、レッスン 1 で Products テーブルに入力した、価格が 10.00より安い 2 つの製品の名前が返されます。

EXECUTE pr_Names 10.00;
GO

関連コンテンツ

• レッスン 2: データベース オブジェクトに対するアクセス許可の構成