SQL

1

SQL

1. Introducción

SQL (Standar Query Lenguaje) es un lenguaje estandarizado de base de datos. Actualmente, es el lenguaje de

consulta de base de datos por excelencia, aunque SQL es un lenguaje muy completo que consta de varias partes:

Lenguaje de definición de datos (LDD): Proporciona órdenes para definir esquemas de relación, eliminar

relaciones, modificar la estructura de una relación existente, crear o eliminar índices y vistas…

Lenguaje de manipulación de datos (LMD): incluye un leguaje de consultas que permite rescatar datos de las

relaciones. También incluye órdenes para insertar, suprimir y modificar tuplas.

Manipulación de datos

DM

L SELECT Recupera datos de la base de datos

INSERT Inserta nuevas filas en una tabla de la base de datos

DELETE Elimina filas de las tablas de la base de datos

UPDATE Modifica datos existentes en la base de datos

Definición de datos

DD

L

CREATE TABLE Añade una nueva tabla a la base de datos

DROP TABLE Suprime una tabla de la base de datos

ALTER TABLE Modifica la estructura de una tabla existente

CREATE VIEW Añade una nueva vista a la base de datos

DROP VIEW Elimina una vista de la base de datos

CREATE INDEX Construye un índice para una columna

DROP INDEX Suprime el índice para una columna

2. Manipulación de datos

Consultas SELECT

Para recuperar información, o lo que es lo mismo, para realizar consultas a la base de datos, utilizaremos la

sentencia SELECT. De una consulta se podrán obtener: cualquier unidad de datos, todos los datos, cualquier

subconjunto de datos, cualquier conjunto de subconjuntos de datos. Lo vemos a continuación.

Estructura básica

La estructura básica de una consulta en SQL consta de tres cláusulas: SELECT, FROM y WHER.

La cláusula SELECT corresponde a la operación proyección del álgebra relacional. Se usa para listar los atributos

que se desean en el resultado de una consulta.

La cláusula FROM corresponde a la operación de producto cartesiano del álgebra relacional. Lista las relaciones

que se van a examinar en la evaluación de la expresión.

La cláusula WHERE corresponde al predicado de selección del álgebra relacional. Consta de un predicado que

incluye atributos de las relaciones que aparecen en la cláusula FROM.

Una consulta típica en SQL tiene la forma ...

SELECT a1, a2, …, an FROM r1, r2, …, rm WHERE P

... que es equivalente a la expresión del álgebra relacional:

a1, a2, …, an ( P (r1 x r2 x … x rm))

SQL

2

La lista de atributos puede sustituirse por un * (asterisco) para seleccionar todos los atributos de todas las

relaciones que aparecen en la cláusula FROM. SQL forma el producto cartesiano de las relaciones nombradas en

FROM, realiza una selección del álgebra relacional usando el predicado de la cláusula WHERE y después proyecta el

resultado a los atributos de la cláusula SELECT. El resultado de una consulta en SQL es una relación.

Vamos a ver unos primeros ejemplos de consultas con SELECT. Utilizaremos la base de datos sobre proveedores,

piezas y envíos vista en temas anteriores y cuyo esquema recordamos a continuación:

S (sn, snombre, estado, ciudad)

P (pn, pnombre, color, peso, ciudad)

SP(sn, pn, cant)

Consulta 1. Muestra los nombres de los proveedores. Consulta 2. Muestra todos los datos sobre piezas.

SELECT snombre SELECT * FROM S; FROM P;

Consulta 3. Muestra el nombre y color de las piezas de Consulta 4. Muestra el nombre y ciudad donde se almacenan peso superior a 15. las piezas de color azul.

SELECT pnombre, color SELECT pnombre, ciudad FROM P FROM P WHERE peso > 15; WHERE color = ‘azul’;

Como hemos comentado anteriormente, el resultado de una consulta es una nueva tabla. Los resultados de las

consultas anteriores se muestran a continuación:

SQL no tiene una representación directa del producto natural, sin embargo, puesto que el producto natural se

define en función de un producto cartesiano, una selección y una proyección, es relativamente sencillo escribir una

expresión en SQL para el producto natural.

Consulta 5. Nombre y ciudad de los proveedores que envían la pieza P1.

SELECT snombre, ciudad FROM S, SP WHERE S.sn=SP.sn AND pn = ‘P1’;

Consulta 6. Nombre y color de las piezas enviadas por el proveedor S2.

SELECT pnombre, color FROM P, SP WHERE P.pn=SP.pn AND sn = ‘S2’;

Consulta 1

snombre

Salazar

Jaimes

Bernal

Corona

Aldana

Consulta 2

pn pnombre color peso ciudad

P1 tuerca verde 12 París

P2 perno rojo 17 Londres

P3 birlo azul 17 Roma

P4 birlo rojo 14 Londres

P5 leva azul 12 París

P6 engrane rojo 19 París

Consulta 3

pnombre color

perno rojo

birlo azul

engrane rojo

Consulta 4

pnombre ciudad

birlo Roma

leva París

Consulta 5

snombre ciudad

Salazar Londres

Jaimes París

Consulta 6

pnombre color

tuerca verde

perno rojo

SQL

3

Obsérvese que SQL usa la notación nombre_relación.nombre_atributo,

como el álgebra relacional, para evitar ambigüedad.

Los datos, como ‘azul’, ‘P1’ ó ‘S2’ van entre comillas simples.

Predicados y conectores

Los operadores de comparación, aritméticos y lógicos, se resumen en las siguientes tablas:

Operadores de comparación Operadores aritméticos Operadores lógicos Operador Función Operador Función Operador Función

= Igual a + Suma AND

Devuelve TRUE cuando las dos condiciones son verdaderas > Mayor que – Resta

< Menor que * Multiplicación OR

Devuelve TRUE cuando una de las dos condiciones es verdadera >= Mayor o igual que / División

<= Menor o igual que NOT

Devuelve TRUE si la condición es falsa <> Distinto de

SQL incluye un operador de comparación BETWEEN para simplificar cláusulas WHERE que especifican que un

valor sea menor o igual que un valor dado y mayor o igual que otro. Análogamente, podemos usar el operador de

comparación NOT BETWEEN.

Consulta 7. Nombre y color de las piezas con peso entre 15 y 20 (inclusive).

SELECT pnombre, color SELECT pnombre, color FROM P FROM P

WHERE peso >= 15 AND peso <= 20; WHERE peso BETWEEN 15 AND 20;

Consulta 8. Nombre y color de las piezas cuyo peso no esté entre 15 y 20.

SELECT pnombre, color SELECT pnombre, color FROM P FROM P WHERE peso < 15 OR peso > 20; WHERE peso NOT BETWEEN 15 AND 20;

SQL también incluye un operador de selección para comparaciones de cadenas de caracteres. Los modelos se

describen usando dos caracteres especiales:

* (asterisco), igual a cualquier subcadena (en ORACLE, en lugar de * se usa % ).

? (interrogación), igual a cualquier carácter (en ORACLE, en lugar de ? se usa _ ).

Los modelos se expresan en SQL usando el operador de comparación LIKE para las igualdades y NOT LIKE para las

desigualdades.

Consulta 9. Nombres de piezas que terminen en ‘a’. Consulta 10. Nombre de proveedores que no empiecen por ‘b’.

SELECT pnombre SELECT snombre FROM P FROM S WHERE pnombre LIKE ‘*a’; WHERE snombre NOT LIKE ‘b*’;

Consulta 7

pnombre color

perno rojo

birlo azul

engrane rojo

Consulta 8

pnombre color

tuerca verde

birlo rojo

leva azul

SQL

4

Tuplas duplicadas

Los lenguajes de consulta formales se basan en la noción matemática de relación como un conjunto. Por ello

nunca aparecen tuplas duplicadas en las relaciones. En la práctica, la eliminación de duplicados lleva bastante tiempo,

por lo que SQL permite duplicados en las relaciones. Así pues, en las consultas se listaran todas las tuplas inclusive las

repetidas. En aquellos casos en los que queremos forzar la eliminación de duplicados, insertamos la palabra clave

DISTINCT después de SELECT. Con ALL se podrá especificar explícitamente que no se eliminen duplicados.

Consulta 11. Ciudades donde se almacenan piezas.

Con duplicados: SELECT ciudad FROM P;

Sin duplicados: SELECT DISTINCT ciudad FROM P;

Alias

SQL nos ofrece la posibilidad de renombrar tanto los atributos como las relaciones, por medio del uso de alias o

seudónimos (cláusula AS). El uso de alias en los atributos nos permite cambiar el nombre de los atributos de la

respuesta a la consulta. El uso de alias en las relaciones nos permite referirnos a éstas de forma más breve y clara,

anteponiendo el alias y un punto al nombre del atributo. También son muy útiles para comparar dos tuplas de la

misma relación (en tales casos el álgebra relacional usaría la operación renombrar).

Consulta 12. Código y nombre de los proveedores de Londres.

SELECT sn AS "Proveedor", snombre AS "Nombre" FROM S WHERE ciudad = 'Londres';

Consulta 13. Nombre de los proveedores cuya ciudad sea la misma de Salazar.

SELECT S2.snombre FROM S S1, S S2 WHERE S1.snombre='Salazar' AND S1.ciudad=S2.ciudad;

Ordenación de la presentación de tuplas

SQL ofrece al usuario cierto control sobre el orden en que se van a presentar las tuplas en una relación. La

cláusula ORDER BY hace que las tuplas del resultado salgan en un orden determinado. Por omisión, SQL muestra los

elementos en orden ascendente. Para especificar el tipo de ordenación, podemos utilizar DESC para orden

descendente y ASC para el ascendente. Además el orden puede realizarse sobre múltiples atributos, en caso de

igualdad entre valores del primer atributo, el orden continuará según los valores del segundo.

Consulta 14. Nombre de las piezas ordenadas por ciudad y por peso (descendente).

SELECT pnombre, ciudad, peso FROM P ORDER BY ciudad ASC, peso DESC;

Con duplicados

ciudad

París

Londres

Roma

Londres

París

París

Sin duplicados

ciudad

Londres

París

Roma

Consulta 12

Proveedor Nombre

S1 Salazar

S4 Corona

Consulta 13

snombre

Corona

Salazar

pnombre ciudad peso

perno Londres 17

birlo Londres 14

engrane París 19

leva París 12

tuerca París 12

birlo Roma 17

SQL

5

Valores nulos

Se dice que una columna de una fila es NULL si está completamente vacía. Usaremos la expresión IS NULL para

comprobar si el valor de una columna es nulo, y la expresión IS NOT NULL para comprobar que el valor no es nulo.

Cuando comparamos con valores nulos no podemos utilizar los operadores de igualdad, mayor o menor.

Consulta 15. Proveedores cuyo estado es nulo. Consulta 16. Proveedores cuyo estado no es nulo.

SELECT * SELECT * FROM S FROM S WHERE estado IS NULL; WHERE estado IS NOT NULL;

Reunión de Relaciones

En SQL tenemos la posibilidad de vincular relaciones combinando aquellos campos de las tablas que sean del

mismo dominio y que contengan el mismo tipo de datos. No es necesario que estos campos tengan el mismo nombre.

Esta reunión la haremos con la cláusula INNER JOIN.

Consulta 17. Ciudades en las que viven proveedores y también se almacenan piezas.

Forma 1: Forma 2:

SELECT DISTINCT S.ciudad SELECT DISTINCT S.ciudad FROM S INNER JOIN P ON S.ciudad=P.ciudad; FROM S, P

WHERE S.ciudad=P.ciudad;

Operaciones de conjuntos

SQL incluye las operaciones UNION, INTERSECT y MINUS que operan sobre relaciones y corresponden a las

operaciones del álgebra relacional

El operador UNION combina los resultados de dos consultas. Por omisión, la operación UNION elimina las

tuplas duplicadas. Para retener duplicados se debe escribir UNION ALL.

El operador INTERSECT devuelve las filas que son iguales en ambas consultas. Todas las filas duplicadas

serán eliminadas antes de la generación del resultado final.

El operador MINUS devuelve aquellas filas que están en la primera consulta y no en la segunda. Como en las

operaciones anteriores, se eliminarán del resultado final las filas duplicadas.

Consulta 18. Ciudades donde viven proveedores o se almacenan piezas.

Consulta 19. Ciudades donde viven proveedores y se almacenan piezas.

Consulta 20. Ciudades donde viven proveedores y no se almacenan piezas.

18. SELECT ciudad FROM S 19. SELECT ciudad FROM S 20. SELECT ciudad FROM S UNION INTERSECT MINUS SELECT ciudad FROM P; SELECT ciudad FROM P; SELECT ciudad FROM P;

En OpenOffice Base, estas operaciones las realizaremos usando los operadores IN y NOT IN.

Consulta 17

ciudad

Londres

París

SQL

6

Pertenencia a un conjunto

El conector IN prueba si se es miembro de un conjunto, donde el conjunto es una colección de valores

producidos por una cláusula SELECT. El conector NOT IN prueba la no pertenencia al conjunto.

Consulta 21. Nombre de las piezas almacenadas en Londres o en Roma.

Forma 1: Forma 2:

SELECT DISTINCT pnombre SELECT DISTINCT pnombre FROM P FROM P WHERE (ciudad = ‘Londres’ OR ciudad = ‘Roma’); WHERE ciudad IN (‘Londres’, ‘Roma’);

Consulta 22. Código de las piezas que no sean ni rojas ni verdes.

Forma 1: Forma 2:

SELECT DISTINCT pn SELECT DISTINCT pn FROM P FROM P WHERE (color <> ‘rojo’ AND color <> ‘verde’); WHERE color NOT IN (‘rojo’, ‘verde’);

Subconsultas

A veces, para realizar algunas operaciones de consulta, necesitamos los datos devueltos por otra consulta. Para

ello, utilizaremos una subconsulta, que no es más que una sentencia SELECT dentro de otra SELECT. Una subconsulta

consistirá en incluir una declaración SELECT como parte de una cláusula WHERE. La subconsulta (el comando SELECT

entre paréntesis) se ejecutará primero y, posteriormente, el valor extraído es “introducido” en la consulta principal.

Consulta 23. Nombre de los proveedores cuya ciudad sea la misma que la de Salazar.

SELECT snombre FROM S WHERE ciudad = ( SELECT ciudad FROM S WHERE snombre='Salazar' );

Consulta 24. Nombre de las piezas de peso mayor que el de la tuerca.

SELECT DISTINCT pnombre FROM P WHERE peso > ( SELECT peso FROM P WHERE pnombre='tuerca' );

SQL permite la expresiones “mayor que algún”, “menor que algún”, etc… Para ello utilizaremos las

comparaciones >ANY, <ANY, >=ANY, <=ANY, =ANY, <>ANY. La palabra SOME es sinónimo de ANY en SQL. De

la misma forma, permite las expresiones “mayor que todos”, “menor que todos”, etc… para lo que utilizaremos las

comparaciones >ALL, < ALL, >=ALL, <=ALL, =ALL, <>ALL.

Consulta 25. Nombre de las piezas de peso mayor al de las piezas almacenadas en Londres.

SELECT DISTINCT pnombre FROM P WHERE peso > ALL ( SELECT peso FROM P WHERE ciudad='Londres' );

Consulta 21

pnombre

perno

birlo

Consulta 22

pn

P3

P5

Consulta 23

snombre

Salazar

Corona

Consulta 24

pnombre

birlo

engrane

perno

Consulta 25

pnombre

engrane

Nota: ésta es otra forma de resolver la consulta 13

SQL

7

Consulta 26. Nombre de los proveedores que no suministran la pieza P1.

SELECT DISTINCT snombre FROM S WHERE sn <> ALL ( SELECT sn FROM SP WHERE pn='P1' );

Consulta 27. Código de los proveedores que suministran algunas de las

piezas que suministra el proveedor S2.

SELECT DISTINCT sn FROM SP WHERE pn = ANY ( SELECT pn FROM SP WHERE sn='S2' );

Consultas de agrupación y totales

La sentencia SELECT posibilita agrupar uno o más conjuntos de filas. El agrupamiento se lleva a cabo mediante la

cláusula GROUP BY por las columnas especificadas y en el orden especificado. Las funciones para grupos de valores

se describen a continuación:

Función Propósito

AVG Calcula la media de todos los valores seleccionados, ignorando los valores nulos.

COUNT Cuenta el total de elementos de un grupo.

MIN Muestra el valor más pequeño del grupo.

MAX Muestra el valor máximo de todo el conjunto de registros agrupados.

SUM Suma los valores numéricos de los registros agrupados.

Consulta 28. Número total de proveedores. Consulta 29. Peso mínimo de entre todas las piezas.

SELECT COUNT(*) AS “Total proveedores” SELECT MIN(peso) AS “Peso mínimo” FROM S; FROM P;

Consulta 30. Cantidad total de envíos por proveedor. Consulta 31. Número de piezas de cada color.

SELECT sn, SUM(cant) AS “Total envíos” SELECT color, COUNT(*) AS “Total piezas” FROM SP FROM P GROUP BY sn; GROUP BY color;

Del mismo modo que existe la condición de búsqueda WHERE para filas individuales, también hay una condición

de búsqueda para grupos de filas: HAVING. La cláusula HAVING se emplea para controlar cuál de los conjuntos de

filas se visualiza. Se evalúa sobre la tabla que devuelve el GROUP BY. No puede existir sin GROUP BY.

Consulta 26

snombre

Aldana

Bernal

Corona

Consulta 27

sn

S1

S2

S3

S4

Consulta 30

sn Total envíos

S1 1300

S2 700

S3 200

S4 1000

Consulta 31

color Total piezas

azul 2

rojo 3

verde 1

Consulta 29

Peso mínimo

12

Consulta 28

Total proveedores

5

Nota:

Esta consulta la podríamos haber resuelto usando NOT IN en lugar de <> ALL

Nota:

Esta consulta la podríamos haber resuelto usando IN en lugar de = ANY

SQL

8

Consulta 32. Código de los proveedores que en total envíen 1000 o más piezas.

SELECT sn AS "Proveedor", SUM(cant) AS "Total envíos" FROM SP GROUP BY sn HAVING SUM(cant)>=1000;

La cláusula HAVING es similar a la cláusula WHERE, pero trabaja con grupos de filas: pregunta por una

característica de grupo, es decir, pregunta por los resultados de las funciones de grupo, lo cual WHERE no puede hacer.

Consulta 33. Nombre de los proveedores que envían más de 2 tipos distintos de piezas.

SELECT snombre, COUNT(pn) AS "Piezas distintas" FROM SP, S WHERE SP.sn=S.sn GROUP BY snombre, SP.sn HAVING COUNT(pn)>2 ORDER BY snombre DESC;

Cuando usamos la cláusula ORDER BY con columnas y funciones de grupo, hemos de tener en cuenta que ésta se

ejecuta detrás de las cláusulas WHERE, GROUP BY y HAVING. En ORDER BY podemos especificar funciones de grupo,

columnas de GROUP BY o su combinación.

Modificación de la base de datos

Vamos a ver cómo cambiar los datos de las tablas de la base de datos, es decir, vamos a ver cómo se insertan

nuevas filas en una tabla, se actualizan los valores de las columnas en las filas y se borran filas enteras.

Para ejecutar las órdenes que veremos a continuación, OpenOffice Base proporciona una herramienta que permite

ejecutar cualquier sentencia SQL. La encontrarás en el menú Herramientas > SQL de la ventana principal del programa.

Inserción de datos. INSERT.

La orden INSERT permite añadir filas de datos en una tabla. Las filas a insertar deben tener el número exacto de

atributos o campos definidos en la tabla. También es fundamental que los tipos de los campos coincidan.

INSERT INTO S VALUES ('S6', 'Marco', '20', 'Londres');

INSERT INTO SP VALUES ('S6','P1','100');

INSERT INTO SP VALUES ('S6','P3','300');

Si no se recuerda el orden de los campos, SQL permite especificar los campos como parte de la sentencia INSERT.

INSERT INTO S (sn, snombre, estado, ciudad) VALUES ('S7', 'Andreu', '10', 'Atenas');

INSERT INTO SP (sn, pn, cant) VALUES ('S7','P4','500');

Consulta 32

Proveedor Total envíos

S1 1300

S4 1000

Consulta 33

Snombre Piezas distintas

Salazar 6

Corona 4

SQL

9

Cualquier campo que no se encuentre en la lista de campos recibirá el valor NULL, siempre y cuando no esté

definido como NOT NULL, en cuyo caso INSERT fallará. Si no se da la lista de campos, se han de introducir valores en

todos los campos.

También podemos insertar filas basadas en el resultado de una consulta SELECT.

Inserta en la tabla de envíos SP para el proveedor S7 tantas piezas P1 como envía el proveedor S1.

INSERT INTO SP (sn, pn, cant) SELECT 'S7', 'P1', cant FROM SP WHERE sn='S1' AND pn='P1';

Modificación de datos. UPDATE.

Para actualizar los valores de los campos de una o varias filas de una tabla, utilizaremos la orden UPDATE. La

cláusula SET indicará las columnas que se van a actualizar y sus valores, y la cláusula WHERE seleccionará las filas que se

van a actualizar (si se omite, la actualización afectará a todas las filas de la tabla).

Sube a 300 los envíos del proveedor S6 de la pieza P1 Aumenta en 100 todos los envíos del proveedor S7 UPDATE SP UPDATE SP SET cant = 300 SET cant = cant + 100 WHERE sn='S6' AND pn='P1'; WHERE sn='S7';

También podemos incluir una subconsulta en una sentencia UPDATE contenida en la cláusula WHERE.

Para todos los proveedores que envíen piezas azules, aumentar en 50 la cantidad de todos sus envíos.

UPDATE SP SET cant=cant+50 WHERE sn IN

( SELECT sn FROM SP, P WHERE P.pn=SP.pn AND color='azul' );

Borrado de filas. DELETE.

Para eliminar una o varias filas de una tabla, utilizaremos la orden DELETE. DELETE puede ir o no seguido de

FROM. La cláusula WHERE seleccionará sólo aquellas filas que se desean eliminar. Sin la cláusula WHERE, DELETE borrará

todas las filas de la tabla. La condición puede incluir una subconsulta.

Eliminar todos los envíos del proveedor S7. Borrar todos los envíos inferiores a la media. DELETE FROM SP DELETE P WHERE sn='S7'; WHERE cant < (SELECT AVG(cant) FROM SP);