SQL Creazione della vista (Esercizio CREATE VIEW)

In questo tutorial, imparerai come creare, aggiornare e eliminare viste utilizzando SQL.

creare viste per semplificare l'accesso alle tabelle

Una vista è una tabella virtuale, la cui definizione è memorizzata nel database. Tuttavia, a differenza delle tabelle, una vista non contiene alcun dato. Invece, fornisce un metodo per memorizzare query complesse e comuni nel database. Tuttavia, puoi usareFrase SQL SELECTper accedere ai dati della vista, proprio come si farebbe con una tabella o una tabella di base.

Le viste possono anche essere utilizzate come meccanismo di sicurezza, permettendo agli utenti di accedere ai dati attraverso le viste invece di concedere l'accesso completo alla tabella base, accedendo ai dati della vista come se fossero una tabella o una tabella di base.

Sintassi

Creare una vista utilizzando la frase CREATE VIEW.

CREATE　VIEW　view_name　AS　select_statement;

Per comprendere chiaramente questo, diamo un'occhiata a quanto segueemployeesedepartmentsTabella.

+--------+--------------+--------+---------+
|　emp_id　|　emp_name　　　　　|　salary　|　dept_id　|
+--------+--------------+--------+---------+
|　　　　　　1　|　Ethan　Hunt　　　|　　　5000　|　　　　　　　4　|
|　　　　　　2　|　Tony　Montana　|　　　6500　|　　　　　　　1　|
|　　　　　　3　|　Sarah　Connor　|　　　8000　|　　　　　　　5　|
|　　　　　　4　|　Rick　Deckard　|　　　7200　|　　　　　　　3　|
|　　　　　　5　|　Martin　Blank　|　　　5600　|　　　　NULL　|
+--------+--------------+--------+---------+

+---------+------------------+
|　dept_id　|　dept_name　　　　　　　　|
+---------+------------------+
|　　　　　　　1　|　Administration　　　|
|　　　　　　　2　|　Customer　Service　|
|　　　　　　　3　|　Finance　　　　　　　　　　|
|　　　　　　　4　|　Human　Resources　　|
|　　　　　　　5　|　Sales　　　　　　　　　　　　|
+---------+------------------+

Supponiamo che tu voglia cercare l'ID e il nome dell'impiegato e il nome del loro dipartimento, allora dovrai eseguireAggiunta a sinistraOperazione, come mostrato di seguito:

SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id;

Una volta eseguita la query sopra, otterrete l'output seguente:

+--------+--------------+-----------------+
|　emp_id　|　emp_name　　　　　|　dept_name　　　　　　　|
+--------+--------------+-----------------+
|　　　　　　1　|　Ethan　Hunt　　　|　Human　Resources　|
|　　　　　　2　|　Tony　Montana　|　Administration　　|
|　　　　　　3　|　Sarah　Connor　|　Sales　　　　　　　　　　　|
|　　　　　　4　|　Rick　Deckard　|　Finance　　　　　　　　　|
|　　　　　　5　|　Martin　Blank　|　NULL　　　　　　　　　　　　|
+--------+--------------+-----------------+

However, whenever you need to access this record, you need to enter the entire query again. If you frequently perform such operations, it will become very inconvenient and annoying.

In this case, you can create a view to make the query results easier to access, as follows:

CREATE VIEW emp_dept_view AS
SELECT t1.emp_id, t1.emp_name, t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id;

Now, you can use the view emp_dept_view to access the same records as follows:

SELECT * FROM emp_dept_view;

As you can see, how much time and effort you can save on the view.

Replace the existing view

In MySQL, if you want to update or replace an existing view, you can delete the view and create a new one, or simply use the OR REPLACE clause in the CREATE VIEW statement, as shown below:

CREATE OR REPLACE VIEW view_name AS select_statement;

The following SQL statement will replace or modify the existing viewemp_dept_viewThe definition of the view, which is to add a new column salary.

-- MySQL database syntax
CREATE OR REPLACE VIEW emp_dept_view AS
SELECT　t1.emp_id,　t1.emp_name,　t1.salary,　t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id;

更新视图后，如果执行以下语句：

SELECT　*　FROM　emp_dept_view　ORDER　BY　emp_id;

您将在结果输出中看到另一列salary，如下所示：

+--------+--------------+--------+-----------------+
|　emp_id　|　emp_name　　　　　|　salary　|　dept_name　　　　　　　|
+--------+--------------+--------+-----------------+
|　　　　　　1　|　Ethan　Hunt　　　|　　　5000　|　Human　Resources　|
|　　　　　　2　|　Tony　Montana　|　　　6500　|　Administration　　|
|　　　　　　3　|　Sarah　Connor　|　　　8000　|　Sales　　　　　　　　　　　|
|　　　　　　4　|　Rick　Deckard　|　　　7200　|　Finance　　　　　　　　　|
|　　　　　　5　|　Martin　Blank　|　　　5600　|　NULL　　　　　　　　　　　　|
+--------+--------------+--------+-----------------+

Aggiornare i dati attraverso la vista

In teoria, oltre aSELECTOltre alle istruzioni, puoi eseguireINSERT,UPDATEeDELETEMa non tutte le viste sono aggiornabili, ossia in grado di modificare i dati della tabella sorgente di base. Ci sono alcune limitazioni di aggiornabilità.

Di solito, se una vista contiene qualsiasi dei seguenti elementi, la vista non è aggiornabile:

• DISTINCT, clausola GROUP BY o HAVING.

• Funzioni aggregate, ad esempio AVG(), COUNT(), SUM(), MIN(), MAX() e così via.

• Operatori UNION, UNION ALL, CROSSJOIN, EXCEPT o INTERSECT.

• La sottoquery nella clausola WHERE si riferisce alla tabella nella clausola FROM.

Se una vista soddisfa queste condizioni, è possibile utilizzare la vista per modificare la tabella di origine.

Le seguenti istruzioni aggiorneranno lo stipendio (salary) dell'impiegato con emp_id uguale a 1.

UPDATE emp_dept_view SET salary = '6000'　
WHERE emp_id = 1;

Eliminazione della vista

Analogamente, se non si ha più bisogno della vista, è possibile eliminarla dal database utilizzando la dichiarazione DROP VIEW, come indicato nella seguente sintassi:

DROP VIEW view_name;

Le seguenti istruzioni elimineranno la vista dal databaseemp_dept_view.

DROP VIEW emp_dept_view;