Mysql Vistas

Introducción

• Una vista es una tabla 'virtual' formada a partir de las tablas reales de una base de datos.

• Recordar que cuando vimos los diferentes visiones de representación de una base de datos (física, conceptual y externas), las vistas se corresponden a las visiones externas.

• Una vista se forma a partir de una o varias tablas.

La vista tendrá un nombre asociado y una serie de columnas a partir de las columnas de las tablas reales subyacentes.

• Las vistas pueden ser utilizadas:

• Como mecanismo de seguridad ya que los usuarios no tienen acceso directo a las tablas de la base de datos.
• Como una forma de simplificar el acceso a las datos de la base de datos, ya que podemos tener vistas formadas por los JOINS de diferentes tablas.

Esto al usuario le será transparente y sólo verá una tabla con los datos que necesita obtener.

• Al igual que sucede con las tablas podemos 'proteger' el acceso a las vistas y a las diferentes operaciones sobre ella (SELECT, UPDATE, INSERT y DELETE).

• Una vista puede estar formada por tablas, otras vistas, subconsultas y joins.

• Podemos realizar operaciones sobre las vistas que 'afecten' a las tablas que conforman la vista, pero con las siguientes limitaciones:

• No se pueden utilizar tablas temporales.
• No usar cláusulas GROUP BY o HAVING.
• No usar joins externos (LEFT JOIN / RIGHT JOIN)
• No usar subconsultas correlacionadas.
• En el caso de utilizar cláusulas JOIN se podrán actualizar o insertar en las tablas siempre que los datos a modificar/insertar hagan referencia a una única tabla.
• Tiene que haber una relación 1 a 1 entre el registro de la vista a actualizar y el registro de la tabla relacionada.

• Si una vista hace referencia a columnas derivadas, no se podrá añadir una fila, pero sí se podrá actualizar siempre que se referencien a las columnas que no son derivadas.
• Si se añade una fila se tendrán que referenciar al menos todas las columnas de la tabla que no tengan un valor por defecto.

• Una vista no puede ser 'asociada' a un trigger.

Tenéis más limitaciones en el enlace del CREATE VIEW puesto anteriormente.

Creación de una vista

• La orden SQL para crear una vista es CREATE VIEW.

• Creando una vista en Mysql
• 

  En MysqlWorkBench debemos ir a la sección de vistas y pulsar el botón derecho del ratón, escogiendo la opción CREATE VIEW.

• 

  En PhpMyAdmin debemos de escoger la base de datos y una vez escogida, en la sección de vistas, escoger la opción Nueva.

• Para poder crear una vista necesitamos tener el permiso CREATE VIEW y el permiso SELECT sobre las columnas que conforman la vista.
• Si queremos utilizar la opción CREATE OR REPLACE necesitaremos también el permiso DROP.

• Aunque no vimos esta opción (también está en los procedimientos almacenados), la cláusula SECURITY puede ser utilizada para indicar que la vista se ejecute con los permisos de quien la utiliza (opción INVOKER) o que sea ejecutada con los permisos de quién creó la vista (opción DEFINER).
• La cláusula 'DEFINER' permite indicar quien es el usuario que 'crea' la vista. Si queremos indicar un usuario diferente a quien está definiendo la vista debemos de poseer el permiso SUPER.
• La cláusula 'WITH CHECK OPTION' hace referencia a que si se utiliza, sólo se podrán actualizar o insertar registros que cumplan las condiciones establecidas en la vista (la cláusula WHERE de la vista).

Dispone de dos opciones: LOCAL o CASCADE. Por defecto utiliza la opción CASCADE. Lo que indican estas opciones es si queremos que la comprobación la realiza sólo en la vista que estamos definiendo (LOCAL) o si nuestra vista hace referencia a otras vistas, que verifique en esas otras vistas (CASCADE) las condiciones para realizar la operación de INSERT/UPDATE)

• Podéis consultar para que sirve la cláusula ALGORITHM en este enlace.

Con MERGE y TEMPTABLE se crea una tabla temporal y con UNDEFINIED es Mysql el que decide cual de los dos debe utilizarse (normalmente escoge MERGE para poder actualizar la vista).

Con TEMPTABLE se crea una tabla temporal con los datos de la vista y es sobre ella sobre la que se realizan las consultas.

La principal desventaja de utilizar TEMPTABLE es que la vista no es actualizable y como principal ventaja, el acceso por parte de otras consultas a la tabla original es más rápida ya que los bloqueos que pueda provocar la vista por sus accesos son más limitados (son de solo lectura).

• La forma más sencilla de crear una vista sería:

1 CREATE VIEW miVista
2 AS
3  SELECT col
4  FROM TABLA;

Si no indicamos el nombre de las columnas, cada columna de la vista tendrá el mismo nombre que las columnas del SELECT.

• Veamos ejemplos aplicados sobre la base de datos employees.

• Creamos una vista para mostrar los empleados que están actualmente trabajando:

1 CREATE VIEW `sueldos_actuales` AS
2     SELECT salary
3     FROM salaries
4     WHERE to_date > CURDATE()

En este caso estamos ocultando información al usuario que use esta vista, ya que le mostramos los salarios pero no puede averiguar a quien pertenece cada uno.

Podemos saber cuanto gasta la empresa en salarios con los empleados actuales:

1 select sum(salary)
2 from sueldos_actuales;

Resultado:

• En este caso, creamos una vista para mostrar los datos de los empleados junto con el nombre del departamento en el que trabaja.

Un usuario que acceda a esta vista no sabrá que para obtener esos datos tiene que hacer un JOIN con dos tablas. Es transparente para él.

1 CREATE VIEW `empleados_departamentos` 
2 AS
3 	SELECT dept_name,emp_no,from_date,to_date
4 	FROM dept_emp INNER JOIN departments ON (departments.dept_no = dept_emp.emp_no)
5 	ORDER BY dept_name,emp_no

Ahora sobre esta vista (siempre que se tenga permiso SELECT sobre la misma) se podrá acceder a sus columnas e incluir cláusula WHERE, ORDER BY,... exactamente igual a que si tuviéramos una tabla.

1 select *
2 from empleados_departamentos;

Resultado:

Modificar una vista

• La orden SQL que modifica una vista es ALTER VIEW.

• Son necesarios los permisos CREATE VIEW y DROP.

• En función de las opciones de modificación, serán necesarios los mismos permisos que necesitamos con la orden CREATE VIEW.

• Por ejemplo:

1 ALTER VIEW `sueldos_actuales` AS
2     SELECT `salaries`.`salary` AS `salary`
3     FROM `salaries`
4     WHERE (`salaries`.`to_date` >= CURDATE())
5     ORDER BY salary;

Borrar una vista

• La orden SQL para eliminar una vista es DROP VIEW.

• Necesitamos el permiso DROP.

Consultar una vista

• Para ver la definición de una vista debemos ejecutar la orden SQL SHOW CREATE VIEW.

• Necesitamos tener los permisos SHOW VIEW y SELECT sobre la vista.

• Como siempre toda esta información es guardada en el diccionario de datos, concretamente en la tabla VIEWS de la base de datos INFORMATION_SCHEMA, por lo que podríamos acceder directamente a ella de la forma:

1 SELECT VIEW_DEFINITION
2 FROM INFORMATION_SCHEMA.VIEWS
3 WHERE TABLE_SCHEMA = 'employees';

Ejercicios propuestos

-- Ángel D. Fernández González -- (2017).