SQL: Introducción al SQL. Instalación de PostgreSQL

    Este cursillo de SQL se hará en tres parte, en la primera nos centraremos en la generalidad del SQL, trataremos con postgreSQL que es un servidor de bases de datos vía SQL de dominio público. La segunda parte estudiaremos los comandos SQL con un mayor detalle. En la tercera parte trataremos comando SQL con opciones avanzadas así como funciones del propio gestor PostgreSQL que pueden ser interesantes para nuestros desarrollos y haremos un pequeño programa en C.

Introducción

Hasta el momento, el acceso a los datos se hacía mediante accesos a entidades que se relacionaban entre sí mediante una ligaduras definidas en el esquema de la base de datos, eso tenía una ventaja, rapidez, pero una gran desventaja, sólo podíamos acceder a los datos mediante una ligadura, ejemplo :

    país -> provincias -> municipios

    país -> municipios

Si queríamos realizar esa segunda relación, debíamos redefinir el esquema y recompilarlo...

En efecto, en una BD jerarquica, la relación entre las diversas entidades es estática y solo modificable mediante modificación del esquema de la base de datos y recompilacion de este ultimo.

La idea básica de los gestores de bases de datos relacionales es justamente ligar los datos en el momento de la petición de estos, pero sin necesitar una ligadura estática, sino una identificación que permita ligar un registro con otro.

Esto que acabo de escribir necesita una Aspirina :-)

Los gestores de base de datos relacionales no precisan unas ligaduras estáticas para poder descender una jerarquia de entidades, sino que usan un código único que les identifica para realizar una relación temporanea que es el resultado de una pregunta al gestor.

Esta identificación no es más que el código. Ej: mi número de telefono no es el :

1234567

sino el :

34 6 1234567

En efecto mi numero de telefono esta identificado por el código país (34), el código de la provincia (6) y el propio número de aparato (1234567).

• En la entidad paises, el código 34 (España) es único.
• En la entidad provincias, el código 34-6 (España/Valencia) es único.
• En la entidad aparatos, el código 34-6-1234567 (España/Valencia/mi telefono) es único.

Vamos a poner las bases del primer ejemplo que ilustrara lo que acabo de decir.

    Todos los municipios tienen un código, pertenecen a una provincia y a un país
    Todas las provincias tienen un código y pertenecen a un país
    Todos los países tienen un código

Para conocer todos los municipios de una provincia, relaciono el municipio con la provincia por el código de país y provincia; para saber todos los municipio de un país, relaciono el municipio con el país por el código de país. Estas relaciones son temporáneas y sólo existen durante la realización de mi pregunta.

Es un poco duro, pero con los primeros ejemplos comprenderemos un poco mejor este concepto de código y de pertenencia.

Al realizar mi pregunta el gestor me entregara todos los datos que se relacionen entre sí. Pero ¿qué datos me va a dar? Pues la conjunción de los datos de países y municipios, para cada municipio me repetirá los datos del país.

Durante la realización de mi pregunta se ha creado un nueva entidad que no tiene nombre y que contiene una réplica de países y municipios. Esa nueva entidad, y me repito, desaparecerá una vez terminada mi lectura.

Antes llamábamos a los conjuntos de datos, ficheros. Estos se componen de registros y estos últimos se componen de campos. Bien, pues en una base de datos relacional, un "fichero" se llama tabla, una tabla se compone de tuplas y una tupla contiene columnas, no es más que un matiz... ;-)

Hay que destacar que ciertos gestores de BD jerárquicos introducían SQL como lenguaje de acceso, pero esto es anecdótico. El lenguaje SQL es casi una exclusividad de los gestores relacionales.

Para ilustrar el curso utilizaremos el gestor relacional PostgreSQL, aunque no cumple con todas las normas SQL, sí que es más que suficiente para nosotros, y para otros menesteres más duros también.

Voy a explicar muy brevemente el proceso de instalación, dado que el objetivo de este artículo es SQL. Primero bajamos los fuentes de www.postgresql.org, así como los parches. Los extraemos (tar zxvf) en un directorio, cd postgresql-6.3

cd src
./configure --prefix=/el/path/deseado
make all >& make.log &
tail -f make.log

export PATH=$PATH:/el/path/deseado/pgsql/bin
export MANPATH=$MANPATH:/el/path/deseado/pgsql/man
export PGLIB=/el/path/deseado/pgsql/lib
export PGDATA=/el/path/deseado/pgsql/data

initdb
createdb prueba
psql prueba
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: postgres

prueba=>

prueba=>create table prueba (campo1 varchar(10));
CREATE

prueba=>insert into prueba values ('hello');
INSERT numerito 1

prueba=>commit work;
NOTICE:EndTransactionBlock and not inprogress/abort state
END

prueba=>select * from prueba;
campo1
------
hello
(1 row)

prueba=>drop table prueba;
DROP

prueba=>Ctrl-d

Si no habéis conseguido compilar e instalar Postgres95 correctamente, referiros al fichero INSTALL que está en el directorio de entrada de la distribución.

Como comentario, vamos a ver como esta construido un servidor de bases de datos relacional :

1. La capa de acceso a los datos
2. La capa gestora SQL
3. La capa traductora SQL
4. La capa de comunicaciones

Primer Paso

Fichero: paises.sql
create table paises (cod_pais integer, nombre varchar(30));
insert into paises values (1, 'pais 1');
insert into paises values (2, 'pais 2');
insert into paises values (3, 'pais 3');
commit work;

Fichero: provincias.sql
create table provincias (cod_provincia int, 
			cod_pais int, 
			nom_provincia varchar(30));
insert into provincias values (1, 1, 'Provincia 1, Pais 1');
insert into provincias values (2, 1, 'Provincia 2, Pais 1');
insert into provincias values (1, 2, 'Provincia 1, Pais 2');
insert into provincias values (2, 2, 'Provincia 2, Pais 2');
insert into provincias values (1, 3, 'Provincia 1, Pais 3');
insert into provincias values (2, 3, 'Provincia 2, Pais 3');
commit work;

Fichero: municipios.sql
create table municipios (cod_pais int, 
			cod_provincia int, 
			cod_municipio int,
			nombre_municipio varchar(60));
insert into municipios values (1, 1, 1, 'Municipio 1, Provincia 1, Pais 1');
insert into municipios values (2, 1, 1, 'Municipio 2, Provincia 1, Pais 1');
insert into municipios values (3, 1, 1, 'Municipio 3, Provincia 1, Pais 1');
insert into municipios values (1, 2, 1, 'Municipio 1, Provincia 2, Pais 1');
insert into municipios values (2, 2, 1, 'Municipio 2, Provincia 2, Pais 1');
insert into municipios values (3, 2, 1, 'Municipio 3, Provincia 2, Pais 1');
insert into municipios values (1, 3, 1, 'Municipio 1, Provincia 3, Pais 1');
insert into municipios values (2, 3, 1, 'Municipio 2, Provincia 3, Pais 1');
insert into municipios values (3, 3, 1, 'Municipio 3, Provincia 3, Pais 1');
insert into municipios values (1, 1, 2, 'Municipio 1, Provincia 1, Pais 2');
insert into municipios values (2, 1, 2, 'Municipio 2, Provincia 1, Pais 2');
insert into municipios values (3, 1, 2, 'Municipio 3, Provincia 1, Pais 2');
insert into municipios values (1, 2, 2, 'Municipio 1, Provincia 2, Pais 2');
insert into municipios values (2, 2, 2, 'Municipio 2, Provincia 2, Pais 2');
insert into municipios values (3, 2, 2, 'Municipio 3, Provincia 2, Pais 2');
insert into municipios values (1, 3, 2, 'Municipio 1, Provincia 3, Pais 2');
insert into municipios values (2, 3, 2, 'Municipio 2, Provincia 3, Pais 2');
insert into municipios values (3, 3, 2, 'Municipio 3, Provincia 3, Pais 2');
insert into municipios values (1, 1, 3, 'Municipio 1, Provincia 1, Pais 3');
insert into municipios values (2, 1, 3, 'Municipio 2, Provincia 1, Pais 3');
insert into municipios values (3, 1, 3, 'Municipio 3, Provincia 1, Pais 3');
insert into municipios values (1, 2, 3, 'Municipio 1, Provincia 2, Pais 3');
insert into municipios values (2, 2, 3, 'Municipio 2, Provincia 2, Pais 3');
insert into municipios values (3, 2, 3, 'Municipio 3, Provincia 2, Pais 3');
insert into municipios values (1, 3, 3, 'Municipio 1, Provincia 3, Pais 3');
insert into municipios values (2, 3, 3, 'Municipio 2, Provincia 3, Pais 3');
insert into municipios values (3, 3, 3, 'Municipio 3, Provincia 3, Pais 3');
commit work;

Para ejecutar dentro del psql unos comandos sql de un fichero, hacer :

\i nombre_del_fichero

Tambén podemos hacer un cortar y pegar desde estas páginas.

Vamos a ver qué municipios tenemos :

select * from municipios;

cod_pais|cod_provincia|cod_municipio|nombre_municpio
--------+-------------+-------------+--------------------------------
       1|            1|            1|Municipio 1, Provincia 1, Pais 1
       2|            1|            1|Municipio 2, Provincia 1, Pais 1
       3|            1|            1|Municipio 3, Provincia 1, Pais 1
       1|            2|            1|Municipio 1, Provincia 2, Pais 1
       2|            2|            1|Municipio 2, Provincia 2, Pais 1
       3|            2|            1|Municipio 3, Provincia 2, Pais 1
       1|            3|            1|Municipio 1, Provincia 3, Pais 1
       2|            3|            1|Municipio 2, Provincia 3, Pais 1
       3|            3|            1|Municipio 3, Provincia 3, Pais 1
       1|            1|            2|Municipio 1, Provincia 1, Pais 2
       2|            1|            2|Municipio 2, Provincia 1, Pais 2
       3|            1|            2|Municipio 3, Provincia 1, Pais 2
       1|            2|            2|Municipio 1, Provincia 2, Pais 2
       2|            2|            2|Municipio 2, Provincia 2, Pais 2
       3|            2|            2|Municipio 3, Provincia 2, Pais 2
       1|            3|            2|Municipio 1, Provincia 3, Pais 2
       2|            3|            2|Municipio 2, Provincia 3, Pais 2
       3|            3|            2|Municipio 3, Provincia 3, Pais 2
       1|            1|            3|Municipio 1, Provincia 1, Pais 3
       2|            1|            3|Municipio 2, Provincia 1, Pais 3
       3|            1|            3|Municipio 3, Provincia 1, Pais 3
       1|            2|            3|Municipio 1, Provincia 2, Pais 3
       2|            2|            3|Municipio 2, Provincia 2, Pais 3
       3|            2|            3|Municipio 3, Provincia 2, Pais 3
       1|            3|            3|Municipio 1, Provincia 3, Pais 3
       2|            3|            3|Municipio 2, Provincia 3, Pais 3
       3|            3|            3|Municipio 3, Provincia 3, Pais 3
(27 rows)

prueba=>

select * from paises, municipios

cod_pais|nombre|cod_provincia|cod_pais|nom_provincia
--------+------+-------------+--------+-------------------
       1|pais 1|            1|       1|Provincia 1, Pais 1
       2|pais 2|            1|       1|Provincia 1, Pais 1
       3|pais 3|            1|       1|Provincia 1, Pais 1
       1|pais 1|            2|       1|Provincia 2, Pais 1
       2|pais 2|            2|       1|Provincia 2, Pais 1
       3|pais 3|            2|       1|Provincia 2, Pais 1
       1|pais 1|            1|       2|Provincia 1, Pais 2
       2|pais 2|            1|       2|Provincia 1, Pais 2
       3|pais 3|            1|       2|Provincia 1, Pais 2
       1|pais 1|            2|       2|Provincia 2, Pais 2
       2|pais 2|            2|       2|Provincia 2, Pais 2
       3|pais 3|            2|       2|Provincia 2, Pais 2
       1|pais 1|            1|       3|Provincia 1, Pais 3
       2|pais 2|            1|       3|Provincia 1, Pais 3
       3|pais 3|            1|       3|Provincia 1, Pais 3
       1|pais 1|            2|       3|Provincia 2, Pais 3
       2|pais 2|            2|       3|Provincia 2, Pais 3
       3|pais 3|            2|       3|Provincia 2, Pais 3
(18 rows)

En este último comando hemos, realizado una unión de dos tablas, hemos relacionado la tabla de países con la de municipios, como no le hemos dado ninguna regla de unión, nos ha devuelto TODAS las tuplas de países relacionadas con TODAS las tuplas de provincias, es decir 3 tuplas de países por 6 de provincias total 18 tuplas, este resultado es totalmente ilógico e inútil, ahora mejor hacer:

select * from paises, provincias
where paises.cod_pais = provincias.cod_pais;

cod_pais|nombre|cod_provincia|cod_pais|nom_provincia
--------+------+-------------+--------+-------------------
       1|pais 1|            1|       1|Provincia 1, Pais 1
       1|pais 1|            2|       1|Provincia 2, Pais 1
       2|pais 2|            1|       2|Provincia 1, Pais 2
       2|pais 2|            2|       2|Provincia 2, Pais 2
       3|pais 3|            1|       3|Provincia 1, Pais 3
       3|pais 3|            2|       3|Provincia 2, Pais 3
(6 rows)

Si tenemos seis municipios y cada municipio está en un país. Es normal que me dé un número de tuplas igual al de municipios, dado que países es un calificativo de municipios. Acabamos de relacionar la tabla de países con la tabla de provincias mediante el código de país. Recordemos que países tiene código y que provincias tiene el código país al que pertenece.

¿Porqué paises.cod_pais = provincias.cod_pais ?

Código de país en la tabla de países se llama cod_pais y en la tabla de provincias también, entonces:

cod_pais = cod_pais

select * from paises, provincias
		where cod_pais = cod_pais;

ERROR:  Column cod_pais is ambiguous

select * from paises a, provincias b
	where a.cod_pais = b.cod_pais;    

cod_pais|nombre|cod_provincia|cod_pais|nom_provincia
--------+------+-------------+--------+-------------------
       1|pais 1|            1|       1|Provincia 1, Pais 1
       1|pais 1|            2|       1|Provincia 2, Pais 1
       2|pais 2|            1|       2|Provincia 1, Pais 2
       2|pais 2|            2|       2|Provincia 2, Pais 2
       3|pais 3|            1|       3|Provincia 1, Pais 3
       3|pais 3|            2|       3|Provincia 2, Pais 3
(6 rows)

Como hemos pedido "select * from países, provincias", el * es un comodín que indica que queremos TODO, por eso nos devuelve las dos columnas de países y las 3 de provincias, ahora queremos algo más explícito

select a.cod_pais, cod_provincia, nombre, nom_provincia
	from paises a, provincias b
	where a.cod_pais = b.cod_pais;

cod_pais|cod_provincia|nombre|nom_provincia
--------+-------------+------+-------------------
       1|            1|pais 1|Provincia 1, Pais 1
       1|            2|pais 1|Provincia 2, Pais 1
       2|            1|pais 2|Provincia 1, Pais 2
       2|            2|pais 2|Provincia 2, Pais 2
       3|            1|pais 3|Provincia 1, Pais 3
       3|            2|pais 3|Provincia 2, Pais 3
(6 rows)

Complicamos un poco más:

select a.cod_pais, cod_provincia, nombre, nom_provincia
	from paises a, provincias b
	where a.cod_pais = b.cod_pais
		and a.cod_pais = 3;

cod_pais|cod_provincia|nombre|nom_provincia
--------+-------------+------+-------------------
       3|            1|pais 3|Provincia 1, Pais 3
       3|            2|pais 3|Provincia 2, Pais 3
(2 rows)

Funciones

select count(*) from municipios;

count
-----
   27
(1 row)

select cod_pais, count(*) from municipios
	group by cod_pais;

cod_pais|count
--------+-----
       1|    9
       2|    9
       3|    9
(3 rows)

Para ilustrarlo mejor:

select nombre, count(*) from paises a, municipios b
	where a.cod_pais = b.cod_pais
	group by nombre;

nombre|count
------+-----
pais 1|    9
pais 2|    9
pais 3|    9
(3 rows)

Bueno esto era una mera introducción, una forma de ponernos en calor :-)

Revisión de Conceptos

Hemos visto varios comandos:

CREATE TABLE	Este comando permite crear una tabla con sus columnas.
DROP TABLE	Borrará la tabla.
SELECT	Este comando es la base de SQL, es el que permite crear una tabla temporal con los datos que necesitamos. Este comando puede contener funciones o predicados complejos, así como sub_selects: select count(*) from municipios where cod_pais in (select cod_pais from paises); count ----- 27 (1 row)
COMMIT WORK	Este es otro comando muy importante. Este comando indica al gestor que TODAS las modificaciones desde el BEGIN WORK pueden hacerse definitivas. En nuestro gestor lo que marca el inicio de una transacción es el BEGIN WORK, en otros gestores el inicio de una transacción la marca el primer comando que modifica algo en la base de datos. En postgreSQL todo comando que modifique datos lo hará directamente, si previamente no se ha realizado un BEGIN WORK. NOTA: los comandos que modifican el esquema de la base de datos realizan un COMMIT WORK, como tal si se ha abierto una transacción y se realiza un comando que modifique el esquema, nuestra transacción se vera cerrada, con la imposibilidad de realizar un ROLLBACK WORK. Mientras un usuario tenga una transacción activa, este podrá decidir como los demás usuarios tendrán acceso a sus datos : Datos modificados Datos originales a la transacción Bloqueo de acceso
COMMIT WORK	Cierra una transacción dejando las modificaciones como definitivas mientras que ROLLBACK WORK devuelve los datos al estado anterior al inicio de la transacción.

El concepto de transacción es muy importante, dado que permite la vuelta al estado anterior en caso de haberse producido un error. Ahora probemos ese concepto, empezamos por un "rollback work" para cerrar cualquier transacción abierta:

select * from paises;
cod_pais|nombre
--------+------
       1|pais 1
       2|pais 2
       3|pais 3
(3 rows)

begin work;

insert into paises values (5, 'pais de mentira');

select * from paises;
cod_pais|nombre
--------+---------------
       1|pais 1
       2|pais 2
       3|pais 3
       5|pais de mentira
(4 rows)

rollback work;

select * from paises;
cod_pais|nombre
--------+------
       1|pais 1
       2|pais 2
       3|pais 3
(3 rows)

Resumen

SQL nos permite construir una capa de abstracción a los datos y manejar estos según lo necesitemos.

De la forma que hemos visto esto, cabe una pregunta : ¿Cómo aplico SQL a una aplicación?

La respuesta vendrá poco a poco y en la tercera entrega haremos una pequeña aplicación en C.