Tema 4. SQL Juan Ignacio Rodr´ıguez de Leon ´ Resumen Este tema se centra exclusivamente en el estudio del lenguaje de consultas SQL (Structured Query Language). SQL usa una combinacion ´ de a´ lgebra relacional y construcciones del c´alculo relacional. Funciones de agregacion. ´ Valores nulos. Subconsultas anidadas. Relaciones derivadas. Vistas. Modificacion ´ de la base de datos. Reunion ´ de relaciones. Lenguaje de definicion ´ de datos. SQL incorporado. SQL din´amico. Otras caracter´ısticas de SQL

En este apartado se presenta una vision ´ general de SQL basada en la norma SQL-92 ampliamente implementada. Hay ser consciente de que algunos sistemas de bases de datos no soportan todas las caracter´ısticas de SQL-92 y de que muchas bases de datos proporcionan caracter´ısticas no est´andar que no se tratar´an aqu´ı. Los ejemplos de este cap´ıtulo y posteriores se basar´an en una empresa bancaria, con los siguientes esquemas de relacion: ´ EsquemaSucursal EsquemaCliente EsquemaPrestamo EsquemaPrestatario EsquemaCuenta EsquemaImpositor

1.

= = = = = =

(nombreSucursal, ciudadSucursal, activo) (nombreCliente, calleCliente, ciudadCliente) (numeroPrestamo, nombreSucursal, importe) (nombreCliente, numeroPrestamo) (numeroCuenta, nombreSucursal,saldo) (nombreCliente,numeroCuenta)

Estructura B´asica

La estructura b´asica de una expresion ´ SQL consiste en tres cl´ausulas: select, from y where. La cl´ausula select corresponde a la operacion ´ proyeccion ´ del a´ lgebra relacional (Π). Se usa para listar los atributos deseados como resultado de una consulta. La cl´ausula from corresponde a la operacion ´ producto cartesiano del a´ lgebra relacional (Z). Lista las relaciones que deben ser utilizadas en la consulta. La cl´ausula where corresponde al predicado seleccion ´ del a´ lgebra relacional (σ). 1

1

´ ESTRUCTURA BASICA

1.1.

2

La cl´ausula select

Una primera e importante diferencia con respecto al modelo relacional es que, por razones de rendimiento, las consultas SQL no eliminan los duplicados por defecto. Recuerdese que en el sisteme relacional, los tuplas duplicadas est´an prohibidas. Para que los duplicados se eliminen, tenemos que indicarlo de forma expl´ıcita, usando la palabra reservada distinct justo despues del select. Si deseamos obtener todos los atributos de una consulta, se puede usar el simbolo asterisco “*” como forma abreviada. Por ejemplo, la consulta: select * from Prestamo Nos devolver´a una relacion ´ con todos los atributos de Prestamo. La cl´ausula select puede contener expresiones aritm´eticas que contengan los operadores: + , − , ∗ y / operando sobre constantes o atributos.

1.2.

La cl´ausula where

Se pueden usar conectivas logicas, que en SQL se representan como and, ´ or y not, as´ı como los operaciones de comparacion ´ =, = y (,). Tambi´en dispone de un operador ternario, between, que permite comprpbar si un valor determinado esta dentro de un rango, por ejemplo: select numeroPrestamo from Prestamo where importe between 90000 and 120000 Tambi´en se puede usar not between para comprobar que el valor no caiga dentro del rango.

1.3.

la cl´ausula from

La cl´ausula from define un producto cartesiano. Para realizar reuniones naturales y externas existe ciertas extensiones que se tratar´an m´as adelante.

1.4.

La operacion ´ renombramiento

Se puede utilizar la palabra reservada as para renombrar atributos y expresiones (us´andola en la cl´ausula select) o relaciones (us´andola en la cl´ausula from). Es especialmente util ´ cuando aparacen dos o m´as veces la misma relacion ´ en una consulta.

2

OPERACIONES SOBRE CONJUNTOS

1.5.

3

operaciones sobre cadenas

Se utilizan las comillas simples para delimitar cadenas. Se puede concatenr cadenas, con el operador || (Dos barras verticales seguidas), se pueden comparar con los operadores =, y . Tambi´en se permite la extraccion ´ de partes de la cadena (substr), conversion ´ a mayusculas/min usculas, c´alculo de la longitud. ´ ´ Se define un tambi´en un nuevo opperador de comparacion, ´ llamadao like. Este operador sirve para comparar contra patrones de cadena. Para la descripcion ´ de los patrones se utilizan dos caracteres con significados especiales: Tanto por ciento ( %) Casa con cualquier subcadena Subrayado ( ) Casa con cualquier car´acter. Los patrones distinguen entre mayusculas y minusculas. Tambi´en se ´ ´ puede usar la expresion ´ not like para buscar cadenas de texto que no casen con el patron ´ indicado.

1.6.

Orden de presentacion ´ de las tuplas

SQL permite definir el orden en que deben aparecer las tuplas, usando la cl´ausula order by. Por omision, se usa el orden ascendente de los atributos listados en la cl´ausula. En caso de desear un orden descendente, se puede usar el modificador desc despues del atributo.

2.

Operaciones sobre conjuntos

En SQL se pueden usar las operaciones ∪, ∩ y −. Para ello se utilizan las palabras reservadas union, intersect y except, respectivamente. Al igual que en el a´ lgebra relacional, las relaciones deben tener la misma aridad y los dominios de los atributos deben ser compatibles. Estos operadores si eliminan duplicados por omision. ´ Si quieremos incluir los operadores, hay que definirlo explicitamente usando union all, intersect all o except all.

3.

Funciones de agregacion ´

Son equivalentes a lo explicado en el a´ lgebra relacional. Para las agrupaciones se usa la cl´ausula group by. Se pueden eliminar los duplicados antes de calcular una funcion ´ de agregacion ´ utilizando la palabra clave distinct en la expresion ´ de agregacion: ´

4

VALORES NULOS

4

select nombreSucursal, count (distinct nombreCliente) from impositor, cuenta where impositor.numeroCuenta = cuenta.numeroCuenta group by nombreSucursal Podemos realizar una segunda seleccion, ´ bas´andonos en los valores calculados mediante las funciones de agregacion, ´ utilizando la cl´ausula having. Los predicados de la cl´ausula having se aplican despues de realizarse las agrupaciones.

4.

valores nulos

Se usa la palabra reservada null para representar un valor nulo. Se sigue la misma logica para tratar los casos nulos que en el a´ lgebra relacional. La ´ unica excepcion es count(*), que si tendr´a en cuenta los valores nulos. El uso de nulos dentro de expresiones logicas puede conducir a resulta´ dos que no sea verdaderos ni falsos, sino desconocidos. Se pueden usar las cl´ausualas is unknown e is not unknown para tratar estas expresiones.

5.

subconsultas anidadas

Una subconsulta es una expresion ´ select-from-where que se anida dentro de otra consulta. Se suelen usar para comprobaciones sobre pertenencia a conjuntos, comparacion ´ de conjuntos y cardinalidad de conjuntos

5.1.

Pertenencia a conjuntos

Se usa la conectiva in para comprobar la pertenencia del valor de uno o m´as atributos de una relacion ´ a un conjunto, donde el conjunto es la consulta anidada. Se puede usar not in para comprobar la no pertenencia al conjunto.

5.2.

Comparacion ´ de conjuntos

Se pueden utilizar las expresiones some y all, conbinadas con los operadores de comparacion, ´ para comparar el valor de uno o m´as atributos con alguno o con todos los elementos de un conjunto, donde el conjunto es la consulta anidada. Por ejemplo, el comparador atributo > some(subconsulta) sera cierto si el valor del atributo comparado es mayor que al menos uno de los valores obtenidos de la subconsulta. el comparador atributo > all(subconsulta) sera cierto si el valor del atributo comparado es mayor que todos los valores obtenidos de la subconsulta. Existen los comparadores < some, some, >= some y some, as´ı como < all, all, >= all y all.

6

VISTAS

5.3.

5

Comparacion ´ de relaciones vacias

Se puede comprobar si una subconsulta no devuelve ningun ´ resultado, con el operador exists. Tambi´en se puede usar not exists para comprobar que el resultado no est´e vacio. En una subconsulta, solo ´ se pueden usar variables tupla que est´en definidas en la propia subconsulta (local) o en cualquier consulta que la contenga (global).

5.4.

Comprobacion ´ de tuplas duplicadas

Se puede comprobar si una subconsulta produce como resultado tuplas duplicadas. La constructora unique devuelve cierto si la subconsulta que se le pasa como argumento no produce tuplas duplicadas. Se puede utilizar tambi´en not unique.

6.

Vistas Se aplica todo lo explicado en el a´ lgebra relacional.

7.

Consultas complejas

Se estudiar´an dos formas de componer varios bloques SQL para expresar una consulta compleja: las relaciones derivadas y la cl´ausula with.

7.1.

Relaciones derivadas

SQL permite el uso de una expresion ´ de subconsulta en la cl´ausula from. Si se usa una expresion ´ de este tipo se debe dar un nombre a la relacion ´ resultado y se pueden renombrar los atributos usando la cl´ausula as.

7.2.

La cl´ausula with

La cl´ausula with proporciona una forma de definir una vista temporal cuya definicion ´ est´a disponible solo ´ para la consulta en la que aparece esta cl´ausula.

8.

Modificaciones de la base de datos

8.1.

Borrado

delete from r where p

9

´ DE RELACIONES REUNION

8.2.

6

Insercion ´

insert into r values (v1 , v2 , . . . , vn ) o, si no se conoce el orden en que se definieron los atributos, insert into r (a1 , a2 , . . . , an ) values (v1 , v2 , . . . , vn )

8.3.

Actualizaciones

update r set a1 = v1 , a2 = v2 , . . . , an = vn where p

8.4.

Actualizaciones de vistas

No se puede, a no ser que la definicion ´ de la lista compla la siguiente condicion: ´ Una modificacion ´ de una vista es v´alida solo ´ si vista en cuestion ´ se define en t´erminos de la base de datos relacional real –esto es, del nivel logico de la base de datos–, y sin usar agregacion. ´ ´

8.5.

Transacciones

Una transaccion ´ consiste en una secuencia de instrucciones de consulta y actualizaciones. La norma SQL especifica que una transaccion ´ comienza impl´ıcitamente cuando se ejecuta una instruccion ´ SQL. La transaccion ´ puede terminar de dos maneras: Commit work confirma o compromete la transaccion ´ actual; es decir, hace que los cambios realizados por la transaccion ´ sean permanentes en la base de datos. Rollback work causa el retroceso de la transaccion ´ actual; es decir, deshace todas las modificaciones realizadas por las instrucciones SQL de la transaccion. ´ El estado de la base de datos se restaura al que exist´ıa previo a la ejecucion ´ de la transaccion. ´ La palabra work es opcional en ambas instrucciones. El sistema de bases de datos garantiza que en el caso de una ca´ıda, los efectos de la transaccion ´ se retroceder´an si no se hubo ejecutado el commit. En caso de fallo de alimentacion ´ o ca´ıda del sistema, el retroceso ocurre cuando el sistema se reinicia.

9.

Reunion ´ de relaciones

SQL proporciona mecanismos para reunir relaciones, incluyendo reuniones condicionales, reuniones naturales, as´ı como varias formas de reunion ´ externa.

´ DE DATOS LENGUAJE DE DEFINICION

10

7

Con inner join obtenemos una reunion Θ. Los operadotes left outer join y right outer join nos dan una reunion ´ abierta por la izquierda y abierta por la derecha, respectivamente. El operador full outer join nos da una reunion ´ totalmente abierta. Finalmente, con el operador natural join obtenemos una reunion ´ natural.

10.

Lenguaje de definicion ´ de datos

El lenguaje de definicion de datos de SQl permite definir: El esquema de cada relacion ´ El dominio de valores asociado a cada atributo Las restricciones de integridad El conjunto de ´ındices que se deben mantener por cada relacion ´ Informacion ´ de seguridad y autorizacion ´ para cada relacion ´ La estructura de almacenamiento f´ısico de cada relacion ´ en disco

10.1.

Dominios es SQL

La norma SQL soporta un conjunto de tipos de dominios predefinidos, que incluye los siguientes: char (n) es una cadena de caracteres de longitud fija, con una longitud m´axima n especificada por el usuario. Tambi´en se puede utilizar la palabra completa character. varchar (n) es una cadena de caracteres de longitud variable, con una longitud m´axima n especificada el usuario. Tambi´en se puede utilizar la forma completa character varying. int es un entero (un subconjunto finito de los enteros, que es dependiente de la m´aquina). Tambi´en se puede usar la palabra completa integer. smallint es un entero pequeno ˜ (un subconjunto del dominio de los enteros, tambi´en dependiente de la m´aquina). numeric (p,d) es un numero en coma flotante, cuya precision ´ ´ la especifica el usuario. El numero est´a formado por p d´ıgitos (m´as el signo), y de ´ esos p d´ıgitos, d pertenecen a la parte decimal. real, double precision son respectivamente numeros en coma flotante y ´ numeros en coma flotante de doble precision, ´ ´ con precision ´ dependiente de la m´aquina.

10

´ DE DATOS LENGUAJE DE DEFINICION

8

float (n) es un numero en coma flotante, cuya precision ´ ´ es de al menos n d´ıgitos. date es una fecha del calendario, que contiene un ano ˜ (de cuatro d´ıgitos), un mes y un d´ıa del mes. time es la hora del d´ıa, expresada en horas, minutos y segundos. timestamp es una combinacion ´ de date y time.

10.2.

Definicion ´ de esquemas en SQL

Un esquema de relacion ´ se define utilizando la orden create table: create table r ( a1 D 1 , a2 D2 , ... , an Dn ´ integridad 1 > , < restriccion ´ integridad 2 > , < restriccion , ... ´ integridad n > , < restriccion ) donde r es el nombre de la relacion, ´ cada Ai es el nombre de un atributo del esquema de relacion ´ r y Di es el dominio de los valores del atributo ai . Las restricciones de integridad v´alidas incluyen: primary key (A1 , A2 , . . ., Am ) la especificacion ´ de clave primaria dice que los atributos A1 , A2 ,. . .,Am forman la clave primaria de la relacion. ´ Los atributos clave primaria deben ser no nulos y unicos. Aunque la ´ especificacion ´ de clave primaria es opcional, generalmente es buena idea especificar una clave primaria por cada relacion. ´ check (P) la cl´ausula check especifica un predicado P que debe satisfacer cada tupla de la relacion. ´ De manera predeterminada, null es un valor v´alido para cualquier atributo en SQL, a menos que se especifique con not null. SQL tambi´en soporta una restriccion ´ de integridad: unique (A1 , A2 , . . ., Am ) La especificacion ´ unique indica que los atributos A1 , A2 ,. . .,Am forman una clave candidata; es decir, no puede haber dos tuplas en la relacion ´ con todos los atributos A1 , A2 ,. . .,Am iguales.

11

SQL INCORPORADO

9

Un uso habitual de la cl´ausula check es el de asegurar que los valores de los atributos satisfacen unas condiciones Para borrar una relacion ´ de una base de datos SQL, se utiliza la orden drop table. Dicha orden borra de la base de datos toda la informacion ´ sobre la relacion ´ eliminada. La instruccion ´ drop table r tiene una repercusion ´ m´as dr´astica que delete from r porque la primera no solo ´ borra todas las tuplas de la relacion ´ r, sino que tambi´en borra el esquema de r. la instruccion atributos a una relacion ´ alter table se utiliza para anadir ˜ ´ existente. La sintaxis de la instruccion ´ es la siguiente: alter table r add D donde r es el nombre de una relacion ´ existente, A es el nombre del atributo que se desea anadir y D es el dominio del atributo A. ˜ Se pueden eliminar atributos de una relacion ´ utilizando la orden alter table r drop A

11.

SQL incorporado

La norma SQL define la utilizacion ´ de SQL dentro de varios lenguajes de programacion, ´ tales como C, Cobol, Pascal, Java, PL/I y Fortran. Un lenguaje en el cual se introducen consultas SQL se denomina lenguaje anfitri´on y las estructuras SQL que se admiten en el lenguaje anfitrion ´ constituyen SQL incorporado o embebido. Las instrucciones de SQL incorporado son similares en cuanto a la sintaxis a las instrucciones SQL que se han descrito en este cap´ıtulo. Sin embargo, hay varias diferencias que se indican a continuacion. ´ Para formular una consulta relacional se usa la instruccion ´ declare cursor. El resultado de la consulta no se calcula aun. ´ En lugar de esto, el programa debe usar las ordenes open y fetch para obtener las tuplas resultado ´