Microsoft SQL Server チュートリアル
Microsoft SQL Serverを使い始める

備考

バージョン

INSERT / SELECT / UPDATE / DELETE：データ操作言語の基本

Dの ATA Mの anipulation さL anguage（略してDML）のような操作含むINSERT 、 UPDATEおよびDELETE ：

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld

このスクリプトでは、いくつかの基本的なクエリを示すためのテーブルを作成しています 。

次の例は、 テーブルをクエリする方法を示しています。

USE Northwind;
GO
SELECT TOP 10 * FROM Customers 
ORDER BY CompanyName

Northwindデータベース（Microsoftのサンプルデータベースの1つで、 ここからダウンロードできます ）のCompanyName列で注文されたCustomerテーブルの最初の10レコードが選択されます。

Use Northwind;をUse Northwind;ことに注意してUse Northwind;すべての後続のクエリのデフォルトデータベースを変更します。 [データベース]。[スキーマ]。[テーブル]の形式で完全修飾構文を使用しても、データベースを参照できます。

SELECT TOP 10 * FROM Northwind.dbo.Customers 
ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors
ORDER BY City

これは、異なるデータベースからデータを照会する場合に便利です。 "between between"で指定されたdboはスキーマと呼ばれ、完全修飾構文を使用して指定する必要があることに注意してください。データベース内のフォルダと考えることができます。 dboはデフォルトのスキーマです。デフォルトスキーマは省略することができます。他のすべてのユーザー定義スキーマを指定する必要があります。

データベース表に予約語のような名前の列（ Dateなど）が含まれている場合は、次のように列名を角カッコで囲む必要があります。

-- descending order
SELECT TOP 10 [Date] FROM dbo.MyLogTable
ORDER BY [Date] DESC

列名に名前にスペースが含まれている場合も同様です（推奨されません）。代わりの構文は、角括弧の代わりに二重引用符を使用することです。

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
order by "Date" desc 

同等であるがあまり一般的ではない。二重引用符と一重引用符の違いに注目してください。一重引用符は文字列に使用されます。

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
where UserId='johndoe'
order by "Date" desc 

有効な構文です。 T-SQLには、NCharデータ型とNVarcharデータ型のN接頭辞が付いています。

SELECT TOP 10 * FROM Northwind.dbo.Customers 
WHERE CompanyName LIKE N'AL%'
ORDER BY CompanyName

AL始まる会社名を持つすべての企業を返します（ %はワイルドカードです.DOS DIR AL*などのDOSコマンドラインでアスタリスクを使用する場合と同じように使用します）。 LIKEには、いくつかのワイルドカードがあります。詳細はこちらをご覧ください。

結合

結合は、単一の表に存在しない複数の表に存在するフィールドを照会する場合に便利です。例： NorthwindデータベースのRegionテーブルからすべての列を照会します。しかし、別のテーブルRegion格納されているRegionDescriptionも必要であることがRegionDescription Region 。ただし、共通キーRgionIDあります。このキーを使用すると、次のように1つのクエリでこの情報を組み合わせることができます（ Top 5 5行は最初の5行を返します。すべての行を取得するためには省略します）。

SELECT TOP 5 Territories.*, 
    Regions.RegionDescription 
FROM Territories 
INNER JOIN Region 
    ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription

Territoriesからのすべての列とTerritoriesのRegionDescription列が表示されRegion 。結果はTerritoryDescriptionによって注文されます。

テーブルの別名

クエリで2つ以上のテーブルへの参照が必要な場合は、テーブルエイリアスを使用すると便利です。テーブルのエイリアスは、テーブル全体の名前の代わりに使用できるテーブルの略語であり、入力と編集を減らすことができます。エイリアスを使用するための構文は次のとおりです。

<TableName> [as] <alias>

どこasオプションのキーワードです。たとえば、前のクエリは次のように書き換えることができます。

SELECT TOP 5 t.*, 
    r.RegionDescription 
FROM Territories t
INNER JOIN Region r 
    ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

同じテーブルを2回使用する場合でも、エイリアスはクエリ内のすべてのテーブルに対して一意でなければなりません。たとえば、EmployeeテーブルにSupervisorIdフィールドが含まれている場合、このクエリを使用して従業員とそのスーパーバイザの名前を返すことができます。

SELECT e.*, 
    s.Name as SupervisorName -- Rename the field for output
FROM Employee e
INNER JOIN Employee s
    ON e.SupervisorId = s.EmployeeId
WHERE e.EmployeeId = 111

組合

これまで見てきたように、Joinはさまざまなテーブルソースから列を追加します。しかし、異なるソースからの行を結合する場合はどうすればよいでしょうか？この場合、UNIONを使用できます。あなたがパーティーを計画していて、従業員だけでなく顧客も招待したいとします。次に、このクエリを実行してそれを実行できます。

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers

従業員と顧客の名前、住所、都市を1つのテーブルに返します。重複している行（存在する必要がある場合）は自動的に削除されることに注意してください（これを望まない場合は、代わりにUNION ALL使用してください）。列番号、列名、順序およびデータ型は、共用体の一部であるすべてのSELECT文で一致する必要があります。これは、最初のSELECTがFirstNameとLastNameをEmployeeからContactNameするContactNameです。

テーブル変数

テンポラリ・データ（特にストアド・プロシージャ内）を処理する必要がある場合は、表変数を使用すると便利です。「実際の」表と表変数の違いは、一時的な処理のためにメモリー内に存在することだけです。

例：

DECLARE @Region TABLE
(
  RegionID int, 
  RegionDescription NChar(50)
)

メモリにテーブルを作成します。この場合、 @接頭辞は変数であるため必須です。上記のすべてのDML操作を実行して、行の挿入、削除および選択を行うことができます。

INSERT INTO @Region values(3,'Northern')
INSERT INTO @Region values(4,'Southern')

しかし、通常、あなたは実際のテーブルに基づいてそれを設定します

INSERT INTO @Region
SELECT * FROM dbo.Region WHERE RegionID>2;

実際のテーブルdbo.Regionからフィルタリングされた値を読み込み、それをメモリテーブル@Region挿入します。このメモリテーブルは、 @Region処理するために使用できます。たとえば、次のような結合で使用できます。

SELECT * FROM Territories t
JOIN @Region r on t.RegionID=r.RegionID

この場合、 NorthernとSouthernすべての領土が返還されます。より詳細な情報はここで見つけることができます 。そのトピックについて詳しく知りたい場合は、一時テーブルについてここで説明します 。

注：代わりに、 一時テーブル 、または一時テーブルを使用し、大量のデータで作業する場合は、テーブル変数内のデータの行数が100未満の場合、Microsoftは唯一のテーブル変数の使用を推奨しています。

PRINT

出力コンソールにメッセージを表示します。 SQL Server Management Studioを使用すると、[結果]タブではなく[メッセージ]タブに表示されます。

PRINT 'Hello World!';

テーブルからすべての行と列を選択する

構文：

SELECT *
FROM table_name

アスタリスク演算子*を使用すると、表のすべての列を選択するためのショートカットとして機能します。このSELECT文にはWHERE句がないため、すべての行も選択され、フィルタリング条件を指定できません。

これは、テーブルにエイリアスを追加した場合も同様です（この場合はe 。

SELECT *
FROM Employees AS e

または、特定のテーブルからすべてを選択する場合は、別名+ "。*"を使用できます。

SELECT e.*, d.DepartmentName
FROM Employees AS e
    INNER JOIN Department AS d 
        ON e.DepartmentID = d.DepartmentID

データベースオブジェクトには、完全修飾名を使用してアクセスすることもできます。

SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

サーバーやデータベース名を変更すると、無効なオブジェクト名のために完全修飾名を使用したクエリが実行されなくなるため、これは必ずしも推奨されません。

クエリが単一のサーバー、データベース、およびスキーマでそれぞれ実行される場合、多くの場合、 table_name前のフィールドは省略できます。ただし、データベースには複数のスキーマが存在するのが一般的です。この場合、スキーマ名は可能な限り省略しないでください。

警告：本番コードやストアドプロシージャでSELECT *を使用すると、後で問題が発生する可能性があります（新しい列がテーブルに追加されたり、列がテーブルに再配置された場合）。特に、列の順序、または戻された列の数。したがって、プロダクションコードのSELECT文では常にカラム名を明示的に指定する方が安全です。

SELECT col1, col2, col3
FROM table_name

条件に一致する行を選択する

一般的に、構文は次のとおりです。

SELECT <column names>
FROM <table name>
WHERE <condition>

例えば：

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'

条件は複雑になる可能性があります。

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith' AND (City = 'New York' OR City = 'Los Angeles')

UPDATE特定行

UPDATE HelloWorlds
SET HelloWorld = 'HELLO WORLD!!!'
WHERE Id = 5

上記のコードは、フィールド "HelloWorld"の値を "HELLO WORLD !!!"に更新します。 HelloWorldsテーブルの "Id = 5"というレコードの場合

注：更新ステートメントでは、要件が異なる場合を除いて、テーブル全体の更新を避けるために「where」節を使用することをお勧めします。

すべての行を更新

単純な形式の更新では、テーブルの特定のフィールドのすべての値がインクリメントされます。これを行うには、フィールドとインクリメント値を定義する必要があります

以下は、 Scoreフィールドを1ずつ増加させる例です（すべての行）。

UPDATE Scores
SET score = score + 1  

これは、誤って特定の行のUPDATEを表のすべての行に対して行うと、データが破損する可能性があるため危険です。

コード内のコメント

Transact-SQLは、2つの形式のコメント書き込みをサポートしています。コメントはデータベースエンジンによって無視され、人々が読むためのものです。

コメントは--で始まり、新しい行に出会うまで無視されます：

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;

スラッシュスターのコメントは/*始まり*/終わります。これらのデリミタ間のテキストはすべてコメントブロックと見なされます。

/* This is
a multi-line
comment block. */
SELECT Id = 1, [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';

スラッシュスターのコメントには、SQL文が改行文字を失った場合にコメントを使用できるようにする利点があります。これは、トラブルシューティング中にSQLが取得されたときに発生します。

スラッシュスターコメントは入れ子にすることができ、スラッシュスターコメント内の開始/*は*/で有効にする必要があります。次のコードはエラーになります

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/

見積もりの​​内側がコメントの開始と見なされるにもかかわらず、スラッシュスター。それゆえ、別の閉鎖星のスラッシュで終わる必要があります。正しい方法は

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/  */

基本的なサーバー情報を取得する

SELECT @@VERSION

インスタンス上で実行されているMS SQL Serverのバージョンを返します。

SELECT @@SERVERNAME

MS SQL Serverインスタンスの名前を返します。

SELECT @@SERVICENAME

MS SQL Serverが実行されているWindowsサービスの名前を返します。

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

SQL Serverが実行されているマシンの物理名を返します。フェールオーバークラスター内のノードを識別するのに便利です。

SELECT * FROM fn_virtualservernodes();

フェールオーバークラスターでは、SQL Serverを実行できるすべてのノードを返します。クラスタでない場合は何も返しません。

トランザクションを使用して安全にデータを変更する

データを変更するたびに、DML（Data Manipulation Language）コマンドで、変更をトランザクションにラップすることができます。 DMLには、 UPDATE 、 TRUNCATE 、 INSERTおよびDELETE含まれます。適切なデータを変更していることを確認できる方法の1つは、トランザクションを使用することです。

DMLの変更は影響を受ける行をロックします。トランザクションを開始するときは、トランザクションを終了する必要があります。そうしないと、DMLで変更されたすべてのオブジェクトは、トランザクションを開始した人によってロックされたままになります。 ROLLBACKまたはCOMMITを使用してトランザクションを終了することができます。 ROLLBACKは、トランザクション内のすべてを元の状態に戻します。 COMMITは、別のDML文を使用せずに変更を取り消すことができない最終状態にデータを置きます。

例：

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction

ノート：

• これは、エラー処理を含まない単純化された例です。しかし、どのデータベース操作も失敗する可能性があり、したがって例外がスローされます。 このような必要なエラー処理がどのように見えるかの例を次に示します。あなたは、そうでない場合は、あなたが未知の状態で取引を残すかもしれません、 エラーハンドラなしでトランザクションを使用しないでください。
• 分離レベルに応じて、トランザクションは照会または変更されているデータにロックをかけています。トランザクションが長期間実行されていないことを保証する必要があります。トランザクションがデータベース内のレコードをロックし、他の並列実行トランザクションとのデッドロックにつながる可能性があるからです。操作をカプセル化したトランザクションを可能な限り短くし、ロックしているデータの量による影響を最小限に抑えます。

すべての行を削除する

DELETE
FROM Helloworlds

これにより、テーブルからすべてのデータが削除されます。このコードを実行すると、テーブルには行が含まれません。 DROP TABLEとは異なり、これはテーブル自体とその構造を保持しており、引き続きそのテーブルに新しい行を挿入することができます。

テーブル内のすべての行を削除する別の方法は、次のように切り捨てます。

TRUNCATE TABLE HelloWords

DELETE操作との違いはいくつかあります：

1. 切り詰め操作はトランザクションログファイルに格納されません
2. IDENTITYフィールドが存在する場合、これはリセットされます
3. TRUNCATEはテーブル全体とその一部には適用できません（代わりにDELETEコマンドでWHERE句を関連付けることができます）

TRUNCATEの制限

1. FOREIGN KEY参照がある場合はテーブルをTRUNCATEできません
2. テーブルがINDEXED VIEW参加している場合
3. TRANSACTIONAL REPLICATIONまたはMERGE REPLICATIONを使用してテーブルが公開されている場合
4. テーブルに定義されているトリガは起動しません

[sic]

TRUNCATE TABLE

TRUNCATE TABLE Helloworlds 

このコードはHelloworldsテーブルからすべてのデータを削除します。切り捨てテーブルはDelete from TableコードからのDelete from Tableとほぼ同じです。違いは、Truncateでwhere句を使用できないことです。 Truncateテーブルは、トランザクションログスペースが少なくて済むため、削除よりも優れていると見なされます。

ID列が存在する場合、ID列は初期シード値にリセットされます（たとえば、自動増分IDは1から再開されます）。これは、ID列が別の表の外部キーとして使用されている場合、矛盾する可能性があります。

新しいテーブルを作成し、古いテーブルからレコードを挿入する

SELECT * INTO NewTable FROM OldTable

古いテーブルの構造を持つ新しいテーブルを作成し、すべての行を新しいテーブルに挿入します。

いくつかの制限

1. テーブル変数またはテーブル値パラメータを新しいテーブルとして指定することはできません。
2. ソース表がパーティション化されている場合でも、SELECT ... INTOを使用してパーティション表を作成することはできません。 SELECT ... INTOはソーステーブルのパーティションスキームを使用しません。代わりに、既定のファイルグループに新しいテーブルが作成されます。パーティション表に行を挿入するには、まずパーティション表を作成してから、INSERT INTO ... SELECT FROMステートメントを使用する必要があります。
3. ソース表で定義された索引、制約およびトリガーは、新しい表には転送されず、SELECT ... INTO文で指定することもできません。これらのオブジェクトが必要な場合は、SELECT ... INTO文を実行した後で作成できます。
4. ORDER BY句を指定しても、行が指定された順序で挿入されることは保証されません。選択リストにスパース列が含まれている場合、スパース列プロパティは新しいテーブルの列には転送されません。新しい表でこのプロパティーが必要な場合は、SELECT ... INTOステートメントを実行した後で列定義を変更し、このプロパティーを組み込みます。
5. 計算列が選択リストに含まれる場合、新しいテーブルの対応する列は計算列ではありません。新しい列の値は、SELECT ... INTOが実行された時点で計算された値です。

[ sic ]

テーブルの行数の取得

次の例を使用して、 table_nameが照会したいテーブルに置き換えられた場合、データベース内の特定のテーブルの合計行数を見つけることができます。

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;

次のスクリプトを使用して、テーブルのHEAP（index_id = 0）またはクラスタのクラスタ化インデックス（index_id = 1）に基づいてテーブルのパーティションに戻って結合することによって、すべてのテーブルの行数を取得することもできます。

SELECT  [Tables].name                AS [TableName],
        SUM( [Partitions].[rows] )    AS [TotalRowCount]
FROM    sys.tables AS [Tables]
JOIN    sys.partitions AS [Partitions]
    ON  [Tables].[object_id]    =    [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
--WHERE    [Tables].name = N'table name' /* uncomment to look for a specific table */
GROUP BY    [Tables].name;

これは、追加のパーティションが追加されない限り、すべてのテーブルが本質的に単一のパーティションテーブルであるため可能です。このスクリプトには、テーブルの行に対する読み取り/書き込み操作を妨げないという利点もあります。