combinaciones avanzadas

45
NOTAS CURSO “MYSQL BÁSICO” MYSQL es la base de datos de código abierto más popular del mundo. Código abierto significa que todo el mundo puede acceder al código fuente, es decir, al código de  programación de MYSQL. Todo el mundo puede contribuir para incluir elementos, arreglar  problemas, realizar mejoras o sugerir optimizaciones. Michael “MONTY” Widenius es el creador de MYSQL. Actualmente compite con sistemas RDBMS propietarios conocidos, como ORACLE, SQL SERVER y DB2. MYSQL es un sistema de administración de bases de datos relacional (RDBMS) MYSQL utiliza el lenguaje de consulta estructurado (SQL). Para ejecutar una conexión >mysql -h nombre del anfitrión -u nombre de usuario -pcontraseña basededatos En el caso de –p sin espacio en la contraseña. Para dar permisos a un usuario sobre la base de datos >GRANT ALL ON escuela.* to rios IDENTIFIED BY ‘rios’; Para codificar el password de un Usuario >INSERT INTO tabla(user,password) VALUES(‘rios’,PASSWORD(‘r ios’)) Para desconectar basta con QUIT, EXIT o Control –D Para crear una base de datos >CREATE DATABASE nombre_base; Para seleccionar una base de datos >USE base_datos; Para crear una tabla >CREATE TABLE nombre_tabla(employee_number INT, surname VARCHAR(40), first_Name VARCHAR(30), commission TINYINT); Para Mostrar una tabla >SHOW TABLES; ó SHOW TABLES FROM nombre_base; Para mostrar la estructura de una tabla >DESCRIBE tabla; ó DESC tabla; Para insertar datos en una tabla >INSERT INTO tabla(nombre_col tipo_col,[]) VALUES(valor1,valor2....valor n); ó >INSERT INTO tabla VALUES(valor1,valor2...valor N); ó

Upload: alejandra-medel

Post on 06-Jul-2015

429 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 1/45

 

NOTAS CURSO “MYSQL BÁSICO”

MYSQL es la base de datos de código abierto más popular del mundo. Códigoabierto significa que todo el mundo puede acceder al código fuente, es decir, al código de programación de MYSQL. Todo el mundo puede contribuir para incluir elementos, arreglar  problemas, realizar mejoras o sugerir optimizaciones.

Michael “MONTY” Widenius es el creador de MYSQL. Actualmente compite consistemas RDBMS propietarios conocidos, como ORACLE, SQL SERVER y DB2.

MYSQL es un sistema de administración de bases de datos relacional (RDBMS)MYSQL utiliza el lenguaje de consulta estructurado (SQL).

• Para ejecutar una conexión>mysql -h nombre del anfitrión -u nombre de usuario

-pcontraseña basededatosEn el caso de –p sin espacio en la contraseña.

• Para dar permisos a un usuario sobre la base de datos>GRANT ALL ON escuela.* to rios IDENTIFIED BY ‘rios’;

• Para codificar el password de un Usuario>INSERT INTO tabla(user,password) VALUES(‘rios’,PASSWORD(‘rios’))

• Para desconectar basta con QUIT, EXIT o Control –D

• Para crear una base de datos>CREATE DATABASE nombre_base;

• Para seleccionar una base de datos>USE base_datos;

• Para crear una tabla>CREATE TABLE nombre_tabla(employee_number INT,

surname VARCHAR(40), first_Name VARCHAR(30), commission TINYINT);

• Para Mostrar una tabla>SHOW TABLES; ó SHOW TABLES FROM nombre_base;

• Para mostrar la estructura de una tabla>DESCRIBE tabla; ó DESC tabla;

• Para insertar datos en una tabla>INSERT INTO tabla(nombre_col tipo_col,[]) VALUES(valor1,valor2....valor n); ó>INSERT INTO tabla VALUES(valor1,valor2...valor N); ó

Page 2: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 2/45

 

>INSERT INTO tabla(nombre_col tipo_col,[]) VALUES(valor1,valor2..Vlor n),(valor1, valor2,......valor N);

• Inserción desde un archivo de texto con grandes cantidades de datos conLOAD DATA

>LOAD DATA LOCAL INFILE “Sales_rep.sql” INTO TABLE sales_rep;y el formato es:1\tRive\tSol\t101\tGordimer\tCharlene\t151\tRive\tMichael\t101\tRove\tSole\t10

• Correspondencia de Patrones>SELECT * FROM sales_rep WHERE surname LIKE ‘Sero%’;El símbolo % se trata de un comodín que significa 0 o más caracteres.

Ordenación>SELECT * FROM Sales_rep ORDER BY surname, first_name; ó>SELECT * FROM sales_rep ORDER BY commission DESC;

• Limitación de número de resultados>SELECT first_name, surname, comisión FROM sales_rep ORDER BY

commission DESC LIMIT 1,1;

• Devolución del valor máximo con MAX()>SELECT MAX(commission) FROM sales_rep;

• Recuperación de registros distintos>SELECT DISTINCT surname FROM sales_rep ORDER BY surname;

• Cómo contar campos

>SELECT COUNT(surname) FROM sales_rep; ó>SELECT COUNT(DISTINCT surname) FROM sales_rep;

• Realización de cálculos en una consulta>SELECT first_name, surname, comisión+1 FROM sales_rep;

• Eliminación de registros>DELETE FROM sales_rep WHERE employee_number=5;

• Cómo cambiar los registros de una tabla>UPDATE sales_rep SET comisión=12 WHERE employee_numbre=1;

• Cómo agregar una columna>ALTER TABLE sales_rep ADD date_joined DATE; ó>ALTER TABLE sales_rep ADD year_born YEAR;

Page 3: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 3/45

 

• Modificación de una definición de Columna>ALTER TABLE sales_rep CHANGE year_born birthday DATE;

• Para cambiar la definición de una columna y no el nombre

>ALTER TABLE nombre_tabla CHANGE antiguo_nombre antiguo_nombrenueva_definición_de_columna;

• Si se cambia la definición de Columna>ALTER TABLE nombre_tabla MODIFY antiguo_nombre  nueva_definición_de_columna;

• Cómo cambiar el nombre de una tabla>ALTER TABLE sales_rep RENAME cashflow;

• Cómo eliminar la columna

>ALTER TABLE sales_rep DROP enhancement_value;

• Cómo usar LIKE en equivalencias de patrón de SQLCarácter Descripción% cualquier número de caracteres  _ un carácter 

• Expresiones RegularesLas expresiones regulares permiten realizar comparaciones complejas en MYSQL.

Carácter Descripción* Equivale a cero o varias instancias de la cadena situadas por delante

+ Equivale a una o una instancia situadas por delante? Equivale a un solo carácter  [xyz] Equivale a cualquier x, y o z (los caracteres incluidos dentro de los corchetes)[A-Z] Equivale a cualquier letra mayúscula[a-z] Equivale a cualquier letra minúscula[0-9] Equivale a cualquier digito^ Fija la equivalencia desde el principio$ Fija la correspondencia hasta el final| Separa cadenas de una expresión regular  {n,m} La cadena debe tener lugar al menos n veces, pero no más

{n} La cadena debe tener lugar n veces exactamente{n,| La cadena debe tener lugar n veces al menos

MYSQL permite devolver las fechas en un formato especial, en lugar de utilizar elformato estándar AAAA-MM-DD. Para devolver los cumpleaños de toda la plantilla enformato MM/DD/AAAA, utilice la función DATE_FORMAT().

Page 4: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 4/45

 

>SELECT DATE FORMAT(date_joined, ‘%m/%d/%Y’) FROM sales_rep WHEREemployee_number=1;

• Cómo especificar el formato de fecha

%m devuelve el mes(01-12)%d devuelve el día(01-31)%Y devuelve el año en formato de 4 dígitos%y devuelve el año en formato de 2 dígitos%M devuelve el nombre del mes%w devuelve el nombre del día de la semana%e devuelve el día(1-31)%a devuelve el nombre del día de la semana en formato abreviado.%D es el día del mes con sufijo%b es el nombre del mes abreviado

>SELECT DATE_FORMAT(date_joined,’%a %D %b, %Y’) FROMsales_rep WHERE employee_number=1;

• Recuperación de la fecha y hora actual>SELECT NOW(), CURRENT_DATE();>SELECT YEAR(birthday) FROM sales_rep;>SELECT MONTH(birthday), DAYOFMONTH(birthday) FROM sales_rep;

• Cómo aplicar un Nuevo encabezado a una columna con AS>SELECT surname, first_name, MONTH(birthday) AS month,DAYOFMONTH(birthday) AS day FROM sales_rep ORDER BY month;

• Combinación de Columnas con CONCAT>SELECT CONCAT(first_name,’ ’, surname) AS name, MONTH(birthday) AS

month, DAYOFMONTH(birthday) AS day FROM sales_rep ORDER BY month;

• Cómo buscar el día del año>SELECT DAYOFYEAR(date_joined) FROM sales_rep WHEREemployee_number=1;

• Realización de cálculos con fechas>SELECT YEAR(NOW())-YEAR(birthday) FROM sales_rep;

• Función RIGHTLa función RIGHT devuelve los valores de un campo de derecha a izquierda>SELECT RIGHT(CURRENT_DATE,5) FROM sales_rep

Agrupación de una consulta

Page 5: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 5/45

 

Cuando se trabaja con columnas que nos devuelven valores escalares y queremosregresar en el mismo query otros campos no escalares, es necesario agrupar el query por los campos no escalares.

>SELECT sales_rep, SUM(value) as sum FROM sales GROUP BY sales_rep ORDER 

BY sum desc;TIPOS DE DATOS

• Tipos de columnas numéricoUNSIGNED : no permite el uso de números negativos.ZEROFILL : rellena el valor con cero.

TINYINT [(M)][UNSIGNED] [ZEROFILL]

BITBOOL

SMALLINT [(M)][UNSIGNED] [ZEROFILL]INT[(M)] [UNSIGNED][ZEROFILL]

FLOAT [(M,D)][UNSIGNED] [ZEROFILL]

Tipos de columnas de cadenaCHAR(M) [BINARY]VARCHAR(M) [BINARY]

BLOBTEXT

ENUM(‘valor1’,’valor2’, ...)SET (‘valor1’,’valor2’, ...)

• Tipos de columnas de fecha y hora

DATETIMEDATETIMEYEAR 

OPCIONES DE MySQL

• Si activamos -E>SELECT * FROM CUSTOMER\G;

Page 6: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 6/45

 

• Si activamos -i evita errores de espacio>SELECT MAX_(value) FROM sales;

• La opción -H o -html lo saca en html>SELECT * FROM customer;

• Análisis de los distintos tipos de tablas

Existen 2 tipos de tablas de transición segura (InnoDB y BDB). El resto (ISAM, M yISAM, MERGE y HEAP) no son de transacción segura.

• Tablas ISAMLas tablas ISAM (método de acceso secuencial indexado) era el estándar antiguo deMySQL. Fueron sustituidas pos las tablas MyISAM a partir de 3.23 pero se conservanhasta 4.1.

La diferencia es la tabla MyISAM utiliza menos recursos del sistema.

Las tablas ISAM tienen las siguientes características:

• ISAM almacena los archivos de datos con una extensión.• ISD y el archivo de índice con .ISM

Las tablas no son archivos binarios transportables entre diferentes equipos.

• Como convertir una tabla ISAM a MyISAM>ALTER TABLE nombre_de_tabla TYPE = MyISAM;

• Tablas MyISAM Los índices son más pequeños sobre una selección; pero consumen más recursos sobreinserción y borrado. Los archivos de datos se guardan con la extensión .MYD y la deíndice es .MYI.

Existen 3 subtipos de tablas MyISAM: estáticas, dinamicas y comprimidas.

Al crear las tablas, MySQL escoge entre el tipo dinámico y el estático. El valor  predeterminado son las estáticas y se crean s no incluyen columnas VARCHAR, BLOBo TEXT, de lo contrario la tabla se convierte en dinámica.

• Tablas estáticas (tablas de longitud fija)Características:

o Son muy rápidaso Son fáciles de almacenar en cachéo Resultan sencillas de reconstruir o Requieren menos espacio en disco

• Tablas dinámicas

Page 7: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 7/45

 

Características:o Ocupan menos espacio en discoo  No son sencillas de reconstruir tras un falloo Cada nuevo registro consta de un encabezado, lo que indica que columnas

de cadena están vacías y que columnas numérica tienen cero.

• Tablas comprimidasSon de solo lectura, son ideales para su uso con datos comprimidos que no cambien(que sólo se puedan leer y no escribir), y donde no exista mucho espacio disponible,CD_ROM.

EJEMPLO: Usando el comando

myisampack [opciones] nombre_del_archivo

[opciones]

o w, -wait Si se está utilizando la tabla se espera y vuelve a intentarlo.o v, -version Muestra información sobre la versión .o v, -verbose Modo detallado.o f, -force Crea un archivo temporal .TMD.o s, -silent Solo muestra errores.o t, -test No comprime, la tabla, sólo prueba el proceso de compresión.

C:\MySQL\bin> myisampack –v –f ..\data\firstdb\sales_rep

C:\MySQL\bin> myisampack –unpack ..\data\firstdb\sales_rep

• Tablas MERGEFusión de dos tablas iguales en una.

• Tablas HEAPSon más rápidas porque se almacenan en memoria.

>CREATE TABLE sales_rep(employe_number int(11) default NULLsurname varchar(40) default NULLfirst_name varchar(30) default NULLcommission tinyint(4) default NULLdate_joined date default NULLbirthday date default NULL

) TYPE = MyISAM;

Page 8: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 8/45

 

>CREATE TABLE heaptest TYPE = HEAPSELECT first_name, surname FROM sales_rep;

Características:•

 No admiten AUTO_INCREMENT•  No admiten BLOB o TEXT•  No admiten el uso de índices en una columna NULL•  No se puede ORDER BY

Buscar sobre las tablas MERGE, COMPRIMIDAS, y las INNODB y DBD.

 

Combinaciones avanzadas

Las combinaciones avanzadas son una parte fundamental de una buena B.D.

CREATE TABLE customer(id int(11) default NULL,first_name varchar(30) default NULL,surname varchar(40) default NULL)TYPE=MyISAM;

 INSERT INTO customer VALUES(1,'Yvonne','Clegg');INSERT INTO customer VALUES(2,'Johnny','Boris');INSERT INTO customer VALUES(3,'Winston','Powers');INSERT INTO customer VALUES(4,'Patricia','Mankunku'); CREATE TABLE sales(code int(11) default NULL,sales_rep int(11) default NULL,customer int(11) default NULL,value int(11) default NULL

)TYPE=MyISAM; INSERT INTO sales VALUES(1,1,1,2000);INSERT INTO sales VALUES(2,4,3,250);INSERT INTO sales VALUES(3,2,3,500);INSERT INTO sales VALUES(4,1,4,450); CREATE TABLE sales_rep(employee_number int(11) default NULL,surname varchar(40) default NULL,

Page 9: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 9/45

 

first_name varchar(30) default NULL,commission tinyint(4) default NULL,date_joined date default NULL,birthday date default NULL

)TYPE=MyISAM;

 INSERT INTO sales_rep VALUES(1,'Rive','Sol',10,'2000-02-15','1976-03-18');INSERT INTO sales_rep VALUES(2,'Gordimer','Charlene',15,'1998-07-09','1958-11-30');INSERT INTO sales_rep VALUES(3,'Serote','Mike',10,'2001-05-14','1971-06-18');INSERT INTO sales_rep VALUES(4,'Rive','Morgane',10,'2002-11-23','1982-01-04');

Comencemos por una combinación básica:mysql> SELECT sales_rep, customer, value, first_name, surname FROM sales, sales_repWHERE code=1 AND sales_rep.employee_number=sales.sales_rep;

Una combinación más complejaMysql> SELECT sales_rep.first_name, sales_rep.surname,

value, customer.first_name, customer.first_name, customer.surname FROMsales, sales_rep,customer WHERE sales_rep.employee_number =sales.sales_rep AND customer.id= sales.customer;

Combinaciones InternasLas combinaciones internas son otra forma de describir el primer tipo de combinaciónaprendido. Las siguientes consultas son idénticas:

mysql> SELECT first_name, surname, value FROM customer, sales WHEREid=customer;mysql> SELECT first_name, surname, value FROM customer INNER JOIN sales

ON id=customer;

Combinaciones por la izquierda(o combinaciones externas por la izquierda)Imagine que hemos hecho otra venta, con la diferencia de que esta vez el pago se harealizado al contado y el cliente se ha marchado con los artículos sin que hayamos tomadosus datos. No hay problema porque todavía podemos agregarlos a la tabla sales utilizandoun valor NULL para el cliente;

mysql>INSERT INTO sales(code,sales_rep,customer,value) VALUES (7,2,NULL,670);

ahora ejecutemos la instrucción anterior Mysql> SELECT sales_rep.first_name, sales_rep.surname,

value, customer.first_name, customer.first_name, customer.surname FROMsales, sales_rep,customer WHERE sales_rep.employee_number =sales.sales_rep AND customer.id= sales.customer;

¿Qué ocurre? ¿Dónde está la nueva venta? El problema está en que como el cliente es NULL en la tabla sales, la condición de combinación no se cumple. La solución es

Page 10: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 10/45

 

implementar una combinación externa.Una combinación externa por la izquierda devuelvetodas las filas coincidentes de la tabla izquierda, independientemente de si existe una filacorrespondiente en la tabla de la derecha. La sintaxis es la siguiente:

SELECT campo1, campo2 FROM tabla1 LEFT JOIN tabla2 ON campo1=campo2;

mysql>SELECT first_name, surname, value FROM sales LEFT JOIN customer ONid=customer;mysql>SELECT first_name, surname, value FROM customer LEFT JOINsales ON id=customer;mysql>SELECT sales_rep.first_name, sales_rep.surname, value, customer.first_name,customer.surname FROM sales LEFT JOIN sales_rep ON sales_rep.employee_number=sales.sales_rep LEFT JOIN customer ON customer.id=sales.customer;

Combinaciones por la derecha(o combinaciones externas por la derecha)

mysql>SELECT first_name, surname,value FROM customer RIGHT JOINsales ON id=customer;

Combinaciones externas completas

SELECT campo1,campo2 FROM tabla1 FULL OUTER JOIN tabla2

Combinaciones naturals y la palabra clave USING

El campo id de la tabla customer y el campo customer de la tabla sales están relacionados.Si les asignáramos el mismo nombre, podríamos utilizar varios métodos de SQL que

 permiten que las instrucciones JOIN resulten más sencillas de manejar.mysql>ALTER TABLE sales CHANGE customer id INT;

ahora utilizando NATURAL

mysql>SELECT first_name,surname, value FROM customer NATURAL JOINsales;

sería equivalente a:

mysql>SELECT first_name,surname, value FROM customer INNER JOINsales ON customer.id=sales.id;

las combinaciones naturales también pueden ser por la izquierda o por la derecha. Lassiguientes dos instrucciones son idénticas:

mysql>SELECT first_name,surname, value FROM customer LEFT JOINsales ON customer.id=sales.id;

mysql>SELECT first_name,surname, value FROM customer NATURALLEFT JOIN sales;

Page 11: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 11/45

 

La palabra clave USING brinda un mayor control sobre una combinación natural. Si dostablas constan de varios campos idénticos, esta palabra clave permite especificar aquellosque se utilizarán como condiciones de comparaciónSELECT * FROM A LEFT JOIN B USING (a,b,c,d)

SELECT * FROM A NATURAL LEFT JOIN B

• Combinación de resultados con UNION

Combina los resultados de diferentes instrucciones SELECT. Cada instrucción debe constar delmismo número de columnas

>SELECT id, first_name, surname FROM old_customer UNION SELECT id, first_name,surname FROM customer;

no devuelve resultados duplicados. Se puede modificar este comportamiento especificandocon la palabara ALL:

>SELECT id FROM customer UNION ALL SELECT id FROM sales;

Subselecciones

Muchas consultas realizan una operación de selección dentro de una selección. Está programada para la versión 4.1.

Cómo escribir subselecciones como combinaciones:>SELECT first_name, surname FROM sales_rep WHERE sales_rep.employee_number 

IN(SELECT id FROM sales WHERE value>1000);

Ahora con una combinación :

>SELECT DISTINCT first_name, surname FROM sales_rep INNER JOIN sales ONemployee_number=id WHERE value>1000;

o alternativamente

>SELECT DISTINCT first_name, surname FROM sales_rep, sales WHEREsales.id=sales_rep.employee_number AND value>1000;

Para los que no hayan hecho una venta :

>SELECT first_name, surname FROM sales_rep WHERE employee_number NOT IN(SELECT DISTINCT id FROM sales);

ó

Page 12: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 12/45

 

>SELECT DISTINCT first_name, surname FROM sales_rep LEFT JOIN sales ONsales_rep=employee number WHERE sales _ rep is NULL;

Page 13: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 13/45

 

• Cómo agregar registros a una tabla desde otras tablas con INSERT SELECT

>SELECT first_name, surname,SUM(value) FROM sales NATURAL JOIN  customer GROUP BY first_name, surname;>CREATE TABLE customer_sales_values(

first_name VARCHAR(30), surname VARCHAR(40), value INT);>INSERT INTO customer_sales_values(first_name, surname, value)SELECT first_name, surname, SUM(value) FROM sales NATURAL JOINCustomer GROUP BY first_name, surname;

>SELECT * FROM customer_sales_values;

• Más sobre la agregación de registros

INSERT también permite una sintaxis similar a la utilizada por una instrucciónUPDATE.En lugar de la siguiente secuencia:

>INSERT INTO customer_sales_values(first_name, surname, value)VALUES(‘Charles’,’Dube’,0);

Podríamos utilizar esta otra:

>INSERT INTO customer_sales_values SET first_name=’Charles’, surname=’Dube’, value=0;

también se puede llevar a cabo una forma limitada de cálculo al agregar registros. Parademostrarlo, agregue otro campo sobre la tabla customer_sales_value:

>ALTER TABLE customer_sales_values ADD value2 INT;

A continuación, puede insertar elementos dentro de esta tabla y completar value2 con eldoble valor:

>INSERT INTO customer_sales_values(first_name, surname, value,value2)VALUES(‘Gladys’,’Malherbe’,5,value*2);

Este registró contiene los siguientes elementos:

>SELECT * FROM customer_sales_values WHEREfirst_name=’Gladys’;

Más sobre cómo eliminar registros (DELETE y TRUNCATE)

La instrucción DELETE trabaja un poco más lento para tablas de grandes tamaños. Asíque se recomienda usar TRUNCATE para estos casos.

>DELETE FROM customer_sales_values;

Page 14: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 14/45

 

La forma más rápida de eliminar estos valores consiste en utilizar TRUNCATE :

>INSERT INTO customer_sales_values (first_name, surname, value,value2) VALUES(‘Johnny’, ’chaka-chaka’, 500, NULL), (‘Patricia’,‘Mankunku’, 450, NULL), (‘Winston’, ‘Powers’, 750, NULL), (‘Yvonne’,

‘Clegg’, 5800, NULL), (‘Charles’, ‘Dube’, 0, NULL);>TRUNCATE customer_sales_values;

La única diferencia además de la velocidad es que TRUNCATE elimina el conjuntocompleto sin contar los elementos eliminados y DELETE nos informa el número defilas que se han eliminado.

Variable de usuario

MySQL consta de una función que permite almacenar valores como variables

temporales para poder utilizarlas en una instrucción posterior. Resultan útiles en la líneade comandos. El valor de la variable se establece con la instrucción SET o en unainstrucción SELECT con : = .

>SELECT @avg:=AVG(commission) FROM sales_rep;>SELECT surname, first_name FROM sales_rep WHERE commission>@avg;

El símbolo @ indica que es una variable de MySQL.

>SET @result=22/7*33.23;>SELECT @result;

Sin embargo no se pueden utilizar para sustituir parte de la consulta, o reemplazar elnombre de una tabla.

>SET @t=’sales’;>SELECT * FROM @t;

Las variables de usuario se establecen en un subproceso dado (o conexión a unservidor ) y ningún otro proceso puede acceder a ellas (es decir trabaja por sesión).

Ejecución de instrucciones SQL almacenadas en archivos

A menudo se suelen guardar grupos de instrucciones SQL en un archivo para volver autilizarlas. Esta operación se conoce como ejecutar MySQL en modo de procesamiento por lotes. Cree un archivo de texto test.sql y agrega las siguientes líneas.

>INSERT INTO customer(id, first_name, surname) VALUES(5, ‘Francois’, ‘Papo’),(6, ‘Neil’, ‘Beneke’);

C:\MySQL\bin\mysql firstdb <C:\MySQL\test.sql

Page 15: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 15/45

 

Para obligar a MySQL continuar procesando la acción aunque existan errores loharemos con la opción force:

C:\MySQL\bin\mysql -f firstdb <C:\MySQL\test.sql

Redireccionamiento de la salida hacia un archivoEscriba en el archivo test.sql la siguiente instrucción :

>DELETE FROM customer WHERE id>=6;>INSERT INTO customer (id, first_name, surname) VALUES(6, ‘Neil’, ‘Beneke’),

(7, ‘Winnie’, ‘Dlamini’);>SELECT * FROM customer;

Ahora escriba la siguiente línea :

C:\MYSQL\bin\mysql -t firstdb < test.sql > test_output.txtC:\Archivos de programa\MySQL\MySQL Server 4.1\bin>mysql –u root –H –  padministrador <entrada.sql >salida.html

Cómo usar archivos desde la línea de comandos

Se puede utilizar el comando SOURCE.

>SOURCE test.sql

Transacciones y bloqueos

Con todos los tipos de tablas pueden utilizar la función de bloqueo, pero solos los tiposInnoDB y BDB disponen de funciones transaccionales integradas.

Las transacciones en las tablas InnoDB

Se pueden utilizar en transacciones bancarias.

>CREATE TABLE innotest (f1 INT, f2 CHAR(10), INDEX (f1))TYPE=InnoDB;

>INSERT INTO innotest(f1) VALUES(1);>SELECT f1 FROM innotest;

A continuación procederemos a empaquetar una consulta con las instruccionesBEGIN/COMMIT :

>BEGIN;>INSERT INTO innotest(f1) VALUES(2);>SELECT f1 FROM innotest;

Ahora invertimos con el comando ROLLBACK:

Page 16: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 16/45

 

>ROLLBACK;>SELECT f1 FROM innotest;Lecturas coherentes

De manera predeterminada, las tablas InnoDB realizan una lectura coherente. Esto

significa que al realizar una consulta de selección, MySQL devuelve los valores presentes de la base de datos hasta la última transacción completa. Si en el momento derealizar la consulta existe alguna transacción en progreso, los resultados de lasinstrucciones UPDATE o INSERT no se reflejarán, con una excepción: la transacciónabierta puede modificarse (puEde que haya observado que al realizar la consultaBEGIN-INSERT-SELECT, se visualizó el resultado insertado). Para poder verlo,necesita tener dos ventanas abiertas y estar conectado la base de datos. En primer lugar agregue un registro desde una transacción en la ventana 1:

>BEGIN;>INSERT INTO innotest(f1) VALUES(3);

Pase a la ventana 2:

>SELECT f1 FROM innotest;El 3 que hemos insertado no se devuelve porque forma parte de una transacciónincompleta.

Regrese a la ventana 1 :

>COMMIT;

En la ventana 2, la consulta reflejará la transacción completada:>SELECT f1 FROM innotest;

Lectura de bloqueos para actualizaciones

Las lecturas coherentes no siempre resultan adecuadas. Por ejemplo, ¿qué ocurría sivarios usuarios están intentando agregar un nuevo registro en una tabla innotest ? Cadanuevo registro inserta un número ascendente exclusivo. Como en este ejemplo: elcampo f1 no es la clave principal o un campo de incremento automático, por lo quenada impide que se cree un registro duplicado. Sin embargo, no queremos que estoocurra. Lo que desearíamos es leer el valor actual de f1 e insertar un nuevo valor,incrementado en una unidad. Pero esta acción no garantiza un valor único. Examine elsiguiente ejemplo, comenzando en la ventana 1:

>BEGIN;>SELECT MAX (f1) FROM innotest;+ +

Page 17: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 17/45

 

| MAX(f1) |+ +| 3 |+ +

Simultáneamente, otro usuario realiza la misma operación en la ventana 2:>BEGIN;>SELECT MAX (f1) FROM innotest;+ +| MAX(f1) |+ +| 3 |+ +1 row in set (0.11 sec)

Ahora, los dos usuarios (ventana 1 y ventana 2) agregan un nuevo registro y confirmansus transacciones:

>INSERT INTO innotest (f1) VALUES (4);Query ok, 1 row affected(0.11 sec)

>COMMIT;Query ok, 0 row affected(0.00 sec)

Si uno de los usuarios realiza una consulta de selección, recibirán los siguientesresultados:

>SELECT f1 FROM innotest;+ +| f1 |+ +| 1 || 2 || 3 || 4 || 4 |+ +

La lectura coherente no ha devuelto lo esperado: los registros con los valores 4 y 5. laforma de activar este resultado es realizando un bloqueo de actualización sobre laoperación de selección. Si indicamos a MySQL que estamos realizando una lectura deactualización, no permitirá que nadie más lea el valor hasta que nuestra transacción sehaya completado. En primer lugar, elimine el 4 incorrecto de la tabla, para realizar laoperación correctamente esta vez:

>DELETE FROM innotest WHERE f!=4;Query ok, 2 row affected(0.00 sec)

Page 18: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 18/45

 

A continuación establezca el bloqueo de actualización como se indica en la ventana 1:

>BEGIN;>SELECT MAX (f1) FROM innotest FOR UPDATE;+ +

| MAX(f1) |+ +| 3 |+ +

>INSERT INTO innotest (f1) VALUES (4);Query ok, 1 row affected(0.05 sec)

Entretanto, la ventana 2 también intenta establecer en bloqueo de actualización:

>BEGIN;>SELECT MAX (f1) FROM innotest FOR UPDATE;

Fíjese que no se devuelve ningún resultado. MySQL espera a que se complete latransacción de la ventana 1. complete la transacción en la ventana 1:

>COMMIT;Query ok, 0 row affected(0.00 sec)

La ventana 2 devolverá los resultados de su consulta, tras esperar a que se complete laoperación de inserción.

>SELECT MAX (f1) FROM innotest FOR UPDATE;+ +| MAX(f1) |+ +| 4 |+ +1 row in set (4 min 32.65 sec)

Ahora, una vez seguros de que el 4 es el último valor de la tabla, podemos agregar el 5la ventana 2:

>INSERT INTO innotest (f1) VALUES (5);

Query ok, 1 row affected(0.06 sec)

>COMMIT;Query ok, 0 row affected(0.00 sec)

Bloqueos de lectura en modo compartido

Existe otro tipo de bloqueo de lectura que no devuelve un valor si el valor que estáleyendo ha sido modificado por otra transacción incompleta. Devuelve el último valor,  pero no forma parte de una transacción cuya intención es modificar el valor. Por 

Page 19: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 19/45

 

ejemplo, vamos a utilizar el campo f2 creado en la tabla innotest. Asumamos que elcampo f1 consta ya de elementos, pero hasta un momento posterior de la transacción nose introducirá un valor para el campo f2. Al realizar una consulta de Selección, noqueremos recuperar un registro que disponga de un valor para f1 pero no para f2, sinoque queremos obtener siempre el último registro. En este caso, necesitamos esperar a

que se complete la transacción antes de que se recuperen los resultados. Por ejemplo,una transacción comienza en la ventana 1:

>BEGIN;Query ok, 0 row affected(0.00 sec)

>INSERT INTO innotest (f1) VALUES (6);Query ok, 1 row affected(0.00 sec)

>UPDATE innotest set f2=’Sebastian’ WHERE f1=6;Query ok, 1 row affected(0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0

Se realiza una consulta normal de selección en la ventana 2, no recuperamos el últimovalor (porque la transacción anterior no se ha completado y la tabla InnoDB realiza unalectura coherente como parte de su comportamiento predeterminado). Sin embrago, sirealiza una consulta con un bloqueo en modo compartido, no obtendrá en resultadohasta que se complete la transacción en la ventana 1.Si ejecutamos una consulta normal en la ventana 2, se recuperarán los siguientesresultados:

>SELECT MAX (f1) FROM innotest;+ +| MAX(f1) |+ +| 5 |+ +1 row in set (0.17 sec)

Todavía en la ventana 2, si realiza la misma consulta en modo de bloqueo de usocompartido no se generará ningún resultado:>SELECT MAX (f1) FROM innotest LOCK IN SHARE MODE;

Complete la transacción en la ventana 1:

>COMMIT;Query ok, 0 row affected(0.00 sec)

A continuación la ventana 2 devolverá el resultado correcto:

>SELECT MAX (f1) FROM innotest LOCK IN SHARE MODE;+ +| MAX(f1) |+ +

Page 20: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 20/45

 

| 6 |+ +1 row in set (4 min 32.98 sec)

>COMMIT;Query ok, 0 row affected(0.00 sec)

Confirmaciones automáticas

De manera predeterminada, y a menos que se especifique una transacción con BEGIN,MySQL confirmara automáticamente las instrucciones. Por ejemplo, una consulta en laventana 1 devolvería los siguientes resultados:

>SELECT f1 FROM innotest;+ +| f1 |+ +

| 1 || 2 || 3 || 4 || 5 || 6 |+ +6 rows in set (0.11 sec)

A continuación, el usuario de la ventana 2 inserta un registro:

>INSERT INTO innotest (f1) VALUES (7);Query ok, 1 row affected(0.00 sec)

Está inmediatamente disponible en la ventana 1 (recuerde completar todos los ejemplosanteriores con la instrucción COMMIT):

>SELECT f1 FROM innotest;

+ +| f1 |+ +

| 1 || 2 || 3 || 4 || 5 || 6 || 7 |+ +7 rows in set (0.11 sec)

Page 21: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 21/45

 

El registro insertado en la ventana 2 está inmediatamente disponible para el resto de lasventanas porque la acción predeterminada es AUTOCOMMIT. Sin embrago, en lastablas de transacción segura (InnoDB, DBD), puede cambiar este comportamientoasignado 0 a AUTOCOMMIT. En primer lugar, realice dichas operación en la ventana1:

>SET AUTOCOMMIT=0;Query ok, 0 row affected(0.00 sec)

A continuación, ejecute la consulta en la ventana 2:

>SELECT f1 FROM innotest;+ +| f1 |+ +| 1 || 2 || 3 || 4 || 5 || 6 || 7 |+ +7 rows in set (0.22 sec)

Seguidamente, inserte u registro en la ventana 1;

>INSERT INTO innotest (f1) VALUES (8);Query ok, 1 row affected(0.00 sec)

En esta ocasión, no está inmediatamente disponible en la ventana 2:

>SELECT f1 FROM innotest;+ +| f1 |+ +| 1 || 2 || 3 |

| 4 || 5 || 6 || 7 |+ +7 rows in set (0.16 sec)

Como no se ha desactivado la función de confirmación automática, la operación deinserción de la ventana 1 no se confirmará hasta que se ejecute un comando COMMITde manera predeterminada. Confirme la transacción desde la ventana 1:

Page 22: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 22/45

 

>COMMIT;Query ok, 1 row affected(0.00 sec)

Ahora el nuevo registro está disponible en la ventana 2:

>SELECT f1 FROM innotest;+ +| f1 |+ +| 1 || 2 || 3 || 4 || 5 || 6 || 7 |

| 8 |+ +8 rows in set (0.11 sec)

Sin embargo, la secuencia AUTOCOMMIT=0 no se aplica a todo el servidor, sino sóloa la sesión especificada. Si se asigna el valor 0 al comando AUTOCOMMIT en laventana 2, el comportamiento será diferente.En primer lugar, establezca AUTOCOMMIT en la ventana 1 y en la ventana 2:

>SET AUTOCOMMIT=0;Query ok, 0 row affected(0.00 sec)

A continuación, ejecute la siguiente secuencia en la ventana 1 para comprobar suselementos presentes:

>SELECT f1 FROM innotest;+ +| f1 |+ +| 1 || 2 || 3 |

| 4 || 5 || 6 || 7 || 8 |+ +8 rows in set (0.17 sec)

Agregue en registro en la ventana 2 y confirme la transacción:

Page 23: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 23/45

 

>INSERT INTO innotest (f1) VALUES (9);Query ok, 1 row affected(0.00 sec)

>COMMIT;Query ok, 0 row affected(0.00 sec)

A continuación compruebe si aparece en la ventana 1:

>SELECT f1 FROM innotest;+ +| f1 |+ +| 1 || 2 || 3 || 4 || 5 |

| 6 || 7 || 8 |+ +8 rows in set (0.11 sec)

El 9 del nuevo registro no aparece, aunque hayamos confirmado los resultados. Larazón es que la instrucción de selección de la ventana 1 forma también parte de unatransacción. A la lectura coherente se le ha asignado un punto temporal y este puntotemporal avanza si la transacción en la que se estableció sea completado. Confirme latransacción en la ventana 1:

>COMMIT;Query ok, 0 row affected(0.00 sec)

>SELECT f1 FROM innotest;+ +| f1 |+ +| 1 || 2 || 3 || 4 || 5 || 6 || 7 || 8 || 9 |+ +9 rows in set (0.22 sec)

Page 24: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 24/45

 

Como vimos anteriormente, la única forma de examinar los últimos resultados consisteen seleccionarlos en modo bloqueo de uso compartido. En este caso, se hubieraesperado hasta que la transacción que realiza la operación de inserción haya realizadouna operación de confirmación.

Transacciones en tablas DBDLas tablas DBD procesan las transacciones de forma ligeramente diferente a las tablasInnoDB. En primer lugar, cree la tabla (si no lo ha hecho en el capítulo anterior) einserte un registro desde la ventana 1:

>CREATE TABLE bdbtest (f1 INT, f2 CHAR(10)) TYPE=DBD;Query ok, 0 row affected(0.28 sec)

>BEGIN;Query ok, 0 row affected(0.06 sec)

>INSERT INTO bdbtest (f1) VALUES (1);Query ok, 1 row affected(0.00 sec)

A continuación, realice la siguiente consulta desde la ventana 2:

>SELECT f1 FROM bdbtest;

La ventana 2 espera a que la transacción de la ventana 1 esté completada. (no devuelveun conjunto de resultados en función de la situación antes de que dé comienzo latransacción de la ventana 1, como ocurre en las tablas InnoDB.)Sólo cuando la ventana 1 confirma la acción, la ventana 2 recibe los resultados.

Complete la transacción de la ventana 1:

>COMMIT;Query ok, 0 row affected(0.00 sec)

Y la consulta de la ventana 2 se completa (no necesita escribirla de nuevo):

>SELECT f1 FROM bdbtest;

+ +| f1 |

+ +| 1 |+ +1 rows in set (3 min 13.99 sec)

Fíjese en el largo periodo de tiempo que llevó la consulta. El hecho de que no se tratede una consulta de selección “rápida” en las tablas DBD significa que todas lastransacciones que se pospongan pueden dar lugar a graves problemas de rendimiento.

Page 25: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 25/45

 

Como en el caso de las tablas InnoDB, el modo predeterminado es AUTOCOMMIT=1.Esto significa que a menos que coloque sus cambios dentro de una transacción(comenzando con BEGIN), se completarán inmediatamente.

>SELECT f1 FROM bdbtest;

+ +| f1 |+ +| 1 |+ +1 rows in set (0.17 sec)

A continuación ejecute una inserción desde la ventana 2:

>INSERT INTO bdbtest (f1) VALUES (2);Query ok, 1 row affected(0.06 sec)

Resulta inmediatamente recuperable desde la ventana 1:

>SELECT f1 FROM bdbtest;+ +| f1 |+ +| 1 || 2 |+ +2 rows in set (0.16 sec)

Si AUTOCOMMIT se define como 0, el efecto es el mismo al de encerrar todas lasinstrucciones en un comando BEGIN.Asigne AUTOCOMMIT=0 e inserte un registro en la ventana 1:

>SET OPTION AUTOCOMMIT=0;Query ok, 0 row affected(0.11 sec)

>INSERT INTO bdbtest (f1) VALUES (3);Query ok, 1 row affected(0.11 sec)

Una consulta ejecutada desde la ventana 2 esperará a que la transacción esté activa:

>SELECT f1 FROM bdbtest;

El resultado aparecerá sólo cuando la transacción se haya confirmado:Confirme la transacción en la ventana 1:

>COMMIT;Query ok, 0 row affected(0.05 sec)

Page 26: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 26/45

 

Ahora la consulta recupera los resultados en la ventana 2 (no necesita volver a escribir la consulta):

>SELECT f1 FROM bdbtest;+ +

| f1 |+ +| 1 || 2 || 3 |+ +

Otros comportamientos transaccionales

Existe una serie de comandos adicionales que finalizan automáticamente unatransacción (en otras palabras, que se comportan como si hubiéramos realizado unaoperación de confirmación).

• BEGIN• ALTER TABLE• CREATE INDEX• RENAME TABLE (ese un sinónimo de ALTER TABLE X RENAME)• TRUNCATE• DROP TABLE• DROP DATABASE

Incluso si el comando no produce un resultado satisfactorio, el mero hecho de aplicarlo

genera una operación de confirmación. Por ejemplo, comencemos por la siguientetransacción en la ventana 1:

>BEGIN;

>SELECT MAX (f1) FROM innotest FOR UPDATE;+ +| MAX(f1) |+ +| 9 |+ +

Y comience otra transacción en la ventana 2:

>BEGIN;>SELECT MAX (f1) FROM innotest FOR UPDATE;

Los resultados no se muestran, ya que la ventana 1 ha bloqueado la fila para suactualización.

Page 27: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 27/45

 

Sin embrago, el usuario de la ventana 1 cambia de opinión y decide modificar primerola estructura de la tabla.

• Ejecutamos el comando ALTER TABLE en la ventana 1:

>ALTER TABLE innotest add f1 INT;ERROR 1060: Duplicate column name ‘f1’

Aunque la operación ALTER falló, se levantó el bloqueo, se confirmó la transacción yla consulta de la ventana 2 se completó (no es necesario volver a introducirla).

>SELECT MAX (f1) FROM innotest FOR UPDATE;+ +| MAX(f1) |+ +| 9 |+ +1 row in set (2 min 23.52 sec)

Bloqueo de tablasLos bloqueos en el nivel de fila son mucho más eficaces cuando se necesita realizar unagran cantidad de inserciones o actualizaciones en la tabla.

Existen dos tipos de bloqueos de tabla: los bloqueos de lectura y los bloqueos de escritura.La sintaxis para bloquear una tabla es la siguiente:

LOCK TABLE nombre_de_tabla {READ|WRITE}

Para desbloquear una tabla, basta con utilizar la instrucción UNLOCK TABLE de lasiguiente forma:

UNLOCK TABLES;

La siguiente secuencia ilustra un bloqueo en el nivel de tabla, que funcionará con todo tipode tablas.En primer lugar, bloquee la tabla desde la ventana 1:

>LOCK TABLE customer READ;

Se pueden leer otros subprocesos, pero no se pueden escribir, como puede observar si prueba a utilizar el siguiente comando en la ventana 2:

>SELECT * FROM customer;

>INSERT INTO customer(id,first_name,surname) VALUES(5,’Francois’,’Papo’);

Page 28: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 28/45

 

la instrucción INSERT no se procesa hasta que el bloqueo se libera en la ventana 1;

>UNLOCK TABLES;seguidamente se completa la operación de inserción en la ventana 2(no es necesario volver a escribirla):

También puede bloquear más de una tabla a la vez . Aplique los siguientes bloqueos desdela ventana 1:

>LOCK TABLE customer READ, sales WRITE;

Los bloqueos de escritura tienen prioridad sobre los procesos de lectura, de manera que siun subproceso espera un bloqueo de lectura y recibe un bloqueo de escritura, el bloqueo delectura deberá esperar hasta obtener el bloqueo de escritura y a su liberaciónAplique un bloqueo de escritura en la ventana 1:

>LOCK TABLE customer WRITE;Ahora intente aplicar un bloqueo de lectura desde la ventana 2:

>LOCK TABLE customer READ;

el bloqueo de lectura no se puede obtener hasta que se libere el bloqueo de escritura.Entretanto, se recibe otra petición por n bloqueo de escritura, que también debe esperar hasta que se libere el primeroIntente aplicar otro bloqueo de escritura desde una tercera ventana:

>LOCK TABLE customer WRITE;A continuación, libere el bloqueo desde la ventana 1:

>UNLOCK TABLES;Query OK, 0 rows affected (0.00 sec)

Ahora se obtiene el bloqueo de escritura de la ventana 2, aunque fue solicitado tras el bloqueo de lectura, de la siguiente forma

>LOCK TABLE customer WRITE;Query OK, 0 rows affected (0.33 sec)

>UNLOCK TABLES;Query OK, 0 rows affected (0.00 sec)

Sólo cuando se libera el bloqueo de escritura de la ventana 3 se puede obtener el bloqueo de escritura de laventana 2

>LOCK TABLE customer READ;Query OK, 0 rows affected (4 min 2.46 sec)>UNLOCK TABLES;

Page 29: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 29/45

 

Puede variar este comportamiento especificando una prioridad inferior para el bloqueo deescritura, mediante la palabra clave LOW_PRIORITY.

>LOCK TABLE customer LOW_PRIORITY WRITE;

los bloqueo de tabla se suelen utilizar de esta forma sobre tables que no admitentransacciones. Si está utilizando una tabla InnoDB o BDB, utilice los comando BEGIN YCOMMIT para evitar anomalías en los datos. A continuación se incluye un ejemplo en elque podría utilizarse. Si su tabla customer_sales_values está vacía, rellénela conalgunos registros:

>INSERT INTO customer_sales_values(first_name, surname, value, value2) VALUES(‘Juan’,’Pérez’, 500, NULL),(‘Patricia’,’Cuevas’,750,NULL);

Imaginemos que Juan Pérez ha realizado 2 ventas, cada una procesada por unadministrativo diferente. La primera es de 100 dólares y la segunda de 300 dólares. Losadministrativos lee el valor existente, agregan 100 o 300 a dicho valor y actualizan elregistro. El problema surge si ambos realizan la operación de selección antes de quecualquiera de los dos se actualice.En este caso, una de las actualizaciones sobrescribirá a la otra y se perderá dicho valor, dela forma en la que indica a continuación.

Realizar este ejercicio

Page 30: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 30/45

 

CAPITULO II ÍNDICES Y OPTIMIZACION DE CONSULTAS

Un índice es un archivo estructurado que facilita el acceso a los datos.Son listas separadas para cada campo que necesite ordenar. No contendrán todos los

campos, sólo aquellos que necesite ordenar y un puntero a un registro completo de latabla.

Tabla Customer índice Surname

Creación de un índiceExisten 4 tipos de índice en Mysql: una clave primaria un índice exclusivo, un índice detexto completo y un índice primario.

Creación de una Clave PrimariaUna clave primaria es un índice establecido sobre un campo en el que cada valor es

exclusivo y ninguno de los valores es NULL.Para establecer una clave primaria al crear una tabla, utilice la instrucción PRIMARYKEY al final de las definiciones de campo. Sintaxis

>CREATE TABLE nombre_de_tabla(nombre_de_campo tipo_de_columna NOT NULL,[nombre_de_campo...,] PRIMARY KEY(nombre_de_campo1[,nombre_de_campo2...));

Para crear una clave primaria en una tabla existente puede utilizar la palabra claveALTER: Sintaxis

>ALTER TABLE nombre_de_tabla ADD PRIMARY KEY(nombre_de_campo1 [,nombre_de_campo2...]);

ejemplo:

>ALTER TABLE customer MODIFY id INT NOT NULL,ADD PRIMARY KEY(id);

ID FirstName Surname

1 Yvonne Clegg2 Johnny Chaka-Chak 3 Winston Powers

4 Patricia Mankuku5 Francois Papo6 Winnie Dlamini7 Neil Beneke

BenekeChaka_Chak Clegg

DlaminiMankukuPapoPowers

Page 31: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 31/45

 

Creación de una llave primaria que se compone de más de un campo

>CREATE TABLE pk2(id INT NOT NULL, id2 INT NOT NULL, PRIMARYKEY(id,id2));

O si la tabla existe

>ALTER TABLE pk2 ADD PRIMARY KEY(id,id2);

Creación de un índice primario

Los índices que no son primarios permiten el uso de valores duplicados(a menos que loscampos se especifiquen como únicos). Como siempre, es mejor crear el índice a la vez

que se crea la tabla:>CREATE TABLE nombre_de_tabla(nombre_de_campo tipo_de_columna,

nombre_de_campo2 tipo_de_columna, INDEX [nombre_de_índice](nombre_de_campo1 [,nombre_de_campo2...]));

también puede crear más de una índice al crear la tabla, separándolas sencillamentemediante comas:

>CREATE TABLE nombre_de_tabla (nombre_de_campo tipo_de_columna,nombre_de_campo2 tipo_de_columna, INDEX [nombre_de_indice1]

(nombre_de_campo1, nombre_de_campo2), INDEX [nombre_de_indice2](nombre_de_campo1 [,nombre_de_campo2...]));

También puede crear un índice en un momento posterior mediante el siguiente código:

>ALTER TABLE nombre_de_tabla ADD INDEX [nombre_de_índice](nombre_de_campo1 [,nombre_de_campo2...]);

o con el siguiente código

>CREATE INDEX nombre_de_índice ON nombre_de_tablanombre_de_campo1 [,nombre_de_campo2.....]);

Creación de un índice de texto completoPuede crear índices de texto completo en tablas MyISAM sobre cualquier campoCHAR, VARCHAR o TEXT. Los índices de texto completo están diseñados parafacilitar la búsqueda sobre palabras clave en campos de texto de tablas grandes.

Para crear un índice de texto completo al crear la tabla, utilice la siguientes sintaxis:

Page 32: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 32/45

 

CREATE TABLE nombre_de_tabla (nombre_de_campo tipo_de_columna,  Nombre_de_campo2 tipo_de_columna, FULLTEXT(nombre_de_campo1[,nombre_de_campo2...]));

Se puede agregar la palabra clave opcional INDEX, como muestra la siguinte sintaxis:CREATE TABLE nombre_de_tabla (nombre_de_campo tipo_de_columna,nombre_de_campo2 tipo_de_columna, FULLTEXT INDEX(nombre_de_campo1[,nombre_de_campo2...]));

Para crear un índice de texto completo una vez creada la tabla, utilice la siguientesintaxis:

ALTER TABLE nombre_de_tabla ADD FULLTEXT [nombre_de_índice](nombre_De_campo1 [,nombre_de_campo2...]);

O el siguiente código

CREATE FULLTEXT INDEX nombre_de_índice on nombre_de_tablanombre_de_campo [,nombre_de_campo2...]);

Uso de los índices de texto completo

Vamos a crear una tabla con un índice de texto completo y a insertar algunos títulos de

libros para probarlo:>CREATE TABLE ft2(f1 VARCHAR(255), FULLTEXT(f1));>INSERT INTO ft2 VALUES(‘Waiting for the Barbarians’),(‘In the heart of the Country’),(‘The Master of Petersburg’),(‘Writing and Being’),(‘Heart of the Beast’), (‘Heart of the Beast’),(‘The Beginning and the End’),(‘Master Master’),(‘A barbarian at my Door’);

Para devolver los resultados de una búsqueda de texto completo, se utiliza la funciónMATCH(), y se busca la correspondencia de un campo con un valor, como en elsiguiente ejemplo, que busca ocurrencias de la palabra Master.

>SELECT * FROM ft2 MATCH(f1) AGAINST (‘Master’);

NOTA: Recuerde que las búsquedas sobre campos de tipo TEXT no distinguen entreMAYÚSCULAS Y MINÚSCULAS, ni los campos VARCHAR o CHAR declaradossin la palabra clave BINARY

Palabras ruido

Page 33: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 33/45

 

Ahora vamos a ejecutar otra búsqueda :>SELECT * FROM ft2 WHERE MATCH(f1) AGAINST (‘The Master’);+------------------+| f1+------------------+

| Master Master ||The Master of Petersbug |+------------------+

los resultados no son los esperados.

• MySQL tiene lo que se conoce como umbral del 50%. Todas las palabras queaparecen en más de un 50% de los campos se consideran como ruido y seignoran.

• Todas las palabras que tengan un número igual o inferior a tres letras seexcluyen del índice.

Existe una lista predefinida de palabras ruido, entre las que se incluyen the.

Búsquedas booleanas de texto completo

Una de las optimizaciones más útiles de MySQL 4 es su capacidad para realizar búsquedascompletas booleanas. Esta función utiliza un conjunto completo de elementos para buscar  por palabras, combinaciones de palabras, porciones de palabras y otras variantes(véase latabla 4.1).

Tabla 4.1 Operadores de búsquedas booleanas

Operadores Descripción+ La palabra siguiente es obligatoria y debe aparecer en todas las filas devueltas.- La palabra siguiente está prohibida y no debe aparecer en ninguna de las filas

devueltas.< La palabra siguiente tiene una relevancia inferior al resto de palabras.> La palabra siguiente tiene una relevancia superior al resto de palabras( ) Se utiliza para agrupar palabras en subexpresiones.~ La palabra siguiente realiza una contribución negativa a la relevancia de la

fila(no es igual al operador -, que excluye la fila completamente si seencuentra la palabra, o al operador <, que sigue asignando una relevancia

 positiva, aunque más a la baja, a la palabra)..

El comodín que indica cero o varios caracteres Sólo puede aparecer al final dela palabra.

Cualquier expresión encerrada entre comillas dobles se toma como unconjunto.

Page 34: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 34/45

 

Las búsquedas booleanas de texto completo no tienen en cuenta el umbral del 50%.pararealizar una búsqueda booleana de texto completo se utiliza la cláusula IN BOLEANMODE:>SELECT * from ft2 WHERE MATCH(f1) AGAINST (‘+Master –Petersburg’

IN BOOLEAN MODE);

+------------------+| f1 |+------------------+| Master Master |+------------------+en este ejemplo, se excluye la palabra Peterburg , por lo que no se recupera el título The

Master of Peterburg aunque incluye la palabra Master . Fíjese en la diferencia entre estos 2conjuntos de resultados:

>SELECT * FROM ft2 WHERE MATCH(f1) AGAINST (‘Country Master’IN BOOLEAN MODE);

+------------------+| f1 |+------------------+| In the Heart of the Country || The Master of Petersburg || Master Master |+------------------+

>SELECT * FROM ft2 WHERE MATCH(f1) AGAINST (‘+Country Master’IN BOOLEAN MODE);

+------------------+| f1 |+------------------+| In the Heart of the Country |

la palabra Country es obligatoria en la segunda búsqueda

los siguientes 2 ejemplos muestran las diferencias entre una búsqueda utilizando losoperadores “ ” y sin ellos. Estos operadores permiten realizar búsquedas con unacorrespondencia exacta en una frase:

>SELECT * FROM ft2 WHERE MATCH(f1)AGAINST(‘the Heart of the’ IN BOOLEAN MODE);

+------------------+| f1 |+------------------+| In the Heart of the Country || Heart of the Beast || Heart of the Beest |+------------------+

Page 35: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 35/45

 

>SELECT * FROM ft2 WHERE MATCH(f1)AGAINST(‘”the Heart of the”’ IN BOOLEAN MODE);

+------------------+| f1 |

+------------------+| In the Heart of the Country |

no olvide colocar las comillas iniciales al utilizar el operador de comillas dobles. Si lo hace,es como si no utilizara ningún operador.

Ejercicio: realizar al menos 10 ejemplos con cada uno de los operadores booleanos.

Creación de un índice único

Un índice único es lo mismo que un índice ordinario con la salvedad de que no se permitenduplicaciones.Para establecer un índice único al crear una tabla, utilice la siguiente sintaxis:

CREATE TABLE nombre_de_tabla (nombre_de_campo tipo_de_columna,nombre_de_campo_2 tipo_de_columna, UNIQUE(nombre_de_campo[,nombre_de_campo2....]));

O si la tabla ya existe, puede utilizar otra sintaxis:

ALTER TABLE nombre_de_tabla ADD UNIQUE [nombre_de_índice ](nombre_de_campo [,nombre_de_campo2...]);

O esta otra:

CREATE UNIQUE INDEX nombre_de_índice ON nombre_de_tabla(nombre_de_campo [, nombre_de_campo2....]);

Si el índice contiene un solo campo, dicho campo no puede contener valores duplicados.

Si el índice contiene más de campo, los valores de los campos individuales se puededuplicar, pero la combinación de los valores del campo que componen el índice entero nose pueden duplicar.

Cómo recuperar y reiniciar el valor de incremento automático

Puede devolver el valor de incremento automático insertado más recientemente mediante lafunción LAST_INSERT_ID():

Page 36: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 36/45

 

>SELECT LAST_INSERT_ID() FROM customer LIMIT 1

Si desea restablecer el contador de incremento automático para que se inicie en un valor concreto, puede utilizar el siguiente código:

>ALTER TABLE nombre_de_tabla AUTO_INCREMENT = valor_inc_auto;

 NOTA : TRUNCATE, en contraposición a DELETE, reinicia el contador de incrementoautomático.

  NOTA: en la actualidad, está operación sólo funciona con las tablas MyISAM. Por ejemplo, el contador de incremento automático de las tablas InnoDB no se puede establecer en un valor diferente a 1.

Eliminación o modificación de un índice

Para eliminar una clave primaria utilice esta sintaxis

ALTER TABLE nombre_de_tabla DROP PRIMARY KEY;

Para eliminar un índice ordinario, exclusivo o de texto completo, debemos especificar elnombre del índice, de la siguiente forma:

ALTER TABLE nombre_de_tabla DROP INDEX nombre_de_índice;

O de esta otra forma:

DROP INDEX nombre_de_índice ON nombre_de_tabla;

Si no está seguro del índice, la instrucción SHOW KEYS se lo mostrará:

SHOW KEYS FROM nombre_de_tabla;

Selección de índices.

• Los índices sólo deberían crearse en aquellas consultas que los utilicen (por ejemplo, sobre campos de la condición WHERE) y no sobre campos que no vayan autilizarlos(como en caso de que el primer carácter de la condición sea un comodín).

• Cree índices que devuelvan el menor número de filas posible. El mejor lugar essobre una clave primaria ya que éstas se asocian de manera exclusiva a un registro.

• Utilice índices cortos(cree un índice sobre los diez caracteres de un nombre, por ejemplo, en lugar de hacerlo sobre el campo completo).

•  No cree demasiados índices. Los índices aumentan el tiempo necesario paraactualizar o agregar un registro. Por ello, si el índice se crea para una consulta que

Page 37: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 37/45

 

se utilice en contadas ocasiones y pueda aceptare un rendimiento ligeramente máslento, considere la opción de no crear un índice.

• Utilice el sistema de prefijación a la izquierda.

Las fases del desarrollo de aplicaciones

Fase1: análisis de las necesidades

El análisis de las necesidades de un proyecto es un paso obvio en el desarrollo de unaaplicación. Los expertos repiten esta regla una y otra vez al deplorar el pésimo estado deldesarrollo de software.Sin embargo, se suelen escuchar excusas como “no sabíamos que quería eso” o “nunca senos dijo al principio” para justificar el pobre resultado final de los proyectos. La primerafase de un proyecto, y quizás la más importante, consiste en determinar las necesidades.

Determinación de las necesidades del usuario

La mayor parte de las peticiones que realizan los usuarios resultan triviales, para su propiasorpresa. He conocido a usuarios, que tras trabajar con desarrolladores incompetentes ovagos, temían pedir algo más complicado que un campo adicional de una tabla. En lamayor parte de los casos, prácticamente todo es posible siempre y cuando se solicite por adelantado. La principal dificultad no es satisfacer las necesidades del usuario, si no hacerlouna vez desarrollado el marco de trabajo inicial. Los usuarios no siempre saben lo quequieren. Necesitan que les ayudemos a formalizar sus necesidades. Asegúrese, antes denada, de que las necesidades de usuarios han quedado claras, tanto para el equipo dedesarrollo como para los propios usuarios. El equipo del proyecto necesita realizar lossiguientes pasos para determinar las necesidades de los usuarios:

• El equipo debe ayudar a los usuarios a que determinen sus necesidades. El equipodebe guiar a los usuarios, explicándoles por qué determinadas sugerencias noresultan prácticas o proponiéndoles alternativas mejores. El equipo debe utilizar suexperiencia. Debe exponer necesidades no mencionadas para poder documentarlas .lo que resulta obvio para el usuario puede que no lo sea para los desarrolladores y puede que las necesidades importantes se pasen por alto. Las necesidades deben ser lo más completas posibles. Por ejemplo, un usuario puede solicitar un sistema para“realizar reservas”. Sin embargo, esta solicitud no es adecuada ya que no se aclaranlos campos necesarios para llevar a cabo la reserva ni los procesos subyacentes.

• Tras comprender las necesidades del usuario, el equipo debe ponerlas por escrito y

 presentárselas de nuevo a los usuarios y a los propietarios del proyecto(los que pagan por su desarrollo) para su confirmación. Los usuarios deben estar seguros delo que van a obtener. Hay que evitar las sorpresas posteriores.

• Obligue a los propietarios a confirmar formalmente las necesidades. De esta forma,se limita la posibilidad de que alguna de las partes quede descontenta. El incrementocontinuo de las necesidades durante la fase de desarrollo de un proyecto es un problema insidioso que suele producirse con frecuencia cuando no se ha llegado aun acuerdo formal sobre las necesidades en un primer momento. O bien los

Page 38: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 38/45

 

 propietarios del proyecto no dejan de pedir nuevos elementos o una de las partesdescubre nuevos aspectos no previstos hasta entonces.

Determinación de tecnología necesaria

La determinación de la tecnología necesaria es tan importante como la fase dedeterminación de las necesidades de los usuarios. Puede que no resulte imposibleejecutar un sitio Web con más de 20 millones de solicitudes al mes en un solo servidor  pero si necesitará, al menos, un buen ordenador que cumpla una serie de condiciones.El equipo del proyecto no debe imponer ningún requisito previo al proyecto, como queejecute Linux y MySQL. Se deben responder cuestiones como las siguientes:

• ¿Número de equipos y tipo de arquitectura necesaria para unirlos?• ¿Qué tipos de equipos se necesita utilizar?• ¿Qué sistemas operativos, sistemas de bases de datos y otras aplicaciones de

software se necesitan(como servidores Web, clientes de correo y demás)?• ¿Qué lenguajes se utilizan para desarrollar la aplicación?¿Serán orientados a

objetos?

Fase 2: Diseño de la aplicación

Una vez definidas las necesidades, llega el momento de diseñar la aplicación

Modelado

Un modelo simplifica la estructura del programa y traduce las necesidades de los usuarios a

un formato que el programador comprende de manera sencilla. Puede tratarse de modelosformales, como los del lenguaje unificado de modelado de sistemas (UML), un diagrama deflujo de datos o sencillamente un dibujo sobre un pedazo de papel.Los elementos fundamentales que debe incluir son los datos que necesita cada proceso y lainformación que genera cada uno de ellos.

Uso de pseudocódigo

El pseudocódigo es otro paso que puede ayudar a los programadores a desarrollar unaaplicación de forma más rápida y sencilla. En lugar de preocuparse por los requisitosexactos de sintaxis del lenguaje de programación, el pseudocódigo responde a las

necesidades lógicas, creando los algoritmos necesarios para resolver los problemas.

Codificación

Éste es el paso que se suele considerar como único. Sin embargo, la labor de codificaciónresulta a menudo mucho más sencilla cuando se ha creado documentación en los pasosanteriores.

Utilice los siguientes consejos durante la fase de codificación:

Page 39: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 39/45

 

• Documente siempre su código. Incluya comentarios dentro del código y creedocumentos aparte en los que se describan cómo se organiza la aplicación y sufuncionamiento. Si no quiere hacerlo para su uso personal, hágalo pensando en los programadores que lo vayan a utilizar posteriormente. Se puede llamar egoístas alos programadores que no dejan una extensa documentación para su uso posterior.

Asegúrese de asignar tiempo a esta tarea y no utilice la excusa de los plazos para pasarla por alto.• Utilice nombres de archivo, de función y de variable claros. Pro ejemplo, la función

f3( ) no es muy intuitiva, mientras que calcular_interes resulta más clara.•   No intente reinventar la rueda. Existen muchas clases y código de ejemplo

disponibles. Son raros los casos en los que los programadores necesitan crear algoúnico. La mayor parte del trabajo suele ser repetitivo, convirtiendo la labor de los programadores en bibliotecarios a la búsqueda dl código correcto para realizar sutrabajo.

• Inicialice todas las variables y documéntelas en un lugar, incluso si está codificandoen lenguajes que permiten el uso de variables antes de su inicialización. Con ello, no

sólo logrará que resulten más legibles sino también seguras.• Divida su código en partes pequeñas. De esta forma le resultará más sencillo de

entender, de depurar y de mantener. En el mundo del desarrollo web por ejemplo,en algunos comandos, lo que suele dar como resultado una mezcla incomprensible.

• Utilice los directorios de forma inteligente. No debería incluir las distintas partes deuna aplicación dentro del mismo directorio. Agrúpelas de forma lógica y en funciónde criterios de seguridad.

Page 40: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 40/45

 

STORED PROCEDURE EN MYSQL

>USE BASE DE DATOS;>DELIMITER $$>DROP PROCEDURE IF EXISTS HELLOWORLD$$

>CREATE PROCEDURE HELLOWORLD( )>BEGIN>SELECT ‘HOLA MUNDO’;>END$$

>CALL HELLOWORLD( ) $$--------------------------------------------------------------------------------------------------------------

>DELIMITER $$>DROP PROCEDURE IF EXISTS my_sqrt$$>CREATE PROCEDURE my_sqrt(in input_number INT)

>BEGIN>DECLARE l_sqrt FLOAT;>SET l_sqrt=SQRT(input_number);>SELECT l_sqrt;>END$$

>CALL my_sqºrt(9) $$

------------------------------------------------------------------------------------------------------------

>DELIMITER $$

> DROP PROCEDURE IF EXISTS my_sqrt$$> CREATE PROCEDURE my_sqrt(in input_number INT, out out_number FLOAT)> BEGIN>SET out_number=SQRT(input_number);>END$$

>CALL my_sqrt(9,@out_value) $$

>select @out_value $$

--------------------------------------------------------------------------------------------------------------

>DELIMITER $$>DROP PROCEDURE IF EXISTS discounted_price$$>CREATE PROCEDURE discounted_price(in normal_price NUMERIC(8,2),outdiscount_price NUMERIC(8,2))>BEGIN>IF (normal_price>500) THEN>SET discount_price=normal_price*.8;>ELSEIF (normal_price>100) THEN

Page 41: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 41/45

 

>SET discount_price=normal_price*.9;>ELSE>SET discount_price=normal_price;>END IF;>END$$

>CALL discounted_price(300,@new_price) $$>SELECT @new_price; _________________________________________________________________________ 

>DELIMITER $$>DROP PROCEDURE IF EXISTS simple_loop$$>CREATE PROCEDURE simple_loop( )>BEGIN>DECLARE counter INTDEFAULT 0;>My_simple_loop:LOOP>SET counter=counter+1;

>IF counter=10 THEN>LEAVE my_simple_loop;>END IF;>END LOOP my_simple_loop;>SELECT ‘I can count to 10’;>END$$

>Call simple_loop( ) $$

SELECT CANTIDAD INTO PARAM FROM TABLE WHERE

DELIMITER //DELIMITER ;

 _________________________________________________________________________ 

Disparadores

20.1. Sintaxis de CREATE TRIGGER 

CREATE TRIGGER nombre_disp momento_disp evento_disp

ON nombre_tabla FOR EACH ROW sentencia_disp

Un disparador es un objeto con nombre en una base de datos que se asocia con una tabla, y se activa cuando ocurre un evento en particular

para esa tabla.

El disparador queda asociado a la tabla nombre_tabla. Esta debe ser una tabla permanente, no puede ser una tabla TEMPORARY ni una vista.

momento_disp es el momento en que el disparador entra en acción. Puede ser BEFORE (antes) o AFTER (despues), para indicar que el

disparador se ejecute antes o después que la sentencia que lo activa.

evento_disp indica la clase de sentencia que activa al disparador. Puede ser INSERT, UPDATE, o DELETE. Por ejemplo, un disparador BEFORE

para sentencias INSERT podría utilizarse para validar los valores a insertar.

Page 42: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 42/45

 

No puede haber dos disparadores en una misma tabla que correspondan al mismo momento y sentencia. Por ejemplo, no se pueden tener

dos disparadores BEFORE UPDATE. Pero sí es posible tener los disparadores BEFORE UPDATE y BEFORE INSERT o BEFORE UPDATE y AFTER 

UPDATE.

sentencia_disp es la sentencia que se ejecuta cuando se activa el disparador. Si se desean ejecutar múltiples sentencias, deben colocarse

entre BEGIN ... END, el constructor de sentencias compuestas. Esto además posibilita emplear las mismas sentencias permitidas en rutinas

almacenadas. Consulte Sección  19.2.7, “Sentencia compuesta

 

BEGIN ... END ”.

Note : Antes de MySQL 5.0.10, los disparadores no podían contener referencias directas a tablas por su nombre. A partir de MySQL 5.0.10,

se pueden escribir disparadores como el llamado testref, que se muestra en este ejemplo:

CREATE TABLE test1(a1 INT);

CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE test4(

a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

b4 INT DEFAULT 0

);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1

FOR EACH ROW BEGIN

INSERT INTO test2 SET a2 = NEW.a1;

DELETE FROM test3 WHERE a3 = NEW.a1;

UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

END

|

DELIMITER ;

INSERT INTO test3 (a3) VALUES

(NULL), (NULL), (NULL), (NULL), (NULL),

(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES

(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Si en la tabla test1 se insertan los siguientes valores:

mysql> INSERT INTO test1 VALUES

-> (1), (3), (1), (7), (1), (8), (4), (4);

Query OK, 8 rows affected (0.01 sec)

Records: 8 Duplicates: 0 Warnings: 0

Entonces los datos en las 4 tablas quedarán así:

mysql> SELECT * FROM test1;

+------+

| a1 |

+------+

| 1 |

| 3 |

| 1 |

| 7 |

| 1 |

| 8 |

| 4 |

| 4 |

+------+

8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;

+------+

| a2 |

Page 43: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 43/45

 

+------+

| 1 |

| 3 |

| 1 |

| 7 |

| 1 |

| 8 |

| 4 |

| 4 |

+------+

8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;

+----+

| a3 |

+----+

| 2 |

| 5 |

| 6 |

| 9 |

| 10 |

+----+

5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;

+----+------+

| a4 | b4 |

+----+------+

| 1 | 3 |

| 2 | 0 |

| 3 | 1 |

| 4 | 2 |

| 5 | 0 |

| 6 | 0 |

| 7 | 1 |

| 8 | 1 |

| 9 | 0 |

| 10 | 0 |

+----+------+

10 rows in set (0.00 sec)

Las columnas de la tabla asociada con el disparador pueden referenciarse empleando los alias OLD y NEW. OLD.nombre_col hace referencia a

una columna de una fila existente, antes de ser actualizada o borrada. NEW.nombre_col hace referencia a una columna en una nueva fila a

punto de ser insertada, o en una fila existente luego de que fue actualizada.

El uso de SET NEW.nombre_col = valor necesita que se tenga el privilegio UPDATE sobre la columna. El uso de SET nombre_var  =

 NEW.nombre_col necesita el privilegio SELECT sobre la columna.

Nota : Actualmente, los disparadores no son activados por acciones llevadas a cabo en cascada por las restricciones de claves extranjeras.

Esta limitación se subsanará tan pronto como sea posible.

La sentencia CREATE TRIGGER necesita el privilegio SUPER 

Utilización de disparadores

El soporte para disparadores se incluyó a partir de MySQL 5.0.2. Actualmente, el soporte para disparadores es básico, por lo tanto hay ciertas

limitaciones en lo que puede hacerse con ellos. Esta sección trata sobre el uso de los disparadores y las limitaciones vigentes.

Un disparador es un objeto de base de datos con nombre que se asocia a una tabla, y se activa cuando ocurre un evento en particular para la

tabla. Algunos usos para los disparadores es verificar valores a ser insertados o llevar a cabo cálculos sobre valores involucrados en una

actualización.

Page 44: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 44/45

 

Un disparador se asocia con una tabla y se define para que se active al ocurrir una sentencia INSERT, DELETE, o UPDATE sobre dicha tabla.

Puede también establecerse que se active antes o despues de la sentencia en cuestión. Por ejemplo, se puede tener un disparador que se

active antes de que un registro sea borrado, o después de que sea actualizado.

Para crear o eliminar un disparador, se emplean las sentencias CREATE TRIGGER y DROP TRIGGER . La sintaxis de las mismas se describe en

Sección  20.1, “Sintaxis de CREATE TRIGGER  ” y Sección  20.2, “Sintaxis de DROP TRIGGER  ”. 

Este es un ejemplo sencillo que asocia un disparador con una tabla para cuando reciba sentencias INSERT. Actúa como un acumulador que

suma los valores insertados en una de las columnas de la tabla.

La siguiente sentencia crea la tabla y un disparador asociado a ella:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account

-> FOR EACH ROW SET @sum = @sum + NEW.amount;

La sentencia CREATE TRIGGER crea un disparador llamado ins_sum que se asocia con la tabla account. También se incluyen cláusulas que

especifican el momento de activación, el evento activador, y qué hacer luego de la activación:

• La palabra clave BEFORE indica el momento de acción del disparador. En este caso, el disparador debería activarse antes de que

cada registro se inserte en la tabla. La otra palabra clave posible aqui es AFTER .

• La plabra clave INSERT indica el evento que activará al disparador. En el ejemplo, la sentencia INSERT causará la activación.

También pueden crearse disparadores para sentencias DELETE y UPDATE.

• Las sentencia siguiente, FOR EACH ROW , define lo que se ejecutará cada vez que el disparador se active, lo cual ocurre una vez

por cada fila afectada por la sentencia activadora. En el ejemplo, la sentencia activada es un sencillo SET que acumula los valores

insertados en la columna amount. La sentencia se refiere a la columna como NEW.amount, lo que significa “el valor de lacolumna amount que será insertado en el nuevo registro.”

Para utilizar el disparador, se debe establecer el valor de la variable acumulador a cero, ejecutar una sentencia INSERT, y ver qué valor

presenta luego la variable.

mysql> SET @sum = 0;

mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

mysql> SELECT @sum AS 'Total amount inserted';

+-----------------------+

| Total amount inserted |

+-----------------------+| 1852.48 |

+-----------------------+

En este caso, el valor de @sum luego de haber ejecutado la sentencia INSERT es 14.98 + 1937.50 - 100, o 1852.48.

Para eliminar el disparador, se emplea una sentencia DROP TRIGGER . El nombre del disparador debe incluir el nombre de la tabla:

mysql> DROP TRIGGER account.ins_sum;

Debido a que un disparador está asociado con una tabla en particular, no se pueden tener múltiples disparadores con el mismo nombre

dentro de una tabla. También se debería tener en cuenta que el espacio de nombres de los disparadores puede cambiar en el futuro de un

nivel de tabla a un nivel de base de datos, es decir, los nombres de disparadores ya no sólo deberían ser únicos para cada tabla sino para

toda la base de datos. Para una mejor compatibilidad con desarrollos futuros, se debe intentar emplear nombres de disparadores que no se

repitan dentro de la base de datos.

Adicionalmente al requisito de nombres únicos de disparador en cada tabla, hay otras limitaciones en los tipos de disparadores que pueden

crearse. En particular, no se pueden tener dos disparadores para una misma tabla que sean activados en el mismo momento y por el mismo

evento. Por ejemplo, no se pueden definir dos BEFORE INSERT o dos AFTER UPDATE en una misma tabla. Es improbable que esta sea una

gran limitación, porque es posible definir un disparador que ejecute múltiples sentencias empleando el constructor de sentencias compuestas

BEGIN ... END luego de FOR EACH ROW . (Más adelante en esta sección puede verse un ejemplo).

También hay limitaciones sobre lo que puede aparecer dentro de la sentencia que el disparador ejecutará al activarse:

• El disparador no puede referirse a tablas directamente por su nombre, incluyendo la misma tabla a la que está asociado. Sin

embargo, se pueden emplear las palabras clave OLD y NEW . OLD se refiere a un registro existente que va a borrarse o que va a

actualizarse antes de que esto ocurra. NEW se refiere a un registro nuevo que se insertará o a un registro modificado luego de que

ocurre la modificación.

Page 45: Combinaciones avanzadas

5/7/2018 Combinaciones avanzadas - slidepdf.com

http://slidepdf.com/reader/full/combinaciones-avanzadas-559abd87be6cb 45/45

 

• El disparador no puede invocar procedimientos almacenados utilizando la sentencia CALL. (Esto significa, por ejemplo, que no se

puede utilizar un procedimiento almacenado para eludir la prohibición de referirse a tablas por su nombre).

• El disparador no puede utilizar sentencias que inicien o finalicen una transacción, tal como START TRANSACTION, COMMIT, o

ROLLBACK .

Las palabras clave OLD y NEW permiten acceder a columnas en los registros afectados por un disparador. (OLD y NEW no son sensibles a

mayúsculas). En un disparador para INSERT, solamente puede utilizarse NEW.nom_col; ya que no hay una versión anterior del registro. En un

disparador para DELETE sólo puede emplearse OLD.nom_col, porque no hay un nuevo registro. En un disparador para UPDATE se puedeemplear OLD.nom_col para referirse a las columnas de un registro antes de que sea actualizado, y NEW.nom_col para referirse a las columnas

del registro luego de actualizarlo.

Una columna precedida por OLD es de sólo lectura. Es posible hacer referencia a ella pero no modificarla. Una columna precedida por NEW 

puede ser referenciada si se tiene el privilegio SELECT sobre ella. En un disparador BEFORE, también es posible cambiar su valor con SET

 NEW.nombre_col = valor si se tiene el privilegio de UPDATE sobre ella. Esto significa que un disparador puede usarse para modificar los

valores antes que se inserten en un nuevo registro o se empleen para actualizar uno existente.

En un disparador BEFORE, el valor de NEW para una columna AUTO_INCREMENT es 0, no el número secuencial que se generará en forma

automática cuando el registro sea realmente insertado.

OLD y NEW son extensiones de MySQL para los disparadores.

Empleando el constructor BEGIN ... END, se puede definir un disparador que ejecute sentencias múltiples. Dentro del bloque BEGIN,

también pueden utilizarse otras sintaxis permitidas en rutinas almacenadas, tales como condicionales y bucles. Como sucede con las rutinas

almacenadas, cuando se crea un disparador que ejecuta sentencias múltiples, se hace necesario redefinir el delimitador de sentencias si se

ingresará el disparador a través del programa mysql, de forma que se pueda utilizar el caracter ';' dentro de la definición del disparador. El

siguiente ejemplo ilustra estos aspectos. En él se crea un disparador para UPDATE, que verifica los valores utilizados para actualizar cada

columna, y modifica el valor para que se encuentre en un rango de 0 a 100. Esto debe hacerse en un disparador BEFORE porque los valores

deben verificarse antes de emplearse para actualizar el registro:

mysql> delimiter //

mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account

-> FOR EACH ROW

-> BEGIN

-> IF NEW.amount < 0 THEN

-> SET NEW.amount = 0;

-> ELSEIF NEW.amount > 100 THEN

-> SET NEW.amount = 100;

-> END IF;

-> END;//

mysql> delimiter ;

Podría parecer más fácil definir una rutina almacenada e invocarla desde el disparador utilizando una simple sentencia CALL. Esto sería

ventajoso también si se deseara invocar la misma rutina desde distintos disparadores. Sin embargo, una limitación de los disparadores es que

no pueden utilizar CALL. Se debe escribir la sentencia compuesta en cada CREATE TRIGGER donde se la desee emplear.

MySQL gestiona los errores ocurridos durante la ejecución de disparadores de esta manera:

• Si lo que falla es un disparador BEFORE, no se ejecuta la operación en el correspondiente registro.

• Un disparador AFTER se ejecuta solamente si el disparador BEFORE (de existir) y la operación se ejecutaron exitosamente.

• Un error durante la ejecución de un disparador BEFORE o AFTER deriva en la falla de toda la sentencia que provocó la invocación

del disparador.

• En tablas transaccionales, la falla de un disparador (y por lo tanto de toda la sentencia) debería causar la cancelación (rollback) de

todos los cambios realizados por esa sentencia. En tablas no transaccionales, cualquier cambio realizado antes del error no se veafectado.