Proyecto
MySQL
Clemente Cervantes Bustos
Clemente Cervantes Bustos
Índice: 1.- Instalar MySQL en Linux ....................................................................................................... 2
2.- Instalar MySQL en Windows................................................................................................. 2
3.- Instalar Wamp en Windows ............................................................................................... 17
4.- Registro binario.................................................................................................................. 22
5.- Logs ................................................................................................................................... 25
5.1.- Logs en Linux ............................................................................................................... 25
5.2.- Logs en Windows ........................................................................................................ 28
5.3.- Logs en Wamp............................................................................................................. 30
6.- Acceso de forma remota .................................................................................................... 32
7.- Explicación del proyecto .................................................................................................... 36
8.- Texto Deportes-La Mancha ................................................................................................ 36
9.- Diagrama entidad relación ................................................................................................. 37
10.- Paso a tablas .................................................................................................................... 37
11.- Creación de la base de datos ............................................................................................ 38
12.- Exportación de la base de datos ....................................................................................... 41
13.- Creación de usuario y asignación de permisos .................................................................. 41
14.- Funciones......................................................................................................................... 43
15.- Procedimientos ................................................................................................................ 45
16.- Triggers ............................................................................................................................ 49
17.- Eventos ............................................................................................................................ 50
18.- Vistas ............................................................................................................................... 52
Clemente Cervantes Bustos
1.- Instalar MySQL en Linux Lo descargamos del repositorio de Linux ejecutando el siguiente comando:
2.- Instalar MySQL en Windows Nos vamos a la página web de MySQL, a la parte de descargas y le damos a Go to Download
Page.
Elegimos el sistema operativo para el que lo queremos descargar y lo descargamos.
Clemente Cervantes Bustos
Le damos a empezar la descarga.
Clemente Cervantes Bustos
Una vez finalizada la descarga ejecutamos el paquete instalador para empezar la instalación.
Clemente Cervantes Bustos
Aceptamos los términos de la licencia.
Nos saldrán diferentes tipos de instalaciones que podemos instalar. La instalación que nos
viene marcada por defecto es Developer Default que es la opción predeterminada y la que
utiliza los desarrolladores y personas que recién se están iniciando en MySQL. En mi caso elijo
la opción de Custom para instalar manualmente lo que yo quiera instalar.
Clemente Cervantes Bustos
Selecciono MySQL Server 8.0.12-X64 y lo llevo a la columna de productos para instalar.
Clemente Cervantes Bustos
Le damos a ejecutar.
Si necesitásemos algo para tener el MySQL instalado nos lo instalará por defecto, en mi caso
necesito el Microsoft Visual C++ y me lo dice, lo instalo.
Clemente Cervantes Bustos
Le doy a siguiente.
Clemente Cervantes Bustos
Elijo la opción de Standalone.
Clemente Cervantes Bustos
Los campos marcados son los más importantes y por ende los que nos interesan, veo que está
todo correcto por defecto y le doy a siguiente sin tocar nada.
Clemente Cervantes Bustos
Elijo la opción de la contraseña para la autentificación para que me pida una contraseña
cuando quiera acceder al MySQL.
Pongo la contraseña del root y más abajo nos da la opción de añadir más usuarios, ponerles
contraseñas y elegir los permisos que tendrán sobre el servidor MySQL. En mi caso no añado
ningún usuario.
Clemente Cervantes Bustos
Dejamos todo tal como viene por defecto.
Clemente Cervantes Bustos
Le damos a ejecutar.
Le damos a terminar.
Clemente Cervantes Bustos
Clemente Cervantes Bustos
Para acceder al MySQL deberemos dirigirnos al siguiente apartado:
Clemente Cervantes Bustos
En mi caso me pide la contraseña del usuario root que puse previamente.
Clemente Cervantes Bustos
3.- Instalar Wamp en Windows Descargamos el Wamp y seguimos los pasos de instalación.
Clemente Cervantes Bustos
Creo un icono de escritorio para facilitarme luego las posibles configuraciones.
Clemente Cervantes Bustos
Clemente Cervantes Bustos
Una vez finalizado accedemos al mysql. Para ello hacemos clic con el botón izquierdo sobre el
símbolo de Wamp.
Clemente Cervantes Bustos
Y nos vamos a MySQL.
Nos pedirá la contraseña de MySQL, en mi caso no pongo nada porque no le puse contraseña.
Clemente Cervantes Bustos
4.- Registro binario
El registro binario contiene toda la información que está disponible en el registro de actualizaciones, en un formato más eficiente y de una manera que es segura para las transacciones.
El registro binario contiene todas las sentencias que han actualizado datos o podrían haberlo hecho (por ejemplo, un DELETE que no encontró filas concordantes). Las sentencias se almacenan en la forma de “eventos” que describen las modificaciones.
El registro binario también contiene información sobre cuánto ha tardado cada
sentencia que actualizó la base de datos. No contiene sentencias que no hayan
modificado datos.
El propósito principal del registro binario es el de actualizar la base de datos durante una operación de recuperación tan completamente como sea posible, porque el registro binario contiene todas las actualizaciones hechas tras la copia de seguridad.
El registro binario también se utiliza en los servidores maestros de replicación como recordatorio de las sentencias que deben ser enviadas a los servidores esclavos.
Ejecutar el servidor con el registro binario activado hace que el rendimiento baje un
1%. Aun así, los beneficios del registro binario para las operaciones de restauración y el
hecho de permitirnos poder establecer replicación generalmente son superiores a este
decremento de rendimiento.
Es igual que en Linux, solo tenemos que saber que fichero modificar. En lugar de my.ini como
lo es en Windows, en Linux es mysqld.cnf.
Activamos el log binario de la siguiente forma:
Clemente Cervantes Bustos
Clemente Cervantes Bustos
Vemos los logs binarios que tenemos, si nos diese error significa que no los tenemos activados:
Abro uno de ellos con el siguiente comando, para que funcione debemos poner la ruta
completa donde se encuentra el binlog o ponernos o cambiar la dirección del binlog:
El contenido es el siguiente:
Clemente Cervantes Bustos
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET
@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170726 14:57:37 server id 1 end_log_pos 106 Start: binlog v 4,
server v 5.7.22-log created 170726 14:57:37 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
IeZ4WQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAA
AAAAAAAAAAAAAAAAAAAh5nhZEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170726 14:59:31 server id 1 end_log_pos 182 Query thread_id=2
exec_time=0 error_code=0
SET TIMESTAMP=1501095571/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1,
@@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET
@@session.character_set_client=8,@@session.collation_connection=8,@@se
ssion.collation_server=8/*!*/;
..
..
..
# at 14191
#170726 15:20:38 server id 1 end_log_pos 14311 Query
thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1501096838/*!*/;
insert into salary(name,dept) values('Ritu', 'Accounting')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
5.- Logs
5.1.- Logs en Linux El registro general de consultas (general_log) es un registro general de lo que está haciendo mysqld. El servidor escribe información en este registro cuando los clientes se conectan o desconectan, y registra cada instrucción SQL recibida de los clientes. El registro de consultas general puede ser muy útil cuando se sospecha que hay un error en un cliente y quiere saber exactamente qué envió el cliente a mysqld. Para activarlo deberemos irnos al fichero /etc/mysql/mysql.conf.d/mysqld.cnf, ahí
deberemos poner el general_log igual a 1. Para desactivarlo lo ponemos a 0 o comentamos la
línea. En general_log_file ponemos el fichero donde se guardará todo el registro del
general_log.
Clemente Cervantes Bustos
En long_query_time ponemos el tiempo máximo que queremos que duren las consultas, yo
pongo un tiempo muy bajo para que me salte un error indicándome que se ha realizado una
consulta que ha tomado demasiado tiempo hacerla.
Reiniciamos el servicio.
Comprobamos que funciona, para ello accedo con a MySQL con un usuario incorrecto.
Clemente Cervantes Bustos
Nos vamos al fichero donde general_log guarda los registros, /var/log/mysql/error.log
Aquí podemos ver como los [warnings] nos indican fallos o errores, en mi caso de los intentos
fallidos de conexión, y luego vemos [Note] que es la conexión exitosa.
Clemente Cervantes Bustos
5.2.- Logs en Windows Para editar el fichero de configuración de MySQL en Windows deberemos dirigirnos al
siguiente directorio: C:\ProgramData\MySQL\MySQL_Server_8.0\my.ini
Ponemos un 1 al general-log para activar el registro de errores y bajamos el tiempo de espera
al hacer una consulta en long_query_time si queremos ver registradas las consultas que
tardan más del tiempo que especifiquemos. Los errores los podemos ver el en fichero
DESKTOP-ULQ7EP0.log
Clemente Cervantes Bustos
Intentamos entrar con una contraseña errónea y nos vamos al registro de errores.
Vemos como se guardan todos los errores.
Clemente Cervantes Bustos
5.3.- Logs en Wamp Para configurar el binlog en Wamp deberemos irnos al siguiente direcotorio:
C:\wamp\bin\mysql\mysql5.5.24 (en mi caso es 5.5.24 porque esa es la versión de mi mysql)
y abrir el fichero my.ini.
Si está comentada la línea que marco en rojo la descomentamos, esta línea activa el registro
de errores y nos los manda a la dirección que indica: c:/wamp/logs/mysql.log.
Clemente Cervantes Bustos
Accedo de manera errónea al mysql y de forma correcta para ver cómo se registra. Nos vamos
al directorio donde se almacenan los errores: C:\wamp\logs y abrimos el fichero mysql.log.
Vemos que tenemos abajo dos (null) que son los intentos erróneos de acceder al mysql y luego
nos aparece un mensaje que pone: wampmysqld: ready for connections que nos indica que se
ha conectado bien al tercer intento.
Clemente Cervantes Bustos
6.- Acceso de forma remota El acceso de forma remota se hace igual en Windows, Linux y Wamp, solamente deberemos
tocar en los ficheros de configuración de cada uno lo mismo. Por eso, solo lo haré con Linux.
Clemente Cervantes Bustos
En Windows y en Wamp deberemos hacer lo mismo en sus respectivos ficheros de
configuración.
En Wamp:
En Windows:
Clemente Cervantes Bustos
Reiniciamos el servicio de MySQL para que se apliquen los cambios.
Hay que dar permisos específicos de acceso al usuario de la base de datos. Para ello
accedemos a la base de datos como lo hacemos siempre.
Clemente Cervantes Bustos
Le damos permisos a un usuario para que pueda acceder al MySQL de forma remota, puede
ser un usuario ya existente o un nuevo.
Vemos los usuarios que tenemos creados y los equipos desde los que se pueden conectar. Los
que ponen % se pueden conectar desde cualquier ordenador independientemente de la IP que
tengan, los que ponen localhost, solo se pueden desde el mismo equipo en el que está creado
el usuario y el usuario que pone 192.168.70.132 solo se puede conectar desde un equipo que
tenga esa IP.
Clemente Cervantes Bustos
Pruebo a conectarme remotamente, para ello deberé poner mysql seguido de las siguientes
opciones: -h seguido de la IP del equipo al que nos queremos conectar, -u seguido del usuario
con el que nos queremos conectar, -p seguido de la contraseña del usuario, si no ponemos la
contraseña nos la pedirá al intentar conectarse como ocurre en mi caso.
7.- Explicación del proyecto Trataré de elaborar una base de datos para un comercio el cual va a ser una tienda de ropa y
equipación deportiva. Me centraré sobre todo en vender al público diferente material que
previamente haya comprado a mayoristas.
8.- Texto Deportes-La Mancha La empresa tendrá empleados de los cuales guardaremos su DNI, su número de la
seguridad social, su nombre, su teléfono móvil, su teléfono fijo, sus apellidos, su
dirección, su email y su fecha de nacimiento.
Hay dos tipos distintos de empleados: los dependientes y los de la limpieza. De ambos
guardaremos su sueldo que es distinto. Los empleados son los encargados de vender la
ropa de la cual guardaremos su código, el tipo de prenda que es (pantalón largo,
pantalón corto, camiseta, chándal…) y el precio.
Clemente Cervantes Bustos
De todos los clientes que compren en la tienda se guardará el nombre, el email, un
teléfono, el código de la tarjeta descuento (si es que tienen una, en caso de no tenerla
este valor se dejará en blanco para esos clientes) y la dirección.
Cada vez que un cliente, indistintamente de cual, compre ropa quedará registrado el
tipo de ropa que ha comprado, la forma en la que el cliente ha pagado dicha compra
(con tarjeta descuento, en efectivo o con tarjeta) y la fecha en la que tuvo lugar.
Cada vez que un dependiente venda ropa quedará registrado la cantidad de ropa
vendida.
La ropa (sea la cantidad que sea) se guardará en estanterías de distinto tamaño. Las
estanterías estarán en un almacén del que guardaremos una dirección, una breve
descripción y un nombre. Cada estante en el almacén se identifica con dos letras y con
un tamaño en centímetros.
9.- Diagrama entidad relación
10.- Paso a tablas CLIENTES (DNI, CODIGO_TARJETA, TELEFONO, EMAIL, NOMBRE, DIRECCION)
Clemente Cervantes Bustos
ROPA (CODIGO, ID_DEPENDIENTES, TIPO, PRECIO, MARCA) ALMACEN (ID, DIRECCION, DESCRIPCION, NOMBRE) ESTANTERIA (ID, ID_ALMACEN, CODIGO_ROPA, TAMAÑO, LETRA) GUARDAR (ID_GUARDAR, CODIGO_ROPA, ID_ESTANTERIAS, CANTIDAD) DEPENDIENTES (ID, DNI_EMPLEADOS, COMISION) VENDER (ID_VENDER, ID_DEPENDIENTES, CODIGO_ROPA, ID_CLIENTES, CANTIDAD, TIPO_COMPRA, FECHA, FORMA_DE_PAGO) LIMPIEZA (ID, DNI_EMPLEADOS, PRODUCTOS_LIMPIEZA) EMPLEADOS (DNI, DIRECCION, SUELDO, EMAIL, FECHA_NACIMIENTO, NUM_SEG_SOC, NOMBRE, APELLIDOS, TELEFONO, DNI_JEFE) PROVEEDORES (ID_PROVEEDOR, TELEFONO, EMAIL, NOMBRE, DIRECCION) JEFE (ID, DNI_EMPLEADOS) SUMINISTRA (ID, ID_PROVEEDOR, CODIGO_ROPA)
11.- Creación de la base de datos Paso a crear la base de datos:
create database Deportes_la_Mancha;
use Deportes_la_Mancha;
---CLIENTES (DNI, CODIGO_TARJETA, TELEFONO, EMAIL, NOMBRE, DIRECCION, GASTO) create table ClIENTES (DNI int unsigned AUTO_INCREMENT PRIMARY KEY, CODIGO_TARJETA
decimal (16) not null, TELEFONO decimal (9) not null, EMAIL varchar (40), NOMBRE varchar
(20), DIRECCION varchar (50))engine=innodb;
---ALMACEN (ID, DIRECCION, DESCRIPCION, NOMBRE) create table ALMACEN (ID int unsigned AUTO_INCREMENT PRIMARY KEY, DIRECCION varchar
(50), DESCRIPCION varchar (250), NOMBRE varchar (15)) engine=innodb;
Clemente Cervantes Bustos
---PROVEEDORES (ID, TELEFONO, EMAIL, NOMBRE DIRECCION) create table PROVEEDORES (ID int unsigned AUTO_INCREMENT PRIMARY KEY, TELEFONO
decimal (9) not null, EMAIL varchar (50), NOMBRE varchar (100), DIRECCION varchar (100))
engine=innodb;
---EMPLEADOS (DNI, DIRECCION, SUELDO, EMAIL, FECHA_NACIMIENTO, NUM_SEG_SOC, NOMBRE, APELLIDOS, TELEFONO, DNI_JEFE) create table EMPLEADOS (DNI int unsigned AUTO_INCREMENT PRIMARY KEY, DIRECCION
varchar (50), SUELDO decimal (6,2) not null, EMAIL varchar (40), FECHA_NACIMIENTO varchar
(10), NUM_SEG_SOC varchar (12), NOMBRE varchar (20), APELLIDOS varchar (50), TELEFONO
decimal (9) not null, DNI_JEFE decimal (9) not null) engine=innodb;
---DEPENDIENTES (ID, COMISION, DNI_EMPLEADOS) create table DEPENDIENTES (ID int unsigned AUTO_INCREMENT PRIMARY KEY, COMISION
decimal (6,2) not null, DNI_EMPLEADOS INT UNSIGNED, FOREIGN KEY (DNI_EMPLEADOS)
REFERENCES EMPLEADOS (DNI) ON DELETE CASCADE ON UPDATE) engine=innodb;
---ROPA (CODIGO, ID_DEPENDIENTES, TIPO, PRECIO, MARCA) create table ROPA (CODIGO int unsigned AUTO_INCREMENT PRIMARY KEY, TIPO varchar (20),
PRECIO decimal (6,2) not null, MARCA varchar (20), ID_DEPENDIENTES INT UNSIGNED, foreign
key (ID_DEPENDIENTES) references DEPENDIENTES (ID) ON DELETE CASCADE ON UPDATE
CASCADE) engine=innodb;
---ESTANTERIA (ID, TAMAÑO, LETRA, ID_ALMACEN, CODIGO_ROPA) create table ESTANTERIA (ID int unsigned AUTO_INCREMENT PRIMARY KEY, TAMANO varchar
(10), LETRA varchar (1), ID_ALMACEN INT UNSIGNED, FOREIGN KEY (ID_ALMACEN)
REFERENCES ALMACEN (ID) ON DELETE CASCADE ON UPDATE CASCADE, CODIGO_ROPA INT
Clemente Cervantes Bustos
UNSIGNED, FOREIGN KEY (CODIGO_ROPA) REFERENCES ROPA (ID) ON DELETE CASCADE ON
UPDATE CASCADE) engine=innodb;
---LIMPIEZA (ID, PRODUCTOS_LIMPIEZA, DNI_EMPLEADOS) create table LIMPIEZA (ID int unsigned AUTO_INCREMENT PRIMARY KEY,
PRODUCTOS_LIMPIEZA varchar (50), DNI_EMPLEADOS INT UNSIGNED, FOREIGN KEY
(DNI_EMPLEADOS) REFERENCES EMPLEADOS (DNI) ON DELETE CASCADE ON UPDATE
CASCADE) engine=innodb;
---VENDER (ID, ID_DEPENDIENTES, CODIGO_ROPA, ID_CLIENTES, CANTIDAD, TIPO_COMPRA, FECHA, FORMA_DE_PAGO) create table VENDER (ID int unsigned AUTO_INCREMENT PRIMARY KEY, CANTIDAD varchar (20), TIPO_COMPRA varchar (20), FECHA varchar (10), FORMA_DE_PAGO varchar (20), ID_DEPENDIENTES INT UNSIGNED, FOREIGN KEY (ID_DEPENDIENTES) REFERENCES DEPENDIENTES (ID) ON DELETE CASCADE ON UPDATE CASCADE, CODIGO_ROPA INT UNSIGNED, FOREIGN KEY (CODIGO_ROPA) REFERENCES ROPA (ID) ON DELETE CASCADE ON UPDATE CASCADE, ID_CLIENTES INT UNSIGNED, FOREIGN KEY (ID_CLIENTES) REFERENCES CLIENTES (ID) ON DELETE CASCADE ON UPDATE CASCADE) engine=innodb;
---GUARDAR (ID, CANTIDAD, CODIGO_ROPA, ID_ESTANTERIAS) create table GUARDAR (ID int unsigned AUTO_INCREMENT PRIMARY KEY, CANTIDAD varchar
(20), CODIGO_ROPA INT UNSIGNED, FOREIGN KEY (CODIGO_ROPA) REFERENCES ROPA (ID) ON
DELETE CASCADE ON UPDATE CASCADE, ID_ESTANTERIAS INT UNSIGNED, FOREIGN KEY
(ID_ESTANTERIAS) REFERENCES ESTANTERIA (ID) ON DELETE CASCADE ON UPDATE CASCADE)
engine=innodb;
---SUMINISTRA (ID, ID_PROVEEDOR, CODIGO_ROPA) create table SUMINISTRA (id int unsigned AUTO_INCREMENT PRIMARY KEY, ID_PROVEEDOR
INT UNSIGNED, FOREIGN KEY (ID_PROVEEDOR) REFERENCES PROVEEDORES (ID) ON DELETE
CASCADE ON UPDATE CASCADE, CODIGO_ROPA INT UNSIGNED, FOREIGN KEY (CODIGO_ROPA)
REFERENCES ROPA (ID) ON DELETE CASCADE ON UPDATE CASCADE) engine=innodb;
Clemente Cervantes Bustos
---JEFE (ID, DNI_EMPLEADOS) create table JEFE (id int unsigned AUTO_INCREMENT PRIMARY KEY, DNI_EMPLEADOS int
unsigned, foreign key (DNI_EMPLEADOS) references EMPLEADOS (DNI) ON DELETE CASCADE
ON UPDATE CASCADE);
12.- Exportación de la base de datos Vamos a crear una exportación de la base de datos, esto nos servirá de copia de seguridad, ya
que tendremos creado tanto las tablas, como los datos introducidos dentro.
13.- Creación de usuario y asignación de permisos Creamos al usuario clemente que podrá acceder desde todos los equipo y tendrá acceso a
todas las bases de datos ya que será al usuario administrador.
Creamos al usuario juan que podrá visualizar los datos de los dependientes, se conecta desde
la IP 192.168.1.10 con la clave inves.
Clemente Cervantes Bustos
Creamos al usuario Carmen que tendrá acceso a toda la base de datos del taller con permiso
de actualizar, crear y eliminar filas. Se conecta desde la IP 192.168.1.20 con la clave inves.
Creamos al usuario pepe que solo podrá ver los nombres de los clientes. Se conecta desde la IP
192.168.1.30 con la clave inves.
Configuramos las cuotas al usuario para el usuario carmen la cual solo podrá realizar tres conexiones a la hora, una solo actualización, dos consultas y solo podrá tener una conexión activa. GRANT ALL ON deportes_la_mancha.* TO ‘carmen’@’192.168.1.20’ identified by “inves” WITH MAX_QUERIES_PER_HOUR 2 MAX_UPDATES_PER_HOUR 1 MAX_CONNECTIONS_PER_HOUR 3 MAX_USER_CONNECTIONS 1;
Clemente Cervantes Bustos
Creo los usuarios marge y lisa. Marge podrá acceder desde cualquier equipo y lisa solo podrá
acceder desde el equipo local, además, no tendrá contraseña.
A lisa le daré permiso de select en todas las tablas de todas las bases de datos y en todas las
tablas y permiso de drop sobre la base de datos películas.
A marge le daré permiso de update sobre todas las bases de datos y permiso select para ver la
descripción de los almacenes de la base de datos ventas.
14.- Funciones --- Creamos un función que pasando el tipo de un producto nos diga el precio que tiene.
Clemente Cervantes Bustos
Drop function if EXISTS precio;
delimiter //
create function precio(minombre decimal(25,2)) returns decimal(25,2)
BEGIN
declare respuesta decimal(25,2);
select precio from ropa where tipo=minombre into respuesta;
return respuesta;
end;//
delimiter ;
--- Creamos una función que pasando el DNI de un cliente nos devuelva su número de
teléfono.
Drop function if EXISTS modelo;
delimiter //
create function modelo(minombre varchar(200)) returns varchar(200)
BEGIN
declare respuesta int unsigned;
select numero from clientes where dni=minombre limit 1 into respuesta;
return respuesta;
end;//
delimiter ;
Clemente Cervantes Bustos
15.- Procedimientos --- Creamos un procedimiento que nos diga el producto más caro que tenemos
drop procedure pro_max;
delimiter //
create procedure pro_max()
begin
declare fin boolean default false;
declare minombre varchar(20);
declare mitfn varchar(20);
declare migasto int;
declare maxigasto integer default 0;
declare maxnom varchar(20);
declare maxtfn varchar(20);
DECLARE c1 CURSOR FOR select marca, referencia, precio from ropa;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=true;
open c1;
while not fin DO
FETCH c1 INTO minombre, mitfn, migasto;
if not fin then
if migasto > maxigasto then
set maxigasto=migasto;
set maxtfn=mitfn;
Clemente Cervantes Bustos
set maxnom=minombre;
end if;
end if;
end while;
close c1;
select maxnom as “marca”, maxtfn as “referencia”, maxigasto as “precio”;
end;//
delimiter ;
Clemente Cervantes Bustos
--- Creamos un procedimiento que saque cinco clientes aleatorios, los inserte en una tabla
especial para los sorteos y de esta tabla sacar dos ganadores.
drop procedure if exists sorteo;
delimiter //
create procedure sorteo()
begin
declare resultado varchar(50);
declare bucle int default value 0;
declare midni int;
while bucle<5
do
select DNI from CLIENTES order by rand() limit 1 into midni;
insert into sorteo (dni) values (midni);
set bucle = bucle+1;
end while;
select dni from sorteo order by rand() limit 2;
end;//
delimiter ;
Clemente Cervantes Bustos
--- Creamos un procedimiento que nos diga lo que tenemos en stock agrupado por marcas.
drop procedure if exists stock;
delimiter //
create procedure stock()
begin
declare minombre varchar(15);
declare mimarca varchar(15);
declare fin bool default 0;
declare cursor1 cursor for select tipo, marca from ropa group by marca;
declare continue handler for not found set fin=1;
open cursor1;
repeat
fetch cursor1 into minombre, mimarca;
if fin=0 then
if mimarca > 1 then
select concat ('Del tipo ', minombre, ' tenemos ', mimarca, ' unidades ') as 'Stock del almacen';
elseif mimarca = 1 then
select concat ('Del tipo ', minombre, ' tenemos ', mimarca, ' unidad ') as 'Stock del almacen';
elseif mimarca =0 then
select concat('Del tipo ', minombre, ' no hay ninguna unidad ') as 'Stock del almacen';
end if;
Clemente Cervantes Bustos
end if;
until fin end repeat;
close cursor1;
end;//
delimiter ;
16.- Triggers --- Creamos un trigger que nos añada a los nuevos usuario que vamos añadiendo a una tabla
distinta, ya que a estos usuario lo usaremos para enviarle un mensaje con las nuevas ofertas,
por lo que necesitamos almacenar el número y el email junto con el DNI y nombre
DROP TRIGGER if exists newcli; Delimiter // CREATE TRIGGER newcli AFTER INSERT ON CLIENTES FOR EACH ROW BEGIN INSERT INTO newcli VALUES (NEW.DNI, NEW.nombre, NEW.email); END;// delimiter ;
Clemente Cervantes Bustos
--- Creamos un trigger que nos añada en una tabla los proveedores que han sido eliminados.
Esto lo hacemos para evitar el borrado accidental y así no perder los datos.
DROP TRIGGER if exists delprovee; delimiter // CREATE TRIGGER delprovee AFTER DELETE ON PROVEEDORES FOR EACH ROW BEGIN INSERT INTO delprovee VALUES ( OLD.nombre, OLD.telefono, OLD.direccion, OLD.email); END;// delimiter ;
17.- Eventos ---Para poder crear eventos primero debemos habilitar esta opción, para ello ejecutamos el
siguiente comando en MySQL:
--- Creamos un evento que en Navidad cargue una paga extra a los trabajadores.
create event extra on Schedule every 12 month
starts “2019-12-25 00:00:00”
enable
do
update deportes_la_mancha.EMPLEADOS set SUELDO=SUELDO+120;
Clemente Cervantes Bustos
--- Creamos un evento que calcule una vez al mes un descuento de un producto aleatorio
para descontárselo del precio y así hacer el día de descuentos. Para ello, a parte de un
evento, también crearemos una función que genere una marca de ropa aleatoriamente.
CREATE EVENT descuento ON SCHEDULE EVERY 1 MONTH STARTS '2019-03-03 09:00:00'
DO UPDATE ropa SET precio=precio-(precio*0.21) WHERE marca IN (SELECT aleatorio());
--- Creamos la función que sacará un producto aleatorio para el evento del día sin IVA
DROP FUNCTION IF EXISTS aleatorio;
DELIMITER //
CREATE FUNCTION aleatorio() RETURNS VARCHAR(30)
BEGIN
DECLARE var VARCHAR(30);
SET var=(SELECT MARCA FROM ROPA ORDER BY RAND() LIMIT 1);
RETURN var;
END ;//
DELIMITER ;
--- Creamos un evento que añada un proveedor nuevo en la tabla de proveedores.
CREATE EVENT insertar_proveedor ON SCHEDULE AT CURRENT_TIMESTAMP
DO INSERT INTO PROVEEDORES(ID_PROVEEDOR, DIRECCION, TELEFONO, NOMBRE, EMAIL)
VALUES (‘25’, ‘Calle Toledo’, ‘926929268’, ‘Catalina’, ‘[email protected]’ );
Clemente Cervantes Bustos
18.- Vistas --- Creamos una vista que liste la ropa más vendida, esto lo haremos en base al código de la
ropa vendida.
CREATE VIEW top_ventas AS
SELECT TIPO, PRECIO, MARCA FROM ROPA WHERE CODIGO IN (SELECT CODIGO_ROPA FROM
VENDER ORDER BY CANTIDAD DESC);
--- Creamos una vista que nos diga que empleados ganan más dinero.
CREATE VIEW top_sueldos AS
SELECT NOMBRE, APELLIDOS, SUELDO FROM EMPLEADOS ORDER BY SUELDO DESC;