Salta el contingut
 

Vistes

Joan Puigcerver Ib谩帽ez

j.puigcerveribanez@edu.gva.es

Llic猫ncia: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 馃叚

License

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: 

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.

Documentaci贸

Documentaci贸 oficial sobre vistes:

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露

Dades d'exemple

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);

Exemple

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贸: 

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: 

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.

Documentaci贸

Documentaci贸 oficial sobre l'eliminaci贸 vistes:

Exemple露

Exemple

Aquesta sent猫ncia elimina, si existeix, la vista view_cistella: 

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: 

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.

Documentaci贸

Documentaci贸 oficial sobre l'actualitzaci贸 de vistes:

Exemple露

Exemple

Aquesta sent猫ncia modifica la vista view_cistella per a que mostre tamb茅 el n煤mero de productes de la cistella: 

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.
馃搶 Aquest document pot quedar desactualitzat despr茅s d鈥檌mprimir-lo. Pots consultar la versi贸 m茅s recent a la p脿gina web.
馃尶 Abans d鈥檌mprimir aquest document, considera si 茅s realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 馃叚 Joan Puigcerver Ib谩帽ez