SQLite es una base de
datos relacional compatible con ACID (Atomicity, Consistency, Isolation and Durability:
Atomicidad, Consistencia, Aislamiento y Durabilidad en español, todo esto ya lo
explicaremos más adelante en otro post). Lo que la diferencia de otras bases de
datos es que contiene todos sus elementos (definiciones, tablas, índices, y los
propios datos) en un único fichero almacenado en el equipo.
Para manejarla, y como
requisito para seguir los pasos que veremos a continuación, necesitamos un
cliente de SQLite. Yo recomiendo DB
Browser, que utilizaremos a continuación, y que puede ser descargado en:
1.
Diseñando la estructura
Nuestra base de datos va
a representar un compendio de películas de los 90. Para ello, crearemos dos
tablas, una para las películas, y otra para los directores.
El campo director de la tabla films hará referencia al campo id de la tabla directors. Cabe destacar que aquí se realiza una relación de n a 1, esto significa que n
películas, es decir, múltiples películas, pueden hacer referencia a un único
director.
2.
Creando la base de datos
Para crear una base de
datos abrimos nuestro cliente DB Browser y hacemos click en la pestaña Nueva base de datos:
Lo primero que hará el
programa es crear el fichero y para ello te pedirá que elijas una ubicación y
le des un nombre, en este caso, la llamaremos ‘filmsDB’. Se creará un fichero
en la carpeta seleccionada llamado “filmsDB.db”, .db es la extensión por defecto de SQLite.
3.
Creando las tablas
Nos mostrará una pantalla
en la que podremos crear la primera tabla, dividida en dos verticalmente. En la
parte de arriba nos permitirá introducir los campos de la tabla mediante un
formulario, en la parte de abajo, de forma manual con comandos de SQL. Como no
somos expertos en SQL la editaremos con el formulario y trataremos de aprender
lo máximo posible de los comandos que aparecen en pantalla.
Haré una breve
descripción de las opciones que aparecen:
·
Tipo: El tipo de dato que se guardará en cada uno de los campos.
o
INTEGER
o
TEXT
o
BLOB
o
REAL
o
NUMERIC
- No nulo -> NOT NULL: No se podrá dejar el campo vacío.
- PK -> PRIMARY KEY: Significa que será el identificar del registro dentro de la tabla, el cual debe ser único e inconfundible, típicamente será un número.
- AI -> AUTORINCREMENT: El valor del campo será introducido por la base de datos al insertar el registro, añadiendo un valor más, en caso de ids numéricos.
- U -> UNIQUE: El valor de este campo no se podrá repetir en otro campo.
- Por defecto: Valor automático de un campo not null si lo insertamos vacío.
- Check: Restricciones y comprobaciones que se pueden realizar al insertar el valor de un campo.
- Foreign Key: Relación entre un valor de la tabla actual y el de otra tabla, para relacionar registros de ambas.
En nuestro caso, queremos
que ningún campo pueda quedar vacío, luego todos se marcarán como not null, además queremos que el id sea
nuestra primary key y que
autoincremente y que tanto el campo “id” como el campo “title” sean únicos.
Una vez configurada la
tabla, como se muestra en la imagen, damos a ok. En la pestaña Estructura de la base de datos tenemos
el botón Crear Tabla para volver a
abrir la ventana y seguir el mismo mecanismo para generar la tabla “directors”.
4.
Relacionando tablas
Una vez hayáis creado
ambas tablas, llega el momento de relacionarlas con una foreign key. La foreign key nos indica que campo de nuestra tabla
hace referencia a un registro de otra tabla. En nuestro caso, el campo “director”
de la tabla “films” hace referencia al campo “id” de la tabla “directors”.
Para ello pinchamos en la
tabla “films” con el botón derecho y le damos a la opción de Modificar Tabla. Se nos desplegara la
ventana a la que estamos acostumbrados.
Nos posicionamos en el
campo “directors” y en la opción Foreign
Key.
5.
Navegando en los datos y añadiendo registros
Ahora que ya hemos creado
nuestra base de datos, ha llegado el momento de nutrirla. De momento lo haremos
de forma manual, en el siguiente punto os explicare como hacer querys SQL.
Pulsaremos la pestaña Navegar Datos, en la
que nos aparecera un desplegable con la leyenda Tabla:, seleccionamos la tabla “directors” y nos aparecerán en
pantalla los direfentes campos de la tabla representados por columnas. Los
registros se posicionan a modo de filas, en este caso, la tabla estará vacia.
Para remediarlo, pulsamos en Nuevo registro. Esta acción creara una nueva fila en la que
podremos introduccir los datos.
NOTA:
Fíjate como el valor del campo “id” se rellena solo, se autoincrementa.
Pues bien, rellenamos los
campos, en el ejemplo Tarantino es nuestro primer director de los 90.
Realizaremos la misma
acción con las películas. Seleccionamos la tabla “films” y repetimos la misma
operación.
NOTA: Fíjate como en el campo “director” hemos
añadido el valor 1, pues hace referencia al registro de Tarantino de la tabla
“directors” cuyo id es el 1.
Te animo a que introduzcas nuevos registros en ambas tablas, siempre teniendo en consideración la correspondencia entre films.director y directors.id, y por supuesto, que siempre sean buenas pelis de los 90.
6.
Realizando queries
Una query, en castellano
consulta, no es más que una petición de datos a la base de datos. Se aplica más
genéricamente a cualquier interacción con base de datos mediante comandos SQL.
Aquí explicaré los
comandos más básicos para consultar y modificar los datos de nuestra base de
datos de películas de los 90.
Para ello vamos a
posicionarnos en la pestaña Ejecutar SQL.
En el cuadro de diálogo
superior podremos introducir nuestros comandos, donde especificaremos que clase
de consulta queremos realizar y bajo qué condiciones.
Justo debajo, se mostrará la tabla con los datos resultantes, y por último, en el recuadro inferior, nuestro cliente nos mostrará las trazas de las operaciones que va realizando, tanto los éxitos como los errores.
Empecemos:
A.
Consulta de datos
Iniciaremos las consultas
con la más simple de todas, que consiste en traer todas las películas
almacenadas en la tabla “films”. Para ello introduciremos el comando:
SELECT * FROM films
¿Qué estamos escribiendo?
- SELECT: Es el verbo específico para extraer datos de la tabla.
- *: El asterisco significa todo, queremos que nos devuelva todo, si pusiéramos, por ejemplo, title, únicamente nos devolvería el campo “title”.
- FROM: Introduce de que conjunto de datos queremos extraerlos.
- films: Es la fuente de los datos, en este caso la tabla “films”.
Podemos observar que nos
ha traído todos los registros que se encuentran actualmente en la tabla y nos
lo comunica a través de las trazas.
B.
Consultando datos específicos
Complicaremos un poco más
la consulta anterior. En este caso, no preguntaremos por todas las películas de
la tabla, si no por una en concreto:
SELECT * FROM films WHERE id=2
¿Qué estamos escribiendo?
- WHERE: introduce la condición en el comando.
- id=2: Es la condición efectiva, únicamente buscará aquel registro cuyo valor del campo “id” sea 2.
En este caso nos
devolverá un único registro en la tabla:
Mi consejo en este punto
es que pruebes diferentes consultas, que juegues con los parámetros y las
condiciones. Por ejemplo:
- SELECT title FROM films WHERE year=1994
- SELECT * FROM films WHERE director=1
¿Qué sucederá en el
último caso? ¿Cuántos registros debería devolver?
C.
Introducir registros
Igual de importante que
consultar datos, y en ocasiones incluso más, es poder introducir registros en
nuestra base de datos.
Vamos a añadir un nuevo
director a nuestra tabla “directors” con el siguiente comando:
INSERT INTO directors (name, surname)
VALUES ('Steven',
'Spielberg')
- INSERT: Es el verbo para añadir registros
- INTO directors: Indica en que tabla queremos insertar
- (name, surname): Tenemos que especificar los campos a añadir
- VALUES (‘…’,’…’): Damos los respectivos valores
El método INSERT no
devuelve nada en el lugar dónde antes nos mostraba los resultados del SELECT,
sin embargo siempre muestra trazas.
Visualiza la tabla de
“directors”, o haz una consulta ahora que ya sabes, y comprueba que se ha
insertado correctamente y que le ha sido asignado un id nuevo.
Si ahora ejecutamos el
siguiente comando:
select
last_insert_rowid();
Recibiremos como
respuesta el id de la última
inserción. Esto es muy útil.
Supongamos que queremos insertar ahora películas de Spielberg, necesitamos que la base de datos nos devuelva el id del director para poder añadirlo dentro del campo “directors” de las nuevas películas.
Supongamos que queremos insertar ahora películas de Spielberg, necesitamos que la base de datos nos devuelva el id del director para poder añadirlo dentro del campo “directors” de las nuevas películas.
Te animo a que realices
tantas inserciones como necesites para familiarizarte con el método.
D.
Modificar registros
Me he permitido una
pequeña trampa, que dudo que alguien haya notado, y es que la película Reservoir Dogs no se estrenó en 1993 si
no en 1992. Que me perdonen los histéricos, pero me venía muy bien para
introducir este punto.
Muchas veces no queremos
consultar ni añadir datos, si no que queremos modificar los registros ya
existentes, actualizar fechar, añadir contadores, cambiar datos personales, o
como en este caso, rectificar información.
Vamos a corregirlo:
UPDATE films
SET year = 1992
WHERE id=2;
Ya estamos algo
familiarizados con los comandos SQL así que es fácil ver que es lo que está
pasando:
- UPDATE: Es el método para modificar la tabla “films”.
- SET: Ajustamos el campo “year” con el valor “1992”, cuando se dé la condición “id=2”.
Como es costumbre,
comprobamos que todo ha ido bien en las trazas, y comprobamos que los registros
están correctamente modificados.
Puedes modificar y volver a modificar tantos campos como quieras, lo cual sería un buen ejercicio para practicar.
E.
Eliminar registros
Vamos a ver como eliminar
registros. Es muy similar a hacer una consulta, pero con un verbo que elimina
en vez de devolver información.
DELETE FROM directors WHERE id=2
- DELETE: Es el método de eliminación de registros, el resto no es nada nuevo para nosotros.
Estamos eliminando el registro con id=2 de
“directors”, en mi caso Steven Spielberg, pero podría no ser así en otra tabla,
o que ni siquiera existiese el registro. Dependerá de las inserciones que hayas
hecho en tu tabla antes de llegar a este punto.
Fíjate siempre en las trazas para comprobar que todo funciona correctamente.
F.
Consultas multi-tabla
Como habrás podido
observar, cuando consultamos una película recibimos un número en el campo
“director” que sabemos que hace referencia a la tabla “directors”. La
información queda incompleta, debemos coger este id y consultar en dicha tabla
para tener la información de la película con su director.
Esto es un poco aburrido,
¿no se puede hacer todo de una vez? Por supuesto, de hecho, comúnmente se hace
con una única consulta multi-tabla.
Este tipo de consulta es
una consulta compleja que une ambas tablas en la respuesta mediante la
sentencia JOIN
SELECT * FROM films
JOIN directors
on films.director = directors.id
- SELECT * FROM films: La consulta a la tabla principal.
- JOIN directors: Unida a la consulta a la tabla secundaria.
- On films.director = directors.id: Cuando los campos especificados coinciden.
Como podemos ver, esto
nos devuelve la información de ambas tablas en una única búsqueda.
Ahora sí, es importante
que juegues con los JOIN, que investigues los tipos de JOIN, para seguir
aprendiendo acerca de este complejísimo mundo que son las bases de datos.
Puedes descargar un ejemplo mas completo de esta base de datos aquí.
¡¡Nos vemos en el siguiente curso!! Saludos del Blogramador!
No hay comentarios:
Publicar un comentario