Mysql Vistas

De MediaWiki
Ir a la navegación Ir a la búsqueda

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 (columnas que se forman a partir de otras existentes aplicando una expresión, como por ejemplo,5 as col1, col1+5, upper(col1),col1/2,col1+col2,...), 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 establecido en la definición.


  • Una vista no puede ser 'asociada' a un trigger.
Tenéis más limitaciones en el enlace del CREATE VIEW puesto a continuación.



Creación de una vista


  • 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.



  • 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:
Mysql vistas 4.jpg



  • 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.dept_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:
Mysql vistas 3.jpg



Modificar una vista

  • La orden SQL que modifica una vista es ALTER VIEW.
  • 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.




Consultar una vista

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


  • 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

  • Crea una vista que muestre los nombres de los empleados que tengan un salario por encima de los 85.000 euros.
Crea un usuario nuevo (o usa uno de los ya creados) y dale permisos para que pueda usar la vista (seleccionar).
Conéctate con dicho usuario y comprueba que puede hacer uso de la vista.


  • Crea una vista que muestre los datos de la tabla 'titles' pero sólo de los empleados que siguen trabajando actualmente.
Prueba a añadir, borrar o modificar alguno de los datos que obtiene dicha vista.
Modifica la vista para que verifique que los datos nuevos cumplan las condiciones de la vista.
Crea un usuario nuevo (o usa uno de los ya creados) y dale permisos para que pueda usar la vista (seleccionar, modificar y borrar, pero no insertar).


  • Crea una vista que muestre los datos de los empleados que pertenecen al 'staf'. Los datos a mostrar son los que se encuentran en la tabla 'employees'.
Crea una vista, que haciendo uso de la vista anterior, muestre los nombres de los empleados y el departamento en el que trabaja (su nombre).
¿ Qué datos podrían ser actualizados/añadidos en la vista anterior ?


  • Crea un usuario nuevo (o usa uno de los ya creados) tenga permiso para crear vistas en la base de datos employees.
Con el usuario anterior, crea una vista que muestre los empleados que hayan nacido entre 1950 y 1955 y haz que dicha vista se ejecute con los permisos del usuario que la utilice.
Crea un usuario nuevo (o usa uno de los ya creados) y dale permisos para que pueda hacer todas las operaciones sobre la vista.
Conectándote con el usuario anterior, comprueba si puede realizar dichas operaciones. En caso contrario dale los permisos necesarios.
Utilizando la vista anterior, crea otra vista con los sueldos y nombre completos y nombre de los departamentos donde trabajan los empleados anteriores.
¿ Esta vista se puede actualizar ?


  • Crea una vista que haga uso de dos tablas y que permita realizar operaciones de modificación sobre las mismas.






Solución ejercicios propuestos

  • Crea una vista que muestre los datos de los empleados que pertenecen al 'staf'. Los datos a mostrar son los que se encuentran en la tabla 'employees'.
Crea una vista, que haciendo uso de la vista anterior, muestre los nombres de los empleados y el departamento en el que trabaja (su nombre).
¿ Qué datos podrían ser actualizados/añadidos en la vista anterior ? Haz un ejemplo.
1 CREATE VIEW `empleados_del_staff` 
2 AS
3     SELECT employees.emp_no,birth_date,first_name,last_name,gender,hire_date
4     FROM employees INNER JOIN titles ON (employees.emp_no=titles.emp_no)
5     WHERE titles.title = 'staff'


La segunda vista hace uso de la primera: 
1 CREATE  OR REPLACE VIEW `empleados_departamentos_staff` 
2 AS
3     SELECT dept_name,first_name,last_name
4     FROM empleados_del_staff INNER JOIN dept_emp ON (empleados_del_staff.emp_no = dept_emp.emp_no)
5 		             INNER JOIN departments ON (departments.dept_no = dept_emp.dept_no)
6     ORDER BY dept_name,first_name,last_name;
Mostraría también los empleados antiguos ya que no hacemos uso de la condición 'to_date'.
No pueden actualizarse datos, ya que hacen referencia a tablas (employees, departments) en las que no podemos acceder al resto de los campos y estos no tienen un valor por defecto.






Enlace a la página principal de la UD3

Enlace a la página principal del curso


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

Obtenido de «https://wiki.cifprodolfoucha.es/index.php?title=Mysql_Vistas&oldid=10698»