Views

A view is an object defined 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 MariaDB/MySQL can be asked at https://mariadb.com/kb/en/views/.

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 mybd; -- your database, the one on which you have permission to create views

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'; -- ¿por qué no se obtiene ninguna fila?

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

select * from vmb where cod='B002';  -- ¿por qué no se obtiene ninguna fila?

select * from miarticulo where cod='B002';  -- sin embargo, la inserción sí se ha realizado

select * from vma where cod='B002'; -- y en esta vista sí aparece...

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

-- recordando las vistas

select * from vma where cod in ('B001','B002');

select * from vmb where cod in ('B001','B002'); -- no obtiene nada, recuerda la definición de vmb

-- borrando a través de las vistas

select * from bart; -- estado inicial

/*4*/ delete from vma where cod='B001';

select * from bart; -- estado tras el primer borrado

/*5*/ delete from vmb where cod='B002';

select * from bart; -- estado final

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;

-- ejecuta una a una estas instrucciones

/*8*/ insert into vat values ('B003','OtroMás',1100,null,null); --Error: Can not insert into join view without fields list

/*9*/ insert into vat (cod,nombre,pvp,resolucion,tdt) values ('B003','OtroMás',1100,null,null); --Can not modify more than one base table through a join view

/*10*/ insert into vat (cod,nombre,pvp) values ('B003','OtroMás',1100);

-- ¿dónde ha ido a parar la inserción /*10*/

select * from vat where cod = 'B003';

select * from mitv where cod = 'B003';

select * from miarticulo where cod = 'B003';

In general, MariaDB/MYSQL does not support inserts into views that would affect more than one table, or let's say it becomes difficult to do so. The view "vat" is defined as a join of two tables, "myarticle" and "mitv". Therefore, the /*8*/ insert fails because we have not specified specific columns; the /*9*/ insert fails because we have specified columns from both tables. The /*10*/, however, has been executed without problems because it only contains columns from "myarticle". In addition, the view handles the primary key of "myarticle". In other words, we have actually inserted into "myarticle".

With the view "vat" we will never be able to make updates to "mitv" because the view does not include the primary key of the table "mitv".

select * from vat;

select * from bart;

-- estas dos inserciones fallarán

/*11*/ insert into vat (resolucion,tdt) values ('800x600',1); -- ¿con qué valor de mitv.cod?

/*12*/ insert into vat (cod,resolucion,tdt) values ('B004','800x600',1); -- en realidad son miarticulo.cod, mitv.resolucion, mitv.tdt

/*13*/ delete from vat where cod='A0694'; --error: Can not delete from join view

-- comprobamos que no se ha ejecutado nada

select * from vat;

select * from bart;

/*14*/ update vat set pvp = 999 where cod='A0694'; -- ok

/*15*/ update vat set resolucion = '800x600',tdt=1 where cod='A0694'; -- ok

/*16*/ update vat set pvp = 850, resolucion = 'ninguna',tdt=1 where cod='A0694'; -- error: Can not modify more than one base table through a join view

select * from vat;

/*17*/ update vat set cod = 'B004' where cod='A0694'; -- error: solo vemos miarticulo.cod, y no hemos definido ON UPDATE para la clave ajena en mitv (foreign key (cod) references miarticulo (cod))

-- comprueba el estado final

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;

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

/*19*/create view bart2 as select * from miarticulo where cod like 'B%' with check option;

select * from bart;

select * from bart2;

/*20*/insert into bart2 (cod,nombre,pvp) values ('B010','Artículo B10',1999); -- ok

/*21*/insert into bart2 (cod,nombre,pvp) values ('C010','Artículo C10',1999); -- error 

select * from bart;

select * from bart2;

select * from miarticulo where pvp=1999;

/*22*/insert into bart (cod,nombre,pvp) values ('C010','Artículo C10',1999); -- ok

select * from bart;

select * from bart2;

select * from miarticulo where pvp=1999;

The WITH CHECK OPTION modifier forces the where conditions to be fulfilled, whatever the operation to be executed on the view: if "bart2" did not have that option, we could insert in the view any article that, finally, would be stored in "myarticle". However, although the item would exist in my database, I would not be able to see it by querying the view because of the definition of "bart2". With WITH CHECK OPTION the system won't let me insert anything but articles whose "cod" starts with 'B'. 

In the example above, the command /*20*/ could be executed, in bart2, because the article code starts with 'B'.

The command /*21*/ could not be executed because bart2 does not support working with codes starting with another letter.

However, the bart view, in the /*22*/ command, does allow the insertion that bart2 did not allow in /*21*/. That is the difference between using with check option or not.

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;