Inicio‎ > ‎SQL‎ > ‎SQL (english)‎ > ‎

T09 Views

Views

For this section it is necessary to connect to the server with a user allowed to create views.

A view is an object defines from a query and behaviouring as a table. However, depending on the query in which is based, it can do more or less things: to query a view is always possible, but inserting, updating or deleting depends on the definition of the view.

To create a view we have the order:

CREATE VIEW nombrevista AS consulta

A view is a persisten object, therefore, in order to eliminate the view from the catalog, we must execute:

DROP VIEW nombrevista

Besides, it is possible to query the definition of the view (although this a particular for MySQL, other engines have their own methods):

SHOW CREATE VIEW nombrevista

All the information about views in MySQL can be asked at http://dev.mysql.com/doc/refman/5.0/en/views.html.

This session has been presented in a demonstration format and therefore, executing the following sentences is recommended, and moreover, looking for the reasons of some of them working properly and some of them not.


After connecting with the server it is necessary to choose the database that allows us to perform views:

use mibd;


We will copy the contents of some tables of TiendaOnLine to local tables that are from our ownership

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;


We create the first view:

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


A view has the same behaviour as a table and can be queried.

select * from vma where pvp between 500 and 700;


A view can be generated from any kind of query and it has the added feature that it can restrict the access to only a subset of possible rows.

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;



Inserting

A view, under certain conditions, allows to insert new data.

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


Deleting

We create another view to make easy the checking of the actions we order. In this case we will try to delete rows.

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;


Updating

Now we will try the statement update. This statement has the same restrictions as 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;


 

 


With more than one table

A view can be defined over various tables, but this doesn't affect to the statements can be executed and in which conditions.


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

We will redefine bart and create another view 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;

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

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;


Finally, we eliminate all the views and tables created.

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

Comments