Bases de datos: Lenguaje de consultas SQL

EMPEZAR LA CASA POR EL TEJADO Del código máquina a los lenguajes de alto nivel.

Img 0. Del código máquina al lenguaje de alto nivel. Creación propia.

De los sistemas operativos con comandos a las interfaces gráficas de usuario (GUI)

Img 1. Del modo consola al modo gráfico. Creación propia.

Estas y otras evoluciones se han explicado a lo largo de la asignatura. En todas ellas se persigue el mismo objetivo, facilitar el manejo de aplicaciones informáticas a los usuarios. En esta unidad se ha empezado la casa por el tejado. Primero se ha estudiado el intérprete o aplicación para crear bases de datos (OOo Base) y después, en este último tema, el lenguaje textual para manejar las bases de datos (SQL). El lenguaje SQL o lenguaje de consultas es anterior a los gestores de bases de datos.

Img 2. De OOo Base a SQL. Creación propia.

1. Definición y sintaxis

Breve historia Cuando, en 1970, se crean las bases de datos relacionales, también se crea un lenguaje que permita realizar operaciones de consulta y modificación de los datos. El nacimiento de los SGBD significó la implementación del lenguaje de consultas y, aunque IBM trabajó en un antecesor denominado SEQUEL, fue Oracle quién primero lo lanzó al mercado. Su primera versión estandarizada por el ANSI se lanzó en 1986. A partir de ésta, se incorporan nuevos comandos y cláusulas. Para ampliar esta breve historia lee el siguiente artículo: SQL_historia

El lenguaje de consultas estructurado (Structured Query Language) es el lenguaje declarativo de alto nivel diseñado para realizar operaciones con conjuntos de registros en bases de datos relacionales mediante el uso del álgebra y del cálculo relacional.

Las consultas diseñadas en los SGBD son implementaciones del lenguaje SQL. De hecho, desde cualquier consulta en OOo Base se puede acceder a su vista en SQL haciendo clic en botón derecho.

Img 3. Editar en vista SQL

En la siguiente imagen se muestra la vista SQL de una consulta realizada en OOo Base. La apariencia es la de un texto con expresiones y nombres de campos y tablas de la base de datos. Por eso, el diseño consultas en SQL requiere un conocimiento avanzado de sus elementos y su sintaxis.

Img 4. Vista SQL

Esta es la traducción que hace OOo Base de una consulta y este es el formato elegido para distinguir rápidamente los elementos de SQL.

SQL es un lenguaje de datos

nivel diseñado para realizar operaciones en bases de .

Su sintaxis con sentencias formadas por y de la base de datos. Las consultas diseñadas en OOo Base pueden ser editadas en Comprobar

y nombres de .

En OOo Base, el panel de tareas con consultas permite crear una consulta directamente en vista SQL. Img 5. Tareas de creación de consultas

La pantalla que aparece es un editor de texto en blanco. Para crear una consulta es necesario conocer la sintaxis y las sentencias SQL. Img 6. Editor de consultas en SQL

La sintaxis de una sentencia SQL es: Comando Campos Cláusula Tabla Palabras reservadas Condición

En la imagen número 4, "Vista SQL", puedes distinguir en azul y en mayúsculas las expresiones SQL, en naranja y en minúsculas los nombres de las tablas y los campos de la base de datos y en negro los criterios de selección aplicados en la consulta. Los nombres de las tablas y de los campos se escriben entrecomillados y separados por un punto. Seguro que con estas pistas puedes identificar los elementos de la sentencia SQL que aparece en la imagen número 3.

Comando

Campo "Pedidos"."IDPedidos"

Cláusula Tablas

Palabra Condición Reservada

"Pedidos"."CiudadDestin

Comprobar

En el apartado siguiente se hace un estudio pormenorizado de los principales elementos.

1.1. Comandos LMD

Las expresiones que utiliza SQL se dividen según su función en: comandos, cláusulas, operadores y funciones de agregado.

Los comandos son instrucciones que realizan acciones sobre las tablas de la base de datos.

Se clasifican en comandos de definición, manipulación y control de datos, dando lugar a los siguientes lenguajes: Lenguaje de Definición de Datos. Lenguaje de Manipulación de Datos. Lenguaje de Control de Datos.

Cada conjunto de comandos se define como un lenguaje. Relaciona cada abreviatura con el lenguaje al que se refiere. Lenguaje que permite modificar o definir la estructura de los elementos de una base de datos. Lenguaje que permite realizar consultas y manipular los datos. Lenguaje que permite gestionar la seguridad y confidencialidad de la base de datos Comprobar

En el tema se trata SQL como un lenguaje de consultas, por lo que se desarrollará el lenguaje de manipulación de datos o LMD. Los comandos utilizados en este lenguaje son: SELECT Consulta los registros que satisfagan un determinado criterio. INSERT Añade registros por lotes de datos (filas) en una tabla en una única operación. UPDATE Modifica los campos indicados con el valor especificado en la sentencia. DELETE Elimina los registros (filas) indicados en la sentencia.

Estos comandos dividen las consultas en dos tipos: Consultas de selección → SELECT.- muestran toda o parte de la información contenida en las tablas. Consultas de acción → INSERT, UPDATE Y DELETE.- modifican el contenido inicial de las tablas. Existe un tercer tipo de consultas denominadas de unión que devuelven en una única consulta el resultado de varias consultas de selección.

a) Las consultas que has diseñado en el tema 3 comienzan con el comando:

SELECT UPDATE

b) Cuando el resultado de una consulta origina una tabla con un contenido distinto del original se denomina:

Consulta de selección Consulta de acción

c) Los comandos que pueden devolver un número de registros distinto del inicial son:

SELECT y UPDATE INSERT y DELETE

Los comandos de LDD son: CREATE, ALTER, DROP, TRUNCATE. Puede deducirse que se usan para crear, modificar o eliminar objetos o parte de ellos en una base de datos. Estos objetos pueden ser tablas, vistas (consultas) o índices. Dado que este tema se centra en la manipulación de datos (LMD) no se van a utilizar estos comandos. Para ampliar tus wikipedia_sql_ddl

conocimientos

sobre

su

uso

puedes

visitar

este

enlace:

En el siguiente tutorial encontrarás un resumen de las sentencias SQL clasificadas por lenguajes de definición, manipulación y control de datos.

sentencias_sql

1.2. Cláusulas y palabras reservadas

El objetivo de las consultas es seleccionar un determinado grupo de datos. Estos datos se seleccionan mediante expresiones denominadas cláusulas y palabras reservadas que complementan a los comandos.

Las cláusulas y palabras reservadas son expresiones que especifican sobre que datos actuarán los comandos.

Las cláusulas y palabras reservadas utilizadas en LMD son: FROM WHERE

Especifica la tabla o tablas de las que se van a seleccionar los datos. Especifica el o los criterios de búsqueda de datos.

Mediante Group By indicamos los campos por los que se agrupan los GROUP BY registros y mediante Having se especifican los criterios de agrupación de (...HAVING) registros. ORDER BY

Especifica por que campos y en que sentido (Ascendente o Descendente) se van a ordenar los registros.

Puesto que ya has creado consultas en la vista de diseño, seguro que eres capaz de asociar cada cláusula o palabra reservada con la acción que desempeña en la pantalla de diseño de consultas. 1.- SELECT ... 2.3.4.Img 7. Vista de diseño de consultas

Comprobar

Ahora intenta escribir tú esta consulta en SQL. Para señalar los criterios de selección AND y OR recuerda que el operador OR se escribe entre paréntesis y el AND se aplica sobre éstos.

2. Consultas de selección

La palabra consulta lleva implícito en su significado elemento interrogativo, es decir, una consulta es una pregunta.

Los argumentos de dicha pregunta están formados por un sujeto y un predicado que determina la acción a realizar mediante un verbo. El verbo que se utiliza en las consultas más sencillas es seleccionar. Estas son las consultas de selección.

Las consultas de selección permiten indicar a la base de datos los campos (atributos), las tablas (relaciones) y condiciones de los registros que se quieren visualizar. Su estructura es: SELECT ... FROM ... WHERE ...

La expresión SELECT ... FROM ... WHERE ... equivale a la consulta "selecciona los registros ... de la tabla ... que cumplan...".

Esta es la estructura básica, pero se pueden utilizar elementos simplificadores y operadores para especificar las condiciones.

Vamos a seguir trabajando con la tabla Pedidos de nuestra base de datos CompuService. Recuerda que se usa para introducir todos los datos de un pedido desde su identificación en el campo IDPedido (campo llave primaria numérico) hasta la ciudad de destino en el campo CiudadDestinatario. Señala cuál de las opciones que se presentan puede escribirse en cada apartado de la consulta. SELECT

FROM

WHERE

a) IDPedido

a) *

a) IDPedido = "madrid"

b) Pedidos

b) Pedidos

b) Pedidos.Ciudad = "madrid"

c) *.IDPedido

c) "madrid"

c) Pedidos = "madrid"

Comprobar

a) Delante del nombre del campo debe escribirse siempre el nombre de la tabla a la que pertenece. Verdadero

Falso

b) En el SELECT debe aparecer el campo implicado en la condición WHERE. Verdadero

Falso

c) No es obligatorio utilizar campos de todas las tablas incluidas en la consulta. Verdadero

Falso

Los elementos adicionales que se pueden usar en las consultas de selección sirven para nombrar, ordenar o filtrar los campos de la consulta.

Los más utilizados son AS y ORDER BY.

La palabra reservada AS establece el alias del nombre de campo que se ha seleccionado inmediatamente delante.

Los alias son importantes en las consultas sobre varias tablas que pueden contener campos con nombres iguales y en campos con funciones. En general, se usan para nombrar las columnas de forma sencilla y descriptiva. Su sintaxis es:

SELECT "NombreCampo" AS "Alias" ...

La cláusula ORDER BY establece el campo o campos por los que se ordenarán los registros de la consulta.

Si no se especifica nada se ordenarán de forma ascendente, pero puede especificarse ASC (por defecto) o DESC (descendente) tras el nombre de los campos.

Si se "traduce" la pantalla del asistente para creación de consultas se obtiene una expresión cuya sintaxis es: Img 9. Ordenar registros con el asistente de creación de consultas

... ORDER BY Pedidos.FechaPedido ASC

¿Podría utilizarse la siguiente expresión? ¿Qué significado tiene? SELECT "IDPedido", "CiudadDestinatario" "CiudadDestinatario" ASC, "IDPedido" DESC

FROM

"Pedidos"

ORDER

BY

Existen otras palabras reservadas que permiten añadir opciones a la forma de ordenar los registros. El más común es DISTINCT.

Esta palabra se añade tras el comando SELECT para evitar que se muestren filas iguales. Utilizando DISTINCT no pueden aparecer dos filas idénticas.

La opción DISTINCT es la más utilizada para simplificar la visualización de la consulta. En OOo Base también se puede activar en la vista de diseño de consultas mediante el icono "valores unívocos".

2.1. Criterios de selección

WHERE ... ¿Qué se escribe detrás de la palabra WHERE? El diseño de consultas permite introducir varios criterios de selección de datos. En este apartado se describen los signos y expresiones de los operadores utilizados en SQL, agrupados en lógicos y de comparación.

Un operador es la expresión que permite introducir varios criterios o condiciones de búsqueda de registros. Pueden ser lógicos o de comparación.

Los operadores, tanto lógicos como de comparación, se usan en la creación de consultas con el asistente o en vista diseño en OOo Base. Pero el usuario no necesita conocer su sintaxis puesto que los proporciona el programa. Img 11. Creación de consultas con el asistente

Recuerda que en la vista diseño a los criterios tecleados en la misma fila se les aplica el operador AND y a los tecleados en la misma columna se les aplica el operador OR. El resto se seleccionan en un cuadro de lista.

En la consulta realizada en el apartado 1 del tema se han establecido los criterios que aparecen en la imagen en los campos CiudadDestino y TipoTransporte.

Img 12. Criterios consulta

Escribe la "traducción" a SQL de dichos criterios.

Los operadores lógicos devuelven valores que cumplen las funciones lógicas AND, OR y NOT.

Se aplican escribiendo las siguientes expresiones entre dos condiciones o antes en el caso de la negación.

Función "Y" lógica. AND

Evalúa dos condiciones y devuelve verdadero si ambas son ciertas.

el

valor

Evalúa dos condiciones y devuelve el verdadero si cualquiera de ella es cierta.

valor

Función "O" lógica. OR

Negación lógica. NOT

Devuelve los registros con el valor contrario al de la condición.

¿Qué registros se visualizarán en la consulta anterior?

Los pedidos con destino madrid y tipo de transporte 1 y 2. Los pedidos con destino madrid y tipo de transporte 1 ó 2.

Los operadores de comparación devuelven valores que están en el rango establecido en la condición.

Los operadores son los signos >, < ó = y sus combinaciones y las expresiones BETWEEN, LIKE, IN. = =

menor que / menor que igual / distinto de menor o igual que / mayor o igual que

BETWEEN establece un intervalo de valores LIKE IN

compara con un modelo compara con una lista de valores

Los operadores BETWEEN e IN admiten la negación poniendo delante NOT.

El operador LIKE compara el valor de un campo con un modelo que puede ser un texto o una serie de caracteres especiales reconocidos por el sistema. * son válidos un conjunto de valores cualesquiera. ? es válido cualquier carácter. # es válido cualquier valor numérico. Los sistemas operativos también admiten estos caracteres en la opción de búsqueda de archivos.

¿Qué operadores o caracteres especiales utilizaremos para realizar las siguientes consultas? 1. Pedidos realizados en el mes de enero de 2010 (del 1/01/10 al 31/01/10): 01/01/10 31/01/10 2. Pedidos realizados en el último cuatrimestre del año (septiembre, octubre, noviembre y diciembre): bre 3. Facturas o pedidos cobrados (cobrada o cobrado): cobrad 4. Pedidos realizados en la década de los 90 (1991, 1992, ..., 1999): 199 5. Pedidos enviados a provincias de Castilla-La Mancha (Ciudad Real, Albacete, Cuenca, Guadalajara, Toledo): (Ciudad Real, Albacete, Cuenca, Guadalajara, Toledo) 6. Pedidos enviados al resto de provincias de España (Madrid, Cáceres, Ávila, ...): (Ciudad Real, Albacete, Cuenca, Guadalajara, Toledo) Comprobar

2.2. Agrupamiento de registros

El diseño de consultas sigue siempre los mismos pasos. Las cláusulas que admite el comando SELECT permiten ordenar los registros (ORDER BY), especificar las condiciones de búsqueda (WHERE), establecer los alias (AS) y... agrupar los registros y establecer las condiciones de agrupación (GROUP BY).

Img 13. Pasos del diseño de consultas. Agrupación de registros.

La cláusula GROUP BY (... HAVING) permite agrupar en un solo registro todos aquellos cuyo valor del campo indicado sea idéntico. Opcionalmente permite seleccionar solamente los registros que cumplan una condición con la cláusula HAVING.

Se usa en combinación con funciones de agregado para añadir campos de resumen de los registros agregados.

La cláusula GROUP BY debe contener todos los campos nombrados en el comando SELECT, excepto el campo en el que se aplicará la función. Por este motivo, las consultas realizadas con GROUP BY suelen ser sencillas e incluir simplemente el campo que se desea agrupar y el que se desea resumir con una función. Su sintaxis es: SELECT Campo1, FUNCIÓN (Campo2) FROM Tabla WHERE Criterio GROUP BY Campo1

a) La cláusula GROUP BY muestra los registros agrupados según uno o varios campos como en los formularios. Verdadero

Falso

b) En la cláusula GROUP BY hay que indicar solamente el campo por el que se agruparán los registros. Verdadero

Falso

c) GROUP BY ordena los registros como ORDER BY pero no repite el campo indicado en la cláusula. Verdadero

Falso

Cuando en una consulta con GROUP BY sólo interesa el resumen en registros con valores determinados se usa la cláusula HAVING. Se suele definir está cláusula como el WHERE del GROUP BY, es decir, HAVING es a GROUP BY lo que WHERE es a SELECT. Por ejemplo, se usa HAVING cuando además de consultar el número de pedidos enviados a cada provincia lo que se necesita es saber el número de pedidos enviados solamente a Madrid. La sentencia se realiza introduciendo la claúsula HAVING después del campo de agrupación: ... GROUP BY Campo1 HAVING condición

Ya dispones de una base de datos y de consultas diseñadas fácilmente mediante el asistente. Así que tienes una buena colección de sentencias SQL. Sólo tienes que editar las consultas en SQL y OOo Base te proporcionará la sentencia correctamente escrita. En el tema 3 diseñaste la consulta Contar_pedidos como ejemplo del agrupamiento de registros. Edítala en vista SQL y comprueba que contiene la cláusula GROPU BY "Destinatario", puesto que la consulta cuenta los pedidos que ha realizado cada cliente, es decir, agrupa los registros por clientes (o destinatarios del pedido).

Img 14 y 15. Datos y vista SQL de consulta Contar_pedidos

Ejercicio.- Introduce la cláusula HAVING para que se visualicen únicamente los pedidos del cliente "empresa1"

La cláusula HAVING también puede incluir criterios con operadores y funciones.

En el ejercicio anterior se podría haber seleccionado el registro cuyo valor del campo Total_pedido fuese mayor que 4. En este caso el resultado sería el registro:

Img 18 y 19. Datos y vista SQL consulta Contar_pedidos modificada.

En esta web puedes encontrar más ejemplos: sql_having_sum

2.2.1. Funciones de agregado

El objetivo del agrupamiento de registros con un campo en común es obtener información que resuma alguna característica de dichos registros: su número, la suma o el promedio de algún campo numérico. Estas operaciones aritméticas se introducen en el campo del que se obtienen los datos y se denominan funciones de agregado.

Una función de agregado permite realizar cálculos aritméticos con los valores de un campo incluido en una consulta con agrupamiento de registros.

Las funciones más utilizadas son:

AVG

Calcula el promedio (la media aritmética) de los valores contenidos en el campo indicado en el argumento de la función.

COUNT

Cuenta los registros agrupados para cada valor distinto del campo de agrupación, independientemente de su contenido (texto o numérico.

MAX

Selecciona el valor máximo del campo indicado en el argumento.

MIN

Selecciona el valor mínimo del campo indicado en el argumento.

SUM

Suma los valores de los campos indicados en el argumento.

Su sintaxis es: FUNCIÓN (campo). Sin embargo pueden situarse en dos posiciones dependiendo de su objetivo en la sentencia: Deben situarse como parte del comando SELECT cuando se usan en una consulta de agrupamiento de registros. Deben situarse tras la palabra HAVING cuando se usan para filtrar el agrupamiento de registros.

¿En qué posición de la sentencia SQL situarías la función de agregado en cada caso? a) Calcular el promedio de pedidos por cada cliente. b) Mostrar los clientes cuyo promedio de pedidos es mayor de 7. SELECT "Destinatario" Comprobar

FROM "Pedidos" GROUP BY "Destinatario"

El campo obtenido tras la aplicación de una función de agregado tiene un nombre diferente, puesto que realmente es un campo resumen en el que se presenta un resultado. El nombre adjudicado a dicho campo es el nombre de la función y del campo objeto de dicha función. En el ejercicio anterior: AVG ("IDPedido"). Para evitar confusiones y clarificar la vista de las consultas de agrupamiento se debe asignar un alias con la cláusula AS a dicho campo.

a) Las funciones de agregado se aplican sobre campos con contenido numérico. Verdadero

Falso

b) La función MAX aplicada a un campo de texto devuelve la última letra en orden alfabético. Verdadero

Falso

c) El alias se usa para ocultar la función de agregado aplicada. Verdadero

Falso

d) Las funciones de agregado pueden usarse como criterios de filtrado en la claúsula HAVING. Verdadero

Falso

La función COUNT se puede usar sin GROUP BY. Esta función cuenta los registros seleccionados en un agrupamiento o en un SELECT. Se utiliza SELECT COUNT Campo FROM tabla, para contar todos los registros de una tabla o sólo los que cumplan una condición si se añade WHERE.

Esta función se usa tanto para contar registros en una consulta o tabla, o registros con un valor distinto con la claúsula DISTINCT. La sentencia: SELECT COUNT (DISTINCT Campo1) FROM Tabla

Devuelve el número de registros cuyo valor en el campo Campo1 es distinto. Por ejemplo, para saber cuántas empresas han realizado pedidos se puede usar SELECT COUNT (DISTINCT "Destinatario") FROM "Pedidos"

3. Consultas de acción

Hasta ahora las consultas han sido utilizadas para visualizar parte de los datos contenidos en una o varias tablas y para realizar cálculos con ellos. En ningún caso el contenido de las tablas ha sido modificado. Las consultas de acción se utilizan para actualizar el contenido de las tablas en una sola operación previamente diseñada. Se usan cuando esta operación se repite periódicamente o cuando se pueden modificar valores de campos por lotes evitando así repetir la operación muchas veces.

Img 20. Consultas de acción.

Las consultas de acción son aquellas mediante las que se borran, añaden o modifican registros de una tabla, es decir, el contenido de la tabla tras la ejecución de la consulta es distinto al inicial. Los comandos utilizados son: DELETE, INSERT y UPDATE.

Nuestro gestor de bases de datos OOo Base no permite crear consultas de acción por lo que los ejercicios de este apartado serán simulados, pero no podrás probarlos.

Consultas de eliminación

Mediante el comando DELETE FROM WHERE se eliminan los registros de la tabla indicada cuyo valor coincide con el criterio expresado en la cláusula WHERE. Sintaxis: DELETE * FROM Tabla WHERE Criterio

a) El comando DELETE elimina registros completos. Verdadero

Falso

b) Si no se especifica ninguna condición en la cláusula WHERE no se borra nada. Verdadero

Falso

c) Si la operación no es la esperada se puede volver al estado anterior con el botón deshacer. Verdadero

Falso

El comando DELETE permite borrar registros de varias tablas indicándolo en la cláusula FROM.

En este caso la sintaxis requiere que delante de cada nombre de campo se especifique su tabla de procedencia:

DELETE tabla1.* , tabla2.* FROM tabla1, tabla2 WHERE condición

La condición puede ser simple (sólo afecta a un campo) mediante un operador (,=, BETWEEN, LIKE, IN) o compuesta (están implicados varios campos de una o de las dos tablas) median operadores como AND y OR.

Consultas de inserción

Mediante el comando INSERT INTO se añaden los registros que cumplan la condición expresada en la claúsula WHERE a la tabla indicada. Sintaxis: INSERT INTO tabla (campo1, campo2, ...) VALUES (valor1, valor2, ...)

Este comando tiene ciertas peculiaridades: Requiere conocimientos sobre los tipos de datos contenidos en los campos y el resto de restricciones impuestas en su definición. Los valores de campos de tipo texto deben escribirse entre comillas y las fechas en el formato adecuado. A los valores de los campos no especificados en la cláusula VALUES se les asignará un valor NULL si las especificaciones del campo lo permiten o el valor preestablecido por defecto. Cada instrucción INSERT añade un solo registro a una sola tabla. Para añadir más registros se pueden separar bloques de los valores de un registro (entre paréntesis) por comas u obtener previamente los datos de otra tabla mediante un SELECT.

Inserta un nuevo registro en la tabla Tipo_transporte para el transporte por tren de alta velocidad, denominado AVE con el número 6. No asignes ningún valor al tiempo de entrega. Para que la sentencia sea correcta ten en cuenta los tipos de datos de cada campo.

Consultas de modificación

Mediante el comando UPDATE modifica el valor de los campos indicados en la cláusula SET para los registros que cumplan el criterio expresado en la cláusula WHERE. Si no se especifica ninguna condición se actualizarán todos los registros de la tabla. Sintaxis: UPDATE tabla SET campo1 = valor1 WHERE condición

Esta sentencia es especialmente útil cuando se desea actualizar muchos registros a un valor determinado.

Crea una consulta en vista SQL que cambie la dirección del cliente denominado "empresa 3". Elige tú la nueva dirección.

Las consultas con UPDATE pueden modificar un solo registro o varios a la vez. Responde 1 si la consulta solo modifica un registro y 2 si modifica varios. UPDATE "Pedidos" SET "Provincia" = "Madrid" WHERE "Provincia" = "madrid" UPDATE "Transporte" SET "TipoTransporte" = "urgente" WHERE "IDTransporte" = "2" Comprobar