Vistas
## Objetivos de Aprendizaje
- Crear y usar vistas
- Entender los beneficios de las vistas
- Actualizar a través de vistas
- Eliminar vistas de forma segura
## ¿Qué es una Vista?
Una tabla virtual basada en una consulta:
```sql
CREATE VIEW active_employees AS
SELECT
first_name,
last_name,
department,
salary
FROM employees
WHERE is_active = TRUE;
```
### Cómo Funcionan las Vistas
```text
employees table
+-----------+-------------+
| id | name | is_active |
+-----------+-------------+
| 1 | Alice| true |
| 2 | Bob | false |
| 3 | Carol| true |
+-----------+-------------+
active_employees view
+-----------+-------------+
| name | department |
+-------+---------------+
| Alice| Engineering |
| Carol| Sales |
+-------+---------------+
```
## Creando Vistas
### Vista Básica
```sql
CREATE VIEW sales_employees AS
SELECT
employee_id,
first_name,
last_name,
salary
FROM employees
WHERE department = 'Sales';
```
### Vista Compleja
```sql
CREATE VIEW department_summary AS
SELECT
d.department_name,
COUNT(e.employee_id) AS headcount,
AVG(e.salary) AS avg_salary,
MIN(e.salary) AS min_salary,
MAX(e.salary) AS max_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name;
```
## Usando Vistas
### Consultar una Vista
```sql
SELECT *
FROM active_employees
ORDER BY last_name;
```
### Vista en JOIN
```sql
SELECT
v.employee_name,
d.department_name
FROM sales_employees v
JOIN departments d ON v.department_id = d.id;
```
## Tipos de Vistas
### Vistas Actualizables
Pueden INSERT, UPDATE, DELETE:
```sql
-- Vista simple de tabla única
CREATE VIEW simple_view AS
SELECT first_name, last_name, email
FROM employees;
```
### Vistas No Actualizables
No se pueden modificar:
- Vistas con funciones de agregación
- Vistas con DISTINCT
- Vistas con GROUP BY
- Vistas con UNION
- Vistas de múltiples tablas
## INSERT a través de Vista
### Vista Simple
```sql
INSERT INTO simple_view (first_name, last_name, email)
VALUES ('Dave', 'Brown', 'dave@company.com');
```
### Con CHECK OPTION
```sql
CREATE VIEW active_employees AS
SELECT *
FROM employees
WHERE is_active = TRUE
WITH CHECK OPTION;
-- Este INSERT falla (is_active = FALSE violaría el WHERE)
INSERT INTO active_employees
VALUES (4, 'Dave', 'Brown', 'dave@company.com', 'Sales', FALSE);
```
## DROP VIEW
### Eliminación Básica
```sql
DROP VIEW sales_employees;
```
### IF EXISTS
```sql
DROP VIEW IF EXISTS sales_employees;
```
### CASCADE
```sql
DROP VIEW sales_employees CASCADE;
```
## Beneficios de las Vistas
### Simplificar Consultas Complejas
```sql
-- En lugar de escribir consulta compleja cada vez:
CREATE VIEW monthly_sales AS
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS total_sales,
COUNT(*) AS num_sales
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);
-- Fácil de consultar:
SELECT * FROM monthly_sales ORDER BY month;
```
### Seguridad
```sql
-- Solo permitir acceso a ciertas columnas
CREATE VIEW employee_public AS
SELECT first_name, last_name, department
FROM employees;
```
### Abstracción de Datos
```sql
-- Cambiar tabla subyacente sin cambiar aplicación
CREATE VIEW employee_list AS
SELECT employee_id, first_name, last_name
FROM employees_v2; -- Apunta a nueva tabla
```
## Reemplazando Vistas
### OR REPLACE
```sql
CREATE OR REPLACE VIEW active_employees AS
SELECT
employee_id,
first_name,
last_name,
salary,
hire_date
FROM employees
WHERE is_active = TRUE;
```
### Limitaciones
Debe preservar el mismo conjunto y nombres de columnas.
## Vistas del Sistema
### MySQL
```sql
-- Ver todas las vistas
SELECT * FROM information_schema.views;
-- Ver definiciones de vistas
SHOW CREATE VIEW view_name;
```
### PostgreSQL
```sql
-- Ver todas las vistas
SELECT * FROM information_schema.views;
-- Ver definición
SELECT view_definition FROM information_schema.views
WHERE table_name = 'view_name';
```
## Vistas Materializadas
### PostgreSQL (No SQL Estándar)
```sql
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('month', sale_date);
-- Actualizar cuando sea necesario
REFRESH MATERIALIZED VIEW monthly_sales;
```
### Diferencia
| Vista | Vista Materializada |
|-------|---------------------|
| Virtual (consulta se ejecuta cada vez) | Física (resultados almacenados) |
| Siempre actualizada | Necesita actualización |
| Sin costo de almacenamiento | Almacena datos en disco |
## Resumen
- Las vistas son tablas virtuales basadas en consultas
- Simplifican consultas complejas
- Proporcionan capa de seguridad
- Pueden ser actualizables (vistas simples) o solo lectura
- Usar **WITH CHECK OPTION** para imponer cláusulas WHERE
- **DROP VIEW** elimina vistas
- **CREATE OR REPLACE** actualiza definiciones de vistas
- Las vistas materializadas almacenan datos reales (PostgreSQL)
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →