PDF de programación - Programación en SQL con PostgreSQL

Programación en SQL con PostgreSQL

Francisco Alonso Sarría

1

Introducción

El lenguaje estructurado de consultas (SQL) es un lenguaje de base de datos normalizado, utilizado por la gran
mayoría de los servidores de bases de datos que manejan bases de datos relacionales u objeto-relacionales.
Es un lenguaje declarativo en el que las órdenes especifican cual debe ser el resultado y no la manera de
conseguirlo (como ocurre en los lenguajes procedimentales). Al ser declarativo es muy sistemático, sencillo
y con una curva de aprendizaje muy agradable ya que sus palabras clave permiten escribir las ordenes como si
fueran frases en las que se especifica (en inglés) que es lo que queremos obtener. Por ejemplo:
SELECT nombre FROM municipios WHERE poblacion>5000 ORDER BY poblacion;
Devuelve el nombre de aquellos municipios con una población mayor de 5000 habitantes y los presenta orde-
nados por tamaño. Sin embargo los lenguajes declarativos carecen de la potencia de los procedimentales
Se ha convertido, debido a su eficiencia, en un estandar para las bases de datos relacionales, de hecho el gran
éxito del modelo de base de datos relacional se debe en parte a la utilización de un lenguaje como SQL. A pesar
de su tesórico caracter estandar, se han desarrollado, sobre una base común, diversas versiones ampliadas como
las de Oracle o la de Microsoft SQL server. Incluye diversos tipos de capacidades:

• Comandos para la definición y creación de una base de datos (create table).
• Comandos para inserción, borrado o modificación de datos (insert, delete, update).
• Comandos para la consulta de datos seleccionados de acuerdo a criterios complejos que involucran

diversas tablas relacionadas por un campo común (select).

• Capacidades aritméticas: En SQL es posible incluir operaciones aritméticas así como comparaciones, por

ejemplo A > B + 3.

• Asignación y comandos de impresión: es posible imprimir una tabla construida por una consulta o

almacenarla como una nueva tabla.

1

• Funciones de agregación: Operaciones tales como promedio (average), suma (sum), máximo (max), etc.
se pueden aplicar a las columnas de una tabla para obtener una cantidad única y, a su vez, incluirla en
consultas más complejas.

En una base de datos relacional, los resultados de la consulta van a ser datos individuales, tuplas1 o tablas
generados a partir de consultas en las que se establecen una serie de condiciones basadas en valores numéricos.
Por ejemplo una típica consulta sobre una tabla en una base de datos relacional, utilizando SQL podría ser:
bd=# SELECT id, nombre, pob1991
FROM municipios
WHERE pob1991>20000;2
el resultado será una tabla en la que tendremos tres columnas (id, nombre, poblacion) procedentes de la tabla
municipios, las filas corresponderán sólo a aquellos casos en los que la poblacion en 1991 (columna pob1991)
sea mayor que 20000. En el caso de que sólo uno de los municipios cumpliera la condición obtendríamos una
sola fila y en caso de que la consulta fuera:
bd=# SELECT pob1991
FROM municipios
WHERE pob1991>20000;

obtendríamos un sólo número, la población del municipio más poblado.

1.1 Componentes del SQL

El lenguaje SQL está compuesto por comandos, cláusulas, operadores y funciones de agregado. Estos elementos
se combinan en las instrucciones para crear, actualizar y manipular las bases de datos.

1.2 Comandos

Existen dos tipos de comandos SQL:

• Los que permiten crear y definir nuevas bases de datos, campos e índices.

CREATE Utilizado para crear nuevas tablas, campos e índices
DROP Empleado para eliminar tablas e índices

1equivalente a una fila de una tabla
2A partir de este momento, cuando escriba una sentencia SQL lo haré en negrita, utilizando el prompt de PostgreSQL que consiste
en el nombre de la base de datos (si no se especifica ninguna utilizare de forma genérica bd) seguido de =#, y con los diferentes
elementos de la consulta separados por lineas. Esto último facilita la interpretación de la orden, pero recuerda que a la hora de trabajar
es preferible escribir toda la orden en una sola linea

2

ALTER Utilizado para modificar las tablas agregando campos o cambiando la definición de los campos.

• Los que permiten generar consultas para ordenar, filtrar y extraer datos de la base de datos.

SELECT Utilizado para consultar registros de la base de datos que satisfagan un criterio determinado
INSERT Utilizado para cargar lotes de datos en la base de datos en una única operación.
UPDATE Utilizado para modificar los valores de los campos y registros especificados
DELETE Utilizado para eliminar registros de una tabla de una base de datos

1.3 Cláusulas

Las cláusulas son condiciones utilizadas para concretar que datos son los que se desea seleccionar o manipular.

FROM Utilizada para especificar la tabla de la cual se van a seleccionar los registros

WHERE Utilizada para especificar las condiciones que deben reunir los registros que se van a seleccionar

GROUP BY Utilizada para clasificar los registros seleccionados en grupos específicos

HAVING Utilizada para expresar la condición que debe satisfacer cada grupo

ORDER BY Utilizada para ordenar los registros seleccionados de acuerdo con un orden específico

1.4 Operadores Lógicos

AND Evalua dos condiciones y devuelve un valor de verdad sólo si ambas son ciertas.

OR Evalúa dos condiciones y devuelve un valor de verdar si alguna de las dos es cierta.

NOT Devuelve el valor contrario de la expresión.

1.5 Operadores de Comparación

< Menor que

> Mayor que

<> Distinto de

<= Menor ó Igual que

>= Mayor ó Igual que

3

= Igual que

BETWEEN Utilizado para especificar un intervalo de valores.

o LIKE Para la comparación de una cadena de texto con una expresión regular

1.6 Funciones de Agregación

Las funciones de agregación se usan dentro de una cláusula SELECT en grupos de registros para devolver un
único valor que se aplica a un grupo de registros.

AVG Utilizada para calcular el promedio de los valores de un campo determinado

COUNT Utilizada para devolver el número de registros de la selección

SUM Utilizada para devolver la suma de todos los valores de un campo determinado

MAX Utilizada para devolver el valor más alto de un campo especificado

MIN Utilizada para devolver el valor más bajo de un campo especificado

2 Bases de datos relacionales

Es el modelo más utilizado hoy en día. Una base de datos relacional es básicamente un conjunto de tablas,
similares a las tablas de una hoja de cálculo, formadas por filas (registros) y columnas (campos). Los registros
representan cada uno de los objetos descritos en la tabla y los campos los atributos (variables de cualquier tipo)
de los objetos. En el modelo relacional de base de datos, las tablas comparten algún campo entre ellas. Estos
campos compartidos van a servir para establecer relaciones entre las tablas que permitan consultas complejas
(figura 1). En esta figura aparecen tres tablas con información municipal, en la primera aparecen los nombres
de los municipios, en la segunda el porcentaje en cada municipio de los diferentes usos del suelo y en la
tercera la población en cada municipio lo largo del siglo XX. Como campo común aparece ident, se trata de un
identificador numérico, único para cada municipio3
La idea básica de las bases de datos relacionales es la existencia de entidades (filas en una tabla) caracterizadas
por atributos (columnas en la tabla). Cada tabla almacena entidades del mismo tipo y entre entidades de distinto
tipo se establecen relaciones4. Las tablas comparten algún campo entre ellas, estos campos compartidos van a
servir para establecer relaciones entre las tablas. Los atributos pueden ser de unos pocos tipos simples:

3Es preferible utilizar valores numéricos en lugar de una cadena de caracteres ya que se ahorra espacio y se evitan problemas con el

uso de mayúsculas, acentos, etc.

4En la bibliografía inglesa sobre bases de datos se habla de relations (tablas) y relationships relaciones entre las tablas. El término

base de datos relacional hace en realidad referencia a la organización de los datos en forma de tablas, no a las relaciones entre ellas

4

Figure 1: Esquema de base de datos relacional

• Números enteros
• Números reales
• Cadena de caracteres de longitud variable

Estos tipos simples se denominan tipos atómicos y permiten una mayor eficacia en el manejo de la base de datos
pero a costa de reducir la flexibilidad a la hora de manejar los elementos complejos del mundo real y dificultar
la gestión de datos espaciales, en general suponen un problema para cualquier tipo de datos geométricos.
Las relaciones que se establecen entre los diferentes elementos de dos tablas en una base de datos relacional
pueden ser de tres tipos distintos:

• Relaciones uno a uno, se establecen entre una entidad de una tabla y otra entidad de otra tabla. Un

ejemplo aparece en la figura 1.

• Relaciones uno a varios, se establecen entre varias entidades de una tabla y una entidad de otra tabla.
Un ejemplo sería una tabla de pluviómetros en la que se indicara el municipio en el que se encuentra. La
relación sería entre un municipio y varios pluviómetros

• Relaciones varios a varios, se establecen entre varias entidades de cada una de las tablas. Un ejemplo
sería una tabla con retenes de bomberos y otra con espacios naturales a los que cada uno debe acudir en
caso de incendio.

5

3 Entrada en el cliente y exploración de la base de datos

La gestión de bases de datos se basa en la existencia de un programa servidor; que organiza los datos, recibe
las consultas, las ejecuta y las devuelve; y un programa cliente que el usuario ejecuta y que lanza las consultas
creadas por este al servidor. El programa cliente y el servidor no tienen siquiera porque ejecutarse en el mismo
ordenador.
Existen diferentes clientes para conectar al servidor de bases de datos de PostgreSQL. Vamos a utilizar en
principio uno sencillo (psql). Si tecleamos:
psql -l
obtendremos un listado de todas la bases de datos disponibles para el servidor. Si que