CHAPTER 3 GRAYSCALE MASK DESIGN
3.3 Grayscale Encoding Methods
SQL – CONSULTAS SELECT
1.- Introducción a las consultas
La gran mayoría de veces que interactuamos con las tablas de la base de datos es para hacerle preguntas del tipo: necesitamos una serie de columnas con unas condiciones que tienen que cumplir nuestros datos.
Por ejemplo, si tenemos una base de datos de Facturación, básicamente tendríamos una tabla de Clientes y otra de Facturas. Ante esta colección de datos, podríamos plantearnos una serie de preguntas a realizar a nuestras tablas:
• ¿Qué clientes son de Alicante?
• ¿Cuánto se han facturado nuestros clientes durante cada año?
• ¿Cuántos clientes han realizado menos facturas que la media de los clientes de Elche?
Para resolver todas estas dudas existe una sentencia esencial en el Sql que es el SELECT.
La sintaxis básica sería:
SELECT columna1, columna2,… FROM tabla-s
[WHERE criterios] [GROUP BY grupos]
[HAVING criterios para grupos] [ORDER BY ordenación]
Lo único obligatorio es la sentencia SELECT y el FROM, el resto de cláusulas son opcionales.
1.1.- Ejemplos
Para empezar a entender la sentencia Select vamos a ver algunos ejemplos básicos, basados en nuestra base de datos Clínica.
• Si queremos obtener todos los campos de la tabla Pacientes, tendríamos que escribir la siguiente orden:
SELECT * FROM Pacientes
• Queremos ver el Dni del paciente, y otra columna que muestre el Nombre del paciente, pero como título de la columna ponga “Nombre del Paciente”. Para ello utilizaremos un Alias, que vendrá representado por la palabra AS:
SELECT DNI, Nombre AS [Nombre del Paciente] FROM Paciente
• Obtener las visitas cuyo Importe está comprendido entre 100 y 200 €.
SELECT * FROM Visitas
WHERE Importe BETWEEN 100 AND 200
• Deseamos conocer los pacientes que hay de cada Ciudad.
SELECT Ciudad, COUNT(*) FROM Pacientes
GROUP BY Ciudad
• Queremos mostrar el Nombre del paciente y la Fecha de nacimiento ordenado de mayor a menor edad.
SELECT Nombre, FechaNac FROM Pacientes
ORDER BY FechaNac DESC
1.2.- El Diseñador de Consultas
Si lo que queremos es no utilizar el Editor de SQL y escribir las sentencias, podemos utilizar el Diseñador gráfico de Consultas, que con un entorno visual hace más cómodo la creación de consultas. Pero para ello necesitaremos tener instalado el software Management Studio.
Para trabajar con Diseñador de consultas, pulsamos sobre el botón de la barra de herramientas Nueva consulta, y aparece la pantalla en blanco del Editor. Ahora pulsamos botón derecho sobre la página en blanco, y elegimos la opción Diseñar consulta con el editor.
Apareciendo una pantalla donde podremos elegir entre las tablas de nuestra Base de datos Clínica.
Si por ejemplo, queremos crear la última consulta del apartado anterior, donde mostraba la fecha de nacimiento y el
nombre del paciente. Tendremos que seleccionar las siguientes opciones.
En la primera columna indicaremos el nombre de los campos que aparecerán en la consulta SELECT. En la columna que pone Tipo de orden, indicaremos si queremos ordenarlo ascendente o descendente por esa columna. La columna Filtro, serviría para indicar las condiciones que deben cumplir los registros que va a mostrar la consulta.
Cuando pulsemos Aceptar, ese texto se trasladará al Editor de consultas, y podremos ejecutar la consulta, con el botón de ejecutar o pulsando F5, y ver los resultados de la misma.
2.- Select
Antes hemos visto una sintaxis más básica, pero una sintaxis más completa sería:
SELECT [DISTINCT | ALL]
[{TOP entero | TOP entero PERCENT}] [INTO <Nueva table>]
FROM <Tabla-s> [WHERE <Condición>]
[GROUP BY [ALL] <expresión por la que agrupo>] [HAVING <condiciones para los grupos>]
[ORDER BY {<expresión order by> | <posición> [ASC | DESC]}]
ALL
Cuando utilizamos ALL en una sentencia SELECT, el resultado mostrará todos las filas, incluyendo las filas duplicadas (si hay dos filas iguales aparecerán las dos). Si ejecutamos la siguiente sentencia.
SELECT ALL Ciudad FROM Pacientes
Obtendríamos el siguiente resultado:
Podemos comprobar que la Ciudad Alicante aparece varias veces. Esto ocurre si utilizamos ALL, y también si no colocamos nada.
Pero si lo que queremos evitar que aparezcan duplicados (que Alicante sólo apareciese una vez), tendríamos que utilizar DISTINCT después del Select. Por ejemplo, la siguiente sentencia:
SELECT ALL Ciudad FROM Pacientes
El resultado sería el siguiente:
TOP
Utilizaremos el TOP para mostrar las <n> primeras filas o el <n> porcentaje de las filas ordenadas bajo un criterio ORDER BY.
SELECT TOP 10 DniPaciente, Fecha, Importe FROM Visitas
Pero si lo que queremos es ver 50 % de las visitas con un Importe menor, la sentencia que tendríamos que escribir sería la siguiente:
SELECT TOP 50 PERCENT DniPaciente, Fecha, Importe FROM Visitas
ORDER BY Importe
Como resultado nos mostraría las 15 filas con importe más bajo. La cantidad de filas a mostrar dependerá del número total de registros que tenemos en la tabla. Así, en el ejemplo, como tenemos un total de 30 registros, la consulta nos mostrará un total de 15 filas como resultado.
INTO
Sirve para crear una tabla e inserta en ella los registros de la consulta. Veamos un ejemplo, imaginemos que queremos crear una tabla llamada Historico con aquellas Visitas realizadas en 2012.
SELECT * INTO Historico FROM Visitas
La consulta lo que realiza es crear una tabla con el nombre Historico, y que podremos ver en el desplegable de las tablas (puede ser necesario que pulsemos actualizar para poder verlas todas).
2.1.- Cláusulas (From, Where, Group By, Order By,
Having)
FROM
Sirve para especificar las tablas de las que se van obtener los registros y los campos de la consulta.
FROM Pacientes P
Podemos utilizar Alias para las tablas de la consulta colocando una letra a continuación de la tabla, y a partir de ahí se puede utilizar el la letra para hacer referencia a esa tabla en concreto.
Si queremos consultar datos que aparecen en dos tablas, existe dos formas de realizarlo:
a) Utilizando el WHERE, y recordando el tema de Algebra Relacional, se realiza el Producto cartesiano de las dos tablas y luego realiza una Selección.
FROM Paciente P, Visitas V WHERE P.Dni = V.DniPaciente
b) Utilizando el INNER JOIN, que es una forma más eficiente, ya que supone una Reunión (en operativa de Algebra Relacional).
FROM Paciente P INNER JOIN Visitas V ON P.Dni = V.DniPaciente
WHERE
En la cláusula WHERE incluiremos los criterios que deben tener los registros que queremos mostrar en nuestra consulta Select. Veamos varios ejemplos:
GROUP BY
Cuando queremos agrupar registros por uno o varios campos donde se repite la información, y para cada nivel de agrupamiento realizar una serie de operaciones (contar, sumar, media, máximo o mínimo)
SELECT Turno, Ciudad, COUNT(*) FROM Pacientes
GROUP BY Turno, Ciudad
ORDER BY
Esta cláusula sirve como condición de ordenación para los registros que mostremos en la consulta SELECT. Se puede poner a continuación del ORDER BY las columnas que deseemos, pero separadas por comas, e indicando si lo queremos ASC (por defecto sino ponemos nada) o DESC –ascendente o descendente respectivamente-. Veamos un ejemplo:
SELECT * FROM Pacientes
ORDER BY Turno DESC, Ciudad, Nombre
La consulta mostraría todas las columnas de Pacientes, pero ordenadas por Turno descendentemente, y para los que son de un mismo Turno, el siguiente orden sería la Ciudad, y para los que son de la misma Ciudad y Turno, la última ordenación sería por el Nombre del paciente.
HAVING
El HAVING viene a ser como el WHERE para los registros, pero para los grupos (GROUP BY) e indicar que grupos queremos que aparezcan. Por ejemplo:
SELECT Turno, Ciudad, COUNT(*) FROM Pacientes
GROUP BY Turno, Ciudad HAVING COUNT(*)>5
Solo mostraría aquellos grupos, de Turno y Ciudad, donde viviesen más de 5 pacientes.
3.- Operadores
Estos operadores los utilizaremos para indicar las condiciones en las cláusulas WHERE y HAVING.
Aritméticos
En las columnas que mostramos en el Select podremos realizar operaciones con distintos campos de nuestras tablas. Veamos un ejemplo:
SELECT Nombre + ‘ – ‘ + Ciudad, Turno*15 FROM Pacientes
El resultado de la consulta será:
En el ejemplo anterior vemos cómo podemos combinar campos alfanuméricos utilizando el
+
, y como podemos hacer operaciones matemáticas con los operadores+
,-
,*
y/
.De comparación (=, >, <, >=, <=) Veamos varios ejemplos:
WHERE Ciudad = ‘Alicante’ WHERE FechaNac=’12/08/2013’ HAVING SUM(Importe)>200
BETWEEN
El operador BETWEEN sirve para seleccionar solo los registros que están comprendidos entre dos valores (incluidos los dos).
WHERE Importe BETWEEN 4 AND 38
WHERE Fecha BETWEEN ‘01/01/2013’ AND 31/01/2013’
IN
Cuando en los criterios queremos que el campo se igual a una lista de valores que le proporcionamos. Veamos unos ejemplos:
WHERE Importe IN (100, 205, 300)
El ejemplo anterior muestra aquellas visitas cuyo Importe son 100, 205 o 300
WHERE Ciudad IN (‘Alicante’, ‘Elche’, ‘Murcia’, ’Valencia’)
LIKE
Cuando no queremos que la comparación sea exacta, sino que tendrá algunos caracteres en común. Utilizaremos dos símbolos como comodines:
%
(comodín que puede sustituir a varios caracteres) y_
(sólo permite la sustitución de un carácter). Para entenderlo mejor veamos algunos ejemplos:WHERE Nombre LIKE ‘An%’
Mostraría aquellos pacientes cuyo Nombre empieza por ‘An’, por ejemplo aparecerían Antonio, Andrés,…
Si por ejemplo, queremos mostrar aquellos pacientes cuya Ciudad contiene una ‘e’, escribiríamos el siguiente código:
WHERE Ciudad LIKE ‘%e%’
En este siguiente caso mostrará solo aquellos Nombres de pacientes que empiecen por ‘Antoni’ y terminen por cualquier letra (por ejemplo, Antonio o Antonia).
WHERE Nombre LIKE ‘Antoni_’
IS NULL
Para comprobar en una consulta si los valores de un campo contienen valores nulos tendremos que utilizar IS NULL en el WHERE. Por ejemplo, si quiero ver aquellos pacientes que no tienen ningún valor en el campo Ciudad, tendremos que escribir la siguiente sentencia SQL:
SELECT * FROM Pacientes WHERE Ciudad IS NULL
AND, OR y NOT
Utilizaremos AND cuando queramos que nuestros registros cumplan dos condiciones (más restrictivo), OR cuando deseemos que se cumpla cualquiera de las dos condiciones que indiquemos y NOT para negar la condición que coloquemos. Veamos varios ejemplos:
Si queremos mostrar aquellos pacientes cuya Ciudad es Valencia y Turno es igual a 3.
WHERE Ciudad=’Valencia’ AND Turno=3
Si lo que queremos es obtener aquellos pacientes cuyo Nombre empieza por una B o la Ciudad es Elche, la cláusula Where sería la siguiente:
WHERE Nombre LIKE ‘B%’ OR Ciudad=’Elche’
Y para mostrar aquellos Pacientes que no pertenecen al turno 2, seria:
WHERE NOT Turno=2
direcciones y teléfonos de los Clientes y Proveedores (que se encuentran en dos tablas distintas).
Veamos un ejemplo utilizando nuestra base de datos Clínica.
SELECT Nombre, Direccion FROM Pacientes
WHERE Ciudad=’Alicante’ UNION
SELECT Nombre, Direccion
FROM Pacientes P INNER JOIN Visitas V ON P.Dni=V.DniPaciente
En la tabla resultados nos mostrará conjuntamente el Nombre y la Dirección de los pacientes que son de Alicante y de los pacientes que han realizado alguna visita.
EXCEPT
Sería el equivalente a la Diferencia (operación de algebre relacional), donde mostrará todos aquellos registros de la primera consulta y los que no están en la segunda.
SELECT Nombre, Direccion FROM Pacientes
EXCEPT
SELECT Nombre, Direccion
FROM Pacientes P INNER JOIN Visitas V ON P.Dni=V.DniPaciente
El resultado de esta consulta mostrará aquellos pacientes que nunca han realizado una visita. En la primera parte tenemos TODOS los pacientes, y en la segunda SOLO los que han realizado alguna visita.
4.- Funciones
Las Funciones la podremos utilizar principalmente en el SELECT, WHERE y GROUP BY. A continuación veremos las más importantes y algunos ejemplos de las mismas.
4.1.- Cadena
LEFT(cadena, n)
SELECT Nombre, LEFT(Nombre, 3) AS Tres_Letras, Ciudad FROM Pacientes
El resultado sería el siguiente:
LEN(cadena)
Nos da la longitud de la cadena especificada. Por ejemplo, si queremos mostrar aquellos pacientes cuyo Ciudad es de 5 caracteres, escribiremos el siguiente código:
SELECT * FROM Pacientes WHERE LEN(Ciudad)=5
LOWER(cadena)
Convierte las letras de la cadena a minúsculas. Por ejemplo, si quisiera ver el Nombre de los Pacientes en minúsculas, pondría:
SELECT LOWER(Nombre) FROM Pacientes
RIGHT(cadena, n)
Aquí obtendríamos los n caracteres de la derecha de la cadena. Por ejemplo, para ver los 4 últimos caracteres del Nombre del paciente sería:
SELECT RIGHT(Nombre, 4) FROM Pacientes
UPPER(cadena)
Convierte a mayúsculas los caracteres de la cadena. Así, si deseamos convertir a mayúsculas el Concepto de las visitas, tendríamos que escribir:
SELECT UPPER(Concepto) FROM Visitas
4.2.- Numéricas
ABS(a)
Obtendremos el valor absoluto de un número a. En nuestras tablas no tenemos valores negativos para poder obtener el valor absoluto, pero suponiendo que tuviésemos un campo llamado Numero, que incluyese números negativos, la sentencia ABS(Numero), nos daría siempre los valores absolutos del número, o sea, que si vale -3 daría 3.
RAND(semilla)
Aquí lo que hace con el Rand es obtener un número aleatorio entre 0 y 1 con una semilla dada (la semilla puede ser cualquiera para que cambie los números aleatorios generados).
Veamos varios ejemplos. Si quiero un número aleatorio, propongo una semilla cualquiera, por ejemplo 200. Escribimos la siguiente sentencia:
SELECT RAND(200) FROM Pacientes
Y obtendríamos el mismo número aleatorio entre 0 y 1 para todos los registros, ya que la semilla es la misma:
Pero si lo que quiero es un número aleatorio distinto cada vez, puedo utilizar como semilla del Rand un campo
numérico de mi tabla. Por ejemplo, puedo obtener un número distinto para cada visita, utilizando el Nvisita como semilla.
SELECT Fecha, DniPaciente, Importe, RAND(Nvisita) FROM Visitas
Complicando más el uso del Rand, podría utilizarlo para crear números aleatorios entre 2 valores. Por ejemplo, si utilizo yo RAND(Numero)*40, lo que estoy obteniendo es un número aleatorio entre 0 y 40 (ya que al ser 1 el valor máximo del Rand al multiplicarlo por 40, el límite superior se establece en 40).
Veamos que realiza el siguiente ejemplo:
SELECT Nvisita, Fecha, Concepto, Importe, RAND(Importe)*100 AS Descuento,(RAND(Importe)*100)*Importe/100 AS Dcto
FROM Visitas
El resultado sería el siguiente:
ROUND(a,b)
Redondea el número a con precisión b, por ejemplo, si tenemos un número (423, 6239), y ponemos ROUND(423.6239, 2) obtendríamos 423.62, porque le hemos indicado que tenga dos decimales. Un ejemplo de Sql sería:
SELECT Importe, ROUND(Importe,2) FROM Visitas
SQRT(a)
Obtendríamos la raíz cuadrada del número seleccionado. Por ejemplo, SQRT(9) nos daría 3.
SQUARE(a)
Nos daría el cuadrado de un número, con SQUARE(2) obtendríamos 4.
4.3.- Fecha y hora
DATEADD(tipo, a, fecha)
Esta función añade a unidades de fecha del tipo dado (DAY, MONTH o YEAR) a la fecha dada. Veamos varios ejemplos:
• Si quiero añadir 7 días a un campo fecha.
SELECT Fecha, DATEADD(DAY, 7, Fecha) FROM Visitas
• Para añadir 2 años a la fecha para ver cómo quedaría.
SELECT Fecha, DATEADD(YEAR, 2, Fecha) FROM Visitas
DATEDIFF(tipo, fecha1,fecha2)
Indica el número de unidades de fecha del tipo dado entre dos fechas. Por ejemplo, para saber los días que quedan entre la Fecha de la visita y fin de año, escribiríamos lo siguiente:
FROM Visitas
El resultado sería el siguiente:
DATENAME(tipo, fecha) Da el nombre, según el tipo dado, de la fecha especificada. Si pongo MONTH, me dará el
nombre del mes (Enero, Febrero,…). Por ejemplo, la siguiente sentencia SQL, nos mostraría el nombre del mes de cada fecha:
SELECT Fecha, DATENAME(MONTH, Fecha)
FROM Visitas
GETDATE()
Devuelve la fecha y hora actuales del sistema. Por ejemplo, si quiero saber el número de años que hay desde cada paciente nació hasta el día de hoy, escribiremos lo siguiente:
SELECT FechaNac, DATEDIFF(YEAR, FechaNac, GETDATE()) AS Edad
FROM Pacientes
DAY(fecha)
Nos devolvería el día de una fecha determinada.
MONTH(fecha)
Nos devuelve el número del mes, por ejemplo la fecha ‘12/03/2012’ nos devolvería 3, pero como alfanumérico.
YEAR(fecha)
Nos devolvería el año de la fecha.
Veamos un ejemplo dónde utilizamos las tres funciones:
SELECT Fecha, DAY(Fecha) AS Dia, MONTH(Fecha) AS Mes, YEAR(Fecha) AS Año
5.- Consultas multitabla
Cuando trabajamos con datos de dos o más tablas necesitamos implementar relaciones lógicas entre ellas (JOINS).
Una forma poco eficiente de realizar esos enlaces entre tablas, es unir la clave ajena con la clave principal en el WHERE de la consulta (lo que, como ya comentamos, realiza un Producto cartesiano y luego una selección, con lo que perdemos efectividad). Veamos un ejemplo:
SELECT Dni, Nombre, Fecha, Importe FROM Pacientes, Visitas
WHERE Dni=DniPaciente
Imaginemos que tenemos dados de alta 500 pacientes y 3000 visitas, entonces lo que haría el Sql es el producto cartesiano, obteniendo un total de 1.500.000 registros (500 x 3000), y luego pasaría hacer una selección –where- solo de los que cumplen la condición, que son los 3000 registros que mostraría.
Cuando trabajamos con varias tablas puede que coincida el mismo nombre de campo, por lo que tenemos que indicar el nombre de la tabla delante del campo. Por ejemplo, si tengo dos tablas que contienen el campo Codigo, tendré que hacer referencia a el de la siguiente forma:
Y si utilizásemos un alias para las tablas, haríamos referencia de la siguiente manera:
FROM Clientes C, Pedidos P,… WHERE C.Codigo=3
JOINS de dos tablas
Solo mostrará, de manera más eficiente, los registros dónde coinciden los campos por los cuales se relacionan las dos tablas. Sería el equivalente a la operación de Reunión en Algebra Relacional.
El JOIN forma parejas de registros haciendo coincidir los campos de los contenidos relacionados. Veamos un ejemplo, donde solo mostrará los registros de Pacientes relacionados con los de Visitas:
SELECT Dni, Nombre, Fecha, Importe
FROM Pacientes P INNER JOIN Visitas V ON P.Dni=V.DniPaciente
En el caso de que tuviéramos que relacionar 3 tablas, la forma de realizar el Inner Join sería el siguiente.
FROM Tabla1 T1 INNER JOIN (Tabla2 T2 INNER JOIN Tabla3 T3 ON T2.Campo23 = T3.Campo3) ON T1.Campo1=T2.Campo21
JOIN EXTERNO izquierdo y derecho
Este tipo de enlace nos serviría para responder a preguntas del tipo: 1. ¿Qué pacientes no han realizado ninguna visita?
2. ¿Qué visitas no se corresponden con ningún paciente relacionado?
Para resolver a estas preguntas necesitamos utilizar los JOINs izquierdo y derecho: LEFT JOIN y RIGHT JOIN.
El LEFT JOIN muestra todos los registros relacionados en ambas tablas, y además los registros de la tabla de la izquierda que no están relacionados con los de la derecha. Con el siguiente código Sql y resultado lo entenderemos mejor:
SELECT Dni, Nombre, Fecha, Importe
De los resultados de la consulta, vemos que en las columnas Fecha e Importe, hay dos NULL, que son los registros que no han realizado ninguna visita a la clínica. Entonces para resolver la primera pregunta escribiríamos el siguiente código Sql (tendremos que hacer que cualquier campo de la tabla de la derecha sea Null):
SELECT Dni, Nombre
FROM Pacientes P LEFT JOIN Visitas V ON P.Dni=V.DniPaciente WHERE Fecha IS NULL
De la misma forma que hemos dado respuesta a la primera pregunta, la segunda se resolvería con la siguiente instrucción Sql:
SELECT Fecha, DniPaciente, Concepto, Importe
FROM Pacientes P RIGHT JOIN Visitas V ON P.Dni=V.DniPaciente
WHERE Nombre IS NULL
6.- Consultas de agrupar
Cuando tenemos campos o columnas donde se repiten valores, podremos aplicar funciones de agregado que generan valores de datos resumidos.
Las funciones agregadas que más utilizaremos serán las siguientes: • SUM: suma los valores para un determinado grupo. • AVG: calcula la media.
• COUNT: cuenta el número de registros.
• MAX: obtenemos el máximo de una serie de valores. • MIN: nos dará el valor mínimo de un conjunto de datos.
Las funciones no se podrán utilizar en el WHERE, sólo en el SELECT y en el HAVING. Las funciones SUM y AVG sólo se podrán utilizar con campos numéricos.
Con estas funciones se podrá contestar a preguntas del tipo: - ¿Número de Pacientes que hay por cada Ciudad?
- ¿Cuál es la cantidad media que se han gastado los pacientes por cada Turno?
- ¿Cuál es la cantidad máxima que se ha gastado cada paciente en todas sus visitas?
6.1.- Función COUNT
La sintaxis será la siguiente:
COUNT ( { [ ALL | DISTINCT ] expresión | * } )
• ALL: aplica la función contar a todos los valores, pero no tiene en cuenta los