sql curso
DESCRIPTION
sqlTRANSCRIPT
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.