Salta el contingut

5. DDL - Vistes

5.1. Introducció

Suposem que tenim la taula: ALUMNES = codi + nom + edat + grup + domicili

Ja veiérem que a partir del resultat d'una SELECT podíem crear una taula:

SQL
1
2
3
4
CREATE TABLE alumnes_menors AS
    SELECT codi, nom, grup, edat
    FROM alumnes
    WHERE edat < 18;

Eixa instrucció crearà una nova taula amb les columnes especificades en la SELECT (codi, nom, grup i edat) i amb les files que complisquen la WHERE (menors d'edat). Ara bé: els canvis que es facen en la taula alumnes no implicaran canvis en la taula alumnes_menors ni viceversa:

  • Si inserim un nou alumne de 15 anys en la taula alumnes, eixe alumne no estarà en la taula alumnes_menors (ni viceversa).
  • Si esborrem un alumne de alumnes_menors, no s'esborrarà d'alumnes (ni viceversa).
  • Si en la taula d'alumnes modifiquem l'edat d'un alumne que abans era menor d'edat i fem que ara tinga 18 anys, l'alumne no desapareixerà de la taula alumnes_menors.
  • ...

Volem solucionar això. És a dir: volem crear una taula especial a partir d'una altra, de forma que els canvis d'una es reflectisquen en l'altra. Realment, el que farem no serà copiar les dades d'una taula a una altra, sinó que crearem una "finestra" a partir de la qual vore les dades que volem de la taula. Això s'aconsegueix amb les vistes:

SQL
1
2
3
4
CREATE VIEW alu_men AS
    SELECT codi, nom, grup, edat
    FROM alumnes
    WHERE edat < 18;

Així, amb la "finestra" (vista) anomenada alu_men, podrem vore el codi, nom, grup i edat dels alumnes de la taula alumnes que siguen menors de 18 anys. Per tant, si inserim un alumne nou de 1DAM en la taula alumnes, també el vorem des de la vista alu_men.

És a dir: si fem

SQL
1
2
3
SELECT *
    FROM alu_men
    WHERE grup = "1DAM";

... és com si férem:

SQL
1
2
3
SELECT nom, edat
    FROM alumnes
    WHERE edat < 18 AND grup = "1DAM";

Què és una vista?

Una vista és una taula virtual, ja que no té existència pròpia encara que exteriorment ho parega. És a dir, no existeix físicament (no ocupa espai en disc dur), sinó que és com una finestra a partir de la qual només podem vore una part (files i columnes) de les taules sobre les quals està definida.

5.2. Creació d'una vista

SQL
1
2
3
CREATE VIEW nom_vista [ (comallista_nom_col) ]
    AS sentència_select
    [ WITH CHECK OPTION ]
  • (comallista_nom_col): noms de les columnes que tindrà la vista. És opcional.

Tipus de la select de la vista

La select sobre la que es defineix la vista pot ser de qualsevol tipus: pot tindre columnes calculades (expressions), pot ser multitaula, pot tindre unions, group by, subconsultes...

També podem crear vistes a partir de selects sobre altres vistes.

Operacions que es poden fer sobre la vista

En principi, una vista és com una taula. Per tant, podrem fer sobre ella: select, insert, update, delete. Ara bé: cal tindre en compte que les sentències sobre la vista "es tradueixen" a sentències sobre la taula (o taules) sobre les quals està definida. Per tant, sempre podrem fer select sobre elles però no sempre un insert/delete/update sobre la vista es podrà traduir a un insert/delete/update sobre la taula (o taules) corresponent.

Per exemple, si una vista està definida amb un group by, si intentem fer un update sobre la vista, no hi haurà traducció per a fer update sobre la taula corresponent. Suposem la següent vista:

SQL
1
2
3
4
CREATE VIEW quantitats AS
    SELECT grup, count(*) AS quant
    FROM alumnes
    GROUP BY grup;

Podrem fer la següent sentència sobre la vista?

SQL
1
2
3
UPDATE quantitats
    SET quant = 10
    WHERE grup = "1ASIX"

La resposta és no. En eixe cas es diu que la vista no és actualitzable.

Una vista és actualitzable si la select sobre la qual està definida no té "coses rares": no està creada amb UNION ni DISTINCT, ni GROUP BY, ni subconsultes, ni multitaula...

Clàusula WITH CHECK OPTION

Si en la definició d'una vista actualitzable s'inclou la clàusula WITH CHECK OPTION, esta no permetrà inserts o updates que no complisquen les condicions de la vista. Per exemple, si la vista alu_men l'haguérem creada amb esta clàusula, les següents instruccions donarien error:

SQL
1
2
INSERT INTO alu_men VALUES (100, "Pep", "1DAM", 20);
UPDATE alu_men SET edat = 18 WHERE edat = 17;

Destrucció d'una vista

SQL
1
DROP VIEW nom_vista [ RESTRICT | CASCADE ];
  • RESTRICT: no deixa esborrar la vista si hi ha altres vistes definides sobre ella.
  • CASCADE: si hi ha altres vistes definides sobre ella, també les esborra.

MySQL accepta el RESTRICT i CASCADE, però passa d'ells. És a dir: deixa esborrar una vista V1 encara que hi haja alguna altra vista V2 definida a partir d'ella. Si férem una select sobre V2 donaria error. En altres SGBD, com PostgreSQL, sí que funciona.

5.3. Utilitats de les vistes

  • Permetre tindre en una taula informació derivada d'altres taules, de forma que les modificacions en eixes taules primitives també es reflectisquen en la "taula derivada" (vista).

  • Facilitar la construcció de selects complexes. Així podrem fer selects sobre altres "selects" ja fetes.

  • Permetre donar permisos sobre parts d'una taula (ja vorem el tema de permisos).

Exercicis. Vistes (BD lliga1213)

  1. Crear una vista: a) Crea la vista jug_sue amb el dorsal, nom i lloc dels jugadors de l'equip de codi sue (no existeix en la taula de jugadors però dóna igual). No li poses la clàusula del check option. b) Comprova el contingut de la vista jug_sue. Caldrà fer un select sobre la vista. Voràs que no té res, ja que no hi ha jugadors d'eixe equip.

  2. Comprovació del funcionament de la vista: a) Inserix l'equip tav (Tavernes C.F.) i el sue (Sueca United) en la taula d'equips. b) Inserix en la taula de jugadors dos nous davanters: Pep, del Tavernes C.F. i Pau, Sueca United. c) Comprova el contingut de la vista jug_sue. Voràs que ara la vista "sí que té" un jugador (Pau). Realment "no el té" però a través de la vista estem mirant els jugadors de la taula de jugadors que són del Sueca United. d) Fes que el jugador del Tavernes C.F. ara el fitxe el Sueca United (si cal, canviar-li també el dorsal). Caldrà fer un update. e) Torna a comprovar el contingut de la vista jug_sue. Ara estarem veient dos jugadors (Pau i Pep). f) Esborra a Pep de la taula jugadors. Cal fer delete sobre la taula. g) Esborra a Pau a través de la vista jug_sue. Cal fer delete sobre la vista. h) Comprova el contingut de la vista jug_sue. Ja no deu estar ningú dels dos. Comprova que tampoc estan en la taula jugadors. i) Intenta inserir a Pau a través de la vista jug_sue. Et donarà error perquè en l'insert no li posem el codi de l'equip i, per tant, quan s'intente inserir en la taula de jugadors no admetrà un null en el camp de l'equip. Però si no fóra per això, sí que es permet inserir a través d'una vista.

  3. Eliminar una vista: a) Elimina la vista anterior (jug_sue). No que esborres els seus registres, sinó que la destruïsques. Caldrà fer un drop de la vista. Això no afecta a la taula sobre la qual estava definida.

  4. Crear una vista amb el check option: a) Crea la vista equipets amb el codi, nomcurt i pressupost de tots els equips que tinguen un pressupost inferior a 30 milions d'euros. Fes-ho amb el check option. b) Insereix a través de la vista equipets estos equips, en 2 inserts: - Equip gan, "C.F. Gandia", amb 0 milions d'euros de pressupost. - Equip and, "Andorra C.F", amb 31 milions d'euros de pressupost. Ha de donar error per no complir la condició del check option. c) Esborra a través de la vista equipets els equips de més de 40 m. de pressupost. No donarà error però no esborrarà cap equip, degut al check option. d) A través de la vista equipets fes que el nou pressupost del C.F. Gandia ara siga de 31 m. Donarà error per no complir el check option.

  5. Crear una vista no actualitzable: a) Crea la vista equips_nombrosos amb el codi de l'equip, el nom curt, el nom de la ciutat i la quantitat de jugadors de cadascun. Però només amb aquells equips que tinguen més de 30 jugadors en plantilla. Els noms de les columnes seran: codi, nom, ciutat, plantilla. b) En la vista equips_nombrosos modifica el nom de l'equip del Betis (codi bet): ara es dirà "Betis". Donarà un error dient que la vista no és actualitzable. No ho és perquè com té group by (i multitaula), no es pot "traduir" eixe update sobre la vista a un update sobre taules. El mateix passaria si intentem fer inserts o deletes sobre eixa vista.

  6. Crear una vista a partir d'altra (exercici solucionat): a) Crea la vista resultats_equips amb estos camps: - equip: codi de l'equip - pgc: quantitat de Partits Guanyats a Casa - pec: quantitat de Partits Empatats a Casa - ppc: quantitat de Partits Perduts a Casa - pgf: quantitat de Partits Guanyats Fora - pef: quantitat de Partits Empatats Fora - ppf: quantitat de Partits Perduts Fora

    Este tipus de consulta es fa amb subconsultes dins de la pròpia clàusula select. No et preocupes si no t'ix. Estes consultes només s'han vist en algun exercici. La solució seria esta:

    SQL
    1
2
3
4
5
6
7
8
9
    create view resultats_equips as
    select codi as equip,
           (select count(*) from partits where equipc = e.codi and golsc > golsf) as pgc,
           (select count(*) from partits where equipc = e.codi and golsc = golsf) as pec,
           (select count(*) from partits where equipc = e.codi and golsc < golsf) as ppc,
           (select count(*) from partits where equipf = e.codi and golsf > golsc) as pgf,
           (select count(*) from partits where equipf = e.codi and golsf = golsc) as pef,
           (select count(*) from partits where equipf = e.codi and golsf < golsc) as ppf
    from equips e;

    b) Crea la vista classif amb els camps següents. Crea-la a partir de la vista resultats_equips: - equip = Codi de l'equip - pjc = Partits Jugats a Casa - pgc = Partits Guanyats a Casa - pec = Partits Empatats a Casa - ppc = Partits Perduts a Casa - puntsc = Punts a Casa - pjf = Partits Jugats Fora - pgf = Partits Guanyats Fora - pef = Partits Empatats Fora - ppf = Partits Perduts Fora - puntsf = Punts Fora - pjt = Partits Jugats en Total - pgt = Partits Guanyats en Total - pet = Partits Empatats en Total - ppt = Partits Perduts en Total - puntst = Punts en Total

    La solució seria:

    SQL
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    create view classif as
    select equip, (pgc + pec + ppc) as pjc,
           pgc, pec, ppc, (3*pgc + pec) as puntsc,

           (pgf + pef + ppf) as pjf,
           pgf, pef, ppf, (3*pgf + pef) as puntsf,

           (pgc + pec + ppc + pgf + pef + ppf) as pjt,
           (pgc + pgf) as pgt,
           (pec + pef) as pet,
           (ppc + ppf) as ppt,
           (3*pgc + pec + 3*pgf + pef) as puntst
    from resultats_equips;

    c) Crea la vista classif2 amb els camps de classif més altres camps calculats: gols marcats (a casa, fora i en total), rebuts (a casa, fora i en total) i els que vulgues.

    SQL
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
    create view classif2 as
    select *,   (select sum(golsc)   -- Gols Marcats a Casa
                      from partits
                      where partits.equipc = classif.equip) as gmc,

                (select sum(golsf)   -- Gols Marcats Fora
                      from partits
                      where partits.equipf = classif.equip) as gmf,

                (select sum(golsc)   -- Gols Marcats en Total
                      from partits
                      where partits.equipc = classif.equip)
              + (select sum(golsf)
                      from partits
                      where partits.equipf = classif.equip) as gmt,

                (select sum(golsf)   -- Gols Rebuts a Casa
                      from partits
                      where partits.equipc = classif.equip) as grc,

                (select sum(golsc)   -- Gols Rebuts Fora
                      from partits
                      where partits.equipf = classif.equip) as grf,

                (select sum(golsf)   -- Gols Rebuts en Total
                      from partits
                      where partits.equipc = classif.equip)
              + (select sum(golsc)
                      from partits
                      where partits.equipf = classif.equip) as grt

    from classif;

    d) Consultes sobre les vistes. Les vistes que hem fet ens serviran per a fer més fàcils consultes com les següents:

    d.1) Mostra la taula classificatòria (vista classif) ordenada pels punts totals descendents. També ha d'eixir (en la primera columna) el nom llarg de l'equip.

    SQL
    1
2
3
4
    select equips.nomllarg, classif.*
    from classif, equips
    where classif.equip = equips.codi
    order by puntst desc;

    d.2) Equips que han aconseguit més del doble de punts a casa que fora de casa. Mostra també els punts totals a casa, els punts totals fora i els punts totals. Decreixent pels punts a casa.

    SQL
    1
2
3
4
    select equip, puntsc, puntsf, puntst
    from classif
    where puntsc > 2*puntsf
    order by puntsc desc;

    d.3) Mostra els punts que tenen els equips de la ciutat amb més habitants i de la ciutat amb menys habitants. Mostra també el nom de la ciutat, el nom curt de l'equip i els habitants de la ciutat.

    SQL
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    select ciutats.nom as ciutat, equips.nomcurt as equip,
       ciutats.habitants, classif.puntst
    from ciutats, equips, classif
    where classif.equip = equips.codi
       and equips.ciutat = ciutats.codi
       and habitants in (select max(habitants)
                             from ciutats, equips
                             where ciutats.codi = equips.ciutat
                          union
                          select min(habitants)
                             from ciutats, equips
                             where ciutats.codi = equips.ciutat)
    order by 1, 2