Vistas y Tablas Virtuales de MySQL

Introducción

En este laboratorio, aprenderá a trabajar con vistas de MySQL. Una vista es una tabla virtual basada en el conjunto de resultados de una sentencia 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. Aprenderá a crear, actualizar, consultar y eliminar vistas de MySQL.

Comenzará conectándose a un servidor MySQL, creando una base de datos llamada company y poblándola con una tabla employees. Luego, creará una vista llamada sales_employees que filtra la tabla employees para mostrar solo a los empleados del departamento de 'Sales'. Aprenderá a verificar la estructura de la vista y a consultarla como una tabla normal.

Conectar a MySQL y Crear Base de Datos/Tabla

En este paso, se conectará al servidor MySQL y configurará la base de datos y tabla necesarias para el laboratorio. Esto implica crear una base de datos llamada company y una tabla employees dentro de ella, poblada con algunos datos de ejemplo.

Primero, abra una terminal en la VM de LabEx. Debería estar ya en el directorio ~/project.

Conéctese al servidor MySQL como usuario root. Dado que tiene privilegios de sudo y el usuario root en MySQL está configurado para usar el plugin auth_socket, puede conectarse sin contraseña usando sudo.

sudo mysql -u root

Ahora se encuentra en el shell de MySQL. El prompt cambiará a mysql>.

Dentro del shell de MySQL, cree una base de datos llamada company:

CREATE DATABASE company;

Cambie a la base de datos company para que los comandos subsiguientes operen dentro de esta base de datos:

USE company;

Ahora, cree una tabla llamada employees para almacenar información de los empleados:

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

Inserte algunos datos de ejemplo en la tabla employees:

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

Puede verificar los datos en la tabla employees seleccionando todas las filas:

SELECT * FROM employees;

La salida debería mostrar los datos de los empleados insertados:

+----+------------+-----------+----------+-------------+
| id | first_name | last_name | last_name | department  |
+----+------------+-----------+----------+-------------+
|  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   |
+----+------------+-----------+----------+-------------+
5 rows in set (0.00 sec)

Se ha conectado exitosamente a MySQL, ha creado una base de datos y ha poblado una tabla con datos.

Crear una Vista

En este paso, creará una vista basada en la tabla employees. Una vista es una consulta SQL almacenada que actúa como una tabla virtual. No almacena datos en sí misma, sino que presenta datos de una o más tablas subyacentes.

Debería seguir en el shell de MySQL, conectado a la base de datos company. Si no es así, vuelva a conectarse usando sudo mysql -u root y luego USE company;.

Crearemos una vista llamada sales_employees que mostrará solo a los empleados que trabajan en el departamento de 'Sales'. Esta vista seleccionará columnas específicas de la tabla employees.

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

Esta sentencia define una vista llamada sales_employees. La definición de la vista es la consulta SELECT que sigue a la palabra clave AS. Esta consulta selecciona las columnas id, first_name, last_name y salary de la tabla employees, pero solo para las filas donde la columna department es igual a 'Sales'.

Para confirmar que la vista ha sido creada, puede listar las tablas y vistas en la base de datos actual:

SHOW TABLES;

Debería ver tanto employees como sales_employees listadas.

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| sales_employees   |
+-------------------+
2 rows in set (0.00 sec)

También puede describir la estructura de la vista, al igual que lo haría con una tabla:

DESCRIBE sales_employees;

Esto mostrará las columnas incluidas en la vista:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Ha creado exitosamente una vista llamada sales_employees.

Consultar Datos Usando la Vista

En este paso, aprenderá a consultar datos utilizando la vista sales_employees que creó. Las vistas se pueden consultar de la misma manera que las tablas normales, proporcionando una forma simplificada de acceder a subconjuntos específicos de datos.

Debería seguir en el shell de MySQL, conectado a la base de datos company. Si no es así, vuelva a conectarse usando sudo mysql -u root y luego USE company;.

Para recuperar todos los datos de la vista sales_employees, utilice una sentencia SELECT estándar:

SELECT * FROM sales_employees;

Esta consulta ejecuta la sentencia SELECT subyacente definida en la vista y devuelve el resultado. Debería ver solo a los empleados del departamento de 'Sales':

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

También puede aplicar filtros o ordenaciones adicionales a los datos recuperados de la vista. Por ejemplo, para encontrar al empleado de ventas con un salario superior a 55000:

SELECT * FROM sales_employees WHERE salary > 55000;

Esto devolverá solo las filas de la vista donde el salario sea superior a 55000:

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
+------+------------+-----------+----------+
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 mostrará solo los nombres y apellidos de los empleados de ventas:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
| Robert     | Jones     |
+------------+-----------+
2 rows in set (0.00 sec)

Consultar una vista es esencialmente lo mismo que consultar una tabla, pero la vista proporciona un filtro predefinido y una selección de columnas de la fuente de datos subyacente.

Actualizar la Definición de la Vista con ALTER VIEW

En este paso, aprenderá a modificar la definición de una vista existente utilizando la sentencia ALTER VIEW. Esto es útil cuando necesita cambiar las columnas incluidas en la vista o los criterios de filtrado.

Debería seguir en el shell de MySQL, conectado a la base de datos company. Si no es así, vuelva a conectarse usando sudo mysql -u root y luego USE company;.

Actualmente, la vista sales_employees incluye id, first_name, last_name y salary. Modifiquemos la vista para incluir también la columna department.

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

Esta sentencia utiliza ALTER VIEW seguido del nombre de la vista y la nueva consulta SELECT que define la vista. La nueva consulta ahora incluye la columna department.

Para verificar que la vista se ha actualizado, descríbala de nuevo:

DESCRIBE sales_employees;

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

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
| department | varchar(50)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Consulte la vista actualizada para ver la nueva columna:

SELECT * FROM sales_employees;

La salida ahora incluirá la columna department:

+------+------------+-----------+----------+------------+
| 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)

Ha actualizado exitosamente la definición de la vista sales_employees.

Eliminar la Vista y Limpiar

En este paso final, aprenderá a eliminar (borrar) la vista y a limpiar la base de datos y la tabla creadas durante este laboratorio.

Debería seguir en el shell de MySQL, conectado a la base de datos company. Si no es así, vuelva a conectarse usando sudo mysql -u root y luego USE company;.

Para eliminar la vista sales_employees, utilice la sentencia DROP VIEW:

DROP VIEW sales_employees;

Este comando elimina permanentemente la vista sales_employees de la base de datos company.

Para confirmar que la vista ha sido eliminada, puede intentar describirla:

DESCRIBE sales_employees;

Esto debería resultar en un mensaje de error que indique que la vista no existe:

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

Ahora, limpiemos la tabla employees y la base de datos company.

Elimine la tabla employees:

DROP TABLE employees;

Elimine la base de datos company:

DROP DATABASE company;

Puede salir del shell de MySQL escribiendo:

exit

Ha eliminado exitosamente la vista, la tabla y la base de datos, limpiando los recursos utilizados en este laboratorio.

Resumen

En este laboratorio, aprendió a trabajar con vistas de MySQL. Comenzó conectándose al servidor MySQL y configurando una base de datos y una tabla. Luego creó una vista llamada sales_employees para proporcionar una vista filtrada de la tabla employees.

Practicó la consulta de la vista utilizando sentencias SELECT, demostrando cómo las vistas simplifican el acceso a los datos. También aprendió a modificar la definición de una vista existente utilizando la sentencia ALTER VIEW para incluir columnas adicionales. Finalmente, aprendió a eliminar una vista utilizando la sentencia DROP VIEW y limpió la base de datos y la tabla.

Ahora comprende los conceptos y operaciones básicos para trabajar con vistas de MySQL, que son herramientas valiosas para administrar y acceder a datos en una base de datos.