Transact-SQL 基本

Transact-SQL 基本

• http://msdn.microsoft.com/ja-jp/library/ms365303(v=SQL.90).aspx

[SQL Server][Transact-SQL]

 ステートメントをデータベース エンジンに送信する

Transact-SQL ステートメントは次の方法で作成してデータベース エンジンに送信できる。

• SQL Server Management Studio または、Management Studio Express(無料) を使用
• sqlcmd ユーティリティを使用
• 作成したアプリケーションから接続

デーベースオブジェクトの作成

 データベースを作成

CREATE DATABASE

create database TestData
go

GO キーワードは、複数のステートメントを単一のバッチで送信した場合に、ステートメントを区切る。

 テーブルの作成

USE コマンドを実行して、データベースのコンテキストを TestData データベースに変更する

use TestData
go

テーブルの作成

CREATE TABLE

• Products という名前のテーブルを作成

create table dbo.Products (
    ProductID int primary key not null,
    ProductName varchar(25) not null,
    Price money null,
    ProductDescription text null
)
go

 データの挿入と更新

挿入

INSERT

insert dbo.Products (ProductID, ProductName, Price, ProductDescription)
    values(1, 'Clamp', 12.48, 'Workbentch clamp')
go

更新

UPDATE

update dbo.Products
    set ProductName = 'Flat Head Screwdriver'
    where ProductID = 1
go

 データの読み取り

SELECT

select * from dbo.Products
go

データベース オブジェクトに対する権限の構成

 ログインの作成

• データベース エンジンにアクセスするには、ユーザーのログインが必要

ログイン

1. ユーザーの ID を Windows のアカウントまたは Windows グループのメンバとして表す
2. SQL Server のみに存在する SQL Server ログインを使用

既定では、コンピュータの管理者には SQL Server へのフル アクセス権が与えられる

新しい Windows アカウントを作成

ユーザー Yagi を作成

これだけではログインできない

• ログインできませんでした(Microsoft SQL Server、エラー:18456)

ログインを作成

CREATE LOGIN

create login [ComputerName\Yagi]
    from windows
    with default_database = [TestData];
go

エラーメッセージが変わった

• 規定のデータベースを開けません(Microsoft SQL Server、エラー:4064)

データベースへのアクセス権の付与

• TestData データベースに切り替えてから CREATE USER ステートメントを使用して、ユーザーにそのログインをマップする。

USE

use [TestData];
go

CREATE USER

create user [Yagi] for login [ComputerName\Yagi];
go

これで、SQL Server のインスタンスと、データベースにアクセスできるようになる

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

[SQL Server ストアド プロシージャ]

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

 ビューを作成

CREATE VIEW

create view vw_Names
    as
       select ProductName, Price from Products;
go

 ストアド プロシージャ

作成

CREATE PROCEDURE

PRINT]

create procedure pr_Names @VarPrice money
    as 
        begin
            -- ユーザー定義メッセージをクライアントに返す
            print 'Product less than ' + cast(@VarPrice as varchar(10));
            -- 実際に行いたい処理
            select ProductName, Price from vw_Names
                where Price < @varPrice;
        end
go

テスト

EXECUTE

execute pr_Names 100.00;
go

結果

• print ステートメントの内容が出力される

 データベース オブジェクトへのアクセス権の付与

• 管理者は、上記で作成したビューとストアドプロシージャにアクセスできるが、追加したユーザーはアクセスできない
• 必要な権限を付与するには、GRANT ステートメントを使用

ストアドプロシージャへのアクセス権を付与

GRANT

grant execute on pr_Names to Yagi;
go

テーブルへのSELECT権限を付与

GRANT

grant select on dbo.Products to Yagi;
go

 データベース オブジェクトの削除

ストアド プロシージャに対する実行権限を削除

REVOKE

revoke execute on pr_Names from Yagi;
go

テーブルのSELECT権限を削除

REVOKE

revoke select on dbo.Products from Yagi;
go

データベースに対するアクセス権を削除

DROP USER

drop user Yagi;
go

インスタンスに対するアクセス権を削除

DROP LOGIN

drop login [ComputerName\Yagi];
go