Ingresar a Postgres

Primero iniciar una sesión remota en el servidor:

ssh username@192.168.250.2

Con el siguiente comando se abre una consola SQL:

  psql -U bd01 -d bd01

Operaciones del DDL

Running Example

Asuma el siguiente esquema de relaciones para un sistema muy simple de registro de libros en una biblioteca.

 paises (id*, nombre)
 editoriales (id*, nombre, pai_id)
 libros (isbn*,titulo,edi_id)

Script de Creación de Tablas

CREATE TABLE PAISES
  (ID SERIAL NOT NULL,
   NOMBRE VARCHAR(15) NOT NULL);
CREATE TABLE EDITORIALES
  (ID SERIAL NOT NULL,
   NOMBRE VARCHAR(20) NOT NULL,
   PAI_ID INT NOT NULL);
CREATE TABLE LIBROS
  (ISBN VARCHAR(20) NOT NULL,
   TITULO VARCHAR(50) NOT NULL,
   EDI_ID INT NOT NULL);

Algunos otros tipos de datos no utilizados en este ejemplo:

  • Timestamp: Fecha y Hora
  • Double: Flotante de 8 bytes.
  • Float: Flotante de 4 bytes.
  • Text: Cadena de caracteres.
  • Boolean: true/false

La lista completa de tipos se encuentra en: http://www.postgresql.org/docs/8.4/static/datatype.html

Algunas Operaciones Sobre Tablas

Describir una tabla
 \d LIBROS;
Listar todas las tablas de la base de datos
 \dt
Adicionar una Columna
ALTER TABLE LIBROS  ADD NUMPAGS int;
Renombrar una Tabla
ALTER TABLE EDITORIALES RENAME TO EDITORES;
Adicionar una Columna
ALTER TABLE LIBROS  ADD FECHA DATE;
Renombrar una Columna
ALTER TABLE LIBROS RENAME FECHA TO FECHAPUB;
Eliminar una Columna

Supongamos que adicionamos una columna a País:

ALTER TABLE PAISES ADD INDICATIVO INT;

Si la queremos eliminar:

ALTER TABLE PAISES DROP COLUMN INDICATIVO;
Eliminar una Tabla

Creemos una tabla de prueba:

CREATE TABLE TEMP (COD INT);

Para eliminarla:

DROP TABLE TEMP;
Valores por Defecto

Supongamos que se debe adicionar una columna con la fecha de registro del libro que por lo general es la fecha actual del sistema. Esto se puede realizar así:

ALTER TABLE LIBROS ADD FECHAREGISTRO DATE;

Luego especificamos que el valor por defecto:

ALTER TABLE LIBROS ALTER COLUMN FECHAREGISTRO SET DEFAULT NOW();

Script de Restricciones (Constraints)

Después de todas las modificaciones anteriores, el esquema de la base de datos luce de la siguiente manera:

    Listado de relaciones
  Esquema |  Nombre  | Tipo  | Dueño 
 ---------+----------+-------+-------
  public  | editores | tabla | bd01
  public  | libros   | tabla | bd01
  public  | paises   | tabla | bd01
 (3 filas)
                                   Tabla «public.paises»
  Columna |         Tipo          |                         Modificadores                         
 ---------+-----------------------+---------------------------------------------------------------
  id      | integer               | not null valor por omisión nextval('paises_id_seq'::regclass)
  nombre  | character varying(15) | not null
                                    Tabla «public.editores»
  Columna |         Tipo          |                           Modificadores                            
 ---------+-----------------------+--------------------------------------------------------------------
  id      | integer               | not null valor por omisión nextval('editoriales_id_seq'::regclass)
  nombre  | character varying(20) | not null
  pai_id  | integer               | not null
                  Tabla «public.libros»
     Columna    |         Tipo          |      Modificadores      
 ---------------+-----------------------+-------------------------
  isbn          | character varying(20) | not null
  titulo        | character varying(50) | not null
  edi_id        | integer               | not null
  numpags       | integer               | 
  fechapub      | date                  | 
  fecharegistro | date                  | valor por omisión now()

El siguiente paso es definir las llaves primarias:

ALTER TABLE PAISES ADD CONSTRAINT PAI_PK PRIMARY KEY (ID);
ALTER TABLE EDITORES ADD CONSTRAINT EDI_PK PRIMARY KEY (ID);
ALTER TABLE LIBROS ADD CONSTRAINT LIB_PK PRIMARY KEY (ISBN);

Luego se deben crear las llaves foráneas:

ALTER TABLE EDITORES ADD CONSTRAINT 
 EDI_PAI_FK FOREIGN KEY (PAI_ID) REFERENCES PAISES (ID);
ALTER TABLE LIBROS ADD CONSTRAINT 
 LIB_EDI_FK FOREIGN KEY (EDI_ID) REFERENCES EDITORES (ID);

También es posible definir atributos cuyos valores no se pueden repetir. Por ejemplo, no deberían existir dos países con el mismo nombre. Esto se puede especificar por medio de llaves de unicidad:

ALTER TABLE PAISES ADD CONSTRAINT 
 PAI_NOM_UK UNIQUE (NOMBRE);

Finalmente, los valores que pueden tomar algunos atributos deben estar delimitados. Por ejemplo, el número de páginas de un libro no debería ser negativo. Esto se puede realizar con restricciones de chequeo:

ALTER TABLE LIBROS ADD CONSTRAINT 
 LIB_PAGS_CK CHECK (NUMPAGS > 0);

Operaciones del DML

Inserción de Datos

 INSERT INTO PAISES (NOMBRE) VALUES ('COLOMBIA'), ('ALEMANIA');

Note que no es necesario especificar un valor para el campo ID puesto que fue declarado como una secuencia (Tipo de dato SERIAL).

Ahora las otras tablas:

 INSERT INTO EDITORES (NOMBRE,PAI_ID) VALUES ('NORMA',1);
 INSERT INTO EDITORES (NOMBRE,PAI_ID) VALUES ('SPRINGER',2);
 INSERT INTO LIBROS (ISBN,TITULO,FECHAPUB,EDI_ID) VALUES ('9783540443285','Programme Evaluation and Treatment Choice','2003-01-01',2);
 INSERT INTO LIBROS (ISBN,TITULO,FECHAPUB,NUMPAGS,EDI_ID) VALUES ('9789588294780','Confieso','2011-01-11',152,1);

Consultado Información

 SELECT * FROM LIBROS;
 SELECT * FROM PAISES;
 SELECT * FROM EDITORES ORDER BY NOMBRE;
 SELECT ISBN, TITULO FROM LIBROS WHERE NUMPAGS > 10;

Actualizando Datos

UPDATE LIBROS SET NUMPAGS=199 WHERE ISBN='9783540443285';

Eliminando Datos

Insertemos primero un país 'no válido' :

 INSERT INTO PAISES (NOMBRE) VALUES ('XXX');

Ahora lo eliminamos:

 DELETE FROM PAISES WHERE ID = 3;

Scripts Completos

Para ejecutar un archivo con sentencias SQL se utiliza:

 \i file.sql

El archivo ejemplo.zip contiene un ejemplo completo. Utilizar:

 \i contactos.sql

Taller

  1. Completar el esquema de bases de datos anterior para considerar los autores de los libros y las materias afines a cada libro.
  2. Dado el siguiente esquema E/R, generar el script de tablas.

 
materias/creacion_de_tablas.txt · Última modificación: 2011/03/10 17:18 por caolarte
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki