|
|
LMD (Lenguaje de Manipulación de Datos)
|
Programación. Claúsula IN. Claúsula BETWEEN. Claúsula LIKE. Expresiones aritméticas. Funciones de columna.
Agregado: 31 de AGOSTO de 2003 (Por Michel Mosse) | Palabras: 8969 | Votar | Sin Votos | Sin comentarios | Agregar Comentario
Categoría: Apuntes y Monografías > Computación > Varios >
Material educativo de Alipso relacionado con LMD (Lenguaje Manipulación Datos)
Enlaces externos relacionados con LMD (Lenguaje Manipulación Datos)
BASES DE DATOS
2 Cuatrimestre
LMD (Lenguaje de Manipulación de Datos)
SELECT
SELECT [DISTINCT] <lista_columnas> | *
FROM <lista_tablas>
[WHERE <predicado>]
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')
ó 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:
<nombre_columna> 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 *
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
┌──┐ ┌──┐ ┌──┐
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) <f_columna> ([DISTINCT] <nombre_columna>)
2) <f_columna> (<expresión>), donde <expresión> es una expresión aritmética en la cual debe participar, al menos, una columna.
3) COUNT(*)
Funciones
<f_columna>:
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.
COUNT: Cuenta el n de filas de una columna.
|
A |
B |
|
3 |
5 |
|
2 |
8 |
|
3 |
7 |
|
4 |
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 <lista_columnas>, <funciones_de_columna>
FROM <lista_tablas>
[WHERE <predicado>]
[GROUP BY <lista_columnas>]
[HAVING <predicado>]
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'
<f_columna>
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.
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
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 |
COL_B |
|
15 |
10 |
|
35 |
35 |
|
140 |
NULL |
|
NULL |
100 |
|
NULL |
NULL |
|
7 |
110 |
|
33 |
60 |
|
NULL |
NULL |
|
NULL |
NULL |
SELECT COL_A+COL_B
FROM NULOS
COL_A+COL_B
25
70
NULL
NULL
NULL
117
93
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
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
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
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
7 1
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 <lista_columnas>
FROM <lista_tablas>
WHERE <nombre_columna> <CONCATENADOR> (SELECT <nombre_columna>
FROM <lista_tablas>
WHERE <Predicado>)
<CONCATENADOR>
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
Se usan para poder utilizar operadores de comparación con subselects que nos devuelvan más de un valor único como resultado.
SELECT <lista_columna>
FROM <lista_tablas>
WHERE <nombre_columna> <CONCATENADOR> {ANY/ALL} (<Subselect>)
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.
3>ANY(2,5,7) Cierto
3=ANY(2,5,7) Falso
3>ALL(2,5,7) Falso
3<ALL(9,10,11) Cierto
EJ: Se quiere recuperar los aviones cuya longitud sea mayor que la envergadura de todos ellos.
SELECT *
FROM AVIONES
WHERE LONGITUD > ALL (SELECT ENVERGADURA
FROM AVIONES)
ó también
SELECT *
FROM AVIONES
WHERE LONGITUD > (SELECT MAX(ENVERGADURA)
FROM AVIONES)
=ANY e IN tienen la misma función.
3=ANY(2,3,5) y 3 IN (2,3,5) devuelven ambos Cierto.
Subselects correlacionadas
Son un tipo especial de subselect. La sintaxis es similar:
SELECT <lista_columnas>
FROM <nombre_tabla_externa>
WHERE <nombre_columna> <CONCATENADOR> (SELECT <nombre_columna>
FROM <nombre_tabla_interna>
WHERE <Predicado>)
En <Predicado> habrá una sentencia del tipo
<nombre_columna_tabla_interna> <operador> <nombre_columna_tabla_externa>
Las formas de ejecutar una subselect ordinaria y una correlacionadas son diferentes. Las subselects correlacionadas obedecen al siguiente algoritmo:
ALGORITMO Subselect_Correlacionada
1 Seleccionar fila de tabla externa
2 Ejecutar SELECT interno
3 Evaluar la condición del WHERE externo
- Cierto: la fila seleccionada en 1 será una fila de salida
4 Si existe alguna fila más en la tabla externa ir al paso 1
EJ: Se desea recuperar las reservas cuyo número de plazas libres sea mayor que la media para ese mismo vuelo.
SELECT *
FROM RESERVAS, A
WHERE PLAZAS_LIBRES > (SELECT AVG(PLAZAS_LIBRES)
FROM RESERVAS
WHERE NUM_VUELO=A.NUM_VUELO)
RESERVAS
NUM_VUELO FECHA_SALIDA PLAZAS_LIBRES
IB740 20.02.92 5
IB740 25.02.92 15
IB740 03.03.92 10
AVG(PLAZAS_LIBRES)=10
Alias
Es un sobrenombre que se le da a una tabla y que debe ser único para toda la consulta. Se escribe dejando un blanco detrás del nombre de la tabla a la cual se va a calificar.
EJ: Se quiere recuperar los aviones que tienen menos de 1 hora y cuarto de recorrido como término medio.
VUELOS
NUM_VUELO ORIGEN DESTINO DISTANCIA
B747 MADRID LONDRES 10000
B747 MADRID PARIS 4000
AVIONES
NUM_VUELO VELO_CRUC
............ ............
v=e/t, t>e/v, 1.25>e/v, v*1.25 > AVG(DISTANCIA)
SELECT AVIONES SELECT VUELOS
SELECT *
FROM AVIONES
WHERE 1.25*VELO_CRUC > (SELECT AVG(DISTANCIA)
FROM VUELOS
WHERE NUM_VUELO=AVIONES.NUM_VUELO)
EXISTS-NOT EXISTS
Se define para comprobar la existencia o ausencia del valor devuelto por una Subselect. Una expresión con EXIST devuelve Cierto si la Subselect nos devuelve al menos un valor.
WHERE EXISTS (<Subselect>) Cierto
EJ: Seleccionar toda la información de vuelos para aquellos que tengan origen Madrid y en los que queden plazas libres.
SELECT *
FROM VUELOS
WHERE ORIGEN='MADRID'
AND EXISTS (SELECT *
FROM RESERVAS
WHERE PLAZAS_LIBRES > 0
AND NUM_VUELO=VUELOS.NUM_VUELO)
EJ: Obtener los tipos de avión y capacidades para aquellos en los que queden menos de 30 plazas libres (JOIN).
ORDER BY
Se define para ordenar la salida de una consulta por los campos que se especifiquen a continuación.
Sintaxis:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
ORDER BY <especificación de columna><orden>{,<especificación de columna><orden>}
<especificación de columna>=<nombre de columna>|<posición de columna>
<orden>=ASC|DESC
Ej: Obtener el número de plazas libres que quedan para cada vuelo y ordenar el resultado de más a menos plazas libres. Para igual número de plazas ordénese por número de vuelo.
SELECT NUM_VUELO, SUM(PLAZAS-LIBRES)
FROM RESERVAS
GROUP BY NUM_VUELO
ORDER BY 2 DESC, NUM_VUELO
UNION-UNION ALL
Se define para recuperar, usando una única consulta, información que se obtiene a partir de más d una consulta.
Sintaxis:
<SELECT>
UNION [ALL]
<SELECT>
{UNION[ALL]
<SELECT>}
Características:
Cada SELECT devuelve un conjunto de filas. La unión será la tabla resultado.
Condiciones de cada estructura SELECT:
- Todas deben ser iguales o compatibles una a una. Esto supone que por cada columna tengamos un único tipo de dato.
- Pueden ser completas (WHERE, GROUP BY, ...), exceptuando la claúsula ORDER BY, que se ubicará al final de la última SELECT.
UNION sin ALL proporciona un resultado sin filas duplicadas.
Ej: Sacar una lista de todas aquellas ciudades para las que haya vuelo, ordenadas alfabéticamente.
SELECT ORIGEN
FROM VUELOS
UNION
SELECT DESTINO
FROM VUELOS
ORDER BY 1
Catálogo del sistema o diccionario de datos:
Es el alma de un sistema gestor. Se define como un conjunto de tablas que forman una base de datos, y son definidas y mantenidas automáticamente por el sistema gestor. Sirven para almacenar información sobre los objetos definidos por los usuarios.
SELECT *
FROM VUELOS
1- El sistema busca en el catálogo si existe la tabla VUELOS.
2- Verifica si el usuario tiene acceso a esa información.
3- Se pregunta cuáles y cuántas columnas tiene la tabla VUELOS.
DB2 IB
SYSTABLES: una fila por cada tabla definida en la instalación.
SYSCOLUMNS: una fila por cada columna definida.
SYSINDEXES: una fila por cada índice definido.
SYSVIEW: una fila por cada vista.
SYSTABAUTH: una fila por cada autorización definida.
Todas las tablas son directamente consultadas por usuarios autorizados.
ADM (Administrador): es la persona que concede autorizaciones a los usuarios.
Un usuario autorizado puede efectuar operaciones del tipo:
SELECT *
FROM SYSTABLES
WHERE NAME='RESERVAS'
NAME DBNAME CARD
RESERVAS AEROPUERTO ..........
DBNAME: Nombre de la BdD a la que pertenece la tabla.
CARD: N de filas de la tabla.
OWNER: Usuario creador de la tabla.
SELECT *
FROM SYSCOLUMNS
WHERE DBNAME='RESERVAS'
Nos da la información sobre todas las columnas que pertenecen a la tabla reservas.
NAME TBNAME COL_NO COL_TYPE LENGTH NULLS
NUM_VUELO RESERVAS 1 CHAR 6 N
FECHA_SALIDA RESERVAS 2 DATE 8 N
TBNAME: Nombre de la tabla.
COL_NO: Posición de la columna en la tabla.
COL_TYPE: Tipo de dato
LENGTH: Longitud del dato de la columna.
NULLS: Indica si se permite valor nulo.
Ej: Obténgase la última hora de salida para cada destino de los vuelos realizados por aviones capaces de almacenar más combustible que un tercio de la media que pueden almacenar los demás aviones.
SELECT DESTINO, MAX(HORA_SALIDA)
FROM VUELOS
WHERE TIPO_AVION IN (SELECT TIPO
FROM A
WHERE COMBUSTIBLE>1/3*(SELECT AVG(COMBUSTIBLE)
FROM AVIONES
WHERE TIPO<>A.TIPO)
Ej: Crear una vista sobre la tabla vuelos con las columnas ORIGEN y DESTINO para aquellos vuelos que no sean de IBERIA. Visualizar el contenido de la lista para los vuelos que no partan de Madrid. Borrar la vista.
CREATE VIEW V_VUELOS
(V_ORIGEN, V_DESTINO)
AS SELECT ORIGEN, DESTINO
FROM VUELOS
WHERE NUM_VUELO NOT LIKE 'IB%'
SELECT *
FROM V_VUELOS
WHERE V_ORIGEN<>'MADRID'
DROP VIEW V_VUELOS
Ej: Visualice los tipos de avión , el doble de su longitud y la mitad de su envergadura, para aquellos aviones con envergadura mayor que la media y que realizan vuelos desde o hacia Barcelona, ordenándolos de mayor a menor longitud.
SELECT TIPO_AVION, 2*LONGITUD, .5*ENVERGADURA
FROM AVIONES, VUELOS
WHERE ENVERGADURA>(SELECT AVG(ENVERGADURA)
FROM AVIONES)
AND
AVIONES.TIPO_AVION=VUELOS.TIPO_AVION
AND
(ORIGEN='BARCELONA' OR DESTINO='BARCELONA)
ORDER BY 2 DESC
