PDF de programación - Lecciones SQL - T12 Visitas y tablas temporales

https://sites.google.com/site/fbddocs/practicas-sql-espanol/vistas-y-tabl...

Lecciones SQL >

T12 Vistas y tablas temporales

En esta sesión vamos a mostrar dos tipos de objetos de uso habitual

en cualquier SGBD: las vistas y las tablas temporales. Mientras que

las primeras son objetos persistentes que nos permiten resumir

consultas complejas y de uso frecuente, las segundas, como su

nombre indica, son objetos de corta vida, durante una sesión o una

Contenidos

1 Vistas

1.1 Insertando

1.2 Borrando
1.3 Modificando

simple consulta.

Vistas

Para esta sección es necesario conectarse al servidor con

un usuario con permisos para crear vistas.

1.4 Con más de una tabla
1.5 With check option

2 Tablas temporales

2.1 Subconsultas como columnas

calculadas
2.2 Subconsultas en el from

2.3 Invertir una tabla
2.4 Tablas TEMPORARY

Una vista es un objeto que se define a partir de una consulta y que se comporta como una tabla si bien,

dependiendo de la consulta en la que se basa, se pueden hacer más o menos cosas: consultar una vista

siempre será posible pero insertar o borrar filas en una vista o modificar un valor ya depende de cómo sea

esa definición.

Para crear una vista disponemos de la orden:

CREATE VIEW nombrevista AS consulta

Una vista es un objeto persistente, por lo tanto, para eliminarla del catálogo, hay que ejecutar:

DROP VIEW nombrevista

También se puede consultar la definición de una vista ya definida (aunque esto ya es particular de MySQL,

en otros gestores tienen sus propios métodos):

SHOW CREATE VIEW nombrevista

Toda la información sobre vistas en MySQL se puede consultar en http://dev.mysql.com/doc/refman

/5.0/es/views.html.

Esta sesión se ha planteado en formato demostración por lo que se recomienda ejecutar una a una las

siguientes sentencias y buscar los motivos por los que unas sentencias se ejecutan sin problemas y otras

no.

Tras conectarse al servidor hay que elegir una base de datos en la que se nos permita crear vistas:

use mibd;

Vamos a copiar los contenidos de algunas tablas de TiendaOnLine a tablas locales que sean de nuestra

propiedad

1 de 9

17/05/2013 14:01

https://sites.google.com/site/fbddocs/practicas-sql-espanol/vistas-y-tabl...

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;

Creamos la primera vista:

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

Una vista se comporta como una tabla y puede consultarse.

select * from vma where pvp between 500 and 700;

Una vista se puede generar a partir de cualquier consulta, y tiene la característica añadida de poder

restringir el acceso a solo un subconjunto de las filas posibles.

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;

2 de 9

17/05/2013 14:01

https://sites.google.com/site/fbddocs/practicas-sql-espanol/vistas-y-tabl...

Insertando

Una vista, bajo ciertas condiciones, permite insertar nuevos datos.

(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';

Borrando

Creamos otra vista para facilitar la comprobación de las acciones que solicitamos. En este caso

vamos a intentar eliminar filas.

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;

Modificando

Ahora comprobaremos la orden update. Esta orden tiene las mismas restricciones 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';

3 de 9

17/05/2013 14:01

https://sites.google.com/site/fbddocs/practicas-sql-espanol/vistas-y-tabl...

select * from bart;

Con más de una tabla

La vista se puede definir sobre varias tablas, pero eso afecta a las órdenes que se pueden ejecutar y

en qué condiciones.

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;

4 de 9

17/05/2013 14:01

https://sites.google.com/site/fbddocs/practicas-sql-espanol/vistas-y-tabl...

select * from mitv where cod='A0694';

With check option

Vamos redefinir bart y crear otra 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;

Finalmente, eliminamos todas las vistas y tablas creadas.

drop view bart;

drop view bart2;

drop view vat;

drop view vmb;

drop view vma;

drop table mitv;

drop table miarticulo;

El modificador WITH CHECK OPTION obliga a cualquier operación que se haga sobre la vista a

cumplir las condiciones del where: si "bart2" no tuviera esa opción, podríamos insertar en la vista

cualquier artículo que, finalmente, se almacenaría en "miarticulo", el artículo existiría en mi base

de datos pero no podría verlo por la definición de "bart2"; con WITH CHECK OPTION, el sistema no

5 de 9

17/05/2013 14:01

https://sites.google.com/site/fbddocs/practicas-sql-espanol/vistas-y-tabl...

me deja insertar más que artículos cuyo "cod" empiece por 'B'.

Tablas temporales

Nos hemos tomado la libertad de considerar tablas temporales tanto las tablas temporary en MySQL, como

las subconsultas utilizadas como columna en la select o como tabla en el from.

Se entiende por tabla temporal aquella que se crea y utiliza en un contexto limitado, bien sea una orden

concreta de SQL (el caso genérico de las subconsultas) o una sesión o conexión. Las tablas TEMPORARY, por

ejemplo, son objetos que desaparecen automáticamente cuando se cierra la sesión de usuario. Una

subconsulta es accesible solo mientras se ejecuta una determinada orden. En esta lección solo vamos a

tratar los casos de subconsultas, las tablas TEMPORARY no necesitan más explicación.

Subconsultas como columnas calculadas

Una consulta puede utilizarse como una columna más de cualquier consulta, solo hay que tener la

precaución de darle nombre. Aunque MySQL no lo necesita realmente, parece conveniente utilizar

la palabra reservada AS para mejorar la comprensión de cada parte de la consulta. Por ejemplo, la

siguiente orden genera una tabla con 2 columnas a partir de subconsultas completas:

use tiendaonline;

select

(select max(pvp) from articulo) as mart,

(select max(precio) from linped) as mlinped;

En estos casos se utilizan las subconsultas para realizar cálculos sobre ellas y dentro de una consulta

sobre un cierto conjunto de filas:

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;

Subconsultas en el from

A veces es necesario incluir una subconsulta como una tabla más a enlazar en otra consulta de orden

superior. Nuevamente, hay que darle nombre a esa tabla temporal para poder hacer referencia a

ella. En este caso, aprovechamos la consulta anterior para utilizar en otra más compleja:

select tv.cod,resolucion,tdt,tpcpvp

from tv,

(select cod,pvp,

pvp/(select max(pvp) from articulo)*100 as tpcpvp,

6 de 9

17/05/2013 14:01

https://sites.google.com/site/fbddocs/practicas-sql-espanol/vistas-y-tabl...

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 tabla

Lo que en otros sistemas se conoce como columnas PIVOT (en SQL Server, por ejemplo) consiste en

construir columnas a partir de la información contenida en las filas. Aquí vamos a calcular cuántos

pedidos se han realizado en los meses de octubre, noviembre y diciembre de cualquier año. Antes,

vamos a comprobar los datos de