Inicio‎ > ‎SQL‎ > ‎Lliçons SQL‎ > ‎

T12 Vistes i taules temporals


En aquesta sessió mostrarem dos tipus d’objectes d’ús habitual en qualsevol SGBD: les vistes i les taules temporals. Mentres que les primeres són objectes persistents que ens permeten resumir consultes complexes i d’ús freqüent, les segones, com el seu nom indica, són objectes de curta vida, durant una sessió o una simple consulta.


Vistes

Per a aquesta secció és necessari connectar-se al servidor amb un usuari amb permisos per a crear vistes.

Una vista és un objecte que es defineix a partir d’una consulta i que es comporta com una taula si bé, depenent de la consulta en què es basa, es poden fer més o menys coses: consultar una vista sempre serà possible però inserir o esborrar files en una vista o modificar un valor ja depèn de com siga aquesta definició.

Per a crear una vista disposem de l'orde:

CREATE VIEW nombrevista AS consulta

Una vista és un objecte persistent, pel tant, per a eliminar-la del catàleg, hi ha que executar:

DROP VIEW nombrevista

També es pot consultar la definició d’una vista ja definida (encara que açò ja és particular de MySQL, en altres gestors tenen els seus propis mètodes):

SHOW CREATE VIEW nombrevista

Tota la informació sobre vistes en MySQL es pot consultar en http://dev.mysql.com/doc/refman/5.0/en/create-view.html.

Aquesta sessió s’ha plantejat en format demostració pel que es recomana executar una a una les següents sentències i buscar els motius pels quals unes sentències s’executen sense problemes i altres no.


Després de connectar-se al servidor cal triar una base de dades en què se’ns permeta crear vistes:

use mibd;


Copiarem els continguts d’algunes taules de TiendaOnLine a taules locals que siguen de la nostra propietat

drop table if exists mitv;
drop table if exists miarticulo;

create table miarticulo (
cod varchar(7),
nombre varchar(45),
pvp decimal(7,2),
marca varchar(15),
imagen blob,
urlimagen varchar(100),
especificaciones text,
primary key (cod)) engine=innodb;

insert into miarticulo select * from tiendaonline.articulo;

create table mitv (
cod varchar(7),
panel varchar(45),
pantalla smallint(6),
resolucion varchar(15),
hdreadyfullhd varchar(6),
tdt tinyint(1),
primary key (cod),
foreign key (cod) references miarticulo (cod)) engine=innodb;

insert into mitv select * from tiendaonline.tv;


Creem la primera vista:

create view vma as select cod,nombre,pvp from miarticulo;


Una vista es comporta com una taula i pot consultar-se.

select * from vma where pvp between 500 and 700;


Una vista es pot generar a partir de qualsevol consulta, i té la característica afegida de poder restringir l’accés a només un subconjunt de les files possibles.

create view vmb as select cod,nombre,pvp from miarticulo where pvp between 500 and 700;

select * from vmb;

(1) select cod,pvp from vmb where cod in (select cod from mitv) order by pvp;



Inserint

Una vista, en determinades condicions, permet inserir noves dades.

(2) insert into vma (cod,nombre,pvp) values ('B001','MiArtículo',499);

select * from miarticulo where cod='B001';
select * from vma where cod='B001';
select * from vmb where cod='B001';


(3) insert into vmb (cod,nombre,pvp) values ('B002','MiOtroArtículo',701);

select * from vmb where cod='B002';
select * from miarticulo where cod='B002';
select * from vma where cod='B002';


Esborrant

Creem una altra vista per a facilitar la comprovació de les accions que sol·licitem. En aquest cas intentarem eliminar files.

create view bart as select * from miarticulo where cod like 'B%';

select * from bart;

(4) delete from vma where cod='B001';

(5) delete from vmb where cod='B002';

select * from bart;


Modificant

Ara comprovarem l’orde update. Aquesta orde té les mateixes restriccions que delete.

select * from bart;

(6) update vma set pvp = 800 where cod='B002';

select * from bart;

(7) update vmb set pvp = 600 where cod='B002';

select * from bart;


Amb més d'una taula

La vista es pot definir sobre diverses taules, però això afecta les ordes que es poden executar i en quines condicions.


create view vat as
  select a.cod,nombre,pvp,resolucion,tdt
  from miarticulo a, mitv t

  where a.cod=t.cod
    and pvp between 800 and 1200;


select * from vat order by pvp desc;


(8) insert into vat values ('B003','OtroMás',1100,null,null);
(9) insert into vat (cod,nombre,pvp,resolucion,tdt) values ('B003','OtroMás',1100,null,null);
(10) insert into vat (cod,nombre,pvp) values ('B003','OtroMás',1100);

select * from vat;
select * from bart;


(11) insert into vat (resolucion,tdt) values ('800x600',1);
(12) insert into vat (cod,resolucion,tdt) values ('B004','800x600',1);


(13) delete from vat where cod='A0694';

select * from vat;
select * from bart;

(14) update vat set pvp = 999 where cod='A0694';
(15) update vat set resolucion = '800x600',tdt=1 where cod='A0694';
(16) update vat set pvp = 850, resolucion = 'ninguna',tdt=1 where cod='A0694';

select * from vat;


(17) update vat set cod = 'B004' where cod='A0694';

select * from vat;
select * from bart;
select * from mitv where cod='A0694';


With check option

Redefinirem bart i crearem una altra vista bart2.

drop view if exists bart2;
drop view if exists bart;


create view bart as select * from miarticulo where cod like 'B%';
(18) create view bart2 as select * from miarticulo where cod like 'B%' with check option;


select * from bart;
select * from bart2;

insert into bart2 (cod,nombre,pvp) values ('B010','Artículo B10',1999);
insert into bart2 (cod,nombre,pvp) values ('C010','Artículo C10',1999);


select * from bart;
select * from bart2;
select * from miarticulo where pvp=1999;

insert into bart (cod,nombre,pvp) values ('C010','Artículo C10',1999);
select * from bart;
select * from bart2;
select * from miarticulo where pvp=1999;


Finalment, eliminem totes les vistas i taules creades.

drop view bart;
drop view bart2;
drop view vat;
drop view vmb;
drop view vma;
drop table mitv;
drop table miarticulo;



Taules temporals

Ens hem pres la llibertat de considerar taules temporals tant les taules taules temporary en MySQL, com les subconsultes utilitzades com a columna en la select o com a taula en el from.

S’entèn per taula temporal aquella que es crea i utilitza en un context limitat, bé siga una orde concreta de SQL (el cas genèric de les subconsultes) o una sessió o connexió. Les taules TEMPORARY, per exemple, són objectes que desapareixen automàticament quan es tanca la sessió d’usuari. Una subconsulta és accessible només mentres s’executa una determinada orde. En aquesta lliçó només tractarem els casos de subconsultes, les taules TEMPORARY no necessiten més explicació.


Subconsultes com columnes calculades

Una consulta pot utilitzar-se com una columna més de qualsevol consulta, només hi ha que tenir la precaució de donar-li nom. Encara que MySQL no ho necessita realment, pareix convenient utilitzar la paraula reservada AS per a millorar la comprensió de cada part de la consulta. Per exemple, la següent orde genera una taula amb 2 columnes a partir de subconsultes completes:
use tiendaonline;
select
    (select max(pvp) from articulo) as mart,
    (select max(precio) from linped) as mlinped;

En aquests casos s’utilitzen les subconsultes per a realitzar càlculs sobre elles i dins d’una consulta sobre un cert conjunt de files:

select cod,pvp,
       pvp/(select max(pvp) from articulo)*100 as tpcpvp,
       pvp/(select max(precio) from linped)*100 as tpcprecio
from articulo
where pvp between 1000 and 1200; 

Subconsultes en el from

A vegades és necessari incloure una subconsulta com una taula més a enllaçar en una altra consulta d’orde superior. Novament, cal donar-li nom a aqueixa taula temporal per a poder fer referència a ella. En aquest cas, aprofitem la consulta anterior per a utilitzar en una altra més complexa:

select tv.cod,resolucion,tdt,tpcpvp
from tv,
  (select cod,pvp,
       pvp/(select max(pvp) from articulo)*100 as tpcpvp,
       pvp/(select max(precio) from linped)*100 as tpcprecio
   from articulo
   where pvp between 1000 and 1200) as calc 
where tv.cod=calc.cod;




Invertir una taula


El que en altres sistemes es coneix com a columnes PIVOT (en SQL Server, per exemple) consisteix a construir columnes a partir de la informació continguda en les files. Ací calcularem quantes comandes s’han realitzat en els mesos d’octubre, novembre i desembre de qualsevol any. Abans, comprovarem les dades de què disposem:
select month(fecha) mes, count(*)
from pedido
where month(fecha) in (10,11,12)
group by month(fecha)
order by month(fecha);

El que volem aconseguir és que els mesos es convertisquen en columnes. Per a això:

select
    (select count(*)
     from pedido
     where month(fecha) =10) as octubre,
    (select count(*)
     from pedido
     where month(fecha) =11) as noviembre,
    (select count(*)
     from pedido
     where month(fecha) =12) as diciembre;




Complicant-ho un poc, vegem la quantitat de vegades que se sol·licita cada article en cada un d’aquestos mateixos mesos:
select articulo, month(fecha) mes, count(*) veces
from pedido p join linped l on (p.numpedido=l.numpedido)
where month(fecha) in (10,11,12)
group by articulo,month(fecha)
order by articulo,month(fecha);


Amb la mateixa intenció que abans, convertirem els mesos en columnes fent ús de subconsultes:


select distinct articulo,
    (select count(*)
     from pedido p join linped l on (p.numpedido=l.numpedido)
     where month(fecha) =10 and l.articulo=ll.articulo) octubre,
    (select count(*)
     from pedido p join linped l on (p.numpedido=l.numpedido)
     where month(fecha) =11 and l.articulo=ll.articulo) noviembre,
    (select count(*)
     from pedido p join linped l on (p.numpedido=l.numpedido)
     where month(fecha) =12 and l.articulo=ll.articulo) diciembre
from linped ll;


La mateixa consulta però variant lleugerament els orígens de les dades, la qual cosa evita l’ús del distinct. Note’s que ara les subconsultes fan referència a la taula del select extern:

select cod as articulo,
  (select count(*)
   from pedido p join linped l on (p.numpedido=l.numpedido)
   where month(fecha) =10 and l.articulo=cod) octubre,
  (select count(*)
   from pedido p join linped l on (p.numpedido=l.numpedido)
   where month(fecha) =11 and l.articulo=cod) noviembre,
  (select count(*)
   from pedido p join linped l on (p.numpedido=l.numpedido)
   where month(fecha) =12 and l.articulo=cod) diciembre
from articulo
where cod in (select articulo
              from pedido p join linped l on (p.numpedido=l.numpedido)
              where month(fecha) in (10,11,12));

             

Taules TEMPORARY


Aprofitem la consulta anterior per a crear una taula temporal. Recorde’s que aquesta taula desapareixerà del sistema quan tanquem la connexió al servidor. Ací es defineix a partir d’una consulta però aquestes taules es poden crear com qualsevol altra amb l’orde CREATE TABLE.

create temporary table mitabla
    select cod articulo,
      (select count(*)
       from pedido p join linped l on (p.numpedido=l.numpedido)
       where month(fecha) =10 and l.articulo=cod) octubre,
      (select count(*)
       from pedido p join linped l on (p.numpedido=l.numpedido)
       where month(fecha) =11 and l.articulo=cod) noviembre,
      (select count(*)
       from pedido p join linped l on (p.numpedido=l.numpedido)
       where month(fecha) =12 and l.articulo=cod) diciembre
    from articulo
    where cod in (select articulo
                  from pedido p join linped l on (p.numpedido=l.numpedido)
                  where month(fecha) in (10,11,12));

Consultem la taula acabada de crear per a comprovar el seu contingut

select * from mitabla where octubre>0 and noviembre>0;


No ens hem de preocupar d’eliminar-la ja que ja ho farà el propi sistema.