4. Las expresiones del lenguaje SQL

4.1. Introduction

En la mayoría de los comandos SQL, es posible utilizar una expresión. Tomemos, por ejemplo, el comando SELECT:

syntaxe

SELECT expr1, expr2, ... de table

WHERE expression

SELECT selecciona las líneas para las que expression es verdadera y muestra para cada una de ellas los valores de expri.

Ejemplos

SQL> select prix*1.186 from biblio
SQL> select titre from biblio where prix between 100 and 150

En este apartado nos proponemos explicar el concepto de expresión. Una expresión elemental es del tipo:

operando1 opérateur operando2

o

función(parámetros)

Ejemplo

En la expresión GENRE = 'ROMAN'

GENRE es el operando 1
'ROMAN' es el operando 2
= es el operador

En la expresión upper(genre)

upper es una función
«genre» es un parámetro de esta función.

En primer lugar trataremos las expresiones con operadores y, a continuación, presentaremos las funciones disponibles en Firebird.

4.2. Expresiones con operadores

Clasificaremos las expresiones con operadores según el tipo de sus operandos:

numérico
cadena de caracteres
fecha
booleano o lógico

4.2.1. Expresiones con operandos de tipo numérico

4.2.1.1. Lista de operadores

Supongamos que nombre1, nombre2 y nombre3 son números. Los operadores que se pueden utilizar son los siguientes:

Operadores relacionales

`nombre1 > nombre2`	: número1 es mayor que número2
`nombre1 >= nombre2`	: número1 mayor o igual que número2
`nombre1 < nombre2`	: número1 menor que número2
`nombre1 <= nombre2`	: número1 menor o igual que número2
`nombre1 = nombre2`	: número1 es igual a número2
`nombre1 != nombre2`	: número1 diferente de número2
`nombre1 <> nombre2`	: lo mismo
`nombre1 BETWEEN nombre2 AND nombre3`	: número1 dentro del intervalo [nombre2,nombre3]
`nombre1 IN (liste de nombres)`	: número1 pertenece a la lista de números
`nombre1 IS NULL`	: número1 no tiene valor
`nombre1 IS NOT NULL`	: número1 tiene un valor

Operadores aritméticos

`nombre1 + nombre2`	: suma
`nombre1 - nombre2`	: resta
`nombre1 * nombre2`	: multiplicación
`nombre1 / nombre2`	: división

4.2.1.2. Operadores relacionales

Una expresión relacional expresa una relación que es verdadera o falsa. Por lo tanto, el resultado de dicha expresión es un valor booleano o lógico.

Ejemplos:

SQL> select titre,prix from biblio where prix between 100 and 150

SQL> select titre,prix from biblio where prix not between 100 and 150

SQL> select titre,prix from biblio where prix in (200,210)

4.2.1.3. Operadores aritméticos

La expresión aritmética nos resulta familiar. Expresa un cálculo que hay que realizar entre datos numéricos. Ya nos hemos encontrado con expresiones de este tipo: supongamos que el precio almacenado en los registros del archivo BIBLIO es un precio sin impuestos. Queremos visualizar cada título con su precio TTC para un tipo de TVA del 18,6 %:

    SELECT TITRE, PRIX*1.186 FROM BIBLIO

Si los precios deben aumentar un 3 %, el comando será

    UPDATE BIBLIO SET PRIX = PRIX*1.03

En una expresión pueden aparecer varios operadores aritméticos, además de funciones y paréntesis. Estos elementos se procesan según diferentes prioridades:

1	`fonctions`	<---- mayor prioridad
2	`()`
3	`* et /`
4	`+ et -`	<---- menor prioridad

Cuando hay dos operadores de la misma prioridad en la expresión, se evalúa primero el que se encuentra más a la izquierda en la expresión.

Ejemplos

La expresión PRIX*TAUX+TAXES se evaluará como (PRIX*TAUX)+TAXES. De hecho, se utilizará primero el operador de multiplicación. La expresión PRIX*TAUX/100 se evaluará como (PRIX*TAUX)/100.

4.2.2. Expresiones con operandos de tipo carácter

4.2.2.1. Lista de operadores

Los operadores que se pueden utilizar son los siguientes:

Sean cadena1, cadena2, cadena3, un patrón de cadenas de caracteres

`chaine1 > chaine2`	: cadena1 es mayor que cadena2
`chaine1 >= chaine2`	: cadena1 mayor o igual que cadena2
`chaine1 < chaine2`	: cadena1 menor que cadena2
`chaine1 <= chaine2`	: cadena1 menor o igual que cadena2
`chaine1 = chaine2`	: cadena1 igual a cadena2
`chaine1 != chaine2`	: cadena1 diferente de cadena2
`chaine1 <> chaine2`	: lo mismo
`chaine1 BETWEEN chaine2 AND chaine3`	: cadena1 dentro del intervalo [chaine2,chaine3]
`chaine1 IN liste de chaines`	: cadena1 pertenece a la lista de cadenas
`chaine1 IS NULL`	: cadena1 no tiene valor
`chaine1 IS NOT NULL`	: cadena1 tiene un valor
`chaine1 LIKE modèle`	: cadena1 coincide con el patrón

Operador de concatenación

cadena1 || cadena2 : chaine2 concatenada con chaine1

4.2.2.2. Operadores relacionales

¿Qué significa comparar cadenas con operadores como <, <=, etc.?

Cada carácter se codifica mediante un número entero. Cuando se comparan dos caracteres, lo que se compara son sus códigos enteros. La codificación adoptada respeta el orden natural del diccionario:

blanc<..< 0 < 1 < ...< 9 < ...< A < B <... < Z < ... < a < b < ... < z

Los números van antes que las letras, y las mayúsculas antes que las minúsculas.

4.2.2.3. Comparación de dos cadenas

Consideremos la relación «CHAT» < «CHIEN». ¿Es verdadera o falsa? Para realizar esta comparación, el SGBD compara las dos cadenas carácter por carácter basándose en sus códigos enteros. En cuanto se encuentran dos caracteres diferentes, la cadena a la que pertenece el más pequeño de los dos se considera más pequeña que la otra cadena. En nuestro ejemplo, «CHAT» se compara con «CHIEN». Se obtienen los siguientes resultados sucesivos:

    'CHAT'    'CHIEN'
----------------------------
    'C'    =    'C'
    'H'    =    'H'
    'A'    <    'I'

Tras esta última comparación, la cadena «CHAT» se declara más pequeña que la cadena «CHIEN». Por lo tanto, la relación «CHAT» < «CHIEN» es verdadera.

Comparemos ahora «CHAT» y «chat».

    'CHAT'    'chat'
--------------------------
    'C'    <    'c'

Tras esta comparación, la relación «CHAT» < «chat» se declara verdadera.

Ejemplos

SQL> select titre from biblio

SQL> select titre from biblio where upper(titre) between 'L' and 'M'

4.2.2.4. El operador LIKE

El operador LIKE se utiliza de la siguiente manera: chaîne LIKE modèle

La relación es verdadera si chaîne coincide con modèle. Este último es una cadena de caracteres que puede contener dos comodines:

`%`	que designa cualquier secuencia de caracteres
`_`	que designa 1 carácter cualquiera

Ejemplos

SQL> select titre from biblio

SQL> select titre from biblio where titre like 'M%';

SQL> select titre from biblio where titre like 'L_ %';

4.2.2.5. El operador de concatenación

SQL > select '[' || titre || ']' from biblio where upper(titre) LIKE 'L_ %'

4.2.3. Expresiones con operandos de tipo fecha

Sean fecha1, fecha2 y fecha3 unas fechas. Los operadores que se pueden utilizar son los siguientes:

Operadores relacionales

`date1 < date2`	es verdadera si fecha1 es anterior a fecha2
`date1 <= date2`	es verdadera si fecha1 es anterior o igual a fecha2
`date1 > date2`	es verdadera si fecha1 es posterior a fecha2
`date1 >= date2`	es verdadera si fecha1 es posterior o igual a fecha2
`date1 = date2`	es verdadera si fecha1 y fecha2 son iguales
`date1 <> date2`	es verdadera si fecha1 y fecha2 son diferentes.
`date1 != date2`	lo mismo
`date1 BETWEEN date2 AND date3`	es verdadera si fecha1 está entre fecha2 y fecha3
`date1 IN (liste de dates)`	es verdadera si fecha1 se encuentra en la lista de fechas
`date1 IS NULL`	es verdadera si fecha1 no tiene valor
`date1 IS NOT NULL`	es verdadera si fecha1 tiene un valor
`date1 LIKE modèle`	es verdadera si fecha1 coincide con el patrón
`ALL,ANY,EXISTS`

Operadores aritméticos

`date1 - date2`	: número de días que separan fecha1 de fecha2
`date1 - nombre`	: fecha2 tal que fecha1-fecha2=número
`date1 + nombre`	: fecha2 tal que fecha2-fecha1=número

Ejemplos

SQL> select achat from biblio

SQL>select achat from biblio
   where achat between '01.01.1988' and '31.12.1988';

SQL> select titre, achat from biblio
where cast(achat as char(10)) like '1988'

¿Antigüedad de los libros de la biblioteca?

SQL> select titre, cast('now' as date)-achat "age(jours)" from biblio

4.2.4. Expresiones con operandos booleanos

Recordemos que un valor booleano o lógico tiene dos valores posibles: verdadero o falso. El operando lógico suele ser el resultado de una expresión relacional.

Supongamos que booléen1 y booléen2 son dos valores booleanos. Hay tres operadores posibles, que son, por orden de prioridad:

`booléen1 AND booléen2`	es verdadera si booléen1 y booléen2 son ambos verdaderos.
`booléen1 OR booléen2`	es verdadera si booléen1 o booléen2 es verdadera.
`NOT booléen1`	tiene como valor el inverso del valor de booléen1.

Ejemplos

SQL> select titre,genre,prix from biblio order by prix desc

Se buscan libros con un precio comprendido entre:

SQL> select titre,genre,prix from biblio
     where prix>=130 and prix <=170

Búsqueda inversa:

SQL> select titre,genre,prix from biblio
     where prix<130 or prix >170
     order by prix asc

¡Cuidado con la prioridad de los operadores!

SQL> select titre,genre,prix from biblio
  where genre='ROMAN' and prix>200 or prix<100
  order by prix asc

Se utilizan paréntesis para controlar la prioridad de los operadores:

SQL> select titre,genre,prix from biblio
  where genre='ROMAN' and (prix>200 or prix<100)
  order by prix asc

4.3. Las funciones predefinidas de Firebird

Firebird dispone de funciones predefinidas. Estas no se pueden utilizar inmediatamente en los comandos SQL. Primero hay que ejecutar el script SQL <firebird>\UDF\ib_udf.sql donde <firebird> designa el directorio de instalación de SGBD Firebird:

Con IBExpert, procedemos de la siguiente manera:

utilizamos la herramienta [Script Excecutive] obtenida mediante option [Tools/ Script Executive]:

una vez que la herramienta está presente, cargamos el script <firebird>\UDF\ib_udf.sql:

A continuación, ejecutamos el script:

Una vez hecho esto, las funciones predefinidas de Firebird estarán disponibles para la base de datos a la que se estaba conectado cuando se ejecutó el script. Para comprobarlo, basta con ir al explorador de bases de datos y hacer clic en el nodo [UDF] de la base de datos en la que se han importado las funciones:

Arriba vemos las funciones disponibles en la base de datos. Para probarlas, resulta práctico tener una tabla de una sola fila. Llamémosla TEST:

y definámosla de la siguiente manera (clic derecho en Tablas / Nueva tabla):

Pongamos una sola fila en esta tabla:

Pasemos ahora al editor SQL (F12) y emitamos el siguiente comando SQL:

SQL> select cos(0) from test

que utiliza la función predefinida cos (coseno). El comando anterior evalúa cos(0) para cada línea de la tabla TEST, es decir, en realidad para una sola línea. Por lo tanto, simplemente se muestra el valor de cos(0):

Las funciones UDF (funciones definidas por el usuario) son funciones que el usuario puede crear, por lo que se pueden encontrar bibliotecas de funciones UDF en la web. Aquí solo describimos algunas de las disponibles con la version descargable de Firebird (2005). Las clasificamos según el tipo predominante de sus parámetros o según su función:

funciones con parámetros de tipo numérico
funciones con parámetros de tipo cadena de caracteres

4.3.1. Funciones con parámetros de tipo numérico

`abs(nombre)`	valor absoluto de un número abs(-15)=15
`ceil(nombre)`	el entero más pequeño mayor o igual que nombre ceil(15,7) = 16
`floor(nombre)`	el entero mayor menor o igual que nombre floor(14,3)=14
`div(nombre1,nombre2)`	cociente de la división entera (el cociente es entero) de nombre1 por nombre2 div(7,3)=2
`mod(nombre1,nombre2)`	resto de la división entera (el cociente es entero) de nombre1 por nombre2 mod(7,3)=1
`sign(nombre)`	-1 si nombre < 0 0 si nombre=0 +1 si nombre > 0 sign(-6)=-1
`sqrt(nombre)`	raíz cuadrada de nombre si nombre>=0 -1 si nombre<0 sqrt(16)=4

4.3.2. Funciones con parámetros de tipo cadena de caracteres

ascii_char(número)	carácter de código ASCII nombre ascii_char(65)='A'
lower(cadena)	convierte chaine a minúsculas lower('INFO')='info'
ltrim(cadena)	Left Trim: se eliminan los espacios que preceden al texto de chaine: ltrim(' gatito')='gatito'
replace(cadena1,cadena2,cadena3)	sustituye chaine2 por chaine3 en chaine1. replace('gato y perro','g','')='ato y **ero'
rtrim(cadena1,cadena2)	Right Trim: igual que ltrim, pero a la derecha rtrim('gato ')='gato'
substr(cadena, p, q)	subcadena de chaine que comienza en la posición p y termina en la posición q. substr('chaton',3,5)='ato'
ascii_val(carácter)	código ASCII de caractère ascii_val('A') = 65
strlen(cadena)	número de caracteres de chaine strlen('gatito') = 6