Archivo de la categoría: SQL

SQL (Structured Query Language, lenguaje de consultas estructurado) es un lenguaje de dominio específico utilizado en programación, diseñado para administrar y recuperar información de sistemas de gestión de bases de datos relacionales

Lenguaje de programación, SQL, Programación

SQL

07 de octubre de 2024 secarcam

SQL

Al gestionar bases de datos el modelo de datos más utilizado es el modelo relacional y para comunicarse con el gestor se puede utilizar el lenguaje SQL

NO es un lenguaje de programación, sino un lenguaje de gestión de bases de datos que presenta las siguientes características:

LDD (Lenguaje de definición de datos)
El LDD de SQL proporciona comandos para la definición de esquemas de relación, borrado de relaciones y modificación de los esquemas de relación
LMD (Lenguaje interactivo de manipulación de datos)
El LMD de SQL incluye un lenguaje de consultas basado tanto en el álgebra relacional como en el cálculo relacional de tuplas. También incluye comandos para insertar, borrar y modificar tuplas
Integridad
El LDD incluye comandos para especificar restricciones de integridad que deben cumplir los datos almacenados en la base de datos. Las actualizaciones que violan las restricciones de integridad se rechazan
Definición de vistas
El LDD de SQL incluye comandos para la definición de vistas
Control de transacciones
SQL incluye comandos para especificar el comienzo y el final de transacciones
SQL incorporado y SQL dinámico
SQL incorporado y SQL dinámico define cómo se pueden incorporar las instrucciones de SQL en lenguajes de programación de propósito general como C, C++, Java, PL/L, Cobol, Pascal o Fortran, entre otros
Autorización
El LDD de SQL incluye comandos para especificar los derechos de acceso a las relaciones y a las vistas

Evolución del SQL

IBM desarrolló la versión original de SQL, denominado Sequel, como parte del proyecto System R a principios de 1970 por el grupo de San José, California

El lenguaje Sequel ha evolucionado desde entonces y su nombre ha pasado a ser SQL (lenguaje estructurado de consultas, Structured Query Language)

Hoy en día, muchos lenguajes de programación son compatibles con el lenguaje SQL y se ha establecido como el lenguaje estándar para el uso de bases de datos relacionales

1981, IBM lanzó el primer programa comercial basado en SQL

1986, ANSI (Instituto nacional americano de normalización, American National Standards Institute) publicó una norma SQL, denominada SQL-86 o SQL1 y fue confirmada por ISO (Organización internacional de normalización, International Standards Organization) en 1987

1989, ANSI publicó una extensión de la norma para SQL denominada SQL-89

1992, apareció una nueva versión de la norma denominada SQL-92 o SQL2

1999, apareció una nueva versión denominada SQL:1999 o SQL2000. En la cual se agregaron expresiones regulares, consultas recursivas (para relaciones jerárquicas), triggers y algunas características orientadas a objetos

2003, apareció una nueva versión denominada SQL:2003. En la cual se agregaron algunas características de XML, cambios en las funciones, estandarización del objeto sequence y de las columnas autonuméricas

2005, apareció una nueva versión denominada SQL:2005 y se corresponde con la norma ISO/IEC 9075-14:2005. Definiendo cómo SQL puede utilizarse conjuntamente con XML:

Importar y guardar datos XML en una base de datos SQL, manipulándolos dentro de la base de datos y publicando el XML y los datos SQL convencionales en forma XML
Facilidades que permiten a las aplicaciones integrar dentro de su código SQL el uso de XQuery, lenguaje de consulta XML publicado por el W3C (World Wide Web Consortium) para acceso concurrente a datos ordinarios SQL y documentos XML

2008, apareció una nueva versión denominada SQL:2008. En la cual se agregó el uso de la cláusula ORDER BY fuera de las definiciones de los cursores. Incluyendo disparadores del tipo INSTEAD OF. Y la inclusión de la sentencia TRUNCATE

2011, apareció una nueva versión denominada SQL:2011. En la cual se agregaron los datos temporales (PERIOD FOR) y mejoras en las funciones de ventana y de la cláusula FETCH

2016, apareció una nueva versión denominada SQL:2016. En la cual se agregó la búsqueda mediante patrones, funciones de tabla polimórficas y compatibilidad con los ficheros JSON

Lenguaje de programación, SQL, Programación

LDD

07 de octubre de 2024 secarcam

Lenguaje de definición de datos (LDD)

El lenguaje de definición de datos (LDD), es el que se encarga de la modificación de la estructura de los objetos de la base de datos

Incluye órdenes para modificar, borrar o definir las relaciones (tablas) en las que se almacenan los datos de la base de datos

Existen cuatro operaciones básicas:

Definición de los datos

El conjunto de relaciones de cada base de datos debe especificarse en el sistema en términos de un lenguaje de definición de datos (LDD)

El LDD de SQL no sólo permite la especificación de un conjunto de relaciones, sino también de la información relativa a esas relaciones, incluyendo:

El esquema de cada relación
El dominio de valores asociado a cada atributo
Las restricciones de integridad
El conjunto de índices que se deben mantener para cada relación
La información de seguridad y de autorización de cada relación
La estructura de almacenamiento físico de cada relación en el disco

Tipos básicos de dominios

La norma SQL soporta gran variedad de tipos de dominio predefinidos:

CHAR(n)
Una cadena de caracteres de longitud fija, con una longitud n especificada por el usuario. También se puede utilizar la palabra completa character
VARCHAR(n)
Una cadena de caracteres de longitud variable con una longitud máxima n especificada por el usuario. La forma completa, character varying, es equivalente
INT
Un entero (un subconjunto finito de los enteros dependiente del ordenador). La palabra completa, integer, es equivalente
SMALLINT
Un entero pequeño (un subconjunto dependiente del ordenador del tipo de dominio entero)
NUMERIC(p, d)
Un número en coma fija, cuya precisión la especifica el usuario. El número está formado por p dígitos (más el signo) y de esos p dígitos, d pertenece a la parte decimal
DOUBLE
Un número en coma flotante y números en coma flotante de doble precisión, con precisión dependiente del ordenador
FLOAT(n)
Un número en coma flotante cuya precisión es, al menos, de n dígitos
DATE
Dato de tipo fecha, con una precisión de hasta segundo

Definición básica de esquemas

CREATE TABLE

Las relaciones se definen mediante el comando CREATE TABLE:

$\text{CREATE TABLE r} (\\ A_1 D_1, \cdots, A_n D_n,\\ \{rest-integridad_1\}, \cdots, \{rest-integridad_k\} )$

Donde r es el nombre de relación, cada A_i es el nombre de un atributo del esquema de la relación r y D_i es el tipo de dominio de los valores del dominio del atributo A_i

Hay varias restricciónes de integridad válidas

Una de las más usadas es la de clave primaria (primary key), que adopta la siguiente forma:

$\text{primary key}(A_{j1}, \cdots, A_{jm})$

La especificación de clave primaria determina los atributos $A_{j1}, \cdots, A_{jm}$ forman la clave primaria de la relación

Los atributos de la clave primaria tienen que ser no nulos y únicos; es decir, ninguna tupla puede tener un valor nulo para un atributo de la clave primaria y ningún par de tuplas de la relación puede ser igual en todos los atributos de la clave primaria

Aunque la especificación de clave primaria es opcional, suele ser una buena idea especificar una clave primaria para cada relación

CREATE TABLE cuenta(
numero_cuenta char(10),
nombre_sucursal char(15),
saldo numeric(12, 2),
primary key(numero_cuenta) )

En el ejemplo se ha creado una relación llamada cuenta en la cual se han definido los atributos:

numero_cuenta, que puede guardar una palabra de hasta 10 carácteres
nombre_sucursal, que puede guardar una palabra de hasta 15 carácteres
saldo, que puede guardar un número decimal con 12 enteros y 2 decimales
Y con la restricción sobre numero_cuenta como clave primaria de la relación

Si una tupla recién insertada o recién modificada de una relación contiene valores nulos para cualquiera de los atributos que forman parte de la clave primaria, o si tienen el mismo valor que otra tupla de la relación, SQL notificará el error e impedirá la actualización

CREATE TABLE cliente(
numero_cuenta char(10),
nombre_cliente char(15),
primary key(numero_cuenta) )

En el ejemplo se ha creado otra relación llamada cliente en la cual se han definido los atributos:

numero_cuenta, que puede guardar una palabra de hasta 10 carácteres
nombre_cliente, que puede guardar una palabra de hasta 15 carácteres
Y con la restricción sobre numero_cuenta como clave primaria de la relación

Como puede verse podemos crear más de una relación

DROP TABLE

Para eliminar una relación de la base de datos se utiliza el comando DROP TABLE

Este comando elimina de la base de datos toda la información sobre la relación

DROP TABLE cuenta

En el ejemplo se ha elimina de la base de datos toda la información sobre la relación cuenta

Este comando es más drástico que DELETE, que sólo borra sus tuplas manteniendo el esquema de la relación

Sin embargo, DROP, si se elimina cuenta, no se podrá volver a insertar ninguna tupla en dicha relación, a menos que se vuelva a crear con el comando CREATE TABLE

ALTER TABLE

El comando ALTER TABLE se utiliza para añadir atributos a una relación existente

Como valor del nuevo atributo se asigná a todas las tuplas de la relación el valor nulo

ALTER TABLE cuenta ADD
ano DATETIME

En el ejemplo se ha añadido un nuevo atributo

Donde cuenta es el nombre de la relación existente, ano es el nombre del atributo que se desea añadir y DATETIME es el dominio del atributo añadido

También se pueden eliminar atributos usando ALTER TABLE

ALTER TABLE cuenta DROP
ano

En el ejemplo se ha eliminado el atributo que añadimos antes

Donde cuenta es el nombre de la relación existente y ano es el nombre del atributo que se desea eliminar

En este caso no es necesario declarar el dominio del atributo a borrar

Muchos sistemas de bases de datos no permiten el borrado de atributos, aunque sí que permiten el borrado de tablas completas

TRUNCATE

Este comando solo se aplica a tablas y su función es borrar el contenido completo de la relación especificada

La ventaja sobre el comando DELETE, es que si se quiere borrar todo el contenido de la relación, es mucho más rápido, especialmente si la relación es muy grande

La desventaja es que TRUNCATE sólo sirve cuando se quiere eliminar absolutamente todas las tuplas, ya que no se permite la cláusula WHERE

Si bien, en un principio, esta sentencia parecería ser LMD (Lenguaje de Manipulación de Datos), es en realidad una LDD, ya que internamente, el comando TRUNCATE borra la tabla y la vuelve a crear y no ejecuta ninguna transacción

TRUNCATE TABLE cuenta

En el ejemplo hemos borrado todo el contenido de la relación llamada cuenta

Lenguaje de programación, SQL, Programación

LMD

07 de octubre de 2024 secarcam

Lenguaje interactivo de manipulación de datos (LMD)

El lenguaje de manipulación de datos (LMD) es el que se encarga de llevar a cabo las tareas de consulta o manipulación de los datos, organizados por el modelo de datos adecuado

Incluye órdenes para consultar, modificar, borrar o definir las tuplas (filas) que son almacenadas en las relaciones (tablas) de la base de datos

Existen cuatro operaciones básicas:

Definición básica de esquemas

Operación alias

SQL proporciona una operación para renombrar mediante un alias tanto las relaciones como los atributos

Tiene la siguiente sintaxis: nombre_original AS alias

La cláusula AS es opcional aunque si la escribimos resultará más claro porque nos informa que estamos definiendo un alias

Situaciones en las que es útil usar un alias:

Donde tenemos dos relaciones en la cláusula FROM que tienen el mismo nombre para sus atributos

En estas situaciones se puede producir ambigüedad y podremos usar la operación alias para desambigüar los atributos y que quede claro a qué relación pertenecen
Cuando tenemos una operación calculada que genera un atributo en la cláusula SELECT
Cuando tenemos una operación calculada que genera un atributo despues de aplicar una operación de agregación

Para acceder a sus atributos se debe usar el alias seguido de un punto y el nombre del atributo

SELECT

Se puede utilizar el comando SELECT para consultar los datos almacenados en una relación de la base de datos

Devolverá un conjunto de tuplas de esa relación

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1, r_2, \cdots, r_m\\ \text{WHERE} P$

Donde cada A_i representa a un atributo, cada r_i una relación y P es un predicado

Por lo que una consulta SQL tendrá la siguiente expresión de álgebra relacional equivalente:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p(r_1 \Theta r_2 \Theta \cdots \Theta r_m) )$

Si se omite la clausula WHERE, el predicado P es siempre cierto

Pero a diferencia de la expresión del álgebra relacional, el resultado de una consulta SQL puede contener varias tuplas repetidas

Cuando introducimos más de una relación en la claúsula FROM, SQL formará un producto cartesiano de todas las relaciones incluidas

Después se lleva a cabo la selección del álgebra relacional utilizando el predicado de la cláusula WHERE y despues proyecta el resultado sobre los atributos de la cláusula SELECT

Cláusula SELECT

SELECT DISTINCT numero_cuenta, nombre_sucursal, saldo
FROM cuenta

En el ejemplo se ha resuelto la consulta obtener todas las cuentas de la relación cuenta con los atributos numero_cuenta, nombre_sucursal, saldo

En este caso, al usar DISTINCT estamos forzando a que se eliminen los repetidos

SELECT ALL numero_cuenta, nombre_sucursal, saldo
FROM cuenta

En el ejemplo se ha resuelto la consulta obtener todas las cuentas de la relación cuenta con los atributos numero_cuenta, nombre_sucursal, saldo

En este caso, al usar ALL estamos forzando a que NO se eliminen los repetidos

SELECT *
FROM cuenta

En el ejemplo se ha sustituido el comando ALL por el símbolo * y se ha prescindido de los atributos, ya que el resultado es equivalente

SELECT numero_cuenta, nombre_sucursal, saldo * 1500
FROM cuenta

En el ejemplo se ha resuelto la consulta obtener todas las cuentas de la relación cuenta con los atributos numero_cuenta, nombre_sucursal, con un incremento de 1500 en el saldo

Como puede apreciarse, también podemos utilizar expresiones aritméticas que operen sobre los atributos

SELECT numero_cuenta, nombre_sucursal, saldo * 1500 incremento
FROM cuenta

En el ejemplo se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama incremento

Cláusula FROM

La cláusula FROM define por sí misma un producto cartesiano de las relaciones que aparecen en la consulta

Dado que la reunión natural se define en términos de un producto cartesiano, una selección y una proyección, resulta relativamente sencillo escribir una expresión SQL para la reunión natural

Tenemos que en álgebra relacional podemos escribirla como:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (r_1 \Theta r_2 \Theta \cdots \Theta r_m)$

SELECT c.numero_cuenta, c.saldo
FROM cuenta c, cliente cl
WHERE c.numero_cuenta = cl.numero_cuenta AND c.nombre_sucursal = 'Navacerrada'

El ejemplo resuelve la consulta de obtener todos los clientes que tienen cuenta en la sucursal de ‘Navacerrada‘

Para ello se han introducido las relaciones cuenta (se le ha asignado el alias c) y cliente (se le ha asignado el alias cl)

Sólo con la cláusula FROM no podríamos resolver la consulta

Ya que el producto cartesiano se realiza sobre cada una de las posibles parejas que pueden formarse con la relación cuenta y cliente

Dándonos resultados repetidos y también todos aquellos que no fueran de la sucursal de ‘Navacerrada‘

Por eso se ha añadido la condición que relaciona ambas relaciones, el atributo numero_cuenta

Después de añadirlo, tendremos sólo los valores que pertenecen a la relación, aunque puede seguir habiendo repetidos

Y la igualdad para la atributo nombre_sucursal con el valor ‘Navacerrada‘

Cláusula JOIN

SELECT c.numero_cuenta, c.saldo
FROM cuenta c
JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
WHERE c.nombre_sucursal = 'Navacerrada'

En el ejemplo se ha usado la cláusula JOIN que nos devolverá un resultado equivalente

La cláusula JOIN permite realizar operaciones de reunión, las cuales toman dos relaciones y devuelven como resultado otra relación

Normalmente puede utilizarse la cláusula FROM para realizar reuniones externas o para cualquier situación en la que se pueda utilizar una reunión

Para distinguir si la reunión es externa se usará la palabra clave OUTER y para la interna INNER

Ambas son opcionales, aunque deja más claro qué tipo de reunión estamos usando

Hay cuatro tipos de reunión:

INNER JOIN
Devuelve todas las tuplas cuando hay al menos una coincidencia en ambas relaciones
```
SELECT c.numero_cuenta, c.saldo
FROM cuenta c
INNER JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
WHERE c.nombre_sucursal = 'Navacerrada'
```
En el ejemplo se ha usado la cláusula INNER JOIN que nos devolverá un resultado equivalente

Ya que como dijimos, la palabra clave INNER era opcional

Sin embargo, ahora tenemos más claro que la reunión era interna
LEFT OUTER JOIN
Devuelve todas las tuplas de la relación de la izquierda, y las tuplas coincidentes de la relación de la derecha
```
SELECT c.numero_cuenta, c.saldo
FROM cuenta c
LEFT JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
WHERE c.nombre_sucursal = 'Navacerrada'
```
En el ejemplo se ha usado la cláusula LEFT JOIN que nos devolverá un resultado que ya no es equivalente, ya que aparecerán valores nulos

Hemos prescindido de la palabra clave OUTER porque el resultado es equivalente

En este caso LEFT JOIN nos mantiene todas las tuplas de la relación izquierda (cuenta)

Las tuplas de la relación derecha (cliente) se mostrarán si hay una coincidencia con las de la izquierda

Si existen valores en la relación izquierda pero no en la relación derecha, ésta mostrará null
RIGHT OUTER JOIN
Devuelve todas las tuplas de la relación de la derecha, y las tuplas coincidentes de la relación de la izquierda
```
SELECT c.numero_cuenta, c.saldo
FROM cuenta c
RIGHT JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
WHERE c.nombre_sucursal = 'Navacerrada'
```
En el ejemplo se ha usado la cláusula RIGHT JOIN que nos devolverá un resultado que ya no es equivalente, ya que aparecerán valores nulos

Hemos prescindido de la palabra clave OUTER porque el resultado es equivalente

En este caso RIGHT JOIN nos mantiene todas las tuplas de la relación derecha (cliente)

Las tuplas de la relación izquierda (cuenta) se mostrarán si hay una coincidencia con las de la derecha

Si existen valores en la relación derecha pero no en la relación izquierda, ésta mostrará null
FULL OUTER JOIN
Devuelve todas las tuplas de las dos relaciones, la izquierda y la derecha
```
SELECT c.numero_cuenta, c.saldo
FROM cuenta c
FULL JOIN cliente cl ON c.numero_cuenta = cl.numero_cuenta
WHERE c.nombre_sucursal = 'Navacerrada'
```
En el ejemplo se ha usado la cláusula FULL JOIN que nos devolverá un resultado que ya no es equivalente, ya que aparecerán valores nulos

Hemos prescindido de la palabra clave OUTER porque el resultado es equivalente

En este caso FULL JOIN devuelve todas las filas de la tabla izquierda (cuenta) y de la tabla derecha (cliente)

Combina el resultado de los LEFT JOIN y RIGHT JOIN

Aparecerá null en cada una de las tablas alternativamente cuando no haya una coincidencia

El tipo de reunión define la manera de tratar las tuplas de cada relación que no casan con la tupla de la otra relación

Hay tres tipos de condiciones que se aplicarán a las reuniones:

reunión NATURAL
Se utiliza cuando los atributos por los cuales se reúnen las relaciones tienen el mismo nombre
```
SELECT c.numero_cuenta, c.saldo
FROM cuenta c
NATURAL JOIN cliente cl
WHERE c.nombre_sucursal = 'Navacerrada'
```
En el ejemplo se ha usado la cláusula NATURAL JOIN que nos devolverá un resultado equivalente sin utilizar la condición ON

Ya que el atributo numero_cuenta se llama igual en las relaciones cuenta y cliente
condición ON
Su uso es similar al de la cláusula WHERE, pero está especializado en comparar atributos de ambas relaciones
condición USING
Su uso es similar al de la reunión NATURAL, ya que se utiliza cuando los atributos por los cuales se reúnen las relaciones tienen el mismo nombre

Sin embargo, sólo devolverá un resultado de la reunión, no todos como hace la reunión NATURAL
```
SELECT c.numero_cuenta, c.saldo
FROM cuenta c
JOIN cliente cl USING(numero_cuenta)
WHERE c.nombre_sucursal = 'Navacerrada'
```
En el ejemplo se ha usado la condición USING con el el atributo numero_cuenta, que nos devolverá un resultado que ya no es equivalente

Ya que sólo devolverá sólo la primera coincidencia de cada pareja de la relación, no todas

El uso de las condiciones de reunión es obligatorio en las reuniones externas, pero es opcional en las internas (si se omiten, se obtiene un producto cartesiano)

Cláusula WHERE

SELECT *
FROM cuenta
WHERE saldo < 100

En el ejemplo se ha resuelto la consulta obtener sólo las cuentas de la relación cuenta que cumplen la condición introducida, en este caso las que en su atributo saldo tienen un valor inferior a 100

Para conseguirlo hemos utilizado la cláusula WHERE, gracias a la cuál podemos filtrar la consulta al admitir una condición usando el operador lógico de comparación <


Operador	Descripción
<	Menor que
<=	Menor o igual que
>	Mayor que
>=	Mayor o igual que
=	Igual que
<>	Distinto que

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada' AND saldo < 100

En el ejemplo se ha resuelto la consulta obtener sólo las cuentas de la relación cuenta que cumplen la condición introducida, en este caso las que en su atributo nombre_sucursal tienen un valor igual a ‘Navacerrada‘ y su atributo saldo tienen un valor inferior a 100

Para conseguirlo se ha utilizado el conector lógico AND

Operadores lógicos de conexión
Operador	Descripción
AND	Es verdadera si y sólo si los dos operandos son verdaderos
OR	Es verdadera si y sólo si los uno o ambos operandos son verdaderos
NOT	Invierte el valor del operando

Operador BETWEEN

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada' AND saldo >= 100 AND saldo <= 1000

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada' AND saldo BETWEEN 100 AND 1000

En el ejemplo se ha utilizado una consulta equivalente, utilizando el operador de comparación BETWEEN, el cuál nos simplifica un poco las cláusulas WHERE

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada' AND saldo NOT BETWEEN 100 AND 1000

En el ejemplo se ha utilizado una consulta negando el operador de comparación NOT BETWEEN, el cuál nos devolverá aquellos valores que estén fuera de ese rango

Operador LIKE

SELECT *
FROM cuenta
WHERE nombre_sucursal = 'Navacerrada'

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE 'Navacerrada'

En el ejemplo se ha utilizado una consulta equivalente, utilizando el operador de comparación LIKE, el cuál nos simplifica un poco las cláusulas WHERE al permitirnos el uso de patrones con las cadenas de caracteres

Para la descripción de patrones podemos utilizar dos caracteres especiales:

%
El tanto por ciento coincide con cualquier subcadena de caracteres
_
El subrayado coincide con cualquier carácter

Estos patrones pueden ser combinados para obtener distintos tipos de consultas:

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE 'Nava%'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón % después del texto, el cuál nos devolverá aquellos valores que empiecen por la cadena ‘Nava‘

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE '%Nava%'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón % delante y después del texto, el cuál nos devolverá aquellos valores que incluyan como subcadena ‘Nava‘

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE '___'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón _ repetido tres veces, el cuál nos devolverá aquellos valores que tengan exactamente tres caracteres

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE '___%'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón _ repetido tres veces y el patrón %, el cuál nos devolverá aquellos valores que tengan al menos tres caracteres

SELECT *
FROM cuenta
WHERE nombre_sucursal LIKE 'Nava\%rrete' escape '\'

En el ejemplo se ha utilizado el operador de comparación LIKE utilizando el patrón % en conjunto con el carácter especial de escape ‘\‘, el cuál nos devolverá aquellos valores que contengan ‘Nava%rrete‘

El carácter de escape (que puede ser cualquiera que nosotros elijamos) se utiliza antes de los caracteres especiales de patrón para indicar que ese carácter se va a tratar como un carácter normal y no como un patrón

Se define utilizando la palabra clave escape

SELECT *
FROM cuenta
WHERE nombre_sucursal NOT LIKE 'Nava%

En el ejemplo se ha utilizado el operador de comparación NOT LIKE utilizando el patrón %, el cuál nos devolverá aquellos valores que NO contengan ‘Nava%rrete‘

De esta forma también podemos buscar discordancias, solamente negando el operador LIKE

Valores nulos

SQL permite el uso de valores nulos para indicar la ausencia de información sobre el valor de un atributo

Se puede utilizar la palabra clave especial NULL en un predicado para comprobar si un valor es nulo

SELECT *
FROM cuenta
WHERE nombre_sucursal IS NULL

En el ejemplo se ha resuelto la consulta obtener todas las cuentas cuyo nombre de sucursal NO haya sido rellenado

SELECT *
FROM cuenta
WHERE nombre_sucursal IS NOT NULL

En el ejemplo se ha resuelto la consulta obtener todas las cuentas cuyo nombre de sucursal haya sido rellenado

Como puede verse, también puede comprobarse la ausencia de valores nulos usando el operador lógico NOT

Dado que el predicado de las cláusulas WHERE puede incluir operaciones booleanas como AND, OR y NOT sobre los resultados de la comparaciones, los valores NULL amplían las operaciones booleanas

AND	VERDAD	FALSO	NULL
VERDAD	VERDAD	FALSO	NULL
FALSO	FALSO	FALSO	FALSO
NULL	NULL	FALSO	NULL

OR	VERDAD	FALSO	NULL
VERDAD	VERDAD	VERDAD	VERDAD
FALSO	VERDAD	FALSO	NULL
NULL	VERDAD	NULL	NULL

NOT	VERDAD	FALSO	NULL
	FALSO	VERDAD	NULL

Cláusula ORDER BY

En ocasiones necesitamos devolver la información de las tuplas en cierto order

SQL permite ordenar los atributos utilizando la cláusula ORDER BY

SELECT *
FROM cuenta
ORDER BY nombre_sucursal ASC

En el ejemplo se ha utilizado el operador de comparación ASC con la cláusula ORDER BY para el atributo nombre_sucursal, el cuál nos devolverá los valores ordenados de forma ascendente (en order alfabético porque era un atributo alfanumérico) por el atributo nombre_sucursal

SELECT *
FROM cuenta
ORDER BY nombre_sucursal DESC

En el ejemplo se ha utilizado el operador de comparación DESC con la cláusula ORDER BY para el atributo nombre_sucursal, el cuál nos devolverá los valores ordenados de forma descendente (en order alfabético porque era un atributo alfanumérico) por el atributo nombre_sucursal

El tipo de ordenación devuelto por los operadores de comparación ASC y DESC dependerán del tipo del atributo, ya que no se ordena de igual manera un número, una cadena o una fecha

Si necesitamos ordenar más de un atributo, lo haremos separandolos por comas y seguido el operador de comparación (ASC ó DESC )

El método de ordenación por defecto es ASC, es opcional y podríamos prescindir de escribirlo

Operaciones sobre conjuntos

SQL permite realizar las operaciones sobre conjuntos del álgebra relacional unión $\cup$ , intersección $\cap$ y diferencia $\neg$

Para utilizarlas tendremos la única restricción de que los nombres de los atributos de las relaciones implicadas deben ser iguales

Operación UNION

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{UNION}\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q$

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una unión tendrá la siguiente expresión de álgebra relacional equivalente:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \cup \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )$

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cuenta
UNION
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco o que tienen un cliente asociado a ellas

A diferencia de la cláusa SELECT la operación UNION elimina los valores repetidos

Por esta razón es buena idea utilizar la operación UNION para crear claves, ya que con SELECT no obtendríamos claves únicas (permite repetidos)

SELECT numero_cuenta
FROM cuenta
UNION ALL
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco o que tienen un cliente asociado a ellas

Al utilizar el operador ALL en conjunto con la operación UNION, estamos permitiendo que se comporte como un SELECT, ya que admite repetidos

Operación INTERSECT

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{INTERSECT}\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q$

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una intersección tendrá la siguiente expresión de álgebra relacional equivalente:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \cap \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )$

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cuenta
INTERSECT
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco y que tienen un cliente asociado a ellas

La operación INTERSECT elimina los valores repetidos y sólo devuelve aquellos valores que tienen coincidencia en la relación cuenta y en cliente a la vez, no sólo en uno de ellos

SELECT numero_cuenta
FROM cuenta
INTERSECT ALL
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco y que tienen un cliente asociado a ellas

Al utilizar el operador ALL en conjunto con la operación INTERSECT, estamos permitiendo que se comporte como un SELECT, ya que admite repetidos

Operación EXCEPT

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{EXCEPT}\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q$

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una diferencia tendrá la siguiente expresión de álgebra relacional equivalente:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \cap \neg (\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) ) )$

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cuenta
EXCEPT
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco y que NO tienen un cliente asociado a ellas

La operación EXCEPT elimina los valores repetidos y sólo devuelve aquellos valores que tienen coincidencia en la relación cuenta pero no coinciden con cliente

SELECT numero_cuenta
FROM cuenta
EXCEPT ALL
SELECT numero_cuenta
FROM cliente

En el ejemplo se ha resuelto la consulta de todos los números de cuenta que existen en el banco y que NO tienen un cliente asociado a ellas

Al utilizar el operador ALL en conjunto con la operación EXCEPT, estamos permitiendo que se comporte como un SELECT, ya que admite repetidos

Operación IN

SQL permite comprobar la pertenencia de las tuplas a una relación que es equivalente a comprobar la pertenencia a un conjunto del álgebra relacional

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{IN}\\ (\text{SELECT} _1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)$

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \in \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )$

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta IN (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen cuenta en la sucursal de ‘Navacerrada‘

Anteriormente la resolvimos utilizando la cláusula JOIN

Pero como puede comprobarse, también podemos resolverla utilizando la teoría de conjuntos

La pertenencia nos devolverá los valores y en caso de no haber ninguno, mostrará null

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta NOT IN (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que NO tienen cuenta en la sucursal de ‘Navacerrada‘

Al negar la pertenencia nos devolverá el resto de valores y en caso de no haber ninguno, mostrará null

SELECT numero_cuenta
FROM cuenta
WHERE nombre_sucursal IN ('Logroño', 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen cuenta en la sucursal de ‘Logroño‘ ó ‘Navacerrada‘

Como puede apreciarse, la pertenencia o la no pertenencia, aparte de una subconsulta como en los ejemplos anteriores, también admite conjuntos enumerados

Siempre que esos conjuntos enumerados sean del mismo dominio que el atributo del que se quiere comprobar la pertenencia

Operadores de comparación

SQL ofrece la posibilidad de comparar conjuntos utilizando subconsultas

Operador SOME

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{SOME}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)$

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \approx \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )$

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta = SOME (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener algún cliente que tiene cuenta en la sucursal de ‘Navacerrada‘

Anteriormente la resolvimos utilizando la operación IN

Pero como puede comprobarse, también podemos resolverla utilizando una comparación de conjuntos

En este caso utilizando el símbolo = delante del operador SOME para denotar que son iguales

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta <> SOME (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener algún cliente que NO tiene cuenta en la sucursal de ‘Navacerrada‘

Aunque podría parecer que es equivalente al ejemplo anterior con NOT INT, el resultado puede no ser igual

También podemos utilizar el resto de operadores lógicos de comparación

Operador ANY

Puede utilizarse también el operador ANY ya que es equivalente a SOME

Las primeras versiones de SQL sólo admitían el operador ANY, pero se añadió SOME para que se asemejase más al lenguaje natural, ya que any creaba ambigüedad en la lengua inglesa

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{ANY}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)$

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

También podemos utilizar el resto de operadores lógicos de comparación

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta = ANY (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener algún cliente que tiene cuenta en la sucursal de ‘Navacerrada‘

Anteriormente la resolvimos utilizando la operación SOME

Como puede verse es totalmente equivalente y sólo lo usaríamos por compatibilidad con versiones antiguas de SQL

Operador ALL

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE} P\\ \text{ALL}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} Q)$

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_1) ) \forall \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (r_2) )$

Si se omite la clausula WHERE, el predicado P ó Q (depende cuál se omita), es siempre cierto

También podemos utilizar el resto de operadores lógicos de comparación

SELECT numero_cuenta
FROM cliente
WHERE numero_cuenta > ALL (SELECT numero_cuenta
                        FROM cuenta
                        WHERE nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen cuenta en la sucursales después de ‘Navacerrada‘ (alfabéticamente)

En realidad al utilizar el símbolo > delante del operador ALL estamos denotando que buscamos todos los valores que tengan una ordenación alfabética superior a ‘Navacerrada‘

Control de repetidos

SQL permite controlar si una subconsulta tiene tuplas repetidas en su resultado

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_1\\ \text{WHERE UNIQUE}\\ (\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2, \cdots, r_n\\ \text{WHERE} P)$

Donde cada A_i representa a un atributo, cada r_i una relación y P es predicado

Por lo que una consulta SQL que contiene una pertenencia tendrá la siguiente expresión de álgebra relacional equivalente:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma \exists \prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r_2, \cdots, r_n) ) (r_1) )$

Si se omite la clausula WHERE, el predicado P, es siempre cierto

Formalmente la evaluación de UNIQUE sobre una relación se define como falsa si y sólo si la relación contiene dos tuplas r_i y r_k tales que r_i = r_k

Como la comprobación r_i = r_k es falsa si algún atributo de r_i ó r_k es nulo, es posible que el resultado de UNIQUE sea cierto aunque haya varios duplicados de una misma tupla

Siempre que al menos uno de los atributos de la tupla sea nulo

SELECT cl.numero_cuenta
FROM cliente cl
WHERE UNIQUE (SELECT c.numero_cuenta
              FROM cuenta c 
              WHERE cl.numero_cuenta = c.numero_cuenta AND
                    c.nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen a lo sumo una cuenta en la sucursal de ‘Navacerrada‘

Para ello se han introducido las relaciones cuenta (se le ha asignado el alias c) y cliente (se le ha asignado el alias cl)

Sólo con la cláusula FROM no podríamos resolver la consulta

Podríamos haber usado la cláusula JOIN, aunque puede seguir habiendo repetidos

Por eso se ha añadido el operador UNIQUE que relaciona ambas relaciones con el atributo numero_cuenta y la igualdad para la atributo nombre_sucursal con el valor ‘Navacerrada‘

Después de añadirlos, tendremos sólo los valores que pertenecen a la relación

SELECT cl.numero_cuenta
FROM cliente cl
WHERE NOT UNIQUE (SELECT c.numero_cuenta
              FROM cuenta c 
              WHERE cl.numero_cuenta = c.numero_cuenta AND
                    c.nombre_sucursal = 'Navacerrada')

El ejemplo resuelve la consulta de obtener todos los clientes que tienen más de una cuenta en la sucursal de ‘Navacerrada‘

Como puede verse, la negación de UNIQUE nos permite comprobar si hay más de un repetido

Funciones de agregación

Las funciones de agregación son funciones que toman una colección (un conjunto o multiconjunto) de valores como entrada y devuelven un solo valor

SQL permite cinco funciones de agregación:

AVG
Permite realizar la media aritmética de la colección, la cuál debe ser numérica

Si hay valores nulos en la entrada, se ignoran
```
SELECT AVG(saldo) media
FROM cuenta
```
En el ejemplo se ha resuelto la consulta obtener la media aritmética del saldo de todas las cuentas

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama media
MIN
Permite determinar cuál es valor mínimo de la colección, la cuál no es obligatorio que sea numérica

Si hay valores nulos en la entrada, se ignoran
```
SELECT MIN(saldo) menor
FROM cuenta
```
En el ejemplo se ha resuelto la consulta obtener el saldo más bajo de todas las cuentas

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama menor
MAX
Permite determinar cuál es valor máximo de la colección, la cuál no es obligatorio que sea numérica

Hay que tener en cuenta que NO admite DISTINCT pero si ALL, pero como es el valor por defecto, puede omitirse

Si hay valores nulos en la entrada, se ignoran
```
SELECT MAX(saldo) mayor
FROM cuenta
```
En el ejemplo se ha resuelto la consulta obtener el saldo más alto de todas las cuentas

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama mayor
SUM
Permite realizar la suma de la colección, la cuál debe ser numérica

Si hay valores nulos en la entrada, se ignoran
```
SELECT SUM(saldo) liquido
FROM cuenta
```
En el ejemplo se ha resuelto la consulta obtener el saldo total de todas las cuentas

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama liquido
COUNT
Permite contar el número de elementos que compone la colección, la cuál no es obligatorio que sea numérica

Si hay valores nulos en la entrada, se toman como 0
```
SELECT COUNT(saldo) total
FROM cuenta
```
En el ejemplo se ha resuelto la consulta obtener cuantas cuentas hay en total

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama total

Cláusula GROUP BY

Existen circunstancias en las cuales sería deseable aplicar las funciones de agregación no sólo a un único conjunto de tuplas sino también a un grupo de conjuntos de tuplas

En SQL podemos especificarlo mediante la cláusula GROUP BY

La forma más habitual de consulta será:

$\text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r\\ \text{WHERE} P\\ \text{GROUP BY} A_1, \cdots, A_2, \cdots, A_n\\ \text{HAVING} Q$

Donde cada A_i representa a un atributo, cada r_i una relación y P, Q son predicados

Por lo que una consulta SQL que contiene una agrupación tendrá la siguiente expresión de álgebra relacional equivalente:

$\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma q (\prod_{A_1, \cdots, A_2, \cdots, A_n} (\sigma p (r) ) )$

Si se omiten las clausulas WHERE ó HAVING, el predicado P ó Q (depende cuál se omita), es siempre cierto

SELECT nombre_sucursal, COUNT(saldo) total
FROM cuenta
GROUP BY nombre_sucursal, saldo

En el ejemplo se ha resuelto la consulta obtener cuantas cuentas hay en total de cada sucursal

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama total

Para que la operación de agrupación pueda realizarse sin errores debemos utilizar el mismo orden de los atributos tanto en la cláusula SELECT como en GROUP BY

En el caso de que no vayan a intervenir en la agrupación, podremos omitir esos atributos colocándolos a la derecha de los que sí serán usados

Cláusula HAVING

En ocasiones necesitamos que una condición se aplique a los grupos en vez de a las tuplas

Para expresar este tipo de consultas utilizamos la cláusula HAVING

SELECT nombre_sucursal, COUNT(saldo) total
FROM cuenta
GROUP BY nombre_sucursal, saldo
HAVING nombre_sucursal LIKE 'Navacerrada'

En el ejemplo se ha resuelto la consulta obtener cuantas cuentas hay en total de la sucursal de ‘Navacerrada‘

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama total

Se ha aplicado la condición sobre el atributo nombre_sucursal con el valor ‘Navacerrada‘ utilizando la cláusula HAVING para aplicarla sobre la agrupación y no sobre la relación

En el caso de que tendríamos una cláusula WHERE y una HAVING:

SQL aplicará primero el predicado de la cláusula WHERE
El resultado se dividirá en los grupos que satisfagan el predicado de la cláusula HAVING

SELECT nombre_sucursal, COUNT(saldo) total
FROM cuenta
GROUP BY nombre_sucursal, saldo
HAVING nombre_sucursal LIKE 'Navacerrada' AND COUNT(saldo) > 100

En el ejemplo se ha resuelto la consulta obtener cuantas cuentas hay en total de la sucursal de ‘Navacerrada‘ y que tengan un saldo mayor de 100

Se ha creado un atributo calculado (que no pertenece a la relación) a partir de esas operaciones, asignándole un nombre o alias a esa operación, en este caso se llama total

Se ha aplicado la condición sobre el atributo nombre_sucursal con el valor ‘Navacerrada‘

Adicionalmente se ha aplicado la condición sobre el atributo saldo aplicándole la operación de agrupación COUNT con la condición de que sean mayores que 100

Ambas han sido aplicacadas sobre la cláusula HAVING

Como puede verse, pueden aplicarse sin problemas las operaciones de agregación dentro de la cláusula HAVING aunque también aparezcan en la cláusula SELECT

INSERT

Las relaciones recién creadas están inicialmente vacías

Se puede utilizar el comando INSERT para añadir datos a la relación

La forma más habitual de consulta de inserción será:

$\text{INSERT INTO r}\\ \text{VALUES} (A_1, A_2, \cdots, A_n)$

Donde cada A_i representa a un atributo, r la relación y cada P_i es un predicado

INSERT INTO cuenta
VALUES ('C-9732', 'Navacerrada', 1200)

En el ejemplo se ha resuelto la consulta para añadir la cuenta C-9732 en la sucursal de ‘Navacerrada‘ con un saldo de 1200€

Los valores se especifican en el orden en que se relacionan los atributos correspondientes en el esquema de la relación

INSERT INTO cuenta (saldo, numero_cuenta, nombre_sucursal)
VALUES (1200, 'C-9732', 'Navacerrada')

En el ejemplo se ha modificado el orden en que se relacionan los atributos correspondientes en el esquema de la relación y por eso los datos se han introducido de forma obligatoria, en el nuevo orden que hemos especificado

De esta forma podemos elegir nosotros en qué orden introducimos los datos

En el caso de no introducir un atributo, SQL tratará de introducir un valor por defecto o el valor nulo y si es la clave primaria, notificará el error e impedirá la actualización

INSERT INTO cliente
       SELECT numero_cuenta, 'Pedro Almodovar'
       FROM cuenta
       WHERE nombre_sucursal = 'Navacerrada'
       EXCEPT
       SELECT numero_cuenta
       FROM cliente

En el ejemplo se ha resuelto la consulta para añadir el cliente ‘Pedro Almodovar‘ para todos los números de cuenta que existen en la sucursal de ‘Navacerrada‘ y que NO tienen un cliente asociado a ellas

En lugar de especificar una tupla se ha usado una subconsulta ya que la cláusula SELECT nos permitirá especificar un conjunto de tuplas

La forma más habitual de subconsulta para la inserción será:

$\text{INSERT INTO} r1\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_2\\ \text{WHERE} P$

Donde cada A_i representa a un atributo, r_i las relaciones y P es un predicado

Se evaluará en primer lugar la cláusula SELECT y se insertarán en la relación cliente las tuplas obtenidas de la subconsulta

Hemos de tener cuidado al utilizar subconsultas anidadas, ya que al abusar de ellas, podríamos crear un bucle infinito de subconsultas y la inserción se realizaría de forma infinita, quedando la base de datos bloqueada

Por suerte los sistemas gestores de bases de datos tienen mecanismos o herramientas para realizar inserciones masivas de forma controlada, evitando el bloqueo de la base de datos

UPDATE

Se puede utilizar el comando UPDATE para modificar los valores de un conjunto de tuplas existentes de una relación

La forma más habitual de consulta de actualización será:

$\text{UPDATE r}\\ \text{SET} A_1 = P_1, A_2 = P_2, \cdots, A_n = P_n$

Donde cada A_i representa a un atributo, r la relación y cada P_i es un predicado

UPDATE cuenta
SET saldo = 100

En el ejemplo se ha actualizado la relación llamada cuenta, en concreto al atributo saldo le hemos cambiado su valor a 100

El comando actualizará todas las tuplas de la relación cuenta

UPDATE cuenta
SET saldo = 100
WHERE numero_cuenta LIKE 'C-9732'

En el ejemplo se han actualizado sólo las tuplas que cumplen la condición introducida, en este caso las que en su atributo numero_cuenta tienen un valor igual a ‘C-9732‘

En lugar de especificar una tupla se ha usado una subconsulta ya que la cláusula SELECT nos permitirá especificar un conjunto de tuplas

Por suerte los sistemas gestores de bases de datos tienen mecanismos o herramientas para realizar inserciones masivas de forma controlada, evitando el bloqueo de la base de datos

Operador Case

El orden de dos instrucciones UPDATE es importante ya que si se cambia el orden de las instrucciones de actualización podemos obtener datos incoherentes

Para solucionar este problema tenemos el operador CASE

La forma más habitual de consulta de actualización será:

$\text{UPDATE r}\\ \text{SET} A_1 = P_1, A_2 = P_2, \cdots, A_n = P_n$

Donde cada A_i representa a un atributo, r la relación y cada P_i es un predicado el cual podemos sustituir por el siguiente predicado Q:

$\text{CASE}\\ \text{WHEN} P_1 \text{THEN} r_1\\ \text{WHEN} P_2 \text{THEN} r_2\\ \cdots\\ \text{WHEN} P_n \text{THEN} r_n\\ \text{ELSE} r_0\\ \text{END}$

Donde cada P_i cada predicado que puede satisfacerse, cada r_i el resultado que se usará en la actualización

En el caso de no cumplirse ninguno de los predicados, se aplicaría el resultado r₀

UPDATE cuenta
SET saldo = CASE
                WHEN saldo <= 100 THEN saldo * 0.05
                WHEN saldo = 500 THEN saldo * 0.07
                WHEN saldo > 1000 THEN saldo * 0.25
                ELSE saldo * 0.01
            END

En el ejemplo se ha resuelto la consulta de actualización para añadir un 0.05 al saldo cuando es menor o igual que 100 con una modificación del 0.05, cuando es igual que 500 con una modificación del 0.07, cuando es mayor que 1000 con una modificación del 0.25 y una modificación del 0.01 en cualquier otro caso

Se ha utilizado la cláusula CASE para resolverlo al igualarlo con el atributo saldo que era el valor a actualizar

DELETE

Se puede utilizar el comando DELETE para borrar tuplas de una relación

Y sólo de esa relación, ya que no es capaz de trabajar sobre varias

La forma más habitual de consulta de borrado será:

$\text{DELETE FROM r}\\ \text{WHERE} P$

Donde r representa la relación y cada P es un predicado

DELETE FROM cuenta

En el ejemplo se ha borrado la relación llamada cuenta

El comando borrará todas las tuplas de la relación cuenta

DELETE FROM cuenta
WHERE saldo < 100

En el ejemplo se han borrado sólo las tuplas que cumplen la condición introducida, en este caso las que en su atributo saldo tienen un valor inferior a 100

Hay que tener en cuenta que el comando DELETE comprueba primero cada tupla de la relación cuenta y luego borrará todas las tuplas que cumplen la condición

Es importante realizar todas las comprobaciones antes del borrado ya que el orden en que se procesan las tuplas puede variar la operación de borrado

Lenguaje de programación, SQL, Programación

Vistas

07 de octubre de 2024 secarcam

Vistas

Las vistas se utilizan en situaciones en las que por motivos de seguridad o de rendimiento, queremos ocultar ciertos datos a los usuarios

SELECT numero_cuenta, nombre_sucursal, saldo
FROM cuenta
WHERE numero_cuenta LIKE 'C-9732'

En el ejemplo, un cliente necesita saber el número operación de un préstamo a su nombre, el ‘C-9732‘ y el nombre de la sucursal que lo ha tramitado, pero para esa operación no es necesario que vea el saldo o más datos relacionados

Con la consulta anterior resolvíamos lo que el cliente necesitaba, pero le dábamos más datos de los necesarios, pudiendo poner a la entidad bancaria en una situación que podría ser insegura

Las relaciones que no forman parte del modelo lógico pero son visibles a los usuarios mediante relaciones virtuales, se denominan vistas

Definición básica de vistas

CREATE VIEW

Las relaciones se definen mediante el comando CREATE VIEW:

$\text{CREATE VIEW v} AS r$

Donde r es una expresión de consulta y v es el nombre de la vista

CREATE VIEW todas_las_cuentas AS
SELECT numero_cuenta, nombre_sucursal
FROM cuenta

En el ejemplo se ha creado una vista que trata de resolver el problema del ejemplo anterior

Sin embargo, a pesar de que hemos mejorado la seguridad al no mostrar al usuario los saldos, no resuelve el problema, aún deberíamos hacer la subconsulta siguiente para resolverlo:

SELECT numero_cuenta, nombre_sucursal
FROM todas_las_cuentas
WHERE numero_cuenta LIKE 'C-9732'

En el ejemplo se ha tratado la vista como si fuera una relación más

Sin embargo, para el sistema gestor de bases de datos es una relación virtual por ser una vista

Al ser una relación virtual podemos hacer operaciones de consulta SELECT

Pero no podemos hacer operaciones de actualización como INSERT, UPDATE ó DELETE

CREATE VIEW prestamo(nombre_sucursal, total) AS
SELECT nombre_sucursal, COUNT(saldo) total
FROM cuenta
GROUP BY nombre_sucursal, saldo

En el ejemplo se ha creado una vista llamada prestamo(nombre_sucursal, total) que permite obtener los nombres de todas las sucursales y el saldo total que tienen

Como puede verse, es posible devolver cualquier atributo y trabajar normalmente con las consultas

En este caso se ha devuelto el atributo nombre_sucursal y se ha utilizado la función de agregación COUNT despues de usar la cláusula GROUP BY con los atributos nombre_sucursal y saldo

CREATE VIEW cuentas_navacerrada AS
SELECT numero_cuenta, nombre_sucursal
FROM todas_las_cuentas
WHERE nombre_sucursal LIKE 'Navacerrada'

En el ejemplo se ha definido una vista a partir de otra para resolver la consulta que obtiene todos números de cuentas de la sucursal de ‘Navacerrada‘

Para ello hemos usado la vista llamada todas_las_cuentas

Ya que es tomado como una relación virtual para el sistema gestor de bases de datos por ser una vista

Hay que tener en cuenta que anidar vistas sobre si mismas puede producir un efecto recursivo y si están mal definidas bloquear la base de datos al caer en un bucle infinito

Actualización de vistas

Por norma general una vista no puede actualizarse, sin embargo un sistema gestor de bases de datos puede permitir hacerlo en casos concretos

Hay que consultar el manual del sistema gestor de bases de datos para saber en qué casos concretos lo permite y cómo lo resuelve

En el caso de que se permita su actualización, la vista debe cumplir:

La cláusula FROM sólo tiene una relación
La cláusula SELECT sólo contiene nombres de atributos de la relación, sin alias, valores agregados y sin la especificación DISTINCT
Cualquier atributo que no aparece en la cláusula SELECT deberá ser definido como nulo
La consulta no debe contener funciones de agregación (Cláusula GROUP BY, Cláusula HAVING)

DROP VIEW

Se puede utilizar el comando DROP VIEW para borrar una vista

Y sólo de esa vista, ya que no es capaz de trabajar sobre varias

La forma más habitual de consulta de borrado de vistas será:

$\text{DROP VIEW v}$

Donde v es el nombre de la vista

DROP VIEW todas_las_cuentas

En el ejemplo se ha borrado la vista llamada todas_las_cuentas

Cláusula WITH

La cláusula WITH fue introducida en la norma SQL:1999 y permite definir una vista temporal

Esta vista temporal sólo puede ser usada en la consulta en la cuál ha sido definida

La forma más habitual será:

$\text{WITH} v \text{AS} r_0\newline\\ \text{SELECT} A_1, \cdots, A_2, \cdots, A_n\\ \text{FROM} r_0, r_1, r_2, \cdots, r_m, r\\ \text{WHERE} P$

Donde cada A_i representa a un atributo, cada r_i una relación (r₀ es una consulta), P es un predicado y v es el nombre de la vista temporal

WITH todas_las_cuentas AS
SELECT numero_cuenta, nombre_sucursal
FROM cuenta

SELECT numero_cuenta, nombre_sucursal
FROM todas_las_cuentas
WHERE numero_cuenta LIKE 'C-9732'

Este ejemplo es equivalente a la vista que vimos en otro ejemplo anterior

Solo que en esta ocasión no hemos usado una vista definida en la base de datos

Hemos usado una vista temporal definida con la cláusula WITH

Lenguaje de programación, SQL, Programación

Transacciones

07 de octubre de 2024 secarcam

Transacciones

Las transacciones son una secuencia de instrucciones de consulta o de actualización

La norma SQL especifica que una transacción comienza implícitamente cuando se ejecuta una instrucción SQL

Al finalizar la instrucción debe ejecutarse una de las siguientes instrucciones SQL:

COMMIT WORK

Valida las actualizaciones realizadas hasta ese momento y hace que pasen a ser permanentes en la base de datos

Una vez completada, se iniciará de forma automática la siguiente transacción

CREATE TABLE cuenta(
numero_cuenta char(10),
nombre_sucursal char(15),
saldo numeric(12, 2),
primary key(numero_cuenta) );

INSERT INTO cuenta
VALUES ('C-9732', 'Navacerrada', 1200);
INSERT INTO cuenta
VALUES ('C-9733', 'Navacerrada', 1100);
INSERT INTO cuenta
VALUES ('C-9734', 'Navacerrada', 1000);
INSERT INTO cuenta
VALUES ('C-9735', 'Navacerrada', 900);
INSERT INTO cuenta
VALUES ('C-9736', 'Navacerrada', 800);
INSERT INTO cuenta
VALUES ('C-9737', 'Navacerrada', 700);
INSERT INTO cuenta
VALUES ('C-9738', 'Navacerrada', 600);
COMMIT;

En el ejemplo hemos creado una relación llamada cuenta y la hemos poblado con 7 tuplas

Las instrucciones de consulta o actualización están separadas unas de otras por el símbolo ; indicando a SQL que es una nueva instrucción

Al terminar hemos ejecutado COMMIT para que los cambios se guarden en la base de datos

ROLLBACK WORK

Provoca la anulación de la transacción actual, es decir, anula todas las operaciones realizadas en la base de datos

La base de datos vuelve al estado inicial, antes de la primera instrucción de la transacción

CREATE TABLE cuenta(
numero_cuenta char(10),
nombre_sucursal char(15),
saldo numeric(12, 2),
primary key(numero_cuenta) );

INSERT INTO cuenta
VALUES ('C-9732', 'Navacerrada', 1200);
INSERT INTO cuenta
VALUES ('C-9733', 'Navacerrada', 1100);
INSERT INTO cuenta
VALUES ('C-9734', 'Navacerrada', 1000);
INSERT INTO cuenta
VALUES ('C-9735', 'Navacerrada', 900);
INSERT INTO cuenta
VALUES ('C-9736', 'Navacerrada', 800);
INSERT INTO cuenta
VALUES ('C-9737', 'Navacerrada', 700);
INSERT INTO cuenta
VALUES ('C-9738', 'Navacerrada', 600);
ROLLBACK;

En el ejemplo hemos creado una relación llamada cuenta y la hemos poblado con 7 tuplas

Las instrucciones de consulta o actualización están separadas unas de otras por el símbolo ; indicando a SQL que es una nueva instrucción

Al terminar hemos ejecutado ROLLBACK para que los cambios no se guardasen en la base de datos

La palabra clave WORK es opcional en ambas instrucciones, por eso hemos podido omitirla en los ejemplos

Utilizar ROLLBACK es útil cuando se detecta un error y es necesario volver a un estado estable de la base de datos

Una vez que se ha ejecutado COMMIT, no es posible realizar un ROLLBACK, ya que los cambios en los datos se hacen permanentes

Por eso, cuando los sistemas de bases de datos detectan un fallo (ya sea una caída de tensión, una caída del sistema, una mala conexión en la red), internamente realizan un ROLLBACK se deniega el uso del COMMIT, para evitar la pérdida de información

En el caso especial de la caída del sistema, el ROLLBACK se realizará al reinicio del sistema

A partir de la norma SQL:1999 se puede encerrar el contenido de una transacción con el siguiente formato:

$\text{BEGIN}\\ S_1;\\ S_2;\\ \cdots;\\ S_n;\\ \text{END}$

Donde S_i es una instrucción de consulta o una actualización

BEGIN
    CREATE TABLE cuenta(
    numero_cuenta char(10),
    nombre_sucursal char(15),
    saldo numeric(12, 2),
    primary key(numero_cuenta) );

    INSERT INTO cuenta
    VALUES ('C-9732', 'Navacerrada', 1200);
    INSERT INTO cuenta
    VALUES ('C-9733', 'Navacerrada', 1100);
    INSERT INTO cuenta
    VALUES ('C-9734', 'Navacerrada', 1000);
    INSERT INTO cuenta
    VALUES ('C-9735', 'Navacerrada', 900);
    INSERT INTO cuenta
    VALUES ('C-9736', 'Navacerrada', 800);
    INSERT INTO cuenta
    VALUES ('C-9737', 'Navacerrada', 700);
    INSERT INTO cuenta
    VALUES ('C-9738', 'Navacerrada', 600);
END
COMMIT;

El ejemplo es muy similar al mostrado anteriormente para el COMMIT

Sin embargo, en esta ocasión hemos encerrado la transacción con el formato de la norma SQL:1999

De esta forma podremos distinguir dónde empiezan y terminan distintas transacciones sin tener que buscar las instrucciones COMMIT ó ROLLBACK

Lenguaje de programación, SQL, Java, Programación

JDBC

07 de octubre de 2024 secarcam

JDBC

JDBC es un conjunto de clases e interfaces Java para la ejecución de sentencias SQL

Es la parte CLI de Java (Call-Level Interface)

Fue desarrollado conjuntamente por JavaSoft, Sybase, Informix e IBM entre otros

JDBC permite la manipulación de cualquier base de datos SQL

No es necesario hacer un programa específico para manipular Oracle, Sybase, etc…

Nuestro programa puede manipular cualquier base de datos

Uniendo Java con JDBC obtenemos programas que se pueden ejecutar en cualquier plataforma y que pueden manipular cualquier base de datos

Las clases e interfaces JDBC se encuentran dentro del paquete java.sql

Proceso de trabajo con JDBC

El proceso de trabajo en JDBC consiste en los siguientes pasos:

Conectarse a la base de datos (mediante DriverManager)
Emitir sentencias SQL (mediante Statement, PreparedStatement, CallableStatement)
Procesamiento de los resultados (ResultSet)

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;

class ejemplo{
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    public ejemplo(){
        int x = 0;
        String s = "";
        float f = 0.0f;
        String url = "jdbc:odbc:datos";
        String user = "login";
        String pass = "password";

        try{
            con = DriverManager.getConnection(url, user, pass);
            stmt = con.createStatement();
            rs = stmt.executeQuery("SELECT d1, d2, d3 FROM Tabla1");

            while(rs.next()){
                x = rs.getInt("d1");
                s = rs.getString("d2");
                f = rs.getFloat("d3");
            }
            rs.close();
            stmt.close();
        }catch(SQLException e){
            e.printStackTrace();
        }finaly{
            try{
                if(con != null){
                    con.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }
}

Driver JDBC

Cada base de datos concreta implementa la interface JDBC de una manera particular, al igual que las clases auxiliares y de utilidad que sean necesarias para esa base de datos

Por eso son necesarios los drivers de bases de datos

Una misma aplicación podrá conectar con distintas bases de datos simplemente cambiando ese driver (no debemos confundir el driver de la base de datos con la clase Driver, pues son cosas distintas)

El interface Driver especifica los métodos que todo driver JDBC debe implementar, para ello se pueden cargar los drivers de dos maneras:

Cuando se inicia la clase Driver, el DriverManager consulta la propiedad jdbc.drivers

Dicha propiedad contiene una lista de drivers (las clases) que deben ser cargadas

Para ello deberá ejecutarse un comando como este (para usar ODBC) en la línea de comandos:
```
java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver aplicacion
```
Si se desea introducir un nuevo driver después de que el DriverManager se halla inicializado se deberá usar el método forname de la clase Class

Para ello deberemos incluir en nuestro código la siguiente instrucción (para usar ODBC):
```
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
```
Es aconsejable que se use de forma static en nuestra aplicación (porque sólo se cargará el driver una vez, al cargar la clase)

Ejemplos de drivers
Base de datos	Driver
ODBC	sun.jdbc.odbc.JdbcOdbcDriver
Oracle	oracle.jdbc.driver.OracleDriver
SQLServer	com.microsoft.jdbc.sqlserver.SQLServerDriver
MySQL	com.mysql.jdbcDriver

Tipos de driver:

Puente JDBC-ODBC
Traduce JDBC a ODBC y lo retransmite al driver ODBC de la máquina

Es el driver ODBC el que realmente se comunica con la base de datos

Está incluido en el JDK pero no incluye JDBC2

Inconvenientes:
- Es útil para realizar pruebas, pero es lento en producción
- Es necesario el driver ODBC en el cliente (menor portabilidad)
Driver JDBC sobre driver nativo de la BD
Retransmite JDBC al driver nativo instalado en la máquina

El driver nativo es el que realmente se comunica con al base de datos

Inconveniente:
- necesidad de driver nativo (menor portabilidad)
Driver Java sobre red
Traduce las llamadas JDBC a un protocolo de red independiente de la plataforma que contacta con el servidor

El servidor traduce esas peticiones al protocolo concreto de cada base de datos

Se usa un middleware en el servidor de red que es capaz de conectar a los clientes puros Java a muchas bases de datos diferentes

Ventaja:
- es rápido, independiente de la plataforma y no requiere de instalación en el cliente
Driver puro Java con protocolo nativo
Traduce las llamadas JDBC al protocolo específico de la base de datos contactando directamente con ella

Obtener conexiones

Mediante el driver concreto realizaremos las conexiones, pero las solicitudes de conexión deben ser realizadas mediante el DriverManager

Una vez que la clase Driver ha sido cargada y registrada, el DriverManager puede establecer conexiones con la base de datos mediante estos dos pasos:

Se llama al método DriverManager.getConnection(url, user, pass); y se obtiene un objeto de tipo Connection
El DriverManager prueba los drivers registrados para ver si puede establecer la conexión y si no fue posible, lanza una SQLException

Una misma aplicación, puede tener varias conexiones a la misma base de datos o varias conexiones a otras bases de datos (hasta el máximo permitido por la base de datos)

Los parámetros que admite getConnection son la url (que es un subprotocolo que utiliza la base de datos para realizar la conexión), el user (el nombre de usuario que va a conectarse) y el pass (la contraseña que utiliza el usuario para conectarse)

El parametro url tiene el siguiente formato:

jdbc:<subprotocolo>:<subnombre>

El subprotocolo es particular de cada base de datos y lo utiliza el DriverManager para buscar el driver adecuado para manipularla

El subnombre depende del subprotocolo concreto

El driver será el encargado de interpretarlo y le ayudará a localizar la base de datos

Nuestra aplicación no trabajará con el driver concreto, sino que lo hará el DriverManager

De esta manera las aplicaciones pueden trabajar con el objeto Connection sin preocuparse por el tipo de base de datos con la cual estemos trabajando (no hay que hacer modificaciones en el código, solo cambiar el parámetro url)

La conexión debe cerrarse siempre al finalizar, porque sino se consumen recursos de forma innecesaria, aunque se pueden reutilizar conexiones, no es aconsejable, es mejor usar siempre una conexión nueva

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

class conexion{
    static{
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch(ClassNotFoundException e){
            System.out.println("No se pudo cargar el driver de la BD");
        }
     }

     Connection con = null;

     public conexion(){
         try{
             String url = "jdbc:oracle:thin@localhost:1521:balance";
             String user = "contable";
             String pass = "#cap1128";

             con = DriverManager.getConnection(url, user, pass);

             //a partir de esta línea incluiríamos las consultas SQL que necesitáramos
         }catch(SQLException e){
             e.printStackTrace();
         }finaly{
             try{
                 if(con != null){
                     con.close();
                 }
             }catch(SQLException e){
                 e.printStackTrace();
             }
         }
     }
}

En el ejemplo se ha utilizado un esqueleto de conexión para Oracle

Para utilizar la conexión debemos crear un objeto de tipo Connection, el cual representa una sesión abierta con la base de datos

El objeto provee de un contexto con el que poder emitir sentencias SQL y obtener resultados

El objeto Connection debe inicializarse inicialmente a null, así podremos comprobar si hubo conexión porque incluiremos un bloque try para gestionar errores, el cuál lanzará SQLException

Para cerrar la conexión debe hacerse mediante el método close(), dentro de un finaly (el cual también tendrá su bloque try que lanza SQLException)

El interface Statement

La conexión nos permite crear objetos Statement mediante el método createStatemen()

Connection con = DriverManager.getConnection(url, user, pass);
Statement stmt = con.createStatement()

Los objetos Statement permiten la ejecución de sentencias SQL y la obtención de resultados (mediante los objetos ResultSet)

Los métodos para ejecutar SQL aceptan String como parámetro (JDBC es CLI), los cuales pueden componerse dinámicamente en función de valores contenidos en variables

Para componer el String SQL habrá que concatenar los distintos fragmentos SQL (los estáticos y las variables)

String idcuenta = "572001";
String sql = "SELECT id, saldo FROM contabilidad WHERE id= '" + idcuenta + "'";

Hay tres formas de ejecutar sentencias SQL:

Mediante el método executeQuery(<sql>) para consultas mediante la sentencia SELECT que produce túplas como resultado (devuelve un objeto de tipo ResultSet)
```
rs = stmt.executeQuery("SELECT a, b, c FROM Tabla");
```
- Es como una tabla que almacena el resultado de la consulta
- Tiene una serie de métodos de consulta
- Es como un cursor (en terminología PL / SQL)
Mediante el método executeUpdate(<sql>) para actualizaciones mediante las sentencias INSERT, DELETE, UPDATE, así como comandos DDL (CREATE, DROP, ALTER TABLE, ADD) y bloques PL / SQL (entre los bloques begin y end)
```
stmt.executeUpdate("DROP TABLE Tabla");
int n = stmt.executeUpdate("UPDATE Table SET a = a + 1");
```
- Devuelve un entero que indica el número de filas modificadas por el comando
- Con los comandos DDL devuelve 0
Mediante el método execute(<sql>) que ejecuta cualquier sentencia SQL
- Si se utilizó un QUERY, devolverá True
  - El ResultSet se puede obtener mediante el método getResultSet()
- Si se utilizó un UPDATE, devolverá False
  - El total de filas modificadas se pueden obtener mediante el método getUpdateCount()

Después de procesar el resultado, se debe cerrar el Statement mediante el método close()

Este método cierra también el ResultSet asociado, de todas formas, Sun recomendaba cerrar el ResultSet de forma explícita para evitar errores no deseados

Un mismo Statement puede reutilizarse en una misma conexión para ejecutar distintas sentencias

El interface ResultSet

El objeto ResultSet actúa como un cursor dentro de los resultados

La primera vez que se lee apunta encima del primer resultado, pero no lo lee, por eso hay que leer la primera fila avanzando mediante el método next()

El método next() devuelve True si pudo avanzar o False si no pudo

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Tabla");

while(rs.next() ){
    //Procesamiento de cada fila
}

Para obtener los valores del ResultSet se utilizan los métodos get que tienen el siguiente formato get<tipo>(<columna>)

Para nombrar las columnas podemos o hacerlo por su nombre o por un indice, el cual empieza en 1, su numeración viene dada por el orden en que fue introducida en el SELECT

Hay que prestar atención a las fechas, porque se guardan como java.sql.Date, no como java.util.Date como se podría esperar

int a = 0;
String b ="";
java.sql.Date c = null;
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Tabla");

while(rs.next() ){
    a = rs.getInt("a");    //rs.getInt(1);
    b = rs.getString("b");    //rs.getString(2);
    c = rs.getDate("c");    //rs.getDate(3);
    System.out.println("FILA = " + a + " " + b + " " + c);
}

Conviene cerrar el ResultSet aunque se cierra implícitamente al cerrar o reutilizar el Statement que lo creó

Cuando se ha leído un null de SQL usando uno de los métodos get<tipo>, éste devuelve:

Un valor null de Java para aquellos métodos que devuelven objetos Java (getString(), getBigDecimal(), getDate(), getTime(), getTimestamp(), getObject(), etc)
Un valor 0 para aquellos métodos que devuelven tipos numéricos (getByte(), getShort(), getInt(), getLong(), getFloat(), getDouble() )
Un valor False para el método getBoolean()

Para determinar si un valor dado era null, primero debe intentar leerse la columna y usar el método de ResulSet wasNull() para saber si fue null

Devolverá True si lo era, False en caso contrario

int a = 0;
String b ="";
java.sql.Date c = null;
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Tabla");

while(rs.next() ){
    a = rs.getInt("a");    //rs.getInt(1);
    if(a == 0 && rs.wasNull() ){
        //Se procesa el nulo
    }
    b = rs.getString("b");    //rs.getString(2);
    if(b == null){
        //Se procesa el nulo
    }
    c = rs.getDate("c");    //rs.getDate(3);
    if(c == null){
        //Se procesa el nulo
    }
    System.out.println("FILA = " + a + " " + b + " " + c);
}

El interface PreparedStatement

Cada vez que se lanza un Statement la base de datos debe interpretarla y calcular un plan de consulta

Pero al usar PreparedStatement, se puede ejecutar múltiples veces, obteniendo un aumento de rendimiento al tener la consulta ya analizada y optimizada

No todas las bases de datos soportan PreparedStatement, hay que leer la documentación de la misma para saber si se pueden usar

Los objetos PreparedStatement derivan del Statement obtenido de la conexión, mediante el método prepareStatement(<sql>);

Un PreparedStatement sirve para lanzar instrucciones SQL precompiladas, podremos parametrizar una o más entradas mediante el operador ?, cuyos valores podrán ser modificados en distintas ejecuciones de la instrucción

Parametrizar las entradas es útil cuando desconocemos los valores de ciertos tipos de datos SQL en la base de datos de destino,

El valor parametrizado obtiene el valor correcto del driver de la base de datos, por lo que no deberemos preocuparnos por los tipos

Antes de que la instrucción SQL pueda ser ejecutada deberemos asignarle un valor a los parámetros de entrada

Para asignar valores usaremos los métodos set<tipo>(columna, valor); siendo el tipo compatible con el del parámetro

Para ejecutarlo se usan los métodos execute de Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

class conexion{
    static{
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch(ClassNotFoundException e){
            System.out.println("No se pudo cargar el driver de la BD");
        }
     }

     Connection con = null;
     PreparedStatement ps = null;

     public conexion(){
         try{
             String url = "jdbc:oracle:thin@localhost:1521:balance";
             String user = "contable";
             String pass = "#cap1128";
             float sueldos[] = {1500, 2000, 1205.75f};
             String nombres[] = {"Juan", "Ana", "María"};
             String sql = "UPDATE table SET sueldo=? WHERE nombre=?";

             con = DriverManager.getConnection(url, user, pass);
             ps = con.prepareStatement(sql);

            for(int i=0; i<sueldos.length; i++){
                ps.setFloat(1, sueldos[i]);
                ps.setString(2, nombres[i]);
                ps.executeUpdate();
            }
            ps.close();
         }catch(SQLException e){
             e.printStackTrace();
         }finaly{
             try{
                 if(con != null){
                     con.close();
                 }
             }catch(SQLException e){
                 e.printStackTrace();
             }
         }
     }
}

En el ejemplo se ha utilizado un PreparedStatement para Oracle

Página web de Sergio Cárcamo García dedicada a la informática y temas relacionados como los lenguajes de programación, la estadística, las matemáticas, etc

Cookie	Duración	Descripción
CookieLawInfoConsent	Hasta finalizar la sesión del navegador	Controla la visualización del consentimiento de Cookies, su gestión y visualización en la página web por parte del usuario
qtrans_admin_language	Hasta finalizar la sesión del navegador	Permite al administrador gestionar la traducción de la página web a varios idiomas
qtrans_edit_language	Hasta finalizar la sesión del navegador	Permite al administrador editar la traducción de la página web a varios idiomas
viewed_cookie_policy	Hasta finalizar la sesión del navegador	Controla si la visualización del consentimiento de Cookies es visible actualmente en la página web para el usuario o por el contrario está oculta

SQL

Evolución del SQL

Lenguaje de definición de datos (LDD)

Definición de los datos

Tipos básicos de dominios

Definición básica de esquemas

CREATE TABLE

DROP TABLE

ALTER TABLE

TRUNCATE

Lenguaje interactivo de manipulación de datos (LMD)

Definición básica de esquemas

Operación alias

SELECT

Cláusula SELECT

Cláusula FROM

Cláusula JOIN

Cláusula WHERE

Operador BETWEEN

Operador LIKE

Valores nulos

Cláusula ORDER BY

Operaciones sobre conjuntos

Operación UNION

Operación INTERSECT

Operación EXCEPT

Operación IN

Operadores de comparación

Operador SOME

Operador ANY

Operador ALL

Control de repetidos

Funciones de agregación

Cláusula GROUP BY

Cláusula HAVING

INSERT

UPDATE

Operador Case

DELETE

Vistas

Definición básica de vistas

CREATE VIEW

Actualización de vistas

DROP VIEW

Cláusula WITH

Transacciones

JDBC

Proceso de trabajo con JDBC

Driver JDBC

Obtener conexiones

El interface Statement

El interface ResultSet

El interface PreparedStatement

Página web de Sergio Cárcamo García dedicada a la informática y temas relacionados como los lenguajes de programación, la estadística, las matemáticas, etc

Política de Cookies