Vistas y tablas virtuales de MySQL

Introducción

En este laboratorio, aprenderá cómo trabajar con vistas de MySQL, que son tablas virtuales basadas en el conjunto de resultados de una declaración SQL. El laboratorio cubre la creación de una vista a partir de una consulta SELECT, la actualización de la definición de la vista utilizando ALTER VIEW, la consulta de datos a través de la vista y, finalmente, la eliminación de la vista para limpiar.

Comenzará conectándose a un servidor MySQL, creando una base de datos llamada company y llenándola con una tabla employees. Luego, creará una vista llamada sales_employees que filtre la tabla employees para mostrar solo a los empleados del departamento de 'Ventas'. Aprenderá cómo verificar la estructura de la vista y consultarla como si fuera una tabla normal.

Skills Graph

Crear una vista a partir de una consulta SELECT

En este paso, aprenderá cómo crear una vista en MySQL a partir de una consulta SELECT. Una vista es una tabla virtual basada en el conjunto de resultados de una declaración SQL. Las vistas son útiles para simplificar consultas complejas, ocultar la complejidad de los datos y proporcionar un nivel de abstracción para el acceso a los datos.

Primero, conectémonos al servidor MySQL. Abra una terminal en la máquina virtual de LabEx. Ya debería estar en el directorio ~/project.

mysql -u root -p

Cuando se le solicite, ingrese la contraseña de root. Si no ha establecido una contraseña de root, simplemente presione Enter.

Ahora, creemos una base de datos llamada company.

CREATE DATABASE company;

A continuación, cambiemos a la base de datos company.

USE company;

Creemos una tabla simple llamada employees con algunos datos de muestra.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

INSERT INTO employees (id, first_name, last_name, salary, department) VALUES
(1, 'John', 'Doe', 60000.00, 'Sales'),
(2, 'Jane', 'Smith', 75000.00, 'Marketing'),
(3, 'Robert', 'Jones', 50000.00, 'Sales'),
(4, 'Emily', 'Brown', 80000.00, 'Engineering'),
(5, 'Michael', 'Davis', 65000.00, 'Marketing');

Ahora, creemos una vista llamada sales_employees que muestre solo a los empleados del departamento de 'Ventas'.

CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

Esta declaración crea una vista llamada sales_employees. La vista selecciona las columnas id, first_name, last_name y salary de la tabla employees, pero solo para las filas donde el department es 'Sales'.

Para verificar que se haya creado la vista, puede describirla:

DESCRIBE sales_employees;

Este comando mostrará la estructura de la vista sales_employees, similar a describir una tabla.

También puede consultar la vista como si fuera una tabla normal:

SELECT * FROM sales_employees;

Esto mostrará a todos los empleados del departamento de Ventas, según los datos de la tabla employees.

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
|    3 | Robert     | Jones     | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)

Ahora ha creado con éxito una vista a partir de una consulta SELECT.

Actualizar la definición de la vista con ALTER VIEW

En este paso, aprenderá cómo actualizar la definición de una vista existente utilizando la declaración ALTER VIEW en MySQL. Esto es útil cuando necesita modificar las columnas seleccionadas, los criterios de filtrado o cualquier otro aspecto de la consulta subyacente de la vista.

Continuando desde el paso anterior, debería seguir conectado al servidor MySQL y utilizando la base de datos company. Si no es así, vuelva a conectarse utilizando:

mysql -u root -p

Ingrese la contraseña si se le solicita, y luego:

USE company;

Recuerde que creamos una vista llamada sales_employees que muestra a los empleados del departamento de 'Ventas'. Digamos que queremos agregar la columna department a esta vista. Podemos utilizar la declaración ALTER VIEW para modificar la definición de la vista.

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';

Esta declaración modifica la vista sales_employees para incluir la columna department en el conjunto de resultados. La declaración ALTER VIEW es muy similar a la declaración CREATE VIEW; simplemente reemplace CREATE con ALTER.

Para verificar que la vista se haya actualizado, puede describirla nuevamente:

DESCRIBE sales_employees;

Ahora debería ver la columna department en la salida.

También puede consultar la vista actualizada:

SELECT * FROM sales_employees;

Ahora se mostrará la columna department junto con las otras columnas de los empleados del departamento de Ventas.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
|    3 | Robert     | Jones     | 50000.00 | Sales      |
+------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)

Modifiquemos la vista nuevamente para incluir a los empleados con un salario superior a 55000 en el departamento de Ventas.

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales' AND salary > 55000;

Ahora, consulte la vista actualizada:

SELECT * FROM sales_employees;

Ahora se mostrarán los empleados del departamento de Ventas con un salario superior a 55000.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
+------+------------+-----------+----------+------------+
1 row in set (0.00 sec)

Ahora ha actualizado con éxito la definición de una vista utilizando la declaración ALTER VIEW.

Consultar datos utilizando la vista

En este paso, aprenderá cómo consultar datos utilizando la vista que creó y modificó en los pasos anteriores. Las vistas se pueden consultar de la misma manera que las tablas normales, lo que le permite recuperar datos específicos según la definición de la vista.

Continuando desde el paso anterior, debería seguir conectado al servidor MySQL y utilizando la base de datos company. Si no es así, vuelva a conectarse utilizando:

mysql -u root -p

Ingrese la contraseña si se le solicita, y luego:

USE company;

Recuerde que tenemos una vista llamada sales_employees que actualmente muestra a los empleados del departamento de 'Ventas' con un salario superior a 55000.

Para recuperar todos los datos de la vista sales_employees, puede utilizar una simple declaración SELECT:

SELECT * FROM sales_employees;

Esto mostrará todas las columnas y filas que satisfacen la definición de la vista. Según el paso anterior, debería ver solo al (los) empleado(s) del departamento de Ventas con un salario superior a 55000.

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
+------+------------+-----------+----------+------------+
1 row in set (0.00 sec)

También puede utilizar cláusulas WHERE para filtrar aún más los datos recuperados de la vista. Por ejemplo, para encontrar al empleado con id = 1 en la vista sales_employees:

SELECT * FROM sales_employees WHERE id = 1;

Esto devolverá solo la fila donde el id es 1, pero solo si ese empleado también satisface la definición de la vista (departamento de Ventas y salario superior a 55000).

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
+------+------------+-----------+----------+------------+
1 row in set (0.00 sec)

También puede seleccionar columnas específicas de la vista:

SELECT first_name, last_name FROM sales_employees;

Esto solo mostrará las columnas first_name y last_name de los empleados en la vista sales_employees.

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
+------------+-----------+
1 row in set (0.00 sec)

También puede utilizar funciones de agregación con las vistas. Por ejemplo, para encontrar el salario promedio de los empleados en la vista sales_employees:

SELECT AVG(salary) FROM sales_employees;

Esto calculará y mostrará el salario promedio.

+-------------+
| AVG(salary) |
+-------------+
|  60000.0000 |
+-------------+
1 row in set (0.00 sec)

Al consultar la vista, en realidad está consultando la tabla employees subyacente, pero con las restricciones definidas en la vista. Esto simplifica las consultas complejas y proporciona un nivel de abstracción.

Eliminar la vista para limpiar

En este paso, aprenderá cómo eliminar la vista que creó en los pasos anteriores. Eliminar una vista la quita de la base de datos. Es importante limpiar los recursos cuando ya no se necesitan.

Continuando desde el paso anterior, debería seguir conectado al servidor MySQL y utilizando la base de datos company. Si no es así, vuelva a conectarse utilizando:

mysql -u root -p

Ingrese la contraseña si se le solicita, y luego:

USE company;

Tenemos una vista llamada sales_employees que creamos y modificamos. Para eliminar esta vista, utilice la declaración DROP VIEW:

DROP VIEW sales_employees;

Esta declaración elimina la vista sales_employees de la base de datos company.

Para verificar que la vista se ha eliminado, puede intentar describirla:

DESCRIBE sales_employees;

Esto debería devolver un mensaje de error que indique que la tabla (o vista) no existe.

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

Como alternativa, puede intentar seleccionar datos de la vista:

SELECT * FROM sales_employees;

Esto también devolverá un mensaje de error que indique que la tabla (o vista) no existe.

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

Finalmente, eliminemos la tabla employees y la base de datos company para limpiar por completo.

DROP TABLE employees;
DROP DATABASE company;

Ahora ha eliminado con éxito la vista y ha limpiado la base de datos.

Resumen

En este laboratorio, aprendiste cómo crear una vista en MySQL a partir de una consulta SELECT. Esto implicó conectarse al servidor MySQL, crear una base de datos y una tabla llamada employees con datos de muestra. Luego, creaste una vista llamada sales_employees que filtra la tabla employees para mostrar solo a los empleados del departamento de 'Ventas'.

El laboratorio demostró cómo definir una vista utilizando la declaración CREATE VIEW, especificando las columnas a incluir y los criterios de filtrado. También aprendiste cómo verificar la creación de la vista utilizando DESCRIBE y cómo consultar la vista como si fuera una tabla normal utilizando SELECT.