Introducción a SQL

08/11/2013

1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas

Introducción a SQL (DML)

7. Orden de evaluación de cláusulas

Grupo de Ingeniería del Software y Bases de Datos Departamento de Lenguajes y Sistemas Informáticos © Diseño de Amador Durán Toro, 2011

Universidad de Sevilla noviembre 2013

Introducción a SQL

• Objetivos de este tema – Ser capaz de usar el SQL DML para manipular 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

noviembre 2012

IISSI

modelos relaciones (Crear, Modificar y Borrar Datos). – Ser capaz de usar el SQL DML para consultar modelos relacionales.

Introducción a la Ingeniería del Software y a los Sistemas de Información

1

© Diseño de Amador Durán Toro, 2011

1. Introducción

1

Introducción a SQL

08/11/2013

Inserción de datos en una tabla

• Sirve para añadir tuplas en la base de datos – Sintaxis: 1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

• INSERT INTO (, , ...) VALUES (, , ...) • Si se introducen todos los valores de la tupla, la sintaxis puede ser: INSERT INTO VALUES (, , ...)

– Ejemplo dirección,…) VALUES (1,”Mario González”, “Avd República Argentina 4”, …);

noviembre 2012

Introducción a la Ingeniería del Software y a los Sistemas de Información

2

© Diseño de Amador Durán Toro, 2011

• INSERT INTO Empleado (idEmpleado, nombre,

Inserción de datos en una tabla

• Se pueden añadir las filas resultantes de a evaluación de una consulta 1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

– Sintaxis: • INSERT INTO (, , ...) SELECT (, , ...) FROM [WHERE ]

– Ejemplo:

noviembre 2012

IISSI

Introducción a la Ingeniería del Software y a los Sistemas de Información

3

© Diseño de Amador Durán Toro, 2011

• INSERT INTO EmpResponsables(idempleado, idSucursal, salario) SELECT idEmpleado, idSucursal, salario FROM empleado e, sucursal s WHERE s.responsable=e.idEmpleado;

2

Introducción a SQL

08/11/2013

Borrado de datos en una tabla

• Para destruir una tabla (estructura y datos): DROP TABLE ; 1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

• Para eliminar algunas filas de una tabla: DELETE FROM [WHERE ];

• Para eliminar todo el contenido de una tabla, TRUNCATE TABLE ;

noviembre 2012

Introducción a la Ingeniería del Software y a los Sistemas de Información

4

© Diseño de Amador Durán Toro, 2011

pero no su estructura:

Borrado de datos en una tabla

• DELETE elimina las filas completas de una tabla que cumplan la clausula establecida 1. Introducción 2. Inserción

en el WHERE

3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

– Sintaxis: DELETE FROM TABLE WHERE ;

– Ejemplo: elimina los Empleados de Sevilla DELETE FROM empleados WHERE provincia = ‘Sevilla’;

– Ejemplo: Borrar los inmuebles de los empleados de la sucursal 8 DELETE FROM inmuebles WHERE idEmpleado IN WHERE idSucursal=8);

noviembre 2012

IISSI

Introducción a la Ingeniería del Software y a los Sistemas de Información

5

© Diseño de Amador Durán Toro, 2011

(SELECT idEmpleado FROM empleado

3

Introducción a SQL

08/11/2013

Modificación de datos en una tabla

• UPDATE modifica valores de columnas en una o más filas de una sola tabla. 1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

– La Cláusula SET especifica columnas que modificar y nuevos valores – La Cláusula WHERE selecciona las filas a actualizar. Si no hay WHERE, se aplica la modificación a todas las filas. – Sintaxis: SET col1 = [valor1], col2 = [valor2], … WHERE

noviembre 2012

Introducción a la Ingeniería del Software y a los Sistemas de Información

6

© Diseño de Amador Durán Toro, 2011

UPDATE

Modificación de datos en una tabla – Ejemplo: Incrementa un 10% el sueldo de los empleados que cobren menos de 1000 euros 1. Introducción 2. Inserción 3. Borrado

UPDATE empleado SET sueldo = sueldo*1,1 WHERE sueldo 1000 AND departamento=‘Comercial‘;

noviembre 2012

Introducción a la Ingeniería del Software y a los Sistemas de Información

8

© Diseño de Amador Durán Toro, 2011

– SELECT * FROM empleado WHERE ....;

Producto Cartesiano – El producto cartesina devuelve una nueva relación con todas las posibles combinaciones entre las tuplas de las relaciones involucradas. 1. Introducción 2. Inserción 3. Borrado 4. Modificación

SELECT * FROM A, B;

5. Consultas básicas

7. Orden de evaluación de cláusulas

noviembre 2012

IISSI

a b c

x y

a a b b c c

x y x y x y

Introducción a la Ingeniería del Software y a los Sistemas de Información

9

© Diseño de Amador Durán Toro, 2011

6. Consultas avanzadas

5

Introducción a SQL

08/11/2013

Producto Cartesiano

2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

noviembre 2012

SELECT inmueble.idInmueble, empleado.nombre FROM inmueble, empleado WHERE inmueble.idEmpleado = empleado.idEmpleado AND inmueble.precio>1000;

Introducción a la Ingeniería del Software y a los Sistemas de Información

10

© Diseño de Amador Durán Toro, 2011

1. Introducción

• El producto Cartesiano puede ir combinado con condicionales donde se relacionen los atributos de ambas tablas. • Inmuebles de más de 1000 euros con el nombre del empleado que lo gestiona

Natural join

1. Introducción

• Si los campos por los que se hace una comparación de igualdad en el producto cartesiano tienen el mismo nombre, se puede hacer el join natural:

2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

– SELECT inmueble.idInmueble, empleado.nombre FROM (inmueble natural join empleado)

– Es equivalente a:

noviembre 2012

IISSI

Introducción a la Ingeniería del Software y a los Sistemas de Información

11

© Diseño de Amador Durán Toro, 2011

SELECT inmueble.idInmueble, empleado.nombre FROM inmueble, empleado WHERE inmueble.idEmpleado=empleado.idEmpleado

6

Introducción a SQL

08/11/2013

Renombrado

• Se puede renombrar tanto los atributos obtenidos en la proyección, como las tablas 1. Introducción 2. Inserción

involucradas en la consulta.

3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

– SELECT E.nombre AS nom_empleado , S.nombre AS nom_supervisor FROM Empleado E, Empleado S

noviembre 2012

Introducción a la Ingeniería del Software y a los Sistemas de Información

12

© Diseño de Amador Durán Toro, 2011

WHERE E.nssjefe = S.nss;

Operaciones aritméticas

1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

• En las consultas se pueden aplicar operaciones aritméticas sobre las columnas de la relación que se obtiene. • Ejemplo:: Obtener una nueva relación con los salarios de los empleados que trabajan en la sucursal de la Avenida de las Ciencias 10, tras recibir un aumento del 10%. – SELECT nombre, 1.1*sueldo 1.1*sueldo FROM Empleado, Sucursal ’Avenida de las ciencias 10’ AND sucursal.idSucursal = empleado.idSucursal;

noviembre 2012

IISSI

Introducción a la Ingeniería del Software y a los Sistemas de Información

13

© Diseño de Amador Durán Toro, 2011

WHERE sucursal.dirección=

7

Introducción a SQL

08/11/2013

Tuplas repetidas

1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas

• Es sabido que las relaciones en una base de datos relacional no pueden tener tuplas repetidas. • Pero una consulta que obtenga un subconjunto de los atributos de una relación, puedenifmostrar información repetida. nss nombre edad sueldo

6. Consultas avanzadas

12.345.678-Z

123.456.789

Abel Abad

21

24.000

7. Orden de evaluación de cláusulas

23.456.789-D

234.567.890

Braulio Brío

32

36.000

34.567.890-V

345.678.901

Carlos Cepa

32

36.000

45.678.901-G

456.789.012

David Díaz

54

15.000

noviembre 2012

– Eliminación de duplicados costosa (ordenar+recorrer+eliminar) – El usuario puede desear ver las filas repetidas en el resultado – Si se aplica una función agregada a filas, rara vez deben 14 Introducción a la las Ingeniería del Software y a los Sistemas de Información eliminarse duplicadas

© Diseño de Amador Durán Toro, 2011

• SQL no elimina por defecto filas repetidas del resultado de una consulta, porque...

Tuplas repetidas

• Para eliminar explícitamente estas tuplas de salida se utiliza el operador DISTINCT. 1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas

• Ejemplos: Salarios de todos los empleados – SELECT edad, sueldo FROM Empleado; * Pueden salir tuplas duplicadas

– SELECT DISTINCT edad, sueldo FROM Empleado; * No saldrán tuplas duplicadas

noviembre 2012

IISSI

edad

sueldo

21

24.000

32

36.000

32

36.000

54

15.000

edad

sueldo

21

24.000

32

36.000

54

15.000

Introducción a la Ingeniería del Software y a los Sistemas de Información

15

© Diseño de Amador Durán Toro, 2011

7. Orden de evaluación de cláusulas

8

Introducción a SQL

08/11/2013

Ordenación de tuplas

1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

• Es sabido que en el modelo relacional la posición de las tuplas en las tablas no es relevante, ya que una relación corresponde a la idea matemática de conjunto • SQL permite presentar las filas resultado de una consulta de forma ordenada en función de las necesidades de cada momento mediante la cláusula ORDER BY:

• Ejemplo: Nombre de los empleados ordenados por su sueldo – SELECT empleados.nombre FROM empleados ORDER BY sueldo; 16 Introducción a la Ingeniería del Software y a los Sistemas de Información

noviembre 2012

© Diseño de Amador Durán Toro, 2011

– Ordenación según valores de una o varias columnas – Ascendente ASC (por defecto defecto)) o Descendente DESC – Las filas no se ordenan en disco: se ven ordenadas, pero no lo están en la tabla.

Funciones de Agregación

• SQL permite hacer operaciones de agregación sobre las relaciones. 1. Introducción

– Función COUNT( ): Cuenta el número de filas o de valores especificados en una consulta

2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas

– Funciones SUM( ), MAX( ), MIN( ), AVG( ):

6. Consultas avanzadas

Suma, máximo, mínimo y media aritmética (promedio)

7. Orden de evaluación de cláusulas

•

Ejemplo: Calcular la suma de los sueldos, el máximo,

– SELECT SUM(sueldo), MAX(sueldo), MIN(sueldo), AVG(sueldo), COUNT(*) FROM empleados; noviembre 2012

IISSI

Introducción a la Ingeniería del Software y a los Sistemas de Información

17

© Diseño de Amador Durán Toro, 2011

el mínimo, la media y contar todos los empleados.

9

Introducción a SQL

08/11/2013

Funciones de Agregación con Agrupación •

Se utilizan cuando se quieren aplicar funciones agregadas a subgrupos de tuplas de una relación con el mismo valor.

•

Se definen los “atributos de agrupación” y se pueden aplicar las funciones a cada uno de los grupos formados.

•

Ejemplo: Mostrar para cada sucursal, el número de trabajadores y su sueldo medio.

1. Introducción 2. Inserción 4. Modificación 5. Consultas básicas

– SELECT sucursal, COUNT(*), AVG(sueldo) FROM Empleado GROUP BY sucursal;

6. Consultas avanzadas 7. Orden de evaluación de cláusulas

A IdEmpleado

Sueldo

…

1000

1

1000

…

2

1500

…

IdEmpleado

Sucursal

Sueldo

1

A

2

A

1500

3

B

1200

4

C

900

5

C

2100

…

C

B

IdEmpleado

Sueldo

…

3

1200

…

IdEmpleado

Sueldo

…

4

900

…

5

2100

…

Introducción a la Ingeniería del Software y a los Sistemas de Información

noviembre 2012

18

© Diseño de Amador Durán Toro, 2011

3. Borrado

Funciones de Agregación con Condiciones

• Es posible especificar una condición sobre el grupo de tuplas asociado a cada valor de los 1. Introducción

atributos de agrupación con la cláusula HAVING. Sólo los grupos que cumplan la

2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas

condición formarán parte de la relación de salida de la consulta.

6. Consultas avanzadas 7. Orden de evaluación de cláusulas

•

Ejemplo: Mostrar para cada sucursal, el número de trabajadores y su sueldo medio siempre que la sucursal tenga al menos dos trabajadores. – SELECT sucursal, COUNT(*), AVG(sueldo) GROUP BY sucursal HAVING COUNT(*)>1;

noviembre 2012

IISSI

Introducción a la Ingeniería del Software y a los Sistemas de Información

19

© Diseño de Amador Durán Toro, 2011

FROM Empleado

10

Introducción a SQL

08/11/2013

Operacines de Conjuntos •

Como las relaciones corresponden a la idea matemática de conjunto, se pueden utiliza las operaciones de UNION (U), INTERSECT (∩), EXCEPT (—) (minus en

ORACLE). – Como resultado se obtiene un nuevo conjunto de filas donde las filas repetidas se eliminan – Las tablas operando han de ser compatibles en tipo: tipo:

2. Inserción 3. Borrado

• •

4. Modificación 5. Consultas básicas 6. Consultas avanzadas

•

7. Orden de evaluación de cláusulas

igual nº de columnas, columnas y columnas “correspondientes” con el mismo dominio

Ejemplo: Identificador de las propiedades donde está involucrado el empleado con DNI ´78945612D´, tanto como responsable como gerente de la sucursal. ( SELECT inmueble.idPropiedad FROM Inmueble WHERE inmueble.idEmpleado=´78945612D´) UNION ( SELECT inmueble.idPropiedad FROM inmueble, empleado, sucursal WHERE sucursal.responsable = ´78945612D´ and sucursal.idSucursal=empleado.idSucursal and empleado.idempleado=inmueble.idempleado);

•

Para NO eliminar duplicados de manera explícita se utiliza la clausula ALL. ALL –

noviembre 2012

UNION ALL, ALL INTERSECT ALL, ALL EXCEPT ALL Introducción a la Ingeniería del Software y a los Sistemas de Información

20

© Diseño de Amador Durán Toro, 2011

1. Introducción

Más Operadores en la cláusula WHERE

• La cláusula WHERE puede estar formada por: – Una combinación de comparaciones booleanas 1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

con los operadores AND, OR y NOT – Operador EXISTS – Operador IN – Operadores ALL, ANY o SOME – Between – Unique – Top

(no soportado en Oracle 9i, donde hay que utilizar el atributo

‘rownum’ que tienen las tablas)

– Like

noviembre 2012

IISSI

Introducción a la Ingeniería del Software y a los Sistemas de Información

21

© Diseño de Amador Durán Toro, 2011

– Is null

11

Introducción a SQL

08/11/2013

Operador EXISTS

• EXISTS(S), comprueba si la tabla S está vacía 1. Introducción

– Devuelve TRUE si la tabla S contiene al menos una fila y FALSE en caso contrario

2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas

– S suele ser una consulta anidada correlacionada

6. Consultas avanzadas 7. Orden de evaluación de cláusulas

• Ejemplos: – Nombre de empleados que llevan al menos un piso

WHERE inmuebles.idEmpleado=e.idEmpleado);

– Nombre de empleados que NO llevan ningún 22 Introducción a la Ingeniería del Software y a los Sistemas de Información piso

noviembre 2012

© Diseño de Amador Durán Toro, 2011

• SELECT nombre from empleados e WHERE EXISTS (SELECT * FROM inmuebles

Operador IN •

1. Introducción 2. Inserción

El operador de comparación IN permite comparar un valor individual v (generalmente un nombre de atributo) con un conjunto de valores V (generalmente una consulta anidada). – Devuelve TRUE si v es uno de los elementos de V.

3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

– Ejemplo: Nombre de empleados que llevan al menos un piso • SELECT nombre FROM empleados WHERE idEmpleado IN (SELECT idEmpleado FROM inmuebles);

• También es posible utilizar IN con un conjunto explícito de valores • SELECT nombre FROM empleado WHERE idSucursal IN (1,2,3);

noviembre 2012

IISSI

Introducción a la Ingeniería del Software y a los Sistemas de Información

23

© Diseño de Amador Durán Toro, 2011

– Ejemplo: Nombre de los empleados que trabajan en las sucursales 1, 2 ó 3.

12

Introducción a SQL

08/11/2013

Operadores ANY/SOME, ALL

• Operador ANY (SOME) • 1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

Sintaxis:

t [ANY|SOME] S, donde puede ser {>, ≥, , ≥, = ALL (SELECT count(*)

17

Introducción a SQL

08/11/2013

Consultas de Agregados Complejas

• Utilizando SELECT Anidado: – SELECT E.idSucursal, count(*) 1. Introducción 2. Inserción

FROM empleado E

3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas

GROUP BY idSucursal HAVING count(*) >= ALL (SELECT max(maximo)

7. Orden de evaluación de cláusulas

FROM (SELECT count(*) as maximo FROM empleado

noviembre 2012

Introducción a la Ingeniería del Software y a los Sistemas de Información

34

© Diseño de Amador Durán Toro, 2011

GROUP BY idSucursal ) )

Orden de evaluación de las cláusulas.

1) FROM (es decir, la reunión o join de tablas, si se especifica más de una) 1. Introducción 2. Inserción 3. Borrado 4. Modificación 5. Consultas básicas 6. Consultas avanzadas 7. Orden de evaluación de cláusulas

2) WHERE 3) GROUP BY 4) HAVING 5) SELECT

noviembre 2012

IISSI

Introducción a la Ingeniería del Software y a los Sistemas de Información

35

© Diseño de Amador Durán Toro, 2011

6) ORDER BY

18