Bases de Datos (II) Aplicaciones Web (Curso 2015/2016) · PDF fileEdited with emacs + LAT E X+...
Transcript of Bases de Datos (II) Aplicaciones Web (Curso 2015/2016) · PDF fileEdited with emacs + LAT E X+...
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Bases de Datos (II)
Aplicaciones Web (Curso 2015/2016)
Jesus Arias Fisteus // [email protected]
Bases de Datos (II)– p. 1
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Consultas OUTER JOIN
Una limitación de las consultas INNER JOIN esque requieren que haya coincidencia de datosentre las dos tablas. Si una fila de una tabla nocoincide con ninguna de la otra, esa fila no saleentre los resultados.
Las consultas OUTER JOIN permiten recuperarfilas aunque no tengan coincidencia con otras. Serecuperan todas las filas de la tabla primariaincluso si no coinciden con ninguna fila de la tablasecundaria.
Bases de Datos (II)– p. 2
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Consultas OUTER JOIN
Tres tipos de consultas OUTER JOIN:LEFT JOIN: la tabla mecionada antes de LEFTJOIN es la tabla primaria.RIGHT JOIN: la tabla mecionada después deRIGHT JOIN es la tabla primaria.FULL JOIN: ambas tablas son primarias. Serecuperan todas las filas de ambas tablas,aunque no tengan correspondencia en la otra.
Bases de Datos (II)– p. 3
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
CustomerID FirstName LastName
1 William Smith
2 Natalie Lopez
3 Brenda Harper
4 Adam Petrie
OrderID CustomerID OrderDate OrderAmount
1 1 2009-09-01 10.00
2 2 2009-09-02 12.50
3 2 2009-10-03 18.00
4 3 2009-09-15 20.00
RefundID OrderID RefundDate RefundAmount
1 1 2009-09-02 5.00
2 3 2009-10-12 18.00
Bases de Datos (II)– p. 4
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
1 SELECT
2 Customers.FirstName AS 'First Name ',
3 Customers.LastName AS 'Last Name ',
4 Orders.OrderDate AS 'Order Date ',
5 Orders.OrderAmount AS 'Order Amt ',
6 FROM Customers
7 LEFT JOIN Orders
8 ON Customers.CustomerID = Orders.CustomerID
9 ORDER BY Customers.CustomerID, Orders.OrderID
Bases de Datos (II)– p. 5
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
First Last Order Order
Name Name Date Amt
William Smith 2009-09-01 10.00
Natalie Lopez 2009-09-02 12.50
Natalie Lopez 2009-10-03 18.00
Brenda Harper 2009-09-15 20.00
Adam Petrie NULL NULL
Bases de Datos (II)– p. 6
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
1 SELECT
2 Customers.FirstName AS 'First Name ',
3 Customers.LastName AS 'Last Name ',
4 Orders.OrderDate AS 'Order Date ',
5 Orders.OrderAmount AS 'Order Amt ',
6 Refunds.RefundDate AS 'Refund Date ',
7 Refunds.RefundAmount AS 'Refund Amt '
8 FROM Customers
9 LEFT JOIN Orders
10 ON Customers.CustomerID = Orders.CustomerID
11 LEFT JOIN Refunds
12 ON Orders.OrderID = Refunds.OrderID
13 ORDER BY Customers.CustomerID,
14 Orders.OrderID, RefundID
Bases de Datos (II)– p. 7
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
First Last Order Order Refund Refund
Name Name Date Amt Date Amt
William Smith 2009-09-01 10.00 2009-09-02 5.00
Natalie Lopez 2009-09-02 12.50 NULL NULL
Natalie Lopez 2009-10-03 18.00 2009-10-12 18.00
Brenda Harper 2009-09-15 20.00 NULL NULL
Adam Petrie NULL NULL NULL NULL
Bases de Datos (II)– p. 8
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Consultas self join
Combinan datos de una tabla con otra vista de lamisma tabla.
Pueden utilizar tanto INNER JOIN como cualquiertipo de OUTER JOIN.
Bases de Datos (II)– p. 9
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
EmployeeID EmployeeName ManagerID
1 William Smith 2
2 Natalie Lopez NULL
3 Brenda Harper 1
4 Adam Petrie 2
1 SELECT
2 Employees.EmployeeName AS 'Employee Name ',
3 Managers.EmployeeName AS 'Manager Name '
4 FROM Personnel AS Employees
5 INNER JOIN Personnel AS Managers
6 ON Employees.ManagerID = Managers.EmployeeID
7 ORDER BY Employees.EmployeeID
Bases de Datos (II)– p. 10
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
Employee Name Manager Name
William Smith Natalie Lopez
Brenda Harper William Smith
Adam Petrie Natalie Lopez
Bases de Datos (II)– p. 11
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Inserción de datos
El comando INSERT INTO permite insertar datosen tablas.
1 INSERT INTO <table>
2 (<columnlist>)
3 VALUES
4 (<RowValues1>),
5 (<RowValues2>);
Bases de Datos (II)– p. 12
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplos
Customer First Last State
Id Name Name
1 William Smith IL
2 Natalie Lopez WI
3 Brenda Harper NV
1 INSERT INTO Customers
2 (FirstName, LastName, State)
3 VALUES
4 ( 'Virginia ', 'Jones ', 'OH '),
5 ( 'Clark ', 'Woodland ', 'CA ');
Bases de Datos (II)– p. 13
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Inserción de datos
La lista de columnas:No es obligatoria cuando los datos seproporcionan para todas las columnas y en elmismo orden.Sin embargo, es recomendable usar lista decolumnas a pesar de ello para evitar errorespotenciales.
Bases de Datos (II)– p. 14
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Inserción de datos
Columnas autoincremento:Son columnas cuyo valor se estableceautomáticamente mediante un contador. Sonútiles para asignar identificadores numéricos.En la inserción no se les asigna valor, para queel sistema lo haga automáticamente.
Otras columnas no especificadas: reciben su valorpor defecto o valor NULL.
Bases de Datos (II)– p. 15
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Inserción de datos
Es posible insertar en una tabla los resultados deuna consulta.
1 INSERT INTO Customers
2 (FirstName, LastName, State)
3 SELECT
4 Name1,
5 Name2,
6 State
7 FROM CustomerTransactions
8 WHERE State = 'RI ';
Bases de Datos (II)– p. 16
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Inserción de datos
También se puede hacer para una columnaconcreta.
1 INSERT INTO Paises
2 (nombre, capital)
3 VALUES
4 ( 'Suiza ',
5 (SELECT id
6 FROM Ciudades
7 WHERE nombre= 'Berna ')
8 );
Bases de Datos (II)– p. 17
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Eliminación de datos
DELETE FROM permite borrar filas en tablas.
1 DELETE
2 FROM <table>
3 WHERE <condition>;
Bases de Datos (II)– p. 18
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplos
1 DELETE
2 FROM Customers
3 WHERE State = 'RI ';
Bases de Datos (II)– p. 19
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Actualización de datos
UPDATE permite modificar datos en filas que yaexistan en una tabla.
Se indica qué columnas se van a cambiar, y elnuevo valor. El resto permanecerán sinmodificaciones.
1 UPDATE <table>
2 SET <Column1> = <Expression1>,
3 <Column2> = <Expression2>
4 ...repeat any number of times...
5 WHERE <condition>;
Bases de Datos (II)– p. 20
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplos
Customer First Last State
Id Name Name
1 William Smith IL
1 UPDATE Customers
2 SET FirstName = 'Will '
3 WHERE CustomerID = 1;
Customer First Last State
Id Name Name
1 Will Smith IL
Bases de Datos (II)– p. 21
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Actualización de datos
Es posible establecer valores en base a losresultados devueltos por una consulta SELECT.
1 UPDATE Customers23 SET Customers.State =4 (SELECT CustomerTransactions.State5 FROM CustomerTransactions6 WHERE CustomerTransactions.CustomerID = Customers.CustomerID),78 Customers.Zip =9 (SELECT CustomerTransactions.Zip
10 FROM CustomerTransactions11 WHERE CustomerTransactions.CustomerID = Customers.CustomerID)1213 WHERE EXISTS14 (SELECT *15 FROM CustomerTransactions16 WHERE CustomerTransactions.CustomerID = Customers.CustomerID)
Bases de Datos (II)– p. 22
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Creación de tablas
Se puede crear tablas con CREATE TABLE.
Al crear una tabla, se especifican aspectos como:Nombre de la tabla.Para cada columna:
Nombre y tipo de datosValor por defectoSi puede tomar valor NULLSi es un campo de auto-incremento.
Claves primarias y foráneas.
Índices.
Bases de Datos (II)– p. 23
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
1 CREATE TABLE Ciudades (
2 id INT NOT NULL auto_increment,
3 nombre VARCHAR(255) NOT NULL,
4 PRIMARY KEY (id)
5 );
6
7 CREATE TABLE Paises (
8 id INT NOT NULL auto_increment,
9 nombre VARCHAR(64) NOT NULL,
10 capital INT NOT NULL,
11 PRIMARY KEY (id),
12 CONSTRAINT FOREIGN KEY (capital)
13 REFERENCES Ciudades (id)
14 );
Bases de Datos (II)– p. 24
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Consistencia en actualizaciones de da-tos
Cuando se modifica o borra una fila, hay riesgo deintroducir inconsistencias en claves foráneas.
Por ejemplo, cuando se borra una fila a la cualse hace referencia desde otras filas de otrastablas.
Bases de Datos (II)– p. 25
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Consistencia en actualizaciones de da-tos
Es posible especificar qué debe hacer la base dedatos ante modificaciones o borrados que afectena la consistencia:
No realizar las modificaciones/borrados(RESTRICT o NO ACTION).Poner a NULL (SET NULL).Propagar (CASCADE).Establecer el valor por defecto (SET DEFAULT).
La acción por defecto en InnoDB (MySQL) esRESTRICT.
Bases de Datos (II)– p. 26
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Tipos de tablas en MySQL
En MySQL hay varios tipos de tablas que difierenen cómo se almacena la información y quéfuncionalidad ofrecen:
MyISAM: no transaccional, sin integridadreferencial.BerkeleyDB: transaccional, sin integridadreferencial.InnoDB: transaccional, con integridadreferencial.Otros: http://dev.mysql.com/doc/refman/5.5/en/
storage-engines.html
Bases de Datos (II)– p. 27
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
1 CREATE TABLE Ciudades (
2 id INT NOT NULL auto_increment,
3 nombre VARCHAR(255) NOT NULL,
4 PRIMARY KEY (id)
5 ) ENGINE=INNODB;
6
7 CREATE TABLE Paises (
8 id INT NOT NULL auto_increment,
9 nombre VARCHAR(64) NOT NULL,
10 capital INT NOT NULL,
11 PRIMARY KEY (id),
12 CONSTRAINT FOREIGN KEY (capital)
13 REFERENCES Ciudades (id)
14 ON UPDATE CASCADE
15 ) ENGINE=INNODB;
Bases de Datos (II)– p. 28
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
1 CREATE TABLE Ciudades (
2 id INT NOT NULL auto_increment,
3 nombre VARCHAR(255) NOT NULL,
4 PRIMARY KEY (id)
5 ) ENGINE=INNODB;
6
7 CREATE TABLE Paises (
8 id INT NOT NULL auto_increment,
9 nombre VARCHAR(64) NOT NULL,
10 capital INT NOT NULL,
11 PRIMARY KEY (id),
12 CONSTRAINT FOREIGN KEY (capital)
13 REFERENCES Ciudades (id)
14 ON UPDATE CASCADE
15 ON DELETE CASCADE
16 ) ENGINE=INNODB;
Bases de Datos (II)– p. 29
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Índices
Los índices son estructuras de datos construidaspara permitir una consulta más rápida de los datosde una tabla.
Se construyen sobre una columna, o sobre lacombinación de varias columnas.
Puede haber más de un índice por tabla.
Para la clave primaria de una tabla se construyeautomáticamente un índice.
Bases de Datos (II)– p. 30
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Índices
Ventajas:Localización más rápida de datos en la tabla enacceso aleatorio.Acceso a datos en orden de forma más rápida.
Desventajas:Coste adicional en el tiempo necesario parainsertar datos.Necesidad de más espacio de almacenamiento.
Bases de Datos (II)– p. 31
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Ejemplo
1 CREATE [UNIQUE] INDEX <index_name>
2 ON <table_name> (<col_1>, <col_2>,...);
3
4 -- Sintaxis alternativa para crear el índice con la
tabla:
5 CREATE TABLE <table_name> (
6 (...)
7 INDEX [<index_name>] (<col_1>, <col_2>,...)
8 );
Bases de Datos (II)– p. 32
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Otros comandos de gestión de tablas
Mostrar todas las tablas de una base de datos:SHOW TABLES;
Mostrar la estructura de una tabla: DESCRIBE<table_name>;
Eliminar una tabla: DROP TABLE
<table_name>;
Añadir, modificar o eliminar columnas en unatabla: ALTER TABLE ...;
Bases de Datos (II)– p. 33
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Transacciones en SQL
El gestor de bases de datos puede recibirsentencias desde varias conexiones concurrentes.
Una transacción es una secuencia de sentenciasSQL que deben ser tratadas como una unidad.
Deben cumplirse los principios ACID:Atomicidad.Consistencia.Aislamiento.Durabilidad.
Bases de Datos (II)– p. 34
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Transacciones en SQL
Atomicidad:O se ejecutan con éxito todas las sentencias dela transacción, o la base de datos debe volveral estado previo al inicio de la transacción.
Consistencia:Una vez finalizada la transacción, la base dedatos debe estar en un estado consistente (sedeben cumplir todas las restricciones deconsistencia de los datos).
Bases de Datos (II)– p. 35
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Transacciones en SQL
Aislamiento:Durante la ejecución de una transacción, suscambios no pueden ser visibles para el resto detransacciones.
Durabilidad:Una vez finaliza una transacción con éxito, sedebe garantizar que los cambios perdurenincluso antes situaciones de fallo en el sistema.
Bases de Datos (II)– p. 36
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Transacciones en SQL
Normalmente, cada comando SQL se ejecutacomo una transacción.
Se puede deshabilitar de forma temporal parauna transacción individual mediante START
TRANSACTION
Se puede deshabilitar en la sesión actualmediante SET AUTOCOMMIT=0
Para finalizar la transacción:Cancelándola: ROLLBACKConfirmándola: COMMIT
Bases de Datos (II)– p. 37
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Cerrojos en filas de tablas
Los gestores de bases de datos relacionalessuelen utilizar cerrojos para controlar el accesoconcurrente a tablas.
Principalmente, se usan dos tipos de cerrojos:Cerrojos S (shared)Cerrojos X (exclusive)
Bases de Datos (II)– p. 38
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Cerrojos en filas de tablas
La adquisición de un cerrojo S es compatible conotros cerrojos S sobre la misma fila.
La adquisición de un cerrojo X no es compatiblecon ningún otro cerrojo sobre la misma fila.
El intento de adquisición de un cerrojo bloquea laoperación hasta que sea posible. Si hayinterbloqueo con otra sesión, la sentencia falla.
Bases de Datos (II)– p. 39
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Cerrojos en filas de tablas
En modificaciones y eliminaciones se adquiere Xautomáticamente, hasta el final de la transacción.
Las lecturas se pueden realizar de tres formas:Consistent read : no se adquiere cerrojo.Adquisición de S: SELECT ... LOCK IN
SHARE MODE.Adquisición de X: SELECT ... FOR UPDATE.
Bases de Datos (II)– p. 40
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Niveles de aislamiento
En SQL se puede configurar el nivel deaislamiento entre transacciones concurrentes:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Niveles mayores implican mayor protección entransacciones concurrentes pero peor rendimiento.
Bases de Datos (II)– p. 41
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Niveles de aislamiento
1 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;2 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;3 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;4 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Bases de Datos (II)– p. 42
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Referencias
The Language of SQL, Larry Rockoff. CourseTechnology PTR (2010).
Accesible en Safari: http://proquest.
safaribooksonline.com/book/databases/sql/9781435457515
Capítulos: 12, 13, 17, 18.Gran parte de los ejemplos de estastransparencias proceden de este libro.
Bases de Datos (II)– p. 43
Edi
ted
with
emac
s+
LAT E
X+
pros
per
Referencias
MySQL Transactional and Locking Statements:http://dev.mysql.com/doc/refman/5.5/en/
sql-syntax-transactions.html
The InnoDB Transaction Model and Locking:http://dev.mysql.com/doc/refman/5.5/en/
innodb-transaction-model.html
Bases de Datos (II)– p. 44