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.