Nuestra primera base de datos

Sumario

Teoría: 30 min
Ejercicios: 60 min

Preguntas

• ¿Cómo creo una base de datos desde cero?

Objetivos

• ¿Cómo creo las tablas? ¿Se puede automatizar?

• ¿Cómo puedo ejecutar un script de SQL?

• Guardar los resultados en un fichero.

Anteriormente, trabajamos con survey.db, una base de datos que ya os dimos. En este caso vamos a crear una desde cero, crearemos alguna tabla, insertaremos algunos datos de ejemplo y a lo largo de esta lección iremos aumentando la complejidad. Haremos consultas sencillas, crearemos tablas desde un script y acabaramoes haciendo consultas complicadas. Más tarde usaremos transacciones (una unidad única de trabajo) como un modo de trabajo seguro.Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o revertirse, se borran todas las modificaciones de los datos.)

0. Creación de una base de datos

Antes de crearla, vamos a cerciorarmos de que estamos en nuestro directorio de trabajo. Y después crearemos una base de datos llamada test:

$ cd $HOME/SQL/
$ sqlite3 test.db
sqlite> .exit

De esta forma, hemos usado SQLite para crear una base de datos llamada test.db. Al no crear ninguna tabla ni nada en su interior, será una base de datos vacía. Con lo cual ocupará 0 bites:

$ cd $HOME/SQL/
$ ls | grep test.db

0	test.db

Volvamos a entrar en la base de datos y crearemos una tabla sencilla en la cual insertaremos un par de registros.

$ sqlite3 test.db
sqlite> CREATE TABLE agenda (id text, name text, surname text);
sqlite> .tables
sqlite> .schema

Hemos creado una tabla llamada agenda para guardar el DNI, nombre y apellido de un conjunto de personas. Ahora insertaremos dos registros.

sqlite> INSERT INTO agenda VALUES ("3452346Z", "Juan", "Fernández");
sqlite> INSERT INTO agenda VALUES ("1537436A", "Pedro", "Rodríguez");

Como vimos en la lección anterior, podemos recuperar los registros usando SELECT

sqlite> .mode column
sqlite> .header on
sqlite> SELECT * FROM agenda;
sqlite> SELECT id AS DNI FROM agenda;

Como resultado de la última query, tendréis:

DNI       
----------
3452346Z  
1537436A  

Ahora por ejemplo vamos a guardar un número de teléfono en otra tabla:

sqlite> CREATE TABLE telefonos (id text, tlf int) STRICT;
sqlite> INSERT INTO telefonos VALUES ("1537436A", 678654453);

Como veis hemos introducido la palabra “STRICT”, que hace que sqlite sea un poco mas restrictivo … por ello si intentamos insertar un conjunto de caracteres en el campo tlf, que acepta enteros, nos dará un error.

sqlite> sqlite> INSERT INTO telefonos VALUES ("1537436A", "holaquetal");

Nos aparece el siguiente error:

Runtime error: cannot store TEXT value in INT column telefonos.tlf (19)

1. Ejecutar un script

Ya sabemos crear una base de datos, crear una tabla e introducir registros, todo de forma manual. Podemos hacer esto de una forma más automatizada ? La respuesta es si. A lo largo de las lecciones veremos varias formas de automatizar la inserccion de datos, por ahora empezaremos por la ópcion más sencilla: Escribir nuestro código SQL en un fichero de texto plano y hacer que SQLite las lea y ejecute.

Para empezar, teneis que usar un editor de ficheros (el que normalmente utilicéis: vi, gedit, nedit, sublime …). Crearemos un fichero en blanco con extension sql, por ejemplo simple.sql y pondremos lo siguiente:

CREATE TABLE phone (id text, number integer);
INSERT INTO phone VALUES ("3452346Z", 656784123);
INSERT INTO phone VALUES ("3452346Z", 666782375);
INSERT INTO phone VALUES ("1537436A", 631546098);

SELECT * FROM phone;
SELECT * FROM agenda;
SELECT * FROM agenda WHERE name="Juan";
SELECT * FROM phone, agenda WHERE phone.id=agenda.id AND agenda.name="Pedro";

Ahora que tenemos este fichero SQL que crea una tabla, inserta tres registros y ahce varias consultas, vamos a ejecutarlo desde la shell de SQLite:

$ sqlite3 test.db
sqlite> .mode colum
sqlite> .header on
sqlite> .read simple.sql

3452346Z|656784123
3452346Z|666782375
1537436A|631546098

3452346Z|Juan|Fernández
1537436A|Pedro|Rodríguez

3452346Z|Juan|Fernández

1537436A|631546098|1537436A|Pedro|Rodríguez
sqlite> 

2. Guardar los resultados en un fichero de resultados

Aunque apenas tengamos un par de registros, es interesante ver como podemos extraer estos datos de la base de datos a un Excel (por ejemplo). SQLite tiene varias funciones para realizar esta función, de hecho ya la hemos usado y puede que os suene.

sqlite> .tables
sqlite> SELECT * FROM agenda;

El output obtenido es:

id          name        surname   
----------  ----------  ----------
3452346Z    Juan        Fernández 
1537436A    Pedro       Rodríguez 

Ahora vamos a ver, en que formatos podemos guardo:

sqlite> .help mode
.mode MODE ?TABLE?       Set output mode
  MODE is one of:
    ascii    Columns/rows delimited by 0x1F and 0x1E
    csv      Comma-separated values
    column   Left-aligned columns.  (See .width)
    html     HTML <table> code
    insert   SQL insert statements for TABLE
    line     One value per line
    list     Values delimited by "|"
    quote    Escape answers as for SQL
    tabs     Tab-separated values
    tcl      TCL list elements
sqlite> 

Veremos como crea el código HTML y crearemos un fichero csv:

sqlite> .mode html
sqlite> SELECT * FROM agenda;
sqlite> .mode csv
sqlite> .output resultado.csv
sqlite> SELECT * FROM agenda;
sqlite> .exit

Vuestros resultados deberian parecerse mucho a este fichero csv , que podeis abrir con Excel.

Comprendiendo las declaraciones INSERT

¿Como insertarías datos en la tabla phone ?

Solución

sqlite> .mode insert phone 
sqlite> select * from phone;

INSERT INTO phone(id,number) VALUES('3452346Z',656784123);
INSERT INTO phone(id,number) VALUES('3452346Z',666782375);
INSERT INTO phone(id,number) VALUES('1537436A',631546098);

Del resultado obtenido, vemos como son las instrucciones que se usaron para insertar los datos que ahora contiene la tabla.

Seleccionar apellidos

Escriba una consulta que seleccione solo la columna surname de la tabla agenda.

Solución

SELECT surname FROM agenda;

surname
Fernández
Rodríguez

No olvidar

• La creación de una base de datos desde SQLite se puede hacer desde la terminal poniendo un nombre de fichero.db.

• Podemos automatizar la creación de tablas y la inserción de registros a través de scripts de SQL.

• SQLite permite modos de salida para guardar resultados en ficheros en distintos formatos, como HTML y csv.