Show Menu
Cheatography

SQL Avanzado Cheat Sheet by

Resumen de SQL Avanzado

Vistas

Una vista es una relación o "­tab­la" derivada de otras relaci­ones, ya sean "­bás­ica­s" o también derivadas
es transp­arente para el usuario
se genera a partir de una consulta relacional
son actual­iza­bles, pero no deben contener subsel­ects, joins o agrega­ciones. Si la vista es compleja es posible que existan varias maneras de llegar a la misma conclu­sión.
WHITH CHECK OPTION
Limita los UPDATES que pueden hacerse y que afectan a las vistas creadas.

FUNCIONES

Los proced­imi­entos almace­nados son funciones definidas que se almacenan en la Base de datos. Se tratan como un objeto más de la BD
Simpli­fican el desarrollo de aplica­ciones
Mejoran el rendim­iento de la BD
Controlan las operac­iones que se realizan

DIspar­adores (TRIGGERS)

Proveen al DBMS con un recurso para participar de forma más activa en los procesos. Es capaz de reaccionar ante eventos.
es muy importante la docume­ntación de los TRIGGERS
Monito­riz­ación/ alerta ante situac­iones adversas (presu­puesto inferior a n)
Compro­bación de restri­cciones de integr­idad. (no depart­amentos de más de n empleados)
Manten­imiento automá­tico: atributos derivados
El trigger se invoca cuando una operación altera o es suscep­tible de aleterar el estado correcto del sistema

TRANSA­CCIONES y CONCUR­RENCIA

Un DBMS permite el acceso simultaneo de diferentes usuarios a la misma base de datos, preser­vando su integridad y consis­tencia
Una transa­cción es un conjunto de operac­iones de lectura y/o modifi­cación de la base de datos que deben realizarse como una sola operación, y se debe acabar confir­mando (COMMIT) o cancelando (ROLLB­ACK).
Toda transa­cción debe complir las propie­dades ACID:
Atomic­idad, Consis­tencia, Aislam­iento, Defini­tividad
ATOMICIDAD
Las operac­iones se tienen que ejecutar comple­tamente o en absoluto. TODAS O NINGUNA
CONSIS­TENCIA
Las transa­cciones deben cumplir los requisitos de los usuarios
ISOLATION
El acceso simultáneo de dos usuarios no debe producir resultados anómalos
DEFINI­TIVIDAD
Han de exisitir mecanismos para evitar péridda de datos, tanto antiguos como recientes

Transa­cci­ones: INTERF­ERE­NCIAS

Actual­ización perdida
Cuando se pierde un cambio realizado por una operación de escritura. READ UNCOMM­ITED, READ COMMITED, REPETEABLE READ, SERIAL­IZABLE
Lectura no confirmada
Se lee un dato resultante de una modifi­cación realizada por una transa­cción que aborta. READ COMMITED, REPETEABLE READ, SERIAL­IZABLE
Lectura no repetible
REPETEABLE READ, SERIAL­IZABLE
Análisis incons­istente
Interf­erencia respecto a la visión que varias transa­cciones tienen del conjunto de datos. REPETEABLE READ, SERIAL­IZABLE
Fantasmas
Una transa­cción lee un conjunto de datos mientras otra añade nuevos elementos al conjunto. SERIAL­IZABLE
Estas interf­ere­ncias se solucionan con las transa­cci­ones. Las transa­cciones sólo serán necesarias para evitar las interf­ere­ncias a toda costa. No es recome­ndable usarlas siempre ya que puede perjudicar el rendim­iento del sistema. Existen diversos niveles de posibles transa­cciones para equilibrar seguridad y rendim­iento.
SERIAL­IZABLE protege de cualquier interf­erencia pero imposi­bilita parale­lizar. Esto se llama horario serial.
 

-

SELECT ID, nombre, sueldo, proyecto
FROM empleados
WHERE proyecto IN ('IoT2', 'UDT4'); CREATE VIEW EMPLEADOS_IOT_UDT AS
(SELECT ID, nombre, sueldo, proyecto
FROM empleados
WHERE proyecto IN ('IoT2', 'UDT4'));

-

CREATE FUNCTION encontrar_peliculas RETURN TABLE (tconst char(10))
    AS $contenga_pal$
    BEGIN
        RETURN QUERY
              SELECT t.tconst
              FROM title_basics t
              WHERE t.primarytitle LIKE CONCAT ('%', palabra, '%');
        END; $contenga_pal$
LANGUAGE plpgsql;

SELECT * FROM encontrar_peliculas ('AVATAR');

Ejemplos TRIGGERS

BEFORE & FOR EACH STATEMENT:
Se ejecuta una sola vez, antes de la ejecución de la sentencia que dispara el trigger
BEFORE & FOR EACH ROW
se ejecuta una sola vez por cada tupla afectada, y justo antes de que la fila se inserte, modifique o borre
AFTER & FOR EACH ROW
Se ejecuta una sola vez por cada fila afectada, y justo después de la ejecución de la sentencia que dispara el trigger
AFTER & FOR EACH STATEMENT
se ejecuta una sola vez después de la ejecución de la sentencia que dispara el trigger
Variables accesibles dentro de la función que ejecuta el trigger:
TG_OP: contiene qué provocó el salto del dispar­ador: INSTERT, DELETE o UPDATE
 
NEW: Valores de la tupla después del UPDATE o INSERT. No se define para DELETE.
 
OLD: valores de la tupla antes de la ejecución del UPDATE o DELETE. No se define para INSERT.
CREATE TRIGGER nombre­_tr­igger
{BEFORE || AFTER}
{INSERT || DELETE || UPDATE}
ON
nombre­_re­lacion
FOR EACH {ROW || STATEMENT}
EXECUTE PROCEDURE nombre­Fun­ció­n(a­rgu­men­tos);

CREATE FUNCTION nombre­Fun­ción()
RETURNS trigger AS $$
BEGIN
[...]
RETURN {NULL || NEW || OLD};
END;
$$ LANGUAGE plpgsql;

-

UPDATE productos
SET stock = stock + 10
WHERE ID = '1123S'

OLD.ID='1123S'
OLD.stock = #stock
NEW.ID='1123S'
NEW.stock=#stock+10

-

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    CREATE TABLE tabla_nueva (ID INTEGER PRIMARY KEY);
COMMIT;
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          PostgreSQL Cheat Sheet

          More Cheat Sheets by asourv

          Bases de datos relacionales Cheat Sheet