Konzept ansehen

Ansicht ist eine virtuelle Tabelle, die von ausgewählten Spalten in einer oder mehreren Tabellen erstellt wird. Die Tabelle in der generierten Ansicht verweist auf die ursprüngliche Tabelle. Diese Originaltabellen können aus unterschiedlichen Datenbanken stammen. Eine Ansicht kann auch eine Spalte aus einer anderen Ansicht hinzufügen, die in derselben oder sogar einer anderen Datenbank erstellt wurde. Eine Ansicht kann bis zu 1024 Spalten haben. Die aus der Originaltabelle abgerufenen Daten in der Ansicht beziehen sich auf die Ansichtsdefinition. Zeilen und Spalten in der Ansicht werden dynamisch erstellt, wenn auf die Ansicht verwiesen wird.

Einige Situationen, in denen Ansichten als Unternehmen verwendet werden können:

• Sie müssen den gesamten Auftragsumsatz pro Tag erhalten (nehmen Sie nur die Umsatzspalte), um das Berichtsgeschäft zu erfüllen
• Sie müssen eine Tabelle erstellen, die einige Spalten anzeigt, um dem Entwickler Daten zu geben, ohne dass der Entwickler direkt in die Datenbank abfragen kann, um die verbleibenden Spalten sehen zu können, und diese Tabelle zeigt nur die Textdaten und lässt keine Aktualisierungen zu.

In solchen Situationen, wie Situation Nr. 1, verwendet der Datenbankadministrator (DBA) View, um Berichte zu erstellen, anstatt viele Male eine komplexe Select-Anweisung zu schreiben, oder Situation Nr. 2, View stellt sowohl die Sicherheit als auch die Geschäftsperspektive für den DBA sicher.

Ansicht erstellen

Benutzer können Ansichten nur mit Spalten aus Tabellen oder anderen Ansichten erstellen, wenn dem Benutzer Zugriff auf diese Tabellen und Ansichten gewährt wird.

Syntax:

 CREATE VIEW <view_name> AS <select_statement>

Zum Beispiel:

Erstellen Sie eine Ansicht aus der Tabelle Production.Product, um die Produkt-ID, die Produktnummer, den Namen und den Sicherheitsbestand des Produkts anzuzeigen:

 CREATE VIEW vwProductInfo AS SELECt ProductID, ProductNumber,Name,SafetyStockLevel FROM Production.Product; GO

Daten aus der Ansicht abrufen:

 SELECT * FROM vwProductInfo

Erstellen Sie eine Ansicht, die eine JOIN-Klausel kombiniert, um eine Tabelle zu verknüpfen

Jede Ansicht kann Daten vieler verschiedener Tabellen speichern, da sie das Ergebnis einer SELECT-Abfrage sind, das ist der Vorteil, der die Ansicht so leistungsfähig macht.

Das JOIN-Schlüsselwort kann beim Erstellen der Ansicht verwendet werden, es ist im Grunde nur ein Join, um die Tabellen über verwandte Spalten miteinander zu verbinden, nach dem Join wird die Ansicht basierend auf den nach der Operation abgerufenen Daten erstellt.

Syntax:

 CREATE VIEW <view_name> AS SELECT * FROM table_name1 JOIN table_name2 ON table_name1.column_name = table_name2.column_name

Zum Beispiel:

Erstellen Sie eine Ansicht namens vwPersonDetails mit den angegebenen Spalten aus den Tabellen Person und Employee im HumanResources-Schema. Die Schlüsselwörter JOIN und ON werden verwendet, um 2 Tabellen basierend auf der BusinessEntityID-Spalte zu verknüpfen

 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

Im obigen Beispiel gibt es viele Zeilen, die keine Werte in den Titel- und Namensspalten haben und Nullwerte anzeigen. Es ist möglich, dass der Benutzer, der diese Ausgabe sieht, die Bedeutung der NULL-Werte nicht versteht. Um daher alle NULL-Werte in der Ausgabe durch einen leeren String zu ersetzen, kann die Funktion COLALESCE() verwendet werden

 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

Einige Regeln, die bei der Verwendung von Ansichten zu beachten sind:

• Ansichten können nur auf der aktuellen Datenbank erstellt werden. Erst nachdem eine Ansicht erstellt wurde, können Tabellen und Ansichten, die auf dieser Ansicht basieren, von einer anderen Datenbank oder einem anderen Server erstellt werden.
• Der Ansichtsname muss immer eindeutig sein und darf nicht denselben Namen haben wie die Tabelle im Schema.
• Sichten können nicht aus temporären Tabellen erstellt werden.

Ansicht erstellen kombiniert mit Sortieren nach zum Sortieren

Das folgende Beispiel verwendet die TOP -Klausel in Kombination mit ORDER BY , um 10 Mitarbeiter in umgekehrter Reihenfolge basierend auf der FirstName-Spalte abzurufen:

 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

Bearbeiten von Daten über View

Ansichten können verwendet werden, um Daten in einer Tabelle zu bearbeiten, Daten können mit den folgenden Befehlen hinzugefügt, bearbeitet oder gelöscht werden:

• EINFÜGUNG
• AKTUALISIEREN
• LÖSCHEN

INSERT-Ansicht

Die INSERT-Anweisung wird verwendet, um einer Datenbank oder Ansicht eine neue Zeile hinzuzufügen. Wenn während der Befehlsausführung kein Spaltenwert bereitgestellt wird, muss SQL Server-Datenbankmodul einen Wert basierend auf der Definition der Spalte bereitstellen. Wenn die Datenbank-Engine diesen Wert nicht bereitstellt, wird die neue Zeile nicht hinzugefügt.

Werte für automatisch bewertete Spalte:

• Hat das Attribut IDENTITY
• Hat einen definierten Standardwert
• Zeitstempel des Datentyps
• Spalten, die Nullwerte zulassen
• Spalte mit berechnetem Wert

Wenn Sie die INSERT-Anweisung in einer Ansicht verwenden und eine Regel verletzt wird, wird der Datensatz nicht hinzugefügt.

Zum Beispiel:

Schritt 1: Erstellen Sie die Tabelle 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) )

Schritt 2: Erstellen Sie die Tabelle Employee_Salary_Details

 CREATE TABLE Employee_Salary_Details( EmpID int not null, Designation varchar(30), Salary int not null )

Schritt 3: Erstellen Sie die Ansicht „vwEmployee_Details“ mithilfe von Spalten aus den Tabellen „ Employee_Personal_Details “ und „ Employee_Salary_Details “, indem Sie zwei Tabellen über die Spalte „EmpID“ verbinden

 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.EmpID

Verwenden Sie die INSERT-Anweisung, um Daten über die Ansicht vwEmployee_Details in die Tabelle einzufügen. SQL gibt jedoch eine Fehlermeldung aus und die Daten können nicht in die Tabelle aufgenommen werden:

 INSERT INTO vwEmployee_Personal_Details VALUES(2,'Jack','Wilson','Software Developer',16000)

Werte können nur wie folgt zu Spalten mit vordefinierten Benutzerdatentypen hinzugefügt werden:

• Gibt den Wert eines benutzerdefinierten Datentyps an
• Rufen Sie eine benutzerdefinierte Funktion auf, um einen Wert mit einem benutzerdefinierten Datentyp zurückzugeben

Einige Regeln, die beim Einfügen von Daten in die Ansicht zu beachten sind:

• Die INSERT-Anweisung muss den Wert für alle Spalten in der Ansicht in der Tabelle bestimmen, wenn die Tabelle keine Nullen zulässt und es keine DEFAULT-Definition gibt.
• Wenn in derselben Ansicht oder Tabelle ein Self-Join vorhanden ist, funktioniert die INSERT-Anweisung nicht

Das Beispiel erstellt eine Ansicht mit dem Namen vwEmpdetails unter Verwendung der Tabelle Employee_Personal_Details . Die Employee_Personal_Details -Tabelle enthält eine LastName-Spalte, die das Einfügen von Nullwerten nicht zulässt:

Das obige Beispiel gibt beim Einfügen einen Fehler, da es nicht erlaubt ist, Nullwerte für die EmpID-Spalte einzufügen

 CREATE VIEW vwEmpDetails AS SELECT FirstName, Address FROM Employee_Personal_Details GO INSERT INTO vwEmpDetails VALUES('Jack','NYC')

Ansicht aktualisieren

Mit der UPDATE-Anweisung können die Daten in der Ansicht geändert werden. Beim Aktualisieren der Ansicht werden auch die Daten der zugehörigen Tabelle aktualisiert.

Zum Beispiel:

Schritt 1: Erstellen Sie eine Tabelle mit dem Namen 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

Angenommen, Sie fügen der Tabelle einige Werte wie gezeigt hinzu:

Schritt 2: Erstellen Sie eine Ansicht basierend auf der ProductDetails- Tabelle

 CREATE VIEW vwProduct_Details AS SELECT ProductName,Rate FROM Product_Details

Schritt 3: Aktualisieren Sie die Ansicht, um den gesamten DVD-Wert auf 4000 zu ändern

 UPDATE vwProduct_Details SET Rate=3000 WHERE ProductName='DVD Writer'

Diese Datentypen belegen viel Speicher wie nvarchar(max) , varchar(max) , varbinary(max) . Um Daten dieses Typs zu aktualisieren, wird die Klausel .WRITE verwendet. Die Klausel .WRITE gibt die Partition an, in der der Spaltenwert geändert wird. Die .WRITE- Klausel kann nicht verwendet werden, um den NULL-Wert für eine Spalte zu aktualisieren. Daher kann es nicht verwendet werden, um den Spaltenwert auf NULL zu setzen

Syntax:

 column_name .WRITE(expression,@Offeset,@Length)

Angenommen, die Tabelle „Product_Details“ wird so geändert, dass die Spalte „Description“ Daten vom Typ „nvarchar(max) “ enthält.

Die Ansicht wird basierend auf dieser Tabelle erstellt und enthält die Spalten ProductName, Description, Rate

 CREATE VIEW vwProduct_Details AS SELECT ProductName, Description, Rate FROM Product_Details

Das obige Codebeispiel verwendet den UPDATE-Befehl für die vwProduct_Details-Ansicht. Die Klausel .WRITE wird mit den beiden Parametern 0 und 2 verwendet, um den Wert der ersten beiden Zeichen der Spalte Beschreibung zu ändern. Dann ändert sich der Wert 'Internal' zu 'External'

 UPDATE vwProduct_Details SET Description .WRITE(N'Ex',0,2) WHERE ProductName='PortableHardDrive'

Einige Regeln, die bei der Verwendung der UPDATE-Anweisung in der Ansicht zu befolgen sind:

• Der Wert der Spalte mit dem IDENTITY-Attribut kann nicht aktualisiert werden.
• Der Datensatz kann nicht aktualisiert werden, wenn die Tabelle eine Spalte mit dem Wert TIMESTAMP enthält
• Wenn in derselben Ansicht oder Tabelle ein Self-Join vorhanden ist, funktioniert der UPDATE-Befehl nicht.
• Wenn beim Aktualisieren der Zeile die Einschränkung verletzt wird, wird die Anweisung beendet und ein Fehler zurückgegeben, es werden keine Datensätze aktualisiert.

Ansicht LÖSCHEN

SQL Server ermöglicht es Benutzern, Datensätze aus der Ansicht zu löschen. Mit der DELETE-Anweisung können Zeilen aus der VIEW entfernt werden. Wenn ein Datensatz aus der Ansicht entfernt wird, werden die entsprechenden Zeilen aus der Tabelle gelöscht

Beispielsweise gibt es die vwCustDetails-Ansicht, die alle Kontoinformationen von verschiedenen Kunden abruft. Wenn der Kunde das Konto schließt, müssen die Kundendaten gelöscht werden.

Syntax:

 DELETE FROM <view_name> WHERE <search_condition>

Zum Beispiel:

 DELETE FROM vwCustDetails WHERE CustID='C0004'

Ansicht ändern

Neben der Bearbeitung von Daten können Benutzer auch Definitionen innerhalb der Ansicht bearbeiten. Eine Ansicht kann mit dem Befehl ALTER VIEW bearbeitet werden. Der Befehl ALTER VIEW ändert die aktuelle Ansicht, ohne Berechtigungen oder andere Eigenschaften neu zu organisieren.

ALTER VIEW kann auf indizierte Ansichten angewendet werden, entfernt jedoch bedingungslos alle Indizes der Ansicht. Ansichten werden häufig geändert, wenn der Benutzer zusätzliche Informationen anfordert oder Änderungen an der zugrunde liegenden Tabellendefinition vornimmt

 ALTER VIEW <view_name> AS <select_statement>

Zum Beispiel:

 ALTER VIEW vwProductInfo AS SELECT ProductID, ProductNumber,Name, SafetyStockLevel,ReOrderPoint FROM Production.Product; GO

DROP-Ansicht

Ein View kann aus der Datenbank entfernt werden, wenn er nicht benötigt wird, er wird über die DROP VIEW-Anweisung verwendet. Wenn die Ansicht gelöscht wird, sind die Daten in der Haupttabelle nicht betroffen. Die Ansichtsdefinition und ansichtsbezogene Informationen werden aus dem Systemverzeichnis entfernt. Alle Anzeigeberechtigungen werden ebenfalls entfernt. Wenn ein Benutzer eine Ansicht abfragt, die auf eine gelöschte Ansicht verweist, gibt der SQL-Server eine Fehlermeldung zurück.

Syntax:

 DROP VIEW <view_name>

Zum Beispiel:

 DROP VIEW vwProductInfo

Ansichtsdefinition

Die Definition einer Ansicht hilft dem Benutzer zu verstehen, wie seine Daten aus den Quelltabellen abgerufen werden. Es gibt mehrere gespeicherte Prozeduren im System, die beim Abrufen von Ansichtsdefinitionen helfen. Die gespeicherte Prozedur sp_helptext zeigt Informationen zur Ansicht an, wenn der Name der Ansicht als Parameter angegeben wird. Informationen über die Definition einer Ansicht können erhalten werden, wenn die Informationen nicht verschlüsselt sind.

 sp_helptext<view_name>

Zum Beispiel:

 exec sp_helptext vwEmployee_Personal_Details

OPTION PRÜFEN Ansicht

Das Schlüsselwort CHECK OPTION der Ansicht ist optional, wobei es sich um eine Option handelt, die der Anweisung CREATE VIEW zugeordnet ist. Es wird verwendet, um sicherzustellen, dass alle Aktualisierungen in der Ansicht die in der Ansichtsdefinition genannten Bedingungen erfüllen. Wenn die Bedingung nicht erfüllt ist, gibt die Datenbank-Engine einen Fehler zurück. CHECK OPTION wird also verwendet, um die Logik und Integrität der Daten sicherzustellen, es überprüft die Definition der Ansicht, um festzustellen, ob die WHERE-Bedingungen in der SELECT-Anweisung verletzt werden oder nicht.

Erzwingen Sie mit der WITH CHECK OPTION-Klausel, dass alle Modifikatoren für die Ansicht ausgeführt werden, um die in der SELECT-Anweisung festgelegte Bedingung zu erfüllen.

Syntax:

 CREATE VIEW <view_name> AS select_statement [WITH CHECK OPTION]

Beispiel zur Neuerstellung der Ansicht vwProductInfo mit SafetyStockLevel kleiner oder gleich 1000:

 CREATE VIEW vwProductInfo AS SELECT ProductID, ProductNumber, Name, SafetyStockLevel, ReOrderPoint FROM Production.Product WHERE SafetyStockLevel <= 1000 WITH CHECK OPTION; GO

Verwenden Sie im nächsten Schritt die UPDATE-Anweisung zum Bearbeiten der vwProductInfo-Ansicht, indem Sie den Wert der SafetyStockLevel-Spalte für das Produkt mit den IDs 321 und 2500 ändern.

 UPDATE vwProductInfo SET SafetyStockLevel = 2500 WHERE ProductID = 321

Diese Anweisung wird nicht ausgeführt, da sie die WITH CHECK OPTION-Einschränkung verletzt, SafetyStockLevel <= 1000. Daher werden in der vwProductInfo- Ansicht keine Zeilen aktualisiert.

SCHEMABINDUNGSOPTION in Ansicht

Eine Ansicht kann mit der SCHEMABINDING-Option mit dem Schema einer Tabelle verknüpft werden. Diese Option kann mit CREATE VIEW- oder ALTER VIEW-Anweisungen verwendet werden. Wenn SCHEMABIDING verwendet wird, wirken sich die Basistabelle oder -tabellen, die nicht geändert werden können, auf die Ansichtsdefinition aus. Die Ansicht muss zuerst geändert oder gelöscht werden, um die zu ändernden Tabellenabhängigkeiten zu entfernen.

Bei Verwendung von SCHEMABINDING in einer Ansicht muss der Schemaname zusammen mit dem Objektnamen in der SELECT-Anweisung angegeben werden

Syntax:

 CREATE VIEW <view_name> WITH SCHEMABINDING AS <select_statement>

Zum Beispiel:

 CREATE VIEW vwNewProductInfo WITH SCHEMABINDING AS SELECT ProductID,ProductNumber,Name,SafetyStockLevel FROM Production.Product; GO

Verwenden Sie sp_refreshview

Während der Ansichtserstellung können SCHEMABIDING verwendet werden, um Werte aus der Ansicht an das Schema der in der Ansicht hinzugefügten Tabelle zu binden. Ansichten können jedoch erstellt werden, ohne die Option SCHEMABIDING auszuwählen. Wenn in diesen Situationen eine Änderung an dem Objekt auftritt, z. B. einer Tabelle oder Ansicht, von der diese Ansicht abhängt, sollte eine gespeicherte Prozedur namens sp_refreshview ausgeführt werden. Diese gespeicherte Prozedur aktualisiert die Metadaten für die Ansicht. Wenn sie nicht ausgeführt wird, werden die Metadaten der Ansicht nicht entsprechend den Änderungen in der ursprünglichen Tabelle aktualisiert. Die Ergebnismenge gibt einige unerwartete Ergebnisse zurück, wenn die Ansicht angefordert wird.

Syntax:

 sp_refreshview '<view_name>'

Zum Beispiel:

Schritt 1 – Erstellen Sie die Customers-Tabelle

 CREATE TABLE Customers( CustID int, CustName varchar(50), Address varchar(60) )

Schritt 2 – Erstellen Sie eine vwCustomers-Ansicht basierend auf der Customers-Tabelle.

 CREATE VIEW vwCustomers AS SELECT * FROM Customers

Schritt 3 – Wählen Sie die angezeigten Daten aus

 SELECT * FROM vwCustomer

Die obige Ausgabe gibt 3 Spalten CustID, CustName, Address zurück

Schritt 4 – ALTER TABLE und fügen Sie die Spalte Age in der Tabelle Customers hinzu.

 ALTER TABLE Customers ADD Age int

Schritt 5 – Wählen Sie die Daten in der Ansicht erneut aus, aber Sie werden sehen, dass die Altersspalte noch nicht angezeigt wird

 SELECT * FROM vwCustomer

Schritt 6 – Führen Sie die gespeicherte Prozedur sp_refreshview aus, um die Metadaten und die Ausgabe der Ansicht zu aktualisieren:

 EXEC sp_refreshview 'vwCustomers'

Tabellen, die eine Schemaverbindung zu einer Ansicht haben, können nicht gelöscht werden, es sei denn, die Ansicht wird zuerst gelöscht oder die Definition wird in „Keine Schemabindung“ geändert. Wenn die Ansicht nicht gelöscht oder aktualisiert wurde und Sie versuchen, die Tabelle zu löschen, gibt die Datenbank-Engine eine Fehlermeldung zurück.

Ebenso schlägt die Anweisung fehl, wenn eine ALTER TABLE-Anweisung auf eine schemagebundene Ansichtsdefinition angewendet wird.

Zum Beispiel:

 ALTER TABLE Production.Product ALTER COLUMN ProductID varchar(7)

Die Datenbank-Engine gibt einen Fehler zurück, da diese Tabelle mit der Ansicht vwNewProductInfo schemagebunden ist und ihre Änderung daher gegen die Definition der Ansicht verstößt.

Funktionen von Ansicht

Im Allgemeinen bietet die Verwendung von View die folgenden Vorteile:

Über Sicherheit

Sie können Benutzern den direkten Zugriff auf die Tabelle einschränken und ihnen stattdessen den Zugriff über die Ansicht erlauben, damit dies sicherer ist. Wieso den? Da die Ansicht nur eine Ansicht ist, ist es nur erlaubt, die vorhandenen Informationen in der Ansicht zu lesen, keine weiteren Informationen anzuzeigen oder Daten zu ändern.

Sie können Benutzern beispielsweise erlauben, über die Ansicht auf Kundennamen, Telefonnummern und E-Mail-Adressen zuzugreifen, ihnen jedoch den Zugriff auf Bankkonten und andere vertrauliche Informationen verweigern.

Vereinfachen

Wenn Sie eine Abfrage mit vielen Tabellen schreiben, wird es sehr kompliziert, Sie müssen viel testen, um sicher zu sein, dass das Ergebnis korrekt ist, manchmal ist es nicht so. Wenn Sie jedoch Ansichten kombinieren, in viele Segmente aufteilen und jede Ansicht ein Segment ist, ist das Ausführen von Abfragen für die Ansicht viel einfacher zu verstehen.

Konsistenz

Manchmal müssen Sie eine komplexe Formel schreiben, die in vielen Abfragen verwendet wird, und dann müssen Sie sie immer wieder schreiben.

Aber wenn Sie diese Formel in eine Ansicht einfügen, dann beziehen sich andere Abfragen aus der Ansicht darauf, es ist bequem und konsistent, die Formel später zu bearbeiten, bearbeiten Sie sie einfach in der Ansicht.