1 / 116

Structured Query Language ( lenguaje estructurado de consulta )

2.4 Manipulación de datos. 2.4.3. El lenguaje relacional SQL-92. Structured Query Language ( lenguaje estructurado de consulta ) Primer lenguaje de BD de alto nivel . Años 70.

berget
Download Presentation

Structured Query Language ( lenguaje estructurado de consulta )

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 2.4 Manipulación de datos 2.4.3. El lenguaje relacional SQL-92 Structured Query Language (lenguaje estructurado de consulta) • Primer lenguaje de BD de alto nivel. Años 70. • Diseñado e implementado en el IBM’s ResearchLaboratory(San José - California), para el SGBD Relacional experimental System R • Definición de un lenguaje estándar para SGBDR ANSI (American National Standards Institute)+ ISO (International StandardizationOrganization) • SQL1 (ANSI 1986), extendido en 1989 (SQL-89) • SQL-92 (SQL2), y • SQL:1999 (extensiones de Orientación a Objetos, disparadores, …) • SQL:2003 (incluye XML y otros conceptos recientes) • Primeras implementaciones: • ORACLE (finales 70) y poco después INGRES Tema 2. Modelo relacional de datos

  2. 2.4 Manipulación de datos: SQL-92 • Lenguaje de bases de datos completo(no sólo «de consulta») • Definición y Manipulación de Datos (LDD + LMD) • Definición y destrucción de Vistas (LDV) • Creación y destrucción de índices (aunque en SQL-92«ya no existen») • Incorporación de SQL dentro de código escrito con un Lenguaje de Programación de propósito general (Pascal, C, etc.) • Los proveedores de SGBDR comerciales (Oracle) implementan variaciones de SQL • Algunas incluyen características que no están estandarizadas(triggers/reglas activas  incluidos en la versión SQL:1999) • Niveles de compatibilidad con el estándar de SQL • Entry SQL • Intermediate SQL • Full SQL Tema 2. Modelo relacional de datos

  3. 2.4 Manipulación de datos: SQL-92 Lo que vamos a estudiar... • Consultas o Selección de datos • Modificación de datos • Vistas • Definición y Alteración de datos • Esquemas, Dominios, Tablas • Restricciones de Integridad Generales (Asertos) • Seguridad y Control de Acceso Tema 2. Modelo relacional de datos

  4. 2.4 Manipulación de datos: SQL-92 Consultas básicas (4) • SQL-92 vs. Modelo Relacional Formal • No utiliza los términos formales relación,atributo,tupla…, sino tabla,columna,fila… • Permite que las tablas tengan 2 o másfilas idénticas en todos los valores de sus columnas • En general, tabla SQL  conjunto de filas, sino que Tabla SQL = Multiconjuntode filas (saco, bag) • Es posible forzar que las tablas SQL sean conjuntos de filas: • con restricciones de claveo • mediante opción DISTINCT en una SELECT (*se verá*) • Las columnas de una tablaestán ordenadas, y es posible indicar un orden de visualización de las filas • Una clave ajena puede referenciar a una clave candidata Tema 2. Modelo relacional de datos

  5. 2.4 Manipulación de datos: SQL-92 Esquema de base de datos COMPAÑÍA EMPLEADO DEPARTAMENTO OFICINA_DEPTO PROYECTO TRABAJA_EN FAMILIAR Tema 2. Modelo relacional de datos

  6. 2.4 Manipulación de datos: SQL-92 Consultas básicas • OrdenSELECT: • Instrucción básica de obtención de información SELECT <lista columnas> FROM <lista tablas> WHERE <condición> donde: <lista columnas> columnas cuyos valores va a obtener la consulta <lista tablas> tablas necesarias para realizar la consulta <condición> expresión booleana para identificar filas que obtendrá la consulta (expresión de reunión y/o de selección) Fecha de nacimiento y dirección del empleado llamado José Silva SELECT fechan, direccion FROM Empleado WHERE nombre = ‘José’ AND apellido = ‘Silva’; • La consulta selecciona las filas de <lista tablas> que satisfacen <condición>y proyecta el resultado sobre lascolumnas de<lista columnas> Tema 2. Modelo relacional de datos

  7. 2.4 Manipulación de datos: SQL-92 Consultas básicas (2) • La orden SELECT ... FROM ... WHERE... • No es igual a la operación restricción del Álgebra Relacional • SELECT de SQL tiene muchas más opciones y matices • En caso de una única tabla Ten<lista tablas> SELECT <lista columnas> FROM T WHERE <condición> es equivalente a... <lista columnas>(<condición> ( T )) * Nombre, apellido y dirección de los empleados del departamento de Investigación SELECT nombre, apellido, direccion FROM Empleado, Departamento  reunión o join de tablas WHERE nombred=‘Investigación’  condición de selección AND numerod=nd;  condición de reunión entre tablas Tema 2. Modelo relacional de datos

  8. 2.4 Manipulación de datos: SQL-92 Consultas básicas (3) • Cualquier nº de condiciones selección/reunión en SELECT * Para cada proyecto ubicado en Santiago, obtener el nº del proyecto, nº del departamento que lo controla y el apellido, dirección y fecha de nacimiento del gerente de ese departamento SELECT numerop, numd, apellido, direccion, fechan FROM Proyecto, Departamento, Empleado WHERE numd=numerod AND nssdire=nss AND lugarp=‘Santiago’; • Una SELECT puede obtener filas repetidas * Salario de los empleados de los departamentos de Administración y de Investigación SELECT salario FROM Empleado, Departamento WHERE (nombred = ‘Administración’ OR nombred = ‘Investigación’) AND numerod=nd; Tema 2. Modelo relacional de datos

  9. 2.4 Manipulación de datos: SQL-92 Consultas básicas (5): uso de* • Obtención de los valores de todas las columnas de las filas seleccionadas • No es necesario listar todos los nombres tras cláusula SELECT • Uso del símbolo * (todas las columnas) SELECT *FROM Empleado WHERE nd=5; SELECT *FROM DepartamentoWHERE nombred=‘Investigación’; SELECT *FROM Empleado, DepartamentoWHERE nombred=‘Investigación’ AND nd=númerod; Tema 2. Modelo relacional de datos

  10. 2.4 Manipulación de datos: SQL-92 Consultas básicas (6): omisión de WHERE • Selección incondicional • Equivale a una condición TRUE para todas las filas  selección de todas las filas de... • una tabla (si la cláusula FROM sólo contiene una tabla), o • el producto cartesiano entre varias tablas (si FROM incluye más de una) * Seleccionar todos los nss de empleados SELECT nss FROM Empleado; * Obtener todas las combinaciones de nss de empleados y nombres de departamentos SELECT nss, nombred FROM Empleado, Departamento; Tema 2. Modelo relacional de datos

  11. 2.4 Manipulación de datos: SQL-92 Consultas básicas (7): cadenas de caracteres • Operador LIKE • Comparación de cadenas de caracteres • Caracteres reservados: ‘%’ y‘_’(comodines) *Nombres y apellidos de los empleados cuya dirección esté en Higueras, estado de México SELECT nombre, apellido FROM Empleado WHERE direccion LIKE ‘%Higueras, MX%’ ; • Operador || • Concatenación de cadenas de caracteres * Nombres completos en una sola columna de empleados con dirección en Higueras (México) SELECT nombre || ‘ ’ || apellido FROM Empleado WHERE direccion LIKE ‘%Higueras, MX%’ ; Tema 2. Modelo relacional de datos

  12. 2.4 Manipulación de datos: SQL-92 Consultas básicas (8): aritmética y tiempo • Operaciones aritméticas • Aplicación de operadores aritméticos ( + - * /) sobre valores numéricos * Salarios de los empleados que trabajan en el proyecto ProductoX, tras un aumento del 10% SELECT apellido, nombre, 1.1*salarioFROM Empleado, Trabaja_en, ProyectoWHERE nss=nsse AND nump=numerop AND nombrep=‘ProductoX’ ;  el valor realde los salarios en la tabla EMPLEADO no cambia • Operaciones con fechas, horas, marcas de tiempo e intervalos • Especificación del valor de un INTERVAL como diferencia de dos valores DATE, TIME o TIMESTAMP • Incrementoy Decrementode valores de columnas de tipoDATE, TIME, TIMESTAMPen un intervalo compatible con el tipo Tema 2. Modelo relacional de datos

  13. 2.4 Manipulación de datos: SQL-92 Consultas básicas (9): calificación • En SQL los nombres de las columnas deben ser únicos dentro de cada tabla • Consulta que referencia a varias columnas de igual nombre, pero de tablas distintas...  AMBIGÜEDAD --------------Solución: CALIFICACIÓN El esquema COMPAÑÍA incluye las siguientes tablas: DEPARTAMENTO (nombred, numerod, nssdire, fechainicdire) OFICINA_DEPTO (numerod, oficina) * Código, nombre y lugares de los departamentos de Marketing y de Investigación SELECT Departamento.numerod, nombred, oficinaFROM Departamento, Oficina_deptoWHERE Departamento.nombred IN (‘Marketing’, ‘Investigación’) AND Departamento.numerod = Oficina_depto.numerod; Tema 2. Modelo relacional de datos

  14. 2.4 Manipulación de datos: SQL-92 Consultas básicas (10): seudónimos • Puede utilizarse seudónimos para acortar nombres de tabla dentro de las consultascon calificación: SELECT nombred, D.numerod, oficinaFROM Departamento AS D, Oficina_depto L ‘AS’ es opcionalWHERE D.nombred IN (‘Marketing’, ‘Investigación’) AND D.numerod = L.numerod; • Consulta que se refiere dos veces a la misma tabla AMBIGÜEDAD------------------- Solución: SEUDÓNIMOS * Obtener nombre y apellido de cada empleado y de su supervisor inmediato SELECT E.nombre, E.apellido, S.nombre, S.apellido FROM Empleado E, Empleado S WHERE E.nssjefe=S.nss; Tema 2. Modelo relacional de datos

  15. 2.4 Manipulación de datos: SQL-92 Consultas básicas (11): renombrar columnas • En el resultado de evaluar la consulta * Nombres de cada empleado y su supervisor, cambiando al mismo tiempo los nombres de las columnas resultantes a ‘nom_empleado’ y ‘nom_supervisor’ SELECT E.nombre AS nom_empleado, S.nombre AS nom_supervisor FROM Empleado E, Empleado S WHERE E.nssjefe = S.nss;  Nueva cabecera para la tabla resultado • Seudónimos de columnas (y/o tablas) en cláusula FROM SELECT nom, num, oficinaFROM Departamento D(nom, num, dire, inidire), Oficina_depto WHERE nom IN (‘Marketing’, ‘Investigación’) AND num = numerod; Tema 2. Modelo relacional de datos

  16. 2.4 Manipulación de datos: SQL-92 Consultas básicas (y 12): orden de presentación • SQLpermite presentar las filas resultado de una consulta de forma ordenada: Cláusula ORDER BY • Ordenación según valores de una o varias columnas • AscendenteASC (por defecto) o DescendenteDESC • Suele ser una operación muy costosa las filas no se ordenan en disco: se ven ordenadas, pero no lo están *Nombre y apellido de los empleados, y proyectos en los que trabajan, en orden descendente por departamentos y, dentro de cada departamento, en orden alfabético ascendente por apellido y nombre SELECT nombred, apellido, nombre, nombre FROM Departamento, Empleado, Trabaja_en, Proyecto WHERE numerod=nd AND nss=nsse AND nump=numerop ORDER BY nombred DESC, apellido ASC, nombre ASC; Tema 2. Modelo relacional de datos

  17. 2.4 Manipulación de datos: SQL-92 Tablas como conjuntos • SQL no eliminafilas repetidas del resultado de una consulta, porque... • Eliminación de duplicados costosa (ordenar+recorrer+eliminar) • El usuario puede desear ver las filas repetidas en el resultado • Si se aplica una función agregada a filas, rara vez deben eliminarse las duplicadas • Operador DISTINCT: • Para eliminar duplicados del resultado de una consulta SQL • Resultado = Relación del Modelo Relacional Formal (conjunto de filas) * Salarios de todos los empleados SELECT salario FROM Empleado; * Salarios distintos de empleados, sin importar cuántos perciban cada cantidad SELECT DISTINCT salario FROM Empleado; Tema 2. Modelo relacional de datos

  18. 2.4 Manipulación de datos: SQL-92 Tablas como conjuntos(2) • Operaciones de conjuntos • UNION(  ), INTERSECT(  ), EXCEPT ( — ) (minus en ORACLE) • Resultado: conjunto de filas  las filas repetidas se eliminan • Las tablas operando han de ser compatibles en tipo: • igual nº de columnas,y • columnas “correspondientes” con el mismo dominio * Nombres de los proyectos en que participa el empleado de apellido ‘Silva’, ya sea como trabajador o como gerente del departamento que controla el proyecto ( SELECT nombrep FROM Proyecto, Trabaja_en, Empleado WHERE numerop=nump AND nsse=nss AND apellido=‘Silva’ ) UNION ( SELECT nombrep FROM Proyecto, Departamento, Empleado WHERE numd=numerod AND nssdire=nss AND apellido=‘Silva’ ); • Para no eliminar duplicados... • UNION ALL, INTERSECT ALL, EXCEPT ALL Tema 2. Modelo relacional de datos

  19. 2.4 Manipulación de datos: SQL-92 Tablas como conjuntos(3): conjuntos explícitos • Un conjunto explícito de valores es una lista de valores encerrada entre paréntesis • Puede aparecer en la cláusulaWHERE * nss de los empleados que trabajan en los proyectos 1, 2 ó 3 SELECT DISTINCT nsse FROM Trabaja_en WHERE numpIN (1, 2, 3); • Operador IN v IN V • Indica si el valor vpertenece al conjunto de valores V • Devuelve TRUE si algún elemento e de V cumple quev = e * nss de los empleados que trabajan en algún proyecto que no sea el 4 ni el 6 SELECT DISTINCT nsse FROM Trabaja_en WHERE numpNOT IN (4, 6); Tema 2. Modelo relacional de datos

  20. 2.4 Manipulación de datos: SQL-92 Tablas como conjuntos(4): conjuntos explícitos • Operador ANY (o SOME) v <op> ANY V o v <op> SOME V ,, <op>  { , , , , <>,  } • Compara un valor individual vcon los elementos de un conjunto V • Devuelve TRUE si algún elemento e de V cumple quev <op> e * nss de los empleados que trabajan en alguno de los proyectos 1, 2 ó 3 SELECT DISTINCT nsse FROM Trabaja_en WHERE nump = ANY (1, 2, 3); • Operador ALL v <op> ALL V,, <op>  { , , , , <>,  } • Compara un valor v con los elementos de un conjunto V • Devuelve TRUE si para todo elemento e de V se cumple v <op> e * nss de los empleados que no trabajan en ninguno de los proyectos 1, 2 y 3 SELECT DISTINCT nsse FROM Trabaja_en WHERE nump <> ALL (1, 2, 3); Tema 2. Modelo relacional de datos

  21. 2.4 Manipulación de datos: SQL-92 Consultas anidadas • Es una consulta SELECT completa, dentro de cláusula WHERE de otra consulta (consulta exterior) • Obtiene valores de la BD que se usan en la condición de otra consulta, para obtener otros datos * Números de los proyectos en que participa el empleado de apellido ‘Silva’, sea como trabajador o como gerente del departamento que controla el proyecto SELECT DISTINCT numerop FROM PROYECTO WHERE numerop IN ( SELECT nump FROM Trabaja_en, Empleado WHERE nsse=nss AND apellido=‘Silva’ ) OR numerop IN ( SELECT numerop FROM Proyecto, Departamento, Empleado WHERE numd=númerod AND nssdire=nss AND apellido=‘Silva’ ) ; • Es posible tener varios niveles de consultas anidadas Tema 2. Modelo relacional de datos

  22. 2.4 Manipulación de datos: SQL-92 Consultas anidadas (2): comparar conjuntos • Operador IN (otro uso del mismo operador) t IN S • indica si la fila tpertenece al conjunto de filasS (subconsulta) * Nombre y dirección de los empleados que trabajan en algún proyecto. SELECT nombre, dirección FROM Empleado WHERE nssIN ( SELECT nsse FROM TRABAJA_EN ); * Números de seguridad social de aquellos empleados que trabajan en algún proyecto en el que trabaje el empleado ‘José B. Silva’, de forma que ambos tengan la misma combinación (proyecto, horas); es decir, todo empleado que trabaje las mismas horas que ‘José B. Silva’, en cada proyecto en el que trabajen ambos. El nss de ‘José B. Silva’ es ‘123456789’. SELECT DISTINCT nsse FROM Trabaja_en WHERE (númp, horas)IN ( SELECT númp, horas FROM Trabaja_en WHERE nsse=‘123456789’); Tema 2. Modelo relacional de datos

  23. 2.4 Manipulación de datos: SQL-92 Consultas anidadas (3): comparar conjuntos • Operador ANYo SOME (otro uso del mismo operador) t <op> ANY S o t <op> SOME S,, <op>  { , , , , ,  } • Compara una fila tcon las filas resultado de una consulta anidada S • Devuelve TRUE si alguna fila e de S cumple quet <op> e • Operador ALL (otro uso del mismo operador) t <op> ALL S,, <op>  { , , , , ,  } • Compara una fila tcon filas resultado de una consulta anidada S • Devuelve TRUE si para toda fila e de S se cumple quet <op> e * Nombres y apellidos de los empleados cuyo salario es menor que el de todos los empleados del departamento 5 SELECT nombre, apellido FROM Empleado WHERE salario < ALL ( SELECT salario FROM Empleado WHERE nd=5 ); ¿”Mejor” con DISTINCTen la subconsulta? Tema 2. Modelo relacional de datos

  24. 2.4 Manipulación de datos: SQL-92 Consultas anidadas (4): columnas ambiguas • Coincidencia de nombres de columnas en las consultas exterior y anidada Ambigüedad * Nombre y apellidos de cada empleado con familiares de igual nombre y sexo que él SELECT nombre, apellido FROM Empleado WHERE nss IN ( SELECT nsse FROM Familiar WHERE nsse=nss AND nombre_familiar=nombre AND sexo=sexo );  ¿cómo evitar esta ambigüedad? • Regla: Una columna no calificada se refiere a la tabla declarada en la consulta anidada más interior • Si en una consulta anidada es necesario usar columnas de tablas declaradas en una consulta exterior  calificar * Nombre y apellidos de cada empleado con familiares de igual nombre y sexo que él SELECT nombre, apellido FROM Empleado E WHERE nss IN ( SELECT nsse FROM Familiar WHERE nss=nsse AND nombre_familiar=nombre AND sexo= E.sexo ); Tema 2. Modelo relacional de datos

  25. 2.4 Manipulación de datos: SQL-92 Consultas anidadas (5): correlación • Una consulta exterior y otra anidada están correlacionadas si una condición de la anidada contiene columnas de una tabla declarada en la consulta exterior SELECT nombre, apellido FROM Empleado WHERE nss IN ( SELECT nsse FROM Familiar WHERE nss=nsse AND sexo=‘F’ ); • La consulta anidada se evalúauna vez para cada fila (o combinación de filas) de la consulta exterior • Evalúa la consulta anidada para cada fila de EMPLEADO, • Si el valor de nss de la fila EMPLEADO está en el resultado de la consulta anidada, selecciona la fila EMPLEADO para el resultado final • Una consulta anidada que use el operador= o IN siemprepuedeexpresarse como una reunión (join) SELECT E.nombre, E.apellido FROM Empleado, Familiar D WHERE nss=nsse AND D.sexo=‘F’; Tema 2. Modelo relacional de datos

  26. 2.4 Manipulación de datos: SQL-92 Consultas anidadas (6): EXISTS • Operador EXISTS (S): comprobación de tablas vacías • Devuelve TRUE si la tabla S contiene al menos una fila • Devuelve FALSE si S es una tabla vacía (sin filas) • S suele ser una consulta anidada correlacionada * Nombre y apellido de cada empleado con familiares de igual nombre y sexo que él SELECT E.nombre, E.apellido FROM Empleado E WHERE EXISTS ( SELECT * FROM Familiar WHERE nsse=nss AND nombre_familiar=nombre AND sexo=E.sexo ); * Nombres de empleados sin familiares SELECT nombre, apellido FROM Empleado E WHERE NOT EXISTS (SELECT * FROM Familiar WHERE nsse=nss); Tema 2. Modelo relacional de datos

  27. 2.4 Manipulación de datos: SQL-92 Consultas anidadas (y 7): UNIQUE • Operador UNIQUE (S): Comprobación de filas duplicadas • Devuelve TRUE si NO hay filas repetidas en S • S suele ser una consulta anidada correlacionada * Nombres y apellidos de los empleados que trabajan en un único proyecto SELECT nombre, apellido FROM Empleado WHERE UNIQUE ( SELECT nsse FROM Trabaja_en WHERE nsse = nss ); * Nombres, apellidos y salario de los empleados con un solo familiar SELECT nombre, apellido, salario FROM Empleado WHERE UNIQUE ( SELECT * FROM Familiar WHERE nsse = nss ); Tema 2. Modelo relacional de datos

  28. 2.4 Manipulación de datos: SQL-92 Nulos • Null • Ausencia o desconocimiento de información • Comparar NULL con cualquier cosa da FALSE • Operador IS NULL ,, IS NOT NULL v IS NULL • es TRUE si v es NULL v IS NOT NULL • es TRUE si v es un valor no NULL * Nombres de empleados sin supervisores SELECT nombre, apellido FROM Empleado WHERE nssjefeIS NULL; Tema 2. Modelo relacional de datos

  29. 2.4 Manipulación de datos: SQL-92 Funciones agregadas • Función COUNT( ) • Cuenta el número de filas o de valores especificados en una consulta • Funciones SUM( ), MAX( ), MIN( ), AVG( ) • Suma, máximo, mínimo y media aritmética (promedio) • Aplicadas a un multiconjunto (saco, bag) de valores numéricos • Pueden aparecer en cláusula SELECT * Suma de los salarios y salario máximo, mínimo y medio de los empleados SELECT SUM(salario), MAX(salario), MIN(salario), AVG(salario) FROM EMPLEADO; * Suma de los salarios y salario máximo, mínimo y medio de empleados del depto. de Investigación SELECT SUM(salario), MAX(salario), MIN(salario), AVG(salario) FROM Empleado, Departamento WHERE nd=númerod AND nombred=‘Investigación’; • También pueden aparecer en cláusula HAVING(*se verá*) Tema 2. Modelo relacional de datos

  30. 2.4 Manipulación de datos: SQL-92 FUNCIONES AGREGADAS Funciones agregadas (2): uso de * y de DISTINCT • Uso de* *Número total de empleados de la compañía SELECT COUNT(*) FROM Empleado( cuenta filas) * Contar el número de empleados de la compañía que tienen un jefe SELECT COUNT(nssjefe) FROM Empleado; ( cuenta filas con nssjefe no NULL) * Número de empleados en el departamento de Investigación SELECT COUNT(*) FROM Empleado, Departamento WHERE nd=númerod AND nombred=‘Investigación’; • Uso deDISTINCT * Contar el nº de valores distintos de salario que pueden cobrar los empleados SELECT COUNT(salario) FROM Empleado; Error: NO se eliminan duplicados, así que COUNT(salario)  COUNT(*) SELECT COUNT(DISTINCT salario) FROM Empleado; OK !! Tema 2. Modelo relacional de datos

  31. 2.4 Manipulación de datos: SQL-92 Funciones agregadas (y 3) y correlación • Es posible que una consulta anidada y correlacionada con otra exterior, incluya una función agregada * Nombres de los empleados con 2 o más familiares SELECT apellido, nombre FROM Empleado WHERE 2  ( SELECT COUNT(*) FROM Familiar WHERE nss=nsse ); Tema 2. Modelo relacional de datos

  32. 2.4 Manipulación de datos: SQL-92 Agrupación • CláusulaGROUP BY • Para formar subgrupos de filas dentro de una tabla • Los grupos se forman según el valor de las columnas de agrupación • Las filas de cada grupo tendrán el mismo valor en las columnas de agrupación • Aplicación de funciones agregadas a grupos de filas * Para cada departamento, obtener su número, cuántos empleados tiene dicho departamento y el salario medio de los empleados del mismo SELECT nd, COUNT(*), AVG(salario) FROM Empleado GROUP BYnd; una columna de agrupación  Las columnas de agrupación deben aparecer en la cláusula SELECT, antes de cualquier función agregada, para que su valor (único para cada grupo) aparezca junto al resultado de aplicar la función al grupo Tema 2. Modelo relacional de datos

  33. 2.4 Manipulación de datos: SQL-92 Agrupación(2) • Cláusula HAVING • Siempre junto a GROUP BY • Condición que deben cumplir los grupos de filas asociados a cada valor de las columnas de agrupación • Un grupo que no cumple la condición, no es seleccionado para el resultado * Para cada proyecto en el que trabajen más de dos empleados, obtener el número y nombre del proyecto, y el nº de empleados que trabajan en él SELECT numerop, nombrep, COUNT(*) FROM Proyecto, Trabaja_en WHERE numerop=nump GROUP BY numerop, nombrep HAVING COUNT(*) > 2 ; Tema 2. Modelo relacional de datos

  34. 2.4 Manipulación de datos: SQL-92 Agrupación(y 3) • WHERE... se aplica afilas individuales • HAVING... se aplica agrupos de filas * Nº de empleados cuyos salarios superan los 1.800€ en cada departamento, pero sólo en el caso de departamentos en los que trabajen más de 5 empleados (* Consulta incorrecta ¿por qué? *) SELECT nombred, COUNT(*) FROM Departamento, Empleado WHERE númerod=nd AND salario>1800 GROUP BY nombred HAVING COUNT(*) > 5; (* pista: orden de ejecución *) (* Consulta correcta *) SELECT nombred, COUNT(*) FROM Departamento, Empleado WHERE númerod=nd AND salario>1800 AND nd IN (SELECT nd FROM Empleado GROUP BY nd HAVING COUNT(*) > 5) GROUP BY nombred ; Tema 2. Modelo relacional de datos

  35. 2.4 Manipulación de datos: SQL-92 Tablas reunidas • Reunión especificada en la cláusula FROM de una consulta • Hasta ahora la hemos especificado en cláusulas FROM y WHERE * Nombres y dirección de empleados del departamento de Investigación SELECT nombre, apellido, direccionFROM Empleado, Departamento reunión de tablasWHERE nombred=‘Investigacion’ AND nd=numerod;  condición de reunión • Consultas más comprensibles: separa condiciones de reunión y de selección * Nombres y dirección de empleados del departamento de Investigación SELECT nombre, apellido, direccionFROM (Empleado JOIN Departamento ON nd=numerod)  tabla reunidaWHERE nombred=‘Investigacion’; Tema 2. Modelo relacional de datos

  36. 2.4 Manipulación de datos: SQL-92 Tablas reunidas (2):anidamiento • Es posible anidar varias especificaciones de reunión de tablas * Para cada proyecto ubicado en ‘Santiago’, obtener el nº de proyecto, el nº del departamento que lo controla y el apellido, dirección y fecha de nacimiento del gerente de ese departamento SELECT númerop, númd, apellido, dirección, fechan FROM ( ( Proyecto JOIN Departamento ON númd=númerod ) JOIN Empleado ON nssdire=nss ) WHERE lugarp=‘Santiago’; Tema 2. Modelo relacional de datos

  37. 2.4 Manipulación de datos: SQL-92 Reunión Interna de tablas (inner join) • Es el tipo de reunión “por defecto” SELECT ... FROM ( R1JOIN R2 ON <condición_reunión> ) WHERE ... • Si existe una fila t1 en R1 y otra fila t2 en R2, tales que cumplen la condición de reunión, la tabla resultado (reunida) incluirá la fila obtenida al combinar t1 y t2 SELECT E.nombre AS nom_empleado, S.nombre AS nom_supervisor FROM (Empleado E JOIN Empleado S ON E.nssjefe = S.nss); • Son excluidas las filas EMPLEADO con NULL en nssjefe • También puede especificarse como R1INNER JOIN R2 ON <condición_reunión> Tema 2. Modelo relacional de datos

  38. 2.4 Manipulación de datos: SQL-92 Reunión Natural de tablas (natural join) • Sin condición de reunión explícita SELECT ... FROM ( R1NATURALJOIN R2 ) WHERE ... • Equi-reunión implícita para cada par de columnas con igual nombre en una y otra tabla • Sólo se incluye una de estas columnas en el resultado • Si no coinciden los nombres de las columnas, es necesario RENOMBRAR una de ellas mediante AS en la cláusula FROM SELECT nombre, apellido, direccion FROM ( Empleado NATURAL JOIN (Departamento AS DEP(nombred, nd, dire, fech)) ) WHERE nombred=‘Investigacion’; Tema 2. Modelo relacional de datos

  39. 2.4 Manipulación de datos: SQL-92 Reunión Externa de tablas (outer join) • Útil si en una reunión se necesita obtener las filas… • con valor NULL en las columnas de reunión, o • sin correspondencia en la otra tabla • Tipos de reunión externa: • LEFT [OUTER] JOINReunión externa izquierda • RIGHT [OUTER] JOINReunión externa derecha • FULL [OUTER] JOIN Reunión externa completa o total SELECT E.nombre AS nom_empleado, S.nombre AS nom_supervisor FROM (Empleado E LEFT OUTER JOIN Empleado S ON E.nssjefe=S.nss); • Obtiene también los empleados sin supervisor (con NULL en nssjefe) Tema 2. Modelo relacional de datos

  40. 2.4 Manipulación de datos: SQL-92 Evaluación de consultas • En una consulta SQL hay un máximo de 6 cláusulas • Sólo son obligatorias SELECTyFROM • Ordende especificación de las cláusulas: SELECT <lista columnas> columnas o funciones que se van a obtener FROM <lista tablas> tablas necesarias (incluso las reunidas) WHERE <condición para filas> condiciones para selección de filas GROUP BY <lista columnas agrupación> especificación del agrupamiento de filas HAVING <condición para grupos> condición para selección de grupos de filas ORDER BY <lista columnas ordenación> orden de presentación del resultado Tema 2. Modelo relacional de datos

  41. 2.4 Manipulación de datos: SQL-92 Evaluación de consultas (2) • Orden de evaluación de las cláusulas: 1)FROM(es decir, la reunión o join de tablas, si se especifica más de una) 2)WHERE 3)GROUP BY 4)HAVING 5) SELECT 6)ORDER BY • Diversas formas de especificar una misma consulta Ejemplo: es posible expresar una consulta utilizando... a) condiciones de reunión en cláusula WHERE, o b) tablas reunidas en la cláusula FROM, o c) consultas anidadas y el operador de comparación IN ... Flexibilidad Tema 2. Modelo relacional de datos

  42. 2.4 Manipulación de datos: SQL-92 Evaluación de consultas (y 3) • Ventajas e inconvenientes de esta flexibilidad: – el usuarioelige la técnica o enfoque más cómodo  – Confusión del usuario: ¿qué técnica uso? – Algunas técnicas son más eficientes que otras el usuario debe determinar cuál • En condiciones ideales... • Usuario: se preocupa sólo de especificar la consulta correctamente • SGBD: se ocupa de ejecutar la consulta de manera eficiente • Pero en la práctica no suele ser así...  convienesaber qué tipos de consulta son más y menos costosos Recomendación (optimización de consultas):Consultas con mínimo anidamiento correlacionado y mínimo ordenamiento implícito Tema 2. Modelo relacional de datos

  43. 2.4 Manipulación de datos: SQL-92 Inserción de datos • OrdenINSERT • Añade una fila completa a una tabla • Incluye nombre de la tabla y lista de valores para las columnas, escritos en igual orden al especificado en la orden CREATE TABLE INSERTINTO Empleado VALUES ( 'Ricardo', ‘C’, 'Martínez', '653298653123', ‘987654321’, '30-DIC-52', 'Olmo 98, Cedros, MX', ‘M’, 37000, '987654321321', 4 ) ; • Si se desea poner los valores de las columnas en cualquier orden, hay que especificar los nombres de las columnas en dicho orden INSERT INTO Empleado ( nombre, apellido, nss, nif, nd, salario, nssjefe, direccion, fechan, sexo ) VALUES ( 'Ricardo', ‘C’, 'Martínez', '653298653123', ‘987654321’, 4, 37000, '987654321321', 'Olmo 98, Cedros, MX', '30-DIC-52', ‘M’ ) ; Tema 2. Modelo relacional de datos

  44. 2.4 Manipulación de datos: SQL-92 Inserción (2) • Inserción de varias filas en una sola ordenINSERT • Filas separadas por comas • Cada fila se encierra entre paréntesis • Especificación explícita de algunascolumnas(y no todas) • Omisión de columnas cuyo valor se desconoce • Cada columna no especificada tomará el... • valor por omisión: valor tomado de su cláusula DEFAULT, o • NULL: si la columna permite nulos y no se definió cláusula DEFAULT para la misma * Inserción de un empleado del que sólo se conoce su nombre, apellidos, nss y nif INSERT INTO Empleado (nombre, apellido, nss, nif) VALUES ( 'Rubén', 'Ripoll', '553298653111‘, ‘11222333R’ ) ; Tema 2. Modelo relacional de datos

  45. 2.4 Manipulación de datos: SQL-92 Inserción (3): Restricciones de Integridad • Si SGBD con implementación total de SQL-92 • El SGBD maneja e impone todaRI definida en esquema de BD (LDD) • Si SGBD con implementación de algunas RI • Menor complejidad, mayor eficiencia • SGBD implementacomprobaciones para imponer RI que sí maneja INSERT INTO Empleado (nombre, apellido, nd) VALUES ( 'Roberto', 'Huertas', 2 ) ; • Inserción rechazada: no se incluye valor para nss, que debe serNOT NULL • Programador debe asegurar la no violación de las RI no manejadas por el SGBD Supongamos que no existe departamento con numerod=8 INSERT INTO Empleado (nombre, apellido, nss, nif, nd)VALUES ( 'Roberto', 'Huertas', '980760540222', ‘22333444H’, 8 ) ; • Si el SGBD sí maneja la Integridad Referencial • Inserción rechazada • Si el SGBD NO soporta la Integridad Referencial • Inserción permitida  ¡el programador debe asegurar que esto no pase! Tema 2. Modelo relacional de datos

  46. 2.4 Manipulación de datos: SQL-92 Inserción (y 4): filas resultado de una consulta • Carga de una tabla con información sinóptica de la BD Sea una tabla INFO_DEPTOS vacía. En ella queremos almacenar los nombres de cada departamento, su nº de empleados y el salario conjunto de los empleados del mismo. INFO_DEPTOS ( nombre_depto, num_emps, sal_total) INSERT INTO Info_deptos ( nombre_depto, num_emps, sal_total ) SELECT nombred, COUNT(*), SUM(salario) FROM Departamento, Empleado WHERE númerod=nd GROUP BY nombred ; • Es posible hacer SELECT ... FROM Info_deptos ... •  INFO_DEPTOS puede contener información no actualizada • Si se modifica información enEMPLEADOy/oDEPARTAMENTO, los cambios no se reflejarán en la tablaINFO_DEPTOS • Una vista sí “contiene” siempre los datos más actuales (*se verá*) Tema 2. Modelo relacional de datos

  47. 2.4 Manipulación de datos: SQL-92 Eliminación de datos • OrdenDELETE • Elimina filas completas de una tabla • Sólo una tabla en cláusula FROM • CláusulaWHERE para seleccionar las filas que eliminar • Si no hay WHERE, se eliminan todas las filas • La tabla permanece, pero queda vacía DELETE FROM Empleado ;todas las filas DELETE FROM Empleado WHERE apellido=‘Bojórquez’;0 filas DELETE FROM Empleado WHERE nss=‘123456789012’ ;1 fila DELETE FROM Empleado WHERE nd IN ( SELECT numerod FROM Departamento WHERE nombre=‘Investigación’) ; 4 filas • Propagación de eliminaciones • Según acciones de mantenimiento de la Integridad Referencial especificadas con LDD en losCREATE TABLE (esquema de BD) Tema 2. Modelo relacional de datos

  48. 2.4 Manipulación de datos: SQL-92 Actualización de datos • OrdenUPDATE • Modifica valores de columnas en una o más filasde una tabla • Se modifican filas de una sola tabla a la vez • Cláusula SET especifica columnas que modificar y nuevos valores • CláusulaWHERE para seleccionar filas que actualizar • Si no hay WHERE, se aplica la modificación a todas las filas * Para el proyecto 10, cambiar el lugar a Belén y el nº de depto controlador al 5 UPDATE Proyecto SET lugarp = ‘Belen’, númd = 5 WHERE numerop=10 ; • Propagación de modificaciones • Si cambia un valor de clave candidata, este cambio se propaga a valores de clave ajena de filas de otras tablas, si así se especificó en las acciones de mantenimiento de la Integridad Referencial en la definición de la tabla conCREATE TABLE Tema 2. Modelo relacional de datos

  49. 2.4 Manipulación de datos: SQL-92 Actualización (y 2) • Modificación de varias filas a la vez conUPDATE * Conceder a todo empleado del departamento de Investigación un aumento salarial del 10% UPDATE Empleado SET salario = salario*1.1 WHERE nd IN (SELECT númerod FROM Departamento WHERE nombred=‘Investigación’) ; • NULL o DEFAULT comonuevo valor de una columna UPDATE Empleado SET salario = DEFAULT; UPDATE Empleado SET nssjefe = NULL WHERE ... ; Tema 2. Modelo relacional de datos

  50. 2.4 Manipulación de datos: SQL-92 LDD: Definición de datos • Esquema de Base de Datos Relacional • Agrupa tablas y otros elementos, de una misma aplicación • 1as versiones de SQL: todas las tablas dentro de un esquema único y global a todas las aplicaciones que accedían a la BD • OrdenCREATE SCHEMA: definición/creación de esquemas CREATE SCHEMA <nombre de esquema>AUTHORIZATION <identificador de autorización> • <nombre de esquema> identifica el esquema • <identificador de autorización> usuario/cuenta propietaria del esquema CREATE SCHEMA Compañía AUTHORIZATION JSILVA ; • A continuación puede especificarse lasdefiniciones deloselementoscontenidos en dicho esquema • Elementos del esquema: Tablas, Vistas, Dominios, Autorizaciones, Restricciones, etc. Tema 2. Modelo relacional de datos

More Related