Lenguaje SQL (capítulo 4 del libro)

Lenguaje SQL (capítulo 4 del libro)

Previamente llamado Sequel, fue desarrollado por IBM a principios de 1970. Se ha vuelto el lenguaje estándar de bases de datos relacionales. En 1986 fue establecida como norma del ANSI (American

National Standards Institute) como SQL-86. Posteriormente fueron publicadas otras normas: SQL-86,

SQL-89, SQL-92, y la más reciente SQL:1999.

Las normas son de enorme importancia, ya que cada desarrollador de bases de datos incorporan funcionalidades y criterios que pueden diferir unos de otros. Para esta sección, se hará referencia a la norma SQL-92, la cual es la más utilizada.

El lenguaje SQL tiene varios componentes:

• Lenguaje de definición de datos (DDL), donde se proporcionan órdenes para la definición de esquemas de relación, borrado de relaciones, creación de índices y modificación de esquemas. • Lenguaje interactivo de manipulación de datos (DML), incluyendo instrucciones para

consultas, basado en álgebra relacional y el cálculo relacional de tuplas. Incluye órdenes para insertar, borrar y modificar tuplas de la base de datos.

• Definición de vistas, para la presentación ordenada de datos.

• Control de transacciones, para operaciones integradas como una sola operación. Si una sub-operación no se puede efectuar, no se efectúa nada del grupo.

• SQL incorporado y dinámico.

• Integridad, para restricciones de integridad que deberán satisfacer los datos almacenados. • Autorización, para especificar derechos de acceso para relaciones y vistas

Lenguaje de manipulación de datos (DML)

Una estructura básica de una expresión SQL consiste en select, from y where.

• select corresponde a la proyección de álgebra relacional, y se utiliza para listar los atributos deseados.

• from corresponde al producto cartesiano del álgebra relacional, incluyendo las relaciones (tablas) que deben ser consideradas.

• where corresponde a la operación de selección del álgebra relacional, asegurando la

select A1

, A

, …, An

from r1

, r

where P

Donde Ai representa un atributo, cada ir una relación y P un predicado. Equivale a:

Π A

, A

,…, A

(s

(r

) )

Si se omite la cláusula where, el predicado P es cierto para todas las tuplas.

Cláusula select

El lenguaje SQL, para efectos de rendimiento, no omite duplicados, aún cuando se supone que las relaciones son conjuntos.

Si se desea eliminar duplicados, se puede utilizar la clave distinct así:

select distinct nombre-sucursal

from prestamo

Lo opuesto, explicitamente sería:

select all nombre-sucursal

from prestamo

Para seleccionar (proyectar) todos los atributos, se puede utilizar el símbolo *:

especificar rangos, se puede utilizar el operador between:

select numero-prestamo

from prestamo

where importe between 90000 and 100000

Cláusula from

Esta cláusula define el producto cartesiano de las relaciones indicadas. Si hay dos o más relaciones implicadas, es necesario utilizar la cláusula select para hacer coincidir las claves primarias

correspondientes.

select nombre-cliente,

prestatario,numeroprestamo,importe

from prestatario,prestamo

where prestatario.numero-prestamo =

prestamo.numero-prestamo

Operación de renombramiento as

Se puede efectuar un renombramiento de atributos o de relaciones en una consulta:

nombre-antiguo as nombre-nuevo

Es posible que en el resultado de una consulta, dos atributos aparezcan con el mismo nombre. Por ejemplo, tablas diferentes, digamos peliculas y actores, pueden tener atributos con el mismo nombre

nombre. El resultado de la consulta podría resultar confuso.

select peliculas.nombre as titulo,actores.nombre as

actor

from peliculas,actores

También se aplica a las relaciones:

El renombramiento también sirve para comparar tuplas de la misma relación:

select distinct T.nombre-sucursal

from sucursal as T, sucursal as S

where T.activo > S.activo and

S.ciudad-sucursal=’Barcelona’

Operaciones sobre cadenas

Para comparar cadenas usando patrones, se utiliza el operador like. Para la descripción de patrones se utiliza ‘%’ (cualquier subcadena) y ‘_’ (cualquier carácter). Los patrones son sensibles a mayúsculas y minúsculas.

select nombre

from ciudades

where nombre like ‘%tepec’

El carácter de escape para permitir la especificación de caracteres especiales es la diagonal invertida ‘\’. También permite expresiones regulares tipo Unix mediante similar to en lugar de like.

Orden de presentación de tuplas order by, asc, desc

Se puede obtener el resultado de la consulta ordenada de acuerdo a algún atributo:

select nombre,distrito

from ciudades

where estado =’Oaxaca’

order by distrito asc, nombre desc

Operaciones sobre conjuntos

Se pueden realizar operaciones de conjuntos (unión, diferencia, intersección) sobre los resultados de consultas, construyendo operaciones más grandes:

(select nombre

union

(select nombre

from empleados)

En el caso de operaciones sobre conjuntos, los duplicados SÍ se suprimen, a menos que se especifique

union all.

De la misma manera se cuenta con los operadores intersect (aquellos que aparecen en ambos

conjuntos) y except (los que sí están en uno pero no en el otro, equivalente a diferencia de conjuntos).

Funciones de agregación

Existen funciones que toman una colección de valores como entrada y producen un único valor de salida. Las funciones primitivas son:

• avg: media • min: mínimo • max: máximo • sum: total • count: cuenta

Las entradas para las funciones sum y avg deben ser numéricas.

select avg (saldo)

from cuenta

where nombre-sucursal =’Huajuapan’

Si se desea aplicar la función de agregación por grupos, se puede realizar el agrupamiento así:

select nombre-sucursal,avg (saldo)

from cuenta

group by nombre-sucursal

En ocasiones hay que eliminar duplicados antes de aplicar la función de agregación:

from impositor,cuenta

where impositor.numero-cuenta =

cuenta.numero-cuenta

group by nombre-sucursal

En ocasiones no se desea mostrar todos los grupos, sino solo aquellos que cumplan alguna condición. Por ejemplo, si se desea mostrar solo las sucursales cuyo saldo promedio es mayor a 1200, se puede utilizar la instrucción having como en el siguiente ejemplo:

select nombre-sucursal,avg (saldo)

from cuenta

group by nombre-sucursal

having avg (saldo)>1200

Si se desea considerar la relación entera como un grupo, basta una consulta com la siguiente:

select avg (saldo)

from cuenta

Para evitar confusiones, es muy importante entender el siguiente párrafo:

Si en la misma consulta aparece la cláusula where y having, se aplica primero la cláusula where. Las tuplas que satisfagan el where se agruparán de acuerdo a group by. Si existe cláusula having se aplica a cada grupo, eliminando las que no cumplan. Finalmente, la cláusula select utiliza los grupos que restan para generar las tuplas que resultan de la consulta.

Valores nulos

Los valores nulos son importantes y se debe tomar en cuenta la manera en que se tratan. Básicamente, el valor nulo significa “ausencia de información” sobre el valor de un atributo. Así que equivale a decir “desconocido”. Se puede verificar dicha condición (valor) con una consulta así:

select número-préstamo

from préstamo

También se puede consultar lo opuesto: where importe is not null.

En las operaciones aritméticas, si un valor de entrada es nulo, la salida será nula. En las operaciones lógicas se aplican ciertas reglas para interpretar el resultado. En las funciones de agregación, se suelen ignorar los nulos.

Subconsultas anidadas

Se pueden realizar consultas donde se busca la pertenencia o no pertenencia de un atributo al resultado de otra consulta:

select distinct nombre-cliente

from prestatario

where nombre-cliente in

(select nombre-cliente from impositor)

En el ejemplo anterior se debe producir tuplas con un único atributo, cuyo dominio debe coincidir con el dominio buscando en el where. Sin embargo también se puede buscar la coincidencia en más de un atributo:

select distinct nombre-cliente

from prestatario

where (nombre-sucursal,nombre-cliente) in

(select nombre-sucursal,nombre-cliente

from impositor

where)

También se puede usar in y not in sobre conjuntos enumerados, como en el siguiente ejemplo:

select distinct nombre-cliente

from prestatario

where nombre-cliente not in

Vistas

Las consultas más utilizadas se pueden integrar en vistas de SQL, las cuales pueden ser tratadas como tablas, pero dependen de la consulta que la define.

create view v as <consulta>

Se puede, así, crear una vista con todos los empleados y clientes de una empresa así:

create view todos as

(select nombre,dirección

from empleados)

union

(select nombre,dirección

from clientes)

También se pueden definir los nombres de los atributos de la vista:

create view riquillos (nombre,riqueza) as

select nombre-cliente,saldo from cuentas

where saldo>500000

Las vistas se pueden utilizar como cualquier tabla, únicamente para consultas. Hay casos en los que las actualizaciones de datos mediante vistas son posibles, pero se aplican reglas muy estrictas. Es

preferible evitarlas.

Modificación de la base de datos

Por supuesto, la base de datos debe estar en constante cambio para reflejar los cambios de aquello que pretenden modelar.

El borrado de tuplas se efectúa con el comando delete:

delete from r

where P

delete from cuentas

where nombre-sucursal=’Huajuapan’

Para borrar todas las tuplas de una tabla:

delete from cuentas

Nótese que se borran las tuplas pero no la definición de la relación.

Para insertar datos en una relación se especifica la tupla a insertar, o se genera una consulta cuyo resultado sea el conjunto de tuplas deseado para insertar.

insert into clientes

values (‘Juan Pérez’, ‘Nuyoo 15’)

También se pueden especificar los atributos, por si el orden no es el mismo, o si se omitirán valores (serán nulos):

insert into clientes (nombre)

values (‘Juan Pérez’)

También se pueden insertar datos provenientes de otra tabla:

insert into clientes

select nombre,dirección

from empleados

where puesto=”cajero”

Las actualizaciones se realizan para una tupla, varias tuplas, o todas las tuplas:

update cuentas

set saldo=saldo*1.05

La anterior, se aplica para todas. Para algunas:

update cuentas

where saldo<1000

Existen más construcciones por casos con el comando case.

Transacciones

Una transacción es una secuencia de consultas y actualizaciones. El propósito de una transacción es tener la posibilidad de deshacer las operaciones anteriores si una operación falla o da como resultado una condición conocida. Las transacciones se inician con:

begin transaction

Y se finalizan con:

commit transaction

Si por programación se detectara algún fallo o condición particular, la transacción se cancela con:

rollback transaction

Lenguaje de definición de datos (DDL)

El DDL de SQL permite especificar relaciones e información relativa a esas relaciones: • El esquema de cada relación.

• El dominio de valores de cada atributos. • Restricciones de integridad.

• Conjunto de índices necesarios para cada relación.

• Información de seguridad y autorización para cada relación. • La estructura de almacenamiento físico de cada relación en disco.

Tipos de dominio

La norma SQL soporta un conjunto de tipos de dominios predefinidos:

• char (n), cadena de caracteres de longitud fija especificada por el diseñador.

• int, entero.

• smallint, entero pequeño.

• numeric, números de punto flotante.

• real, double precision, números de punto flotante de doble precisión. • float (n) número de punto flotante, con precisión de al menos n dígitos. • date, fecha del calendario, con año de cuatro dígitos, mes y día de mes. • time, hora del día, expresado en horas, minutos y segundos.

• timestamp, combinación de date y time.

Definición de esquemas en SQL

Un esquema de relación se especifica así:

create table r (A1

D

,A

D

, … A

,

<restricción-integridad

>

…

<restricción-integridad

>)

Donde r es el nombre de la relación, cada Ai es el nombre de un atributo y Di es el dominio de los

valores del atributo Ai.

Las restricciones de integridad incluyen:

• primary key (Aj1, Aj2,… ,Ajn): Establece que los atributos indicados forman la clave primaria de

la relación. Deben ser únicos y no-nulos.

• check (P): Esta cláusula especifica un predicado P que debe satisfacer cada tupla de la relación. Hay otras posibles restricciones de integridad, pero no se especifican aquí.

A continuación un ejemplo de definición de una base de datos:

create table cliente

(nombre-cliente

char (20),

calle-cliente

char (30),

primary key (nombre-cliente))

create table sucursal

(nombre-sucursal char (15),

ciudad-sucursal

char (30),

activo

integer,

primary key (nombre-sucursal)

check (activo>=0))

create table cuenta

(número-cuenta

char (10),

nombre-sucursal

char (15),

saldo

integer,

primary key (número-cuenta),

check (saldo>=0)

create table impositor

(nombre-cliente

char (20),

número-cuenta

char (10),

primary key (nombre-cliente,número-cuenta))

Si durante una inserción o modificación se repite un valor o se introduce un valor nulo para una clave primaria, la base de datos notifica el error y la actualización no se realiza.

También se puede establecer, como restricción de integridad, un conjunto de atributos como clave candidata, es decir, en conjunto no deben repetirse:

unique (A

)

El comando check puede restringir un dominio a una lista enumerada, así:

check (nivel-estudios in

(‘Graduado’,’Licenciado’,’Doctorado’) )

Para borrar la definición de una relación se utiliza el comando

drop table r

Para añadir atributos a una relación existente se utiliza

alter table r add A D

mientras que para eliminar atributos se utiliza

alter table r drop A

SQL incorporado

Existe un sinfin de lenguajes de programación que incorporan los elementos necesarios para acceder a bases de datos. Siendo las aplicaciones web unas de las más difundidas, aquí se mostrará la

codificación necesaria para un acceso básico a consultas SQL, por medio del lenguaje PHP, aunque es importante aclarar que hay muchas otras maneras de hacer lo mismo.

Los requisitos para hacer funcionar el ejemplo básico son: • Un servidor Web con soporte a:

◦ PHP

◦ SQLite

echo "<html><body><h1>Consultas</h1>"; try {

$dbruta="./_DB"; $dbf="chinok.db";

$db = new PDO( "sqlite:$dbruta/$dbf" ); $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql="select * from profesores;"; $sth = $db->prepare( $sql );

$sth->execute();

$result = $sth->fetchAll(); } catch( Exception $e ){

echo "<h3>Error en la consulta: " . $e->getMessage() . "</h3>";

}

//si hay resultados, procesar if( count( $result ) == 0 )

echo "<h3>[No hay resultados]</h3>"; else

{

echo "<table class='sql'>";

$paridad=0; //En caso de utilizar estilos para

distinguir los renglones

for( $i=0; $i<count( $result ); $i++ ) {

echo "<tr>"; $j=0;

//ciclo para $ncols

while( isset( $result[$i][$j] ) ) {

echo "<td class='sql td" . $paridad."'>" . $result[$i][$j] . "</td>";

echo "</tr>\n";

$paridad = $paridad==0?1:0; }

echo "</table>";

}//cierre del desplegar resultados de consulta, si hubo echo "</body></html>";

?>