sqlite

Sqlite

Manuel Molino Milla Luis Molina Garzn

15 de abril de 2014

ndice

1. Instalacion 2

2. Ejemplo 2

3. Creacion de la BD 2

4. Establecimiento de soporte de integridad referencial 2

5. Creacion de las tablas 3

6. Indices en las tablas 4

7. Insertar datos 4

8. Ejemplo de consultas 6

9. Vistas en Sqlite 7

10.Triggers 8

11.Esquema nal de la BD 9

12.Copia de seguridad 10

13.Ejercicio nal 10

1

1. Instalacion

Descargamos la aplicacin desde http://www.sqlite.org/download.html y elegi-

mos la opcin que sea de nuestro gusto.

Posteriormente descomprimimos con unzip y ejecutamos con ./sqlite3 :

manu@manu-R40-R41:~/curso_java/diapositivas/sqlite/BD$ unzip sqlite-shell-linux-x86-3080403.zip

Archive: sqlite-shell-linux-x86-3080403.zip

inflating: sqlite3

manu@manu-R40-R41:~/curso_java/diapositivas/sqlite/BD$ ls

sqlite3 sqlite-shell-linux-x86-3080403.zip

manu@manu-R40-R41:~/curso_java/diapositivas/sqlite/BD$ ./sqlite3

SQLite version 3.8.4.3 2014-04-03 16:53:12

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite>

2. Ejemplo

Vamos a utilizar una BD con el siguiente esquema:

3. Creacion de la BD

Para la creacin de la BD solo hace falta ejecutar ./sqlite3 ejemplo donde

ejemplo ser el nombre de la BD. El resultado de la ejucin es una terminal

preparada para establecer el esquema de dicha BD.

manu@manu-R40-R41:~/curso_java/diapositivas/sqlite/BD$ ./sqlite3 ejemplo

SQLite version 3.8.4.3 2014-04-03 16:53:12


sqlite>

4. Establecimiento de soporte de integridad ref-

erencial

Por defecto Sqlite no permite foreign keys para ello hay que forzar el su uso:

2


SQLite version 3.8.4.3 2014-04-03 16:53:12


sqlite> PRAGMA foreign_keys;

0

sqlite> PRAGMA foreign_keys = ON;

sqlite> PRAGMA foreign_keys;

1

sqlite>

5. Creacion de las tablas

Vamos a usar el siguiente esquema:

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS alumno;

CREATE TABLE alumno (

id INTEGER PRIMARY KEY AUTOINCREMENT,

nombre TEXT,

apellidos TEXT

);

DROP TABLE IF EXISTS modulo;

CREATE TABLE modulo (


nombre TEXT,

numeroHoras INTEGER

);

DROP TABLE IF EXISTS curso;

CREATE TABLE curso (


nombre TEXT,

aula TEXT

);

DROP TABLE IF EXISTS evaluacion;

CREATE TABLE evaluacion (

idAlumno INTEGER,

idModulo INTEGER,

idCurso INTEGER,

notas INTEGER,

PRIMARY KEY(idAlumno,idModulo,idCurso),

FOREIGN KEY(idAlumno) REFERENCES alumno(id) ON DELETE CASCADE,

FOREIGN KEY(idModulo) REFERENCES modulo(id) ON DELETE CASCADE,

FOREIGN KEY(idCurso) REFERENCES curso(id) ON DELETE CASCADE

);

3

Para cargarla usamos el comando read que permite ejecutar sentencias SQL

desde un chero:


SQLite version 3.8.4.3 2014-04-03 16:53:12


sqlite> .read crear_tablas

6. Indices en las tablas

El ndice de una base de datos es una estructura de datos que mejora la

velocidad de las operaciones, por medio de identicador nico de cada la de

una tabla, permitiendo un rpido acceso a los registros de una tabla en una base

de datos. Al aumentar drsticamente la velocidad de acceso, se suelen usar, sobre

aquellos campos sobre los cuales se hacen frecuentes bsquedas.

El ndice tiene un funcionamiento similar al ndice de un libro, guardando

parejas de elementos: el elemento que se desea indexar y su posicin en la base

de datos.

Los ndices pueden ser creados usando una o ms columnas, proporcionando

la base tanto para bsquedas rpidas al azar como de un ordenado acceso a

registros eciente.

Para la creacin en Sqlite podemos hacer

CREATE INDEX apell ON alumno(apellidos);

7. Insertar datos

Podemos hacerlo desde un chero usando de nuevo el comando .read. Vamos

a importar los siguientes datos:

INSERT INTO alumno (nombre,apellidos) VALUES ("Juan", "Garca Paniagua");

INSERT INTO alumno (nombre,apellidos) VALUES ("Pedro", "Buenda Garca");

INSERT INTO alumno (nombre,apellidos) VALUES ("Jacinto", "Piedra Hita");

INSERT INTO alumno (nombre,apellidos) VALUES ("Pedro", "Nuez Garca");

INSERT INTO alumno (nombre,apellidos) VALUES ("Inmaculada", "Beat Polonia");

INSERT INTO alumno (nombre,apellidos) VALUES ("Roco", "Cornejo Garca");

INSERT INTO alumno (nombre,apellidos) VALUES ("Victoria", "Buenda Benito");

INSERT INTO alumno (nombre,apellidos) VALUES ("M

a

Dolores", "Molinos Codoba");

INSERT INTO alumno (nombre,apellidos) VALUES ("Alejandro ", "Zapatero Aznar");

INSERT INTO alumno (nombre,apellidos) VALUES ("ngel", "Gmez Gmez");

INSERT INTO alumno (nombre,apellidos) VALUES ("Luisa", "Peralez Santiago");

INSERT INTO alumno (nombre,apellidos) VALUES ("Felix", "Barou Garca-Concha");

INSERT INTO alumno (nombre,apellidos) VALUES ("Samuel", "Garca Fernndez");

INSERT INTO modulo (nombre,numeroHoras) VALUES ("Base de datos", 8);

INSERT INTO modulo (nombre,numeroHoras) VALUES ("Programacin", 8);

INSERT INTO modulo (nombre,numeroHoras) VALUES ("Lenguajes de marcas", 4);

4

6|Roco|Cornejo Garca

7|Victoria|Buenda Benito

8|M

a

Dolores|Molinos Codoba

9|Alejandro |Zapatero Aznar

10|ngel|Gmez Gmez

11|Luisa|Peralez Santiago

12|Felix|Barou Garca-Concha

13|Samuel|Garca Fernndez

Podemos cambiar la forma de imprimirlo con .mode

.mode MODE ?TABLE? Set output mode where MODE is one of:

csv Comma-separated values

column Left-aligned columns. (See .width)

html HTML code

insert SQL insert statements for TABLE

line One value per line

list Values delimited by .separator string

tabs Tab-separated values

tcl TCL list elements

Cambiando a .mode tabs tendremos resultado como:

sqlite> select * from modulo;

1 Base de datos 8

2 Programacin 8

3 Lenguajes de marcas 4

8. Ejemplo de consultas

sqlite> .separator ""

sqlite> .mode list

sqlite> select notas, alumno.nombre, alumno.apellidos, modulo.nombre from \

alumno, modulo, evaluacion, curso where alumno.id=evaluacion.idAlumno and \

modulo.id=idModulo and curso.id=1;

Que origina la siguiente consulta:

5JuanGarca PaniaguaBase de datos

6JuanGarca PaniaguaProgramacin

4JuanGarca PaniaguaLenguajes de marcas

4PedroBuenda GarcaBase de datos

5PedroBuenda GarcaProgramacin

6PedroBuenda GarcaLenguajes de marcas

6JacintoPiedra HitaBase de datos

1JacintoPiedra HitaProgramacin

10JacintoPiedra HitaLenguajes de marcas

5PedroNuez GarcaBase de datos

6PedroNuez GarcaProgramacin

1PedroNuez GarcaLenguajes de marcas

6InmaculadaBeat PoloniaBase de datos

6

6InmaculadaBeat PoloniaProgramacin

6InmaculadaBeat PoloniaLenguajes de marcas

2RocoCornejo GarcaBase de datos

8RocoCornejo GarcaProgramacin

7RocoCornejo GarcaLenguajes de marcas

6VictoriaBuenda BenitoBase de datos

6VictoriaBuenda BenitoProgramacin

6VictoriaBuenda BenitoLenguajes de marcas

3M

a

DoloresMolinos CodobaBase de datos

0M

a

DoloresMolinos CodobaProgramacin

1M

a

DoloresMolinos CodobaLenguajes de marcas

6Alejandro Zapatero AznarBase de datos

6Alejandro Zapatero AznarProgramacin

6Alejandro Zapatero AznarLenguajes de marcas

1ngelGmez GmezBase de datos

1ngelGmez GmezProgramacin

1ngelGmez GmezLenguajes de marcas

6LuisaPeralez SantiagoBase de datos

8LuisaPeralez SantiagoProgramacin

9LuisaPeralez SantiagoLenguajes de marcas

9FelixBarou Garca-ConchaBase de datos

10FelixBarou Garca-ConchaProgramacin

3FelixBarou Garca-ConchaLenguajes de marcas

3SamuelGarca FernndezBase de datos

3SamuelGarca FernndezProgramacin

4SamuelGarca FernndezLenguajes de marcas

9. Vistas en Sqlite

Una vista no es mas que una sentencia SQL que se almacena en la BD con

un nombre asociado. Por lo tanto almacena los datos de una tabla, o parte de

un tabla en funcin del tipo de consulta, o bien de varias tablas en el caso de

hacer subconsultas o join

Ejemplo una cosulta puede ser nombre y apellidos de los alumnos que pertenecen

a Segundo A con sus notas del mdulo Base de datos:

sqlite>select alumno.nombre, alumno.apellidos, evaluacion.notas from

alumno, curso, modulo, evaluacion where alumno.id=evaluacion.idAlumno

and curso.id=evaluacion.idCurso and modulo.id=evaluacion.idModulo

and modulo.nombre='Base de datos' and curso.nombre='Segundo

A';

Que nos d:

Juan|Garca Paniagua|5

Pedro|Buenda Garca|4

Jacinto|Piedra Hita|6

Pedro|Nuez Garca|5

Inmaculada|Beat Polonia|6

Roco|Cornejo Garca|2

7

Victoria|Buenda Benito|6

M

a

Dolores|Molinos Codoba|3

La vista se crea con el comando CREATE VIEW nombreVista AS sentencia

Ejemplo anterior:

CREATE VIEW bd_2A AS select alumno.nombre, alumno.apellidos,

evaluacion.notas from alumno, curso, modulo, evaluacion where alum-

no.id=evaluacion.idAlumno and curso.id=evaluacion.idCurso and mod-

ulo.id=evaluacion.idModulo and modulo.nombre='Base de datos' and

curso.nombre='Segundo A';

La consulta anterios es igual a hacer:

select * from bd_2a;

Tambin se pueden haces consultas sobre dicha vista, por ejemplo de los datos

anteriores queremos los alumnos que aprueban:

select * from bd_2a where notas>=5;

Cuya salida es:

Juan|Garca Paniagua|5

Jacinto|Piedra Hita|6

Pedro|Nuez Garca|5

Inmaculada|Beat Polonia|6

Victoria|Buenda Benito|6

10. Triggers

Un trigger (o disparador) en una Base de datos, es un procedimiento que se

ejecuta cuando se cumple una condicin establecida al realizar una operacin.

Dependiendo de la base de datos, los triggers pueden ser de insercin (INSERT),

actualizacin (UPDATE) o borrado (DELETE). Algunas bases de datos pueden

ejecutar triggers al crear, borrar o editar usuarios, tablas, bases de datos u otros

objetos.

El escenario del ejemplo es el siguiente, cuando un alumno se de de baja se

crear una tupla con los datos del alumno y la fecha de baja en una tabla histo-

rial, pare esto se puede usar el siguiente esquema que crea la tabla y eltrigger :

DROP TABLE IF EXISTS historial;

CREATE TABLE historial (


nombre TEXT,

apellidos TEXT,

fecha_baja TEXT

);

DROP TRIGGER IF EXISTS borrado;

CREATE TRIGGER borrado BEFORE DELETE

ON alumno

8

BEGIN

INSERT INTO historial (nombre, apellidos, fecha_baja) VALUES

(old.nombre, old.apellidos, datetime('now'));

END;

11. Esquema nal de la BD

Al nal la BD queda, tras ejecutar el comando .sqlite:

sqlite> .schema

CREATE TABLE alumno (


nombre TEXT,

apellidos TEXT

);

CREATE TABLE modulo (


nombre TEXT,

numeroHoras INTEGER

);

CREATE TABLE curso (


nombre TEXT,

aula TEXT

);

CREATE TABLE evaluacion (

idAlumno INTEGER,

idModulo INTEGER,

idCurso INTEGER,

notas INTEGER,

PRIMARY KEY(idAlumno,idModulo,idCurso),

FOREIGN KEY(idAlumno) REFERENCES alumno(id) ON DELETE CASCADE,

FOREIGN KEY(idModulo) REFERENCES modulo(id) ON DELETE CASCADE,

FOREIGN KEY(idCurso) REFERENCES curso(id) ON DELETE CASCADE

);

CREATE INDEX apell ON alumno(apellidos);

CREATE VIEW bd_2A AS select alumno.nombre, alumno.apellidos, evaluacion.notas from alumno,

curso, modulo, evaluacion where alumno.id=evaluacion.idAlumno and

curso.id=evaluacion.idCurso and modulo.id=evaluacion.idModulo and

modulo.nombre="Base de datos" and curso.nombre='Segundo A';

CREATE TABLE historial (


nombre TEXT,

apellidos TEXT,

fecha_baja TEXT

);

CREATE TRIGGER borrado BEFORE DELETE

ON alumno

BEGIN

INSERT INTO historial (nombre, apellidos, fecha_baja)

9

VALUES (old.nombre, old.apellidos, datetime('now'));

END;

12. Copia de seguridad

Se puede hacer una copia con el comando .backup

Tambin se puede tratar como una copia de seguridad de un chero de

sistema.

13. Ejercicio nal

El ejercicio nal consiste en la realizacion de una BD para inventariar libros y

permitir su posterior prestamo. Para esto hay que tener en cuenta los siguientes

datos:

Los libros pueden pertenecer a una unica categoria, que puede ser: Bases de

datos, programacion, redes, omatica, hardware, seguridad, aplicaciones

web, sistemas operativos o miscelanea.

De los libros debemos conocer el nombre, autor y editorial.

De los usuarios su nombre y apellidos.

Los libros pueden ser prestados, lo unico que tenemos que conocer es la

persona que lo retira y la fecha de prestamo.

Prepara las siguientes consultas, si es necesario prepara vistas para las

mismas.

1. El numero de libros prestados.

2. El listado de libros prestados.

3. El listado de libros por categorias.

4. El listado de libros prestados por categorias.

5. El listado de libros no prestados por categorias.

6. El listado de libros no prestados.

7. Los libros prestados a un usuario.

8. Listado de usuarios.

Prepara dos triggers para dar de baja a usuarios y libros.

10

Documents

sqlite