sql curso

T01 Concepto de Base de Datos y Relación

Contenidos

1. 1 Base de Datos: Ejemplo

1. 1.1 Extensiones de Ejemplo:

2. 2 Elección de la BD de trabajo

3. 3 Tipos de datos

Una base de datos es un conjunto de información interrelacionada que representa un sistema de información particular, y ESTÁ  compuesta por relaciones, o más comúnmente tablas, que almacenan los datos referentes a un objeto o a una interrelación ENTRE  objetos.

Así, si queremos mantener mediante un gestor de bases de datos información docente, lo que haremos (en este caso en particular) será crear una base de datos que englobe tres tablas: PROFESORES, ASIGNATURAS e IMPARTE. Cada tabla tendrá sus columnas, que representan los correspondientes atributos de la entidad o claves ajenas que permiten relacionar varias tablas entre sí. La BD que gestione esta información se llamará Ejemplo, y las tablas contenidas en ella se presentan en el siguiente cuadro.

Base de Datos: Ejemplo

PROFESORES ( dni : varchar(10), nombre : varchar(40), categoria : char(4), ingreso : date )Clave primaria: dni

ASIGNATURAS ( codigo : char(5), descripcion : varchar(35), CREDITOS  : number(3,1), creditosp : number(3,1) )Clave primaria: codigo

IMPARTE ( dni : varchar(10), asignatura : char(5) )Clave primaria: (dni, asignatura)Clave ajena: dni → PROFESORESClave ajena: asignatura → ASIGNATURAS

Extensiones de Ejemplo:

PROFESORES

dni NOMBREcategoria

ingreso

21111222

EVA GOMEZ TEU 1993-10-01

21222333

MANUEL PALOMAR

TEU 1989-06-16

21333444

RAFAEL ROMERO

ASO6 1992-06-16

ASIGNATURAScodigo descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

HI HISTORIA DE LA INFORMATICA 4.5  

PC PROGRAMACION CONCURRENTE 6.0 1.5

IMPARTEdni asignatura

21111222 DGBD

21111222 FBD

21333444 PC

Elección de la BD de trabajoMySQL utiliza el concepto de base de datos como contenedores independientes de tal forma que para acceder a una tabla en particular se pueden usar dos alternativas:

Acompañar al NOMBRE  de tabla con la base de DATOS  a la que pertenece: select * from ejemplo.profesoresSeleccionar la base de datos: use ejemplo

La opción 2 permite realizar las consultas sin especificar la base de datos en la que están definidas las tablas.

Tipos de datosEn general, la utilización de varias tablas necesita que ellas se puedan relacionar por una columna común, en este caso dni de profesor, para la relación entreimparte y profesor, y código de asignatura, para la relación entre asignatura e imparte. Nótese, sin embargo, que en la tabla imparte el código de asignatura se llama asignatura y en la tabla asignaturas código. En realidad, tales atributos son “comunes” porque el dominio es el mismo para ambos y se PUEDEN comparar. Los dominios vienen definidos por los TIPOS  de datos que ofrece el SGBD.

Los tipos de datos que acompañan en el esquema de BD a cada columna en cada tabla determinan los valores que pueden tomar éstas. Son de capital importancia a la hora de relacionar tablas en una sentencia select, puesto que sólo podremos comparar columnas con idéntico tipo de datos, o a la hora de manipular datos, dado que, como veremos en próximas sesiones, cada tipo de datos presenta unos requisitos específicos para su manipulación.

Algunos de los tipos de datos que nos podemos encontrar en MySQL son:

VARCHAR(x): cadena de caracteres de longitud variable con un máximo de x (1<=x<=4000) CHAR(x): cadena de caracteres de longitud fija de longitud n (1<=x<=2000) INT,INTEGER: NÚMEROS  enteros DECIMAL(p,s): números con precisión p y escala s (1<=p<=38) (-84<=s<=127) DATE : datos de tipo FECHA , con la forma yyyy-mm-dd (año, mes y día). Los valores date deben manejarse encerrados entre

comillas simples. 

T02 Consultas

Contenidos

1. 1 Select-from

2. 2 Where

3. 3 Order by

Select-from

PARA  realizar consultas sobre una base de DATOS  vamos a utilizar la orden select de SQL. En este momento veremos la expresión mínima de la orden, formada por dos cláusulas, select y from, que obligatoriamente tendremos que especificar en cada consulta que realicemos.

Vamos a recuperar toda la información que se ENCUENTRA  en la tabla profesores:

select * from profesores

dni NOMBRE categoria ingreso

21111222 EVA GOMEZ TEU 1993-10-01

21222333 MANUEL PALOMAR TEU 1989-06-16

21333444 RAFAEL ROMERO ASO6 1992-06-16

Al especificar en la lista de columnas un asterisco le indicamos al SGBD que deseamos la información de todas las columnas definidas para la tabla profesores.

select categoria from profesores

categoria

TEU

TEU

ASO6

AHORA  solo hemos obtenido la columna categoria desechando la salida de las otras columnas. Podemos pedir, eso sí, cuantas columnas queramos y estén definidas en la tabla.

select nombre, categoria from profesores

nombre categoria

EVA GOMEZ TEU

MANUEL PALOMAR TEU

RAFAEL ROMERO ASO6

Una opción de la que podemos HACER  uso es la eliminación de duplicados en la salida usando el modificador distinct.

select distinct categoria from profesores

categoria

TEU

ASO6

Where

Con la orden select-from obtenemos la información de las columnas requeridas de toda la tabla. Si únicamente

queremos información de aquellas filas que cumplen una determinada CONDICIÓN  utilizaremos la cláusula where.

select NOMBREfrom profesores where categoria = 'TEU'

nombre

EVA GOMEZ

MANUEL PALOMAR

En la construcción de tales condiciones podemos utilizar las conectivas lógicas AND, OR, y NOT, así COMO  los paréntesis para alterar la evaluación de izquierda a derecha. También, los operadores de comparación >, <, >=, <=, <>, !=. Ante la duda, hay que consultar la tabla de precedencia de operadores, que en el caso de MySQL se puede encontrar aquí: :  no es lo mismo "A and B or C" que "A and (B or C)".

select nombre from profesores where categoria = 'TEU' or categoria = 'ASO6'

nombre

EVA GOMEZ

MANUEL PALOMAR

RAFAEL ROMERO

Order by

Podemos ordenar la salida producida por nuestra orden select por valores ascendentes o descendentes de una columna en particular.

select CREDITOS , descripcionfrom asignaturasorder by CREDITOS

CREDITOS descripcion

4.5 HISTORIA DE LA INFORMATICA

6.0 DISEÑO Y GESTION DE BASES DE DATOS

6.0 FUNDAMENTOS DE LAS BASES DE DATOS

6.0 PROGRAMACION CONCURRENTE

9.0 FUNDAMENTOS DE LA PROGRAMACION

El resultado anterior estaba ordenado ascendentemente. Se puede especificar DESC para hacer la ordenación de forma descendente (igual que se puede hacerASC para ascendente, aunque no es necesario, como ya se ha visto)

select CREDITOS , descripcion from asignaturas where creditos > 4.5 order by creditos DESC 

creditos descripcion

9.0 FUNDAMENTOS DE LA PROGRAMACION

6.0 DISEÑO Y GESTION DE BASES DE DATOS

6.0 FUNDAMENTOS DE LAS BASES DE DATOS

6.0 PROGRAMACION CONCURRENTE

PUEDEN  aplicarse criterios más complejos de ordenación. El siguiente es un ejemplo de ordenación por dos atributos: a igualdad de los valores del primer atributo, el orden lo determinan los valores del segundo. Nótese que es posible combinar en el criterio de ordenación distintos tipos de datos.

select creditos, descripcion from asignaturas order by creditos, descripcion

creditos descripcion

4.5 HISTORIA DE LA INFORMATICA

6.0 DISEÑO Y GESTION DE BASES DE DATOS

6.0 FUNDAMENTOS DE LAS BASES DE DATOS

6.0 PROGRAMACION CONCURRENTE

9.0 FUNDAMENTOS DE LA PROGRAMACION

T02B Consultas 2

Contenidos

1. 1 Nulos (NULL)

2. 2 Constantes

3. 3 Uso de más de una tabla

4. 4 Nombres cualificados de atributo

5. 5 Sinónimos temporales de tabla

Nulos (NULL)

Las BD relacionales TRABAJAN  con un valor especial, NULL, que significa "ignorancia", se desconoce si tiene valor o no, y en el caso de tenerlo, cuál es.

Nótese que NULL no es "cadena vacía" ni "blanco"; éstos son valores concretos, pertenecientes al tipo de DATOScadena de caracteres.

Normalmente, se simplifica su significado dejándolo en "no tiene valor" aunque es discutible.

A la pregunta de "asignaturas que no tienen CRÉDITOS  prácticos" todas estas soluciones dan resultados vacíos o erróneos

select *from asignaturaswhere creditosp = ''

--comilla-simple + comilla-simple = cadena vacía

select *from asignaturas

where creditosp = ' '

--comilla-simple + espacio + comilla-simple = espacio_en_blanco 

select *from asignaturaswhere creditosp = 0

select *from asignaturaswhere creditosp = NULL

Lo que estamos buscando realmente es

select *from asignaturaswhere creditosp is NULL

codigo descripcion CREDITOS creditosp

HI HISTORIA DE LA INFORMATICA 4.5  

Efectivamente, hay una asignatura que no tiene CRÉDITOS  prácticos. Esto se debe a que, en la carga de la BD, se introdujo el valor NULL en la columnacreditosp de la fila de la asignatura HI.

Nótese que ni tan siquiera es posible utilizar la comparación habitual (signo "igual"), es obligado utilizar el operador IS NULL o IS NOT NULL. 

Constantes

Se pueden explicitar constantes en la orden select de forma que dicho valor aparezca en todas las filas.

select 'La asignatura ', descripcion, ' tiene ', CREDITOS , ' CRÉDITOS ' from asignaturas order by creditos

La asignatura descripcion tienecreditos

créditos

La asignatura HISTORIA DE LA INFORMATICA tiene 4.5 créditos

La asignatura DISEÑO Y GESTION DE BASES DE DATOS tiene 6.0 créditos

La asignatura FUNDAMENTOS DE LAS BASES DE DATOS tiene 6.0 créditos

La asignatura PROGRAMACION CONCURRENTE tiene 6.0 créditos

La asignatura FUNDAMENTOS DE LA PROGRAMACION tiene 9.0 créditos

Uso de más de una tabla

Para la resolución de la mayoría de requerimientos es necesario trabajar con información que se obtiene de relacionar varias tablas. La forma de especificar qué tablas vamos a consultar es construir una lista de NOMBRES

 de tablas en la cláusula from.

Si seleccionamos la BD Ejemplo, podemos preguntar por el

NOMBRE  de los profesores y la descripción de las asignaturas que imparten

¿Dónde está la información solicitada?

Consultando el esquema de la base de datos Ejemplo:

NOMBRE  (del profesor) se ENCUENTRA  en la tabla PROFESORES y descripción (de la asignatura) en ASIGNATURAS.Si no lo pensamos mucho podríamos ejecutar la siguiente orden confiando en el todopoderoso SGBD:

select nombre, descripcion from asignaturas, profesores

nombre descripcion

EVA GOMEZ DISEÑO Y GESTION DE BASES DE DATOS

MANUEL PALOMAR DISEÑO Y GESTION DE BASES DE DATOS

RAFAEL ROMERO DISEÑO Y GESTION DE BASES DE DATOS

EVA GOMEZ FUNDAMENTOS DE LAS BASES DE DATOS

MANUEL PALOMAR FUNDAMENTOS DE LAS BASES DE DATOS

RAFAEL ROMERO FUNDAMENTOS DE LAS BASES DE DATOS

EVA GOMEZ FUNDAMENTOS DE LA PROGRAMACION

MANUEL PALOMAR FUNDAMENTOS DE LA PROGRAMACION

RAFAEL ROMERO FUNDAMENTOS DE LA PROGRAMACION

EVA GOMEZ HISTORIA DE LA INFORMATICA

MANUEL PALOMAR HISTORIA DE LA INFORMATICA

RAFAEL ROMERO HISTORIA DE LA INFORMATICA

EVA GOMEZ PROGRAMACION CONCURRENTE

MANUEL PALOMAR PROGRAMACION CONCURRENTE

RAFAEL ROMERO PROGRAMACION CONCURRENTE

Obviamente, el resultado anterior no se corresponde con la información solicitada, hemos hecho un producto cartesiano entre dos conjuntos, la combinación de todos los nombres de profesor con todas las descripciones de asignatura.

En este caso, la relación entre profesores y asignaturas se encuentra en imparte que tiene 2 claves ajenas, una está asociada a la clave primaria de profesor y la otra a la clave primaria de asignaturas. Pensemos en imparte como un "puente" que nos permite enlazar la información de la primera tabla con la segunda: deprofesores pasamos a imparte mediante el dni, y de imparte a asignaturas mediante el código de la asignatura PROFESORES ← dni=dni → IMPARTE ←asignatura=codigo → ASIGNATURAS

Necesitamos, por tanto, incluir la tabla imparte en el from, y especificar en el where las CONDICIONES  para concatenar las tuplas deseadas.

Nombre de los profesores y descripción de las asignaturas que imparten(lo que debemos mostar): select nombre, descripcion (donde ESTÁ  la información necesaria): from asignaturas, profesores, imparte (igualando claves ajenas y claves primarias): where imparte.dni = profesores.dni and asignatura = codigo

nombre descripcion

EVA GOMEZ DISEÑO Y GESTION DE BASES DE DATOS

EVA GOMEZ FUNDAMENTOS DE LAS BASES DE DATOS

RAFAEL ROMERO

PROGRAMACION CONCURRENTE

Para entender mejor CÓMO  se obtienen los resultados de una consulta podemos pensar que el orden de ejecución es

from asignaturas, profesores, imparte (producto cartesiano)where profesores.dni = imparte.dni and asignatura = codigo (selección)select nombre, descripcion (proyección)1. select * from asignaturas, profesores, imparte

codigo descripcion CREDITOS creditosp dni NOMBRE categoria ingreso dni asignatura

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21111222

EVA GOMEZ TEU 1993-10-01 21111222

DGBD

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

DGBD

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

DGBD

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21111222

EVA GOMEZ TEU 1993-10-01 21111222

FBD

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

FBD

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

FBD

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21111222

EVA GOMEZ TEU 1993-10-01 21333444

PC

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21222333

MANUEL PALOMAR TEU 1989-06-16 21333444

PC

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21333444

RAFAEL ROMERO ASO6 1992-06-16 21333444

PC

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21111222

EVA GOMEZ TEU 1993-10-01 21111222

DGBD

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

DGBD

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

DGBD

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21111222

EVA GOMEZ TEU 1993-10-01 21111222

FBD

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

FBD

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

FBD

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21111222

EVA GOMEZ TEU 1993-10-01 21333444

PC

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21333444

PC

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21333444

PC

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21111222

EVA GOMEZ TEU 1993-10-01 21111222

DGBD

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

DGBD

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

DGBD

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21111222

EVA GOMEZ TEU 1993-10-01 21111222

FBD

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

FBD

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

FBD

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21111222

EVA GOMEZ TEU 1993-10-01 21333444

PC

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21333444

PC

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21333444

PC

HI HISTORIA DE LA INFORMATICA 4.5 21111222

EVA GOMEZ TEU 1993-10-01 21111222

DGBD

HI HISTORIA DE LA INFORMATICA 4.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

DGBD

HI HISTORIA DE LA INFORMATICA 4.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

DGBD

HI HISTORIA DE LA INFORMATICA 4.5 21111222

EVA GOMEZ TEU 1993-10-01 21111222

FBD

HI HISTORIA DE LA INFORMATICA 4.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

FBD

HI HISTORIA DE LA INFORMATICA 4.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

FBD

HI HISTORIA DE LA INFORMATICA 4.5 21111222

EVA GOMEZ TEU 1993-10-01 21333444

PC

HI HISTORIA DE LA INFORMATICA 4.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21333444

PC

HI HISTORIA DE LA INFORMATICA 4.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21333444

PC

PC PROGRAMACION CONCURRENTE 6.0 1.5 21111222

EVA GOMEZ TEU 1993-10-01 21111222

DGBD

PC PROGRAMACION CONCURRENTE 6.0 1.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

DGBD

PC PROGRAMACION CONCURRENTE 6.0 1.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

DGBD

PC PROGRAMACION CONCURRENTE 6.0 1.5 21111222

EVA GOMEZ TEU 1993-10-01 21111222

FBD

PC PROGRAMACION CONCURRENTE 6.0 1.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21111222

FBD

PC PROGRAMACION CONCURRENTE 6.0 1.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21111222

FBD

PC PROGRAMACION CONCURRENTE 6.0 1.5 21111222

EVA GOMEZ TEU 1993-10-01 21333444

PC

PC PROGRAMACION CONCURRENTE 6.0 1.5 21222333

MANUEL PALOMAR TEU 1989-06-16 21333444

PC

PC PROGRAMACION CONCURRENTE 6.0 1.5 21333444

RAFAEL ROMERO ASO6 1992-06-16 21333444

PC

2. select * from asignaturas, profesores, imparte where profesores.dni = imparte.dni and asignatura = codigo

codigo

descripcion CREDITOS creditosp dni NOMBRE categoria ingreso dni asignatura

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0 21111222 EVA GOMEZ TEU 1993-10-01

21111222 DGBD

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5 21111222 EVA GOMEZ TEU 1993-10-01

21111222 FBD

PC PROGRAMACION CONCURRENTE 6.0 1.5 21333444 RAFAEL ROMERO ASO6 1992-06-16

21333444 PC

3. select nombre, descripcion from asignaturas, profesores, imparte where profesores.dni=imparte.dni and asignatura=codigo

nombre descripcion

EVA GOMEZ DISEÑO Y GESTION DE BASES DE DATOS

EVA GOMEZ FUNDAMENTOS DE LAS BASES DE DATOS

RAFAEL ROMERO PROGRAMACION CONCURRENTE

Resumiendo, podemos decir que

1. from establece la fuente de datos,2. where la información objetivo en bruto, y3. select la extracción de la información deseada. 

IMPORTANTE: Esto no es necesariamente real, una de las ventajas de utilizar un SGBD es que las consultas se procesan de manera eficiente y de FORMA totalmente transparente para el usuario. Es sólo una forma de comprender las

acciones básicas que representa cada parámetro de la orden select.

Nombres cualificados de atributo

Un NOMBRE  cualificado de atributo es el que especifica el nombre de la tabla a la que pertenece la columna:

profesores.dniasignaturas.descripción

Es obligatorio utilizar nombres cualificados de atributo si hay ambigüedad, si varias tablas de la select TIENEN  columnas que se llaman igual:

profesores.dniimparte.dni

En cualquier otro caso no es necesario.

DNI y nombre de los profesores que imparten alguna asignatura

select profesores.dni, nombre from profesores, imparte where profesores.dni = imparte.dni

dni nombre

21111222

EVA GOMEZ

21111222

EVA GOMEZ

21333444

RAFAEL ROMERO

Igual que el anterior PERO  no cualificando el dni de la proyección

select dni, nombrefrom profesores, imparte where profesores.dni = imparte.dni

Error at Command Line:3 Column:0Error report:SQL Error: Column 'dni' in field list is ambiguous

También es útil cuando, a PARTIR  de varias tablas, se quieren todas las columnas de una y sólo alguna de las otras

select profesores.*, descripcionfrom profesores, asignaturas, impartewhere profesores.dni = imparte.dniand codigo = asignatura 

dni nombrecategoria

ingreso descripcion

21111222 EVA GOMEZ TEU 1993-10-01 DISEÑO Y GESTION DE BASES DE DATOS

21111222 EVA GOMEZ TEU 1993-10-01 FUNDAMENTOS DE LAS BASES DE DATOS

21333444 RAFAEL ROMERO ASO6 1992-06-16 PROGRAMACION CONCURRENTE

Sinónimos temporales de tabla

select * from tabla alias

Una cadena de caracteres a CONTINUACIÓN  del nombre de la tabla en el from, es un alias temporal, un nombre sustitutivo.Es recomendable para simplificar la escritura de la orden select o para hacerla más legible.Es obligatorio para un producto cartesiano de una tabla por si misma: select p1.NOMBRE , p2.nombrefrom profesores p1, profesores p2where p1.nombre <> p2.nombre

nombre nombre

MANUEL PALOMAR EVA GOMEZ

RAFAEL ROMERO EVA GOMEZ

EVA GOMEZ MANUEL PALOMAR

RAFAEL ROMERO MANUEL PALOMAR

EVA GOMEZ RAFAEL ROMERO

MANUEL PALOMAR RAFAEL ROMERO

Cuando se define el sinónimo en el from, sustituye totalmente al nombre de la tabla (no se permite utilizar los dos simultáneamente en el select o el where)Por otro lado, no es necesario definir sinónimos para todas las tablas del from.

DNI y nombre de los profesores que imparten alguna asignatura (utilizando sinónimos temporales de tabla)

select p.dni, nombrefrom profesores p, imparte iwhere p.dni = i.dni 

dni nombre

21111222

EVA GOMEZ

21111222

EVA GOMEZ

21333444

RAFAEL ROMERO

T03 Expresiones de selección de filas

Contenidos

1. 1 Rangos

2. 2 Listas

3. 3 Subcadenas de caracteres

4. 4 Consultas anidadas

1. 4.1 de valor escalar

2. 4.2 de lista de valores

Rangos

Expresiones del tipo 10 <= x <= 100 se pueden construir utilizando el operador de construcción de rangos BETWEEN. La sintaxis de tal subexpresión de la cláusula where es la siguiente:

expresión [NOT] BETWEEN expresión AND expresión 

Créditos y descripción de las asignaturas cuyo número DE CRÉDITOS  está entre 5 y 8. 

select CREDITOS , descripcion from asignaturas where CREDITOS  between 5 and 8 

CREDITOS descripcion

6.0 DISEÑO Y GESTION DE BASES DE DATOS

6.0 FUNDAMENTOS DE LAS BASES DE DATOS

6.0 PROGRAMACION CONCURRENTE

Listas

Mediante el operador IN se puede buscar un determinado valor en una lista construida usando constantes. 

expresión [NOT] IN (listaValores) 

Descripción de las asignaturas FBD y DGBD. 

select descripcion from asignaturas where codigo in ('FBD', 'DGBD')

descripcion

DISEÑO Y GESTION DE BASES DE DATOS

FUNDAMENTOS DE LAS BASES DE DATOS

NOMBRE  de los profesores que no imparten HI, FBD o DGBD. 

select nombre from profesores p, imparte i where p.dni = i.dni and asignatura not in ('HI', 'FBD', 'DGBD')

nombre

RAFAEL ROMERO

Fijémonos en que MANUEL PALOMAR, que no imparte ninguna de las asignaturas objeto de la búsqueda, tampoco aparece en la tabla resultado puesto que su dni no aparece en la tabla IMPARTE. 

Subcadenas de caracteres

Podemos preguntar por subcadenas dentro de columnas de tipo carácter. Para ello utilizaremos los operadores LIKE (o MATCHES ), que soportan la siguiente sintaxis: 

expresión [NOT] LIKE 'cadena' 

La cadena de caracteres cadena admite los comodines % (tanto por ciento) y _ (subrayado): 

% indica una cadena de caracteres de cualquier longitud (Ali% = Alicante, Aligerar, Ali, ...) _ un carácter cualquiera (Ali_ = Alic, Alig, Ali, ...)

Profesores que atiendan al nombre de 'RAFA'. 

select * from profesores where nombre like 'RAFA%' 

dni nombre categoria ingreso

21333444 RAFAEL ROMERO

ASO6 1992-06-16

Código de las asignaturas de 'Bases de Datos' 

select codigo from asignaturas where descripcion like '%BASES DE DATOS%' 

codigo

DGBD

FBD

Código de las asignaturas, siendo tal código de 2 caracteres 

select codigo from asignaturas where codigo like '__' 

codigo

FP

HI

PC

Descripción de las asignaturas cuya última palabra contiene 'INFORMATIC' y un caracter adicional. 

select descripcion from asignaturas where descripcion like '%INFORMATIC_'

descripcion

HISTORIA DE LA INFORMATICA

Consultas anidadas

A veces, las condiciones de filtrado de tuplas o de cálculos de agregación son más complejas que la simple comparación con una constante o un valor almacenado en una tabla. Este DATO  intermedio, no un resultado final, ha de obtenerse mediante una consulta auxiliar, una subconsulta. 

En la condición de filtrado de la orden select (en la cláusula where o en la having) también podemos:

comparar una expresión con el resultado de otra orden select determinar si el valor de una expresión está incluido en los resultados de otra orden select preguntar si una orden select ha obtenido alguna fila.

Descripción y créditos de las asignaturas con menos créditos.

select descripcion, CREDITOSfrom asignaturaswhere CREDITOS  = ( select min(creditos) from asignaturas )

descripcion creditos

HISTORIA DE LA INFORMATICA

4.5

En primer lugar se calcula la select anidada (entre paréntesis) y se obtiene el valor mínimo para la columna créditos de la tabla asignaturas. Con ese valor se compara tupla a tupla y se obtiene la asignatura (o asignaturas) cuya cantidad DE CRÉDITOS  es igual al mínimo. 

Las subconsultas, dependiendo del valor esperado y del tipo de comparación deseado se PUEDEN  utilizar de 3 formas:

expr opcompara {ALL|[ANY|SOME]} (orden select) expr [NOT] IN (orden select) [NOT] EXISTS (orden select)

donde opcompara es cualquier operador de comparación. 

Los valores posibles a devolver por una orden select anidada son :

nada un valor único (una fila y una columna), un conjunto de valores (varias filas y una columna).

Siempre que la subconsulta devuelva algo, únicamente será en una y nada más que una columna (salvo el operador EXISTS, que se verá más adelante).

de valor escalar

expr opcompara (orden select) 

Podemos utilizar los operadores de comparación para preguntar si el valor de una determinada expresión es

mayor, menor, igual, etc. que el resultado de la subconsulta, siempre y cuando ésta devuelva una única fila y una única columna, es decir, un valor escalar.

select descripcionfrom asignaturaswhere CREDITOS  = (select CREDITOS  from asignaturas where CREDITOS  < 9)

SQL Error: Subquery returns more than 1 row

Aunque también se puede utilizar cualquier consulta de la que estamos seguros que nos va a devolver una fila y una columna, lo más habitual es que sea el resultado de buscar un valor concreto de una clave primaria (en general, de cualquier combinación de columnas que no admita duplicados)

Asignaturas que tienen más CRÉDITOS  que la asignatura HI.

select * from asignaturaswhere creditos >(select creditosfrom asignaturaswhere codigo = 'HI')

codigo descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

PC PROGRAMACION CONCURRENTE 6.0 1.5

de lista de valores

expr opcompara ALL|[ANY|SOME] (orden select) 

Cuando la tabla resultado contiene más de una fila (pero una única columna, insistimos) hay que utilizar un modificador para el operador de comparación.

Asignaturas que tienen más créditos que las demás (asignaturas que tienen la máxima cantidad DE CRÉDITOS  en la base de datos)

select descripcionfrom asignaturaswhere CREDITOS  >= ALL (select CREDITOS  from asignaturas)

descripcion

FUNDAMENTOS DE LA PROGRAMACION

El NÚMERO  en créditos de la asignatura tiene que ser mayor o igual que todos (all) los valores obtenidos en la subconsulta (que es la relación DE CRÉDITOS de todas las asignaturas). También se podría haber formulado como

select descripcionfrom asignaturaswhere CREDITOS  = (select max(creditos) from asignaturas)

Nombre de las asignaturas que no son las que menos créditos tienen.

select descripcionfrom asignaturaswhere creditos > ANY (select creditos from asignaturas)

descripcion

DISEÑO Y GESTION DE BASES DE DATOS

FUNDAMENTOS DE LAS BASES DE DATOS

FUNDAMENTOS DE LA PROGRAMACION

PROGRAMACION CONCURRENTE

Serían todas aquellas asignaturas cuyos créditos superen al menos a uno (any) de los valores devueltos por la subconsulta. Los modificadores some y any son sinónimos. Una solución alternativa es

select descripcionfrom asignaturaswhere CREDITOS  != (select min(CREDITOS ) from asignaturas)

Nombre de los profesores que imparten una asignatura que no sea la máxima en número DE CRÉDITOS

select nombrefrom profesores p, asignaturas a, imparte iwhere p.dni = i.dni and i.asignatura = a.codigoand CREDITOS  < ANY ( select CREDITOS  from asignaturas )

nombre

EVA GOMEZ

EVA GOMEZ

RAFAEL ROMERO

NOTA IMPORTANTE:

Por un cambio de configuración que, a su vez, descubre un fallo de la versión INSTALADA  de MySQL, las expresiones expr opcompara {ALL|[ANY|SOME]} provocan un error de compilación y no pueden ejecutarse. Por fortuna, la mayor parte tienen que ver con "el máximo, el mínimo x" por lo que se puede utilizar la forma del ejemplo:

NO funcionará (en nuestra versión de MySQL)

select descripcion, creditosfrom asignaturaswhere creditos <= ALL( select creditos from asignaturas )

SÍ funcionara, y es equivalente en su resultado

select descripcion, creditosfrom asignaturaswhere creditos = ( select min(creditos) from asignaturas )

expr [NOT] IN (orden select) 

También podemos consultar la pertenencia de un valor a la lista de valores devuelta por la subconsulta.

Obtener todos los DATOS  de los profesores que imparte alguna asignatura.

select * from profesoreswhere dni IN (select dni from imparte)

dni NOMBRE categoria ingreso

21111222 EVA GOMEZ TEU 1993-10-01

21333444 RAFAEL ROMERO

ASO6 1992-06-16

O dicho de otra manera: "datos de los profesores cuyo dni aparece en la tabla imparte".

En este caso daría lo mismo procesar la orden

select p.* from profesores p, imparte iwhere p.dni = i.dni 

NOTA: el resultado no es exactamente el mismo porque el uso de IN consigue el mismo resultado que si hubiéramos aplicado el modificador distinct. Mientras que IN va recorriendo la tabla profesores y preguntando por cada uno si se ENCUENTRA  en la tabla imparte o no, la segunda realiza el producto cartesiano y descarta las filas en las que no coinciden los valores de DNI. Es decir, la segunda consulta muestra filas duplicadas (en este estado de base de datos) mientras que la anterior no. Lo que queremos decir, no obstante, es que ambas dan como resultado "profesores que imparten algo".

Se verá más clara la utilidad de este operador si preguntamos justo lo contrario.

Obtener todos los datos de los profesores que no imparten asignaturas.

select * from profesoreswhere dni NOT IN (select dni from imparte)

dni nombre categoria ingreso

21222333 MANUEL PALOMAR TEU 1989-06-16

Por último, veamos que algunos de estos operadores son equivalentes:expr IN (orden select) ≈ expr=ANY(orden select)expr NOT IN (orden select) ≈ expr<>ALL(orden select)

T04 Fechas y tiempo

Contenidos

1. 1 De cadenas de caracteres, números y fechas y tiempos

2. 2 Funciones de fecha y hora

1. 2.1 Ahora

2. 2.2 Formato

3. 2.3 Extracción

La administración y manejo de valores temporales es la parte de los motores de base de DATOS  menos estandarizada y con más diferencias entre uno y otro. Aparte, y junto con la codificación de caracteres y las características regionales (por ejemplo, formato de las FECHAS ) forma parte de un conjunto de parámetros globales del sistema que el administrador del mismo debe comprender y tener presente dependiendo del medio en el que se vayan a ver los resultados de una consulta. Si fuera a formar parte de un conjunto de páginas web dinámicas no solo el servidor de base de datos debe estar correctamente configurado sino que debe tener en CUENTA  el servidor http, el del lenguaje huesped (php, por ejemplo) e incluso los clientes (navegadores, ordenadores, etc.). Es muy posible que la misma fecha, en SQL Developer, vista en la pestaña resultados o en la script output tenga formato diferente (la primera está controlada por el programa cliente y la segunda muestra los datos tal cual se los envía el servidor.

Puestas así las cosas, aquí se va a tratar la superficie de todo lo que se puede hacer con fechas y tiempos y siempre desde el punto de vista estrictamente de la consulta SQL.

MySQL ofrece varios tipos de datos relacionados con el tiempo:

DATETIME     '0000-00-00 00:00:00'DATE     '0000-00-00'TIMESTAMP     00000000000000TIME     '00:00:00'YEAR     0000

Lo que se muestra en la lista anterior da una idea de qué datos maneja cada tipo. En realidad, lo anterior es la relación de "valores cero" que pueden almacenar y que tienen carácter de valor por defecto o valor de prueba. Dejando de lado el tipo TIMESTAMP que tiene unas propiedades y aplicaciones propias, todos los tipos están relacionados y difieren en cuanto a las limitaciones de almacenamiento y, más importante, de conversión automática de tipos.

De cadenas de caracteres, números y fechas y tiempos

Profesores que han ingresado antes de 1990.

select * from profesoreswhere ingreso < '1990-01-01';

dni NOMBRE categoria ingreso

21222333 MANUEL PALOMAR TEU 1989-06-16

Nótese que se está comparando un tipo date con una cadena de caracteres. Lo que ocurre es que  MySQL analiza la cadena y determina si tiene un formato adecuado para el tipo de datos y la procesa si así es. De hecho, aunque la salida genérica de una fecha siempre es aaaa-mm-dd, la cadena de caracteres que usamos para la comparación asume cierta libertad de formato:

ingreso < '1990@01@01'ingreso < '1990/01/01'ingreso < '1990.01.01'ingreso < '1990:01:01'

En todos los casos el resultado es idéntico al anterior. Lo que ya no funciona es 

select * from profesoreswhere ingreso < '01-01-1990'

0 rows selected

Y, además, no se genera ningún mensaje de error, simplemente, no muestra fila alguna en el resultado. Tampoco se comporta COMO  nosotros esperaríamos

select * from profesoreswhere ingreso < 1990-01-01;

0 rows selected

PERO  sí de 

select *from profesoreswhere ingreso < 19900101;

dni NOMBRE categoria ingreso

21222333 MANUEL PALOMAR TEU 1989-06-16

Otro aspecto en el que se da cierta libertad en es la FORMA  del año:

select * from profesoreswhere ingreso < '90-01-01';

dni nombre categoria ingreso

21222333 MANUEL PALOMAR TEU 1989-06-16

Ahora bien, hay que tener en CUENTA  que MySQL hace una interpretación de esa parte de la fecha de tal forma que AÑOS  en el rango 00-69 los convierte en 2000-2069, y los 70-99 en 1970-1999. Si manejamos años anteriores o posteriores debemos usar los 4 caracteres. 

Funciones de fecha y hora

En el manual de referencia de MySQL 5.0 PUEDE  consultarse la funcionalidad completa relativa al manejo del tiempo. Aquí solo expondremos algunas:

now(), curdate(), curtime()date_format(), str_to_date()day(), dayofweek(), dayname(), month(), year(), hour(), minute(), second()

Ahora

now() devuelve la FECHA  y hora del servidor en formato datetime. curdate() y curtime() hacen lo mismo PERO  con la fecha y la hora respectivamente.

select now(), curdate(), curtime();now() curdate() curtime()

2010-12-03 19:40:17 2010-12-03

19:40:17

Formato

Las funciones principales de formato podemos decir que son date_format() y str_to_date(). Una es la inversa de la otra: dateformat() transforma la fecha a un formato de texto determinado y str_to_date() una cadena de caracteres en un formato concreto a fecha. Las dos TRABAJAN  con 2 parámetros, una expresión y una cadena de formato.

La cadena de formato indica a la función que aspecto tiene o queremos que tenga (depende de si es una u otra función) el DATO  que le suministramos en el primer parámetro:

Profesores, con la fecha de ingreso en formato "dd/mm/aaaa".

select dni, NOMBRE , date_format(ingreso, '%d/%m/%Y') ingreso from profesores;

dni nombre ingreso

21111222 EVA GOMEZ 01/10/1993

21222333 MANUEL PALOMAR 16/06/1989

21333444 RAFAEL ROMERO 16/06/1992

Profesores que han ingresado antes de 1/1/1990.

select * from profesoreswhere ingreso < str_to_date('1/1/90','%d/%m/%y');.

dni nombre categoria ingreso

21222333 MANUEL PALOMAR TEU 1989-06-16

Así, la cadena de formato para la primera consulta representa "cómo queremos la salida" y la de la segunda "cómo está escrita la fecha que quiero comparar". La totalidad de los códigos posibles se PUEDE  consultar en la descripción de la función date_format() en http://dev.mysql.com/doc/refman/5.0/es/date-and-time-functions.html.

Extracción

Las otras funciones a las que vamos a PRESTAR  atención son las que extraen parte de la expresión temporal.

select day(ingreso) día, month(ingreso) mes, year(ingreso) año from profesoreswhere NOMBRE ='EVA GOMEZ';

díames

año

1 10 1993

select dayname(ingreso) día, dayofweek(ingreso) ndía, monthname(ingreso) mes from profesoreswhere nombre='EVA GOMEZ';

dnd

m

Friday 6 October

select date_format(now(),'%Y%m%d -- %H:%i:%s') AHORA ;

ahora

20101203 -- 20:12:49

T05 Reunión (join)

Contenidos

1. 1 Tipos de reunión

1. 1.1 Equijoin

2. 1.2 Self join

3. 1.3 Producto cartesiano

4. 1.4 Inner join, simple join

5. 1.5 Outer join

6. 1.6 Antijoin

7. 1.7 Semijoin

2. 2 Outer join

El uso de varias tablas en una consulta, y su concatenación siguiendo cualquier criterio, se conoce habitualmente COMO  join, el término en inglés adoptado y utilizado. Estamos hablando, por ejemplo, de: 

select * from asignaturas, profesores, impartewhere profesores.dni = imparte.dni and asignatura = codigo

Tipos de reunión

Dependiendo de qué tablas se especifiquen en el from y del TIPO  de condición exigida para relacionar las filas de esas tablas, el join recibe distintos NOMBRES  (terminología que se puede considerar estándar):

Equijoin

Consultas que conllevan el uso de igualdades PARA  la concatenación de filas de varias tablas. El ejemplo anterior es una equijoin. 

Self join

Estas consultas concatenan una tabla consigo misma: 

select i1.dni, ' imparte la misma asignatura que ', i2.dni from imparte i1, imparte i2 where i1.asignatura= i2.asignatura

dni imparte la misma asignatura que dni

21111222

imparte la misma asignatura que 21111222

21111222

imparte la misma asignatura que 21111222

21333444

imparte la misma asignatura que 21333444

Producto cartesiano

El producto cartesiano utiliza dos tablas sin la CONDICIÓN  de concatenación del where: 

select dni, codigo from profesores, asignaturas

dni codigo

21111222 DGBD

21222333 DGBD

21333444 DGBD

21111222 FBD

21222333 FBD

21333444 FBD

21111222 FP

21222333 FP

21333444 FP

21111222 HI

21222333 HI

21333444 HI

21111222 PC

21222333 PC

21333444 PC

Inner join, simple join

Realmente, se trata de la consulta habitual en SQL. Sin embargo, existe una sintaxis particular alternativa usando inner join: 

select NOMBRE , descripcion from asignaturasjoin imparte on (codigo=asignatura)join profesores on (imparte.dni=profesores.dni)

nombre descripcion

EVA GOMEZ DISEÑO Y GESTION DE BASES DE DATOS

EVA GOMEZ FUNDAMENTOS DE LAS BASES DE DATOS

RAFAEL ROMERO PROGRAMACION CONCURRENTE

El resultado será la concatenación de todas aquellas filas, y únicamente esas, que cumplen la CONDICIÓN  que las relaciona. Es una construcción alternativa a la que hemos venido utilizando hasta ahora que nos debe ser familiar: 

select NOMBRE , descripcionfrom asignaturas, profesores, impartewhere profesores.dni = imparte.dni and asignatura = codigo

Si acaso, PUEDE  tener cierta utilidad PARA  no olvidarnos de enlazar cada par de tablas y dejar el where para otro tipo de CONDICIONES :

select nombre, descripcion from asignaturas join imparte on (codigo=asignatura) join profesores on (imparte.dni=profesores.dni) where descripcion NOT LIKE 'PROGRAMACION%'

nombre descripcion

EVA GOMEZ DISEÑO Y GESTION DE BASES DE DATOS

EVA GOMEZ FUNDAMENTOS DE LAS BASES DE DATOS

Outer join

El outer join se diferencia del inner join en que las filas de una tabla que se muestran en el resultado no necesariamente TIENEN  su correspondiente fila o filas en la otra tabla. 

Por ejemplo, podríamos querer obtener todos los profesores y, si da alguna asignatura, el código de esas asignatura: select p.*, i.asignaturafrom profesores p left join imparte i on (p.dni=i.dni); 

dni nombrecategoria

ingreso asignatura

21111222

EVA GOMEZ TEU 1993-10-01 DGBD

21111222

EVA GOMEZ TEU 1993-10-01 FBD

21222333

MANUEL PALOMAR TEU 1989-06-16  

21333444

RAFAEL ROMERO ASO6 1992-06-16 PC

Más adelante se profundiza en el outer join. 

Antijoin

Estas consultas son las que utilizan el NOT IN para obtener aquellas filas de una tabla que no se relacionan con las de otra: 

select * from profesores where dni not in (select dni from imparte);

dni nombre categoria ingreso

21222333 MANUEL PALOMAR TEU 1989-06-16

Semijoin

Un semijoin devuelve filas que HACEN  cierta una subconsulta de un operador EXISTS sin duplicar filas. 

select * from profesores p where exists (select * from imparte i where i.dni = p.dni); 

dni NOMBRE categoria ingreso

21111222 EVA GOMEZ TEU 1993-10-01

21333444 RAFAEL ROMERO

ASO6 1992-06-16

El operador exists y este TIPO  de subconsultas se verán en sesiones más avanzadas.

Démonos CUENTA  que, prácticamente, lo único que hemos hecho es dar nombre a los distintos tipos de consultas, algunas ya utilizadas durante el curso. No es importante ese nombre sino entender las necesidades de cada consulta y cómo satisfacerla.

Sí es NUEVA , para este curso, la sintaxis del inner y el outer join. El primero, no hace falta desarrollarlo más, el segundo sí lo tratamos en la siguiente sección.

Téngase en cuenta, también, que éste no es un curso exhaustivo de SQL. Hay detalles de rendimiento que favorecen el uso de unos u otros tipos de consultas y, por supuesto, muchas más opciones a la hora de incrementar ese rendimiento sobre todo en entornos de medianas o grandes bases de DATOS .

Para más información, podéis consultar Oracle® Database SQL Reference 10g Release 2 (10.2), que ha sido la fuente de esta sesión. 

Outer join

Como ya hemos dicho, el outer join extiende el resultado de una consulta simple de, por ejemplo, dos tablas, obteniendo todas las filas que cumplen la condición de concatenación y, además, todas o algunas de las filas de una tabla que no cumplen tal condición. 

Supongamos dos tablas A y B: 

• select * from A left [outer] join B on (CONDICIÓN )Obtiene todas las filas relacionadas de A y B, y todas las no relacionadas de A. 

• select * from A right [outer] join B on (condición)Obtiene todas las filas relacionadas de A y B, y todas las no relacionadas de B. 

• select * from A full [outer] join B on (condición)(No soportado por MySQL) Obtiene todas las filas relacionadas de A y B, y todas las no relacionadas de A y B. 

BD EjemploPROFESORES ( dni varchar2(10), NOMBRE  varchar(40), categoria char(4), ingreso date )CP (dni)

ASIGNATURAS ( codigo char(5), descripcion varchar(35), CREDITOS  decimal(3,1), creditosp decimal(3,1) )CP (código)

IMPARTE ( dni varchar(10), asignatura char(5) )CP (dni, asignatura)CAj (dni) → PROFESORESCAj (asignatura) → ASIGNATURAS

COORDINADORES ( dni varchar(10), nombre varchar(40), dpto char(4), asig char(5) )CP (dni)CAj (asig) → ASIGNATURAS

Para ver mejor el funcionamiento de las distintas alternativas de join, vamos a trabajar con una tabla adicional, COORDINADORES, en nuestra base de DATOS Ejemplo.

Muestra todos los coordinadores y, si lo hacen, las asignaturas que coordinan. 

select * from coordinadores left join asignaturas on (codigo=asig);

dni nombre dpto asig codigo descripcion CREDITOS creditosp

55777666

AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION

9.0 4.5

66555444

ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  

99222111

CATURLO PEREZ DLSI          

Muestra los coordinadores que tienen asignatura y todas las asignaturas. 

select * from coordinadores right join asignaturas on (codigo=asig);

dni nombre dpto asigcodigo

descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

        FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5

55777666

AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

66555444

ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  

        PC PROGRAMACION CONCURRENTE 6.0 1.5

Muestra todos los coordinadores y todas las asignaturas y si hay relación entre ellos. 

select * from coordinadores full join asignaturas on (codigo=asig); 

Lo que se espera de un full join es que aparezcan todos los DATOS  de una y otra tabla, estén o no relacionados, más o menos, lo que se muestra a CONTINUACIÓN :

dni NOMBRE dpto asigcodigo

descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

55777666

AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

66555444

ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  

        FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5

        PC PROGRAMACION CONCURRENTE 6.0 1.5

99222111

CATURLO PEREZ DLSI          

Sin embargo, full join no está soportado por MySQL aunque sí por otros motores (Oracle PL/SQL) y si ejecutáramos la sentencia anterior el resultado es idéntico a un join simple. 

dni nombre dpto asig codigo descripcion CREDITOS creditosp

55777666

AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION

9.0 4.5

66555444

ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  

T06 Definición y manipulación de datos

Contenidos

1. 1   Definición de datos

1. 1.1   Create table

1. 1.1.1   Restricciones

2. 1.1.2   Motores de MySQL

2. 1.2   Drop table

3. 1.3   Información del catálogo

2. 2   Manipulación de datos

1. 2.1      Insert   

1. 2.1.1   Inserción de más de una fila

2. 2.2      Delete   

1. 2.2.1   Integridad referencial

3. 2.3      Update   

1. 2.3.1   Integridad referencial

3. 3   MySQL y delete y update

Definición de datos

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.

Supongamos que vamos a implementar un esquema de base de datos relacional de profesores, asignaturas (sólo es un listado de profesores y asignaturas, sin relaciones entre ellos). En primer lugar debemos decidir cuáles son los atributos de cada uno de ellos y sus tipos de datos:

 PROFESORES  ASIGNATURAS

DNI: varchar(10), NOMBRE : varchar(40),categoría: char(4),ingreso: date

código: char(5),descripción: varchar(35),CRÉDITOS : decimal(3,1),créditosp: decimal(3,1)

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 http://dev.mysql.com/doc/refman/5.0/es/column-types.html.

RestriccionesLas 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, sólo vamos a utilizar, de momento, la clave primaria, que puede contener tantas columnas COMO se necesiten:

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

Las siguientes órdenes crean las tablas PROFESORES y 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,foreign key (asignatura) references asignaturas);

Motores de MySQL

Lo dicho en la sección anterior constituye el estándar de SQL y es la sintaxis admitida en los SGBD de mayor penetración comercial.

Sin embargo, MySQL es un producto peculiar ya que ofrece varias opciones de almacenamiento y gestión de las tablas en aras de ofrecer alternativas que mejoren el rendimiento o la integridad de DATOS . En particular podemos elegir ENTRE  tablas MyISAM e InnoDB.

Si queremos mantener integridad referencial entre nuestras tablas es imprescindible que especifiquemos que queremos usar el motor InnoDB. En una INSTALACIÓN  estándar de MySQL, si no decimos nada, la creación de una tabla se hace por defecto en MyISAM y TIENE  un efecto importantísimo: si definimos claves ajenas, el sistema las ignora y no revisa la integridad referencial.

Por tanto, y puesto que queremos garantizar las restricciones de clave ajena, las definiciones anteriores no nos valen:

create table profesores (DNI varchar(10),NOMBRE  varchar(40),categoria char(4),ingreso date,primary key (DNI)) engine=innodb;

create table asignaturas (codigo char(5),descripcion varchar(35),CREDITOS  decimal(3,1),creditosp decimal(3,1),primary key (codigo)) engine=innodb;

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)) engine=innodb;

Es más, todas las tablas implicadas en una clave ajena han de estar definidas en este motor, tanto la que ALOJA  a la clave ajena como a la que hace refencia, y es obligatorio indicar la columna o columnas que constituyen la clave primaria en ESTA  segunda.

Drop table

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

DROP TABLE nombreTabla

Borra la tabla asignaturas

drop table asignaturas

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.

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.

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. PARA  establecer los nombres de las restricciones, se suele seguir el siguiente convenio: ser nombres descriptivos, que empiecen, por ejemplo, por

PK_ si se trata de una Primary Key FK_ si se trata de una Foreign Key C_ si se trata de una CHECK

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

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.

La sentecia DESCRIBE es, en realidad, un atajo para acceder a las tablas del catálogo mediante una sentencia select.  

Manipulación de datos

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 profesoresvalues ('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 profesoresvalues ('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 profesoresvalues ('66','JUAN JUANITEZ','XXX','1905-1-1')

A veces no nos interesa o no podemos darle valor a todas y cada una de las columnas, o lo vamos a hacer en un orden distinto al que tienen las columnas en el create table que la definió. Especificar una lista de columnas antes de VALUES permite decirle al sistema qué columnas van a tener valor y cuál es.

Da de alta a un profesor con DNI 88888888 y nombre ARMANDO SUÁREZ

insert into profesores (dni, nombre)

values ('88888888', 'ARMANDO SUAREZ');

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, sean o no 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 que tuviéramos escritas.

Existe la posibilidad de hacer uso del valor NULL, siempre que la columna lo admita. Aunque se suele simplificar por "ausencia de valor", recuérdese que NULL significa realmente ignorancia (no sabemos si tiene valor ni, si lo tuviera, cuál es). En cualquier caso, si la columna los admite se PUEDE  especificar en la orden de inserción:

insert into profesores (dni,nombre,categoría,ingreso)values ('88888888','ARMANDO SUAREZ', NULL, NULL);

En realidad el efecto es el mismo que el de la orden anterior propuesta (ejecutarla AHORA provocaría un error por duplicado en clave primaria).

Igualmente, el cambio de orden de las columnas se debe corresponder con la posición exacta del valor a asignar:

insert into profesores (categoria, dni, NOMBRE )values (NULL, '88888888','ARMANDO SUAREZ');

Inserción de más de una filaSupongamos 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.

Vamos a crear dicha tabla, eligiendo como clave primaria el código de la asignatura y poniendo además otra restricción, que todas las filas tengan un valor no nulo en la columna CRÉDITOS

create table optativas (asignatura char (5), CREDITOS  decimal(3,1) not null,primary key (asignatura)) engine=innodb;

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. Otra opción es insertar en una sola operación el resultado de la SELECT en la tabla OPTATIVAS.

insert into optativas (asignatura, CREDITOS )

select codigo, creditos from asignaturas where creditos < 9;

El resultado de la orden SELECT deberá ser coherente en cantidad de columnas y tipos de datos. Las siguientes órdenes provocarán errores de compilación:

insert into optativas (asignatura) 

select codigo, CREDITOS  from asignaturas where CREDITOS  < 9;

insert into optativas (asignatura,CREDITOS ) 

select codigo from asignaturas where CREDITOS  < 9;

insert into optativas (asignatura,creditos) 

select dni,ingreso from profesores;

Este caso ya es diferente, hay que asegurarse de que el resto de columnas permiten la inserción:

insert into optativas (asignatura) select codigo from asignaturas where creditos < 9

La restricción NOT NULL sobre la columna creditos impide que se realice la inserción de filas, para asegurar la integridad de los datos, evitando que se pongan valores nulos en esa columna.

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)) engine=innodb;

create table asignaturas (codigo char(5),descripcion varchar(35),creditos decimal(3,1),creditosp decimal(3,1),primary key (codigo)) engine=innodb;

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)) engine=innodb;

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

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

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

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

DELETE [FROM] nombreTabla [WHERE CONDICIÓN ]

Borrar todas las filas de la tabla IMPARTE

-- para ver lo que hay ahoraselect * from imparte; 

dni asignatura

21111222 DGBD

21111222 FBD

21333444 PC

delete from imparte;

-- para ver el resultadoselect * from imparte; 

dni asignatura

0 filas seleccionadas

Borra todas las asignaturas de menos de 5 créditos

delete from asignaturas where creditos < 5;

PARA borrar filas de varias tablas habrá que ejecutar tantas sentencias DELETE COMO de tablas queramos borrar.

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

Integridad referencial 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);insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('FBD', 'FUNDAMENTOS DE LAS BASES DE DATOS', 6.0, 1.5);insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('DGBD', 'DISEÑO Y GESTION DE BASES DE DATOS', 6.0, 3.0);insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('PC', 'PROGRAMACION CONCURRENTE', 6.0, 1.5);insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('FP', 'FUNDAMENTOS DE LA PROGRAMACION', 9.0, 4.5);

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

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

La integridad referencial es la propiedad de las claves ajenas que nos asegura que todas las referencias desde una hacia otra tabla son consistentes.

La tabla imparte relaciona profesores y asignaturas informando de qué profesores imparten qué asignatura:

    IMPARTE ( dni : varchar(10), asignatura : char(5) )    Clave primaria: (dni, asignatura)    Clave ajena: dni → PROFESORES    Clave ajena: asignatura → ASIGNATURAS

La integridad referencial exige que el valor que tenga la columna dni exista en la tabla PROFESORES; de la misma manera cada código de asignatura deberá existir en la tabla ASIGNATURAS.

En este momento, los DNI almacenados en PROFESORES son:

 dni

21111222

21222333

21333444

y los códigos de ASIGNATURAS:

 codigo

DGBD

FBD

FP

HI

PC

Inserta la información de que el profesor identificado COMO 55555555 imparte la asignatura identificada como AAA

insert into imparte (dni, asignatura)values ('55555555','AAA');

Cannot add or UPDATE  a child row: a foreign key constraint fails (`xxx`.`imparte`, CONSTRAINT `imparte_ibfk_1` FOREIGN KEY (`dni`) REFERENCES `profesores` (`DNI`))

Inserta la información de que el profesor identificado COMO 21333444 imparte la asignatura identificada como DGBD (valores que sí existen)

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

select * from imparte;

 dni asignatura

21333444 DGBD

Sólo se podrán borrar aquellas filas que no estén siendo referenciadas, a través de ninguna clave ajena, DESDE  otra tabla.

Por ejemplo la asignatura DGBD es impartida por los profesores con dni 21111222 y 21333444 (consulta la tabla del anterior resultado): no se podrá borrar la asignatura si antes no se eliminan las filas correspondientes a esa asignatura en la tabla imparte.

delete from asignaturas where codigo='DGBD';

Cannot delete or update a parent row: a foreign key constraint fails (`xxx`.`imparte`, CONSTRAINT `imparte_ibfk_2` FOREIGN KEY (`asignatura`) REFERENCES `asignaturas` (`codigo`))

Eliminando las referencias que nos impiden el borrado:

-- eliminamos las referencias en imparte

delete from imparte where asignatura='DGBD';

-- AHORA ya puedo borrar la asignatura DGBD-- porque ya no hay ninguna clave ajena que le ESTÉ haciendo referencia

delete from asignaturas where codigo='DGBD';

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);insert into asignaturas (codigo, descripcion, CREDITOS , creditosp) values ('FBD', 'FUNDAMENTOS DE LAS BASES DE DATOS', 6.0, 1.5);insert into asignaturas (codigo, descripcion, CREDITOS , creditosp) values ('DGBD', 'DISEÑO Y GESTION DE BASES DE DATOS', 6.0, 3.0);insert into asignaturas (codigo, descripcion, CREDITOS , creditosp) values ('PC', 'PROGRAMACION CONCURRENTE', 6.0, 1.5);insert into asignaturas (codigo, descripcion, creditos, creditosp) values ('FP', 'FUNDAMENTOS DE LA PROGRAMACION', 9.0, 4.5);

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

insert into imparte (dni, asignatura) values ('21111222','FBD');insert into imparte (dni, asignatura) values ('21111222','DGBD');insert into imparte (dni, asignatura) values ('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 varias habrá que ejecutar tantas sentencias UPDATE como tablas queramos modificar. (NOTA: al igual que con delete MySQL sí proporciona sintaxis específica para poder hacerlo pero otros SGBD no).

select codigo,creditos from asignaturas;

 codigo CREDITOS

HI 4.5

FBD 6

DGBD 6

PC 6

PC 9

UPDATE asignaturas set CREDITOS = 0;

select codigo,CREDITOS  from asignaturas;

 codigo

creditos

HI 0

FBD 0

DGBD 0

PC 0

PC 0

Cuando se desea modificar más de una columna se indicará la lista de columnas y valores separadas por comas:

Modifica los CRÉDITOS de las asignaturas a 4, y los créditos prácticos a 2.

update asignaturas set creditos=4, creditosp=2;

 codigo

creditos

creditosp

HI 4 2

FBD 4 2

DGBD 4 2

PC 4 2

PC 4 2

En el caso de que se indique alguna CONDICIÓN , se modificarán sólo aquellas filas de la tabla que cumplan la condición o condiciones impuestas:

Modifique la FECHA de ingreso a 1 de enero de 2003 sólo a aquellos profesores cuya categoría sea TEU.

UPDATE profesoresset ingreso='01/01/2003'where categoria = 'TEU';

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.

Es importante que nos aseguremos de que la subconsulta devuelve un único valor y que éste sea consistente con el tipo de dato esperado.

update imparte set asignatura='BDA',

dni = (select dni from profesores)where asignatura like '%BD%';

ERROR:la subconsulta de una sola fila devuelve más de una fila

Finalmente, podemos combinar todo lo visto en una única sentencia.

select * from asignaturas;

 codigo

 descripcion  CREDITOS

 creditosp

HI HISTORIA DE LA INFORMATICA 4 2

FBD FUNDAMENTOS DE LAS BASES DE DATOS

4 2

DGBD DISEÑO Y GESTION DE BASES DE DATOS

4 2

PC PROGRAMACION CONCURRENTE 4 2

FP FUNDAMENTOS DE LA PROGRAMACION

4 2

UPDATE asignaturasset CREDITOS = 5,(descripcion, creditosp) = (select NOMBRE , 3 from profesores where dni = '21333444')where codigo like '%BD%';

select * from asignaturas;

 codigo

 descripcion  CREDITOS

 creditosp

HI HISTORIA DE LA INFORMATICA 4 2

FBD  RAFAEL ROMERO 5  3

DGBD  RAFAEL ROMERO 5  3

PC PROGRAMACION CONCURRENTE 4 2

FP FUNDAMENTOS DE LA PROGRAMACION

4 2

NOTA: hemos utilizado una constante en la subconsulta (el valor 3)

Integridad referencial

En general, las claves ajenas generan las mismas restricciones de integridad referencial que las vistas para el DELETE salvo por la naturaleza de la operación: el UPDATE sólo generará problemas de integridad referencial si el DATO  a modificar es un valor de clave primaria que está siendo referenciada por alguna clave ajena.

La siguiente orden generaría un problema con la integridad referencial.

update asignaturas set codigo = 'BD1' where codigo = 'FBD';

select * from imparte;

dni asignatura

21111222 DGBD

21111222 FBD

21333444 PC

Para poder realizar esta operación, será necesario insertar una NUEVA  fila en asignaturas con el identificador BD1 y copiando el resto de los valores, después cambiar las referencias a FBD por BD1 y, por último, borrar la fila de FBD.

-- 1. nueva asignatura con los DATOS de la antiguainsert into asignaturas (codigo,descripcion,CREDITOS ,creditosp) select 'BD1', descripcion,CREDITOS ,creditosp from asignaturas where codigo = 'FBD';

select * from asignaturas;

 codig  descripcion  CREDITO  creditos

o S p

HI HISTORIA DE LA INFORMATICA 4 2

FBD RAFAEL ROMERO 5 3

DGBD RAFAEL ROMERO 5 3

PC PROGRAMACION CONCURRENTE 4 2

FP FUNDAMENTOS DE LA PROGRAMACION

4 2

 BD1  RAFAEL ROMERO   5  3

-- 2. modificación de imparteUPDATE imparte set asignatura = 'BD1' where asignatura = 'FBD';

select * from imparte;

dni asignatura

21111222 DGBD

21111222 BD1

21333444 PC

-- 3. borrado de la asignatura antiguadelete from asignaturas where codigo = 'FBD';

select * from asignaturas;

 codigo

 descripcion  CREDITOS

 creditosp

HI HISTORIA DE LA INFORMATICA 4 2

DGBD RAFAEL ROMERO 5 3

PC PROGRAMACION CONCURRENTE 4 2

FP FUNDAMENTOS DE LA PROGRAMACION

4 2

BD1 RAFAEL ROMERO 5 3

MySQL y delete y update

Es importante comprender que cada producto se acerca y se aleja del estándar de SQL en función de sus objetivos y de su ESTADO  de desarrollo. Sin ir más lejos, en MySQL es posible realizar borrados y modificaciones de filas de varias tablas en una única orden, cosa que otros SGBD (Oracle por ejemplo) no permiten.

Por otro lado, la siguiente orden funciona en otros SGBD pero MySQL no permite modificar una tabla a partir de una subconsulta de la misma tabla; si es necesario hay que crear una tabla temporal donde almacenar primero el valor y utilizarlo después en el UPDATE . Si se accede a la base de DATOS  a través de un lenguaje de programación, Php por ejemplo, ya sería más normal almacenar el valor en una variable y hacer la modificación en base al mismo.

Modica la FECHA  de ingreso de los profesores con categoría TEU para que coincida con la del profesor con DNI 21333444

select * from profesores;

 dni NOMBRE  categoria

 ingreso 

21111222 EVA GOMEZ TEU 2003-01-01

21222333 MANUEL PALOMAR

TEU 2003-01-01

21333444 RAFAEL ROMERO

ASO6 1992-06-16

update profesoresset ingreso = (select ingreso from profesores where dni='21333444')where categoria = 'TEU';

De esta orden se debería obtener:

 dni nombre  categoria

 ingreso 

21111222 EVA GOMEZ TEU 1992-

06-16

21222333 MANUEL PALOMAR

TEU 1992-06-16

21333444 RAFAEL ROMERO

ASO6 1992-06-16

Sin embargo, MySQL devuelve un error con el mensaje, más o menos, "no se puede hacer un update de la misma tabla que se está consultando). Esto también ocurre con delete. La solución consiste en "engañar" a MySQL con una consulta temporal:

UPDATE  profesoresset ingreso = (select ingreso                from (select ingreso                      from profesores                      where dni='21333444') ttemp              )where categoria = 'TEU';

En realidad no es que estemos "engañando" a MySQL; COMO se puede ver en el tema de Vistas y tablas temporales, lo que estamos HACIENDO es generar una tabla temporal (ttemp) donde se copian los DATOS de PROFESORES que nos interesan.

T07 Opciones de integridad referencial

Contenidos

1. 1 Las opciones para mantener la integridad referencial

2. 2 On delete

1. 2.1 Propagar

2. 2.2 Anular

3. 3 On update

Las opciones para mantener la integridad referencial

Ya se ha practicado con la integridad referencial en sesiones anteriores y se ha visto que el intento de borrar ciertas filas es rechazado por el SGBD si éstas están siendo referenciadas por alguna clave ajena. El sistema ESTÁ velando por la integridad de los DATOS  almacenados.

No obstante, es posible automatizar y prever estas situaciones expresando en el esquema de la base de datos nuestra voluntad de propagar las operaciones de borrado de filas y de actualización de valores de clave primaria

hasta donde haga falta. Antes de mostrar estas opciones, el siguiente ejemplo nos recuerda las restricciones que impone el exigir integridad referencial a las claves ajenas. 

-- selecciona tu base de datos-- use zXXX

select '****************    Preparando la BD MiEjemplo...' acción from dual;drop table if exists imparte;drop table if exists asignaturas;drop table if exists profesores;

create table profesores( dni varchar(10), NOMBRE  varchar(40), categoria char(4), ingreso date, primary key (dni)) engine=innodb;

create table asignaturas( codigo char(5), descripcion varchar(35), CREDITOS  decimal(3,1), creditosp decimal(3,1),primary key (codigo)) engine=innodb;

insert into profesores (select * from ejemplo.profesores);insert into asignaturas (select * from ejemplo.asignaturas);

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)) engine=innodb;

insert into imparte (select * from ejemplo.imparte);

select * from asignaturas;select * from profesores;select * from imparte;

**************** ESTADO de la BD INICIAL

codigo descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

HI HISTORIA DE LA INFORMATICA 4.5

PC PROGRAMACION CONCURRENTE 6.0 1.5

dni NOMBRE categoria ingreso

21111222 EVA GOMEZ TEU 1993-10-01

21222333 MANUEL TEU 1989-06-16

PALOMAR

21333444 RAFAEL ROMERO ASO6 1992-06-16

dni asignatura

21111222 DGBD

21111222 FBD

21333444 PC

delete from asignaturas where codigo = 'FBD';

La ejecución fallará porque hay profesores que imparten la asignatura FBD.

delete from asignaturas where codigo = 'HI';

Se cumple la orden porque nadie imparte HI.

select * from asignaturas;select * from profesores;select * from imparte;;

**************** ESTADO de la BD FINAL

codigo descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

PC PROGRAMACION CONCURRENTE 6.0 1.5

dni NOMBRE categoria ingreso

21111222 EVA GOMEZ TEU 1993-10-01

21222333 MANUEL PALOMAR

TEU 1989-06-16

21333444 RAFAEL ROMERO ASO6 1992-06-16

dni asignatura

21111222 DGBD

21111222 FBD

21333444 PC

On delete

Propagar

En ciertos sistemas de información es posible redefinir las restricciones de clave ajena PARA  que no se den estos mensajes de error. Ello es posible mediante la cláusula ON DELETE al crear una tabla:

FOREIGN KEY (columna[,columna[, ...]]) REFERENCES tabla (clave primaria)ON DELETE {CASCADE | SET NULL} 

La acción a realizar ante el borrado de una fila que está siendo referenciada por alguna clave ajena PUEDE  ser el propagar la operacion (ON DELETE CASCADE) o anular (ON DELETE SET NULL), dependiendo de la decisión del diseñador de la base de datos.

Veamos AHORA  el efecto de utilizar la opción ON DELETE CASCADE. 

drop table if exists imparte;delete from asignaturas;delete from profesores;insert into asignaturas (select * from ejemplo.asignaturas);insert into profesores(select * from ejemplo.profesores);

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)ON DELETE CASCADE ) engine=innodb;

insert into imparte (select * from ejemplo.imparte);

select * from asignaturas;select * from profesores;select * from imparte;

codigo descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

HI HISTORIA DE LA INFORMATICA 4.5

PC PROGRAMACION CONCURRENTE 6.0 1.5

dni NOMBRE categoria ingreso

21111222 EVA GOMEZ TEU 1993-10-01

21222333 MANUEL PALOMAR

TEU 1989-06-16

21333444 RAFAEL ROMERO ASO6 1992-06-16

dni asignatura

21111222 DGBD

21111222 FBD

21333444 PC

El borrado de FBD provoca borrados automáticos en IMPARTE y el borrado de HI sigue sin generar problemas

delete from asignaturas where codigo = 'FBD';delete from asignaturas where codigo = 'HI';

codigo

descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

PC PROGRAMACION CONCURRENTE 6.0 1.5

dni NOMBRE categoria ingreso

21111222 EVA GOMEZ TEU 1993-10-01

21222333 MANUEL PALOMAR

TEU 1989-06-16

21333444 RAFAEL ROMERO ASO6 1992-06-16

dni asignatura

21111222 DGBD

21333444 PC

Sin embargo, en la CAj a PROFESORES no hemos definido método alguno PARA  mantener la IR.

delete from profesores where dni = '21111222';Falla porque ESTE  profesor imparte DGBD

delete from profesores where dni = '21222333';Éxito ya que no imparte ninguna asignatura

codigo

descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

PC PROGRAMACION CONCURRENTE 6.0 1.5

dni NOMBRE categoria ingreso

21111222 EVA GOMEZ TEU 1993-10-01

21333444 RAFAEL ROMERO ASO6 1992-06-16

dni asignatura

21111222 DGBD

21333444 PC

Anular

Si la modificación de una clave ajena es ON DELETE SET NULL, la acción que llevará a cabo automáticamente el SGBD es la de poner NULOS en aquellos casos en que la integridad referencial se vea comprometida.

Esta definición tiene más dificultad de aplicación puesto que prevalecen las definiciones de VALOR NO NULO. Por ejemplo, es inútil utilizarla en IMPARTE.DNI ya que FORMA  parte de la clave primaria y no admite nulos en ningún caso. Vamos a cambiar el esquema de IMPARTE.

drop table if exists imparte;delete from asignaturas;delete from profesores;insert into profesores (select * from ejemplo.profesores);insert into asignaturas (select * from ejemplo.asignaturas);

create table imparte ( ficha integer, dni varchar(10), asignatura char(5),primary key (ficha),foreign key (dni) references profesores (dni) ON DELETE SET NULL,foreign key (asignatura) references asignaturas (codigo) ) engine=innodb;

Las claves ajenas, AHORA , permiten nulos.

insert into imparte values (1,'21111222','FBD');insert into imparte values (2,'21111222','DGBD');insert into imparte values (3,'21333444','PC');

select * from asignaturas;select * from profesores;select * from imparte;

codigo descripcion creditos creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS

6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

HI HISTORIA DE LA INFORMATICA 4.5

PC PROGRAMACION CONCURRENTE 6.0 1.5

dni nombre categoria ingreso

21111222 EVA GOMEZ TEU 1993-10-01

21222333 MANUEL TEU 1989-06-16

PALOMAR

21333444 RAFAEL ROMERO ASO6 1992-06-16

ficha dni asignatura

1 21111222 DGBD

2 21111222 FBD

3 21333444 PC

delete from profesores where dni = '21111222';

codigo descripcion creditos creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS

6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

HI HISTORIA DE LA INFORMATICA 4.5

PC PROGRAMACION CONCURRENTE 6.0 1.5

dni nombre categoria ingreso

21222333 MANUEL PALOMAR

TEU 1989-06-16

21333444 RAFAEL ROMERO ASO6 1992-06-16

ficha dni asignatura

1 DGBD

2 FBD

3 21333444 PC

On update

La definición ON UPDATE se rige por los mismos parámetros que la anterior, se puede elegir entre propagar o anular (o rechazar, si no definimos nada). 

Nótese que se pueden hacer todas las combinaciones posibles en todas las claves ajenas, no necesariamente ha de ser todas propagar o todas anular, ni tampoco tienen por qué coincidir en la misma clave ajena.

drop table if exists imparte;delete from asignaturas;delete from profesores;insert into asignaturas (select * from ejemplo.asignaturas);insert into profesores(select * from ejemplo.profesores);

create table imparte( dni varchar(10), asignatura char(5),primary key (dni, asignatura),foreign key (dni) references profesores(dni)ON DELETE CASCADE ON UPDATE CASCADE ,foreign key (asignatura) references asignaturas(codigo)ON DELETE CASCADE ON UPDATE CASCADE ) engine=innodb;

insert into imparte (select * from ejemplo.imparte);

select * from asignaturas;select * from profesores;select * from imparte;

codigo

descripcion creditos

creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS

6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS

6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION

9.0 4.5

HI HISTORIA DE LA INFORMATICA 4.5PC PROGRAMACION CONCURRENTE 6.0 1.5

dni NOMBRE categoria

ingreso

21111222

EVA GOMEZ TEU 1993-10-01

21222333

MANUEL PALOMAR

TEU 1989-06-16

21333444

RAFAEL ROMERO

ASO6 1992-06-16

dni asignatura

21111222

DGBD

21111222

FBD

21333444

PC

UPDATE  asignaturas set codigo = 'AAA' where codigo = 'FBD';update profesores set dni = '33' where dni = '21111222';

codigo

descripcion CREDITOS

creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS

6.0 3.0

AAA FUNDAMENTOS DE LAS BASES DE DATOS

6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION

9.0 4.5

HI HISTORIA DE LA INFORMATICA 4.5PC PROGRAMACION CONCURRENTE 6.0 1.5

dni nombre categoria

ingreso

33 EVA GOMEZ TEU 1993-10-

01

21222333

MANUEL PALOMAR

TEU 1989-06-16

21333444

RAFAEL ROMERO

ASO6 1992-06-16

dni asignatura

33 DGBD

33 AAA

21333444

PC

T08 Aritmética de columna

Contenidos

1. 1 Funciones

2. 2 Funciones de agregados

1. 2.1 Sobre el modificador DISTINCT

Se pueden utilizar expresiones aritméticas tanto en la cláusula select, para obtener una nueva columna en la tabla resultado, como en la construcción de condiciones de selección de filas.

Suponiendo que el curso se divide en 2 semestres y que 3 CRÉDITOS  se corresponden con 1 hora de clase semanal, nombre de las asignaturas y NÚMERO  de horas de clase semanales de cada una en un único semestre.

select descripcion, (CREDITOS /3)*2 horasfrom asignaturas;

descripcion horas

DISEÑO Y GESTION DE BASES DE DATOS 4.00000

FUNDAMENTOS DE LAS BASES DE DATOS 4.00000

FUNDAMENTOS DE LA PROGRAMACION 6.00000

HISTORIA DE LA INFORMATICA 3.00000

PROGRAMACION CONCURRENTE 4.00000

Los créditos de cada asignatura son anuales: divididos entre 3, obtenemos las horas a impartir durante un año lectivo cada semana; si lo reducimos a un único semestre, cada semana tendremos el doble de horas de clase.

Descripción de las asignaturas y número de horas semanales de las asignaturas con menos de 4 horas semanales de clase

select descripcion, CREDITOS  from asignaturas where (CREDITOS /3)*2 < 4

descripcion CREDITOS

HISTORIA DE LA INFORMATICA 4.5

Funciones

La lista completa de funciones disponibles en MySQL es mejor consultarla en su manual (http://dev.mysql.com/doc/refman/5.0/es/functions.html). Téngase en CUENTA  que cada SGBD puede proporcionar sus funciones que coincidirán o no con las que maneja MySQL.

Redondea 15.1297 a dos decimales

select round(15.1297,2) redondeo from dual;

redondeo

15.13

Obviamente, para realizar y mostrar un cálculo COMO  este no hace falta ninguna tabla. Dual es una tabla virtual que se utiliza en estos casos para mostrar DATOS  no almacenados ni calculados a partir de ninguna tabla. En realidad, se mantiene por compatibilidad con otros SGBD (Oracle) ya que también se puede ejecutar select round(15.1297,2) con el mismo resultado.

Funciones de agregados

Se dispone de una serie de funciones de agregados que retornan valores calculados sobre una determinada columna o columnas.

La diferencia con las mostradas en la sección anterior es que estas funciones TRABAJAN  sobre conjuntos de valores: devuelven un único valor resultado de procesar varias tuplas seleccionadas mediante la condición de la cláusula where; si no se especifica ésta, el cálculo se realiza sobre la totalidad de la columna. 

Las funciones de tipo estadístico precisan que la expresión que se evalúe se construya sobre columnas NUMÉRICAS . La expresión expr puede contener el nombre de una columna o un cálculo sobre una o varias columnas.

Si se especifica la palabra clave distinct la expresión obligatoriamente ha de ser un nombre de columna, y se asume que la función se calcula únicamente sobre valores distintos de la expresión.

COUNT( * ) NÚMERO  de filas

COUNT( [DISTINCT] expr ) número de valores distintos en expr

SUM( [DISTINCT] expr ) suma de todos los valores en expr

AVG( [DISTINCT] expr ) promedio de todos los valores en expr

MIN( expr ) el más pequeño de todos los valores en expr

MAX( expr ) el mayor de todos los valores en expr

¿Cuántos profesores hay en nuestra base de DATOS ?

select count(*) profes from profesores;

profes

3

¿Cuántas asignaturas de más de 4 créditos tenemos?

select count(*) from asignaturaswhere CREDITOS  > 4;

count(*)

5

¿Cuantos valores DE CRÉDITOS  distintos hay?

select count(distinct CREDITOS ) quecreditos from asignaturas;

quecreditos

3

Hay 3 valores distintos DE CRÉDITOS : 6.0, 9.0 y 4.5.

codigo descripcion creditos creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5

FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5

HI HISTORIA DE LA INFORMATICA 4.5  

PC PROGRAMACION CONCURRENTE 6.0 1.5

Sobre el modificador DISTINCT

Todas las funciones de agregados (menos count(*)) ignoran los nulos (NULL). Por tanto, select count(creditos) from asignaturasdevolvería la cantidad de filas en la tabla asignaturas que no tienen un nulo en creditos.

select count(*) filas, count(creditosp) valores, count(distinct creditosp) distintos from asignaturas;

filasvalores

distintos

5 4 3

El uso del modificador distinct implica, además, que no se tienen en CUENTA  los valores duplicados. Supongamos una tabla con cinco filas y una columna colx y que queremos obtener la media de los valores almacenados en ella (NULL,1,1,1,3): AVG(colx) nos devolvería 1.5, mientras que

AVG(distinct colx) nos devolvería 2.

Por ejemplo:

select avg(creditosp) sinDis, avg(distinct creditosp) conDis from asignaturas;

sinDis conDis

2.62500

3.00000

T09 Agregación

Contenidos

1. 1 Group by

1. 1.1 Where y group by

2. 1.2 Atributos no clave

3. 1.3 Ordenación de la salida

2. 2 Having

3. 3 MySQL permite alejarse del SQL estándar

Group by

Recordemos que el uso de funciones de agregados nos permite realizar cálculos sobre la totalidad de filas que cumplen una determinada CONDICIÓN  (o sobre todas las filas de la tabla)

Cantidad DE CRÉDITOS  que se imparten

select sum(CREDITOS ) credsImp from imparte i, asignaturas awhere i.asignatura = a.codigo

credsImp

18.0

Sin embargo, es muy habitual la necesidad de realizar ese cálculo no de forma global sino particularizando por algún criterio.

Cantidad DE CRÉDITOS  que imparte cada profesor.

select sum(CREDITOS ) credsImp from imparte i, asignaturas awhere i.asignatura = a.codigo

group by dni

credsImp

12.0

6.0

En realidad, se entenderá mejor si introducimos más información en la salida

select dni, sum(CREDITOS ) credsImp from imparte i, asignaturas awhere i.asignatura = a.codigogroup by dni

dni credsImp

21111222

12.0

21333444

6.0

El criterio definido en el group by (dni), establece los distintos grupos de filas sobre los que se va a realizar la suma. Digamos que el sistema busca cada uno de los valores distintos de dni dentro de la tabla imparte y que, para todas las filas que comparten cada uno de esos valores hace una suma DE CRÉDITOS .

A partir de aquí ya podemos añadir cualquier información que consideremos adecuada. 

NOMBRE  de los profesores y CRÉDITOS  que imparten.

select nombre, sum(CREDITOS ) credsPorProf from imparte i, asignaturas a, profesores pwhere i.asignatura = a.codigo and p.dni = i.dnigroup by p.dni

SQL Error: 'ejemplo.p.nombre' isn't in GROUP BY 

El error en la interpretación de la orden anterior viene dado por que es obligatorio poner en el group by todas las columnas no calculadas que vayan a salir en el resultado.

Al contrario, no es necesario que todas las columnas especificadas en el group by aparezcan en la salida.

select nombre, sum(CREDITOS ) credsPorProf from imparte i, asignaturas a, profesores pwhere i.asignatura = a.codigo and p.dni = i.dnigroup by p.dni, nombre

nombre credsPorProf

EVA GOMEZ 12.0

RAFAEL ROMERO 6.0

Aunque con el "nombre" bastaba, hemos puesto también el "DNI" por prevención, podría darse el caso de tener 2 profesores distintos que se llamaran igual.

Where y group by

Cuando se utiliza la cláusula where, aparte de enlazar tablas por columnas comunes, como PROFESORES e IMPARTE por profesores.dni e imparte.dni respectivamente, se PUEDE  utilizar para eliminar ciertas filas del cálculo.

Así, si queremos calcular cuantas asignaturas imparte CADA  profesor sin contar FBD, escribiremos la siguiente sentencia.: 

select NOMBRE , count(*)from profesores p, imparte iwhere p.dni = i.dni    and asignatura != 'FBD'group by p.dni, nombre;

nombre count(*)

EVA GOMEZ 1

RAFAEL ROMERO

1

 PARA  entendernos, "primero" se ejecuta el select-from-where sin cálculos y "después", sobre las filas resultantes, se realizan los cálculos según el criterio de agrupación del group by.

Atributos no clave

Hemos ESTADO  introduciendo la clave primaria de profesores en los group by para prevenir cálculos, en principio, erróneos. Los únicos atributos que aseguran la identificación de una tupla respecto de las demás son los que forman la clave primaria (en general, clave candidata).

Así, si la clave primaria de una tabla de personas (profesores o alumnos, por ejemplo) es el D.N.I., asumimos que no habrá duplicados en ESTE atributo. No obstante, el nombre no será clave y, por lo tanto, admite duplicados. Es decir, es perfectamente posible encontrar a dos personas distintas que se llamen igual.

Nombre del coordinador y cuántas asignaturas coordina.

select nombre, count(*) coordina from asignaturas a, coordinadores c where c.asig = a.codigo group by nombre;

ombre coordina

AGAPITO CIFUENTES 2

ROMUALDO GOMEZ 1

DNI y nombre del coordinador y cuántas asignaturas coordina.

select dni, nombre, count(*) coordina from asignaturas a, coordinadores c where c.asig = a.codigo group by dni, nombre;

dni NOMBRE coordina

10111222

AGAPITO CIFUENTES 1

55777666

AGAPITO CIFUENTES 1

66555444

ROMUALDO GOMEZ 1

Parece claro que la primera solución no es correcta, más si cabe teniendo en cuenta el diseño de la tabla y el significado de sus DATOS  (un coordinador no PUEDE  coordinar más de una asignatura).

Ordenación de la salida

Se puede ordenar la salida utilizando las columnas calculadas.

Todas estas órdenes obtienen la misma salida:

utilizando la propia expresión

select nombre, count( * ) asignaturas from profesores p, imparte iwhere p.dni=i.dnigroup by p.dni, nombreorder by count( * );

utilizando la etiqueta de columna

select nombre, count( * ) asignaturas from profesores p, imparte iwhere p.dni=i.dnigroup by p.dni, nombreorder by asignaturas;

utilizando el orden de la columna

select nombre, count( * ) asignaturasfrom profesores p, imparte iwhere p.dni=i.dni

group by p.dni, nombreorder by 2; 

nombre asignaturas

RAFAEL ROMERO

1

EVA GOMEZ 2

Having

Una vez que se obtiene información calculada sobre grupos de filas mediante el group by, se pueden filtrar estos resultados y mostrar sólo aquellos que nos interesen mediante la cláusula having. Having es a group by lo mismo que where a select-from. 

Al igual que la cláusula where selecciona filas, la cláusula having selecciona grupos; si en la where la CONDICIÓN  que se especifica afecta a las tuplas de toda la tabla, el group by efectúa cálculos en función de esa selección previa y da COMO  resultado una tabla con la información calculada para cada grupo dentro de esa selección. Sobre esta última el having eliminaría aquellas tuplas que no cumplen la condición.

Where filtra los DATOS  almacenados en la tabla y having la información calculada.

Podemos entender el proceso de una consulta con group by-having como la ejecución previa de la parte select-from-where y, a partir de este resultado intermedio, la obtención de la información calculada con group by y, finalmente, el filtrado con having. 

¿Cuántas asignaturas imparte cada profesor?

select p.dni, NOMBRE , count(*) asignaturasfrom profesores p, imparte iwhere p.dni = i.dnigroup by p.dni, nombre

dni nombre asignaturas

21111222

EVA GOMEZ 2

21333444

RAFAEL ROMERO 1

¿Cuántas asignaturas imparten los profesores con 2 o más asignaturas?

select p.dni, nombre, count(*) asignaturasfrom profesores p, imparte iwhere p.dni = i.dnigroup by p.dni, nombrehaving count(*) >= 2

dni nombre asignaturas

21111222

EVA GOMEZ 2

Having no permite utilizar la etiqueta de las columnas calculadas, en este caso "asignaturas", ni tampoco el orden de la columna que interpretaría como un NÚMERO  entero.

Sin tener en CUENTA  la asignatura FBD, ¿cuántas asignaturas imparten los profesores con 2 o más asignaturas?

select p.dni, NOMBRE , count(*) asignaturasfrom profesores p, imparte iwhere p.dni = i.dniand asignatura != 'FBD'group by p.dni, nombrehaving count(*) >= 2

Ninguna fila seleccionada

Paso a paso, en la consulta anterior:

1. where ha eliminado las tuplas de imparte de código de asignatura FBD.2. group by calcula, para cada profesor, el NÚMERO  de asignaturas que imparte. Puesto que no

contamos FBD, Eva Gómez solo imparte, igual que Rafael Romero, una única asignatura.3. having elimina del resultado del paso anterior todas aquellas tuplas con un valor en la cuenta de

filas menor que 2. El resultado es vacío puesto que ninguno de los grupos supera la CONDICIÓN .

La condición del having no es necesariamente sobre el cálculo a mostrar, como se PUEDE  ver en el siguiente ejemplo.

¿Cuántas asignaturas imparte cada profesor en el caso de que la suma DE CRÉDITOS  sea mayor que 5?

select p.dni, nombre, count(*) asignaturasfrom profesores p, imparte i, asignaturas awhere p.dni = i.dni and a.codigo = i.asignaturagroup by p.dni, nombrehaving sum(CREDITOS ) > 5

dni nombre asignaturas

21111222

EVA GOMEZ 2

21333444

RAFAEL ROMERO 1

MySQL permite alejarse del SQL estándar

Hay que tener cuidado con una característica de MySQL que depende también de cómo esté configurado el servidor. El caso es que podría darse el caso de que MySQL acepte la siguiente orden en la que hemos omitido la columna NOMBRE  del group by:

DNI y nombre del coordinador y cuántas asignaturas coordina.

select dni, nombre, count(*) coordinafrom asignaturas a, coordinadores c where c.asig = a.codigo group by dni;

En este caso, no hay problema puesto que para cada dni hay un único nombre, dni es el identificador de los coordinadores, pero no siempre es así. Como en muchas otras cosas, MySQL ofrece funcionalidades propias que el usuario PUEDE  elegir o no, pero alejarse del estándar dificulta la posibilidad de migrar a otros sistemas de gestión de base de DATOS .

MySQL deja en manos del que realiza la consulta el que tenga cuidado con el criterio de agrupación que utiliza asegurándose de que las columnas omitidas no van a alterar el resultado. Se puede ampliar esta información en el manual de MySQL. Hay autores que defienden este "alejamiento" del estándar, comoRoland Bouman.

Otra característica que MySQL permite por defecto es utilizar having sin group by:

select * from asignaturashaving CREDITOS  > 3;

La idea que subyace en esta "permisividad" es que where no permite utilizar funciones de agregado mientras que having sí. NUEVAMENTE , esta extensión de MySQL no es aceptada por todos los gestores de BD.

En cualquier caso, no recomendamos estas prácticas, al menos en un contexto de aprendizaje básico, ni tampoco las aceptaremos en la evaluación de este tipo de ejercicios.

T11 Conjuntos

Contenidos

1. 1 Operaciones de conjuntos y MySQL

2. 2 Unión

3. 3 Intersección

4. 4 Diferencia

5. 5 Producto cartesiano

6. 6 Concatenación natural

7. 7 Material adicional

Operaciones de conjuntos y MySQL

Un operador sobre conjuntos combina el resultado de dos sentencias select en un único resultado. Dependiendo del tipo de operación esas sentencias deberán cumplir unos requisitos en cuanto al resultado que dan.

Los operadores de conjuntos definidos para el álgebra relacional, base sobre la que se cimenta SQL, son la unión, intersección, diferencia, producto cartesiano y división. En otros motores de base de DATOS  se dispone de algunos operadores como UNION, INTERSECT y MINUS.

En MySQL solo está disponible el operador UNION, aunque la operativa de los otros puede simularse con otros operadores de los que ya hemos visto unos cuantos.

Unión

Al utilizar el operador UNION entre dos sentencias select, el resultado final estará compuesto por todas aquellas filas que aparecen en el resultado de como mínimo una de las select. El operador UNION elimina filas duplicadas en el resultado final. El operador UNION ALL opera de igual modo que el operador UNION, pero no elimina filas duplicadas en el resultado final. 

Supongamos que queremos saber el NOMBRE  de los profesores que son ASO6 o imparten asignaturas de 6 CRÉDITOS . Veamos primero el resultado de cada consulta por separado.

1) Nombre de los profesores cuya categoría es ASO6.

select nombre from profesores where categoria='ASO6';

2) Nombre de los profesores que imparten asignaturas de 6 CRÉDITOS .

select nombrefrom profesores p, imparte i, asignaturaswhere p.dni=i.dni and asignatura=codigo and CREDITOS =6;

NOMBRE  de los profesores que son ASO6 o imparten asignaturas de 6 CRÉDITOS .

select nombre from profesores where categoria='ASO6'UNIONselect nombrefrom profesores p, imparte i, asignaturaswhere p.dni=i.dni and asignatura=codigo and CREDITOS =6;

nombre

RAFAEL ROMERO

EVA GOMEZ

La misma consulta pero solucionada con UNION ALL

select nombre from profesores where categoria='ASO6'UNION ALLselect nombrefrom profesores p, imparte i, asignaturaswhere p.dni=i.dni and asignatura=codigo and CREDITOS =6;

nombre

RAFAEL ROMERO

EVA GOMEZ

EVA GOMEZ

RAFAEL ROMERO

Intersección

Aunque no existe tal operador en MySQL, normalmente se PUEDE  sustituir por consultas ya conocidas.

DNI de los profesores que imparten y preparan.

IMPARTE[dni]∩(COORDINADORES[dni])

Lo que debería solucionarse como

select dni from imparteINTERSECTselect dni from prepara;

en realidad, en MySQL, debemos expresarlo COMO :

select distinct i.dnifrom imparte i, prepara ppwhere pp.dni=i.dni;

dni

21111222

21333444

Se usa el modificador distinct para que la expresión sustituya realmente la operativa de la intersección: las operaciones de conjuntos no devuelven duplicados (al igual que union all es una extensión para que se PUEDAN  resolver cierto tipo de consultas que sí necesitan los duplicados). No obstante, lo usaremos o no dependiendo de si lo necesitamos o no.

Diferencia

El operador MINUS tampoco está implementado en MySQL pero es fácilmente sustituible por expresiones NOT IN. En todo caso, el resultado final estará compuesto sólo por aquellas filas que aparecen en el resultado de la primera select y no aparecen en el resultado de la segunda.

NOMBRE  de los profesores que son TEU y no imparten asignaturas de 6 CRÉDITOS .

PROFESORES donde categoría='TEU' [nombre]

-

(PROFESORES x IMPARTE x ASIGNATURAdonde (PROFESORES.dni = IMPARTE.dni y codigo=asignatura y CRÉDITOS =6)[nombre])

Lo que debería solucionarse como

select nombre from profesores where categoria='TEU'MINUS select nombre from profesores p, imparte i, asignaturas where p.dni=i.dni and asignatura=codigo and CREDITOS =6;

se soluciona así:

select NOMBRE  from profesores where categoria='TEU'and nombre NOT IN (select nombre from profesores p, imparte i, asignaturas where p.dni=i.dni and asignatura=codigo and CREDITOS =6);

nombre

MANUEL PALOMAR

ESTA  sería la consulta exactamente equivalente a la diferencia de conjuntos tal y COMO  está definida en el álgebra relacional, con relaciones compatibles, pero esto es SQL, podemos simplificar la consulta y obtendremos el mismo resultado:

select nombre from profesores where categoria='TEU'  and dni NOT IN (select dni from imparte i, asignaturas

 where asignatura=codigo and CREDITOS =6);

Producto cartesiano

En SQL, y como ya se ha dicho anteriormente, el producto cartesiano se realiza con una select "sin where". 

select dni, codigo from profesores, asignaturas

dni codigo

21111222 DGBD

21222333 DGBD

21333444 DGBD

21111222 FBD

21222333 FBD

21333444 FBD

21111222 FP

21222333 FP

21333444 FP

21111222 HI

21222333 HI

21333444 HI

21111222 PC

21222333 PC

21333444 PC

No obstante, el producto cartesiano, como cualquier otro tipo de consulta, PUEDE  filtrar las filas resultado a nuestra conveniencia. 

Dni de los profesores que imparten 2 o más asignaturas

select distinct i1.dnifrom imparte i1, imparte i2where i1.dni = i2.dni     and i1.asignatura != i2.asignatura;

Del producto cartesiano de una tabla por sí misma solo nos interesan las filas en las que el profesor "de la izquierda" es el mismo que el "de la derecha"; sí, además, las asignaturas son diferentes, la conclusión no puede ser otra que ese profesor cumple con la CONDICIÓN .

Aunque para el ejemplo que hemos propuesto hay una forma más amigable de solucionar este enunciado, la que mostramos a CONTINUACIÓN , el producto cartesiano es una opción que podemos utilizar cuando creamos conveniente

select dni from imparte group by dni having count(*) >= 2;

NOMBRE  de los profesores que imparten asignaturas de 6 CRÉDITOS  y no son TEU.

PROFESORES x IMPARTE x ASIGNATURA

donde (PROFESORES.dni = IMPARTE.dni y codigo=asignatura y CRÉDITOS =6)[nombre]-(PROFESORES donde categoría='TEU' [nombre])

select nombrefrom profesores p, imparte i, asignaturaswhere p.dni=i.dni and asignatura=codigo and CREDITOS =6and nombre NOT IN (select nombre from profesores where categoria='TEU');

nombre

RAFAEL ROMERO

Concatenación natural

Este operador está implementado en MySQL y Oracle Database pero no es seguro que otros SGBD lo tengan implementado igualmente. Igual que en álgebra relacional, este operador asume que hay columnas comunes en dos tablas diferentes (en principio, que se llamen igual) y automatiza la reunión (join) en base a la igualdad de valores en estas columnas comunes. Dicho de otra forma: 

Todos los DATOS  de profesores que imparten alguna asignatura y códigos de esas asignaturas.

select p.dni, p.NOMBRE , p.categoria, p.ingreso, i.asignaturafrom profesores p, imparte iwhere p.dni=i.dni;

obtiene el mismo resultado que 

select * from profesores NATURAL JOIN imparte;

En ambos casos la salida es la que se muestra a CONTINUACIÓN . Nótese que solo se muestra una columna de "dni" (concretamente la de PROFESORES, la tabla a la izquierda del operador).

dni nombrecategoria

ingreso asignatura

21111222

EVA GOMEZ TEU 1993-10-01 DGBD

21111222

EVA GOMEZ TEU 1993-10-01 FBD

21333444

RAFAEL ROMERO ASO6 1992-06-16 PC

Su expresión en álgebra relacional sería:

PROFESORES ∞ IMPARTE

A pesar de la posible comodidad de uso de ESTE  operador hay que tener mucho cuidado, si no existen columnas comunes se devolverá un producto cartesiano. Otra fuente de resultados no esperados es que haya más columnas con idéntico nombre de las deseadas.

Material adicionalAunque es un operador que se comporta de manera diferente a los anteriores TAMBIÉN  podríamos incluir EXISTS dentro de la categoría de operadores SQL de conjuntos. Además, es la base a una de las simulaciones clásicas del operador DIVISIÓN del álgebra relacional. Ambos se comentan en una lección complementaria.

Conjuntos 2

Contenidos

1. 1 El operador EXISTS

1. 1.1 Eficiencia

2. 1.2 Alternativas

2. 2 División

1. 2.1 La división del álgebra relacional con EXISTS

El operador EXISTS

EXISTS es un operador de un único argumento y cuyo resultado es un valor de verdad, informa de la presencia o no de tuplas en una tabla. 

     [NOT] EXISTS (orden select) 

El operador exists nos informa de si una subconsulta ha obtenido algún resultado: devuelve verdadero si hay al MENOS  una tupla en la relación derivada y falso si la relación derivada es vacía.

¿Tenemos profesores?

select exists (select 1 from profesores) respuesta

respuesta

1

Puesto que no necesitamos nada de la tabla profesores salvo saber si hay o no filas, por eso nos basta con "select 1": el resultado de la subconsulta sería tantas filas con "1" COMO  filas haya en la tabla profesores.

Es más habitual componer una CONDICIÓN  que nos permita filtrar filas.

Todos los DATOS  de las asignaturas que son impartidas por algún profesor

select * from asignaturas a where exists (select 1 from imparte i where i.asignatura=a.codigo)codigo

descripcion CREDITOS creditosp

DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0

FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5

PC PROGRAMACION CONCURRENTE 6.0 1.5

Evidentemente, el resultado sería el mismo si pidiéramos

select * from asignaturas where codigo IN (select asignatura from imparte)

Eficiencia

Aunque para la cantidad de filas que estamos manejando tanto en la BD Ejemplo COMO  en TiendaOnLine la diferencia es irrelevante, el uso de exists PUEDE  ser adecuado para mejorar el rendimiento de ciertas consultas. Sea, por ejemplo:

select distinct NOMBREfrom profesores p, imparte iwhere p.dni = i.dni;

En realidad, sólo nos interesa saber el nombre de los profesores que imparten alguna asignatura, sin reparar en cuáles son esas asignaturas. Para eso, es mejor utilizar:

select nombre from profesores pwhere exists (select 1 from imparte where dni = p.dni);

nombre

EVA GOMEZ

RAFAEL ROMERO

La ventaja de ESTA  segunda consulta es que la subconsulta sólo necesita devolver una fila por cada profesor, mientras que la primera primero obtiene la concatenación de todos los códigos de asignatura con su profesor correspondiente y después proyecta sobre la columna nombre. 

Podemos suponer un ESTADO  de la base de DATOS  que contuviera 1000 asignaturas, y que los 3 profesores imparten todas las asignaturas: la primera consulta trabajaría con 3000 filas, mientras que la segunda no necesitaría más de 3.

No obstante, lo dicho aquí debe considerarse como un apunte sobre la necesidad en ciertos entornos de mejorar el rendimiento de aquellas consultas que son críticas por su frecuencia o por un gran volumen de datos, esto es, "ayudar" al optimizador de consultas. 

select nombre from profesores pwhere exists(select codigo from asignaturas awhere exists(select asignatura from prepara ppwhere pp.asignatura=a.codigo and p.dni=pp.dni))

sería equivalente a "NOMBRE  de los profesores que preparan alguna asignatura", que se soluciona mucho más fácil como

select nombrefrom profesores p, prepara pp

where p.dni=pp.dni

nombre

EVA GOMEZ

RAFAEL ROMERO

O si negamos SOLO  uno de los exists

Nombre de los profesores que no preparan todas las asignaturas (que existe al MENOS  una asignatura que no prepara)

select nombre from profesores pwhere exists(select codigo from asignaturas awhere not exists(select asignatura from prepara ppwhere pp.asignatura=a.codigo and p.dni=pp.dni))

nombre

EVA GOMEZ

MANUEL PALOMAR

Alternativas

A veces, el resultado buscado permite otro TIPO  de soluciones.

Nombre de los profesores que preparan todas las asignaturas.

select nombre from profesores p, prepara ppwhere p.dni = pp.dnigroup by p.dni, nombrehaving count(*) = (select count(*) from asignaturas);

Nombre de los profesores que no preparan ninguna asignatura.

select NOMBRE  from profesores pwhere not exists(select codigo from asignaturas awhere exists(select asignatura from prepara ppwhere pp.asignatura=a.codigo and p.dni=pp.dni)); select nombre from profesores pwhere not exists(select asignatura from prepara ppwhere p.dni=pp.dni);

Realmente, la alternativa más clara y fácil de entender es esta:

select nombre from profesoreswhere dni NOT IN (select dni from prepara);

nombre

MANUEL PALOMAR

División

NOTA: el operador división no se va a exigir en ninguna prueba DESDE  el curso 2013-14. El texto que sigue se mantiene COMO material adicional para quienes tengan interés en ampliar conocimientos de álgebra relacional y SQL.

La división como operador definido en el álgebra relacional nos da como resultado "algo que se relaciona con todos los que cumplen una CONDICIÓN ". Por ejemplo, 

IMPARTE[asignatura, dni] ÷ (PROFESORES[dni])

Esta expresión daría como resultado los códigos de las asignaturas que son impartidas por todos los profesores de mi base de DATOS . No existe, la división, como tal operador en SQL por lo que debemos simularlo con exists.

La división del álgebra relacional con EXISTS

Uno de los posibles usos, aunque ciertamente confuso al principio, es el de resolver consultas del TIPO  "x hace algo con todos los y de nuestra base de datos".

Supongamos una tabla adicional en nuestra BD Ejemplo, PREPARA, que es una relación muchos a muchos entre asignaturas y profesores, y que podría representar qué profesores intervienen en la preparación de cada asignatura. Su contenido es el que se muestra a CONTINUACIÓN :

Profesores que preparan todas las asignaturas.

select NOMBRE  from profesores pwhere not exists(select codigo from asignaturas awhere not exists(select asignatura from prepara pp

where pp.asignatura=a.codigo and p.dni=pp.dni))

nombre

RAFAEL ROMERO

Vamos a reformular la consulta siguiendo las apariciones del operador exists dentro de las sucesivas órdenes select:

"Nombre de los profesores tales que no hay ninguna asignatura que no prepare él".

Supongamos que la FORMA  que tiene el SGBD de resolver esta sentencia es la siguiente :

1. En la primera select recorremos la tabla de profesores.2. PARA  cada profesor de la anterior, la segunda select recorre la tabla de asignaturas.3. Para cada profesor y asignatura de las anteriores comprueba que el primero prepara la segunda.

T12 Vistas y tablas temporales

Contenidos

1. 1 Vistas

1. 1.1 Insertando

2. 1.2 Borrando

3. 1.3 Modificando

4. 1.4 Con más de una tabla

5. 1.5 With check option

2. 2 Tablas temporales

1. 2.1 Subconsultas como columnas calculadas

2. 2.2 Subconsultas en el from

3. 2.3 Invertir una tabla

4. 2.4 Tablas TEMPORARY

En ESTA  sesión vamos a mostrar dos tipos de objetos de uso habitual en cualquier SGBD: las vistas y las tablas temporales. Mientras que las primeras son objetos persistentes que nos permiten resumir consultas complejas y de uso frecuente, las segundas, como su NOMBRE  indica, son objetos de corta vida, durante una sesión o una simple consulta.

Vistas

PARA  esta sección es necesario conectarse al servidor con un usuario con permisos para crear vistas.

Una vista es un objeto que se define a partir de una consulta y que se comporta como una tabla si bien, dependiendo de la consulta en la que se basa, se PUEDEN  hacer más o menos cosas: consultar una vista siempre será posible pero insertar o borrar filas en una vista o modificar un valor ya depende de cómo sea esa

definición.

Para crear una vista disponemos de la orden:

CREATE VIEW nombrevista AS consulta

Una vista es un objeto persistente, por lo tanto, para eliminarla del catálogo, hay que ejecutar:

DROP VIEW nombrevista

También se PUEDE  consultar la definición de una vista ya definida (aunque esto ya es particular de MySQL, en otros gestores tienen sus propios métodos):

SHOW CREATE VIEW nombrevista

Toda la información sobre vistas en MySQL se puede consultar en http://dev.mysql.com/doc/refman/5.0/es/views.html.

Esta sesión se ha planteado en formato demostración por lo que se recomienda ejecutar una a una las siguientes sentencias y buscar los motivos por los que unas sentencias se ejecutan sin problemas y otras no.

Tras conectarse al servidor hay que elegir una base de DATOS  en la que se nos permita crear vistas:

use mibd;

Vamos a copiar los contenidos de algunas tablas de TiendaOnLine a tablas locales que sean de nuestra propiedad

drop table if exists mitv;drop table if exists miarticulo;

create table miarticulo (cod varchar(7),NOMBRE  varchar(45),pvp decimal(7,2),marca varchar(15),imagen blob,urlimagen varchar(100),especificaciones text,primary key (cod)) engine=innodb;

insert into miarticulo select * from tiendaonline.articulo;

create table mitv (cod varchar(7),panel varchar(45),pantalla smallint(6),resolucion varchar(15),

hdreadyfullhd varchar(6),tdt tinyint(1),primary key (cod),foreign key (cod) references miarticulo (cod)) engine=innodb;

insert into mitv select * from tiendaonline.tv;

Creamos la primera vista:

create view vma as select cod,nombre,pvp from miarticulo;

Una vista se comporta como una tabla y PUEDE  consultarse.

select * from vma where pvp between 500 and 700;

Una vista se PUEDE  generar a partir de cualquier consulta, y tiene la característica añadida de poder restringir el acceso a solo un subconjunto de las filas posibles.

create view vmb as select cod,nombre,pvp from miarticulo where pvp between 500 and 700;

select * from vmb;

(1) select cod,pvp from vmb where cod in (select cod from mitv) order by pvp;

Insertando

Una vista, bajo ciertas condiciones, permite insertar nuevos datos.

(2) insert into vma (cod,nombre,pvp) values ('B001','MiArtículo',499);

select * from miarticulo where cod='B001';select * from vma where cod='B001';select * from vmb where cod='B001';

(3) insert into vmb (cod,nombre,pvp) values ('B002','MiOtroArtículo',701);

select * from vmb where cod='B002';

select * from miarticulo where cod='B002';select * from vma where cod='B002';

Borrando

Creamos otra vista para facilitar la comprobación de las acciones que solicitamos. En este caso vamos a intentar eliminar filas.

create view bart as select * from miarticulo where cod like 'B%';

select * from bart;

(4) delete from vma where cod='B001';

(5) delete from vmb where cod='B002';

select * from bart;

Modificando

Ahora comprobaremos la orden update. Esta orden tiene las mismas restricciones que delete.

select * from bart;

(6) UPDATE  vma set pvp = 800 where cod='B002';

select * from bart;

(7) update vmb set pvp = 600 where cod='B002';

select * from bart;

Con más de una tabla

La vista se puede definir sobre varias tablas, pero eso afecta a las órdenes que se pueden ejecutar y en qué CONDICIONES .

create view vat as  select a.cod,NOMBRE ,pvp,resolucion,tdt  from miarticulo a, mitv t  where a.cod=t.cod    and pvp between 800 and 1200;

select * from vat order by pvp desc;

(8) insert into vat values ('B003','OtroMás',1100,null,null);(9) insert into vat (cod,nombre,pvp,resolucion,tdt) values ('B003','OtroMás',1100,null,null);(10) insert into vat (cod,nombre,pvp) values ('B003','OtroMás',1100);

select * from vat;select * from bart;

(11) insert into vat (resolucion,tdt) values ('800x600',1);(12) insert into vat (cod,resolucion,tdt) values ('B004','800x600',1);

(13) delete from vat where cod='A0694';

select * from vat;select * from bart;

(14) update vat set pvp = 999 where cod='A0694';(15) update vat set resolucion = '800x600',tdt=1 where cod='A0694';(16) update vat set pvp = 850, resolucion = 'ninguna',tdt=1 where cod='A0694';

select * from vat;

(17) UPDATE  vat set cod = 'B004' where cod='A0694';

select * from vat;select * from bart;select * from mitv where cod='A0694';

With check option

Vamos redefinir bart y crear otra vista bart2.

drop view if exists bart2;drop view if exists bart;

create view bart as select * from miarticulo where cod like 'B%';(18) create view bart2 as select * from miarticulo where cod like 'B%' with check option;

select * from bart;

select * from bart2;

insert into bart2 (cod,NOMBRE ,pvp) values ('B010','Artículo B10',1999);insert into bart2 (cod,nombre,pvp) values ('C010','Artículo C10',1999);

select * from bart;select * from bart2;select * from miarticulo where pvp=1999;

insert into bart (cod,nombre,pvp) values ('C010','Artículo C10',1999);select * from bart;select * from bart2;select * from miarticulo where pvp=1999;

Finalmente, eliminamos todas las vistas y tablas creadas. 

drop view bart;drop view bart2;drop view vat;drop view vmb;drop view vma;drop table mitv;drop table miarticulo;

El modificador WITH CHECK OPTION obliga a cualquier operación que se haga sobre la vista a cumplir las CONDICIONES  del where: si "bart2" no tuviera esa opción, podríamos insertar en la vista cualquier artículo que, finalmente, se almacenaría en "miarticulo", el artículo existiría en mi base de DATOS  pero no podría verlo por la definición de "bart2"; con WITH CHECK OPTION, el sistema no me deja insertar más que artículos cuyo "cod" empiece por 'B'.

Tablas temporales

Nos hemos tomado la libertad de considerar tablas temporales tanto las tablas temporary en MySQL, COMO las subconsultas utilizadas como columna en la select o como tabla en el from.

Se entiende por tabla temporal aquella que se crea y utiliza en un contexto limitado, bien sea una orden concreta de SQL (el caso genérico de las subconsultas) o una sesión o conexión. Las tablas TEMPORARY, por ejemplo, son objetos que desaparecen automáticamente cuando se cierra la sesión de usuario. Una subconsulta es accesible solo mientras se ejecuta una determinada orden. En ESTA  lección solo vamos a tratar los casos de subconsultas, las tablas TEMPORARY no necesitan más explicación.

Subconsultas como columnas calculadas

Una consulta PUEDE  utilizarse como una columna más de cualquier consulta, solo hay que tener la precaución

de darle NOMBRE . Aunque MySQL no lo necesita realmente, parece conveniente utilizar la palabra reservada AS PARA  mejorar la comprensión de cada parte de la consulta. Por ejemplo, la siguiente orden genera una tabla con 2 columnas a partir de subconsultas completas:

use tiendaonline;select     (select max(pvp) from articulo) as mart,     (select max(PRECIO ) from linped) as mlinped;

En estos casos se utilizan las subconsultas para realizar cálculos sobre ellas y dentro de una consulta sobre un cierto conjunto de filas:

select cod,pvp,       pvp/(select max(pvp) from articulo)*100 as tpcpvp,        pvp/(select max(precio) from linped)*100 as tpcprecio from articulo where pvp between 1000 and 1200;  

Subconsultas en el from

A veces es necesario incluir una subconsulta como una tabla más a enlazar en otra consulta de orden superior. Nuevamente, hay que darle nombre a esa tabla temporal para poder hacer referencia a ella. En este caso, aprovechamos la consulta anterior para utilizar en otra más compleja:

select tv.cod,resolucion,tdt,tpcpvpfrom tv,  (select cod,pvp,       pvp/(select max(pvp) from articulo)*100 as tpcpvp,        pvp/(select max(precio) from linped)*100 as tpcprecio    from articulo    where pvp between 1000 and 1200) as calc  where tv.cod=calc.cod;

Invertir una tabla

Lo que en otros sistemas se conoce como columnas PIVOT (en SQL Server, por ejemplo) consiste en construir columnas a partir de la información contenida en las filas. Aquí vamos a calcular cuántos pedidos se han realizado en los meses de octubre, noviembre y diciembre de cualquier año. Antes, vamos a comprobar los DATOS  de los que disponemos:

select month(FECHA ) mes, count(*) from pedido where month(fecha) in (10,11,12)group by month(fecha) order by month(fecha);

Lo que queremos conseguir es que los meses se conviertan en columnas. PARA  ello:

select    (select count(*)      from pedido      where month(fecha) =10) as octubre,    (select count(*)      from pedido      where month(fecha) =11) as noviembre,    (select count(*)      from pedido      where month(fecha) =12) as diciembre;

Complicándolo un poco, veamos la cantidad de veces que se solicita CADA  artículo en cada uno de esos mismos meses:

select articulo, month(fecha) mes, count(*) veces from pedido p join linped l on (p.numpedido=l.numpedido)where month(fecha) in (10,11,12)group by articulo,month(fecha) order by articulo,month(fecha);

Con la misma intención que ANTES , convertiremos los meses en columnas haciendo uso de subconsultas:

select distinct articulo,    (select count(*)      from pedido p join linped l on (p.numpedido=l.numpedido)     where month(FECHA ) =10 and l.articulo=ll.articulo) octubre,    (select count(*)      from pedido p join linped l on (p.numpedido=l.numpedido)     where month(fecha) =11 and l.articulo=ll.articulo) noviembre,    (select count(*)      from pedido p join linped l on (p.numpedido=l.numpedido)     where month(fecha) =12 and l.articulo=ll.articulo) diciembrefrom linped ll;

La misma consulta pero variando ligeramente los orígenes de los DATOS , lo que evita el uso del distinct. Nótese que AHORA  las subconsultas hacen referencia a la tabla del select externo:

select cod as articulo,  (select count(*) 

   from pedido p join linped l on (p.numpedido=l.numpedido)   where month(fecha) =10 and l.articulo=cod) octubre,  (select count(*)    from pedido p join linped l on (p.numpedido=l.numpedido)   where month(fecha) =11 and l.articulo=cod) noviembre,  (select count(*)    from pedido p join linped l on (p.numpedido=l.numpedido)   where month(fecha) =12 and l.articulo=cod) diciembrefrom articulowhere cod in (select articulo               from pedido p join linped l on (p.numpedido=l.numpedido)               where month(fecha) in (10,11,12));

Tablas TEMPORARY

Aprovechamos la consulta anterior para crear una tabla temporal. Recuérdese que esta tabla desaparecerá del sistema en cuanto cerremos la conexión al servidor. Aquí se define a partir de una consulta pero estas tablas se PUEDEN  crear como cualquier otra con la orden CREATE TABLE.

create temporary table mitabla    select cod articulo,      (select count(*)        from pedido p join linped l on (p.numpedido=l.numpedido)       where month(fecha) =10 and l.articulo=cod) octubre,      (select count(*)        from pedido p join linped l on (p.numpedido=l.numpedido)       where month(fecha) =11 and l.articulo=cod) noviembre,      (select count(*)        from pedido p join linped l on (p.numpedido=l.numpedido)       where month(fecha) =12 and l.articulo=cod) diciembre    from articulo    where cod in (select articulo                   from pedido p join linped l on (p.numpedido=l.numpedido)                   where month(fecha) in (10,11,12));

Consultemos la tabla recién creada PARA  comprobar su contenido

select * from mitabla where octubre>0 and noviembre>0;

No nos tenemos que preocupar de eliminarla puesto que ya lo hará el propio sistema.