Bases de datos y sistemas de información

Tema 5: Lenguaje de acceso a bases de datos SQL Contenido: 5.1 Introducción 5.2 Sentencias DML 5.2.1 Select 5.2.2 Insert, Delete y Update 5.2.3 Recursividad 5.3 Sentencias DDL 5.4 Sentencias DAL 5.5 SQL y el álgebra relacional

Tema 5: El lenguajes de acceso a bases de datos SQL 5.1 Introducción Este tema se dedica al estudio de lenguajes de acceso a base de datos relacional SQL. En realidad al hablar de SQL se está hablando de un lenguaje que contiene instrucciones de los 3 grupos de lenguajes de bases de datos de los que hablamos en el tema introductorio. • • •

Consultar y modificar datos. DML: Data Manipulation Language. Definir la base de datos. DDL: Data Definition Language Controlar el acceso (seguridad). DAL: Data Access Language

A diferencia de los estudiados en el tema de los lenguajes formales, éstos no son formales e incluyen características no contempladas en aquéllos. Algunos lenguajes: • ISBL. Casi equivalente al álgebra relacional. • QUEL. Lenguaje del cálculo de tuplas. • QBE. Lenguaje del cálculo de dominios. • SQL. Lenguaje relacional estándar de facto superconjunto del álgebra relacional. • DATALOG: Lenguaje de acceso basado en programación lógica. • XQUERY: Lenguaje basado en el modelo jerárquico –no relacional- usado para realizar consultas sobre XML. Este tema se dedica en particular a SQL, un estándar para bases de datos relacionales: última versión SQL1999 que se utiliza en multitud de SGBD . Nosotros vamos a fijarnos en la versiones de ORACLE (SQL*PLUS), Access y MySql

5.2 Sentencias DML SQL contempla 4 sentencias de este tipo:

5-1

Bases de datos y sistemas de información

-

Select para consultas, (4.2.1) Insert para añadir filas (4.2.2) Delete para borrar filas (4.2.3) Update para modificar filas (4.2.4)

5.2.1 Sentencia SELECT Forma básica de SELECT

SELECT lista_de_atributos FROM lista de tablas WHERE condición; Ej. Seleccionar los códigos de proyecto en los que trabaja el empleado con dni4 SELECT codigoPr FROM distribución WHERE dniEmp = 4; CodigoPr P1 P2 P3 P4 - La parte WHERE es opcional, pero la parte SELECT y FROM son obligatorias Conjunto de datos seleccionados.

Ej. DNI trabajadores que trabajan entre 10 y 20 horas en algún proyecto SELECT DNIEmp FROM distribución WHERE horas>=10 AND horas =10 AND horas , >=, 10) Programadores que no trabajan en ningún proyecto SELECT Nombre FROM programadores WHERE not exists (SELECT CodigoPr FROM distribución WHERE distribución.DNIEmp = programadores.DNI) Cláusula ORDER BY

SELECT * FROM distribución WHERE horas > 10 ORDER BY horas CodigoPr DNIEmp Horas P4 4 20 P3 5 30 P3 3 40 P1 2 40 SELECT * FROM distribución WHERE horas > 10 ORDER BY horas desc, CodigoPr CodigoPr DNIEmp Horas P1 2 40 P3 3 40 P3 5 30 5-6

Bases de datos y sistemas de información

P4

4

20

Cláusula GROUP BY

SUM AVG MAX MIN COUNT SELECT COUNT(*) FROM programadores 4 SELECT COUNT(CodigoPr) FROM distribución 10 SELECT COUNT(DISTINCT codigoPr) FROM distribución 4 SELECT SUM(horas) FROM distribución SUM(horas) -- --- - - - - 180 SELECT CodigoPr, SUM(horas) FROM distribución GROUP BY CodigoPr Trabajadores que trabajan un total de mas de 20 horas SELECT DNIEMP, SUM(horas) FROM distribución GROUP BY DNIEmp HAVING SUM(horas)>20

Nombres y total de horas de los programadores que trabajan en mas de 2 proyectos diferentes. SELECT programadores.Nombre, distribución.DNIEMp, SUM(distribución.horas) FROM distribución, programadores WHERE programadores.DNI = distribución.DNIEMp GROUP BY distribución.DNIEmp, programadores.nombre HAVING COUNT ((distribución.CódigoPr)) >= 2 ORDER BY SUM(distribución.horas)

Vistas Creación de tablas virtuales. No existen en la realidad sino que se refieren a consultas en otras tablas Nombres de los programadores que trabajan más horas que la media

5-7

Bases de datos y sistemas de información

CREATE VIEW DNIHoras(DNI, horas) AS SELECT DNIEMp, SUM(horas) FROM distribución GROUP BY DNIEMP; CREATE VIEW NombreHoras(nombre,horas) AS SELECT programadores.nombre, DNIHoras.horas FROM programadores, distribución WHERE programadores.Dni = distribución.DNIEmp; CREATE VIEW MediaHoras(media) AS SELECT AVG(horas) FROM NombreHoras; SELECT N.Nombre FROM NombreHoras WHERE horas > (SELECT media FROM MediaHoras);

Condiciones con ANY, ALL Se trata de condiciones que se pueden utilizar en las secciones where y/o having y cuya sintaxis es de la forma Expr op ALL (subconsulta) O Expr op ANY (subconsulta) El operador puedes ser cualquier operador relacional: >, =, =all (select horas from DNIHoras);

Otra forma de hacer la vista resultado: CREATE view RESULTADO(DNI,horas) AS Select * from DNIHORAS Where horas = (select max(horas) from DNIHoras);

5-8

Bases de datos y sistemas de información

Observación Las funciones de agrupación se pueden aplicar a subconsultas siempre y cuando estas tengan una sola expresión en su parte select y ésta sea de tipo numérico. Condiciones con DECODE, IIF, CASE DECODE, IIF y CASE son distintos nombres para sentencias de tipo condicional. Ejemplo: Obtener un listado con el nombre y si trabaja menos de 8 horas la palabra 'poco' y en otro caso la palabra 'mucho’. La segunda columna debe llamarse ‘Trabaja’ select nombre, if(horas inferir (R,I1) ⊆ inferir (R,I2) Si inferir es monótona, dado un conjunto de hechos I0 que es un subconjunto de hechos verdaderos, se puede asegurar que todos los hechos del conjunto inferir (R,I0) son verdaderos. Las expresiones del álgebra relacional que utilizan los operadores Π, σ, x, |x|, ∪, ∩ o ρ son monótonas. Las vistas recursivas se pueden definir utilizando dichas expresiones. Sin embargo, las expresiones relacionales que utilizan el operador – no son monótonas. Ej: sean jefe1 y jefe2 relaciones con el mismo esquema que la relación jefe. Dadas I1 = { jefe1 (“Arteaga”, “Benzoaga”), jefe1 (“Benzoaga”, “Erejalde”), jefe2 (“Arteaga”, “Benzoaga”)} I2 = { jefe1 (“Arteaga”, “Benzoaga”), jefe1 (“Benzoaga”, “Erejalde”), jefe2 (“Arteaga”, “Benzoaga”), jefe2 (“Benzoaga”, “Erejalde”)} Considérese la expresión jefe1 – jefe2. El resultado de la expresión I1 anterior es (“Benzoaga”,”Erejalde”), mientras que el resultado de la expresión I2 es la relación vacía. Se cumple que I1 ⊆ I2. Según la definición, la expresión no es monótona. Las expresiones que se definen utilizando los operadores de agrupación del álgebra relacional extendida tampoco son monótonas. Recursividad en Oracle

Oracle también proporciona recursividad lineal en las denominadas consultas jerárquicas. Sirven fundamentalmente para realizar recorridos en profundidad de árboles. Las consultas jerárquicas permiten seleccionar filas en orden jerárquico usando las cláusulas: START WITH START_CONDITION CONNECT BY CONNECT_CONDITION La cláusula START WITH permite especificar las filas que son raíces en la jerarquía (puede haber más de una raíz).

5-16

Bases de datos y sistemas de información

La cláusula CONNECT BY especifica la relación que existe entre las filas padre e hijo en la jerarquía. Se usa el operador unario PRIOR para obtener la fila padre de la fila actual. Se puede usar también la cláusula WHERE para restringir las filas devueltas por la consulta. Oracle ejecuta las consultas jerárquicas siguiendo los pasos siguientes: • Selecciona las filas raíz de la jerarquía, que son las que cumplen START_CONDITION. • Selecciona las filas hijo de cada fila raíz. Las filas hijo son aquellas filas de la tabla que satisfacen la condición CONNECT_CONDITION, con respecto a alguna de las filas raíz. Pueden ser condiciones complejas pero no pueden incluir subconsultas. • Selecciona generaciones sucesivas de filas hijo. Oracle selecciona en primer lugar los hijos de las filas del apartado 2, y después los hijos de estos hijos y así sucesivamente (recorre la jerarquía en profundidad). • Si la consulta contiene una cláusula WHERE, se eliminan todas las filas de la jerarquía que no satisfacen la condición del WHERE. La condición se evalúa para cada fila de la tabla de manera individual y no se eliminan los hijos de una fila que no cumpla la condición del WHERE. select empl, jefe from jefes connect by prior empl = jefe start with empl = 1; Para conocer el nivel de la jerarquía se dispone automáticamente de la pseudocolumna level. Con ella se puede conocer fácilmente el nivel en la jerarquía de cada empleado: select empl, jefe, level from jefes connect by prior empl = jefe start with empl = 1;

5.3 Sentencias DDL Es parte del lenguaje SQL, pero ya lo hemos estudiado en el tema 3, por lo que no lo repetimos aquí.

5.4 Sentencias DAL Permiten especificar directivas de control de acceso a los recursos. El control de acceso se especifica por usuario: del sistema operativo o del SGBD. Recursos y privilegios Recursos: Tablas, campos, vistas y dominios. Privilegios: Derechos de acceso de un usuario a los recursos. Los privilegios están caracterizados por: - El recurso al que se refiere. 5-17

Bases de datos y sistemas de información

- El usuario que concede el privilegio. - El usuario que lo recibe. - La acción permitida sobre el recurso. - Si el privilegio se puede transmitir por el usuario al que se le ha concedido. Usuario administrador de la base de datos: _system. Privilegios: - insert - update - delete - select - references. Ej: Integridad referencial. - usage. Aplicado sobre los dominios, permiten usar dominios en la definición del esquema de una tabla. Comandos para la concesión y revocación de privilegios Concesión

Sintaxis general: grant Privilegio on Recurso to Usuarios [with grant option] Concede el privilegio Privilegio sobre el recurso Recurso a los usuarios Usuarios, permitiendo a su vez que los usuarios Usuarios puedan propagarlo a otros. En lugar de Privilegio se puede especificar all privileges. Ej: grant select on Distribución to UsuarioInvitado. grant Privilegio on Recurso to Usuarios [with grant option] Revocación

Sintaxis general: revoke Privilegio on Recurso from Usuarios [restrict|cascade] Privilegio puede ser cualquiera de los listados anteriormente más grant option. restrict|cascade se refieren a la revocación de los permisos propagados a otros usuarios. Papeles (Oracle) Un papel es una colección de permisos. Los papeles se asignan a usuarios. CREATE ROLE NombrePapel GRANT SELECT ON Distribución TO NombrePapel

5.5 SQL y el álgebra relacional - Unión T = R∪S INSERT INTO T (SELECT * FROM R) UNION (SELECT * FROM S); De otra forma: INSERT INTO T SELECT * FROM R; INSERT INTO T SELECT * FROM S;

5-18

Bases de datos y sistemas de información

- Diferencia de conjuntos T =R−S En primer lugar se hace la unión R ∪ S , y después: DELETE FROM T WHERE (A1,...,An) IN (SELECT * FROM S); - Intersección T = R ∩ S = R − (R − S ) - Producto cartesiano T = R1 × L × Rk INSERT INTO T SELECT * FROM R1,..., Rk; - Proyección T = π i1 ,K,ik ( R ) INSERT INTO T SELECT R.Ai1,..., R.Aik FROM R; - Selección T = σ θ ' ( R) INSERT INTO T SELECT * FROM R WHERE θ;

donde θ’ es una expresión equivalente a θ, en la que se sustituye los operadores lógicos (AND, ...) por los correspondientes del álgebra relacional ( ∧, K ).

- Reunión zeta (|>