Definición y manipulación de datos

Por definición de datos entendemos la creación de las estructuras de almacenamiento: crear bases de datos, crear tablas, índices, procedimientos, etc. También eliminarlas y modificarlas.

Por manipulación de datos, todo lo que tiene ver con el contenido de esas estructuras: consultar, insertar filas, borrar filas, modificar datos...

Como resumen de lo que se va a desarrollar a partir de aquí, veremos:

• CREATE TABLE nombreTabla (columnas y restricciones)

  • crear una tabla definiendo sus columnas y tipos de datos, y con las restricciones que hagan falta (clave primaria, ajena, no nulos, etc.)

    • las tablas persisten en el esquema hasta que se las elimina

• DROP TABLE nombreTabla

  • eliminar una tabla del esquema (también desaparecen las filas que hubiera en ella)

• INSERT INTO nombreTabla VALUES (valores)

  • añadir una fila a la tabla con sus correspondientes valores en cada componente de la fila

• INSERT INTO nombreTabla consultaSelect

  • variante de la anterior que obtiene los valores desde una consulta a la misma u otra base de datos

• DELETE FROM nombreTabla [WHERE condicion]

  • eliminar una o varias filas, dependiendo de las condiciones que exijamos

• UPDATE nombreTabla SET asignaciones [WHERE condiciones]

  • modificar valores concretos en una o varias filas, dependiendo de las condiciones que exijamos

Create table

La definición de tablas es el primer paso en la creación de una base de datos. El conjunto de descripciones de tablas conforma el esquema de base de datos y representa a un sistema de información concreto.

Para cumplir con las restricciones del modelo relacional, además, debemos elegir las claves primarias adecuadas : DNI para profesores y código para asignaturas. Obviamente, la forma que tienen estas tablas ha sido una decisión nuestra como diseñadores de esta base de datos concreta, en otra situación hubiéramos, probablemente, decidido definir otros atributos y otras tablas.

La orden CREATE TABLE nos permite crear cada una de las tablas necesarias para nuestra base de datos:

CREATE TABLE nombreTabla ( {listaColumnas} [,{restricciones}] )

La lista de columnas, en su forma más sencilla, es un conjunto de expresiones (tantas como columnas deseemos, y separadas por comas) del tipo:

columna tipoDatos[,columna tipoDatos[, ...]]

La totalidad de tipos de datos que maneja MySQL, siendo la mayoría comunes con ligeras diferencias a cualquier motor de base de datos, se puede encontrar en https://mariadb.com/kb/en/data-types/.

Restricciones

Las restricciones son reglas, que normalmente se establecen en el momento de crear una tabla, para garantizar la integridad de los datos.

Básicamente, las restricciones obligan a cumplirse ciertas reglas cuando una fila es insertada, borrada o modificada, de forma que la operación se llevará a efecto sólo si se cumplen las restricciones definidas en la tabla.

Podemos contemplar los siguientes tipos de restricciones de integridad de datos:

• NOT NULL: especifica que la columna no puede contener un valor nulo.

• PRIMARY KEY: identifica de manera única a cada fila de la tabla mediante una o varias columas, estas columnas que forman la clave primaria no pueden tener valores nulos.

• FOREIGN KEY: establece una relación entre una(s) columna(s) de la tabla y otra(s) columna(s) de la tabla referenciada, siendo esta última(s) columna(s) la PRIMARY KEY

• UNIQUE: no permite duplicados; combinado con NOT NULL es la forma de definir una clave alternativa.

• CHECK: especifica una condición que se debe evaluar a "cierto".

De las restricciones,solo vamos a utilizar, de momento, la clave primaria, que puede contener tantas columnas como se necesiten:

PRIMARY KEY (columna[,columna[, ...]])

Drop table

Si queremos borrar una tabla debemos ordenárselo al SGBD mediante la orden DROP TABLE:

DROP TABLE nombreTabla

Al utilizar esta orden también se eliminan los datos (las filas) que pudiera contener (en este caso, ninguna). Se puede borrar y crear la tabla tantas veces como queramos. 

No obstante, la definición de claves ajenas puede impedir el borrado de una tabla:

Borra la tabla asignaturas

drop table asignaturas;

/* Error de SQL (1451): Cannot delete or update a parent row: a foreign key constraint fails */

No podemos eliminar esta tabla porque IMPARTE.asignatura es clave ajena hacia ASIGNATURAS.codigo. Para poder eliminar ASIGNATURAS, debemos eliminar primero IMPARTE o, al menos, la definición de clave ajena.

drop table imparte;

drop table asignaturas;

Información del catálogo

Recordemos que ejecutar DESC nombreTabla o DESCRIBE nombreTabla, muestra información sobre las columnas que componen la tabla, el orden interno de las mismas en la tabla, y sus tipos de datos. La sentecia DESCRIBE es, en realidad, un atajo para acceder a las tablas del catálogo mediante una sentencia select. 

En el momento de crear una restricción, además de especificar las reglas que se deben cumplir, podemos dar un nombre a la misma con constraint. Para establecer los nombres de las restricciones, se suele seguir el siguiente convenio: ser nombres descriptivos, que empiecen, por ejemplo, por

Obviamente, este convenio es eso, una recomendación, al sistema le da igual el nombre que elijamos.

Esto nos permite ejecutar órdenes como

-- Eliminar restricción de clave ajena

ALTER TABLE imparte DROP FOREIGN KEY FK_imparte_asignaturas;

No vamos a profundizar más en los nombres de las restricciones ni en la sintaxis para crearlos, pero sí que debemos saber que si no especificamos ningún nombre, el sistema le asignará un nombre único a cada restricción con un formato propio.

Insert

Para introducir datos nuevos en una base de datos vamos a utilizar la orden INSERT de SQL. Con la sintaxis que se muestra a continuación seremos capaces de introducir datos nuevos en cualquiera de las tablas que componen una determinada BD. En principio, veremos la expresión mínima de la orden, formada por dos cláusulas, INTO y VALUES.

INSERT INTO nombreTabla VALUES ( listaExpresiones )

Alta un nuevo profesor con los siguientes datos:

• dni 55555555

• nombre PATRICIO MARTÍNEZ,

• categoría TU

• fecha de incorporación 01/01/2000

insert into profesores values ('55555555','PATRICIO MARTINEZ','TU','2000-1-1') 

Alta un nuevo profesor con los siguientes datos:

• dni 66

• nombre ERNESTO PEREZ

• categoría ASO

• fecha de incorporación 01/01/1985

insert into profesores values ('66','ERNESTO PEREZ','ASO','1985-1-1'); 

El resultado que devuelve una orden INSERT, será siempre el número de filas insertadas, en el caso de que la ejecución haya sido correcta. Para los casos en que la ejecución de la sentencia viole alguna restricción de la BD y por tanto, su ejecución no sea correcta, el resultado indicará cuál es la restricción violada. El SGBD, cada vez que insertamos un nuevo dato en una tabla, se encarga de verificar las restricciones activas, en nuestro caso las claves primarias, que como sabemos, no admiten valores duplicados, ni valores nulos.

Alta un nuevo profesor con los siguientes datos:

• dni 66

• nombre JUAN JUANÍTEZ

• categoría XXX

• fecha de incorporación 01/01/1905

insert into profesores values ('66','JUAN JUANITEZ','XXX','1905-1-1')

El sistema intentará asignar a las columnas no indicadas el valor por defecto, si se ha definido, o  valor nulo. 

Es recomendable acostumbrarse a poner siempre las columnas a las que se va a dar valor, aunque vayan a ser todas las de la tabla. Las razones que lo aconsejan son:

• No habrá que fijarse en si se va a dar valor a todas o sólo a alguna de las columnas para acomodar la sintaxis de la sentencia INSERT.

• Si por alguna razón se modifica la estructura de una tabla, es decir, se añaden columnas nuevas, y tenemos costumbre de no indicar las columnas cuando se inserta valor a todas, con la modificación dejarán de funcionar las sentencias de inserción que tuviéramos guardadas.

Inserción de más de una fila

Supongamos que en la BD Ejemplo existiera una tabla llamada OPTATIVAS que contuviera los códigos y los créditos de aquellas asignaturas de carácter optativo. 

Existe la posibilidad de insertar el resultado de una consulta en lugar de indicar la lista concreta de valores a insertar. Esto nos permite insertar varias filas en una tabla con una sola operación, en concreto, tantas filas como tuplas devuelva la orden SELECT.

INSERT INTO nombreTabla [ ( listaColumnas ) ] consulta 

Supongamos que serán optativas todas las asignaturas que tengan menos de 9 créditos. Se trata de introducir los códigos de dichas asignaturas en la tabla OPTATIVAS. En este caso, como ya tenemos las asignaturas en la tabla ASIGNATURAS, tenemos dos opciones. Una opción es, hacer la SELECT e ir haciendo las INSERT una a una, copiando los datos de las filas obtenidas. 

Delete

Antes de comenzar, limpiemos la base de datos

drop table if exists optativas;

drop table if exists imparte;

drop table if exists profesores;

drop table if exists asignaturas;

create table profesores (

DNI varchar(10),

nombre varchar(40),

categoria char(4),

ingreso date,

primary key (DNI));

create table asignaturas (

codigo char(5),

descripcion varchar(35),

creditos decimal(3,1),

creditosp decimal(3,1),

primary key (codigo));

create table imparte (

dni varchar(10),

asignatura char(5),

primary key (dni,asignatura),

foreign key (dni) references profesores (dni),

foreign key (asignatura) references asignaturas (codigo));

insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('HI', 'HISTORIA DE LA INFORMATICA', 4.5, NULL), ('FBD', 'FUNDAMENTOS DE LAS BASES DE DATOS', 6.0, 1.5), ('DGBD', 'DISEÑO Y GESTION DE BASES DE DATOS', 6.0, 3.0), ('PC', 'PROGRAMACION CONCURRENTE', 6.0, 1.5), ('FP', 'FUNDAMENTOS DE LA PROGRAMACION', 9.0, 4.5);

insert into profesores (dni, nombre, categoria, ingreso) values ('21111222','EVA GOMEZ','TEU','1993-10-01'), ('21222333','MANUEL PALOMAR','TEU','1989-06-16'), ('21333444','RAFAEL ROMERO','ASO6','1992-06-16');

insert into imparte (dni, asignatura) values ('21111222','FBD'), ('21111222','DGBD'), ('21333444','PC');

La sentencia DELETE nos permite borrar las filas contenidas en una tabla.

DELETE [FROM] nombreTabla [WHERE condición]

Para borrar filas de varias tablas habrá que ejecutar tantas sentencias DELETE como tablas tengamos como objetivo.

(Nota: en MariaDB/MySQL se puede borrar sobre varias tablas pero no se puede asegurar que otros SGBD lo permitan también)

Update

Antes de comenzar, reconstruyamos la base de datos:

delete from imparte;

delete from profesores;

delete from asignaturas;

insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('HI', 'HISTORIA DE LA INFORMATICA', 4.5, NULL), ('FBD', 'FUNDAMENTOS DE LAS BASES DE DATOS', 6.0, 1.5), ('DGBD', 'DISEÑO Y GESTION DE BASES DE DATOS', 6.0, 3.0), ('PC', 'PROGRAMACION CONCURRENTE', 6.0, 1.5), ('FP', 'FUNDAMENTOS DE LA PROGRAMACION', 9.0, 4.5);

insert into profesores (dni, nombre, categoria, ingreso) values ('21111222','EVA GOMEZ','TEU','1993-10-01'), ('21222333','MANUEL PALOMAR','TEU','1989-06-16'), ('21333444','RAFAEL ROMERO','ASO6','1992-06-16');

insert into imparte (dni, asignatura) values ('21111222','FBD'), ('21111222','DGBD'), ('21333444','PC');

La sentencia UPDATE nos permite modificar la información contenida en una tabla.

UPDATE nombreTabla [aliasTabla]

SET { {columna=expresion | columna=subconsulta} | listaColumnas=subconsulta}

[WHERE condición] 

No se pueden modificar varias tablas a la vez en una misma sentencia. Para modificar los valores de varias tablas habrá que ejecutar tantas sentencias UPDATE como tablas queramos modificar.

Existe la posibilidad de modificar la información contenida en una tabla asignando como nuevo valor o valores, el resultado de una consulta.

El resultado de la consulta puede asignarse a una única columna o a una lista de columnas. En el primer caso, la sentencia SELECT sólo devolverá un valor (una fila y una columna) el cual debe coincidir en tipo de dato y longitud con el tipo de dato y longitud de la columna a la cual asignamos el valor.