B3-T3 SQL.pdf

1. Lenguaje SQL 1.1. Introducción SQL es un lenguaje de consulta estructurado con el que podemos manejar la información de una base de datos, modificando esos datos, añadiendo o eliminando. Es un lenguaje 4GL declarativo + extensión procedural. No es un lenguaje de programación sino d...

1. Lenguaje SQL 1.1. Introducción SQL es un lenguaje de consulta estructurado con el que podemos manejar la información de una base de datos, modificando esos datos, añadiendo o eliminando. Es un lenguaje 4GL declarativo + extensión procedural. No es un lenguaje de programación sino de consulta Trabaja bajo el ISO-9075 Productos que utilizan sql: Oracle, Microsoft Server, MySql/MariaDB, Informix, IDM DB2, MaxDB… SQL Lite: es un sistema de gestión de bases de datos relacional(RDBMS) compatible muy ligero que cumple con ACID. Sin configuración: Al ser "sin servidor", no requiere la configuración habitual de un sistema de bases de datos tradicional, lo que lo hace ideal para aplicaciones pequeñas o medianas que no necesitan la robustez de un sistema como MySQL SQL/PSM(SQL Persistent Stored Modules):es un estándar ISO - extensión de SQL que introduce programación procedural dentro de las bases de datos relacionales. Soporta la creación de funciones y procedimientos almacenados. El código se ejecuta en el propio gestor de BBDD. PL/SQL: lenguaje procedimental diseñado y desarrollado por Oracle para trabajar con su sistema de bases de datos.  Nota En SQL Server se llama Transact (Transact-SQL) 1.2. Tipos BBDD Relacionales Oracle (1521 vs SQL Server MySQL (3306) Apache Derby MariaDB(3306) 2483) (1433) (1527) MaxDB SQLLite HSQLDB PostgreSQL(54 Aurora 32) 1.3. Tipos de ejecuciones Tipos: DDL: creación/Borrado/Eliminación de objetos (tablas,índice …) DML: consulta/borrado/modificación/inserción de datos DCL: permiten al administrador controlar el acceso a los datos contenidos en la Base de Datos TCL: para controlar el procesamiento de transacciones en una base de datos. Ejemplos de comandos: ○ commit: guarda el trabajo realizado ○ savepoint : Identifica un punto en una transacción a la que más tarde se puede volver. ○ rollback: Restaurar. ○ set transaction: Cambia las opciones de transacción como nivel de aislamiento y que segmento de cancelación utiliza  1.4. DDL Creación/Borrado/Eliminación de objetos (tablas,índice …) Create Database: CREATE DATABASE nbBasedeDatos [ ON [ PRIMARY ] [ [ ,...n ] [ , [ ,...n ] ] [ LOG ON { < esp_fichero > [ ,...n ] } ] ] [ COLLATE nbintercalacion] [ WITH ] ] [;] Drop database DROP DATABASE Create table CREATE [TEMPORARY] TABLE [nombre de la tabla] ( [definiciones de columna] ) [parámetros de la tabla]. drop table DROP TABLE NombreTabla; Alter table Agregar una columna Modificar una columna Agregar una restricción Constraints: Reglas que ofrecen una garantía de integridad en la información vinculada. Se usa en las instrucciones ALTER TABLE y CREATE TABLE Tipos de constraint ○ Primary Key: Asegura que cada fila en una tabla tenga un identificador único. No puede haber valores nulos en una columna definida como clave primaria, y cada valor debe ser único ○ Foreign key: Establece una relación entre dos tablas. Una columna en una tabla (la clave foránea) debe coincidir con una columna en otra tabla (la clave primaria o una clave única), garantizando así la integridad referencial Puede ser null ○ Unique: Asegura que todos los valores en una columna son diferentes ○ Not Null: Asegura que una columna no pueda tener valores nulos. Cada fila debe tener un valor para esa columna. ○ Check: Permite especificar una condición que debe cumplirse para que los valores en una columna sean válidos.  Por ejemplo, se puede usar para asegurar que una edad sea siempre mayor de 0. ○ Default: Establece un valor predeterminado para una columna si no se proporciona un valor explícito al insertar un nuevo registro. Recuerda: Sequence: Es un objeto que se emplea para generar valores enteros secuenciales únicos y asignarlos a campos numéricos. Se utilizan generalmente para las claves primarias de las tablas garantizando que sus valores no se repitan. los autoincrement en windows se llaman “Identity values” vistas: es una tabla virtual cuyo contenido está definido por una consulta. Consta de un conjunto de columnas y filas de datos con un nombre dinámicas: pueden contener datos de una o dos tablas e incluir automáticamente todas las columnas de la tabla o tablas especificadas estáticas: pueden contener datos de varias tablas y las columnas necesarias de estas tablas se deben especificar en las cláusulas SELECT y WHERE de la vista estática índices: estructura de datos definida sobre una columna de tabla (o varias) y que permite localizar de forma rápida las filas de la tabla en base a su contenido en la columna indexada además de permitir recuperar las filas de la tabla ordenadas por esa misma columna.  1.5. DCL Permite al administrador controlar el acceso a los datos contenidos en la Base de Datos Los dos comandos DCL más comunes son: Grant y Revoke 1.5.1. Grant Se utiliza para otorgar permisos a usuarios o roles en la base de datos GRANT , ,... ON TABLE TO ,... [ WITH GRANT OPTION] With Grant Option: para otorgar permisos a un usuario o rol y, al mismo tiempo, permitir que ese usuario o rol pueda conceder esos mismos permisos a otros usuarios. 1.5.2. Revoke Se utiliza para quitar permisos otorgados anteriormente. Permite especificar qué permisos se deben retirar. REVOKE privilegio(s) ON objeto FROM usuario_rol;  1.6. DML Consulta/borrado/modificación/inserción de datos INSERT: Insertar datos en una tabla UPDATE: Actualizar datos existentes en una tabla DELETE: Eliminar datos de una tabla SELECT: Leer o consultar datos de una tabla SELECT [ALL | DISTINCT] columna1, columna2, … as alias * FROM [TABLA|VISTA] as alias [WHERE condicion] [AND|OR|NOT condicion] [GROUP BY columna1, columna2,...] [HAVING condicion_agregada] [ORDER BY columna1 [ASC|DESC], columna2 [ASC|DESC]] [LIMIT número_filas OFFSET inicio]; Posibles valores en el campo Columna, ○ Columna ○ Función de agregado (SUM, COUNT, MAX, MIN, AVG) / escalar Nota Count cuenta los “no nulos” ○ Subconsulta ○ Calculo GROUP BY columna1, columna2,... (opcional): Agrupa los registros por una o más columnas, generalmente usado con funciones de agregación como SUM(), COUNT(), AVG(), etc. HAVING condicion_agregada (opcional): Filtra los grupos creados por el GROUP BY, permitiendo usar condiciones sobre las funciones agregadas (ejemplo: SUM(Salario) > 10000). ORDER BY columna1 [ASC|DESC] (opcional): Ordena los resultados por una o más columnas en orden ascendente (ASC) o descendente (DESC). El valor predeterminado es ASC. LIMIT número_filas OFFSET inicio (opcional): Limita el número de filas devueltas y define el punto de inicio ○ LIMIT: Especifica el número máximo de registros a devolver. ○ OFFSET: Define desde qué fila empezar (útil para paginación). Laboratorio Simple de Select  1.7. TCL 1.7.1. ROLLBACK Comando utilizado en el contexto de transacciones para deshacer los cambios realizados en la base de datos desde el inicio de la transacción (revertir la transacción a su estado anterior), garantizando la integridad de los datos. Una transacción es un conjunto de operaciones SQL que se ejecutan como una unidad. Una transacción debe cumplir las propiedades ACID: Se utiliza en combinación con los comandos de transacción BEGIN TRANSACTION, COMMIT y ROLLBACK para controlar cómo y cuándo se aplican los cambios a la base de datos Ejemplo: 1.7.2. SAVEPOINT Se puede utilizar un punto de guardado (SAVEPOINT) dentro de una transacción, para hacer rollback solo a ese punto en lugar de deshacer toda la transacción.  1.8. Delete y Truncate Delete: Elimina filas específicas Cada fila se elimina individualmente, lo que significa que las operaciones activan disparadores (triggers) y registros de transacciones para cada fila eliminada. Se puede combinar con COMMIT y ROLLBACK Es más lento en comparación con TRUNCATE, especialmente cuando se eliminan muchas filas Respeta las restricciones de integridad referencial (como claves foráneas) y no permite eliminar filas que tengan dependencias en otras tablas sin manejar adecuadamente las relaciones. Se necesita deshabilitar las claves foráneas antes de usarlo, o fallará. No reinicia los valores de columnas con auto-increment Truncate (No elimina la tabla) Elimina todas las filas de una tabla, pero no permite usar una cláusula WHERE. No se pueden eliminar filas individuales; se eliminan todas a la vez. No activa disparadores (trigger) ni registra cada fila eliminada de manera individual, lo que hace que sea más rápido en comparación con DELETE Una vez ejecutado, no se puede deshacer individualmente mediante ROLLBACK (algunos sistemas permiten deshacerlo si no se ha confirmado la transacción) Se reinicia los valores de columnas con auto-increment Resumen Característica DELETE TRUNCATE Eliminación Fila por fila, se puede usar WHERE Todas las filas, sin WHERE Rendimiento Más lento Más rápido Soporte para transacciones Sí, se puede hacer ROLLBACK Limitado en algunas bases de datos Triggers Activa disparadores (triggers) No activa disparadores Reinicio de auto-incremento No reinicia valores autoincrementales Reinicia valores autoincrementales Liberación de espacio No libera espacio de inmediato Libera espacio de inmediato Puede requerir deshabilitar claves Claves foráneas Respeta claves foráneas foráneas  1.9. Trigger Conjunto de instrucciones que se ejecutan automáticamente en respuesta a ciertos eventos que ocurren en una tabla o vista Los triggers pueden activarse antes o después de que se realice un evento Tipos de triggers según el estado del evento: BEFORE: Se ejecuta antes de que ocurra la operación (INSERT, UPDATE, DELETE). AFTER: Se ejecuta después de que ocurra la operación (INSERT, UPDATE, DELETE). INSTEAD OF (disponible en algunas bases de datos como SQL Server): Se ejecuta el trigger en lugar de la operación (INSERT, UPDATE, DELETE), CREATE TRIGGER NombreTrigger {BEFORE | AFTER| INSTEAD OF} {delete | insert | update [OF NombreColumna[, NombreColumna]...] } ON {NombreTabla | Nombre-Vista} [REFERENCING { old [as] NombreViejo | new [as] NombreNuevo}] [FOR EACH {row | statement}] [WHEN (Condición)] [BEGIN ATOMIC] Code [END] Before Insert After Update Instead Of update Este trigger se ejecuta en lugar de la operación de actualización, lo que permite modificar el comportamiento predeterminado de la operación UPDATE da igual la sentencia de negocia (sql) que se haya ejecutado, que solo se va a lanzar el trigger Anula la sentencia disparadora y solo se ejecuta el trigger ¿Cómo queremos que actúe? Fila a fila o por sentencia FOR EACH ROW: el trigger se ejecutará tantas veces como líneas haya ○ FOR EACH STATEMENT: el trigger se ejecutará solamente una vez Nota Un trigger nunca podrá ser lanzado por un truncate ni será lanzado por una select Limitaciones No aceptan parámetros No pueden ser START TRANSACTION / COMMIT / ROLLBACK 1.10. Merge (Sincronización) Para realizar operaciones de inserción (INSERT), actualización (UPDATE) o eliminación (DELETE) en función de la coincidencia de datos entre dos tablas o entre una tabla y una consulta Actualiza un destino (una tabla o vista, o las tablas o vistas subyacentes de una selección completa) utilizando datos de una fuente (resultado de una referencia de tabla). No se puede actualizar varias veces la misma fila de la tabla destino, en la misma sentencia MERGE. 1.11. Monitor Transaccional El Monitor Transaccional en SQL es una herramienta o característica que permite supervisar y gestionar el estado de las transacciones en una base de datos. Este monitor te proporciona información sobre: Transacciones activas. Bloqueos y esperas de recursos (como tablas o filas). Conflictos entre transacciones, como deadlocks (interbloqueos). Rendimiento y estado de las transacciones en ejecución. El nivel de aislamiento en las transacciones determina cómo se controlan las interacciones entre transacciones concurrentes. Los niveles de aislamiento controlan: Lectura sucia:ocurre cuando se le permite a una transacción la lectura de una fila que ha sido modificada por otra transacción concurrente pero todavía no ha sido commit Lectura no repetible: ocurre cuando en el curso de una transacción una fila se lee dos veces y los valores no coinciden. Lectura fantasma: ocurre cuando, durante una transacción, se ejecutan dos consultas idénticas, y los resultados de la segunda no son iguales a los de la primera. Niveles de aislamiento: Read Uncommitted: Lectura de datos no confirmados (menos restrictivo, más rápido, pero menos seguro). Read Committed: Lectura solo de datos confirmados (comúnmente usados). ○ Un SGBDR mantiene los bloqueos de escritura (de los datos seleccionados) hasta el final de la transacción. ○ Se coloca bloqueo de lectura (bloqueo compartido) solo mientras la operación de lectura esté en curso Repeatable Read: Lecturas repetidas de los mismos datos son consistentes, pero se permiten "fantasmas". ○ Bloqueo exclusivo de escritura en los datos, evitando que otras transacciones lean o escriban esos datos hasta que la transacción termine ○ Las lecturas colocan un bloqueo de lectura compartido en los datos, y este bloqueo se mantiene hasta que la transacción se complete (se haga un COMMIT o ROLLBACK) Serializable: Transacciones se ejecutan de manera completamente aislada (más restrictivo, pero garantiza consistencia total). ○ Colocan bloqueos exclusivos en los datos, impidiendo que otras transacciones lean o escriban en ellos ○ El alcance de los bloqueos es mayor, ya que no solo se bloquean los datos actuales, sino también los rangos de datos afectados, lo que impide que otras transacciones inserten o modifiquen filas en esos rangos. Comparativa Bloqueo Bloqueo Bloqueo Nivel de Aislamiento Escritura Lectura de Rango Read Uncommitted - - - Read Committed X - - Repeatable Read X X - Serializable X X X Lecturas Lecturas Lecturas Nivel de Aislamiento sucias no repetibles Fantasmas Read Uncommitted Sí Sí Sí Read Committed No Sí Sí Repeatable Read No No Sí Serializable No No No 1.12. Procedimientos almacenados Conjunto de instrucciones SQL precompiladas que se almacenan en una base de datos y que pueden ser ejecutadas de forma repetida. Permiten encapsular lógica compleja dentro de la base de datos, mejorando el rendimiento al evitar la recompilación de consultas y permitiendo reutilización de código. Tipos de parámetros que puede tener: IN:Se usa para pasar valores al procedimiento (de solo lectura dentro del procedimiento). OUT: Se usa para devolver valores desde el procedimiento (se pueden modificar dentro del procedimiento). ○ ○ El parámetro p_salario es de salida. El procedimiento no toma un valor inicial para este parámetro, pero al final del procedimiento almacenado, devolverá el salario del empleado especificado en p_id_empleado. INOUT: Se usa para pasar y devolver valores, es decir, se recibe un valor, puede ser modificado y luego devuelto ○ Como crear un procedimiento almacenado Para invocar un Procedimiento Almacenado: CALL En SQL SERVER se llama con EXEC o EXECUTE Nota Procedimiento Almacenado no devuelve valores (de eso se encargan las funciones).  2. Tipos de Joins Outer Join permite combinar las filas de dos tablas y, en los casos donde no haya coincidencia entre las tablas, rellena con valores NULL en la tabla que no tiene coincidencia Nota JOIN también es conocido como UNION Tipo de JOIN Descripción Ejemplo (tabla A y tabla B) Devuelve el producto cartesiano de ambas tablas. Cada fila de A se combina con cada Cada fila de A se une con todas las filas de B, CROSS JOIN fila de B, sin condición de unión. combinando todas las posibilidades. Devuelve las filas que tienen coincidencias en ambas tablas. Solo filas donde A y B coinciden. Si no INNER JOIN Si no hay coincidencia, la fila es descartada. coinciden, se eliminan. Devuelve todas las filas de la tabla izquierda (A), y las coincidencias de la tabla derecha (B). Si no hay coincidencia, muestra NULL en la Todas las filas de A y, si hay coincidencias, las LEFT [OUTER] JOIN tabla derecha. de B. Si no, NULL en B. Devuelve todas las filas de la tabla derecha (B), y las coincidencias de la tabla izquierda (A). RIGHT [OUTER] Si no hay coincidencia, muestra NULL en la Todas las filas de B y, si hay coincidencias, las JOIN tabla izquierda. de A. Si no, NULL en A. Devuelve todas las filas donde hay coincidencia en A o B. FULL [OUTER] Si no hay coincidencia, muestra NULL en la Todas las filas de A y B, con NULL donde no JOIN tabla sin coincidencia. haya coincidencia. Es un tipo especial de JOIN donde una tabla se une consigo misma. Utilizado cuando se necesita comparar filas Compara filas de una tabla consigo misma (por SELF JOIN dentro de la misma tabla. ejemplo, para encontrar jerarquías). Une tablas automáticamente basándose en las columnas con el mismo nombre y tipo en ambas tablas. No requiere condiciones Combina A y B donde las columnas que NATURAL JOIN explícitas de unión. coinciden en nombre tienen valores iguales.  CROSS JOIN Ha realizado el producto cartesiano de los conjuntos (todos con todos) inner [outer]join Devuelve las filas que tienen coincidencias en ambas tablas. Solo filas donde A y B coinciden. Si no INNER JOIN Si no hay coincidencia, la fila es descartada. coinciden, se eliminan. Nota En el Producto Cartesiano primero saca todo y después hace la sentencia where, mientras que con inner la query se hace online Inner consume menos memoria Sintaxis: Cambia como se escribe. No hay cláusula where  left [Outer] join Devuelve todas las filas de la tabla izquierda (A), y las coincidencias de la tabla derecha (B). Si no hay coincidencia, muestra NULL en la Todas las filas de A y, si hay coincidencias, las LEFT [OUTER] JOIN tabla derecha. de B. Si no, NULL en B. Right [Outer] Join Devuelve todas las filas de la tabla derecha (B), y las coincidencias de la tabla izquierda (A). RIGHT [OUTER] Si no hay coincidencia, muestra NULL en la Todas las filas de B y, si hay coincidencias, las JOIN tabla izquierda. de A. Si no, NULL en A.  Full [Outer] join Devuelve todas las filas donde hay coincidencia en A o B. FULL [OUTER] Si no hay coincidencia, muestra NULL en la Todas las filas de A y B, con NULL donde no JOIN tabla sin coincidencia. haya coincidencia. Es como hacer primero un left y un right Pasos a ejecutar: left (coincidentes + no coincidentes ) + rigth(coincidentes) Nota En MariaDB no existe Full Outer Join Ejercicio: Tabla A → 10 Filas Tabla B → 10 Filas Número de Filas Coincidentes → 10 ¿Cuántos registros tendrá el resultado cuando? ○ Inner join: 5 filas ○ Cross Join (sin Where): 100 Filas ○ Cross Join (Con Where): 100 Filas ○ LeftJoin (tablaA, tableB): 10 Filas ○ RightJoin (tablaA, tableB): 10 Filas ○ FullJoin (tablaA, tableB): Pasos a ejecutar: left (coincidentes (5) + no coincidentes (5) ) + rigth(coincidentes(5))  3. Select/Subselect Avanzado subselect : Es una consulta anidada dentro de otra consulta. Utilizado para devolver un conjunto de resultados que pueden ser usados por la consulta externa. Existen varias formas de utilizar subconsultas en SQL, dependiendo de dónde y cómo las utilices (en las cláusulas SELECT, FROM, WHERE, o HAVING). Subselect en la cláusula Where Nota en la sentencia aparece IN, pero también se podría escribir NOT IN Truco La subquery referencia a columnas de la query externa/principal Subselect en la cláusula SELECT La subconsulta en el SELECT calcula el promedio de column2 en tableB para cada fila de tableA donde column3 de tableB es igual a column1 de tableA. El resultado de la subconsulta se muestra como una columna adicional llamada avg_column2. Subselect en la cláusula FROM (consulta derivada) La subconsulta devuelve un conjunto de resultados (column1 y column2 de tableB) que se trata como una tabla temporal llamada temp. La consulta externa selecciona columnas de la tabla derivada temp. Subselect con EXISTS Nota EXISTS se utiliza para verificar si la subconsulta devuelve al menos una fila. Se usa para determinar la existencia de registros. La subconsulta devuelve una fila si encuentra coincidencias entre tableA y tableB en column1. La consulta externa selecciona las filas de tableA solo si la subconsulta devuelve al menos una fila. Subselect en la cláusula HAVING La subconsulta interna calcula la media de los conteos de registros en tableA agrupados por column2. La consulta externa selecciona los grupos de column1 donde el número de filas en ese grupo es mayor que la media calculada. All, Any, Some: ANY(igual que SOME) significa que, para que una fila de la consulta externa satisfaga la condición especificada, la comparación se debe cumplir para al menos un valor de los devueltos por la subconsulta SOME: Sinónimo de ANY (funciona exactamente igual) ALL, Se usa para comparar un valor con todos los valores del conjunto de resultados de una subconsulta. La consulta externa solo se ejecuta si la condición es verdadera para todos los valores devueltos por la subconsulta. ○ Si la subconsulta no devuelve ninguna fila ALL da True La subconsulta devuelve los salarios de todos los empleados que trabajan en el departamento de "Ventas". La consulta externa: selecciona a todos los empleados cuyo salario sea mayor que cualquiera de los salarios en la subconsulta (es decir, mayor que al menos uno de los salarios en el departamento de "Ventas"). Nota Se ha utilizado el operador =, pero también valdría >=, … Funciones Agregadas: Funciones tipo MAX(), MIN(), COUNT(), AVG() y SUM() (permiten realizar cálculos sobre un conjunto de valores y devolver un único valor.) Se utilizan comúnmente en conjunto con la cláusula GROUP BY  4. Miscelánea Tipos de datos especiales: CLOB: Almacena grandes cantidades de texto como cadenas de caracteres. ○ Es útil para almacenar documentos de texto, como archivos XML o JSON, contenido HTML, ○ contiene datos de caracteres basados en el juego de caracteres predeterminados del servidor. ○ NCLOB: Similar a CLOB, pero diseñado para almacenar grandes cantidades de texto usando el conjunto de caracteres nacionales (como Unicode). ○ BLOB (Binary Large Object): Almacena datos binarios de gran tamaño, como imágenes, videos, archivos multimedia, archivos ejecutables, documentos PDF, etc. ○ GLOB: General Large Object) no es un tipo de datos oficial en la mayoría de los sistemas SQL, se usa a veces como una referencia genérica para objetos de datos grandes (como BLOB o CLOB). Tablas Journal: tienen como objetivo registrar cambios o eventos en las tablas de una base de datos, lo que permite realizar auditorías, recuperar datos o simplemente llevar un historial de las transacciones realizadas sobre ciertos registros. Estas tablas pueden ser utilizadas para llevar un "diario" o "bitácora" de las modificaciones en las tablas principales de una base de datos.ñ Nota sysdate es la fecha