<div class="page">
<div class="cover text-center">
<img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo">
# Vistes
<div class="text-end fit-content ms-auto my-3 mt-auto pt-3">
<p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p>
<p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p>
<p><strong>Curs:</strong> 2023/2024</p>
</div>
<div>
<p class="fw-bold mb-0">Llicència: BY-NC-SA</p>
<p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p>
<a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank">
<img class="mx-auto" src="/itb/images/license.png" alt="Licence"/>
</a>
</div><!--license-->
</div><!--cover-->
</div><!--page-->
{:toc}
## Introducció
Les __vistes__ en SQL són consultes que es guarden en la base de dades i que defineixen una __taula virtual__ a partir de
les dades d'una o més taules. Aquestes vistes es poden utilitzar com si foren taules reals,
però no emmagatzemen dades; en lloc d'això, es defineixen per mitjà d'una consulta SQL que s'executa cada vegada que
s'utilitza la vista i les dades que es retornen es calculen dinàmicament.
Les vistes són molt útils per a:
- __Simplificar__ consultes complexes.
- __Abstracció__: Les vistes permeten als desenvolupadors crear una visió lògica de les dades,
ocultant els detalls complexes de la implementació i estructura de la base de dades.
Permeten crear taules virtuals simples a partir de relacions complexes entre taules reals.
- __Seguretat__: Les vistes permeten als desenvolupadors limitar l'accés a les dades.
- Es poden excluir camps sensibles de les vistes.
- Es poden limitar les files que es poden veure amb una clàusula `WHERE`.
- __Optimització__: Les vistes permeten que els desenvolupadors més experimentats optimitzen les consultes utilitzades en les vistes
perquè siguin més eficients i que, en canvi, els desenvolupadors menys experimentats puguin utilitzar-les sense preocupar-se
de l'eficiència de les consultes.
## Creació de vistes: `CREATE VIEW`
La sentència `CREATE VIEW` permet crear una vista a partir d'una consulta.
La sintaxi és la següent:
```sql
CREATE
[OR REPLACE]
VIEW view_name [(column_list)]
AS select_statement
```
- `CREATE ... VIEW`: Crea una vista.
- `OR REPLACE`: Si la vista ja existeix, es reemplaça.
- `view_name`: Nom de la vista.
- `column_list`: Llista de noms de les columnes de la vista.
Les vistes han de tindre noms de columnes únics, el mateix que una taula normal.
Per defecte, el nom de les columnes de la vista és el mateix que el nom de les columnes de la consulta.
Per definir un nom de columna diferent, es pot definir mitjançant aquesta clàsula opcional.
- `select_statement`: Consulta que defineix la vista.
:::docs
Documentació oficial sobre vistes:
- MariaDB: https://mariadb.com/kb/en/views/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/create-view.html
:::
## Restriccions
Les definicions de vistes tenen les següents restriccions:
- La sentència `SELECT` no pot contindre una subconsulta en la clàusula `FROM`.
- La sentència `SELECT` no pot contindre una clàusula `INTO`.
- La sentència `SELECT` no pot referenciar variables d'usuari.
- Totes les taules referenciades en la consulta han d'existir en el moment
la definició de la vista, i han de ser accessibles per l'usuari que crea la vista.
No obstant això, les taules podrien ser eliminades posteriorment i la vista seguiria
existint, però es produiria un error si s'intenta utilitzar la vista.
- No es poden referenciar taules temporals.
- No es poden associar disparadors (_triggers_) a vistes.
### Exemple
::: info "Dades d'exemple"
```sql
drop database if exists example_vistes;
create database example_vistes;
use example_vistes;
CREATE TABLE producte (
id_producte INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(50) NOT NULL,
preu DECIMAL(5,2) NOT NULL
);
CREATE TABLE cistella_compra (
id_cistella INT PRIMARY KEY AUTO_INCREMENT
);
CREATE TABLE producte_cistella (
id_producte INT,
id_cistella INT,
quantitat INT NOT NULL,
PRIMARY KEY (id_producte, id_cistella),
FOREIGN KEY (id_producte) REFERENCES producte (id_producte),
FOREIGN KEY (id_cistella) REFERENCES cistella_compra (id_cistella)
);
INSERT INTO producte (id_producte, nom, preu) VALUES
(1, "Barra de pà", 0.7),
(2, "Pastís de xocolate", 5.3),
(3, "Botella d'aigua 50cL", 0.8),
(4, "Piles AA", 1.5),
(5, "Pera", 0.3),
(6, "Plàtan", 0.5);
INSERT INTO cistella_compra (id_cistella) VALUES
(1),
(2),
(3);
INSERT INTO producte_cistella (id_producte, id_cistella, quantitat) VALUES
(1, 1, 2),
(2, 1, 1),
(3, 1, 3),
(4, 1, 4),
(5, 2, 1),
(6, 2, 2),
(1, 3, 1),
(2, 3, 1),
(3, 3, 1),
(4, 3, 1),
(5, 3, 1),
(6, 3, 1);
```
:::
::: example
Una consulta que es repetirà sovint és calcular el preu total
d'una cistella de la compra. Aquesta consulta es pot encapsular
en una vista per a simplificar la seua utilització:
```sql
CREATE VIEW view_cistella AS
SELECT
c.id_cistella,
SUM(p.preu * pc.quantitat) AS preu_total
FROM cistella_compra c
INNER JOIN producte_cistella pc
ON pc.id_cistella = c.id_cistella
INNER JOIN producte p
ON pc.id_producte = p.id_producte
GROUP BY id_cistella;
SELECT * FROM view_cistella;
+-------------+------------+
| id_cistella | preu_total |
+-------------+------------+
| 1 | 15.10 |
| 2 | 1.30 |
| 3 | 9.10 |
+-------------+------------+
```
:::
## Eliminació de vistes: `DROP VIEW`
La sentència `DROP VIEW` permet eliminar una vista.
La sintaxi és la següent:
```sql
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
```
- `DROP VIEW`: Elimina una vista.
- `IF EXISTS`: Si la vista no existeix, no es produeix cap error.
- `view_name`: Nom de la vista. Es poden especificar múltiples vistes separades per comes.
- `RESTRICT | CASCADE`:
- `RESTRICT`: Si la vista està referenciada per una altra vista, es produeix un error.
- `CASCADE`: També s'eliminen les vistes que fan referència a la vista que s'elimina.
:::docs
Documentació oficial sobre l'eliminació vistes:
- MariaDB: https://mariadb.com/kb/en/drop-view/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/drop-view.html
:::
### Exemple
::: example
Aquesta sentència elimina, si existeix, la vista `view_cistella`:
```sql
DROP VIEW IF EXISTS view_cistella;
```
:::
## Actualització de vistes: `ALTER VIEW`
La sentència `ALTER VIEW` s'utilitza per modificar
una vista existent a partir d'una nova consulta `SELECT`.
La sintaxi és la següent:
```sql
ALTER VIEW view_name [(column_list)]
AS select_statement
```
- `ALTER VIEW`: Modifica una vista.
- `view_name`: Nom de la vista.
- `column_list`: Llista de noms de les columnes de la vista.
- `select_statement`: Consulta que defineix la vista.
:::docs
Documentació oficial sobre l'actualització de vistes:
- MariaDB: https://mariadb.com/kb/en/alter-view/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/alter-view.html
:::
### Exemple
::: example
Aquesta sentència modifica la vista `view_cistella` per a que
mostre també el número de productes de la cistella:
```sql
ALTER VIEW view_cistella AS
SELECT
c.id_cistella,
SUM(p.preu * pc.quantitat) AS preu_total,
COUNT(p.id_producte) AS num_productes
FROM cistella_compra c
INNER JOIN producte_cistella pc
ON pc.id_cistella = c.id_cistella
INNER JOIN producte p
ON pc.id_producte = p.id_producte
GROUP BY id_cistella;
SELECT * FROM view_cistella;
+-------------+------------+------------------+
| id_cistella | preu_total | nombre_productes |
+-------------+------------+------------------+
| 1 | 15.10 | 4 |
| 2 | 1.30 | 2 |
| 3 | 9.10 | 6 |
+-------------+------------+------------------+
```
:::
## Bibliografia
- Documentació oficial indicada anteriorment.