Sintaxis avanzada de consultas SQL

Introducción

En esta lección, utilizaremos sentencias SELECT de SQL avanzadas con operadores, cláusulas y funciones adicionales para extraer y posiblemente manipular y formatear los datos antes de mostrarlos.

Discutiremos ciertas consultas complejas usando lo siguiente:

1. Cláusula WHERE para recuperar solo registros selectivos que cumplan ciertos criterios

2. Funciones escalares como TRIM y REPLACE para manipular los datos recuperados antes de mostrarlos

3. Agregue funciones como SUM, COUNT y AVG para agregar datos

4. Cláusula ORDER BY para secuenciar la salida de filas.

Nota: esta lección cubre solo consultas que usan una sola tabla. El uso de varias tablas con JOIN no se tratará en esta lección.

A lo largo de esta lección, se utilizará una tabla de muestra denominada PODelivery . La estructura de la tabla junto con algunos datos de muestra es como se muestra en la imagen a continuación. Esta tabla muestra las entregas que se han realizado en una línea de pedido de compra. Se supone que las órdenes de compra contienen varios artículos de línea y que cada artículo puede tener varias entregas.

Modificar o transformar datos sin procesar extraídos de tablas

Suponga que se requiere una lista de todos los POnumber y POitem. La consulta SQL para listar estos datos será:

SELECCIONE Pnumber, POitem FROM PODelivery;

El resultado de esta consulta es como se muestra a continuación:

Habría notado que hay algunas filas con los mismos datos para la combinación POnumber y POitem. Esto es de esperar ya que el modelo utilizado en nuestro ejemplo permite múltiples entregas en diferentes fechas con una combinación de número de pedido / artículo de pedido. Si solo se requieren filas únicas de la combinación POnumber / POitem, entonces la declaración original se puede modificar agregando la palabra clave DISTINCT . La declaración es como se muestra a continuación:

SELECCIONE NÚMERO DE PUNTO DISTINTO, PUNTO elemento DE POD

La salida posterior es como se muestra a continuación, con todos los duplicados eliminados.

En la instrucción SELECT, también podemos aplicar funciones a los campos seleccionados antes de mostrarlos. Considere la consulta a continuación:

SELECCIONE MÁS INFERIOR (POnumber), POitem, DateOfDelivery, (QuantityDelivered * 2) FROM PODelivery;

Esta consulta convierte todos los valores de POnumber a minúsculas y duplica la CantidadDelivered antes de mostrarlos.

El siguiente ejemplo que se muestra usa múltiples funciones en una sola declaración SQL para extraer datos de la tabla dada.

SELECCIONE PONumber, POitem, CONCAT (PONumber, '/', POItem) AS 'PO Identifier', REPLACE (PONumber, 'P', 'X'), DateOfDelivery, (QuantityDelivered * 2) FROM PODelivery;

El resultado se muestra en el siguiente diagrama. Aquí, la función CONCAT se usa para concatenar el campo PONumber con un carácter ‘/’ y luego el campo POItem. Mientras tanto, la función REPLACE reemplaza cualquier aparición de la letra ‘P’ con ‘X’ para los valores en el campo PONumber, como se puede ver, números como ‘P0001’ se han transformado en ‘X0001’.

Otras funciones útiles que se pueden utilizar para manipular los datos seleccionados incluyen las funciones TRIM, RIGHT, LEFT y LEN. Brevemente,

• La función TRIM elimina los espacios en blanco finales, por lo tanto, si el nombre del campo tiene el valor ‘Peter’, TRIM (name) da ‘Peter’
• La función DERECHA extrae una subcadena de caracteres ‘x’ de la derecha de una cadena, por lo tanto, DERECHA (TRIM (nombre), 2) da ‘er’
• De manera similar, la función IZQUIERDA funciona como la DERECHA, por lo tanto, LEFT (TRIM (nombre), 2) da ‘Pe’
• Finalmente, la función LEN proporciona la longitud del valor almacenado. LEN (nombre) aquí dará el valor 10 ya que los espacios en blanco finales también se contarán.

Tenga en cuenta que en SQL, las funciones también pueden contener otras funciones como parámetros. Se da un ejemplo en LEFT (TRIM (nombre), 2) DONDE las funciones LEFT y TRIM se utilizan juntas.

Clasificación

De forma predeterminada, las instrucciones SELECT no garantizan el orden en el que aparecerán los registros en la salida. Sin embargo, la cláusula ORDER BY se puede utilizar para garantizar el pedido en base a ciertos campos. Considere la siguiente declaración:

SELECCIONE * DE PODelivery ORDEN POR POnumber, POitem DESC;

Esta consulta ordenará la salida como se muestra en la imagen a continuación. La salida se ordena primero por POnumber en orden ascendente, y luego por POitem en orden descendente (como lo indica el calificador DESC. ASC se puede usar para ordenar los datos en orden ascendente. Si no se especifica DESC / ASC, entonces está en orden ascendente por defecto).

Visualización selectiva de registros

Hasta ahora, las sentencias SELECT que hemos usado han estado recuperando todos los registros de la tabla. Si queremos mostrar solo ciertos registros que cumplen con criterios específicos, entonces se debe usar la cláusula WHERE . Considere la siguiente declaración:

SELECCIONE POnumber DISTINTO, POitem FROM POelivery DONDE POnumber = 'P0001';

Esta consulta enumerará todas las combinaciones únicas de POnumber / POitem para POnumber P0001.

Hay muchos otros criterios de selección posibles además de la verificación de igualdad, por ejemplo, verificación de desigualdad, comparación entre valores y selecciones basadas en comodines . También se pueden aplicar varias condiciones junto con la cláusula WHERE usando conjunciones como AND , NOT y OR . Considere la siguiente declaración:

SELECCIONE DISTINCT POnumber, POitem FROM PODelivery DONDE POnumber = 'P0001' O POnumber = 'P0002';

El resultado de esta consulta es el siguiente:

A partir de los datos de prueba proporcionados, se habrá dado cuenta de que hay dos categorías de números de PO: una que comienza con una ‘P’ y otra con una ‘C’. Suponga que el primer carácter de este campo indica el tipo de orden de compra. Si ahora queremos mostrar todos los registros que contienen solo ‘P’ o ‘C’ como primer carácter, usaremos la siguiente declaración:

SELECCIONE DISTINCT POnumber, POitem FROM PODelivery DONDE POnumber LIKE 'C%';

Otra declaración alternativa puede ser la siguiente:

SELECT DISTINCT POnumber, POitem FROM PODelivery WHERE LEFT (POnumber, 1) = 'C';

Ambas consultas darán el mismo resultado que se muestra a continuación. Solo se mostrarán aquellos registros cuyo número de PO comience con ‘C’.

Notará que hemos utilizado el operador comodín ‘%’. Es un operador muy útil, especialmente para buscar datos de cadenas. Algunos de los operadores comodín comunes, además del operador ‘%’, disponibles en SQL son:

• _ – esto representa una coincidencia de un solo carácter – por ejemplo .. DONDE nombre LIKE ‘B_rt ‘ mostrará nombres como ‘Bert’, ‘Bart’, ‘Burt’.
• [ab] – esto representa una coincidencia si aparece el carácter aob – por ejemplo .. DONDE el nombre LIKE ‘[AB]% ‘ incluirá ‘Albert’, ‘Bernard’ …
• [^ ab]: esto representa una coincidencia si el carácter aob NO aparece, por ejemplo , DONDE NOMBRE COMO [^ AB]% enumerará todos los nombres que NO comiencen con ‘A’ o ‘B’ como ‘Catherine’, ‘James ‘etc.

Los comodines utilizados en el ejemplo anterior son válidos para MS SQL. Otros entornos SQL pueden implementar estas características de manera diferente; por ejemplo, mySQL y Oracle usan expresiones regulares ampliamente en lugar de comodines para la coincidencia de patrones.

Funciones agregadas

Las funciones agregadas actúan sobre datos numéricos y normalmente se usan junto con la cláusula GROUP BY. A continuación se muestra una declaración muy simple que ilustra esto:

SELECCIONE SUM (Cantidad entregada) COMO 'Cantidad total entregada' DESDE PODelivery;

O

SELECCIONE AVG (Cantidad entregada) 'Cantidad promedio entregada' DESDE PODelivery;

Observe que el encabezado de la columna aparece como (Sin nombre de columna); la declaración real se puede mejorar como se indica a continuación para dar más significado a los resultados:

SELECCIONE SUMA (Cantidad entregada) COMO 'Cantidad total entregada';

Aunque la declaración anterior parece simple, realiza un procesamiento pesado antes de producir la salida. Las declaraciones anteriores dan un total o un promedio del campo especificado de toda la tabla. Los valores SUM o AVG se basan normalmente en una clase en particular o se agregan para brindar información más significativa, por ejemplo, para sumar la cantidad total entregada para cada orden de compra. Para ello, se utiliza la cláusula GROUP BY junto con las funciones agregadas. La siguiente declaración sumará la cantidad entregada por combinación de número de pedido / artículo de pedido y generará un registro para cada combinación:

SELECCIONE Número de PO, POitem, SUM (Cantidad entregada) COMO 'Cantidad total entregada' DE PODelivery GROUP POR POnumber, POitem ORDEN POR POnumber, POitem;

En nuestro ejemplo, el número de pedido C0001 tiene dos entregas para el artículo 02 – 600 y 1300.

La salida que se muestra a continuación tiene una fila para la combinación C0001 / 02 con 1900 (600 más 1300) como la cantidad total entregada.

Considere otro ejemplo, donde es necesario enumerar el número de entregas para cada número de pedido. La función COUNT se puede utilizar para esto.

La declaración será la siguiente:

SELECCIONE NÚMERO DE PIEZA, CUENTA (*) COMO 'Total de entregas' DEL GRUPO DE ENTREGA POR PODO PEDIDO POR NÚMERO DE PIE;

La salida es como se muestra a continuación:

Si se ejecuta una versión ligeramente modificada de la declaración anterior,

SELECCIONE Pnumber, COUNT (POitem) COMO 'Total de entregas' DE PODelivery GROUP BY POnumber QUE TIENEN RECUENTO (POitem)> 1 PEDIDO POR POnumber;

La salida es como se muestra a continuación:

Observe que la única diferencia entre los resultados de estas dos declaraciones es que la fila con el número de PO ‘C0002’ falta en la segunda consulta. La cláusula HAVING COUNT (POitem)> 1 elimina este registro de la salida ya que solo hay una entrega para este POnumber.

Resumen de la lección

La instrucción SELECT se puede utilizar para extraer datos sin procesar, así como para manipular y procesar los datos sin procesar antes de mostrarlos.

En esta lección hemos visto:

• cómo se puede usar la cláusula DISTINCT para mostrar filas únicas de datos
• cómo se puede utilizar ORDER BY para ordenar los datos
• cómo se puede usar la cláusula WHERE para mostrar datos que coinciden con ciertos criterios junto con comodines
• cómo se pueden utilizar las funciones agregadas con la cláusula GROUP BY y
• otros ejemplos complejos que utilizan una combinación de todos los anteriores

Rodrigo Ricardo Editor y fundador