Algunas preguntas y respuestas sobre el SQL de Firebird

7 comentarios

1. ¿Cómo creo una nueva Base de Datos?

Usando el comando CREATE DATABASE

2. ¿Cómo creo una tabla?

Usando el comando CREATE TABLE

3. ¿Qué es un dominio?

Un tipo de datos que yo puedo definir, usando para ello un tipo de datos ya existente

4. ¿Cómo le agrego datos a una tabla?

Con el comando INSERT o con el comando UPDATE OR INSERT

5. ¿Cómo modifico los datos de una tabla?

Con el comando UPDATE o con el comando UPDATE OR INSERT

6. ¿Cómo borro filas de una tabla?

Con el comando DELETE

7. ¿Cómo puedo conocer el contenido de una tabla?

Con el comando SELECT

8. ¿Cómo puedo combinar datos de dos o más tablas?

Con las cláusulas JOIN y UNION

9. ¿Una tabla puede combinarse con sí misma?

10. ¿Cómo puedo mostrar los resultados de un SELECT ordenados?

Con la cláusula ORDER BY

11. ¿Qué debo hacer para buscar un dato dentro de una tabla?

Usar la cláusula WHERE o la cláusula HAVING

12. ¿Cómo puedo totalizar los valores de una columna de una tabla?

Usando la función agregada SUM().

 13. ¿Cómo hago para agrupar datos similares?

Usando la cláusula GROUP BY

14. ¿Qué es una clave primaria o Primary Key?

Un valor único que sirve para identificar a cada fila de una tabla, las Primary Key no pueden repetirse ni tener valores NULL

15. ¿Qué es un valor NULL?

Un valor desconocido. NULL no es lo mismo que una cadena vacía ni una fecha vacía ni un cero. NULL significa “desconocido” o sea que no se tiene la menor idea de cual es su valor.

16. ¿Qué es una clave foránea o Foreign Key?

Un valor que sirve para relacionar a dos tablas entre sí.

17. ¿Qué es una clave única o Unique Key?

Un valor que no puede estar repetido.

18. ¿Qué es una restricción check?

Una condición que debe cumplirse para que una fila pueda ser insertada o actualizada

19. ¿Qué es un stored procedure?

Una rutina que se ejecuta cuando el desarrollador de la Base de Datos quiere que se ejecute

20. ¿Qué es un trigger?

Una rutina que se ejecuta automáticamente cuando se cumple una condición

21. ¿Cómo puedo restringir el acceso al contenido de la Base de Datos?

Otorgándoles derechos, también llamados permisos o privilegios, a las personas autorizadas. Si muchas personas compartirán los mismos derechos entonces suele ser conveniente crear roles. Un rol es un grupo de usuarios que tienen exactamente los mismos derechos.

Artículos relacionados

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Relacionando dos tablas: la forma vieja y la forma nueva

2 comentarios

En Firebird, cuando queremos relacionar dos tablas (o conjuntos de resultados) entre sí tenemos dos posibilidades:

  1. Usando la forma vieja
  2. Usando la forma nueva

Caso 1. Usando la forma vieja

Esta sintaxis fue establecida en el año 1989. Las tablas se listan separadas por comas después de la cláusula FROM y la condición que las relaciona se pone en la cláusula WHERE. No hay una sintaxis especial que distinga cuales de las condiciones del WHERE son para filtrar filas y cuales son para relacionar tablas, se supone que mirando la sentencia el desarrollador sabrá cual es cual.

¿Problemas?

  • La cláusula WHERE puede volverse muy larga y muy complicada de leer porque se la usa para relacionar tablas y también para filtrar filas
  • Solamente se pueden relacionar tablas que tengan valores idénticos, si una de las tablas tiene NULL en una columna no se podrá hacer la relación. En otras palabras: solamente se puede hacer un INNER JOIN, no se pueden hacer OUTER JOIN.

Sintaxis:

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla1,
   MiTabla2
WHERE
   MiCondición

Caso 2. Usando la forma nueva

Esta sintaxis fue establecida en el año 1992. La tabla principal se coloca después de la cláusula FROM y la tabla secundaria se coloca después de la cláusula JOIN, la condición que las relaciona se coloca después de ON. Por lo tanto se puede distinguir fácilmente cual es la condición usada para relacionar a la tablas y cual es la condición usada para filtrar filas, no hay confusión posible.

¿Ventajas?

  • Es muy fácil saber cual es la condición usada para relacionar a las dos tablas
  • Es muy fácil saber cual es la condición usada para filtrar filas
  • La cláusula WHERE es más corta y por lo tanto más fácil de leer que cuando se usa la forma vieja
  • Se puede usar tanto INNER JOIN como OUTER JOIN

Sintaxis:

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición

Cuidado:

Tú puedes elegir cualquiera de las dos formas para relacionar tablas pero NUNCA debes mezclarlas. En un SELECT o usas la forma vieja o usas la forma nueva, no las mezcles, porque si las mezclas eso solamente te ocasionará problemas y ningún beneficio.

Recomendación:

La forma nueva es mejor, por eso se la inventó, por eso existe. Si ya tienes SELECTs escritos con la forma vieja y funcionan bien entonces déjalos como están, no los toques, pero para escribir todos tus nuevos SELECTs usa la forma nueva porque es la que verás cada vez más en todos los libros y documentos sobre SQL. Y es además la que siempre se usa en este blog.

Artículos relacionados:

Entendiendo a los JOIN

JOIN implícito y JOIN explícito

El índice del blog Firebird21

 

JOIN implícito y JOIN explícito

Deja un comentario

Cuando necesitamos relacionar dos tablas (o más propiamente: dos conjuntos de resultados) podemos hacerlo usando un JOIN implícito o un JOIN explícito.

El JOIN implícito fue establecido en SQL-89 y ocurre cuando a continuación de la cláusula FROM escribimos una coma. La sintaxis es la siguiente:

SELECT
   Columna1,
   Columna2,
   Columna3
FROM
   Tabla1,
   Tabla2
WHERE
   MiCondición

Ejemplo 1:

SELECT
   PRD_CODSUC,
   SUC_NOMBRE,
   PRD_CODIGO,
   PRD_NOMBRE
FROM
   PRODUCTOS,
   SUCURSALES
WHERE
   PRD_CODSUC = SUC_CODIGO

El JOIN explícito fue establecido en SQL-92 y ocurre cuando escribimos la cláusula JOIN. La sintaxis es la siguiente:

SELECT
   Columna1,
   Columna2,
   Columna3
FROM
   Tabla1
JOIN
   Tabla2
      ON MiCondición

Ejemplo 2:

SELECT
   PRD_CODSUC,
   SUC_NOMBRE,
   PRD_CODIGO,
   PRD_NOMBRE
FROM
   PRODUCTOS
JOIN
   SUCURSALES
      ON PRD_CODSUC = SUC_CODIGO

Puntos importantes a recordar:

  1. El JOIN implícito (después del FROM escribir alguna coma) fue establecido en SQL-89, ya es obsoleto y no se recomienda su uso.
  2. El JOIN explícito (después del FROM escribir JOIN) fue establecido en SQL-92 y es la sintaxis recomendada.
  3. Tanto si usas el JOIN implícito como el JOIN explícito podrás obtener los mismos resultados cuando en ninguna de las columnas relacionadas hay NULL
  4. Con el JOIN implícito no se puede hacer OUTER JOIN
  5. Con el JOIN explícito sí se puede hacer OUTER JOIN
  6. Es muy mala idea en un solo SELECT mezclar JOINs implícitos con JOIN explícitos. O usas uno o usas el otro, pero nunca uses ambos porque te causará problemas.

Recomendación:

Los SELECTs que ya tienes escritos y que funcionan bien déjalos como están, no los toques, pero en todos los nuevos SELECTs que escribas usa la notación explícita (es decir: escribe la cláusula JOIN) porque es la recomendada y en pocos años ya casi nadie usará la notación implícita. Ya es “del viejazo”.

Artículos relacionados:

 Entendiendo a los JOIN

Ejemplo Nº 001 – Usando INNER JOIN

Ejemplo Nº 002 – Usando INNER JOIN eficientemente

El índice del blog Firebird21

 

 

Agrupar y ordenar por nombres o por posiciones de las columnas

Deja un comentario

Como sabes, en SQL para ordenar el resultado de una consulta puedes escribir:

SELECT
   PRD_CODIGO,
   PRD_NOMBRE
FROM
   PRODUCTOS
ORDER BY
   PRD_NOMBRE

o escribir algo como:

SELECT
   PRD_CODIGO,
   PRD_NOMBRE
FROM
   PRODUCTOS
ORDER BY
   2

Y el resultado que obtendrás será exactamente el mismo. De la misma manera puedes agrupar por nombres de las columnas o por la posición que esas columnas ocupan.

¿Qué es preferible, usar el nombre de la columna o la posición de la columna?

Antes de que existiera SQL la forma normal era utilizar la posición de la columna, el hecho de poder utilizar el nombre de la columna en SQL resultó un gran avance en esa época.

Si usas la posición, el problema que puedes tener es que agregas una columna a tu SELECT y si te olvidas de actualizar también las cláusulas ORDER BY o GROUP BY, entonces obtendrás un resultado incorrecto.

SELECT
   PRD_CODIGO,
   PRD_UNIMED,
   PRD_NOMBRE
FROM
   PRODUCTOS
ORDER BY
   2

Aquí, se agregó la columna PRD_UNIMED (unidad de medida) pero no se actualizó la cláusula ORDER BY y por lo tanto el orden de las filas no será el correcto. En cambio, en este caso:

SELECT
   PRD_CODIGO,
   PRD_UNIMED,
   PRD_NOMBRE
FROM
   PRODUCTOS
ORDER BY
   PRD_NOMBRE

También se agregó la columna PRD_UNIMED pero sin embargo se sigue mostrando a las filas en el orden correcto porque en la cláusula ORDER BY se utiliza el nombre de la columna, no su posición.

Conclusión:

Usar el nombre de la columna o la posición de la columna tiene ventajas y desventajas.

La ventaja de usar el nombre de la columna es que aunque agregues columnas el resultado siempre estará ordenado o agrupado correctamente.

La ventaja de usar la posición de la columna es que escribes menos.

Los buenos profesionales siempre recomiendan usar el nombre de la columna porque se escribe más, sí, pero se entiende mejor.

Artículo relacionado:

El índice del blog Firebird21

 

Usando un SELECT en una columna computada

Deja un comentario

Las columnas computadas son columnas donde no se introducen datos, sino que su contenido es obtenido después de realizar algunas operaciones en otras columnas.

https://firebird21.wordpress.com/2013/06/23/columnas-computadas/

Por ejemplo, tenemos una columna para guardar los APELLIDOS de una persona, otra columna para guardar los NOMBRES de esa persona y creamos una columna computada para tener en ella los APELLIDOS y los NOMBRES. Algo como esto:

ALU_APENOM COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE)

Los usuarios introducen datos en las columnas ALU_APELLD (apellidos del alumno) y ALU_NOMBRE (nombres del alumno) pero no en la columna ALU_APENOM (apellidos y nombres del alumno). ¿Por qué no en esa columna? porque esa es una columna computada como podemos ver en la definición, ya que el nombre de la columna está seguido por COMPUTED BY. En el ejemplo anterior las columnas ALU_APELLD y ALU_NOMBRE pertenecen a la misma tabla que la columna ALU_APENOM. ¿Pero y si queremos columnas de otra tabla, es posible conseguir eso?

Sí, es posible.

Para ello debemos:

  1. Crear un stored procedure seleccionable
  2. Usar doble paréntesis, rodeando al SELECT que nos traerá los datos que queremos.

Ejemplo:

Supongamos que tenemos una tabla llamada EMPLEADOS y otra tabla llamada SALARIOS y deseamos que la tabla EMPLEADOS tenga una columna computada que contendrá el total de los salarios pagados a cada empleado.

CREATE PROCEDURE TOTAL_PAGADO(
   tnIdeEmp INTEGER)
RETURNS(
   ftnPagado INTEGER)
AS
BEGIN

   ftnPagado = (SELECT SUM(SAL_MONTOX) FROM SALARIOS WHERE SAL_IDEEMP = :tnIdeEmp);

   SUSPEND;

END;

Este stored procedure seleccionable (sabemos que es seleccionable porque tiene el comando SUSPEND en él) halla y devuelve la suma de todos los salarios pagados a cada empleado. Recibe como parámetro de entrada el identificador del empleado que nos interesa y devuelve la suma de los salarios que se le pagaron. Recuerda que el stored procedure debe ser seleccionable para que esta técnica funcione.

CREATE TABLE EMPLEADOS (
   EMP_IDENTI BIGINT NOT NULL,
   EMP_NOMBRE VARCHAR(20),
   EMP_APELLD VARCHAR(20),
   EMP_PAGADO COMPUTED BY ((SELECT FTNPAGADO FROM TOTAL_PAGADO(EMP_IDENTI))));

En la columna computada EMP_PAGADO el SELECT debe estar rodeado por dos paréntesis. Si pones un solo paréntesis el Firebird se enojará contigo y te dirá que cometiste un error. Debes usar dobles paréntesis.

 Luego, escribiendo un comando tal como:

SELECT
   *
FROM
   EMPLEADOS

Esto será lo que obtendremos:

COMPUTED1

Captura 1. Si haces clic en la imagen la verás más grande

Que es exactamente lo que queríamos obtener. Los datos de cada empleado y el total de los salarios que le hemos pagado hasta este momento.

Desde luego que podríamos haber obtenido estos mismos datos sin necesidad de usar columnas computadas, el hecho de usar columnas computadas tiene la ventaja de que nuestros SELECTs son más sencillos de escribir. Fíjate en el SELECT que nos mostró la Captura 1., si no las hubiéramos usado ese SELECT hubiera sido más complicado de escribir. Aquí simplemente escribimos: SELECT * FROM EMPLEADOS y ya obtuvimos lo que queríamos.

NOTA IMPORTANTE:

Esta técnica debe solamente ser empleada con versiones de Firebird 2.1 ó posteriores, ya que las versiones anteriores tenían un problema que causaba que los backups no pudieran ser restaurados. Así que si estás usando Firebird 1.0 ó Firebird 1.5 ó Firebird 2.0, ni se te ocurra utilizar esta técnica.

Y aunque estés usando Firebird 2.1 ó posterior siempre es recomendable que hagas un backup y lo restaures inmediatamente, para verificar de que tu backup puede ser restaurado exitosamente.

Conclusión:

Usar el comando SELECT dentro de una columna computada es una técnica de programación que nos facilitará escribir futuros SELECTs. Para que esta técnica funcione debemos hacer dos cosas:

  1. Escribir un stored procedure seleccionable
  2. Rodear con doble paréntesis a la columna COMPUTED BY

Artículos relacionados:

Columnas computadas

El índice del blog Firebird21

El foro del blog Firebird21

Usando alias para identificar a las tablas

Deja un comentario

En SQL tenemos la posibilidad de utilizar alias (apodos, sobrenombres, nombres alternativos) cuando nos referimos a una tabla. Esto tiene dos principales ventajas:

  1. Escribimos menos
  2. Facilita la lectura

Ejemplo 1:

SELECT
   C.CLI_CODSUC,
   S.SUC_NOMBRE AS CLI_NOMSUC,
   C.CLI_IDENTI,
   C.CLI_NOMBRE
FROM
   CLIENTES   C
JOIN
   SUCURSALES S
      ON C.CLI_CODSUC = S.SUC_CODIGO
ORDER BY
   C.CLI_NOMBRE

En el Ejemplo 1. el alias “C” identifica a la tabla CLIENTES y el alias “S” identifica a la tabla SUCURSALES. De esta manera jamás puede haber confusión, el Servidor del Firebird siempre sabrá si una columna pertenece a la tabla CLIENTES o si pertenece a la tabla SUCURSALES, porque el alias le dice a cual de esas tablas pertenece.

El alias siempre se escribe a continuación del nombre de la tabla que referencia. No se requiere que tenga una sola letra, puede tener varias letras e inclusive letras y números si así lo deseas.

El mismo ejemplo anterior, sin el uso de alias, quedaría así:

Ejemplo 2.

SELECT
   CLIENTES.CLI_CODSUC,
   SUCURSALES.SUC_NOMBRE AS CLI_NOMSUC,
   CLIENTES.CLI_IDENTI,
   CLIENTES.CLI_NOMBRE
FROM
   CLIENTES
JOIN
   SUCURSALES
      ON CLIENTES.CLI_CODSUC = SUCURSALES.SUC_CODIGO
ORDER BY
   CLIENTES.CLI_NOMBRE

Como puedes ver, tampoco hay confusión posible para saber a cual tabla pertenece cada columna, pero se escribe mucho más y es innecesario porque usando un alias ahorraríamos letras y también tiempo de escritura.

Ejemplo 3

SELECT
   C.CLI_CODSUC,
   C.CLI_IDENTI,
   C.CLI_NOMBRE
FROM
   CLIENTES   C
ORDER BY
   C.CLI_NOMBRE

 En el Ejemplo 3. hay un pequeño error, no afectará al resultado que obtengas pero demuestra que aún no has comprendido como funciona SQL. ¿Cuál es ese error? Que en el SELECT se especificó solamente una tabla y sin embargo se está usando un alias. Eso le hará trabajar al Servidor más de lo que debería, probablemente la diferencia en tiempo sea de unos milisegundos pero de todas maneras no es lo más eficiente. Y un buen profesional siempre busca la máxima eficiencia.

Entonces, la regla es: “Usar alias siempre que en el SELECT intervenga más de una tabla. No usar alias si interviene una sola tabla”.

Artículo relacionado:

El índice del blog Firebird21

 

Un error al usar la cláusula GROUP BY

Deja un comentario

Como seguramente sabes, cuando escribes un SELECT puedes agrupar los datos que obtendrás como resultado si utilizas la cláusula GROUP BY. Esto es muy útil en muchas ocasiones pero también a veces podrías encontrarte con un error y no entiendes el motivo. Aquí se muestra uno de esos casos.

Este artículo está basado en una consulta que respondieron Dimitry Sibiryakov y Dmitry Yemanov, desarrolladores de Firebird.

Supongamos que quieres usar COLLATE para indicarle al Firebird el orden en que debe aparecer el texto dentro de una columna CHAR o VARCHAR.

Un SELECT que sí funciona

SELECT
   CLI_NOMBRE COLLATE ES_ES_CI_AI
FROM
   CLIENTES
GROUP BY
   CLI_NOMBRE
ORDER BY
   CLI_NOMBRE

Otro SELECT que sí funciona

SELECT
   CLI_NOMBRE COLLATE ES_ES_CI_AI
FROM
   CLIENTES
GROUP BY
   CLI_NOMBRE COLLATE ES_ES_CI_AI
ORDER BY
   CLI_NOMBRE COLLATE ES_ES_CI_AI

Un SELECT que no funciona

SELECT
   CLI_NOMBRE
FROM
   CLIENTES
GROUP BY
   CLI_NOMBRE COLLATE ES_ES_CI_AI
ORDER BY
   CLI_NOMBRE COLLATE ES_ES_CI_AI

Al ejecutar el tercer SELECT el Firebird muestra el mensaje:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

¿Por qué, qué pasó? ¿Por qué los dos primeros SELECT funcionaron bien y el tercero no?

El primero funcionó bien porque está agrupado por el valor original.

El segundo funcionó bien porque se usa el mismo COLLATE en el resultado y en el grupo. Siempre que uses exactamente los mismos valores en el resultado y en el grupo, funcionará.

Sin embargo, el tercero es diferente porque en el resultado se desea obtener el valor original pero se lo agrupa por el COLLATE y eso es contradictorio, ya que el agrupamiento siempre debe hacerse por el valor original o por un valor que lo incluya. Y como en el tercer ejemplo no es así, por eso obtendrás el error que se mostró arriba.

El problema puede ser más fácil de entender con los siguientes ejemplos:

  1. SELECT SIN(X) … GROUP BY X
  2. SELECT SIN(X) … GROUP BY SIN(X)
  3. SELECT X … GROUP BY SIN(X)

No puedes pedir que te muestre la columna X y que la agrupe por el seno de X ya que algo así no tiene sentido. Piensa en COLLATE como si se tratara de una especie de CAST. Al hacer un COLLATE se sobreescriben las reglas de la comparación y por lo tanto “MiColumna” y “MiColumna COLLATE ES_ES_CI_AI” pueden producir diferentes agrupamientos. Por ejemplo:

COLLATE1

Captura 1. Si haces clic en la imagen la verás más grande

En la Captura 1 vemos los datos de los empleados, tal y como fueron cargados en la tabla.

SELECT
   EMP_NOMBRE
FROM
   EMPLEADOS
GROUP BY
   EMP_NOMBRE

COLLATE2

Captura 2. Si haces clic en la imagen la verás más grande

En la Captura 2 vemos el resultado de escribir un SELECT sin COLLATE. Nos muestra los datos agrupados como fueron cargados.

SELECT
   EMP_NOMBRE COLLATE ES_ES_CI_AI
FROM
   EMPLEADOS
GROUP BY
   EMP_NOMBRE COLLATE ES_ES_CI_AI

COLLATE3

Captura 3. Si haces clic en la imagen la verás más grande

En la Captura 3 vemos el resultado de usar la cláusula COLLATE. Como puedes ver se obtiene un resultado diferente y además el nombre de la columna cambió a CAST. Eso significa que internamente para el Firebird el COLLATE es una especie de CAST. Y también explica el motivo por el cual obtuvimos un error en nuestro tercer SELECT: porque la cláusula GROUP BY requiere que la lista de columnas en el SELECT y la lista de columnas en el GROUP BY sean exactamente las mismas expresiones; y cuando no es así muestra un mensaje de error.

Artículo relacionado:

El índice del blog Firebird21

Older Entries