9. Curso SQL - Píldoras informáticas

¿Qué es SQL?

• Structured Query Language: Lenguaje estructurado de consultas. Inventado por IBM.
• En un lenguaje para interactuar con BBDD relacionales.

Comandos SQL

• DDL (Data definition language) Se utilizan para crear y modificar la estructura de una base de datos. Comandos: CREATE, ALTER, DROP, TRUNCATE
• DML (Data manipulation language) Se utilizan para seleccionar registros de una base de datos lo que conocemos como consultas también para insertar nuevos registros, actualizar o borrar información. Básicamente se utilizan para hacer consultas de selección o de acción. Comandos: SELECT, INSERT, UPDATE, DELETE
• DCL (Data control language) Se utilizan para proporcionar seguridad a la informacion en la base de datos. Comandos: GRANT, REVOKE
• TCL (Transaction control language) Se utilizan para la gestión de los cambios en los datos. Comandos: COMMIT, ROLLBACK, SAVEPOINT

Clausulas

• FROM: Especifica la tabla de la que se quieren obtener los registros.
• WHERE: Especifica las condiciones o criterios de los registros seleccionados.
• GROUP BY: Para agrupar los registros seleccionados en función de un campo.
• HAVING: Especifica las condiciones o criterios que deben cumplir los grupos.
• ORDER BY: Ordena los registros seleccionados en función de un campo.

Operadores de comparación

Operadores lógicos

Consultas de agrupación o totales

Las consultas de agrupación o totales en SQL se utilizan para realizar cálculos sobre grupos de filas en una tabla o conjunto de resultados. Estas consultas permiten resumir datos, como calcular sumas, promedios, conteos, máximos y mínimos de los datos agrupados, etc., basados en la agrupación de datos por una o más columnas.

Estas consultas utilizan la cláusula GROUP BY junto con funciones de agregación para obtener resultados significativos.

Funciones de agregación

Filtrado de Resultados Agrupados Para filtrar los resultados después de la agrupación, se utiliza la cláusula HAVING, que permite aplicar condiciones sobre los grupos creados. Por ejemplo, si se desea mostrar solo los departamentos con más de 10 empleados:

SELECT departamento, COUNT(*) AS TotalEmpleados
FROM empleados
GROUP BY departamento
HAVING COUNT(*) > 10;

Consideraciones Importantes:

• GROUP BY y Columnas Seleccionadas: Cuando se utiliza GROUP BY, todas las columnas en la cláusula SELECT que no son parte de una función de agregación deben estar incluidas en la cláusula GROUP BY.
• HAVING vs WHERE: WHERE se usa para filtrar filas antes de aplicar GROUP BY, mientras que HAVING se usa para filtrar los grupos resultantes después de aplicar GROUP BY.

Consultas de cálculo

Las consultas de cálculo en SQL son aquellas que realizan operaciones aritméticas o aplican funciones sobre los datos de las tablas para obtener resultados calculados directamente en la consulta. Estos cálculos pueden incluir operaciones matemáticas básicas, funciones de fecha, conversión de datos, entre otros.

Operaciones Aritméticas

Funciones Matemáticas Comunes:

Funciones de Fecha y Hora:

Funciones de Conversión:

Consultas Multitabla o Consultas de Unión

En SQL, las consultas Multitabla (también conocidas como JOINs) y las consultas de Unión (UNION) son dos formas de combinar datos de múltiples tablas en una sola consulta. Cada una tiene un propósito y funcionamiento específico.

Consultas Multitabla

Las consultas multitabla permiten extraer información de más de una tabla en una base de datos. Esto se logra utilizando cláusulas JOIN, que establecen cómo se relacionan las tablas entre sí. Existen varios tipos de JOIN, incluyendo:

Tipos de JOINs:

• INNER JOIN: Devuelve solo las filas que tienen coincidencias en ambas tablas.
• LEFT JOIN (o LEFT OUTER JOIN): Devuelve todas las filas de la tabla de la izquierda y las filas coincidentes de la tabla de la derecha. Si no hay coincidencias, las columnas de la derecha tendrán valores NULL.
• RIGHT JOIN (o RIGHT OUTER JOIN): Similar al LEFT JOIN, pero devuelve todas las filas de la tabla de la derecha.
• FULL JOIN (o FULL OUTER JOIN): Devuelve todas las filas cuando hay coincidencia en una de las tablas, y las filas no coincidentes de ambas tablas con valores NULL donde no hay coincidencias.
• CROSS JOIN: Devuelve el producto cartesiano de ambas tablas, combinando cada fila de la primera tabla con cada fila de la segunda.
• SELF JOIN: Es un JOIN en el que una tabla se une consigo misma. Es útil para comparar filas dentro de la misma tabla.

Consultas de Unión (UNION)

Las consultas de Unión combinan los resultados de dos o más consultas SELECT en un solo conjunto de resultados. A diferencia de los JOINs, las tablas no necesitan estar relacionadas.

Características de las Consultas de Unión

1. Coincidencia de Columnas: Las consultas unidas deben tener el mismo número de columnas, y estas deben ser del mismo tipo de datos.
2. Eliminación de Duplicados: Por defecto, **UNION** elimina duplicados. Si se desea incluir duplicados, se utiliza **UNION ALL**.
3. Ordenamiento: Se puede aplicar un **ORDER BY** al final de la consulta para ordenar el conjunto de resultados combinado.

Comandos:

• UNION: Combina los resultados de dos consultas y elimina las filas duplicadas.
• UNION ALL: Similar a UNION, pero no elimina las filas duplicadas, mostrando todas las filas del conjunto de resultados combinado.

Diferencias clave entre JOIN y UNION:

• JOINs combinan datos de múltiples tablas basándose en una relación definida (una columna común), y el resultado suele incluir columnas de ambas tablas.
• UNION combina el resultado de múltiples consultas en una tabla única, pero las tablas no necesitan estar relacionadas y deben tener el mismo número de columnas en las consultas.

Subconsultas

Las subconsultas en SQL son consultas anidadas dentro de otra consulta principal. Se utilizan para calcular valores que se necesitan en la consulta principal o para filtrar datos basados en criterios complejos. Una subconsulta puede devolver un solo valor, un conjunto de valores, o incluso una tabla completa, dependiendo de cómo se utilice.

Tipos de Subconsultas en SQL

• Subconsultas Escalares (Scalar Subqueries) Devuelven un único valor (una fila y una columna) y se pueden usar donde se espera un solo valor, como en las listas SELECT, las cláusulas WHERE, o HAVING.

  Ejemplo:

  SELECT 
      employee_id, 
      salary,
      (SELECT AVG(salary) FROM employees) AS avg_salary
  FROM 
      employees
  WHERE 
      salary > (SELECT AVG(salary) FROM employees);
  

  En este ejemplo, la subconsulta calcula el salario promedio, y luego se utiliza para filtrar empleados cuyo salario es mayor que el promedio.

• Subconsultas Multivalor (Multi-Row Subqueries) Devuelven más de un valor, pero solo una columna. Se suelen usar con operadores como IN, ANY, o ALL en las cláusulas WHERE.

  SELECT 
      employee_id, 
      name
  FROM 
      employees
  WHERE 
      department_id IN (SELECT department_id FROM departments WHERE location = 'New York');
  

  Aquí, la subconsulta devuelve una lista de department_id que se encuentra en 'New York', y la consulta principal selecciona empleados de esos departamentos.

• Subconsultas Multicolumna (Multi-Column Subqueries) Devuelven más de una columna. Se utilizan generalmente en la cláusula WHERE con operadores de comparación de tuplas, como IN o comparaciones directas entre múltiples columnas.

  Ejemplo:

  SELECT 
      employee_id, 
      name
  FROM 
      employees
  WHERE 
      (department_id, job_title) IN (SELECT department_id, job_title FROM open_positions);
  

  Este ejemplo selecciona empleados cuyos departamentos y títulos coinciden con los de la tabla open_positions.

• Subconsultas Correlacionadas (Correlated Subqueries) Dependen de la consulta principal para su evaluación. Se ejecutan repetidamente para cada fila de la consulta principal, utilizando valores de dicha fila.

  Ejemplo:

  SELECT 
      employee_id, 
      name, 
      salary
  FROM 
      employees e
  WHERE 
      salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
  

  En este ejemplo, la subconsulta correlacionada calcula el salario promedio por departamento para cada empleado y selecciona solo aquellos cuyo salario es superior al promedio de su departamento.

• Subconsultas en la Clausula FROM (Inline Views) Actúan como tablas temporales dentro de la consulta principal. Se colocan en la cláusula FROM y son tratadas como una tabla derivada.

  Ejemplo:

  SELECT 
      department_id, 
      AVG(salary) AS avg_salary
  FROM 
      (SELECT department_id, salary FROM employees WHERE job_title = 'Engineer') AS engineers
  GROUP BY 
      department_id;
  

  Aquí, la subconsulta filtra solo a los ingenieros y la consulta principal calcula el salario promedio por departamento.

Operadores usados en subconsultas

Los operadores que se pueden usar en subconsultas incluyen:

• Operadores básicos de comparación (>, >=, <, ≤, !=, <>, =)
• Predicados ALL y ANY
• Predicados IN y NOT IN
• Predicados EXISTS y NOT EXISTS

Ubicación de subconsultas

Las subconsultas se pueden usar en varias partes de una consulta SQL:

• En la cláusula WHERE para filtrar filas
• En la cláusula HAVING para filtrar grupos
• En la lista de selección SELECT
• En la cláusula FROM para crear tablas derivadas

Algunas reglas importantes para las subconsultas:

• La lista de selección de una subconsulta con operador de comparación solo puede incluir una expresión o columna
• Los tipos de datos ntext, text y image no están permitidos en las listas de selección
• Las cláusulas GROUP BY y HAVING no se permiten en subconsultas que devuelven un solo valor

Usos y Ventajas de las Subconsultas:

• Modularidad: Permiten dividir una consulta compleja en partes más manejables.
• Reutilización de Resultados: Los resultados de una subconsulta pueden ser reutilizados en la consulta principal sin necesidad de ejecutar consultas separadas.
• Simplificación de Código: Facilitan la expresión de consultas complejas que de otro modo requerirían uniones o múltiples pasos.

Las subconsultas son una herramienta poderosa en SQL que permite realizar consultas complejas de manera más eficiente y organizada. Su uso adecuado puede mejorar tanto la claridad como el rendimiento de las consultas.

Consultas de acción

Las consultas de acción en SQL son comandos que permiten modificar datos y la estructura de la base de datos. Estas consultas son parte del conjunto de comandos Data Manipulation Language (DML) y Data Definition Language (DDL) en SQL.

Comandos DML y DDL

• INSERT - Se utiliza para insertar nuevas filas en una tabla.

  Ejemplo:

  -- Sintaxis básica
  INSERT INTO nombre_tabla (campo1, campo2, ...) VALUES (valor1, valor2, ...);
  
  -- Ejemplo
  INSERT INTO empleados (nombre, puesto, salario)
  VALUES ('Juan Pérez', 'Desarrollador', 50000);
  

• UPDATE - Se usa para modificar los datos existentes en una tabla.

  Ejemplo:

  -- Sintaxis básica
  UPDATE nombre_tabla SET campo1 = valor1, campo2 = valor2 WHERE condición;
  
  -- Ejemplo
  UPDATE empleados
  SET salario = 55000
  WHERE nombre = 'Juan Pérez';
  

• DELETE - Se emplea para eliminar filas de una tabla.

  Ejemplo:

  -- Sintaxis básica
  DELETE FROM nombre_tabla WHERE condición;
  
  -- Ejemplo
  DELETE FROM empleados
  WHERE nombre = 'Juan Pérez';
  

• CREATE - Se utiliza para crear nuevas bases de datos, tablas, vistas, índices, entre otros.

  Ejemplo:

  -- Sintaxis básica
  CREATE TABLE nombre_tabla (
      columna1 tipo_dato [opciones],
      columna2 tipo_dato [opciones],
      ...
  );
  
  -- Ejemplo
  CREATE TABLE departamentos (
      id INT PRIMARY KEY,
      nombre VARCHAR(100),
      ubicacion VARCHAR(100)
  );
  

• ALTER - Permite modificar la estructura de una tabla existente, como agregar, eliminar o modificar columnas.

  Ejemplo:

  -- Sintaxis básica
  ALTER TABLE nombre_tabla ADD COLUMN tipo_dato [opciones];
  
  -- Ejemplo
  ALTER TABLE empleados
  ADD COLUMN fecha_contratacion DATE;
  

• DROP - Se usa para eliminar tablas, vistas, bases de datos, índices, entre otros.

  Ejemplo:

  -- Sintaxis básica
  DROP TABLE nombre_tabla;
  
  -- Ejemplo
  DROP TABLE empleados;
  

Consultas de datos anexados

Las consultas de datos anexados en SQL se refieren a aquellas que añaden datos a una tabla existente. Estas consultas se utilizan para combinar datos de diferentes fuentes o para transferir datos entre tablas.

Las consultas de datos anexados son consultas que añaden filas enteras a una tabla.

Los nuevos registros se agregan siempre al final de la tabla.

Las consultas de datos anexados son útiles para tareas como la consolidación de bases de datos, la migración de datos, o la preparación de datos para análisis o reportes.

Comandos

• INSERT INTO … SELECT

  Esta consulta permite copiar datos de una tabla o consulta a otra tabla. Es una combinación de las declaraciones INSERT INTO y SELECT, donde los datos seleccionados se anexan (insertan) a la tabla destino.

  Ejemplo:

  INSERT INTO empleados_nuevos (nombre, puesto, salario)
  SELECT nombre, puesto, salario
  FROM empleados_antiguos
  WHERE puesto = 'Desarrollador';
  

  Explicación: En este ejemplo, se están anexando a la tabla empleados_nuevos todos los empleados que tienen el puesto de "Desarrollador" desde la tabla empleados_antiguos.

• INSERT INTO (con valores especificados) Aunque la mayoría de las veces se utiliza para insertar una fila específica, también puede ser usada para anexar múltiples filas especificando todos los valores manualmente.

  Ejemplo:

  INSERT INTO empleados (nombre, puesto, salario)
  VALUES 
      ('María López', 'Diseñadora', 45000),
      ('Carlos Jiménez', 'Analista', 47000),
      ('Ana Gómez', 'Gerente', 65000);
  

  Explicación: Aquí se están anexando varias filas con datos especificados directamente en la consulta a la tabla empleados.

• INSERT ALL Una característica de Oracle SQL, INSERT ALL permite anexar datos en múltiples tablas en una sola operación.

  Ejemplo:

  INSERT ALL
      INTO empleados (nombre, puesto, salario) VALUES ('Pedro García', 'Contador', 52000)
      INTO salarios (empleado_id, salario, fecha) VALUES (1, 52000, SYSDATE)
  SELECT * FROM dual;
  

  Explicación: Este comando inserta datos en dos tablas diferentes en una sola operación.

• MERGE (UPSERT) El comando MERGE se utiliza para combinar datos de dos tablas. Puede realizar tanto inserciones como actualizaciones dependiendo de si los datos ya existen en la tabla destino. Este proceso es conocido como "UPSERT" (una combinación de UPDATE e INSERT).

  Ejemplo:

  MERGE INTO empleados_destino d
  USING empleados_fuente s
  ON (d.id = s.id)
  WHEN MATCHED THEN
      UPDATE SET d.salario = s.salario
  WHEN NOT MATCHED THEN
      INSERT (id, nombre, puesto, salario)
      VALUES (s.id, s.nombre, s.puesto, s.salario);
  

  Explicación: En este ejemplo, se está anexando datos de la tabla empleados_fuente a empleados_destino. Si el empleado ya existe (coincide por id), se actualiza el salario. Si no existe, se inserta como un nuevo registro.

Índices

Los índices en SQL son estructuras de datos que mejoran la velocidad de las operaciones de consulta en una base de datos. Funcionan como una tabla de búsqueda rápida, permitiendo que el sistema localice registros específicos sin necesidad de escanear toda la tabla. Esto es especialmente útil en bases de datos grandes, donde el rendimiento puede verse afectado significativamente por la cantidad de datos.

Tipos de índices

• Índice Simple - Un índice que se crea en una sola columna.

  Ejemplo:

  CREATE INDEX idx_nombre ON empleados(nombre);
  

• Índices compuesto (o Multicolumna) - Un índice que se crea en dos o más columnas de una tabla.

  Ejemplo:

  CREATE INDEX idx_nombre_puesto ON empleados(nombre, puesto);
  

• Índices único - Aseguran que los valores en la columna o columnas indexadas sean únicos, lo que es útil para mantener la integridad de los datos.

  Ejemplo:

  CREATE UNIQUE INDEX idx_email ON empleados(email);
  

• Índices de clave primaria - Es un tipo especial de índice único creado automáticamente cuando se define una columna como clave primaria (PRIMARY KEY).

  Ejemplo:

  ALTER TABLE empleados ADD CONSTRAINT pk_empleado_id PRIMARY KEY (id);
  

• Índices de clave foránea - Se usa para acelerar las consultas que involucran relaciones entre tablas basadas en claves foráneas.

  Ejemplo:

  CREATE INDEX idx_departamento_id ON empleados(departamento_id);
  

• Índice de Espaciales y Texto Completo - Se utilizan para optimizar consultas en datos espaciales y para realizar búsquedas complejas en textos, respectivamente.

  Mejoran el rendimiento de las búsquedas de texto completo en columnas de tipo texto (como VARCHAR o TEXT).

  Ejemplo:

  CREATE FULLTEXT INDEX idx_descripcion ON productos(descripcion);
  

• Índice Clustered (Agrupado) - Este tipo de índice determina el orden físico de los datos en la tabla. Solo puede haber un índice agrupado (clustered) por tabla, ya que los datos se almacenan en el mismo orden que el índice.

  Ejemplo:

  CREATE CLUSTERED INDEX idx_id ON empleados(id);
  

• Índice Non-Clustered (No Agrupado) - Estos índices crean una estructura separada que apunta a las ubicaciones de los datos en la tabla. Se pueden crear múltiples índices no agrupados en una tabla.

  Ejemplo:

  CREATE NONCLUSTERED INDEX idx_nombre ON empleados(nombre);
  

• Índices ordinarios - Los índices ordinarios, también conocidos como índices no filtrados o índices tradicionales, son los tipos más comunes de índices en SQL. Un índice ordinario se crea en una columna o conjunto de columnas y almacena una entrada para cada fila de la tabla, sin importar el valor de la columna.

  Ejemplo:

  CREATE INDEX idx_ordinario ON empleados(apellido);
  

• Índices Filtrados - Los índices filtrados son una versión más especializada de los índices ordinarios, que solo incluyen un subconjunto de filas de la tabla, basado en una condición específica (un filtro). Estos índices son útiles cuando solo un segmento de los datos se consulta con frecuencia.

  Ejemplo:

  CREATE INDEX idx_filtrado ON empleados(salario)
  WHERE salario > 50000;
  

Eliminación de índices

Eliminar un índice en SQL es un proceso sencillo que se puede realizar con el comando DROP INDEX.

La sintaxis básica para eliminar un índice es la siguiente:

DROP INDEX [IF EXISTS] index_name ON table_name;

• index_name: Es el nombre del índice que deseas eliminar.
• table_name: Es el nombre de la tabla a la que pertenece el índice.
• IF EXISTS: Esta opción, disponible desde SQL Server 2016, permite evitar errores si el índice no existe.

Ejemplos:

• Eliminar un índice específico: Para eliminar un índice llamado ix_cust_email de la tabla sales.customers, se usaría la siguiente consulta:

DROP INDEX IF EXISTS ix_cust_email ON sales.customers;

• Eliminar varios índices: Si deseas eliminar múltiples índices de la misma tabla, puedes hacerlo de la siguiente manera:

DROP INDEX
ix_cust_city ON sales.customers,
ix_cust_fullname ON sales.customers;

Consideraciones al Eliminar Índices:

• Impacto en el Rendimiento: Antes de eliminar un índice, considera que puede afectar negativamente el rendimiento de las consultas que dependían de ese índice para ejecutarse rápidamente.
• Restricciones de Clave: No se puede eliminar un índice que esté asociado con una clave primaria (PRIMARY KEY) o una clave única (UNIQUE) sin primero eliminar o modificar esa restricción. Para eliminar estos índices, se debe usar ALTER TABLE DROP CONSTRAINT.
• Revisión Previa: Es recomendable revisar qué consultas utilizan el índice que deseas eliminar, para evitar degradaciones inesperadas en el rendimiento.
• Alternativas: Si un índice no es necesario, pero quieres mantenerlo disponible por si lo necesitas en el futuro, podrías considerar deshabilitarlo temporalmente si tu DBMS lo permite, en lugar de eliminarlo permanentemente.

Triggers

Un trigger (o disparador) en SQL es un conjunto de instrucciones que se ejecutan automáticamente en respuesta a ciertos eventos en una tabla o vista, como inserciones, actualizaciones o eliminaciones de registros. Los triggers se utilizan para mantener la integridad de los datos, automatizar procesos, registrar auditorías, y realizar validaciones antes o después de que ocurran estos eventos.

Tipos de Triggers

• Según el Momento de Ejecución:

  • BEFORE Triggers: Se ejecutan antes de que ocurra la acción que los dispara. Son útiles para validar o modificar los datos antes de que se realicen cambios en la base de datos.
  • AFTER Triggers: Se ejecutan después de que la acción que los dispara haya ocurrido. Se utilizan para auditar cambios, actualizar otras tablas o llevar a cabo tareas posteriores a la modificación.

• Según el Tipo de Evento: o Triggers DML (Data Modification Language): Se activan por eventos que modifican los datos, como INSERT, UPDATE o DELETE.

  • INSERT Triggers: Se activan cuando se inserta un nuevo registro en la tabla.
  • UPDATE Triggers: Se activan cuando se actualiza un registro existente.
  • DELETE Triggers: Se activan cuando se elimina un registro de la tabla.

• Triggers DDL (Data Definition Language): Se activan por eventos que modifican la estructura de la base de datos, como la creación o eliminación de tablas.

Creación, Modificación y Eliminación de Triggers

Creación

Para crear un trigger, se utiliza la sintaxis:

CREATE TRIGGER nombre_trigger
BEFORE|AFTER INSERT|UPDATE|DELETE ON nombre_tabla
FOR EACH ROW
BEGIN
    -- acciones a realizar
END;

Por ejemplo, un trigger que ajusta el valor de una columna antes de una inserción podría verse así:

CREATE TRIGGER ajustar_nota
BEFORE INSERT ON alumnos
FOR EACH ROW
BEGIN
    IF NEW.nota < 0 THEN
        SET NEW.nota = 0;
    ELSEIF NEW.nota > 10 THEN
        SET NEW.nota = 10;
    END IF;
END;

Modificación

Para modificar un trigger, generalmente se debe eliminar y luego volver a crear con los cambios deseados, ya que no se puede alterar directamente.

DROP TRIGGER IF EXISTS nombre_trigger;
CREATE TRIGGER nombre_trigger
-- nueva definición

Eliminación

Para eliminar un trigger, se utiliza el comando:

DROP TRIGGER nombre_trigger;

Esto eliminará el trigger de la base de datos y dejará de ejecutarse en respuesta a los eventos definidos.

Utilidad de los Triggers

Los triggers son útiles para:

• Automatizar tareas: Permiten ejecutar acciones automáticamente sin intervención manual. Los triggers pueden automatizar procesos, como actualizar un campo de última modificación, calcular valores derivados o manejar secuencias.
• Mantener la integridad de los datos: Ayudan a mantener la integridad referencial y de los datos, asegurando que ciertas reglas de negocio se apliquen automáticamente. Pueden validar datos antes de que se inserten o actualicen.
• Auditoría: Registran automáticamente las acciones de inserción, actualización o eliminación, lo que es útil para auditar cambios en la base de datos. Se pueden usar para registrar cambios en las tablas, lo que ayuda en el seguimiento de la actividad de la base de datos.
• Implementar reglas de negocio: Permiten aplicar lógicas complejas que no se pueden manejar solo con restricciones de base de datos.
• Validación: Pueden usarse para validar datos antes de que se realicen cambios en la base de datos.

Consideraciones al Usar Triggers

• Impacto en el Rendimiento: Los triggers pueden afectar el rendimiento si no se implementan correctamente, ya que añaden procesamiento adicional cada vez que se activa el evento correspondiente.
• Complejidad: Pueden hacer que el flujo de la lógica de la base de datos sea más complejo y difícil de seguir, especialmente si se utilizan múltiples triggers en una sola tabla.
• Recursión: Es importante evitar la recursión infinita, donde un trigger activa otro trigger, que a su vez vuelve a activar el primero.

Los triggers son una herramienta poderosa en SQL que permiten implementar reglas de negocio complejas directamente en la base de datos, asegurando que ciertas acciones se realicen automáticamente cuando ocurren eventos específicos, pero deben ser utilizados con precaución debido a su capacidad para afectar el rendimiento y la complejidad de la lógica de la aplicación.

Procedimientos almacenados

Un procedimiento almacenado (o stored procedure) es un conjunto de instrucciones SQL precompiladas y almacenadas en la base de datos y se puede ejecutar de forma repetida. Estos procedimientos pueden incluir múltiples instrucciones, como SELECT, INSERT, UPDATE o DELETE, y pueden aceptar parámetros de entrada y salida.

Estos procedimientos pueden ser invocados por aplicaciones, scripts o incluso por otros procedimientos almacenados. Se utilizan para realizar tareas repetitivas, encapsular lógica de negocio, y mejorar la seguridad y el rendimiento de las bases de datos.

Características de los Procedimientos Almacenados

1. Encapsulación de Lógica: Los procedimientos almacenados permiten encapsular la lógica compleja en un único bloque de código, lo que facilita su mantenimiento y reutilización.

2. Reutilización de Código: Permiten encapsular lógica de negocio en un solo lugar, evitando la duplicación de código y facilitando su mantenimiento. Esto significa que cualquier cambio en la lógica solo necesita hacerse en el procedimiento, no en todas las aplicaciones que lo utilizan. Pueden ser llamados múltiples veces desde diferentes partes de una aplicación, promoviendo la reutilización del código.

3. Seguridad Mejorada: Los procedimientos almacenados pueden ayudar a proteger la base de datos al permitir que los usuarios ejecuten operaciones sin tener acceso directo a las tablas subyacentes. Esto se logra mediante el uso de permisos específicos y la encapsulación de la lógica de acceso a datos. Al encapsular la lógica en procedimientos almacenados, los usuarios pueden ser autorizados para ejecutar el procedimiento sin tener acceso directo a las tablas subyacentes.

4. Mejora del Rendimiento: Al estar precompilados, los procedimientos almacenados pueden ejecutarse más rápido que ejecutar consultas SQL dinámicas.

5. Manejo de Parámetros: Los procedimientos almacenados pueden aceptar parámetros de entrada, lo que permite personalizar su ejecución en función de diferentes situaciones.

6. Facilidad de Mantenimiento: Al centralizar la lógica en procedimientos almacenados, las aplicaciones cliente pueden permanecer independientes de los cambios en la estructura de la base de datos. Esto simplifica el mantenimiento y las actualizaciones.

7. Facilidad para Manejar Errores: Pueden incluir manejo de errores y devolver códigos de estado, lo que ayuda a gestionar situaciones excepcionales de manera más efectiva.

Utilidad de los Procedimientos Almacenados

1. Automatización de Tareas Repetitivas: Los procedimientos almacenados son ideales para tareas que se repiten frecuentemente, como cálculos de reportes, validación de datos, o procesos de ETL (Extract, Transform, Load).

2. Implementación de Lógica de Negocio: Al permitir la inclusión de condiciones, ciclos, y manejo de excepciones, los procedimientos almacenados pueden encapsular lógica de negocio compleja que de otro modo tendría que implementarse en el código de la aplicación.

3. Mejora del Rendimiento: Los procedimientos almacenados son compilados y optimizados por el servidor la primera vez que se ejecutan, lo que puede resultar en un rendimiento mejorado en ejecuciones posteriores. Dado que están precompilados y optimizados por el DBMS, pueden ofrecer mejoras significativas en el rendimiento en comparación con la ejecución repetida de consultas ad-hoc.

4. Reducción del Tráfico de Red: Al ejecutar múltiples instrucciones como un solo bloque, se reduce la cantidad de datos que deben enviarse entre el cliente y el servidor. Esto es especialmente útil en aplicaciones que realizan muchas operaciones de base de datos. Al realizar operaciones complejas en el servidor, se reduce la cantidad de datos que necesitan ser transferidos a través de la red, lo que puede mejorar la eficiencia en sistemas distribuidos.

5. Seguridad y Control: Pueden restringir el acceso a los datos permitiendo solo la ejecución de procedimientos específicos, en lugar de otorgar acceso directo a las tablas, lo que protege la integridad y confidencialidad de la información.

Creación de un Procedimiento Almacenado

Para crear un procedimiento almacenado en SQL Server, se utiliza la siguiente sintaxis:

CREATE PROCEDURE nombre_procedimiento
    @parametro1 tipo_dato,
    @parametro2 tipo_dato
AS
BEGIN
    -- Instrucciones SQL
END;

Ejemplo:

CREATE PROCEDURE ObtenerClientes
    @Ciudad NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Clientes WHERE Ciudad = @Ciudad;
END;

Cómo Llamar a un Procedimiento Almacenado

En SQL Server, puedes llamar a un procedimiento almacenado utilizando la instrucción **EXEC** o **EXECUTE**. Aquí tienes la sintaxis:

EXEC nombre_procedimiento @parametro1 = valor1, @parametro2 = valor2;

Ejemplo:

Si tienes un procedimiento almacenado llamado ObtenerClientes que acepta un parámetro @Ciudad, lo llamarías así:

EXEC ObtenerClientes @Ciudad = 'Madrid';

En MySQL, se utiliza la instrucción **CALL** para ejecutar un procedimiento almacenado. La sintaxis es la siguiente:

CALL nombre_procedimiento(parametro1, parametro2);

Ejemplo:

Si tienes un procedimiento almacenado llamado ObtenerClientes que acepta un parámetro Ciudad, lo llamarías así:

CALL ObtenerClientes('Madrid');

Modificación de un Procedimiento Almacenado

Para modificar un procedimiento almacenado, generalmente se debe eliminar el existente y crear uno nuevo, ya que no se puede alterar directamente.

DROP PROCEDURE IF EXISTS nombre_procedimiento;
GO

CREATE PROCEDURE nombre_procedimiento
    @nuevo_parametro tipo_dato
AS
BEGIN
    -- Nuevas instrucciones SQL
END;

Eliminación de un Procedimiento Almacenado

Para eliminar un procedimiento almacenado, se utiliza el comando DROP PROCEDURE.

DROP PROCEDURE nombre_procedimiento;

Consideraciones al Usar Procedimientos Almacenados

• Mantenimiento: Aunque los procedimientos almacenados pueden simplificar las aplicaciones, también pueden hacer que la lógica de negocio esté dispersa entre la aplicación y la base de datos, lo que puede complicar el mantenimiento.

• Portabilidad: Los procedimientos almacenados están estrechamente ligados al DBMS específico, por lo que su código puede no ser portable entre diferentes sistemas de bases de datos.

• Depuración: La depuración de procedimientos almacenados puede ser más complicada que la depuración de código de aplicación, ya que muchos DBMS no ofrecen herramientas avanzadas para la depuración de procedimientos almacenados.

Vistas

Una vista en SQL es una tabla virtual que se basa en el resultado de una consulta SQL. No almacena datos por sí misma, sino que presenta datos almacenados en una o más tablas. Las vistas son útiles para simplificar consultas complejas, proporcionar seguridad de datos y ofrecer una capa de abstracción entre los usuarios y las tablas subyacentes.

Ventajas de las Vistas

1. Simplicidad: Permiten simplificar consultas complejas, ya que se pueden utilizar como si fueran tablas regulares. Esto facilita la reutilización de consultas sin necesidad de reescribirlas.

2. Seguridad: Se pueden usar para restringir el acceso a datos sensibles. Los usuarios pueden acceder a la vista sin tener acceso directo a las tablas subyacentes.

3. Personalización: Permiten personalizar la forma en que se presentan los datos a diferentes usuarios, adaptando la visualización según las necesidades específicas.

4. Consistencia y Estabilidad: Al encapsular la lógica en una vista, aseguras que los usuarios siempre trabajen con una versión consistente de los datos, independientemente de cómo se almacenen en las tablas subyacentes.

5. Mantenimiento: Cambios en la estructura de las tablas subyacentes pueden ser manejados en la vista sin afectar a las aplicaciones que la utilizan, siempre que la consulta siga siendo válida.

6. Mejoras en el Rendimiento: Las vistas indexadas pueden mejorar el rendimiento de ciertas consultas al almacenar los resultados de la vista como una tabla materializada.

7. Abstracción de Datos: Las vistas proporcionan una capa de abstracción, permitiendo a los usuarios interactuar con datos sin conocer la estructura de las tablas subyacentes.

8. Reutilización: Una vista puede ser reutilizada en múltiples consultas o aplicaciones, lo que promueve la reutilización del código y reduce la redundancia.

Creación de Vistas

Para crear una vista, se utiliza la instrucción CREATE VIEW. La sintaxis básica es:

CREATE VIEW nombre_vista AS
SELECT columna1, columna2, ...
FROM tabla
WHERE condicion;

Ejemplo:

Supongamos que quieres crear una vista que muestre solo los empleados del departamento de "Ventas":

CREATE VIEW SalesEmployees AS
SELECT employee_id, first_name, last_name, department
FROM employees
WHERE department = 'Sales';

Con esta vista, los usuarios pueden consultar fácilmente todos los empleados del departamento de "Ventas" sin tener que escribir la consulta completa cada vez:

SELECT * FROM SalesEmployees;

Modificación de Vistas

Para modificar una vista, se utiliza el comando ALTER VIEW. La sintaxis es similar a la de creación:

ALTER VIEW nombre_vista AS
SELECT nueva_columna1, nueva_columna2, ...
FROM nueva_tabla
WHERE nueva_condicion;

Ejemplo:

ALTER VIEW VistaClientes AS
SELECT nombre, direccion, telefono
FROM Clientes
WHERE activo = 1;

Eliminación de Vistas

Para eliminar una vista, se utiliza el comando DROP VIEW:

DROP VIEW nombre_vista;

Ejemplo:

DROP VIEW SalesEmployees;

Consideraciones al Usar Vistas

• Rendimiento: Las vistas no almacenan datos, por lo que cada vez que se consulta una vista, la consulta subyacente se ejecuta. Esto puede tener un impacto en el rendimiento si la consulta es muy compleja o si la vista se utiliza frecuentemente en operaciones intensivas.

• Limitaciones en las Actualizaciones: No todas las vistas son actualizables. En particular, las vistas que se basan en consultas complejas con uniones, agregaciones, o subconsultas pueden no permitir la inserción, actualización o eliminación directa de datos.

• Dependencias: Si las tablas subyacentes de una vista cambian (por ejemplo, si se eliminan columnas), la vista podría dejar de funcionar o devolver resultados incorrectos.

• Seguridad: Aunque las vistas pueden restringir el acceso a datos sensibles, no sustituyen un control de acceso adecuado en la base de datos. Es importante combinar vistas con permisos y roles adecuados para garantizar la seguridad de los datos.