BASES DE DATOS 2º Cuatrimestre LMD (Lenguaje de Manipulación de Datos) SELECT SELECT [DISTINCT] | * FROM [WHERE ] EJ: Visualizar todos los vuelos que tengan como origen o destino Cáceres. SELECT * FROM VUELOS WHERE ORIGEN='CACERES' OR DESTINO='CACERES' EJ: Visualizar todos los vuelos que tengan como origen Madrid o Londres y como destino Londres o Madrid. SELECT * FROM VUELOS WHERE (ORIGEN='MADRID' AND DESTINO='LONDRES') OR (ORIGEN='LONDRES' AND DESTINO='MADRID') Claúsula IN Expresa la pertenencia del valor de una columna a un determinado conjunto de valores. EJ: Seleccionar aquellos vuelos que tengan como origen Madrid, Barcelona o Sevilla. SELECT * FROM VUELOS WHERE ORIGEN IN ('MADRID','BARCELONA','SEVILLA')
1
ó también SELECT * FROM VUELOS WHERE ORIGEN='MADRID' OR ORIGEN='BARCELONA' OR ORIGEN='SEVILLA' EJ: Visualizar todos los vuelos existentes excepto aquellos que llegan a Londres o a Copenhague. SELECT * FROM VUELOS WHERE DESTINO NOT IN ('LONDRES','COPENHAGUE') Claúsula BETWEEN Sirve para establecer o expresar un rango de valores. Obedece a la siguiente sintaxis: BETWEEN valor1 AND valor2 El rango será [valor1, valor2], extremos incluidos.
EJ: Recuperar todos los vuelos que salgan entre las 6 y las 12 de la mañana. SELECT * FROM VUELOS WHERE HORA_SALIDA BETWEEN '06.00.00' AND '12.00.00' ó también SELECT * FROM VUELOS WHERE HORA_SALIDA >= '06.00.00' AND HORA_SALIDA <= '12.00.00' EJ: En la columna NUM_VUELO representaré los vuelos con 6 caracteres. Los dos primeros caracteres indicarán la compañía a la que pertenece cada vuelo (IBIberia, BABritish Airways), los cuatro caracteres siguientes corresponderán al número de vuelo. Bajo estas condiciones recupérense todos los vuelos que no pertenecen a IBERIA. SELECT * 2
FROM VUELOS WHERE NUM_VUELO NOT BETWEEN 'IB0000' AND 'IB9999' Claúsula LIKE Sirve para especificar, con la ayuda de metasímbolos, cadenas de caracteres que comparten ciertos caracteres en común. Los metasímbolos que serán utilizados son: % Equivale a una cadena de caracteres de longitud comprendida entre 0 y n. 'AB%' AB, ABCDE, AB 497 _ Equivale a un único carácter 'A_B' A B, A4B, AJB EJ: Recuperar todos los vuelos pertenecientes a la compañía IBERIA. SELECT * FROM VUELOS WHERE NUM_VUELOS LIKE 'IB%' ó también SELECT * FROM VUELOS WHERE NUM_VUELOS LIKE 'IB_ _ _ _' Expresiones aritméticas +, −, *, / Pueden ser utilizadas tanto después de SELECT como después de WHERE. En el primer caso trabajarían sobre columnas y en el segundo sobre filas.
EJ: Visualizar la longitud y la envergadura de todos los aviones, expresando las magnitudes en pies (en la base de datos está almacenado en metros, para pasar 1 metro a pies se ha de multiplicar por 3.28), y la velocidad de crucero en mph(está en Km/h, habrá que dividir por 1.6). SELECT LONGITUD*3.28, ENVERGADURA*3.28, VELO_CRUC/1.6 FROM AVIONES
3
%%%% %%%% %%%% Etiquetas %%%% %%%% %%%% −−−−−−−− −−−−−−−− −−−−−−− −−−−−−−− −−−−−−−− −−−−−−− −−−−−−−− −−−−−−− −−−−−− En DB/2 de IBM las etiquetas toman los nombres de las columnas (col1, col2, col3) En SQL−SERVER las etiquetas quedarían así (LONGITUD*3.28, ENVERGADURA*3.28, VELO_CRUC/1.6) EJ: Relación entre la longitud y la envergadura de todos los aviones. SELECT LONGITUD/ENVERGADURA FROM AVIONES EJ: Seleccionar aquellos aviones cuya longitud supere a su envergadura en más de un 10%. SELECT * FROM AVIONES WHERE LONGITUD > ENVERGADURA*1.10 Funciones de columna Son funciones que operan con todas las filas que cumplen la condición expuesta en la claúsula WHERE. Su resultado es un único valor. Sintaxis: 1º) ([DISTINCT] ) 2º) (), donde es una expresión aritmética en la cual debe participar, al menos, una columna. 3º) COUNT(*) Funciones : MIN: Calcula el valor mínimo de una columna. MAX: Calcula el valor máximo de una columna. AVG: Calcula la media aritmética de una columna. SUM: Calcula la suma de todos los campos de una columna. 4
COUNT: Cuenta el nº de filas de una columna. A 3 2 3 4
B 5 8 7 3
COUNT(A)=COUNT(B) COUNT(A)=4, COUNT(B)=4 El COUNT de dos columnas de una misma tabla es igual. COUNT(*) sirve para obtener el nº de filas.
EJ: Seleccionar los valores mínimo y máximo de la columna que almacena las velocidades de crucero. SELECT MIN(VELO_CRUC), MAX(VELO_CRUC) FROM AVIONES EJ: Averiguar a que hora parte el primer vuelo hacia Madrid. SELECT MIN (HORA_SALIDA) FROM VUELOS WHERE DESTINO='MADRID' Regla que cumplen las funciones de columna La función de columna sólo podrá especificarse detrás de la particula SELECT o en la claúsula HAVING, pero nunca dentro de la claúsula WHERE. EJ: Se desea saber cuál es el vuelo que tiene la mínima hora de salida. SELECT * FROM VUELOS WHERE HORA_SALIDA=(SELECT MIN(HORA_SALIDA) FROM VUELOS) Claúsula GROUP BY−HAVING Sirve para dividir una tabla en grupos de filas que comparten características comunes. La sintaxis es: SELECT ,
5
FROM [WHERE ] [GROUP BY ] [HAVING ] EJ: Efectúese una SELECT que visualice el mínimo valor de hora de salida para cada uno de los diferentes destinos. SELECT DISTINCT DESTINO FROM VUELOS SELECT MIN(HORA_SALIDA) FROM VUELOS WHERE DESTINO LIKE '%' A continuación se muestra un ejemplo de lo que no se debe hacer: SELECT MIN(HORA_SALIDA) FROM VUELOS WHERE DESTINO IN (SELECT DISTINCT DESTINO FROM VUELOS) Sentencia GROUP BY: SELECT DESTINO, MIN(HORA_SALIDA) FROM VUELOS GROUP BY DESTINO
Tabla VUELOS Tabla auxiliar Tabla x 'MADRID' WHERE GROUP BY DESTINO Tabla y 'BARCELONA' Tabla z 'SEVILLA' GROUP BY crea una serie de subtablas compuestas por filas con el mismo valor para la columna de agrupamiento (en este ejemplo la columna DESTINO). Se aplicarán a continuación funciones de columna sobre cada subtabla de forma independiente. 6
MADRID, x BARCELONA, y SEVILLA, z No se puede poner en GROUP BY un campo que no se haya incluido en la sentencia SELECT. EJ: Obtener el origen del vuelo para cada uno de los vuelos que tienen la mínima hora de salida para cada uno de los destinos. EJ: Obtener el número de vuelos que existen para cada uno de los orígenes. SELECT ORIGEN, COUNT(*) FROM VUELOS GROUP BY ORIGEN Claúsula HAVING Permite elegir aquellos grupos que se quieren visualizar. EJ: Visualizar los grupos que tienen para cada uno de los orígenes la mínima hora de salida siendo anterior a las 12 horas. SELECT ORIGEN, MIN(HORA_SALIDA) FROM VUELOS GROUP BY ORIGEN HAVING MIN(HORA_SALIDA) < '12.00' HAVING no interferirá en la agrupación por filas de GROUP BY. EJ: Se desea seleccionar la hora de salida más temprana para cada origen y destino. SELECT ORIGEN, DESTINO, MIN(HORA_SALIDA) FROM VUELOS GROUP BY ORIGEN, DESTINO EJ: Visualizar los orígenes que tengan más de dos vuelos. SELECT ORIGEN FROM VUELOS GROUP BY ORIGEN
7
HAVING COUNT(*) > 2
EJ: Visualizar los vuelos de IBERIA que tengan más de 150 plazas libres. SELECT NUM_VUELO, SUM(PLAZAS_LIBRES) FROM RESERVAS GROUP BY NUM_VUELO HAVING NUM_VUELO LIKE 'IB%' AND SUM(PLAZAS_LIBRES)>150 ó también SELECT NUM_VUELO, SUM(PLAZAS_LIBRES) FROM RESREVAS WHERE NUM_VUELO LIKE 'IB%' GROUP BY NUM_VUELO HAVING NUM_VUELO 'IB%' AND SUM(PLAZAS_LIBRES)>150 TRATAMIENTO DE NULOS Operaciones aritméticas Cualquier operación aritmética sobre un campo nulo nos devolverá como resultado un valor nulo. Tomemos como ejemplo la siguiente tabla: NULOS COL_A 15 35 140 NULL NULL 7 33 NULL NULL
Funciones de columna Ignoran los campos NULL, exceptuando la función COUNT. SELECT AVG(COL_A) SELECT SUM(COL_A)/COUNT(*) AVG(COL_A)=46 SUM(COL_A)/COUNT(*)=25.5 Comparaciones Dos valores nulos no son iguales ni son distintos, sino indeterminados. SELECT * FROM NULOS WHERE COL_A=COL_B COL_A COL_B 35 35
9
SELECT * FROM NULOS WHERE COL_A<>COL_B COL_A COL_B 15 10 140 NULL NULL 100 7 110 33 60 SELECT * FROM NULOS WHERE COL_A IS NULL Esta orden visualiza todas las filas en las que el campo perteneciente a la columna COL_A es nulo. Ordenación Dependiendo del sistema gestor en uso los valores nulos serán los de mayor o los de menor peso. DB/2 de IBM NULL Mayor peso. en ordenación ascendente serán los últimos. SELECT COL_A FROM NULOS ORDER BY COL_A COL_A 7 15 33 35 140
10
NULL NULL NULL NULL
SQL−SERVER NULL Menor peso. En ordenación ascendente serán los primeros. SELECT COL_A FROM NULOS ORDER BY COL_A COL_A NULL NULL NULL NULL 7 15 33 33 140 DISTINCT No elimina los valores nulos repetidos. SELECT DISTINCT COL_A FROM NULOS COL_A 15
11
35 140 NULL NULL 7 33 NULL NULL Indices únicos Sobre una columna de índice único sólo está permitida la existencia de un valor nulo. CREATE UNIQUE INDEX IXNULOS ON NULOS (COL_A) Devolvería un error, ya que existe más de un campo con NULL. Para este caso los nulos se interpretan como valores iguales. GROUP BY Todos los nulos quedarán agrupados en el mismo grupo. SELECT COL_A, COUNT(*) FROM NULOS GROUP BY COL_A
COL_A COUNT(*) 15 1 35 1 140 1 NULL 4
12
71 33 1 Todos los valores NULL se agrupan y COUNT devuelve el número de filas que tenían NULL en COL_A. SUBSELECT Responde a la siguiente sintaxis: SELECT FROM WHERE (SELECT FROM WHERE ) Puede ser un operador de comparación o la claúsula IN Operadores de comparación: >,<,>=,<=,=,<> Restricciones: ha de exigirse que el resultado de la Subselect sea un único valor al usar como concatenador un operador de comparación. Si usamos IN puede devolver más de un valor. Cada Select se ejecuta una única vez, desde la más interna, hasta la más externa. EJ: Se desea recuperar las plazas libres que hay en cada vuelo MADRID−LONDRES del día 20/02/92. {Las plazas libres es un campo de la tabla de reservas. En la tabla de vuelos tenemos el origen y el destino de cada vuelo.} SELECT * FROM RESERVAS WHERE FECHA_SALIDA='20.02.1992' AND NUM_VUELO IN(SELECT NUM_VUELO FROM VUELOS WHERE ORIGEN='MADRID' AND DESTINO='LONDRES') ANY, ALL
13
Se usan para poder utilizar operadores de comparación con subselects que nos devuelvan más de un valor único como resultado. SELECT FROM WHERE {ANY/ALL} () Una expresión ANY es cierta si lo es para algún valor de los que devuelve la Subselect. Una expresión ALL es cierta si lo es para todos los valores que devuelve la Subselect.