Tema 4 SQL PDF

15 - BLOQUE 3 – TEMA 4 – SQL SQL: 4GL declarativo + extensión procedural (Procedimientos almacenados. SQL/PSM: SQL / Persistent Stored Modules ≈ PL/SQL y Transact-SQL) Estándar Productos ANSI:86 Oracle...

15 - BLOQUE 3 – TEMA 4 – SQL SQL: 4GL declarativo + extensión procedural (Procedimientos almacenados. SQL/PSM: SQL / Persistent Stored Modules ≈ PL/SQL y Transact-SQL) Estándar Productos ANSI:86 Oracle SQLite (RDBMS no C/S): ANSI-92: Revisión grande. Es el más conocido. Microsoft SQL Server - No es un gestor de BD, sino un formato de fichero. ISO 9075 SQL:1999: SQL 3. Triggers. MySQL / MariaDB - Es local, no se usa por red. ISO 9075 SQL:2003: objeto SEQUENCE. Informix - Muy usado en Android. ISO 9075 SQL:2006: XML nativo. IBM Db2 - Librería (ACID compatible): permite realizar tx. ISO 9075 SQL:2008: sentencia TRUNCATE. PostgreSQL ACID: Atomicidad Consistencia Aislamiento Durabilidad. ISO 9075 SQL:2016: Compatibilidad JSON. MaxDB Transacción: conjunto de sentencias SQL que se tienen Sublenguajes: que hacer todas o ninguna. Procesos atómicos. DDL: Lenguaje de Definición de Datos. Creación / Borrado / Modificación de objetos. CREATE TABLE / INDEX / VIEW / PROCEDURE TRUNCATE: vacía la tabla. Algunos gestores borran la tabla y la DROP SEQUENCE / FUNCTION / TYPE / TRIGGER crean de nuevo. No soporta transaccionalidad, no es recuperable. ALTER DOMAIN / SCHEMA / ROLE Table: objetos. Procedure y Function: SQL/PSM. Triggers: procedimientos que se ejecutan a partir de un evento. Index: estructura de datos que sirve para agilizar búsquedas mediante el uso de árboles B. Schema: forma de agrupar tablas. Domain: dominio de valores. Type: tipos de datos. Role: para dar de alta permisos y funcionalidades a usuarios. CONSTRAINTS: restricciones. PRIMARY KEY; FOREIGN KEY; CHECK (Ej: campo > 0); UNIQUE (admite un único null). DML: Lenguaje de Manipulación de Datos. Consulta / Borrado / Modificación / Inserción de datos. SELECT UPDATE INSERT DELETE MERGE: mezcla registros de una tabla sobre otra. *TRUNCATE podría ser DML. DCL: Lenguaje de Control de Datos. Control del acceso a los datos. GRANT: da permisos. REVOKE: quita permisos. CALL procedure: algunos fabricantes lo consideran DML. TCL: Lenguaje de Control de Transacciones. Es una parte del DCL. COMMIT / ROLLBACK: confirma o deshace cambios de tx. Previene inconsistencias. SAVEPOINT: Crea un punto de salvaguarda. Cuando haces rollback lo haces hasta ese punto. RELEASE SAVEPOINT: quita el savepoint. SET TRANSACTION: configura la tx. START TRANSACTION: empieza la tx. END TRANSACTION: finaliza la tx. CREATE TABLE distribuidores ALTER TABLE nom_tabla ADD COLUMN nom_col (dist_id CHAR(4) NOT NULL, ALTER COLUMN nom_col SET DEFAULT valor dist_nombre VARCHAR(40), sales_rep INT, zip char(5), SET NOT NULL CONSTRAINT pk_dist_id PRIMARY KEY (dist_id), SET DATA TYPE CONSTRAINT fk_emp_id DROP COLUMN nom_col FOREIGN KEY (sales_rep) -- campos ADD REFERENCES empleados (empid) -- tabla y campos GRANT ON objeto TO grantee [WITH GRANT OPTION] CONSTRAINT uniq_zip UNIQUE (zip) -- clave candidata T  T usu/roles delegación de permisos CONSTRAINT chk-zip CHECK (zip LIKE '[0-9][0-9][0-9][0-9][0-9]') : SELECT, UPDATE, INSERT, DELETE, ALL, USAGE (Ej: secuencia) ); + lista columnas EXECUTE (para procedures) REVOKE ON objeto FROM grantee TRANSACCIONES: locales (1 SGBD) y distribuidas (two-phase commit). Monitor Transaccional: CICS y Tuxedo. O O O En JEE está dentro del servidor de aplicaciones: JBoss, WebLogic, WebSphere. API de Java para hablar con el monitor tx: JTA. Niveles de aislamiento y bloqueos (SET TRANSACTION). Situaciones en las que hay problemas dependiendo de la configuración: READ UNCOMMITED: leo datos no consolidados. READ COMMITED: es el método usado por defecto. REPEATABLE READ: tx1 consulta, tx2 cambia y commit. Tx1 no tendrá los nuevos datos de tx2 hasta que no termine su tx. Desde que tx1 inicia la tx trabaja con los mismos datos. SERIALIZABLE: pone las tx en fila. 0% problemas concurrencia.  Rendimiento. Seguro. - Lectura sucia: datos aún no confirmados. - Lectura no repetible: si la tx1 es muy larga, tx2 puede alterar los datos que usaba tx1. Si tx1 vuelve a leer, los datos habrán cambiado. - Lectura fantasma: no se bloquean rangos/grupos de datos, sino solo filas concretas. Tx1 puede recuperar un rango 1-5 y tx2 insertar un 3. Tx1 no vería el valor 3. Se pueden bloquear filas o columnas. El bloqueo de tablas solo se consigue con SERIALIZABLE. Columna / Cálculo / Subconsulta / SELECT [ALL | DISTINCT ] item1 AS alias → Función de agregado/escalar: INSERT INTO (col1, col2, …) VALUES (‘…’,’…’,…) SUM, MAX/MIN, AVG INSERT INTO (col1, col2, …) SELECT … COUNT(*): sí cuenta nulos FROM tabla | vista AS alias, … COUNT(columna): no cuenta nulos [[tipo join] JOIN ] UPDATE SET col1 = val1, col2 = val2 WHERE WHERE [AND | OR | NOT + ]…  DEFAULT / NULL / Query GROUP BY Siempre van HAVING de la mano DELETE FROM WHERE ORDER BY campo1 [ASC | DESC], … CARLOS VELILLA GARCIA – Telegram @CARLOSVELILLA 15 - B3-T4 - 1 - Subconsultas (dentro del Where) SELECT … FROM tabla1 WHERE EXISTS (subquery) → Correlacionada. Referencias a columnas de la query externa/principal. True si devuelve filas WHERE col1 [NOT] IN (SELECT col2 FROM …) → IN es el operador de conjuntos. WHERE col1 ANY / SOME (SELECT col2 FROM …)  ALL Autor (id, nombre) Libro (id, titulo, id_autor)  = >= < … funciones de agregado. 1, dani 10, xx, 1 JOINS: … XXX JOIN ON 2, pepe 20, yy, 2 CROSS JOIN: ≈ FROM tabla1, tabla2. Es el producto cartesiano de las tablas. (6 reg) 3, luis [INNER] JOIN: el resultado son las filas coincidentes según la condición. (2 reg) LEFT [OUTER] JOIN: el resultado son todas las filas de la tabla de la izquierda. Los campos de la tabla de la derecha tendrán valor si cumplen la condición, si no, tomarán valor nulo. El nº de filas resultante es el nº de filas de la tabla de la izquierda. (3 reg) RIGHT [OUTER] JOIN: igual que el LEFT pero manda la tabla de la derecha. (2 reg) FULL [OUTER] JOIN: mandan las 2 tablas y rellena con nulos cuando los registros de una tabla no cumple la condición. (3 reg) reg INNER + reg LEFT - INNER + reg RIGHT - INNER = 2 + 1 + 0 NATURAL JOIN: cuando los nombres de los campos en ambas tablas tienen los mismo nombres. El propio gestor hace el match de los campos. UNIONES: operador de conjuntos como EXCEPT, INTERSECT. Requisito: las querys deben ser compatibles, deben tener la misma estructura, con el mismo nº de campos y tipo. UNION [ALL] UNION ALL no elimina filas duplicadas. MEZCLA: de una tabla (source) sobre otra (target). Fusionas en base a una condición de búsqueda. Si un registro existe en las dos tablas, entonces se actualiza el destino (target). Si un registro existe en el origen (source) únicamente, entonces se inserta en el destino (target). TRUNCATE: según el último exámen de GSI, no se borra la tabla para después crearla. MERGE INTO Hace un borrado rápido, marcando zonas de memoria de golpe. Extends = data pages. USING Se salta los controles que pueda haber y que sí respeta la sentencia DELETE. ON Deja intactos nombres de columnas, índices y estructuras. La estructura de la tabla persiste. WHEN MATCHED En Oracle hace COMMIT automáticamente, por lo que no se puede deshacer. THEN UPDATE SET col1 = … No se quedan los registros en el fichero de log. WHEN NOT MATCHED No usa WHERE. THEN INSERT (col1, …) VALUES (…, …) En MySQL necesitas tener privilegio de DROP porque sí borra y luego crea la tabla. TRIGGERS / DISPARADORES: lógica de negocio con 1 o N sentencias que se ejecuta a partir de un evento sobre determinados objetos. Restricciones: No aceptan parámetros. No pueden usar START TRANSACTION / COMMIT / ROLLBACK. No podemos usar tx dentro. Tipos: de fila o de sentencia. CREATE TRIGGER auditar_phone_book ¿Cuándo?: AFTER UPDATE ON phone_book FOR EACH ROW AFTER INSERT BEGIN FOR EACH STATEMENT: una vez por sentencia, no registro BEFORE + UPDATE INSERT INTO phone_book_auditoria (col1, …) VALUES (auditoria_seq.nextval, 'UPDATE', INSTEAD OF DELETE OLD.col1, OLD.col2, NEW.col1, NEW.col2, SYSDATE); END; INSTEAD OF anula la sentencia disparadora y solo tiene efecto el cuerpo del trigger. Ej: Seguridad. DELETE → INSERT en una tabla auditorías. PROCEDURES: son scripts de base de datos. Lógica de negocio que se ejecuta (CALL) en el ámbito del SGBBDD. Acepta parámetros de entrada, salida y entrada/salida, pero a diferencia de las funciones, los procedimientos no retornan valor. CREATE PROCEDURE (IN | OUT | INOUT , …) LANGUAGE AS $$ Un cursor es un objeto que permite recorrer el resultado de una sentencia. BEGIN Un datareader (.NET) y un resulset (JDBC), por debajo, son cursores. SELECT/CURSOR FOR … UPDATE + IF var_reg cursor_tabla%ROWTYPE; -- define la variable var_reg con la estructura del cursor cursor_tabla … CASE … COMMIT; FETCH cursor_tabla INTO var_reg; END; … $$ Si se llena el registro de transacciones, hace que falle la sentencia que se está ejecutando y se deshace la operación. Cuando haces operaciones de inserción, actualización o borrado puedes hacerlo en bloques de X registros, con LIMIT, ROWID, etc. EXPLAIN PLAN: sentencia que muestra el plan de ejecución que va a seguir el SGBD para sentencias SELECT, INSERT, UPDATE y DELETE. Es la secuencia de operaciones que realiza el SGBD. CREATE INDEX ON (, ); CREATE VIEW AS [WITH CHECK OPTION] → no permite insertar ni actualizar registros si no cumplen la sentencia. CALL [()] Separador de parámetros “,” / EXEC Separador de parámetros “,” CARLOS VELILLA GARCIA – Telegram @CARLOSVELILLA 15 - B3-T4 - 2 -