SQLServerでのVIEW
Mục lục
コンセプトを見る
ビューは、1つ以上のテーブルの選択された列によって作成された仮想テーブルです。生成されたビューのテーブルは、元のテーブルを参照します。これらの元のテーブルは、異なるデータベースからのものである可能性があります。ビューは、同じデータベースまたは異なるデータベースで作成された別のビューから列を追加することもできます。ビューには最大1024列を含めることができます。元のテーブルから取得されたビュー内のデータは、ビュー定義を参照します。ビューの行と列は、ビューが参照されるときに動的に作成されます。
ビューをビジネスとして使用できるいくつかの状況：
- レポートビジネスを満たすには、1日あたりの合計注文収益を取得する必要があります（収益列のみを取得します）。
- 残りの列を表示できるように、開発者がデータベースに直接クエリを実行せずに、開発者にデータを提供するためにいくつかの列を表示するテーブルを作成する必要があります。このテーブルにはテキストデータのみが表示され、更新は許可されません。新規追加。
このような状況では、状況＃1のように、データベース管理者（DBA）は、複雑なselectステートメントを何度も作成する代わりに、ビューを使用してレポートビジネスを実行します。状況＃2の場合、ビューはDBAのセキュリティとビジネスの両方の観点を保証します。
ビューの作成
ユーザーは、テーブルまたは他のビューへのアクセスが許可されている場合にのみ、テーブルまたは他のビューの列を使用してビューを作成できます。
構文：
CREATE VIEW <view_name> AS <select_statement>例えば：
Production.Productテーブルからビューを作成して、製品の製品ID、製品番号、名前、安全在庫を表示します。
CREATE VIEW vwProductInfo AS SELECt ProductID, ProductNumber,Name,SafetyStockLevel FROM Production.Product; GO 
ビューからデータを取得します。
SELECT * FROM vwProductInfo 
JOIN句を組み合わせてテーブルを結合するビューを作成します
各ビューは、SELECTクエリの結果であるため、さまざまなテーブルのデータを格納できます。これは、ビューを非常に強力にする利点です。
JOINキーワードは、ビューの作成時に使用できます。基本的には、関連する列を介してテーブルを結合するための単なる結合です。結合後、操作後に取得したデータに基づいてビューが作成されます。connect。
構文：
CREATE VIEW <view_name> AS SELECT * FROM table_name1 JOIN table_name2 ON table_name1.column_name = table_name2.column_name例えば：
HumanResourcesスキーマのPersonテーブルとEmployeeテーブルから指定された列を使用して、vwPersonDetailsという名前のビューを作成します。 JOINおよびONキーワードは、BusinessEntityID列に基づいて2つのテーブルを結合するために使用されます
CREATE VIEW vwPersonDetails AS SELECT p.Title, p.[FirstName], p.[MiddleName], p.[LastName], e.[JobTitle] FROM [humanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]; GO SELECT * FROM vwPersonDetails
上記の例では、タイトル列と名前列に値がなく、null値を表示する行が多数あります。この出力を見ているユーザーは、NULL値の意味を理解していない可能性があります。したがって、出力内のすべてのNULL値を空の文字列に置き換えるには、COLALESCE（）関数を使用できます
CREATE VIEW vwPersonDetails AS SELECT COALESCE(p.Title,'') AS Title, p.[FirstName], COALESCE(p.[MiddleName],'') AS MiddleName, p.[LastName], e.[JobTitle] FROM [humanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]; GO SELECT * FROM vwPersonDetails
ビューを使用するときに従うべきいくつかのルール：
- ビューは、現在のデータベースでのみ作成できます。ビューが作成された後でのみ、そのビューに基づくテーブルとビューを別のデータベースまたはサーバーから作成できます。
- ビュー名は常に一意である必要があり、スキーマのテーブルと同じ名前にすることはできません。
- 一時テーブルからビューを作成することはできません。
ビューを作成して[並べ替え]と組み合わせて並べ替える
以下の例では、 TOP句をORDER BYと組み合わせて使用して、FirstName列に基づいて10人の従業員を逆に取得します。
CREATE VIEW vwSortedPersonDetails AS SELECT TOP 10 COALESCE (p.title,' ') AS Title, p.[FirstName] ,COALESCE(p.MiddleName,' ') AS MiddleName ,p.[LastName] ,e.[JobTitle] FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] ORDER BY p.FirstName DESC GO SELECT * FROM vwSortedPersonDetails 
ビューを介したデータの編集
ビューを使用してテーブル内のデータを編集できます。データは、次のコマンドを使用して追加、編集、または削除できます。
- 入れる
- アップデート
- 消去
ビューを挿入
INSERTステートメントは、コマンドの実行中にデータベースまたはビューに新しい行を追加するために使用されます。列の値が指定されていない場合、SQLServerデータベースエンジンは列の定義に基づいて値を指定する必要があります。データベースエンジンがこの値を提供できない場合、新しい行は追加されません。
自動値列の値：
- IDENTITY。属性があります
- デフォルト値が定義されています
- データ型のタイムスタンプ
- null値を許可する列
- 計算値のある列
ビューでINSERTステートメントを使用する場合、いずれかのルールに違反すると、レコードは追加されません。
例えば：
ステップ1：テーブルEmployee_Personal_detailsを作成します
CREATE TABLE Employee_Personal_Details( EmpID int NOT NULL, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL, Address varchar(30) )ステップ2：テーブルEmployee_Salary_Detailsを作成します
CREATE TABLE Employee_Salary_Details( EmpID int not null, Designation varchar(30), Salary int not null )ステップ3： EmpID列を介して2つのテーブルを結合することにより、 Employee_Personal_DetailsテーブルとEmployee_Salary_Detailsテーブルの列を使用してvwEmployee_Detailsビューを作成します
CREATE VIEW vwEmployee_Personal_Details AS SELECT e1.EmpID, FirstName, LastName, Designation, Salary FROM Employee_Personal_Details e1 JOIN Employee_Salary_Details e2 ON e1.EmpID = e2.EmpIDINSERTステートメントを使用して、 vwEmployee_Detailsビューを介してデータをテーブルに配置します。ただし、SQLはエラーメッセージを表示し、データをテーブルに含めることはできません。
INSERT INTO vwEmployee_Personal_Details VALUES(2,'Jack','Wilson','Software Developer',16000) 
値は、次のように事前定義されたユーザーデータ型を持つ列にのみ追加できます：
- ユーザー定義のデータ型の値を指定します
- ユーザー定義関数を呼び出して、ユーザー定義データ型の値を返します
ビューにデータを挿入するときに従うべきいくつかのルール：
- テーブルでNULLが許可されておらず、DEFAULT定義がない場合、INSERTステートメントはテーブル内のビューのすべての列の値を決定する必要があります。
- 同じビューまたはテーブルに自己結合がある場合、INSERTステートメントは機能しません
この例では、 Employee_Personal_Detailsテーブルを使用してvwEmpdetailsという名前のビューを作成します。 Employee_Personal_Detailsテーブルには、null値の挿入を許可しないLastName列が含まれています。
上記の挿入時の例では、EmpID列にnull値を挿入することが許可されていないため、エラーが発生します
CREATE VIEW vwEmpDetails AS SELECT FirstName, Address FROM Employee_Personal_Details GO INSERT INTO vwEmpDetails VALUES('Jack','NYC')
ビューを更新
UPDATEステートメントを使用して、ビュー内のデータを変更できます。ビューを更新すると、関連するテーブルのデータも更新されます。
例えば：
ステップ1： Product_Detailsという名前のテーブルを作成します
CREATE TABLE Product_Details( ProductID int, ProductName varchar(30), Rate money ) GO INSERT INTO Product_Details VALUES (1,'DVD Writer',1250), (2,'DVD Writer',2250), (3,'DVD Writer',1250),(4,'External Hard Drive',2250), (5,'External Hard Drive',2250), (6,'External Hard Drive',2250); GO次のように、テーブルにいくつかの値を追加するとします。
ステップ2： ProductDetailsテーブルに基づいてビューを作成します
CREATE VIEW vwProduct_Details AS SELECT ProductName,Rate FROM Product_Detailsステップ3：ビューを更新して、DVD全体の値を4000に変更します
UPDATE vwProduct_Details SET Rate=3000 WHERE ProductName='DVD Writer'
これらのデータ型は、 nvarchar（max） 、 varchar（max） 、 varbinary（max）のように大きなメモリサイズを占有します。このタイプのデータを更新するには、 .WRITE句を使用します。 .WRITE句は、列の値が変更されるパーティションを指定します。 .WRITE句を使用して、列のNULL値を更新することはできません。したがって、列の値をNULLに設定するために使用することはできません。
構文：
column_name .WRITE(expression,@Offeset,@Length)[説明]列にタイプnvarchar（max）のデータが含まれるように、Product_Detailsテーブルが変更されていると想定します。
ビューはこのテーブルに基づいて作成され、ProductName、Description、Rateの列があります
CREATE VIEW vwProduct_Details AS SELECT ProductName, Description, Rate FROM Product_Details上記のコード例では、vwProduct_DetailsビューにUPDATEコマンドを使用しています。 .WRITE句は、説明列の最初の2文字の値を変更するために、2つのパラメーター0および2とともに使用されます。次に、値「内部」が「外部」に変更されます
UPDATE vwProduct_Details SET Description .WRITE(N'Ex',0,2) WHERE ProductName='PortableHardDrive'
ビューでUPDATEステートメントを使用するときに従うべきいくつかのルール：
- IDENTITY属性を持つ列の値は更新できません。
- テーブルに値TIMESTAMPの列が含まれている場合、レコードを更新できません。
- 同じビューまたはテーブルに自己結合がある場合、UPDATEコマンドは機能しません。
- 行の更新中に制約に違反した場合、ステートメントは終了し、エラーが返され、レコードは更新されません。
ビューを削除
SQL Serverを使用すると、ユーザーはビューからレコードを削除できます。行は、DELETEステートメントを使用してVIEWから削除できます。ビューからレコードが削除されると、対応する行がテーブルから削除されます
たとえば、vwCustDetailsビューがあり、さまざまな顧客からすべてのアカウント情報を取得します。顧客がアカウントを閉鎖するとき、顧客の詳細を削除する必要があります。
構文：
DELETE FROM <view_name> WHERE <search_condition>例えば：
DELETE FROM vwCustDetails WHERE CustID='C0004' 
ビューの変更
データの編集に加えて、ユーザーはビュー内の定義を編集することもできます。ビューは、ALTERVIEWコマンドを使用して編集できます。 ALTER VIEWコマンドは、権限やその他のプロパティを再編成せずに現在のビューを変更します。
ALTER VIEWはインデックス付きビューに適用できますが、ビューのすべてのインデックスを無条件に削除します。ユーザーが追加情報を要求したり、基になるテーブル定義を変更したりすると、ビューが変更されることがよくあります。
ALTER VIEW <view_name> AS <select_statement>例えば：
ALTER VIEW vwProductInfo AS SELECT ProductID, ProductNumber,Name, SafetyStockLevel,ReOrderPoint FROM Production.Product; GOドロップビュー
ビューは、不要な場合はデータベースから削除できます。ビューはDROPVIEWステートメントを介して使用されます。ビューが削除されると、メインテーブルのデータは影響を受けません。ビュー定義とビュー関連情報は、システムディレクトリから削除されます。すべての表示権限も削除されます。ユーザーが削除されたビューを参照するビューを照会すると、SQLサーバーはエラーメッセージを返します。
構文：
DROP VIEW <view_name>例えば：
DROP VIEW vwProductInfoビューの定義
ビューの定義は、ユーザーがそのデータがソーステーブルからどのように取得されるかを理解するのに役立ちます。システムには、ビュー定義の取得に役立ついくつかのストアドプロシージャがあります。 sp_helptextストアドプロシージャは、ビューの名前がパラメータとして指定されている場合、ビューに関連する情報を表示します。ビューの定義に関する情報は、情報が暗号化されていない場合に取得できます。
sp_helptext<view_name>例えば：
exec sp_helptext vwEmployee_Personal_Details 
オプションの確認ビュー
ビューのCHECKOPTIONキーワードはオプションであり、CREATEVIEWステートメントに関連付けられたオプションです。これは、ビュー内のすべての更新がビュー定義に記載されている条件を満たすことを保証するために使用されます。条件が満たされない場合、データベースエンジンはエラーを返します。したがって、CHECK OPTIONは、データのロジックと整合性を保証するために使用されます。ビューの定義をチェックして、SELECTステートメントのWHERE条件に違反していないかどうかを確認します。
WITH CHECK OPTION句を使用すると、SELECTステートメントで設定された条件に準拠するように、ビューに対してすべての修飾子が強制的に実行されます。
構文：
CREATE VIEW <view_name> AS select_statement [WITH CHECK OPTION]SafetyStockLevelが1000以下のビューvwProductInfoを再作成する例：
CREATE VIEW vwProductInfo AS SELECT ProductID, ProductNumber, Name, SafetyStockLevel, ReOrderPoint FROM Production.Product WHERE SafetyStockLevel <= 1000 WITH CHECK OPTION; GO次のステップでは、ID 321および2500の製品のSafetyStockLevel列の値を変更して、vwProductInfoビューを編集するために使用されるUPDATEステートメントを使用します。
UPDATE vwProductInfo SET SafetyStockLevel = 2500 WHERE ProductID = 321このステートメントは、 WITH CHECK OPTION制約、 SafetyStockLevel <= 1000に違反しているため、実行されません。したがって、 vwProductInfoビューで行は更新されません。
ビュー内のスキーマバインドオプション
ビューは、SCHEMABINDINGオプションを使用してテーブルのスキーマにリンクできます。このオプションは、CREATEVIEWまたはALTERVIEWステートメントで使用できます。 SCHEMABIDINGが使用されている場合、変更できないベーステーブルがビュー定義に影響します。変更するテーブルの依存関係を削除するには、最初にビューを変更または削除する必要があります。
ビューでSCHEMABINDINGを使用する場合は、SELECTステートメントでスキーマ名をオブジェクト名と一緒に指定する必要があります。
構文：
CREATE VIEW <view_name> WITH SCHEMABINDING AS <select_statement>例えば：
CREATE VIEW vwNewProductInfo WITH SCHEMABINDING AS SELECT ProductID,ProductNumber,Name,SafetyStockLevel FROM Production.Product; GOsp_refreshviewの使用
ビューの作成中に、SCHEMABIDINGを使用して、ビューからビューに追加されたテーブルのスキーマに値をバインドできます。ただし、ビューはSCHEMABIDINGオプションを選択せずに作成できます。このような状況で、このビューが依存するテーブルやビューなどのオブジェクトに変更が発生した場合は、sp_refreshviewという名前のストアドプロシージャを実行する必要があります。このストアドプロシージャは、ビューのメタデータを更新します。実行されない場合、ビューのメタデータは元のテーブルの変更に応じて更新されません。ビューが要求されると、結果セットは予期しない結果を返します。
構文：
sp_refreshview '<view_name>'例えば：
ステップ1-Customers.tableを作成します
CREATE TABLE Customers( CustID int, CustName varchar(50), Address varchar(60) )ステップ2-Customersテーブルに基づいてvwCustomersビューを作成します。
CREATE VIEW vwCustomers AS SELECT * FROM Customersステップ3-ビュー内のデータを選択します
SELECT * FROM vwCustomer上記の出力は、CustID、CustName、Addressの3つの列を返します。
ステップ4-ALTERTABLEを実行し、CustomersテーブルにAge列を追加します。
ALTER TABLE Customers ADD Age intステップ5-ビューでデータを再選択しますが、[年齢]列がまだ表示されていないことがわかります
SELECT * FROM vwCustomer手順6-sp_refreshviewストアドプロシージャを実行して、ビューのメタデータと出力を更新します。
EXEC sp_refreshview 'vwCustomers'ビューへのスキーマ接続があるテーブルは、ビューが最初にドロップされるか、定義がスキーマビディングなしに変更されない限り、ドロップできません。ビューが削除または更新されておらず、テーブルを削除しようとすると、データベースエンジンはエラーメッセージを返します。
同様に、ALTER TABLEステートメントがスキーマにバインドされたビュー定義に適用されると、ステートメントは失敗します。
例えば：
ALTER TABLE Production.Product ALTER COLUMN ProductID varchar(7)このテーブルはビューvwNewProductInfoにスキーマバインドされているため、データベースエンジンはエラーを返します。したがって、このテーブルを変更すると、ビューの定義に違反します。
ビューの機能
一般に、Viewを使用する場合、次の利点があります。
セキュリティについて
ユーザーがテーブルに直接アクセスするように制限することができます。代わりに、ビューを介してテーブルにアクセスできるようにすることで、より安全になります。なんで？ビューは単なるビューであるため、ビュー内の既存の情報の読み取りのみが許可され、詳細情報の表示やデータの変更は許可されません。
たとえば、ユーザーがビューを介して顧客名、電話番号、電子メールにアクセスすることを許可し、銀行口座やその他の機密情報へのアクセスを制限することができます。
簡素化する
多くのテーブルを使用してクエリを作成する場合、非常に複雑になります。結果が正しいことを確認するために多くのテストを行う必要がありますが、正しくない場合もあります。ただし、ビューを組み合わせると、多くのセグメントに分割され、各ビューがセグメントになるため、ビューでクエリを実行する方がはるかに理解しやすくなります。
一貫性
多くのクエリで使用される複雑な数式を作成する必要がある場合があります。その時点で、何度も何度も作成する必要があります。
ただし、その数式をビューに配置すると、他のクエリがビューからその数式を参照するため、後で数式を編集するだけで、ビューで編集するだけで便利で一貫性があります。
Bài viết liên quan:
Dịch vụ thiết kế Wesbite
