7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
1/32
Captulo 5
Edicin de datos
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
2/32
NDICE CAPTULO 5 Insercin de registros
Clusula INSERT
Clusula REPLACEModificacin de registrosBorrado de registrosBorrados/Modificaciones e integridad referencial
TransaccionesPolticas de bloqueo de tablas Comandos de bloqueo Tipos de bloqueo Adquisicin/liberacin de un bloqueo
Bloqueos y transacciones Inserciones concurrentes
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
3/32
Insercin de datos: INSERT I
Formato bsico sentencia INSERT
Formato 1
I NSERT [ LOW_PRI ORI TY | DELAYED | HI GH_PRI ORI TY] [ I GNORE][ I NTO] t bl _name [ ( col _name, . . . ) ]
{VALUES | VALUE} ( {expr | DEFAULT}, . . . ) , ( . . . ) , . . .[ ON DUPLI CATE KEY UPDATE
col _name=expr [ , col _name=expr ] . . . ]
I NSERT [ I NTO] { nombr e_t abl a | nombr e_vi st a } [ ( l i st acol umnas) ]
{ VALUES ( expr esi on [ , expr esi on ] . . . ) |sent enci a_SELECT }
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
4/32
Insercin de datos: INSERT IIEjemplo insercin con formato 1:
-Insertar en la tabla jugadores a Antonio Martnez del equipo 6cuyo id de capitn es el 13, fecha de alta uno de enero de 2010,salario 45000, altura 2.16 y cuyo puesto es pvot.
I NSERT I NTO j ugadoresVALUES( 0, Ant oni o , Mar t i nez , pi vot , 13, 2010- 10-
01 , 45000, 6, 2. 16) ;
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
5/32
Insercin de datos: INSERT IIIFormato 2
I NSERT [ LOW_PRI ORI TY | DELAYED | HI GH_PRI ORI TY] [ I GNORE][ I NTO] t bl _nameSET col _name={expr | DEFAULT}, . . .
[ ON DUPLI CATE KEY UPDATE col _name=expr[ , col _name=expr ] . . . ]
Ejemplo insercin formato 2:
-Insertar un nuevo jugador con nombre Juan, id 16 y el resto devalores por defecto.
I NSERT I NTO j ugador SET i d=16, nombr e= J uan ;
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
6/32
Insercin de datos: INSERT IV
Formato 3
I NSERT [ LOW_PRI ORI TY | HI GH_PRI ORI TY] [ I GNORE]
[ I NTO] t bl _name [ ( col _name, . . . ) ]SELECT . . .[ ON DUPLI CATE KEY UPDATE
col _name=expr[ , col _name=expr ] . . . ]
Ejemplo insercin formato 3:
-Insertar en la tabla jugadores_histrico (con los mismos camposque la tabla jugadores) los datos de jugadores que se dieron de
alta antes del ao actual:
I NSERT I NTO j ugador es_hi st or i co SELECT * FROM j ugador esWHERE YEAR( f echa_al t a) < YEAR( cur_dat e( ) ) ;
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
7/32
Insercin de datos: REPLACE
Formato 1REPLACE [ LOW_PRI ORI TY | DELAYED]
[ I NTO] t bl _name[ PARTI TI ON ( par t i t i on_name, . . . ) ]
[ ( col _name, . . . ) ]{VALUES | VALUE} ( {expr | DEFAULT}, . . . ) , ( . . . ) , . . .
Formato 2REPLACE [ LOW_PRI ORI TY | DELAYED]
[ I NTO] t bl _name[ PARTI TI ON ( par t i t i on_name, . . . ) ]SET col _name={expr | DEFAULT}, . . .
Formato 3REPLACE [ LOW_PRI ORI TY | DELAYED]
[ I NTO] t bl _name[ PARTI TI ON ( par t i t i on_name, . . . ) ][ ( col _name, . . . ) ]
SELECT
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
8/32
Modificacin de datos: UPDATE I
Formato para una sola tabla
UPDATE [ LOW_PRI ORI TY] [ I GNORE] t abl e_r ef erenceSET col _name1={expr 1| DEFAULT} [ ,
col _name2={expr 2| DEFAULT}] . . .[ WHERE where_condi t i on][ ORDER BY . . . ][ LI MI T r ow_count ]
Formato para varias tablas
UPDATE [ LOW_PRI ORI TY] [ I GNORE] t abl e_r ef erencesSET col _name1={expr 1| DEFAULT} [ ,
col _name2={expr 2| DEFAULT}] . . .[ WHERE where_condi t i on]
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
9/32
Modificacin de datos: UPDATE II
Ejemplo actualizacin para una sola tabla
UPDATE [ LOW_PRI ORI TY] [ I GNORE] t abl e_r ef erence
-Sube el salario de los jugadores del equipo 5 en 1000 euros:
UPDATE j ugador es SET sal ar i o=sal ar i o+1000 WHEREequi po=5;
Ejemplo actualizacin en varias tablas
-Aade un campo id_capitan en la tabla equipo y actualiza losvalores segn la informacin de la tabla jugador:
UPDATE equi pos e J OI N j ugador j ONe. i d_equi po=j . i d_j ugador SET e. i d_capi t an=j . capi t an;
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
10/32
Borrado de registros: DELETE I
Formato para una sola tabla
DELETE [ LOW_PRI ORI TY] [ QUI CK] [ I GNORE] FROM t bl _name
[ WHERE where_condi t i on][ ORDER BY . . . ][ LI MI T r ow_count ]
usando TRUNCATE
TRUNCATE TABLE t bl _name
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
11/32
Borrado de registros: DELETE II
Formato 1 para varias tablas
DELETE [ LOW_PRI ORI TY] [ QUI CK] [ I GNORE]
t bl _name[ . *] [ , t bl _name[ . *] ] . . .FROM t abl e_r ef er ences[ WHERE where_condi t i on]
Formato 2 para varias tablas
DELETE [ LOW_PRI ORI TY] [ QUI CK] [ I GNORE]FROM t bl _name[ . *] [ , t bl _name[ . *] ] . . .USI NG t abl e_r ef er ences[ WHERE where_condi t i on]
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
12/32
Borrado de registros: DELETE III
Ejemplo borrado varias tablas
-Si disponemos de tres tablas t1, t2 y t3 podemos borrar datos dedos de ellas, t1 y t2, usando cualquiera de los siguientes
comandos:
DELETE t 1, t 2 FROM t 1, t 2, t 3 WHERE t 1. i d=t 2. i d ANDt 2. i d=t 3. i d;
DELETE FROM t 1, t 2 USI NG t 1, t 2, t 3 WHERE t 1. i d=t 2. i dAND t 2. i d=t 3. i d;
Ejemplo borrado con clusulas ORDER BY y LIMIT
-Eliminar todos los equipos que no hayan jugado partidos comolocales:DELETE equi po FROM equi po e LEFT J OI N par t i do ONe. i d_equi po=p. el ocal WHERE p. el ocal I S NULL;
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
13/32
Borrado/Modificacin e IntegridadReferencial I
Acciones posibles frente al borrado o modificacin de datoscon integridad referencial
NO ACTION
Se impide la eliminacin o modificacin de un registro en una tabla que tieneregistros relacionados (mediante alguno de sus tributos comunes) en otras tablas.
RESTRICT
Equivalente a NO ACTION.
SET NULL
Permite borrar o actualizar un registro en la tabla padre poniendo a NULL el campoo campos relacionados en la tabla hija (salvo que se hayan definido como NOT
NULL).
CASCADE
Es el valor ms habitual ya que propaga las modificaciones o borrados a los registrosde la tabla hija relacionados con los de la tabla padre.
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
14/32
Borrado/Modificacin e IntegridadReferencial II
Aspectos a considerar segn la operacin
INSERCION DE DATOS
Genera errores en carga masiva de datos
MODIFICACIN DE DATOS
Los cambios se suelen propagar en cascada. Si est a SET NULLpuede generar demasiados valores nulos
ELIMINACIN DE DATOS
Es peligrosa si est en modo CASCADE
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
15/32
TRANSACCIONES I
Propiedades ACID1. Atomicidad
Asegura que se realizan todas las operaciones o ninguna, no puede quedar a
medias
2. Consistencia o integridad
Asegura que solo se empieza lo que se puede acabar.
3. Aislamiento
Asegura que ninguna operacin afecta a otras pudiendo causar errores
4. Durabilidad
Asegura que una vez realizada la operacin, sta no podr cambiar y permanecernlos cambios
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
16/32
TRANSACCIONES II
Secuencia transaccin en MySQL
1. Iniciar una transaccin con el uso de la sentencia START TRANSACTION oBEGIN
2. Actualizar, insertar o eliminar registros en la base de datos
3. Si se quieren los cambios a la base de datos, completar la transaccin con el usode la sentencia COMMIT. nicamente cuando se procesa un COMMIT loscambios hechos por las consultas sern permanentes
4. Si sucede algn problema, podemos hacer uso de la sentencia ROLLBACK paracancelar los cambios que han sido realizados por las consultas que han sidoejecutadas hasta el momento
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
17/32
TRANSACCIONES III
Ejemplo transaccin en MySQL1. Comprobamos el estado de la variable autocommit.
SHOW VARI ABLES LI KE aut ocommi t ;
Si tiene el valor 1 la desactivamos con SET. Otra opcin es realizar los ejemplos con STARTTRANSACTION
2. Creamos una tabla, en la base de datos test, del tipo InnoDB e insertamos algunos datos
Para crear una tabla InnoDB, procedemos con el cdigo SQL estndar CREATE TABLE, pero
debemos especificar que se trata de una tabla del tipo InnoDB (TYPE= InnoDB).La tabla se llamar trantest y tendr un campo numrico. Primero activamos la base test con lainstruccin USE para despus crear la tabla e introducir algunos valores
USE t est ;
CREATE TABLE t r ant est ( campo I NT NOT NULL PRI MARY KEY) TYPE = I nnoDB;I NSERT I NTO t r ant est VALUES( 1) , ( 2) , ( 3) ;
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
18/32
TRANSACCIONES IV
Ejemplo transaccin en MySQL
3. Una vez cargada la tabla iniciamos una transaccin
BEGI N;
I NSERT I NTO t r ant est VALUES( 4) ;
Si en este momento ejecutamos un ROLLBACK, la transaccin no ser completaday los cambios realizados sobre la tabla no tendrn efectomysql > ROLLBACK;
Si ahora hacemos un SELECT para mostrar los datos de trantest veremos que nose ha llegado a producir ninguna insercin
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
19/32
TRANSACCIONES V
Ejemplo transaccin en MySQL
4. Ahora vamos a ver qu sucede si perdemos la conexin al servidor antes de que latransaccin sea completada
BEGI N;I NSERT I NTO t r ant est VALUES( 4) ;SELECT * FROM t r ant est ;+- - - - - - - +| campo |+- - - - - - - +
| 1 || 2 || 3 || 4 |
4 r ows i n set ( 0. 00 sec)
Cuando obtengamos de nuevo la conexin, podemos verificar que el registro no se insert, yaque la transaccin no fue completada
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
20/32
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
21/32
TRANSACCIONES VII
Otros comandos transacciones
SAVEPOINT id
Es una instruccin que permite nombrar cierto paso en un conjunto de instrucciones
de una transaccin
ROLLBACK TO SAVEPOINT id
Permite deshacer el conjunto de operaciones realizadas a partir del identificador de
savepoint
RELEASE SAVEPOINT id
Elimina o libera el savepoint creado
Cualquier operacin COMMIT o ROLLBACK sin argumentos eliminar todos los
savepoints creados
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
22/32
Comandos bloqueo de tablas
Bloqueo de todas las tablas
LOCK TABLES WI TH READ LOCK
Desbloqueo de tablasUNLOCK TABLES
Sintaxis General
LOCK TABLESt bl _name [ [ AS] al i as] l ock_t ype[ , t bl _name [ [ AS] al i as] l ock_t ype] . . .
l ock_t ype:
READ [ LOCAL]| [ LOW_PRI ORI TY] WRI TE
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
23/32
Tipos de bloqueo
Bloqueo de lectura
El cliente que obtiene el bloqueo puede leer pero ni l ni nadie puedemodificar el registro bloqueado
READ [ LOCAL]
Bloqueo escritura
Solo el usuario que obtiene el bloqueo puede leer y escribir sobre elregistro bloqueado
[ LOW PRI ORI TY] WRI TE
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
24/32
Adquisicin/liberacin de un bloqueo
Cuando hay varias solicitudes de bloqueo tiene prioridad el deescritura
Poltica de bloqueos seguida por MySQL
1. Ordena internamente las tablas a bloquear
2. Si una tabla debe bloquearse para lectura y escritura sita la solicitudde bloqueo de escritura en primer lugar
3. Se bloquea cada tabla hasta que la sesin obtiene todos sus bloqueos
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
25/32
Bloqueos y transacciones I
Variable autocommit
-Con valor=1
Est activada lo que implica que cada comando SQL es en si mismo unatransaccinEl comando START TRANSACTION desactiva este comportamiento
-Con valor =0
Se desactiva lo que hace que slo los comandos COMMIT y ROLLBACK
confirmen o revoquen una sentencia SQL
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
26/32
Bloqueos y transacciones II
Tipos de bloqueo en InnoDB
-Bloqueo compartido: tipo s
Varias transacciones adquieren bloqueos sobre las mismas filas. Ninguna puedemodificarlas hasta que se liberen todos los bloqueos
-Bloqueo exclusivo: tipo x
Permite adquirir bloqueos para modificacin o borradoEn este caso lastransacciones que deseen adquirir un bloqueo exclusivo debern esperar a quese libere el bloqueo sobre las filas afectadas
-Intencin de bloqueo: permite evitar conflictos (deadlocks) entre varias
sesiones concurrentes
De lectura: ISDe escritura: IX
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
27/32
Bloqueos y transacciones III
Ejemplo bloqueo IS
Supongamos que queremos agregar un registro en la tabla jugador peroasegurndonos de que existe su equipo (suponemos adems que no hay
integridad referencial).Para asegurar que existe el equipo haramos una consulta sobre equipopara comprobarlo y despus insertaramos el jugador. Sin embargo nadienos asegura que entre medio se elimine dicho equipo. Para evitarloharamos la consulta de este modo:
SELECT * FROM equi po WHERE nombr e= i d_equi po LOCK I N SHAREMODE;
Con lo que conseguimos un bloqueo de lectura de modo que mientras nose confirme o deshaga la transaccin nadie podr modificar los datos deequipo.
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
28/32
Bloqueos y transacciones IV
Ejemplo bloqueo IX
Si queremos aadir un nuevo jugador con un id determinado por el valorde un contador almacenado en la tabla contador. Si hacemos un bloqueo
de lectura es posible que ms de una transaccin acceda al mismo valorde contador para dos jugadores distintos de manera que se producir unerror al ser un campo clave y no poder repetirse. Para evitarlo usaramosun bloque de escritura o exclusivo para despus incrementar el contadorcon la siguiente orden:
SELECT num_j ugador es FROM cont ador FOR UPDATE;UPDATE num_j ugador es SET num_j ugador es= num_j ugador es+1;
Ahora cualquier transaccin que quiera leer el campo num_jugadoresdeber esperar a que se libere el bloqueo exclusivo y por tanto obtendrel valor correcto.
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
29/32
Bloqueos y transacciones V
Niveles de aislamiento1. READ UNCOMITTED
Las lecturas se realizan sin bloqueo de manera que podemos estar leyendo un dato que ya hasido modificado por otra transaccin. Es lo que se denomina lectura inconsistente o sucia
2. READ COMMITED
En este tipo de lectura en cada instruccin se usan los valores ms recientes y no los decomienzo del bloqueo. Es decir, si en alguna instruccin se modifica un valor que despus seusar en otra, el valor ser el modificado y no el original cuando se adquiri el bloqueo
3. REPETEABLE READ
Es el valor por defecto. En este caso se obtiene el valor establecido al comienzo de latransaccin. Es decir, aunque durante la transaccin los valores cambien, se tendrn en cuentasiempre los del comienzo
4. SERIALIZABLE
Es como la anterior con la diferencia de que ahora de manera interna se convierten los SELECTen SELECT LOCK IN SHARE MODE si autocommit est desactivado
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
30/32
Bloqueos y transacciones VI
Modificacin niveles aislamiento
Comando SET TRANSACTIONSET [ GLOBAL | SESSI ON] TRANSACTI ON I SOLATI ON LEVEL {
READ UNCOMMI TTED | READ COMMI TTED | REPEATABLE READ| SERI ALI ZABLE }
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
31/32
Bloqueos y transacciones VII
Inserciones concurrentes
Permiten la lectura simultnea de datosComportamiento controlado por una variable de sistema llamadaconcurrent_insert cuyos valores posibles son:
AUTO 1 se activan las inserciones concurrentes
0 se impiden las inserciones concurrentes
2 se permiten las inserciones incluso aunque haya filasborradas entre los datos de la tabla
7/25/2019 Bases_asir_cap5 Edicin de Datos Luis Hueso Ibez
32/32
FIN CAPTULO 5
Top Related