Visualizza il concetto

La vista è una tabella virtuale creata dalle colonne selezionate in una o più tabelle. La tabella nella vista generata fa riferimento alla tabella originale. Queste tabelle originali possono provenire da database diversi. Una vista può anche aggiungere una colonna da un'altra vista creata nello stesso database o anche in un database diverso. Una vista può avere fino a 1024 colonne. I dati all'interno della vista recuperati dalla tabella originale faranno riferimento alla definizione della vista Le righe e le colonne nella vista verranno create dinamicamente quando si fa riferimento alla vista.

Alcune situazioni in cui le viste possono essere utilizzate come attività:

• Necessità di ottenere le entrate totali degli ordini al giorno (prendere solo la colonna delle entrate) per soddisfare l'attività di reporting
• È necessario creare una tabella che mostri alcune colonne per fornire dati allo sviluppatore senza consentire allo sviluppatore di interrogare direttamente nel database per poter vedere le colonne rimanenti e questa tabella mostra solo i dati di testo e non consente gli aggiornamenti. aggiungi nuovo.

In tali situazioni, come la situazione n. 1, l'amministratore del database (DBA) utilizzerà la visualizzazione per fare affari di reporting invece di scrivere molte volte una complessa dichiarazione di selezione, o la situazione n. 2, la visualizzazione garantirà sia la sicurezza che la prospettiva aziendale per DBA.

Crea vista

Gli utenti possono creare viste utilizzando colonne di tabelle o altre viste solo se all'utente è concesso l'accesso a queste tabelle e viste.

Sintassi:

 CREATE VIEW <view_name> AS <select_statement>

Per esempio:

Crea una vista dalla tabella Produzione.Prodotto per visualizzare l'ID prodotto, il codice prodotto, il nome, lo stock di sicurezza del prodotto:

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

Ottieni dati dalla vista:

 SELECT * FROM vwProductInfo

Crea una vista che combina una clausola JOIN per unire una tabella

Ogni vista può memorizzare i dati di molte tabelle diverse perché è il risultato di una query SELECT, questo è il vantaggio che rende la vista così potente.

La parola chiave JOIN può essere utilizzata durante la creazione della vista, è fondamentalmente solo un join per unire le tabelle tra loro tramite colonne correlate, dopo l'unione, la vista viene creata in base ai dati recuperati dopo l'operazione.connect.

Sintassi:

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

Per esempio:

Creare una vista denominata vwPersonDetails con le colonne specificate dalle tabelle Persona e Dipendente nello schema HumanResources. Le parole chiave JOIN e ON vengono utilizzate per unire 2 tabelle in base alla colonna BusinessEntityID

 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

Nell'esempio sopra, ci sono molte righe che non hanno valori nelle colonne del titolo e del nome e mostrano valori nulli. È possibile che l'utente che vede questo output non comprenda il significato dei valori NULL. Pertanto, per sostituire tutti i valori NULL nell'output con una stringa vuota, è possibile utilizzare la funzione 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

Alcune regole da seguire quando si utilizzano le viste:

• Le viste possono essere create solo sul database corrente. Solo dopo la creazione di una vista è possibile creare tabelle e viste basate su tale vista da un altro database o server.
• Il nome della vista deve essere sempre univoco e non può avere lo stesso nome della tabella nello schema.
• Non è possibile creare viste da tabelle temporanee.

crea vista combinata con Ordina per per ordinare

L'esempio seguente utilizza la clausola TOP in combinazione con ORDER BY per recuperare 10 dipendenti al contrario in base alla colonna FirstName:

 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

Modifica dei dati tramite Visualizza

Le viste possono essere utilizzate per modificare i dati in una tabella, i dati possono essere aggiunti, modificati o eliminati tramite i seguenti comandi:

• INSERIRE
• AGGIORNARE
• ELIMINA

INSERIRE la vista

L' istruzione INSERT viene utilizzata per aggiungere una nuova riga a un database o a una vista, durante l'esecuzione del comando, se non viene fornito un valore di colonna, Motore di database di SQL ServerSQL Server Database Engine dovrà fornire un valore basato sulla definizione della colonna. Se Motore di database non fornisce questo valore, la nuova riga non verrà aggiunta.

Valori per la colonna con valore automatico:

• Ha l'attributo IDENTITY
• Ha un valore predefinito definito
• Data e ora del tipo di dati
• Colonne che consentono valori Null
• Colonna con valore calcolato

Quando si utilizza l'istruzione INSERT in una vista, se viene violata una regola, il record non verrà aggiunto.

Per esempio:

Passaggio 1: crea la tabella 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) )

Passaggio 2: crea la tabella Employee_Salary_Details

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

Passaggio 3: crea la vista vwEmployee_Details utilizzando le colonne delle tabelle Employee_Personal_Details e Employee_Salary_Details , unendo 2 tabelle tramite la colonna EmpID

 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

Utilizzare l'istruzione INSERT per inserire i dati nella tabella tramite la vista vwEmployee_Details . Tuttavia, SQL fornisce un messaggio di errore e i dati non possono essere inclusi nella tabella:

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

I valori possono essere aggiunti solo alle colonne con tipi di dati utente predefiniti come segue:

• Specifica il valore di un tipo di dati definito dall'utente
• Chiamare una funzione definita dall'utente per restituire un valore con un tipo di dati definito dall'utente

Alcune regole da seguire quando si inseriscono i dati nella vista:

• L'istruzione INSERT deve determinare il valore per tutte le colonne nella vista nella tabella se la tabella non consente valori null e non esiste una definizione DEFAULT.
• Quando è presente un'unione automatica nella stessa vista o tabella, l'istruzione INSERT non funzionerà

L'esempio crea una vista denominata vwEmpdetails utilizzando la tabella Employee_Personal_Details . La tabella Employee_Personal_Details contiene una colonna LastName che non consente l'inserimento di valori null:

L'esempio sopra durante l'inserimento darà un errore perché non è consentito inserire valori null per la colonna EmpID

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

Aggiorna vista

L'istruzione UPDATE può essere utilizzata per modificare i dati nella vista. L'aggiornamento della vista aggiornerà anche i dati della tabella correlata.

Per esempio:

Passaggio 1: crea una tabella denominata 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

Supponiamo di aggiungere alcuni valori alla tabella come mostrato:

Passaggio 2: crea una vista basata sulla tabella ProductDetails

 CREATE VIEW vwProduct_Details AS SELECT ProductName,Rate FROM Product_Details

Passaggio 3: aggiorna la visualizzazione per modificare l'intero valore del DVD su 4000

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

Questi tipi di dati occupano grandi dimensioni di memoria come nvarchar(max) , varchar(max) , varbinary(max) . Per aggiornare dati di questo tipo viene utilizzata la clausola .WRITE . La clausola .WRITE specifica la partizione in cui verrà modificato il valore della colonna. La clausola .WRITE non può essere utilizzata per aggiornare il valore NULL per una colonna. Pertanto, non può essere utilizzato per impostare il valore della colonna su NULL

Sintassi:

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

Si supponga che la tabella Product_Details sia modificata in modo che la colonna Descrizione abbia dati di tipo nvarchar(max)

La vista viene creata in base a questa tabella, ha le colonne ProductName,Description, Rate

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

L'esempio di codice precedente utilizza il comando UPDATE per la vista vwProduct_Details. La clausola .WRITE verrà utilizzata con 2 parametri 0 e 2 per modificare il valore dei primi 2 caratteri della colonna Descrizione. Quindi il valore "Interno" cambierà in "Esterno"

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

Alcune regole da seguire quando si utilizza l'istruzione UPDATE nella vista:

• Impossibile aggiornare il valore della colonna con l'attributo IDENTITY.
• Il record non può essere aggiornato se la tabella contiene una colonna con il valore TIMESTAMP
• Quando è presente un collegamento automatico nella stessa vista o tabella, il comando UPDATE non funziona.
• Durante l'aggiornamento della riga, se il vincolo viene violato, l'istruzione viene terminata e viene restituito un errore, nessun record viene aggiornato.

CANCELLA Visualizza

SQL Server consente agli utenti di eliminare i record dalla visualizzazione. Le righe possono essere rimosse da VIEW utilizzando l'istruzione DELETE. Quando un record viene rimosso dalla vista, le righe corrispondenti vengono eliminate dalla tabella

Ad esempio, esiste la vista vwCustDetails, che recupererà tutte le informazioni sull'account da diversi clienti. Quando il cliente chiude l'account, i dettagli del cliente devono essere eliminati.

Sintassi:

 DELETE FROM <view_name> WHERE <search_condition>

Per esempio:

 DELETE FROM vwCustDetails WHERE CustID='C0004'

Modifica vista

Oltre a modificare i dati, gli utenti possono anche modificare le definizioni all'interno della vista. Una vista può essere modificata tramite il comando ALTER VIEW. Il comando ALTER VIEW modifica la vista corrente senza riorganizzare i permessi o altre proprietà.

ALTER VIEW può essere applicato alle viste indicizzate, tuttavia rimuoverà tutti gli indici della vista incondizionatamente. Le visualizzazioni vengono spesso modificate quando l'utente richiede informazioni aggiuntive o apporta modifiche alla definizione della tabella sottostante

 ALTER VIEW <view_name> AS <select_statement>

Per esempio:

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

Vista a GOCCIA

Una vista può essere rimossa dal database se non è necessaria, viene utilizzata tramite l'istruzione DROP VIEW. Una volta eliminata la vista, i dati nella tabella principale non vengono interessati. La definizione della vista e le informazioni relative alla vista verranno rimosse dalla directory di sistema. Anche tutte le autorizzazioni di visualizzazione verranno rimosse. Se un utente interroga una vista che fa riferimento a una vista eliminata, il server sql restituirà un messaggio di errore.

Sintassi:

 DROP VIEW <view_name>

Per esempio:

 DROP VIEW vwProductInfo

Visualizza definizione

La definizione di una vista aiuta l'utente a capire come i suoi dati vengono recuperati dalle tabelle di origine. Nel sistema sono presenti diverse procedure memorizzate che aiutano a recuperare le definizioni delle viste. La stored procedure sp_helptext visualizza le informazioni relative alla visualizzazione quando il nome della visualizzazione viene fornito come parametro. È possibile ottenere informazioni sulla definizione di una vista se le informazioni non sono crittografate.

 sp_helptext<view_name>

Per esempio:

 exec sp_helptext vwEmployee_Personal_Details

CONTROLLA OPZIONE Visualizza

La parola chiave CHECK OPTION della vista è facoltativa, che è un'opzione associata all'istruzione CREATE VIEW. Viene utilizzato per garantire che tutti gli aggiornamenti nella vista soddisfino le condizioni menzionate nella definizione della vista. Se la condizione non è soddisfatta, il motore di database restituirà un errore. Quindi, CHECK OPTION viene utilizzato per garantire la logica e l'integrità dei dati, verificherà la definizione della vista per vedere se le condizioni WHERE nell'istruzione SELECT vengono violate o meno.

Con la clausola WITH CHECK OPTION, imporre l'esecuzione di tutti i modificatori rispetto alla vista per soddisfare la condizione impostata nell'istruzione SELECT.

Sintassi:

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

Esempio per ricreare la vista vwProductInfo con SafetyStockLevel minore o uguale a 1000:

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

Passaggio successivo, utilizzare l'istruzione UPDATE utilizzata per modificare la vista vwProductInfo modificando il valore della colonna SafetyStockLevel per il prodotto con ID 321 e 2500.

 UPDATE vwProductInfo SET SafetyStockLevel = 2500 WHERE ProductID = 321

Questa istruzione non verrà eseguita perché viola il vincolo WITH CHECK OPTION , SafetyStockLevel <= 1000. Pertanto, nessuna riga viene aggiornata nella vista vwProductInfo .

OPZIONE COLLEGAMENTO SCHEMA in vista

È possibile collegare una vista allo schema di una tabella utilizzando l'opzione SCHEMABINDING. Questa opzione può essere utilizzata con le istruzioni CREATE VIEW o ALTER VIEW. Quando si utilizza SCHEMABIDING, la tabella o le tabelle di base che non possono essere modificate influiranno sulla definizione della vista. La vista deve essere prima modificata o eliminata per rimuovere le dipendenze della tabella da modificare.

Quando si utilizza SCHEMABINDING in una vista, è necessario specificare il nome dello schema insieme al nome dell'oggetto nell'istruzione SELECT

Sintassi:

 CREATE VIEW <view_name> WITH SCHEMABINDING AS <select_statement>

Per esempio:

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

Utilizzo di sp_refreshview

Durante la creazione della vista, SCHEMABIDING può essere utilizzato per associare i valori dalla vista allo schema della tabella aggiunto nella vista. Tuttavia, è possibile creare viste senza selezionare l'opzione SCHEMABIDING. In queste situazioni, se si verifica una modifica all'oggetto, ad esempio una tabella o una vista da cui dipende questa vista, è necessario eseguire una stored procedure denominata sp_refreshview. Questa procedura memorizzata aggiorna i metadati per la vista, se non viene eseguita, i metadati della vista non verranno aggiornati in base alle modifiche nella tabella originale. Il set di risultati restituirà alcuni risultati imprevisti quando viene richiesta la visualizzazione.

Sintassi:

 sp_refreshview '<view_name>'

Per esempio:

Passaggio 1: creare la tabella Clienti

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

Passaggio 2: crea una vista vwCustomers basata sulla tabella Clienti.

 CREATE VIEW vwCustomers AS SELECT * FROM Customers

Passaggio 3: selezionare i dati in vista

 SELECT * FROM vwCustomer

L'output precedente restituirà 3 colonne CustID, CustName, Address

Passaggio 4 – ALTER TABLE e aggiungi la colonna Età nella tabella Clienti.

 ALTER TABLE Customers ADD Age int

Passaggio 5: riseleziona i dati nella vista, ma vedrai che la colonna Età non è ancora visualizzata

 SELECT * FROM vwCustomer

Passaggio 6: eseguire la stored procedure sp_refreshview per aggiornare i metadati e l'output della vista:

 EXEC sp_refreshview 'vwCustomers'

Le tabelle che hanno una connessione di schema a una vista non possono essere eliminate a meno che la vista non venga prima eliminata o non modifichi la definizione in nessuna disposizione dello schema. Se la vista non è stata eliminata o aggiornata e si tenta di eliminare la tabella, Motore di database restituirà un messaggio di errore.

Allo stesso modo, quando un'istruzione ALTER TABLE viene applicata a una definizione di vista associata a uno schema, l'istruzione avrà esito negativo.

Per esempio:

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

Il Motore di database restituirà un errore perché questa tabella è associata a uno schema con la vista vwNewProductInfo e quindi la sua modifica viola la definizione della vista.

Caratteristiche di View

In generale, quando usi View, ci sono i seguenti vantaggi:

A proposito di sicurezza

Puoi limitare gli utenti ad accedere direttamente alla tabella, consentendo invece loro di accedervi attraverso la vista in modo che sia più sicuro. Come mai? Poiché la vista è solo una vista, è consentito solo leggere le informazioni esistenti nella vista, non visualizzare ulteriori informazioni o modificare i dati.

Ad esempio, puoi consentire agli utenti di accedere al nome del cliente, al telefono, all'e-mail attraverso la vista, ma impedire loro di accedere a conti bancari e altre informazioni sensibili.

Semplificare

Quando si scrive una query con molte tabelle, sarà molto complicato, devi testare molto per essere sicuro che il risultato sia corretto, a volte non lo è. Tuttavia, se combini le viste, le dividi in molti segmenti e ogni vista è un segmento, l'esecuzione di query sulla vista sarà molto più facile da capire.

Consistenza

A volte è necessario scrivere una formula complessa che viene utilizzata in molte query, a quel punto dovrai scriverla più e più volte.

Ma se metti quella formula in una vista, altre query fanno riferimento ad essa dalla vista, sarà conveniente e coerente, in seguito modificare la formula, modificarla semplicemente nella vista.