Bases de datos relacionales y SQL

(1)

Bases de datos relacionales y SQL

Jes´us Arias Fisteus

(2)

Parte I

Introducci´

on a las bases de datos

(3)

Bases de datos relacionales

I Colecci´on de datos almacenados en una o m´as tablas.

I Las tablas constan de filas y columnas.

(4)

Ejemplo: una ´

unica tabla

Tabla “Paises”

nombre continente superficie poblacion capital

Espa˜na Europa 505370 46438422 Madrid

Francia Europa 643801 64590000 Par´ıs

Canad´a Am´erica 9984670 36155487 Ottawa

Alemania Europa 357022 81770900 Berl´ın

(5)

Ejemplo: varias tablas relacionadas

Tabla “Paises”

id nombre continente superficie poblacion capital

1 Espa˜na 1 505370 46438422 1 2 Francia 1 643801 64590000 3 3 Canad´a 2 9984670 36155487 4 4 Alemania 1 357022 81770900 6 5 Australia 3 7692024 23613193 7 Tabla “Continentes”

id nombre superficie poblacion

(6)

Ejemplo: varias tablas relacionadas

Tabla “Ciudades”

1 Madrid 605.77 3141991 2 Barcelona 102.15 1604555 3 Par´ıs 105.40 2229621 4 Ottawa 2778.64 1083391 5 Nueva York 1214.00 8491079 6 Berl´ın 891.68 3469849 7 Canberra 814.20 381488

(7)

Relaciones entre tablas

I Las relaciones entre tablas se explicitan por medio de:

I Clave primaria: columna o combinaci´on de columnas que identifican un´ıvocamente a las filas de una tabla.

I Clave ajena (también conocida como clave externa o foránea): columna o combinación de columnas en una tabla que hacen referencia a la clave primaria de otra tabla.

(8)

Sistema gestor de bases de datos relacionales

I Programa que da soporte al uso de bases de datos relacionales.

I Ejemplos:

I Oracle Database

I Microsoft SQL Server

I _{IBM DB2}

I SAP Advantage Database Server

I MySQL

I PostgreSQL

(9)

El lenguaje SQL

I Lenguaje est´andar para utilizar y mantener bases de datos relacionales.

I Utilizado en los principales gestores de bases de datos relacionales.

I Aunque hay peque˜nas variaciones (dialectos) dependiendo del gestor.

(10)

Parte II

(11)

La sentencia SELECT

I La sentencia SELECT se utiliza para recuperar datos de la base de datos.

1 S E L E C T

2 < c o l u m n a _ 1 > , < c o l u m n a _ 2 > , < c o l u m n a _ 3 >

3 F R O M < tabla >

(12)

La sentencia SELECT: ejemplos

1 S E L E C T *

2 F R O M P a i s e s ;

1 Espa˜na 1 505370 46438422 1

2 Francia 1 643801 64590000 3

3 Canad´a 2 9984670 36155487 4

4 Alemania 1 357022 81770900 6

5 Australia 3 7692024 23613193 7

id nombre continente superficie poblacion capital 1 Espa˜na 1 505370 46438422 1 2 Francia 1 643801 64590000 3 3 Canad´a 2 9984670 36155487 4 4 Alemania 1 357022 81770900 6 5 Australia 3 7692024 23613193 7

(13)

La sentencia SELECT: ejemplos

1 S E L E C T nombre , p o b l a c i o n 2 F R O M P a i s e s ; nombre poblacion Espa˜na 46438422 Francia 64590000 Canad´a 36155487 Alemania 81770900 Australia 23613193

(14)

La sentencia SELECT: ejemplos

1 S E L E C T s u p e r f i c i e , p o b l a c i o n 2 F R O M P a i s e s 3 W H E R E n o m b r e ='E s p a ~n a'; superficie poblacion 505370 46438422

(15)

La sentencia SELECT: ejemplos

1 S E L E C T s u p e r f i c i e , p o b l a c i o n 2 F R O M P a i s e s 3 W H E R E id =1; superficie poblacion 505370 46438422

(16)

L´

ogica Booleana

I Operadores Booleanos: AND, OR, NOT.

I Expresiones de comparaci´on: =, <>,<,>,<=, >=, BETWEEN.

I Pertenencia a un conjunto de valores: IN.

(17)

La sentencia SELECT: ejemplos

1 S E L E C T nombre , p o b l a c i o n 2 F R O M P a i s e s 3 W H E R E p o b l a c i o n > 5 0 0 0 0 0 0 0 ; nombre poblacion Francia 64590000 Alemania 81770900

(18)

La sentencia SELECT: ejemplos

1 S E L E C T nombre , p o b l a c i o n 2 F R O M P a i s e s 3 W H E R E p o b l a c i o n < 5 0 0 0 0 0 0 0 AND c o n t i n e n t e =1; nombre poblacion Espa˜na 46438422

(19)

La sentencia SELECT: ejemplos

1 S E L E C T nombre , p o b l a c i o n 2 F R O M P a i s e s 3 W H E R E p o b l a c i o n B E T W E E N 5 0 0 0 0 0 0 0 AND 7 0 0 0 0 0 0 0 ; nombre poblacion Francia 64590000

(20)

La sentencia SELECT: ejemplos

1 S E L E C T n o m b r e 2 F R O M P a i s e s 3 W H E R E c o n t i n e n t e IN (1 , 3) ; nombre Espa˜na Francia Alemania Australia

(21)

Ordenaci´

on y restricci´

on del n´

umero de resultados

I Ordenaci´on:

I _{ORDER BY}_{especifica la columna (o columnas) sobre la cual se}

deben ordenar las filas.

I Sentido de ordenaci´on:ASC(por defecto),DESC.

I Restricci´on del n´umero de resultados:

I Palabra claveLIMIT.

(22)

La sentencia SELECT: ejemplos

1 S E L E C T nombre , p o b l a c i o n 2 F R O M P a i s e s 3 O R D E R BY p o b l a c i o n ; nombre poblacion Australia 23613193 Canad´a 36155487 Espa˜na 46438422 Francia 64590000 Alemania 81770900

(23)

La sentencia SELECT: ejemplos

1 S E L E C T nombre , p o b l a c i o n 2 F R O M P a i s e s 3 W H E R E p o b l a c i o n < 4 0 0 0 0 0 0 0 4 O R D E R BY p o b l a c i o n ; nombre poblacion Australia 23613193 Canad´a 36155487

(24)

La sentencia SELECT: ejemplos

1 S E L E C T nombre , p o b l a c i o n 2 F R O M P a i s e s 3 O R D E R BY p o b l a c i o n D E S C 4 L I M I T 2; nombre poblacion Alemania 81770900 Francia 64590000

(25)

La sentencia SELECT: ejemplos

1 S E L E C T nombre , p o b l a c i o n 2 F R O M P a i s e s 3 O R D E R BY p o b l a c i o n D E S C 4 L I M I T 2 O F F S E T 2; nombre poblacion Espa˜na 46438422 Canad´a 36155487

(26)

Ejercicios pr´

acticos

I Pr´actica 1:

I Ejercicio 1.1

(27)

Agregaci´

on de datos: resultados ´

unicos

I A veces no se desea obtener resultados duplicados en una consulta. 1 S E L E C T c o n t i n e n t e 2 F R O M P a i s e s ; continente 1 1 1

(28)

Agregaci´

on de datos: resultados ´

unicos

I Si en los resultados de una consulta aparecen filas duplicadas, el modificador DISTINCT elimina todas excepto una.

1 S E L E C T D I S T I N C T c o n t i n e n t e 2 F R O M P a i s e s ; continente 1 2 3

(29)

Agregaci´

on de datos: sumas, medias, etc.

I C´omputo de agregaciones sobre las filas obtenidas: SUM, AVG, MIN, MAX.

1 S E L E C T SUM( s u p e r f i c i e )

2 F R O M P a i s e s

3 W H E R E c o n t i n e n t e =1;

SUM(superficie) 1506193

(30)

Cuenta del n´

umero de resultados

I El operador COUNT modifica la consulta de tal forma que devuelva el n´umero de filas seleccionadas.

1 S E L E C T C O U N T(*)

3 W H E R E c o n t i n e n t e =1;

COUNT(*) 3

(31)

Cuenta del n´

umero de resultados

I Tambi´en es posible contar valores sin tener en cuenta sus repeticiones.

1 S E L E C T C O U N T(D I S T I N C T c o n t i n e n t e )

2 F R O M P a i s e s ;

COUNT(DISTINCT continente) 3

(32)

Agregaci´

on de datos: agrupaci´

on de filas

I GROUP BY permite hacer c´omputos agregados (suma, media, etc.) sobre grupos de filas.

1 S E L E C T c o n t i n e n t e , SUM( s u p e r f i c i e ) 2 F R O M P a i s e s 3 G R O U P BY c o n t i n e n t e ; continente SUM(superficie) 1 1506193 2 9984670 3 7692024

(33)

Agregaci´

on de datos: agrupaci´

on de filas

I HAVING filtra los grupos resultantes.

1 S E L E C T c o n t i n e n t e , SUM( s u p e r f i c i e ) 2 F R O M P a i s e s 3 G R O U P BY c o n t i n e n t e 4 H A V I N G SUM( s u p e r f i c i e ) > 5 0 0 0 0 0 0 ; continente SUM(superficie) 2 9984670 3 7692024

(34)

Agregaci´

on de datos: agrupaci´

on de filas

I N´otese que con WHERE se seleccionan las filas que ser´an agrupadas, mientras que con HAVING, los grupos.

1 S E L E C T c o n t i n e n t e , SUM( s u p e r f i c i e ) 2 F R O M P a i s e s 3 W H E R E p o b l a c i o n < 7 0 0 0 0 0 0 0 4 G R O U P BY c o n t i n e n t e 5 H A V I N G SUM( s u p e r f i c i e ) < 9 0 0 0 0 0 0 ; continente SUM(superficie) 1 1149171 3 7692024

(35)

Alias de columnas

I Se puede establecer el nombre que tomar´an las columnas resultantes de una consulta.

1 S E L E C T

2 c o n t i n e n t e ,

3 SUM( s u p e r f i c i e ) AS s u p e r f i c i e _ a g r e g a d a

5 G R O U P BY c o n t i n e n t e ;

continente superficie agregada

1 1506193

(36)

Columnas calculadas

I Se pueden obtener columnas resultantes de realizar c´alculos sobre los valores de otras columnas.

1 S E L E C T 2 nombre , 3 p o b l a c i o n / s u p e r f i c i e AS d e n s i d a d 4 F R O M P a i s e s ; nombre densidad Espa˜na 91.88994598017294 Francia 100.3260324230624 Canad´a 3.6210998460640162 Alemania 229.0360257911277 Australia 3.0698283052678983

(37)

Ejercicios pr´

acticos

I Pr´actica 1:

(38)

Parte III

(39)

Consultas sobre m´

ultiples tablas

I Es habitual que los datos a consultar est´en distribuidos en varias tablas relacionadas entre s´ı. Por ejemplo:

I Obtener los pares nombre de pa´ıs y nombre de capital.

I Obtener la lista de pa´ıses cuya capital tenga m´as de 3 millones de habitantes.

(40)

Ejemplo

Tabla “Paises”

1 Espa˜na 1 505370 46438422 1 2 Francia 1 643801 64590000 3 3 Canad´a 2 9984670 36155487 4 4 Alemania 1 357022 81770900 6 5 Australia 3 7692024 23613193 7 Tabla “Ciudades”

1 Madrid 605.77 3141991 2 Barcelona 102.15 1604555 3 Par´ıs 105.40 2229621 4 Ottawa 2778.64 1083391 5 Nueva York 1214.00 8491079 6 Berl´ın 891.68 3469849 7 Canberra 814.2 381488

(41)

Consultas

INNER JOIN

I El operador INNER JOINpermite unir dos tablas indicando bajo qu´e criterio se deben emparejar las filas de una tabla con las de la otra:

I Se forman todos los pares posibles de filas que cumplan el criterio.

(42)

Ejemplo

1 S E L E C T *

3 I N N E R J O I N C i u d a d e s

4 ON c a p i t a l = C i u d a d e s . id ;

id nombre cont. sup. pob. cap. id nombre sup. pob. 1 Espa˜na 1 505370 46438422 1 1 Madrid 605.77 3141991 2 Francia 1 643801 64590000 3 3 Par´ıs 105.40 2229621 3 Canad´a 2 9984670 36155487 4 4 Ottawa 2778.64 1083391 4 Alemania 1 357022 81770900 6 6 Berl´ın 891.68 3469849 5 Australia 3 7692024 23613193 7 7 Canberra 814.2 381488

(43)

Ejemplo

1 S E L E C T 2 P a i s e s . nombre , C i u d a d e s . n o m b r e 3 F R O M P a i s e s 4 I N N E R J O I N C i u d a d e s 5 ON c a p i t a l = C i u d a d e s . id ; nombre nombre Espa˜na Madrid Francia Par´ıs Canad´a Ottawa Alemania Berl´ın

(44)

Ejemplo

1 S E L E C T 2 P a i s e s . nombre , C i u d a d e s . n o m b r e 3 F R O M P a i s e s 4 I N N E R J O I N C i u d a d e s 5 ON c a p i t a l = C i u d a d e s . id 6 W H E R E C i u d a d e s . p o b l a c i o n > 3 0 0 0 0 0 0 ; nombre nombre Espa˜na Madrid Alemania Berl´ın

(45)

Alias de nombres de tablas

1 S E L E C T 2 P . nombre , C . n o m b r e 3 F R O M P a i s e s AS P 4 I N N E R J O I N C i u d a d e s AS C 5 ON c a p i t a l = C . id 6 W H E R E C . p o b l a c i o n > 3 0 0 0 0 0 0 ; nombre nombre Espa˜na Madrid Alemania Berl´ın

(46)

Consultas

INNER JOIN

I Las filas de cualquiera de las dos tablas que no cumplan el criterio para ning´un posible par no aparecer´an entre los resultados.

I Ejemplo: filas de Nueva York y Barcelona.

I Las filas de cualquiera de las dos tablas que cumplan el criterio para varios pares aparecer´an varias veces entre los resultados, una por cada par.

(47)

Ejemplo

Tabla “Ciudades”

1 Madrid 605.77 3141991 2 Barcelona 102.15 1604555 3 Par´ıs 105.40 2229621 4 Ottawa 2778.64 1083391 5 Nueva York 1214.00 8491079 6 Berl´ın 891.68 3469849 7 Canberra 814.2 381488 Tabla “Atracciones” id nombre ciudad

(48)

Ejemplo

1 S E L E C T 2 A t r a c c i o n e s . nombre , C i u d a d e s . n o m b r e 3 F R O M A t r a c c i o n e s 4 I N N E R J O I N C i u d a d e s 5 ON c i u d a d = C i u d a d e s . id ; nombre nombre

Estatua de la Libertad Nueva York

Torre Eiffel Par´ıs

(49)

Ejercicios pr´

acticos

I Pr´actica 1:

(50)

Consultas

OUTER JOIN

I Se recuperan todas las filas de la tabla primariaaunque no cumplan el criterio con ninguna fila de la otra tabla.

I Tres tipos deOUTER JOIN:

I _{LEFT JOIN}: la tabla primaria es la especificada antes del operador JOIN(a la izquierda).

I RIGHT JOIN: la tabla primaria es la especificada tras el operador JOIN(a la derecha).

I FULL JOIN: ambas tablas son primarias (no disponible en MySQL).

(51)

Ejemplo

1 S E L E C T 2 A t r a c c i o n e s . nombre , C i u d a d e s . n o m b r e 3 F R O M A t r a c c i o n e s 4 L E F T J O I N C i u d a d e s 5 ON c i u d a d = C i u d a d e s . id ; nombre nombre

Empire State Building Nueva York

(52)

Ejemplo

1 S E L E C T 2 A t r a c c i o n e s . nombre , C i u d a d e s . n o m b r e 3 F R O M A t r a c c i o n e s 4 R I G H T J O I N C i u d a d e s 5 ON c i u d a d = C i u d a d e s . id ; nombre nombre NULL Madrid NULL Barcelona

NULL Ottawa

Estatua de la Libertad Nueva York Empire State Building Nueva York

NULL Berl´ın

(53)

Ejemplo

1 S E L E C T 2 A t r a c c i o n e s . nombre , C i u d a d e s . n o m b r e 3 F R O M C i u d a d e s 4 L E F T J O I N A t r a c c i o n e s 5 ON c i u d a d = C i u d a d e s . id ; nombre nombre NULL Madrid NULL Barcelona

NULL Ottawa

(54)

Ejemplo (no disponible en MySQL)

1 S E L E C T 2 A t r a c c i o n e s . nombre , C i u d a d e s . n o m b r e 3 F R O M C i u d a d e s 4 F U L L J O I N A t r a c c i o n e s 5 ON c i u d a d = C i u d a d e s . id ; nombre nombre

Empire State Building Nueva York

NULL Madrid

NULL Barcelona

NULL Ottawa

NULL Berl´ın

NULL Canberra

(55)

Consultas

self join

I Es posible unir una tabla consigo misma mediante el uso de alias de nombres de tablas:

I Se toman dos “copias” de la misma tabla, cada una con un alias que la diferencia de la otra.

I Es compatible con cualquier tipo de operador JOIN.

I Ejemplo:

I Obtener todos los pares de monumentos que est´en en la misma ciudad.

(56)

Ejemplo

1 S E L E C T 2 A . nombre , B . n o m b r e 3 F R O M A t r a c c i o n e s AS A 4 I N N E R J O I N A t r a c c i o n e s AS B 5 ON A . c i u d a d = B . c i u d a d ; nombre nombre

Estatua de la Libertad Estatua de la Libertad Empire State Building Estatua de la Libertad

Torre Eiffel Torre Eiffel

Estatua de la Libertad Empire State Building Empire State Building Empire State Building

(57)

Ejemplo

1 S E L E C T 2 A . nombre , B . n o m b r e 3 F R O M A t r a c c i o n e s AS A 4 I N N E R J O I N A t r a c c i o n e s AS B 5 ON A . c i u d a d = B . c i u d a d 6 W H E R E A . id < B . id ; nombre nombre

(58)

Parte IV

El lenguaje SQL: inserci´

on y

(59)

Creaci´

on de tablas

I Se crean tablas con CREATE TABLE.

I Al crear una tabla, se especifican aspectos como:

I Nombre de la tabla.

I Para cada columna:

I Nombre y tipo de datos

I Valor por defecto

I Si puede tomar valorNULL.

I Si es un campo de auto-incremento.

I Claves primarias y ajenas.

(60)

Ejemplo

1 C R E A T E T A B L E C o n t i n e n t e s ( 2 id INT NOT N U L L a u t o _ i n c r e m e n t , 3 n o m b r e V A R C H A R( 2 5 5 ) NOT NULL, 4 s u p e r f i c i e D O U B L E NOT NULL, 5 p o b l a c i o n L O N G NOT NULL, 6 P R I M A R Y KEY( id ) 7 ) ;

(61)

Ejemplo

1 C R E A T E T A B L E C i u d a d e s ( 2 id INT NOT N U L L a u t o _ i n c r e m e n t , 3 n o m b r e V A R C H A R( 2 5 5 ) NOT NULL, 4 s u p e r f i c i e D O U B L E NOT NULL, 5 p o b l a c i o n L O N G NOT NULL, 6 P R I M A R Y KEY( id ) 7 ) ;

(62)

Ejemplo

1 C R E A T E T A B L E P a i s e s (

2 id INT NOT N U L L a u t o _ i n c r e m e n t ,

3 n o m b r e V A R C H A R( 2 5 5 ) NOT NULL,

4 c o n t i n e n t e INT NOT NULL,

5 s u p e r f i c i e D O U B L E NOT NULL,

6 p o b l a c i o n L O N G NOT NULL,

7 c a p i t a l INT NOT NULL,

8 P R I M A R Y KEY( id ) , 9 C O N S T R A I N T F O R E I G N KEY ( c o n t i n e n t e ) 10 R E F E R E N C E S C o n t i n e n t e s ( id ) , 11 C O N S T R A I N T F O R E I G N KEY ( c a p i t a l ) 12 R E F E R E N C E S C i u d a d e s ( id ) 13 ) ;

(63)

Ejemplo

1 C R E A T E T A B L E A r e a s G e o g r a f i c a s ( 2 id INT NOT N U L L a u t o _ i n c r e m e n t , 3 n o m b r e V A R C H A R( 2 5 5 ) NOT NULL, 4 t i p o E N U M ('c o n t i n e n t e', 'p a i s', 5 'r e g i o n', 'c i u d a d') NOT NULL, 6 a r e a D O U B L E NOT NULL, 7 h a b i t a n t e s L O N G NOT NULL, 8 P R I M A R Y KEY( id ) 9 ) ;

(64)

Tipos de datos

I Las columnas tienen un tipo de datos asociado.

I Principales grupos de tipos de datos:

I Num´ericos.

I Cadenas.

I Fechas / horas.

I Los tipos de datos concretos var´ıan seg´un el gestor de bases de datos.

(65)

Tipos de datos num´

ericos en MySQL

I Bits:BIT(p.e. cuatro bits: BIT(4)).

I Enteros con signo (opcionalUNSIGNED):

I 1 byte:TINYINT/BOOL /BOOLEAN.

I 2 bytes:SMALLINT.

I 3 bytes:MEDIUMINT.

I _{4 bytes:}_INT_/_INTEGER_. I 8 bytes:BIGINT.

I Punto flotante:

I 4 bytes (precisi´on aprox. 7 decimales):FLOAT.

I 8 bytes (precisi´on aprox. 15 decimales):DOUBLE.

(66)

Tipos de datos de cadenas

I Cadenas de caracteres:

I _Tama˜_{no fijo:}_CHAR_(p.e. _CHAR(8)_).

I Tama˜no variable:VARCHAR(p.e.VARCHAR(255)).

I Tama˜no grande:TEXT.

I Cadenas de bytes:

I Tama˜no fijo:BINARY(p.e.BINARY(8)).

I Tama˜no variable:VARBINARY(p.e. VARBINARY(255)).

I Tama˜no grande:BLOB.

I Enumerados: ENUM

I P.e.:ENUM('profesor', 'alumno', 'administrativo').

I Conjuntos: SET

(67)

Tipos de datos de fechas y horas en MySQL

I Fecha: DATE.

I Fecha y hora (con minutos y segundos, sin zona horaria): DATETIME.

I Hora: TIME.

I A˜no: YEAR (para a˜no con dos d´ıgitos,YEAR(2)).

I Sello temporal (n´umero de segundos desde 1-1-1970: TIMESTAMP.

I _{Por defecto, la primera columna de tipo}_TIMESTAMP_se

(68)

Otras acciones de gesti´

on de tablas

I Mostrar todas las tablas de una base de datos:SHOW TABLES

I Mostrar la estructura de una tabla: DESCRIBE <nombre de tabla>

I Eliminar una tabla: DROP TABLE <nombre de tabla>;

I A˜nadir, modificar o eliminar columnas en una tabla: ALTER TABLE ...

(69)

Inserci´

on de filas en tablas

I Se insertan filas nuevas en tablas medianteINSERT INTO:

I Se puede insertar una o m´as filas en una ´unica sentencia.

I Se puede especificar qu´e columnas se proporcionan y en qu´e orden (por defecto, se deben proporcionar todas y en el mismo orden en que se definieron al crear la tabla).

1 I N S E R T I N T O < tabla >

2 ( < c o l u m n a _ 1 > , < c o l u m n a _ 2 > , < c o l u m n a _ 3 >)

3 V A L U E S

4 ( < v a l o r e s _ f i l a _ 1 >) ,

(70)

Ejemplo

1 I N S E R T I N T O C o n t i n e n t e s

2 V A L U E S

(71)

Columnas para las que no se proporciona valor

I Se puede omitir el valor de algunas columnas:

I Columnas autoincrementales:

I Reciben automáticamente un valor numérico único que se incrementa a medida que se insertan filas en la tabla.

I Se suelen utilizar como clave primaria.

I Otros tipos de columnas:

I Reciben el valor por defecto definido para dicha columna, o

(72)

Ejemplo

1 I N S E R T I N T O C o n t i n e n t e s 2 ( nombre , s u p e r f i c i e , p o b l a c i o n ) 3 V A L U E S 4 ('A f r i c a', 3 0 3 7 0 0 0 0 , 1 1 0 0 0 0 0 0 0 0 ) , 5 ('Ant ´a r t i d a', 1 4 0 0 0 0 0 0 , 1 3 5 ) ;

(73)

Inserci´

on de los resultados de una consulta

I Se puede insertar filas resultantes de una consultaSELECT.

1 I N S E R T I N T O C i u d a d e s 2 ( nombre , s u p e r f i c i e , p o b l a c i o n ) 3 S E L E C T 4 nombre , area , h a b i t a n t e s 5 F R O M A r e a s G e o g r a f i c a s 6 W H E R E t i p o ='c i u d a d';

(74)

Inserci´

on de los resultados de una consulta

I Tambi´en es posible insertar el resultado de una consulta en una columna concreta.

1 I N S E R T I N T O P a i s e s 2 ( nombre , capital , c o n t i n e n t e , s u p e r f i c i e , p o b l a c i o n ) 3 V A L U E S 4 ('I t a l i a', 5 (S E L E C T id 6 F R O M C i u d a d e s 7 W H E R E n o m b r e ='R o m a') , 8 (S E L E C T id 9 F R O M C o n t i n e n t e s 10 W H E R E n o m b r e ='E u r o p a') , 11 3 0 1 3 4 0 . 0 , 12 6 0 6 0 0 0 0 0 13 ) ;

(75)

Ejercicios pr´

acticos

I Pr´actica 2:

I Ejercicio 1.1

(76)

Eliminaci´

on de filas

I Se eliminan filas en una tabla mediante la sentenciaDELETE.

I Se indica el nombre de la tabla y la condici´on que deben cumplir las filas a eliminar:

I Si no se indica condici´on, se eliminan todas las filas de la tabla.

1 D E L E T E

2 F R O M < tabla >

(77)

Ejemplo

1 D E L E T E

(78)

Actualizaci´

on de datos

I Se actualizan valores mediante la sentencia UPDATE.

I Se indica qu´e columnas se desea cambiar y su nuevo valor. El resto de columnas mantendr´an su valor.

I Con la cláusulaWHERE se indica qué filas se modifican (todas si se omite esta cláusula).

1 U P D A T E <table>

2 SET < C o l u m n a _ 1 > = < E x p r e s s i ´o n_1 > ,

3 < C o l u m n a _ 2 > = < E x p r e s s i ´o n_2 >

(79)

Ejemplo

1 U P D A T E P a i s e s 2 SET 3 p o b l a c i o n = 4 6 4 3 8 5 0 0 , 4 s u p e r f i c i e = 5 0 5 3 7 1 5 W H E R E id =1;

(80)

Ejemplo

1 U P D A T E P a i s e s

2 SET

3 p o b l a c i o n = p o b l a c i o n -1

(81)

Ejemplo

1 U P D A T E P a i s e s 2 SET 3 p o b l a c i o n = 4 (S E L E C T h a b i t a n t e s 5 F R O M A r e a s G e o g r a f i c a s 6 W H E R E P a i s e s . n o m b r e = A r e a s G e o g r a f i c a s . n o m b r e ) 7 W H E R E E X I S T S 8 (S E L E C T * 9 F R O M A r e a s G e o g r a f i c a s 10 W H E R E P a i s e s . n o m b r e = A r e a s G e o g r a f i c a s . n o m b r e ) ;

(82)

Parte V

(83)

Consistencia en las claves ajenas

I Una clave ajena debe corresponderse con una clave primaria que exista en la tabla a la que dicha clave ajena hace referencia.

I Ejemplo: sicontinente=2 en la fila conid=3 en la tabla

Paises, en la tablaContinentes debe existir una fila con

id=2.

I De lo contrario, la base de datos est´a en un estado inconsistente.

I La columna de clave ajena puede admitir valor NULLsi se establece as´ı al crear la tabla. Esto no supondr´ıa una

(84)

Consistencia en las claves ajenas

I Existe el riesgo de introducir inconsistencias en claves ajenas cuando:

I Se inserta una nueva fila:

I Ejemplo: se inserta una fila enPaisesconcontinente=27, pero no existe esta fila enContinentes.

I Se elimina una fila:

I Ejemplo: se elimina la fila conid=1 enContinentes, pero se mantienen filas concontinente=1 enPaises.

I Se modifica una columna en una fila:

I Ejemplo: se establece el valorcontinente=27 en una fila de

Paises, pero no existe esta fila enContinentes.

I _{Ejemplo: se modifica}_id_{de 1 a 7 en una fila de}_Continentes_,

(85)

Ejemplo

1 D E L E T E F R O M C o n t i n e n t e s W H E R E id =1; 2 3 I N S E R T I N T O P a i s e s 4 ( nombre , c o n t i n e n t e , capital , 5 s u p e r f i c i e , p o b l a c i o n ) 6 V A L U E S 7 ('E s t a d o s U n i d o s', 27 , 5 , 0.0 , 0) ; 8 9 U P D A T E P a i s e s SET c o n t i n e n t e =27 W H E R E id =1; 10 11 U P D A T E C o n t i n e n t e s SET id =7 W H E R E id =1;

(86)

Consistencia en las claves ajenas

I El gestor de bases de datos puede detectar sentencias que introducir´ıan inconsistencias y evitarlo.

I Para cada clave ajena se puede configurar el comportamiento deseado en estas situaciones:

I No ejecutar la sentencia y notificar el error (RESTRICTo NO ACTION). Es la opci´on por defecto.

I Establecer valor NULL en la clave ajena afectada si esta lo permite (SET NULL).

I Propagar el cambio a la clave ajena (CASCADE):

I Ejemplo: si se elimina la fila conid=1 enContinentes, se eliminan autom´aticamente todas las filas concontinente=1 enPaises.

I Ejemplo: si se modificaidde 1 a 7 en una fila de

Continentes, se cambiacontinentede 1 a 7 en todas las filas afectadas dePaises.

I Establecer el valor por defecto de la clave ajena (SET DEFAULT).

(87)

Ejemplo

8 P R I M A R Y KEY( id ) , 9 C O N S T R A I N T F O R E I G N KEY ( c o n t i n e n t e ) 10 R E F E R E N C E S C o n t i n e n t e s ( id ) 11 ON U P D A T E C A S C A D E 12 ON D E L E T E SET NULL, 13 C O N S T R A I N T F O R E I G N KEY ( c a p i t a l ) 14 R E F E R E N C E S C i u d a d e s ( id )

(88)

Tipos de tablas en MySQL

I En MySQL hay varios tipos de tablas que difieren en cómo se almacena la información y qué funcionalidad ofrecen:

I MyISAM: no transaccional, sin integridad referencial.

I BerkeleyDB: transaccional, sin integridad referencial.

I InnoDB: transaccional, con integridad referencial (por defecto desde Mysql 5.5).

I Otros:http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html

(89)

Ejemplo

8 P R I M A R Y KEY( id ) , 9 C O N S T R A I N T F O R E I G N KEY ( c o n t i n e n t e ) 10 R E F E R E N C E S C o n t i n e n t e s ( id ) 11 ON U P D A T E C A S C A D E 12 ON D E L E T E SET NULL, 13 C O N S T R A I N T F O R E I G N KEY ( c a p i t a l ) 14 R E F E R E N C E S C i u d a d e s ( id )

(90)

Parte VI

(91)

´Indices

I Los ´ındices consisten en estructuras de datos adicionales cuyo objeto es agilizar la ejecuci´on de determinadas b´usquedas de datos en una tabla.

I Ventajas:

I Localización más rápida de datos en la tabla en acceso aleatorio.

I Acceso a datos en orden de forma m´as r´apida.

I Desventajas:

I Coste adicional en la inserci´on/modificaci´on de datos.

(92)

Creaci´

on de ´ındices

1 - - C r e a r un ´ı n d i c e en una t a b l a e x i s t e n t e 2 C R E A T E [U N I Q U E] I N D E X < n o m b r e _ d e _ ´ı ndice > 3 ON < n o m b r e _ d e _ t a b l a > ( < col_1 > , < col_2 > ,...) ; 4 5 - - C r e a r un ´ı n d i c e al m i s m o t i e m p o que la t a b l a : 6 C R E A T E T A B L E < n o m b r e _ d e _ t a b l a > ( 7 ( . . . ) 8 [U N I Q U E] I N D E X [ < n o m b r e _ d e _ ´ı ndice >] 9 ( < col_1 > , < col_2 > ,...) 10 ) ;

(93)

Ejemplo

1 C R E A T E I N D E X i d x _ c i u d a d e s _ n o m b r e

(94)

Ejemplo

1 C R E A T E T A B L E C i u d a d e s ( 2 id INT NOT N U L L a u t o _ i n c r e m e n t , 3 n o m b r e V A R C H A R( 2 5 5 ) NOT NULL, 4 s u p e r f i c i e D O U B L E NOT NULL, 5 p o b l a c i o n L O N G NOT NULL, 6 P R I M A R Y KEY( id ) , 7 I N D E X ( n o m b r e ) 8 ) ;

(95)

Ejercicios pr´

acticos

I Pr´actica 2:

(96)

Parte VII

(97)

Transacciones en SQL

I El gestor de bases de datos puede recibir sentencias desde varias conexiones concurrentes.

I Una transacci´on es una secuencia de sentencias SQL que deben ser tratadas como una unidad.

I Deben cumplirse los principios ACID:

I Atomicidad.

I Consistencia.

I Aislamiento.

(98)

Transacciones en SQL

I Atomicidad:

I O se ejecutan con éxito todas las sentencias de la transacción, o la base de datos debe volver al estado previo al inicio de la transacción.

I Consistencia:

I Una vez finalizada la transacci´on, la base de datos debe estar en un estado consistente (se deben cumplir todas las

(99)

Transacciones en SQL

I Aislamiento:

I Durante la ejecuci´on de una transacci´on, sus cambios no pueden ser visibles para el resto de transacciones.

I Durabilidad:

I Una vez finaliza una transacci´on con ´exito, se debe garantizar que los cambios perduren incluso antes situaciones de fallo en el sistema.

(100)

Transacciones en SQL

I Normalmente, cada sentencia SQL individual se ejecuta como una transacci´on separada.

I Se puede deshabilitar de forma temporal para realizar solo una transacci´on con varias sentencias medianteSTART

TRANSACTION

I Se puede deshabilitar en la sesi´on actual medianteSET AUTOCOMMIT=0

I Para finalizar la transacci´on:

I Cancel´andola:ROLLBACK I Confirm´andola:COMMIT

(101)

Cerrojos en filas de tablas

I Los gestores de bases de datos relacionales suelen utilizar cerrojos para controlar el acceso concurrente a tablas.

I Principalmente, se usan dos tipos de cerrojos:

I Cerrojos S (shared)

(102)

Cerrojos en filas de tablas

I La adquisici´on de un cerrojo S es compatible con otros cerrojos S sobre la misma fila.

I La adquisici´on de un cerrojo X no es compatible con ning´un otro cerrojo sobre la misma fila.

I El intento de adquisici´on de un cerrojo bloquea la operaci´on hasta que sea posible. Si hay interbloqueo con otra

(103)

Cerrojos en filas de tablas

I En modificaciones y eliminaciones se adquiere X autom´aticamente, hasta el final de la transacci´on.

I Las lecturas se pueden realizar de tres formas:

I Consistent read: no se adquiere cerrojo (por defecto).

I Adquisici´on de S:SELECT ... LOCK IN SHARE MODE.

(104)

Niveles de aislamiento

I En SQL se puede configurar el nivel de aislamiento entre transacciones concurrentes:

I READ UNCOMMITTED

I READ COMMITTED

I REPEATABLE READ(por defecto)

I SERIALIZABLE

I Niveles mayores implican mayor protecci´on en transacciones concurrentes pero peor rendimiento.

(105)

Niveles de aislamiento

1 SET SESSION TRANSACTION ISOLATION LEVEL READUNCOMMITTED;

2 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

(106)

Referencias

I The Language of SQL, Second Edition, Larry Rockoff. Course Technology PTR (2016).

I Accesible en Safari:

(107)

Referencias

I MySQL Transactional and Locking Statements:

I http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-transactions.html

I The InnoDB Transaction Model and Locking: