PostgreSQL
AnteriorCapítulo 61. SQLSiguiente

El Lenguaje SQL

Como en el caso de los más modernos lenguajes relacionales, SQL está basado en el cálculo relacional de tuplas. Como resultado, toda consulta formulada utilizando el cálculo relacional de tuplas ( o su equivalente, el álgebra relacional) se pude formular también utilizando SQL. Hay, sin embargo, capacidades que van más allá del cálculo o del álgebra relaciona. Aquí tenemos una lista de algunas características proporcionadas por SQL que no forman parte del álgebra y del cálculo relacionales:

Select

El comando más usado en SQL es la instrucción SELECT, que se utiliza para recuperar datos. La sintaxis es: 

   SELECT [ALL|DISTINCT] 
          { * | expr_1 [AS c_alias_1] [, ... 
                [, expr_k [AS c_alias_k]]]}
   FROM table_name_1 [t_alias_1] 
        [, ... [, table_name_n [t_alias_n]]]
   [WHERE condition]
   [GROUP BY name_of_attr_i 
             [,... [, name_of_attr_j]] [HAVING condition]]
   [{UNION [ALL] | INTERSECT | EXCEPT} SELECT ...]
   [ORDER BY name_of_attr_i [ASC|DESC] 
             [, ... [, name_of_attr_j [ASC|DESC]]]];

Ilustraremos ahora la compleja sintaxis de la instrucción SELECT con varios ejemplos. Las tablas utilizadas para los ejemplos se definen en: La Base de Datos de Proveedores y Artículos.

Select sencillas

Aquí tenemos algunos ejemplos sencillos utilizando la instrucción SELECT:

Ejemplo 61-4. Query sencilla con cualificación

Para recuperar todas las tuplas de la tabla PART donde el atributo PRICE es mayor que 10, formularemos la siguiente consulta: 

   SELECT * FROM PART
     WHERE PRICE > 10;
y obtenemos la siguiente tabla: 
                   PNO |  PNAME      |  PRICE
                  -----+-------------+--------
                    3  |  Cerrojos   |   15
                    4  |  Levas      |   25

Utilizando "*" en la instrucción SELECT solicitaremos todos los atributos de la tabla. Si queremos recuperar sólo los atributos PNAME y PRICE de la tabla PART utilizaremos la instrucción: 

   SELECT PNAME, PRICE 
   FROM PART
   WHERE PRICE > 10;
En este caso el resultado es: 
                      PNAME      |  PRICE
                     ------------+--------
                      Cerrojos   |   15
                      Levas      |   25
Nótese que la SELECT SQL corresponde a la "proyección" en álgebra relaciona, no a la "selección" (vea Álgebra Relacional para más detalles).

Las cualificaciones en la clausula WHERE pueden también conectarse lógicamente utilizando las palabras claves OR, AND, y NOT: 

   SELECT PNAME, PRICE 
   FROM PART
   WHERE PNAME = 'Cerrojos' AND
         (PRICE = 0 OR PRICE < 15);
dará como resultado: 
                      PNAME      |  PRICE
                     ------------+--------
                      Cerrojos   |   15

Las operaciones aritméticas se pueden utilizar en la lista de objetivos y en la clausula WHERE. Por ejemplo, si queremos conocer cuanto cuestan si tomamos dos piezas de un artículo, podríamos utilizar la siguiente consulta: 

   SELECT PNAME, PRICE * 2 AS DOUBLE
   FROM PART
   WHERE PRICE * 2 < 50;
y obtenemos: 
                      PNAME      |  DOUBLE
                     ------------+---------
                      Tornillos  |    20
                      Tuercas    |    16
                      Cerrojos   |    30
Nótese que la palabra DOBLE tras la palabra clave AS es el nuevo título de la segunda columna. Esta técnica puede utilizarse para cada elemento de la lista objetivo para asignar un nuevo título a la columna resultante. Este nuevo título recibe el calificativo de "un alias". El alias no puede utilizarse en todo el resto de la consulta.

Joins (Cruces)

El siguiente ejemplo muestra como las joins (cruces) se realizan en SQL.

Para cruzar tres tablas SUPPLIER, PART y SELLS a través de sus atributos comunes, formularemos la siguiente instrucción: 

   SELECT S.SNAME, P.PNAME
   FROM SUPPLIER S, PART P, SELLS SE
   WHERE S.SNO = SE.SNO AND
         P.PNO = SE.PNO;
y obtendremos la siguiente tabla como resultado: 
                       SNAME | PNAME
                      -------+-------
                       Smith | Tornillos
                       Smith | Tuercas
                       Jones | Levas
                       Adams | Tornillos
                       Adams | Cerrojos
                       Blake | Tuercas
                       Blake | Cerrojos
                       Blake | Levas

En la clausula FROM hemos introducido un alias al nombre para cada relación porque hay atributos con nombre común (SNO y PNO) en las relaciones. Ahora podemos distinguir entre los atributos con nombre común simplificando la adicción de un prefijo al nombre del atributo con el nombre del alias seguido de un punto. La join se calcula de la misma forma, tal como se muestra en Una Inner Join (Una Join Externa) ################### WARNING ################# ¿Externa? ¿No será Interna? -- Isma. #############################################. Primero el producto cartesiano: SUPPLIER × PART × SELLS Ahora seleccionamos únicamente aquellas tuplas que satisfagan las condiciones dadas en la clausula WHERE (es decir, los atributos con nombre común deben ser iguales). Finalmente eliminamos las columnas repetidas (S.SNAME, P.PNAME).

Operadores Agregados

SQL proporciona operadores agregados (como son AVG, COUNT, SUM, MIN, MAX) que toman el nombre de un atributo como argumento. El valor del operador agregado se calcula sobre todos los valores de la columna especificada en la tabla completa. Si se especifican grupos en la consulta, el cálculo se hace sólo sobre los valores de cada grupo (vean la siguiente sección).

Ejemplo 61-5. Aggregates

Si queremos conocer el coste promedio de todos los artículos de la tabla PART, utilizaremos la siguiente consulta: 

   SELECT AVG(PRICE) AS AVG_PRICE
   FROM PART;

El resultado es: 

                         AVG_PRICE
                        -----------
                           14.5

Si queremos conocer cuantos artículos se recogen en la tabla PART, utilizaremos la instrucción: 

   SELECT COUNT(PNO)
   FROM PART;
y obtendremos: 
                           COUNT
                          -------
                             4

Agregación por Grupos

SQL nos permite particionar las tuplas de una tabla en grupos. En estas condiciones, los operadores agregados descritos antes pueden aplicarse a los grupos (es decir, el valor del operador agregado no se calculan sobre todos los valores de la columna especificada, sino sobre todos los valores de un grupo. El operador agregado se calcula individualmente para cada grupo).

El particionamiento de las tuplas en grupos se hace utilizando las palabras clave GROUP BY seguidas de una lista de atributos que definen los grupos. Si tenemos GROUP BY A1, ⃛, Ak habremos particionado la relación en grupos, de tal modo que dos tuplas son del mismo grupo si y sólo si tienen el mismo valor en sus atributos A1, ⃛, Ak.

Ejemplo 61-6. Agregados

Si queremos conocer cuántos artículos han sido vendidos por cada proveedor formularemos la consulta: 

   SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
   FROM SUPPLIER S, SELLS SE
   WHERE S.SNO = SE.SNO
   GROUP BY S.SNO, S.SNAME;
y obtendremos: 
                     SNO | SNAME | COUNT
                    -----+-------+-------
                      1  | Smith |   2
                      2  | Jones |   1
                      3  | Adams |   2
                      4  | Blake |   3

Demos ahora una mirada a lo que está ocurriendo aquí. Primero, la join de las tablas SUPPLIER y SELLS: 

                  S.SNO | S.SNAME | SE.PNO
                 -------+---------+--------
                    1   |  Smith  |   1
                    1   |  Smith  |   2
                    2   |  Jones  |   4
                    3   |  Adams  |   1
                    3   |  Adams  |   3
                    4   |  Blake  |   2
                    4   |  Blake  |   3
                    4   |  Blake  |   4

Ahora particionamos las tuplas en grupos reuniendo todas las tuplas que tiene el mismo atributo en S.SNO y S.SNAME: 

                  S.SNO | S.SNAME | SE.PNO
                 -------+---------+--------
                    1   |  Smith  |   1
                                  |   2
                 --------------------------
                    2   |  Jones  |   4
                 --------------------------
                    3   |  Adams  |   1
                                  |   3
                 --------------------------
                    4   |  Blake  |   2
                                  |   3
                                  |   4

En nuestro ejemplo, obtenemos cuatro grupos y ahora podemos aplicar el operador agregado COUNT para cada grupo, obteniendo el resultado total de la consulta dada anteriormente.

Nótese que para el resultado de una consulta utilizando GROUP BY y operadores agregados para dar sentido a los atributos agrupados, debemos primero obtener la lista objetivo. Los demás atributos que no aparecen en la clausula GROUP BY se seleccionarán utilizando una función agregada. Por otro lado, usted no puede utilizar funciones agregadas en atributos que aparecen en la clausula GROUP BY.

Having

La clausula HAVING trabaja muy similarmente a la clausula WHERE, y se utiliza para considerar sólo aquellos grupos que satisfagan la cualificación dada en la clausula HAVING. Las expresiones permitidas en la clausula HAVING deben involucrar funcionen agregadas. Cada expresión que utilice sólo atributos planos deberá recogerse en la clausula WHERE. Por otro lado, toda expresión que involucre funciones agregadas debe aparecer en la clausula HAVING.

Ejemplo 61-7. Having

Si queremos sólo los proveedores que venden más de un artículo, utilizaremos la consulta: 

   SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
   FROM SUPPLIER S, SELLS SE
   WHERE S.SNO = SE.SNO
   GROUP BY S.SNO, S.SNAME
   HAVING COUNT(SE.PNO) > 1;
y obtendremos: 
                     SNO | SNAME | COUNT
                    -----+-------+-------
                      1  | Smith |   2
                      3  | Adams |   2
                      4  | Blake |   3

Subconsultas

En las clausulas WHERE y HAVING se permite el uso de subconsultas (subselects) en cualquier lugar donde se espere un valor. En este caso, el valor debe derivar de la evaluación previa de la subconsulta. El uso de subconsultas amplía el poder expresivo de SQL.

Ejemplo 61-8. Subselect

Si queremos conocer los artículos que tienen mayor precio que el artículo llamado 'Tornillos', utilizaremos la consulta: 

   SELECT *
   FROM PART
   WHERE PRICE > (SELECT PRICE FROM PART
                  WHERE PNAME='Tornillos');

El resultado será: 

                   PNO |  PNAME      |  PRICE
                  -----+-------------+--------
                    3  |  Cerrojos   |   15
                    4  |  Levas      |   25

Cuando revisamos la consulta anterior, podemos ver la palabra clave SELECT dos veces. La primera al principio de la consulta - a la que nos referiremos como la SELECT externa - y la segunda en la clausula WHERE, donde empieza una consulta anidada - nos referiremos a ella como la SELECT interna. Para cada tupla de la SELECT externa, la SELECT interna deberá ser evaluada. Tras cada evaluación, conoceremos el precio de la tupla llamada 'Tornillos', y podremos chequear si el precio de la tupla actual es mayor.

Si queremos conocer todos los proveedores que no venden ningún artículo (por ejemplo, para poderlos eliminar de la base de datos), utilizaremos: 

   SELECT * 
   FROM SUPPLIER S
   WHERE NOT EXISTS
             (SELECT * FROM SELLS SE
              WHERE SE.SNO = S.SNO);

En nuestro ejemplo, obtendremos un resultado vacío, porque cada proveedor vende al menos un artículo. Nótese que utilizamos S.SNO de la SELECT externa en la clausula WHERE de la SELECT interna. Como hemos descrito antes, la subconsulta se evalúa para cada tupla de la consulta externa, es decir, el valor de S.SNO se toma siempre de la tupla actual de la SELECT externa.

Unión, Intersección, Excepción

Estas operaciones calculan la unión, la intersección y la diferencia de la teoría de conjuntos de las tuplas derivadas de dos subconsultas.

Ejemplo 61-9. Union, Intersect, Except

La siguiente consulta es un ejemplo de UNION: 

   SELECT S.SNO, S.SNAME, S.CITY
   FROM SUPPLIER S
   WHERE S.SNAME = 'Jones'
   UNION
   SELECT S.SNO, S.SNAME, S.CITY
   FROM SUPPLIER S
   WHERE S.SNAME = 'Adams';
Dará el resultado: 
                     SNO | SNAME |  CITY
                    -----+-------+--------
                      2  | Jones | Paris
                      3  | Adams | Vienna

Aquí tenemos un ejemplo para INTERSECT: 

   SELECT S.SNO, S.SNAME, S.CITY
   FROM SUPPLIER S
   WHERE S.SNO > 1
   INTERSECT
   SELECT S.SNO, S.SNAME, S.CITY
   FROM SUPPLIER S
   WHERE S.SNO > 2;
que dará como resultado: 
                     SNO | SNAME |  CITY
                    -----+-------+--------
                      2  | Jones | Paris
         La única tupla devuelta por ambas partes de la consulta es la única que tiene $SNO=2$.

Finalmente, un ejemplo de EXCEPT: 

   SELECT S.SNO, S.SNAME, S.CITY
   FROM SUPPLIER S
   WHERE S.SNO > 1
   EXCEPT
   SELECT S.SNO, S.SNAME, S.CITY
   FROM SUPPLIER S
   WHERE S.SNO > 3;
que dará como resultado: 
                     SNO | SNAME |  CITY
                    -----+-------+--------
                      2  | Jones | Paris
                      3  | Adams | Vienna

Definición de Datos

Hay incluidos en el lenguaje SQL un conjunto de comandos utilizados para definición de datos.

Create Table

El comando fundamental para definir datos es el que crea una nueva relación (una nueva tabla). La sintaxis del comando CREATE TABLE es: 

   CREATE TABLE table_name
                (name_of_attr_1 type_of_attr_1
                 [, name_of_attr_2 type_of_attr_2 
                 [, ...]]);

Ejemplo 61-10. Creación de una tabla

Para crear las tablas definidas en La Base de Datos de Proveedores y Artículos se utilizaron las siguientes instrucciones de SQL: 

   CREATE TABLE SUPPLIER
                (SNO   INTEGER,
                 SNAME VARCHAR(20),
                 CITY  VARCHAR(20));
	
   CREATE TABLE PART
                (PNO   INTEGER,
                 PNAME VARCHAR(20),
                 PRICE DECIMAL(4 , 2));
	
   CREATE TABLE SELLS
                (SNO INTEGER,
                 PNO INTEGER);

Tipos de Datos en SQL

A continuación sigue una lista de algunos tipos de datos soportados por SQL:

  • INTEGER: entero binario con signo de palabra completa (31 bits de precisión).

  • SMALLINT: entero binario con signo de media palabra (15 bits de precisión).

  • DECIMAL (p[,q]): número decimal con signo de p dígitos de precisión, asumiendo q a la derecha para el punto decimal. (15 ≥ p ≥ qq ≥ 0). Si q se omite, se asume que vale 0.

  • FLOAT: numérico con signo de doble palabra y coma flotante.

  • CHAR(n): cadena de caracteres de longitud fija, de longitud n.

  • VARCHAR(n): cadena de caracteres de longitud variable, de longitud máxima n.

Create Index

Se utilizan los índices para acelerar el acceso a una relación. Si una relación R tiene un índice en el atributo A podremos recuperar todas la tuplas t que tienen t(A) = a en un tiempo aproximadamente proporcional al número de tales tuplas t más que en un tiempo proporcional al tamaño de R.

Para crear un índice en SQL se utiliza el comando CREATE INDEX. La sintaxis es: 

   CREATE INDEX index_name 
   ON table_name ( name_of_attribute );

Ejemplo 61-11. Create Index

Para crear un índice llamado I sobre el atributo SNAME de la relación SUPPLIER, utilizaremos la siguiente instrucción: 

   CREATE INDEX I
   ON SUPPLIER (SNAME);

El índice creado se mantiene automáticamente. es decir, cada vez que una nueva tupla se inserte en la relación SUPPLIER, se adaptará el índice I. Nótese que el único cambio que un usuario puede percibir cuando se crea un índice es un incremento en la velocidad.

Create View

Se puede ver una vista como una tabla virtual, es decir, una tabla que no existe físicamente en la base de datos, pero aparece al usuario como si existiese. Por contra, cuando hablamos de una tabla base, hay realmente un equivalente almacenado para cada fila en la tabla en algún sitio del almacenamiento físico.

Las vistas no tienen datos almacenados propios, distinguibles y físicamente almacenados. En su lugar, el sistema almacena la definición de la vista (es decir, las reglas para acceder a las tablas base físicamente almacenadas para materializar la vista) en algún lugar de los catálogos del sistema (vea System Catalogs). Para una discusión de las diferentes técnicas para implementar vistas, refiérase a SIM98.

En SQL se utiliza el comando CREATE VIEW para definir una vista. La sintaxis es: 

   CREATE VIEW view_name
   AS select_stmt
donde select_stmt es una instrucción select válida, como se definió en Select. Nótese que select_stmt no se ejecuta cuando se crea la vista. Simplemente es almacenada en los catalogo del sistema y se ejecuta cada vez que se realiza una consulta contra la vista.

Sea la siguiente definición de una vista (utilizamos de nuevo las tablas de La Base de Datos de Proveedores y Artículos ): 

   CREATE VIEW London_Suppliers
      AS SELECT S.SNAME, P.PNAME
         FROM SUPPLIER S, PART P, SELLS SE
         WHERE S.SNO = SE.SNO AND
               P.PNO = SE.PNO AND
               S.CITY = 'London';

Ahora podemos utilizar esta relación virtual London_Suppliers como si se tratase de otra tabla base: 

   SELECT *
   FROM London_Suppliers
   WHERE P.PNAME = 'Tornillos';
Lo cual nos devolverá la siguiente tabla: 
                       SNAME | PNAME
                      -------+----------
                       Smith | Tornillos

Para calcular este resultado, el sistema de base de datos ha realizado previamente un acceso oculto a las tablas de la base SUPPLIER, SELLS y PART. Hace esto ejecutando la consulta dada en la definición de la vista contra aquellas tablas base. Tras eso, las cualificaciones adicionales (dadas en la consulta contra la vista) se podrán aplicar para obtener la tabla resultante.

Drop Table, Drop Index, Drop View

Se utiliza el comando DROP TABLE para eliminar una tabla (incluyendo todas las tuplas almacenadas en ella): 

   DROP TABLE table_name;

Para eliminar la tabla SUPPLIER, utilizaremos la instrucción: 

   DROP TABLE SUPPLIER;

Se utiliza el comando DROP INDEX para eliminar un índice: 

   DROP INDEX index_name;

Finalmente, eliminaremos una vista dada utilizando el comando DROP VIEW: 

   DROP VIEW view_name;

Manipulación de Datos

Insert Into

Una vez que se crea una tabla (vea Create Table), puede ser llenada con tuplas utilizando el comando INSERT INTO. La sintaxis es: 

   INSERT INTO table_name (name_of_attr_1 
                             [, name_of_attr_2 [,...]])
   VALUES (val_attr_1 
           [, val_attr_2 [, ...]]);

Para insertar la primera tupla en la relación SUPPLIER (de La Base de Datos de Proveedores y Artículos) utilizamos la siguiente instrucción: 

   INSERT INTO SUPPLIER (SNO, SNAME, CITY)
   VALUES (1, 'Smith', 'London');

Para insertar la primera tupla en la relación SELLS, utilizamos: 

   INSERT INTO SELLS (SNO, PNO)
   VALUES (1, 1);

Update

Para cambiar uno o más valores de atributos de tuplas en una relación, se utiliza el comando UPDATE. La sintaxis es: 

   UPDATE table_name
   SET name_of_attr_1 = value_1 
       [, ... [, name_of_attr_k = value_k]]
   WHERE condition;

Para cambiar el valor del atributo PRICE en el artículo 'Tornillos' de la relación PART, utilizamos: 

   UPDATE PART
   SET PRICE = 15
   WHERE PNAME = 'Tornillos';

El nuevo valor del atributo PRICE de la tupla cuyo nombre es 'Tornillos' es ahora 15.

Delete

Para borrar una tupla de una tabla particular, utilizamos el comando DELETE FROM. La sintaxis es: 

   DELETE FROM table_name
   WHERE condition;

Para borrar el proveedor llamado 'Smith' de la tabla SUPPLIER, utilizamos la siguiente instrucción: 

   DELETE FROM SUPPLIER
   WHERE SNAME = 'Smith';

System Catalogs

En todo sistema de base de datos SQL se emplean catálogos de sistema para mantener el control de qué tablas, vistas, índices, etc están definidas en la base de datos. Estos catálogos del sistema se pueden investigar como si de cualquier otra relación normal se tratase. Por ejemplo, hay un catálogo utilizado para la definición de vistas. Este catálogo almacena la consulta de la definición de la vista. Siempre que se hace una consulta contra la vista, el sistema toma primero la consulta de definición de la vista del catálogo y materializa la vista antes de proceder con la consulta del usuario (vea SIM98 para obtener una descripción más detallada). Diríjase aDATE para obtener más información sobre los catálogos del sistema.

SQL Embebido

En esta sección revisaremos como se puede embeber SQL en un lenguaje de host (p.e. C). Hay dos razones principales por las que podríamos querer utilizar SQLdesde un lenguaje de host:

Un programa que utiliza SQL embebido en un lenguaje de host consiste en instrucciones del lenguaje del host e instrucciones de SQL embebido (ESQL). Cada instrucción de ESQL empieza con las palabras claves EXEC SQL. Las instrucciones ESQL se transforman en instrucciones del lenguaje del host mediante un precompilador (que habitualmente inserta llamadas a rutinas de librerías que ejecutan los variados comandos de SQL).

Cuando vemos los ejemplos de Select observamos que el resultado de las consultas es algo muy próximo a un conjunto de tuplas. La mayoría de los lenguajes de host no están diseñados para operar con conjuntos, de modo que necesitamos un mecanismo para acceder a cada tupla única del conjunto de tuplas devueltas por una instrucción SELECT. Este mecanismo puede ser proporcionado declarando un cursor. Tras ello, podemos utilizar el comando FETCH para recuperar una tupla y apuntar el cursor hacia la siguiente tupla.

Para una discusión más detallada sobre el SQL embebido, diríjase a [Date and Darwen, 1997], [Date, 1994], o [Ullman, 1988].

AnteriorInicioSiguiente
Operaciones en el Modelo de Datos RelacionalSubirArquitectura