レッスン 1: データベース オブジェクトの作成とクエリ
適用対象: 
SQL Server Azure SQL データベース Azure SQL Managed Instance Analytics Platform System (PDW)
Note
「Transact-SQL を使用したクエリの実行の概要」ラーニング パスでは、より詳しい内容と実用的な例が提供されています。
このレッスンでは、データベースを作成する方法、データベースにテーブルを作成する方法、およびテーブル内のデータにアクセスして変更する方法を説明します。 これは、Transact-SQL の入門レッスンであるため、これらのステートメントに使用できる多くのオプションは使用せず、その説明も行いません。
データベース エンジンへの Transact-SQL ステートメントの書き込みと送信を行うには、次の方法を実行します。
SQL Server Management Studioを使用する。 このチュートリアルでは、 Management Studioを使用することを前提としていますが、 Management Studio Express も使用できます。これは Microsoft ダウンロード センターから無料でダウンロードできます。
sqlcmd ユーティリティを使用する。
作成したアプリケーションから接続する。
コードは、そのステートメントを送信する方法にかかわらず、 データベース エンジン で同じ権限を使用して同様に実行します。
Management Studio で Transact-SQL ステートメントを実行するには、Management Studio を開き、SQL Server データベース エンジンのインスタンスに接続します。
前提条件
このチュートリアルを実行するには、SQL Server Management Studio と SQL Server インスタンスへのアクセスが必要です。
- SQL Server Management Studio をインストールします。
SQL Server インスタンスがない場合は、インスタンスを作成します。 インスタンスを作成するには、次のリンクからプラットフォームを選択します。 SQL 認証を選択する場合は、SQL Server のログイン資格情報を使用します。
- Windows: SQL Server 2022 Developer Edition をダウンロードします。
- Linux: SQL Server 2022 をコンテナーにダウンロードします。
データベースを作成する
多くの Transact-SQL ステートメント同様、CREATE DATABASE ステートメントは、データベースの名前が必須パラメーターです。 CREATE DATABASE には、データベース ファイルを配置するディスクの場所など、多くのオプションのパラメーターもあります。 SQL Server でオプション パラメーターを指定せずに CREATE DATABASE を実行すると、これらの多くのパラメーターでは既定値が使用されます。
クエリ エディターのウィンドウで、次のコードを入力します。ただし、実行はしないでください。
CREATE DATABASE TestData GOポインターを使用して
CREATE DATABASEの語句を選択し、 F1キーを押します。CREATE DATABASEアーティクルが開きます。 この方法を使用して、このチュートリアルで使用するCREATE DATABASEやその他のステートメントの全構文を見つけることができます。クエリ エディターで、 F5 キーを押してステートメントを実行し、
TestDataという名前のデータベースを作成します。
データベースを作成すると、 SQL Server によって model データベースのコピーが作成され、その名前がデータベース名に変更されます。 オプション パラメーターでデータベースに大きな初期サイズを指定しなければ、この操作は数秒で完了します。
Note
GO キーワードは、複数のステートメントを単一のバッチで送信した場合に、ステートメントを区切ります。 GO は、バッチにステートメントが 1 つしか入っていない場合はオプションです。
テーブルを作成する
適用対象: SQL Server Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW)
テーブルを作成するには、テーブルの名前と、テーブル内の各列の名前とデータ型を入力する必要があります。 また、各列でヌル値を許可するかどうかを指定することも推奨されます。 テーブルを作成するには、テーブルを追加するスキーマに対して CREATE TABLE アクセス許可と ALTER SCHEMA アクセス許可を持っている必要があります。 db_ddladmin 固定データベース ロールには、これらのアクセス許可があります。
ほとんどのテーブルに、テーブルの 1 つ以上の列で構成された主キーがあります。 主キーは常に一意です。 データベース エンジンによって、主キーの値がテーブルで重複しないように制限されます。
データ型の一覧と、それぞれの説明へのリンクについては、「データ型 (Transact-SQL)」を参照してください。
Note
データベース エンジン は、大文字と小文字を区別するか区別しないかを設定してインストールできます。 大文字と小文字を区別するように設定して データベース エンジン をインストールした場合は、オブジェクト名を常に大文字か小文字に統一する必要があります。 たとえば、OrderData という名前のテーブルと、ORDERDATA という名前のテーブルは別のテーブルです。 大文字と小文字を区別しないように設定して データベース エンジン をインストールした場合、この 2 つのテーブル名は同じテーブルと見なされるため、その名前は一度しか使用できません。
クエリ エディター接続から 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 ステートメントを使用して行の内容を変更します。 更新を 1 つの行に制限するには、UPDATE ステートメントの WHERE 句を使用します。 4 つのステートメントによって、次のデータが入力されます。
| ProductID | ProductName | 価格 | ProductDescription |
|---|---|---|---|
| 1 | Clamp | 12.48 | Workbench clamp |
| 50 | Screwdriver | 3.17 | Flat head |
| 75 | Tire Bar | Tool for changing tires | |
| 3000 | 3 mm Bracket | 0.52 |
基本的な構文は、INSERT、テーブル名、列一覧、VALUES、および挿入する値の一覧です。 行の先頭にある 2 つのハイフンは、その行がコメントであることを示します。この行のテキストはコンパイラによって無視されます。 この場合、コメントは構文に許可されているバリエーションを記述します。
データをテーブルに挿入
次のステートメントを実行し、前のタスクで作成した
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コマンドを繰り返します。次のステートメントは、フィールド一覧 (かっこ内) と値一覧の両方にある
ProductIDとProductNameの配置を交換することで、パラメーターの順序を変更する方法を示しています。-- Changing the order of the columns INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription) VALUES ('Screwdriver', 50, 3.17, 'Flat head') GO次のステートメントは、値が正しい順序で示されている限り、列の名前はオプションであることを示しています。 この構文は一般的ですが、他のユーザーがコードを理解しにくいため、推奨されません。
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スキーマ名は、既定のスキーマ内のテーブルにアクセスし、変更している場合にはオプションです。
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 つの基本的なバージョンを使います。
テーブル内のデータを読み取る
次のステートメントを入力して実行し、
Productsテーブルのデータを読み取ります。-- The basic syntax for reading data from a single table SELECT ProductID, ProductName, Price, ProductDescription FROM dbo.Products GOアスタリスク (
*) を使用すると、テーブルの列をすべて選択できます。 アスタリスクはアドホック クエリで使用します。 永続的なコード内では列一覧を指定して、新しい列が後からテーブルに追加された場合でも、予測された列がステートメントによって返されるようにしてください。-- Returns all columns in the table -- Does not use the optional schema, dbo SELECT * FROM Products GO返す必要のない列は省略できます。 列は、一覧の順序で返されます。
-- Returns only two of the columns from the table SELECT ProductName, Price FROM dbo.Products GOユーザーに返される行を制限するには、
WHERE句を使用します。-- Returns only two of the records in the table SELECT ProductID, ProductName, Price, ProductDescription FROM dbo.Products WHERE ProductID < 60 GO列内の値は、列が返されたときに操作できます。 次の例では、
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
次のステップ
次の記事では、データベース オブジェクトに対してアクセス許可を構成する方法について説明します。 レッスン 1 で作成したオブジェクトは、レッスン 2 でも使用されます。
詳細については、次の記事に進んでください